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

There was a node tonight I came across that mentioned DBD::DBM in regards to a more rebust system than just DB_File and SDBM_File but more usable for those who don't know MySQL/SQL. The node is Creating HTML tables.

I have very little experience with MySQL or any database other than the ones that came with my Perl package, so bare with me.

Using the example code from CPAN (see below) resulted in three files being created. test.pag, test.dir and test.lck. Then I created a new table and that created three more files prepended with "test1".

There must be some way to have the set of tabular data not each be their own separate files. If I have 100 tables, I can't handle 300 files in one directory just for the database to work.

From the code you can see I tried adding the extension ".db" as per the manual but that still resulted in the creation of three files per table.

When using my $dbh=DBI->connect('dbi:DBM:type=DB_File'); file instead, it still created three files but one of them didn't have an extension. So the question is, is there a way for this to create one or maybe two files total for the entire database or is this what to expect from DBD::DBM?

Second question is seems more of an SQL question but when you SELECT something from the table such as: 

SELECT phone FROM user WHERE user_name < 'Fred';

[download]
To store the results, you could use: 
my $var = SELECT phone FROM user WHERE user_name < 'Fred';

[download]
Right? Again, I have never really gotten into MySQL but this little DBI looks like it could come in handy so I don't have to continue split()ing all my values from my hashes.

Thanks for your help. 

 use DBI;
 my $dbh = DBI->connect('dbi:DBM:ext=.db'); 
 $dbh->{RaiseError} = 1;
 for my $sql( split /;\n+/,"
     CREATE TABLE test ( user_name TEXT, phone TEXT );
     INSERT INTO test VALUES ('Fred Bloggs','233-7777');
     INSERT INTO test VALUES ('Sanjay Patel','777-3333');
     INSERT INTO test VALUES ('Junk','xxx-xxxx');
     DELETE FROM test WHERE user_name = 'Junk';
     UPDATE test SET phone = '999-4444' WHERE user_name = 'Sanjay Pate
+l';
     SELECT * FROM test
 "){
     my $sth = $dbh->prepare($sql);
     $sth->execute;
     $sth->dump_results if $sth->{NUM_OF_FIELDS};
 }
 $dbh->disconnect;

[download]


"Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us"

sulfericacid

Replies are listed 'Best First'.
Re: DBD::DBM file creation
by aquarium (Curate) on Nov 29, 2004 at 12:11 UTC
    First of all: you're not using MySQL...you're using dbi::DBM. Or basically DBM file format with dbi, dbi being an abstraction layer that allows access to different kinds of "databases" with standard SQL. Secondly, if you tell the database to create table 100 times, it will create 100 tables, which will depend on the exact database as to how many actual system files it creates. It's good that you're hacking some code and trying to get it to do what you want -- but without a basic understanding of databases and their underlying concepts, you will be forever confused as to how you're supposed to store/retrieve the data. Don't mean to be mean or anything with what i said...but I do think that you'd seriously benefit from a basic database and dbi tutorial
    the hardest line to type correctly is: stty erase ^H
[reply]
      I agree with aquarium - my advice is to pick a database, get yourself a good beginners book for that database, and read. MySQL is a good database, and Paul Dubois wrote a good intro book called "MySQL", and I think there may(?) be a 2nd edition out but not sure about that. I learned MySQL using Paul's book - excellent book that takes you through the whole thing, including MySQL setup and authentication, and gives you an SQL primer too. The book pretty much assumes you are a database beginner.

      HTH.

[reply]
Re: DBD::DBM file creation
by jZed (Prior) on Nov 29, 2004 at 22:56 UTC
    There must be some way to have the set of tabular data not each be their own separate files.
    The .lck file is created by DBD::DBM, if you don't need locking, you can turn locking off and that file won't be created. The other two files are created by your underlying DBM implemnetation, some of those create only a single file per table and some create two files per table, that's how DBM works.
    If I have 100 tables, I can't handle 300 files in one directory just for the database to work.
    What's wrong with 300 files? The whole point of a database is that you shouldn't need to worry about how it physically stores the data so the fact that there are multiple files is really irrelevant. You will be using SQL to CREATE and DROP tables, the fact that creating and dropping happens to create multiple files, doesn't really impact the basic SQL operations. You will never actually handle the files as files, only as SQL tables, the same way you would any SQL table. But, in any case, if that bothers you, use DBD::SQLite which stores all tables for a given database in a single file.
    Second question is seems more of an SQL question but when you SELECT something from the table such as: SELECT phone FROM user WHERE user_name < 'Fred'; To store the results, you could use: my $var = SELECT phone FROM user WHERE user_name < 'Fred';
    Well, that's actually a DBI question. You can put the reuslts of a query in a variable with DBI's $dbh->selectrow_array, $dbh->selectall_arrayref, etc. or prepare and execute a statement handle and use a loop with $sth->fetch or $sth->fetchrow_harshref, etc.

    While I agree with the other posters that for most production work (but by no means all), a full RDBMS like PostgreSQL or MySQL is better than DBD::DBM, but since you appear to be in the early learning stages, DBD::DBM should work fine for you - you can learn basic SQL and DBI with it just fine.

    disclaimer I'm the author of DBD::DBM so I'm prejudiced. OTOH, the reason it is included in the DBI distribution is because it is sufficient for learning the basics of DBI access to databases.

[reply]
      I forgot to address your question about 300 files directly. What i meant to say was, if you learn the basics of databases, including how to design them properly by normalization, then you should end up with a set of database tables (a few system level files?) that can store/retrieve/represent a much larger set of HTML tables, as long as you can correctly abstract/normalize the common facts about those tables. The MySQL book is good....if you want to learn practical mysql (in perl) as well as getting your breakfast of database concepts and basic SQL.
      the hardest line to type correctly is: stty erase ^H
[reply]

Back to Seekers of Perl Wisdom