SQLite over MySQL?

Hey John (or anyone else),

On 2011-06-17, John Delacour wrote:
>I'm afraid I can't advise since I can't stand MySQL and find life much eas=
ier with SQLite.

This aroused my curiosity because I have to switch some Perl=20
programs written long ago with various flat-file data tables=20
accessed via howe-brew parsing to something more transferable.=20
None of my uses has more than a few hundred thousand records,=20
and most have far fewer.

Would you care to say a bit more about your preference? Do you=20
use the Perl DBI with SQLite? If something that reflects your=20
views can be read elsewhere, please point.

CAUTION: I'm not interested in enflaming a huge debate about the=20
merits and demerits of these and other database systems, their=20
developers, companies that "own" them, etc. Just a bit of=20
practical curiosity...

Thanks.


    - Bruce

_bruce__van_allen__santa_cruz_ca_

0
bva
6/17/2011 10:43:11 PM
perl.macosx 2309 articles. 0 followers. Follow

3 Replies
543 Views

Similar Articles

[PageSpeed] 52
Get it on Google Play
Get it on Apple App Store

On Fri, Jun 17, 2011 at 18:43, Bruce Van Allen <bva@cruzio.com> wrote:
> Hey John (or anyone else),
>
> On 2011-06-17, John Delacour wrote:
>>
>> I'm afraid I can't advise since I can't stand MySQL and find life much
>> easier with SQLite.
>
> This aroused my curiosity because I have to switch some Perl programs
> written long ago with various flat-file data tables accessed via howe-brew
> parsing to something more transferable. None of my uses has more than a few
> hundred thousand records, and most have far fewer.
>
> Would you care to say a bit more about your preference? Do you use the Perl
> DBI with SQLite? If something that reflects your views can be read
> elsewhere, please point.
>
> CAUTION: I'm not interested in enflaming a huge debate about the merits and
> demerits of these and other database systems, their developers, companies
> that "own" them, etc. Just a bit of practical curiosity...
snip

SQLite is definitely more of a pleasure to work with than MySQL, but
it is not suitable for all applications.  This text from [SQLite's
site][1] sums it up nicely:

    There are advantages and disadvantages to being serverless. The main
    advantage is that there is no separate server process to install,
    setup, configure, initialize, manage, and troubleshoot. This is one
    reason why SQLite is a "zero-configuration" database engine. Programs
    that use SQLite require no administrative support for setting up the
    database engine before they are run. Any program that is able to
    access the disk is able to use an SQLite database.

    On the other hand, a database engine that uses a server can provide
    better protection from bugs in the client application - stray pointers
    in a client cannot corrupt memory on the server. And because a server
    is a single persistent process, it is able to control database access
    with more precision, allowing for finer grain locking and better
    concurrency.

You access SQLite DBs in Perl the same way as MySQL DBs, with a DBD
module using the DBI.  The primary difference is that the entire DB
engine is contained within the DBD::SQLite module.  There is no setup,
you just say

    #!/usr/bin/perl

    use strict;
    use warnings;
    use DBI;

    my $dbh = DBI->connect("dbi:SQLite:dbname=foo.db", "", "");

and if the file foo.db didn't exist already, it is created.  That is
it; you have a database now.

That said, it doesn't provide some things you might expect.  So far as
I know, it provides no security layer.  there is no username or
password to connect, no users own tables (and therefore there are no
privileges to grant).  The database does no type checking.  If you
declare a column as being an integer, there is nothing to stop you
from storing a string in it (this is a simplification of its dynamic
typing, but an integer column can contain "fred").

[1]: http://www.sqlite.org/serverless.html



-- 
Chas. Owens
wonkden.net
The most important skill a programmer can have is the ability to read.
0
chas
6/19/2011 11:37:08 PM
>>>>> "Chas" == "Chas Owens" <chas.owens@gmail.com> writes:

Chas> That said, it doesn't provide some things you might expect.  So far as
Chas> I know, it provides no security layer.  there is no username or
Chas> password to connect, no users own tables (and therefore there are no
Chas> privileges to grant).  The database does no type checking.  If you
Chas> declare a column as being an integer, there is nothing to stop you
Chas> from storing a string in it (this is a simplification of its dynamic
Chas> typing, but an integer column can contain "fred").

There is no longer a gap between SQLite and PostgreSQL that MySQL could
fill nicely, as it had in the past.  They overlap in the middle.

MySQL should not be used for new starts.

Or, as I say, "Friends don't let friends use MySQL".

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.posterous.com/ for Smalltalk discussion
0
merlyn
6/23/2011 11:27:44 PM
>>>>> "Eberhard" == Eberhard W Lisse <nospam@lisse.NA> writes:

Eberhard> I beg to differ, if one writes Perl code (and even if one only
Eberhard> dabbles like the elderly Gynaecologist writing this) one can figure out
Eberhard> how to set up and run mysql, which in my case involves fink or even
Eberhard> PostgreSQL which now comes with Lion.

Eberhard> SQLite has its moments but if there ever is a need to upgrade to a
Eberhard> proper multi-user environment it may come back to bite me.

The problem is not knowledge of how to set up MySQL, but that MySQL is
not the the thing to set up.

At a minimum, if you need MySQL, install MariaDB.
If you need an actual database, consider whether SQLite will do, or
whether you need more power, in which case, install PostgreSQL.

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.posterous.com/ for Smalltalk discussion
0
merlyn
12/29/2011 11:28:56 PM
Reply: