Re: [sqlite] Multiple constraints per table?

[flakpit]

Thank you Igor, the solution below (that you also posted) is what I found in
the forums after hours of searching when I should have been asleep. And
rather that produce shoddy code, I downloaded a proper sqlite tool to verify
that it was all working fine. Thank you for the response, I have learned a
lot from you recently:)

create table t(a, b, unique(a, b));
insert into t values('a', 'b'); -- ok
insert into t values('a', 'c'); -- ok
insert into t values('z', 'b'); -- ok
insert into t values('z', 'b'); -- fails: this pair already exists 
-- 
View this message in context: 
http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18230769.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] rollback/replay journals and durability of the most recent transaction

[Karl Tomlinson]

I've done a little looking into journals, fsyncs, and filesystems recently.

One thing I'm trying to understand is whether there was a reason for SQLite
choosing to use a rollback journal (of the steps to undo a transaction) rather
than a replay journal (of the steps to perform a transaction).

In the same way as SQLite uses the presence of a complete (and consistent)
journal to indicate that a rollback should be performed, the presence of a
complete (and consistent) journal can instead indicate that a replay should
be performed.

One difference between rollback and replay journals is that with a rollback
journal the transaction becomes committed on removal of the journal, but with
a replay journal the transaction commits on completion of the journal.

This difference means that to ensure durability of a commit with a rollback
journal, the parent directory must be synced (after the removal).  I can't see
that this is done in SQLite, which would mean that durability against
filesystem interruption is not obtained for the most recent transaction.
(This is not important to me personally, but some may like to know that, on
return from a commit statement, the transaction is committed to disk.)

The reason that I was considering a replay journal is that for exclusive
access mode (at least - it gets more complicated with multiple connections)
several transactions could be "committed" to the journal before any need to
sync the database file itself.

As a possible further optimization, if some "buffer" was kept containing
(perhaps locations of) pages modified by the transactions in the journal, then
transactions in the journal would not need to be written to the database
itself until the journal was so large as to make maintenance of the buffer too
costly.

This could significantly reduce the frequency of filesystem barrier operations
required for consistency, particularly on filesystems with some sort of
safe-append behavior (even if it's just that unwritten blocks are zeroed out
as on xfs and ntfs).

(If durability of each transaction is desired, then writes to the database
itself might as well commence after each transaction is committed and synced
to the journal, but need not be synced in the database itself until the
size of the journal is such that it should be removed.)

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

Re: [sqlite] blob /transaction wierdness

[smlacc1 leador]

this worked great.  thank you.

On Tue, Jul 1, 2008 at 6:10 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> smlacc1 leador <[EMAIL PROTECTED]> wrote:
> > I'm having some trouble with blobs.  I have 4 blobs tht I want to
> > insert into a db, and it works fine when I execute each insert as a
> > single commit. However, when i try to use transactions to input
> > blocks of 255 inserts, blob 4 gets inputted in the position of blob3,
> > blob 3 in position of blob 2 etc etc.  Very strange.  I've written
> > some code to explain what I mean.  The code takes a long time to
> > finish, so if you want to try it, compile it, then execute for a few
> > seconds before ctrl-c'ing it to quit.  It should give you enough data
> > to see what I mean.  In example 1, the fields are where they should
> > be.  In code 2, the fields all get shifted left by 1 column.
>
> They aren't. It's just that most of your inserts in the inner loops
> actually fail. The regular pattern of failures makes it look like the
> values are shifted. Check the return value of sqlite3_step to see for
> yourself.
>
> You must call sqlite3_reset before you can rebind parameters and step
> again.
>
> 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] blob /transaction wierdness

[Alex Katebi]

Can you update your SQLite to the latest revision?

On Tue, Jul 1, 2008 at 3:42 PM, smlacc1 leador <[EMAIL PROTECTED]> wrote:

> Hi,
>
> I'm having some trouble with blobs.  I have 4 blobs tht I want to insert
> into a db, and it works fine when I execute each insert as a single commit.
> However, when i try to use transactions to input blocks of 255 inserts,
> blob
> 4 gets inputted in the position of blob3, blob 3 in position of blob 2 etc
> etc.  Very strange.  I've written some code to explain what I mean.  The
> code takes a long time to finish, so if you want to try it, compile it,
> then
> execute for a few seconds before ctrl-c'ing it to quit.  It should give you
> enough data to see what I mean.  In example 1, the fields are where they
> should be.  In code 2, the fields all get shifted left by 1 column.  But
> the
> code is identical except for where the "begin" and "commit" statements are
> located.  Anyone know what could be the problem?  Im using sqlite v3, th
> specific version is the one that came with fedora core 8 - 3.4.2.  The
> code
> is in "c".
>
> here are 2 pieces of code - 1 using transactions, 1 without - but otherwise
> identical.
>
> Any help much apreciated.  What I'm trying to acheive would be way too slow
> without transactions.
> 
> #include 
> #include 
> #include 
>
> sqlite3* db;
> sqlite3_stmt *state;
>
> void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
> char c1[1];
> c1[0] = v1;
> char c2[1];
> c2[0] = v2;
> char c3[1];
> c3[0] = v3;
> char c4[1];
> c4[0] = v4;
> sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
> sqlite3_prepare(db,statement,-1,,NULL);
> sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);
>
> sqlite3_step(state);
> sqlite3_finalize(state);
> sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
> }
>
> int main (){
>  sqlite3_open("./blobs.db", );
>  if (db == 0){
>  printf ("database could not be opened.\n");
>  return 1;
>  }
>  char *errmsg;
>  sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
> BLOB)",0,0,);
>  char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
>  int looper1 = 0;
>  int looper2 = 0;
>  int looper3 = 0;
>  int looper4 = 0;
>
>
>  while (looper1 < 256){
>  while (looper2 < 256){
>   while (looper3 < 256){
>  /*  sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
>sqlite3_prepare(db,stata,-1,,NULL);*/
>while (looper4 < 256){
> execblobsql(stata, looper1, looper2, looper3, looper4);
> looper4++;
>}
>looper3++;
>looper4=0;
>  /*  sqlite3_finalize(state);
>sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
>   }
>   looper2++;
>   looper3=0;
>   looper4=0;
>  }
>  looper1++;
>  looper2=0;
>  looper3=0;
>  looper4=0;
>  }
>
> }
>
> 
> #include 
> #include 
> #include 
>
> sqlite3* db;
> sqlite3_stmt *state;
>
> void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
> char c1[1];
> c1[0] = v1;
> char c2[1];
> c2[0] = v2;
> char c3[1];
> c3[0] = v3;
> char c4[1];
> c4[0] = v4;
>  /*sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
> sqlite3_prepare(db,statement,-1,,NULL);*/
> sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
> sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);
>
> sqlite3_step(state);
>  /*sqlite3_finalize(state);
> sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
> }
>
> int main (){
>  sqlite3_open("./blobs.db", );
>  if (db == 0){
>  printf ("database could not be opened.\n");
>  return 1;
>  }
>  char *errmsg;
>  sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
> BLOB)",0,0,);
>  char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
>  int looper1 = 0;
>  int looper2 = 0;
>  int looper3 = 0;
>  int looper4 = 0;
>
>
>  while (looper1 < 256){
>  while (looper2 < 256){
>   while (looper3 < 256){
>sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
>sqlite3_prepare(db,stata,-1,,NULL);
>while (looper4 < 256){
> execblobsql(stata, looper1, looper2, looper3, looper4);
> looper4++;
>}
>looper3++;
>looper4=0;
>sqlite3_finalize(state);
>sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
>   }
>   looper2++;
>   looper3=0;
>   looper4=0;
>  }
>  looper1++;
>  looper2=0;
>  looper3=0;
>  looper4=0;
>  }
>
> }
> ___
> 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] prepackaged sql statement

[Alex Katebi]

Hi Steve,
This is my problem:

create table inet0 (dest, mask, nexthop);
create index inet0_idx on inet0(mask, destination);
insert into inet0 values(x'0100', x'FF00', x'08080808');
insert into inet0 values(x'0101', x'', x'16161616');
insert into inet0 values(x'01010100', x'FF00', x'24242424');
insert into inet0 values(x'01010100', x'FF00', x'');

select * from inet0 where destination=bitand(x'01010101', (select mask from
inet0 where destination=bitand(x'01010101, mask) order by mask desc)) order
by mask desc;

The objective of the above select is to find one or more longest mask
nexthop values.
I want to make a view for the above select and pass in a destination
variable as parameter.

Thanks,
-Alex

On Tue, Jul 1, 2008 at 2:41 PM, Stephen Woodbridge <[EMAIL PROTECTED]>
wrote:

> Alex Katebi wrote:
> > The problem with the view is that you can not pass a parameter or
> variable
> > from the outer select to the views select.
>
> Right that is not the purpose of a view. Think of a view as a virtual
> TABLE. You can not pass a parameter to a table either. You can just
> query the table or view and you CAN query with a parameter in the select
> statement.
>
> select * from  where 
>
> Maybe it would be help to restate what you are trying to do.
>
> -Steve
>
> > On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]>
> > wrote:
> >
> >> "Henrik Bechmann" <[EMAIL PROTECTED]>
> >> wrote in message news:[EMAIL PROTECTED]
> >>> Thanks Igor! And the SQL statement can be bound to parameters in the
> >>> usual ways?
> >> No, not in the view. You can, of course, select from the view (as if it
> >> were a table), and _that_ query can be parameterized.
> >>
> >> 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
>
> ___
> 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] Table Level Locking

[Alex Katebi]

Table level locking is used among statements for the same connection. File
level locking is used among connections. Your case is file level.

On Tue, Jul 1, 2008 at 8:00 PM, Joanne Pham <[EMAIL PROTECTED]> wrote:

> Hi All,
> I read the online document regarding "Table Level Locking" as below:
>  At any one time, a single table may have any number of active
> read-locks or a single active write lock. To read data a table, a connection
> must first obtain a read-lock. To write to a table, a
> connection must obtain a write-lock on that table. If a required table lock
> cannot be obtained, the query fails and SQLITE_LOCKED is
> returned to the caller
> So the question that I had is  while writing the data to table(write lock)
> another process can read the data from same table without any problem?
> Thanks,
> Joanne
>
>
>
> ___
> 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] blob /transaction wierdness

[smlacc1 leador]

ok, I'll try that.  Thanks.

On Tue, Jul 1, 2008 at 6:10 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> smlacc1 leador <[EMAIL PROTECTED]> wrote:
> > I'm having some trouble with blobs.  I have 4 blobs tht I want to
> > insert into a db, and it works fine when I execute each insert as a
> > single commit. However, when i try to use transactions to input
> > blocks of 255 inserts, blob 4 gets inputted in the position of blob3,
> > blob 3 in position of blob 2 etc etc.  Very strange.  I've written
> > some code to explain what I mean.  The code takes a long time to
> > finish, so if you want to try it, compile it, then execute for a few
> > seconds before ctrl-c'ing it to quit.  It should give you enough data
> > to see what I mean.  In example 1, the fields are where they should
> > be.  In code 2, the fields all get shifted left by 1 column.
>
> They aren't. It's just that most of your inserts in the inner loops
> actually fail. The regular pattern of failures makes it look like the
> values are shifted. Check the return value of sqlite3_step to see for
> yourself.
>
> You must call sqlite3_reset before you can rebind parameters and step
> again.
>
> 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

[sqlite] Table Level Locking

[Joanne Pham]

Hi All,
I read the online document regarding "Table Level Locking" as below:
         At any one time, a single table may have any number of active 
read-locks or a single active write lock. To read data a table, a connection    
             must first obtain a read-lock. To write to a table, a connection 
must obtain a write-lock on that table. If a required table lock cannot be      
       obtained, the query fails and SQLITE_LOCKED is returned to the caller
So the question that I had is  while writing the data to table(write lock) 
another process can read the data from same table without any problem?
Thanks,
Joanne


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

Re: [sqlite] blob /transaction wierdness

[Ken]

Yup thats what i was thinking, sqlite3_column. My mistake and thanks for 
catching that!


Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ken  wrote:
> Column numbering for binding starts at 0 Not 1.
> sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);

Not true. Parameters in sqlite3_bind_* are numbered from 1. Columns in 
sqlite3_column_* are numbered from 0.

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] blob /transaction wierdness

[Igor Tandetnik]

smlacc1 leador <[EMAIL PROTECTED]> wrote:
> I'm having some trouble with blobs.  I have 4 blobs tht I want to
> insert into a db, and it works fine when I execute each insert as a
> single commit. However, when i try to use transactions to input
> blocks of 255 inserts, blob 4 gets inputted in the position of blob3,
> blob 3 in position of blob 2 etc etc.  Very strange.  I've written
> some code to explain what I mean.  The code takes a long time to
> finish, so if you want to try it, compile it, then execute for a few
> seconds before ctrl-c'ing it to quit.  It should give you enough data
> to see what I mean.  In example 1, the fields are where they should
> be.  In code 2, the fields all get shifted left by 1 column.

They aren't. It's just that most of your inserts in the inner loops 
actually fail. The regular pattern of failures makes it look like the 
values are shifted. Check the return value of sqlite3_step to see for 
yourself.

You must call sqlite3_reset before you can rebind parameters and step 
again.

Igor Tandetnik 



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

Re: [sqlite] blob /transaction wierdness

[Igor Tandetnik]

Ken <[EMAIL PROTECTED]> wrote:
> Column numbering for binding starts at 0 Not 1.
> sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);

Not true. Parameters in sqlite3_bind_* are numbered from 1. Columns in 
sqlite3_column_* are numbered from 0.

Igor Tandetnik 



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

Re: [sqlite] blob /transaction wierdness

[Ken]

Column numbering for binding starts at 0 Not 1.
 sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);




smlacc1 leador <[EMAIL PROTECTED]> wrote: Hi,

I'm having some trouble with blobs.  I have 4 blobs tht I want to insert
into a db, and it works fine when I execute each insert as a single commit.
However, when i try to use transactions to input blocks of 255 inserts, blob
4 gets inputted in the position of blob3, blob 3 in position of blob 2 etc
etc.  Very strange.  I've written some code to explain what I mean.  The
code takes a long time to finish, so if you want to try it, compile it, then
execute for a few seconds before ctrl-c'ing it to quit.  It should give you
enough data to see what I mean.  In example 1, the fields are where they
should be.  In code 2, the fields all get shifted left by 1 column.  But the
code is identical except for where the "begin" and "commit" statements are
located.  Anyone know what could be the problem?  Im using sqlite v3, th
specific version is the one that came with fedora core 8 - 3.4.2.  The code
is in "c".

here are 2 pieces of code - 1 using transactions, 1 without - but otherwise
identical.

Any help much apreciated.  What I'm trying to acheive would be way too slow
without transactions.

#include 
#include 
#include 

sqlite3* db;
sqlite3_stmt *state;

void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
 char c1[1];
 c1[0] = v1;
 char c2[1];
 c2[0] = v2;
 char c3[1];
 c3[0] = v3;
 char c4[1];
 c4[0] = v4;
 sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
 sqlite3_prepare(db,statement,-1,,NULL);
 sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);

 sqlite3_step(state);
 sqlite3_finalize(state);
 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
}

int main (){
 sqlite3_open("./blobs.db", );
 if (db == 0){
  printf ("database could not be opened.\n");
  return 1;
 }
 char *errmsg;
 sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
BLOB)",0,0,);
 char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
 int looper1 = 0;
 int looper2 = 0;
 int looper3 = 0;
 int looper4 = 0;


 while (looper1 < 256){
  while (looper2 < 256){
   while (looper3 < 256){
  /*  sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
sqlite3_prepare(db,stata,-1,,NULL);*/
while (looper4 < 256){
 execblobsql(stata, looper1, looper2, looper3, looper4);
 looper4++;
}
looper3++;
looper4=0;
  /*  sqlite3_finalize(state);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
   }
   looper2++;
   looper3=0;
   looper4=0;
  }
  looper1++;
  looper2=0;
  looper3=0;
  looper4=0;
 }

}


#include 
#include 
#include 

sqlite3* db;
sqlite3_stmt *state;

void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
 char c1[1];
 c1[0] = v1;
 char c2[1];
 c2[0] = v2;
 char c3[1];
 c3[0] = v3;
 char c4[1];
 c4[0] = v4;
  /*sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
 sqlite3_prepare(db,statement,-1,,NULL);*/
 sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);

 sqlite3_step(state);
 /*sqlite3_finalize(state);
 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
}

int main (){
 sqlite3_open("./blobs.db", );
 if (db == 0){
  printf ("database could not be opened.\n");
  return 1;
 }
 char *errmsg;
 sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
BLOB)",0,0,);
 char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
 int looper1 = 0;
 int looper2 = 0;
 int looper3 = 0;
 int looper4 = 0;


 while (looper1 < 256){
  while (looper2 < 256){
   while (looper3 < 256){
sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
sqlite3_prepare(db,stata,-1,,NULL);
while (looper4 < 256){
 execblobsql(stata, looper1, looper2, looper3, looper4);
 looper4++;
}
looper3++;
looper4=0;
sqlite3_finalize(state);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
   }
   looper2++;
   looper3=0;
   looper4=0;
  }
  looper1++;
  looper2=0;
  looper3=0;
  looper4=0;
 }

}
___
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] blob /transaction wierdness

[Ken]

Column numbering for binding starts at 0 Not 1.
 sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);




smlacc1 leador <[EMAIL PROTECTED]> wrote: Hi,

I'm having some trouble with blobs.  I have 4 blobs tht I want to insert
into a db, and it works fine when I execute each insert as a single commit.
However, when i try to use transactions to input blocks of 255 inserts, blob
4 gets inputted in the position of blob3, blob 3 in position of blob 2 etc
etc.  Very strange.  I've written some code to explain what I mean.  The
code takes a long time to finish, so if you want to try it, compile it, then
execute for a few seconds before ctrl-c'ing it to quit.  It should give you
enough data to see what I mean.  In example 1, the fields are where they
should be.  In code 2, the fields all get shifted left by 1 column.  But the
code is identical except for where the "begin" and "commit" statements are
located.  Anyone know what could be the problem?  Im using sqlite v3, th
specific version is the one that came with fedora core 8 - 3.4.2.  The code
is in "c".

here are 2 pieces of code - 1 using transactions, 1 without - but otherwise
identical.

Any help much apreciated.  What I'm trying to acheive would be way too slow
without transactions.

#include 
#include 
#include 

sqlite3* db;
sqlite3_stmt *state;

void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
 char c1[1];
 c1[0] = v1;
 char c2[1];
 c2[0] = v2;
 char c3[1];
 c3[0] = v3;
 char c4[1];
 c4[0] = v4;
 sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
 sqlite3_prepare(db,statement,-1,,NULL);
 sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);

 sqlite3_step(state);
 sqlite3_finalize(state);
 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
}

int main (){
 sqlite3_open("./blobs.db", );
 if (db == 0){
  printf ("database could not be opened.\n");
  return 1;
 }
 char *errmsg;
 sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
BLOB)",0,0,);
 char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
 int looper1 = 0;
 int looper2 = 0;
 int looper3 = 0;
 int looper4 = 0;


 while (looper1 < 256){
  while (looper2 < 256){
   while (looper3 < 256){
  /*  sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
sqlite3_prepare(db,stata,-1,,NULL);*/
while (looper4 < 256){
 execblobsql(stata, looper1, looper2, looper3, looper4);
 looper4++;
}
looper3++;
looper4=0;
  /*  sqlite3_finalize(state);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
   }
   looper2++;
   looper3=0;
   looper4=0;
  }
  looper1++;
  looper2=0;
  looper3=0;
  looper4=0;
 }

}


#include 
#include 
#include 

sqlite3* db;
sqlite3_stmt *state;

void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
 char c1[1];
 c1[0] = v1;
 char c2[1];
 c2[0] = v2;
 char c3[1];
 c3[0] = v3;
 char c4[1];
 c4[0] = v4;
  /*sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
 sqlite3_prepare(db,statement,-1,,NULL);*/
 sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);

 sqlite3_step(state);
 /*sqlite3_finalize(state);
 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
}

int main (){
 sqlite3_open("./blobs.db", );
 if (db == 0){
  printf ("database could not be opened.\n");
  return 1;
 }
 char *errmsg;
 sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
BLOB)",0,0,);
 char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
 int looper1 = 0;
 int looper2 = 0;
 int looper3 = 0;
 int looper4 = 0;


 while (looper1 < 256){
  while (looper2 < 256){
   while (looper3 < 256){
sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
sqlite3_prepare(db,stata,-1,,NULL);
while (looper4 < 256){
 execblobsql(stata, looper1, looper2, looper3, looper4);
 looper4++;
}
looper3++;
looper4=0;
sqlite3_finalize(state);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
   }
   looper2++;
   looper3=0;
   looper4=0;
  }
  looper1++;
  looper2=0;
  looper3=0;
  looper4=0;
 }

}
___
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] Index and ORDER BY

[Jeff Gibson]

Yes, this has been my experience as well.  I've tried 3.5.6 and 3.5.9.
Jeff

Alexey Pechnikov wrote:
> В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а):
>   
>> On Tue, 1 Jul 2008, Alexey Pechnikov wrote:
>> 
>>> Is any difference between "CREATE INDEX ev_idx ON events(type,eid)"
>>> and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc"
>>> keyword for index?
>>>   
>> The DESC keyword creates the index in descending collation order, rather
>> than ascending order (default). I believe this sort order may not be
>> observed in older versions, but more recent ones do so.
>> 
>
> I'm using SQLite 3.5.9 and there are no differents in my tests between DESC 
> and default indeces. I try create index with keywork DESC for optimize DESC 
> sorting but it don't work for me. My tests you can see above.
> ___
> 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] unresolved external symbol

[Teg]

Hello Mauricio,

Tuesday, July 1, 2008, 3:43:16 PM, you wrote:

MC> Hi list.

MC> I'm currently making modifications to a project, and want to add SQLite3 to
MC> it.

MC> I'm using VS6, have the sqlite3.h and sqlite3.c in the same directory, and
MC> this is the code:

MC> #include "sqlite3.h"

MC> 

MC> bool TestSQLite()
MC> {
MC> sqlite3 *db;
MC> int rc;
MC> rc = sqlite3_open("test.sqlite",);
MC> return true;
MC> }

MC> but when I build, get the following error
MC> Configuration: mfc01 - Win32 Release
MC> Compiling...
MC> mfc01Dlg.cpp
MC> Linking...
MC> mfc01Dlg.obj : error LNK2001: unresolved external symbol _sqlite3_open


MC> However, if I just compile, everything goes ok.

MC> Any help?
MC> ___
MC> sqlite-users mailing list
MC> sqlite-users@sqlite.org
MC> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

You know, you actually have to build SQLite3.c and the linker has to
include it when you link right? It's not enough that they be in the
same folder.

This is pretty basic and I'm sorry if I dumbed it down too much. I'm
just not seeing where you linked it in, in the messages.


-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

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

Re: [sqlite] SQLite and updating VIEWs

[Shawn Wilsher]

On Tue, Jul 1, 2008 at 4:05 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Will it?  part is supposed to
> be false when the data is in fact currently in temp table. A WHERE
> clause that is always false will, naturally, produce no records.
Fair.  I hadn't thought of doing something like that.

>>  because we do not want to delete the data
>> from the temporary table (that involves a write and fsyncing).
>> However, I think I've come up with a solution:
>> CREATE TRIGGER 
>> BEGIN
>>  INSERT OR REPLACE INTO temp_table
>
> REPLACE clause works by deleting a conflicting record then inserting a
> new one. Since you say you don't want to delete records from temp_table,
> I don't quite see what you are gaining.
We don't want to delete from the permanent table because we are trying
to avoid the write and fsync.  Our temporary table is in memory
(although regardless of that, temp tables don't fsync).

> UNION has nothing to do with primary key. It only eliminates duplicate
> records - records with all fields equal.
Hrm, that means I have a whole other problem to solve now :(

Cheers,

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

Re: [sqlite] SQLite and updating VIEWs

[Igor Tandetnik]

Shawn Wilsher <[EMAIL PROTECTED]> wrote:
> On Tue, Jul 1, 2008 at 3:37 PM, Igor Tandetnik
> <[EMAIL PROTECTED]> wrote:
>> They won't fail - they will successfully insert zero records. It's
>> perfectly valid to run INSERT ... SELECT and have the SELECT part
>> produce an empty resultset. It simply does nothing.
>
> Sorry, I should have been more explicit in what we are doing.  The
> select will return results

Will it?  part is supposed to 
be false when the data is in fact currently in temp table. A WHERE 
clause that is always false will, naturally, produce no records.

>  because we do not want to delete the data
> from the temporary table (that involves a write and fsyncing).
> However, I think I've come up with a solution:
> CREATE TRIGGER 
> BEGIN
>  INSERT OR REPLACE INTO temp_table

REPLACE clause works by deleting a conflicting record then inserting a 
new one. Since you say you don't want to delete records from temp_table, 
I don't quite see what you are gaining.

> This works since the UNION will select entries from the temp table
> first, and ignore those in the permanent table that have the same
> primary key (that is, assuming I understand UNION properly).

UNION has nothing to do with primary key. It only eliminates duplicate 
records - records with all fields equal.

Igor Tandetnik 



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

Re: [sqlite] SQLite and updating VIEWs

[Shawn Wilsher]

On Tue, Jul 1, 2008 at 3:37 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> They won't fail - they will successfully insert zero records. It's
> perfectly valid to run INSERT ... SELECT and have the SELECT part
> produce an empty resultset. It simply does nothing.
Sorry, I should have been more explicit in what we are doing.  The
select will return results because we do not want to delete the data
from the temporary table (that involves a write and fsyncing).
However, I think I've come up with a solution:
CREATE TRIGGER 
BEGIN
  INSERT OR REPLACE INTO temp_table
  SELECT * FROM table_view
  WHERE 
  AND ;

  UPDATE temp_table SET ...;
END;

This works since the UNION will select entries from the temp table
first, and ignore those in the permanent table that have the same
primary key (that is, assuming I understand UNION properly).  I'm also
making the assumption that it is valid to query the view that the
trigger is running on.

Is this a sound approach?

Cheers,

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

Re: [sqlite] Index and ORDER BY

[Alexey Pechnikov]

В сообщении от Tuesday 01 July 2008 23:47:50 [EMAIL PROTECTED] написал(а):
> On Tue, 1 Jul 2008, Alexey Pechnikov wrote:
> > Is any difference between "CREATE INDEX ev_idx ON events(type,eid)"
> > and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc"
> > keyword for index?
>
> The DESC keyword creates the index in descending collation order, rather
> than ascending order (default). I believe this sort order may not be
> observed in older versions, but more recent ones do so.

I'm using SQLite 3.5.9 and there are no differents in my tests between DESC 
and default indeces. I try create index with keywork DESC for optimize DESC 
sorting but it don't work for me. My tests you can see above.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index and ORDER BY

[cmartin]

On Tue, 1 Jul 2008, Alexey Pechnikov wrote:

> Is any difference between "CREATE INDEX ev_idx ON events(type,eid)"
> and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword
> for index?

The DESC keyword creates the index in descending collation order, rather 
than ascending order (default). I believe this sort order may not be 
observed in older versions, but more recent ones do so.

Chris Martin
UNC-CH


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

Re: [sqlite] SQLite and updating VIEWs

[Stephen Woodbridge]

Shawn Wilsher wrote:
> Hey all,
> 
> I'm working with a partitioned table setup with a permanent table and
> a temp table with the same columns and indexes.  Every X time we dump
> all the records in the temp table over to the permanent one.  In order
> to make selection queries easier to manage, I've gone and created a
> view like so:
> CREATE TEMPORARY VIEW table_view AS
> SELECT * FROM table_temp
> UNION
> SELECT * FROM table
> 
> This was all going well, until I realized that updating was going to
> be very hard (insertion always goes to the temporary table).  That
> seemed easy enough to manage if I use an INSTEAD OF trigger on the
> view for UPDATE statements.  The problem is what I want to do in the
> trigger, which is this:
> 1) if the data is in the temporary table, update that
> 2) if the data is not in the temporary table, copy the data from the
> permanent table into the temp one, and then update the temp table
> Sadly, my SQL-fu isn't strong enough to know how to do this, and I'm
> starting to wonder if it's even possible.  If someone could tell me if
> I can do it, and then provide a pointer as to how to go about it, I'd
> really appreciate it.

Given a row that you have to work with, your current setup is lacking 
enough info to know which table the row came from in the view. You might 
define you view like:

  CREATE TEMPORARY VIEW table_view AS
  SELECT *, 'T' as which FROM table_temp
  UNION
  SELECT *, 'P' as which FROM table

Now in your trigger you can check "which" and know if you need to copy 
the row to table_temp.

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

[sqlite] unresolved external symbol

[Mauricio Camayo]

Hi list.

I'm currently making modifications to a project, and want to add SQLite3 to
it.

I'm using VS6, have the sqlite3.h and sqlite3.c in the same directory, and
this is the code:

#include "sqlite3.h"



bool TestSQLite()
{
sqlite3 *db;
int rc;
rc = sqlite3_open("test.sqlite",);
return true;
}

but when I build, get the following error
Configuration: mfc01 - Win32 Release
Compiling...
mfc01Dlg.cpp
Linking...
mfc01Dlg.obj : error LNK2001: unresolved external symbol _sqlite3_open


However, if I just compile, everything goes ok.

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

[sqlite] blob /transaction wierdness

[smlacc1 leador]

Hi,

I'm having some trouble with blobs.  I have 4 blobs tht I want to insert
into a db, and it works fine when I execute each insert as a single commit.
However, when i try to use transactions to input blocks of 255 inserts, blob
4 gets inputted in the position of blob3, blob 3 in position of blob 2 etc
etc.  Very strange.  I've written some code to explain what I mean.  The
code takes a long time to finish, so if you want to try it, compile it, then
execute for a few seconds before ctrl-c'ing it to quit.  It should give you
enough data to see what I mean.  In example 1, the fields are where they
should be.  In code 2, the fields all get shifted left by 1 column.  But the
code is identical except for where the "begin" and "commit" statements are
located.  Anyone know what could be the problem?  Im using sqlite v3, th
specific version is the one that came with fedora core 8 - 3.4.2.  The code
is in "c".

here are 2 pieces of code - 1 using transactions, 1 without - but otherwise
identical.

Any help much apreciated.  What I'm trying to acheive would be way too slow
without transactions.

#include 
#include 
#include 

sqlite3* db;
sqlite3_stmt *state;

void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
 char c1[1];
 c1[0] = v1;
 char c2[1];
 c2[0] = v2;
 char c3[1];
 c3[0] = v3;
 char c4[1];
 c4[0] = v4;
 sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
 sqlite3_prepare(db,statement,-1,,NULL);
 sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);

 sqlite3_step(state);
 sqlite3_finalize(state);
 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
}

int main (){
 sqlite3_open("./blobs.db", );
 if (db == 0){
  printf ("database could not be opened.\n");
  return 1;
 }
 char *errmsg;
 sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
BLOB)",0,0,);
 char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
 int looper1 = 0;
 int looper2 = 0;
 int looper3 = 0;
 int looper4 = 0;


 while (looper1 < 256){
  while (looper2 < 256){
   while (looper3 < 256){
  /*  sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
sqlite3_prepare(db,stata,-1,,NULL);*/
while (looper4 < 256){
 execblobsql(stata, looper1, looper2, looper3, looper4);
 looper4++;
}
looper3++;
looper4=0;
  /*  sqlite3_finalize(state);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
   }
   looper2++;
   looper3=0;
   looper4=0;
  }
  looper1++;
  looper2=0;
  looper3=0;
  looper4=0;
 }

}


#include 
#include 
#include 

sqlite3* db;
sqlite3_stmt *state;

void execblobsql(const char* statement,int v1,int v2,int v3,int v4){
 char c1[1];
 c1[0] = v1;
 char c2[1];
 c2[0] = v2;
 char c3[1];
 c3[0] = v3;
 char c4[1];
 c4[0] = v4;
  /*sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
 sqlite3_prepare(db,statement,-1,,NULL);*/
 sqlite3_bind_blob(state,1,c1,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,2,c2,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,3,c3,1,SQLITE_TRANSIENT);
 sqlite3_bind_blob(state,4,c4,1,SQLITE_TRANSIENT);

 sqlite3_step(state);
 /*sqlite3_finalize(state);
 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);*/
}

int main (){
 sqlite3_open("./blobs.db", );
 if (db == 0){
  printf ("database could not be opened.\n");
  return 1;
 }
 char *errmsg;
 sqlite3_exec(db,"create table blobs (b1 BLOB, b2 BLOB, b3 BLOB, b4
BLOB)",0,0,);
 char stata[] = "insert into blobs values (?1 ,?2 ,?3 ,?4)";
 int looper1 = 0;
 int looper2 = 0;
 int looper3 = 0;
 int looper4 = 0;


 while (looper1 < 256){
  while (looper2 < 256){
   while (looper3 < 256){
sqlite3_exec (db, "BEGIN", NULL, NULL, NULL);
sqlite3_prepare(db,stata,-1,,NULL);
while (looper4 < 256){
 execblobsql(stata, looper1, looper2, looper3, looper4);
 looper4++;
}
looper3++;
looper4=0;
sqlite3_finalize(state);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
   }
   looper2++;
   looper3=0;
   looper4=0;
  }
  looper1++;
  looper2=0;
  looper3=0;
  looper4=0;
 }

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

Re: [sqlite] SQLite and updating VIEWs

[Igor Tandetnik]

Shawn Wilsher <[EMAIL PROTECTED]> wrote:
> On Tue, Jul 1, 2008 at 3:24 PM, Igor Tandetnik
> <[EMAIL PROTECTED]> wrote:
>> CREATE TRIGGER 
>> BEGIN
>>insert into temp_table
>>select * from perm_table
>>where  and
>>   ;
>>
>>update temp_table set ...;
>> END;
> I had thought of this, but I'm pretty sure this will only work
> correctly the first time you try to update the view.  Subsequent calls
> will try to copy the data into the temp table, but correctly fail.

They won't fail - they will successfully insert zero records. It's 
perfectly valid to run INSERT ... SELECT and have the SELECT part 
produce an empty resultset. It simply does nothing.

Igor Tandetnik 



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

Re: [sqlite] SQLite and updating VIEWs

[Shawn Wilsher]

On Tue, Jul 1, 2008 at 3:24 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> CREATE TRIGGER 
> BEGIN
>insert into temp_table
>select * from perm_table
>where  and
>   ;
>
>update temp_table set ...;
> END;
I had thought of this, but I'm pretty sure this will only work
correctly the first time you try to update the view.  Subsequent calls
will try to copy the data into the temp table, but correctly fail.
However, that means the update will never actually run, correct?

Cheers,

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

Re: [sqlite] Index and ORDER BY

[Alexey Pechnikov]

Is any difference between "CREATE INDEX ev_idx ON events(type,eid)" 
and "CREATE INDEX ev_idx ON events(type,eid desc)"? What is "desc" keyword 
for index?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and updating VIEWs

[Igor Tandetnik]

Shawn Wilsher <[EMAIL PROTECTED]> wrote:
> I'm working with a partitioned table setup with a permanent table and
> a temp table with the same columns and indexes.  Every X time we dump
> all the records in the temp table over to the permanent one.  In order
> to make selection queries easier to manage, I've gone and created a
> view like so:
> CREATE TEMPORARY VIEW table_view AS
> SELECT * FROM table_temp
> UNION
> SELECT * FROM table
>
> This was all going well, until I realized that updating was going to
> be very hard (insertion always goes to the temporary table).  That
> seemed easy enough to manage if I use an INSTEAD OF trigger on the
> view for UPDATE statements.  The problem is what I want to do in the
> trigger, which is this:
> 1) if the data is in the temporary table, update that
> 2) if the data is not in the temporary table, copy the data from the
> permanent table into the temp one, and then update the temp table
> Sadly, my SQL-fu isn't strong enough to know how to do this

CREATE TRIGGER 
BEGIN
insert into temp_table
select * from perm_table
where  and
   ;

update temp_table set ...;
END;

Igor Tandetnik 



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

Re: [sqlite] Index and ORDER BY

[Jeff Gibson]

I see.  It turns out that the selectivity of "type" is highly 
variable - some types are very common and some are quite rare.  What 
made me curious is that when I have an index on type and I look for the 
first few entries in ascending order, the query is very fast - it seems 
that it does the indexed search on type and then starts searching the 
matching rows in ascending order.  For the descending order, it seems 
that it has to find all matching rows and then return the last one (I 
don't know for sure how it's working, but that seems to fit the 
performance measurements).  Is there any way to have it use the index on 
type and then search the matching rows in descending order so no sorting 
is required?  My confusion is that it seems to search in descending 
order when only the primary key is involved, but not when using an 
index, even if that index has DESC specified.
As you say, I have put in the "+type" trick, and that speeds up my 
common case (where a matching type is nearby so the linear search isn't 
so bad), so now I'm in the much better situation of just worrying about 
the hypothetical case where it has to search a long way to find a 
matching type.
Thanks,
Jeff

D. Richard Hipp wrote:
> On Jul 1, 2008, at 2:17 PM, Jeff Gibson wrote:
>
>   
>> I'm including a copy of Alexey's relevant message below.  Unless I
>> misunderstand, he has a test case that demonstrates that for the  
>> table:
>>
>> CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)
>>
>> the query:
>>
>> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY
>> eid DESC LIMIT 1;
>>
>> runs much faster if there is no index on type.  The culprit seems to  
>> be
>> the <= in conjunction with the descending ordering.  If you change
>> either, the query gets much faster.  He tried the using indices
>> events(type,eid) and events(type,eid desc).  I also tried your  
>> original
>> suggestion of just events(type) and got the same result.
>> 
>
> That would be a case of SQLite choosing a suboptimal index, which is  
> very different from ignoring an index all together, which is what your  
> original statement said.  I see that if there is an index on  
> events(type) that index is used rather than the primary key.  This is  
> because the query optimizer is assuming that type=22 is highly  
> selective.  Running ANALYZE might help.  But a sure-fire solution is  
> to change the query as follows:
>
> SELECT * FROM events
>  WHERE eid<=32619750
>AND +type=22
>  ORDER BY eid DESC
>   LIMIT  1;
>
> Note the "+" operator in front of the "type" field in the WHERE  
> clause.  This + size makes that term of the WHERE clause an  
> expression, rather than a constraint on a column, and this  
> disqualifies it from use by an index.  That forces SQLite to use the  
> other query strategy, which is to use the integer primary key.
>
> Note that in this case, the correct index choice depends on the kind  
> of data contained in the table.  If there is only a single row out of  
> 20 million for which type=22, but there are hundreds of thousands of  
> rows with eid<=32619750, then the use of the index on event(type) is  
> clearly the better strategy.  Only when type=22 is a common occurrence  
> does it become better to use the integer primary key.  SQLite does not  
> attempt to keep statistics on table contents, so it has no way of  
> knowing which approach is really better.  It makes its best guess.  In  
> this case, it happened to guess wrong.  But, as I pointed out, a  
> programmer with higher-level knowledge of the table content can steer  
> SQLite toward the better choice with the judicious use of a "+" symbol.
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] SQLite and updating VIEWs

[Shawn Wilsher]

Hey all,

I'm working with a partitioned table setup with a permanent table and
a temp table with the same columns and indexes.  Every X time we dump
all the records in the temp table over to the permanent one.  In order
to make selection queries easier to manage, I've gone and created a
view like so:
CREATE TEMPORARY VIEW table_view AS
SELECT * FROM table_temp
UNION
SELECT * FROM table

This was all going well, until I realized that updating was going to
be very hard (insertion always goes to the temporary table).  That
seemed easy enough to manage if I use an INSTEAD OF trigger on the
view for UPDATE statements.  The problem is what I want to do in the
trigger, which is this:
1) if the data is in the temporary table, update that
2) if the data is not in the temporary table, copy the data from the
permanent table into the temp one, and then update the temp table
Sadly, my SQL-fu isn't strong enough to know how to do this, and I'm
starting to wonder if it's even possible.  If someone could tell me if
I can do it, and then provide a pointer as to how to go about it, I'd
really appreciate it.

Cheers,

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

Re: [sqlite] Index and ORDER BY

[D. Richard Hipp]

On Jul 1, 2008, at 2:17 PM, Jeff Gibson wrote:

> I'm including a copy of Alexey's relevant message below.  Unless I
> misunderstand, he has a test case that demonstrates that for the  
> table:
>
> CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)
>
> the query:
>
> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY
> eid DESC LIMIT 1;
>
> runs much faster if there is no index on type.  The culprit seems to  
> be
> the <= in conjunction with the descending ordering.  If you change
> either, the query gets much faster.  He tried the using indices
> events(type,eid) and events(type,eid desc).  I also tried your  
> original
> suggestion of just events(type) and got the same result.

That would be a case of SQLite choosing a suboptimal index, which is  
very different from ignoring an index all together, which is what your  
original statement said.  I see that if there is an index on  
events(type) that index is used rather than the primary key.  This is  
because the query optimizer is assuming that type=22 is highly  
selective.  Running ANALYZE might help.  But a sure-fire solution is  
to change the query as follows:

SELECT * FROM events
 WHERE eid<=32619750
   AND +type=22
 ORDER BY eid DESC
  LIMIT  1;

Note the "+" operator in front of the "type" field in the WHERE  
clause.  This + size makes that term of the WHERE clause an  
expression, rather than a constraint on a column, and this  
disqualifies it from use by an index.  That forces SQLite to use the  
other query strategy, which is to use the integer primary key.

Note that in this case, the correct index choice depends on the kind  
of data contained in the table.  If there is only a single row out of  
20 million for which type=22, but there are hundreds of thousands of  
rows with eid<=32619750, then the use of the index on event(type) is  
clearly the better strategy.  Only when type=22 is a common occurrence  
does it become better to use the integer primary key.  SQLite does not  
attempt to keep statistics on table contents, so it has no way of  
knowing which approach is really better.  It makes its best guess.  In  
this case, it happened to guess wrong.  But, as I pointed out, a  
programmer with higher-level knowledge of the table content can steer  
SQLite toward the better choice with the judicious use of a "+" symbol.


D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] Performance on HP

[Jeffrey Rennie (レニー）]

Are there any other processes or threads trying to open your db file while
you run your tests?

On Mon, Jun 23, 2008 at 9:48 AM, Andrea Connell <[EMAIL PROTECTED]>
wrote:

>
>
> >> The program took 47 seconds to run, but the results only account for
> >> .39 seconds
> >
> > Most likely all the time is being spent in IO related system calls
> > - read(), write() and fsync().
> >
> > Dan.
>
>
> Thanks for the idea Dan. How can I confirm this or try reducing the time
> spent? I use the same method for reading my input file when I run both
> SQLite and our in house system, and the other way only takes 4 seconds
> total so I don't think it could be from that. Also, when I run our in
> house system and use the profiler the time spent adds up to 100%
>
> So this must be something within SQLite. I am using a transaction for my
> queries. How can I find all of that missing time?
>
>
> If anybody is interested, here is my main chunk of code. ReadLine()
> parses the input file and fills the required variables. This method is
> shared for both database systems (SQLite and ours). ReadSQLiteComponent
> just calls one of the sqlite3_column functions based on the type of the
> field, and a similar method is used for our system.
>
>
>std::ifstream inf(argv[1]);
>
>sqlite3 *db;
>sqlite3_stmt *stmt;
>sqlite3_stmt *stmt2;
>sqlite3_stmt *stmt3;
>int rc = sqlite3_open(argv[3], );
>if( rc )
>{
>printf("Can't open database: %s\n",
> sqlite3_errmsg(db));
>sqlite3_close(db);
>return -1;
>}
>sqlite3_prepare(db,"BEGIN TRANSACTION;", 100, ,0);
>sqlite3_step(stmt);
>
>char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ?
> AND DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY LIKE ?
> ;";
>int p = sqlite3_prepare_v2(db,qry,1000,,0);
> char * qry2 = "SELECT * FROM LEVEL2 WHERE PARENT_KEY = ?
> AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;";
> int p2 = sqlite3_prepare_v2(db,qry2,1000,,0);
> char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ?
> ;";
> int p3 = sqlite3_prepare_v2(db,qry3,1000,,0);
>if ( p || p2 || p3 )
>{
>printf("Can't create prepared statement: %s\n",
> sqlite3_errmsg(db));
>sqlite3_close(db);
>return -1;
>}
>
>while (ReadLine(inf))
>{
>sqlite3_bind_text(stmt, 1, cntryid, -1,
> SQLITE_TRANSIENT);
>sqlite3_bind_int(stmt, 2, searcharea);
>sqlite3_bind_text(stmt, 3, addrtype, -1,
> SQLITE_TRANSIENT);
>int len = strlen(phnkey);
>phnkey[len] = '%';
>phnkey[len+1] = '\0';
>sqlite3_bind_text(stmt, 4, phnkey, -1,
> SQLITE_TRANSIENT);
>
>while(sqlite3_step(stmt)==SQLITE_ROW)
>{
>for(int i=0; i{
>ReadSQLiteComponent(0,i, stmt);
>if (i==51) //LEVEL1.RECORDKEY
>
> sqlite3_bind_int(stmt2,1,sqlite3_column_int(stmt, i));
>}
>sqlite3_bind_text(stmt2,2,prmlow, -1,
> SQLITE_TRANSIENT);
>sqlite3_bind_text(stmt2,3,prmhigh, -1,
> SQLITE_TRANSIENT);
>while(sqlite3_step(stmt2)==SQLITE_ROW)
>{
>for(int i=0; i i++)
>{
>ReadSQLiteComponent(1,i,
> stmt2);
>if (i==27)
> //LEVEL2.RECORDKEY
>
> sqlite3_bind_int(stmt3,1,sqlite3_column_int(stmt2, i));
>}
>
> while(sqlite3_step(stmt3)==SQLITE_ROW)
>{
>for(int i=0;
> i
> ReadSQLiteComponent(2,i, stmt3);
>q++;
>}
>sqlite3_reset(stmt3);
>}
>sqlite3_reset(stmt2);
>}
>sqlite3_reset(stmt);
>}
>
>sqlite3_prepare(db,"END TRANSACTION;", 100, ,0);
>sqlite3_step(stmt);
>
>sqlite3_finalize(stmt);
>sqlite3_finalize(stmt2);
> 

Re: [sqlite] Rigging up SQLite over LINQ (Entity Framework)

[Robert Simpson]

In case anyone stopped reading after the convoluted LINQ query, here's a
simplified version of what it's trying to do.  It's kindof confusing, but
that's what you get when a computer constructs a query I guess:

create table a (id integer primary key, bid integer);
create table b (id integer primary key, myval varchar);

select *, (select count(c) from (select 1 as c from b where b.id = a.bid))
from a;
SQL error: no such column: a.bid

Is there a way to reference an outer value from an inner sub-query like its
attempting to do here?


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert Simpson
Sent: Monday, June 30, 2008 9:10 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] Rigging up SQLite over LINQ (Entity Framework)

Just when I thought I was hitting the home stretch, I came across an
aggregate query test that I'm not sure how to solve.   LINQ generated the
following query programmatically:

-- Begin auto-generated query
SELECT 
1 AS [C1], 
[Project2].[ProductID] AS [ProductID], 
[Project2].[ProductName] AS [ProductName], 
[Project2].[QuantityPerUnit] AS [QuantityPerUnit], 
[Project2].[UnitPrice] AS [UnitPrice], 
[Project2].[UnitsInStock] AS [UnitsInStock], 
[Project2].[UnitsOnOrder] AS [UnitsOnOrder], 
[Project2].[ReorderLevel] AS [ReorderLevel], 
[Project2].[Discontinued] AS [Discontinued], 
[Project2].[CategoryID] AS [CategoryID], 
[Project2].[SupplierID] AS [SupplierID]
FROM ( SELECT 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[ProductName] AS [ProductName], 
[Extent1].[SupplierID] AS [SupplierID], 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], 
[Extent1].[UnitPrice] AS [UnitPrice], 
[Extent1].[UnitsInStock] AS [UnitsInStock], 
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 
[Extent1].[ReorderLevel] AS [ReorderLevel], 
[Extent1].[Discontinued] AS [Discontinued], 
(SELECT 
Count([Project1].[C1]) AS [A1]
FROM ( SELECT 
1 AS [C1]
FROM[Order Details] AS [Extent2]
LEFT OUTER JOIN [Orders] AS [Extent3] ON
[Extent2].[OrderID] = [Extent3].[OrderID]
LEFT OUTER JOIN [Customers] AS [Extent4] ON
[Extent3].[CustomerID] LIKE [Extent4].[CustomerID]
INNER JOIN [Suppliers] AS [Extent5] ON
[Extent4].[Country] LIKE [Extent5].[Country]
WHERE ([Extent1].[SupplierID] =
[Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID])
)  AS [Project1]) AS [C1]
FROM [Products] AS [Extent1]
)  AS [Project2]
WHERE [Project2].[C1] > 2
-- End autogenerated query


The error SQLite throws is "no such column: Extent1.ProductID" and I believe
it's in the inner WHERE clause:
WHERE ([Extent1].[SupplierID] =
[Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID])

I can of course supply a schema, but I think it may be irrelevant and have
more to do with the fact that it's got some inner subqueries referencing an
outer object?

Since the SQL is autogenerated, I have limited control over it.  The actual
LINQ query was:

  var query = from p in db.Products
  where p.Order_Details.Count(od => od.Orders.Customers.Country
== p.Suppliers.Country) > 2
  select p;

Aside from this little issue involving aggregates, SQLite is working nicely
with Microsoft's new Entity Framework.

Robert Simpson



___
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] Insert mil.rows to table can take up to a day

[Mihai Limbasan]

Joanne Pham wrote:
> Thank you so much Mihai.
> It worked!!! Once again thanks a ton.
> JL
>   
Glad to hear that - have fun!

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

Re: [sqlite] Index and ORDER BY

[Jeff Gibson]

I'm including a copy of Alexey's relevant message below.  Unless I 
misunderstand, he has a test case that demonstrates that for the table: 

CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)

the query:

SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
eid DESC LIMIT 1;

runs much faster if there is no index on type.  The culprit seems to be 
the <= in conjunction with the descending ordering.  If you change 
either, the query gets much faster.  He tried the using indices 
events(type,eid) and events(type,eid desc).  I also tried your original 
suggestion of just events(type) and got the same result.
Thanks,
Jeff


D. Richard Hipp wrote:
> On Jul 1, 2008, at 1:24 PM, [EMAIL PROTECTED] wrote:
>   
>> Is it a problem in sqlite that it will only optimize:  "WHERE
>> primary_key<=X ORDER BY primary_key DESC" if it's not using an index?
>> Is it supposed to?
>> 
>
> It would be a problem if it where the case.  But in every test I have  
> tried, SQLite does in fact use an index on WHERE pk<=X ORDER BY pk  
> DESC.  If you can demonstrate a case where it does not, we will fix it.
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   


Alexey Pechnikov wrote:
> Really, there is problem with multi-column indexes. You must use only primary 
> key index for ">=" where clause and "ASC" sorting and "<=" where clause and 
> DESC sorting. 
>
>
> 1. I try with primary key:
>
> #!/usr/bin/tclsh
> package require sqlite3
> sqlite3 db index_order.db
> db eval {DROP TABLE IF EXISTS events}
> db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)}
> db transaction {
> for {set i 0} {$i<1} {incr i} {
> set type [expr {$i % 50}]
> db eval {insert into events values ($i,$type)}
> }
> }
> db close
>
> So, "type" is equal ("eid" mod 50).
>
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
> eid DESC LIMIT 1;
> 32619722|22
> CPU Time: user 0.00 sys 0.00
>
> sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 
> and 
> type=22 ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events USING PRIMARY KEY ORDER BY
>
> 
> Result: this index is good.
> 
>
> 2. And I try with two-columns common order index:
> #!/usr/bin/tclsh
> package require sqlite3
> sqlite3 db index_order.db
> db eval {DROP TABLE IF EXISTS events}
> db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)}
> db transaction {
> for {set i 0} {$i<1} {incr i} {
> set type [expr {$i % 50}]
> db eval {insert into events values ($i,$type)}
> }
> }
> db eval {CREATE INDEX ev_idx ON events(type,eid)}
> db close
>
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
> eid DESC LIMIT 1;
> 32619722|22
> CPU Time: user 1.400088 sys 1.696106
>
> sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 
> and 
> type=22 ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events WITH INDEX ev_idx ORDER BY
>
> 
> Result: this index is bad.
> 
>
> 3. And I try with two-columns desc order index:
> #!/usr/bin/tclsh
> package require sqlite3
> sqlite3 db index_order.db
> db eval {DROP TABLE IF EXISTS events}
> db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)}
> db transaction {
> for {set i 0} {$i<1} {incr i} {
> set type [expr {$i % 50}]
> db eval {insert into events values ($i,$type)}
> }
> }
> db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)}
> db close
>
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
> eid DESC LIMIT 1;
> 32619722|22
> CPU Time: user 0.600037 sys 0.608038
>
> sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 
> and 
> type=22 ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY
>
>
> And with modified query:
>
> sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY 
> eid DESC LIMIT 1;
> 9972|22
> CPU Time: user 0.00 sys 0.00
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
> eid ASC LIMIT 1;
> 22|22
> CPU Time: user 0.00 sys 0.004000
> sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY 
> eid ASC LIMIT 1;
> 32619772|22
> CPU Time: user 0.284018 sys 0.820051
>
>
>
> 
> Result: this index is bad.
> 
>
>
> P.S. Try with primary key index only and write your results.
> ___
> 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] Insert mil.rows to table can take up to a day

[Joanne Pham]

Thank you so much Mihai.
It worked!!! Once again thanks a ton.
JL



- Original Message 
From: Mihai Limbasan <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Tuesday, July 1, 2008 10:57:31 AM
Subject: Re: [sqlite] Insert mil.rows to table can take up to a day

Joanne Pham wrote:
> Hi All,
> I have the file which has million rows of insert statement to insert the data 
> to the database like below:
>  insert into tables values(...)
> 
> (there are million insert statement to insert to the table)
> (file name is insertTables)
> Then I ran the following command to insert to table
> sqlite3 MyDB < insertTables
> For compeleting to insert 1 million rows it may take up to a day. Is there 
> any way to speech up the insertion in this case.
> Thanks,
> JL
>
>  
Hi, Joanne.

First of all, have you wrapped the insert sequence in a transaction? 
That speeds up things considerably.
Second, try dropping all indexes on the table prior to insertion and 
recreate them after insertion is done.
Third, make sure no complicated triggers run on insertion into that table.

Other than that - are you perchance hosting the database on a flash 
medium, or generally a slow storage medium? Can you give us more details 
about the operating system environment and the table schemas?
___
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] Insert mil.rows to table can take up to a day

[Joanne Pham]

Thanks a lot Mihai,
I will try it.
Again thank for quick response.
JL



- Original Message 
From: Mihai Limbasan <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Tuesday, July 1, 2008 10:57:31 AM
Subject: Re: [sqlite] Insert mil.rows to table can take up to a day

Joanne Pham wrote:
> Hi All,
> I have the file which has million rows of insert statement to insert the data 
> to the database like below:
>  insert into tables values(...)
> 
> (there are million insert statement to insert to the table)
> (file name is insertTables)
> Then I ran the following command to insert to table
> sqlite3 MyDB < insertTables
> For compeleting to insert 1 million rows it may take up to a day. Is there 
> any way to speech up the insertion in this case.
> Thanks,
> JL
>
>  
Hi, Joanne.

First of all, have you wrapped the insert sequence in a transaction? 
That speeds up things considerably.
Second, try dropping all indexes on the table prior to insertion and 
recreate them after insertion is done.
Third, make sure no complicated triggers run on insertion into that table.

Other than that - are you perchance hosting the database on a flash 
medium, or generally a slow storage medium? Can you give us more details 
about the operating system environment and the table schemas?
___
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] Insert mil.rows to table can take up to a day

[Mihai Limbasan]

Joanne Pham wrote:
> Hi All,
> I have the file which has million rows of insert statement to insert the data 
> to the database like below:
>  insert into tables values(...)
> 
> (there are million insert statement to insert to the table)
> (file name is insertTables)
> Then I ran the following command to insert to table
> sqlite3 MyDB < insertTables
> For compeleting to insert 1 million rows it may take up to a day. Is there 
> any way to speech up the insertion in this case.
> Thanks,
> JL
>
>   
Hi, Joanne.

First of all, have you wrapped the insert sequence in a transaction? 
That speeds up things considerably.
Second, try dropping all indexes on the table prior to insertion and 
recreate them after insertion is done.
Third, make sure no complicated triggers run on insertion into that table.

Other than that - are you perchance hosting the database on a flash 
medium, or generally a slow storage medium? Can you give us more details 
about the operating system environment and the table schemas?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert mil.rows to table can take up to a day

[Joanne Pham]

Hi All,
I have the file which has million rows of insert statement to insert the data 
to the database like below:
 insert into tables values(...)

(there are million insert statement to insert to the table)
(file name is insertTables)
Then I ran the following command to insert to table
sqlite3 MyDB < insertTables
For compeleting to insert 1 million rows it may take up to a day. Is there any 
way to speech up the insertion in this case.
Thanks,
JL


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

Re: [sqlite] [newbie] linking two tables with index...

[Mihai Limbasan]

Mihai Limbasan wrote:
> In your example it wasn't.
Oops, misread what you wrote.

What I mean is that it doesn't matter whether the ID is autogenerated or 
not (of
course, if it isn't then the whole issue becomes moot - it in fact 
*should* be PRIMARY KEY AUTOINCREMENT.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [newbie] linking two tables with index...

[Mihai Limbasan]

Igor Tandetnik wrote:
>> Sure. I'll use your examples, just making sure to add an id insertion
>> in the first statement since the id column isn't specified as
>> autoincrement.
>> 
>
> But it _is_ specified as INTEGER PRIMARY KEY.
In your example it wasn't. It also wasn't in Martin's original table 
creation statement.
> If I'm inserting a 
> specific Id into snapshot, I'm just going to explicily specify the same 
> id when inserting into sample, and I wouldn't need all the song and 
> dance you show. The whole issue is that the ID in snapshot table is in 
> fact automatically generated.
>
>   
It makes absolutely no difference. I just noted that so you wouldn't 
think it relied on a specific ID.

Here's the whole thing again - I updated the table creation on the 
snapshot table and removed the id from the insertions on it. The 
triggers still work perfectly.

sqlite> .headers on
sqlite> DROP TABLE IF EXISTS snapshot;
sqlite> CREATE TABLE snapshot(id INTEGER PRIMARY KEY AUTOINCREMENT, bumf 
TEXT);
sqlite> DROP TABLE IF EXISTS sample;
sqlite> CREATE TABLE sample(snapshotid INTEGER, type TEXT, used INTEGER);
sqlite> DROP TABLE IF EXISTS last_snapid;
sqlite> CREATE TABLE last_snapid(snapid INTEGER); -- Table will hold a 
single row with the last inserted snapshot id
sqlite> INSERT INTO last_snapid(snapid) VALUES(NULL); -- Ensure the 
(single) row exists, we'll keep updating it
sqlite> SELECT * FROM last_snapid;
snapid

sqlite> CREATE TRIGGER capture_last_snapid AFTER INSERT ON snapshot BEGIN
   ...> UPDATE last_snapid SET snapid = NEW.id WHERE rowid = 1;
   ...> END;
sqlite> CREATE TRIGGER update_sample AFTER INSERT ON sample BEGIN
   ...> UPDATE sample SET snapshotid = (SELECT snapid FROM last_snapid 
WHERE rowid = 1) WHERE rowid = NEW.rowid;
   ...> END;
sqlite> INSERT INTO snapshot (bumf) VALUES ('one');
sqlite> SELECT * FROM snapshot;
id|bumf
1|one
sqlite> SELECT * FROM last_snapid; -- To verify capture_last_snapid is 
working
snapid
1
sqlite> INSERT INTO sample(type, used) VALUES ('a', 1);
sqlite> INSERT INTO sample(type, used) VALUES ('b', 1);
sqlite> SELECT * FROM sample;
snapshotid|type|used
1|a|1
1|b|1
sqlite> INSERT INTO snapshot (bumf) VALUES ('another one');
sqlite> SELECT * FROM snapshot;
id|bumf
1|one
2|another one
sqlite> INSERT INTO sample(type, used) VALUES ('c', 3);
sqlite> INSERT INTO sample(type, used) VALUES ('d', 54);
sqlite> INSERT INTO sample(type, used) VALUES ('e', 168);
sqlite> SELECT * FROM sample;
snapshotid|type|used
1|a|1
1|b|1
2|c|3
2|d|54
2|e|168


On a sidenote: Are we nitpicking without even checking our facts, or are 
we trying to help someone?

> For more details on the relationship between INTEGER PRIMARY KEY and 
> AUTOINCREMENT, see http://sqlite.org/autoinc.html
>   

I'm very familiar with it, but thanks for your concern.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] prepackaged sql statement

[Stephen Woodbridge]

Alex Katebi wrote:
> The problem with the view is that you can not pass a parameter or variable
> from the outer select to the views select.

Right that is not the purpose of a view. Think of a view as a virtual 
TABLE. You can not pass a parameter to a table either. You can just 
query the table or view and you CAN query with a parameter in the select 
statement.

select * from  where 

Maybe it would be help to restate what you are trying to do.

-Steve

> On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]>
> wrote:
> 
>> "Henrik Bechmann" <[EMAIL PROTECTED]>
>> wrote in message news:[EMAIL PROTECTED]
>>> Thanks Igor! And the SQL statement can be bound to parameters in the
>>> usual ways?
>> No, not in the view. You can, of course, select from the view (as if it
>> were a table), and _that_ query can be parameterized.
>>
>> 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

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

Re: [sqlite] Index and ORDER BY

[D. Richard Hipp]

On Jul 1, 2008, at 1:24 PM, [EMAIL PROTECTED] wrote:
>
> Is it a problem in sqlite that it will only optimize:  "WHERE
> primary_key<=X ORDER BY primary_key DESC" if it's not using an index?
> Is it supposed to?

It would be a problem if it where the case.  But in every test I have  
tried, SQLite does in fact use an index on WHERE pk<=X ORDER BY pk  
DESC.  If you can demonstrate a case where it does not, we will fix it.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] [newbie] linking two tables with index...

[Mihai Limbasan]

Igor Tandetnik wrote:
> Can you show an example of a trigger that would make these statements 
> work:
>
> insert into snapshot (bumf) values ('one');
> insert into sample(type, used) values ('a', 1);
> insert into sample(type, used) values ('b', 1);
>
> so that, at the end, the two new records in sample table would have 
> snapshotid equal to snapshot.id from the record inserted into snapshot 
> table.
>   
Sure. I'll use your examples, just making sure to add an id insertion in 
the first statement since the id column isn't specified as autoincrement.

sqlite> .headers on
sqlite> DROP TABLE IF EXISTS snapshot;
sqlite> CREATE TABLE snapshot(id INTEGER PRIMARY KEY, bumf TEXT);
sqlite> DROP TABLE IF EXISTS sample;
sqlite> CREATE TABLE sample(snapshotid INTEGER, type TEXT, used INTEGER);
sqlite> DROP TABLE IF EXISTS last_snapid;
sqlite> CREATE TABLE last_snapid(snapid INTEGER); -- Table will hold a 
single row with the last inserted snapshot id
sqlite> INSERT INTO last_snapid(snapid) VALUES(NULL); -- Ensure the 
(single) row exists, we'll keep updating it
sqlite> SELECT * FROM last_snapid;
snapid

sqlite> CREATE TRIGGER capture_last_snapid AFTER INSERT ON snapshot BEGIN
   ...> UPDATE last_snapid SET snapid = new.id WHERE rowid = 1;
   ...> END;
sqlite> CREATE TRIGGER update_sample AFTER INSERT ON sample BEGIN
   ...> UPDATE sample SET snapshotid = (SELECT snapid FROM last_snapid 
WHERE rowid = 1) WHERE rowid = NEW.rowid;
   ...> END;
sqlite> INSERT INTO snapshot (id, bumf) VALUES (42, 'one');
sqlite> SELECT * FROM snapshot;
id|bumf
42|one
sqlite> SELECT * FROM last_snapid; -- To verify capture_last_snapid is 
working
snapid
42
sqlite> INSERT INTO sample(type, used) VALUES ('a', 1);
sqlite> INSERT INTO sample(type, used) VALUES ('b', 1);
sqlite> SELECT * FROM sample;
snapshotid|type|used
42|a|1
42|b|1
sqlite> INSERT INTO snapshot (id, bumf) VALUES (999, 'another one');
sqlite> SELECT * FROM snapshot;
id|bumf
42|one
999|another one
sqlite> INSERT INTO sample(type, used) VALUES ('c', 3);
sqlite> INSERT INTO sample(type, used) VALUES ('d', 54);
sqlite> INSERT INTO sample(type, used) VALUES ('e', 168);
sqlite> SELECT * FROM sample;
snapshotid|type|used
42|a|1
42|b|1
999|c|3
999|d|54
999|e|168

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

Re: [sqlite] Index and ORDER BY

[Noah Hart]

Jeff, try this select instead 

sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE
eid<=32619760 and +type=22  ORDER BY eid DESC LIMIT 1;
0|0|TABLE events USING PRIMARY KEY ORDER BY


Regards-- Noah

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, July 01, 2008 9:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Index and ORDER BY

I agree.  If I drop indices that use "type", I get my performance back  
for this query:

sqlite> SELECT events.* FROM events WHERE eid<=32619760 AND type=22  
ORDER BY eid DESC LIMIT 1;
16643833|27906245|5972704|0|22|9|4
CPU Time: user 0.001000 sys 0.001000

sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE  
eid<=32619760 AND type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events USING PRIMARY KEY ORDER BY

The problem is that indexing the type column gives me a huge  
performance benefit for other queries in my application.  Is there any  
way I can force sqlite to not use an index for a particular query?
Thanks for your help!
Jeff



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] Index and ORDER BY

[Alexey Pechnikov]

В сообщении от Tuesday 01 July 2008 19:26:47 John Stanton написал(а):
> I haven't looked closely at this problem but a cursory glance suggests
> that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause.

But primary key index work fine. Why?

> Try doing the selection ASC and then sorting the output DESC as a
> seperate action.

It's impossible for big tables. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index and ORDER BY

[John Stanton]

I haven't looked closely at this problem but a cursory glance suggests 
that Sqlite is not using an ASC indesx if there is a DESC ORDER By clause.

Try doing the selection ASC and then sorting the output DESC as a 
seperate action.

Alexey Pechnikov wrote:
> Really, there is problem with multi-column indexes. You must use only primary 
> key index for ">=" where clause and "ASC" sorting and "<=" where clause and 
> DESC sorting. 
> 
> 
> 1. I try with primary key:
> 
> #!/usr/bin/tclsh
> package require sqlite3
> sqlite3 db index_order.db
> db eval {DROP TABLE IF EXISTS events}
> db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)}
> db transaction {
> for {set i 0} {$i<1} {incr i} {
> set type [expr {$i % 50}]
> db eval {insert into events values ($i,$type)}
> }
> }
> db close
> 
> So, "type" is equal ("eid" mod 50).
> 
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
> eid DESC LIMIT 1;
> 32619722|22
> CPU Time: user 0.00 sys 0.00
> 
> sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 
> and 
> type=22 ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events USING PRIMARY KEY ORDER BY
> 
> 
> Result: this index is good.
> 
> 
> 2. And I try with two-columns common order index:
> #!/usr/bin/tclsh
> package require sqlite3
> sqlite3 db index_order.db
> db eval {DROP TABLE IF EXISTS events}
> db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)}
> db transaction {
> for {set i 0} {$i<1} {incr i} {
> set type [expr {$i % 50}]
> db eval {insert into events values ($i,$type)}
> }
> }
> db eval {CREATE INDEX ev_idx ON events(type,eid)}
> db close
> 
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
> eid DESC LIMIT 1;
> 32619722|22
> CPU Time: user 1.400088 sys 1.696106
> 
> sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 
> and 
> type=22 ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events WITH INDEX ev_idx ORDER BY
> 
> 
> Result: this index is bad.
> 
> 
> 3. And I try with two-columns desc order index:
> #!/usr/bin/tclsh
> package require sqlite3
> sqlite3 db index_order.db
> db eval {DROP TABLE IF EXISTS events}
> db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)}
> db transaction {
> for {set i 0} {$i<1} {incr i} {
> set type [expr {$i % 50}]
> db eval {insert into events values ($i,$type)}
> }
> }
> db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)}
> db close
> 
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
> eid DESC LIMIT 1;
> 32619722|22
> CPU Time: user 0.600037 sys 0.608038
> 
> sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 
> and 
> type=22 ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY
> 
> 
> And with modified query:
> 
> sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY 
> eid DESC LIMIT 1;
> 9972|22
> CPU Time: user 0.00 sys 0.00
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
> eid ASC LIMIT 1;
> 22|22
> CPU Time: user 0.00 sys 0.004000
> sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY 
> eid ASC LIMIT 1;
> 32619772|22
> CPU Time: user 0.284018 sys 0.820051
> 
> 
> 
> 
> Result: this index is bad.
> 
> 
> 
> P.S. Try with primary key index only and write your results.
> ___
> 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] Performance on HP

[Andrea Connell]

>On Jun 30, 2008, at 2:37 PM, Andrea Connell wrote:
>
>> Any ideas? It's driving me crazy why SQLite is this much slower on  
>> UNIX
>> boxes, while other applications maintain their speed.
>
>What filesystem are you using on the unix boxes?  Are you *sure* you  
>are not using NFS?
>
>D. Richard Hipp
>[EMAIL PROTECTED]
>

You know what, I'm not sure but I'd be willing to bet I am using NFS.
I'll check around and see if there's anything else I can move it to.

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

Re: [sqlite] How does the database file grow?

[D. Richard Hipp]

On Jul 1, 2008, at 12:16 PM, Stephen Woodbridge wrote:

>
>> On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote:
>>> Because I can in many cases calculate in advance the size to which  
>>> the
>>> file will grow, i would like to reserve the disk space before
>>> inserting
>>> all the data. Can this be done?
>>
> Would something like this work (on linux)?
>
> dd if=/dev/zero of=mydatabase.db bs=1024 count=65536
>
> would create a file that is 64MB in size and zero it out. I'm not use
> how sqlite would react to being given a preallocated but empty file  
> that
> exists.


No.  SQLite sees a file of zeros as a corrupt database.  The internal  
structure of the file must be correctly initialized.


D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] prepackaged sql statement

[Alex Katebi]

The problem with the view is that you can not pass a parameter or variable
from the outer select to the views select.

On Mon, Jun 30, 2008 at 12:49 AM, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:

> "Henrik Bechmann" <[EMAIL PROTECTED]>
> wrote in message news:[EMAIL PROTECTED]
> > Thanks Igor! And the SQL statement can be bound to parameters in the
> > usual ways?
>
> No, not in the view. You can, of course, select from the view (as if it
> were a table), and _that_ query can be parameterized.
>
> 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] How does the database file grow?

[Stephen Woodbridge]

D. Richard Hipp wrote:
> On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote:
> 
>> Hi all,
>>
>> i create and fill database files which reach quite a large size  
>> after a
>> while, because i only add data and never remove it.
>> The database files themselves become quite fragmented on the disk.
>> Because I can in many cases calculate in advance the size to which the
>> file will grow, i would like to reserve the disk space before  
>> inserting
>> all the data. Can this be done?
> 
> 
> Perhaps like this:
> 
>  CREATE TABLE dummy(x);
>  INSERT INTO dummy VALUES(zeroblob(1000));
>  DROP TABLE dummy;
> 
> Change the argument of zeroblob to get the size you want.

Would something like this work (on linux)?

dd if=/dev/zero of=mydatabase.db bs=1024 count=65536

would create a file that is 64MB in size and zero it out. I'm not use 
how sqlite would react to being given a preallocated but empty file that 
exists.

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

Re: [sqlite] How does the database file grow?

[D. Richard Hipp]

On Jul 1, 2008, at 11:03 AM, Martin.Engelschalk wrote:

> Hi all,
>
> i create and fill database files which reach quite a large size  
> after a
> while, because i only add data and never remove it.
> The database files themselves become quite fragmented on the disk.
> Because I can in many cases calculate in advance the size to which the
> file will grow, i would like to reserve the disk space before  
> inserting
> all the data. Can this be done?


Perhaps like this:

 CREATE TABLE dummy(x);
 INSERT INTO dummy VALUES(zeroblob(1000));
 DROP TABLE dummy;

Change the argument of zeroblob to get the size you want.


D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] How does the database file grow?

[Martin.Engelschalk]

Hi all,

i create and fill database files which reach quite a large size after a 
while, because i only add data and never remove it.
The database files themselves become quite fragmented on the disk.
Because I can in many cases calculate in advance the size to which the 
file will grow, i would like to reserve the disk space before inserting 
all the data. Can this be done?

Thanks,
Martin

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

Re: [sqlite] Newbie questions regarding in-memory database.

[Igor Tandetnik]

Karthik <[EMAIL PROTECTED]> wrote:
> is it possible to set a limit on the database size?

PRAGMA max_page_count
http://sqlite.org/pragma.html

> how does sqlite
> behave when database size reaches the threshold(the
> default/configured one)?

You get an error executing the statement that takes it over threshold.

Igor Tandetnik



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

Re: [sqlite] [newbie] linking two tables with index...

[Igor Tandetnik]

Mihai Limbasan <[EMAIL PROTECTED]> wrote:
>> Igor Tandetnik wrote:
>>> You can't make this happen automatically with SQLite.
>
>> Hmm? Of course you can. Just use a trigger:
>> http://sqlite.org/lang_createtrigger.html

Can you show an example of a trigger that would make these statements 
work:

insert into snapshot (bumf) values ('one');
insert into sample(type, used) values ('a', 1);
insert into sample(type, used) values ('b', 1);

so that, at the end, the two new records in sample table would have 
snapshotid equal to snapshot.id from the record inserted into snapshot 
table.

Igor Tandetnik 



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

Re: [sqlite] Multiple constraints per table?

[Harold Wood]

your main issue seems to be that you really need to normalize your db.  using 
your example of pepper 220 gr, when you examine that string it consists of 3 
parts, pepper = product description or product name, 220 = weight or volume or 
measurement of product, followed by the measurement type. All 3 of those 
properties really dont make a unique item but instead are properties of the 
item.
 
I think that multiple tables would give you a better db design and fix some of 
your isses with the constraints.  looking at your current create table 
statement you have columns that belong in a store table, an item table, a 
purchase history table as well as store_carried table.  
 
just imho.

Woody

--- On Tue, 7/1/08, flakpit <[EMAIL PROTECTED]> wrote:

From: flakpit <[EMAIL PROTECTED]>
Subject: [sqlite] Multiple constraints per table?
To: sqlite-users@sqlite.org
Date: Tuesday, July 1, 2008, 1:10 AM

This is the way I normally create my shopping database, but this leads to
thousands of duplicates.

CREATE TABLE shopping(item TEXT,units TEXT,quantity TEXT,category TEXT,shop
TEXT,aisle TEXT,price TEXT,total TEXT,date TEXT,note TEXT,record INTEGER
PRIMARY KEY AUTOINCREMENT)


'item' is the full retailer's description for this shopping item.
If I make
this unique, then i eliminate all duplicates and any further entries of this
item, so that's no good.

item TEXT CONSTRAINT item UNIQUE

is it legal sql syntax to allow more than one constraint field in table
creation? I need at least these four below to guarantee that duplicate items
do make it into the database but not on the same day.

item TEXT CONSTRAINT item UNIQUE
units TEXT CONSTRAINT units UNIQUE
shop TEXT CONSTRAINT shop UNIQUE
date TEXT CONSTRAINT date UNIQUE


So the below three records would be allowed as the unit weight is different
and also there are two different dates and as everyone knows, peppers come
in all shapes and sizes (grin) (This is okay)

pepper, 120gm, coles, 02/02/2006
pepper, 50gm, coles, 02/02/2006
pepper, 50gm, coles, 04/11/2007


Or would the multiple constraints work globally (if legal syntax) Would it
end up with only the single record below because the all constraints
operated globally?

pepper, 120gm, coles, 02/02/2006
-- 
View this message in context:
http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18209309.html
Sent from the SQLite mailing list archive at Nabble.com.

___
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] Newbie questions regarding in-memory database.

[Karthik]

Igor Tandetnik wrote:
> "Karthik" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>   
>> Newbie question regarding in-memory databases. I read in the wiki
>> pages that it is not safe to use same db connection across multiple
>> threads.
>> 
>
> It's actually safe with recent SQLite versions. A lot of issues have 
> been eliminated. SQLite know uses a mutex to serialize concurrent API 
> calls from multiple threads.
>
> You have to know what you are doing of course. E.g. if one thread starts 
> a transaction, then another thread issues some statements, they go into 
> that transaction.
>
>   
>> I am trying to use an in-memory database. My understanding
>> is that opening a connection with ":memory:" creates the database and
>> closing it erases the same. If I were to use one connection per
>> thread will i end up creating distinct memory db's for each thread?
>> 
>
> Yes.
>
>   
>> is it possible to use a single in-memory db that can be accessed by
>> different threads in a multithreaded environment?
>> 
>
> The only way is to pass the same db connection between threads.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
Thank you! I did try passing around the connection across threads(before 
reading the wiki at 
http://www.sqlite.org/cvstrac/wiki?p=MultiThreading), and things seemed 
to work without any trouble. Thanks again for clarifying.
Another question about in-memory databases. I have a producer-consumer 
sort of application, different threads keep on updating data in a table 
and after a scheduled interval, i extract aggregated data from the table 
and clean up the table, and in the worst cases the producers may flood 
the db before the cleanup happens,
is it possible to set a limit on the database size? how does sqlite 
behave when database size reaches the threshold(the default/configured one)?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [newbie] linking two tables with index...

[Mihai Limbasan]

Igor Tandetnik wrote:

You can't make this happen automatically with SQLite.
Hmm? Of course you can. Just use a trigger: 
http://sqlite.org/lang_createtrigger.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Three LEFT JOIN issues

[Igor Tandetnik]

"Csaba" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> 1)  In the following LEFT JOIN, is it possible to alter
> the query so that there is no w. prefix at the
> beginning of each returned column name:
>
> SELECT w.* FROM Words AS w LEFT JOIN Words as w2
>ON w.Id=w2.Id AND w.Lang=w2.Lang AND w.RevWHERE w2.Id IS NULL

select * from Words where not exists (
  select * from Words w2
  where Words.Id = w2.Id and Words.Lang=w2.Lang and Words.Rev 3)  Which of the following two queries is more efficient (Ie. is
> it better to have the w.Id=527 before or after the WHERE keyword)?

I doubt it makes any difference.

Igor Tandetnik



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

[sqlite] Three LEFT JOIN issues

[Csaba]

1)  In the following LEFT JOIN, is it possible to alter
the query so that there is no w. prefix at the
beginning of each returned column name:

SELECT w.* FROM Words AS w LEFT JOIN Words as w2
ON w.Id=w2.Id AND w.Lang=w2.Lang AND w.Revhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newbie questions regarding in-memory database.

[Igor Tandetnik]

"Karthik" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> Newbie question regarding in-memory databases. I read in the wiki
> pages that it is not safe to use same db connection across multiple
> threads.

It's actually safe with recent SQLite versions. A lot of issues have 
been eliminated. SQLite know uses a mutex to serialize concurrent API 
calls from multiple threads.

You have to know what you are doing of course. E.g. if one thread starts 
a transaction, then another thread issues some statements, they go into 
that transaction.

> I am trying to use an in-memory database. My understanding
> is that opening a connection with ":memory:" creates the database and
> closing it erases the same. If I were to use one connection per
> thread will i end up creating distinct memory db's for each thread?

Yes.

> is it possible to use a single in-memory db that can be accessed by
> different threads in a multithreaded environment?

The only way is to pass the same db connection between threads.

Igor Tandetnik



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

Re: [sqlite] SQLite assert failure in sqlite3FaultEndBenign()

[Andrew de los Reyes]

thanks very much!

On Mon, Jun 30, 2008 at 4:52 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> >
> >
> > It is not a bug because sqlite3FaultEndBenign() is used during testing
> > only.  And the tests in which this function are used only run a single
> > thread.
> >
>
>
> Recompile with -DSQLITE_OMIT_BUILTIN_TEST=1 to completely disable the
> EndBenign() function and its friends.  Or omit the -DSQLITE_DEBUG=1
> that you are currently using and the assertion will not fail.  Either
> way, it should start working for you.
>
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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] Multiple constraints per table?

[Igor Tandetnik]

"flakpit" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> is it legal sql syntax to allow more than one constraint field in
> table creation? I need at least these four below to guarantee that
> duplicate items do make it into the database but not on the same day.
>
> item TEXT CONSTRAINT item UNIQUE
> units TEXT CONSTRAINT units UNIQUE
> shop TEXT CONSTRAINT shop UNIQUE
> date TEXT CONSTRAINT date UNIQUE

It is possible to have multiple constraints, but declaring each field 
unique individually won't do what you want. Consider:

create table t(a unique, b unique);
insert into t values('a', 'b'); -- ok
insert into t values('a', 'c'); -- fails: column a is not unique
insert into t values('z', 'b'); -- fails: column b is not unique

create table t(a, b, unique(a, b));
insert into t values('a', 'b'); -- ok
insert into t values('a', 'c'); -- ok
insert into t values('z', 'b'); -- ok
insert into t values('z', 'b'); -- fails: this pair already exists

Igor Tandetnik 



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

Re: [sqlite] select with union fails on an attached database for 500 records

[Naganathan Rajesh]

Anyone has any clues on this?
 

Best Regards,
N.Rajesh
Courage is the knowledge of how to fear what ought to be feared and how not to 
fear what ought not to be feared.> From: [EMAIL PROTECTED]> To: 
sqlite-users@sqlite.org> Date: Fri, 27 Jun 2008 11:33:53 +> Subject: 
[sqlite] select with union fails on an attached database for 500 records> > > 
Hi All,> > We are facing a problem with the select command with union on an 
attached database and getting an error "SQL logic error or missing database"> > 
1.We have an active database connection (for eg a.db which contains a table 
with following columns)CREATE TABLE Contact_Primary_Info([uid] BIGINT PRIMARY 
KEY NOT NULL,[phonename] VARCHAR(41) ,[phonenumber] VARCHAR(42));> 2.We have 
created an in memory database with the attach database command (attach 
':memory:' as 'SIM' ) with a similar schema.CREATE TABLE SIM.Contact_SIM 
([itemId] BIGINT PRIMARY KEY NOT NULL,[name] VARCHAR(41) ,[number] 
VARCHAR(42));> where SIM is the attached database.> > 3.Now the usecase we want 
to get all the records in the Contact_Primary_Info and Contact_SIM table..The 
following is the query i wrote:> > select phonename,uid from 
contact_primary_info union select name,itemId from Contact_SIM order by 1 ASC;> 
> > We created 250 records in the SIM.Contact_SIM table and tried the above 
query for 10 to 100 records in the table Contact_Primary_Info.> It worked 
perfectly.When we create 500 records in the Contact_Primary_Info table,we are 
getting an error on the sqlite3_step command which states > "SQL logic error or 
missing database"> > > Can anyone please help us on whats going wrong with 
this?The system we are trying is an embedded device which runs on a ARM11 
processor.> > > Best Regards,> N.Rajesh> Courage is the knowledge of how to 
fear what ought to be feared and how not to fear what ought not to be feared.> 
_> 2000 
Placements last year. Are You next ? Find out> 
http://ss1.richmedia.in/recurl.asp?pid=499> 
___> sqlite-users mailing list> 
sqlite-users@sqlite.org> 
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_
Timely update on all current affairs, sports, events and all thats in News here 
on MSN videos.
http://video.msn.com/?mkt=en-in
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index and ORDER BY

[Alexey Pechnikov]

Really, there is problem with multi-column indexes. You must use only primary 
key index for ">=" where clause and "ASC" sorting and "<=" where clause and 
DESC sorting. 


1. I try with primary key:

#!/usr/bin/tclsh
package require sqlite3
sqlite3 db index_order.db
db eval {DROP TABLE IF EXISTS events}
db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)}
db transaction {
for {set i 0} {$i<1} {incr i} {
set type [expr {$i % 50}]
db eval {insert into events values ($i,$type)}
}
}
db close

So, "type" is equal ("eid" mod 50).

sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
eid DESC LIMIT 1;
32619722|22
CPU Time: user 0.00 sys 0.00

sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and 
type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events USING PRIMARY KEY ORDER BY


Result: this index is good.


2. And I try with two-columns common order index:
#!/usr/bin/tclsh
package require sqlite3
sqlite3 db index_order.db
db eval {DROP TABLE IF EXISTS events}
db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)}
db transaction {
for {set i 0} {$i<1} {incr i} {
set type [expr {$i % 50}]
db eval {insert into events values ($i,$type)}
}
}
db eval {CREATE INDEX ev_idx ON events(type,eid)}
db close

sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
eid DESC LIMIT 1;
32619722|22
CPU Time: user 1.400088 sys 1.696106

sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and 
type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events WITH INDEX ev_idx ORDER BY


Result: this index is bad.


3. And I try with two-columns desc order index:
#!/usr/bin/tclsh
package require sqlite3
sqlite3 db index_order.db
db eval {DROP TABLE IF EXISTS events}
db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY,type INTEGER)}
db transaction {
for {set i 0} {$i<1} {incr i} {
set type [expr {$i % 50}]
db eval {insert into events values ($i,$type)}
}
}
db eval {CREATE INDEX ev_desc_idx ON events(type asc,eid desc)}
db close

sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
eid DESC LIMIT 1;
32619722|22
CPU Time: user 0.600037 sys 0.608038

sqlite> EXPLAIN QUERY PLAN SELECT events.* FROM events WHERE eid<=32619760 and 
type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE events WITH INDEX ev_desc_idx ORDER BY


And with modified query:

sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY 
eid DESC LIMIT 1;
9972|22
CPU Time: user 0.00 sys 0.00
sqlite> SELECT events.* FROM events WHERE eid<=32619760 and type=22 ORDER BY 
eid ASC LIMIT 1;
22|22
CPU Time: user 0.00 sys 0.004000
sqlite> SELECT events.* FROM events WHERE eid>=32619760 and type=22 ORDER BY 
eid ASC LIMIT 1;
32619772|22
CPU Time: user 0.284018 sys 0.820051




Result: this index is bad.



P.S. Try with primary key index only and write your results.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA temp_store confusion

[Mihai Limbasan]

Roger Binns wrote:

Where is the appropriate place to discuss 3.6 issues?  For example in my
case I have very strong feelings about error codes vs return values in
the VFS api.
  

The sqlite-dev mailing list would be a good place.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users