[sqlite] Checking for open transactions attach/detach database and Trigger behaviour with attached databases
Hi, My first submission 1 Problem and 1 question J My problem I have 2 processes that are causing problems after commits/locks 1. Copy main database to copydatabase using filesystem object How to determine when copydatabase is ready for attach? Attach copydatabase <-- need to retry a few times Delete some rows Detach copydatabase 2. Attach copydatabase Begin transaction Update main database from data in copydatabase Commit How to determine when copydatabase is ready for detach? Detach copydatabase <-- Need to retry many times The main application traps the errors before my MS VBScript can use ON ERROR so I need to test for the error before it occurs. Hope There is an easy solution My question Are triggers restricted to their respective databases? Main Database Table Name = Songs Attached Database Table Name = Songs Both have triggers CREATE TRIGGER delete_songs DELETE ON Songs BEGIN ... END Will these act only their respective tables or will they both act on the main database? Regards Terry Ganly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted sqlite_sequence table
On May 22, 2008, at 7:02 PM, Samuel Neff wrote: > I have a corrupt sqlite_sequence table. It has table names in the > "seq" > field. > > here is a trimmed version of data in sqlite_sequence: > > > -- Loading resources from C:\Documents and Settings\sam/.sqliterc > SQLite version 3.5.7 > Enter ".help" for instructions > sqlite> .width 50 50 > sqlite> select * from sqlite_sequence; > nameseq > -- > -- > Transactions2 > ActiveTransaction 2 > AnnotationIcons_History 21 > TransactionSequences494 > CaptureDeviceTransactions 8 > Annotations > CaptureDeviceTransactions > Annotations_History 24 > ChecklistVersions > Annotations_History > ChecklistVersions_History 4 > sqlite> > > > > basically it gets corrupt when I run a bunch of scripts that update > from one > schema to a new one (usually add new columns). The queries do a lot > of > > ALTER TABLE x RENAME TO y; > CREATE TABLE x (...); > INSERT INTO x SELECT ... FROM y; > DROP TABLE y; > > and after that the sqlite_sequence table is messed up as shown > above. It > doesn't happen every time--in the above listed data both Annotations > and > Annotations_History were updated this way, but only Annotations got > messed > up. How easily repeatable is the problem? Can you send me a database file before and after the schema update and a sequence of SQL operations the performed the update? > > > We cache all the data in sqlite_sequence before we do our updates > and our > intention is to update it afterwards (we haven't written this part > yet due > to sqlite_sequence corruption, so at this point we're only reading > from the > table, not updating/inserting into it). > > Our app is using sqlite 3.5.9. I'm still using sqlite3.exe 3.5.7 > but I see > the same corrupted data in both. > > Please help. > > Thanks, > > Sam > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > 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] Sqlite 3.5.9 journal_mode vs ext3 journaling
On May 22, 2008, at 6:50 PM, Bob Ebert wrote: > I wonder if anyone is in a position to compare and contrast the > journal_mode and synchronous settings with the various ext3 journal > modes? > > Up until now we've been using ext3 with data=ordered, and sqlite3 with > synchronous=normal, journal_mode=delete. We're on an embedded system > with a very high frequency of random power cycling. In this mode, > we've > seen an unusually large number of corrupted databases. > > My best theory right now is that we lose power after the ext3 metadata > has written the delete of the -journal file, but before all of the > page > overwrites for the db file are fully flushed, since these are done by > two different processes in normal linux. SQLite calls fsync() before it calls unlink() on the journal file. So if fsync() is working as documented, all database data should be safely on oxide prior to the unlink(). We have seen instances before where fsync() returned long before the data was on oxide, so I would not be surprised by this. You can often see this yourself by mounting a flash memory stick, writing a file on the stick, calling fsync() then watching the LED on the end of the stick continue to flash long after the fsync() has returned. Please recognize that there really is nothing that SQLite can do to correct this problem. SQLite depends on the operating system and/or disk controller living up to its end of the contract. If the disk says all data is on oxide, SQLite has no choice but to believe it because SQLite has no way to independently verify the matter. And if the disk controller and/or operating system is lying, and a power failure follows, the database can be corrupted. There isn't much you can do when your hardware starts lying to you. > I believe if we lose power at > this point, then after a restart ext3 will replay the journal delete, > but will leave the main DB in a semi-written state, and thus corrupt > the > DB. > > I'm wondering what impact switching to journal_mode=persist will > have on > this scenario. I believe this will change how the last step of the > atomic commit (clearing the journal) is written to disk, and thus > changes how ext3 will recover the file after a power loss. Currently > our IO scheduler doesn't guarantee ordered writes, so in theory the > journal header clearing could still make it to disk before all the > main > db pages. We're in a position to adjust the IO scheduler if necessary > to prevent this. Once again, SQLite calls fsync() on the database prior to calling write() to clear the journal header. So *if* fsync is doing its job, you should have no problems. *If*. Note the the whole point of these fsync() calls in SQLite is to act as an I/O barrier operation - to guarantee that all I/O operations that are issued prior to the fsync() complete prior to any I/O operations issued afterwards. We must have an I/O barrier in order to preserve database integrity across a power failure. If your fsync() is not work quite right, then all bets are off. I don't know of anything SQLite can do to make the situation better. > > > Are there any other potential holes or races between ext3 journal data > and sqlite file contents that I should worry about? Is > synchronous=full > the only way to guarantee atomic commits under these conditions? > A synchronous=FULL commit goes like this: 1. Write all pages that will change into the journal 2. fsync() the journal 3. Overwrite the journal header to indicate that it is valid 4. fsync() the journal 5. Write changes into the database file. 6. fsync() the database files 7. Delete or truncate or overwrite the journal header (depending on journaling mode) The difference between FULL and NORMAL is that NORMAL omits the fsync() on step 2. That is the only difference. The step-2 fsync is important on some filesystems, but on ext3 it is probably unnecessary. So I don't think that going to synchronous=FULL is going to help you. 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] Very simple table...
Hi Scott,
> I'm trying to decide whether LogDate should be in unixtime
> format, or raw date format ('2008-01-01 13:12:11').
I use juliandate (real) to store the dates. See this web page for
details:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
Tom
BareFeet
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Corrupted sqlite_sequence table
I have a corrupt sqlite_sequence table. It has table names in the "seq" field. here is a trimmed version of data in sqlite_sequence: -- Loading resources from C:\Documents and Settings\sam/.sqliterc SQLite version 3.5.7 Enter ".help" for instructions sqlite> .width 50 50 sqlite> select * from sqlite_sequence; nameseq -- -- Transactions2 ActiveTransaction 2 AnnotationIcons_History 21 TransactionSequences494 CaptureDeviceTransactions 8 Annotations CaptureDeviceTransactions Annotations_History 24 ChecklistVersions Annotations_History ChecklistVersions_History 4 sqlite> basically it gets corrupt when I run a bunch of scripts that update from one schema to a new one (usually add new columns). The queries do a lot of ALTER TABLE x RENAME TO y; CREATE TABLE x (...); INSERT INTO x SELECT ... FROM y; DROP TABLE y; and after that the sqlite_sequence table is messed up as shown above. It doesn't happen every time--in the above listed data both Annotations and Annotations_History were updated this way, but only Annotations got messed up. We cache all the data in sqlite_sequence before we do our updates and our intention is to update it afterwards (we haven't written this part yet due to sqlite_sequence corruption, so at this point we're only reading from the table, not updating/inserting into it). Our app is using sqlite 3.5.9. I'm still using sqlite3.exe 3.5.7 but I see the same corrupted data in both. Please help. Thanks, Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite 3.5.9 journal_mode vs ext3 journaling
I wonder if anyone is in a position to compare and contrast the journal_mode and synchronous settings with the various ext3 journal modes? Up until now we've been using ext3 with data=ordered, and sqlite3 with synchronous=normal, journal_mode=delete. We're on an embedded system with a very high frequency of random power cycling. In this mode, we've seen an unusually large number of corrupted databases. My best theory right now is that we lose power after the ext3 metadata has written the delete of the -journal file, but before all of the page overwrites for the db file are fully flushed, since these are done by two different processes in normal linux. I believe if we lose power at this point, then after a restart ext3 will replay the journal delete, but will leave the main DB in a semi-written state, and thus corrupt the DB. I'm wondering what impact switching to journal_mode=persist will have on this scenario. I believe this will change how the last step of the atomic commit (clearing the journal) is written to disk, and thus changes how ext3 will recover the file after a power loss. Currently our IO scheduler doesn't guarantee ordered writes, so in theory the journal header clearing could still make it to disk before all the main db pages. We're in a position to adjust the IO scheduler if necessary to prevent this. Are there any other potential holes or races between ext3 journal data and sqlite file contents that I should worry about? Is synchronous=full the only way to guarantee atomic commits under these conditions? --Bob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Firefox 3 and the SQLite "bug"
Thanks! I've posted that information in the bug. Cheers, Shawn On Thu, May 22, 2008 at 4:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On May 22, 2008, at 9:40 AM, Shawn Wilsher wrote: > >> It was mentioned in the bug that opening the file with the O_SYNC flag >> would no longer require fsyncs. Has this been looked into before by >> sqlite? >> > > I have a prepared a version of SQLite that uses O_SYNC on the main > database file and its journal and never calls fsync(). I ran this on > SuSE 10.1 x86 and found that preformance was roughly half of what we > got using fsync() (with synchronous=FULL). Here are the numbers: > > O_SYNC: > > real13m6.918s > user 0m14.693s > sys 0m22.329s > > fsync: > > real 7m5.159s > user0m14.745s > sys 0m11.049s > > But versions were compiled with -Os. Gcc version 4.1.0. > > Of course, your mileage may vary, but based on the magnitude of the > difference seen above, I'm thinking that O_SYNC is probably a bad idea. > > As a point of comparison, the same code compiled with - > DSQLITE_NO_SYNC=1 is between 40 and 70 times faster: > > real0m10.479s > user 0m6.736s > sys 0m3.732s > > Oh, what a difference a disk cache makes. > > 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] Firefox 3 and the SQLite "bug"
On May 22, 2008, at 9:40 AM, Shawn Wilsher wrote: > It was mentioned in the bug that opening the file with the O_SYNC flag > would no longer require fsyncs. Has this been looked into before by > sqlite? > I have a prepared a version of SQLite that uses O_SYNC on the main database file and its journal and never calls fsync(). I ran this on SuSE 10.1 x86 and found that preformance was roughly half of what we got using fsync() (with synchronous=FULL). Here are the numbers: O_SYNC: real13m6.918s user 0m14.693s sys 0m22.329s fsync: real 7m5.159s user0m14.745s sys 0m11.049s But versions were compiled with -Os. Gcc version 4.1.0. Of course, your mileage may vary, but based on the magnitude of the difference seen above, I'm thinking that O_SYNC is probably a bad idea. As a point of comparison, the same code compiled with - DSQLITE_NO_SYNC=1 is between 40 and 70 times faster: real0m10.479s user 0m6.736s sys 0m3.732s Oh, what a difference a disk cache makes. 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] last_insert_rowid reproducible bug with triggers and FTS2 and 3
D. Richard Hipp wrote:
>
> I think the FTS virtual table is doing the INSERTs inside its xCommit
> method, after the trigger has been exited. So the mechanism that
> resets the last_insert_rowid when a trigger exits does not apply since
> the trigger has already existed by the time the FTS virtual table does
> its INSERT. (NB: I have not verified this in a debugger - it is just
> my theory.)
>
It looks like you are correct. If the update happens in an explicit
transaction then the last_insert_rowid value is not changed until after
the explicit transaction is commited.
insert into one (value) values ("hello1");
select last_insert_rowid(); -- returns 1
begin;
insert into one (value) values ("hello2");
select last_insert_rowid(); -- returns 2
update one set value="hello3" where id=1;
select last_insert_rowid(); -- still returns 2
commit;
select last_insert_rowid(); -- now returns 3
This also seems to indicate a problem. It seems even less correct for a
commit to change the value of the last insert rowid.
I can see how this may be complicated to correct unless the lastrowid
and nchanges values are saved and restored around the xCommit calls that
happen when the active transaction ends. Would that be a possible solution?
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Ralf Junker wrote: > Darren Duncan wrote: > >> Ralf Junker wrote: >> >>> Can you suggest an alternative to a single reserved name to represent the >>> column which uniquely identifies a database record under any and all >>> circumstances? >> Yes, change the interface to RowID into a routine call rather than a column >> name; eg use "RowID()" rather than "RowID". > > I can not see how this would actually work with SQLite. Any use-created RowID column would override and hide the implicit rowid column even for the RowID() function, would it not? No it wouldn't. You can still access SQLite's hidden RowID no matter what users name their columns. The thing is, since my proposal involves SQLite making syntax for accessing its hidden rowid using a function rather than as a fake column name, that function or the syntax for invoking it can be anything the SQLite developers pick that they know will be in a separate namespace from the one that table columns are in. >> Then when using it in a SELECT, you can say "RowID() as foo" in the select list where "foo" is different than a normal table field. Such is how 'standard' SQL does it. > > What is 'standard" SQL? Can you give an example how this is used with other DB engines? I am not familiar with MySQL, but searching the documentation I could not find that it supports this concept. Maybe others do? Actually, what I was meaning to get at here was the concept of a user's SQL statement using 'as' to rename the result of the special keyword for a rowid et al to some arbitrary other word to represent it as a column name, that didn't conflict with any column names the user chose for their tables. Various examples using (ANSI/ISO SQL:2003) standard SQL or other DBMS did things like this; the other reason for renaming is eg so that when joining 2 tables, the rowid from each table has a distinct column name. >> Any manager app can read the database schema first and generate a name "foo" that is distinct. > > As things are at the moment, the implicit, unambigous RowID can not be retrieved from the database schema if all three "RowID", "_rowid_", and "OId" column names are overridden. This applies to SQL as well as to user-defined functions. Then a candidate fix is for SQLite to use some namespace syntax for referring to those special things that is distinct from the namespaces of user-defined things. If it were me, I would have eg all system-defined operators named sys.foo, and all user-defined ones grouped under usr.foo, or that idea. And don't worry about whether or not doing this is compatible with other DBMSs or not, considering that the whole rowid/oid stuff is very non-portable and wildly implementation dependent anyway. If you want true portability, you do this by ignoring all these special ids and have explicit (unique) keys on your data columns, and then you use your actual data as its own unique identifier ... which is how the true relational model works anyway. Doing other than using the actual data to identify itself is just asking for trouble, and is a main reason why this whole rowid problem started. Doing it right saves trouble and gives portability. In fact, data identifying itself is the only approach I really advocate; any of my other suggestions which may try to use the special rowids are never my first choice, and I have less impetus to argue for them. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bind arguments for insert and not null columns with default values
Eric Minbiole wrote: >> I have a table like this >> >> CREATE TABLE foo (bar TEXT NOT NULL DEFAULT 'default_value'); >> >> and I'd like to create a reusable statement to do inserts into foo, like >> this: >> >> INSERT INTO foo (bar) VALUES (?); >> >> Sometimes I have values for bar and sometimes I don't and want the >> default. Is there any way to indicate to the statement that I want the >> bound parameter to be "nothing" therefore giving me the default value? >> If I bind that column to NULL I get a constraint error. > > Could you simply create two separate prepared statements (one which sets > bar and one which doesn't), then use the appropriate one, depending on > whether you know the value of bar? Perhaps not as elegant as reusing > one statement for everything, but it should work easily enough. > Eric's suggestion is the only correct way to do this. The default value is only used if no value, not even a null, is supplied by the insert statement. You need one statement that supplies a bar value, and one that does not, which will use the default value. If you have lots of fields that you want to do this with the required number of combinations can get large very quickly, so you may be better off building the statements on the fly. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] last_insert_rowid reproducible bug with triggers and FTS2 and 3
On May 22, 2008, at 3:10 PM, Dennis Cote wrote: > Bram de Jong wrote: >> >> I have found a bug which happens in both FTS2 and FTS3. >> >> The bug happens when a trigger updates an FTS table: the insert ID >> gets trashed: >> > I think both Richard and Scott may have misread this one a little bit. > > It seems to me that all the operations Bram has done are using the > normal (i.e. non-virtual table) table, one. All the accesses of the > virtual table search are done inside the trigger routines. SQLite is > supposed to be saving and restoring the last_insert_rowid value for > each > trigger execution context. These operation should work as expected > regardless of what is done inside the trigger. An update of a normal > table should not be changing the last inserted rowid value. > > Perhaps the problem is simply that the update trigger is not doing the > save and restore operation correctly. In any case it looks like > there is > a real problem here. I think the FTS virtual table is doing the INSERTs inside its xCommit method, after the trigger has been exited. So the mechanism that resets the last_insert_rowid when a trigger exits does not apply since the trigger has already existed by the time the FTS virtual table does its INSERT. (NB: I have not verified this in a debugger - it is just my theory.) 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] last_insert_rowid reproducible bug with triggers and FTS2 and 3
Bram de Jong wrote:
>
> I have found a bug which happens in both FTS2 and FTS3.
>
> The bug happens when a trigger updates an FTS table: the insert ID gets
> trashed:
>
> <<<
> create table one
> (
> id integer not null primary key autoincrement,
> value text not null default ''
> );
>
> create virtual table search using fts2(one_id, data);
>
> create trigger sound_insert after insert on one for each row
> begin
> insert into search (one_id, data) values (new.id, new.value);
> end;
>
> create trigger one_update after update on one for each row
> begin
> update search set data = random() where search.one_id=new.id;
> end;
>
> insert into one (value) values ("hello1");
> select last_insert_rowid(); -- returns 1
> insert into one (value) values ("hello2");
> select last_insert_rowid(); -- returns 2
> update one set value="hello3" where id=1;
> select last_insert_rowid(); -- returns 3, but should return 2
> <<<
>
I think both Richard and Scott may have misread this one a little bit.
It seems to me that all the operations Bram has done are using the
normal (i.e. non-virtual table) table, one. All the accesses of the
virtual table search are done inside the trigger routines. SQLite is
supposed to be saving and restoring the last_insert_rowid value for each
trigger execution context. These operation should work as expected
regardless of what is done inside the trigger. An update of a normal
table should not be changing the last inserted rowid value.
Perhaps the problem is simply that the update trigger is not doing the
save and restore operation correctly. In any case it looks like there is
a real problem here.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Equivalent of mysql_real_escape_string() ?
Skip Evans wrote:
> Hey all,
>
> Okay, I'm looking all through the PDO docs on
> php.net, but am unable to find the SQLite
> equivalent to the MySQL function
>
> mysql_real_escape_string()
>
> in case, among other things, a text field contains
> single quotes, etc.
>
> How is this done in SQLite? I'm still scouring the
> the docs but having no luck.
>
> Does it have something to do with
>
> $dbh->prepare()
>
> ...or am I on the wrong track with that one?
As mentioned above the BEST way to do it is with prepared statement and
bound variables. If you have to use raw SQL then just use the PDO::quote
method:
http://php.web-ster.com/manual/en/pdo.quote.php
$conn = new PDO('sqlite:/home/lynn/music.sql3');
$string = 'Nice';
print "Quoted string: " . $conn->quote($string) . "\n";
I'm open to discussion about whether or not this is this is still
vulnerable to SQL injection.
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Equivalent of mysql_real_escape_string() ?
On May 22, 2008, at 2:33 PM, Doug wrote: > Besides substituting ' with '' (double single-quotes) you might also > want to > consider trimming trailing spaces. I ended up inserting strings > like 'Doug' > and 'Doug ' in a unique-indexed column. SQLite let me do it and all > was > well. One day I exported that data to MS SQL and it complained > about the > second 'Doug ' coming in because it ignores trailing spaces, so > there was an > index collision. > If you add "COLLATE RTRIM" to your text columns, then trailing spaces will no longer make them unique and they will work like MSSQL. 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] Equivalent of mysql_real_escape_string() ?
Besides substituting ' with '' (double single-quotes) you might also want to consider trimming trailing spaces. I ended up inserting strings like 'Doug' and 'Doug ' in a unique-indexed column. SQLite let me do it and all was well. One day I exported that data to MS SQL and it complained about the second 'Doug ' coming in because it ignores trailing spaces, so there was an index collision. Doug > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Dennis Cote > Sent: Thursday, May 22, 2008 10:36 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Equivalent of mysql_real_escape_string() ? > > Skip Evans wrote: > > Hey all, > > > > Okay, I'm looking all through the PDO docs on > > php.net, but am unable to find the SQLite > > equivalent to the MySQL function > > > > mysql_real_escape_string() > > > > in case, among other things, a text field contains > > single quotes, etc. > > > > How is this done in SQLite? I'm still scouring the > > the docs but having no luck. > > > > Does it have something to do with > > > > $dbh->prepare() > > > > ...or am I on the wrong track with that one? > > While I agree with Jay, using bound parameters is a much better > approach, there is a partial answer to your question. > > SQLite provides the sqlite3_mprintf() function and it's associated %q > and %Q format specifiers for quoting SQL strings. See > http://www.sqlite.org/c3ref/mprintf.html for details. > > I'm not sure if this functionality is exposed through the Perl PDO > wrapper though. > > HTH > Dennis Cote > ___ > 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 to use "IN" keyword for multi-column index
> 2. I tried this first: [ select * from map where (x=1 and y=1) or (x=1 and > y=2) or (x=1 and y=3) ] but that didn't use the index -- not on 3.5.6 anyway AFAIK "OR" will always omit indexes, this is why I am trying to use "IN" -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use "IN" keyword for multi-column index
On Thu, May 22, 2008 at 2:02 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > IN only works on a single column. The closest you can get to this is > something like > > SELECT map.* > FROM map join ( > select 1 x, 1 y > union all > select 1 x, 2 y > union all > select 1 x, 3 y) t > ON map.x = t.x AND map.y=t.y; > > I checked - it does use map_xy index. The subselect in parentheses > essentially creates a temporary table, which is then joined with your > main table. > > Igor Tandetnik Hah! I was going to test something similar out, but decided not to. I figured that that would never actually work. That'll teach me to make assumptions :) This is what you'd proposed: sqlite> explain query plan select * from map m join (select 1 as x, 1 as y union all select 1, 2 union all select 1, 3) z on z.x=m.x and z.y=m.y; orde from deta - 0 1 TABLE AS z 1 0 TABLE map AS m WITH INDEX map_xy And this is what I considered: sqlite> explain query plan select * from map where x=1 and y=1 union all select * from map where x=1 and y=2 union all select * from map where x=1 and y=3; orde from deta - 0 0 TABLE map WITH INDEX map_xy 0 0 TABLE map WITH INDEX map_xy 0 0 TABLE map WITH INDEX map_xy -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use "IN" keyword for multi-column index
> IN only works on a single column. The closest you can get to this is > something like > > SELECT map.* > FROM map join ( > select 1 x, 1 y > union all > select 1 x, 2 y > union all > select 1 x, 3 y) t > ON map.x = t.x AND map.y=t.y; Thanks. I will use more than 3 keys, so I will create temporary memory table with keys for this stuff. -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use "IN" keyword for multi-column index
On Thu, May 22, 2008 at 1:41 PM, Alexander Batyrshin <[EMAIL PROTECTED]>
wrote:
> Hello All,
> For example we have table like this:
>
> CREATE TABLE map (
> name text,
> x integer,
> y integer
> );
> CREATE INDEX map_xy ON map(x,y);
>
> How to query this table with "IN" keyword?
> Query like this, doesn't work:
>
> SELECT * FROM map WHERE (x,y) IN ((1,1),(1,2),(1,3));
select * from map where x=1 and y in (1,2,3);
1. Some people might suggest some crazy things like [ SELECT * FROM map
WHERE x||'.'||y in ('1.1','1.2','1.3') ]. While this would technically work,
it wouldn't be able to use your index.
2. I tried this first: [ select * from map where (x=1 and y=1) or (x=1 and
y=2) or (x=1 and y=3) ] but that didn't use the index -- not on 3.5.6 anyway
3. If you're going to have a bunch of choices with different values for X
and Y, you *might* want to creating a precomputed statement of the form
'select * from map where x=? and y=?', then binding and re-executing the
statement for each (x,y) pair you're interested in, and piece them together
in your application. You wouldn't be able to take advantage of ORDER BY,
GROUP BY, or DISTINCT that way, but it would work.
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use "IN" keyword for multi-column index
Alexander Batyrshin <[EMAIL PROTECTED]> wrote: > Hello All, > For example we have table like this: > > CREATE TABLE map ( > name text, > x integer, > y integer > ); > CREATE INDEX map_xy ON map(x,y); > > How to query this table with "IN" keyword? > Query like this, doesn't work: > > SELECT * FROM map WHERE (x,y) IN ((1,1),(1,2),(1,3)); IN only works on a single column. The closest you can get to this is something like SELECT map.* FROM map join ( select 1 x, 1 y union all select 1 x, 2 y union all select 1 x, 3 y) t ON map.x = t.x AND map.y=t.y; I checked - it does use map_xy index. The subselect in parentheses essentially creates a temporary table, which is then joined with your main table. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to use "IN" keyword for multi-column index
Hello All, For example we have table like this: CREATE TABLE map ( name text, x integer, y integer ); CREATE INDEX map_xy ON map(x,y); How to query this table with "IN" keyword? Query like this, doesn't work: SELECT * FROM map WHERE (x,y) IN ((1,1),(1,2),(1,3)); -- Alexander Batyrshin aka bash bash = Biomechanica Artificial Sabotage Humanoid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Very simple table...
I have a very simple table:
CREATE TABLE Log (
LogDate,
LogText
);
Just when did the event happen, and what was it. So now I'm populating the
fields and I'm trying to decide whether LogDate should be in unixtime
format, or raw date format ('2008-01-01 13:12:11').
I'm assuming unixtime would store as less bytes? Are there any inherent
speed advantages either way? Do the date functions work faster on either one?
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] do someone know? DotGnu
David Alejandro Garcia Garcia wrote: > do some one know how i can conect from dotGnu to sqlite? i have mandriva > i hope some one can helpme Have you looked at any of the .Net wrappers at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers? You may also want to look at the ODBC drivers at http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc which may work for you as well. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] baffling performance decrease across network (specific case)
Serena Lien wrote: > Thanks for the response - no it hadn't occurred to me to try a different > network filesystem, as I don't really have access to non windows machines. > I'm just using windows xp machines set up on the same domain, where the > databases reside on shared folders. > > I actually thought there might be extra work sqlite is doing (no not > sleeping!) when more than one client is accessing the same database, like > having to move between extra locking states or something like that, and that > it might be easily explainable, but you're perfectly right that it could > just be down to the OS. > This slowdown is almost certainly due to the use of opportunistic locking in the SMB protocols. With a single client the it uses exclusive oplocks and can cache remote file data locally. When a second client connects it can no longer do this and subsequently slows down to the real speed of remote file access. See http://en.wikipedia.org/wiki/Opportunistic_Locking for additional info. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_ENABLE_FTS3
paul breen wrote: > I want to build sqlite3.dll with fts3 support. > > I found "#ifdef SQLITE_ENABLE_FTS3" by searching the code but I do not know > how to "def" SQLITE_ENABLE_FTS3. I can guess by setting it to some value > somewhere. > Please tell me how to do this and I will go back to powerbasic and leave > you guys alone. > Paul, You can use brute force and add the following line to the beginning of your copy of the amalgamation source file. #define SQLITE_ENABLE_FTS3 Or you can add the definition to your compiler command line by adding this option to the command -DSQLITE_ENABLE_FTS3 Or you can add the definition to your IDE's project options file. I'm not sure how to do this with VS 2005, but you can probably find it in the help for additional compiler definitions. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Equivalent of mysql_real_escape_string() ?
Skip Evans wrote: > Hey all, > > Okay, I'm looking all through the PDO docs on > php.net, but am unable to find the SQLite > equivalent to the MySQL function > > mysql_real_escape_string() > > in case, among other things, a text field contains > single quotes, etc. > > How is this done in SQLite? I'm still scouring the > the docs but having no luck. > > Does it have something to do with > > $dbh->prepare() > > ...or am I on the wrong track with that one? While I agree with Jay, using bound parameters is a much better approach, there is a partial answer to your question. SQLite provides the sqlite3_mprintf() function and it's associated %q and %Q format specifiers for quoting SQL strings. See http://www.sqlite.org/c3ref/mprintf.html for details. I'm not sure if this functionality is exposed through the Perl PDO wrapper though. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Question
Scott Hess wrote: > I think you're going to have to run some code to generate the string > to match against. The problem is that you need to take all of the > 'query' fields from 'category' and combine them into a string like > 'query1 OR query2 OR query3 OR ...'. I'm not aware of a way to do > this with straight SQL. You could perhaps build an aggregate function > which took strings and combined them, then it might be something like: > > SELECT guid FROM data WHERE text MATCH (SELECT string_join(query, ' > OR ') FROM category); > This function already exists, and is included in SQLite. It is called group_concat(). See http://www.sqlite.org/lang_aggfunc.html for details. SELECT guid FROM data WHERE text MATCH (SELECT group_concat(query, ' OR ') FROM category); HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limitation of sqlite3_last_insert_rowid()
Joanne Pham wrote: > U12232 If a separate thread does a new insert on the same database connection > while the sqlite3_last_insert_rowid() function is running and thus changes > the last insert rowid, then the value returned by sqlite3_last_insert_rowid() > is unpredictable and might not equal either the old or the new last insert > rowid. > Hi All, This limitation is still in new release 3.5.9. Yes. The last inserted rowid is maintained on a per connection basis. If multiple threads share the same connection then they are sharing the same last inserted rowid value with no mutual exclusion provided by SQLite. You can of course use your own mutex to control access to this variable (through the API function), to allow your threads to read the value reliably after an insert. Or you could simply have each thread use a separate connection. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] interrupting sqlite3_prepare_v2
Daniel Önnerby wrote: > > Sometimes this interrupt occur in the middle of a > sqlite3_prepare_v2 and in some cases this will cause my application to > break in the SQLite code somewhere. > > Please let me know if you want me to investigate this futher. > Yes, please do so if you have the time. If you locate a bug it will help all users of SQLite. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help!!! sqlite 3.5.8 crash: access violation
[EMAIL PROTECTED] wrote: > The attachment size limitation of the bug report is 100k :( I'm sorry about the late follow up, but I have been away for a while. Can you contact me off list to see about transferring the compressed database so I can use it to look into the problem? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Firefox 3 and the SQLite "bug"
On May 22, 2008, at 9:40 AM, Shawn Wilsher wrote: > It was mentioned in the bug that opening the file with the O_SYNC flag > would no longer require fsyncs. Has this been looked into before by > sqlite? > I don't think this would work well. The O_SYNC flag causes each write() system call to block until the data is on oxide. But sometimes SQLite will do a sequence of small consecutive writes of a few bytes each with the anticipation that the filesystem will buffer these writes and only do a single write to oxide at the end. So if we just blindly turned on O_SYNC, the number of disk writes would increase dramatically. We could update the unix backend so that some amount of buffering was done in the backend itself to coalesce a sequence of small writes into a single write() system call. This would reduce the amount of I/O back to what it was without the O_SYNC. But it still does not reduce the amount of I/O and it also deprives the filesystem the opportunity to reorder write requests, which will likely result in an efficiency loss, not a gain. Nevertheless, these kinds of things often defy logic, so I will give it a try and see what happens 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] Fetching records from Temp table
On 5/22/08, Farzana <[EMAIL PROTECTED]> wrote: > > Dear All, > > We have a table named Brand(data is not ordered by Branddescription) where > BrandDescription is one of the column and we tried to copy the Brand with > the same stucture with the table name Brand_temp and inserted the data > ordered by BrandDescription into a temp table as follows: > CREATE TABLE Brand_temp AS SELECT * FROM Brand WHERE 1=2 > insert into Brand_temp select * from brand order by branddescription > > Then we dropped the Brand table and renamed the temp table as brand like as > follows: > Drop table Brand > ALTER table brand_temp RENAME TO Brand > > When we tried with the original Brand Table in the application it is taking > around 15 minutes to fetch the record. But when we use the temp table it's > taking 11 secs to fetch the record. > We are not clear why there is a drastic change in fetching the records since > the structure remains the same and we are using the same application for > fetching the records from both the table. > Do you, did you have an INDEX on TABLE Brand (BrandDescription)? If you did, it would get whacked when you DROP the TABLE Brand and ALTER RENAME Brand_Temp to Brand. Rebuild the INDEX and then see what the performance is like. > Kindly clarify us in this regard. > Thanks in advance. > > Regards, > Farzana. > > > > -- > View this message in context: > http://www.nabble.com/Fetching-records-from-Temp-table-tp17399000p17399000.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 > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Firefox 3 and the SQLite "bug"
It was mentioned in the bug that opening the file with the O_SYNC flag would no longer require fsyncs. Has this been looked into before by sqlite? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Firefox 3 and the SQLite "bug"
On May 22, 2008, at 7:20 AM, Russell Leighton wrote: > > Digg has an article where it is said that the new Firefox "locks" up > under Linux due to SQLite: > > http://digg.com/linux_unix/ > Firefox_3_has_system_killing_performance_problem_for_Linux > > Bug here: > > https://bugzilla.mozilla.org/show_bug.cgi?id=421482 > > Scanning the bug it seems to be with the behavior of fsync(). > > Is the issue with SQLite use of fsync() or the behavior of filesystem > (e.g., ext3) or the Linux kernel? > See http://www.0xdeadbeef.com/weblog?p=368 for one (more rational and informed) views on this issue. The underlying problem is that Firefox is doing a lot of COMMITs, and as you probably know, each COMMIT requires 3 fsyncs. See http://www.sqlite.org/atomiccommit.html for more details. The latest changes to Firefox set "PRAGMA synchronous=NORMAL" instead of the default "PRAGMA synchronous=FULL" which reduces the fsync() count from 3 to 2 for each commit. That plus the fix to ticket #3015 seem to have taken care of most of the problem. Parts of the problem have to do (I am told) with a misconfigured scheduler in some Linux kernels that is particular slow about doing fsync(). Finally, Mozilla is working to reduce the number of COMMITs by storing recent changes in TEMP tables then transferring updates over to the main database in a single transaction periodically. That means that if Firefox or your system crashes, you might loss your last 30 seconds of browser history, but nobody really cares about that really. 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] Fetching records from Temp table
"Farzana" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > CREATE TABLE Brand_temp AS SELECT * FROM Brand WHERE 1=2 > insert into Brand_temp select * from brand order by branddescription > > Then we dropped the Brand table and renamed the temp table as brand > like as follows: > Drop table Brand > ALTER table brand_temp RENAME TO Brand > > When we tried with the original Brand Table in the application it is > taking around 15 minutes to fetch the record. But when we use the > temp table it's taking 11 secs to fetch the record. What statement do you use to "fetch the record"? Have you measured the time to retrieve the record that's located near the end of Brand table, as well as one near beginning? Have you tried running VACUUM on the original database, before all these manipulations (it could be that the table was badly fragmented, and you simply defragmented it; VACUUM would have, too)? How large is Brand table - how many rows? -- With best wishes, Igor Tandetnik With sufficient thrust, pigs fly just fine. However, this is not necessarily a good idea. It is hard to be sure where they are going to land, and it could be dangerous sitting under them as they fly overhead. -- RFC 1925 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Firefox 3 and the SQLite "bug"
Digg has an article where it is said that the new Firefox "locks" up under Linux due to SQLite: http://digg.com/linux_unix/ Firefox_3_has_system_killing_performance_problem_for_Linux Bug here: https://bugzilla.mozilla.org/show_bug.cgi?id=421482 Scanning the bug it seems to be with the behavior of fsync(). Is the issue with SQLite use of fsync() or the behavior of filesystem (e.g., ext3) or the Linux kernel? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Darren Duncan wrote: >Ralf Junker wrote: > >>Can you suggest an alternative to a single reserved name to represent the >>column which uniquely identifies a database record under any and all >>circumstances? > >Yes, change the interface to RowID into a routine call rather than a column >name; eg use "RowID()" rather than "RowID". I can not see how this would actually work with SQLite. Any use-created RowID column would override and hide the implicit rowid column even for the RowID() function, would it not? >Then when using it in a SELECT, you can say "RowID() as foo" in the select >list where "foo" is different than a normal table field. Such is how >'standard' SQL does it. What is 'standard" SQL? Can you give an example how this is used with other DB engines? I am not familiar with MySQL, but searching the documentation I could not find that it supports this concept. Maybe others do? >Any manager app can read the database schema first and generate a name "foo" >that is distinct. As things are at the moment, the implicit, unambigous RowID can not be retrieved from the database schema if all three "RowID", "_rowid_", and "OId" column names are overridden. This applies to SQL as well as to user-defined functions. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fetching records from Temp table
Dear All, We have a table named Brand(data is not ordered by Branddescription) where BrandDescription is one of the column and we tried to copy the Brand with the same stucture with the table name Brand_temp and inserted the data ordered by BrandDescription into a temp table as follows: CREATE TABLE Brand_temp AS SELECT * FROM Brand WHERE 1=2 insert into Brand_temp select * from brand order by branddescription Then we dropped the Brand table and renamed the temp table as brand like as follows: Drop table Brand ALTER table brand_temp RENAME TO Brand When we tried with the original Brand Table in the application it is taking around 15 minutes to fetch the record. But when we use the temp table it's taking 11 secs to fetch the record. We are not clear why there is a drastic change in fetching the records since the structure remains the same and we are using the same application for fetching the records from both the table. Kindly clarify us in this regard. Thanks in advance. Regards, Farzana. -- View this message in context: http://www.nabble.com/Fetching-records-from-Temp-table-tp17399000p17399000.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
