Re: [Omaha.pm] Embeddable database options.

[Dan Linder <dan@linder.org>]

Mario,

Thanks for the feedback.

2009/8/24 Mario Steele <mario@ruby-im.net>:
> SQLite is a great Database system, for File Based data storage.
> Unfortunately, it stores in binary, so you can't exactly use grep, vi, etc,
> etc, to read the contents of the database file.  But unlike it's big
> brother, you can only have one transactional lock (EG Database Open) at a
> time on a database file.  This is to prevent corruption of the data.  (And
> yes, this locks even if your just doing a read query.)

Thankfully, the database is very light on the write/update side.  Does
the read lock lock out other readers at the same time?

> DBD overall, is a great front end for you to use, for database storage, as
> it gives you a common api across many different DB Backends.  If you want
> consistency, and the ability to test different database storage engines,
> then I would strongly recommend you use DBD.

As much as there is some pressure to stay "Pure Perl" and not rely on
non-core modules, I think this is the only route toward expanding this
tool.  (Plus the added flexibility of adding other DB options by
including the appropriate perl module.)

>> Internally I have some constraints:
>>  * We'd like to keep the number of non-core Perl modules down
>> (currently we're 90% core), and a couple customers are extremely
>> sensitive to anything that is not supplied by their OS provider
>> (Solaris and HPUX for example).
>
> This is true in many facets, but you'll find standard that MySQL and SQLite
> are often the biggest thing that is distributed on most Operating Systems
> (Aside from Windows, but we won't go there).

Thankfully the server portion is 100% Unix. :-)

>> * We would also like to keep the files on disk and in a
>> human-readable form so the end users and support staff can peruse this
>> data with simple tools (grep, vi, etc).
>
> Again, as stated above, SQLite, and MySQL won't let you use grep, vi, etc,
> to view the data, but simple tools can be created to create the same effect,
> and highly optimize it to specific tasks, instead of looking through
> hundreds of lines of data, to find a specific field.

I'm thinking that as a work-around to this, I can keep both versions
available.  Since these data files are only updated in a couple key
locations (and the update is mostly through non-interactive means),
this should be easily achievable.  Once the data is saved in both
forms and the flat and db files are consistent, updating the reporting
pieces should be easier since I won't (shouldn't) break anything
during the transition.  An added bonus is that customers who rely on
the textual data will not have to immediately re-code for the new DB
chosen.

As I'm writing it, I'm leaning toward using the DBD interface and
accessing SQLite initially.  If/when the time comes that
MySQL/Postgres/Oracle/AnotherDB is requested, the changes should be
minimal.  The downfall of being an external module is greatly
out-weighed by the flexibility it provides us.

I'm hoping to carve out some free time over the next couple weeks to
put some test code together to see what speed differentials are
achieved by replacing dump/eval with SQLite, MySQL, etc.

Thanks,

Dan


-- 
******************* ***************** ************* ***********
******* ***** *** **
"Quis custodiet ipsos custodes?" (Who can watch the watchmen?) -- from
the Satires of Juvenal
"I do not fear computers, I fear the lack of them." -- Isaac Asimov (Author)
** *** ***** ******* *********** ************* *****************
*******************