.read - use variables?

(1) By ThanksRyan on 2022-06-24 00:58:49 [link] [source]

Hi,

I have a script that invokes SQLite cli through the .read command.

Is it possible to use variables in the text file?

.import --csv /home/ryan/www/default.website/reports/Jun2022.csv Mytable

I'd like to have the Jun2022 be a variable so that I don't need to edit the file
at month's end.

Thanks!

(2) By jake on 2022-06-24 01:39:59 in reply to 1 [source]

Perhaps you could try something like this, which copies the last month's file to /tmp/tmp.csv and then just imports that:

SELECT WriteFile('/tmp/tmp.csv', data)
  FROM fsdir('/home/ryan/www/default.website/reports/')
 WHERE name LIKE '%' || CASE StrFTime('%m', 'now', 'localtime', '-1 month') 
                          WHEN '01' THEN 'Jan'
                          WHEN '02' THEN 'Feb'
                          WHEN '03' THEN 'Mar'
                          WHEN '04' THEN 'Apr'
                          WHEN '05' THEN 'May'
                          WHEN '06' THEN 'Jun'
                          WHEN '07' THEN 'Jul'
                          WHEN '08' THEN 'Aug'
                          WHEN '09' THEN 'Sep'
                          WHEN '10' THEN 'Oct'
                          WHEN '11' THEN 'Nov'
                          WHEN '12' THEN 'Dec'
                        END || StrFTime('%Y', 'now', 'localtime', '-1 month') || '.csv'

.import --csv /tmp/tmp.csv Mytable

(3) By Keith Medcalf (kmedcalf) on 2022-06-24 01:56:48 in reply to 2 [link] [source]

You should insert the modifier 'start of month' into the strftime modifiers such that they read:

strftime(..., 'now','localtime','start of month','-1 month')

If today is a day (day number of month) that is greater than the last day number of the previous month, then '-1 month' results in the same month as you started with, not the prior month.

You need to convert the localtime back to the start of the month (day 1) so that when you go back a month, you actually change the month number.

sqlite> select datetime('2022-03-31 12:00:00', 'localtime', '-1 month');
┌──────────────────────────────────────────────────────────┐
│ datetime('2022-03-31 12:00:00', 'localtime', '-1 month') │
├──────────────────────────────────────────────────────────┤
│ '2022-03-03 06:00:00'                                    │
└──────────────────────────────────────────────────────────┘
sqlite> select datetime('2022-03-31 12:00:00', 'localtime', 'start of month', '-1 month');
┌────────────────────────────────────────────────────────────────────────────┐
│ datetime('2022-03-31 12:00:00', 'localtime', 'start of month', '-1 month') │
├────────────────────────────────────────────────────────────────────────────┤
│ '2022-02-01 00:00:00'                                                      │
└────────────────────────────────────────────────────────────────────────────┘

(4) By jake on 2022-06-24 02:42:54 in reply to 3 [link] [source]

Thanks Keith. This is good to know.

(5) By ThanksRyan on 2022-06-24 04:15:56 in reply to 4 [link] [source]

So with either method, I couldn't have something like this in the .read file?

monthyear=$(date +"%b%Y")

.import --csv /home/ryan/www/default.website/reports/$monthyear.csv Mytable

(6) By jake on 2022-06-24 04:39:01 in reply to 5 [link] [source]

I don't believe it's possible to use variables in dot commands like this. Another alternative might be to create a symbolic link to the latest report e.g.

monthyear=$(date +"%b%Y")
ln -s /home/ryan/www/default.website/reports/$monthyear.csv /home/ryan/www/default.website/reports/latest.csv

.import --csv /home/ryan/www/default.website/reports/latest.csv Mytable

(7) By Keith Medcalf (kmedcalf) on 2022-06-24 14:55:02 in reply to 5 [link] [source]

Yes, of course you can. You can put whatever you want in any file anywhere at anytime.

It will not make any difference, however, and will still not achieve anythng beyond error messages.

Putting a prayer in the file is allowed as well. It will not, however, have any meaningful effect, other than causing error messages.

(8) By ThanksRyan on 2022-06-24 16:38:02 in reply to 7 [link] [source]

Yes, of course you can.

Of course!

The proper question was answered by you:

It will not make any difference, however, and will still not achieve anythng beyond error messages.

(9) By ThanksRyan on 2022-06-24 16:38:47 in reply to 1 [link] [source]

It would be really neat if variables could be used in the .read file, so consider this a feature request. :D

(10) By Tim Streater (Clothears) on 2022-06-24 17:03:03 in reply to 9 [link] [source]

Can't you construct your SQLite3 CLI script using a scripting language like PHP or similar?

(11) By Larry Brasfield (larrybr) on 2022-06-24 18:31:24 in reply to 9 [link] [source]

With the CLI built on the cli_extension branch as sqlite3x, after loading the Tcl extension built for the tcl_shell_extension make target, it is simple to set variables and use them in dot commands. For example:

$ make sqlite3x tcl_shell_extension
$ ./sqlite3x
 <banner>
sqlite> .shxload ./tclshext
sqlite> .. set myvar Hello
sqlite> ..print $myvar
Hello

(12) By ThanksRyan on 2022-06-25 01:41:42 in reply to 11 [link] [source]

excellent, thank you.