Re: [sqlite] DEFAULT values replace explicit NULLs?
On 3/4/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Is the DEFAULT value for a column suppose to replace > an explicit NULL value? Or does the DEFAULT value only > get used if no values for an insert is specified? What > is the correct SQL behavior? > > SQLite does the latter - the DEFAULT value is only used > if no value is given for the column. If you insert an > explicit NULL value then a NULL value is inserted instead > of the DEFAULT value. Ticket #1705 says this is > incorrect. > > Which is right? The current SQLite implementation or > ticket #1705? IMO the NOTNULL keyword should be a clue for this, if the column has DEFAULT value and NOTNULL flag that should be inserted of DEFAULT value if no value is given, if column has no NOTNULL and has DEFAULT value that should be DEFAULT value is inserted otherwise NULL value is inserted. If no value given into the NOTNULL column that should be raised the syntax error. -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] DEFAULT values replace explicit NULLs?
On Fri, 3 Mar 2006, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: Is the DEFAULT value for a column suppose to replace an explicit NULL value? Or does the DEFAULT value only get used if no values for an insert is specified? What is the correct SQL behavior? SQLite does the latter - the DEFAULT value is only used if no value is given for the column. If you insert an explicit NULL value then a NULL value is inserted instead of the DEFAULT value. Ticket #1705 says this is incorrect. Which is right? The current SQLite implementation or ticket #1705? I don't know which is "right" but I certainly have a strong preference. If I explicitly insert a value into a column, it's because I want *that* value inserted -- even if the value I insert is NULL. If I don't insert any value, then I expect the DEFAULT value, if one is specified for the column to be inserted in that column. Derrell Hello, I can only second this statement. I would consider it very counterintuitive to have another values inserted instead of the explicitly specified one. cu, Thomas
Re: [sqlite] DEFAULT values replace explicit NULLs?
Is the DEFAULT value for a column suppose to replace an explicit NULL value? Or does the DEFAULT value only get used if no values for an insert is specified? What is the correct SQL behavior? SQLite does the latter - the DEFAULT value is only used if no value is given for the column. If you insert an explicit NULL value then a NULL value is inserted instead of the DEFAULT value. Ticket #1705 says this is incorrect. FWIW, MS SQL Server 2000 does it the same way as SQLite. Specifically inserting a null results in a null in the table, unless there is a 'not null' constraint on the field, of course, in which case inserting a null generates an error.
Re: [sqlite] DEFAULT values replace explicit NULLs?
> I don't know which is "right" but I certainly have a strong preference. If I > explicitly insert a value into a column, it's because I want *that* value > inserted -- even if the value I insert is NULL. If I don't insert any value, > then I expect the DEFAULT value, if one is specified for the column to be > inserted in that column. That's the behaviour I've gotten from all the databases I've tried.
Re: [sqlite] DEFAULT values replace explicit NULLs?
Default is only supposed to apply on insert, and if no value is specified. If you explicitly insert a null it should be null, not the default. On 3/3/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Is the DEFAULT value for a column suppose to replace > an explicit NULL value? Or does the DEFAULT value only > get used if no values for an insert is specified? What > is the correct SQL behavior? > > SQLite does the latter - the DEFAULT value is only used > if no value is given for the column. If you insert an > explicit NULL value then a NULL value is inserted instead > of the DEFAULT value. Ticket #1705 says this is > incorrect. > > Which is right? The current SQLite implementation or > ticket #1705?
Re: [sqlite] DEFAULT values replace explicit NULLs?
[EMAIL PROTECTED] writes: > Is the DEFAULT value for a column suppose to replace > an explicit NULL value? Or does the DEFAULT value only > get used if no values for an insert is specified? What > is the correct SQL behavior? > > SQLite does the latter - the DEFAULT value is only used > if no value is given for the column. If you insert an > explicit NULL value then a NULL value is inserted instead > of the DEFAULT value. Ticket #1705 says this is > incorrect. > > Which is right? The current SQLite implementation or > ticket #1705? I don't know which is "right" but I certainly have a strong preference. If I explicitly insert a value into a column, it's because I want *that* value inserted -- even if the value I insert is NULL. If I don't insert any value, then I expect the DEFAULT value, if one is specified for the column to be inserted in that column. Derrell
[sqlite] DEFAULT values replace explicit NULLs?
Is the DEFAULT value for a column suppose to replace an explicit NULL value? Or does the DEFAULT value only get used if no values for an insert is specified? What is the correct SQL behavior? SQLite does the latter - the DEFAULT value is only used if no value is given for the column. If you insert an explicit NULL value then a NULL value is inserted instead of the DEFAULT value. Ticket #1705 says this is incorrect. Which is right? The current SQLite implementation or ticket #1705? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Using SQLite on networked drive
Deepak , I simply got it done by maping the shared folder to a drive letter and made the connection persistant. Moreover changed all the inherent script was also changed in php cli program and every this is rocking Best Regards, Vishal Kashyap. http://vishal.net.in On 3/3/06, Deepak Kaul <[EMAIL PROTECTED]> wrote: > I was tasked to use sqlite on a NFS mount on MacOSX 10.4. I had > numerous problems with it because MacOSX does not implement locks > properly. It would run for about 15 minutes or so and then I would > start receiving Database Malformed errors. I had to come up with a > different solution. > > I came up with a scheme where only one process would handle updating the > database directly. All other processes locally or remotely would update > the database through a file hand shaking protocol. > > Here is an example > Database Updater Process (Server) > Database Client Process (Client) > > Server defines two directories (queries and responses). > > Client wants to insert, update or delete data from a database. > 1. client creates a file with the necessary information > 2. client moves file into queries directory > 3. server sees new file in queries directory > 4. server parses file > 5. server inserts, updates or deletes data from database. > > Client wants to select data from a database. > 1. client creates a file with the appropriate sql statement > 2. client moves file into queries directory > 3. server sees new file in queries directory > 4. server parses file > 5. server preforms select statement > 6. server creates response file > 7. server moves response file into response directory > 8. client sees new response file in response directory > 9. client parses file > 10. client obtains data > > This scheme is preferred over sockets because if the database updater > process dies you won't lose information. All inserts, updates and > deletes will be sitting in the queries directory waiting for the > database updater process to start again. > > This is just one solution to work around the NFS problem I was having. > If you find NFS does not work for you I would try either some sort of > sockets implementation or some sort of file hand shaking protocol. > > Vishal Kashyap wrote: > > >Dear Ray , > > > > > > > >>I would be interested in knowing how you handle simulatneous inserts and/or > >>updates... > >> > >> > > > >Their is a possibility of simultaneous selects thats all. Moreover the > >shared drive would be mapped > > > > > >-- > >With Best Regards, > >Vishal Kashyap. > >http://www.vishal.net.in > > > > > > > > > > -- > Software Engineer > [EMAIL PROTECTED] > 301.286.7951 > -- With Best Regards, Vishal Kashyap. http://www.vishal.net.in
Re: [sqlite] Expressions
On Fri, 3 Mar 2006, Roger wrote: >How do i use expressions in SQLite. > >For instance, i want to be able to use Case when i execute my sql so >that i have a row which looks up a value rather than querying it from a >table > If you just want to use CASE to do the lookup with hard coded values, then use something like: SELECT CASE col WHEN 'foo' THEN 'foo value' WHEN 'bar' THEN 'bar value' ELSE 'default value' END FROM atable; Have as many WHEN clauses as you like, and leave the ELSE off which defaults to NULL, I believe. Note, however, that lots of WHEN clauses will take a long time (relatively) to parse, and result in a large compiled statement. You might well be advised to pre-compile such a statement. Note also, that the generated compiled statement does a linear search through the WHEN cases, and will do this search for each and every row, and so this could get quite expensive, as well as being static. What specifically are you trying to achieve? Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] large table performance
"Daniel Franke" <[EMAIL PROTECTED]> wrote: >> > Another question that arose today: > Is there any penalty for switching tables during INSERTs within the > same COMMIT? E.g. > > BEGIN; > INSERT INTO tableA VALUES ...; > INSERT INTO tableB VALUES ...; > INSERT INTO tableA VALUES ...; > INSERT INTO tableB VALUES ...; > : > COMMIT; > > opposed to > > BEGIN; > INSERT INTO tableA VALUES ...; > INSERT INTO tableA VALUES ...; > INSERT INTO tableA VALUES ...; > : > COMMIT; > BEGIN; > INSERT INTO tableB VALUES ...; > INSERT INTO tableB VALUES ...; > INSERT INTO tableB VALUES ...; > : > COMMIT; > > Yesterday I did the former, it seemed to take ages. Today I use the > latter ... it seems to be faster?! > My guess is that locality of reference would make the second approach faster than the first. I would also guess that the resulting database would run queries faster as well. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Re: large table performance
On Fri, 3 Mar 2006, Daniel Franke wrote: > >Another question that arose today: >Is there any penalty for switching tables during INSERTs within the >same COMMIT? E.g. > >BEGIN; >INSERT INTO tableA VALUES ...; >INSERT INTO tableB VALUES ...; >INSERT INTO tableA VALUES ...; >INSERT INTO tableB VALUES ...; > : >COMMIT; > >opposed to > >BEGIN; >INSERT INTO tableA VALUES ...; >INSERT INTO tableA VALUES ...; >INSERT INTO tableA VALUES ...; > : >COMMIT; >BEGIN; >INSERT INTO tableB VALUES ...; >INSERT INTO tableB VALUES ...; >INSERT INTO tableB VALUES ...; > : >COMMIT; > >Yesterday I did the former, it seemed to take ages. Today I use the >latter ... it seems to be faster?! You could probably mix the two, by inserting into tableA all the values required, then inserting into tableB second, all in a single transaction: BEGIN; INSERT INTO tableA VALUES ...; INSERT INTO tableA VALUES ...; INSERT INTO tableA VALUES ...; : INSERT INTO tableB VALUES ...; INSERT INTO tableB VALUES ...; INSERT INTO tableB VALUES ...; : COMMIT; Given the large number of inserts, the above will not save much on the second example, as we're saving a single sequence of synchronous I/O. But it will also keep all the inserts atomic with respect to each other. I think the second is faster due to cache thrashing perhaps? You're switching the page cache from tableA's working set to tableB's working set back and forth. Doing all of tableA's inserts followed by tableB's inserts utilises the cache better. Increasing the cache size may also improve performance: PRAGMA cache_size = ; Another thing to look out for, if generating inserts in roughly the correct index order, create the index after inserting all the values. Inserting in index order will generate worst case index BTree maintenance, as tree nodes will be continually being rebalanced. I think this is why it's quicker to create the index afterwards as suggested in other posts. > > >Many thanks for your replies, everyone =) > >Daniel > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Re: large table performance
On Fri, Mar 03, 2006 at 04:00:58PM +0100, Daniel Franke wrote:
[...]
> > Finally, parsing a few million INSERT statements is very fast
> > in SQLite but it still takes time. You can increase the speed
> > by a factor of 3 or more by using prepared statements, if you
> > are not already.
> As I understand it, sqlite_prepare() and friends will speed up
> statements that can be reused. After the initial parsing of the data
> file(s), all I got is a looong string of INSERT statements. Then, each
> statement is used exactly once.
[...]
The idea is to
prepstm = prepare("insert into table values(?, ?)");
The "?" are vairables. You can assign to them and then run
the "compiled" (prepared) statement once for those values,
then set new values, run it again.
See sqlite3_bind_* and sqlite3_step for further details.
It might speed up your stuff a lot, as sqlite doesn't need
to parse the sql for each insert.
Elrond
[sqlite] Re: large table performance
> > CREATE TABLE genotypes( > > markerid integer NOT NULL REFERENCES marker(id), > > individualid integer NOT NULL REFERENCES individuals(id), > > genA integer, > > genB integer, > > UNIQUE(markerid, individualid)); > > > > CREATE INDEX genotypeidx ON genotypes(markerid, individualid); > > > > [...] So if you are inserting into both a large table > and a large index, the index insertion time dominates. > > In the schema above, you have created two identical indices. Thanks for pointing this out. I actually found this a few hours ago by checking the sqlite_master table. Since I prefer explicit statements over implicit indices, I removed the unique contrained from the table definition and added it to the index as suggested. > We have also found that inserts go faster still if you do the > table inserts first, then after the table is fully populated > create the index. If you are actively using the uniqueness > constraint to reject redundant entries, this approach will not > work for you - you will need to specify the uniqueness constraint > and thus create the index before any data has been inserted. We decided to employ the uniqueness contraint as additional quality measure. It sometimes happens that individuals are assigned multiple genotypes at the same locus or something similar. Adding the index afterwards will detect such errors but it will be quite difficult to find them in the original data files (and to report them back). Instead we opt for another approach: The initial marker files (>= 100.000) are translated into SQL INSERT statements. If the file is valid (not malformed), the statements are written to stdout and piped into another application that reads and passes them to sqlite (for documentation and replay purpose, one could also "tee" to a log). Since all INSERTs are INSERT OR ROLLBACK, the database will never ever be tainted with partially commited input files. If there are errors, the files are checked and INSERTed again en block. > Anything you can do to improve locality of reference while > inserting data into the database will help. If you are > inserting all markers for the same individual at once, then > you will do better to create your index as > > CREATE UNIQUE INDEX idx ON genotypes(individualid, markerid) > > rather than the other way around. That's something to keep in mind. I will give it a try, thanks =) > Finally, parsing a few million INSERT statements is very fast > in SQLite but it still takes time. You can increase the speed > by a factor of 3 or more by using prepared statements, if you > are not already. As I understand it, sqlite_prepare() and friends will speed up statements that can be reused. After the initial parsing of the data file(s), all I got is a looong string of INSERT statements. Then, each statement is used exactly once. Another question that arose today: Is there any penalty for switching tables during INSERTs within the same COMMIT? E.g. BEGIN; INSERT INTO tableA VALUES ...; INSERT INTO tableB VALUES ...; INSERT INTO tableA VALUES ...; INSERT INTO tableB VALUES ...; : COMMIT; opposed to BEGIN; INSERT INTO tableA VALUES ...; INSERT INTO tableA VALUES ...; INSERT INTO tableA VALUES ...; : COMMIT; BEGIN; INSERT INTO tableB VALUES ...; INSERT INTO tableB VALUES ...; INSERT INTO tableB VALUES ...; : COMMIT; Yesterday I did the former, it seemed to take ages. Today I use the latter ... it seems to be faster?! Many thanks for your replies, everyone =) Daniel
Re: [sqlite] large table performance
While talking performance: Did anyone compare sqlite in a simplistic CREATE TABLE t(key BLOB PRIMARY KEY, value BLOB); scenario to other dedicated key/value DBs (like berkeley deb, gdbm, ...)? Elrond
Re: [sqlite] Busy management
On 3/3/06, Ludovic Ferrandis <[EMAIL PROTECTED]> wrote:
> I want to manage the SQLITE_BUSY error like this: If it fails, sleep X
> ms then try the command Y times. I found 2 ways to do it:
I do it using sqlite3_step(); Using bound variables and step eliminates the need
for escaping string data and prevents SQL injection attacks. I retry
the statement
up to 10 times in case another process has locked the database.
Psuedo code looks like this:
// open database
dbOpen();
// Get configuration information for this website instance
string sql = "SELECT blah"
" FROM Setup"
;
// prepare statement instead of building it to avoid sql injection attacks
if ( ! dbPrep( sql ) )
throw ConException( string("Cannot prepare sql: ") + sql +
string(", ") + + sqlite3_errmsg(db) );
bool loop = true;
for ( int i = 0; ( i < 10 ) && ( loop ); i++ )
switch ( dbStep() )
{
// if database busy wait for a short time
// to see if it becomes available
case SQLITE_BUSY:
case SQLITE_LOCKED:
break;
case SQLITE_ROW:
// get results ( 0 based index!!! )
blah = dbColumn( 0 );
break;
case SQLITE_DONE:
if ( CookieUser.empty() )
throw ConException( string("Invalid configuration") );
loop = false;
break;
default:
throw ConException( string("Cannot execute sql: ") + sql );
break;
}
// clean up when finished
dbFinalize();
dbClose();
Re: [sqlite] Using SQLite on networked drive
Deepak Kaul said: > I was tasked to use sqlite on a NFS mount on MacOSX 10.4. I had > numerous problems with it because MacOSX does not implement locks > properly. It would run for about 15 minutes or so and then I would > start receiving Database Malformed errors. I had to come up with a > different solution. > > I came up with a scheme where only one process would handle updating the > database directly. All other processes locally or remotely would update > the database through a file hand shaking protocol. This is pretty ingenious, but I really think that in a situation like this you'd be better off looking at something like PostgreSQL, that can handle multiple network clients. SQLite is a great tool, but it's not the only tool. Use the right tool for the job, and a file-based database is rarely the right tool when network access is needed. Clay Dowling -- Simple Content Management http://www.ceamus.com
Re: [sqlite] Using SQLite on networked drive
I was tasked to use sqlite on a NFS mount on MacOSX 10.4. I had numerous problems with it because MacOSX does not implement locks properly. It would run for about 15 minutes or so and then I would start receiving Database Malformed errors. I had to come up with a different solution. I came up with a scheme where only one process would handle updating the database directly. All other processes locally or remotely would update the database through a file hand shaking protocol. Here is an example Database Updater Process (Server) Database Client Process (Client) Server defines two directories (queries and responses). Client wants to insert, update or delete data from a database. 1. client creates a file with the necessary information 2. client moves file into queries directory 3. server sees new file in queries directory 4. server parses file 5. server inserts, updates or deletes data from database. Client wants to select data from a database. 1. client creates a file with the appropriate sql statement 2. client moves file into queries directory 3. server sees new file in queries directory 4. server parses file 5. server preforms select statement 6. server creates response file 7. server moves response file into response directory 8. client sees new response file in response directory 9. client parses file 10. client obtains data This scheme is preferred over sockets because if the database updater process dies you won't lose information. All inserts, updates and deletes will be sitting in the queries directory waiting for the database updater process to start again. This is just one solution to work around the NFS problem I was having. If you find NFS does not work for you I would try either some sort of sockets implementation or some sort of file hand shaking protocol. Vishal Kashyap wrote: Dear Ray , I would be interested in knowing how you handle simulatneous inserts and/or updates... Their is a possibility of simultaneous selects thats all. Moreover the shared drive would be mapped -- With Best Regards, Vishal Kashyap. http://www.vishal.net.in -- Software Engineer [EMAIL PROTECTED] 301.286.7951
Re: [sqlite] large table performance
Daniel Franke <[EMAIL PROTECTED]> wrote: > > Is there any chance to speed this up? > > CREATE TABLE genotypes( > markerid integer NOT NULL REFERENCES marker(id), > individualid integer NOT NULL REFERENCES individuals(id), > genA integer, > genB integer, > UNIQUE(markerid, individualid)); > > CREATE INDEX genotypeidx ON genotypes(markerid, individualid); > Inserting bulk data into a table is very fast since the insert can be done by a simple append. Inserting data into an index, on the other hand, is slower because the data has to be inserted in order. So if you are inserting into both a large table and a large index, the index insertion time dominates. In the schema above, you have created two identical indices. The clause UNIQUE(markerid, individualid) that appears in the table defintion defines an index on the two fields. Then you turn around and create a second, redundant index on those same two fields. Simply eliminating one or the other of the two indices should increase your performance by a constant factor which approaches 2.0. We have also found that inserts go faster still if you do the table inserts first, then after the table is fully populated create the index. If you are actively using the uniqueness constraint to reject redundant entries, this approach will not work for you - you will need to specify the uniqueness constraint and thus create the index before any data has been inserted. But if your initial bulk insert contains no redundancy, then delaying the index creating until after the insert completes will improve performance. Since you are using INSERT OR ROLLBACK, you can presumably do without the uniqueness constraint during your initial data insert. Then add the constraint by specifying the UNIQUE keyword when you create your index: CREATE UNIQUE INDEX genotypeidx ON ^^ Anything you can do to improve locality of reference while inserting data into the database will help. If you are inserting all markers for the same individual at once, then you will do better to create your index as CREATE UNIQUE INDEX idx ON genotypes(individualid, markerid) rather than the other way around. On the other hand, if you use your database to search by markerid then you will want to use your original ordering, even if it is slower to insert. Finally, parsing a few million INSERT statements is very fast in SQLite but it still takes time. You can increase the speed by a factor of 3 or more by using prepared statements, if you are not already. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] large table performance
>Given the schema below, feeding a million INSERTs into the database by >sqlite3_exec() takes about 30 minutes (this includes transactions, indices >and "PRAGMA synchronous=off" as well as a fully optimized build of sqlite). >Is there any chance to speed this up? Production datasets could easily bring a >billion genotypes ... I assumed from your description that you populate many rows in one shot. If that is the case I recommend that you just create tables without indices and populate the dB with the data. Then create the indices afterwards to improve reading performance. This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] Busy management
Hi,
Newbie to SQLite, so maybe my question will seems trivial.
I want to manage the SQLITE_BUSY error like this: If it fails, sleep X
ms then try the command Y times. I found 2 ways to do it:
- First is to manage a loop like:
tryIt = X;
do
{
err = sqlite3_exec(...)
} while (err == SQLITE_BUSY && tryIt-- && sleep(Y))
-- Alternative (??)
sqlite3_busy_timeout(sqlite3*, Y);
tryIt = X;
do
{
err = sqlite3_exec(...)
} while (err == SQLITE_BUSY && tryIt--)
- The second is to set a busy callback
sqlite3_busy_handler(sqlite3*, foo, void*)
And in
int foo(void*,int count)
{
if (count > X)
return 0;
sleep(Y);
return 1;
}
-
The second solution seems better to me, because we don't have to manage
a loop for each function that can return SQLITE_BUSY (like exec, step or
even close).
In a lot of example, the first solution is often use. Is there any
issues with the second one?
Any advise ?
Thanks.
[sqlite] libsqlite3-dev sources
Hi, where can I find the sources for libsqlite3-dev? I would like o compile and install it my self, because I have no root rights on our suse Linux system. cheers, Alex.
[sqlite] Expressions
How do i use expressions in SQLite. For instance, i want to be able to use Case when i execute my sql so that i have a row which looks up a value rather than querying it from a table
Re: [sqlite] large table performance
Daniel Franke wrote: Hi all. I spent the last days bragging that a single database file as provided by sqlite is a far better approach to store data than -literally- thousands of flat files. Now, I got a small amount of testing data an wow ... I'm stuck. Area: Bioinformatics. Imagine a matrix of data: genetic marker names (attribute A) in columns and individuals (attribute B) in rows. Since the number of features per attribute varies between projects, I decided to create three tables: * Table markers: the genetic markers (attribute A), e.g. 100.000 rows * Table individuals: individual ids (attribute B), e.g. 1.000 rows * Table genotypes: the genetic data Tables "markers" and "individuals" have 2 and 6 columns respectively, a unique primary key, and the (basically) the name of the feature, "genotypes" holds foreign keys to "markers"/"individuals" respectively as well as the genotype column(s), see below. Genotypes are inserted by: INSERT OR ROLLBACK INTO genotypes VALUES ((SELECT id FROM marker WHERE name='$markername$'), (SELECT id FROM individuals WHERE pedigree='$pedigree$' AND person='$person$'), $genA$, $genB$); Where $markername$, ..., $genB$ are replaced with the appropiate values. Given the schema below, feeding a million INSERTs into the database by sqlite3_exec() takes about 30 minutes (this includes transactions, indices and "PRAGMA synchronous=off" as well as a fully optimized build of sqlite). Is there any chance to speed this up? Production datasets could easily bring a billion genotypes ... Any pointer would be appreciated! With kind regards Daniel Franke -- The database schema: CREATE TABLE marker ( id integer PRIMARY KEY AUTOINCREMENT, name varchar UNIQUE); CREATE INDEX markernameidx on marker(name); CREATE TABLE individuals ( id integer PRIMARY KEY AUTOINCREMENT, pedigree varchar NOT NULL, person varchar NOT NULL, father varchar, mother varchar, sex integer NOT NULL, UNIQUE(pedigree, person)); CREATE INDEX individualidx ON individuals (pedigree, person); CREATE TABLE genotypes( markerid integer NOT NULL REFERENCES marker(id), individualid integer NOT NULL REFERENCES individuals(id), genA integer, genB integer, UNIQUE(markerid, individualid)); CREATE INDEX genotypeidx ON genotypes(markerid, individualid); Test with sqlite3 command line shell with transaction(s) That would be a better (an easier to reproduce) test. Regards Boguslaw
