Re: [sqlite] Retrieving constraint name

[Keith Medcalf]

After stripping out comments and so forth of course ...


---
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 petern
>Sent: Sunday, 10 December, 2017 22:37
>To: SQLite mailing list
>Subject: Re: [sqlite] Retrieving constraint name
>
>Igor/Cezary,
>
>It is remarkable how 'struct Parse' already contains the constraint
>name as
>Cezary pointed out.
>->  Token constraintName;/* Name of the constraint currently being
>parsed */
>But is not included in the 'struct FKey' linked list node that is
>reeled in
>to produce columns in the PRAGMA report.
>For the official release, presumably, test cases would have to be
>added in
>addition to simply hooking it up as suggested.
>
>In the meantime, parsing wouldn't be difficult even with primitive
>built-in
>SQL string functions.  Consider how the constraint name must occur
>within
>the comma delimited part of the well formed CREATE TABLE statement.
>When
>obtained from sqlite_master, the statement is guaranteed to be well
>formed.  Simply examine each comma delimited candidate part.  If
>present,
>the first word between keyword CONSTRAINT and keyword REFERENCES is
>the
>constraint name.
>
>Peter
>
>
>
>
>
>
>
>
>
>On Sun, Dec 10, 2017 at 7:29 PM, Igor Korot 
>wrote:
>
>> Hi,
>>
>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta
>
>> wrote:
>> > Hello,
>> >
>> > On 2017-12-11 01:04, Igor Korot wrote:
>> >
>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta
>
>> >> wrote:
>> >
>> >
>> >>> On 2017-12-10 07:21, Igor Korot wrote:
>> >
>> >
>>  The CREATE TABLE statement supports the following syntax:
>> 
>>  CREATE TABLE( , CONSTRAINT  FOREIGN
>>  KEY() REFERENCES (ref_column_list>);
>> >
>> >
>>  [...] If not - does this mean that the only way to get the
>name is to
>>  parse the
>>  sql
>>  from sqlite_master? Or there is a better way?
>> >
>> >
>> >>> The answer is ``not''. Constraint names are ignored and
>disappearing
>> >>> without
>> >>> a trace except for ``CHECK'' constraint (the name is used to
>build an
>> >>> error
>> >>> message). Unparsed ``sql'' column of ``sqlite_master'' is the
>sole
>> place
>> >>> which contains an indirect info about ``FOREIGN KEY''
>constraint's
>> name.
>> >
>> >
>> >> Thank you for confirming.
>> >
>> > You are welcome. BTW, SQLite parses SQL every time it creates a
>table
>> > (by a SQL command or after an opening of BTree file) -- I believe
>there
>> > is no better way. You do not need to parse SQL on your own (it is
>hard,
>> > if not impossible, to establish a link between a name and a
>particular
>> > constraint). All you need is to append ``char *'' field to
>``struct
>> > FKey'' and to inject a function
>``build.c:sqlite3CreateForeignKey()'':
>> > ``pParse->constraintName'' will contain the constraint's name
>(note
>> > that the name is not dequoted -- you will have to dequote it;
>look at
>> > ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of
>a
>> > constraint's name is done). This will allow you to build your own
>map of
>> > ``FOREIGN KEY'' names. For example, if you want to expand
>``PRAGMA
>> > foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
>> > PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.
>>
>> Thank you, but I need to keep the official SQLite code.
>>
>>
>> >
>> >
>> > -- best regards
>> >
>> > Cezary H. Noweta
>> > ___
>> > 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] Retrieving constraint name

[Igor Korot]

Hi,

On Sun, Dec 10, 2017 at 11:36 PM, petern  wrote:
> Igor/Cezary,
>
> It is remarkable how 'struct Parse' already contains the constraint name as
> Cezary pointed out.
> ->  Token constraintName;/* Name of the constraint currently being parsed */
> But is not included in the 'struct FKey' linked list node that is reeled in
> to produce columns in the PRAGMA report.
> For the official release, presumably, test cases would have to be added in
> addition to simply hooking it up as suggested.

Now I'm curious if there is a plan to include the foreign key name in that
PRAGMA output. It looks like everything is in place for that.

Mr. Hipp?

>
> In the meantime, parsing wouldn't be difficult even with primitive built-in
> SQL string functions.  Consider how the constraint name must occur within
> the comma delimited part of the well formed CREATE TABLE statement.  When
> obtained from sqlite_master, the statement is guaranteed to be well
> formed.  Simply examine each comma delimited candidate part.  If present,
> the first word between keyword CONSTRAINT and keyword REFERENCES is the
> constraint name.

Yes, I may try to do that in the meantime.

Thank you.

>
> Peter
>
>
>
>
>
>
>
>
>
> On Sun, Dec 10, 2017 at 7:29 PM, Igor Korot  wrote:
>
>> Hi,
>>
>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta 
>> wrote:
>> > Hello,
>> >
>> > On 2017-12-11 01:04, Igor Korot wrote:
>> >
>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta 
>> >> wrote:
>> >
>> >
>> >>> On 2017-12-10 07:21, Igor Korot wrote:
>> >
>> >
>>  The CREATE TABLE statement supports the following syntax:
>> 
>>  CREATE TABLE( , CONSTRAINT  FOREIGN
>>  KEY() REFERENCES (ref_column_list>);
>> >
>> >
>>  [...] If not - does this mean that the only way to get the name is to
>>  parse the
>>  sql
>>  from sqlite_master? Or there is a better way?
>> >
>> >
>> >>> The answer is ``not''. Constraint names are ignored and disappearing
>> >>> without
>> >>> a trace except for ``CHECK'' constraint (the name is used to build an
>> >>> error
>> >>> message). Unparsed ``sql'' column of ``sqlite_master'' is the sole
>> place
>> >>> which contains an indirect info about ``FOREIGN KEY'' constraint's
>> name.
>> >
>> >
>> >> Thank you for confirming.
>> >
>> > You are welcome. BTW, SQLite parses SQL every time it creates a table
>> > (by a SQL command or after an opening of BTree file) -- I believe there
>> > is no better way. You do not need to parse SQL on your own (it is hard,
>> > if not impossible, to establish a link between a name and a particular
>> > constraint). All you need is to append ``char *'' field to ``struct
>> > FKey'' and to inject a function ``build.c:sqlite3CreateForeignKey()'':
>> > ``pParse->constraintName'' will contain the constraint's name (note
>> > that the name is not dequoted -- you will have to dequote it; look at
>> > ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of a
>> > constraint's name is done). This will allow you to build your own map of
>> > ``FOREIGN KEY'' names. For example, if you want to expand ``PRAGMA
>> > foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
>> > PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.
>>
>> Thank you, but I need to keep the official SQLite code.
>>
>>
>> >
>> >
>> > -- best regards
>> >
>> > Cezary H. Noweta
>> > ___
>> > 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] Retrieving constraint name

[petern]

Igor/Cezary,

It is remarkable how 'struct Parse' already contains the constraint name as
Cezary pointed out.
->  Token constraintName;/* Name of the constraint currently being parsed */
But is not included in the 'struct FKey' linked list node that is reeled in
to produce columns in the PRAGMA report.
For the official release, presumably, test cases would have to be added in
addition to simply hooking it up as suggested.

In the meantime, parsing wouldn't be difficult even with primitive built-in
SQL string functions.  Consider how the constraint name must occur within
the comma delimited part of the well formed CREATE TABLE statement.  When
obtained from sqlite_master, the statement is guaranteed to be well
formed.  Simply examine each comma delimited candidate part.  If present,
the first word between keyword CONSTRAINT and keyword REFERENCES is the
constraint name.

Peter









On Sun, Dec 10, 2017 at 7:29 PM, Igor Korot  wrote:

> Hi,
>
> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta 
> wrote:
> > Hello,
> >
> > On 2017-12-11 01:04, Igor Korot wrote:
> >
> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta 
> >> wrote:
> >
> >
> >>> On 2017-12-10 07:21, Igor Korot wrote:
> >
> >
>  The CREATE TABLE statement supports the following syntax:
> 
>  CREATE TABLE( , CONSTRAINT  FOREIGN
>  KEY() REFERENCES (ref_column_list>);
> >
> >
>  [...] If not - does this mean that the only way to get the name is to
>  parse the
>  sql
>  from sqlite_master? Or there is a better way?
> >
> >
> >>> The answer is ``not''. Constraint names are ignored and disappearing
> >>> without
> >>> a trace except for ``CHECK'' constraint (the name is used to build an
> >>> error
> >>> message). Unparsed ``sql'' column of ``sqlite_master'' is the sole
> place
> >>> which contains an indirect info about ``FOREIGN KEY'' constraint's
> name.
> >
> >
> >> Thank you for confirming.
> >
> > You are welcome. BTW, SQLite parses SQL every time it creates a table
> > (by a SQL command or after an opening of BTree file) -- I believe there
> > is no better way. You do not need to parse SQL on your own (it is hard,
> > if not impossible, to establish a link between a name and a particular
> > constraint). All you need is to append ``char *'' field to ``struct
> > FKey'' and to inject a function ``build.c:sqlite3CreateForeignKey()'':
> > ``pParse->constraintName'' will contain the constraint's name (note
> > that the name is not dequoted -- you will have to dequote it; look at
> > ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of a
> > constraint's name is done). This will allow you to build your own map of
> > ``FOREIGN KEY'' names. For example, if you want to expand ``PRAGMA
> > foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
> > PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.
>
> Thank you, but I need to keep the official SQLite code.
>
>
> >
> >
> > -- best regards
> >
> > Cezary H. Noweta
> > ___
> > 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] Retrieving constraint name

[Igor Korot]

Hi,

On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta  wrote:
> Hello,
>
> On 2017-12-11 01:04, Igor Korot wrote:
>
>> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta 
>> wrote:
>
>
>>> On 2017-12-10 07:21, Igor Korot wrote:
>
>
 The CREATE TABLE statement supports the following syntax:

 CREATE TABLE( , CONSTRAINT  FOREIGN
 KEY() REFERENCES (ref_column_list>);
>
>
 [...] If not - does this mean that the only way to get the name is to
 parse the
 sql
 from sqlite_master? Or there is a better way?
>
>
>>> The answer is ``not''. Constraint names are ignored and disappearing
>>> without
>>> a trace except for ``CHECK'' constraint (the name is used to build an
>>> error
>>> message). Unparsed ``sql'' column of ``sqlite_master'' is the sole place
>>> which contains an indirect info about ``FOREIGN KEY'' constraint's name.
>
>
>> Thank you for confirming.
>
> You are welcome. BTW, SQLite parses SQL every time it creates a table
> (by a SQL command or after an opening of BTree file) -- I believe there
> is no better way. You do not need to parse SQL on your own (it is hard,
> if not impossible, to establish a link between a name and a particular
> constraint). All you need is to append ``char *'' field to ``struct
> FKey'' and to inject a function ``build.c:sqlite3CreateForeignKey()'':
> ``pParse->constraintName'' will contain the constraint's name (note
> that the name is not dequoted -- you will have to dequote it; look at
> ``build.c:sqlite3AddCheckConstraint()'' to know how assigning of a
> constraint's name is done). This will allow you to build your own map of
> ``FOREIGN KEY'' names. For example, if you want to expand ``PRAGMA
> foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
> PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.

Thank you, but I need to keep the official SQLite code.


>
>
> -- best regards
>
> Cezary H. Noweta
> ___
> 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] Retrieving constraint name

[Cezary H. Noweta]

Hello,

On 2017-12-11 01:04, Igor Korot wrote:


On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta  wrote:



On 2017-12-10 07:21, Igor Korot wrote:



The CREATE TABLE statement supports the following syntax:

CREATE TABLE( , CONSTRAINT  FOREIGN
KEY() REFERENCES (ref_column_list>);



[...] If not - does this mean that the only way to get the name is to parse the
sql
from sqlite_master? Or there is a better way?



The answer is ``not''. Constraint names are ignored and disappearing without
a trace except for ``CHECK'' constraint (the name is used to build an error
message). Unparsed ``sql'' column of ``sqlite_master'' is the sole place
which contains an indirect info about ``FOREIGN KEY'' constraint's name.



Thank you for confirming.

You are welcome. BTW, SQLite parses SQL every time it creates a table
(by a SQL command or after an opening of BTree file) -- I believe there
is no better way. You do not need to parse SQL on your own (it is hard,
if not impossible, to establish a link between a name and a particular
constraint). All you need is to append ``char *'' field to ``struct
FKey'' and to inject a function ``build.c:sqlite3CreateForeignKey()'':
``pParse->constraintName'' will contain the constraint's name (note
that the name is not dequoted -- you will have to dequote it; look at
``build.c:sqlite3AddCheckConstraint()'' to know how assigning of a
constraint's name is done). This will allow you to build your own map of
``FOREIGN KEY'' names. For example, if you want to expand ``PRAGMA
foreign_key_list'', go to ``pragma.c:sqlite3Pragma():case
PragTyp_FOREIGN_KEY_LIST:'' and append new FKey's field.

-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieving constraint name

[Igor Korot]

Hi,

On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta  wrote:
> Hello,
>
> On 2017-12-10 07:21, Igor Korot wrote:
>>
>> The CREATE TABLE statement supports the following syntax:
>>
>> CREATE TABLE( , CONSTRAINT  FOREIGN
>> KEY() REFERENCES (ref_column_list>);
>>
>> However, the statement "PRAGME foreign_key_list;" does not list the
>> foreign key name ("fk_name" in the statement above).
>>
>> Does the info for the aforementioned PRAGMA stored somewhere?
>> If yes - does it include the key name and it just not printed with the
>> PRAGMA?
>> If not - does this mean that the only way to get the name is to parse the
>> sql
>> from sqlite_master? Or there is a better way?
>
>
> The answer is ``not''. Constraint names are ignored and disappearing without
> a trace except for ``CHECK'' constraint (the name is used to build an error
> message). Unparsed ``sql'' column of ``sqlite_master'' is the sole place
> which contains an indirect info about ``FOREIGN KEY'' constraint's name.

Thank you for confirming.

>
> -- best regards
>
> Cezary H. Noweta
> ___
> 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] Retrieving constraint name

[Cezary H. Noweta]

Hello,

On 2017-12-10 07:21, Igor Korot wrote:

The CREATE TABLE statement supports the following syntax:

CREATE TABLE( , CONSTRAINT  FOREIGN
KEY() REFERENCES (ref_column_list>);

However, the statement "PRAGME foreign_key_list;" does not list the
foreign key name ("fk_name" in the statement above).

Does the info for the aforementioned PRAGMA stored somewhere?
If yes - does it include the key name and it just not printed with the PRAGMA?
If not - does this mean that the only way to get the name is to parse the sql
from sqlite_master? Or there is a better way?


The answer is ``not''. Constraint names are ignored and disappearing 
without a trace except for ``CHECK'' constraint (the name is used to 
build an error message). Unparsed ``sql'' column of ``sqlite_master'' is 
the sole place which contains an indirect info about ``FOREIGN KEY'' 
constraint's name.


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE SET using column-name-list Syntax error

[javaj1...@elxala.com]

Richard Hipp wrote:

On 12/10/17, javaj1...@elxala.com  wrote:

According to the documentation UPDATE SET admits column-name-list as
argument
but I cannot get it working. Here some tries

DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c);

-- ok
UPDATE test SET a = "vala", b = "valb" ;
UPDATE test SET a = (SELECT "vala"), b = (SELECT "valb") ;

-- not ok
UPDATE test SET (a, b) = "vala", "valb" ;
Error: near "(": syntax error
UPDATE test SET (a, b) = (SELECT "vala", "valb") ;
Error: near "(": syntax error

What am I doing wrong ? or is this syntax really supported ?

It is supported beginning with SQLite 3.15.0 (2016-10-14).  What
version of SQLite are you running?
Certantly! my application uses sqlite 3.17 2017-02-08 which is pretty 
new and support it
but I did the check in the command line actually with an older version 
(3.8.8.1 2015-01-20)

I will update this executable as well to avoid such wrong checks in future.

thank you!

PD:

-- not ok anyway (bad syntax from me)

UPDATE test SET (a, b) = "vala", "valb" ;
Error: 2 columns assigned 1 values

-- both ok
UPDATE test SET (a, b) = ("vala", "valb") ;
UPDATE test SET (a, b) = (SELECT "vala", "valb") ;






___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] random value get re-generated too often in SQLite

[Luuk]

On 08-12-17 23:34, Scott Doctor wrote:
>
> Is it possible that the first call to random is cached and the cached
> value is being returned in subsequent calls?
>
> -
> Scott Doctor
> sc...@scottdoctor.com
> -
>

The easiest way to get this behaviour is to store the random value in a
temp table, than you can use it as long as you like.

sqlite> create temp table rnd (i integer);
sqlite> select * from rnd;
sqlite> insert into rnd values (random());
sqlite> select * from rnd;
7337803792641969525
sqlite>

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE SET using column-name-list Syntax error

[x]

>UPDATE test SET (a, b) = "vala", "valb" ;

Should that not be (a, b) = (‘vala’, ‘valb’);


>UPDATE test SET (a, b) = (SELECT "vala", "valb") ;

Should that not be (a, b) = ((SELECT ‘vala’), ‘valb’);


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE SET using column-name-list Syntax error

[Richard Hipp]

On 12/10/17, javaj1...@elxala.com  wrote:
>
> According to the documentation UPDATE SET admits column-name-list as
> argument
> but I cannot get it working. Here some tries
>
> DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c);
>
> -- ok
> UPDATE test SET a = "vala", b = "valb" ;
> UPDATE test SET a = (SELECT "vala"), b = (SELECT "valb") ;
>
> -- not ok
> UPDATE test SET (a, b) = "vala", "valb" ;
> Error: near "(": syntax error
> UPDATE test SET (a, b) = (SELECT "vala", "valb") ;
> Error: near "(": syntax error
>
> What am I doing wrong ? or is this syntax really supported ?

It is supported beginning with SQLite 3.15.0 (2016-10-14).  What
version of SQLite are you running?
-- 
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

[sqlite] UPDATE SET using column-name-list Syntax error

[javaj1...@elxala.com]

Hi,

According to the documentation UPDATE SET admits column-name-list as 
argument

but I cannot get it working. Here some tries

DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c);

-- ok
UPDATE test SET a = "vala", b = "valb" ;
UPDATE test SET a = (SELECT "vala"), b = (SELECT "valb") ;

-- not ok
UPDATE test SET (a, b) = "vala", "valb" ;
Error: near "(": syntax error
UPDATE test SET (a, b) = (SELECT "vala", "valb") ;
Error: near "(": syntax error

What am I doing wrong ? or is this syntax really supported ?

I am specially interested in the syntax using SELECT since it could 
optimize the update

reducing the number of needed SELECT's

thanks,
Alejandro

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users