Generating Ranges in SQLite

[Gianni]

at this link (https://www.geekytidbits.com/date-range-table-sqlite/) there is an example on how to generate ranges using an SQL query in SQLite so to generate a recordset "on the fly" even if you don't have a table.
This script, for example, generates a recordset of specific days of the week (e.g. the list of Mondays contained within 2 dates)

#include <SQLite.au3>

; -- Start SQLiit ------------------------------
Global Static $g__sSQliteDll = _SQLite_Startup(".\sqlite3.dll")
Global Static $hDb = _SQLite_Open()
; ----------------------------------------------

; finds all aweekdays between 2 dates where:
; (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday)
_runSQL('2020-03-01', '2020-03-31', '1') ; find all Mondays in March 2020

_SQLite_Shutdown()

; https://www.geekytidbits.com/generate-date-range-sqlite/
Func _runSQL($Date1, $date2, $weekday)
    Local $aMyRcordset, $iMyRows, $iMyColumns
    Local $Query = "WITH RECURSIVE cnt(x) AS ( SELECT julianday('" & $Date1 & "') " & _
            "UNION ALL SELECT x+1 FROM cnt LIMIT ( (julianday('" & $Date2 & "') - julianday('" & $Date1 & "'))+1)) " & _
            "SELECT date(x) as date FROM cnt where strftime('%w', date) = '" & $weekday & "';"

    _SQLite_GetTable(-1, $Query, $aMyRcordset, $iMyRows, $iMyColumns)
    _ArrayDisplay($aMyRcordset)
EndFunc   ;==>_runSQL

Similarly I would like to find a query that returns a recordset by dividing the elements by a comma separated string instead of extracting it from a table. Is anyone aware of this possibility?
for example, if I have this string "January, February, March, April, May, June, July, August, September, October, November, December", I would like to have a query that returns a recordset of all the months contained in the string.

Thanks for any tip.

[jchd]

You can get rid of julianday():

WITH RECURSIVE cnt(x) AS (
     SELECT '2020-03-01' d
     UNION ALL
     SELECT date(x, '+1 day') FROM cnt where x < '2020-03-31'
)
SELECT x Dates FROM cnt where strftime('%w', Dates) = '1';

For your question, you can probably get away with some table-valued function, for instance by converting your string into json and using json_extract(). [see edit]
Alternatively you might also built a painful CTE with clever use of string functions to achieve that, but is it worth the pain?

EDIT: reality check shows that it's impossible to index a json array with a column, so place the variable of the month# - 1 (json arrays start at 0) in place of the 4 (this results in 'May').

SELECT json_extract(json_array('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'), '$[4]')

 

Edited March 8, 2020 by jchd

[Gianni]

thanks @jchd
nice the simplifycation of the query to find the days of the week,

Also interesting are the json functions of sqlite (if you have a well-formed JSON string), thanks.

#include <SQLite.au3>
; -- turn on the sql engine --------------------
Global Static $g__sSQliteDll = _SQLite_Startup(".\sqlite3.dll")
Global Static $hDb = _SQLite_Open()
; MsgBox(0, '', "SQLite v. " & _SQLite_LibVersion ())
; ----------------------------------------------
; a JSON formatted string
Local $sString = "'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'"
Local $aMyResult, $iMyRows, $iMyColumns
Local $Query = "SELECT json_extract(json_array(" & $sString & "), '$[4]');" ; base 0, therefore [4] corresponds to May
_SQLite_GetTable(-1, $Query, $aMyResult, $iMyRows, $iMyColumns)

_ArrayDisplay($aMyResult) ;

_SQLite_Shutdown()

However I would like to find a query that can return a recordset extracted from a generic delimited string (maybe even choosing the separator or even more than one separator?)

At this link (https://stackoverflow.com/questions/24258878/how-to-split-comma-separated-value-in-sqlite) there is an example to do this, but maybe it can be simplified further?,  also is there a way to specify multiple separators? that is, rather than being able to use only the comma as a separator, having the possibility to use several of them in the same string, such as the comma and/or the semicolon... ? (ie something similar to the second parameter of the AutoIt StringSplit() function)

#include <SQLite.au3>
; -- turn on the sql engine --------------------
Global Static $g__sSQliteDll = _SQLite_Startup(".\sqlite3.dll")
Global Static $hDb = _SQLite_Open()
; MsgBox(0, '', "SQLite v. " & _SQLite_LibVersion ())
; ----------------------------------------------
Local $sCSV_String = "January,February,March,April,May,June,July,August,September,October,November,December"
_ArrayDisplay(_SQL_Test($sCSV_String))
_SQLite_Shutdown()

; https://stackoverflow.com/questions/24258878/how-to-split-comma-separated-value-in-sqlite
; see the solution posted by the user 'peak'
Func _SQL_Test(ByRef $s)
    Local $aMyResult, $iMyRows, $iMyColumns

    Local $Query = "WITH RECURSIVE split(value, str) AS ( " & _
            "SELECT null, '" & $s & "' || ',' " & _ ;  -- the string to be split
            "UNION ALL " & _
            "SELECT " & _
            "substr(str, 0, instr(str, ',')), " & _
            "substr(str, instr(str, ',')+1) " & _
            "FROM split WHERE str!='' " & _
            ") SELECT value FROM split WHERE value is not NULL;"

    _SQLite_GetTable(-1, $Query, $aMyResult, $iMyRows, $iMyColumns)

    Return $aMyResult
EndFunc   ;==>_SQL_Test

as always many thanks for any tip

Edited March 8, 2020 by Chimp
added a link

[jchd]

Yes as I said you can use a CTE and split the string this way. You can pass the separator substring as parameter to the calling function, or place it in an auxiliary table.

Else only table-valued functions can produce a set of rows from splitting data.

[TheXman]

On 3/7/2020 at 10:30 AM, Chimp said:

Similarly I would like to find a query that returns a recordset by dividing the elements by a comma separated string instead of extracting it from a table. Is anyone aware of this possibility?

On 3/8/2020 at 6:38 AM, Chimp said:

However I would like to find a query that can return a recordset extracted from a generic delimited string (maybe even choosing the separator or even more than one separator?)

Maybe this might work?  The example below uses _SQLite_SQLiteExe to create a result set from a separated list.  As you can see, I changed the separator from commas to colons just to show how easy it is to use a different separator,  The import script does all of the work.  If you want to change to separators, you just need to modify the first ".separator" line.  If you want to modify the result set, you just need to modify the "select" statement.

#include <Constants.au3>
#include <SQLite.au3>

example()

Func example()
    Const $SQLITE_EXE    = "C:\Program Files\Sqlite\sqlite3.exe"   ;<== Modify as necessary
    Const $TEMP_DB       = "~temp.db"
    Const $TEMP_DATA     = "~temp.txt"

    Const $IMPORT_SCRIPT = _
                           "drop table if exists items;" & @CRLF & _
                           "create table items (item collate nocase);" & @CRLF & _
                           ".separator | :" & @CRLF & _
                           ".import " & $TEMP_DATA & " items" & @CRLF & _
                           ".separator | \n" & @CRLF & _
                           "select * from items;"

    Local $iReturnCode
    Local $sOutput

    ;Write temp data file
    If FileExists($TEMP_DATA) Then FileDelete($TEMP_DATA)
    FileWriteLine($TEMP_DATA, "January:February:March:April:May:June:July:August:September:October:November:December")

    ;Execute script (Creates temp db & table, imports data, outputs data)
    _SQLite_SQLiteExe($TEMP_DB, $IMPORT_SCRIPT, $sOutput, $SQLITE_EXE)
    If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error executing _SQLite_SQLiteExe - @error = " & @error)

    ;Display ouput
    ConsoleWrite($sOutput)

    ;Delete temp files
    If FileExists($TEMP_DATA) Then FileDelete($TEMP_DATA)
    If FileExists($TEMP_DB)   Then FileDelete($TEMP_DB)
EndFunc

Output:

January
February
March
April
May
June
July
August
September
October
November
December

 

 

Edited March 10, 2020 by TheXman
Removed reference to sqlite dll

[Gianni]

thanks @TheXman for your script, interesting example on how to import from a file.
My intent would still be not to use temporary files or "command line shell for SQLite", but to do everything instead with a "self contained" query.
Also, when I wrote "(maybe even choosing the separator or even more than one separator?)" I meant not to be able to use a different separator, but to be able to use multiple separators at the same time (sorry for my poor English )
Testing your script I saw that it uses ".separator |:" to choose the separator. Reading at point 5 of this link https://sqlite.org/cli.html I see that the .separator is a "command to change the separator. For example, to change the separator to a comma and a space, you could do this:"

.separator ", "

It appears that more than one separator can be used simultaneously.
I tried to put multiple separators into your script without success. which syntax should be used to put multiple separators?
Thanks again for your post

[TheXman]

@Chimp

As far as I'm aware, the separators for columns and rows have to be single characters.

The .separator directive has up to 2 parameters, the first parameter is the character that identifies column breaks.  The second, optional, character identifies row breaks.  In my example, ",separator | :" says the pipe symbol (|) identifies columns and the colon (:) identifies rows.

Edited March 10, 2020 by TheXman
Corrected type in .separator example

[jchd]

Did you try:

...
'.separator ": "' & @CRLF & _
...

 

Edited March 10, 2020 by jchd

[TheXman]

Yes, I tried using multi-character separators enclosed in quotes and it generates a SQLITE error.  That's one of the reasons that I said that I do not think that you can have multi-character separators.

"Error: multi-character row separators not allowed for import"

 

'.separator ": "' & @CRLF & _

@jchd Your example, above, attempts to set the COLUMN separator, not the ROW separator.  The first parameter identifies the COLUMN separator.  The syntax is  ".separator COL ?ROW?"

Edited March 10, 2020 by TheXman

[Gianni]

Thanks @TheXman  for the explanation,

in your script, by using following string as input some data is lost in output... ?

"January:February|March:April|May:June|July:August|September:October|November:December"

how can I get a 2d output?

Edited March 10, 2020 by Chimp

[TheXman]

You need to make sure that the table that is defined can handle the number of columns.

#include <Constants.au3>
#include <SQLite.au3>

example()

Func example()
    Const $SQLITE_EXE    = "C:\Program Files\Sqlite\sqlite3.exe"   ;<== Modify as necessary
    Const $TEMP_DB       = "~temp.db"
    Const $TEMP_DATA     = "~temp.txt"

    Const $IMPORT_SCRIPT = _
                           "DROP TABLE IF EXISTS items;" & @CRLF & _
                           "CREATE TABLE items (col1 TEXT COLLATE NOCASE, col2 INT);" & @CRLF & _
                           '.separator | ,' & @CRLF & _
                           ".import " & $TEMP_DATA & " items" & @CRLF & _
                           ".mode list" & @CRLF & _
                           "SELECT * FROM items;"

    Local $iReturnCode
    Local $sOutput

    ;Write temp data file
    If FileExists($TEMP_DATA) Then FileDelete($TEMP_DATA)
    FileWriteLine($TEMP_DATA, "January|1,February|2,March|3,April|4,May|5,June|6,July|7,August|8,September|9,October|10,November|11,December|12")

    ;Execute script (Creates temp db & table, imports data, outputs data)
    _SQLite_SQLiteExe($TEMP_DB, $IMPORT_SCRIPT, $sOutput, $SQLITE_EXE)
    If @error Then Exit MsgBox($MB_ICONERROR, "ERROR", "Error executing _SQLite_SQLiteExe - @error = " & @error)

    ;Display ouput
    ConsoleWrite($sOutput)

    ;Delete temp files
    If FileExists($TEMP_DATA) Then FileDelete($TEMP_DATA)
    If FileExists($TEMP_DB)   Then FileDelete($TEMP_DB)
EndFunc

Output:

January|1
February|2
March|3
April|4
May|5
June|6
July|7
August|8
September|9
October|10
November|11
December|12

 

Edited March 10, 2020 by TheXman
Corrected typo in months string and changed column names

[Gianni]

yes, .... sure, the createtable statement must be adapted accordingly to the input string
got it, thanks