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