Alrighty: embedded hardware _does_ make sense. Some options:
0. gzip the dumped database. don't save indexes, etc. Rebuild the database on startup. You can even diff against older copies if your flash has a [significantly] limited number of write cycles. this is probably the best/easiest method, but if you don't have control of the startup routine, and can guarantee some code is run at shutdown, it probably won't work for you. 1. Examine the sqlite source code: write a routine that locks the main db exclusive, checks for the -journal file, and as necessary unlocks/waits until it can get a lock with the -journal file missing. Use this, then copy the database normally. This requires some coding, and gaining understanding of what sqlite uses various files for. It is the least invasive on your existing system, even if it's probably the hardest to get right. 2. Use a ramdisk, and put LVM on it. Or if this is not a linux or freebsd-based embedded box, alter your operating system to allow you to temporarily defer all filesystem operations "somehow". Fork the filesystem using LVM's snapshots (freebsd has a similar invention, ms windows has shadow copies, but i don't know as to their availability in the embedded world). If you can defer filesystem operations, simply copy the database+journal to another directory while all other filesystem operations are suspended. Use sqlite's open on the database+journal to recover it on the forked volume, then close: copy the resulting db to flash. This is actually really simple, and could be made very general for saving all kinds of configuration data. I use a method very similar to this for router configuration as I can simply do a snapshot and cpio directly into flash (skipping my temporaries as I do a O_EXCL->link- >unlink to guarantee atomicity), then delete the snapshot. It does (however) take advantage of the fact that sqlite is crashproof. It'll do it's recovery later. On Sun, 2005-05-01 at 10:37 +0800, Damian Slee wrote: > hi, > i had have a look, i may be able to use this. this will be in an embedded > hardware application, so i don't have a command line, only what i code in. > > i was hoping to do a binary copy of the db from a ram file system to flash > memory for permanent storage. flash is really slow, so a copy would be a lot > quicker than a series of .dump Inserts. which really requires sqlite running > on a flash file system as well. a copy is really all i want, but knowing > when everything is commited, or locking out other threads without them having > to close the db. > > thanks, > > damian > > > > > ________________________________ > > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > Sent: Sun 1/05/2005 1:07 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] backup open database > > > > Why is it inappropriate to use: > > sqlite originaldb ".dump" | sqlite backupdb > > > On Sun, 2005-05-01 at 00:28 +0800, Damian Slee wrote: > > hi all, > > i want to make a copy of a sqlite3 database file while it is open. is > > there anyway that i can tell programatically that any caches/journals are > > flushed out? or is there any way to get and exclusive lock i guess, before > > making a backup copy? > > > > my proposed application would have quite a few threads which may have the > > same sqlite db open for read or write (rarer), but i dont want to shut down > > the application to definitely know it is safe to copy. > > > > > > thanks, > > > > damian > > > >
