Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
I didn't say I knew how you to do it in SQLitespeed, I said you your
use-case was very likely the same as mine.
I did notice the new keyword API and will move to 3.24 so I can use that.
Will look further into using explain to solve this problem, but guess the
output of explain may change in future versions,
so maybe can't rely on that.
RBS
On Wed, Aug 1, 2018 at 8:27 PM, R Smith wrote:
> 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
>
___
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] [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
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?
> 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
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?
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 000 Start at 1 > 1 Halt 0 0 000 > > sqlite> pragma case_sensitive_like = 1; > > sqlite> select 'a' like 'A'; > 'a' like 'A' > 0 > > sqlite> explain pragma case_sensitive_like = 0; > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Init 0 1 0 00 Start at 1 > 1 Halt 0 0 0 00 > > sqlite> pragma case_sensitive_like = 0; > > sqlite> select 'a' like 'A'; > 'a' like 'A' > 1 > > sqlite> > > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Wednesday, August 01, 2018 10:50 AM > To: SQLite mailing list > Subject: 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 Integer 0 1 0 00 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 m
Re: [sqlite] [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?
--- - -- - 0 Init 0 1 000 Start at 1 1 Halt 0 0 000 sqlite> pragma case_sensitive_like = 1; sqlite> select 'a' like 'A'; 'a' like 'A' 0 sqlite> explain pragma case_sensitive_like = 0; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 1 000 Start at 1 1 Halt 0 0 000 sqlite> pragma case_sensitive_like = 0; sqlite> select 'a' like 'A'; 'a' like 'A' 1 sqlite> -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert Sent: Wednesday, August 01, 2018 10:50 AM To: SQLite mailing list Subject: 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 I
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?
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
