Hi,

I've started a project which is more of a proof of concept than anything. It involves pushing stats from a backup server into a MySQL database. The database will then be used to create certain reports.

I've settled on a schema that make sense and I am ready to begin creating the database. This is my first stab at any kind of databse programming, so my question might seem a bit newby-ish.

My question is, is there a way that I can programmatically create my tables from hash keys? I hope so, since doing this manually would take several hours!

Here's example content of one of the hashes that I will use: 

$VAR435 = 273118;
$VAR436 = {
             'try_1_server' => 'netbackup2',
             'jobtype' => 'immediate',
             'try' => 1,
             'group' => '',
             'subtype' => 1,
             'try_1_ended' => '1031010812',
             'try_1_statuscount' => 12,
             'filelistcount' => 1,
             'status' => '0',
             'try_1_stunit' => 'netbackup2_stu',
             'try_1_statusdescription' => 'the requested operation was
+ 
successfully completed',
             'schedule_type' => 4,
             'percent' => '',
             'trycount' => 1,
             'try_1_kbyteswritten' => 1,
             'operation' => '',
             'retentionunits' => 3,
             'files' => 'D:\\',
             'try_1_started' => '1031010638',
             'try_1_elapsed' => 174,
             'masterserver' => 'netbackup1',
             'jobid' => 273118,
             'try_1_pid' => 13032,
             'kbyteslastwritten' => '0',
             'path' => '',
             'fileslastwritten' => '0',
             'try_1_status' => '0',
             'client' => 'esmem',
             'server' => 'netbackup2',
             'jobpid' => 13032,
             'started' => '1031008015',
             'class' => 'NT-Main',
             'elapsed' => 2797,
             'stunit' => 'netbackup2_stu',
             'state' => 'done',
             'retentionperiod' => 1,
             'classtype' => 13,
             'try_1_statuslines' => '09/02/2002 19:50:40 - connecting
09/02/2002 19:50:40 - connected; connect time: 000:00:00
09/02/2002 19:50:40 - mounting CC0530
09/02/2002 19:50:40 - mounted; mount time: 000:00:00
09/02/2002 19:50:40 - positioning to file 138
09/02/2002 19:50:40 - positioned; position time: 000:00:00
09/02/2002 19:50:40 - begin writing
09/02/2002 19:50:40 - positioning to file 139
09/02/2002 19:50:40 - positioned; position time: 000:00:00
09/02/2002 19:51:30 - positioning to file 140
09/02/2002 19:51:30 - positioned; position time: 000:00:00
09/02/2002 19:53:20 - end writing; write time: 000:02:48',
             'ended' => '1031010812',
             'schedule' => 'NT-Differentials',
             'try_1_fileswritten' => '0',
             'kbytes' => 1,
             'owner' => 'root',
             'compression' => '0',
             'priority' => 10
           };

[download]
In this case, the outer most hash key would be the primary key, named 'jobid', the inner most keys would be column names, and the inner values would be that data to be pushed into each table row.

The other problem I just realized as I'm typing this, is that if you notice that there are several entries which refer to "no. of tries". There could be as many as 5 tries per jobid. I suppose that could simply create 5 'try_n' tables, all using 'jobid' as their primary key, no?

Again, this is a first go around for me, so if I've left any pertinent info out or I'm not being clear, please ask for clarification

Edit kudra, 2002-09-05 Added a readmore tag

In reply to MySQL Table Creation using DBI by blink

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.