RE: [sqlite] Re: File Syste
The public C API is well-documented here: http://www.sqlite.org/capi3.html There may be other documentation of the internals, but I'd imagine the public API will get you pretty far. Pat -Original Message- From: Cesar Rodas [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 13, 2006 6:21 AM To: sqlite-users@sqlite.org Subject: [sqlite] Re: File Syste I mean.. is there a manual that explain how to use SQLite Core in low level API, like berkeley DB, without SQL. Thank to all On 13/12/06, Cesar Rodas <[EMAIL PROTECTED]> wrote: > > Hello to all > > > > I starting a project http://code.google.com/p/gxdfs/ that is a Distributed > File System, following the white paper of GFS(Google File system). If some > one want to contribute please send emails with ideas. > > I would like to know how sqlite works.. is there a manual that explain > every ".c" file? > > -- > Cesar Rodas > http://www.phpclasses.org/grank (A PHP implementation of PageRank) -- Cesar Rodas http://www.phpclasses.org/grank (A PHP implementation of PageRank) - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] windows dll
All static goodness aside, I think the OP's question is still reasonable. The best suggestion I've heard for those who want to version the DLL is to do it themselves, but many feel much more comfortable with a project-approved binary. Perhaps someone who feels strongly enough about a versioned DLL could post a patch to the build and propose having it added to the distribution. Pat -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 11, 2006 12:12 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] windows dll I agree with you 100%. Statically linking Sqlite to applications gives you some freedom from "DLL HELL" and "REV LOCK SHOCK" and can only result in inefficiency if you have multiple different application programs using Sqlite on the individual workstation. To my way of implementing KISS, using an Sqlite DLL makes no sense. Teg wrote: > Hello Art, > > I don't depend on the DLL version because I compile SQLite in. There's > never an issue. Why not generate your own DLL and include the version > information? You can then rename the DLL so, you're not competing with > other versions of the DLL. > > These days, I'd rather have a larger executable that I know works > everywhere and doesn't depend on DLL versioning. > > C > > Sunday, December 10, 2006, 12:31:33 PM, you wrote: > > A> is there a reason that the version information cannot be > A> included in the resource of the windows dll? its not easy to > A> cleanup a system that you might encounter or have to debug aa > A> product installation on if there's multiple versions of sqlite3.dll > A> on a user's system without any version resource information > A> available in the dll. this is standard practice to include this > A> information. > > A> thank you > > > A> Thank you, > > A> Art Zerger > > A> [EMAIL PROTECTED] > > > > > A> - > A> Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates. > > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] about call back of sqlite.
You might also be able to watch the database file itself for changes using a platform specific mechanism. On UNIX, you could use kqueue's and on Windows a combination of FindFirstChangeNotification, FindNextChangeNotification and WaitForMultipleObjects. This would allow you to watch for database changes without polling. Pat -Original Message- From: Roberto [mailto:[EMAIL PROTECTED] Sent: Friday, December 08, 2006 4:23 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] about call back of sqlite. On 08/12/06, hongsion <[EMAIL PROTECTED]> wrote: > Hi sqlite, > I want to using sqlite this way. Application A register callback > to sqlite db. And application B will modify sqlite db. I want each time > sqlite was modified by B, A is notified by callback. Is this possible in > sqlite? A and B run in different process. Thanks! > No it is not possible, you have to implement your own cross process mechanism to notify applications of changes. Another idea DRH brough up in the past, is to poll a table which stores ID's of items that have changed. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] outputting select in cpp file?
Check out the callback parameter to sqlite_exec (the third parameter). The quickstart gives an example of this: http://www.sqlite.org/quickstart.html Alternatively, check out sqlite3_prepare, sqlite3_bind, and sqlite3_step, sqlite3_result*, and sqlite3_finalize. All of these functions are documented here: http://www.sqlite.org/capi3ref.html Pat -Original Message- From: Keiichi McGuire [mailto:[EMAIL PROTECTED] Sent: Friday, July 28, 2006 3:36 PM To: sqlite-users@sqlite.org Subject: [sqlite] outputting select in cpp file? This sounds like a very simple problem, but I cannot figure it out! Basically what I have is this settings table that has a boolean data type, and I want to check it via a cpp program. sqlite_exec(db,"select flag from setting",0,0,&errMsg); and I want to be able to return a 0, or a 1 according to what I put into the flag entry. Thanks! -Keiichi
RE: [sqlite] help with win32/iis install
The error probably comes from this line of code:
$db = sqlite_open("test.db") or die("failed to open/create the
database");
I know nothing about this configuration, but when sqlite_open fails, my
first instinct is a permissions issue. sqlite_open("test.db") attempts
to create or open a file "./test.db" ("./" is relative to the working
directory of the process, whatever that may be). The process needs
appropriate read/write/create permissions to do this.
You can test your permissions by creating another plain file in the same
script. If you don't have create permissions here, you could alter the
permissions for the directory, or give a full or relative path to a
directory in which you do have permissions.
I'd also note that this script does not look threadsafe (a real issue if
you're actually going to have multiple users querying it from web
browsers). There is a race condition that begins with the test for file
existence and ends with the creation of the database table based on that
condition.
Pat
-Original Message-
From: greenshire [mailto:[EMAIL PROTECTED]
Sent: Friday, July 21, 2006 7:55 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] help with win32/iis install
Greetings. I recently installed php 5 on Win 2k3 server (iis 6). I
enabled the pdo and sqlite extensions for sqlite, but using the test
script
from
http://www.tanguay.at/installPhp5.php5?step=8 ,
I get error message: "failed to open/create the database." Any ideas?
Thank you,
--
greenshire
RE: [sqlite] Encoding with spanish characters
It depends (I know, not the answer you had hoped for!) How are your query and parameter strings obtained (command line, compiled static strings, files, gui, other)? What encoding are these input strings? Are you using sqlite3_exec, sqlite3_prepare or sqlite3_prepare16 to execute the query? The key is to identify how the strings are encoded in the first place. Then, you need to decide whether you'll use the sqlite UTF8 or UTF16 functions. Once you have know these, you can figure out how to convert from input to output using platform specific or other conversion code. If you are on a platform that has Unicode strings available to you, you can go straight from those Unicode strings to the sqlite3_*16 functions without conversion. Pat -Original Message- From: Pedro J. Vivancos [mailto:[EMAIL PROTECTED] Sent: Thursday, July 13, 2006 12:19 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Encoding with spanish characters No, I'm no sure. How can I test it? I've just started to learn C/C++ and I have no idea how to do it. Thanks again. Pedro. Will Leshner escribió: > On 7/12/06, Pedro J. Vivancos <[EMAIL PROTECTED]> wrote: > >> I'm developing a program using C++ and SQLite3 as database. I have a >> problem with encoding when I try to execute a SQL query with some >> characters like "ñ, á, é, í, ó, ú". My db is UTF-8 encoded and when I'm >> debugging I can see that the sentence is right but I have no result. >> However, when I try to execute the same sentence by using the SQLite3 >> console I have no problem. > > Are you sure your query string is also encoded as UTF8? > >
RE: [sqlite] sqlite3 on MacOSX
On my mac, sqlite3 -version shows 3.1.3 which is not forward compatible with the default format for 3.3.6 unless you use the PRAGMA: PRAGMA legacy_file_format=TRUE; Before you create the database. To use the old version as the default, you can compile the lib yourself with: -DSQLITE_DEFAULT_FILE_FORMAT=1 Alternatively, you could get the source and build a compatible sqlite client. Pat -Original Message- From: Hartwig Wiesmann [mailto:[EMAIL PROTECTED] Sent: Sunday, July 09, 2006 3:25 PM To: sqlite-users@sqlite.org Subject: [sqlite] sqlite3 on MacOSX Hello! I have created a database (let's name it "Test.db") on the Mac by using the sqlite lib (sqlite version 3.3.6) in my program. Everything works fine. I also wanted to check the database contents using the sqlite command line program. Calling "sqlite3 Test.db" from the terminal and afterwards sending the command ".databases" leads to an error message "unsupported file format". What am I doing wrong? Hartwig
RE: [sqlite] endian-specific code in pure c source release?
This isn't quite what I'm looking for. I understand that resulting binaries are endian dependent, and the database file itself is endian portable after version 3. What I'm really asking (though not as clearly as I had hoped ;-)) is whether or not the sqlite source code is endian dependent. If it is, I assume that configure/make work some "magic" to generate sources for the target endian platform. If the source is endian independent, I expect that I could use the pure c source provided for windows users and let xcode target mactel, ppc or universal during compilation. A quick search through the sqlite source release led me to believe that the source may be coded around endian issues. I guess what I'm looking for is affirmation or denial of my cursory reading. If the source is endian dependent, how does sqlite configure and/or make determine the endian nature of the platform on which it is building? What does it change? If I know these things, I suspect that I can build a project that either runs configure as part of the process, or does the same things that make/configure do to make endian-ness correct. If these aren't documented somewhere, I can reverse engineer configure/make, but I was hoping that someone here might have the answers. Thanks again! Pat -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, July 03, 2006 10:04 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] endian-specific code in pure c source release? Sqlite data is endian agnostic, but the executables are, like any executables, dependent upon the endian nature of the host processor. Just compile the Sqlite library for each platform and share the data. On platforms other than Windows use configure, otherwise use the prepared windows source. If you make any extensions to Sqlite, such as your own functions, they will be platform independent. By using the regular Sqlite source distribution you will be able to upgrade easily, and not have your application rev-locked. Pat Wibbeler wrote: > For a couple of reasons: > * I'd like to use xcode to build a universal binary. If I run > ./configure, I imagine that any endian specific code that is fixed using > configure will be set to whatever platform I run configure on (i386 or > ppc). > * I already have the packaged source for a windows build using visual > studio and I'd like to use the same sources if possible to avoid > confusion. > > Pat > > > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Monday, July 03, 2006 9:24 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] endian-specific code in pure c source release? > > Why not use the regular source and run configure? > > Pat Wibbeler wrote: > >>I'd like to build an xcode project for sqlite. One straightforward >>approach is to take the sqlite-source-3_3_6.zip "pure c" source > > release > >>and build the xcode project from that. >> >>Is there any endian specific code in that source release that might > > trip > >>me up on power pc processors? I ask this because I know that this >>release is "provided as a service to MS-Windows users who lack the > > build > >>support infrastructure of Unix." >> >>Thanks! >> >>Pat > >
RE: [sqlite] endian-specific code in pure c source release?
For a couple of reasons: * I'd like to use xcode to build a universal binary. If I run ./configure, I imagine that any endian specific code that is fixed using configure will be set to whatever platform I run configure on (i386 or ppc). * I already have the packaged source for a windows build using visual studio and I'd like to use the same sources if possible to avoid confusion. Pat -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, July 03, 2006 9:24 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] endian-specific code in pure c source release? Why not use the regular source and run configure? Pat Wibbeler wrote: > I'd like to build an xcode project for sqlite. One straightforward > approach is to take the sqlite-source-3_3_6.zip "pure c" source release > and build the xcode project from that. > > Is there any endian specific code in that source release that might trip > me up on power pc processors? I ask this because I know that this > release is "provided as a service to MS-Windows users who lack the build > support infrastructure of Unix." > > Thanks! > > Pat
RE: [sqlite] default value in hex
Are you performing computations with that number? For example, will you be performing addition, subtraction, or bitwise and/or? If not, you could just leave it as a string (and likely change the create to be "... text(32) default '0xFF'"). I'm not an expert on the topic, but sqlite uses "manifest typing" meaning that it doesn't enforce the type of a given column. This allows you to put whatever you want, wherever you want. SQLite will try to "coerce" the data into the type you've specified. So, when it sees xFF, it decides that you really want a string and makes it so. When it sees 0xFF it likely coerces that into the integer type specified in the create. When storing the number as an integer, it's stored as binary. The fact that you see it in base 10 when you select it is an artifact of whatever you are using to display the number. The sqlite command line utility displays integers in base 10 (for good reason, most of the time, you expect this!). I had a quick look at the sqlite built-in sql functions to see if there is a function that could convert an integer to a hex string (like TO_CHAR() in oracle, or STR() in MSSQL), but it looks like there is not. SQLite is intentinonally sparse in the included functions, so you can either write or find a custom sqlite function to do this. Alternatively, simply use whatever number->string conversion routines the language/platform you are using supplies (e.g. printf for c). Pat -Original Message- From: Wilfried Mestdagh [mailto:[EMAIL PROTECTED] Sent: Sunday, July 02, 2006 11:16 PM To: sqlite-users@sqlite.org Subject: [sqlite] default value in hex Hi, How to specify a hexadecimal value in the default by create of a table ? I try thinks like: create table Test ( [Name] char, [Color] integer default 0xFF ); But have syntax errors. When I leave out the 0 then the xFF seems to be stored as a string. it is just that 0xFF makes more sence that 16777215, specially if it is a color. -- View this message in context: http://www.nabble.com/default-value-in-hex-tf1882855.html#a5147011 Sent from the SQLite forum at Nabble.com.
[sqlite] endian-specific code in pure c source release?
I'd like to build an xcode project for sqlite. One straightforward approach is to take the sqlite-source-3_3_6.zip "pure c" source release and build the xcode project from that. Is there any endian specific code in that source release that might trip me up on power pc processors? I ask this because I know that this release is "provided as a service to MS-Windows users who lack the build support infrastructure of Unix." Thanks! Pat
RE: [sqlite] real time gui updates
Another alternative might be using an API that waits for events on the database file - for instance kqueues some unix variants or WaitForMultipleObjects and FindFirstChangeNotification/FindNextChangeNotification on windows. I agree that polling causes issues. It doesn't require much CPU if you use the right query, but it may access the disk, and it may interfere with OS power management. Pat -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 6:50 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] real time gui updates In that case you might use some form of inter-process communication like a named pipe or a semaphore which is activated by a change to the DB. The viewing process would have a thread waiting on the IPC channel or semaphore. When the DB changed it would automatically trigger a refresh in the viewer process/processes. If you were ambitious you could write an Sqlite function to perform the synchronization and drive it from a trigger. That would make the logic simpler in your application. Rob Menegon wrote: > Not sure whether I understand how this would occur. > > The application is not doing or responsible for the updates to the database. > Its only function in life is to retrieve and display data. Updates, > modifications occur via another application, so I was incorrect in my > previous response to you - one user (app) doing updates and another > displaying data - independent processes. > > > > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Thursday, 29 June 2006 2:51 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] real time gui updates > > In that case your application knows whenever the database is changed and can > call a refresh routine. > > Rob Menegon wrote: > >>No a single user/instance of the application reading from the database. >> >> >> >>-Original Message- >>From: John Stanton [mailto:[EMAIL PROTECTED] >>Sent: Wednesday, 28 June 2006 10:45 PM >>To: sqlite-users@sqlite.org >>Subject: Re: [sqlite] real time gui updates >> >>Rob Menegon wrote: >> >> >>>I have an application that displays data retrieved from various SQLite >>>tables. As the data within the tables changes I want these changes >>>reflected in the application real time. >>> >>>Can someone advise on the best way of doing this? >>> >>>Rob Menegon >>> >>> >> >>Do you have multiple independent users? >> > > >
RE: [sqlite] Problems with multiple threads?
No problem. In fact, I had to consult the docs again to be sure! I've sorted through these several times myself. They are quite good, though I have to admit that I'm constantly having to consult them to remember how the locking works. I do agree that a BEGIN SHARED would be nice for cases where you'd like consistent reads across multiple statements without using a BEGIN IMMEDIATE and locking other threads doing the same out. Pat -Original Message- From: A. Pagaltzis [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 7:01 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problems with multiple threads? * Pat Wibbeler <[EMAIL PROTECTED]> [2006-06-07 22:55]: > It's entirely possible I'm reading these docs incorrectly, but > this strategy has worked quite well for me. No, I don't see any error in your reading. My apologies; I should have consulted the docs instead of going by mailing list posts. It's interesting that there's no way to force a SHARED lock to be obtained immediately. The available mechanisms allow serialising write operations with respect to each other, but not forcing a well-defined sequence of read operations relative to write operations. Regards, -- Aristotle Pagaltzis // <http://plasmasturm.org/>
RE: [sqlite] Problems with multiple threads?
Reading the BEGIN docs here: http://www.sqlite.org/lang_transaction.html I saw "After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE". This is because "If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed". To me, this means that if all threads do BEGIN IMMEDIATE, it will prevent other threads from doing BEGIN IMMEDIATE, thus serializing all transactions. It also says that "The first read operation against a database creates a SHARED lock". I took this to mean that a BEGIN (meaning deferred) transaction would only escalate to SHARED on the first select, where a BEGIN IMMEDIATE would escalate to RESERVED. So, for transactions where a write occurs (usually including a read), I do a BEGIN IMMEDIATE. For transactions where I know that I'm only reading (select), I simply do BEGIN and let the database escalate to SHARED. It's entirely possible I'm reading these docs incorrectly, but this strategy has worked quite well for me. Pat -Original Message- From: A. Pagaltzis [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 1:27 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problems with multiple threads? * Pat Wibbeler <[EMAIL PROTECTED]> [2006-06-07 20:50]: > Beginning everything with BEGIN IMMEDIATE should eliminate the > possibility of deadlock, but you will serialize read-only > operations. Why? BEGIN IMMEDIATE acquires a for-read lock. Multiple for-read locks can be acquired concurrently. It is only for-write locks that can only be acquired in the absence of any other locks, which leads to serialisation. Putting all your read operations in BEGIN IMMEDIATE means that all your write operations will be serialised in relation to all other operations taking place, but read operations can proceed apace. Of course, if your writes are short and frequent, they will likely take much longer than necessary if all your operations acquire read locks before they *really* need them. Regards, -- Aristotle Pagaltzis // <http://plasmasturm.org/>
RE: [sqlite] Problems with multiple threads?
Beginning everything with BEGIN IMMEDIATE should eliminate the possibility of deadlock, but you will serialize read-only operations. If your transactions are short or contention is low, using BEGIN IMMEDIATE makes things easy. However, if you find that you have a set of read-only operations that run frequently, or take a long time, you may want to consider simply using BEGIN on these (or for single statement reads, you could leave the transaction wrapping out altogether). Pat -Original Message- From: Jiri Hajek [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 11:33 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Problems with multiple threads? > If it is inconvenient to rollback and retry the entire transaction, then start the transaction initially with BEGIN EXCLUSIVE. > This will acquire the reserved lock immediately (instead of waiting to the first write occurs) and so you will either get an > SQLITE_BUSY right away (when it is a simple matter to just rerun the BEGIN EXCLUSIVE statement until it works) or you can be > assured of never getting another SQLITE_BUSY again until you try to COMMIT (and there too, you can simply rerun COMMIT > repeatedly until it works.) Thanks, I overlooked that by default transactions are DEFERRED in SQLite. It really fixes the problem. As I think about it, if I make _all_ transactions in my application IMMEDIATE, there shouldn't be any risk of a deadlock, right? Thanks, Jiri
RE: [sqlite] Problems with multiple threads?
Do you have any transactions that look like: BEGIN SELECT INSERT/DELETE/UPDATE COMMIT If you do, you may have multiple threads trying to escalate from a SHARED to a RESERVED lock as described here: http://sqlite.org/capi3ref.html#sqlite3_busy_handler It's important that if you have multithreaded access and a strategy that involves either retrying or waiting on SQLITE_BUSY that you specify at the beginning of write transactions that the transaction requires a higher level lock. I do this using "BEGIN IMMEDIATE". The thread currently on this list with subject "[sqlite] BEGIN and Backup [was [sqlite] Problems with multiple threads?]" Discusses a similar issue. Pat -Original Message- From: Jiri Hajek [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 9:26 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Problems with multiple threads? Thanks for an additional explanation, I used sqlite3_get_autocommit() for debugging and it helped me to find out that it really was my fault. There was an incorrect processing after COMMIT returned SQLITE_BUSY. So sorry for this. However, right after fixing this, I found another problem. It certainly can be my fault, but I don't see how could it be: If I don't use transactions, multiple threads seem to proceed well, but then right after I add BEGIN and COMMIT to some place, all threads lock eventually. I debugged it and found that _all_ threads accessing SQLite are in a loop waiting for an action to proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be somehow my fault, or is it some kind of a dead-lock in SQLite? In the simpliest form it takes only two threads to reproduce and the problem looks like: Thread 1: BEGIN TRANSACTION<-- proceeded INSERT INTO ... <-- Processing stops here, waiting in a loop, Sqlite3_step() infinitely returns SQLITE_BUSY COMMIT Thread 2: // no explicit transaction start here DELETE FROM ... <-- Processing stops here, waiting in a loop, Sqlite3_step() infinitely returns SQLITE_BUSY Thread 2 can also look like this in order to reproduce the problem (situation in Thread 1 remains the same): BEGIN TRANSACTION<-- proceeded DELETE FROM ... <-- proceeded COMMIT <-- Processing stops here, waiting in a loop, Sqlite3_step() infinitely returns SQLITE_BUSY No other thread calls any SQLite function. Do you have any idea what could be wrong? Thanks, Jiri
RE: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with multiple threads?]
You can use BEGIN IMMEDIATE or BEGIN EXCLUSIVE depending on the type of lock you'd like. SQLite BEGIN syntax: http://sqlite.org/lang_transaction.html SQLite locks: http://sqlite.org/lockingv3.html SQLite Busy Handler: http://sqlite.org/capi3ref.html#sqlite3_busy_handler Pat -Original Message- From: Russell Leighton [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 8:24 AM To: sqlite-users@sqlite.org Subject: [sqlite] BEGIN and Backup [was Re: [sqlite] Problems with multiple threads?] So, this was very enlightening...I have a simple backup function that I now question is correct. It does: - execute "begin" // lock from writes -copy db file to new file byte by byte - execute "commit" // unlock ...I was thinking that "begin" would lock the file. If I use an flock() [or should it be lockf() ?? ] to bracket the file copy will I be safe? I was hoping to have something more efficient than a table by table copy for backup. Thx [EMAIL PROTECTED] wrote: >As various people search for application and/or SQLite bugs >related to multiple threads and BEGIN, let me try to aid the >effort by better describing exactly what BEGIN does and >suggesting some debugging tricks. > >Realize that BEGIN does not actually create any file locks >or check to see if any file locks already exist, nor >interact in any other way with the filesystem. File locks >are only created by SELECT, UPDATE, INSERT, and DELETE >statements. (OK, also CREATE and DROP statements, but >let's ignore those for now for simplicity. Presumably >the schema is fixed at the point where the problems are >occuring.) All BEGIN does is to set a flag that says >"do not automatically perform a COMMIT after each write >to the database". This is the autoCommit flag that I >mentioned in a prior email. autoCommit is a boolean >member of the sqlite3 structure. A lock is acquired >at the beginning of each UPDATE, INSERT, or DELETE >if it does not already exists. After each UPDATE, >INSERT, or DELETE, sqlite checks the value of the >autoCommit flag, and if it is true it automatically >does a COMMIT. A read-lock is acquired before each >SELECT if it does not already exists, and after the >SELECT is done, the read-lock is dropped if autoCommit >is true. > >So the BEGIN instruction does not do anything with the >filesystem. It does not interact in any way with the >operating system or with other database connections. >All BEGIN does is clear the autoCommit flag. So it is >hard to imagine how having other threads could possibly >effect its behavior. > >At any time, you can determine the value of the autoCommit >flag using the sqlite3_get_autocommit() API. See > > http://www.sqlite.org/capi3ref.html#sqlite3_get_autocommit > >I suggest that people who are getting back unexpected >"cannot start a transaction within a transaction" errors >should use the sqlite3_get_autocommit() API in some printf()s >to trace the status of the autocommit flag within their >application. Prior to running BEGIN, it should always >be the case that sqlite3_get_autocommit() returns TRUE. >If sqlite3_get_autocommit() returns FALSE, then the BEGIN >that follows will give the "cannot start..." error. > >I suspect what is happening is that some prior COMMIT >or ROLLBACK is not setting the autoCommit flag back to >TRUE. This might be because the COMMIT or ROLLBACK >failed. Or, there could be some kind of bug in SQLite >that is causing the autoCommit flag to not be set >correctly. I suspect the former, but am open to evidence >pointing to the latter. It might be useful to use the >sqlite3_get_autocommit() function to print out the value >of the autoCommit flag after each COMMIT and ROLLBACK is >executed. This might help to isolate the problem. > >-- >D. Richard Hipp <[EMAIL PROTECTED]> > > >
RE: [sqlite] Problems with multiple threads?
One means of troubleshooting this is to emit a log statement that
includes the thread id with every BEGIN/COMMIT (e.g. printf("%d - %s",
thread, sql)). It may be useful to log other sql statements this way as
well.
This sort of troubleshooting has always shown the mistake to be mine,
not SQLite when I see this issue.
Pat
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 06, 2006 6:36 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problems with multiple threads?
Bill KING <[EMAIL PROTECTED]> wrote:
> Could this be cause by one thread opening a transaction, then a second
> on a second connection trying to open a transaction on it, and failing
> to open the transaction file (as it already exists?).
>
No.
Each database connection (each sqlite3* pointer) has a boolean
field called "autoCommit". If autoCommit is false, that means
a transaction is active. If autoCommit is true, that means you
are not inside a transaction. autoCommit is true by default,
of course.
Executing BEGIN does not do anything with the disk. All it does
is change the autoCommit flag from true to false. If the flag
was already false at the time BEGIN is executed, it generates
the "cannot start a transaction within a transaction" error.
The important thing to note here is that BEGIN never does
any system calls - it never interacts with the database file
in any way. All it does is set what should be a private boolean
variable to false.
Now, if you do BEGIN EXCLUSIVE or some other variation on BEGIN,
that is a different matter. But you said you just did a simple
BEGIN. And in that case, the nothing visible to other database
connections ever gets touched.
This leads me to conjecture that you have multiple threads
messing with the same database connection
--
D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Memory DB: Load from file
This sounded fun, so I thought I'd give it a try. Here's a sample
pulling schema and data from an on-disk to an in-memory database in c.
I've omitted error handling and debug output to make it shorter.
int process_ddl_row(void * pData, int nColumns,
char **values, char **columns);
int process_dml_row(void *pData, int nColumns,
char **values, char **columns);
void test()
{
sqlite3* memorydb;
sqlite3* budb;
sqlite3_open(":memory:", &memorydb);
// Looks for backup.db in pwd. For testing, you may want to
// initialize the database to a known state.
sqlite3_open("backup.db", &budb);
// Create the in-memory schema from the backup
sqlite3_exec(budb, "BEGIN", NULL, NULL, NULL);
sqlite3_exec(budb, "SELECT sql FROM sqlite_master WHERE sql NOT
NULL",
&process_ddl_row, memorydb, NULL);
sqlite3_exec(budb, "COMMIT", NULL, NULL, NULL);
sqlite3_close(budb);
// Attach the backup to the in memory
sqlite3_exec(memorydb, "ATTACH DATABASE 'backup.db' as backup",
NULL, NULL, NULL);
// Copy the data from the backup to the in memory
sqlite3_exec(memorydb, "BEGIN", NULL, NULL, NULL);
sqlite3_exec(memorydb,
"SELECT name FROM backup.sqlite_master WHERE
type='table'",
&process_dml_row, memorydb, NULL);
sqlite3_exec(memorydb, "COMMIT", NULL, NULL, NULL);
sqlite3_exec(memorydb, "DETACH DATABASE backup", NULL, NULL,
NULL);
sqlite3_close(memorydb);
}
/**
* Exec an sql statement in values[0] against
* the database in pData.
*/
int process_ddl_row(void * pData, int nColumns,
char **values, char **columns)
{
if (nColumns != 1)
return 1; // Error
sqlite3* db = (sqlite3*)pData;
sqlite3_exec(db, values[0], NULL, NULL, NULL);
return 0;
}
/**
* Insert from a table named by backup.{values[0]}
* into main.{values[0]} in database pData.
*/
int process_dml_row(void *pData, int nColumns,
char **values, char **columns)
{
if (nColumns != 1)
return 1; // Error
sqlite3* db = (sqlite3*)pData;
char *stmt = sqlite3_mprintf("insert into main.%q "
"select * from backup.%q", values[0], values[0]);
sqlite3_exec(db, stmt, NULL, NULL, NULL);
sqlite3_free(stmt);
return 0;
}
-Original Message-
From: Dave Gierok [mailto:[EMAIL PROTECTED]
Sent: Monday, June 05, 2006 5:45 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Memory DB: Load from file
Thank you for the help Andrew and D. Richard Hipp. But how would I do
this (page 24 & 25 of the slides) using the C/C++ interface?
{
sqlite3* pFileDB;
sqlite3* pMemoryDB;
sqlite3_open(fileName, &pFileDB);
sqlite3_open(":memory:", &pMemoryDB);
//
//WHAT DO I DO NOW?
//
}
Thanks,
Dave Gierok
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, June 05, 2006 12:49 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory DB: Load from file
Andrew Piskorski <[EMAIL PROTECTED]> wrote:
> On Mon, Jun 05, 2006 at 12:05:52PM -0700, Dave Gierok wrote:
>
> > Is there a way to load a Sqlite file based DB and then specify we
want
> > that to go into memory? Or is there a more efficient way to create
our
> > memory DB?
>
> You could use attach to copy from an on-disk SQLite database:
>
> http://www.sqlite.org/google-talk-slides/page-024.html
> http://www.sqlite.org/google-talk-slides/page-025.html
>
Andrews suggestion above is better than the sqlite3_raw_write()
routine of my previous post because Andrew's idea is portable.
Use this idea if it is fast enough for you. The raw_write()
thing will be a little bit faster, but as I said, it is subject
to break without warning.
--
D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Multithreading. Again.
I don't think it's that uncommon to ask the user of the statement to finalize database resources explicitly, even in a managed environment. For example, Java collects memory, but the programmer must explicitly close network, file, database, and other resources. In java, I do this in a finally block. In c++, I do it using a stack based destructor guard. Pat -Original Message- From: Florian Weimer [mailto:[EMAIL PROTECTED] Sent: Friday, June 02, 2006 9:19 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multithreading. Again. > As long as no prepared statements are outstanding, you should > be safe moving sqlite database connections across threads, as > of version 3.3.1. The rules are not really quite that strict, > but the exact rules are more complex and this strict rule > gives you an extra margin of safety. Is it possible to finalize statements in a separate thread? This would be a rather important feature because on most multi-threaded VMs, user-defined finalizers (which would be used to clean up SQLite objects which are no longer used) run in a separate thread.
RE: [sqlite] Multithreading. Again.
I was reading sqlite3_open documentation earlier this week and noticed that the docs say: "The returned sqlite3* can only be used in the same thread in which it was created. It is an error to call sqlite3_open() in one thread then pass the resulting database handle off to another thread to use. This restriction is due to goofy design decisions (bugs?) in the way some threading implementations interact with file locks." http://www.sqlite.org/capi3ref.html#sqlite3_open I was surprised to see this because I was under the impression that this issue had been fixed as you mentioned below. I'm glad to hear that it really is fixed. Is this a "documentation bug" leftover from previous versions? Thanks! Pat -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 01, 2006 8:38 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: Re: [sqlite] Multithreading. Again. "Peter Cunderlik" <[EMAIL PROTECTED]> wrote: > Hello everybody, > > I'd like to ask a couple of silly questions before newcomers like me > get moderated. :-) I've browsed through the documentation and this > mailing list, trying to understand issues with multithreading. I'd > like if someone could confirm my conclusions. > > 1. As of SQLite 3.3.5, there is no multithreading (MT) problem with > the SQLite itself. All problems come from the underlying OS libraries. I would argue that this has always been the case. But beginning in version 3.3.1, SQLite has taken additional steps to partially work around problems in the OS. So the OS problems are less troublesome. > > 2. The only MT problem is with locking the database in a case when a > thread uses sqlite3 structure created by a different thread. Thus, > using connection pool (allocated by one thread, used by other threads) > is not safe. This was the case prior to version 3.3.1. Beginning with 3.3.1 and following, you can move an sqlite3 structure from one thread to another as long as there are no locks being held by that structure. The easiest way to make sure that no locks are held is to finalize all statements associated with the sqlite3 structure. > > 3. Just a thought: In case of in-memory database, no file locking is > required, so MT is no issue. (?) Correct. Of course, it has always been the case and probably always will be that you cannot use the same sqlite3 connection in two or more threads at the same time. You can use different sqlite3 connections at the same time in different threads, or you can move the same sqlite3 connection across threads (subject to the constraints above) but never, never try to use the same connection simultaneously in two or more threads. Often you will get an SQLITE_MISUSE error if you try to use the same sqlite3 connection in two threads at the same time, but there are race conditions which can cause this error check to be missed, so do not count on it. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] sqlite3_exec returning SQLITE_CANTOPEN
I'm periodically seeing sqlite3_exec return SQLITE_CANTOPEN. Unfortunately, I haven't boiled this down to a simple sample that I can send to this list, but I thought I'd check to see if there is a known solution before I dive too deep. I'm using sqlite version 3.2.7 on windows. Thanks! Pat The Details --- The sql statement that causes this always seems to be: "BEGIN IMMEDIATE" When I inspect my threads in the debugger, there is generally another transaction closing the database after a "COMMIT", and a third doing "BEGIN IMMEDIATE" concurrently. One peculiarity of my system is that I open and close a new "connection" to the database for each transaction. I also use a busy_handler that returns 1. I have debug log statement in my busy handler and I usually see it called just before this problem occurs. The threads look like this (snipped to sqlite calls): THREAD A - The thread from which the SQLITE_CANTOPEN is returned. The windows GetLastError appears to be "Access is denied" in an attempt to open the journal file. I've paused this stack at the line where the SQLITE_CANTOPEN is returned - line 286 of os_win.c App.exe!sqlite3OsOpenExclusive(const char * zFilename=0x02425b6a, OsFile * id=0x02423a58, int delFlag=0) Line 286 App.exe!pager_open_journal(Pager * pPager=0x024239f8) Line 2654 App.exe!sqlite3pager_begin(void * pData=0x024276f8, int exFlag=0) Line 2748 App.exe!sqlite3BtreeBeginTrans(Btree * pBt=0x02421730, int wrflag=1) Line 1638 App.exe!sqlite3VdbeExec(Vdbe * p=0x0240ccb8) Line 2337 App.exe!sqlite3_step(sqlite3_stmt * pStmt=0x0240ccb8) Line 217 App.exe!sqlite3_exec(sqlite3 * db=0x02427240, const char * zSql=0x0268c7e4, int (void *, int, char * *, char * *)* xCallback=0x, void * pArg=0x, char * * pzErrMsg=0x0268c468) Line 79 THREAD B, also doing a "BEGIN IMMEDIATE" App.exe!sqlite3OsFileExists(const char * zFilename=0x0241bb72) Line 148App.exe!hasHotJournal(Pager * pPager=0x02419a00) Line 2270 App.exe!sqlite3pager_get(Pager * pPager=0x02419a00, unsigned int pgno=1, void * * ppPage=0x0278b90c) Line 2336 App.exe!getPage(Btree * pBt=0x0241f7c0, unsigned int pgno=1, MemPage * * ppPage=0x0278ba10) Line 1125 App.exe!lockBtree(Btree * pBt=0x0241f7c0) Line 1445 App.exe!sqlite3BtreeBeginTrans(Btree * pBt=0x0241f7c0, int wrflag=1) Line 1634 App.exe!sqlite3VdbeExec(Vdbe * p=0x02422d58) Line 2337 App.exe!sqlite3_step(sqlite3_stmt * pStmt=0x02422d58) Line 217 App.exe!sqlite3_exec(sqlite3 * db=0x0241f970, const char * zSql=0x0278d02c, int (void *, int, char * *, char * *)* xCallback=0x, void * pArg=0x, char * * pzErrMsg=0x0278ccb0) Line 79 THREAD C, calling close after finishing up a transaction: App.exe!sqlite3OsClose(OsFile * id=0x0240a770) Line 416 App.exe!sqlite3pager_close(Pager * pPager=0x0240a718) Line 2011 App.exe!sqlite3BtreeClose(Btree * pBt=0x0243e800) Line 1299 App.exe!sqlite3_close(sqlite3 * db=0x0240d088) Line 159
[sqlite] means for explicitly escalating a transaction from RESERVED to PENDING/EXCLUSIVE
Is there a means for explicitly escalating an existing transaction from RESERVED to EXCLUSIVE (either through issuing sql statements or the C API)? I'm using the following locking strategy: * Read only transactions start with a BEGIN and allow sqlite to escalate to SHARED on read. * Transactions that may (or are known to) write use BEGIN IMMEDIATE to avoid the potential lock escalation issue that might occur with two simultaneous SHARED transactions that each attempt to escalate to RESERVED by issuing a read followed by a write. * Install a busy handler that directs sqlite to continue trying to obtain the lock. Unfortunately, I also have some transactions that I'd like to be EXCLUSIVE. In some cases, I know when beginning the transaction that these should be EXCLUSIVE, and in some cases, I don't know until I'm within the transaction that I'd like to be EXCLUSIVE. Even in cases where I know I'll eventually be exclusive, I'd like to delay exclusivity as long as possible, allowing read operations to continue. Wwhat I'd like to do is begin this transaction BEGIN IMMEDIATE, allowing SHARED locks to be acquired and the database read. At some point within the transaction, I'd like to force an escalation to EXCLUSIVE, forcing all SHARED and other locks to clear before continuing. Is there a way to do this? Thanks! Pat
