Okay, we’ve opened the database and we’ve created the table. Now we need to insert data into the table. It is time to add functionality to our --import switch.
First, let’s add a subroutine called “insertRow”. This subroutine will be similar to the “createTable” subroutine, but it will insert rows of data. We’ll pass it the same data: a handle to the database, the name of the table that will receive the data, and the associative array of fieldnames as a hard reference.
This subroutine will also expect that the field names in that associative array match an existing scalar variable with the same name. So if there is a field named “artist”, this subroutine will look in $artist for that field’s value.
#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
#also expects that the field names match a currently-existing scalar variable
sub insertRow {
my($dbHandle) = shift;
my($tableName) = shift;
my($tableFields) = shift;
my(@fieldNames, @fieldValues, $fieldNames, $fieldValues, $fieldValue);
my($insertQuery, $queryHandle);
#create the field list and value list
@fieldNames = keys %$tableFields;
foreach $fieldName (@fieldNames) {
$fieldValue = $$fieldName;
#set $fieldValue to an appropriate SQL value
if ($fieldValue eq "") {
$fieldValue = NULL;
} else {
$fieldValue = '$dbHandle->quote($$fieldname);
}
$fieldValues[$#fieldValues+1] = $fieldValue;
}
$fieldNames = join(", ", @fieldNames);
$fieldValues = join(", ", @fieldValues);
$insertQuery = "INSERT INTO $tableName ($fieldNames) VALUES ($fieldValues)";
#insert into the database
doQuery($dbHandle, $insertQuery);
}
If you aren’t familiar with SQL, this will create queries such as “INSERT INTO music (artist, album, year) VALUES ("Foreigner", "4", NULL);”. If a field doesn’t have a value—if it equals the empty string ""—then we set the value to NULL. Otherwise, we tell the database to correctly quote this piece of text. We need to do this because SQL requires text to be surrounded by quotes. But some text contains quotes. The quote method on the database handle understands this, and fixes the text accordingly.
Once we have the query created, executing the query is the same as executing the CREATE TABLE query. Rather than have the same code duplicated for each kind of query, we can make a subroutine to handle it. The subroutine will need the database handle and the query.
sub doQuery {
my($dbHandle) = shift;
my($query) = shift;
if ($queryHandle = $dbHandle->prepare($query)) {
if (!$queryHandle->execute) {
print "Unable to execute $query: $!\n";
}
} else {
print "Unable to prepare $query: $!\n";
exit;
}
}
You should go replace the “if $queryHandle =” section of the createTable subroutine so that it calls
#tell database to create the table
doQuery($dbHandle, $createQuery);
Now would be a good time to try “./show” with no parameters to see if you have any errors. It should just give you the help message.
Finally, we need to call this insertRow subroutine for every matching row. At the end of the “if ($matched) {” section, add:
#import into a database?
if ($importDB) {
insertRow($importHandle, $dbTable, \%dbFields);
}
Let’s see if we can make a database of love songs:
./show --song love --import LoveSongs songs.txt
This works, but it is slow. SQLite can be much faster than this. What we need to tell it to do is to wait until we’re done feeding it rows before it writes them all out to the disk. After the createTable call in our switches area, add:
$importHandle->{AutoCommit} = 0;
This tells SQLite not to automatically write rows out to the disk as soon as it receives them. We’ll need to explicitly tell it when to “commit” our changes. Just in front of “if (%artists) {” and just at the end of the loop through all lines, add:
#commit insertions if we are importing into a database
$importHandle->commit;
Go ahead and remove the file LoveSongs, and run that again:
./show --song love --import LoveSongs songs.txt
It should go much faster.
How do you know the data has been inserted? If you have sqlite3 on your system, you can type “sqlite3 databasename” to open the database by hand. Then, type:
SELECT * FROM music;
You should see a long list of all of the data that’s been imported. Use “.quit” to exit.
If you don’t have sqlite3 installed on your system, you’ll just have to wait until the next section.
Related Pages
