If you have not used DBI before then this article by MJD is a good place to start.

You would proceed along these lines. To begin create the database and connect to it. Now create the "TableIndex" table once only. 

CREATE TABLE TableIndex
(
    table_date    date,
    table_id      integer
)

[download]

Each day you add an entry to the "TableIndex" table that consists of the date and a unique name "table_id" for the days data table (the concatenated date would do or an MD5 hash or an incremented integer - whatever). Now create a new table called "table_id" and insert your days data. Each day you will add one record to the TableIndex table and create one new table in the database. This should get you started: 

my ($dbh,$sth,$sql);

# connect to database
$dbh = DBI->connect('DBI:Oracle:mydata')
    or die "Couldn't connect to database: " . DBI->errstr;

# do some stuff to get data into $date and $table_id

# insert the days table date and table_id into the index
$sql = 'INSERT INTO TableIndex VALUES (?,?)';
$sth = $dbh->prepare($sql)
    or die "Couldn't prepare SQL\n$sql\n" . $dbh->errstr;
$sth->execute($date,$table_id)
    or die "Couldn't execute SQL\n$sql\n" . $dbh->errstr;
$sth->finish;

# create a new table to hold the days data
$sql =<<SQL;
CREATE TABLE "$table_id" 
(
    data1    varchar(10),
    data2    varchar(10)
)
SQL;
# now execute this sql to create the table

# insert the data into the new table
$sql = qq/INSERT INTO "$table_id" VALUES (?,?)/;
# now insert your data...etc

[download]

You can now access you data by date by quering the "TableIndex" table for the table id(s) you are interested in and then quering that/those table(s)

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

In reply to Re: Database setup for daily db by tachyon
in thread Database setup for daily db by perleager

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.