Re: [sqlite] Retrieving constraint name

[Igor Korot]

Hi, Keith,

On Mon, Dec 11, 2017 at 12:07 AM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
> After stripping out comments and so forth of course ...

Could you please clarify that statement?
There is a patch from Cezary linked in this thread. I guess he is
successfully using it in his own application.

Thank you.

>
>
> ---
> 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 <ikoro...@gmail.com>
>>wrote:
>>
>>> Hi,
>>>
>>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta
>><c...@poczta.onet.pl>
>>> wrote:
>>> > Hello,
>>> >
>>> > On 2017-12-11 01:04, Igor Korot wrote:
>>> >
>>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta
>><c...@poczta.onet.pl>
>>> >> 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
___
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 2018-01-07 23:35, Igor Korot wrote:

Then maybe it could be considered to be included in the main tree by
simply applying the patch.


I'm afraid that if I change the order of fields in my patch, then it 
will not help. Exchanging the fields in the pragma is trivial. The whole 
patch is trivial also. At least for me, thus, by implication, for the 
team. I think that the reason for not extending the pragma lies elsewhere.


-- 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, Cezary et al,

On Mon, Dec 11, 2017 at 5:48 PM, Cezary H. Noweta  wrote:
> Hello,
>
> On 2017-12-11 04:29, Igor Korot wrote:
>>
>> Thank you, but I need to keep the official SQLite code.
>
> Anyway, for the people who are interested in foreign key names:
> http://sqlite.chncc.eu/fknames/. Certainly, all tests expecting original
> output of ``PRAGMA foreign_key_list'' will fail.

It would be nice if the extra column for the fk name will appear last in the
output. I understand that that's how most DBMSs are work, but  that
way the backward
compatibility can be preserved.

Then maybe it could be considered to be included in the main tree by
simply applying the patch.

Thank you.

>
>
> -- 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

[Igor Korot]

On Mon, Dec 11, 2017 at 4:48 PM, Cezary H. Noweta  wrote:
> Hello,
>
> On 2017-12-11 04:29, Igor Korot wrote:
>>
>> Thank you, but I need to keep the official SQLite code.
>
> Anyway, for the people who are interested in foreign key names:
> http://sqlite.chncc.eu/fknames/. Certainly, all tests expecting original
> output of ``PRAGMA foreign_key_list'' will fail.

Hopefully this patch can be included in the release...

Thank you.

>
>
> -- 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 04:29, Igor Korot wrote:

Thank you, but I need to keep the official SQLite code.
Anyway, for the people who are interested in foreign key names: 
http://sqlite.chncc.eu/fknames/. Certainly, all tests expecting original 
output of ``PRAGMA foreign_key_list'' will fail.


-- 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

[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 <ikoro...@gmail.com>
>wrote:
>
>> Hi,
>>
>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta
><c...@poczta.onet.pl>
>> wrote:
>> > Hello,
>> >
>> > On 2017-12-11 01:04, Igor Korot wrote:
>> >
>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta
><c...@poczta.onet.pl>
>> >> 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

[sqlite] Retrieving constraint name

[Igor Korot]

Hi,
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?

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