On Sun, 15 Apr 2018 14:38:33 +0100, Martin <mdransfield...@btinternet.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
