Is pragma index_list without supplied table name valid SQL?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Is pragma index_list without supplied table name valid SQL?

Bart Smissaert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is pragma index_list without supplied table name valid SQL?

David Raymond
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

Hick Gunter
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         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Integer        0     1     0                    00  NULL
1     String8        0     2     0     test_i         00  NULL
2     Integer        0     3     0                    00  NULL
3     ResultRow      1     3     0                    00  NULL
4     Halt           0     0     0                    00  NULL

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von David Raymond
Gesendet: Mittwoch, 01. August 2018 16:31
An: SQLite mailing list <[hidden email]>
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is pragma index_list without supplied table name valid SQL?

Bart Smissaert
In reply to this post by David Raymond
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 <[hidden email]>
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:[hidden email]]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

Bart Smissaert
In reply to this post by Hick Gunter
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 <[hidden email]> 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         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Integer        0     1     0                    00  NULL
> 1     String8        0     2     0     test_i         00  NULL
> 2     Integer        0     3     0                    00  NULL
> 3     ResultRow      1     3     0                    00  NULL
> 4     Halt           0     0     0                    00  NULL
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von David Raymond
> Gesendet: Mittwoch, 01. August 2018 16:31
> An: SQLite mailing list <[hidden email]>
> 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:[hidden email]]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

David Raymond
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         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Halt           0     0     0                    00

sqlite> explain pragma index_list();--with parenthesis but no table name
Error: near ")": syntax error

sqlite> explain pragma index_list(missingTable);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Halt           0     0     0                    00

sqlite> create table noIndexes (a int);

sqlite> explain pragma index_list(noIndexes);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     2     0                    00  Start at 2
1     Halt           0     0     0                    00
2     Transaction    0     0     1     0              01  usesStmtJournal=0
3     Goto           0     1     0                    00

sqlite> create table withIndexes (a text primary key, b unique);

sqlite> explain pragma index_list(withIndexes);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     14    0                    00  Start at 14
1     Integer        0     1     0                    00  r[1]=0
2     String8        0     2     0     sqlite_autoindex_withIndexes_2  00  r[2]='sqlite_autoindex_withIndexes_2'
3     Integer        1     3     0                    00  r[3]=1
4     String8        0     4     0     u              00  r[4]='u'
5     Integer        0     5     0                    00  r[5]=0
6     ResultRow      1     5     0                    00  output=r[1..5]
7     Integer        1     1     0                    00  r[1]=1
8     String8        0     2     0     sqlite_autoindex_withIndexes_1  00  r[2]='sqlite_autoindex_withIndexes_1'
9     Integer        1     3     0                    00  r[3]=1
10    String8        0     4     0     pk             00  r[4]='pk'
11    Integer        0     5     0                    00  r[5]=0
12    ResultRow      1     5     0                    00  output=r[1..5]
13    Halt           0     0     0                    00
14    Transaction    0     0     2     0              01  usesStmtJournal=0
15    Goto           0     1     0                    00

sqlite> explain pragma thisIsABadPragmaName;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Halt           0     0     0                    00

sqlite> explain pragma foreign_keys;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Int64          0     1     0     1              00  r[1]=1
2     ResultRow      1     1     0                    00  output=r[1]
3     Halt           0     0     0                    00

sqlite> explain pragma cache_spill;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Int64          0     1     0     15857          00  r[1]=15857
2     ResultRow      1     1     0                    00  output=r[1]
3     Halt           0     0     0                    00

sqlite> explain pragma case_sensitive_like;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Halt           0     0     0                    00

sqlite> explain pragma case_sesitive_like = 1;--typo
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Halt           0     0     0                    00

sqlite> explain pragma case_sensitive_like = 1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Halt           0     0     0                    00

sqlite> pragma case_sensitive_like = 1;

sqlite> select 'a' like 'A';
'a' like 'A'
0

sqlite> explain pragma case_sensitive_like = 0;
addr  opcode         p1    p2    p3    p4             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:[hidden email]] 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 <[hidden email]> 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         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Integer        0     1     0                    00  NULL
> 1     String8        0     2     0     test_i         00  NULL
> 2     Integer        0     3     0                    00  NULL
> 3     ResultRow      1     3     0                    00  NULL
> 4     Halt           0     0     0                    00  NULL
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von David Raymond
> Gesendet: Mittwoch, 01. August 2018 16:31
> An: SQLite mailing list <[hidden email]>
> 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:[hidden email]]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

Bart Smissaert
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 <[hidden email]>
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         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     1     0                    00  Start at 1
> 1     Halt           0     0     0                    00
>
> sqlite> explain pragma index_list();--with parenthesis but no table name
> Error: near ")": syntax error
>
> sqlite> explain pragma index_list(missingTable);
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     1     0                    00  Start at 1
> 1     Halt           0     0     0                    00
>
> sqlite> create table noIndexes (a int);
>
> sqlite> explain pragma index_list(noIndexes);
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     2     0                    00  Start at 2
> 1     Halt           0     0     0                    00
> 2     Transaction    0     0     1     0              01  usesStmtJournal=0
> 3     Goto           0     1     0                    00
>
> sqlite> create table withIndexes (a text primary key, b unique);
>
> sqlite> explain pragma index_list(withIndexes);
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     14    0                    00  Start at 14
> 1     Integer        0     1     0                    00  r[1]=0
> 2     String8        0     2     0     sqlite_autoindex_withIndexes_2  00
> r[2]='sqlite_autoindex_withIndexes_2'
> 3     Integer        1     3     0                    00  r[3]=1
> 4     String8        0     4     0     u              00  r[4]='u'
> 5     Integer        0     5     0                    00  r[5]=0
> 6     ResultRow      1     5     0                    00  output=r[1..5]
> 7     Integer        1     1     0                    00  r[1]=1
> 8     String8        0     2     0     sqlite_autoindex_withIndexes_1  00
> r[2]='sqlite_autoindex_withIndexes_1'
> 9     Integer        1     3     0                    00  r[3]=1
> 10    String8        0     4     0     pk             00  r[4]='pk'
> 11    Integer        0     5     0                    00  r[5]=0
> 12    ResultRow      1     5     0                    00  output=r[1..5]
> 13    Halt           0     0     0                    00
> 14    Transaction    0     0     2     0              01  usesStmtJournal=0
> 15    Goto           0     1     0                    00
>
> sqlite> explain pragma thisIsABadPragmaName;
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     1     0                    00  Start at 1
> 1     Halt           0     0     0                    00
>
> sqlite> explain pragma foreign_keys;
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     1     0                    00  Start at 1
> 1     Int64          0     1     0     1              00  r[1]=1
> 2     ResultRow      1     1     0                    00  output=r[1]
> 3     Halt           0     0     0                    00
>
> sqlite> explain pragma cache_spill;
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     1     0                    00  Start at 1
> 1     Int64          0     1     0     15857          00  r[1]=15857
> 2     ResultRow      1     1     0                    00  output=r[1]
> 3     Halt           0     0     0                    00
>
> sqlite> explain pragma case_sensitive_like;
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     1     0                    00  Start at 1
> 1     Halt           0     0     0                    00
>
> sqlite> explain pragma case_sesitive_like = 1;--typo
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     1     0                    00  Start at 1
> 1     Halt           0     0     0                    00
>
> sqlite> explain pragma case_sensitive_like = 1;
> addr  opcode         p1    p2    p3    p4             p5  comment
> ----  -------------  ----  ----  ----  -------------  --  -------------
> 0     Init           0     1     0                    00  Start at 1
> 1     Halt           0     0     0                    00
>
> sqlite> pragma case_sensitive_like = 1;
>
> sqlite> select 'a' like 'A';
> 'a' like 'A'
> 0
>
> sqlite> explain pragma case_sensitive_like = 0;
> addr  opcode         p1    p2    p3    p4             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:[hidden email]]
> 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 <[hidden email]> 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         p1    p2    p3    p4             p5  comment
> > ----  -------------  ----  ----  ----  -------------  --  -------------
> > 0     Integer        0     1     0                    00  NULL
> > 1     String8        0     2     0     test_i         00  NULL
> > 2     Integer        0     3     0                    00  NULL
> > 3     ResultRow      1     3     0                    00  NULL
> > 4     Halt           0     0     0                    00  NULL
> >
> > -----Ursprüngliche Nachricht-----
> > Von: sqlite-users [mailto:[hidden email]]
> > Im Auftrag von David Raymond
> > Gesendet: Mittwoch, 01. August 2018 16:31
> > An: SQLite mailing list <[hidden email]>
> > 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:[hidden email]]
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

R Smith-2
In reply to this post by Bart Smissaert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

Bart Smissaert
> 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

David Raymond
Use the CLI code as an example and see how they do it?



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

Bart Smissaert
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 <[hidden email]>
wrote:

> Use the CLI code as an example and see how they do it?
>
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> 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 <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

R Smith-2
In reply to this post by Bart Smissaert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Is pragma index_list without supplied table name valid SQL?

Bart Smissaert
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users