pragma foreign_key_list deprecated in 3.7.4?

classic Classic list List threaded Threaded
48 messages Options
123
Reply | Threaded
Open this post in threaded view
|

pragma foreign_key_list deprecated in 3.7.4?

Petite Abeille-2
Hello,

The pragma foreign_key_list appears to be deprecated in 3.7.4:

http://www.sqlite.org/pragma.html#pragma_foreign_key_list

Any reason for such deprecation?

What's the alternative to that pragma to achieve the same effect?

Thanks in advance.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Richard Hipp-3
On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille <[hidden email]>wrote:

> Hello,
>
> The pragma foreign_key_list appears to be deprecated in 3.7.4:
>
> http://www.sqlite.org/pragma.html#pragma_foreign_key_list
>
> Any reason for such deprecation?
>

Now that foreign key constraints are enforced natively, why would you want
to have a list of them?  Why should the foreign_key_list pragma continue to
consume code space and developer maintenance time?



>
> What's the alternative to that pragma to achieve the same effect?
>
> Thanks in advance.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

ChrisWolf

On Dec 8, 2010, at 2:44 PM, Richard Hipp wrote:

> On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille <[hidden email]
> >wrote:
>
>> Hello,
>>
>> The pragma foreign_key_list appears to be deprecated in 3.7.4:
>>
>> http://www.sqlite.org/pragma.html#pragma_foreign_key_list
>>
>> Any reason for such deprecation?
>>
>
> Now that foreign key constraints are enforced natively, why would  
> you want
> to have a list of them?  Why should the foreign_key_list pragma  
> continue to
> consume code space and developer maintenance time?
>


Why would a developer NOT want to be able to query any part of the  
data dictionary
to see any aspect of the schema?   In addition to the info in  
sqlite_master, it would
be nice to be able to access column information as a query rather then  
a pragma.

For example, on Oracle you can get a list of foreign keys, like:
select  
owner
,constraint_name,constraint_type,table_name,r_owner,r_constraint_name 2
from all_constraints where constraint_type in ('P','U')
Sybase provides the "sp_fkeys" system stored proc for the same purpose.

If the point is that SQLite is meant to be be small and embedable, maybe
the extra data dictionary querying capability can be provided by a  
separate,
laodable extension.

   -Chris

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Bogdan Ureche-2
In reply to this post by Richard Hipp-3
Now that foreign key constraints are enforced natively, why would you want

> to have a list of them?  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?
>
>
>
It would make life easier for developers of administration tools for SQLite,
for displaying/editing foreign key constraints visually. The alternative
would be to parse the CREATE TABLE statements.

Bogdan Ureche
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Petite Abeille-2
In reply to this post by Richard Hipp-3

On Dec 8, 2010, at 8:44 PM, Richard Hipp wrote:

> Now that foreign key constraints are enforced natively, why would you want
> to have a list of them?

To know what they are. In the same way as there are pragma that list database, table, column, index, etc...

To paraphrase:

"Now that tables are implemented natively, why would you want to have a list of them?"

To know what they are. These are the building blocks of a data dictionary. Data dictionaries allows introspection. Introspection allows dynamic programming.

Case in point, one can pretty much build a useful information schema out of SQLite patchwork of pragmas:

http://dev.alt.textdrive.com/browser/IMDB/Info.ddl

Out of such information schema, one can build a full ETL (extract, process, load):

http://dev.alt.textdrive.com/browser/IMDB/ETL.ddl

Why would you want to prevent that?

>  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?

Because this is useful information. Such information is added value, not some whimsical luxury.

There is a trove of self describing information in the DDL that you seem to purposefully want to keep out of reach.

Please reconsider.

Thanks in advance.
 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

jan-118
In reply to this post by Petite Abeille-2
That is very bad news ...

My application uses this information e.g. to automatically populate
comboboxes (FKs) etc.



Am 08.12.2010 20:35, schrieb Petite Abeille:

> Hello,
>
> The pragma foreign_key_list appears to be deprecated in 3.7.4:
>
> http://www.sqlite.org/pragma.html#pragma_foreign_key_list
>
> Any reason for such deprecation?
>
> What's the alternative to that pragma to achieve the same effect?
>
> Thanks in advance.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Vivien Malerba
In reply to this post by Richard Hipp-3
On 8 December 2010 20:44, Richard Hipp <[hidden email]> wrote:

> On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille <[hidden email]>wrote:
>
>> Hello,
>>
>> The pragma foreign_key_list appears to be deprecated in 3.7.4:
>>
>> http://www.sqlite.org/pragma.html#pragma_foreign_key_list
>>
>> Any reason for such deprecation?
>>
>
> Now that foreign key constraints are enforced natively, why would you want
> to have a list of them?  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?

Libgda uses that information a lot. If you remove this pragma, I'll
have to add a lot of code to parse the table create statement. Please
don't remove that pragma!

Regards,

Vivien
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Simon Slavin-3
In reply to this post by Bogdan Ureche-2

On 8 Dec 2010, at 8:03pm, Bogdan Ureche wrote:

> Now that foreign key constraints are enforced natively, why would you want
>
>> to have a list of them?  Why should the foreign_key_list pragma continue to
>> consume code space and developer maintenance time?
>
> It would make life easier for developers of administration tools for SQLite,
> for displaying/editing foreign key constraints visually. The alternative
> would be to parse the CREATE TABLE statements.

Just like Jan, I have a system that populates blank forms based on the default values for columns and provides popup lists based on FOREIGN KEYs.

I would find it useful to be able to retrieve all elements of the database schema, up to and including which TRIGGERs exist.  I don't know that the 'foreign_key_list' is the best way to do it, but I would like /a/ way to do it.  Perhaps expanding 'PRAGMA table_info' by creating a second parameter for that could be 'tables', 'columns', 'indexes', 'triggers', 'foreign keys', etc. would be a good way to go.

Ideally it should be possible to completely reproduce the schema from columns, so I don't have to write a text parser for sqlite_master.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Gabor Grothendieck
In reply to this post by Richard Hipp-3
On Wed, Dec 8, 2010 at 2:44 PM, Richard Hipp <[hidden email]> wrote:

> On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille <[hidden email]>wrote:
>
>> Hello,
>>
>> The pragma foreign_key_list appears to be deprecated in 3.7.4:
>>
>> http://www.sqlite.org/pragma.html#pragma_foreign_key_list
>>
>> Any reason for such deprecation?
>>
>
> Now that foreign key constraints are enforced natively, why would you want
> to have a list of them?  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?
>

Surely if the objective is to be small yet useful its important to
give users the capability to easily implement what would otherwise
have to be done in the database itself.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Adam Kennedy-3
In reply to this post by Richard Hipp-3
Foreign key information is highly useful in many different ORM systems
in many different languages.

My personal example, the "An ORM for any SQLite database in one line
of code" ORLite module in Perl.

http://search.cpan.org/perldoc?ORLite

It is expected to generate entire class trees for any arbitrary
database unassisted with just

use ORLite 'mydatabase.sqlite';

It uses introspection of the foreign key information to generate code
which allows something like the following to return a different object
instead of just the id.

$child->father

Right now I'm doing some extremely fragile SQL parsing but was about
to switch over to the more reliable pragma. I'd dearly love to
continue to be able to.

That said, I don't specifically need it to be a pragma, just as long
as there's some place to get the information instead of writing my own
SQL parser. Somewhere in a sqlite__columns table would be totally fine
too.

In summary, the enforcement of foreign key constraints is not the only
use case for the pragma. While the enforcement use case is now
removed, the rest still remain.

Adam K

On 9 December 2010 06:44, Richard Hipp <[hidden email]> wrote:

> On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille <[hidden email]>wrote:
>
>> Hello,
>>
>> The pragma foreign_key_list appears to be deprecated in 3.7.4:
>>
>> http://www.sqlite.org/pragma.html#pragma_foreign_key_list
>>
>> Any reason for such deprecation?
>>
>
> Now that foreign key constraints are enforced natively, why would you want
> to have a list of them?  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?
>
>
>
>>
>> What's the alternative to that pragma to achieve the same effect?
>>
>> Thanks in advance.
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

BareFeetWare-2
In reply to this post by Richard Hipp-3
>> The pragma foreign_key_list appears to be deprecated in 3.7.4:

Oh no. That is bad news. Please restore this feature.

> Now that foreign key constraints are enforced natively, why would you want to have a list of them?  Why should the foreign_key_list pragma continue to consume code space and developer maintenance time?

Because we need to access the schema in order to properly construct our applications. For instance, a list of foreign keys allows us to present to the user of our applications a list of related rows, as shown here:
http://www.databare.com/related.html

and a popup list of available entries in a column, as shown here:
http://www.databare.com/data_choices.html

It's similar to our requirement to extract a list of tables and views for a database or the list of columns in a table or view. Sure, SQLite internally knows about them, but we need to be able to extract that list to present to the user, either directly or indirectly. So we use select * from SQLite_Master and pragma table_info().

As for the bigger picture of "why would you want to have a list of ..." schema information in general:

I'd like to see it taken further, to make most of the schema parsing information available either via SQL or at worst via C. At present, there are a few pragmas that give us limited info. For instance, pragma table_info gives us the name and declared types of columns in a table or view, but it doesn't tell us the expression used to create that column in a view, and other column specific attributes.

Instead of pragmas, it would be most useful to have schema info accessible via queries on built in tables or views (similar to SQLite_Master). That way we can join various tables in a pure SQL query, without having to reinject data or parse it outside of SQL. For example, to get the steps of the trigger responsible for updates to the column "Name First" in the view "Customers Entry", we could:

select Step_SQL
from SQLite_Trigger_Steps
        left join SQLite_Trigger_Update_Columns on SQLite_Trigger_Update_Columns.Trigger_Name = SQLite_Trigger_Steps.Trigger_Name
        left join SQLite_Master on Name = SQLite_Trigger_Update_Columns.Trigger_Name
where SQLite_Master.Tbl_Name = 'Customers Entry' and SQLite_Master.Type = 'trigger' and SQLite_Trigger_Update_Columns.Column_Name = 'Name First'

This example is very useful to make data editable in views in my applications, since it tells me the dependancies for any changes. For example, see the screenshots here:
http://www.databare.com/column_trigger.html
http://www.databare.com/update_view.html

At present, I have to do this by parsing the whole schema in my own code, which seems unnecessarily repetitive (since SQLite already does it internally but I can't get to it), inefficient (since I'm separated from the internal SQLite workings) and inaccurate (because I'm using the documentation to make an educated guess as to how SQLite is dealing with the schema).

Thanks for your consideration and a great product,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Duquette, William H (393K)
In reply to this post by Richard Hipp-3
Richard,


On 12/8/10 11:44 AM, "Richard Hipp" <[hidden email]> wrote:

On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille <[hidden email]>wrote:

> Hello,
>
> The pragma foreign_key_list appears to be deprecated in 3.7.4:
>
> http://www.sqlite.org/pragma.html#pragma_foreign_key_list
>
> Any reason for such deprecation?
>

Now that foreign key constraints are enforced natively, why would you want
to have a list of them?  Why should the foreign_key_list pragma continue to
consume code space and developer maintenance time?

Because it lets me introspect the schema, and write code (or code generators) that depend on the logical structure of the schema, without having to parse the DDL myself.  For example, it makes it easier to undo
cascading deletes.  Given the foreign_key_list pragma, I can determine the tables that depend on a given table, and then target triggers to those specific tables to capture the undo information.

Why get rid of introspection that already exists?  If code space is the big deal, make it an optional part of the build.

Will

>
> What's the alternative to that pragma to achieve the same effect?
>
> Thanks in advance.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Will Duquette -- [hidden email]
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

jan-118
In reply to this post by Richard Hipp-3


Am 08.12.2010 20:44, schrieb Richard Hipp:
> Now that foreign key constraints are enforced natively, why would you want
> to have a list of them?  Why should the foreign_key_list pragma continue to
> consume code space and developer maintenance time?

Was this only a rhetorical question?
Or is there a small chance that this pragma will survive?

The answers at least show that there are people out there who find this
pragma very useful.



Jan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Richard Hipp-3
On Fri, Dec 10, 2010 at 4:54 AM, Jan <[hidden email]> wrote:

>
>
> Am 08.12.2010 20:44, schrieb Richard Hipp:
> > Now that foreign key constraints are enforced natively, why would you
> want
> > to have a list of them?  Why should the foreign_key_list pragma continue
> to
> > consume code space and developer maintenance time?
>
> Was this only a rhetorical question?
> Or is there a small chance that this pragma will survive?
>

I relented on this two days ago.  See
http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)


>
> The answers at least show that there are people out there who find this
> pragma very useful.
>
>
>
> Jan
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

jan-118


Am 10.12.2010 12:17, schrieb Richard Hipp:

> I relented on this two days ago.  See
> http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
> 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)

That is great! Thanks a lot. Guess you saved many people from a *sigh*

>
>
>>
>> The answers at least show that there are people out there who find this
>> pragma very useful.
>>
>>
>>
>> Jan
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Petite Abeille-2
In reply to this post by Richard Hipp-3

On Dec 10, 2010, at 12:17 PM, Richard Hipp wrote:

> I relented on this two days ago.  See
> http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
> 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)

Much excellent. Thanks :)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Duquette, William H (393K)
On 12/10/10 10:36 AM, "Petite Abeille" <[hidden email]> wrote:


On Dec 10, 2010, at 12:17 PM, Richard Hipp wrote:

> I relented on this two days ago.  See
> http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
> 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)

Much excellent. Thanks :)

Agreed!

Will

--
Will Duquette -- [hidden email]
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list deprecated in 3.7.4?

Daniel Vogelbacher
In reply to this post by Richard Hipp-3
On [Fri, 10.12.2010 06:17], Richard Hipp wrote:

> On Fri, Dec 10, 2010 at 4:54 AM, Jan <[hidden email]> wrote:
>
> >
> >
> > Am 08.12.2010 20:44, schrieb Richard Hipp:
> > > Now that foreign key constraints are enforced natively, why would you
> > want
> > > to have a list of them?  Why should the foreign_key_list pragma continue
> > to
> > > consume code space and developer maintenance time?
> >
> > Was this only a rhetorical question?
> > Or is there a small chance that this pragma will survive?
> >
>
> I relented on this two days ago.  See
> http://www.sqlite.org/docsrc/info/d399230aae for the check-in.  Version
> 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)
>

That are great news. Thanks for continuing this feature!




--
     Daniel Vogelbacher

----
web: http://daniel.vogelbacher.name
irc: cytrinox @ (freenode|ircnet|quakenet)
www.informave.org
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list

BareFeetWare-2
In reply to this post by Richard Hipp-3
On 10/12/2010, at 10:17 PM, Richard Hipp wrote:

> Version 3.7.5 will list PRAGMA foreign_key_list as fully supported.  (sigh)

Great news, thank you.

Why the sigh? Do you not see the value in us having introspection into schemas?

Could you please consider taking it further, providing foreign keys via a select query instead of a pragma? SQLite currently provides limited introspection, some via select * from SQLite_Master and some via pragmas. Of the two, select is far more useful, since it can be filtered directly and joined with other tables.

For instance, if foreign keys could be accessed via select, we could get all the foreign keys for a given column via one statement:

select * from SQLite_Foreign_Keys where To_Table = 'My Table' and To_Column = 'My Column';

Rather than having to virtually recreate SQL functionality externally in order to scan through the result of a pragma.

Pragmas seem unnecessarily separated from the SQLite engine, returning a table of info that can't be manipulated by the SQL engine in which it resides.

Thanks for your consideration,
Tom
BareFeetWare

--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma foreign_key_list

Simon Slavin-3

On 10 Dec 2010, at 10:51pm, BareFeetWare wrote:

> Could you please consider taking it further, providing foreign keys via a select query instead of a pragma? SQLite currently provides limited introspection, some via select * from SQLite_Master and some via pragmas. Of the two, select is far more useful, since it can be filtered directly and joined with other tables.
>
> For instance, if foreign keys could be accessed via select, we could get all the foreign keys for a given column via one statement:
>
> select * from SQLite_Foreign_Keys where To_Table = 'My Table' and To_Column = 'My Column';

The problem with foreign keys (and triggers !) as separate rows of SQLITE_MASTER is that it would all have to be one long string, so you'd have to write a parser.

I think a better idea would be to expand table_info with a second parameter so it could list all tables, and report on columns, indexes, triggers and foreign keys all in one PRAGMA.  Then all the other PRAGMAs that do this could be removed.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
123