Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Single file RDBMS w/o system install

by gryphon (Abbot)
on Nov 12, 2001 at 23:15 UTC ( [id://124894]=perlquestion: print w/replies, xml ) Need Help??

gryphon has asked for the wisdom of the Perl Monks concerning the following question:

Greetings fellow monks,

I have an interesting dilemma; one which could be written up under Perl's "too many good options to solve a simple problem" index. I've been playing around with DBI and DBD::ODBC to run queries off an Access database living in Win32. All's well and good here except that I'd like to have an equivalent "system" that'll run on both Win32 and UNIX.

What I'm asking specifically is this: What database style, in your opinion, is the "best" when it comes to the following requirements?

  1. Uses only a single file per database, despite multiple internal tables
  2. Lives as a stand-alone file; doesn't require an RDBMS install
  3. Allows for SQL querying (via DBI or other is fine)
  4. Supports stored table relationships
  5. Supports column data typing
  6. Supports a "decent" level of normalization

Right now, I'm less concerned with what modules I'm going to use to get at this database than I am about deciding on the database type. If I could wave a magic wand, I'd love to have a MySQL database live as a single file apart from the MySQL system. Obviously, my requirement to have this as a single file rather than a system is going to cut into speed, functionality, and a few other things, but I'm willing to make that sacrifice.

Up 'til now, I've been using either text files, CVS, or XML with DBD::AnyData or similar. I'd like something that's just a simple and "isolated" from the OS/box/system, but houses its data better.

Am I just dreaming? Or does such a thing exist? If not, any ideas as to where I should go or where I should look for ideas? Thanks.

-gryphon
code('Perl') || die;

Replies are listed 'Best First'.
Re: Single file RDBMS w/o system install
by Bobcat (Scribe) on Nov 13, 2001 at 05:36 UTC

    How about using DBD::XBase?

    Granted, XBase lacks some functionality but is probably leaps and bounds ahead of an XML file or CSV file in terms of searchability. While it's not nearly as "full featured" as Access, it does handle simple SELECT/INSERT/UPDATE/DELETE statements.

    The requirement of not having a database server makes things a little tough, but the XBase seems (to me, at least) to the closest to what you've described.

    Good luck!

Re: Single file RDBMS w/o system install
by perrin (Chancellor) on Nov 13, 2001 at 03:32 UTC
    The best you'll be able to do without an RDBMS is one of the things you've probably already tried: DBD::AnyData, one of the XML modules, DBD::CSV, or DBD::Sprite. To go beyond that you need an actual RDBMS.
Re: Single file RDBMS w/o system install
by thunders (Priest) on Nov 13, 2001 at 03:19 UTC
    I have had luck with MySQL. It fails the "single file" requirement, but it is still fairly simple to control, all files in a given database are stored in the /data directory in it's own subdirectory So it's easy to move and manage the multiple files. The Mysql project i'm working on is for a Linux Server, and I'm developing it on a Windows NT box running Apache. Besides some minor changes in my Perl scripts, it runs correctly on both platforms. MySQL has a very good web based gui called phpMyAdmin that also works on Win32 and UNIX.
    As for the specific requirements you mention the only product that behaves that way that I can think of is MS Access, but it's not really a multiuser solution. Any particular reason for the single file requirement?

      I've been struggling with the same decision for one of my projects: text-based database vs. MySQL (or PostIngres or even other more costly RDBMS'es). I've been asked to take over for another consultant who has recently become consumed by the arrangements for his daughter's Bat Mitzvah.

      He chose the module JSprite, which is a standalone version of DBD::Sprite, for better or worse. This works fairly well, with each "table" being stored in its own (CSV) file, the directory containing these being nicely contained in one place.

      My own concern is less about speed (which cannot begin to compete with an RDBMS) than about mutli-user access (the client claims not to expect more than 10-12 users at any given time).

      JSprite uses flock() to manage contention, which, although arguably adequate for 10-12 users, is certainly insufficient for ten times that many.

      Still agonizing over whether to force the issue, I'm proceeding with development using the JSprite configuration. One nice thing about the SQL syntax it supports is that it is vaguely Oracle-like in form. Once the system is running, I can replace the underlying database (if necessary) with a minimum of pain.

      Not sure how helpful this was to you...

      dmm

Re: Single file RDBMS w/o system install
by gryphon (Abbot) on Nov 13, 2001 at 04:09 UTC

    Greetings again,

    One of the major factors influencing the listed requirements is the fact that I may or may not have root on the system where the database will live, and I can't depend on any single RDBMS being installed on the system. This has been a major reason why, until now, I've been content with flat text files, CVS, and XML for most of it all.

    I'm aware that the Jet "RDBMS" powers Access when on Win32. Never the less, you can still use ODBC to get into the file. I know it's possible to play around with Access files on UNIX systems, but you have to install/config some kind of ODBC enviornment. (At least, that's what I've seen posted here and elsewhere.) So, in theory, couldn't there be a binary file with defined column sizes and so forth that's "stand alone" from a full RDBMS? I'd imagine a DBD::? would let you interface with the file via DBI...

    I must be dreaming, but this sure would be nice to have.

    -gryphon
    code('Perl') || die;

      I know it's possible to play around with Access files on UNIX systems, but you have to install/config some kind of ODBC enviornment.

      You're correct. A quick search on Google turned up several possible ODBC drivers for Unix. On Windows this is a great solution because the Jet engine works quite well and you have the "one file" convenience of the MDB (Access) file. Keep in mind that even on Windows, your program isn't talking to the file directly, it's talking to a full-featured data base server (Jet or MSDE) via ODBC. However, with this solution on Unix, you'd still need a Windows server to host the Jet engine and the Access/MDB file. Probably not what you want. As mentioned here, I think AnyData or XBase would be your best bet. If your data set is going to be large and search time is important then you may need to bite the bullet and go with a "real" DB server to get the performance you need.

      The dbd::csv and dbd::xbase modules will let you run your own mini database without installing any additional rdbms software. They will, however, create multiple files per db.

      Is the single file requirement for runtime or for convenience? There are ways you could make it feel like a single file db... You could store your db files in a single tar file and then extract them at runtime, stuff them back in on program exit. This would give your code a very crude rollback functionality also.

      What about running against dbd::ram and then using something like dbd::anydata to dump the entire contents to a file periodically?

      Since you're going to take a performance hit with *any* sort of perl based db solution, you could just alter your design. Combine your tables into one big table and just query it for what you're looking for.
Re: Single file RDBMS w/o system install
by mattr (Curate) on Nov 13, 2001 at 18:45 UTC
    I wouldn't recommend DBD::CSV but DBD::RAM is supposed to be pretty wild from what I've seen. Haven't done any real world with it but it seems to try to do good things with the catalog mechanism and it's fast if you have RAM to feed it.

    Incidentally it's not like an Access MDB file plus Jet engine plus ODBC plus Windows is anything as simple or efficient as slurping a text file. Why not use the Berkeley system? It probably gives you plenty of power as an embedded object store without necessarily using SQL.

    The answer to an information retrieval strategy has a lot to do with what you are trying to do, how much it will be accessed, whether it is going to cost you money if it goes down or loses data, etc. That said maybe you'd like to rent yourself a relatively cheap account somewhere that will host your (small) mysql or maybe postgresql database for you.

    Move SIG!

Using DBD::CSV
by johanvdb (Beadle) on Nov 14, 2001 at 02:33 UTC
    I have to say, I'm happy with DBD::CSV. You can have SELECT/UPDATE/DELETE, CREATE/DELETE tables .... It uses the same SQL engine as DBD::RAM i.e. SQL-Statement. Although not as performant as a MySQL or Postgresql db, it really works well. DBD::RAM is really a RDBMS in RAM, ie exit your program and the data fades away ;-) Not soo good when you use the RDBMS to store data for future access ;-)

    Johan
      Actually no, DBD::RAM offers RAM only, RAM with write to disk on command, and continuous write, in addition to supporting a number of file types including CSV and having a catalog method, which makes a table-datatype-file association for continuous read/write like other drivers. That said, I do not have much realworld experience with the driver, just wanted to correct your summary of RAM.

      I have not used the latest DBD::CSV version either, so maybe it is better now. But I have had many problems with it in particular porting a web-based sql administration program from ordinary Perl DBI to work with DBD::CSV. The biggest problems I had were having to make separate files to hold the number of records (difficulty making a unique id for a row), blank lines and duplicate records cropping up forcing periodic manual editing of the csv file, and trouble with vertical tabs (embedded from excel) and binary data (Japanese). I'm sure I pushed it too hard when I used it so if you are not doing anything serious with it (i.e. you don't really need sql anyway) you're probably safe.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://124894]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others exploiting the Monastery: (8)
As of 2024-03-28 09:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found