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

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

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

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] regular expressions

>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

[sqlite] regular expressions

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] Attach a database dynamically if not already available within a trigger

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

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

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

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

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] VACUUM & journal size

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

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

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

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] VACUUM & journal size

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] Problem with DATABASE_LOCKED

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] Problem with DATABASE_LOCKED

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] VACUUM & journal size

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] Can default column values use max() ?

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

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

[sqlite] Problem with DATABASE_LOCKED

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] FTS3 (Version 2 .6.23) - Is the boolean operator "AND" no more available?

> 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

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

> 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 offsets() function, sorted list?

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

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

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

[sqlite] BUG 3.6.23 CorruptE.test needs ifcapable oversize_cell_check

(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. # #

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

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

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

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] [sqlite-dev] Poor SQLite indexing performance

> 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

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] On conflicting Primary key how to insert record and increment the Key

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] [sqlite-dev] Poor SQLite indexing performance

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

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] Attach a database dynamically if not already available within a trigger

> 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] How can I deal with hexadecimal numerical value?

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] On conflicting Primary key how to insert record and increment the Key

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] On conflicting Primary key how to insert record and increment the Key

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

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: >

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

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

[sqlite] How can I deal with hexadecimal 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] Attach a database dynamically if not already available within a trigger

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] FTS3 (Version 2 .6.23) - Is the boolean operator "AND" no more available?

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:

Re: [sqlite] VACUUM & journal size

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