On Sat, 13 Jun 2009 23:42:21 +0100, Simon Slavin
<slav...@hearsay.demon.co.uk> wrote:
>I'm writing an application which involves lots of relations between
>tables. Seen from a high level, my application will have to enforce
>lots of rules to ensure database integrity. Before I used SQLite I
>would have enforced all these rules in my software. But SQLite has
>lots of ways to do this itself, with ON CONFLICT, TRIGGERs, and stuff
>like that. But I don't see any real reason to use these features, and
>I'm concerned about how well I can document what each of them is there
>for.
>
>I'm an experienced programmer and will have no problem enforcing the
>rules in my software. On the other hand, SQLite does some of them
>very neatly, with less code than I'd need in my application. On the
>gripping hand, if my software fails to do an operation it knows why
>and can generate a specific error message, whereas if SQLite hits a
>CONFLICT my error message would have to list all the possible reasons
>and let the user decide which one was the cause.
That's a trade off you have to decide on for yourself.
User input should be validated by the application anyway, so
the most common errors will be handled by the application.
Using CONSTRAINTs and TRIGGERs protects you against
programming errors, I would .
>Do any of you have experience with doing this ? Are there some
>conclusive points which will make me decide immediately that I should
>do it one way or the other ? I accept reasoned argument, URLs,
>anecdotes, or anything else relevant.
I tried to enforce consistency and integrity by implementing
a "value domain" system in awk. The schema source uses
domain names instead of types. They are simply substituted
by the domain definition.
The utility primes a new database, creates dictionary tables
and registers domains, tables, columns, including the
comments from the schema definition, together with dtcreated
and dtmodified timestamps. It also keeps a log of all DDL
and DML passed through it, loads .csv files by generating
INSERT statements, trims values, and optionally analyses the
datatypes, min and max values, and min and max length of the
values. It focuses on creating (portentially large)
databases in batch. No support for referential integrity.
It's undocumented, and I don't have time to answer any
questions about it, so it's not fit for publication.
Snipped of such a schema:
--[domains]
longname = VARCHAR(64) -- long name alphanum_64
shortname = CHAR(8) -- identifier (userid, account, ...)
longtext = CLOB -- text field of arbitrary length
counter = INTEGER -- integer
--[help]
CREATE TABLE %OBJECT% ( --
hlpfor shortname, -- knowledge domain
hlpname longname, -- name or short description
hlptext longtext, -- descriptive text
PRIMARY KEY (hlpfor,hlpname) ON CONFLICT ABORT
);
>By the way, the SQLite documentation is excellent but it's a little
>short on examples (unless there are a treasure trove of them somewhere
>I missed ?). How would I, for example, make SQLite refuse to delete
>an account if any transactions are recorded for it ? Make up your own
>schema for the two tables, as long at they're convincing.
Referential integrity can be obtained with REFERENCES
constraints (foreign key relations). SQLite parses the
syntax but doesn't enforce them yet. But the sqlite3 command
line tool has a command, .genfkey, which converts those
constraints into TRIGGERs that implement them.
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
There's also a site that implements it:
http://www.rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator
>Simon.
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
