CLI thoughts

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

CLI thoughts

Martin-2

  I am a relatively new user of sqlite.  My current use is to learn
sqlite sql to solve solitaire battleship puzzles. I am not a programmer,
so use the command line interface on a Mac.

  As a result I wonder whether it is worth considering a few
amendments to the commands in the CLI tool. My first thoughts being:

  1. adding an escape option to <LIKE> where it is available; such as
     sha3sum. (Naively I have defined some tables with names begining
     '_'. I know I can rename them, but I prefer not to.)

  2. adding a new command to allow a simple variable to be set.
     For example,

       .let a ...

     where ... is a bona fide dot command (less the dot) that makes
     usage sense.  For example, I find the .cd command tedious in
     practice. Global exported variables are not interned so such as
     .cd $HOME fails.

     Perhaps a sequence like the following:

       .let a system echo $dev
       .cd -let a

     Here, -let as an option was my initail (no thought) idea, but
     thinking of an alternative begins to break the association
     between setting (.let) and using.  In one's mind read '.let a' as
     'let the variable a be', and '-let a' as 'use the value of letter
     a'.

     Further, I thought restricted permissible variable names 'a'
     through to 'z' would be plenty. Certainly better than none as
     seems to be the case currently.  Similarly, they should be
     limited in size to a pathname. Or possibly set the size using
     .limit?

     Alternative usage as a dot command option could be
       .cd -val a
     or
       .cd $a
     or
       .cd *a

     Obviously these variable names do not exist unless set.

     Example (maybe via .read):
       .once .dat
       select date('now');
       .let f system echo "words-`cat .dat`.txt"
       .once -let f
       select word from words order by 1;

     The system call argument maybe just 'todaysfile' which executes.

  3. the .load command does not have a complement .loaded; how does
     one check?

Well, just thinking out loud.

Thanks, Martin.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CLI thoughts

Kees Nuyt
On Sun, 15 Apr 2018 14:38:33 +0100, Martin
<[hidden email]> wrote:

>
>  I am a relatively new user of sqlite.  My current use is to learn
> sqlite sql to solve solitaire battleship puzzles. I am not a programmer,
> so use the command line interface on a Mac.
>
>  As a result I wonder whether it is worth considering a few
> amendments to the commands in the CLI tool. My first thoughts being:
>
>  1. adding an escape option to <LIKE> where it is available; such as
>     sha3sum. (Naively I have defined some tables with names begining
>     '_'. I know I can rename them, but I prefer not to.)

https://www.sqlite.org/lang_expr.html#like
, the ESCAPE keyword.

>  2. adding a new command to allow a simple variable to be set.
>     For example,
>
>       .let a ...
>
>     where ... is a bona fide dot command (less the dot) that makes
>     usage sense.  For example, I find the .cd command tedious in
>     practice. Global exported variables are not interned so such as
>     .cd $HOME fails.

Use a temp table (local to the database session):
CREATE TEMP TABLE vars (
        var TEXT PRIMARY KEY NOT NULL
, val
) WITHOUT ROWID;

Assign a new value:
INSERT OR REPLACE INTO vars (var,val) VALUES ('var1','value1');
Retrieve:
SELECT val FROM vars WHERE var='var1');

For more complex cases, people usually use a host language, like
php, perl, python, tcl, lua, C, dotnet, or even a shell script.
SQLite originally was a database extension to tcl, and that is
powerful.

>     Perhaps a sequence like the following:
>
>       .let a system echo $dev
>       .cd -let a
>
>     Here, -let as an option was my initail (no thought) idea, but
>     thinking of an alternative begins to break the association
>     between setting (.let) and using.  In one's mind read '.let a' as
>     'let the variable a be', and '-let a' as 'use the value of letter
>     a'.
>
>     Further, I thought restricted permissible variable names 'a'
>     through to 'z' would be plenty. Certainly better than none as
>     seems to be the case currently.  Similarly, they should be
>     limited in size to a pathname. Or possibly set the size using
>     .limit?
>
>     Alternative usage as a dot command option could be
>       .cd -val a
>     or
>       .cd $a
>     or
>       .cd *a
>
>     Obviously these variable names do not exist unless set.
>
>     Example (maybe via .read):
>       .once .dat
>       select date('now');
>       .let f system echo "words-`cat .dat`.txt"
>       .once -let f
>       select word from words order by 1;
>
>     The system call argument maybe just 'todaysfile' which executes.

You could write and load an extension function or a virtual
table function in C to retrieve exported environment variables.
Somebody probably has done that already. However, those will
only work within SQL, not in dot commands.

The problem with expanding the dot command intterface to a full
scripting language is that there is no end to it, people will
ask for more, like conditional statements, control structures
etc..

>  3. the .load command does not have a complement .loaded; how does
>     one check?

I guess the easiest way is to just load the extension again, if
you are not sure it is loaded at some point.
If it fails to load, an exception is raised.

> Well, just thinking out loud.

No problem, so do I ;)

--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CLI thoughts

E.Pasma
In reply to this post by Martin-2
Martin wrote:
...
      Example (maybe via .read):
        .once .dat
        select date('now');
        .let f system echo "words-`cat .dat`.txt"
        .once -let f
        select word from words order by 1;
..

Sorry for replying only to this single point. The proposed ".let"  
command is not essential here. As a student I was impressed by the  
SNOBOL programming language because it allowed "dynamic code  
generation" (writing before the foot). From the SQLite command line  
this works too:

  .once tmp1.sql
values ('.once words.' || date() || '.dat'),
            ('select * from words;');
sqlite> .read tmp1.sql


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users