[Omaha.pm] Embeddable database options.

[Mario Steele]

Hey Dan,

On Wed, Aug 26, 2009 at 10:30 AM, Dan Linder <dan at linder.org> wrote:

> Mario,
>
> Thanks for the feedback.


Not a problem.


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

This is an excerpt from SQLite's manual regarding concurrency:

SQLite uses reader/writer locks on the entire database file. That means if
any process is reading from any part of the database, all other processes
are prevented from writing any other part of the database. Similarly, if any
one process is writing to the database, all other processes are prevented
from reading any other part of the database. For many situations, this is
not a problem. Each application does its database work quickly and moves on,
and no lock lasts for more than a few dozen milliseconds. But there are some
applications that require more concurrency, and those applications may need
to seek a different solution.

In simpler terms, the longer your SQL Statement has to execute, the longer
the database is going to be locked in either read/write mode.  If your
getting small subsets of data from the database, then the milliseconds quote
is correct, and it won't take long to execute the SQL Statements.  However,
if you are getting a few hundred megs of records from the database in a SQL
statement, either inserting, or retrieving, then it's going to take the
database engine longer to fetch the data, and will hold the lock.  I believe
you can have as many readers as you want reading from the database, but once
a program sends a write SQL statement, all other readers will lock till the
writer finishes, the same for the opposite direction.

But if it's small updates, and small record sets being retrieved, then you
shouldn't have anything to worry about.


> 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.)
>

There's a lot of people that way, same with wanting "Pure Ruby" or "Pure
Python" or "Pure" any other language out there.  But the thing about it is,
you need to realize that with Perl, Ruby, Python, C# and such languages,
they are all interpreted languages, and anything "Pure" in those languages,
are going to be slow to execute.

The best thing about SQLite, atleast in Ruby, haven't really dealt much with
the Perl one, is that the extension that binds with the engine itself, is
all compiled together into a single dll/so extension, so you don't have to
rely on an external library to be installed.  And most times, it compiles
without any problems, especially on Linux based systems.


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


And again, this is a life send for you, as stated above.


> 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.


Another nice thing that you can do, if you want to go this route, with
having the ability for grep/vi, is that you can create simple wrappers
around grep and vi, and run SQL statements to generate the output, and pipe
it into grep, or vi, and have no problems with it.


> 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 would definitely agree with that, you want flexibility, not just in the
code that you have to write, but flexibility in the programs themselves, to
meet the needs of various customers.  Not all customers have the same need,
and when you start to use the DBD interface, you'll find that it works quite
well, and will require hardly any differences between the various database
engine backends, that DBD Supports.


> 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.


I can almost garuntee that SQLite, and MySQL will overpower dump/eval in
nothing flat.  At that point, your bottlenecks should dis-appear, least you
code sloppy SQL Statements, but JOINs are your friends when cross table data
retrieval.

And your welcome for the help,

Mario


>
> 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)
> ** *** ***** ******* *********** ************* *****************
> *******************
> _______________________________________________
> Omaha-pm mailing list
> Omaha-pm at pm.org
> http://mail.pm.org/mailman/listinfo/omaha-pm
>



-- 
Mario Steele
http://www.trilake.net
http://www.ruby-im.net
http://rubyforge.org/projects/wxruby/
http://rubyforge.org/projects/wxride/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/omaha-pm/attachments/20090826/53206255/attachment.html>