Pass a directory variable to .read?

(1) By Leam (LeamHall) on 2021-12-28 03:48:29 [link] [source]

I have an SQL file that uses .read to build the schema and another file .read to populate the data. For example:

.read 'data/write_cultures_table.sql' .read 'data/add_cultures_data.sql'

Called with:

sqlite3 test/data/test_people.db < data/write_people_db.sql

What I'd like to do is make the "data/" bit a variable, so that I could call it with a "test/data/" directory, and populate a test DB with the same process as the production DB. Is that possible? Or is there a better way to accomplish the same thing?

Thanks!

Leam

(2) By Simon Slavin (slavin) on 2021-12-28 04:40:51 in reply to 1 [source]

With regard to 'a better way' in your post, I would recommend you learn how environment variables work in whatever shell you're using. You may be able to do things like

export SQLDIR="data"
echo "Working in directory $SQLDIR ."
sqlite3 test/$SQLDIR/test_people.db < $SQLDIR/write_people_db.sql

This example would work only in bash. Other shells may use a different format for setting and referring to variables. Read up on whatever shell you're using.

(3) By Larry Brasfield (larrybr) on 2021-12-28 05:04:45 in reply to 1 [link] [source]

This is an option in addition to Simon's (which is simple and effective.)

As explained at the SQLite CLI shell doc, in section 8.2 "Reading SQL from a file", the .read command, instead of getting input from a named file, can get it from the (stdout) output of a subprocess. That subprocess, which may be a shell or other program running code of your choosing, can decide what to emit (into .read's maw) in all the wonderous ways a programmer might devise. Those ways could include use of variables, or possibly arguments to whatever invocation launches that subprocess.

(4) By Leam (LeamHall) on 2021-12-29 23:58:26 in reply to 1 [link] [source]

My apologies for the lack of explicit clarity. I'm asking if the use of variable substitution can be done in an SQL file, not a shell file.

(5) By Larry Brasfield (larrybr) on 2021-12-30 00:34:07 in reply to 4 [link] [source]

By "SQL file", I take it you mean a SQLite shell input file. The answer, then, is: Not at this time. It would be nice, though. I've been experimenting with keeping a sqlite3 shell running as a Bash co-process, so that a high-falutin shell can do SQLite stuff. It look promising.