Retrieving constraint name

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

Retrieving constraint name

Igor Korot
Hi,
The CREATE TABLE statement supports the following syntax:

CREATE TABLE( <column_name_list>, CONSTRAINT <fk_name> FOREIGN
KEY(<fk_field>) REFERENCES <ref_table>(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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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( <column_name_list>, CONSTRAINT <fk_name> FOREIGN
> KEY(<fk_field>) REFERENCES <ref_table>(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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Retrieving constraint name

Igor Korot
Hi,

On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta <[hidden email]> wrote:

> Hello,
>
> On 2017-12-10 07:21, Igor Korot wrote:
>>
>> The CREATE TABLE statement supports the following syntax:
>>
>> CREATE TABLE( <column_name_list>, CONSTRAINT <fk_name> FOREIGN
>> KEY(<fk_field>) REFERENCES <ref_table>(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
> [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: 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 <[hidden email]> wrote:

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

>>> The CREATE TABLE statement supports the following syntax:
>>>
>>> CREATE TABLE( <column_name_list>, CONSTRAINT <fk_name> FOREIGN
>>> KEY(<fk_field>) REFERENCES <ref_table>(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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Retrieving constraint name

Igor Korot
Hi,

On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta <[hidden email]> wrote:

> Hello,
>
> On 2017-12-11 01:04, Igor Korot wrote:
>
>> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta <[hidden email]>
>> wrote:
>
>
>>> On 2017-12-10 07:21, Igor Korot wrote:
>
>
>>>> The CREATE TABLE statement supports the following syntax:
>>>>
>>>> CREATE TABLE( <column_name_list>, CONSTRAINT <fk_name> FOREIGN
>>>> KEY(<fk_field>) REFERENCES <ref_table>(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
> [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: 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 <[hidden email]> wrote:

> Hi,
>
> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta <[hidden email]>
> wrote:
> > Hello,
> >
> > On 2017-12-11 01:04, Igor Korot wrote:
> >
> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta <[hidden email]>
> >> wrote:
> >
> >
> >>> On 2017-12-10 07:21, Igor Korot wrote:
> >
> >
> >>>> The CREATE TABLE statement supports the following syntax:
> >>>>
> >>>> CREATE TABLE( <column_name_list>, CONSTRAINT <fk_name> FOREIGN
> >>>> KEY(<fk_field>) REFERENCES <ref_table>(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
> > [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: Retrieving constraint name

Igor Korot
Hi,

On Sun, Dec 10, 2017 at 11:36 PM, petern <[hidden email]> 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 <[hidden email]> wrote:
>
>> Hi,
>>
>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta <[hidden email]>
>> wrote:
>> > Hello,
>> >
>> > On 2017-12-11 01:04, Igor Korot wrote:
>> >
>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta <[hidden email]>
>> >> wrote:
>> >
>> >
>> >>> On 2017-12-10 07:21, Igor Korot wrote:
>> >
>> >
>> >>>> The CREATE TABLE statement supports the following syntax:
>> >>>>
>> >>>> CREATE TABLE( <column_name_list>, CONSTRAINT <fk_name> FOREIGN
>> >>>> KEY(<fk_field>) REFERENCES <ref_table>(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
>> > [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: Retrieving constraint name

Keith Medcalf
In reply to this post by petern

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-
>[hidden email]] 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 <[hidden email]>
>wrote:
>
>> Hi,
>>
>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta
><[hidden email]>
>> wrote:
>> > Hello,
>> >
>> > On 2017-12-11 01:04, Igor Korot wrote:
>> >
>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta
><[hidden email]>
>> >> wrote:
>> >
>> >
>> >>> On 2017-12-10 07:21, Igor Korot wrote:
>> >
>> >
>> >>>> The CREATE TABLE statement supports the following syntax:
>> >>>>
>> >>>> CREATE TABLE( <column_name_list>, CONSTRAINT <fk_name> FOREIGN
>> >>>> KEY(<fk_field>) REFERENCES <ref_table>(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
>> > [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: Retrieving constraint name

Cezary H. Noweta
In reply to this post by Igor Korot
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Retrieving constraint name

Igor Korot
On Mon, Dec 11, 2017 at 4:48 PM, Cezary H. Noweta <[hidden email]> 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
> [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: Retrieving constraint name

Igor Korot
In reply to this post by Cezary H. Noweta
Hi, Cezary et al,

On Mon, Dec 11, 2017 at 5:48 PM, Cezary H. Noweta <[hidden email]> 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
> [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: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Retrieving constraint name

Igor Korot
In reply to this post by Keith Medcalf
Hi, Keith,

On Mon, Dec 11, 2017 at 12:07 AM, Keith Medcalf <[hidden email]> 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-
>>[hidden email]] 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 <[hidden email]>
>>wrote:
>>
>>> Hi,
>>>
>>> On Sun, Dec 10, 2017 at 8:30 PM, Cezary H. Noweta
>><[hidden email]>
>>> wrote:
>>> > Hello,
>>> >
>>> > On 2017-12-11 01:04, Igor Korot wrote:
>>> >
>>> >> On Sun, Dec 10, 2017 at 5:01 PM, Cezary H. Noweta
>><[hidden email]>
>>> >> wrote:
>>> >
>>> >
>>> >>> On 2017-12-10 07:21, Igor Korot wrote:
>>> >
>>> >
>>> >>>> The CREATE TABLE statement supports the following syntax:
>>> >>>>
>>> >>>> CREATE TABLE( <column_name_list>, CONSTRAINT <fk_name> FOREIGN
>>> >>>> KEY(<fk_field>) REFERENCES <ref_table>(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
>>> > [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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users