[sqlite] SQLite 3.5.9 bug with journals and file locking

If a transaction is opened on a DB for which a journal file exists, and fcntl() returns EACCES or EPERM on the attempt to acquire a write lock on the DB to replay the journal, SQLite 3.5.9 quietly ignores the journal without replaying it and continues on. This is a serious bug. SQLite 3.6.14.2

[sqlite] Fast data duplication

Hello, We need to implement fast Insert into users ItemID, Name Select ItemID, Name from users where itemActive = 1; The short description is: we need to copy many records of a table in the same table but changing the "Name" value. So we have added a new function which process the

[sqlite] Alignment bug in SQLIte 3.6.14.2

We've run into an alignment issue in SQLite 3.6.14.2 which only seems to cause a problem under Solaris Sparc in our testing. Failure narrowed down to: src/rowset.c:186 pEntry->v = rowid pEntry is 0xX4 __alignof(*pEntry) is 8 (because of an i64) However sizeof(RowSet) is 28 (and 28%8 = 4),

Re: [sqlite] Slow Transaction Speed?

> On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm > wrote: > [..] >> So the question is: >> Is it somehow normal to have only 7 transactions per second? > > Yes ehm... why? > > [..] >> Any comment on this ? > > http://www.sqlite.org/faq.html#q19 the faq as well as the

[sqlite] suggested changes to file format document

1. In the following, s/less than/less than or equal to/ """ 2.3.3.4 Index B-Tree Cell Format [snip 2 paragraphs] If the record is small enough, it is stored verbatim in the cell. A record is deemed to be small enough to be completely stored in the cell if it consists of less than:

Re: [sqlite] Question on errors - IOERR and CANTOPEN

Hi Filip, > what SQLite version and on what platform are you using? There was a > bug in SQLite 3.6.13 on Windows where SQLITE_CANTOPEN was incorrectly > returned during journal check when race condition between two threads > was hit. Also there could be some other software interfering with the >

Re: [sqlite] Slow Transaction Speed?

On Tue, May 26, 2009 at 9:47 PM, Marcus Grimm wrote: [..] > So the question is: > Is it somehow normal to have only 7 transactions per second? Yes [..] > Any comment on this ? http://www.sqlite.org/faq.html#q19 Regards, ~Nuno Lucas

Re: [sqlite] create index on view

Hi Wying, > Create View MyView1 as > SELECT > t1.A + t2.A as Col1 > , t1.B + t2.B as Col2 > , t2.Cas Col3 > ... > > Create View MyView2 as > SELECT > t1.A + t3.A as Col1 > , t1.B + t3.B as Col2 > , t3.Cas Col3 > ... > In the users' query:- > SELECT > ... > FROM >

Re: [sqlite] Question on errors - IOERR and CANTOPEN

Hi Dennis, what SQLite version and on what platform are you using? There was a bug in SQLite 3.6.13 on Windows where SQLITE_CANTOPEN was incorrectly returned during journal check when race condition between two threads was hit. Also there could be some other software interfering with the journal

Re: [sqlite] database file header: "schema layer file format" anomaly

On 27/05/2009 3:03 AM, D. Richard Hipp wrote: > John - what were you doing when you discovered this? > > On May 26, 2009, at 10:57 AM, John Machin wrote: > >> According to the file format document >> (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block >> starting at byte offset

Re: [sqlite] Select on foreign key NULL

Thanks a lot. Both works fine. Leo John Machin schrieb: > On 27/05/2009 1:09 AM, Leo Freitag wrote: > >> Hallo, >> >> I got some problems with a select on a foreign key with value null. >> I want to filter all male singers. >> >> CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT,

[sqlite] Slow Transaction Speed?

Hello List, I have a slightly dissapointing issue here with the update/insert speed of sqlite3 on win32 using version 3.6.14.1: On my system a single INSERT statement needs appx. 150ms, the same is the case for DELETE statements. I test this by doing single INSERTs in a loop. Of course, the speed

Re: [sqlite] create indexed view

On Tue, 26 May 2009 14:44:25 +0800, wying wy wrote: >Hi > >May I know if we can create index on a View? You can't create an index on a VIEW. A VIEW can be seen as a stored SELECT statement. >Thanks in advance. >wying -- ( Kees Nuyt ) c[_]

Re: [sqlite] a problem with sqlite3_get_table

Enrico Piccinini wrote: > thanks Pavel, I tried but the problem persist. > > I used ostringstream in a lot of function and query in my sw. But the > problem arise in thi function only when I substitute an INNER JOIN > with thew > LEFT JOIN. In fact, if I keep the INNER

Re: [sqlite] a problem with sqlite3_get_table

Pavel Ivanov wrote: > Stringstream never puts 0 byte at the end of the string. Does too. You might be thinking about ostrstream (whose str() method returns char* pointing to a non-NUL-terminated buffer), but the OP uses ostringstream, whose str() method returns an

Re: [sqlite] Getting last inserted rowid?

Nikolaus Rath wrote: > How can I determine the rowid of the last insert if I am accessing the > db from different threads? If I understand correctly, > last_insert_rowid() won't work reliably in this case. Last inserted rowid is maintained per connection. Do your threads use

Re: [sqlite] Getting last inserted rowid?

On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: > Hello, > > How can I determine the rowid of the last insert if I am accessing the > db from different threads? If I understand correctly, > last_insert_rowid() won't work reliably in this case. It should work if you do:

[sqlite] Getting last inserted rowid?

Hello, How can I determine the rowid of the last insert if I am accessing the db from different threads? If I understand correctly, last_insert_rowid() won't work reliably in this case. I can't believe that I really have to do a SELECT on the data that I just INSERTed only to get the rowid...

Re: [sqlite] database file header: "schema layer file format" anomaly

John - what were you doing when you discovered this? On May 26, 2009, at 10:57 AM, John Machin wrote: > According to the file format document > (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block > starting at byte offset 44 of a well-formed database file, the schema > layer file

Re: [sqlite] a problem with sqlite3_get_table

thanks Pavel, I tried but the problem persist. I used ostringstream in a lot of function and query in my sw. But the problem arise in thi function only when I substitute an INNER JOIN with thew LEFT JOIN. In fact, if I keep the INNER JOIN instead LEFT JOIN evrthing works as it would. Using

Re: [sqlite] a problem with sqlite3_get_table

Instead of exe_query(query.str()); try to do this: size_t len = query.pcount(); exe_query(string(query.str(), len).c_str()); Stringstream never puts 0 byte at the end of the string. Pavel On Tue, May 26, 2009 at 12:12 PM, Enrico Piccinini wrote: > Hy to all, I'm

[sqlite] a problem with sqlite3_get_table

Hy to all, I'm very new to database and sqlite. I'm writing a C++ code to execute some queries. A query of mine does a select with some inner join and a left join as reported here: //inline int getFunctList(const string& testName, vector& result, int& nCol, int& nRow) //{ [...] ostringstream

Re: [sqlite] Minor errors in CREATE TABLE syntax diagrams

On 27/05/2009 12:33 AM, Jim Wilcoxson top-posted: > For my money, I'd prefer to have a smaller, faster parser that worked > correctly on correct input at the expense of not catching all possible > syntax errors on silly input. Firstly, none of the examples that I gave are syntactically incorrect.

Re: [sqlite] Select on foreign key NULL

On 27/05/2009 1:09 AM, Leo Freitag wrote: > Hallo, > > I got some problems with a select on a foreign key with value null. > I want to filter all male singers. > > CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT, > 'fkvoice' INTEGER, 'sex' TEXT); > INSERT INTO "tblsinger"

[sqlite] Select on foreign key NULL

Hallo, I got some problems with a select on a foreign key with value null. I want to filter all male singers. CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT, 'fkvoice' INTEGER, 'sex' TEXT); INSERT INTO "tblsinger" VALUES(1,'Anna Netrebko',1, 'f'); INSERT INTO "tblsinger"

Re: [sqlite] Minor errors in CREATE TABLE syntax diagrams

For my money, I'd prefer to have a smaller, faster parser that worked correctly on correct input at the expense of not catching all possible syntax errors on silly input. There is a definite trade-off here, and I could see where a totally complete parser that caught every possible error in SQL

Re: [sqlite] Select on foreign key NULL

2009/5/26 Leo Freitag : > Hallo, > > I got some problems with a select on a foreign key with value null. > I want to filter all male singers. > > CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT, > 'fkvoice' INTEGER, 'sex' TEXT); > INSERT INTO "tblsinger"

Re: [sqlite] create index on view

Hi Tom Thanks. Here you go:- CREATE TABLE Table1 (A INTEGER ,B INTEGER) (10 rows) CREATE TABLE Table2 (A INTEGER ,B INTEGER, C TEXT, D TEXT) (100 rows) CREATE TABLE Table3 (A INTEGER ,B INTEGER, C TEXT) (50 rows) Create Index X1 on Table1 (A, B) Create Index X2 on Table2 (A, B) Create Index

Re: [sqlite] database file header: "schema layer file format" anomaly

On May 26, 2009, at 9:57 PM, John Machin wrote: > According to the file format document > (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block > starting at byte offset 44 of a well-formed database file, the schema > layer file format, contains a big-endian integer value between 1

[sqlite] database file header: "schema layer file format" anomaly

According to the file format document (http://www.sqlite.org/fileformat.html): "[H30120] The 4 byte block starting at byte offset 44 of a well-formed database file, the schema layer file format, contains a big-endian integer value between 1 and 4, inclusive." However it is possible to end up

[sqlite] Minor errors in CREATE TABLE syntax diagrams

1. SQLite allows NULL as a column-constraint. E.g. CREATE TABLE tname (col0 TEXT NOT NULL, col1 TEXT NULL); The column-constraint diagram doesn't show this possibility. Aside: The empirical evidence is that NULL is recognised and *ignored*; consequently there is no warning about sillinesses

[sqlite] DBI sqlite usage problem: Insert seems asynchronous, and data lost at CTRL-C

I am using sqlite with DBI in a perl SMTP system. The program runs until it gets terminated, currently using CTRL-C but in the future when all runs fine, using kill of a daemon process. It appears that when I do an insert in the code, the real insert is sometimes delayed for quite a while, and is

Re: [sqlite] FTS3

thank you, this helped a lot and confirmed what I expected. Best Martin Von: D. Richard Hipp An: General Discussion of SQLite Database Gesendet: Dienstag, den 26. Mai 2009, 12:27:59 Uhr Betreff: Re: [sqlite] FTS3 On

Re: [sqlite] create index on view

Hi wying, > May I know if we can create index on a View? > Otherwise, is there any recommendation to speed up the query > involving join > between two Views? No you can't create an index on a view, but you can create an index on the underlying tables that the view uses. Two preliminary

Re: [sqlite] (no subject)

On Tue, May 26, 2009 at 6:34 PM, John Machin wrote: > Don't try that with your 100MB database without ensuring that your > keyboard interrupt mechanism isn't seized up :-) > > Perhaps you meant > > .schema tablename I did indeed. I even remember going, "oh, yeah, don't want

Re: [sqlite] (no subject)

On 26/05/2009 7:58 PM, Samuel Baldwin wrote: > On Tue, May 26, 2009 at 4:45 PM, Martin.Engelschalk > wrote: >> select * from sqlite_master; > > Or: > .dump tablename Don't try that with your 100MB database without ensuring that your keyboard interrupt mechanism isn't

[sqlite] create index on view

Hi May I know if we can create index on a View? Otherwise, is there any recommendation to speed up the query involving join between two Views? Thanks in advance. wying ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] FTS3

On May 26, 2009, at 5:03 AM, Martin Pfeifle wrote: > Dear all, > we need full and fuzzy text search for addresses. > Currently we are looking into Lucene and SQLite's FTS extension. > For us it is crucial to understand the file structures and the > concepts behind the libraries. > Is there a

Re: [sqlite] (no subject)

On Tue, May 26, 2009 at 4:45 PM, Martin.Engelschalk wrote: > select * from sqlite_master; Or: .dump tablename -- Samuel 'Shardz' Baldwin - staticfree.info/~samuel ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] create indexed view

Hi May I know if we can create index on a View? Thanks in advance. wying ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS3

Dear all, we need full and fuzzy text search for addresses. Currently we are looking into Lucene and SQLite's FTS extension. For us it is crucial to understand the file structures and the concepts behind the libraries. Is there a self-contained, comprehensive document for FTS3 (besides the

Re: [sqlite] (no subject)

Hi, select * from sqlite_master; Martin PS.: Please provide a subject which summarises your question. Manasi Save schrieb: > Hi All, > > Can anyone help me out with the command to see the SQLite table defination > on command-line SQLite application. >

[sqlite] (no subject)

Hi All, Can anyone help me out with the command to see the SQLite table defination on command-line SQLite application. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. manasi.s...@artificialmachines.com Ph:- 9833537392 ___

Re: [sqlite] Simple Outer Join question?

Hi Igor, thanks, works fine! Where do I find a tutorial that deals with 'IN', 'NOT IN' and subquerys in general? Leo Igor Tandetnik schrieb: > "Kees Nuyt" wrote in > message news:8u3m151rqbbel40ilsvaatqmfhcnhsj...@dim53.demon.nl > >> On Mon, 25 May 2009 23:14:50 +0200,

[sqlite] sqlite case and accent insensitive searches

I'm porting to sqlite a database from MySQL. on MYSQL, I was able, with the proper collation, to perform case insensitive and also accent insensitive searches on the db. I mean, a search like this: search * from table where description like '%cafe%' matches the descriptions containing cafe

[sqlite] create indexed view

Hi May I know if we can create index on a View? Thanks in advance. wying ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users