On Mon, Jul 20, 2009 at 10:57:33PM +0200, Kees Nuyt scratched on the wall:
> On Mon, 20 Jul 2009 08:34:52 -0500, "Jay A. Kreibich"
> <j...@kreibi.ch> wrote:
>
> >On Mon, Jul 20, 2009 at 12:41:59PM +0200, Jan scratched on the wall:
> >> thank you roger.
> >>
> >> Seems it's not an easy task. Guess I should go through some c tutorials.
> >> Would be really nice to have this included in sqlite itself.
> >
> >
> > If someone is going to take the time to refactor the genfkey code
> > into a stand-alone piece of code, I would like to suggest it is made
> > into it a loadable module, so that it can be used pretty much anywhere.
> >
> > Also, if the code is written carefully, it is possible to use the same
> > code to compile a module (.dll, .so, .dylib, etc.) or to compile
> > directly into an SQLite build with very minimal changes (e.g. using
> > #defines). That would be provide the most flexibility to the most
> > people.
> >
> > -j
>
> Think Lite.
I'm trying. That's why I didn't suggest just rolling it into
the main code. By having it as a module you can load it (or not) and
use it (or not) as needed. Those that feel they have a strong need
to have the code in the engine can have it, but those of use that
think it is overkill can leave it out. Heck, having it as a module
would let you remove it from sqlite3....
Personally I don't think this is worth it. I'm not raising my hand
to take this one on. The .genfkey command does a nice job of
spitting out the required SQL, making it easy to cut-and-paste,
even if your database and application are running on an embedded
system that can't otherwise use the sqlite3 CLI.
That said, I'll say this again: *IF* someone is going to take the
time to refactor the code, I would suggest it is made into a loadable
module. I think the current system to generate the trigger functions
is quite acceptable, but if someone is going to spend time, they
might as well move in a direction that is likely to have the biggest
impact and help the largest number of people.
> After a dynamic development phase, most databases have a
> static schema. At-run-time schema changes are usually a
> result of bad design. Run-time schema changes on referential
> constraints are very unlikely.
>
> Assuming the schema is static, there is no need to run
> .genfkey very time.
There is no need to run .genfkey, but many applications need to
create their own databases from scratch. There is definitely a
need to be able to issue the proper CREATE TRIGGER commands, even
if there isn't specifically a need to run the genfkey command.
As you point out, the only need for the command is if the
schema is unknown and/or dynamic. For known schemas, it is trivial
to run .genfkey on an empty, stub database (even on a different
platform) and extract the required SQL. This does a great job of
servicing all those applications with static schema which, I agree,
should be the vast majority of them.
That said, there are some legit needs for dynamic use of the .genfkey
code. Database managers are the first thing that come to mind.
Systems that programmatically generate their tables are another**.
And while it is true that this type of application is only a small
part of what SQLite services, a simple and easy module is something
I bet they would like. Others may have a use for it as well.
** for example, the logging system I'm working on that uses a
common "event" definition to generate SQL, C structs, C
code to pack/unpack those structs into streams, as well
as C code to read/write structs to the database.
* * * * * * * *
As the conversation shifts from the current state of .genfkey to the
bigger picture of SQLite and referential constraints, I want to be
sure we all remember one very important point:
The current referential constraint work-around
is incomplete and can lead to silent failure.
I specifically use the term "work-around" because I don't feel it is
a "solution." It is a great low-cost system that works most of the
time. It also fails to live up to a true referential constraint
system and can fail silently without any type of user notification
that they've done something dangerous.
Remember that the current solution is based off triggers, and SQLite
trigger support is also incomplete-- a trigger that triggers itself
will not run. From that, a constraint check that requires a
constraint check on the same table will also fail to run. No error
is given, the trigger is simply not called, the constraints fail
to do their job, and the database becomes inconsistent: i.e.
logically corrupt. This is trivial to demonstrate with a
self-referencing "tree" table that is more than two levels deep
and has a CASCADE ON DELETE constraint. If you delete a node, the
database will delete the node and it's children, but the rest of
the sub-tree remains, disjoint.
Of course none of this is a secret... the genfkey documentation is
very clear that the system has limitations, including the fact that
no errors will be reported. And I admit that the current system
is simple, easy to use, and *does* work the majority of the time.
http://www.sqlite.org/cvstrac/fileview?f=sqlite/tool/genfkey.README
But the current situation isn't an a closed case.
With that in mind....
> drh told us implementing real referential constraints in
> SQLite is difficult. I think we'll have to do with triggers
> for quite some time, which IMHO is no problem for a Lite
> product.
I don't doubt referential constraints are difficult. And I recognize
that translates into a lot of development time and money that
somebody, somewhere has to pony up. And I'll be the first to admit
that I'm not in a position to write that check (or write that code).
That said, ACID transactions are really hard too, but it wouldn't be
a database without them. To me, referential constraints aren't too
much further along the list of important points. It is, in a sense,
one of the *VERY* few things left that someone can point to and say,
"it isn't a *real* RDBMS...".
Yeah, I don't believe that either, but I also understand that point
of view-- especially if you're working with a code base that assumes
constraints like CASCADE work correctly. At that point referential
constraints aren't just a safety net, they're a way of getting things
done.
It isn't that I'm against a trigger based solution-- even a "manual"
one. I assume that if referential constraints where integrated into
the engine, they'd be done as some kind of behind-the-scenes trigger.
I also have no idea what would be harder: a "native" referential
system, or an improved trigger system that is capable of recursive
calls. I'm not even sure recursive trigger support is the missing
piece to full support for referential constraints (via genfkey or
some internal system).
But I do wish that SQLite had a more robust referential model, and I
think that's a legitimate point of view-- "Lite" or otherwise.
Some part of the SQLite user base are using genfkey and clearly
want referential constraints. "Lite" is a good reason to have a
feature or not have a feature, but I don't think "Lite" is a very
good reason to have a sorta-kinda feature.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
