Re: [sqlite] Bi-directional unique

[Darren Duncan]

Black, Michael (IS) wrote:
> I have a need to create a unique bi-directional relationship.
> 
> You can think of it as pairings of people who eat dinner together.
> 
> You can't guarantee that one column is less than the other so there's no win 
> there.

Why can't you have a well-known ordering for your values?  It doesn't have to 
be 
meaningful, and it should be very easy.  Do a sort on the binary 
representations 
if you don't have a better one.  As long as you have that, put the value that 
orders first or equal in the same column all the time.  Then have a unique key 
constraint over the pair of columns.  Problem solved.

> Speed is of the utmost concern here so fast is really important (how many 
> ways can I say that???).
> 
> Is there anything clever here that can be done with indexes or such?

Just what I said will do what you want and it is the simplest solution plus 
most 
efficient in both performance and disk usage.

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

Re: [sqlite] determine SQLITE_MAX_LENGTH or sql_limit/sqlite3_limit

[Dan Kennedy]

On 02/10/2011 01:56 AM, Vannus wrote:
> Zeoslib is reading sqlite field lengths incorrectly, as it checks for
> brackets after the field typename ie. CHAR(123)
> presumably this is only affecting me because I haven't defined field lengths
> in my sqlite3 db.
>
> I don't want to hard-code 1,000,000,000 or 2147483647 in as the field length
> - but how do I check what SQLITE_MAX_LENGTH or the limit imposed by
> sql_limit or sqlite3_limit is?

At http://www.sqlite.org/c3ref/limit.html the third paragraph
has:

   Regardless of whether or not the limit was changed, the
   sqlite3_limit() interface returns the prior value of the limit.
   Hence, to find the current value of a limit without changing it,
   simply invoke this interface with the third parameter set to -1.

Maybe you can use that.

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

Re: [sqlite] Where to find amalgamation source for the 3.6.23.1

[Kevin Benson]

On Wed, Feb 9, 2011 at 8:51 PM, Sam Carleton wrote:

> 3.6.23.1 amalgamation


http://www.*sqlite*.*org*/sqlite-*amalgamation*-*3.6.23.1*.tar.gz

-- 
   --
   --
   ô¿ô¬
K e V i N
   /¯\
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SIGSEGV error when using sqlite3_exec()

[Zaryab M. Munir]

I am using an in-memory dbase in a multi-threaded application and have
the following two questions:
I create  dbase connections by each thread using the API:
{
Sqlite3 *db =3D NULL;
Sqlite3_open(":memory:", );
When I try to use sqlite3_exec( )   I get segmentation faults.
}
Question1:  Can I have multiple connections opened for each thread to
the same in-memory dbase.  According to the documentation, the
sqlite3_open( ) API can be used multiple times to open multiple
connections to the same database, but its not clear is same applies to
in-memory databases too.
Question2:  Why is sqlite3_exec( ) giving Segmentation Fault error:
segfault at 2b90153ac905 rip 2b90153ac905 rsp
7fff95b1d938
error 15
Segmentation fault


Below is my code:
uint32  aim_sql_open (char *ptr, sqlite3 **db)
{

int n;
n = sqlite3_open(ptr,db);

if( n != SQLITE_OK )
{
/* zz get the wt_id */
printf("Error opening database for thread = %d.\n",n);
return (0);
}
else {
/* zz get the wt_id */
printf("Database open for thread = %d ok.\n",n);
}
}
NOTE:  sqlite3_open( ) returns SQLITE_OK and the value of db is updated.

uint32 aim_db_init( ) {
char dbname[10];
char tbname[13];
char statement[760], *db_err;
sqlite3 *db = NULL;
int n;
bzero(dbname,10);
bzero(tbname,13);
bzero(statement,760);
memcpy(dbname,DB_NAME,9);
/* no need for dbname for inmemory dbase */
n = aim_sql_open(":memory:", );
memcpy(tbname,AIM_TABLE_NAME,12);
if(!strcmp(tbname,""))
return(FALSE);
sprintf(statement, "CREATE TABLE %s (%s INTEGER PRIMARY KEY,%s
DATE,%s INTEGER,%s INTEGER,%s INTEGER,%s TEXT,%s TEXT,%s TEXT,%s
INTEGER,%s INTEGER,%s INTEGER,%s INTEGER,%s DATE,%s INTEGER,%s DATE,%s
INTEGER,%s DATE,%s INTEGER,%s DATE,%s INTEGER,%s INTEGER,%s DATE);", 
tbname,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,CO
L13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22);
n = sqlite3_exec(db, statement, NULL, 0, _err);
if( n != SQLITE_OK )
{
printf("Error executing: %s\n",db_err);
sqlite3_free(db_err);
return (FALSE);
}

The backtrace from GDB is as follows:
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x2b7070afe1a0 (LWP 699)]
0x2b706fcc6905 in read () from /usr/lib64/libaimdb.so
(gdb) bt
#0  0x2b706fcc6905 in read () from /usr/lib64/libaimdb.so
#1  0x2b70703a4120 in unixRandomness (NotUsed=3D,
nBuf=, zBuf=3D0x7fff3b203050 "") at
../sqlite3.c:27932
#2  0x2b707035b0f5 in sqlite3_randomness (N=3D4, pBuf=3D)
at ../sqlite3.c:13876
#3  0x2b707035b28f in writeJournalHdr (pPager=3D0x62b788)
at ../sqlite3.c:36096
#4  0x2b707035b4fb in pager_write (pPg=3D0x639e08) at
../sqlite3.c:39815
#5  0x2b7070366195 in sqlite3PagerWrite (pDbPage=3D0x7) at
../sqlite3.c:40151
#6  0x2b7070368466 in newDatabase (pBt=3D0x62b6d8) at
../sqlite3.c:47668
#7  0x2b707037a18c in sqlite3BtreeBeginTrans (p=3D0x62b678, =
wrflag=3D1)
at ../sqlite3.c:47798
#8  0x2b707037fa78 in sqlite3Step (p=3D0x63a988) at =
../sqlite3.c:63062
#9  0x2b7070383803 in sqlite3_step (pStmt=3D)
at ../sqlite3.c:58768
#10 0x2b7070386592 in sqlite3_exec (db=3D0x62b078,
zSql=3D0x7fff3b203b10 "CREATE TABLE aim_db_table (hash4ADandDC =
INTEGER
PRIMARY KEY,entrytime DATE,state INTEGER,bwgain INTEGER,cctimestamp
INTEGER,foreignkeyAD TEXT,popclass TEXT,deviceclass TEXT,vdoBitrate
INTEGER,qualityI"...,
xCallback=3D0, pArg=3D0x0, pzErrMsg=3D0x7fff3b203e38) at
../sqlite3.c:84165
#11 0x2b706fac52ac in aim_db_init () at aim_db_main.c:645
#12 0x0040566d in main ()


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

[sqlite] Where to find amalgamation source for the 3.6.23.1

[Sam Carleton]

I hacked the 3.6.23.1 amalgamation code a while back.  I need to move
those hacks to 3.7.500, but I don't seem to have a copy of the
3.6.23.1 amalgamation code.  Where can I find a copy?

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

Re: [sqlite] Bi-directional unique

[Igor Tandetnik]

On 2/9/2011 7:29 PM, Samuel Adam wrote:
> In pseudo-C:
>
>   int32_t x, y;
>   int64_t xk; /* k lives in the low bits of xk */
>   
>   if (x<  0 || y<  0) ;   /* Return an error. */
>   
>   xk = (int64_t)x<<32 | x^y;
>   /* Weird integer concatenation; yes, precedence is right. */

It seems (1, 2) and (2, 1) would result in distinct xk values, thus 
defeating the point of the exercise. It is again possible to insert two 
pairs that differ only in order.
-- 
Igor Tandetnik

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

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 17:54:37 -0500, Igor Tandetnik   
wrote:

> On 2/9/2011 5:49 PM, Jim Wilcoxson wrote:
>> I assumed you could generate k in a procedural language outside of SQL,  
>> but
>> if you want to do this purely in SQL, I think you can just say:
>>
>> create table t(k int primary key)
>> insert into t values (min(?,?)<<32 | max(?,?))
>>
>> and bind i,j,i,j to the parameters.
>
> Or make it
>
> insert into t values (min(:first,:second)<<32 | max(:first,:second))
>
> and bind two parameters like you always did.

If the order of (i, j) versus (j, i) is unimportant, then this is superior  
on most points to my corrected bit-XOR design.  Normalizing away that  
order may be desired, so discarding it could be an advantage.  But the XOR  
preserves that order, and that order may be part of the data; so it really  
depends on application requirements.  And the XOR could also be done in  
pure SQL with SQLite patched to have a ^ operator.

Due credit:  I of course cribbed from the quoted idea to correct my  
previous error.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 18:51:45 -0500, Samuel Adam   
wrote:

> On Wed, 09 Feb 2011 18:39:14 -0500, David Bicking 
> wrote:
>
>> I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you
>> would fail to insert proper pairs. Or am I missing something?  (At least
>> I assume that the integers are not limited to just 1 2 or 3 as in the
>> examples.
>
> You are right, as xoring on my fingers would have verified.  In polite
> terms, evidently I just demonstrated publicly math as not my forté ||
> today as not my day.  Apologies for the noise.

At risk of worsening my today’s foot-in-mouth syndrome, I believe that the  
following design would keep *some* of the advantages I outlined at cost of  
the following:

(a) It would only work for 31-bit integers (or 32-bit integers with  
additional tricks to store two 32-bit unsigneds in a 64-bit signed).

(b) Increased computational cost.

However, it adds one advantage (in addition to being correct!):

(c) It would collapse the full informational content of "x" and "y" 
into  
the same column "xk" (viz., the all-important INTEGER PRIMARY KEY).  And  
still, no additional indices are required.

In pseudo-C:

int32_t x, y;
int64_t xk; /* k lives in the low bits of xk */

if (x < 0 || y < 0) ;   /* Return an error. */

xk = (int64_t)x<<32 | x^y;
/* Weird integer concatenation; yes, precedence is right. */

If you don’t mind living dangerously, use union or pointer tricks to  
coerce uint32_t x, y into an sqlite3_int64.

Upon the foregoing, only one column ("xk" INTEGER PRIMARY KEY) is required  
to actually store the data.  With the foregoing pseudo-C placed in an SQL  
user function, however, this is also possible:

CREATE TABLE "" (
"xk" INTEGER PRIMARY KEY, "x" INTEGER, "y" INTEGER,
CHECK ("xk" IS compose_xk("x", "y"))
);

I think Shannon bit me before, but he’s on my side now.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g


> Very truly,
>
> Samuel Adam ◊ 
> 763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
> Legal advice from a non-lawyer: “If you are sued, don’t do what the
> Supreme Court of New Jersey, its agents, and its officers did.”
> http://www.youtube.com/watch?v=iT2hEwBfU1g
>
>
>> David
>>
>> On 02/09/2011 05:58 PM, Samuel Adam wrote:
>>> On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS)
>>>   wrote:
>>>
>>>
 I have a need to create a unique bi-directional relationship.

 You can think of it as pairings of people who eat dinner together.

>>> Two questions come to mind:
>>>
>>> (a) Do you trust app-level code to maintain data integrity, or do you
>>> need SQLite to do this?
>>>
>>> (b) How much relational rigor do you need?  Will the values be used  
>>> for
>>> some kind of relational algebra, or is SQLite simply serving as an ACID
>>> reliability layer?
>>>
>>> Since you’ve been considering the bit-math tricks suggested by Mr.
>>> Wilcoxson, the answers to these questions may let you consider some XOR
>>> cleverness.  Unfortunately, I halfway wrote this up before I realized
>>> that
>>> SQLite lacks a ^ operator[1]; so you will need a trivial SQL user
>>> function
>>> and/or app-land code.  Still, with the following, you can store any
>>> pairs
>>> of 63-bit integers>= 0.  In pure SQL:
>>>
>>> CREATE TABLE "" ("k" INTEGER PRIMARY KEY, "x" INTEGER);
>>> -- WRONG: INSERT INTO "" ("k", "x") VALUES (:x ^ :y, :x);
>>> INSERT INTO "" ("k", "x") VALUES (xor(:x, :y), :x);
>>> -- Faster on the app level; you understand.
>>> SELECT "x", xor("k", "x") AS "y" FROM "";
>>>
>>> (Add NOT NULL or CHECK(typeof("x") IS 'integer') and salt to taste.
>>> N.b.,
>>> I *think* the above binding scenario will work but have not tested it.)
>>>
>>> [1] 2009·12·15 thread with reference to ^ patch by Will Clark:
>>> Message-ID:
>>> 
>>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html
>>>
>>> Key points:
>>>
>>> * Pair uniqueness is enforced for free.  At least, I think it’s really
>>> for free because SQLite always requires a unique rowid.  Somebody  
>>> please
>>> correct me if there is any penalty for user-selected rowids, which  
>>> would
>>> make the performance impact nonzero.
>>>
>>> * Order of (x, y) versus (y, x) pairings is preserved.  Sorts on y  
>>> will
>>> be a pain, though.
>>>
>>> * No extra indices are required.
>>>
>>> * I don’t see a reasonable way to stop arbitrary data from being
>>> stuffed
>>> in from 

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 18:39:14 -0500, David Bicking   
wrote:

> I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you
> would fail to insert proper pairs. Or am I missing something?  (At least
> I assume that the integers are not limited to just 1 2 or 3 as in the
> examples.

You are right, as xoring on my fingers would have verified.  In polite  
terms, evidently I just demonstrated publicly math as not my forté ||  
today as not my day.  Apologies for the noise.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g


> David
>
> On 02/09/2011 05:58 PM, Samuel Adam wrote:
>> On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS)
>>   wrote:
>>
>>
>>> I have a need to create a unique bi-directional relationship.
>>>
>>> You can think of it as pairings of people who eat dinner together.
>>>
>> Two questions come to mind:
>>
>>  (a) Do you trust app-level code to maintain data integrity, or do you
>> need SQLite to do this?
>>
>>  (b) How much relational rigor do you need?  Will the values be used for
>> some kind of relational algebra, or is SQLite simply serving as an ACID
>> reliability layer?
>>
>> Since you’ve been considering the bit-math tricks suggested by Mr.
>> Wilcoxson, the answers to these questions may let you consider some XOR
>> cleverness.  Unfortunately, I halfway wrote this up before I realized  
>> that
>> SQLite lacks a ^ operator[1]; so you will need a trivial SQL user  
>> function
>> and/or app-land code.  Still, with the following, you can store any  
>> pairs
>> of 63-bit integers>= 0.  In pure SQL:
>>
>>  CREATE TABLE "" ("k" INTEGER PRIMARY KEY, "x" INTEGER);
>>  -- WRONG: INSERT INTO "" ("k", "x") VALUES (:x ^ :y, :x);
>>  INSERT INTO "" ("k", "x") VALUES (xor(:x, :y), :x);
>>  -- Faster on the app level; you understand.
>>  SELECT "x", xor("k", "x") AS "y" FROM "";
>>
>> (Add NOT NULL or CHECK(typeof("x") IS 'integer') and salt to taste.   
>> N.b.,
>> I *think* the above binding scenario will work but have not tested it.)
>>
>> [1] 2009·12·15 thread with reference to ^ patch by Will Clark:
>> Message-ID:
>> 
>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html
>>
>> Key points:
>>
>>  * Pair uniqueness is enforced for free.  At least, I think it’s really
>> for free because SQLite always requires a unique rowid.  Somebody please
>> correct me if there is any penalty for user-selected rowids, which would
>> make the performance impact nonzero.
>>
>>  * Order of (x, y) versus (y, x) pairings is preserved.  Sorts on y will
>> be a pain, though.
>>
>>  * No extra indices are required.
>>
>>  * I don’t see a reasonable way to stop arbitrary data from being  
>> stuffed
>> in from within SQLite, even with a user function; for although :y is  
>> being
>> bound on INSERT, a CHECK constraint has no way to touch it.  But see  
>> below
>> for a modified table with a different set of tradeoffs.
>>
>>  * Since two small integers XORed will be another small integer, you do
>> not suffer the loss of variable-length integer storage as spoken of by
>> Messrs. Vlasov and Tandetnik.
>>
>>  * XOR is *fast*.  And the number of integers is kept to a bare minimum
>> (for keeping up to 63 bits for each), cutting cache pressure at all
>> levels—from SQLite’s page-cache to the processor caches.  I am no expert
>> in optimization, but the foregoing practically begs to be benchmarked.
>>
>>  * If for some reason you can’t use xor("k", "x") for all your SQL needs
>> (foreign keys come to mind), add another explicit "y" column.  You then
>> lose some of the foregoing advantages.  But then, a trivial (and  
>> probably
>> quite fast) pure-SQL constraint could then be used to enforce some
>> integrity:
>>
>>  CREATE TABLE "" (
>>  "k" INTEGER PRIMARY KEY, "x" INTEGER, "y" INTEGER,
>>  CHECK ("k" IS xor("x", "y")) -- NOT NULL for free!
>>  );
>> i
>>  * If you try to use negative integers, your database will trigger a HCF
>> instruction.  At the cost of some more performance, CHECK("x">= 0 AND
>> xor("k", "x")>= 0) will *partially* solve that.  I say “partially”
>> because per the foregoing, SQLite cannot guarantee that "y" = "k"^"x"
>> unless you use the modified table, anyway.
>>
>> Bear in mind, this suggestion stems from a personal bias toward clever  
>> XOR
>> tricks; at that, I once wrote a set of endian-swab functions with no
>> (explicit) temporary variables, purely using XOR-swap and shifts.  I  
>> found
>> it the most pleasant way to satisfy aliasing rules; yet I am to this day
>> uncertain whether the result 

Re: [sqlite] Multiple clients accessing one DB

[Richard Hipp]

On Wed, Feb 9, 2011 at 6:35 PM, Sam Carleton wrote:

> Ok, the question now is if I should stop the slow walk towards a
> client/server DB and start running...
>

I'm guessing that if you enable WAL you will get all the concurrency you
need and then some.

-- 
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] Bi-directional unique

[David Bicking]

I don't think this will work. xor(6,1) = 7 and xor(4,3) = 7, so you 
would fail to insert proper pairs. Or am I missing something?  (At least 
I assume that the integers are not limited to just 1 2 or 3 as in the 
examples.

David

On 02/09/2011 05:58 PM, Samuel Adam wrote:
> On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS)
>   wrote:
>
>
>> I have a need to create a unique bi-directional relationship.
>>
>> You can think of it as pairings of people who eat dinner together.
>>  
> Two questions come to mind:
>
>   (a) Do you trust app-level code to maintain data integrity, or do you
> need SQLite to do this?
>
>   (b) How much relational rigor do you need?  Will the values be used for
> some kind of relational algebra, or is SQLite simply serving as an ACID
> reliability layer?
>
> Since you’ve been considering the bit-math tricks suggested by Mr.
> Wilcoxson, the answers to these questions may let you consider some XOR
> cleverness.  Unfortunately, I halfway wrote this up before I realized that
> SQLite lacks a ^ operator[1]; so you will need a trivial SQL user function
> and/or app-land code.  Still, with the following, you can store any pairs
> of 63-bit integers>= 0.  In pure SQL:
>
>   CREATE TABLE "" ("k" INTEGER PRIMARY KEY, "x" INTEGER);
>   -- WRONG: INSERT INTO "" ("k", "x") VALUES (:x ^ :y, :x);
>   INSERT INTO "" ("k", "x") VALUES (xor(:x, :y), :x);
>   -- Faster on the app level; you understand.
>   SELECT "x", xor("k", "x") AS "y" FROM "";
>
> (Add NOT NULL or CHECK(typeof("x") IS 'integer') and salt to taste.  N.b.,
> I *think* the above binding scenario will work but have not tested it.)
>
> [1] 2009·12·15 thread with reference to ^ patch by Will Clark:
> Message-ID:
> 
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html
>
> Key points:
>
>   * Pair uniqueness is enforced for free.  At least, I think it’s really
> for free because SQLite always requires a unique rowid.  Somebody please
> correct me if there is any penalty for user-selected rowids, which would
> make the performance impact nonzero.
>
>   * Order of (x, y) versus (y, x) pairings is preserved.  Sorts on y will
> be a pain, though.
>
>   * No extra indices are required.
>
>   * I don’t see a reasonable way to stop arbitrary data from being stuffed
> in from within SQLite, even with a user function; for although :y is being
> bound on INSERT, a CHECK constraint has no way to touch it.  But see below
> for a modified table with a different set of tradeoffs.
>
>   * Since two small integers XORed will be another small integer, you do
> not suffer the loss of variable-length integer storage as spoken of by
> Messrs. Vlasov and Tandetnik.
>
>   * XOR is *fast*.  And the number of integers is kept to a bare minimum
> (for keeping up to 63 bits for each), cutting cache pressure at all
> levels—from SQLite’s page-cache to the processor caches.  I am no expert
> in optimization, but the foregoing practically begs to be benchmarked.
>
>   * If for some reason you can’t use xor("k", "x") for all your SQL needs
> (foreign keys come to mind), add another explicit "y" column.  You then
> lose some of the foregoing advantages.  But then, a trivial (and probably
> quite fast) pure-SQL constraint could then be used to enforce some
> integrity:
>
>   CREATE TABLE "" (
>   "k" INTEGER PRIMARY KEY, "x" INTEGER, "y" INTEGER,
>   CHECK ("k" IS xor("x", "y")) -- NOT NULL for free!
>   );
> i
>   * If you try to use negative integers, your database will trigger a HCF
> instruction.  At the cost of some more performance, CHECK("x">= 0 AND
> xor("k", "x")>= 0) will *partially* solve that.  I say “partially”
> because per the foregoing, SQLite cannot guarantee that "y" = "k"^"x"
> unless you use the modified table, anyway.
>
> Bear in mind, this suggestion stems from a personal bias toward clever XOR
> tricks; at that, I once wrote a set of endian-swab functions with no
> (explicit) temporary variables, purely using XOR-swap and shifts.  I found
> it the most pleasant way to satisfy aliasing rules; yet I am to this day
> uncertain whether the result qualifies as abstract art.
>
> P.S.:  Consider the foregoing a real-life use case in support of adding a
> bitwise ^ operator to SQLite.
>
> Very truly,
>
> Samuel Adam ◊
> 763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
> Legal advice from a non-lawyer: “If you are sued, don’t do what the
> Supreme Court of New Jersey, its agents, and its officers did.”
> http://www.youtube.com/watch?v=iT2hEwBfU1g
>
>
>
>> create table t(i int, j int);
>>
>> insert into t(1,2);
>>
>> insert into t(2,1);<<  should give an error because the pairing of 1-2
>> already exists.
>>
>> insert into t(3,2);<<  OK
>>
>> insert into t(3,1);<<  OK
>>
>> insert into 

Re: [sqlite] Multiple clients accessing one DB

[Sam Carleton]

On Wed, Feb 9, 2011 at 1:46 PM, Igor Tandetnik  wrote:
>
> On 2/9/2011 1:42 PM, Sam Carleton wrote:
>
> > It is my understanding that SQLite is designed to allow multiple clients
> > from the same computer to access the DB file at one time.
>
> Yes. But if one of those clients starts to write, others cannot write or
> read.

This I am aware of, but I was under the impression the on a modern
desktop machine, the overhead in of the write is pretty minimal,
assuming you are not writing very much.

>
> > The system does far more reading then writing.
>
> But apparently, it still does some writing.

Yep, it does do writing, both programs do.

Ok, the question now is if I should stop the slow walk towards a
client/server DB and start running...  I am working on enhancing the
Qt application quite a bit, doing a lot more DB reading/writing.  Once
I got that done, I was going to convert from SQLite to a client/server
DB.  Do I have my priorities backwards, should I do the DB conversion
first?  The Apache server is services between 3 and 50 active clients
on a local network.

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

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 13:12:32 -0500, Black, Michael (IS)  
 wrote:

> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.

Two questions come to mind:

(a) Do you trust app-level code to maintain data integrity, or do you  
need SQLite to do this?

(b) How much relational rigor do you need?  Will the values be used for 
 
some kind of relational algebra, or is SQLite simply serving as an ACID  
reliability layer?

Since you’ve been considering the bit-math tricks suggested by Mr.  
Wilcoxson, the answers to these questions may let you consider some XOR  
cleverness.  Unfortunately, I halfway wrote this up before I realized that  
SQLite lacks a ^ operator[1]; so you will need a trivial SQL user function  
and/or app-land code.  Still, with the following, you can store any pairs  
of 63-bit integers >= 0.  In pure SQL:

CREATE TABLE "" ("k" INTEGER PRIMARY KEY, "x" INTEGER);
-- WRONG: INSERT INTO "" ("k", "x") VALUES (:x ^ :y, :x);
INSERT INTO "" ("k", "x") VALUES (xor(:x, :y), :x);
-- Faster on the app level; you understand.
SELECT "x", xor("k", "x") AS "y" FROM "";

(Add NOT NULL or CHECK(typeof("x") IS 'integer') and salt to taste.  N.b.,  
I *think* the above binding scenario will work but have not tested it.)

[1] 2009·12·15 thread with reference to ^ patch by Will Clark:
Message-ID:  

http://www.mail-archive.com/sqlite-users@sqlite.org/msg49112.html

Key points:

* Pair uniqueness is enforced for free.  At least, I think it’s really  
for free because SQLite always requires a unique rowid.  Somebody please  
correct me if there is any penalty for user-selected rowids, which would  
make the performance impact nonzero.

* Order of (x, y) versus (y, x) pairings is preserved.  Sorts on y will 
 
be a pain, though.

* No extra indices are required.

* I don’t see a reasonable way to stop arbitrary data from being 
stuffed  
in from within SQLite, even with a user function; for although :y is being  
bound on INSERT, a CHECK constraint has no way to touch it.  But see below  
for a modified table with a different set of tradeoffs.

* Since two small integers XORed will be another small integer, you do  
not suffer the loss of variable-length integer storage as spoken of by  
Messrs. Vlasov and Tandetnik.

* XOR is *fast*.  And the number of integers is kept to a bare minimum  
(for keeping up to 63 bits for each), cutting cache pressure at all  
levels—from SQLite’s page-cache to the processor caches.  I am no expert  
in optimization, but the foregoing practically begs to be benchmarked.

* If for some reason you can’t use xor("k", "x") for all your SQL needs 
 
(foreign keys come to mind), add another explicit "y" column.  You then  
lose some of the foregoing advantages.  But then, a trivial (and probably  
quite fast) pure-SQL constraint could then be used to enforce some  
integrity:

CREATE TABLE "" (
"k" INTEGER PRIMARY KEY, "x" INTEGER, "y" INTEGER,
CHECK ("k" IS xor("x", "y")) -- NOT NULL for free!
);
i
* If you try to use negative integers, your database will trigger a HCF 
 
instruction.  At the cost of some more performance, CHECK("x" >= 0 AND  
xor("k", "x") >= 0) will *partially* solve that.  I say “partially”  
because per the foregoing, SQLite cannot guarantee that "y" = "k"^"x"  
unless you use the modified table, anyway.

Bear in mind, this suggestion stems from a personal bias toward clever XOR  
tricks; at that, I once wrote a set of endian-swab functions with no  
(explicit) temporary variables, purely using XOR-swap and shifts.  I found  
it the most pleasant way to satisfy aliasing rules; yet I am to this day  
uncertain whether the result qualifies as abstract art.

P.S.:  Consider the foregoing a real-life use case in support of adding a  
bitwise ^ operator to SQLite.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g


> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1); << should give an error because the pairing of 1-2  
> already exists.
>
> insert into t(3,2); << OK
>
> insert into t(3,1); << OK
>
> insert into t(1,3); << should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no  
> win there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how  
> many ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior 

Re: [sqlite] Bi-directional unique

[Igor Tandetnik]

On 2/9/2011 5:49 PM, Jim Wilcoxson wrote:
> I assumed you could generate k in a procedural language outside of SQL, but
> if you want to do this purely in SQL, I think you can just say:
>
> create table t(k int primary key)
> insert into t values (min(?,?)<<32 | max(?,?))
>
> and bind i,j,i,j to the parameters.

Or make it

insert into t values (min(:first,:second)<<32 | max(:first,:second))

and bind two parameters like you always did.
-- 
Igor Tandetnik

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

Re: [sqlite] Bi-directional unique

[Jim Wilcoxson]

On Wed, Feb 9, 2011 at 5:25 PM, Simon Slavin  wrote:

>
> On 9 Feb 2011, at 7:32pm, Black, Michael (IS) wrote:
>
> > Didn't we just determine a couple of days ago that triggers were
> performance killers?
> >
> > That's one reason I want to avoid those.
>
> Okay, then since your program knows the logic of how that table will be
> used, it can do it for itself.  Just do two INSERTS.
>
> Alternatively, if your language is capable of it, simply well-order the two
> personIDs, so that the one in one column is always smaller than the one in
> the other column.  Then change your SELECT logic so that it returns the
> UNION of SELECTing on both columns.
>
> One system speeds up the INSERTs, the other speeds up the SELECTs.  It
> depends which is the  more important to you.
>

I assumed you could generate k in a procedural language outside of SQL, but
if you want to do this purely in SQL, I think you can just say:

create table t(k int primary key)
insert into t values (min(?,?)<<32 | max(?,?))

and bind i,j,i,j to the parameters.

For the select, same thing:

select * from t where k=min(?,?)<<32 | max(?,?)
and bind i,j,i,j

I don't see the need to do 2 selects or 2 inserts, but maybe I'm not
understanding something.

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

Re: [sqlite] Bi-directional unique

[Simon Slavin]

On 9 Feb 2011, at 7:32pm, Black, Michael (IS) wrote:

> Didn't we just determine a couple of days ago that triggers were performance 
> killers?
> 
> That's one reason I want to avoid those.

Okay, then since your program knows the logic of how that table will be used, 
it can do it for itself.  Just do two INSERTS.

Alternatively, if your language is capable of it, simply well-order the two 
personIDs, so that the one in one column is always smaller than the one in the 
other column.  Then change your SELECT logic so that it returns the UNION of 
SELECTing on both columns.

One system speeds up the INSERTs, the other speeds up the SELECTs.  It depends 
which is the  more important to you.

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

Re: [sqlite] Bi-directional unique

[Igor Tandetnik]

On 2/9/2011 4:36 PM, Black, Michael (IS) wrote:
> This then begs the question of what happens when an update needs a bigger int?

Same thing that happens when an update to a text field inserts longer text.
-- 
Igor Tandetnik

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

Re: [sqlite] Bi-directional unique

[Ian Hardingham]

My apologies if this is stupid, or it's already been discussed.

There's a way of choosing an ordering on anything, even strings.

Have two tables - one where members of column A are "larger", one where 
members of column B are "larger".  Only insert into the correct table 
(O(1) operation).  Insert with a time and when doing the select choose 
the earlier time.

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

Re: [sqlite] Bi-directional unique

[Black, Michael (IS)]

I like this one...but I tested it a bit and the bad part is that doing a select 
on t requires a complete table scan.  So finding these values is too slow.
I did have to modify a bit to get it to work correctly...now I'll try one of 
the other suggestions.

sqlite> create table t (nm integer primary key);
sqlite> create view tv as select (nm & 1073741823) as n, nm >> 30 as m from t;
sqlite> create trigger tv_ins instead of insert on tv begin
   ...> select case when typeof(NEW.n) != 'integer' then raise(fail,'n is not 
an integer') end;
   ...> select case when typeof(NEW.m) != 'integer' then raise(fail,'m is not 
an integer') end;
   ...> select case when NEW.m > (1 << 30) then raise(fail, 'n is too large') 
end;
   ...> select case when NEW.n < 0 then raise(fail, 'n must be non-negative') 
end;
   ...> select case when NEW.m < 0 then raise(fail, 'm must be non-negative') 
end;
   ...> select case (select count(*) from t where (nm = (NEW.m << 30) | NEW.n) 
or (nm = (New.n << 3
) | New.m)) when 1 then raise(fail, 'This relation already exists!') end;
   ...> insert into t select New.n | (New.m << 30);
   ...> end;
sqlite> insert into tv (n,m) values(1,2);
sqlite> insert into tv (n,m) values(1,2);
Error: This relation already exists!
sqlite> insert into tv(n,m) values(3,1);
sqlite> insert into tv(n,m) values(3,2);
sqlite> insert into tv(n,m) values(5,4);
sqlite> insert into tv(n,m) values(4,5);
Error: This relation already exists!
sqlite> select * from tv;
3|1
1|2
3|2
5|4

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Nico Williams [n...@cryptonector.com]
Sent: Wednesday, February 09, 2011 2:28 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Bi-directional unique

On Wed, Feb 9, 2011 at 2:00 PM, Nico Williams  wrote:
> This way an insert into t requires one lookup and update in t_idx and
> one lookup and update in the table's implied rowid index.  I.e., twice
> the work of an insert without this additional index and trigger.

Also, you get double the DB size this way.

Using Jim's suggestion you get to avoid those problems:

sqlite> create table t (nm integer primary key);
sqlite> create view tv as select nm >> 30 as n, (nm & (1 << 30)) >> 30
as m from t;
sqlite> create trigger tv_ins instead of insert on tv begin
   ...> select case when typeof(NEW.n) != 'integer' then raise(fail,
'n is not an integer') end;
   ...> select case when typeof(NEW.m) != 'integer' then raise(fail,
'm is not an integer') end;
   ...> select case when NEW.n > (1 << 30) then raise(fail, 'n is too
large') end;
   ...> select case when NEW.m > (1 << 30) then raise(fail, 'm is too
large') end;
   ...> select case when NEW.n < 0 then raise(fail, 'n must be
non-negative') end;
   ...> select case when NEW.m < 0 then raise(fail, 'm must be
non-negative') end;
   ...> select case (select count(*) from t where nm = NEW.n << 30 |
NEW.m) when 1 then raise(fail, 'This relation already exists!') end;
   ...> insert into t select NEW.m << 30 | NEW.n;
   ...> end;
sqlite> insert into tv (n, m) values (1, 2);
sqlite> insert into tv (n, m) values (1, 2);
Error: PRIMARY KEY must be unique
sqlite> insert into tv (n, m) values (3, 1);
sqlite> insert into tv (n, m) values (3, 2);
sqlite> insert into tv (n, m) values (1, 3);
Error: This relation already exists!
sqlite>

This still requires two rowid index lookups for every insert, but only
one update.

Nico
--
___
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] Bi-directional unique

[Black, Michael (IS)]

This then begs the question of what happens when an update needs a bigger int?
Doesn't that cause a fair bit more overhead than just keeping it 4 bytes?  
Fragging the database?
I suppose for embedded use that might be important for most applications 
needing 2 bytes or less usually.


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Wednesday, February 09, 2011 3:19 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] EXT :Re: Bi-directional unique

On 2/9/2011 4:13 PM, Black, Michael (IS) wrote:
> And what's the advantage of packing like this?

SQLite uses variable-length encoding for integers - smaller values
(nearer to zero, to be precise) get packed into fewer bytes. So if you
have two 1 values to store, combining them in the way Max Vlasov sugests
would produce 2 (takes one byte in the database) rather than 0x00010001
(takes three, maybe four bytes).

> So what if you're storing 0x0001 and 0x0001.
> Same space

No, not the same space. Whether the benefit is worth the complexity is a
question, of course.
--
Igor Tandetnik

___
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] EXT :Re: Bi-directional unique

[Igor Tandetnik]

On 2/9/2011 4:13 PM, Black, Michael (IS) wrote:
> And what's the advantage of packing like this?

SQLite uses variable-length encoding for integers - smaller values 
(nearer to zero, to be precise) get packed into fewer bytes. So if you 
have two 1 values to store, combining them in the way Max Vlasov sugests 
would produce 2 (takes one byte in the database) rather than 0x00010001 
(takes three, maybe four bytes).

> So what if you're storing 0x0001 and 0x0001.
> Same space

No, not the same space. Whether the benefit is worth the complexity is a 
question, of course.
-- 
Igor Tandetnik

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

Re: [sqlite] EXT :Re: Bi-directional unique

[Black, Michael (IS)]

And what's the advantage of packing like this?
It would seem all you do is add a lot more shifts to insert/retrieve the value. 
 I don't see the benefit.

So what if you're storing 0x0001 and 0x0001.
Same space, only one shift and an or/and to insert/retrieve necessary.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Max Vlasov [max.vla...@gmail.com]
Sent: Wednesday, February 09, 2011 3:06 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXT :Re: Bi-directional unique

Michael, another small improvement... to take advantage of effective integer
packing you can encode two numbers interleaving them dedicating odd bits to
one and even to another. This will require extra steps in the program so
this is your turn to decide worth it or not.

Max


On Wed, Feb 9, 2011 at 10:35 PM, Black, Michael (IS)  wrote:

> I think I can get away with 31 bits (I'll have to add some error checking
> just in case).
>
> I like this idea as it collapses into a single field that is easily
> indexed.
>
> ...
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Jim Wilcoxson [pri...@gmail.com]
>
>
>
If you don't care about the order and the integers are smallish, like 31
> bits or less, I'd do this:
>
> create table t(k int primary key);
>
> i = whatever
> j = whatever
> if i < j:
>   k = i<<32 | j
> else:
>   k = j<<32 | i
> insert k into table
>
>
___
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] EXT :Re: Bi-directional unique

[Max Vlasov]

Michael, another small improvement... to take advantage of effective integer
packing you can encode two numbers interleaving them dedicating odd bits to
one and even to another. This will require extra steps in the program so
this is your turn to decide worth it or not.

Max


On Wed, Feb 9, 2011 at 10:35 PM, Black, Michael (IS)  wrote:

> I think I can get away with 31 bits (I'll have to add some error checking
> just in case).
>
> I like this idea as it collapses into a single field that is easily
> indexed.
>
> ...
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Jim Wilcoxson [pri...@gmail.com]
>
>
>
If you don't care about the order and the integers are smallish, like 31
> bits or less, I'd do this:
>
> create table t(k int primary key);
>
> i = whatever
> j = whatever
> if i < j:
>   k = i<<32 | j
> else:
>   k = j<<32 | i
> insert k into table
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Nico Williams]

On Wed, Feb 9, 2011 at 2:00 PM, Nico Williams  wrote:
> This way an insert into t requires one lookup and update in t_idx and
> one lookup and update in the table's implied rowid index.  I.e., twice
> the work of an insert without this additional index and trigger.

Also, you get double the DB size this way.

Using Jim's suggestion you get to avoid those problems:

sqlite> create table t (nm integer primary key);
sqlite> create view tv as select nm >> 30 as n, (nm & (1 << 30)) >> 30
as m from t;
sqlite> create trigger tv_ins instead of insert on tv begin
   ...> select case when typeof(NEW.n) != 'integer' then raise(fail,
'n is not an integer') end;
   ...> select case when typeof(NEW.m) != 'integer' then raise(fail,
'm is not an integer') end;
   ...> select case when NEW.n > (1 << 30) then raise(fail, 'n is too
large') end;
   ...> select case when NEW.m > (1 << 30) then raise(fail, 'm is too
large') end;
   ...> select case when NEW.n < 0 then raise(fail, 'n must be
non-negative') end;
   ...> select case when NEW.m < 0 then raise(fail, 'm must be
non-negative') end;
   ...> select case (select count(*) from t where nm = NEW.n << 30 |
NEW.m) when 1 then raise(fail, 'This relation already exists!') end;
   ...> insert into t select NEW.m << 30 | NEW.n;
   ...> end;
sqlite> insert into tv (n, m) values (1, 2);
sqlite> insert into tv (n, m) values (1, 2);
Error: PRIMARY KEY must be unique
sqlite> insert into tv (n, m) values (3, 1);
sqlite> insert into tv (n, m) values (3, 2);
sqlite> insert into tv (n, m) values (1, 3);
Error: This relation already exists!
sqlite>

This still requires two rowid index lookups for every insert, but only
one update.

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

Re: [sqlite] Bi-directional unique

[Black, Michael (IS)]

That works too..except you need

create UNIQUE index t_idx on t(m,n);
Otherwise duplicate inserts can exist.

I'll give this one a try too.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Nico Williams [n...@cryptonector.com]
Sent: Wednesday, February 09, 2011 2:00 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Bi-directional unique

sqlite> create table t(n integer not null check (typeof(n) =
'integer'), m integer not null check (typeof(m) = 'integer'));
sqlite> create index t_idx on t (n, m);
sqlite> explain query plan select count(*) != 0 from t t2 where t2.n =
? and t2.m = ?;
0|0|TABLE t AS t2 WITH INDEX t_idx
sqlite> create trigger t_ins before insert on t begin
   ...> select case when (select count(*) != 0 from t t2 where t2.n =
NEW.m and t2.m = NEW.n) then raise(fail, 'That relation is already in
t!') end;
   ...> end;
sqlite> insert into t values (1,2);
sqlite> insert into t values (2,1);
Error: That relation is already in t!
sqlite> insert into t values (3,2);
sqlite> insert into t values (3,1);
sqlite> insert into t values (1,3);
Error: That relation is already in t!
sqlite>

This way an insert into t requires one lookup and update in t_idx and
one lookup and update in the table's implied rowid index.  I.e., twice
the work of an insert without this additional index and trigger.

Nico
--
___
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] Bi-directional unique

[Nico Williams]

sqlite> create table t(n integer not null check (typeof(n) =
'integer'), m integer not null check (typeof(m) = 'integer'));
sqlite> create index t_idx on t (n, m);
sqlite> explain query plan select count(*) != 0 from t t2 where t2.n =
? and t2.m = ?;
0|0|TABLE t AS t2 WITH INDEX t_idx
sqlite> create trigger t_ins before insert on t begin
   ...> select case when (select count(*) != 0 from t t2 where t2.n =
NEW.m and t2.m = NEW.n) then raise(fail, 'That relation is already in
t!') end;
   ...> end;
sqlite> insert into t values (1,2);
sqlite> insert into t values (2,1);
Error: That relation is already in t!
sqlite> insert into t values (3,2);
sqlite> insert into t values (3,1);
sqlite> insert into t values (1,3);
Error: That relation is already in t!
sqlite>

This way an insert into t requires one lookup and update in t_idx and
one lookup and update in the table's implied rowid index.  I.e., twice
the work of an insert without this additional index and trigger.

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

Re: [sqlite] EXT :Re: Bi-directional unique

[Black, Michael (IS)]

I think I can get away with 31 bits (I'll have to add some error checking just 
in case).

I like this idea as it collapses into a single field that is easily indexed.

I'll try doing this the typical way with a query first and then compare with 
this approach (which I expect should be notably faster).

I'll post the results when I'm done.


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jim Wilcoxson [pri...@gmail.com]
Sent: Wednesday, February 09, 2011 1:00 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Bi-directional unique

If you don't care about the order and the integers are smallish, like 31
bits or less, I'd do this:

create table t(k int primary key);

i = whatever
j = whatever
if i < j:
   k = i<<32 | j
else:
   k = j<<32 | i
insert k into table

To see if a pair is in the table, do the same steps and lookup k.

If you do care about the order, you can add k as primary key to the table
layout you mentioned with i and j.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com


On Wed, Feb 9, 2011 at 1:12 PM, Black, Michael (IS)
wrote:

> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1); << should give an error because the pairing of 1-2
> already exists.
>
> insert into t(3,2); << OK
>
> insert into t(3,1); << OK
>
> insert into t(1,3); << should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no
> win there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how many
> ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Black, Michael (IS)]

Didn't we just determine a couple of days ago that triggers were performance 
killers?

That's one reason I want to avoid those.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jim Morris [jmor...@bearriver.com]
Sent: Wednesday, February 09, 2011 12:42 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Bi-directional unique

If you don't care about the order then use and instead of trigger to
force the ordering then you will get the collisions you expect.

On 2/9/2011 10:12 AM, Black, Michael (IS) wrote:
> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1);<<  should give an error because the pairing of 1-2 
> already exists.
>
> insert into t(3,2);<<  OK
>
> insert into t(3,1);<<  OK
>
> insert into t(1,3);<<  should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no win 
> there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how many 
> ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite async

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/08/2011 09:50 PM, Mike Blumenkrantz wrote:
> I have checked out the documentation and found information about
> using sqlite in async mode with threads,

Do you mean this?

  http://www.sqlite.org/asyncvfs.html

> but I am wondering if there is any
> (somewhat) easy way to get direct fd access and maintain asyncronicity without
> threads.

What is wrong with the asyncvfs?  It only needs one thread to do the I/O.
Using aio means that some combination of C library and kernel threads will
be used depending on the platform anyway.

In any event lets say you do implement aio, have you put any thought into
how you would test it?  If you do not thoroughly test it then there will be
potential for data loss or corruption.  Async code is a lot harder to test
and cause all the various combinations of race and error conditions.

Using pre-existing code from the SQLite team means it gets tested like this:

  http://www.sqlite.org/testing.html

How much test code would you need to write to be at the same level?  Or
maybe you can spare one I/O thread after all :-)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk1S6PwACgkQmOOfHg372QQRaACgth621uZN+dDay9d7qvywhV/7
ZAYAoMnWLdpeueMXZw/j2L5p/qlObBvG
=ReyF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Jim Wilcoxson]

If you don't care about the order and the integers are smallish, like 31
bits or less, I'd do this:

create table t(k int primary key);

i = whatever
j = whatever
if i < j:
   k = i<<32 | j
else:
   k = j<<32 | i
insert k into table

To see if a pair is in the table, do the same steps and lookup k.

If you do care about the order, you can add k as primary key to the table
layout you mentioned with i and j.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com


On Wed, Feb 9, 2011 at 1:12 PM, Black, Michael (IS)
wrote:

> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1); << should give an error because the pairing of 1-2
> already exists.
>
> insert into t(3,2); << OK
>
> insert into t(3,1); << OK
>
> insert into t(1,3); << should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no
> win there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how many
> ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
> ___
> 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

[sqlite] determine SQLITE_MAX_LENGTH or sql_limit/sqlite3_limit

[Vannus]

Zeoslib is reading sqlite field lengths incorrectly, as it checks for
brackets after the field typename ie. CHAR(123)
presumably this is only affecting me because I haven't defined field lengths
in my sqlite3 db.

I don't want to hard-code 1,000,000,000 or 2147483647 in as the field length
- but how do I check what SQLITE_MAX_LENGTH or the limit imposed by
sql_limit or sqlite3_limit is?

refs:
http://www.sqlite.org/limits.html#max_length bullet 1
http://www.sqlite.org/c3ref/limit.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple clients accessing one DB

[Igor Tandetnik]

On 2/9/2011 1:42 PM, Sam Carleton wrote:
> Currently I have two and sometimes three clients access the SQLite db, all
> on the same machine.
>
> * A C# program that doesn't ever stay connection all that long.
> * An Apache application that stays connected all the time.
> * A Qt application that stays connected when it is running.
>
> I am getting report that when the Qt application is running, the Apache
> application is crashing.  The only connection is the SQLite db.

This likely means the Apache application doesn't properly handle errors 
it gets from SQLite.

> It is my understanding that SQLite is designed to allow multiple clients
> from the same computer to access the DB file at one time.

Yes. But if one of those clients starts to write, others cannot write or 
read.

> The system does far more reading then writing.

But apparently, it still does some writing.
-- 
Igor Tandetnik

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

[sqlite] Multiple clients accessing one DB

[Sam Carleton]

Currently I have two and sometimes three clients access the SQLite db, all
on the same machine.

* A C# program that doesn't ever stay connection all that long.
* An Apache application that stays connected all the time.
* A Qt application that stays connected when it is running.

I am getting report that when the Qt application is running, the Apache
application is crashing.  The only connection is the SQLite db.

It is my understanding that SQLite is designed to allow multiple clients
from the same computer to access the DB file at one time.  Assuming this to
be true, what is the ideal flags when opening the file?

The system does far more reading then writing.  I am currently using v3.6, I
have not upgraded to v3.7 and WAL, would that also work to my advantage?

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

Re: [sqlite] Bi-directional unique

[Jim Morris]

If you don't care about the order then use and instead of trigger to 
force the ordering then you will get the collisions you expect.

On 2/9/2011 10:12 AM, Black, Michael (IS) wrote:
> I have a need to create a unique bi-directional relationship.
>
> You can think of it as pairings of people who eat dinner together.
>
>
>
> create table t(i int, j int);
>
> insert into t(1,2);
>
> insert into t(2,1);<<  should give an error because the pairing of 1-2 
> already exists.
>
> insert into t(3,2);<<  OK
>
> insert into t(3,1);<<  OK
>
> insert into t(1,3);<<  should be error
>
>
>
> You can't guarantee that one column is less than the other so there's no win 
> there.
>
>
>
> Speed is of the utmost concern here so fast is really important (how many 
> ways can I say that???).
>
>
>
> Is there anything clever here that can be done with indexes or such?
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
> ___
> 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] Bi-directional unique

[Jay A. Kreibich]

On Wed, Feb 09, 2011 at 06:12:32PM +, Black, Michael (IS) scratched on the 
wall:
> I have a need to create a unique bi-directional relationship.

> You can't guarantee that one column is less than the other so there's
> no win there.

  Not sure what you mean by that.  If the values are not equal,
  *everything* sorts somehow.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Simon Slavin]

On 9 Feb 2011, at 6:12pm, Black, Michael (IS) wrote:

> I have a need to create a unique bi-directional relationship.
> 
> You can think of it as pairings of people who eat dinner together.
> 
> 
> 
> create table t(i int, j int);
> 
> insert into t(1,2);
> 
> insert into t(2,1); << should give an error because the pairing of 1-2 
> already exists.
> 
> insert into t(3,2); << OK
> 
> insert into t(3,1); << OK
> 
> insert into t(1,3); << should be error

Make a trigger that when you insert a record into the table inserts one with 
the columns swapped.  Use "INSERT OR FAIL" so you don't get recursive looping.

The other way to do it would be to create a view that views the actual table 
both ways around (try UNION).  That will be faster for creating the data and 
slower for consulting it.

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

Re: [sqlite] database disk image is malformed 3.7.x

[Dan Kennedy]

> I didn't find a way yet to reproduce the issue with a "clean" database.
> Only way I can reproduce it is with some of the database, like the
> test.db3. So I'm running out of ideas.

This is the theory. test.db3 is an auto-vacuum database.

   http://www.sqlite.org/src/info/89b8c9ac54

Dan.

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

[sqlite] Bi-directional unique

[Black, Michael (IS)]

I have a need to create a unique bi-directional relationship.

You can think of it as pairings of people who eat dinner together.



create table t(i int, j int);

insert into t(1,2);

insert into t(2,1); << should give an error because the pairing of 1-2 already 
exists.

insert into t(3,2); << OK

insert into t(3,1); << OK

insert into t(1,3); << should be error



You can't guarantee that one column is less than the other so there's no win 
there.



Speed is of the utmost concern here so fast is really important (how many ways 
can I say that???).



Is there anything clever here that can be done with indexes or such?





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate


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

Re: [sqlite] database disk image is malformed 3.7.x

[Dan Kennedy]

On 02/10/2011 12:10 AM, Dan Kennedy wrote:
> On 02/09/2011 08:17 PM, Dennis Geldhof wrote:
>> I checked some things for the attached database. It is created with
>> sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite
>> wrapper. The header displays (with the ./showdb) version 3.7.4, so it is
>> opened and changed with a database viewer tool. Before executing the
>> "query" the database integrity is OK in both 3.6.23.1 and 3.7.4, after
>> executing it is OK for 3.6.23.1 but malformed for 3.7.4. The "query" is
>> executed using the system.data.sqlite wrapper (sqlite v3.6.23.1). It
>> does not matter if the database was encrypted while executing the
>> "query", the result stays the same.
>
> Thanks for doing this. It sounds like it is worth trying to figure
> out what makes this db special.
>
> Are you able to make the test.db3 database available for download or
> mail it to me directly? This mailing list strips out attachments.

You already did that... My mistake.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN

[Eric Smith]

Fredrik Karlsson wrote:

> package require sqlite3
> sqlite3 db :memory:
> db eval {create table a (id INTEGER);}
> db eval {insert into a values (1);}
> db eval {insert into a values (2);}
> db eval {select * from a where id in (1,3);} vals {parray vals}
> vals(*)  = id
> vals(id) = 1
> set alist [list 1 3]
> 1 3
> db eval {select * from a where id in $alist;} vals {parray vals}
> near "$alist": syntax error
> --

This implies that the manner in which $alist gets expanded should be
sensitive to the SQL context in which the expansion happens (and also,
for the purposes of backward compatibility, to the value of the
variable iteslf).  

Unless I'm mistaken that would require pushing the expansion logic down 
much further into sqlite, and probably would still fail in a number of 
cases.

So I doubt you'll get much traction there, especially since this can be 
pretty easily done from your application.

Here's an option off the top of my head:

proc qSqlList {alistname} {
  set magic_array_name _qSql_${alistname}_arr
  upvar $alistname alist $magic_array_name alist_arr
  #assert {[string is list $alist]} ;# or whatever your infra provides
  array unset alist_arr
  set i 0
  set out [list]
  foreach item $alist {
set alist_arr($i) $item
lappend out \$${magic_array_name}($i)
incr i
  }
  return ([join $out ,])
}

So your call becomes:

db eval "select * from a where id in [qSqlList alist]" vals {parray vals}

SQLite does the expansion on the underlying list values with the proper 
sqlite3_bind_* calls etc.

The proc isn't 100% ideal because:

1. it creates this magic array in the caller's scope (not the prettiest
   thing in the world), and

2. for that reason it disallows dumb copies of the return value to float
   around.  You need to make the sqlite call close to where you do the
   quoting.

Still, it might be good enough for your purposes.  Or maybe you can 
expand on the idea, wrap it up into a cleaner interface, and go from there.

Eric

--
Eric A. Smith

Money is the root of all wealth.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dan Kennedy]

On 02/09/2011 08:17 PM, Dennis Geldhof wrote:
> I checked some things for the attached database. It is created with
> sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite
> wrapper. The header displays (with the ./showdb) version 3.7.4, so it is
> opened and changed with a database viewer tool. Before executing the
> "query" the database integrity is OK in both 3.6.23.1 and 3.7.4, after
> executing it is OK for 3.6.23.1 but malformed for 3.7.4. The "query" is
> executed using the system.data.sqlite wrapper (sqlite v3.6.23.1). It
> does not matter if the database was encrypted while executing the
> "query", the result stays the same.

Thanks for doing this. It sounds like it is worth trying to figure
out what makes this db special.

Are you able to make the test.db3 database available for download or
mail it to me directly? This mailing list strips out attachments.

Dan.





>
> The "query" is used to change some table structures in the database and
> executes the following actions on the database;
> BEGIN TRANSACTION;
>
> ALTER TABLE [A] RENAME TO [TMP_A]
> ALTER TABLE [B] RENAME TO [TMP_B]
>
> CREATE TABLE [A] (); -- where A is a new table, stripped columns
> CREATE TABLE [B] (); -- where B is a new table, stripped columns
> CREATE TABLE [C] (); -- stripped columns
>
> INSERT INTO [A]
> SELECT
>   (SELECT [ColumnA] FROM [D] WHERE [ColumnB] = [TMP_A].[ColumnB]),
>   (SELECT [ColumnC] FROM [TMP_B] WHERE ([ColumnD] =
> [TMP_A].[ColumnD]) AND (NOT [ColumnC] ISNULL) LIMIT 1),
>   -- stripped more columns
> FROM [TMP_A];
>
> -- stripped some more INSERT INTO [] SELECT
> -- stripped some UPDATE
>
> DROP TABLE [TMP_A];
> DROP TABLE [TMP_B];
> DROP TABLE [D];
> DROP TABLE [E];
> DROP TABLE [F];
>
> COMMIT TRANSACTION;
>
>
> When omitting the DROP TABLE [TMP_B], the 3.7.4 does not detect
> corruption directly, until some more updates are executed on the
> database.
>
> When the database before the "query" is .dump-ed to file, and .read into
> a new 3.6.23.1 database, the corruption does not become visible after
> the "query". So it seems to be in the header of that database.
>
> I didn't find a way yet to reproduce the issue with a "clean" database.
> Only way I can reproduce it is with some of the database, like the
> test.db3. So I'm running out of ideas.
>
> Dennis
>
> This message contains confidential information and is intended only for the 
> individual named. If you are not the named addressee you should not 
> disseminate, distribute or copy this e-mail. Please notify the sender 
> immediately by e-mail if you have received this e-mail by mistake and delete 
> this e-mail from your system. E-mail transmission cannot be guaranteed to be 
> secure or error-free as information could be intercepted, corrupted, lost, 
> destroyed, arrive late or incomplete, or contain viruses. The sender 
> therefore does not accept liability for any errors or omissions in the 
> contents of this message, which arise as a result of e-mail transmission. If 
> verification is required please request a hard-copy version.
>   Please consider the environment before printing this email message
> ___
> 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

[sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN

[Fredrik Karlsson]

Dear list,

I find the IN operator quite useful for selecting a set number of things.
However, I often have a Tcl list with the things I want to match
already when I get to the stage there I should issue a SELECT on the
database.
I then paste all the elements of the list together with ',' or just ,
chars and supply that inside a () to the sqlite3 database command.

Would it not be a nice, and safer, addition to the interface to be
able to do something like this (i.e. sot that there would be no syntax
error at the end):

--
package require sqlite3
sqlite3 db :memory:
db eval {create table a (id INTEGER);}
db eval {insert into a values (1);}
db eval {insert into a values (2);}
db eval {select * from a where id in (1,3);} vals {parray vals}
vals(*)  = id
vals(id) = 1
set alist [list 1 3]
1 3
db eval {select * from a where id in $alist;} vals {parray vals}
near "$alist": syntax error
--

Also seems much safer to have a proper binding of values here..

/Fredrik



-- 
"Life is like a trumpet - if you don't put anything into it, you don't
get anything out of it."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite async

[Pavel Ivanov]

> but I am wondering if there is any
> (somewhat) easy way to get direct fd access and maintain asyncronicity without
> threads.

How do you see it's possible? Even if we assume that all files were
opened in async mode and only aio functions were used what would that
mean? You are calling sqlite3_step to change some data, SQLite calls
aio functions to write all changed database pages to disk, then what?
sqlite3_step returns and you continue to do something even though
transaction is not committed yet? When and how should SQLite call
fsync to ensure that journal is on the disk, to ensure that all
changed database pages are on the disk? If you don't care about that
just execute 'pragma synchronous = off' and your OS kernel will do
async writes to disk for you without any user-space threads and
without any changes to SQLite code base.


Pavel

On Wed, Feb 9, 2011 at 12:50 AM, Mike Blumenkrantz  wrote:
> Hi,
>
> I am writing an open source database library, and I would like to add an 
> sqlite
> backend to it. I have checked out the documentation and found information 
> about
> using sqlite in async mode with threads, but I am wondering if there is any
> (somewhat) easy way to get direct fd access and maintain asyncronicity without
> threads.
>
> I have read through the source a bit, and I am guessing that this is
> unlikely to be possible due to the heavy use of mmap, but I thought I would 
> mail
> and ask anyway :)
>
> --
> Mike Blumenkrantz
> Zentific: NULL pointer dereferences now 50% off!
> ___
> 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] database disk image is malformed 3.7.x

[Dennis Geldhof]

I checked some things for the attached database. It is created with
sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite
wrapper. The header displays (with the ./showdb) version 3.7.4, so it is
opened and changed with a database viewer tool. Before executing the
"query" the database integrity is OK in both 3.6.23.1 and 3.7.4, after
executing it is OK for 3.6.23.1 but malformed for 3.7.4. The "query" is
executed using the system.data.sqlite wrapper (sqlite v3.6.23.1). It
does not matter if the database was encrypted while executing the
"query", the result stays the same. 

The "query" is used to change some table structures in the database and
executes the following actions on the database;
BEGIN TRANSACTION;

ALTER TABLE [A] RENAME TO [TMP_A]
ALTER TABLE [B] RENAME TO [TMP_B]

CREATE TABLE [A] (); -- where A is a new table, stripped columns
CREATE TABLE [B] (); -- where B is a new table, stripped columns
CREATE TABLE [C] (); -- stripped columns

INSERT INTO [A]
SELECT
(SELECT [ColumnA] FROM [D] WHERE [ColumnB] = [TMP_A].[ColumnB]),
(SELECT [ColumnC] FROM [TMP_B] WHERE ([ColumnD] =
[TMP_A].[ColumnD]) AND (NOT [ColumnC] ISNULL) LIMIT 1),
-- stripped more columns
FROM [TMP_A];

-- stripped some more INSERT INTO [] SELECT
-- stripped some UPDATE

DROP TABLE [TMP_A];
DROP TABLE [TMP_B];
DROP TABLE [D];
DROP TABLE [E];
DROP TABLE [F];

COMMIT TRANSACTION;


When omitting the DROP TABLE [TMP_B], the 3.7.4 does not detect
corruption directly, until some more updates are executed on the
database.

When the database before the "query" is .dump-ed to file, and .read into
a new 3.6.23.1 database, the corruption does not become visible after
the "query". So it seems to be in the header of that database.

I didn't find a way yet to reproduce the issue with a "clean" database.
Only way I can reproduce it is with some of the database, like the
test.db3. So I'm running out of ideas.

Dennis

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.
 Please consider the environment before printing this email message
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Memory Databases

[Igor Tandetnik]

Bastian Clarenbach  wrote:
> My environment does not have direct file access, instead I can only request
> files and get a memblock returned that contains the entire file.

You should be able to write a virtual file system that reads and writes to a 
block of memory rather than a physical file. See

http://www.sqlite.org/c3ref/vfs_find.html

-- 
Igor Tandetnik

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

Re: [sqlite] SQLite and Memory Databases

[Bastian Clarenbach]

Yes, I expect the database to be small enough.

It is the loading a :memory: database from and storing to memory blocks that
still eludes me.
I will take a look at the backup link.

Thanks!

On 9 February 2011 13:48, Simon Slavin  wrote:

>
> On 9 Feb 2011, at 10:14am, Bastian Clarenbach wrote:
>
> > My environment does not have direct file access, instead I can only
> request
> > files and get a memblock returned that contains the entire file. I am
> trying
> > to figure out how to do one, preferably both, of the following scenarios.
> >
> > 1. I want to create a database 'offline' and then load and use that db as
> a
> > static resource (no inserts or other changes)
> > 2. I want to create a database in memory, store that into a memory block
> and
> > then be able to restore it like in 1
>
> The biggest question with this is whether you expect your entire database
> file to be small enough that you would want to hold it in memory all that
> the same time.  SQLite handles databases in memory just fine: address the
> filename as ':memory:'.  See
>
> http://www.sqlite.org/inmemorydb.html
>
> You can delve into the depths of SQLite and mess with the file system
> routines.  But a more appropriate way to do this might be to use the backup
> API:
>
> http://www.sqlite.org/backup.html
>
> It might be possible to use this to copy your database between the memblock
> and memory or a local file.
>
> Simon.
> ___
> 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] SQLite and Memory Databases

[Simon Slavin]

On 9 Feb 2011, at 10:14am, Bastian Clarenbach wrote:

> My environment does not have direct file access, instead I can only request
> files and get a memblock returned that contains the entire file. I am trying
> to figure out how to do one, preferably both, of the following scenarios.
> 
> 1. I want to create a database 'offline' and then load and use that db as a
> static resource (no inserts or other changes)
> 2. I want to create a database in memory, store that into a memory block and
> then be able to restore it like in 1

The biggest question with this is whether you expect your entire database file 
to be small enough that you would want to hold it in memory all that the same 
time.  SQLite handles databases in memory just fine: address the filename as 
':memory:'.  See

http://www.sqlite.org/inmemorydb.html

You can delve into the depths of SQLite and mess with the file system routines. 
 But a more appropriate way to do this might be to use the backup API:

http://www.sqlite.org/backup.html

It might be possible to use this to copy your database between the memblock and 
memory or a local file.

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

[sqlite] sqlite async

[Mike Blumenkrantz]

Hi,

I am writing an open source database library, and I would like to add an sqlite
backend to it. I have checked out the documentation and found information about
using sqlite in async mode with threads, but I am wondering if there is any
(somewhat) easy way to get direct fd access and maintain asyncronicity without
threads.

I have read through the source a bit, and I am guessing that this is
unlikely to be possible due to the heavy use of mmap, but I thought I would mail
and ask anyway :)

-- 
Mike Blumenkrantz
Zentific: NULL pointer dereferences now 50% off!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite and Memory Databases

[Bastian Clarenbach]

Hi,

My environment does not have direct file access, instead I can only request
files and get a memblock returned that contains the entire file. I am trying
to figure out how to do one, preferably both, of the following scenarios.

1. I want to create a database 'offline' and then load and use that db as a
static resource (no inserts or other changes)
2. I want to create a database in memory, store that into a memory block and
then be able to restore it like in 1

I would really like as few operations on the db on load as possible, so the
usually mentioned  'just ATTACH and mirror the tables over' approach. Also I
do not know if all my troubles could be gone with an implementation of an
VFS for SQLite... but the Pager implementation of in-memory databases looks
quite robust and speedy.

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

Re: [sqlite] database disk image is malformed 3.7.x

[Dennis Geldhof]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: woensdag 9 februari 2011 5:57
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] database disk image is malformed 3.7.x
> 

> The only problem we know of was caused by 3.7.0, which was replaced
> by 3.7.0.1 when the problem was discovered. Even with 3.7.0, you
needed
> the right sequence of writes from 3.7.0 and some earlier version.
> 
> I don't know how the corruption you're seeing is caused. I would like
> to though.

OK. I'll spent time today to try to get a real reproducible example. But
on the moment I have really no clue why some have the issue and some
don't have the issue.

> 
> Do you use auto-vacuum mode? Or incremental vacuum?
> 
> Dan.

I did not set any vacuum-modes (so they are still on the defaults). And
I never VACUUM the database explicitly. 

Dennis

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.
 Please consider the environment before printing this email message
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users