Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: have a script autmatically create a SQL database

by pmas (Hermit)
on Sep 08, 2001 at 09:03 UTC ( #111129=note: print w/replies, xml ) Need Help??


in reply to have a script autmatically create a SQL database

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.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (2)
As of 2022-05-19 02:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (71 votes). Check out past polls.

    Notices?