[sqlite] "INSERT" and "END TRANSACTION" time
On a recent thread, some of you told me that if I wrap lots of "INSERT" statements inside "BEGIN TRANSACTION" and "END TRANSACTION", then executing "END TRANSACTION" will take the most (99%) of the time. This is not happening. _bind() on a prepared statement and execution of "INSERT" are taking 70% of the time, but the "END TRANSACTION" only 30% of the time. The time between _bind() and execution of "INSERT" is roughly 50/50, so it's a total of: _bind(): 35% INSERT: 35% END TRANSACTION: 30% I would have expected the execution of INSERT to taken almost 0% within a transaction. Why is this not the case? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transactions
Is this legal? 1) Create "INSERT" prepared statement with parameters. 2) Create a second non-parameter "BEGIN TRANSACTION" prepared statement, execute it with _step(), and finalize it. 3) Attach values to the parameters of the first "INSERT" prepared statement, _step() it, _reset() it. Repeat 3) many times. 4) Create a third non-parameter "END TRANSACTION" prepared statement, execute it with _step(), and finalize it. 5) Finalize the original "INSERT" prepared statement. Could these prepared statements conflict with each other? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why SQLITE_FLOAT instead of SQLITE_REAL?
SQLite has the REAL data type: https://www.sqlite.org/datatype3.html Then why do we have SQLITE_FLOAT instead of SQLITE_REAL? All the other data types (INTEGER, BLOB, TEXT, NULL) match with the SQLITE_ constants. Is this just a historical quirk that stuck, or something else? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recommended way to delete rows
> I expect #2 to work best. Make sure to enclose the whole thing in an > explicit transaction (or at least, run large batches within explicit > transactions; one implicit transaction per deleted row will be slow as > molasses). If I do this, would you expect _step() for the "BEGIN TRANSACTION" query and _step() for each "DELETE" query to be very fast, but the _step() for the "END TRANSACTION" query to take most (99%) of the time? Would you expect a similar speed boost for "INSERT"? Is one by one "INSERT" in a similar way slow as molasses, and wrapping many inserts in a transaction recommended? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Recommended way to delete rows
If I already have a collection of row IDs of rows I wish to delete from a table, what is a recommended/fast way to delete them from the table? The collection is just a set/array of integers, not necessarily contiguous. I can think of three ways: * Prepare query containing single ID with _prepare(). Execute it with _step(). Repeat for each ID. * Call _prepare() with a query with single parameter for the ID. _bind(), _step(), and _reset() - repeat for each ID. * Call _prepare() with a query containing every single one of the IDs. Then _step() on it once. Done. Is this even possible? Since there can be a million IDs, I'm not sure if the query can be so long. Which way do you recommend? Are there other ways? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concrete example of corruption
> D. Richard Hipp wrote: > Please try the changes in the branch at > http://www.sqlite.org/src/info/8759a8e4d8 and > let me know if they adequately cover your concerns. I don't have enough expertise with the SQLite codebase to be able to tell for sure. You will always know better than me whether these changes fully address your (1)-(5) corruption scenario. If they do, maybe you can add them to the next SQLite version? You have outlined one corruption scenario in your (1)-(5) steps. I trust there are no other corruption scenarios possible as a result of a simple document (package folder) move? > Yuriy Kaminskiy wrote: > Let's suppose user just did... I don't expect SQLite to guard against a user who is hell-bent on messing things up. My case wasn't about messing anything up, but simply moving a document. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concrete example of corruption
> Simon Slavin wrote: > If you can't trust your users not to > move data files out of place I was not talking about data files but regular documents (package folders). Moving regular documents has nothing to do with trust. It's a fact of everyday reality. > Stephen Chrzanowski wrote: > Even with MySQL, if you start messing around with directory > structures, file permissions, or whatever, you're GOING to > kill the software. Moving regular documents is not "messing around" with anything. > William Garrison wrote: > It's your job to code for this possibility. Use the > rest of the system to help you do that. > Simon Slavin wrote: > My conclusion is that those programmers who want this > can do it inside their app, outside the code which handles > the database itself. > Stephen Chrzanowski wrote: > File handling is NOT SQLites responsibility, but your > applications responsibility in knowing how the OS > handles itself. I can think of two ways to "achieve" this (neither actually works): * Before every single SQLite C function call that accesses the database, check whether the document has moved. This is horrible for two reasons: (1) It will slow down the database operations. (2) The document could still be moved *between* our "move check" and the database operation. * Using fsevents etc. to monitor the document move, as someone suggested. This will not work because lengthy database operations occur on a separate thread, so the database can get corrupted *before* we actually handle the document move. And even if fsevents and database operations are handled in the same thread, there is no guarantee that the system sends my program the event before the database gets corrupted. Summary: AFAIK, there is nothing my program can do to handle this. So IMO, this is a problem that SQLite should cope with. At most, it should give us errors to deal with - not corrupt the database. If you disagree, why? If you agree: Let's go back to D. Richard Hipp's original (1)-(5) steps of corruption. Could there be a way for a future version of SQLite to avoid that corruption? I asked whether opening the journal file only once was a potential solution to this. I'm not sure it is. If not, why not, and do you have any other ideas? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concrete example of corruption
> Simon wrote: > > Doesn't help. Because the corruption can happen > if the journal file is moved after the app has crashed. > And if the app has crashed it doesn't have any of the files > open and can't monitor them being moved. D. Richard Hipp's scenario was not about "our" program crashing. It was about the "other person's" program crashing, leaving a journal file behind that "our" program cannot find because it tries to look for the journal file at the old path, causing corruption. Hence, my question about opening the journal file only once. > RSmith wrote: > > Your previous post simply asked for changes > without showing any cause to do so. I can clarify: My folder /Users/lwood/Desktop/folder.ext is a *document* (I have added ".ext" to the name now). My program has a document format of such folder packages. The database file is inside this package. A fact of reality: Documents can be moved by the program's users. The database should not be corruptible in this case. At most, I should get errors from SQLite that I can handle gracefully. This is a normal thing. We are simply driving our car, or at most not stopping completely at a stop sign - not driving off a cliff. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concrete example of corruption
> Simon Slavin wrote: > Could you be clear on what issue it is that you want > solved, and how your proposal solves it any better than > what is currently being done ? We are trying to find ways to avoid the corruption problem that D. Richard Hipp outlined. See his steps (1)-(5) in a previous post. > When writing that, don't forget that Unix, the underlying > OS used for the Mac operating system, allows a file to be > moved/renamed/deleted even while an application has it open. Yes, and that is exactly why I asked what I asked, namely whether a mode that opened the journal file only once is a possibility or not. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concrete example of corruption
> Metadata is not cross-platform. That would only work on a mac. Here is a cross-platform idea: Add a new mode where the journal is opened once for reading/writing when first needed and left open until the database is closed (as opposed to the default way, to repeatedly create/delete the journal file). This way, the file could moved without any problems. D. Richard Hipp, is this a possibility for solving this issue or yet another impossibility? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concrete example of corruption
> Not possible. Doing any writes to the database file (such as to set a > flag) requires a journal file, otherwise a power loss in the middle of the > write can cause database corruption. It doesn't matter at all whether the flag is a regular write to the actual file or merely metadata on the file (as I suggested)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concrete example of corruption
This makes sense now, thanks. D. Richard Hipp, are there any plans to make this more robust in the future, so reading/writing a corrupt database (with no -journal file available) will return an *error* instead of causing further damage? One idea: A "flag" could be added to the main database file to indicate that it has a corresponding -journal file. If it has the flag but no -journal file exists, that would be an error. To add the flag, Mac OS X has extended attributes. Not necessarily available on all filesystems, but a nice protection against corruption where available. What do you think? Possible? Impossible? > From: d...@sqlite.org > Date: Sat, 30 Nov 2013 17:30:01 -0500 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Concrete example of corruption > > On Sat, Nov 30, 2013 at 5:15 PM, L. Wood wrote: > >> Thanks, that makes sense. >> >> But I can pretty much rule this case out. In my case, it's highly unlikely >> that the folder would be moved during the brief instant between _open() and >> the actual opening of the "-journal" file. >> >> Are there other possible cases of corruption? >> > > > > (1) Process A opens database at /first/path/to/database.db > (2) The directory is renamed to /second/path/to/database.db > (3) Process B begins writing to the database, creating a rollback journal > at /second/path/to/database.db-journal > (4) Process B crashes without committing or rolling back. > (5) Process A tries to read the database. It checks for a hot journal > file at /first/path/to/database.db-journal, finds none (since the hot > journal is now in a different place) and therefore does not know that it > needs to recover the database file. It sees the database as being corrupt. > Or if A tries to write to the database, it makes the corruption worse, such > that even a third process that does see the hot journal is no longer able > to recover. > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Concrete example of corruption
Thanks, that makes sense. But I can pretty much rule this case out. In my case, it's highly unlikely that the folder would be moved during the brief instant between _open() and the actual opening of the "-journal" file. Are there other possible cases of corruption? > Date: Sat, 30 Nov 2013 22:26:21 +0100 > From: clem...@ladisch.de > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Concrete example of corruption > > L. Wood wrote: >> /Users/lwood/Desktop/folder/db.sqlite >> >> Suppose I can only move/rename the *folder*. Suppose I never mess with the >> folder's contents. >> >> Can you name me a concrete example of corruption that could occur? > > 1. Open "/Users/lwood/Desktop/folder/db.sqlite". > 2. Rename the folder. > 3. Try to open "/Users/lwood/Desktop/folder/db.sqlite-journal". > > > Regards, > Clemens > ___ > 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] Concrete example of corruption
Let me put it this way: Suppose I have my main database file *in a folder*: /Users/lwood/Desktop/folder/db.sqlite Suppose I can only move/rename the *folder*. Suppose I never mess with the folder's contents. Can you name me a concrete example of corruption that could occur? It seems that all the examples out there have to do with the "db.sqlite-journal" file getting a mismatching name or a different location from "db.sqlite", but that's not possible here. Both files are always together. I'm on Mac OS X. I don't see any problem. I can imagine getting an *error* from the SQLite C functions, from which I can easily recover by re-opening the database from the new path. But I can't imagine an example of *corruption*. Please name one. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Your thoughts on these observations
Of course, Richard has already answered question 1 and that answer definitely doesn't change. Questions 2 and 3 now remain. > From: lwoo...@live.com > To: sqlite-users@sqlite.org > Date: Fri, 29 Nov 2013 15:21:51 + > Subject: Re: [sqlite] Your thoughts on these observations > > Before answering my questions: > > Please get rid of "rename" entirely in my post. Replace "rename/move" with > just "move". > > Also, assume that whenever I move my main database file, the -journal file > always moves with it too. > > I apologize for the confusion. My mistake. Sorry Richard. > >> I have made a few experimental observations and I would appreciate your >> thoughts. >> >> 1. >> >> _open() does indeed actually open the main database file. It is left open >> all the way until _close(), when it is actually closed. Correct? >> >> I ask because someone mentioned before that _open() doesn't really open the >> file - that it only prepares the file for opening at a later time when open >> is actually needed. >> >> >> 2. >> >> If my SQL statements do nothing but read from the database (no writes), I >> can rename/move the main database file any time I wish after _open() without >> any problems whatsoever. Correct? >> >> >> 3. >> >> If my SQL statements write to the database and I rename/move the main >> database file just after _open(), the next call to _step() gives me the >> error SQLITE_IOERR. >> >> This happens NOT because the main database file has been renamed/moved. >> Correct? >> >> Rather, this happens because the old path has been saved by SQLite for use >> by the -journal files. The error happens because SQLite cannot match the >> -journal file name and the main database file name (which has now changed). >> But here I'm just guessing. Is this correct? If not, then why exactly does >> the error occur? > ___ > 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] Your thoughts on these observations
Before answering my questions: Please get rid of "rename" entirely in my post. Replace "rename/move" with just "move". Also, assume that whenever I move my main database file, the -journal file always moves with it too. I apologize for the confusion. My mistake. Sorry Richard. > I have made a few experimental observations and I would appreciate your > thoughts. > > 1. > > _open() does indeed actually open the main database file. It is left open all > the way until _close(), when it is actually closed. Correct? > > I ask because someone mentioned before that _open() doesn't really open the > file - that it only prepares the file for opening at a later time when open > is actually needed. > > > 2. > > If my SQL statements do nothing but read from the database (no writes), I can > rename/move the main database file any time I wish after _open() without any > problems whatsoever. Correct? > > > 3. > > If my SQL statements write to the database and I rename/move the main > database file just after _open(), the next call to _step() gives me the error > SQLITE_IOERR. > > This happens NOT because the main database file has been renamed/moved. > Correct? > > Rather, this happens because the old path has been saved by SQLite for use by > the -journal files. The error happens because SQLite cannot match the > -journal file name and the main database file name (which has now changed). > But here I'm just guessing. Is this correct? If not, then why exactly does > the error occur? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Your thoughts on these observations
I have made a few experimental observations and I would appreciate your thoughts. 1. _open() does indeed actually open the main database file. It is left open all the way until _close(), when it is actually closed. Correct? I ask because someone mentioned before that _open() doesn't really open the file - that it only prepares the file for opening at a later time when open is actually needed. 2. If my SQL statements do nothing but read from the database (no writes), I can rename/move the main database file any time I wish after _open() without any problems whatsoever. Correct? 3. If my SQL statements write to the database and I rename/move the main database file just after _open(), the next call to _step() gives me the error SQLITE_IOERR. This happens NOT because the main database file has been renamed/moved. Correct? Rather, this happens because the old path has been saved by SQLite for use by the -journal files. The error happens because SQLite cannot match the -journal file name and the main database file name (which has now changed). But here I'm just guessing. Is this correct? If not, then why exactly does the error occur? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_last_insert_rowid() and AUTOINCREMENT
I have my own whateverId column of type INTEGER PRIMARY KEY AUTOINCREMENT. Does AUTOINCREMENT change anything? * Is whateverId still just an alias for the built-in rowid (as it would be without the AUTOINCREMENT)? * Does sqlite3_last_insert_rowid() return the whateverId of the last INSERTed row, as I would expect? Just wanted to make sure. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple string question
> D. Richard Hipp wrote: > It is safer and faster to use the sqlite3_bind_text() interface. > > First prepare your statement like this: > > INSERT INTO my_table(col1) VALUES(?1); > > Then run: > > sqlite3_bind_text(pStmt, 1, zYourString, -1, SQLITE_TRANSIENT); > > Then run your statement: > > sqlite3_step(pStmt); > > Further information: http://www.sqlite.org/c3ref/bind_blob.html Brilliant, what a great API! But are there never quotes around the ?1, ?2, etc. even in SELECT statements? For example: SELECT * FROM table WHERE col1=?1 AND col2=?2; Is this correct or should there be '' around the ?1 or ?2 parameters? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index performance
Suppose I have a table Foo with two columns: state, company_stock_symbol. (There are other columns but let's ignore them.) The states are relatively few (50). There are tens of thousands of companies. * If I frequently do queries like this: "SELECT * FROM Foo WHERE company_stock_symbol='bar' AND state='baz';" what index should I use? Should I use (company_stock_symbol), (state,company_stock_symbol), or (company_stock_symbol,state)? * Does the answer change if the query condition is reversed? Like so: "SELECT * FROM Foo WHERE state='baz' AND company_stock_symbol='bar';" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simple string question
Suppose I have a table with one text column. I insert like this:
INSERT INTO my_table (col1) VALUES ('arbitrary UTF-8 string');
* Isn't it true that the string must indeed be surrounded by single quotes as I
do above?
* Isn't it true that I have to replace all occurrences of ' in the original
string with '' (to escape each single quote)?
* Do I have to do anything else at all?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What takes the most time
> Yes, _step would generally take the most time, as that's where the > actual work happens. > > Yes, you would call _step once for every row produced by SELECT (as well > as certain PRAGMAs), and only once for other statements that don't > produce a resultset. Great, thank you. Another question: If a single _step() call takes a long time to execute (a few minutes), is my only option to just wait for it? Does SQLite not allow any kind of callback mechanism for each _step() to indicate how many percentages are done (or how many bytes have been read/written), and allow for cancellation of the process? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What takes the most time
These are the main SQLite functions: _open() _prepare_v2() _step() _column() _finalize() _close() Suppose I'm calling these once to execute a single SQL statement. Can you give me a feel for which of these functions takes the longest to execute? There are two cases I'm interested in: 1. "Read statements" like this: "SELECT * FROM SomeHugeTable;" I guess that _step() will take most of the time, and that we will have to call it multiple times for each row in the table? Correct me if I'm wrong. 2. "Write statements" like these: "INSERT INTO SomeHugeTable (col1,col2) VALUES (val1,val2);" or "UPDATE SomeHugeTable SET col1='whatever' WHERE rowid=10566;" Here I would also guess that _step() would take the longest? Also, am I correct in assuming that both of these statements would only need a single _step() call (since there are no results returned)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Internal workings of reading/writing -journal files
* For each transaction, i.e. between _prepare_v2() and _finalize(), does SQLite on Mac OS X only use the POSIX open(), read(), write(), lseek() etc. functions to read/write into the -journal file? Or does it use some other functions? * For each transaction, i.e. between _prepare_v2() and _finalize(), does SQLite on Mac OS X open the -journal file once in the beginning of the transaction for both reading and writing and close it at the end of the transaction? Or does it open it once for writing at the beginning of the transaction, and open it again for reading at the end of the transaction? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: Better support for database file moving
Thanks for the replies. I'd like to address some of your comments so far. -- Stephan Beal: > If the app is sandboxed, how does the user possibly > get a chance to move the files? A sandboxed Mac OS X program can gain access to regular user-selected documents (files or directories). And of course, users can move around such documents. That doesn't change when the program is sandboxed. > Until you asked about this last week i had never heard > of a use case where any app needs to account for its data > files being moved while it is running. I may not have mentioned this clearly, but I wasn't discussing moving the program's data files. The program's data files are located within some ~/Library subdirectory and of course I'm not trying to handle the user messing with those. What I'm trying to handle is the user moving *regular documents* around. As I mentioned, such a document is in my case a package (directory) that contains the SQLite database file. Being a regular document, the user can move it around like any other document. > If it's on the same device it's just a > rename and the inode stays the same. If it's across devices then it's > basically a copy+delete. Yeah, I'm aware of this case and there is nothing one can do here. A "move" to a different device always has to be equivalent to a delete, unfortunately. -- Simon Slavin: > ...where to find data files for a sandboxed app... Please see above replies to Stephan. I'm not discussing data files. Sorry for the confusion. > Besides which, you can write it yourself externally to SQLite: > use FSEvent on the folder your database is in checking for > kFSEventStreamEventFlagItemRemoved, and act accordingly. > No need to put that facility inside SQLite. The problem is that the user could move a document *during a transaction*, i.e. between _prepare_v2() and _finalize(). SQLite saves the old path into memory when a -journal file is created. Sure, I can watch the file moves myself as you suggest, but there is nothing I can do to influence SQLite in this matter. Hence my feature request. -- Tim Streater: > It's unclear if the OP wants to do this or expects to be > able to move the whole lot while the app is running, which > is not something I'd expect to be able to do. Sorry for the confusion. I'm talking about moving individual documents. See above replies to Stephan and Simon. -- Stephen Chrzanowski: > they shouldn't be playing with the file structure to begin with. Sorry for the confusion. I'm talking about moving individual documents. See above replies to Stephan and Simon. > Basically, with SQLite anyways, I think that if you have a file that is > moved during a transaction, your results are going to fall under the > "UNDEFINED" category. I'm afraid you're right. That's the way it is now and that's why I posted the feature request :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature request: Better support for database file moving
Here is my situation and why SQLite doesn't seem to satisfy it. It would be great to hear what some of the SQLite developers think about this. * My Mac OS X program has a document file format of packages (which are nothing but glorified directories). My database file is always inside of a package. So, it can be assumed that all the extra files (such as -journal) will always move with the main database file if the user moves the document (the package). * The program runs in sandbox mode (a requirement for all programs on Apple's Mac App Store), meaning that if a user moves a package, the program will gain read/write access to the new path but LOSE read/write access to the old path. This poses a problem: * A user could move the file during a transaction, i.e., between _prepare_v2(); and _finalize(); It seems that SQLite needs support for this situation. Would you agree? If so, the question is how. Maybe through callback functions requesting an updated directory when needed? Or by internally following the changed path of the main database file instead of storing it in memory at the outset? Until then, I guess I just have to live with the risk of either an error or data corruption when a user moves a document (a package). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What constitutes one SQL transaction?
> Simon Slavin wrote: > No. So by "no", you mean that the only other alternative is that the C calls I mentioned can constitute ZERO transactions, and this case happens only if * the (current) prepared statement contains BEGIN OR * some old prepared statement contained BEGIN and no END has yet occurred -AND- * the (current) prepared statement doesn't contain END Did I get it right? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What constitutes one SQL transaction?
SQLite creates a -journal file for each single transaction. But what exactly is "one transaction" and when does it happen in terms of the C functions? For instance, can we safely say that the successive calls _prepare_v2() _step() ... _step() _finalize() always and everywhere constitute one transaction and hence, involve a single -journal file? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
> Richard Hipp wrote: > Rollback journal files might be closed and reopened. But the main database > file is opened once and held open until sqlite3_close() (or DETACH if the > file was originally opened using ATTACH). Thanks for this information. But suppose, while a journal file is open, it is moved (along with the main database file) to a new directory. * The next close() of the journal will work just fine, I assume? * But what about the next open() of a new journal file? Will it use the new location of the database file or the old one? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling move/rename of an SQLite database file
> D. Richard Hipp wrote: > No errors are returned. SQLite never notices that the database file has > been renamed. I did a simple experiment and got an error from sqlite3_step() after renaming the file. This is what I did: After the call to sqlite3_open(), I halt/sleep my program for 10 seconds to give me ample time to rename the file. I rename the file. The subsequent sqlite3_prepare_v2() works without errors. However, the sqlite3_step() *does* give an error: Error code 10, "disk I/O error". This does not happen if I do not rename the file. Am I missing something? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Where does SQLite write out files?
What directories can SQLite possibly write files to? Modern Mac OS X programs run in "sandbox mode". This is a requirement to publish apps on Apple's Mac App Store. "Sandboxing" means that it is impossible for an app to write files into locations outside a given authorized "sandbox". For instance, a proper way to get a safe temporary directory is Apple's own C API, NSTemporaryDirectory(). How would you adapt SQLite to this environment? Here are my own thoughts so far (based on the webpage http://sqlite.org/tempfiles.html): * I'm aware of the files that SQLite can write to the *same* directory as that of the actual database file, and I have ways to deal with that (by putting the database file into a so-called package, so all the files will be together as a unit). * If I set the global variable sqlite3_temp_directory (http://sqlite.org/c3ref/temp_directory.html) to the value returned by Apple's NSTemporaryDirectory(), I'm hoping to be fine. Will I? Unfortunately, the webpage also says: "The manner in which SQLite uses temporary files is not considered part of the contract that SQLite makes with applications. The information in this document is a correct description of how SQLite operates at the time that this document was written or last updated. But there is no guarantee that future versions of SQLite will use temporary files in the same way. New kinds of temporary files might be employed and some of the current temporary file uses might be discontinued in future releases of SQLite." This renders any efforts in this regard ill-defined. Are there any solid ways out? I'm using SQLite 3.8.1, and are the rules on what webpage even true for this version? That is nowhere mentioned. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Handling move/rename of an SQLite database file
Users could rename/move a database file while my (Mac OS X) program has made an SQLite connection to the file. I want to handle this properly. Mac OS X (Unix) has ways to track a file that is renamed/moved, so if I can get enough information about how SQLite works in this regard, handling this should be possible. Below is a question that I believe I need answered to be able handle this properly, but feel free to add more thoughts of your own if you think I need more information. QUESTION: What error codes can each of the following functions possibly return while the database file is renamed/moved during the execution of the function? * sqlite3_open() * sqlite3_prepare_v2() * sqlite3_step() * The various "sqlite3_column()" functions - could they return something unexpected? * sqlite3_finalize() * sqlite3_close() My thinking is that if I knew all the possible error codes, I could - upon such an error - check if the file was renamed/moved. If it was, I could close and reopen the database connection on the new path and continue/restart execution as required. If it was not renamed/moved, it would be a genuine error to be handled like any other error. (And of course, I would perform the renamed/moved check in between the SQLite function calls, but that's not related to SQLite per se.) Again, you're welcome to add your own thoughts to the mix. Thank you. I'm using SQLite 3.8.1. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
