Paul,
here are a few odd thoughts on this:
--Flash memory devices in general are subject to 'finite' numbers of I/O
events, mostly affecting writes; more recent flash memory devices ease this
constraint a bit, if only by supporting more I/O events before they hit
their useful life-limit. I think you're asking the right questions though;
can you model the likely no. of writes over the intended life-cycle based on
anticipated activity etc?
--Some of how you implement this depends on what embedded OS you're working
with (is it a RTOS, a Linux hybrid, WinCE, Win Mobiles 5 or 6.x etc?. I'd
want to make sure I could implement the software that manages the Sqlite as
a daemon or background-service, for better flexibility.
--Your idea of posting transactions from near-real-time event traffic to an
in-memory definition (":memory:") may be good, certainly bears some study.
It complicates how you might implement periodic handoffs from the ":memory:"
instance to the non-volatile 'disk' instance of sqlite though, particularily
when a serious exception (say, something really severe like a power-outage)
might need to be trapped almost instantaneously. This is coincidentally
(precisely) when you may need assurance that the most-recent data is safely
stored on the non-volatile side of the embedded system. This in turn raises
the question of your assessment on how much data can you afford to lose? (if
any).... a metric that governs how often you schedule your
synchronization-transactions; I'm guessing you'll use a delta-oriented sync
logic to avoid messy redundancy.
--To try to address your question on how intimately you might need to know
the insides of sqlite3, you can get away with knowing less in proportion to
how closely your implementation strategy resembles a standard use-case
(writing to 'disk'); the more multi-tier mechanism you decide to support,
the more you'll benefit from knowing sqlite better on the inside (IMHO); at
least you can study the code anytime etc.
--Overall, always writing to the flash memory as a default strategy would be
safer, simpler.. presuming you can afford the amount of time the I/O takes
(some SD devices are notoriously slow at write-side I/O, so watch that).
I'm assuming that each transaction is fairly small and atomic. I would
prototype any of this using Python's binding to sqlite, to give yourself a
good feel for the dynamics.
--My own experience in this sort of implementation is using SyBASE
Anywhere's mobile database,(along with MobiLink for device-to-host
synchronization) on WinCE,Win Mobile 5 (not yet v6). I've used sqlite
primarily on several non-embedded application area, but am looking hard
myself at just the questions you are addressing.
I don't if any of this helped.. but good luck.
joe
On Thu, Oct 2, 2008 at 2:20 AM, Paul McMahon <[EMAIL PROTECTED]> wrote:
> What's the consensus on using sqlite with flash based storage such as sd
> cards on embedded devices?
>
> Has anyone done this successfully yet with a real product (that needs to
> last a few years in the field)?
>
> How does one solve the problem of minimizing writes to flash? I've done
> a search through the user lists but there seems to be no generally
> agreed way to do this. Does it require intimate knowledge of the inner
> workings of sqlite?
>
> One scheme I thought of was to use an in memory sqlite database in
> conjunction with a flash based one.
>
> New entries go in the in memory database (using ":memory:" on filename).
> Searches search both the in memory and flash based database. Every now
> and then the in memory database entries get written to the flash based
> database.
>
> Any ideas would be greatly appreciated!
>
>
>
> _______________________________________________
> 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
