Importing the data

Importing the data requires two steps. First we must create the table in the MySQL database. Then we must move the data itself from the text file into the MySQL table.

1. Creating the table

The code to create the table is found in the file rom-sql.sql. When a text file has SQL commands and has the file extension ".sql," you can use the source statement in the MySQL monitor program to execute the code in that file. The rom-sql.sql file also has comments that show what the database should like like when we are done. Here are some excerpts from that file:

create table gen44_rom (
    ID int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    DanceName varchar(255),
    Rhythm varchar(64),
    Phase varchar(16),
    Choreographer varchar (64),
    Label varchar(64),
    Record varchar (32),
    Year int,
    MonthNum int,
    Month varchar (16),
    Extra varchar (255));

mysql> describe gen44_rom;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| ID            | int(11)      | NO   | PRI | NULL    | auto_increment |
| DanceName     | varchar(255) | YES  |     | NULL    |                |
| Rhythm        | varchar(64)  | YES  |     | NULL    |                |
| Phase         | varchar(16)  | YES  |     | NULL    |                |
| Choreographer | varchar(64)  | YES  |     | NULL    |                |
| Label         | varchar(64)  | YES  |     | NULL    |                |
| Record        | varchar(32)  | YES  |     | NULL    |                |
| Year          | int(11)      | YES  |     | NULL    |                |
| MonthNum      | int(11)      | YES  |     | NULL    |                |
| Month         | varchar(16)  | YES  |     | NULL    |                |
| Extra         | varchar(255) | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

2. Importing the data

Then I have a PERL program caled setup.pl that reads the various text files that have information encoded as shown on the ROMs page. This program runs from the command line. It extracts the data from the text files, and inserts the data into the database like this:

  for ($i=0; $i<@src; $i++) 
  {
    if ($src[$i] eq "%\n") 
    {
      $choreo = $src[$i+1];
      $name = $src[$i+2];
      $label = $src[$i+3];
      $record = $src[$i+4];
      $rhythm = $src[$i+5];
      $phase = $src[$i+6];
      $extra = $src[$i+8];
      $monthyear = $src[$i+7];
      @monthyear = split (/ /, $monthyear);
      $month = $monthyear[0];
      $year = $monthyear[1];
      $month = substr ($month, 0, 3);
      $monthNumber = 1 + find_month ($month);

      my $sth = $dbh->prepare ("INSERT INTO gen44_rom values (
        '', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");

      $sth->execute ($name, $rhythm, $phase, $choreo, $label, 
        $record, $year, $monthNumber, $month, $extra);
    }
  }

(This is an abbreviated version of the code. There are lots of extra checks in there like chop to get rid of extra line feeds, trim to get rid of extra spaces, checks for empty form fields, etc.)

Note that I extract the month, which is encoded as a text string like "May" or "Sept" or "September," but I re-encode the month also as a number between 1 and 12, so we could use that field to sort the MySQL output using ORDER BY Year DESC, MonthNum DESC or other similar mechanisms. (Membership usually wants to see the dances displayed starting with the most recent entries.)