Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Frank Missel]

Hi Bo,

> boun...@sqlite.org] On Behalf Of Bo Peng
> > I wonder if it would be better on just having the data organized
> > before loading it, so that the records in each of the 5000 tables
> > would be contiguously stored. Of course, that also depends on how much
> > new data will be added to the tables.
> 
> I have been thinking about this too, but I do not know how to do it
> efficiently.
>
>3. Create 5000 files, insert records to them, and import the files to the
main database. This seems to be the best option although I need to pay
>attention to OS limit on opened files.
> 
> Any suggest is welcome.

A variation of option 3 could is to load the data to an SQLite In-Memory
database (see http://www.sqlite.org/inmemorydb.html) in chunks so that every
time the in-memory database is full you open the disk files consecutively
one at a time appending the data to them from the corresponding table in the
memory database. Afterwards the in-memory database is cleared (close the
connection or drop the tables) and the cycle starts over.
This way you will not have to worry about the number of open files in the OS
which could give complications as you write.

You could of course code this this yourself holding the same amount of the
original records in a memory structure directly without using SQLite.
However, I could imagine that it would be faster to implement (and less
error prone) just using the SQLite in-memory database.

Once all 5000 files had been fully written they would then be loaded to the
final SQLite database.

> > Is the primary key an integer so that it in fact is the rowid thus
> > saving some space?
> > (see http://sqlite.org/lang_createtable.html, the section: "ROWIDs and
> > the INTEGER PRIMARY KEY".)
> 
> This certainly worth trying, as long as the performance is acceptable.
> It would be good if I can tell sqlite that there is no duplicate and
missing
> values so it does not have to check.

Well, if you don't have any particular use of a primary key its probably
best to not define any so that no processing time is used on it.
Also, if you do define a column as "INTEGER PRIMARY KEY" it becomes an alias
for the rowid for that record and thus will not be a value that you supply.
The rowid in this case will be frozen for a given record and stay the same
even when a vacuum is done on the database.

> > If there are several calculations to be done for each table it would
> > perhaps be better to work with several threads concentrating on a
> > single table at a time. This would be particularly effective if your
> > cache_size was set large enough for a table to be contained entirely
> > in memory as the threads could share the same connection and (as I
> > understand it) memory cache (see
> http://www.sqlite.org/sharedcache.html).
> 
> I am running SELECT COUNT(*), MAX(QT) FROM TABLE_XX; etc as long as the
> WHERE clause is the same. I am also running queries in multiple threads
> which works well on SSD drive, but not on mechanical drive due to the
> bottleneck on random-access speed. I am not sure if we are talking about
the
> same cache but because the tables are spread sparsely across the database,
> caching more pages did not really help.

What I mean here is that if you have a number of different COUNT, MAX or
other statistical functions to be calculated for example with several
different WHERE clauses (e.g. 10, 20 or 30), it will pay off to focus on one
table at a time performing all the queries (whether consecutively or
concurrently using several threads) as the table will be loaded to the
memory cache the first time it is accessed. This is assuming that a table
contains about a million records and thus can be contained entirely in
memory.
This will work regardless of whether you have organized your database so
that records physically are stored contiguously. Of course, it is still best
to have the data stored contiguously as that will give a huge load
improvement.
If there is only a single SELECT COUNT and MAX statement to be done for the
entire 5000 tables it will not make any difference.

> > One might even consider a different strategy:
> > Do not use the statistical function of SQLite (count, average, max,
> > etc.) but access the records individually in your code. This requires
> > that data are loaded according to the primary key and that the threads
> > accessing them do so in a turn based fashion, using the modulo
> > function to decide which thread gets to handle the record, e.g.:
> 
> If all tables have the same set of item IDs, this can be a really good
idea. This
> is unfortunately not the case because each tables have a different set of
IDs,
> despite of 70% to 90% overlapping keys. I even do not know in advance all
> available IDs.

Okay, so each table will have to be handled by itself. 


Best regards,

Frank

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

Re: [sqlite] triggers : NEW keyword with multiple tables

[Igor Tandetnik]

On 10/25/2011 10:59 AM, Sébastien Escudier wrote:


Hello,

I used to do something like this on older sqlite versions :
(this does not really makes sense here, but this is a simplified)
CREATE VIEW my_view AS SELECT table1.type, table2.type FROM table1 INNER
JOIN table2 ON table1.id = table2.id;

CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
BEGIN
INSERT INTO table1(type) VALUES(NEW.table1.type);
INSERT INTO table2(type) VALUES(NEW.table2.type);
END;


Try NEW."table1.type" and NEW."table2.type"
--
Igor Tandetnik

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

Re: [sqlite] Help with sqlite3OsRead - porting to a new OS

[David Garfield]

Also, if this is debugger output (as it appears), it could be that an
optimizer is confusing the parameters.  I see that all the time on GCC
i386 when I set a breakpoint at the start of a function.

--David

David Garfield writes:
> Sounds like it could be a difference in calling convention...  Check
> compile options and function declaration modifiers.
> 
> --David
> 
> Stuart Thomson writes:
> > Hi,
> > 
> > I'm in the middle of porting sqlite3 to a new Operating System and have 
> > come across a problem with the sqlite3OsRead function in the 
> > sqlite3PagerReadFileheader function body.
> > 
> > Before the sqlite3OsRead function is called, the parameters are correctly 
> > populated
> > 
> > for example:
> > pPager->fd= \u565c,  0x6208565c
> > pDest= \ufea8,   0x2000fea8
> > N  = d,  0x0064
> > Offset= 0
> > 
> > But when the function sqlite3OsRead is actually implemented the parameters 
> > are in the incorrect order. More specifically:
> > 
> > Sqlite3_file *id= d,  0x0064
> > Void *pBuf = \u565c,  0x6208565c
> > Int amt  = \ufea8,   0x2000fea8
> > I64 offset   = \0,  0x2000fea8
> > 
> > I have built a Database using sqlite3 3.7.5 and the code is built to 3.7.5.
> > 
> > Any ideas are welcome,
> > 
> > Cheers
> > Stuart
> > 
> > 
> > 
> > BitWise Ltd - Crescent House, Carnegie Campus, Dunfermline, KY11 8GR, 
> > United Kingdom
> > tel: +44 (0)1383 625151   -mob:
> > web: BitWise Group 
> > 
> > This e-mail may be confidential and privileged. Do not open it if you are 
> > in any doubt that you are the intended recipient. You must scan this e-mail 
> > and any attachments for the presence of viruses or any other unwelcome 
> > content. This e-mail must be read in conjunction with the important legal 
> > notice at BitWise Group/Legal 
> > ___
> > 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] Help with sqlite3OsRead - porting to a new OS

[David Garfield]

Sounds like it could be a difference in calling convention...  Check
compile options and function declaration modifiers.

--David

Stuart Thomson writes:
> Hi,
> 
> I'm in the middle of porting sqlite3 to a new Operating System and have come 
> across a problem with the sqlite3OsRead function in the 
> sqlite3PagerReadFileheader function body.
> 
> Before the sqlite3OsRead function is called, the parameters are correctly 
> populated
> 
> for example:
> pPager->fd= \u565c,  0x6208565c
> pDest= \ufea8,   0x2000fea8
> N  = d,  0x0064
> Offset= 0
> 
> But when the function sqlite3OsRead is actually implemented the parameters 
> are in the incorrect order. More specifically:
> 
> Sqlite3_file *id= d,  0x0064
> Void *pBuf = \u565c,  0x6208565c
> Int amt  = \ufea8,   0x2000fea8
> I64 offset   = \0,  0x2000fea8
> 
> I have built a Database using sqlite3 3.7.5 and the code is built to 3.7.5.
> 
> Any ideas are welcome,
> 
> Cheers
> Stuart
> 
> 
> 
> BitWise Ltd - Crescent House, Carnegie Campus, Dunfermline, KY11 8GR, United 
> Kingdom
> tel: +44 (0)1383 625151   -mob:
> web: BitWise Group 
> 
> This e-mail may be confidential and privileged. Do not open it if you are in 
> any doubt that you are the intended recipient. You must scan this e-mail and 
> any attachments for the presence of viruses or any other unwelcome content. 
> This e-mail must be read in conjunction with the important legal notice at 
> BitWise Group/Legal 
> ___
> 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] triggers : NEW keyword with multiple tables

[Doug Currie]

On Oct 25, 2011, at 10:59 AM, Sébastien Escudier wrote:

> CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
> BEGIN
> INSERT INTO table1(type) VALUES(NEW.table1.type);
> INSERT INTO table2(type) VALUES(NEW.table2.type);
> END;
> 
> ...
> 
> Why this syntax does not work anymore ?

You haven't given the view explicit column names, and the ones SQLite3 invents 
are arbitrary; try this instead:

CREATE VIEW my_view AS SELECT table1.type as table1_type, table2.type as 
table2_type FROM 

…

CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
BEGIN
INSERT INTO table1(type) VALUES(NEW.table1_type);
INSERT INTO table2(type) VALUES(NEW.table2_type);
END;


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

[sqlite] triggers : NEW keyword with multiple tables

[Sébastien Escudier]

Hello,

I used to do something like this on older sqlite versions :
(this does not really makes sense here, but this is a simplified)
CREATE VIEW my_view AS SELECT table1.type, table2.type FROM table1 INNER
JOIN table2 ON table1.id = table2.id;

CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view
BEGIN
INSERT INTO table1(type) VALUES(NEW.table1.type);
INSERT INTO table2(type) VALUES(NEW.table2.type);
END;


But the latest version gives this error :
no such column: NEW.table1.type

If I replace NEW.table1.type and NEW.table2.type with NEW.type it gives
no error but it does not work correctly because it always takes the
value of table1.type.

Why this syntax does not work anymore ?

Regards 
Sebastien


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

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Kit]

2011/10/25 Bo Peng :
> Tables are added in batch and then kept unchanged. I mean, a database
> might have 1000 new tables one day, and 2000 later. All operations are
> on single tables.
>
> Each table is for one 'sample'. All tables have one column for 'item
> id', and optional (zero - perhaps 6) columns for item attributes,
> which can be INT or FLOAT.

Is your 'id' type INTEGER PRIMARY KEY?
Each table in SQLite have a hidden key 'rowid INTEGER PRIMARY KEY'.
You may merge it with 'id'.

> There is no index and no key because I was concerned about insertion
> performance and size of database. My understanding is that index or
> key would not help simple aggregation operations because all records
> will be iterated sequentially.

If you do not need indexes, you can use a simpler solution: CSV.
One sample (now a table) -> one CSV file. Searching will be faster.

> I am running SELECT COUNT(*), MAX(QT) FROM TABLE_XX; etc as long as
> the WHERE clause is the same.

You can create additional indexes after inserting data into a table.
To search for maximum serve well.

If the content of tables also constant, it is possible after a
creating to generate
the aggregated values? For additional search would only use this summary data.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Concurrent readonly access to a large database.

[Bo Peng]

> Doing vacuum on a 288 Gb database is probably going to take some time.

I submitted the command yesterday night and nothing seems to be
happening after 8 hours (sqlite3 is running and there is disk
activity, but I do not see a .journal file).

> I wonder if it would be better on just having the data organized before
> loading it, so that the records in each of the 5000 tables would be
> contiguously stored. Of course, that also depends on how much new data will
> be added to the tables.

I have been thinking about this too, but I do not know how to do it
efficiently.

1. Create a temporary database and copy tables one by one to the main
database. This will take a very long time for databases with for
example 5000 tables.

2. Create single-table databases and merge them to the main database.
Because there is a limit on simultaneously attached databases, I might
not be able to create and attach 5000 databases, insert records, and
copy the tables to the main database afterwards. I might be able to
set SQLITE_LIMIT_ATTACHED dynamically, but I do not know how to do
this in Python sqlite3 module.

3. Create 5000 files, insert records to them, and import the files to
the main database. This seems to be the best option although I need to
pay attention to OS limit on opened files.

4. Insert records to some external tools (e.g. a levelDB database, or
even pipe to a sort command) to sort the records before they are
inserted to sqlite.

Any suggest is welcome.

> How many new tables/records will be added per day/month?

Tables are added in batch and then kept unchanged. I mean, a database
might have 1000 new tables one day, and 2000 later. All operations are
on single tables.

> Are records divided amongst the 5000 tables  based on time so that new
> records will go into new tables rather than be inserted evenly among the
> 5000?

No. The records have to be inserted to their associated tables.

> How many fields in the tables (I understand the 5000 tables are identical in
> structure)
> What type of data is it?

Each table is for one 'sample'. All tables have one column for 'item
id', and optional (zero - perhaps 6) columns for item attributes,
which can be INT or FLOAT.

> Are there any indexes besides the primary key?

There is no index and no key because I was concerned about insertion
performance and size of database. My understanding is that index or
key would not help simple aggregation operations because all records
will be iterated sequentially.

> Unless your calculations are always or mostly on the same fields it is
> probably best not to have any indexes.

I agree.

> Are there any redundancy in the data, e.g. character values which could be
> normalized to separate tables using an integer key reference thus reducing
> the size of the data carrying tables. Converting field contents to integer
> or bigint wherever it is possible may give improvements in both size and
> performance.

Unfortunately no, all columns are int or float. Some columns have a
large proportion of NULLs, but I do not know that before all data are
inserted, so I cannot separate them to auxiliary tables.

> Is the primary key an integer so that it in fact is the rowid thus saving
> some space?
> (see http://sqlite.org/lang_createtable.html, the section: "ROWIDs and the
> INTEGER PRIMARY KEY".)

This certainly worth trying, as long as the performance is acceptable.
It would be good if I can tell sqlite that there is no duplicate and
missing values so it does not have to check.

> If there are several calculations to be done for each table it would perhaps
> be better to work with several threads concentrating on a single table at a
> time. This would be particularly effective if your cache_size was set large
> enough for a table to be contained entirely in memory as the threads could
> share the same connection and (as I understand it) memory cache (see
> http://www.sqlite.org/sharedcache.html).

I am running SELECT COUNT(*), MAX(QT) FROM TABLE_XX; etc as long as
the WHERE clause is the same. I am also running queries in multiple
threads which works well on SSD drive, but not on mechanical drive due
to the bottleneck on random-access speed. I am not sure if we are
talking about the same cache but because the tables are spread
sparsely across the database, caching more pages did not really help.

> One might even consider a different strategy:
> Do not use the statistical function of SQLite (count, average, max, etc.)
> but access the records individually in your code. This requires that data
> are loaded according to the primary key and that the threads accessing them
> do so in a turn based fashion, using the modulo function to decide which
> thread gets to handle the record, e.g.:

If all tables have the same set of item IDs, this can be a really good
idea. This is unfortunately not the case because each tables have a
different set of IDs, despite of 70% to 90% overlapping keys. I even
do not know in advance all available IDs.

[sqlite] [patch] Re: Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

[Yuriy Kaminskiy]

David wrote:
> Simon L wrote 2011-10-25 06:20:
>> To reproduce this problem, enter the following 5 SQL statements at the
>> SQLite command line.
>>
>> create table X(id INTEGER primary key ON CONFLICT REPLACE);
>> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
>> insert into X values (1);
>> insert into Y select * from X;
>> insert into Y select * from X;
>>
>>
>> When I tried to run the last SQL statement twice,  SQLite produced the
>> following error message.
>> Error: PRIMARY KEY must be unique
>>
>>
>> Is this a bug? Please advise. Thank you.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> This certainly looks like a bug. I got a constraint failure when I tried
> it in sqlite 3.7.8.
> 
> But it works fine when you state the column name explicitly in the
> select clause.
> 
> Like this:
> 
> create table X(id INTEGER primary key ON CONFLICT REPLACE);
> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
> insert into X values (1);
> insert into Y select id from X;
> insert into Y select id from X;
>
> I recall the "INSERT INTO ... SELECT * ..." had a bug related to foreign
> key checks,
> which was reported on the mailing list earlier this year:
> 
> http://www.sqlite.org/src/tktview?name=6284df89de
> 
> Hopefully, a member of the sqlite dev team will acknowledge this bug soon.

Thanks for pointer; root cause, indeed, transfer optimization (it ignores table
INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use table's
ON CONFLICT clause by default; falls back to regular transfer if destination
table is not empty and we cannot handle ON CONFLICT resolution);

Disclaimer: /me is not sqlite dev team member, review carefully, use with care.

The author or authors of this code dedicate any and all copyright interest
in this code to the public domain. We make this dedication for the benefit
of the public at large and to the detriment of our heirs and successors.
We intend this dedication to be an overt act of relinquishment in perpetuity
of all present and future rights to this code under copyright law.

Signed-off-by: Yuriy M. Kaminskiy 

Index: sqlite3-3.7.8/src/insert.c
===
--- sqlite3-3.7.8.orig/src/insert.c 2011-10-25 15:20:26.0 +0400
+++ sqlite3-3.7.8/src/insert.c  2011-10-25 15:54:54.0 +0400
@@ -1626,6 +1626,7 @@ static int xferOptimization(
   int regAutoinc;  /* Memory register used by AUTOINC */
   int destHasUniqueIdx = 0;/* True if pDest has a UNIQUE index */
   int regData, regRowid;   /* Registers holding data and rowid */
+  int keyConf = pDest->iPKey>=0 ? pDest->keyConf : OE_Default;

   if( pSelect==0 ){
 return 0;   /* Must be of the form  INSERT INTO ... SELECT ... */
@@ -1639,7 +1640,22 @@ static int xferOptimization(
   }
 #endif
   if( onError==OE_Default ){
-onError = OE_Abort;
+if( keyConf==OE_Abort || keyConf==OE_Rollback )
+  /* can be handled - take ON CONFLICT from table declaration */
+  onError = keyConf;
+else {
+  if( keyConf==OE_Default )
+keyConf = OE_Abort;
+  else {
+/* Not default and cannot be handled;
+** fallback to regular transfer if destination is not empty (below)
+*/
+  }
+  onError = OE_Abort;
+}
+  } else {
+/* statement ON CONFLICT overrides table ON CONFLICT */
+keyConf = onError;
   }
   if( onError!=OE_Abort && onError!=OE_Rollback ){
 return 0;   /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
@@ -1766,7 +1782,8 @@ static int xferOptimization(
   iDest = pParse->nTab++;
   regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
   sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
-  if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ){
+  if( (pDest->iPKey<0 && pDest->pIndex!=0) || destHasUniqueIdx ||
+  (/*pDest->iPKey>=0 && */keyConf!=onError) ) {
 /* If tables do not have an INTEGER PRIMARY KEY and there
 ** are indices to be copied and the destination is not empty,
 ** we have to disallow the transfer optimization because the
@@ -1776,6 +1793,9 @@ static int xferOptimization(
 ** we also disallow the transfer optimization because we cannot
 ** insure that all entries in the union of DEST and SRC will be
 ** unique.
+**
+** Or if destination INTEGER PRIMARY KEY has ON CONFLICT clause
+** that we cannot handle and destination is not empty.
 */
 addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0);
 emptyDestTest = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0);

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

Re: [sqlite] Virtual Tables and tcl / apsw

[Dan Kennedy]

On 10/25/2011 04:28 PM, Alexey Pechnikov wrote:

2011/10/25 Dan Kennedy:

Not possible. The Tcl interface has no bindings for either the
virtual table or VFS interfaces.


But why? Is there any technical/ideological problems?



None that are insurmountable, I would think. Just that
those interfaces were not considered all that useful for
scripting languages. That's a matter of opinion though
obviously.



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

Re: [sqlite] Virtual Tables and tcl / apsw

[Alexey Pechnikov]

2011/10/25 Dan Kennedy :
> Not possible. The Tcl interface has no bindings for either the
> virtual table or VFS interfaces.

But why? Is there any technical/ideological problems?

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with FTS4 - Floating point error.

[Mohd Radzi Ibrahim]

On 25-Oct-2011, at 2:53 PM, Dan Kennedy wrote:

> This was fixed and then I forgot to follow up here. I should have.
> Sorry about that. It's fixed here:
> 
>  http://www.sqlite.org/src/ci/3126754c72?sbs=0
> 
> Either updating to the latest trunk or just applying the linked
> patch to fts3.c should fix your crash.
> 
> Dan.
> 

I've clone the fossil repository and tried to run make. I am having problem at 
linking stage with these errors:

/tmp/ccwXZcmF.o: In function `one_input_line':
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:393: undefined 
reference to `readline'
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:395: undefined 
reference to `add_history'
/tmp/ccwXZcmF.o: In function `main':
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:2949: undefined 
reference to `read_history'
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:2953: undefined 
reference to `stifle_history'
/home/radzi/devtools/sqlite-canonical/bld/../src/shell.c:2954: undefined 
reference to `write_history'

I am running on Ubuntu 10.10 and have installed readline6.2.

Any suggestion?

thanks.

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

Re: [sqlite] Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

[David]

Simon L wrote 2011-10-25 06:20:

To reproduce this problem, enter the following 5 SQL statements at the
SQLite command line.

create table X(id INTEGER primary key ON CONFLICT REPLACE);
create table Y(id INTEGER primary key ON CONFLICT REPLACE);
insert into X values (1);
insert into Y select * from X;
insert into Y select * from X;


When I tried to run the last SQL statement twice,  SQLite produced the
following error message.
Error: PRIMARY KEY must be unique


Is this a bug? Please advise. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This certainly looks like a bug. I got a constraint failure when I tried it in 
sqlite 3.7.8.

But it works fine when you state the column name explicitly in the select 
clause.

Like this:

create table X(id INTEGER primary key ON CONFLICT REPLACE);
create table Y(id INTEGER primary key ON CONFLICT REPLACE);
insert into X values (1);
insert into Y select id from X;
insert into Y select id from X;

I recall the "INSERT INTO ... SELECT * ..." had a bug related to foreign key 
checks,
which was reported on the mailing list earlier this year:

http://www.sqlite.org/src/tktview?name=6284df89de

Hopefully, a member of the sqlite dev team will acknowledge this bug soon.
--
Regards,
David

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

[sqlite] Help with sqlite3OsRead - porting to a new OS

[Stuart Thomson]

Hi,

I'm in the middle of porting sqlite3 to a new Operating System and have come 
across a problem with the sqlite3OsRead function in the 
sqlite3PagerReadFileheader function body.

Before the sqlite3OsRead function is called, the parameters are correctly 
populated

for example:
pPager->fd= \u565c,  0x6208565c
pDest= \ufea8,   0x2000fea8
N  = d,  0x0064
Offset= 0

But when the function sqlite3OsRead is actually implemented the parameters are 
in the incorrect order. More specifically:

Sqlite3_file *id= d,  0x0064
Void *pBuf = \u565c,  0x6208565c
Int amt  = \ufea8,   0x2000fea8
I64 offset   = \0,  0x2000fea8

I have built a Database using sqlite3 3.7.5 and the code is built to 3.7.5.

Any ideas are welcome,

Cheers
Stuart



BitWise Ltd - Crescent House, Carnegie Campus, Dunfermline, KY11 8GR, United 
Kingdom
tel: +44 (0)1383 625151   -mob:
web: BitWise Group 

This e-mail may be confidential and privileged. Do not open it if you are in 
any doubt that you are the intended recipient. You must scan this e-mail and 
any attachments for the presence of viruses or any other unwelcome content. 
This e-mail must be read in conjunction with the important legal notice at 
BitWise Group/Legal 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual Tables and tcl / apsw

[Dan Kennedy]

On 10/25/2011 02:31 PM, sqlite-us...@h-rd.org wrote:

Hi,

I have some questions on virtual tables and tcl compared to perl and
python/apsw.

As I understand you can build your own virtual table implementation with
apsw (and also with perl). Is this also possible with tclsqlite?
Any pointers greatly appreciated, I could not find it in the docs.

A bit related is a question on the vfs. apsw allows to implement a vfs.
Is this also possible with tcl?


Not possible. The Tcl interface has no bindings for either the
virtual table or VFS interfaces.

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

[sqlite] Virtual Tables and tcl / apsw

[sqlite-us...@h-rd.org]

Hi,

I have some questions on virtual tables and tcl compared to perl and  
python/apsw.


As I understand you can build your own virtual table implementation  
with apsw (and also with perl).  Is this also possible with tclsqlite?

Any pointers greatly appreciated, I could not find it in the docs.

A bit related is a question on the vfs.  apsw allows to implement a  
vfs.  Is this also possible with tcl?


thanks,


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

Re: [sqlite] Problem with FTS4 - Floating point error.

[Dan Kennedy]

On 10/25/2011 12:49 PM, Mohd Radzi Ibrahim wrote:


On 18-Oct-2011, at 6:52 PM, Dan Kennedy wrote:




On 10/18/2011 05:02 PM, Mohd Radzi Ibrahim wrote:

Hi,

This is my table schema:

CREATE VIRTUAL TABLE LocationFTS using FTS4
(
name text,
address text,
email text,
remark text,
telno text,
contact text,
isDeleted text
);

This select statement gives floating point error in both sqlite3.exe and also 
my program (both my Ubuntu and Windows versions failed) :

select docid, name, address, email, remark, telNo, contact
from locationFTS
where locationFTS match 'suai plantation sdn bhd, p.o.box 730 97008 bintulu 
swk, tel 2 07-6637460 ah leng   06-9766061 a3.0';

PharmacyServer.exe!GenericKedaiRuncitDB::GetLocationFTS(LocationRecord& 
  r)  Line 147 + 0x14 bytes C++
PharmacyServer.exe!wxSQLite3Statement::ExecuteQuery(bool 
transferStatementOwnership)  Line 1486 + 0xc bytes C++
PharmacyServer.exe!sqlite3_step(sqlite3_stmt * pStmt)  Line 62029 + 0x9 
bytes   C
PharmacyServer.exe!sqlite3Step(Vdbe * p)  Line 61954 + 0x9 bytes
C
PharmacyServer.exe!sqlite3VdbeExec(Vdbe * p)  Line 3856 + 0x30 bytes
C
PharmacyServer.exe!fts3FilterMethod(sqlite3_vtab_cursor * pCursor, int 
idxNum, const char * idxStr, int nVal, Mem * * apVal)  Line 51378 + 0x9 bytes   
 C
PharmacyServer.exe!fts3EvalStart(Fts3Cursor * pCsr)  Line 52714 + 0x13 
bytesC
PharmacyServer.exe!fts3EvalSelectDeferred(Fts3Cursor * pCsr, Fts3Expr * 
pRoot, Fts3TokenAndCost * aTC, int nTC)  Line 52634 + 0x2a bytesC

Does anybody know what's going on here?


Can you send me the database by email? Not via the list,
as it will strip the attachment.

Dan Kennedy.



Hi,

I've been chasing this bug and tried couple of options and found out that these 
issues fix my problem:

1. The number of words in match string is capped at 16.
2. These characters in the match string I replaced with spaces.
 case ',':
 case '.':
 case '/':
 case '-':
 case ':':
 case '\'':
 case '"':
 case '(':
 case ')':
 case '\\':
 case '@':

Was it a bug or was it the limitation? Or Perhaps I missed the documentation on 
FTS4.


This was fixed and then I forgot to follow up here. I should have.
Sorry about that. It's fixed here:

  http://www.sqlite.org/src/ci/3126754c72?sbs=0

Either updating to the latest trunk or just applying the linked
patch to fts3.c should fix your crash.

Dan.



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