SQLite Forum

sqlite3 .shell - conditional executions with `[[ expr ]]` constructs
Login

sqlite3 .shell - conditional executions with `[[ expr ]]` constructs

(1) By peter on 2021-01-20 22:11:48 [link] [source]

I wanted to execute some conditional processing in sqlite3 shell (in fact fossil sql shell due to presence of repository specific functions) with compound, double bracket [[ command construct as:

 set variables; [[ expr1 && expr2 ... ]] && { command if true ; } || { commands if false ; }

with expressions mostly file/directory existence check and simple string comparisons.

I could not figure why I get errors with double brackets, as sqlite> .system $SHELL --version ; was returning GNU bash, version 4.4.20(1)-release (x86_64-pc-linux-gnu).

After some digging it seems that "true" shell involved in sqlite3 .shell execution in my case is /bin/dash lacking [[ compound command.

sqlite> .system $SHELL --version ;
GNU bash, version 4.4.20(1)-release (x86_64-pc-linux-gnu)

sqlite> .shell echo "`readlink -f $(which $0)`";
/bin/dash

So far I am unable to figure out how to make sqlite3 .shell executable constructs of above mentioned structure in my environment. Experiments with simple test [ command brought just many different errors I could not make sense of.

Example below seems to try to execute "doc" as a command if it is as a first thing on the new line, not if there is a space before it, why?

sqlite> .shell fin=file1.doc ; [ "${fin##*.}" = "doc" ] ; echo "$?\n${fin##*.}" ; echo "$fin" ;
0
sh: 2: doc: not found
file1.doc

No error with space before doc

sqlite> .shell fin=file1.doc ; [ "${fin##*.}" = "doc" ] ; echo "$?\n ${fin##*.}" ; echo "$fin" ;
0
 doc
file1.doc

Attempts to "force" /bin/bash to have [[ available in sqlite3 shell failed me with not expanding defined shell variables and other errors.

sqlite> .shell /bin/bash -c 'fin=file1.doc ; [[ "${fin##*.}" = "doc" ]] ; echo "$?\n${fin##*.}" ; echo "$fin"' ;
/bin/bash: -c: line 0: conditional binary operator expected
/bin/bash: -c: line 0: syntax error near `doc'
/bin/bash: -c: line 0: `fin=file1.doc ; [[  = doc ]] ; echo 0n ; echo '
System command returns 256

Constructs seem to be ok in regular bash

$ fin=file1.doc ; [ "${fin##*.}" = "doc" ] ; echo "$?\n${fin##*.}" ; echo "$fin"
0\ndoc
file1.doc

$ fin=file1.doc ; [[ "${fin##*.}" = "doc" ]] ; echo "$?\n${fin##*.}" ; echo "$fin"
0\ndoc
file1.doc

I would appreciate suggestions how to make 'extended test' [[ expr ]] constructs available for sqlite .shell or example of evaluation of multiple conditions with simple test [ operational in sqlite3 .shell.

Thanks, Peter

environment

Ubuntu 18.04.5 LTS and

sqlite3
SQLite version 3.35.0 2021-01-18 12:35:16

fossil sql 
SQLite version 3.35.0 2021-01-18 12:35:16

(2) By Larry Brasfield (LarryBrasfield) on 2021-01-21 03:08:17 in reply to 1 [source]

After some digging it seems that "true" shell involved in sqlite3 .shell execution in my case is /bin/dash

If you had dug into shell.c, you would have found that the .shell meta-command merely defers to whatever the system() C library call does. That is likely to vary from one system to another for bare shell-like commands, but I dare say it is enough to let you specify that a particular shell be run if you simply name it as the first word in the arguments to .shell.

(4) By peter on 2021-01-21 14:48:23 in reply to 2 [link] [source]

it is enough to let you specify that a particular shell be run if you simply name it as the first word in the arguments to .shell.

Thank you for your suggestion. I wish I know the syntax for that or a way to reliable iterate by myself to this/another solution. My attempts so far end up in vain.

sqlite> .shell /bin/bash; fin=file1.doc ; [[ "${fin##*.}" = "doc" ]] ; echo "$?\n${fin##*.}" ; echo "$fin" ;
$ exit   #<-- user typed on prompt 
exit
sh: 1: [[: not found
127
sh: 2: doc: not found
file1.doc
sqlite>

sqlite> .shell #!/bin/bash ; fin=file1.doc ; [[ "${fin##*.}" = "doc" ]] ; echo "$?\n${fin##*.}" ; echo "$fin" ;

sqlite>

sqlite> .shell #!/bin/bash fin=file1.doc ; [[ "${fin##*.}" = "doc" ]] ; echo "$?\n${fin##*.}" ; echo "$fin" ;

sqlite>

and many more...

(5) By Larry Brasfield (LarryBrasfield) on 2021-01-21 18:46:25 in reply to 4 [link] [source]

I have to say that Warren's suggestion makes more sense than what follows here. Nevertheless:

In the sqlite3 shell, this does what you apparently intend: .output /tmp/furd .print fin=file1.doc .print [[ \"${fin##.}\" = \"doc\" ]] .print echo \"$?\n${fin##.}\" .print echo \"$fin\" .output stdout .shell chmod +x /tmp/furd .shell /bin/bash -c /tmp/furd .shell rm /tmp/furd . Note that the backslashes are understood by the sqlite3 shell meta-command scanner to mean "Don't treat the next character specially." Without being escaped, the double-quotes are used to group text and then they vanish from the result. If I was not too lazy to try it, I might have found a way to do this without a temporary file, but it is too hard to see what is happening that way for my taste.

(6) By peter on 2021-01-21 19:29:13 in reply to 5 [link] [source]

Thank you for your efforts and preparing this demonstration.

Now I see I can generate shell script and have it execute actions I want without too much messing with sqlite3 .shell meta-command or dash peculiarities.

Maybe I will figure out later, how to generate that all in once oneliner, I was hoping for.

Regarding Warren's suggestion, as of now I am not clear how to apply it in my situation. I have a fossil repo with increasing set of thousands of versioned objects (mostly various documents/scripts, ...) in different "stages". Operations as adding new stuff and extracting, combining different elements of those is gradually evolving, intermingled with manual processing of some documents. I want to derive most actions from info already present in repo, so the idea is to have SELECT generating those scripts. For simple actions I have SELECT's I wanted to add more conditional logic to it and got stuck with this bash dash, double bracket, single bracket, and so on fights.

Certainly I love to have this done easier way.

Thanks

sqlite> 
sqlite> .output /tmp/furd
sqlite> .print fin=file1.doc
sqlite> .print [[ \"${fin##.}\" = \"doc\" ]]
sqlite> .print echo \"$?\n${fin##.}\"
sqlite> .print echo \"$fin\"
sqlite> .output stdout
sqlite> .shell chmod +x /tmp/furd
sqlite> .shell /bin/bash -c /tmp/furd
1
file1.doc
file1.doc
sqlite> .shell cat /tmp/furd
fin=file1.doc
[[ "${fin##.}" = "doc" ]]
echo "$?
${fin##.}"
echo "$fin"
sqlite> .shell rm /tmp/furd
sqlite>

(7) By Stephan Beal (stephan) on 2021-01-21 19:41:29 in reply to 6 [link] [source]

Certainly I love to have this done easier way.

Such as what Warren suggested: invert the equation and call sqlite3 from shell code instead of calling shell code from sqlite. The path you're taking now is... well, That Way Lies Madness.

(8) By Warren Young (wyoung) on 2021-01-22 00:15:38 in reply to 6 [link] [source]

I am not clear how to apply [your suggestion] in my situation.

That's because you're presenting us with an XY problem, telling us how your actual problem must be solved, then telling us about the problems you've run into trying to solve the actual problem with that method. Instead, tell us precisely what end goal you're trying to accomplish, so we can suggest ways of tackling that entirely separate problem.

Your actual use case isn't to solve the Dash vs Bash problem, nor to sort out test(1) vs double-bracket expression limitations, nor to fight with the conflicting punctuation and whitespace escaping rules of the SQLite shell vs that of POSIX shells. Tell us about this document processing problem, not about your increasingly convoluted attempts to solve the shell scripting problem.

I suspect that what you actually need is a GNU Makefile or a shell script iterating over the contents of a Fossil manifest file, but until I get those details, those are just guesses based on vague data.

I'd attempt to invert your latest example, but it doesn't even have any SQL statements or Fossil commands in it. I mean, I could write a shell script that itself wrote and executed another shell script, as your above code does, but I'm not seeing the point of doing the work.

Let's pop a few levels of indirection and get concrete, okay?

(3.2) By Warren Young (wyoung) on 2021-01-21 03:33:24 edited from 3.1 in reply to 1 [link] [source]

conditional processing in sqlite3 shell

Why not invert it? SQLite is designed primarily to be driven by a program other than its command shell.

The simplest method is to put your logic in a separate shell script that makes fossil shell calls under control of the logic in the script. Worst case, you can fall down to fossil sql calls.

If that's not powerful enough, Fossil's 2-clause BSD license and straightforward C code will let you extract its custom functions and whatever support infrastructure (such as SQLite) needed to run those functions against the Fossil repo DB.

/bin/dash

Dash provides little more than what POSIX specifies, so it doesn't understand [[ constructs. You're lucky it has built-in test(1)/[ at all. :)

This gets us to Larry's point about system(3): it's documented to use /bin/sh, which is Dash on your system. There is no documented way to make it use another shell, almost certainly for security reasons.

Even if you move this to another system where /bin/sh is Bash or Zsh, I'll probably drop all of its non-POSIX features because of the argv[0] value. Only when called by the shell's full name do you get the post-POSIX extensions.

.shell /bin/bash -c

Bleah. Quit trying to arm-twist it into working inside out and upside down. You're just going to make yourself dizzy.