Re: [sqlite] Where does SQLite write out files?

On 11/7/13, L. Wood wrote: > What directories can SQLite possibly write files to? > > * I'm aware of the files that SQLite can write to the *same* directory as > that of the actual database file, Take particular attention to master journal files. As

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

Thanks Simon. See inline -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, November 07, 2013 4:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite db getting corrupt on

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

Thanks Richard. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, November 07, 2013 4:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite db getting corrupt on power outage

Re: [sqlite] Need suggestion for database scheama

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/11/13 19:47, James K. Lowden wrote: > You might guess from my email domain name that I take an interest in > posts like yours. And it's pretty good first cut, no pun intended. > ;-) It is also worthwhile looking at musicbrainz

Re: [sqlite] Need suggestion for database scheama

On Thu, 7 Nov 2013 14:50:44 +0400 dd wrote: > I am working on sqlite database schema for Music/Track files. I am > posting few tables schema here. > > CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY > AUTOINCREMENT, file_path TEXT NOT NULL, file_type

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

On Thu, Nov 7, 2013 at 7:57 PM, Simon Slavin wrote: > > > I will definitely look into the wal mode. Are there any test results for > disk failure robustness of this mode compared to journal mode. > > This won't help. As long as you're using a network file system which does

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

On 7 Nov 2013, at 11:50pm, Mayank Kumar (mayankum) wrote: > Are there known tools which tell us what data corrupted the db or some way of > removing only the corrupted section of the db ? Download the SQLite shell tool for your platform. Use .dump to dump the database to a

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

On Thu, Nov 7, 2013 at 6:50 PM, Mayank Kumar (mayankum) wrote: > > I also saw some references to corruption being caused when the same db is > written to by 3.6 and then being written to by 3.7. Is that true, because > we have similar scenario where the db was originally

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

Thanks Richard. Are there known tools which tell us what data corrupted the db or some way of removing only the corrupted section of the db ? I will definitely look into the wal mode. Are there any test results for disk failure robustness of this mode compared to journal mode. I also saw some

Re: [sqlite] Row Level Locking as in InnoDB

On 7 Nov 2013, at 6:31pm, Raheel Gupta wrote: > Any idea when will SQLite4 be released as stable ? No. It's not even feature-frozen yet, as far as we know. And whenever it is, it's incredibly unlikely to have row level locking. Simon.

Re: [sqlite] Row Level Locking as in InnoDB

>> SQLite4 still in development phase. It is not production ready. Any idea when will SQLite4 be released as stable ? On Tue, Nov 5, 2013 at 8:20 AM, Howard Chu wrote: > Aris Setyawan wrote: > >> Hi Howard, >> >> I just looked, sophia is nothing special. See these microbench

Re: [sqlite] Where does SQLite write out files?

On 7 Nov 2013, at 5:42pm, L. Wood wrote: > 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

Re: [sqlite] Handling move/rename of an SQLite database file

On 7 Nov 2013, at 6:03pm, L. Wood wrote: > 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.

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

Re: [sqlite] Handling move/rename of an SQLite database file

On 7 Nov 2013, at 5:38pm, Richard Hipp wrote: > Rollback journal files might be closed and reopened. Okay. That gives us more of the answer to the original question. Wood, this is the only file that SQLite would care about if moved. And technically you could use

[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

Re: [sqlite] Handling move/rename of an SQLite database file

On Thu, Nov 7, 2013 at 12:04 PM, Simon Slavin wrote: > > Assuming that the user is using OS X, use of standard VFS, access on a > local disk, no ATTACH, no Jedi mind tricks ... > > Does SQLite ever close and reopen database or journal files between > _open() and _close() ?

Re: [sqlite] Handling move/rename of an SQLite database file

On 7 Nov 2013, at 3:58pm, Richard Hipp wrote: > On Thu, Nov 7, 2013 at 10:31 AM, L. Wood wrote: > >> QUESTION: What error codes can each of the following functions possibly >> return while the database file is renamed/moved during the execution of the >>

Re: [sqlite] PRAGMA cache_size when copying data from two DBs

"Richard Hipp" asked... On Thu, Nov 7, 2013 at 10:13 AM, jose isaias cabrera wrote: Greetings! When copying data from two DBs, do the cache_size of PRAGMA needs to match both connection? What type of response, issues or problem would it cause when the two

Re: [sqlite] PRAGMA cache_size when copying data from two DBs

On Thu, Nov 7, 2013 at 10:13 AM, jose isaias cabrera wrote: > > Greetings! > > When copying data from two DBs, do the cache_size of PRAGMA needs to match > both connection? What type of response, issues or problem would it cause > when the two connection's PRAGMA

Re: [sqlite] Handling move/rename of an SQLite database file

On Thu, Nov 7, 2013 at 4:31 PM, L. Wood wrote: > 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. One thing you have to be particularly careful about is the extra files sqlite

Re: [sqlite] Handling move/rename of an SQLite database file

On Thu, Nov 7, 2013 at 10:31 AM, L. Wood wrote: > > 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? > No errors are returned. SQLite never notices that the

[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

[sqlite] PRAGMA cache_size when copying data from two DBs

Greetings! When copying data from two DBs, do the cache_size of PRAGMA needs to match both connection? What type of response, issues or problem would it cause when the two connection's PRAGMA cache_size are different? thanks, josé ___

Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro

Thanks Stephen - had a quick play with SQLite Expert just now before sleep - found a 3.7.4 dll and dropped it in and it loaded up fine. I'll see if the queries take the same time as in Flash tomorrow. On Thu, Nov 7, 2013 at 10:53 PM, Stephen Chrzanowski wrote: > SQLite

Re: [sqlite] tools list from sqlite.org

On 7 Nov 2013, at 1:48pm, dd wrote: > I used to include sqlite3.h/c files (from amalgamation) in > application. I would like to know about tools from sqlite.org. > > What are all the tools available from sqlite.org? Where can I find > the list of tools/utilities? any

[sqlite] tools list from sqlite.org

Hi, I used to include sqlite3.h/c files (from amalgamation) in application. I would like to know about tools from sqlite.org. What are all the tools available from sqlite.org? Where can I find the list of tools/utilities? any document? Thanks. ___

Re: [sqlite] Need suggestion for database scheama

It has to work with multiple devices in future. This is valid point for me. Thanks. On Thu, Nov 7, 2013 at 3:42 PM, Stephan Beal wrote: > On Thu, Nov 7, 2013 at 12:15 PM, dd wrote: > >> Thanks for pointing multimedia id, Stephan Beal. I missed it. I

[sqlite] Registering Virtual Tables on the spot (when needed)

Hi, I would like to use SQLite in a scripting context where there are thousands of "potential" virtual tables, but of these, only a very very small subset (a couple or a handful at most) would be actually used in any given script, so it wouldn't make sense to expose them all, all the time. I'm

Re: [sqlite] Query / schema optimization procedure?

Thanks alot, I will read through your email in detail later and think it through, but want to send a quick reply right now. About complexity: I see that it looks overly complex, especially when using table names like 't1' instead of names which makes sense in the real world. I understand

Re: [sqlite] Query / schema optimization procedure?

On 7 Nov 2013, at 10:52am, Daniel Polski wrote: > I would be happy to get advice on how to think / what to look for when trying > to optimize a query, views, adding indexes, optimizing schema design and so > on. > > So far I've figured out that I could add appropriate

Re: [sqlite] Disparity between query time in SQLite Administrator and Flash Pro

SQLite Expert allows you to use any SQLite DLL. Its free for non-commercial use, but even that said, its an awesome package to buy, life time upgrades, and seemingly always being updated. http://www.sqliteexpert.com/ On Wed, Nov 6, 2013 at 7:30 PM, SongbookDB

Re: [sqlite] Need suggestion for database scheama

On Thu, Nov 7, 2013 at 12:15 PM, dd wrote: > Thanks for pointing multimedia id, Stephan Beal. I missed it. I will > ad this to my schema. > If you're only storing the list for local use on one machine, adding the media ID is almost certainly overkill, but i found it useful

Re: [sqlite] Need suggestion for database scheama

Thanks for pointing multimedia id, Stephan Beal. I missed it. I will ad this to my schema. On Thu, Nov 7, 2013 at 3:07 PM, Stephan Beal wrote: > On Thu, Nov 7, 2013 at 11:50 AM, dd wrote: > >> CREATE TABLE if not exists AllFilesTbl (Id INTEGER

Re: [sqlite] Need suggestion for database scheama

On Thu, Nov 7, 2013 at 11:50 AM, dd wrote: > CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY > AUTOINCREMENT, file_path TEXT NOT NULL, file_type INTEGER NOT NULL, > UNIQUE(file_path)); > Some years ago i worked on something similar to keep track of my

[sqlite] Query / schema optimization procedure?

Hello, I have an SQL query which fetches the requested data from the database, but is using too much resources when doing so. I would be happy to get advice on how to think / what to look for when trying to optimize a query, views, adding indexes, optimizing schema design and so on. So far

[sqlite] Need suggestion for database scheama

Hi All, I am working on sqlite database schema for Music/Track files. I am posting few tables schema here. CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY AUTOINCREMENT, file_path TEXT NOT NULL, file_type INTEGER NOT NULL, UNIQUE(file_path)); CREATE TABLE if not exists AlbumTbl