Re: [sqlite] Is there a way to create a foreign key in existing table?

[Peter Haworth]

And remembering that you will lose any indexes or triggers defined for the
table by doing this.

There are plenty of third party tools out there that will take care of
adding/removing constraints to existing tables and a whole lot of other
functions that aren't available in SQLite's DDL, while accounting for all
their secondary effects.

Pete
lcSQL Software 


On Tue, Jan 1, 2013 at 9:00 AM,  wrote:

> Message: 1
> Date: Mon, 31 Dec 2012 10:13:40 -0800
> From: Ward Willats 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Is there a way to create a foreign key in
> existingtable?
> Message-ID: 
> Content-Type: text/plain; charset=us-ascii
>
>
>
> Or, if you want to do it "live:" use ALTER TABLE to rename the existing
> table, CREATE TABLE to make the table with the FK you want, INSERT SELECT
> to bring the records from the renamed table to the new table, and DROP
> TABLE to get rid of the renamed original.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write performance question for 3.7.15

[Dan Frankowski]

Ah, interesting. However, yes, we need production-ready. Good luck with
sqlite4 tho.

On Tue, Jan 1, 2013 at 11:43 AM, Richard Hipp  wrote:

> On Tue, Jan 1, 2013 at 12:33 PM, Dan Frankowski 
> wrote:
>
> >
> > We are comparing to leveldb, which seems to have much better write
> > performance even in a limited-memory situation. Of course it offers much
> > less than sqlite. It is a partially-ordered key/value store, rather than
> a
> > relational database.
> >
>
> The default LSM storage layer for SQLite4 gives much better performance
> than LevelDB on average.  Note that most LevelDB inserts are a little
> faster than LSM, however, every now and then LevelDB encounters a really,
> really slow insert.  SQLite4 LSM avoids these spikes and hence is able to
> perform significantly faster in the long run.  SQLite4 LSM also gives you
> concurrent access and transactions - capabilities that are missing from
> LevelDB.
>
> SQLite4 gives you all the high-level schema and querying capabilities as
> SQLite3, with enhancements.
>
> OTOH, SQLite4 is not anything close to being production ready at this time.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Write performance question for 3.7.15

[Richard Hipp]

On Tue, Jan 1, 2013 at 12:33 PM, Dan Frankowski  wrote:

>
> We are comparing to leveldb, which seems to have much better write
> performance even in a limited-memory situation. Of course it offers much
> less than sqlite. It is a partially-ordered key/value store, rather than a
> relational database.
>

The default LSM storage layer for SQLite4 gives much better performance
than LevelDB on average.  Note that most LevelDB inserts are a little
faster than LSM, however, every now and then LevelDB encounters a really,
really slow insert.  SQLite4 LSM avoids these spikes and hence is able to
perform significantly faster in the long run.  SQLite4 LSM also gives you
concurrent access and transactions - capabilities that are missing from
LevelDB.

SQLite4 gives you all the high-level schema and querying capabilities as
SQLite3, with enhancements.

OTOH, SQLite4 is not anything close to being production ready at this time.


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

Re: [sqlite] Write performance question for 3.7.15

[Dan Frankowski]

I appreciate everyone's thoughts about this.

Knowing larger batch sizes help is interesting. Unfortunately, we don't
always control the batch size. We're using 1000 as an optimistic estimate,
but we receive things and may just have to commit after awhile.

Knowing that more OS file cache or a faster disk helps is also interesting.
Unfortunately, it is non-trivial to switch to SSDs. We will have a whole
fleet of machines, each storing several hundred terabytes. The sqlite
databases are meta-data about that. We might be able to use one SSD just
for the meta-data. We haven't explored that yet. We also can't use lots of
OS disk cache, as it will probably be taken by writing things other than
this meta-data.

Still, all of your observations are useful.

We are comparing to leveldb, which seems to have much better write
performance even in a limited-memory situation. Of course it offers much
less than sqlite. It is a partially-ordered key/value store, rather than a
relational database.

Michael Black writes:

Referencing the C program I sent earlierI've found a COMMIT every 1M
records does best.  I had an extra zero on my 100,000 which gives the EKG
appearance.
I averaged 25,000 inserts/sec over 50M records with no big knees in the
performance (there is a noticeable knee on the commit though around 12M
records).  But the average performance curve is pretty smooth.
Less than that and you're flushing out the index too often which causes an
awful lot of disk thrashing it would seem.
During the 1M commit the CPU drops to a couple % and the disk I/O is pretty
constant...albeit slow

P.S. I'm using 3.7.15.1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite4 Performance

[Michael Black]

Thanks...progress...now we get a SIGBUS later on...

Program received signal SIGBUS, Bus error.
0x0046ce4c in treeShmalloc (pDb=0x6c9728, bAlign=0, nByte=25,
pRc=0x7fffd784) at src/lsm_tree.c:668
668 pNext->iNext = 0;

pRc looks suspiciously like it's ready to overflow

#include 
#include 
#include 
#include 
#include "sqlite4/sqlite4.h"

#define NRECORDS 5000

time_t base_seconds;
suseconds_t base_useconds;

double tic() {
  struct timeval tv;
  double f;
  gettimeofday(,NULL);
  base_seconds=tv.tv_sec;
  base_useconds=tv.tv_usec;
  f= base_seconds + base_useconds/1.0e6;
  return f;
}

// returns time in seconds since tic() was called
double toc() {
  struct timeval tv;
  gettimeofday(,NULL);
  double mark=(tv.tv_sec-base_seconds)+(tv.tv_usec-base_useconds)/1.0e6;
  return mark;
}

void checkrc(sqlite4 *db,int rc,int checkrc,int flag,char *msg,char *str) {
  if (rc != checkrc) {
fprintf(stderr,msg,str);
fprintf(stderr,"%s\n",sqlite4_errmsg(db));
if (flag) { // then fatal
  exit(1);
}
  }
}

int main(int argc, char *argv[]) {
  int rc;
  long i;
  double startTime,stopTime;
  char *sql,*errmsg=NULL;
  char *databaseName="data.db";
  sqlite4 *db;
  sqlite4_env *env=NULL;
  sqlite4_stmt *stmt1,*stmt2;
  remove(databaseName);
  rc =
sqlite4_open(env,"data.db",,SQLITE4_OPEN_READWRITE|SQLITE4_OPEN_CREATE,NU
LL);
  checkrc(db,SQLITE4_OK,rc,1,"Error opening database '%s': ",databaseName);
  //sql = "create table if not exists t_foo (key binary(16) primary key,
value binary(16))";
  sql = "create table if not exists t_foo (key binary(16) , value
binary(16))";
  rc=sqlite4_prepare(db,sql,-1,,NULL);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  rc=sqlite4_step(stmt1);
  checkrc(db,SQLITE4_DONE,rc,1,"Error executing statement '%s': ",sql);
  rc=sqlite4_finalize(stmt1);
  checkrc(db,SQLITE4_OK,rc,1,"Error finalizing statement '%s': ",sql);
  rc=sqlite4_exec(db, "PRAGMA journal_mode=wal",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on WAL mode statement '%s': ",sql);
  rc=sqlite4_exec(db, "PRAGMA synchronous=OFF",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite4_exec(db, "PRAGMA cache_size=8000",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  rc=sqlite4_exec(db, "PRAGMA page_size=4096",NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error on synchronous mode statement '%s':
",sql);
  sql="BEGIN";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  sql = "insert or replace into t_foo(key,value) values(?,?)";
  rc=sqlite4_prepare(db,sql,-1,,NULL);
  checkrc(db,SQLITE4_OK,rc,1,"Error preparing statement '%s': ",sql);
  startTime=tic();
  for(i=0; i<=NRECORDS; ++i) {
char key[16],value[16];
long number = random();
memcpy(key,,8);
memcpy([8],,8);
memcpy(value,,8);
rc=sqlite4_bind_blob(stmt2,1,key,16,SQLITE4_STATIC);
checkrc(db,SQLITE4_OK,rc,1,"Error bind1 statement '%s': ",sql);
rc=sqlite4_bind_blob(stmt2,2,value,16,SQLITE4_STATIC);
checkrc(db,SQLITE4_OK,rc,1,"Error bind2 statement '%s': ",sql);
rc=sqlite4_step(stmt2);
checkrc(db,SQLITE4_DONE,rc,1,"Error finalizing statement '%s': ",sql);
rc=sqlite4_reset(stmt2);
checkrc(db,SQLITE4_OK,rc,1,"Error resetting statement '%s': ",sql);
#if 0
if  (i>0&&(i % 100)==0) {
  sql="COMMIT";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg);
  sql="BEGIN";
  rc=sqlite4_exec(db,sql,NULL,NULL,);
  checkrc(db,SQLITE4_OK,rc,1,"Error executing statement '%s': ",errmsg);
}
#endif
if (i>0 && (i % 10) == 0) {
  //printf("%ld,%g \n",i,10/toc());
  printf("%g \n",10/toc());
  fflush(stdout);
  tic();
}
  }
  rc=sqlite4_finalize(stmt2);
  checkrc(db,SQLITE4_OK,rc,1,"Error finalizing statement '%s': ",sql);
  rc=sqlite4_close(db);
  checkrc(db,SQLITE4_OK,rc,1,"Error closing database'%s': ",databaseName);
  stopTime=tic();
  printf("avg %.0f inserts/sec\n",NRECORDS/(stopTime-startTime));
  return 0;
}

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

Re: [sqlite] How to verify referential integrity of SQLite database

[Richard Hipp]

On Thu, Dec 27, 2012 at 11:43 AM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
lhask...@bloomberg.net> wrote:

> Hi Richard,
>
> Sorry, I missed your message before.
>
> Look, in my example:
>
> SQLite version 3.7.16 2012-12-20 01:15:20
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table p(a, b, primary key(a, b), unique(b, a));
> sqlite> create table c(x, y, foreign key(x, y) references p);
> sqlite> insert into p values (1, 2);
> sqlite> insert into c values (1, 2), (2, 1);
> sqlite> pragma foreign_key_check(c);
> c|2|p|0
> sqlite> pragma foreign_key_list(c);
> 0|0|p|x||NO ACTION|NO ACTION|NONE
> 0|1|p|y||NO ACTION|NO ACTION|NONE
> sqlite> pragma table_info(p);
> 0|a||0||1
> 1|b||0||1
> sqlite> pragma index_list(p);
> 0|sqlite_autoindex_p_2|1
> 1|sqlite_autoindex_p_1|1
>
> No pragma will give me enough info to tell whether fkey implies that c.x =
> p.a and c.y = p.b *or* c.x = p.b and c.y = p.a.
>
> Am I missing something?
>

In the latest code on the SQLite trunk, the table_info pragma has been
enhanced so that the "pk" column (the right-most column) gives the order of
the columns in the primary key.



>
> Thanks,
> - Levi
>
> - Original Message -
> From: d...@sqlite.org
> To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN)
> Cc: sqlite-users@sqlite.org
> At: Dec 20 2012 23:05:20
>
>
>
> On Thu, Dec 20, 2012 at 3:05 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
> lhask...@bloomberg.net> wrote:
>
>> Tested, works perfectly. Thanks!
>>
>> Two questions/observation:
>>
>> 1. Should there be a way to determine the parent key w/o looking at or
>> parsing the schema DDL commands?
>>
>> For example:
>> SQLite version 3.7.16 2012-12-20 01:15:20
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>>
>> sqlite> create table p(a, b, primary key(a, b), unique(b, a));
>> sqlite> create table c(x, y, foreign key(x, y) references p);
>>  sqlite> insert into p values (1, 2);
>> sqlite> insert into c values (1, 2), (2, 1);
>> sqlite> pragma foreign_key_check(c);
>> c|2|p|0
>>
> The fourth column is the foreign_key_id.  If you look at the output of
> PRAGMA foreign_key_list(c), you'll find all the information about parent
> table and the columns that map between parent and child, for that id.
>
>
>>
>> Now I know that the second record is in violation but I don't know what
>> key/index the foreign key actually refers to (and no other combination of
>> existing pragmas will tell me).
>>
>> 2. While I do like your API far better than what I originally proposed, I
>> found that returning no result in case of success may lead to confusion
>> since unknown pragmas behave the same way. So if I run "pragma
>> foreign_key_check;" and get empty result it can mean any of the following:
>>
>> 1. There are no foreign key violations - good!
>> 2. My version of SQLite does not support this pragma yet
>> 3. (In case of using the shell) I made a typo in the pragma name
>>
> The pragma throws an error if you enter the name of a table that does not
> exist.  That handles case 3.  To verify 2, that the version of SQLite you
> are using support foreign_key_check, simply use the name of a table that
> does not exist and verify that you get an error back:
>PRAGMA foreign_key_check('no-such-table');  --- expect an error
>
>
>
>
>
>>
>> While I don't have a better suggestion now, I just wanted to point it out
>> to you.
>>
>> Again thanks a lot!
>> - Levi
>>
>> - Original Message -
>> From: d...@sqlite.org
>> To: LEVI HASKELL (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org
>> At: Dec 19 2012 21:10:52
>>
>>
>>
>> On Wed, Dec 12, 2012 at 2:29 PM, Levi Haskell (BLOOMBERG/ 731 LEXIN) <
>> lhask...@bloomberg.net> wrote:
>>
>>> My suggestion would be to have check_integrity command verify
>>> referential integrity as well only if it's executed while the foreign key
>>> enforcement is enabled on the connection.
>>>
>>
>> The latest SQLite from trunk (not the 3.7.15.1 patch release, but the
>> code that is destined to become 3.7.16) has a new pragma:
>> PRAGMA foreign_key_check;
>> PRAGMA foreign_key_check(TABLE);
>>  The second from checks all of the REFERENCES clauses in TABLE.  The
>> first form checks the keys on all tables in the database.
>>
>>
>> The result of the pragma is a table, with one row per mismatched key.
>> The row contains the name of the child table, the rowid of the child table,
>> the name of the parent table, and the "foreign key index" which is an
>> integer that describes the foreign key in PRAGMA foreign_key_list(CHILD).
>> If the foreign_key_check pragma returns an empty set, that means that all
>> of the keys are correct.
>>
>> PRAGMA foreign_key_check works regardless of whether or not foreign keys
>> are currently enabled or disabled.
>>
>>
>> --
>> D. Richard Hipp
>>
>> d...@sqlite.org
>>
>>
>
> --
> D. Richard Hipp
>
> d...@sqlite.org
>
>


-- 
D. Richard Hipp
d...@sqlite.org
___

Re: [sqlite] SQLite4 Performance

[Dan Kennedy]

On 01/31/2013 11:13 PM, Michael Black wrote:

Do we still get to report bugs?
I checked out the fossil repository
fossil clone http://www.sqlite.org/src4 sqlite4.fossil


I tried my insert test and ran into a problem.
I'm running Redhat 5.7 gcc 4.4.4

This program dies (showing inserts/sec)
123536
113110
110154
107018
105489
100335
100165
100382
100086
99336.9
insert4: src/lsm_shared.c:996: lsmReadlock: Assertion
`(((u32)iShmMax-(u32)iShmMin)<  (1<<30))' failed.
Aborted


Thanks for this. Fixed here:

  http://www.sqlite.org/src4/info/bc0f61fc3b

Dan.


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