Re: [sqlite] Problem with incremental_vacuum and WAL
On Wed, Apr 6, 2011 at 9:27 AM, Filip Navara wrote: > On Wed, Apr 6, 2011 at 6:36 AM, Dan Kennedy wrote: >> On 04/05/2011 04:49 PM, Filip Navara wrote: >>> Hello, >>> >>> we are having problem with database that originated on computer of one >>> of our customers. >>> >>> The database is used in WAL mode with auto_vacuum=1 and page_size=1024. >>> >>> When running the "pragma incremental_vacuum(1);" command the WAL file >>> grows to 14Mb, while we would expect it to grow only to about 4Kb (1Kb >>> per page + some overhead). This causes the transaction to run for much >>> longer time than expected and eventually the WAL file grows to several >>> gigabytes when we try to run incremental_vacuum for 4096 pages. >>> >>> Additional facts: >>> - The database was created with SQLite 3.7.5 running on Windows >>> - "pragma integrity_check;" reports ok >>> - Some free trunk list pages contain weird values in the "number of >>> entries" field >>> >>> I have attached most of the dumps that I could create with the regular >>> SQLite tools (showdb dbheader, sqlite3_analyzer output, beginning of >>> the free list dump). I'm willing to send the whole database file >>> (~5Gb) to sqlite developers on request. >>> >>> My questions are: >>> - Is is expected behavior that "pragma incremental_vacuum(1);" can >>> create 14Mb WAL file even though the page size is just 1Kb? >>> - If not, is it a know bug? If yes, in what circumstances? >>> - Is the free list corrupted? And if it is, how could this happen and >>> should "pragma integrity_check;" verify it? >> >> Was there a *-wal file in the file-system when you ran the >> [showdb] tool? [showdb] just reads the database file, it is >> not smart enough to know when it should read pages out of the >> wal file instead of the db. So if there is a wal file that >> contains content, [showdb] could report corruption. >> >> Also, can you try with this patch? >> >> http://www.sqlite.org/src/ci/311d0b613d >> >> It might help with the 14MB wal files. >> >> Dan. > > Hi Dan, > > there was no -wal file when I ran the showdb tool. > > I recompiled sqlite from the current 3.7.6 snapshot > (sqlite-amalgamation-201104052208.zip) and verified that it contained > your patch. Unfortunately it didn't help with the large WAL file for > this particular database. > > Best regards, > Filip Navara > After some more diagnosis I discovered that the free list is not corrupted afterall. It's just that the showdb tool fails to work properly with databases larger than 4Gb and since the free list trunk pages are located beyond the 4Gb barrier it caused problems. This leaves me with the incremental vacuum problem, where the journal/WAL grows uncontrollably. Also I have created an anonymized version of the database that doesn't contain most of the original data and where all free list leaf pages are overwritten with zeroes. It can be downloaded from http://www.emclient.com/temp/mail_data.zip (~ 30Mb). Best regards, Filip Navara ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No journal vs WAL journal
On 6 Apr 2011, at 8:14pm, Nikolaus Rath wrote: > Simon Slavin writes: >> On 6 Apr 2011, at 5:06pm, Nikolaus Rath wrote: >> >>> Another question though: what is the recommended way to insert lots >>> of data with WAL enabled? Without a transaction, the WAL file stays >>> small but the insertions take very long time. With a transaction, >>> things are faster but the WAL file grows extremely big. >> >> The recommended way is not to care about the size of the journal file. >> Do it in a transaction and make sure you have some free disk space. > > From http://www.sqlite.org/wal.html: > > , > | WAL works best with smaller transactions. WAL does not work well for > | very large transactions. For transactions larger than about 100 > | megabytes, traditional rollback journal modes will likely be faster. For > | transactions in excess of a gigabyte, WAL mode may fail with an I/O or > | disk-full error. It is recommended that one of the rollback journal > | modes be used for transactions larger than a few dozen megabytes. > ` > > That sounds as if large transactions are not recommended with wall. Is > that page no longer up-to-date? I have no reason to believe that the page is not up-to-date. Your first question was about how to do something with WAL enabled -- it presupposed that WAL was enabled. The quote from the web page tells you that using WAL might not be a good idea for all situations. Your most frequent situation for inserting 100 megabytes of data into a database only happens once per user: when you import the data from the user's old datastore. So it happens once, under conditions where an expert in the software can see what's happening and monitor disk space. Once you quit the importing program (which closes the only connection to the database file) the journal file vanishes anyway, so the fact that it was huge is a problem only for a short time. Under normal situations of usage you're unlikely to encounter a transaction that involves more than a megabyte of data, so WAL once again becomes a plausible candidate for a journaling system. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No journal vs WAL journal
Simon Slavin writes: > On 6 Apr 2011, at 5:06pm, Nikolaus Rath wrote: > >> Another question though: what is the recommended way to insert lots >> of data with WAL enabled? Without a transaction, the WAL file stays >> small but the insertions take very long time. With a transaction, >> things are faster but the WAL file grows extremely big. > > The recommended way is not to care about the size of the journal file. > Do it in a transaction and make sure you have some free disk space. From http://www.sqlite.org/wal.html: , | WAL works best with smaller transactions. WAL does not work well for | very large transactions. For transactions larger than about 100 | megabytes, traditional rollback journal modes will likely be faster. For | transactions in excess of a gigabyte, WAL mode may fail with an I/O or | disk-full error. It is recommended that one of the rollback journal | modes be used for transactions larger than a few dozen megabytes. ` That sounds as if large transactions are not recommended with wall. Is that page no longer up-to-date? Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Content filtered message notification
-- Forwarded message -- From: Date: Wed, Apr 6, 2011 at 2:48 PM Subject: Content filtered message notification To: sqlite-users-ow...@sqlite.org The attached message matched the sqlite-users mailing list's content filtering rules and was prevented from being forwarded on to the list membership. You are receiving the only remaining copy of the discarded message. -- Forwarded message -- From: Oliver Schneider To: sqlite-users@sqlite.org Date: Wed, 06 Apr 2011 18:48:11 + Subject: Re: [sqlite] Access violation at address 6090B662 in module 'sqlite3.dll'. Read of address DE8D6B84 Hi, On 2011-04-06 18:36, Vander Clock Stephane wrote: > in heavy multithread environnmeent we receive (one time a month, so not > very often), this error : > Access violation at address 6090B662 in module 'sqlite3.dll'. Read of > address DE8D6B84 > > any idea ? my suggestion would be to install the proper exception handlers at the top-level of the application and then use MiniDumpWriteDump to write a dump file. Load that into WinDbg with the proper symbols available in the path and you will learn a lot about where it happens and potentially why. Should this be a heap corruption, things are usually bleak at first, but there are some methods here as well. If your application is cross-platform I strongly suggest to get your hands on Valgrind and run the code under it. It will point out all kinds of issues. If it is not, Application Verifier and some profilers such as AQTime can be of help on Windows in tracking down such issues. Consider that a heap corruption is often caused long before the attempt is made to read/write the corrupted location(s). // Oliver -- 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] Access violation at address 6090B662 in module 'sqlite3.dll'. Read of address DE8D6B84
> any idea ? Your application have race conditions and corrupts memory. Pavel On Wed, Apr 6, 2011 at 2:36 PM, Vander Clock Stephane wrote: > hello, > > in heavy multithread environnmeent we receive (one time a month, so not > very often), this error : > Access violation at address 6090B662 in module 'sqlite3.dll'. Read of > address DE8D6B84 > > any idea ? > ___ > 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
[sqlite] Access violation at address 6090B662 in module 'sqlite3.dll'. Read of address DE8D6B84
hello, in heavy multithread environnmeent we receive (one time a month, so not very often), this error : Access violation at address 6090B662 in module 'sqlite3.dll'. Read of address DE8D6B84 any idea ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] substr fails in a query: looks like a bug
Thanks for the interest, but I now can't repro the bug using pure sqlite3, and only trigger it using Python (2.6) with its sqlite3 module. - Eric On Tue, Apr 5, 2011 at 5:21 PM, Eric Promislow wrote: > This won't be a trivial case to reproduce -- I need to create two tables, > with several rows, and my main db tool is broken right now. I did find a > workaround, > groveling over the results in Python. > > If I bind the values to literals like so: > select "... str1" as p1_path and "... str2" as p2_path where > > the substr clause works. So I do need to create a real table to > repro this. > > > On Tue, Apr 5, 2011 at 5:12 PM, Igor Tandetnik wrote: > >> On 4/5/2011 6:59 PM, Eric Promislow wrote: >> > Notice the clause in the middle of the query: >> >and (substr(p2.path, length(p1.path) + 1, 1) = "/" >> > or substr(p2.path, length(p1.path) + 1, 1) != "/") >> > >> > If I comment out this full clause, the query returns the expected result >> > set. >> > >> > But if I leave it uncommented, the query returns an empty set. But the >> > clause >> > should be tautologically true -- either the character after the prefix >> is >> > "/" or it >> > isn't. >> >> The third possiblity is that substr() returns null, in which case both >> comparisons will be false. >> -- >> Igor Tandetnik >> >> ___ >> 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] No journal vs WAL journal
On 6 Apr 2011, at 5:06pm, Nikolaus Rath wrote: > Another question though: what is the recommended way to insert lots of > data with WAL enabled? Without a transaction, the WAL file stays small > but the insertions take very long time. With a transaction, things are > faster but the WAL file grows extremely big. The recommended way is not to care about the size of the journal file. Do it in a transaction and make sure you have some free disk space. You will, of course, eventually close the database file -- possibly immediately after a long importing session. This will collapse the WAL journal. Big journal files may make WAL mode unsuitable for small devices, of course, but on a desktop computer it shouldn't be a problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Balance between keeping DBs open vs repeated open/close
On Wed, Apr 6, 2011 at 6:11 PM, Technology Lighthouse wrote: > Thanks Stephan and Simon - I kind of figured it was one of those "how > long is a ball of string" questions, but I just wanted to check there > weren't any particular gotchas to watch out for. > LOL, i'll have to remember the ball of string comparison. In the off chance that you don't know about it already: http://www.sqlite.org/lang_attach.html with that you can attach several db files as one virtual db. That won't inherently cut the number of open file handles (it should be the same as manually opening them), but it might simplify the usage of multiple dbs. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Balance between keeping DBs open vs repeated open/close
Thanks Stephan and Simon - I kind of figured it was one of those "how long is a ball of string" questions, but I just wanted to check there weren't any particular gotchas to watch out for. Regarding the separate files, it seems the best way to go. Each individual table could get pretty large (possibly several gb), and the one table per DB approach means that should any one file get damaged in some way, only that one set of data would be lost. -- Paul Roberts ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read full txt file in one record
On Wed, 6 Apr 2011 00:08:47 +0300, Gert Van Assche
wrote:
>Dear all,
>
>what would be the best way to read a full txt file into one record?
>Now, when I use
> .import FILE TABLE
>I have every line on a record.
>
>I need all lines of one file on one record.
>The next record I need to fill with all lines of another file.
Here's a oneliner (line wrapped by mail) that stores a series of
text files in BLOBs, one file per row.
It works for binary files as well.
d=test.db3 ; echo "CREATE TABLE Files (name TEXT PRIMARY
KEY,contents BLOB);" | sqlite3 $d ; for f in yourfiles* ; do echo
"INSERT INTO Files (name,contents) VALUES ('$f',X'$(od -v -A n -t x1
$f|tr -d '\r\n\t ')');" | sqlite3 $d ; done
It might not be what you're looking for.
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No journal vs WAL journal
Pavel Ivanov writes: >> Furthermore, if I turn off auto checkpointing, the WAL file grows to >> more than 5 GB without transactions, but only to about 922 MB with a >> transaction. Are the commit markers really taking that much space? > > WAL-journal is not some kind of change log with commit markers. It's a > sequence of changed pages with commit markers (which AFAIK can take as > much as 1Kb in size). So let's say your database has 1Kb pages, each > transaction adds a row of 50 bytes. For each transaction SQLite must > write all changed pages into WAL-journal. It would be at least one > page per transaction. So the size of WAL-journal would be like 20-40 > times bigger than amount of data changed. If you combine for example > each 40 transactions into one then you'll get size of WAL-journal > probably only twice or triple the total size of changed data. With all > transactions combined into single one the size of WAL-journal will be > almost equal to size of all changed pages written once... > > I hope you understand now why you see the difference in WAL-journal size. Yeah, that makes sense, thanks (but I still don't understand the time difference). Another question though: what is the recommended way to insert lots of data with WAL enabled? Without a transaction, the WAL file stays small but the insertions take very long time. With a transaction, things are faster but the WAL file grows extremely big. The only idea I can come up with is to either arbitrarily split the inserts into small transactions, or to switch to a different journaling mode. Is there any better solution? Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No journal vs WAL journal
Igor Tandetnik writes: > On 4/5/2011 10:01 PM, Nikolaus Rath wrote: >> I'm experimenting with creation of a 156 MB database from scratch. I've >> set synchronous = NORMAL and locking_mode = exlusive. >> >> With journal_mode = off and without wrapping the INSERTs into a >> transaction, creating the db takes 54 seconds. >> >> With journal_mode = WAL (and still no transaction), it takes an >> impressive 900 seconds. >> >> With journal_mode = WAL and the INSERTs packed into a transaction, the >> total time is 60 seconds. >> >> I am slightly surprised by this. I was expecting that the difference >> between transaction/no transaction with WAL is just the writing of an >> additional few bytes of commit marker into the WAL file with every >> insert. But apparently something much more time consuming is happening >> as well. > > Committing a transaction involves waiting until the data is physically > transferred to the disk surface (as opposed to sitting in OS buffers). My understanding was that with WAL and synchronous = NORMAL, only checkpointing waits for the data to physically arrive on the disk, and committing a transaction happily continues once the data has been handed to the OS. Am I wrong? > This requires at least two complete revolutions of the disk platter. > With an above-average hard drive running at 15000 RPM, you can have a > throughput of 125 transactions per second, at best. How many rows are > you inserting? About 2 million. Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increment a row value...
Awesome! Thank you. > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Enrico Thierbach > Sent: Wednesday, April 06, 2011 11:16 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Increment a row value... > > > On 06.04.2011, at 17:13, John D. Marinuzzi wrote: > > > Hello, > > > > > > > > Perhaps this is more of a SQL question, but I am curious if SQLite has > > some kind of implementation for incrementing an integer within a row > > without actually reading the value and then updating the record. Is that > possible? > > > > > > UPDATE table SET number=number+1 WHERE id=4711 > > /eno > > ___ > 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] Increment a row value...
On 06.04.2011, at 17:13, John D. Marinuzzi wrote: > Hello, > > > > Perhaps this is more of a SQL question, but I am curious if SQLite has some > kind of implementation for incrementing an integer within a row without > actually reading the value and then updating the record. Is that possible? > > UPDATE table SET number=number+1 WHERE id=4711 /eno ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Increment a row value...
Hello, Perhaps this is more of a SQL question, but I am curious if SQLite has some kind of implementation for incrementing an integer within a row without actually reading the value and then updating the record. Is that possible? Thanks, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No journal vs WAL journal
> Two ? One for the page holding the table, another for the page holding the > primary key ? If table has INTEGER PRIMARY KEY then it is the same page I believe. :) I.e. table storage is in fact an index on rowid (or its alias). Pavel On Wed, Apr 6, 2011 at 10:50 AM, Simon Slavin wrote: > > On 6 Apr 2011, at 3:15pm, Pavel Ivanov wrote: > >> For each transaction SQLite must >> write all changed pages into WAL-journal. It would be at least one >> page per transaction. > > Two ? One for the page holding the table, another for the page holding the > primary key ? > > Simon. > ___ > 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] No journal vs WAL journal
On 6 Apr 2011, at 3:15pm, Pavel Ivanov wrote: > For each transaction SQLite must > write all changed pages into WAL-journal. It would be at least one > page per transaction. Two ? One for the page holding the table, another for the page holding the primary key ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No journal vs WAL journal
> Furthermore, if I turn off auto checkpointing, the WAL file grows to > more than 5 GB without transactions, but only to about 922 MB with a > transaction. Are the commit markers really taking that much space? WAL-journal is not some kind of change log with commit markers. It's a sequence of changed pages with commit markers (which AFAIK can take as much as 1Kb in size). So let's say your database has 1Kb pages, each transaction adds a row of 50 bytes. For each transaction SQLite must write all changed pages into WAL-journal. It would be at least one page per transaction. So the size of WAL-journal would be like 20-40 times bigger than amount of data changed. If you combine for example each 40 transactions into one then you'll get size of WAL-journal probably only twice or triple the total size of changed data. With all transactions combined into single one the size of WAL-journal will be almost equal to size of all changed pages written once... I hope you understand now why you see the difference in WAL-journal size. Pavel On Tue, Apr 5, 2011 at 10:01 PM, Nikolaus Rath wrote: > Hello, > > I'm experimenting with creation of a 156 MB database from scratch. I've > set synchronous = NORMAL and locking_mode = exlusive. > > With journal_mode = off and without wrapping the INSERTs into a > transaction, creating the db takes 54 seconds. > > With journal_mode = WAL (and still no transaction), it takes an > impressive 900 seconds. > > With journal_mode = WAL and the INSERTs packed into a transaction, the > total time is 60 seconds. > > I am slightly surprised by this. I was expecting that the difference > between transaction/no transaction with WAL is just the writing of an > additional few bytes of commit marker into the WAL file with every > insert. But apparently something much more time consuming is happening > as well. > > Furthermore, if I turn off auto checkpointing, the WAL file grows to > more than 5 GB without transactions, but only to about 922 MB with a > transaction. Are the commit markers really taking that much space? > > Can someone shed some light on what factor I am missing here? > > > Thanks, > > -Nikolaus > > -- > »Time flies like an arrow, fruit flies like a Banana.« > > PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C > ___ > 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] read full txt file in one record
>3. edit the database file with a hex editor, replacing "~~" with "0D 0A". That seems pretty dangerous! Rather update the table using the standard replace() function. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite and Create_collation
jarno.lavikonm...@nokia.com wrote: > So I defined > > #define SQLITE_ENABLE_ICU > > Then I try call to create collation: > >int iRetVal = > sqlite3_create_collation(m_sqliteDb,pstrDbName,SQLITE_UTF8,NULL,sqliteLocaleAwareCompare); You are implementing your own custom collation. What does this have to do with ICU? > But this sqliteLocaleAwareCompare never gets called when query is executed to > database Does your query mention the collation? The second parameter of sqlite3_create_collation - pstrDbName in your example - is the collation name. Let's say it's "MYCOLL". Run a query that looks like this: select * from MyTable where SomeField = 'xyz' collate MYCOLL; > Then I was wondering that should I do that create_collation to this following > method defined in sqlite3.c but that never succeeded > static int icuLikeCompare( > const uint8_t *zPattern, /* LIKE pattern */ > const uint8_t *zString,/* The UTF-8 string to compare against */ > const UChar32 uEsc /* The escape character */ This is not a collation, this function is for implementing LIKE operator. > Now I just wonder which way to proceed ? That rather depends on what you are trying to achieve. It's not clear to me. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug: SQLite Encryption Extension And Backup API
Hi,
I think I found a bug creating an encrypted backup.
The source database is encrypted using key:
aes256:A94A8FE5CCB19BA61C4C0873D391E987982FBBD3
The source database is readable, I can run select queries and insert data
with no problem.
When I use the following function to create a backup of the database, the
backup database becomes unreadable.
I used the same encryption key as the source database for the backup one,
but when I try to read something from the backed up database I get an error
that the database is encrypted.
Thanks
int backupDb(sqlite3 *pDb, const char *zFilename, const char *zKey)
{
int rc;
sqlite3 *pBackupDb;
sqlite3_backup *pBackup;
rc = sqlite3_open(zFilename, &pBackupDb);
if( rc==SQLITE_OK )
{
if (zKey != NULL)
sqlite3_key(pBackupDb, zKey, strlen(zKey));
pBackup = sqlite3_backup_init(pBackupDb, "main", pDb, "main");
if( pBackup )
{
do
{
rc = sqlite3_backup_step(pBackup, 100);
if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
sqlite3_sleep(10);
}
}
while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );
sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pBackupDb);
}
sqlite3_close(pBackupDb);
return rc;
}
Ulric Auger
--
Groupe Techna Inc.
ul...@gtechna.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] read full txt file in one record
From: Gert Van Assche To: sqlite-users@sqlite.org Sent: Wed, April 6, 2011 12:08:47 AM Subject: [sqlite] read full txt file in one record >Dear all, >what would be the best way to read a full txt file into one record? Until the experts reply to this, I'll try my cumbersome solution: (I am no expert, but I tried to find a walk around which worked for me) 1. In every text file: replace (with a hex editor) the hex sequence "0D 0A" (this is the CR + LF code ) with any other tow ASCII chars that you know for sure they won't appear in the text files (i.e. "~~"). 2. .import FILE TABLE 3. edit the database file with a hex editor, replacing "~~" with "0D 0A". To test it: SELECT rowid, your_text_data_column FROM your_table; Hopes it helps .. somehow. >Now, when I use .import FILE TABLE >I have every line on a record. >I need all lines of one file on one record. >The next record I need to fill with all lines of another file. >thanks for your advise. >Gert ___ 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
[sqlite] Sqlite and Create_collation
Hi ,
We are facing some issues now, when we need to make SQL queries and in our
databases there are non ASCII characters, and SQL queries should be case
insensitive ..
Then I read from the documentationn about enable ICU for sqlite
So I defined
#define SQLITE_ENABLE_ICU
Then I try call to create collation:
int iRetVal =
sqlite3_create_collation(m_sqliteDb,pstrDbName,SQLITE_UTF8,NULL,sqliteLocaleAwareCompare);
which returns SQLITE_OK
and then I implemented as a test following method:
int sqliteLocaleAwareCompare(void *, int ll, const void *l, int rl,
const void *r)
{
QString left = QString::fromUtf8((const char*)l);
QString right = QString::fromUtf8((const char *)r);
//qLog(Sql) << "comparing:" << left << "with" << right << "result"
return QString::localeAwareCompare(left, right);
}
To do the comparison ..
But this sqliteLocaleAwareCompare never gets called when query is executed to
database
Then I was wondering that should I do that create_collation to this following
method defined in sqlite3.c but that never succeeded
static int icuLikeCompare(
const uint8_t *zPattern, /* LIKE pattern */
const uint8_t *zString,/* The UTF-8 string to compare against */
const UChar32 uEsc /* The escape character */
Now I just wonder which way to proceed ?
Br. Jarno
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [ANN] ODB C++ ORM 1.3.0 released, adds support for SQLite
Hi, I am pleased to announce the release of ODB 1.3.0. ODB is an open-source object-relational mapping (ORM) system for C++. It allows you to persist C++ objects to a relational database without having to deal with tables, columns, or SQL and without manually writing any of the mapping code. This release adds support for the SQLite embedded database including the integration of the shared cache mode and unlock notification functionality for multi-threaded applications. A more detailed discussion of the new features can be found in the following blog post: http://www.codesynthesis.com/~boris/blog/2011/04/06/odb-1-3-0-released/ For the complete list of new features in this version see the official release announcement: http://www.codesynthesis.com/pipermail/odb-announcements/2011/03.html ODB is written in portable C++ and you should be able to use it with any modern C++ compiler. In particular, we have tested this release on GNU/Linux (x86/x86-64), Windows (x86/x86-64), Mac OS X, and Solaris (x86/x86-64/SPARC) with GNU g++ 4.2.x-4.5.x, MS Visual C++ 2008 and 2010, and Sun Studio 12. More information, documentation, source code, and pre-compiled binaries are available from: http://www.codesynthesis.com/products/odb/ Enjoy, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with incremental_vacuum and WAL
On Wed, Apr 6, 2011 at 6:36 AM, Dan Kennedy wrote: > On 04/05/2011 04:49 PM, Filip Navara wrote: >> Hello, >> >> we are having problem with database that originated on computer of one >> of our customers. >> >> The database is used in WAL mode with auto_vacuum=1 and page_size=1024. >> >> When running the "pragma incremental_vacuum(1);" command the WAL file >> grows to 14Mb, while we would expect it to grow only to about 4Kb (1Kb >> per page + some overhead). This causes the transaction to run for much >> longer time than expected and eventually the WAL file grows to several >> gigabytes when we try to run incremental_vacuum for 4096 pages. >> >> Additional facts: >> - The database was created with SQLite 3.7.5 running on Windows >> - "pragma integrity_check;" reports ok >> - Some free trunk list pages contain weird values in the "number of >> entries" field >> >> I have attached most of the dumps that I could create with the regular >> SQLite tools (showdb dbheader, sqlite3_analyzer output, beginning of >> the free list dump). I'm willing to send the whole database file >> (~5Gb) to sqlite developers on request. >> >> My questions are: >> - Is is expected behavior that "pragma incremental_vacuum(1);" can >> create 14Mb WAL file even though the page size is just 1Kb? >> - If not, is it a know bug? If yes, in what circumstances? >> - Is the free list corrupted? And if it is, how could this happen and >> should "pragma integrity_check;" verify it? > > Was there a *-wal file in the file-system when you ran the > [showdb] tool? [showdb] just reads the database file, it is > not smart enough to know when it should read pages out of the > wal file instead of the db. So if there is a wal file that > contains content, [showdb] could report corruption. > > Also, can you try with this patch? > > http://www.sqlite.org/src/ci/311d0b613d > > It might help with the 14MB wal files. > > Dan. Hi Dan, there was no -wal file when I ran the showdb tool. I recompiled sqlite from the current 3.7.6 snapshot (sqlite-amalgamation-201104052208.zip) and verified that it contained your patch. Unfortunately it didn't help with the large WAL file for this particular database. Best regards, Filip Navara ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
