Now we know how to open the database. SQL databases require tables. We need to create one if it does not already exist. Let’s go ahead and just make a subroutine to do this.
#expects a handle to a database connection, a name for a table, and an associative
#array of field names and field types passed as a reference
sub createTable {
my($dbHandle) = shift;
my($tableName) = shift;
my($tableFields) = shift;
my(@fieldCreators, $createQuery, $queryHandle, $fieldType, $fieldName);
@tables = $importHandle->tables();
if (!grep(/^"$dbTable"$/, @tables)) {
#construct query to create table
foreach $fieldName (keys %$tableFields) {
$fieldType = $$tableFields{$fieldName};
$fieldCreators[$#fieldCreators+1] = "$fieldName $fieldType";
}
$createQuery = "CREATE TABLE $tableName (" . join(", ", @fieldCreators) . ")";
#tell database to create the table
if ($queryHandle = $dbHandle->prepare($createQuery)) {
if (!$queryHandle->execute) {
print "Unable to execute $createQuery: $!\n";
}
} else {
print "Unable to prepare $createQuery: $!\n";
exit;
}
}
}
There are two sections to this subroutine. The first one creates the query that will create the table. The second one hands that query off to the database. When you use a SQL query in Perl, you usually hand it off in two steps: first, you prepare it on the handle. This creates a “statement handle” for the query statement. Then, you execute it from that statement handle. Since both of these handles are objects with methods, we use the handle->method syntax to prepare and to execute the query.
Pay close attention to the third parameter that this subroutine is expecting: it’s an associative array. We can’t pass associative arrays to subroutines in Perl. We can only pass scalar variables and lists of scalar variables. What we’ll need to do when we call this subroutine is pass that associative array by reference.
If you recall when we were sorting, Perl handed our sort subroutine associative arrays by hard reference. Well, we can hand hard references out to our subroutines, too.
Change the “$importHandle=” section of the switch for imports. We’ll call the createTable subroutine as soon as we open the database.
if ($importHandle = DBI->connect("dbi:SQLite:dbname=$importDB")) {
createTable($importHandle, $dbTable, \%dbFields);
} else {
print "Unable to open database $importDB: $!\n";
}
When you want to pass a variable as a hard reference, precede the variable name with a backslash.
We also need to create the %dbFields associative array. At the top, add the following defaults:
#name of table
$dbTable = "music";
#what kind of fields need to be in the database?
%dbFields = (
"ID"=>"INTEGER PRIMARY KEY AUTOINCREMENT",
"song"=>"TEXT",
"duration"=>"TEXT",
"artist"=>"TEXT",
"album"=>"TEXT",
"year"=>"INTEGER",
"rating"=>"INTEGER",
"ripdate"=>"TEXT",
"genre"=>"TEXT"
);
Note that we are putting this “single line” of Perl code onto several lines. Perl only cares about that semicolon. That’s how Perl sees the end of a line of code. Separating the associations into separate lines makes it easier for you, as programmer, to read them.
Also, this is the first time we’ve created an associative array by specifying the keys and values all at once. It is similar to how we create simple arrays, but each item is a key and a value, separated by “=>”. The key is on the left and the value is on the right.
Related Pages
