Re: [sqlite] Foreign keys
Am 29.12.2007 um 17:00 schrieb Kees Nuyt: Better try it in the sqlite3 command line tool. That's common ground to everyone of us. Maybe no problem in the sqlite api. I will build create table scripts on the fly from XML via XSLT, that way I could also create the triggers. Second problem: The constraint is really ignored. (Referencing a non existent table) Indeed it is ignored. the syntax is parsed, that's all. I need the meta information. Not really forcing foreign key constraints. My application will do that. But when contributing to another library I should also create the required triggers. I have no idea how to catch this while creating the tables (triggers are not possible on system tables). You have several options: 1- Add the required CREATE TRIGGER statements to the schema source by hand. (easiest, that's what I do) Metainformation as above mentioned is required. It has nothing to do with the triggers. 2- Build a tool which parses the schema when you create a database and generates the required CREATE TRIGGER statements before you pipe the CREATE statements into the database. (relatively easy) XML -> XSLT will do that for me - at least. If the engine would create these triggers, I should have to omit that in my script. 3- Build a tool which parses the schema when you open a database and CREATE the triggers if they aren't in place. (more difficult) My currently preferred workaround :-) And it is not really a big issue, because there are existing samples using a Lex and Yacc parser. I tend to borrow parts of that code and build an extension library. Parts of the code would go in the open database code to optionally create the triggers and the rest goes to a function that creates a resultset for the foreign keys of a given table, 4- Change the SQLite source to implement foreign key constraints yourself. (very hard) I then have to more deeply look into the Sqlite source. There are more experienced developers for Sqlite :-) 5- Wait until it is implemented in SQLite. Implementing foreign key constraints is on the ToDo list of the developers (my way) I don't like to wait too long. 3 is better for me. I may create a consistency check while opening the database. That's too late, the database would already be inconsistent. You really need to implement the FK constraint, using triggers or otherwise. The triggers per table are required, I agree, but creating triggers on dangling references could be avoided. Doing a little more than ignoring the foreign constraint would help. Any ideas on this ? You could create a hook on every schema change (look for updates of sqlite_master in the sqlite library source) which checks if the required triggers are already CREATEd and if not, create them. Maybe the best way for both, detecting dangling references and the point to create the triggers. Is a separate system table for relations possible ? If so, the hook function could fill that table while creating the triggers. Also it would be easier for me to query for the foreign keys. Lothar Thanks, Lothar HTH -- ( Kees Nuyt ) c[_] --- -- To unsubscribe, send email to [EMAIL PROTECTED] --- -- -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Foreign keys
On Sat, 29 Dec 2007 15:17:27 +0100, Lothar Behrens <[EMAIL PROTECTED]> wrote: > >Am 29.12.2007 um 13:59 schrieb Kees Nuyt: > >> >> Hi Lothar, >> >> On Sat, 29 Dec 2007 13:13:04 +0100, Lothar Behrens >> <[EMAIL PROTECTED]> wrote: >> >> The only implementation I'm aware of is the one using triggers, >> but the creation is not implemented as an extension. >> >> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers >> http://www.sqlite.org/contrib (second entry) >> http://www.justatheory.com/computers/databases/sqlite/ >> foreign_key_triggers.html >> http://www.rcs-comp.com/site/index.php/view/Utilities- >> SQLite_foreign_key_trigger_generator >> >>> Thanks, Lothar >> > >In general this will help. But I have encountered a problem: > >create table IF NOT EXISTS regressiontest ( > id int primary key, > test char(100) >); > >create table IF NOT EXISTS test ( > id int primary key, > id_reg int, > constraint fk_reg foreign key (id_reg) references regression (id) >); > >First problem (propably only in sqliteman): > >The statement could not be executed at once ?? Better try it in the sqlite3 command line tool. That's common ground to everyone of us. >Second problem: > >The constraint is really ignored. >(Referencing a non existent table) Indeed it is ignored. the syntax is parsed, that's all. >I have no idea how to catch this while creating the >tables (triggers are not possible on system tables). You have several options: 1- Add the required CREATE TRIGGER statements to the schema source by hand. (easiest, that's what I do) 2- Build a tool which parses the schema when you create a database and generates the required CREATE TRIGGER statements before you pipe the CREATE statements into the database. (relatively easy) 3- Build a tool which parses the schema when you open a database and CREATE the triggers if they aren't in place. (more difficult) 4- Change the SQLite source to implement foreign key constraints yourself. (very hard) 5- Wait until it is implemented in SQLite. Implementing foreign key constraints is on the ToDo list of the developers (my way) >I may create a consistency check while opening the database. That's too late, the database would already be inconsistent. You really need to implement the FK constraint, using triggers or otherwise. >Any ideas on this ? You could create a hook on every schema change (look for updates of sqlite_master in the sqlite library source) which checks if the required triggers are already CREATEd and if not, create them. >Thanks, Lothar HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Foreign keys
Am 29.12.2007 um 13:59 schrieb Kees Nuyt: Hi Lothar, On Sat, 29 Dec 2007 13:13:04 +0100, Lothar Behrens <[EMAIL PROTECTED]> wrote: The only implementation I'm aware of is the one using triggers, but the creation is not implemented as an extension. http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers http://www.sqlite.org/contrib (second entry) http://www.justatheory.com/computers/databases/sqlite/ foreign_key_triggers.html http://www.rcs-comp.com/site/index.php/view/Utilities- SQLite_foreign_key_trigger_generator Thanks, Lothar In general this will help. But I have encountered a problem: create table IF NOT EXISTS regressiontest ( id int primary key, test char(100) ); create table IF NOT EXISTS test ( id int primary key, id_reg int, constraint fk_reg foreign key (id_reg) references regression (id) ); First problem (propably only in sqliteman): The statement could not be executed at once ?? Second problem: The constraint is really ignored. (Referencing a non existent table) I have no idea how to catch this while creating the tables (triggers are not possible on system tables). I may create a consistency check while opening the database. Any ideas on this ? Thanks, Lothar -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Foreign keys
Hi Lothar, On Sat, 29 Dec 2007 13:13:04 +0100, Lothar Behrens <[EMAIL PROTECTED]> wrote: >Hi all, > >now I have got more information on how to implement foreign key support. > >My plan is to use extension functions to do it by parsing the system >table's sql statement and returning >a result set array or the like. > >Before I start implementing the extension, has someone done such an >implementation that could be reused ? >(public domain or LGPL is preferred) The only implementation I'm aware of is the one using triggers, but the creation is not implemented as an extension. http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers http://www.sqlite.org/contrib (second entry) http://www.justatheory.com/computers/databases/sqlite/foreign_key_triggers.html http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator >Thanks, Lothar Hope this helps. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Foreign keys
Hi all, now I have got more information on how to implement foreign key support. My plan is to use extension functions to do it by parsing the system table's sql statement and returning a result set array or the like. Before I start implementing the extension, has someone done such an implementation that could be reused ? (public domain or LGPL is preferred) Thanks, Lothar -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: building SQLite 3.5.4 with SQLITE_OMIT_LOAD_EXTENSION
Hi, We're building the amalgamated source file with SQLITE_OMIT_LOAD_EXTENSION defined. This had been working just fine until the release of SQLite 3.5.2. I'm sorry, I need to add that we're trying to build sqlite3.c without header file sqlite3ext.h: http://www.sqlite.org/cvstrac/tktview?tn=2858 The problem occurs when sqlite3ext.h is removed: [...] sqlite3.c:80297:26: error: sqlite3ext.h: No such file or directory sqlite3.c: At top level: sqlite3.c:80335: error: expected '=', ',', ';', 'asm' or '__attribute__' before 'static' sqlite3.c: In function 'endsInWhiteSpace': sqlite3.c:80545: warning: implicit declaration of function 'safe_isspace' [...] -- Dimitri - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] building SQLite 3.5.4 with SQLITE_OMIT_LOAD_EXTENSION
Hi, We're building the amalgamated source file with SQLITE_OMIT_LOAD_EXTENSION defined. This had been working just fine until the release of SQLite 3.5.2. It stopped working after the release of SQLite 3.5.3 because sqlite3.c contains: #ifndef SQLITE_OMIT_LOAD_EXTENSION #define SQLITE_CORE 1 /* Disable the API redefinition in sqlite3ext.h */ and then: #ifndef SQLITE_CORE #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 #endif Changing from: #ifndef SQLITE_OMIT_LOAD_EXTENSION #define SQLITE_CORE 1 /* Disable the API redefinition in sqlite3ext.h */ to : #define SQLITE_CORE 1 /* Disable the API redefinition in sqlite3ext.h */ #ifndef SQLITE_OMIT_LOAD_EXTENSION "works" for me - functions such as qlite3OsDlOpen() are defined but not used but at least SQLite builds. Any clue? Regards, -- Dimitri - To unsubscribe, send email to [EMAIL PROTECTED] -
