Re: [sqlite] SQLite version 3.7.5
On Feb 1, 2011, at 2:32 AM, Richard Hipp wrote: > performance is improved over the previous release by 1% or 2%. Hmmm... under 3.7.3, a little process of mine was handling around 52M records (~3GB) in about 113 minutes... now, with 3.7.5, the same process, running on the same machine, under the same load conditions, using the same data, takes only 82 minutes... about 1/2 hour less... was 3.7.3 a particularly poky vintage? Or did 3.7.5 introduce some kick-ass improvements? Or am I just lucky? :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite and windows server 2008 DFS
Hi, any experience using an sqlite db on Windows server 2008 with DFS enabled ? A customer with this configuration reported me that there is a lost of data. Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.5
- Original Message From: Richard Hipp To: General Discussion of SQLite Database ; sqlite-annou...@sqlite.org Sent: Mon, January 31, 2011 5:32:15 PM Subject: [sqlite] SQLite version 3.7.5 >As of this release, the popular ADO.NET provider for SQLite by Robert >Simpson, System.Data.SQLite, is hosted on the SQLite website. See >http://System.Data.SQLite.org/ for additional information. Release builds >of System.Data.SQLite will appears on the SQLite download page over the >course of the next week. Very impressive. Is there a story to tell about how the SQLite team came to choose this path? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG REPORT: 3.7.5 fails to compile with SQLITE_OMIT_WAL
The routine pager_write_changecounter in pager.c at line 2933 is eliminated by the #ifndef SQLITE_OMIT_WAL at line 2854 This causes a compile error in the pager_write_pagelist routine at line 4014 error LNK2001: unresolved external symbol _pager_write_changecounter@4 pager.obj sqlite3 Moving the routing in pager.c to after the #endif at line 3025 will solve the problem. Are you running your test suite against comditional compilation with SQLITE_OMIT_WAL? Regards, Noah Hart -- View this message in context: http://old.nabble.com/BUG-REPORT%3A-3.7.5-fails-to-compile-with-SQLITE_OMIT_WAL-tp30818271p30818271.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
Re: [sqlite] Optimising a bad design decision
Many thanks Puneet and Igor - I will do those things. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising a bad design decision
On Tuesday, February 1, 2011 at 10:30 AM, Ian Hardingham wrote: > Hi Igor, thankyou. > > If I wish to make this modification now, what steps would I need to > take? And in your opinion what % of the optimisation of doing it with > integers would this provide? > ALTER TABLE MyTable RENAME TO MyTable_orig; CREATE TABLE MyTable ( id INDEX PRIMARY KEY, username TEXT COLLATE NOCASE, .. rest of schema from MyTable .. ); INSERT INTO MyTable (username, .. other old columns ..) SELECT username, .. other old columns .. FROM MyTable_orig; The above commands will preserve your existing MyTable, and also create a new table called MyTable_new that will have an integer primary key, and will also have a username column that doesn't care about case. Then, start refactoring your code one step at a time. Resist the urge to make mass modifications. Don't try fancy tricks with grep and regexp replace. Every time you make a mod, test it. Oh, and use a version control system for your code. Your db will be safe because your original table will be untouched. When you are completely satisfied, DROP TABLE MyTable_orig. > Thanks, > Ian > > On 01/02/2011 16:19, Igor Tandetnik wrote: > > On 2/1/2011 10:10 AM, Ian Hardingham wrote: > > > My core users table has a user defined by a string which is their name. > > > This string is used to address many other tables relating to users. Not > > > only is the primary key a string (which I understand is bad enough), but > > > I also have to use LIKE rather than = because the high level language I > > > use is a bit eccentric about case-ing. > > You could have created your table like this: > > > > create table MyTable(username text primary key collate NOCASE, ...); > > > > Then, plain vanilla = comparison would be case-insensitive (for latin > > characters A-Z and a-z only, but then LIKE has the same limitation), and > > would use the index. > > ___ > 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] Optimising a bad design decision
On 2/1/2011 11:30 AM, Ian Hardingham wrote: > If I wish to make this modification now, what steps would I need to > take? Export data from the table, drop table, recreate table with the new schema, import data back. Update all queries from LIKE to =. > And in your opinion what % of the optimisation of doing it with > integers would this provide? Test it, find out. Create a table having an INTEGER PRIMARY KEY column, and another with TEXT PRIMARY KEY COLLATE NOCASE column. Fill both with dummy data. Run queries that are common in your application. See what timing you get. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising a bad design decision
Hi Igor, thankyou. If I wish to make this modification now, what steps would I need to take? And in your opinion what % of the optimisation of doing it with integers would this provide? Thanks, Ian On 01/02/2011 16:19, Igor Tandetnik wrote: > On 2/1/2011 10:10 AM, Ian Hardingham wrote: >> My core users table has a user defined by a string which is their name. >> This string is used to address many other tables relating to users. Not >> only is the primary key a string (which I understand is bad enough), but >> I also have to use LIKE rather than = because the high level language I >> use is a bit eccentric about case-ing. > You could have created your table like this: > > create table MyTable(username text primary key collate NOCASE, ...); > > Then, plain vanilla = comparison would be case-insensitive (for latin > characters A-Z and a-z only, but then LIKE has the same limitation), and > would use the index. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising a bad design decision
On 2/1/2011 10:10 AM, Ian Hardingham wrote: > My core users table has a user defined by a string which is their name. > This string is used to address many other tables relating to users. Not > only is the primary key a string (which I understand is bad enough), but > I also have to use LIKE rather than = because the high level language I > use is a bit eccentric about case-ing. You could have created your table like this: create table MyTable(username text primary key collate NOCASE, ...); Then, plain vanilla = comparison would be case-insensitive (for latin characters A-Z and a-z only, but then LIKE has the same limitation), and would use the index. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
Hello Marian, On Feb 1, 2011, at 1:28 PM, Marian Cascaval wrote: > Since this topic has lead to different sub-topic I dare ask a question (I'm a > beginner both in C++ and, Oh boy, in SQLite too). > > Tito, do you really need the 5th argument in sqlite3_prepare_v2() i.e. > "&oneSQL"? > From what I understand from your code, there's only one SQL statement to be > prepared, so there would be no need for the supposedly next SQL statement. > Do you reuse (reset) these statements? > I was under the impression that the 5th argument is used when the SQL > statement > string contains more than one SQL statement. > > Thanks for your patience if I misunderstood something .. or all. Yes, you understood right. I was using the 5th arg for debugging reasons, as I was checking whether the memory was being smashed somewhere. As for the statements, yes, I'm reusing them all the time. Regards, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
Since this topic has lead to different sub-topic I dare ask a question (I'm a beginner both in C++ and, Oh boy, in SQLite too). Tito, do you really need the 5th argument in sqlite3_prepare_v2() i.e. "&oneSQL"? >From what I understand from your code, there's only one SQL statement to be prepared, so there would be no need for the supposedly next SQL statement. Do you reuse (reset) these statements? I was under the impression that the 5th argument is used when the SQL statement string contains more than one SQL statement. Thanks for your patience if I misunderstood something .. or all. Marian Cascaval From: Tito Ciuro To: General Discussion of SQLite Database Sent: Mon, January 31, 2011 9:38:57 PM Subject: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases // Build the first statement sqlite3_stmt *oneStatement = NULL; const char *oneSQL = [[NSString stringWithFormat:@"INSERT INTO %@(%@, %@, %@, %@) VALUES (?,?,?,?);", NSFValues, NSFKey, NSFAttribute, NSFValue, NSFDatatype]UTF8String]; int statusOne = sqlite3_prepare_v2(db, oneSQL, (int)strlen(oneSQL), &oneStatement, &oneSQL); ___ 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] Optimising a bad design decision
Quoth Ian Hardingham , on 2011-02-01 15:10:43 +: [paragraphs reordered] > As there are hundreds of queries around my server codebase I would > prefer not to have to change every single one to use a numeric id for a > user rather than a string - but maybe this is the only real way of > optimising this properly? That might be preferable, but what form do these queries take? Are you talking about joins with other tables, or lookups based on incoming parameters, or... ? If they're joins, then you need to change the semantics of the columns in the other tables and update the join expressions accordingly (which might propagate into the application if it also relies on those name columns being directly accessible). If they're lookups, then the effects could propagate into the surrounding application directly. Note that it's possible to do this sort of thing incrementally without totally breaking the queries in the middle, since you almost definitely want to keep a UNIQUE index on the name anyway. Regardless of that: > My core users table has a user defined by a string which is their name. > This string is used to address many other tables relating to users. Not > only is the primary key a string (which I understand is bad enough), but > I also have to use LIKE rather than = because the high level language I > use is a bit eccentric about case-ing. That sounds suspicious. If it's only case distinctions and not whitespace or other cruft, and in particular if the names in the database can be in a normalized form and it's only the parameters that are problematic, you can normalize the parameter on the SQL side first: « name = LOWER(:foo) », for instance. Or « name = TRIM(LOWER(:foo)) ». That might get you better index usage on the name regardless of whether you change to a synthetic integer primary key. Of course you have to do the normalization the same way when writing the records to the DB in the first place. > Thanks, > Ian ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Optimising a bad design decision
Hey guys. First off, thanks to all who have helped me in the recent weeks. We're in crunch on my project and my rather complicated server, combined with my lack of DB experience, has given me plenty of problems to deal with. My core users table has a user defined by a string which is their name. This string is used to address many other tables relating to users. Not only is the primary key a string (which I understand is bad enough), but I also have to use LIKE rather than = because the high level language I use is a bit eccentric about case-ing. The time has come for me to sort out this problem, as it is negatively impacting the speed of almost every query I do, and the speed of these queries is definitely a problem. As there are hundreds of queries around my server codebase I would prefer not to have to change every single one to use a numeric id for a user rather than a string - but maybe this is the only real way of optimising this properly? I would appreciate any general advice about a solution to this which doesn't require refactoring every query, and if the opinion is that I do need to just bite the bullet and make every reference to a user in SQLite an integer, what would be the best way of going about this exactly. Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Doc errors: Too many "checked out." in Status Parameters for database connections
There are far too many "checked out." in the following docs, probably left-overs from copy-paste operations: ** ^(SQLITE_DBSTATUS_LOOKASIDE_HIT ** This parameter returns the number malloc attempts that were ** satisfied using lookaside memory. Only the high-water value is meaningful; ** the current value is always zero. ** checked out.)^ ** ** ^(SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE ** This parameter returns the number malloc attempts that might have ** been satisfied using lookaside memory but failed due to the amount of ** memory requested being larger than the lookaside slot size. ** Only the high-water value is meaningful; ** the current value is always zero. ** checked out.)^ ** ** ^(SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL ** This parameter returns the number malloc attempts that might have ** been satisfied using lookaside memory but failed due to all lookaside ** memory already being in use. ** Only the high-water value is meaningful; ** the current value is always zero. ** checked out.)^ Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
Hi Drake, On Feb 1, 2011, at 9:33 AM, Drake Wilson wrote: > Quoth Tito Ciuro , on 2011-02-01 09:01:09 -0200: >> I don't think so. The fileSystemRepresentation method should we used >> when dealing with file-based paths. > > But not when dealing with SQLite paths, unless I'm mistaken about what > fileSystemRepresentation does. sqlite3_open_v2 always takes UTF-8 and > does any filesystem-specific encoding transformations internally. (It > may still be that it does it incorrectly on some platforms, in which > case that may be a bug.) Thanks for the heads up. -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
Quoth Tito Ciuro , on 2011-02-01 09:01:09 -0200: > I don't think so. The fileSystemRepresentation method should we used > when dealing with file-based paths. But not when dealing with SQLite paths, unless I'm mistaken about what fileSystemRepresentation does. sqlite3_open_v2 always takes UTF-8 and does any filesystem-specific encoding transformations internally. (It may still be that it does it incorrectly on some platforms, in which case that may be a bug.) > -- Tito ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Second sqlite3_prepare_v2() call fails on iOS path-based databases
Hi Afriza, On Feb 1, 2011, at 4:16 AM, Afriza N. Arief wrote: > On Tue, Feb 1, 2011 at 3:38 AM, Tito Ciuro wrote: > >> Hello, >> >> The following code snippet runs fine on Mac OS X, but fails on the iOS >> simulator: >> >> // Obtain a path for the database >> NSString *docs = >> [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, >> YES) lastObject]; >> NSString *path = [[docs stringByAppendingPathComponent:@ >> "myDB.sqlite"]fileSystemRepresentation]; >> >> // Open the database >> sqlite3 *db = NULL; >> int statusOpen = sqlite3_open_v2( fileSystemRepresentation, &db, >> SQLITE_OPEN_READWRITE | >> SQLITE_OPEN_CREATE | SQLITE_OPEN_AUTOPROXY | SQLITE_OPEN_FULLMUTEX, NULL); >> > > Do you need UTF8String for the sqlite3_open_v2() ? I don't think so. The fileSystemRepresentation method should we used when dealing with file-based paths. This is because the length of the encoded string in foreign file systems can be longer than the number of Unicode characters in the NSString. So, you would end up with a different length (a "wrong" string) by using UTF8String. Regards, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer or string in fts3 table
On Mon, Jan 31, 2011 at 9:27 PM, Samuel Adam wrote: > I can’t help you there other than to say, give your boss the facts and > point out that if QA approved that schema, QA failed its job. Would that it were it so easy. Unfortunately I work for a startup, and this code was developed by a partner, I'm simply helping debug because it runs on our systems. Assigning blame does nothing for me, and thankfully I don't have to deal with organizational politics anyway so my interest is only in the expediency of a solution. > Per my previous mail, FTS3 is quietly ignoring your PRIMARY KEY > declaration on "id". To use your ID values as an INTEGER PRIMARY KEY, you > will need to INSERT, SELECT, and otherwise manipulate your ID values using > one of the identifiers “"docid" [] "rowid", "oid" [or] "_oid_"” according > to FTS3 docs. > No. If you are using an integer PK to do the JOIN (which is a good idea), > you need to join on "rowid" in your FTS3 table and an INTEGER PRIMARY KEY > in your other table(s). Okay so when using fts tables, generally they should only ever join on the official primary key (oid). If other foreign key relations are necessary, there should be a corresponding regular sqlite table to hold the foreign keys (and anything that doesn't require MATCHing). That's my takeaway, correct me if I'm wrong. > With your current schema, your primary key constraint is not enforced; > referential integrity is not guaranteed; and if bad data is accidentally > inserted, joins (and other actions) may fail *even* on the box where your > "id" is coming out as an INTEGER. Not a big concern because this is generated once from a mysql database that *does* have the constraint and never changed for the lifetime of the database. Point taken however. > I suggest you refocus your efforts; unless you want to dig into FTS3 > sources with an eye to patching for your needs, the problem is already > diagnosed. Good advice, but the speed at which I can deploy a change to the script is orders of magnitude greater than the speed at which an application patch can be developed, tested, and distributed to end users, so I may have to do both. Anyway, thanks for all your help. -gabe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL for single user database on NFS and Solaris
On Sat, Jan 29, 2011 at 10:06 PM, Ginn Chen wrote: > I think a single process accessing a single Sqlite database at a time over > NFS is supposed to be fine. > > But it is not working on Solaris. > On Solaris, man page of mmap() has > > EAGAINThe file to be mapped is already locked using > advisory or mandatory record locking. See > fcntl(2). > > I found if the file on NFS has been locked with fcntl(), mmap() may fail. > It doesn't fail if both l_start and l_end are 0. > > Thus, I got "Error: disk I/O error" for any operation after setting > journal_mode to WAL. > See https://bugzilla.mozilla.org/show_bug.cgi?id=629296 > > The problem is setting journal_mode to WAL is successful, but it cannot be > set back. > User has to move the db file to another filesystem and set journal_mode to > delete. > > I guess -DSQLITE_SHM_DIRECTORY="/var/tmp" might be a solution, but it is > unsupported. > We looked at that idea when we were originally developing WAL, but it falls down badly for processes in a chroot jail. > > Any idea how can we make the behavior better on Solaris? > Maybe fallback to another lock method if it is on NFS? > > BTW: With the same NFS server, sqlite3 works fine on Linux and Mac OS X. > > Thanks, > > Ginn > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cause of "disk I/O errors"
I did report an issue much earlier on with regards to retrying io operations when some other process (like virus scanners) are touching the db file. Hopefully it will be of use to you: http://www.mail-archive.com/sqlite-users@sqlite.org/msg51895.html - Nasron Cheong ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL for single user database on NFS and Solaris
On Mon, Jan 31, 2011 at 5:44 AM, Dan Kennedy wrote: > Right. At most a single connection at a time. Unfortunately, that won't work for this issue since we use more than one connection in our process. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL and big transaction
The documentation says that "for transactions in excess of a gigabyte, WAL mode may fail with an I/O or disk-full error". Can anybody explain for what exact situations an I/O error occurs? I have tested transaction in excess of 2Gb several times on Windows platform with the latest SQLite version and it works OK. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
