Re: [sqlite] VACUUM & journal size

2010-03-15 Thread Matthew L. Creech
On Sun, Mar 14, 2010 at 9:18 PM, Jay A. Kreibich wrote: > >  Are you sure it is the journal file that is growing too large? > ... > >  Now, if I'm following you correctly, the numbers you gave seem to >  indicate that this should work... If the old database is 100MB and >  the new

Re: [sqlite] FTS3 (Version 2 .6.23) - Is the boolean operator "AND" no more available?

2010-03-15 Thread Dan Kennedy
On Mar 14, 2010, at 5:34 AM, gerard.jouannot wrote: > Hello all SQLlite users. > > I wonder if the operator "AND" (in capitals letters) is yet > available and > different from the basic term "and" (in lower letters). Available only if FTS3 is compiled with this option:

[sqlite] Attach a database dynamically if not already available within a trigger

2010-03-15 Thread andy
Hello I am hoping someone can help with the following problem. - I want to create a log of all changes to tables in a database. - I would like to keep the log table in a separate database to the main data files - I plan to use triggers to capture changes and insert them into the log table.

[sqlite] How can I deal with hexadecimal numerical value?

2010-03-15 Thread How can I deal with hex numerical value?
sqlite doesn't support 0xFF in SQL, such as: select 0xFF; insert into t values(0xFF); Error: unrecognized token: "0xFF" But sometime, this is not convenient. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] On conflicting Primary key how to insert record and increment the Key

2010-03-15 Thread dravid11
Hello ! I have been trying to search for this solutions for days,yet did not find the solution. I want to write an insert query on a table. When there is a conflicting primary key then it should increment the primary key and insert the row . For example i want to run this query INSERT INTO

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

2010-03-15 Thread Tim Romano
If all you want to do is to insert a new row, do not mention the primary key column name in the insert statement: INSERT INTO temp (address, weight) values( "blah blah", 100) The autoincrementing primary key will be autoincremented. Regards Tim Romano On 3/15/2010 9:15 AM, dravid11 wrote: >

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

2010-03-15 Thread dravid11
Well the situation is that i am merging data of one table in data of another table using a program so it is going to add all values by it self .I am not actually using insert query in that case to select values to add. There is another scenario , what if inserting a data and i want to bypass

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

2010-03-15 Thread P Kishor
On Mon, Mar 15, 2010 at 8:32 AM, dravid11 wrote: > > Well the situation is that i am merging data of one table in data of another > table using a program > so it is going to add all values by it self .I am not actually using insert > query in that case to select values to

Re: [sqlite] How can I deal with hexadecimal numerical value?

2010-03-15 Thread Pavel Ivanov
If you need to insert integer value then you'll have to convert it to decimal notation by yourself. It's not a big deal, right? If you want to insert blob or text string with this byte/character in it then you can use notation like this:    select X'FF';    insert into t values(X'FF'); Pavel On

Re: [sqlite] Attach a database dynamically if not already available within a trigger

2010-03-15 Thread Pavel Ivanov
> How can I embed the 'attach" command within the trigger statement so > that the log database is attached if it is not already available? AFAIK, this is impossible. But why do you need that? If you write your own application you can always attach this database at the beginning. But if you want

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

2010-03-15 Thread dravid11
As i told you before i am using this database inside an application which is merging/combining one source database table to destination database table. So in that case i am not writing the actual insert query.and in that case all columns are taken. The trigger inside the database should handle

Re: [sqlite] [sqlite-dev] Poor SQLite indexing performance

2010-03-15 Thread Pavel Ivanov
First of all you should write to sqlite-users mailing list, not sqlite-dev. > Why is the indexing so slow and bogs down as we proceed with insertions ? > Any suggestions ? > Also, how could I improve performance ? I can't say exactly why performance with index degrades so significantly with the

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

2010-03-15 Thread P Kishor
On Mon, Mar 15, 2010 at 9:18 AM, dravid11 wrote: > > As i told you before i am using this database inside an application which is > merging/combining one source database table to destination database table. > > So in that case i am not writing the actual insert query.and

Re: [sqlite] Porting Sqlite to MQX OS: Question 2

2010-03-15 Thread GeoffW
Hi Kees Thanks for the response and giving me a clue to look at dot locking, I had missed the dot lock mechanism as I had concentrated mainly on the osWin.c file. I cant find any documentation other than the source code on how this low level file locking stuff is implemented. I am currently

Re: [sqlite] [sqlite-dev] Poor SQLite indexing performance

2010-03-15 Thread Max Vlasov
> Also it's quite known that > creating index after inserting all rows is much faster than creating > index before that. So it can be even beneficial in inserting huge > amount of rows somewhere in the middle of the work: first delete all > indexes, then insert rows, then create indexes once more.

Re: [sqlite] Porting Sqlite to MQX OS: Question 2

2010-03-15 Thread Dan Kennedy
On Mar 15, 2010, at 10:04 PM, GeoffW wrote: > > Hi Kees > > Thanks for the response and giving me a clue to look at dot locking, > I had > missed the dot lock mechanism as I had concentrated mainly on the > osWin.c > file. I cant find any documentation other than the source code on > how

Re: [sqlite] BUG: shell.c ATTACH parses filename poorly

2010-03-15 Thread Noah Hart
There may be a documentation bug According to http://sqlite.org/lang_attach.html the format of the attach command is ATTACH [DATABASE] AS Jay reports below that the filename can be an expression. Looks like the documentation needs to be clarified. REQUEST: Add explanation to Syntax

[sqlite] BUG 3.6.23 CorruptE.test needs ifcapable oversize_cell_check

2010-03-15 Thread Noah Hart
(Resent to sqlite-users) CorruptE.test gives different results with and without SQLITE_ENABLE_OVERSIZE_CELL_CHECK Coding similar to the following from corrupt7.test needs to be added # Deliberately corrupt some of the cell offsets in the btree page # on page 2 of the database. # #

[sqlite] FTS3 offsets() function, sorted list?

2010-03-15 Thread Nasron Cheong
Are the results of the offsets() function used in the FTS3 module sorted in any form? Can I rely on this always being the case? Thanks. - Nasron ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] FTS3 offsets() function, sorted list?

2010-03-15 Thread D. Richard Hipp
On Mar 15, 2010, at 1:42 PM, Nasron Cheong wrote: > Are the results of the offsets() function used in the FTS3 module > sorted in > any form? Can I rely on this always being the case? Do not rely on the order of offsets returned by the offsets() function of FTS3. I think they are currently

Re: [sqlite] Can default column values use max() ?

2010-03-15 Thread Max Vlasov
> When I enter a new book, I want the author to default to the last author in > the database. (For the sake of this example you can ignore tricks involving > deleting authors and reusing ids.) > I'm not sure I understood you correctly, but the query INSERT INTO Books (Author, Title) VALUES

Re: [sqlite] FTS3 (Version 2 .6.23) - Is the boolean operator "AND" no more available?

2010-03-15 Thread Max Vlasov
> I wonder if the operator "AND" (in capitals letters) is yet available and > different from the basic term "and" (in lower letters). > > Make sure you compiled the sources with SQLITE_ENABLE_FTS3_PARENTHESIS, since according to docs SQLITE_ENABLE_FTS3_PARENTHESIS This option modifies the

[sqlite] Problem with DATABASE_LOCKED

2010-03-15 Thread Martin Engelschalk
Dear Sqlite users, we experience a problem at a customer site, where the very first statement the program executes on a newly created database failes with a "database locked" error. The statement that failes is "PRAGMA synchronous = OFF". The reason seems to be that the customer inists on

Re: [sqlite] VACUUM & journal size

2010-03-15 Thread Matthew L. Creech
On Mon, Mar 15, 2010 at 2:11 AM, Matthew L. Creech wrote: > > I'll give this a try tomorrow on a real device with journaling off, > and see how much space it uses in /tmp with journaling turned off. > I ran some tests on a real device with a real database, and got the

Re: [sqlite] Can default column values use max() ?

2010-03-15 Thread Simon Slavin
On 14 Mar 2010, at 8:12pm, Max Vlasov wrote: > I'm not sure I understood you correctly, but the query > > INSERT INTO Books > (Author, Title) > VALUES > ((Select Max(rowid) FROM Authors), "test") > > just worked (tested it), sure you have to make id autoincrement to ensure > Max(rowid) is

Re: [sqlite] VACUUM & journal size

2010-03-15 Thread D. Richard Hipp
On Mar 14, 2010, at 7:19 PM, Matthew L. Creech wrote: > Hi, > > I have a SQLite database with one large table, and I'd like to shrink > the size of that table to free up space in the filesystem. > > I'm finding that it needs a full 100 MB for the journal, even > though once the VACUUM succeeds

Re: [sqlite] Problem with DATABASE_LOCKED

2010-03-15 Thread Igor Tandetnik
Martin Engelschalk wrote: > we experience a problem at a customer site, where the very first > statement the program executes on a newly created database failes > with a "database locked" error. The statement that failes is "PRAGMA > synchronous = OFF". The reason

Re: [sqlite] Problem with DATABASE_LOCKED

2010-03-15 Thread Martin Engelschalk
Hello Igor, yes, sorry, i forgot to mention that, i do have write access. The database file itself gets created. Martin Am 15.03.2010 20:02, schrieb Igor Tandetnik: > Martin Engelschalk > wrote: > >> we experience a problem at a customer site, where the very

Re: [sqlite] VACUUM & journal size

2010-03-15 Thread Scott Hess
On Sun, Mar 14, 2010 at 5:18 PM, Jay A. Kreibich wrote: > On Sun, Mar 14, 2010 at 07:19:59PM -0400, Matthew L. Creech scratched on the > wall: >> I have a SQLite database with one large table, and I'd like to shrink >> the size of that table to free up space in the filesystem.  

Re: [sqlite] VACUUM & journal size

2010-03-15 Thread Jay A. Kreibich
On Mon, Mar 15, 2010 at 11:18:32AM -0800, Scott Hess scratched on the wall: > On Sun, Mar 14, 2010 at 5:18 PM, Jay A. Kreibich wrote: > > ?While I have not tested this, I was under the impression that the > > ?journal file is very very small, as no modifications are made to the >

Re: [sqlite] Porting Sqlite to MQX OS: Question 2

2010-03-15 Thread GeoffW
Hello Dan Thanks for your useful input. To answer your questions. >Do you have any file-locking primitives provided by the OS? There are no file locking OS Primitives at all that I can use. >Do you have clients connecting to the database from multiple >processes? Or only multiple threads

Re: [sqlite] VACUUM & journal size

2010-03-15 Thread Scott Hess
On Mon, Mar 15, 2010 at 11:18 AM, Scott Hess wrote: > AFAICT, the operation to copy the pages back _is_ journaled, and the > journal will get any pages which are overwritten in the front of the > main database.  If the initial database has half of the pages used, it > seems like

[sqlite] Attach a database dynamically if not already available within a trigger

2010-03-15 Thread andy
Hello I am hoping someone can help with the following problem. - I want to create a log of all changes to tables in a database. - I would like to keep the log table in a separate database to the main data files - I plan to use triggers to capture changes and insert them into the log table.

Re: [sqlite] On conflicting Primary key how to insert record and increment the Key

2010-03-15 Thread Tim Romano
If you want to "combine two tables" by copying rows from TableB into TableA, and TableA has an autoincrementing primary key, there is no need to reference the PK column in the SQL statement: insert into TableA(address, weight) select address, weight from TableB The approach above does indeed

Re: [sqlite] Attach a database dynamically if not already available within a trigger

2010-03-15 Thread Simon Slavin
On 15 Mar 2010, at 10:52pm, andy wrote: > How can I embed the 'attach" command within the trigger statement so > that the log database is attached if it is not already available? Why not just attach it outside the TRIGGER ? Simon. ___ sqlite-users

[sqlite] regular expressions

2010-03-15 Thread Ralf Jantschek
Hi, is anybody aware of a possibility to do s.th. like select * from table where field like '[A|a]%' Thanks Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regular expressions

2010-03-15 Thread Jean-Christophe Deschamps
>is anybody aware of a possibility to do s.th. like >select * from table where field like '[A|a]%' Unless non-standard compile option and provided you don't issue PRAGMA case_sensitive_like = 1; LIKE is case-insensitive, so LIKE 'A%' is the same as LIKE 'a%' SQLite offers another filtering

Re: [sqlite] Attach a database dynamically if not already available within a trigger

2010-03-15 Thread andy
Thank you. Dont worry readers Im not trying anything "007" Rather, I have variety of separate utilities that insert/update/ delete records across several database and hoped to use triggers to log changes to a single place. By embeding the attach statement as part of the trigger (i.e

Re: [sqlite] Porting Sqlite to MQX OS: Question 2

2010-03-15 Thread Dan Kennedy
On Mar 16, 2010, at 5:22 AM, GeoffW wrote: > > Hello Dan > > Thanks for your useful input. To answer your questions. > >> Do you have any file-locking primitives provided by the OS? > There are no file locking OS Primitives at all that I can use. > >> Do you have clients connecting to the