have a script autmatically create a SQL database

[lwilliams]

I'm sorry about all the DBI-related questions. I'm working on a major addon for a popular news script and I've got yet another question for you Perl sages. I want the script to automatically create a database if it doesn't already exist. I don't know how to do this. By the way, the name of the DB i want to create will be contained in the variable, $database_name. Example code please!!

[blakem]

Here is a snippet that creates a *mysql* database:

sub create_tables {
    # set $dbdriver,$mysqldb,$mysqluser,$mysqlpwd
    # [SNIP]

    # Make the new database
    my $drh = DBI->install_driver($dbdriver);
    my $rc = $drh->func("createdb", $mysqldb, 'localhost', $mysqluser,
                        $mysqlpwd, 'admin');
    # Make some tables
    # [SNIP]
}

Does that help?

-Blake

[lwilliams]

What will happen if the DB already exists??

[MadraghRua]

You might want to invest in Programming the Perl DBI by Descartes and Bunce - you can get it quickly and cheaply from Bookpool.com. I suggest you look at Chapter 3, starting around page 75. This section covers creating tables. Otherwise you might want to read the Perl DBI documentation.

Concerning creation of the database, you might have to interact with something like sqlplus in Oracle or mysqladmin in MySQL to actually create the database - I'm not aware of a create_database utility that can be accessed in DBI. You can create tables in the databse once you have created the database though.

MadraghRua
yet another biologist hacking perl....

[zdog]

You may want to take a look at the DBIx::DataSource module for creating DBs. Example code is included in the perldoc free of charge :-)

Zenon Zabinski | zdog | [e-mail]

[pmas]

Creating new tables is database specific, although DBI allows you to send through any database-specific commands directly to driver.

I am guessing if you want to create many tables, all these tables will be similar, right? Maybe you can create one meta-table, with fields like TBLNAME, KEY, CHARFLD1, CHARFLD2, ... NUMFLD1, NUMFLD2..., TXTFLD1, TXTFLD2... etc. Then, instead of creating table, you need just insert rows into meta-table. This way you will have new table implemented as "SELECT * FROM meta-table WHERE TBLNAME = 'mynewtable'". I know that proposed implementation might be half-empty meta-table (often most of the fields will not be used), but OTOH you can add "table" dynamically.

We used this approach once and it was very successfull. We have database which was very touchy when DB stucture changed - all code required recompilation. Using our approach we avoided a lot of pain. We put more that hundred small simple tables (CODE DESCRIPTION) and were able to add new features into our code which usually reqired changes of structure rather easy.

Here is how to optimize this approach: (1) you need index by TBLNAME and KEY. (2) if you want to avoid many empty fields, you may put all remaining fields (after TABLENAME and KEY) into one long text field as CSV (comma-separated values), packing and unpacking them as needed.

Obviously, meta-table approach is valid if you need to store hundreds small tables (between dozen and couple hundred rows each max), if you want to have hundreds of thousands of rows, it deserves special table.

I hope it makes sense, it's too late and I am sleepy like a dog now...If you need more info, /msg or reply here.

pmas
To make errors is human. But to make million errors per second, you need a computer.

[seesik]

although you CAN create a database w/ perl, it'd be dbms-specific, and is generally not a good idea. the whole concept of a single 'database' instance varies among dbms, as does the creation procedure. like blakem demonstrated, you could create a mysql database w/ relative ease. an oracle instance would involve a svrmgrl spawn, tnsnames/listener configuration, ad nauseum.

in summary, i can appreciate your desire to automate as much as possible, but IMO, you need to draw the line somewhere. if you're only deploying to mysql, then you're relatively safe. if you intend to port to other dbms, make having an existing instance/db a prerequisite. now auto-creating schema... that's a different story ;)

btw, the dbi questions are perfectly legit..

cheers

[princepawn]

you can also use Alzabo to do this, but it is a very large, powerful and necessarily complex tool.