Re: [sqlite] Is pragma index_list without supplied table name valid SQL?
Don't forget this point about pragmas: https://www.sqlite.org/pragma.html "No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact." That way if there's a typo, or if you try a new pragma in an old version then it won't complain, it just won't do anything. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert Sent: Wednesday, August 01, 2018 10:13 AM To: General Discussion of SQLite Database Subject: [sqlite] Is pragma index_list without supplied table name valid SQL? Using SQLite 3.22.0 In my app I have code to determine if a given SQL string is data-producing, non-data producing or invalid. It uses these 3 SQLite functions: sqlite3_prepare_v3 sqlite3_stmt_readonly sqlite3_column_count Have been using this code for a few years and sofar never failed, but now come across: pragma index_list so, without a supplied table name. This gives me data-producing and I think it should give me invalid. Firstly is this SQL indeed invalid? Secondly, if it is I need to add some code to pick this up and was thinking about using explain for that. Explain pragma index_list gives me: addr opcode p1 p2 p3 p4 p5 comment --- 0 Init 0 1 0 00 Start at 1 1 Halt 0 0 0 00 And that to me looks it is indeed an invalid SQL as it gives a Halt already in the second row and produces no further rows. Am I right here and would this be a good way to pick up invalid SQL? RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
I think you might be right there, but for my practical purpose I need the result to be invalid. I just wonder if a Halt at row 2 and no further rows produced is good way to determine this. RBS On Wed, Aug 1, 2018 at 3:39 PM, Hick Gunter wrote: > Judging from the following output, I would say it is data producing, but > returns no rows for no table or a table that has no indexes. Just because a > given select statement returns no matching rows does not make it invalid > > asql> create temp table test (i integer, t text); > asql> create index test_i on test(i); > asql> pragma index_list(test); > seq name uniq > - > 0 test_i 0 > asql> explain pragma index_list(test); > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Integer0 1 000 NULL > 1 String80 2 0 test_i 00 NULL > 2 Integer0 3 000 NULL > 3 ResultRow 1 3 000 NULL > 4 Halt 0 0 000 NULL > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von David Raymond > Gesendet: Mittwoch, 01. August 2018 16:31 > An: SQLite mailing list > Betreff: [EXTERNAL] Re: [sqlite] Is pragma index_list without supplied > table name valid SQL? > > Don't forget this point about pragmas: > > https://www.sqlite.org/pragma.html > "No error messages are generated if an unknown pragma is issued. Unknown > pragmas are simply ignored. This means if there is a typo in a pragma > statement the library does not inform the user of the fact." > > That way if there's a typo, or if you try a new pragma in an old version > then it won't complain, it just won't do anything. > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Wednesday, August 01, 2018 10:13 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Is pragma index_list without supplied table name valid > SQL? > > Using SQLite 3.22.0 > > In my app I have code to determine if a given SQL string is > data-producing, non-data producing or invalid. It uses these 3 SQLite > functions: > > sqlite3_prepare_v3 > sqlite3_stmt_readonly > sqlite3_column_count > > Have been using this code for a few years and sofar never failed, but now > come across: > pragma index_list > so, without a supplied table name. > This gives me data-producing and I think it should give me invalid. > > Firstly is this SQL indeed invalid? > > Secondly, if it is I need to add some code to pick this up and was > thinking about using explain for that. Explain pragma index_list gives me: > > addr opcode p1 p2 p3 p4 p5 comment > --- > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > And that to me looks it is indeed an invalid SQL as it gives a Halt > already in the second row and produces no further rows. > > Am I right here and would this be a good way to pick up invalid SQL? > > > RBS > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) > +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
Use the CLI code as an example and see how they do it? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert Sent: Wednesday, August 01, 2018 11:56 AM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL? > May I ask about your use-case and what specifically is needed? Probably exactly the same as you are using in your SQLitespeed app. There is a SQL text box and the user can type anything in there he/she wants. App then needs to determine how to handle that string: Produce data to show, run a non-data producing SQL, or reject it because it is invalid. > The "let's feed it to the engine and see if it cries" method of error detection is dangerous to my mind Not sure what you mean with that. The user decides what he wants to do, app needs to determine how it should be handled. RBS On Wed, Aug 1, 2018 at 4:39 PM, R Smith wrote: > On 2018/08/01 4:50 PM, Bart Smissaert wrote: > >> I think you might be right there, but for my practical purpose I need the >> result to be invalid. >> I just wonder if a Halt at row 2 and no further rows produced is good way >> to determine this. >> > > Such a hard question to answer. It's like asking if a Robo-suitcase is a > good idea for your fishing tackle... It /might/ be, but since none of us > use it that way, it's hard to answer definitively. > > That said, David Raymond already did a good job of checking some of the > pragmas for you and at least proved that false positives exist for the > simple rule you expressed. > > May I ask about your use-case and what specifically is needed? Perhaps a > simpler way exists to get to it. The "let's feed it to the engine and see > if it cries" method of error detection is dangerous to my mind, especially > for pragmas that alter the DB, but it might be perfectly o.k. in your > use-case. > > > Cheers, > Ryan > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
Judging from the following output, I would say it is data producing, but returns no rows for no table or a table that has no indexes. Just because a given select statement returns no matching rows does not make it invalid asql> create temp table test (i integer, t text); asql> create index test_i on test(i); asql> pragma index_list(test); seq name uniq - 0 test_i 0 asql> explain pragma index_list(test); addr opcode p1p2p3p4 p5 comment - - -- - 0 Integer0 1 000 NULL 1 String80 2 0 test_i 00 NULL 2 Integer0 3 000 NULL 3 ResultRow 1 3 000 NULL 4 Halt 0 0 000 NULL -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von David Raymond Gesendet: Mittwoch, 01. August 2018 16:31 An: SQLite mailing list Betreff: [EXTERNAL] Re: [sqlite] Is pragma index_list without supplied table name valid SQL? Don't forget this point about pragmas: https://www.sqlite.org/pragma.html "No error messages are generated if an unknown pragma is issued. Unknown pragmas are simply ignored. This means if there is a typo in a pragma statement the library does not inform the user of the fact." That way if there's a typo, or if you try a new pragma in an old version then it won't complain, it just won't do anything. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert Sent: Wednesday, August 01, 2018 10:13 AM To: General Discussion of SQLite Database Subject: [sqlite] Is pragma index_list without supplied table name valid SQL? Using SQLite 3.22.0 In my app I have code to determine if a given SQL string is data-producing, non-data producing or invalid. It uses these 3 SQLite functions: sqlite3_prepare_v3 sqlite3_stmt_readonly sqlite3_column_count Have been using this code for a few years and sofar never failed, but now come across: pragma index_list so, without a supplied table name. This gives me data-producing and I think it should give me invalid. Firstly is this SQL indeed invalid? Secondly, if it is I need to add some code to pick this up and was thinking about using explain for that. Explain pragma index_list gives me: addr opcode p1 p2 p3 p4 p5 comment --- 0 Init 0 1 0 00 Start at 1 1 Halt 0 0 0 00 And that to me looks it is indeed an invalid SQL as it gives a Halt already in the second row and produces no further rows. Am I right here and would this be a good way to pick up invalid SQL? RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
On 2018/08/01 5:29 PM, Charles Leifer wrote:
You can simply use:
PRAGMA table_info('my_table')
To get a list of columns, which you can check against and then
conditionally add your column.
Aye, but during a script in SQL-only you don't have that luxury. One
could also use a similar pragma to check if a table exists before
creating it, but the SQL for:
CREATE TABLE IF NOT EXISTS... makes it possible to add things without
failing mid-script with no programmatic help (and to be blunt, much
easier and nicer).
That said, I never add columns this way - but that might only be
precisely because its IF NOT EXISTS does not exist. So...
+1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
On 01 Aug 2018, at 14:34, Simon White wrote: > I would like to suggest the addition of the "If not exists" to the Add > Column feature of SQLite. There are quite common situations where > ensuring a column exists is important so that an update to remote > devices will not fail but it is not so important that deprecated fields > be removed. This is often the case with backward compatibility. New > columns will not affect old systems but allows all remote devices > running older software to be updated using the same process as new > devices. Once the hardware reaches end of life it will be replaced and > the new hardware will use the new columns. So having the ability to > Alter the table with a series of Add Column commands ensures that the > new records included in the update are added to the table. These is > especially true for limited remote devices where full database > management is not feasible. In this scenario all that is required is > that the required columns exist. So to be able to alter the table with > a standard SQL command is the most efficient method on such devices. > Developing scripts to drop and re-create and re-load tables on hundreds > of remote devices greatly increases the risk of failures. Having the "if > not exists" would remove all of this potential complexity and allow a > quick and easy method to ensure the column exists in the table. I'd like to second this and for just the same reasons. Something like: alter table add column if not exists my_new_col ...; I would find very helpful. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
On 2018/08/01 4:50 PM, Bart Smissaert wrote: I think you might be right there, but for my practical purpose I need the result to be invalid. I just wonder if a Halt at row 2 and no further rows produced is good way to determine this. Such a hard question to answer. It's like asking if a Robo-suitcase is a good idea for your fishing tackle... It /might/ be, but since none of us use it that way, it's hard to answer definitively. That said, David Raymond already did a good job of checking some of the pragmas for you and at least proved that false positives exist for the simple rule you expressed. May I ask about your use-case and what specifically is needed? Perhaps a simpler way exists to get to it. The "let's feed it to the engine and see if it cries" method of error detection is dangerous to my mind, especially for pragmas that alter the DB, but it might be perfectly o.k. in your use-case. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
Yes, good idea. I would be interested how other users handle this problem, that is determining if a statement is (potentially) data producing, non-data producing or just invalid. RBS On Wed, Aug 1, 2018 at 5:23 PM, David Raymond wrote: > Use the CLI code as an example and see how they do it? > > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Wednesday, August 01, 2018 11:56 AM > To: SQLite mailing list > Subject: Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied > table name valid SQL? > > > May I ask about your use-case and what specifically is needed? > > Probably exactly the same as you are using in your SQLitespeed app. > There is a SQL text box and the user can type anything in there he/she > wants. > App then needs to determine how to handle that string: > Produce data to show, run a non-data producing SQL, or reject it because it > is invalid. > > > The "let's feed it to the engine and see if it cries" method of error > detection is dangerous to my mind > > Not sure what you mean with that. > The user decides what he wants to do, app needs to determine how it should > be handled. > > RBS > > > > > On Wed, Aug 1, 2018 at 4:39 PM, R Smith wrote: > > > On 2018/08/01 4:50 PM, Bart Smissaert wrote: > > > >> I think you might be right there, but for my practical purpose I need > the > >> result to be invalid. > >> I just wonder if a Halt at row 2 and no further rows produced is good > way > >> to determine this. > >> > > > > Such a hard question to answer. It's like asking if a Robo-suitcase is a > > good idea for your fishing tackle... It /might/ be, but since none of us > > use it that way, it's hard to answer definitively. > > > > That said, David Raymond already did a good job of checking some of the > > pragmas for you and at least proved that false positives exist for the > > simple rule you expressed. > > > > May I ask about your use-case and what specifically is needed? Perhaps a > > simpler way exists to get to it. The "let's feed it to the engine and see > > if it cries" method of error detection is dangerous to my mind, > especially > > for pragmas that alter the DB, but it might be perfectly o.k. in your > > use-case. > > > > > > Cheers, > > Ryan > > > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is pragma index_list without supplied table name valid SQL?
Using SQLite 3.22.0 In my app I have code to determine if a given SQL string is data-producing, non-data producing or invalid. It uses these 3 SQLite functions: sqlite3_prepare_v3 sqlite3_stmt_readonly sqlite3_column_count Have been using this code for a few years and sofar never failed, but now come across: pragma index_list so, without a supplied table name. This gives me data-producing and I think it should give me invalid. Firstly is this SQL indeed invalid? Secondly, if it is I need to add some code to pick this up and was thinking about using explain for that. Explain pragma index_list gives me: addr opcode p1 p2 p3 p4 p5 comment --- 0 Init 0 1 0 00 Start at 1 1 Halt 0 0 0 00 And that to me looks it is indeed an invalid SQL as it gives a Halt already in the second row and produces no further rows. Am I right here and would this be a good way to pick up invalid SQL? RBS ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
You can simply use:
PRAGMA table_info('my_table')
To get a list of columns, which you can check against and then
conditionally add your column.
On Wed, Aug 1, 2018 at 9:13 AM, Tim Streater wrote:
> On 01 Aug 2018, at 14:34, Simon White
> wrote:
>
> > I would like to suggest the addition of the "If not exists" to the Add
> > Column feature of SQLite. There are quite common situations where
> > ensuring a column exists is important so that an update to remote
> > devices will not fail but it is not so important that deprecated fields
> > be removed. This is often the case with backward compatibility. New
> > columns will not affect old systems but allows all remote devices
> > running older software to be updated using the same process as new
> > devices. Once the hardware reaches end of life it will be replaced and
> > the new hardware will use the new columns. So having the ability to
> > Alter the table with a series of Add Column commands ensures that the
> > new records included in the update are added to the table. These is
> > especially true for limited remote devices where full database
> > management is not feasible. In this scenario all that is required is
> > that the required columns exist. So to be able to alter the table with
> > a standard SQL command is the most efficient method on such devices.
> > Developing scripts to drop and re-create and re-load tables on hundreds
> > of remote devices greatly increases the risk of failures. Having the "if
> > not exists" would remove all of this potential complexity and allow a
> > quick and easy method to ensure the column exists in the table.
>
> I'd like to second this and for just the same reasons. Something like:
>
>alter table add column if not exists my_new_col ...;
>
> I would find very helpful.
>
>
> --
> Cheers -- Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
This makes me feel there is a lot of pain coming in the future.
Given an update statement for n dbs of unknown state,
When a db lacks columns necessary to successfully execute the sql
Then add the columns to the db
I'm trying to imagine how to keep n remote dbs in a known state, say z,
when various updates sent to them put result in states a, b, c, d...z. How
do you keep all the db states sync'd when update 1 could create a column
but it fails or was not sent to all n dbs, and update 2 could create a
column but it fails or was not sent to all n dbs? How do you know what
state each remote db is in, or isn't in?
On Wed, Aug 1, 2018 at 10:46 AM R Smith wrote:
> On 2018/08/01 5:29 PM, Charles Leifer wrote:
> > You can simply use:
> >
> > PRAGMA table_info('my_table')
> >
> > To get a list of columns, which you can check against and then
> > conditionally add your column.
>
> Aye, but during a script in SQL-only you don't have that luxury. One
> could also use a similar pragma to check if a table exists before
> creating it, but the SQL for:
> CREATE TABLE IF NOT EXISTS... makes it possible to add things without
> failing mid-script with no programmatic help (and to be blunt, much
> easier and nicer).
>
> That said, I never add columns this way - but that might only be
> precisely because its IF NOT EXISTS does not exist. So...
>
> +1
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Add Column with "If Not Exists"
Hi I would like to suggest the addition of the "If not exists" to the Add Column feature of SQLite. There are quite common situations where ensuring a column exists is important so that an update to remote devices will not fail but it is not so important that deprecated fields be removed. This is often the case with backward compatibility. New columns will not affect old systems but allows all remote devices running older software to be updated using the same process as new devices. Once the hardware reaches end of life it will be replaced and the new hardware will use the new columns. So having the ability to Alter the table with a series of Add Column commands ensures that the new records included in the update are added to the table. These is especially true for limited remote devices where full database management is not feasible. In this scenario all that is required is that the required columns exist. So to be able to alter the table with a standard SQL command is the most efficient method on such devices. Developing scripts to drop and re-create and re-load tables on hundreds of remote devices greatly increases the risk of failures. Having the "if not exists" would remove all of this potential complexity and allow a quick and easy method to ensure the column exists in the table. -- Regards, Simon White dCipher Computing 705-500-0191 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
Looking like the generalized answer is no, as you can still get that with some valid pragma statements, especially ones that don't return a value. D:\>sqlite3 SQLite version 3.24.0 2018-06-04 19:24:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> explain pragma index_list; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 1 000 Start at 1 1 Halt 0 0 000 sqlite> explain pragma index_list();--with parenthesis but no table name Error: near ")": syntax error sqlite> explain pragma index_list(missingTable); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 1 000 Start at 1 1 Halt 0 0 000 sqlite> create table noIndexes (a int); sqlite> explain pragma index_list(noIndexes); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 2 000 Start at 2 1 Halt 0 0 000 2 Transaction0 0 1 0 01 usesStmtJournal=0 3 Goto 0 1 000 sqlite> create table withIndexes (a text primary key, b unique); sqlite> explain pragma index_list(withIndexes); addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 14000 Start at 14 1 Integer0 1 000 r[1]=0 2 String80 2 0 sqlite_autoindex_withIndexes_2 00 r[2]='sqlite_autoindex_withIndexes_2' 3 Integer1 3 000 r[3]=1 4 String80 4 0 u 00 r[4]='u' 5 Integer0 5 000 r[5]=0 6 ResultRow 1 5 000 output=r[1..5] 7 Integer1 1 000 r[1]=1 8 String80 2 0 sqlite_autoindex_withIndexes_1 00 r[2]='sqlite_autoindex_withIndexes_1' 9 Integer1 3 000 r[3]=1 10String80 4 0 pk 00 r[4]='pk' 11Integer0 5 000 r[5]=0 12ResultRow 1 5 000 output=r[1..5] 13Halt 0 0 000 14Transaction0 0 2 0 01 usesStmtJournal=0 15Goto 0 1 000 sqlite> explain pragma thisIsABadPragmaName; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 1 000 Start at 1 1 Halt 0 0 000 sqlite> explain pragma foreign_keys; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 1 000 Start at 1 1 Int64 0 1 0 1 00 r[1]=1 2 ResultRow 1 1 000 output=r[1] 3 Halt 0 0 000 sqlite> explain pragma cache_spill; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 1 000 Start at 1 1 Int64 0 1 0 15857 00 r[1]=15857 2 ResultRow 1 1 000 output=r[1] 3 Halt 0 0 000 sqlite> explain pragma case_sensitive_like; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 1 000 Start at 1 1 Halt 0 0 000 sqlite> explain pragma case_sesitive_like = 1;--typo addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 1 000 Start at 1 1 Halt 0 0 000 sqlite> explain pragma case_sensitive_like = 1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 1 0
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
Yes, it looks indeed explain doesn't help me out there, thanks. Best probably to compare to the pragma list and check the SQL length. RBS On Wed, Aug 1, 2018 at 4:06 PM, David Raymond wrote: > Looking like the generalized answer is no, as you can still get that with > some valid pragma statements, especially ones that don't return a value. > > D:\>sqlite3 > SQLite version 3.24.0 2018-06-04 19:24:41 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> explain pragma index_list; > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 1 000 Start at 1 > 1 Halt 0 0 000 > > sqlite> explain pragma index_list();--with parenthesis but no table name > Error: near ")": syntax error > > sqlite> explain pragma index_list(missingTable); > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 1 000 Start at 1 > 1 Halt 0 0 000 > > sqlite> create table noIndexes (a int); > > sqlite> explain pragma index_list(noIndexes); > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 2 000 Start at 2 > 1 Halt 0 0 000 > 2 Transaction0 0 1 0 01 usesStmtJournal=0 > 3 Goto 0 1 000 > > sqlite> create table withIndexes (a text primary key, b unique); > > sqlite> explain pragma index_list(withIndexes); > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 14000 Start at 14 > 1 Integer0 1 000 r[1]=0 > 2 String80 2 0 sqlite_autoindex_withIndexes_2 00 > r[2]='sqlite_autoindex_withIndexes_2' > 3 Integer1 3 000 r[3]=1 > 4 String80 4 0 u 00 r[4]='u' > 5 Integer0 5 000 r[5]=0 > 6 ResultRow 1 5 000 output=r[1..5] > 7 Integer1 1 000 r[1]=1 > 8 String80 2 0 sqlite_autoindex_withIndexes_1 00 > r[2]='sqlite_autoindex_withIndexes_1' > 9 Integer1 3 000 r[3]=1 > 10String80 4 0 pk 00 r[4]='pk' > 11Integer0 5 000 r[5]=0 > 12ResultRow 1 5 000 output=r[1..5] > 13Halt 0 0 000 > 14Transaction0 0 2 0 01 usesStmtJournal=0 > 15Goto 0 1 000 > > sqlite> explain pragma thisIsABadPragmaName; > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 1 000 Start at 1 > 1 Halt 0 0 000 > > sqlite> explain pragma foreign_keys; > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 1 000 Start at 1 > 1 Int64 0 1 0 1 00 r[1]=1 > 2 ResultRow 1 1 000 output=r[1] > 3 Halt 0 0 000 > > sqlite> explain pragma cache_spill; > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 1 000 Start at 1 > 1 Int64 0 1 0 15857 00 r[1]=15857 > 2 ResultRow 1 1 000 output=r[1] > 3 Halt 0 0 000 > > sqlite> explain pragma case_sensitive_like; > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 1 000 Start at 1 > 1 Halt 0 0 000 > > sqlite> explain pragma case_sesitive_like = 1;--typo > addr opcode p1p2p3p4 p5 comment > - - -- - > 0
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
> May I ask about your use-case and what specifically is needed? Probably exactly the same as you are using in your SQLitespeed app. There is a SQL text box and the user can type anything in there he/she wants. App then needs to determine how to handle that string: Produce data to show, run a non-data producing SQL, or reject it because it is invalid. > The "let's feed it to the engine and see if it cries" method of error detection is dangerous to my mind Not sure what you mean with that. The user decides what he wants to do, app needs to determine how it should be handled. RBS On Wed, Aug 1, 2018 at 4:39 PM, R Smith wrote: > On 2018/08/01 4:50 PM, Bart Smissaert wrote: > >> I think you might be right there, but for my practical purpose I need the >> result to be invalid. >> I just wonder if a Halt at row 2 and no further rows produced is good way >> to determine this. >> > > Such a hard question to answer. It's like asking if a Robo-suitcase is a > good idea for your fishing tackle... It /might/ be, but since none of us > use it that way, it's hard to answer definitively. > > That said, David Raymond already did a good job of checking some of the > pragmas for you and at least proved that false positives exist for the > simple rule you expressed. > > May I ask about your use-case and what specifically is needed? Perhaps a > simpler way exists to get to it. The "let's feed it to the engine and see > if it cries" method of error detection is dangerous to my mind, especially > for pragmas that alter the DB, but it might be perfectly o.k. in your > use-case. > > > Cheers, > Ryan > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNION ALL bug in Multi-threading
I find a bug that exists in `UNION ALL`.
tldr: `UNION ALL` will not merge the data in different schemas in the moment
that one of schema is committed but the another not. BUT, `UNION` will.
Here are the reproduce steps:
Preparation:
1. Prepare a database named "OLD"
1.1 Create a table for "OLD": `CREATE TABLE old(i INTEGER)`
1.2 Insert values into "OLD" to make it large enough: `INSERT INTO old
VALUES(?1)`
2. Prepare a database named "NEW"
2.1 Create a table for "NEW": `CREATE TABLE new(i INTEGER)`
Migration:
For thread 1:
1. Attach "OLD" to "NEW" as "oldSchema": `ATTACH OLD AS oldSchema`
2. Migrate data from "OLD" to "NEW" in same transaction. Note that they should
be executed with same handle using ATTACH mentioned in 1.
2.1 `BEGIN IMMEDIATE`
2.1 Select one of the row from "OLD": `SELECT i FROM oldSchema.old`
2.2 Insert the row into "NEW": `INSERT INTO main.new VALUES(?1)`
2.3 Delete the row from "OLD": `DELETE FROM oldSchema.old WHERE i == ?1`
2.4 `COMMIT`
For thread 2-N:
1. Create a view that union two tables: `CREATE TEMP VIEW v AS SELECT i FROM
oldSchema.old UNION ALL SELECT i FROM main.new`
2. Select one of the value from view: `SELECT i FROM temp.v ORDER BY i LIMIT 1
OFFSET ?1`.
Here is the strange result:
As an example, if the values of 0-999 is inserted into "OLD", then value N
should be selected as expected at offset N.
But in these kind of steps, it will not.
It can be a little bit hard to reproduce due to the multi-threading. BUT if it
sleeps for a while when committing, it will be much easier to reproduce:
// vdbeCommit method of vdbeaux.c
for(i=0; rc==SQLITE_OK idb-nDb; i++){
Btree *pBt = db-aDb[i].pBt;
sqlite3_sleep(10); // additional sleep here
if( pBt ){
rc = sqlite3BtreeCommitPhaseOne(pBt, 0);
}
}
It seems that the bug happens when one of the schema is committed but the
another one is not.
On the other handle, if `UNION ALL` is changed to `UNION` while creating view,
the bug will not happen too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNION ALL bug in Multi-threading
I find a bug that exists in `UNION ALL`.
tldr: `UNION ALL` will not merge the data in different schemas in the moment
that one of schema is committed but the another not. BUT, `UNION` will.
Here are the reproduce steps:
Preparation:
1. Prepare a database named "OLD"
1.1 Create a table for "OLD": `CREATE TABLE old(i INTEGER)`
1.2 Insert values into "OLD" to make it large enough: `INSERT INTO old
VALUES(?1)`
2. Prepare a database named "NEW"
2.1 Create a table for "NEW": `CREATE TABLE new(i INTEGER)`
Migration:
For thread 1:
1. Attach "OLD" to "NEW" as "oldSchema": `ATTACH OLD AS oldSchema`
2. Migrate data from "OLD" to "NEW" in same transaction. Note that they should
be executed with same handle using ATTACH mentioned in 1.
2.1 `BEGIN IMMEDIATE`
2.1 Select one of the row from "OLD": `SELECT i FROM oldSchema.old`
2.2 Insert the row into "NEW": `INSERT INTO main.new VALUES(?1)`
2.3 Delete the row from "OLD": `DELETE FROM oldSchema.old WHERE i == ?1`
2.4 `COMMIT`
For thread 2-N:
1. Create a view that union two tables: `CREATE TEMP VIEW v AS SELECT i FROM
oldSchema.old UNION ALL SELECT i FROM main.new`
2. Select one of the value from view: `SELECT i FROM temp.v ORDER BY i LIMIT 1
OFFSET ?1`.
Here is the strange result:
As an example, if the values of 0-999 is inserted into "OLD", then value N
should be selected as expected at offset N.
But in these kind of steps, it will not.
It can be a little bit hard to reproduce due to the multi-threading. BUT if it
sleeps for a while when committing, it will be much easier to reproduce:
// vdbeCommit method of vdbeaux.c
for(i=0; rc==SQLITE_OK idb-nDb; i++){
Btree *pBt = db-aDb[i].pBt;
sqlite3_sleep(10); // additional sleep here
if( pBt ){
rc = sqlite3BtreeCommitPhaseOne(pBt, 0);
}
}
It seems that the bug happens when one of the schema is committed but the
another one is not.
On the other handle, if `UNION ALL` is changed to `UNION` while creating view,
the bug will not happen too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading
Are you sure it is not the special case documented in the ATTACH command? (see
https://sqlite.org/lang_attach.html)
" Transactions involving multiple attached databases are atomic, assuming that
the main database is not ":memory:" and the journal_mode is not WAL. If the
main database is ":memory:" or if the journal_mode is WAL, then transactions
continue to be atomic within each individual database file. But if the host
computer crashes in the middle of a COMMIT where two or more database files are
updated, some of those files might get the changes where others might not."
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von sanhua.zh
Gesendet: Mittwoch, 01. August 2018 09:57
An: sqlite-users ; drh
Betreff: [EXTERNAL] [sqlite] UNION ALL bug in Multi-threading
I find a bug that exists in `UNION ALL`.
tldr: `UNION ALL` will not merge the data in different schemas in the moment
that one of schema is committed but the another not. BUT, `UNION` will.
Here are the reproduce steps:
Preparation:
1. Prepare a database named "OLD"
1.1 Create a table for "OLD": `CREATE TABLE old(i INTEGER)`
1.2 Insert values into "OLD" to make it large enough: `INSERT INTO old
VALUES(?1)` 2. Prepare a database named "NEW"
2.1 Create a table for "NEW": `CREATE TABLE new(i INTEGER)`
Migration:
For thread 1:
1. Attach "OLD" to "NEW" as "oldSchema": `ATTACH OLD AS oldSchema` 2. Migrate
data from "OLD" to "NEW" in same transaction. Note that they should be executed
with same handle using ATTACH mentioned in 1.
2.1 `BEGIN IMMEDIATE`
2.1 Select one of the row from "OLD": `SELECT i FROM oldSchema.old`
2.2 Insert the row into "NEW": `INSERT INTO main.new VALUES(?1)`
2.3 Delete the row from "OLD": `DELETE FROM oldSchema.old WHERE i == ?1`
2.4 `COMMIT`
For thread 2-N:
1. Create a view that union two tables: `CREATE TEMP VIEW v AS SELECT i FROM
oldSchema.old UNION ALL SELECT i FROM main.new` 2. Select one of the value from
view: `SELECT i FROM temp.v ORDER BY i LIMIT 1 OFFSET ?1`.
Here is the strange result:
As an example, if the values of 0-999 is inserted into "OLD", then value N
should be selected as expected at offset N.
But in these kind of steps, it will not.
It can be a little bit hard to reproduce due to the multi-threading. BUT if it
sleeps for a while when committing, it will be much easier to reproduce:
// vdbeCommit method of vdbeaux.c
for(i=0; rc==SQLITE_OK idb-nDb; i++){
Btree *pBt = db-aDb[i].pBt;
sqlite3_sleep(10); // additional sleep here if( pBt ){
rc = sqlite3BtreeCommitPhaseOne(pBt, 0); } }
It seems that the bug happens when one of the schema is committed but the
another one is not.
On the other handle, if `UNION ALL` is changed to `UNION` while creating view,
the bug will not happen too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading
On Wed, Aug 1, 2018 at 10:39 AM Hick Gunter wrote: > Are you sure it is not the special case documented in the ATTACH command? > (see https://sqlite.org/lang_attach.html) > Good point. OP will tell us if it applies to his/her case. > " Transactions involving multiple attached databases are atomic, assuming > that the main database is not ":memory:" and the journal_mode is not WAL. > If the main database is ":memory:" or if the journal_mode is WAL, then > transactions continue to be atomic within each individual database file. > But if the host computer crashes in the middle of a COMMIT where two or > more database files are updated, some of those files might get the changes > where others might not." > Still, why would UNION behave differently from UNION ALL? That's the puzzling part IMHO. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading
UNION ALL will just return whatever both sides produce, irrespective of duplicates, whereas UNION will return only 1 copy of duplicated records. asql> select 1 as x union select 1 as x; x - 1 asql> select 1 as x union all select 1 as x; x - 1 1attacho So depending on the relative positions of the read and write transactions and the order of commits, the query will see either one (before or after both commits), two (NEW commits before OLD) or even zero (OLD commits before NEW) copies of any given record. My guess is that NEW always commits before OLD and so UNION ALL is sometimes returning 2 copies of one record. I also expect that changing the order of the databases (i.e main database is OLD and NEW gets attached) will have 1 record missing both in UNION and UNION ALL. Additionally, the query has an ORDER BY clause that requires sorting, which drastically changes the query plan. asql> create table t1 (i integer); asql> create table t2 (i integer); asql> create view un as select * from t1 union select * from t2; asql> create view ua as select * from t1 union all select * from t2; asql> .explain asql> explain query plan select * from un order by 1; sele order from deta - 2 0 0 SCAN TABLE t1 (~100 rows) 3 0 0 SCAN TABLE t2 (~100 rows) 1 0 0 COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION) 0 0 0 SCAN SUBQUERY 1 (~200 rows) 0 0 0 USE TEMP B-TREE FOR ORDER BY asql> explain query plan select * from ua order by 1; sele order from deta - 1 0 0 SCAN TABLE t1 (~100 rows) 1 0 0 USE TEMP B-TREE FOR ORDER BY 2 0 0 SCAN TABLE t2 (~100 rows) 2 0 0 USE TEMP B-TREE FOR ORDER BY 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Mittwoch, 01. August 2018 11:34 An: General Discussion of SQLite Database Betreff: Re: [sqlite] [EXTERNAL] UNION ALL bug in Multi-threading On Wed, Aug 1, 2018 at 10:39 AM Hick Gunter wrote: > Are you sure it is not the special case documented in the ATTACH command? > (see https://sqlite.org/lang_attach.html) > Good point. OP will tell us if it applies to his/her case. > " Transactions involving multiple attached databases are atomic, > assuming that the main database is not ":memory:" and the journal_mode is not > WAL. > If the main database is ":memory:" or if the journal_mode is WAL, then > transactions continue to be atomic within each individual database file. > But if the host computer crashes in the middle of a COMMIT where two > or more database files are updated, some of those files might get the > changes where others might not." > Still, why would UNION behave differently from UNION ALL? That's the puzzling part IMHO. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Known reasons why sqlite3_open_v2 can take over 60s on windows?
Hi mailing list gurus! I will start with TL;DR version as this may be enough for some of you: * We are trying to investigate an issue that we see in diagnostic data of our C++ product. * The issue was pinpointed to be caused by timeout on `sqlite3_open_v2` which supposedly takes over 60s to complete (we only give it 60s). * We tried multiple different configurations, but never were able to reproduce even 5s delay on this call. So the question is if maybe there are some known scenarios in which `sqlite3_open_v2` can take that long (on windows)? Now to the details: * We are using version `3.10.2` of SQLite. We went through changelogs from this version till now and nothing we've found in bugfixes section seems to suggest that there was some issue that was addressed in consecutive sqlite releases and may have caused our problem. * The issue we see affects around 0.1% unique user across all supported versions of windows (Win 7, Win 8, Win 10). There are no manual user complains/reports about that - this can suggest that problem happens in the context where something serious enough is happening with user machine/system that he doesn't expect anything to work. So something that indicates system wide failure is a valid possibility as long as it can possibly happen for 0.1% of random windows users. * There are no data indicating that the same issue ever occurred on Mac which is also supported platform with large enough sample of diagnostic data. * We are using Poco (https://github.com/pocoproject/poco, version: 1.7.2) as a tool for accessing our SQLite database, but we've analyzed the Poco code and it seems that failure on this code level can only (possibly) explain ~1% of all collected samples. This is how we've determined that problem lies in `sqlite3_open_v2` taking long time. * This happens on both `DELETE` journal mode as well as on `WAL`. * It seems like after this problem happens first time for a particular user each consecutive call to `sqlite3_open_v2` takes that long until user restarts whole application (possibly machine, no way to tell from our data). * We are using following flags setup for `sqlite3_open_v2` (as in Poco): > sqlite3_open_v2(..., ..., SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_URI, NULL); * This usually doesn't happen on startup of the application so it's not likely to be caused by something happening while our application is not running. This includes power cuts offs causing data destruction (which tend to return SQLITE_CORRUPT anyway, as mentioned in: https://www.sqlite.org/howtocorrupt.html). * We were never able to reproduce this issue locally even though we tried different things: ** Multiple threads writing and reading from DB with synchronization required by particular journaling system. ** Keeping sqlite connection open for long time and working on db normally in a mean while. ** Trying to hit HDD hard with other data (dumping /dev/rand (WSL) to multiple files from different processes while accessing DB normally). ** Trying to force antivirus software to scan db on every file access (tested with Avast with basically everything enabled including "scan on open" and "scan on write"). ** Breaking our internal synchronization required by particular journaling systems. ** Calling WinAPI CreateFile with all possible combinations of file sharing options on db file - this caused issues but `sqlite3_open_v2` always returned fast - just with error. ** Calling WinAPI LockFile on random parts of DB file which is btw. nice way of reproducing `SQLITE_IOERR`, but no luck with reproducing the discussed issue. ** Some additional attempts to actually stretch Poco layer and double check if our static analysis of codes are right. * We've tried to look for similar issues online but anything somewhat relevant we've found was here http://sqlite.1065341.n5.nabble.com/sqlite3-open-v2-performance-degrades-as-number-of-opens-increase-td37482.html . This doesn't seem to explain our case though, as the numbers of parallel connections are way beyond what we have as well as what would typical windows user have (unless there is some somewhat popular app exploiting sqlite which we don't know about). Do you have any ideas what can cause that issue? Maybe some hints what else should we check or what additional diagnostic data that we can collect from users would be useful to pinpoint the real reason why that happens? Thanks in advance :) Andrzej 'Yester' Fiedukowicz ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Known reasons why sqlite3_open_v2 can take over 60s on windows?
On 7/31/18, Andrzej Fiedukowicz wrote: > * The issue was pinpointed to be caused by timeout on > `sqlite3_open_v2` which supposedly takes over 60s to complete (we only > give it 60s). Are you sure the time is happening on sqlite3_open_v2()? Because that routine just opens the file descriptors but never reads from the database files. Perhaps Poco is doing something else (like setting some PRAGMAs or something) after the sqlite3_open_v2() call? Anyhow - if the database needs recovery, the first thing that SQLite will do will run recovery. And if the database is large and the transaction(s) that are being recovered are large, and if your machine is slow, then I suppose that could take a long time. Even so, 60 milliseconds seems like a more reasonable number than 60 seconds. I don't know what is going on. I wish you could reproduce the problem. AV software is also notorious for causing problems on Windows machines. That might also be a problem. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Known reasons why sqlite3_open_v2 can take over 60s on windows?
On 2018/07/31 5:02 PM, Andrzej Fiedukowicz wrote: Hi mailing list gurus! I will start with TL;DR version as this may be enough for some of you: * We are trying to investigate an issue that we see in diagnostic data of our C++ product. * The issue was pinpointed to be caused by timeout on `sqlite3_open_v2` which supposedly takes over 60s to complete (we only give it 60s). * We tried multiple different configurations, but never were able to reproduce even 5s delay on this call. The only thing that comes to mind (others may know of more scenarios) is that there was a hot journal file that needed to be rolled back. If SQLite (or the host application) crashes in the midst of a transaction, there will remain a journal file, which depending on the Journal mode, will contain either the uncommitted or committed bits (I see you tried both WAL and DELETE, so I assume the difference doesn't matter). Rolling back a hot journal can take some time, granted, I haven't seen many >60s time-frames, but I have seen several seconds, which means it might be the case[*]. And if you limit the time to n where the journal rollback cannot complete by n time, then every time you start the program, you will probably have the hot journal again, and the rollback starting again, which could explain the phenomenon where once triggered, the time-out occurs constantly on re-starts - although, a computer re-start shouldn't fix this (unless perhaps a cache-holdup is responsible for slow IO before a restart or such, but that's just guesswork). Lastly, version 3.10 is ancient. You may well have researched that change-logs for fixes, but I don't think this is a bug in SQLite, hence not seeing any fixes. If the rollback is allowed enough time to complete, it should get fixed, that said, if you DID update SQLite, the simple fact that it runs probably around 50% faster than it used to back at 3.10[**] might already have saved your system (in that the time-out may have sufficed on a faster DB Engine). [*] - I'm also not 100% that the open_v2 thing itself does the detecting & rollback of hot journals, but I think so. [**] - I'm just ball-park guessing here, I think Richard posted some time-lines at some point with speed increases over versions, but I don't have it handy. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table
Thanks ! The problem was that lseek of fastfs behaves differently from the posix specs. If you do a fatfs lseek to a place beyond the actual file size and the file is opened for writing, he will automatically increase the file size which is not OK according to posix. (lseek is called by de read function to read at an offset) And the next time you do xFileSize() you will get a non zero result, resulting in the not a valid database file error. The strange thing is that even though the file size is increased in the SW structures maintained by fatfs, the larger file is never actually written to disk so i did not notice it when checking the file system. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy Sent: zaterdag 28 juli 2018 17:59 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table On 07/28/2018 10:51 PM, Bram Peeters wrote: > Ah maybe that is also interesting to know: i put a breakpoint on the write > convenience wrapper in sqlite, it is never called. > So it is not that there is a problem with the write function . > It is that there is never an attempt to write anything to the file before it > is being read... > Which is why i started looking at who might be responsible for writing > this, but it is a bit hard to debug why something does not happen if > you are not familiar with the 211k lines of code :) > Suggest checking the xFileSize() implementation. Is it setting the output variable to 0 when the file is 0 bytes in size on disk? I think if the file on disk is zero bytes in size, this bit: sqlite3PagerPagecount(pBt->pPager, ); should set nPageFile to 0. Not 1. Dan. > > Regards > Bram > > > From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on > behalf of Bram Peeters [bram.peet...@dekimo.com] > Sent: Saturday, July 28, 2018 17:45 > To: SQLite mailing list > Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during > create table > >> An empty file and a file filled with zeros are two different things > The file is empty/has size 0 in the file system. > > So the filesystem interface indeed returns SQLITE_IOERR_SHORT_READ. > > But the page1 pointer (this is not in the file, but in the structures managed > by sqlite) points to a buffer in memory with all 0's (probably cos it is > malloced somewhere and it does not get filled with contents of the file). > > Regards, > Bram > > > > > > > From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on > behalf of Clemens Ladisch [clem...@ladisch.de] > Sent: Saturday, July 28, 2018 15:07 > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during > create table > > Bram Peeters wrote: >> He reads a page from the file in sqlite3PagerSharedLock, but the file is >> still 0 so the page is all zeros. > > An empty file and a file filled with zeros are two different things. > > Does the file system return SQLITE_IOERR_SHORT_READ? > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma index_list without supplied table name valid SQL?
Yes, thanks, I did forget about that. Would the method with explain then be a good way to pick up that the pragma was unknown, so invalid? I suppose a simpler way might be to compare to all the pragma's produced by pragma_list and determine that pragma index_list is too short (missing the table). RBS On Wed, Aug 1, 2018 at 3:30 PM, David Raymond wrote: > Don't forget this point about pragmas: > > https://www.sqlite.org/pragma.html > "No error messages are generated if an unknown pragma is issued. Unknown > pragmas are simply ignored. This means if there is a typo in a pragma > statement the library does not inform the user of the fact." > > That way if there's a typo, or if you try a new pragma in an old version > then it won't complain, it just won't do anything. > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Wednesday, August 01, 2018 10:13 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Is pragma index_list without supplied table name valid > SQL? > > Using SQLite 3.22.0 > > In my app I have code to determine if a given SQL string is data-producing, > non-data producing or invalid. It uses these 3 SQLite functions: > > sqlite3_prepare_v3 > sqlite3_stmt_readonly > sqlite3_column_count > > Have been using this code for a few years and sofar never failed, but now > come across: > pragma index_list > so, without a supplied table name. > This gives me data-producing and I think it should give me invalid. > > Firstly is this SQL indeed invalid? > > Secondly, if it is I need to add some code to pick this up and was thinking > about using explain for that. Explain pragma index_list gives me: > > addr opcode p1 p2 p3 p4 p5 comment > --- > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > And that to me looks it is indeed an invalid SQL as it gives a Halt already > in the second row > and produces no further rows. > > Am I right here and would this be a good way to pick up invalid SQL? > > > RBS > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: foreign key mismatch - "loan" referencing "user"
Hi Clemens,
The table user stores info about all users.
But some users have administrator privileges and can register a loan or
a devolution in the system.
On the line:
FOREIGN KEY (id_admin_loan, id_admin_devolution) REFERENCES user (id_user,
id_user)
The id_admin_loan field is the id_user of a user who has adminstrator
privileges and registered a loan of a book.
The id_admin_devolution field is the id_user of a user with
administrator privilege and who registered the devolution of a book.
My intention is to register "who" did "what" in the system.
I followed your suggestion and separated the double constraint into two
separate single constraint:
FOREIGN KEY(id_admin_loan) REFERENCES user(id_user),
FOREIGN KEY(id_admin_devolution) REFERENCES user(id_user)
and worked fine. :-)
Thank you for the tip.
But I'm curious to understand why the original (double) restriction was
generating this error?
Because the other double constraint:
FOREIGN KEY(id_book, copy_number) REFERENCES copy(id_book, copy_number),
do not generate any error.
Thank you very much for your attention.
Markos
Markos wrote:
CREATE TABLE user (
id_user integer PRIMARY KEY,
...
CREATE TABLE loan (
...
FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES user(id_user,
id_user)
I do not understand what this is trying to accomplish.
Why not two single-column FK constraints?
Error: foreign key mismatch - "loan" referencing "user"
Because there is no unique index on (id_user,id_user).
Regards,
Clemens
Em 30-07-2018 20:42, Markos escreveu:
Hi,
I am a beginner in database and I'm developing a Database (with
Tcl/Tk) to control the loans of books in a reading room.
I created a table to register the users:
...
db1 eval {CREATE TABLE user (
id_user integer PRIMARY KEY,
name text NOT NULL,
...
id_admin integer NOT NULL
);}
Then create a table for the books:
db1 eval {CREATE TABLE book (
id_book integer PRIMARY KEY,
title text NOT NULL,
...
subject text NOT NULL,
UNIQUE (id_book, title));}
And another table for each copy of the books, because we can have
different copies of a book:
db1 eval {CREATE TABLE copy (
id_copy integer PRIMARY KEY,
id_book integer NOT NULL,
copy_number integer NOT NULL,
...
UNIQUE (id_book, copy_number),
FOREIGN KEY(id_book) REFERENCES book(id_book)
);}
And finally a table to register the loans with many FOREIGN KEYS:
db1 eval {CREATE TABLE loan (
id_loan integer PRIMARY KEY,
id_user integer NOT NULL,
id_book integer NOT NULL,
copy_number integer NOT NULL,
loan_date integer NOT NULL,
devolution_date integer NOT NULL,
...
UNIQUE (id_user, id_book, copy_number,
loan_date),
FOREIGN KEY(id_book, copy_number)
REFERENCES copy(id_book, copy_number),
FOREIGN KEY(id_user) REFERENCES user(id_user),
FOREIGN KEY(id_admin_loan,
id_admin_devolution) REFERENCES user(id_user, id_user));}
But the command to insert a new loan return an error.
The command:
db1 eval "INSERT INTO loan (id_user, id_book, copy_number, loan_date,
devolution_date, ...) VALUES (1, 2, 1, 1532919600, 1514772000, ...)"
The error:
Error: foreign key mismatch - "loan" referencing "user"
foreign key mismatch - "loan" referencing "user"
foreign key mismatch - "loan" referencing "user"
while executing
"db1 eval "INSERT INTO $table_name ($field_list) VALUES ($value_list)""
The insert command works well for insertion into the user, book, and
copy tables.
I'm just having error to insert into the loan table.
I can't find the reason for the error.
Any tip?
Thank you,
Markos
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
On Aug 1, 2018, at 3:57 PM, Tim Streater wrote: > > On Aug 1, 2018, at 1:52 PM, Tim Streater wrote: >>> >>> I don't use the pragma since, officially, they are unsupported. >> > Specific pragma statements may be removed and others added in future releases > of SQLite. There is no guarantee of backwards compatibility. CREATE TABLE version ( schema INTEGER NOT NULL ); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error: foreign key mismatch - "loan" referencing "user"
Because the required unique index on copy(id_book, copy_number) exists (in the
table definition).
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Markos
>Sent: Wednesday, 1 August, 2018 16:23
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Error: foreign key mismatch - "loan"
>referencing "user"
>
>Hi Clemens,
>
>The table user stores info about all users.
>
>But some users have administrator privileges and can register a loan
>or
>a devolution in the system.
>
>On the line:
>> FOREIGN KEY (id_admin_loan, id_admin_devolution) REFERENCES user
>(id_user, id_user)
>
>The id_admin_loan field is the id_user of a user who has adminstrator
>privileges and registered a loan of a book.
>
>The id_admin_devolution field is the id_user of a user with
>administrator privilege and who registered the devolution of a book.
>
>My intention is to register "who" did "what" in the system.
>
>I followed your suggestion and separated the double constraint into
>two
>separate single constraint:
>
>FOREIGN KEY(id_admin_loan) REFERENCES user(id_user),
>
>FOREIGN KEY(id_admin_devolution) REFERENCES user(id_user)
>
>and worked fine. :-)
>
>Thank you for the tip.
>
>But I'm curious to understand why the original (double) restriction
>was
>generating this error?
>
>Because the other double constraint:
>
>FOREIGN KEY(id_book, copy_number) REFERENCES copy(id_book,
>copy_number),
>
>do not generate any error.
>
>Thank you very much for your attention.
>
>Markos
>
>
>
>Markos wrote:
>
>> CREATE TABLE user (
>> id_user integer PRIMARY KEY,
>> ...
>> CREATE TABLE loan (
>> ...
>> FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES
>user(id_user, id_user)
>
>I do not understand what this is trying to accomplish.
>Why not two single-column FK constraints?
>
>> Error: foreign key mismatch - "loan" referencing "user"
>
>Because there is no unique index on (id_user,id_user).
>
>
>Regards,
>Clemens
>
>
>Em 30-07-2018 20:42, Markos escreveu:
>> Hi,
>>
>> I am a beginner in database and I'm developing a Database (with
>> Tcl/Tk) to control the loans of books in a reading room.
>>
>> I created a table to register the users:
>>
>> ...
>>
>> db1 eval {CREATE TABLE user (
>> id_user integer PRIMARY KEY,
>> name text NOT NULL,
>>
>> ...
>>
>> id_admin integer NOT NULL
>> );}
>>
>>
>> Then create a table for the books:
>>
>> db1 eval {CREATE TABLE book (
>> id_book integer PRIMARY KEY,
>> title text NOT NULL,
>>
>> ...
>> subject text NOT NULL,
>> UNIQUE (id_book, title));}
>>
>>
>> And another table for each copy of the books, because we can have
>> different copies of a book:
>>
>> db1 eval {CREATE TABLE copy (
>> id_copy integer PRIMARY KEY,
>> id_book integer NOT NULL,
>> copy_number integer NOT NULL,
>> ...
>> UNIQUE (id_book, copy_number),
>> FOREIGN KEY(id_book) REFERENCES
>book(id_book)
>> );}
>>
>>
>> And finally a table to register the loans with many FOREIGN KEYS:
>>
>> db1 eval {CREATE TABLE loan (
>> id_loan integer PRIMARY KEY,
>> id_user integer NOT NULL,
>> id_book integer NOT NULL,
>> copy_number integer NOT NULL,
>> loan_date integer NOT NULL,
>> devolution_date integer NOT NULL,
>> ...
>> UNIQUE (id_user, id_book, copy_number,
>> loan_date),
>> FOREIGN KEY(id_book, copy_number)
>> REFERENCES copy(id_book, copy_number),
>> FOREIGN KEY(id_user) REFERENCES
>user(id_user),
>> FOREIGN KEY(id_admin_loan,
>> id_admin_devolution) REFERENCES user(id_user, id_user));}
>>
>> But the command to insert a new loan return an error.
>>
>> The command:
>>
>> db1 eval "INSERT INTO loan (id_user, id_book, copy_number,
>loan_date,
>> devolution_date, ...) VALUES (1, 2, 1, 1532919600, 1514772000,
>...)"
>>
>> The error:
>>
>> Error: foreign key mismatch - "loan" referencing "user"
>>
>> foreign key mismatch - "loan" referencing "user"
>> foreign key mismatch - "loan" referencing "user"
>> while executing
>> "db1 eval "INSERT INTO $table_name ($field_list) VALUES
>($value_list)""
>>
>> The insert command works well for insertion into the user, book,
>and
>> copy tables.
>>
>> I'm just
[sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all
The attached testcase injects the foreign key violation into a long
transaction. This makes the remainder of the transaction much slower,
even though the foreign key is deferred, and should only be checked in
the end of the transaction.
While working on this testcase, I found that sometimes the foreign key
violation doesn't trigger the error at all. Please change VIOLATION to
0, and observe that there is no failure now, though it should be.
sqlite3-3.24.0 on FreeBSD 11.2
Yuri
---testcase---
#include
#include
#include
#include
void doSql(sqlite3 *db, const char *sql) {
char *err_msg = 0;
int rc = sqlite3_exec(db, sql, 0, 0, _msg);
if (rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", err_msg);
sqlite3_free(err_msg);
sqlite3_close(db);
exit(1);
}
}
#define NROWS 10
#define VIOLATION 100
int main(void) {
sqlite3 *db;
char s[512];
int rc = sqlite3_open(":memory:", );
if (rc != SQLITE_OK) {
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
doSql(db, "PRAGMA foreign_keys = ON;");
printf("creating B ...\n");
doSql(db, "create table b (id int PRIMARY KEY, name text)");
printf("populating B ...\n");
for (int i = 0; i < NROWS; i++) {
sprintf(s, "insert into b values(%d, 'The name field for %d')", i, i);
doSql(db, s);
}
printf("creating A ...\n");
doSql(db, "create table a (id int PRIMARY KEY, name text, aid int not
null, FOREIGN KEY(aid) REFERENCES a(id))");
printf("populating A ...\n");
doSql(db, "BEGIN TRANSACTION;");
doSql(db, "PRAGMA defer_foreign_keys=ON;");
for (int i = 0; i < NROWS; i++) {
if (i % 1000 == 0)
printf("...row#%d... (time=%ld)\n", i, time(0));
sprintf(s, "insert into a values(%d, 'The name field for %d', %d)",
i, i, i);
doSql(db, s);
// introfuce the FK violation
if (i == NROWS/4) {
sprintf(s, "insert into a values(%d, 'The name field for %d',
%d)", NROWS+i, i, NROWS+i+VIOLATION);
doSql(db, s);
}
}
doSql(db, "COMMIT TRANSACTION;");
sqlite3_close(db);
return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
On 2018/08/01 5:56 PM, Bart Smissaert wrote:
May I ask about your use-case and what specifically is needed?
Probably exactly the same as you are using in your SQLitespeed app.
There is a SQL text box and the user can type anything in there he/she
wants.
App then needs to determine how to handle that string:
Produce data to show, run a non-data producing SQL, or reject it because it
is invalid.
Ah, well, since you already know SQLitespeed does it, I'll confess how
it's done. Firstly the API is really helpful in both determining if a
Keyword is valid, and in counting the valid Keywords - making parsing
easy. You can see here: https://sqlite.org/c3ref/keyword_check.html
Secondly, I confess, we actually maintain a list of pragmas inside the
code to verify against, and we update this list on every major update.
To confess more - We actually keep an entire list of all sqlite keywords
and for all the major SQL phrases/pragmas/etc. direct help links to the
sqlite online documentation and of course the code hinting and
highlighting needs it. (It's a little harder to maintain than a simple
"check_keyword()" api, but so much more friendly and the tool isn't as
sensitive to code-bloat as the sqlite engine).
Last confession, every table-data-producing pragma has a
table-valued-function alternative in the form "pragma_xxx" where a
pragma that can be called like this:
PRAGMA table_info(MyTable);
can also be called like this via said t.v.f:
SELECT * FROM pragma_table_info('MyTable');
And THAT will error out if it doesn't exist, or is misused, right upon
prepare - no guessing.
You'll have to still keep a list to know which pragmas are
data-producing and which not, some can be used both ways, so a 2-list
approach works better. Of course, once you maintain a list of valid
Pragmas, the quest for a way to know which are valid, becomes somewhat moot.
PS: SQLitespeed hasn't seen an update for a while, but the final
adjustments and testing is ongoing now for the newest release due later
this Month. The SQLitespeed community has been alpha testing and pencils
down for beta starting in about a week. If anyone not on the list would
like to join testing, please mail me off-list, otherwise we'll share
release details later in August.
Most notable new addition: Schema-testing to warn about misspelled type
names, unintentional errors, using Integer FK on a Text parent column,
and all kinds of similar mishaps we know of thanks to people posting to
this list - so thank you all for that.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
On Aug 1, 2018, at 1:52 PM, Tim Streater wrote: > > I don't use the pragma since, officially, they are unsupported. “Unsupported” how? It’s documented and part of the SQLite file header, which is quite stable. If you mean this is not standard SQL and thus doesn’t work on non-SQLite DBs, then just do what we do, since we didn’t start out on SQLite: keep the schema version number in a separate file. You probably have some kind of preference file, or INI file, or registry key, or whatever associated with this software. Put it there if you don’t want to give it to SQLite to manage. > But I'd rather have the IF NOT EXISTS. That sounds wasteful. 99+% of the time, the column will exist, because it was created the first time the program was run after being upgraded. Imagine how many redundant SQL statements you’ll have to run on program startup a dozen years hence when you have maybe a hundred schema changes. I’ll stick by my advice: this is an upgrade-time operation only. After the software’s been successfully upgraded, the schema is stable and implicitly trustworthy, always. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
On 01 Aug 2018, at 21:06, Warren Young wrote: > On Aug 1, 2018, at 1:52 PM, Tim Streater wrote: >> >> I don't use the pragma since, officially, they are unsupported. > > “Unsupported” how? It’s documented and part of the SQLite file header, which > is quite stable. > > If you mean this is not standard SQL and thus doesn’t work on non-SQLite DBs, > then just do what we do, ... No, I mean this, from https://www.sqlite.org/pragma.html: Specific pragma statements may be removed and others added in future releases of SQLite. There is no guarantee of backwards compatibility. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
On Aug 1, 2018, at 7:34 AM, Simon White wrote: > > I would like to suggest the addition of the "If not exists" to the Add Column > feature of SQLite. I maintain an application that’s been through dozens of schema changes over its nearly quarter century of life, so let me tell you what works for us: a DB schema serial number. Any time the DB schema changes, we bump the schema version number and modify a small program we include with the software that upgrades the schema. Each schema change is contained in a single function within this program, most of which are just a simple CREATE or ALTER TABLE statement. A few are more complex, moving data around or transforming it. If you upgrade the software on a machine running DB schema 5 to with software that requires DB schema 8, there are 3 steps that, if performed in order, always result in you running DB schema 8. This program simply calls those three functions in sequence based on the old schema number and the current schema number. We’ve so rarely needed to roll back to older schema versions that we’ve done it by hand. If this happens to you often, you could code an inverse for each upgrade step that lets you roll back each change. We started out with a simple integer version number, starting with 1, but once we started having multiple major versions in the wild with parallel development on each major version branch, we’d occasionally have to upgrade the DB schema in an older major version in cases where upgrading to the current major version wasn’t possible. That caused us to modify the DB schema version numbering scheme: 800 # first DB schema for software major version 8 801 802 etc. 900 # software version 9 901 etc. In this system, we can say that schema 802 and 901 do the same thing for the 800 and 900 series, respectively, so that if a system is upgraded from 802 to 902 in a single step, the upgrade program knows to skip the step done in 901, since it was already done in 802. That’s as close as we’ve come to ever needing a fully-general tree-structured DB schema versioning system. We’ve never come close to defining over a hundred schema versions, but if you think you will, then it’s easily handled by adding a digit. If you’re defining over a thousand DB schema versions between major software versions, you probably don’t understand change control. :) Whenever we upgrade the software, the installer/package for the target runs a post-installation script that runs this DB schema upgrading program. (e.g. The %post script in RPM, InstallFinalize in WIX/MSI, etc.) If the schema upgrading program runs to completion successfully, it updates the schema version number and returns a success code, which tells the calling script that it can restart the software. Now the neat bit: SQLite already supports doing this with its user_version feature: https://sqlite.org/pragma.html#pragma_user_version ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add Column with "If Not Exists"
On 01 Aug 2018, at 20:40, Warren Young wrote: > On Aug 1, 2018, at 7:34 AM, Simon White > wrote: >> >> I would like to suggest the addition of the "If not exists" to the Add >> Column feature of SQLite. > > I maintain an application that’s been through dozens of schema changes over > its nearly quarter century of life, so let me tell you what works for us: a DB > schema serial number. > > Any time the DB schema changes, we bump the schema version number and modify a > small program we include with the software that upgrades the schema. > > Each schema change is contained in a single function within this program, most > of which are just a simple CREATE or ALTER TABLE statement. A few are more > complex, moving data around or transforming it. > > If you upgrade the software on a machine running DB schema 5 to with software > that requires DB schema 8, there are 3 steps that, if performed in order, > always result in you running DB schema 8. This program simply calls those > three functions in sequence based on the old schema number and the current > schema number. [snip] > Now the neat bit: SQLite already supports doing this with its user_version > feature: > > https://sqlite.org/pragma.html#pragma_user_version Yeah, I already also do all of this, although I don't use the pragma since, officially, they are unsupported. But I'd rather have the IF NOT EXISTS. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
