Re: [sqlite] Database logic in TRIGGER and CONFLICT, or in software ?

[Jay A. Kreibich]

On Sat, Jun 13, 2009 at 11:42:21PM +0100, Simon Slavin scratched on the wall:

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

  Every system that uses a database must deal with this problem, and
  there are no real easy answers.  But here are a few deeper points to
  consider.

  From the view point of Relational Model, the formal mathematical
  model that most RDBMS engines are based off (in theory, anyways), you
  should push as many constraints, rules, and general enforcement into
  the database as possible.  The database is meant to represent "truth"
  and under the Model it should be impossible (or at least as difficult
  as possible) to make the database become inconsistent-- that is, be
  in a state that doesn't represent truth.

  There are two main issues with this.  First, an modern SQL database
  is not a Relational Model database.  SQL only offers a number of
  different constraints, but it is not as flexible or complete as the
  theoretical model presented by the Relational Model.

  Second, the Relational Model doesn't account for access methods or
  the concept of middle-ware or tiered applications.  It is assumed
  that anyone and everything from applications to command-line systems
  will be accessing the database.  If you have a user typing in raw SQL
  commands and you want your database to remain consistent, you have no
  choice but to push as much as possible into the database and do lots
  of audits.

  Thankfully, most real-world applications do not work this way.  You
  provide some piece of software that accesses the database, and (in
  general) the only people that have command line access are DBAs and
  other admin types.  In that case, you can relax some of the constraints
  or rules that are difficult to express in the database and move those
  out to the application.

  This can be good or bad.  In the case of an old-school mainframe
  application, where you logged into a central system via your VT100 or
  TN3270 terminal and ran the application on the mainframe, the
  software was centralized and easy to maintain.  A system-wide
  application upgrade consists of replacing one binary.  This made it
  relatively safe to push rules and constraints into the application, as
  it was easy to keep the end-user application and the database --
  including schema changes and format updates -- in sync.  After all,
  both the DB and the application were sitting on one machine.

  Then came the era of desktop systems.  Now it was common to have a
  GUI desktop application that was used to access and manipulate the
  database.  The problem is, it is nearly impossible to keep every
  desktop system in perfect sync.  If a schema change required an
  update to a query, that required a new version of the desktop
  application, which required a network-wide upgrade.  Really fast, you
  learned to either make your upgrades backwards compatible OR you had
  an extremely simply automatic update system.  Regardless, there were
  dangers associated with pushing too many high-level rules into the
  application.  If someone managed to access the database with an older
  client that had a different set of rules, bad things could happen.
  To get around these problems people learned to push more and more
  logic into the database, including complex stored functions and
  procedures to do just about any update or adjustment and views for
  all but the most basic filtering of a query.  This would allow for
  schema changes without client updates.

  As the web gained popularity, along with middle-ware and multi-tier
  architectures, we returned more or less to the model of "everything
  on one machine" or, at least, under one control.  If the application
  is completely web driven, the problem of upgrading the SQL access
  application (e.g. the web server code) and the database are greatly
  reduced compared to the desktop model, allowing more and more logic
  to return to the code.

  So much of the answer to your question depends on the environment
  you're trying to support and how much control you have over all the
  bits of code that have direct access to the database.  If you control
  the database AND the next tier down, pushing logic, rules, and
  constraints into that layer has fewer issues and ramifications than
  keeping them in the database.

  From a pure software engineering standpoint, the best solution is to
  keep the rules and constraints as close to the data as possible.
  Just like the Relational Model thinking, the fewer ways there are to
  screw up the data, the better.  If you have control over the next
  layer you can "let it hang out" a bit.  There is still the danger
  that some admin can screw something up with a command line tool, but
  in theory those people know what they're 

[sqlite] Error message from RAISE just plain text ?

[Simon Slavin]

I'm getting my information about RAISE from



and an example from



To my surprise, error-message is not defined in



which should probably be rectified.



The examples for the error text I've found are all simple text  
strings, for instance
RAISE(ROLLBACK, 'delete on table "foo" violates foreign key constraint  
"fk_foo_id"')
What I want to do is more like
RAISE(ROLLBACK, 'Attempt to delete author '||old.name||' who has  
books.')
but that doesn't work.  And since
RAISE(ROLLBACK, old.id)
doesn't work I assume you can't use functions of any kind.  Which is a  
pity, because I would like to have SQL generate an error message with  
context.  Can this not be done with the current version ?  And if not,  
rather than documenting the current behaviour, can error-message be  
made to be an expression in the next version ?

Simon.
-- 
  http://www.hearsay.demon.co.uk | I'd expect if a computer was involved
 | it all would have been much worse.
No Buffy for you.|-- John "West" McKenna
Leave quickly now. -- Anya   |  THE FRENCH WAS THERE

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite3 - SQL injection using ruby

[John Elrick]

dave lilley wrote:
> I am using sqlite3 with ruby and hope I'm not out of place here in ask for
> some help on how to stop or reduce injection threats via sql statements made
> by a user be it accidental or deliberate.
>
> I want to build a select query from user entered data and then return rows
> that match.
>
> e.g. stmt = "select * from customers where cust_no = #{uservar}"
>
> row = db.execute(stmt)
>   


stmt = "select * from customers where cust_no = ?"

row = db.execute(stmt, uservar)


HTH


John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I create an automatically generated primary key id column.

[Simon Slavin]

On 14 Jun 2009, at 6:52pm, Tim Bradshaw wrote:

> Well, it can if you are willing to be a bit devious.  Something like
> this works:
>
> create table frobs (
>  id integer primary key,
>  frob varchar);
>
> create temporary table frobs_import (
>  frob varchar);
>
> .import data frobs_import
>
> insert into frobs(frob)
>  select * from frobs_import;

Okay, that wins my deviousity award of the day.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I create an automatically generated primary key id column.

[Kermit Mei]

在 2009-06-14日的 18:33 +0100，Simon Slavin写道：
> On 14 Jun 2009, at 4:43pm, Kermit Mei wrote:
> 
> > But I want to insert values from a file underlinux,
> > and I don't want write any code for it.
> 
> Okay, that kind of information is important enough to appear in your / 
> first/ post.
> 
> > I hope sqlite3 > .import FILE
> > can do it
> 
> It can't.  However, if you are good with a spreadsheet you can get  
> your data in two ways without any programming.  One is to add your own  
> column for the unique id numbers and use a trivial calculation to fill  
> it.  The other is to turn your text file into a set of import commands  
> to do the importing for you.  Then you can use .read instead.  The  
> first one is easy, for the second one ...
> 
> Import your data file into a spreadsheet, and make sure you have your  
> values in columns of the spreadsheet.  Then create a new column which  
> has the appropriate INSERT command to insert those values. In my  
> favourite spreadsheet application this would be something like
> 
> (formula to go in cell C1)
> 
> =CONCAT("INSERT mytable (name, age) VALUES ('",a1,"', "+b1+");")
> 
> Then fill the C column downwards with copies of that formula.
> 
> Once you have that, copy the contents of column C, paste it into a  
> text file, and you have all the instructions you'd need to put your  
> data into that table.  Then use the .read command in sqlite to run  
> those commands.
> 
> Simon.

Yes, I see. 
In fact, my manager asked me to provide a txt file for the others to
import something into the database. And the ones, who will import data
may not understand database at all. 
Maybe I'd better to write a GUI for them:P 
Thank you, Simon:P

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I create an automatically generated primary key id column.

[Tim Bradshaw]

On 14 Jun 2009, at 18:33, Simon Slavin wrote:

>> I hope sqlite3 > .import FILE
>> can do it
>
> It can't.

Well, it can if you are willing to be a bit devious.  Something like  
this works:

create table frobs (
  id integer primary key,
  frob varchar);

create temporary table frobs_import (
  frob varchar);

.import data frobs_import

insert into frobs(frob)
  select * from frobs_import;

--tim

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I create an automatically generated primary key id column.

[Simon Slavin]

On 14 Jun 2009, at 4:43pm, Kermit Mei wrote:

> But I want to insert values from a file underlinux,
> and I don't want write any code for it.

Okay, that kind of information is important enough to appear in your / 
first/ post.

> I hope sqlite3 > .import FILE
> can do it

It can't.  However, if you are good with a spreadsheet you can get  
your data in two ways without any programming.  One is to add your own  
column for the unique id numbers and use a trivial calculation to fill  
it.  The other is to turn your text file into a set of import commands  
to do the importing for you.  Then you can use .read instead.  The  
first one is easy, for the second one ...

Import your data file into a spreadsheet, and make sure you have your  
values in columns of the spreadsheet.  Then create a new column which  
has the appropriate INSERT command to insert those values. In my  
favourite spreadsheet application this would be something like

(formula to go in cell C1)

=CONCAT("INSERT mytable (name, age) VALUES ('",a1,"', "+b1+");")

Then fill the C column downwards with copies of that formula.

Once you have that, copy the contents of column C, paste it into a  
text file, and you have all the instructions you'd need to put your  
data into that table.  Then use the .read command in sqlite to run  
those commands.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I create an automatically generated primary key id column.

[Kermit Mei]

在 2009-06-14日的 14:50 +0100，Simon Slavin写道：
> On 14 Jun 2009, at 2:23pm, Kermit Mei wrote:
> 
> > I want to create a new table, its primary key is an int 'id' column,  
> > but
> > I don't want to insert the data by hand, I hope the system can  
> > generate
> > it by adding id.
> >
> > For example,
> > mytable(id, name, age)
> >
> > When I insert name and age, the id is generated automatically.
> 
> Just don't mention the id column when you insert data:
> 
> INSERT INTO mytable (name, age) VALUES ('Frank', 18)
> 
> > How can I import data from a file, and SPECIFY the column?
> 
> The above might answer your question, but if it doesn't, we probably  
> need to know how you are doing your importing: are you writing your  
> own software or using a tool we're all familiar with ?
> 
> Simon.
 Thank you, Simon. But I want to insert values from a file underlinux,
and I don't want write any code for it. I hope sqlite3 > .import FILE
can do it:)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Shortest time interval? [Was: Re: repeating events?]

[Dennis Cote]

Allen Fowler wrote:
>
>
>   
>> You could be storing event duration, not stop time.  Or perhaps store  
>> both.
>>
>> 
>
> Here is what I have so far:
>
> sqlite> create table events (id INTEGER PRIMARY KEY AUTOINCREMENT, name, 
> kind, start, end);
>
> # Now add some events for "tomorrow"
> sqlite>
> insert into events values (null, 'tom', 'hour', datetime('now', '+1
> day','start of day', '+11 hours'), datetime('now', '+1 day','start of
> day', '+12 hours'));
> sqlite> insert into events values (null,
> 'tom', 'hour', datetime('now', '+1 day','start of day', '+9 hours'),
> datetime('now', '+1 day','start of day', '+10 hours'));
> sqlite>
> insert into events values (null, 'joe', 'hour', datetime('now', '+1
> day','start of day', '+9 hours'), datetime('now', '+1 day','start of
> day', '+10 hours'));
>
> # Now add an all-day event for tomorrow.  (It overlaps a couple of above 
> events.)
> sqlite> insert into events values (null,
> 'tom', 'day', datetime('now', '+1 day','start of day'), datetime('now',
> '+1 day','start of day', '+1 day'));
>
> # Show all events and duration:
> sqlite> select *, (strftime('%s', end) -  strftime('%s', start)) as length 
> from events;
> idname  kind  start   
>   end   length
>       
>     --
> 1 tom   hour  2009-06-13 
> 11:00:00   2009-06-13 12:00:00   3600  
> 2 tom   hour  2009-06-13 
> 09:00:00   2009-06-13 10:00:00   3600  
> 3 joe   hour  2009-06-13 
> 09:00:00   2009-06-13 10:00:00   3600  
> 4 tom   day   2009-06-13 
> 00:00:00   2009-06-14 00:00:00   86400
>
>
> #
> # And now, I want to get a result table with one row per user showing the 
> "shortest active at 9:30 AM event" for each user.
>
> sqlite>
> select *, min((strftime('%s', end) -  strftime('%s', start))) as length
> from
>...> events where 
>...> start < datetime('now', '+1 day','start of day',
> '+9 hours','+30 minutes') 
>...> and end >  datetime('now', '+1 day','start
> of day', '+9 hours','+30 minutes')
>...> group by name;
>
> idname  kind  start   
>   end   length
>       
>     --
> 3 joe   hour  2009-06-13 
> 09:00:00   2009-06-13 10:00:00   3600  
> 4 tom   day   2009-06-13 
> 00:00:00   2009-06-14 00:00:00   3600  
> sqlite> 
>
> However this result returned is very wrong.The length col is correct but 
> the other cols for "tom" are wrong.  (It should be "2 | tom | hour | 
> 2009-06-13 09:00:00 | 2009-06-13 10:00:00 | 3600" )
>
> What am I missing here?  Am I doing the query wrong?
>
>   
Allen,

Yes, you are doing something wrong. It is probably the most common 
mistake made when using grouping in SQL.

When grouping, keep in mind that all columns that appear in your SELECT 
column list, that are not aggregated (used along with one of the SQL 
aggregate functions), have to appear in the GROUP BY clause too.

The only valid columns in your output (i.e. with a select *, min()) is 
the column named in the group by clause (i.e. the name) and the 
aggregate value (i.e. the min()). You have determined the length of the 
minimum event for each name that meets your other conditions and nothing 
else. You do not know its id, what kind it is, or when it starts or 
ends. The values shown for those other fields are randomly selected from 
the set of rows in the matching group.

SQLite and some other database programs don't complain when you to break 
this rule because it is sometimes useful to get a randomly selected 
value for a column in the group. Usually it just leads to the confusion 
you are seeing.

HTH
Dennis Cote


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SPHiveDB: A server for sqlite database.

[Olaf Schmidt]

"Olaf Schmidt"  schrieb im
Newsbeitrag news:h131eu$m0...@ger.gmane.org...

> Loop 1000 times
> With LoopCounter Modulo 7 (case-switch)
> ...
> ...
> Ending up with 11 new records in the DB

 Ok - make the loop-counter 7000 to reach the
result of 11 new records.

Sorry.

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SPHiveDB: A server for sqlite database.

[Olaf Schmidt]

"stephen liu"  schrieb
im Newsbeitrag
news:e5a33d590906140609h4f3f35fah9083be3560f5d...@mail.gmail.com...
http://code.google.com/p/sphivedb/

> SPHiveDB is a server for sqlite database. It use JSON-RPC over
> HTTP to expose a network interface to use SQLite database.
> It supports combining multiple SQLite databases into one file.
> It also supports the use of multiple files ( through Tokyo Cabinet ).
> It is designed for the extreme sharding schema -- one SQLite
> database per user. It supports to Automatic synchronization
> of database structure depending on a create table statement.

Interesting approach - (such a mem-vfs offers some nice
options at the serverside, once it is done).

Did you already tested, how the whole approach behaves
in concurrency-scenarios with e.g. only 4-8 concurrent clients?
Using your schema, especially the concurrent writes should get
a nice boost.

I imagine a simple script, which is executed by each client-
process and could fill-up a fresh DB to a certain amount of
records in a single table maybe (to keep the test-scenario simple).

Each client could do (with a 1:10 relation of writes and reads)

Loop 1000 times
With LoopCounter Modulo 7 (case-switch)
case 0: Insert one single record
case 1: Insert 10 records
case 2: Insert 100 records
case 3: Update one single Record
case 4: Update 10 affected Records
case 5: Update 100 affected Record
case 6: Delete one single Record
End of WritePart

Followed by 10 different Read-Jobs,
each requesting a completely delivered
resultset, based on different where-clauses, etc.
reporting only the recordcount to the console or
into a file, together with the current timing

(maybe fill up two different tables - and also include
 some Joins for the read-direction-jobs)
End loop

Ending up with 11 new records in the DB
(or just redefine the loop-counter for more).

Final Job for each client then a good matching single-
record-select with appropriate aggregates on the
currently contained data in the DB(-tables).

The last returning client should then always deliver the
same (correct) results - the timing then counted on this
last returning client.

Something like that is of course a very simplified
concurrency-scenario(-test) - a result-comparison would
be interesting nonetheless - and such a small schema
shouldn't be that difficult to implement over different
languages (working against different backends).

Maybe there's already something simple available,
that defines easy to follow stresstest-requirements,
one is able to implement without too much effort
(maybe for PostgreSQL something like that is already
 online).

Would be interested in such a simple(r) concurrency-
test-definition, in case there already is one ... does
anybody have a link for me?

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I create an automatically generated primary key id column.

[Simon Slavin]

On 14 Jun 2009, at 2:23pm, Kermit Mei wrote:

> I want to create a new table, its primary key is an int 'id' column,  
> but
> I don't want to insert the data by hand, I hope the system can  
> generate
> it by adding id.
>
> For example,
> mytable(id, name, age)
>
> When I insert name and age, the id is generated automatically.

Just don't mention the id column when you insert data:

INSERT INTO mytable (name, age) VALUES ('Frank', 18)

> How can I import data from a file, and SPECIFY the column?

The above might answer your question, but if it doesn't, we probably  
need to know how you are doing your importing: are you writing your  
own software or using a tool we're all familiar with ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I create an automatically generated primary key idcolumn.

[Igor Tandetnik]

Kermit Mei wrote:
> I want to create a new table, its primary key is an int 'id' column,
> but I don't want to insert the data by hand, I hope the system can
> generate it by adding id.

http://sqlite.org/autoinc.html

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How can I create an automatically generated primary key id column.

[Kermit Mei]

Hello, community!

I want to create a new table, its primary key is an int 'id' column, but
I don't want to insert the data by hand, I hope the system can generate
it by adding id.

For example, 
mytable(id, name, age)

When I insert name and age, the id is generated automatically.

And another question about this:
How can I import data from a file, and SPECIFY the column?
For example, how to insert the follow file into mytable which I define
as before:
age   name
18 Frank
16 Niu Talk
16 Jim

Note: the table is defined as (id, age, name),but the file is wrote as
(age name).

How can I do it?

I'm newbie for sqlite, so this may be a easy question, and I 
hope somebody can help me:P

Thanks.
Have Fun!

Kermit

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SPHiveDB: A server for sqlite database.

[stephen liu]

http://code.google.com/p/sphivedb/

SPHiveDB is a server for sqlite database. It use JSON-RPC over HTTP to
expose a network interface to use SQLite database. It supports
combining multiple SQLite databases into one file. It also supports
the use of multiple files ( through Tokyo Cabinet ). It is designed
for the extreme sharding schema -- one SQLite database per user. It
supports to Automatic synchronization of database structure depending
on a create table statement.

The lastest release:
http://sphivedb.googlecode.com/files/sphivedb-0.2.src.tar.gz

The system architecture diagram:
http://lh4.ggpht.com/_ujFk6cBIKlI/SjT1OS--ARI/AJQ/rz7tE60kbYY/s576/sphivedb.jpg

The json packet format:
http://code.google.com/p/sphivedb/wiki/JsonFormat

It also provides a memvfs for sqlite:
http://sphivedb.googlecode.com/files/spmemvfs-0.2.src.tar.gz
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database logic in TRIGGER and CONFLICT, or in software ?

[Simon Slavin]

On 14 Jun 2009, at 12:09pm, Kees Nuyt wrote:

> [useful stuff]

Many thanks for this, which was useful throughout.  I'm still  
interested in anything other people have to say.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_IOERR_READ errors

[list67]

Hello.


I'm seeing SQLITE_IOERR_READ errors in an application that reads from a SQLite 
.db file in one or more threads.? The 3.6.14.2 SQLite Amalgamation was compiled 
with SQLITE_THREADSAFE=1 defined.? Each thread has its own sqlite3* that is 
openned with sqlite3_open_v2 using these flags:? 
SQLITE_OPEN_READONLY|SQLITE_OPEN_FULLMUTEX.? The SELECT statements are prepared 
(in the thread, during each read) using sqlite3_prepare_v2.? The errors occur 
during the sqlite3_step(...) call.? The error is "disk I/O error" and the 
extended error code is SQLITE_IOERR_READ.? The error seems to occur in 
sqlite3.c's winRead(...) function during a ReadFile(...) call.


When I limit the number of "read threads" to 1, GetLastError() reports:
ERROR_NOACCESS
998 (0x3E6) Invalid access to memory location.


When I limit the number of "read threads" to 3, GetLastError() reports:
ERROR_INVALID_USER_BUFFER
1784 (0x6F8) The supplied user buffer is not valid for the requested operation.
This page (http://msdn.microsoft.com/en-us/library/aa365467.aspx) indicates 
that ERROR_INVALID_USER_BUFFER can occur "whenever there are too many 
outstanding asynchronous I/O requests".


If these error messages indicate that I might be using (or building) SQLite 
incorrectly, can you provide any input on what might be incorrect?


Thank you for your time.


??? Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database logic in TRIGGER and CONFLICT, or in software ?

[Kees Nuyt]

On Sat, 13 Jun 2009 23:42:21 +0100, Simon Slavin
 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% ( -- 
hlpforshortname, -- 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