To paraphrase Phillip Greenspun, "A few databases would make our website more useful." Not noticing this excellent thread, I set out to figure out whether or not to go the RDMS or the file system route.

The mod_perl people seem to think simple file based system like DB_File can handle 5,000 to 10,000 rows --larger than I can imagine any of our tables getting.

As a nice plus, DB_File comes with Perl on LINUX where I would use it. Given this information and these two tenets of XP

...I thought I was set. Thinking a little more, I realize I'm going to want to use SQL almost right away and DB_File doesn't do SQL. (right?)

So, back to the drawing board... This level 10 person has a strong negative opinion of DBI. However, the DBI clan looks like a happy and helpful group. This node by trantor touches on avoiding the problems switching between different databases comforts me. (Even though I am not very clear on how s/// and the database independent  quote method are different.)

For those not yet part of the clan, DBI translates to "Database interface" and is a generic way to access a large number of file and server based databases from perl. DBD translates roughly to "database driver". For each database, (Oracle,Sybase, Postgres, MySQL, etc) there is a seperate DBD module. DBM refers to Berkley Database files see DB_File.

I'm still hoping "SQL" ne "database server", so I look at the database category on CPAN and search perlmonks. A bunch of people say nice things about DBD::RAM. I come across this helpful node by anonymous (but wise) monk pointing out that DBD::AnyData replaces DBD::RAM

But wait, DBD::AnyData doesn't support joins! and my first query is likely to be something like:

SELECT 
  users.name, AVG(tests.score)
FROM
  users, tests
WHERE
  users.name=tests.name
GROUP BY users.name
ORDER BY users.name;

[download]
I guess I need a database server after all. The two contenders in my price range (free) seem to be MySQL and Postgres There seem to be more people using MySQL, but a year ago, the OpenACS people had some mean things to say about MySQL. It does look like there have been improvements. However, useful stuff like stored procedures, triggers and nested queries don't look like they are there yet.

While the feature list for DBD::pg doesn't include access to stored procedures....????... It looks like Postgres is the database for me...

(Barring counter arguments from my wiser peers...)

--mandog

Edit: chipmunk 2001-09-03

update: changed "qoute" to "quote" removed extra (wrong) comma

In reply to exploring XP DBI DBD DBM SQL RDMS MySQL Postgres by mandog

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.