SQLite Forum

Timeline
Login

40 forum posts by user RoboManni

2021-10-03
16:01 Reply: install sqlite on windows10 (artifact: ffbb9e15c6 user: RoboManni)

The homepage of Singular.gr is not active anymore and it was not easy to find something but here is at least one linke to an older version , which works. sqlite3Explorer.zip I am not sure which version it is abut for sure not 3.04, which I am using. It is much older, from year 2004.

2021-09-09
17:18 Reply: SQLite3.exe fails to handle huge multi-line-comment (artifact: 21806f511c user: RoboManni)

Even if the SQLite3.exe remains as it is, it is now good to know where "performance killers" might come from ... did you ever thought about comments as being a reason?

In first point I also expected that something with my sql statements might have been bad when the whole did not finish within 15 minutes - but then I tried one framing transaction for all the individual inserts and it did not become significantly faster (this has usually helped me in the past) ... and then I tried to reduce the text to find the bottle neck. Finally I stayed with almost just the remaining comment block. I did not expect the text to ignore being the reason for performance drop.

Sorry for having bothered you with my exceptional finding; however, hopefully someone at some day will find it helpful being mentioned here.

13:11 Reply: SQLite3.exe fails to handle huge multi-line-comment (artifact: c0702b3a9c user: RoboManni)

understandable and acceptable ... the sql statements itself also could contain strings which look like comment invocations .... quoted -- and /*

11:06 Reply: SQLite3.exe fails to handle huge multi-line-comment (artifact: 68682a7368 user: RoboManni)

The solution, as mentioned above, is to write a preprocessor and pipe the output through it before it gets as input into the sqlite3.exe and/or to write a wrapper which scans the .sql file to remove all the multi-comment-lines. - As I wrote at the beginning, the sql file was generated. Where the generator is under my control I will try to work around at the source (as explained as my solution).

Regarding the explanation from Richard I was thinking that each line is read and checked before added to the realloc'd buffer. (That's what Command Line Interpreter means to me.) Once a multi line buffer has been started and if any next line does not contain the closing */ sequence then simple do NOT add that line to the realloc'd buffer. I understand now the issue and reason in the not required buffering, which is kept to let the SQLite3 core later on throw out the comment block. My suggestion would be to avoid adding not required lines to that buffer - then while the comment is to be continued with any next line the buffer would not grow.

There are some cases of combinations to consider:

  • normal line, not collecting the lines of a multi-line-comment : add the line to buffer
  • if a line starts with -- then such a line could be dropped from buffering
  • line with starting single line comment (any /* to be ignored behind -- ) : add this line to buffer if -- is not starting the line
  • line with starting multi-line comment ( /* detected ) : add this line to buffer, remember the state
  • while state is 'multi-line-comment started check next line if */ is contained : if yes then add line to buffer if not then drop the line from buffer
  • finally the buffer is handed into the sqlite3_prepare, sqlite3_exec, etc with not more than few lines of comment

I hope my thinking and explanations are understandable enough.

2021-09-08
18:43 Reply: SQLite3.exe fails to handle huge multi-line-comment (artifact: 761296aace user: RoboManni)

My comment block did NOT contain any ; semicolon (I searched for it) but only text, numbers, commas and colons and some few other special symbols, which are used in paths and cmd commands.

09:05 Post: SQLite3.exe fails to handle huge multi-line-comment (artifact: 1482dba4aa user: RoboManni)

What happened

I generated a text file which starts with a huge multi-line comment of about 258000 lines and contains the following 46000 sql statements to execute.

Appearance

>sqlite3.exe version
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
>sqlite3.exe TagList-ua.sqb ".read sql-command.sql"

This command (Windows OS) did not finish within 15 minutes before I aborted it.

Solution

When I delete the huge comment block from the .sql file and reduce the number of comment block lines to e.g. 15, then the sql statements, which are framed with a transaction begin and end, completes within 1 second.

With 16000 lines of multi-line-comment the execution took still some few seconds; but at least did the intended job.

Complaint

Skipping multi-line-comments is not performant and can almost inhibit its use.

Question

Why does processing the /* multi-line-comment */ take soooo much time? Can this be improved with next SQLite3 version 3.37 ?

2021-09-02
12:56 Reply: install sqlite on windows10 (artifact: b0278db300 user: RoboManni)

.. or to do it in winter times at very low temperature ... then water is also known as ice

09:52 Reply: install sqlite on windows10 (artifact: 07bdcfe4bd user: RoboManni)

I am still a fan of the good (very) old Sqlite3Explorer from Singular in version 3.04 . It might be difficult to find it nowadays in the internet as it is really old, it's from about year 2009 (version 3.01 from about 2006).

Beside the single exe file it uses just the one sqlite3.dll, which can be downloaded from www.sqlite.org homepage. A report generator could be loaded in addition but I did not require it and have used it only seldom.

Even when it is very old it has almost all I need from such a tool every day and I did not find a better one for me yet.

This GUI has run with all versions that I can think of since used SQLite3 version 3.5.9 ; and this GUI still does the good job of keeping the compatibility also with my latest version 3.36.0 .

2021-08-07
09:52 Reply: SQLite3.exe - How to use arguments AND remain in interactive mode? (artifact: 8bab8b07bf user: RoboManni)

I found the solution which works fine for me and better than before:

I am calling the command line interface (CLI) tool SQLite3.exe now per batch file and added loading of the extension dll into an additional file. The location of the batch file is on the environmental path configuration for me as user.

The command parameter -interactive does NOT make any difference if I supply it or not. As soon as there is at least one SQL command provided the tool will not go into interactive mode but exits after execution:

D:\UTL\sql3.bat

@D:\UTL\SQLite3.exe -echo -interactive -init D:\UTL\.SQLiteRC %*

D:\UTL\.SQLiteRC

.load D:\UTL\SQLite3.dll
.mode box

Command line input

D:\UTL>sql3.bat -version
-- Loading resources from D:\UTL\.SQLiteRC
3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5

D:\UTL>sql3.bat :memory: "select nv('name1|value1|name2|value2|name3|value3','name2|name1') as result"
-- Loading resources from D:\UTL\.SQLiteRC
select nv('name1|value1|name2|value2|name3|value3','name2|name1') as result
┌───────────────┐
│    result     │
├───────────────┤
│ value2|value1 │
└───────────────┘

D:\UTL>sql3.bat :memory:
-- Loading resources from D:\UTL\.SQLiteRC
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .exit
.exit

D:\UTL>

question

Is this ignorance of the -interactive command line option intended or is this a buggy behavior? Where can I find the documentatino of this option?

08:52 Reply: SQLite3.exe - How to use arguments AND remain in interactive mode? (artifact: bf3989f459 user: RoboManni)

As it is my intention to always automatically load my extension functions and virtual tables into the standard SQLite3.exe when it starts, the trick with a hidden file .sqliterc would be a good solution for me.

However, I cannot find that file on my HD partitions at all. Where on Windows 10 is that file stored? Do I need to prepare something to make it created the first time?

I scanned the whole tree C:/Users/MBL/ but there is no such file to find.

2021-08-06
14:15 Reply: Fail to calculate long expression (artifact: 2b1a27bbcc user: RoboManni)

Euler Math Toolbox = 8.25981692062e+12

08:50 Post: SQLite3.exe - How to use arguments AND remain in interactive mode? (artifact: f5bc6525d8 user: RoboManni)

I am using often SQLite3.exe (Windows 10) and would like to start with command line parameters but then remain in interactive mode.

I only get either or.... but is there any trick to start with parameters AND remain in interactive command line mode?

D:\Debug>sqlite3.exe :memory: .load SQLite3.dll
Usage: .load FILE ?ENTRYPOINT?

D:\Debug>sqlite3.exe :memory: ".load SQLite3.dll"

D:\Debug>sqlite3.exe :memory: ".load SQLite3.dll" -
sqlite3.exe: Error: unknown option: -
Use -help for a list of options.

D:\Debug>sqlite3.exe :memory: ".load SQLite3.dll" .interactive
Error: unknown command or invalid arguments:  "interactive". Enter ".help" for help

D:\Debug>sqlite3.exe .help
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite>

.exit
D:\Debug>sqlite3.exe :memory: ".load SQLite3.dll" --
sqlite3.exe: Error: unknown option: -
Use -help for a list of options.

D:\Debug>sqlite3.exe -help
Usage: sqlite3.exe [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -A ARGS...           run ".archive ARGS" and exit
   -append              append the database to the end of the file
   -ascii               set output mode to 'ascii'
   -bail                stop after hitting an error
   -batch               force batch I/O
   -box                 set output mode to 'box'
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -deserialize         open the database using sqlite3_deserialize()
   -echo                print commands before execution
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -json                set output mode to 'json'
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory
   -markdown            set output mode to 'markdown'
   -maxsize N           maximum size for a --deserialize database
   -memtrace            trace all memory allocations and deallocations
   -mmap N              default mmap size set to N
   -newline SEP         set output row separator. Default: '\n'
   -nofollow            refuse to open symbolic links to database files
   -nullvalue TEXT      set text string for NULL values. Default ''
   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory
   -quote               set output mode to 'quote'
   -readonly            open the database read-only
   -separator SEP       set output column separator. Default: '|'
   -stats               print memory stats before each finalize
   -table               set output mode to 'table'
   -tabs                set output mode to 'tabs'
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS
   -zip                 open the file as a ZIP Archive

D:\Debug>sqlite3.exe -interactive :memory: ".load SQLite3.dll"

D:\Debug>

In this example the SQLite3.dll contains my loadable extension functions and virtual table extensions, it is based on the 3.36 amalgamation.

The .load parameter was executed successfully but then the executable terminated immediately - without remaining in interactive mode. Some arguments which I tried did not help, they are unknown. The .help command does not show me any option which would be good to remain in interactive mode when using command line parameters. Is there a trick which I do not know yet?

Usage: sqlite3.exe [OPTIONS] FILENAME [SQL]

 -interactive         force interactive I/O

How to use this option together with the other dot-arguments like .load, which the usage hint does not even show as optional?

2021-07-21
13:44 Edit reply: SELECT optimization for constant expression (artifact: b086bc84a2 user: RoboManni)

there was recently (the last few days) another thread about query plan in conjunction with LIKE.

If a pattern does not start with a constant (or literal?) then SCAN needs to be done instead of being able to use the index.

Maybe a splitting into an ugly looking sequence could help to make the query plan look as expected.

Example of what I mean

...
case substr(?1 COLLATE NOCASE,1,1) 
  when 'A' then name like 'A'||substr(?1,2)
  when 'B' then name like 'B'||substr(?1,2)
  when 'C' then name like 'C'||substr(?1,2)
...
  when 'Y' then name like 'Y'||substr(?1,2)
  when 'Z' then name like 'Z'||substr(?1,2)
else 
  name like ?1  -- uses SCAN query plan
end

EDIT: copy-paste-error correction

13:43 Reply: SELECT optimization for constant expression (artifact: 34482a6069 user: RoboManni)

there was recently (the last few days) another thread about query plan in conjunction with LIKE.

If a pattern does not start with a constant (or literal?) then SCAN needs to be done instead of being able to use the index.

Maybe a splitting into an ugly looking sequence could help to make the query plan look as expected.

Example of what I mean

...
case substr(?1 COLLATE NOCASE,1,1) 
  when 'A' then name like 'A'||substr(?1,2)
  when 'B' then name like 'B'||substr(?1,2)
  when 'C' then name like 'C'||substr(?1,2)
...
  when 'Y' then name like 'A'||substr(?1,2)
  when 'Z' then name like 'A'||substr(?1,2)
else 
  name like ?1  -- uses SCAN query plan
end
06:28 Reply: Calculating duration in ISO8601 timestamp (artifact: 5b6ab9f4ed user: RoboManni)
SQLite version 3.36.0 2021-06-18 18:36:39

sqlite> select julianday('2021-07-20 15:18:26.623');
2459416.13780814
sqlite> select julianday(substr('2021-07-20 15:18:26:623',1,19)||'.'||substr('2021-07-20 15:18:26:623',21,23));
2459416.13780814
sqlite> select julianday(substr(dt,1,19)||'.'||substr(dt,21,23)) from (select '2021-07-20 15:18:26:623' as dt);
2459416.13780814
sqlite>
06:20 Reply: Calculating duration in ISO8601 timestamp (artifact: d6897e29aa user: RoboManni)

thanks for this link ... seems also something for me to go through...

06:09 Reply: Csv or Vsv parsing blob column (artifact: 8231ce69a4 user: RoboManni)

it is not easy to understand your request but I tried some basics with 'eval', which herein is one of my extension functions:

sandbox>sqlite3.exe
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select typeof(X'010203');
blob
sqlite> select eval('select 3.1415');
Error: no such function: eval
sqlite> .load sqlite3.dll
sqlite> select eval('select 3.1415');
3.1415
sqlite> select eval('select '||X'010203');
unrecognized token: ""
sqlite> select eval('select typeof('||X'010203'||')');
unrecognized token: ""
sqlite> select eval('select typeof("'||X'010203'||'")');
text
sqlite>
05:53 Reply: SELECT optimization for constant expression (artifact: de4e252c9f user: RoboManni)

Try reformulating your statement using CASE for the conditional decisions:

SELECT * FROM person
WHERE case when ?1 NOT NULL then firstname  LIKE ?1 else 1 end
  AND case when ?2 NOT NULL then lastname   LIKE ?2 else 1 end
  AND case when ?3 NOT NULL then birthdate   ==  ?3 else 1 end
  AND case when ?4 NOT NULL then maidenname LIKE ?4 else 1 end
ORDER BY lastname, firstname;
2021-07-16
07:08 Reply: More math help and what I've learned (artifact: 34e0195277 user: RoboManni)

you could probably get rid of the case exceptions if you provide a value 0.0 instead of the NULL for the case where you need the other formulas. Both would work as expected when a value of 0.0 would substitute the NULL ... isn't it?

select null as V1, ifnull(NULL,0) as V2good, ifnull(3.14,0) as V3, 5-null as V4, 5-ifnull(null,0) as V5

would produce as output:

V1    V2good   V3     V4     V5
null  0        3.14   null   5

as you can see, with value 0 you would get the found value reduced by nothing (0.0) while the null would not (and did not) give you the expected results.

ifnull will also be your friend for future queries like case is already, I guess... have fun with SQL and SQLite3 !

06:44 Reply: Should SQLite be able to optimize this query? (artifact: dcea280c08 user: RoboManni)

"Natural ordered" (aka orderByConsumed) is also an order and NOT random.

I the past - before the "as MATERIALIZED" for the WITH clause was introduced - there was an issue where I had to add "limit 8e9" to solve my issue, which I cannot remember exactly now what it was. Might also not well be related to this thread here but just to mention as a hint about other effects out from the CTE into the framing query.

My query:

with
tab as (
  select * from ViewAllAlarms limit 8e9
),
alarms as (
  select rowid,TimeStamp,Seconds,Severity,Reason,Source,RestMsg,Message2,Message3
  from tab
  where (State isNull) and (TestEnd isNull) and (Control isNull)
),
aggregated as (
select   Severity,Reason,Source,RestMsg,Message2,Message3,count(*)NumCount
from alarms
where Severity notNull
group by Severity,Reason,Source,RestMsg,Message2,Message3
having NumCount>1
order by Severity,NumCount desc,Reason,Source,RestMsg,Message2,Message3
)
select * from aggregated;
2021-07-15
16:44 Reply: More math help and what I've learned (artifact: 344358f4c1 user: RoboManni)

Hello, you will realize that only the first row in your table has no value for the previous_transId. But then it is easy to not only show this in the table but also use it to select between the different formulas. If there is a previous then use the formula using it and else use the formula for the first record. I think the case will be your future helper!

SELECT *,
	(GrowthPool-GCOVAW)/1000000-EstGrowth as Diff
FROM (
  SELECT
	TransactionId,
	GrowthPool,

        TransactionId               as current_transId,

        lag(TransactionId,1) OVER w as previous_transId,

        case when lag(TransactionId,1) OVER w
             then ((TransactionId) -  (lag(TransactionId,1,1) OVER w))*0.003*250
             else                                       TransactionId *0.003*250
             end as EstGrowth,

	lag(GrowthCarry) OVER
	w Previous_GCOV, GrowthCarry as Current_GCOV,
	(lag(GrowthCarry) OVER w) as GCOVAW

  FROM CalculateGrowth
  WINDOW w AS (PARTITION BY PIG ORDER BY PIG)
) e
WHERE EstGrowth IS NOT NULL;
15:40 Reply: Feature request: add index info to WITH clause table (artifact: 65b5b446b8 user: RoboManni)

YES, I like your proposal to take the CTE table declaration similar to a "CREATE TABLE" statement to include the index declarations.

11:30 Edit: Feature request: add index info to WITH clause table (artifact: 74cbdcafc0 user: RoboManni)

I have some use cases where I read several lookup tables from text files. I do this in WITH clauses where I do filtering and apply formatting for further use steps.

Then in the main query I want to left outer join these tables (based on time stamps and time ranges as the only link of data - loose relationship).

Because my table preparation within the WITH clause is reading text files using one of my virtual table extension, there is no support by indexes; they do not exist.

If I understand it correctly from what I read in all the documentation about SQLite3 over the time then each of the left outer join steps have to scan the pre-prepared tables and cannot (yet) simply lookup an index for faster access.

That's why I suggest to think about an extension of the WITH clause by some syntax to give some additional index information.

Of cause I could use temporary tables and temporary indexes - but I do not want to fill any database (not even a temporary) when it is not necessary - the source of information can remain in their text files. Only my (very complex) query is contained in the SQLite3 database and reading from a subfolder relative allows me to simply copy the database to somewhere else to run the same query with different data without any changes to it.

To give some hints about my use case: I am using SQLite3 version 3.36.0 with several of my extension functions and virtual tables. Windows OS. The result table has 29000 rows and the total query time took 544 seconds to get them while looking up just one table with contained 29000 records. The main table without filtering consists of 255000 lines out of 2 text files with a total of 27MB size (there could be many more of them and also the size could be much bigger). My database which contains the complex query has just 98kB size.

The Query Plan is short but seems to be not as efficient as it could become when supported by an index:

id  parent  notused  detail
3   0       0        MATERIALIZE ViewStateMachineState
6   3       0        SCAN AllStateMachineText VIRTUAL TABLE INDEX 0:
25  0       0        SCAN AllPLCcommLog       VIRTUAL TABLE INDEX 0:
47  0       0        SCAN L

How can accessing of WITH clause generated volatile tables be supported with indexes? At least with one primary index would be great!

The Virtual table could probably help but at the moment of reading the text from file there was not filtering on any extracted data happening yet. And as one and the same virtual table file reading process can be used in independent views it is also not neccessary to generate always all possible index info when the use in the with clause requires just one to support performance.

My proposal: as the WITH clause allows repetition with a comma the separated index could be constructed like a select is done. All is kept temporary and only as much as the whole statement requires and as long as the statement connection lives. That would really be a great push and improvement to many of my use cases, where the complex statement is used only once - to fill the result table for further processing and presentations.

with 
LookupTable( TimeStamp, LookupContent ) as MATERIALIZE (
   select substr(Content,1,24)        as TimeStamp
        , trim(substr(Content,26,40)) as LookupContent 
     from VirtualLookupTextTable
    where instr(Content,'keyword')
),
LookupIndex as (
   create primary key( TimeStamp ) on LookupTable
)
select FromTimeStamp, ToTimeStamp, M.Content, L.Content
  from VirtualMainTextTable   M
  left outer join LookupTable L
               on M.ToTimeStamp>M.FromTimeStamp and L.TimeStamp > M.FromTimeStamp and L.TimeStamp <= M.ToTimeStamp
                                                 or L.TimeStamp == M.ToTimeStamp

EDIT: typo detected and corrected and added content from the lookup table to the final result

11:29 Edit: Feature request: add index info to WITH clause table (artifact: 0532d5bb8a user: RoboManni)

I have some use cases where I read several lookup tables from text files. I do this in WITH clauses where I do filtering and apply formatting for further use steps.

Then in the main query I want to left outer join these tables (based on time stamps and time ranges as the only link of data - loose relationship).

Because my table preparation within the WITH clause is reading text files using one of my virtual table extension, there is no support by indexes; they do not exist.

If I understand it correctly from what I read in all the documentation about SQLite3 over the time then each of the left outer join steps have to scan the pre-prepared tables and cannot (yet) simply lookup an index for faster access.

That's why I suggest to think about an extension of the WITH clause by some syntax to give some additional index information.

Of cause I could use temporary tables and temporary indexes - but I do not want to fill any database (not even a temporary) when it is not necessary - the source of information can remain in their text files. Only my (very complex) query is contained in the SQLite3 database and reading from a subfolder relative allows me to simply copy the database to somewhere else to run the same query with different data without any changes to it.

To give some hints about my use case: I am using SQLite3 version 3.36.0 with several of my extension functions and virtual tables. Windows OS. The result table has 29000 rows and the total query time took 544 seconds to get them while looking up just one table with contained 29000 records. The main table without filtering consists of 255000 lines out of 2 text files with a total of 27MB size (there could be many more of them and also the size could be much bigger). My database which contains the complex query has just 98kB size.

The Query Plan is short but seems to be not as efficient as it could become when supported by an index:

id  parent  notused  detail
3   0       0        MATERIALIZE ViewStateMachineState
6   3       0        SCAN AllStateMachineText VIRTUAL TABLE INDEX 0:
25  0       0        SCAN AllPLCcommLog       VIRTUAL TABLE INDEX 0:
47  0       0        SCAN L

How can accessing of WITH clause generated volatile tables be supported with indexes? At least with one primary index would be great!

The Virtual table could probably help but at the moment of reading the text from file there was not filtering on any extracted data happening yet. And as one and the same virtual table file reading process can be used in independent views it is also not neccessary to generate always all possible index info when the use in the with clause requires just one to support performance.

My proposal: as the WITH clause allows repetition with a comma the separated index could be constructed like a select is done. All is kept temporary and only as much as the whole statement requires and as long as the statement connection lives. That would really be a great push and improvement to many of my use cases, where the complex statement is used only once - to fill the result table for further processing and presentations.

with 
LookupTable( TimeStamp, LookupContent ) as MATERIALIZE (
   select substr(Content,1,24)        as TimeStamp
        , trim(substr(Content,26,40)) as LookupContent 
     from VirtualLookupTextTable
    where instr(Content,'keyword')
),
LookupIndex as (
   create primary key( TimeStamp ) on LookupTable
)
select FromTimeStamp, ToTimeStamp, M.Content
  from VirtualMainTextTable   M
  left outer join LookupTable L
               on M.ToTimeStamp>M.FromTimeStamp and L.TimeStamp > M.FromTimeStamp and L.TimeStamp <= M.ToTimeStamp
                                                 or L.TimeStamp == M.ToTimeStamp

EDIT: typo detected and corrected

11:27 Post: Feature request: add index info to WITH clause table (artifact: 0c3edab209 user: RoboManni)

I have some use cases where I read several lookup tables from text files. I do this in WITH clauses where I do filtering and apply formatting for further use steps.

Then in the main query I want to left outer join these tables (based on time stamps and time ranges as the only link of data - loose relationship).

Because my table preparation within the WITH clause is reading text files using one of my virtual table extension, there is no support by indexes; they do not exist.

If I understand it correctly from what I read in all the documentation about SQLite3 over the time then each of the left outer join steps have to scan the pre-prepared tables and cannot (yet) simply lookup an index for faster access.

That's why I suggest to think about an extension of the WITH clause by some syntax to give some additional index information.

Of cause I could use temporary tables and temporary indexes - but I do not want to fill any database (not even a temporary) when it is not necessary - the source of information can remain in their text files. Only my (very complex) query is contained in the SQLite3 database and reading from a subfolder relative allows me to simply copy the database to somewhere else to run the same query with different data without any changes to it.

To give some hints about my use case: I am using SQLite3 version 3.36.0 with several of my extension functions and virtual tables. Windows OS. The result table has 29000 rows and the total query time took 544 seconds to get them while looking up just one table with contained 29000 records. The main table without filtering consists of 255000 lines out of 2 text files with a total of 27MB size (there could be many more of them and also the size could be much bigger). My database which contains the complex query has just 98kB size.

The Query Plan is short but seems to be not as efficient as it could become when supported by an index:

id  parent  notused  detail
3   0       0        MATERIALIZE ViewStateMachineState
6   3       0        SCAN AllStateMachineText VIRTUAL TABLE INDEX 0:
25  0       0        SCAN AllPLCcommLog       VIRTUAL TABLE INDEX 0:
47  0       0        SCAN L

How can accessing of WITH clause generated volatile tables be supported with indexes? At least with one primary index would be great!

The Virtual table could probably help but at the moment of reading the text from file there was not filtering on any extracted data happening yet. And as one and the same virtual table file reading process can be used in independent views it is also not neccessary to generate always all possible index info when the use in the with clause requires just one to support performance.

My proposal: as the WITH clause allows repetition with a comma the separated index could be constructed like a select is done. All is kept temporary and only as much as the whole statement requires and as long as the statement connection lives. That would really be a great push and improvement to many of my use cases, where the complex statement is used only once - to fill the result table for further processing and presentations.

with 
LookupTable( TimeStamp, LookupContent ) as MATERIALIZE (
   select substr(Content,1,24)        as TimeStamp
        , trim(substr(Content,26,40)) as LookupContent 
     from VirtualLookupTextTable
    where instr(Content,'keyword')
),
LookupIndex as (
   create primary key( TimeStamp ) on MainTable
)
select FromTimeStamp, ToTimeStamp, M.Content
  from VirtualMainTextTable   M
  left outer join LookupTable L
               on M.ToTimeStamp>M.FromTimeStamp and L.TimeStamp > M.FromTimeStamp and L.TimeStamp <= M.ToTimeStamp
                                                 or L.TimeStamp == M.ToTimeStamp
2021-07-11
16:29 Reply: Unstack one column to multiple (artifact: fbdf78e300 user: RoboManni)
$ sqlite3.exe
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> drop table if exists FileBlob;
sqlite> create table if not exists FileBlob( id integer primary key, content text, size integer, separator text );
sqlite> insert or replace into FileBlob(id,content) values( 1, replace(ReadFromFile('D:\PEMS.WIN\RAD\SQLite3x\sandbox\RowsInLines.txt'),X'0d0a',X'0a') );
sqlite> update FileBlob set size=length(Content), separator=cast(X'0a0a' as text) where id==1;

then some basic stuff before I try to go into the details for better understandin how it is working:

sqlite> .mode box
sqlite> with recursive Lines(RowContent,EndPos) as (
   ...>   values( NULL, 1 )  --previous endpos=startpos
   ...>   union
   ...>   select L.EndPos||'+content' as  RowContent
   ...>        ,(L.EndPos + 6) as EndPos  -- new endpos
   ...>     from Lines L join FileBlob B on B.id==1
   ...>    where L.EndPos < length(B.content)
   ...> )
   ...> select * from Lines where RowContent notNull;
┌─────────────┬────────┐
│ RowContent  │ EndPos │
├─────────────┼────────┤
│ 1+content   │ 7      │
│ 7+content   │ 13     │
│ 13+content  │ 19     │
│ 19+content  │ 25     │
│ 25+content  │ 31     │
│ 31+content  │ 37     │
...
│ 469+content │ 475    │
│ 475+content │ 481    │
│ 481+content │ 487    │
└─────────────┴────────┘
sqlite>

now replacing the constant values by results from instr() function using the FileBlob and its content size

sqlite> .mode line
sqlite> with recursive Lines(RowContent,EndPos) as (
   ...>   values( NULL, 1 )  --previous endpos=startpos
   ...>   union
   ...>   select substr(B.content,L.EndPos, instr(substr(B.content,L.EndPos),B.separator) ) as RowContent
   ...>        , L.EndPos + instr(substr(B.content,L.EndPos),B.separator) + 2               as EndPos
   ...>     from Lines L join FileBlob B on B.id==1
   ...>    where L.EndPos between 1 and B.size    -- recursion end condition
   ...> )
   ...> select * from Lines where RowContent notNull;
RowContent = Hardside Cabin Luggage Trolley Black
senator
View Product Details
N18602991A      KH132-20_BLK
KH13220BLK
02
Days
04
Hrs
25
Mins
25
Sec
1
1
0

    EndPos = 141

RowContent = Hardside 4 Wheels Cabin Luggage Trolley Burgundy
senator
View Product Details
N42588980A      KH134-20_BGN
KH13420BGN
02
Days
04
Hrs
25
Mins
25
Sec
1
1
0

    EndPos = 293

RowContent = Softside Cabin Luggage Trolley Purple
senator
View Product Details
N32139616A      LL051-20_PRP
LL05120PRP
02
Days
09
Hrs
25
Mins
25
Sec
1
1
0

    EndPos = 434
sqlite>

or for easier readability with the line breaks in .box mode

sqlite> .mode box
sqlite> with recursive Lines(StartPos,RowContent,EndPos) as (
   ...>   values( NULL, NULL, 1 )  --previous endpos=startpos
   ...>   union
   ...>   select L.EndPos                                                                   as StartPos
   ...>        , substr(B.content,L.EndPos, instr(substr(B.content,L.EndPos),B.separator) ) as RowContent
   ...>        , L.EndPos + instr(substr(B.content,L.EndPos),B.separator) + 2               as EndPos
   ...>     from Lines L join FileBlob B on B.id==1
   ...>    where L.EndPos between 1 and B.size    -- recursion end condition
   ...> )
   ...> select StartPos,replace(RowContent,X'0a',' ')Content,EndPos
   ...>   from Lines
   ...>  where RowContent notNull;
┌──────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬────────┐
│ StartPos │                                                                        Content                                                                         │ EndPos │
├──────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼────────┤
│ 1        │ Hardside Cabin Luggage Trolley Black senator View Product Details N18602991A       KH132-20_BLK     KH13220BLK 02 Days 04 Hrs 25 Mins 25 Sec 1 1 0              │ 141    │
│ 141      │ Hardside 4 Wheels Cabin Luggage Trolley Burgundy senator View Product Details N42588980A   KH134-20_BGN     KH13420BGN 02 Days 04 Hrs 25 Mins 25 Sec 1 1 0  │ 293    │
│ 293      │ Softside Cabin Luggage Trolley Purple senator View Product Details N32139616A      LL051-20_PRP     LL05120PRP 02 Days 09 Hrs 25 Mins 25 Sec 1 1 0             │ 434    │
└──────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────┘
sqlite>

So far just to proove that reading blocks of lines can be done with standard SQLite3 with the help of the functions instr() and substr()

Next exercise will be to use the rows and separate the fields out of them.... to be continued

2021-07-07
18:36 Post: Strange filter condition with notNull (artifact: 7c8aa68187 user: RoboManni)

I realized a difference in filtering by where clause in conjunction with the in operator and notNull when I wanted to suppress the Null filled field and it did not happen, looking like the and was ignored; but the order does NOT make any difference.

SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .mode box
sqlite> select TagGroup,TagName,Op,typeof(TagName) from ExitTagList where Op in ('write','written') and Op notNull;
┌──────────┬─────────────┬─────────┬─────────────────┐
│ TagGroup │   TagName   │   Op    │ typeof(TagName) │
├──────────┼─────────────┼─────────┼─────────────────┤
│          │             │ written │ null            │
│ ExitHsk  │ REQUEST     │ write   │ text            │
│ ExitHsk  │ SuccessCode │ write   │ text            │
│ ExitHsk  │ VALID       │ write   │ text            │
└──────────┴─────────────┴─────────┴─────────────────┘
sqlite> select TagGroup,TagName,Op,typeof(TagName) from ExitTagList where Op notNull and Op in ('write','written');
┌──────────┬─────────────┬─────────┬─────────────────┐
│ TagGroup │   TagName   │   Op    │ typeof(TagName) │
├──────────┼─────────────┼─────────┼─────────────────┤
│          │             │ written │ null            │
│ ExitHsk  │ REQUEST     │ write   │ text            │
│ ExitHsk  │ SuccessCode │ write   │ text            │
│ ExitHsk  │ VALID       │ write   │ text            │
└──────────┴─────────────┴─────────┴─────────────────┘
sqlite> select TagGroup,TagName,Op,typeof(TagName) from ExitTagList where Op in ('write','written') and Op notNull and TagGroup = 'ExitHsk';
┌──────────┬─────────────┬───────┬─────────────────┐
│ TagGroup │   TagName   │  Op   │ typeof(TagName) │
├──────────┼─────────────┼───────┼─────────────────┤
│ ExitHsk  │ REQUEST     │ write │ text            │
│ ExitHsk  │ SuccessCode │ write │ text            │
│ ExitHsk  │ VALID       │ write │ text            │
└──────────┴─────────────┴───────┴─────────────────┘
sqlite> select * from ExitTagList;
┌──────────┬─────────────┬─────────┬───────┐
│ TagGroup │   TagName   │   Op    │ Used  │
├──────────┼─────────────┼─────────┼───────┤
│          │             │ written │ 19310 │
│          │             │ loaded  │ 27420 │
│ ExitHsk  │ REQUEST     │ write   │ 13115 │
│ ExitHsk  │ REQUEST     │ read    │ 13115 │
│ ExitHsk  │ SuccessCode │ write   │ 9218  │
│ ExitHsk  │ SuccessCode │ read    │ 9218  │
│ ExitHsk  │ VALID       │ write   │ 4609  │
│ ExitHsk  │ VALID       │ read    │ 4609  │
└──────────┴─────────────┴─────────┴───────┘
sqlite>

What did I miss? Or is this a buggy behaviour?

2021-06-27
11:05 Reply: Will SQLite support GQL or SQL/PGQ in the future? (artifact: e463f2536f user: RoboManni)

As was written above the question about a graph oriented "language" within SQLite can maybe done with a translation from a value enabled extension table into proper SQL statements. This way the graph language would just act as a simplified syntax for more complicated sql statements.

My vision here would be some kind of automated translation of the graph query syntax into normal SQLite3 statements ... even with many join enhancements. I have no fears that SQLite3 would be able to use them ...

Without showing examples and advantages for any new query syntax nobody becomes convinced to do in SQLite3 project what other projects are already doing. You, OP, gave a description of what we could work on in this thread - if you want to keep it alive for the purpose of showing where advantages could be expected.

Let's start with one use case and show the table structure and the queries in both syntaxes. My experience is that talking about and comparing may make the vision more clear. My idea for syntax transformation from a graph language into sql statements is to use Lua or LuaJIT and its lpeg module with the statement_vtab as value enabled table extension; the Lemon parser is not known to me .. that could become a final implementation once some advantages would ever become supported natively in C. First make it run and then make it faster and optimized.

I see potentials for future to continue looking at graph query languages.

10:31 Reply: Feature request: Stored Procedures (artifact: 7f55482020 user: RoboManni)

I am quiet happy with my own way of implementation as a user defined function, which works like a wrapper around a table as "call stack" and using normal trigger mechanisms. It would be nice to have a "standard language frame" for this but it is unlikely to come with SQLite in standard.

What I was missing I found in that value enabled virtual table "statement". This works nice and offers probably even more opportunities than what it does now for me.

I also implemented Lua into my list of extensions and with few extension functions I am able to use that for whatever would be difficult to code in SQL otherwise. My applications became script enabled this way.

2021-06-25
06:04 Reply: How to insert certain table_1 column value in table_2 along with new user input values (artifact: bff3647c00 user: RoboManni)

"INSERT INTO goods select max(cogs_id), ?, ?, ?, ?, ?, ?, ? from cogs", (itemName, itemDesc, itemCode, itemCP, itemQnty)

Count the number of parameters supplied and compare with the number of parameters expected: 7 expected but only 5 supplied. I guess this is your problem.

2021-06-21
07:36 Edit: FILTER clause with window functions (artifact: e3d91ae798 user: RoboManni)

I formed an sql query where I want to calculate the difference between two time stamps of two different events and for that I need to use the filter clause for this one and last_value().

I request improvements for the window (aggregate?) function first_value() and the relaxed usage of the FILTER clause.

What needs to be done to make these two window functions become window aggregate functions, which can be used together with the filter clause? They are not listed as window scalar functions but also do not work like described as window aggregate functions.

The following column definition works fine and is doing what I expect it to do.

Working:

,case when Sent
      then csv(  group_concat(TStamp,'|') filter(where Client or QueueIndex) over ( rows between 500 preceding and 1 preceding) ,-1,'|')
      end as FromTime1

(My extension function csv(TEXT,-1) takes the last field from the concatenated string. group_concat() as a window aggregate function is working fine.

But the following should do the same much easier without having to create a long string.

Not working:

,case when Sent
      then last_value(TStamp) filter(where Client or QueueIndex) over ( rows between 500 preceding and 1 preceding )
      end as FromTime2

But I get following error:

1:FILTER clause may only be used with aggregate window functions

Questions:

  • Why are last_value() and first_value() not also window aggregate functions?
  • Why can they not be used together with the filter clause ?
  • Can the filter clause not be enabled for any window function?

To give some more background:

  • Within a with clause framed query I read a log file in text format line by line from one of my virtual table extensions.
  • Field extractions are done by use of some of my scalar extension functions.
  • Then the calculation happens on that table (MATERIALIZED or not does not make any difference in results)
  • I often do this type of text file analysis but it is the first time I try to do the calculatinos with the WINDOW clauses.

If the first_value() would accept the FILTER clause and if I search through the frame spec descendent then my most recent previous timestamp would be found fastest. The Window frame scan could be finished with the first occurance of a non-Null value which goes into the first_value() aggregate function instead of having to scan the whole frame, which in my case could be up to 500 rows earlier but is usually just 5 rows before the current row.

My next approach should be something like the following, where I also try to influence the window scan direction from CURRENT ROW backwards to the frame start and stop early each window frame scan with the first non-NULL filter match:

,case when Sent
      then TStamp - first_value(TStamp) filter(where Client or QueueIndex) over ( order by Line desc rows between 1 preceding and 500 preceding )
      end as Duration

Result example:

sqlite> select Line,Time,TStamp,Client,QueueIndex,Sent,Duration from ViewMessageSequence limit 45 offset 100;
┌──────┬─────────────────────┬────────────────┬───────────┬────────────┬─────────┬──────────┐
│ Line │        Time         │     TStamp     │  Client   │ QueueIndex │  Sent   │ Duration │
├──────┼─────────────────────┼────────────────┼───────────┼────────────┼─────────┼──────────┤
│ 719  │ 20210618.153515.666 │ 1624037715.666 │           │            │         │          │
│ 720  │ 20210618.153515.668 │ 1624037715.668 │           │            │         │          │
│ 723  │ 20210618.153515.668 │ 1624037715.668 │           │            │         │          │
│ 724  │ 20210618.153515.670 │ 1624037715.67  │           │            │         │          │
│ 725  │ 20210618.153515.670 │ 1624037715.67  │           │            │ 211/211 │ 0.004    │
│ 727  │ 20210618.153517.675 │ 1624037717.675 │ 127.0.0.1 │            │         │          │
│ 757  │ 20210618.153517.675 │ 1624037717.675 │           │            │         │          │
│ 758  │ 20210618.153517.676 │ 1624037717.676 │           │            │         │          │
│ 759  │ 20210618.153517.676 │ 1624037717.676 │           │            │         │          │
│ 760  │ 20210618.153517.676 │ 1624037717.676 │           │            │         │          │
│ 761  │ 20210618.153517.677 │ 1624037717.677 │           │            │         │          │
│ 765  │ 20210618.153517.677 │ 1624037717.677 │           │            │         │          │
│ 766  │ 20210618.153517.679 │ 1624037717.679 │           │            │         │          │
│ 767  │ 20210618.153517.679 │ 1624037717.679 │           │            │ 279/279 │ 0.004    │
│ 769  │ 20210618.153517.881 │ 1624037717.881 │ 127.0.0.1 │            │         │          │
│ 783  │ 20210618.153517.881 │ 1624037717.881 │           │            │         │          │
│ 784  │ 20210618.153517.881 │ 1624037717.881 │           │            │         │          │
│ 785  │ 20210618.153517.883 │ 1624037717.883 │           │            │         │          │
│ 787  │ 20210618.153517.883 │ 1624037717.883 │           │            │         │          │
│ 788  │ 20210618.153517.885 │ 1624037717.885 │           │            │         │          │
│ 789  │ 20210618.153517.885 │ 1624037717.885 │           │            │ 144/144 │ 0.004    │
│ 793  │ 20210618.153519.590 │ 1624037719.59  │           │ 125        │         │          │
│ 794  │ 20210618.153519.590 │ 1624037719.59  │           │            │         │          │
│ 800  │ 20210618.153519.590 │ 1624037719.59  │           │            │         │          │
│ 801  │ 20210618.153519.593 │ 1624037719.593 │           │            │         │          │
│ 802  │ 20210618.153519.593 │ 1624037719.593 │           │            │ 500/500 │ 0.003    │
│ 803  │ 20210618.153519.593 │ 1624037719.593 │           │ 126        │         │          │
│ 804  │ 20210618.153519.593 │ 1624037719.593 │           │            │         │          │
│ 806  │ 20210618.153519.593 │ 1624037719.593 │           │            │         │          │
│ 807  │ 20210618.153519.595 │ 1624037719.595 │           │            │         │          │
│ 808  │ 20210618.153519.595 │ 1624037719.595 │           │            │ 222/222 │ 0.002    │
│ 811  │ 20210618.153519.696 │ 1624037719.696 │           │ 127        │         │          │
│ 812  │ 20210618.153519.696 │ 1624037719.696 │           │            │         │          │
│ 818  │ 20210618.153519.696 │ 1624037719.696 │           │            │         │          │
│ 819  │ 20210618.153519.743 │ 1624037719.743 │           │            │         │          │
│ 820  │ 20210618.153519.744 │ 1624037719.744 │           │            │ 501/501 │ 0.048    │
│ 822  │ 20210618.153519.944 │ 1624037719.944 │ 127.0.0.1 │            │         │          │
│ 852  │ 20210618.153519.944 │ 1624037719.944 │           │            │         │          │
│ 853  │ 20210618.153519.944 │ 1624037719.944 │           │            │         │          │
│ 854  │ 20210618.153519.944 │ 1624037719.944 │           │            │         │          │
│ 855  │ 20210618.153519.944 │ 1624037719.944 │           │            │         │          │
│ 856  │ 20210618.153519.947 │ 1624037719.947 │           │            │         │          │
│ 860  │ 20210618.153519.947 │ 1624037719.947 │           │            │         │          │
│ 861  │ 20210618.153520.047 │ 1624037720.047 │           │            │         │          │
│ 862  │ 20210618.153520.047 │ 1624037720.047 │           │            │ 295/295 │ 0.103    │
└──────┴─────────────────────┴────────────────┴───────────┴────────────┴─────────┴──────────┘
sqlite>
2021-06-20
17:32 Post: FILTER clause with window functions (artifact: 8eac9c99e2 user: RoboManni)

I formed an sql query where I want to calculate the difference between two time stamps of two different events and for that I need to use the filter clause.

The following column definition works fine and is doing what I expect it to do.

,case when Sent
      then csv(  group_concat(TStamp,'|') filter(where Client or >QueueIndex) over ( rows between 500 preceding and 1 preceding) ,-1,'|')
      end as FromTime1

(My extension function csv(TEXT,-1) takes the last field from the concatenated string. group_concat() as a window aggregate function is working fine.

But the following should do the same much easier without having to create a long string.

,case when Sent
      then last_value(TStamp) filter(where Client or QueueIndex) over ( >rows between 500 preceding and 1 preceding )
      end as FromTime2

But I get following error:

1:FILTER clause may only be used with aggregate window functions

Questions:

  • Why are last_value() and first_value() not also window aggregate functions?
  • Why can they not be used together with the filter clause ?
  • Can the filter clause not be enabled for any window function?
2021-06-13
17:50 Reply: Feature request: Stored Procedures (artifact: dfbb2e2b57 user: RoboManni)

Thanks for the hint .. this is really working great!

I also found another virtual table, which I often were working around for, which is the https://github.com/jakethaw/pivot_vtab to transpose a long table into a wide table. Unfortunately that implementation seems to work only with a single columns solution; there is some space for improvements...

2021-06-10
18:35 Reply: Feature request: Stored Procedures (artifact: 50f971012f user: RoboManni)

Yes, I also wrote some virtual tables e.g. to read a directory listing or a list of lines from all files which are selected by another SQL statement or simply to use DBase/FoxPro files from within SQLite (long time ago now and not used anymore since about 2010); but this was long before the value enabled virtual tables became invented. I may have to step into this subject myself once more to see if and how our wishes for parameterized functionality can be accomplished with that capabilities. Unfortunately I am very busy and short in time. But thanks for this good hint.

2021-06-09
15:15 Reply: Feature request: Stored Procedures (artifact: a316ef2123 user: RoboManni)

My example of UDF (user defined function) was just to show how I mimic stored procedures. My implementation is more like an UDF than stored procedure or function because I still need to use the select statement or an other expression consumer. However, what was important for me -to put this effort into such a small set of extension functions- was that big portions of logic were required to be handled. Let's say: My application get data from various sources (text formatted and blobs), inserted into one table and has to schedule different actions based on them. My application has to run uninterrupted for years. By putting the logic into the database in form of triggers enabled to do that without interrupting the operation; enabled also to improve the logic for some data formats while the scheduler for the others were not affected. "SQL injection as a feature." (I am using a controlling input pipe for this).

It is possible to load extension .so/.dll files - but how can they be unloaded or updated/replaced on a permanently in-use db connection? This is OS dependent if possible at all, I guess. With triggers this is very easy, you can simply drop them and create a new one - even both within one transaction, which makes sure that the rest does not get disturbed and even the next message can use the updated logic without getting lost.

For me the main benefit for UDF or Stored Procedure is the parameterization. If a view could be called with parameters, that would be almost what was in my mind from the beginning.

My implementation cannot generate table results, which a stored procedure (called with parameters) might be able to return. That's the main difference and where I see the benefit.

2021-06-06
11:22 Edit: Feature request: Stored Procedures (artifact: 78a60bdeec user: RoboManni)

Until now SQLite3 does not support Stored Procedures (at least as far as I am aware)

Are there any plans to introduce also Stored Procedures into SQLite3 ?

Since many more than 10 years I am using therefore my own extension functions to mimic such a feature. I named my function "call" and its first parameter is the name of the procedure and the other parameters are the inputs to that function.

The call function inserts the parameters into a table with name "stack", which fires a trigger which is watching the name, the first parameter of my call.

The trigger itself can work with all the parameters from the stack record, a helper extension function supports this task.

The trigger itself calls a function return, which can be filled into the stack record but more important becomes the return value of the "call" extended function call and therefore is then the reply into the original sql statement, which caused the trigger to fire.

Because all this happens in scope of the same db context the stack leaves only a trace if there was no delete all from stack happening. I am controlling this for debug purposes, too.

Here is an example for the recursive form of the faculty for number 6 ( = 1 * 2 * 3 * 4 * 5 * 6 = 720 ) , 7! = 5040 , 8! = 40320

D:\SQLite3x\sandbox>sqlite3 LoadExTest.SQB
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> .load sqlite3.dll
sqlite> .mode box
sqlite> select call('Fakt',6),call('Fakt',7),call('Fakt',8);
┌────────────────┬────────────────┬────────────────┐
│ call('Fakt',6) │ call('Fakt',7) │ call('Fakt',8) │
├────────────────┼────────────────┼────────────────┤
│ 720            │ 5040           │ 40320          │
└────────────────┴────────────────┴────────────────┘
sqlite>

The stored function itself and the stack table looks like the following:

  • Debug() sends output to Windows function OutputDebugString
  • argc() returns the count if non-NULL parameters
  • argv() returns one parameter from the recent stack table record
  • JobLevel() return the recursive depth of calls from the level column
  • result() fills the column result and will be the return value for call
CREATE TABLE stack( job text, -- the function name which will fire the trigger
   level integer, inserted float, returned float,        -- some debug support
   result blob,
   par0 blob, par1 blob, par2 blob, par3 blob, par4 blob,
   par5 blob, par6 blob, par7 blob, par8 blob, par9 blob );

CREATE TRIGGER Fakt after UPDATE OF job ON stack
FOR EACH ROW WHEN new.job=='Fakt' and argc()>=1
BEGIN
  select Debug(concat('JobLevel()=',JobLevel(),': Fakt(',argv(0),')=',
    result( argv(0) * case
      when argv(0)<2 then 1
      else call(new.job,argv(0)-1)
    end   )   )      );
END;
10:26 Post: Feature request: Stored Procedures (artifact: f5377c4ab1 user: RoboManni)

Until now SQLite3 does not support Stored Procedures (at least as far as I am aware)

Since many more than 10 years I am using therefore my own extension functions to mimic such a feature. I named my function "call" and its first parameter is the name of the procedure and the other parameters are the inputs to that function.

The call function inserts the parameters into a table with name "stack", which fires a trigger which is watching the name, the first parameter of my call.

The trigger itself can work with all the parameters from the stack record, a helper extension function supports this task.

The trigger itself calls a function return, which can be filled into the stack record but more important becomes the return value of the "call" extended function call and therefore is then the reply into the original sql statement, which caused the trigger to fire.

Because all this happens in scope of the same db context the stack leaves only a trace if there was no delete all from stack happening. I am controlling this for debug purposes, too.

Here is an example for the recursive form of the faculty for number 6 ( = 1 * 2 * 3 * 4 * 5 * 6 = 720 )

D:\SQLite3x\sandbox>sqlite3 LoadExTest.SQB
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> .load sqlite3.dll
sqlite> .mode box
sqlite> select call('Fakt',6);
┌────────────────┐
│ call('Fakt',6) │
├────────────────┤
│ 720            │
└────────────────┘
sqlite>

Are there any plans to introduce also Stored Procedures into SQLite3 ?

2021-04-01
05:52 Edit: Request for correction: EXCLUDE NO OTHERS (artifact: 772b9c7e4b user: RoboManni)

in window-defn railroad syntax diagram it is shown EXCLUDE NOT OTHERS but correct would be EXCLUDE NO OTHERS (without the T)

2021-03-31
18:02 Reply: Request for correction: EXCLUDE NO OTHERS (artifact: 0d9e3e4202 user: RoboManni)

thank you very much

17:06 Post: Request for correction: EXCLUDE NO OTHERS (artifact: 76e8b5f5ef user: RoboManni)

in window-defn railroad syntax diagram it is shown EXCLUDE NOT OTHERS but correct would be EXCLUDE NO OTHERS (without the T)