Some time ago I think it was Carol Murphy who accidentally deleted the contents of her videos table full of
test data for the
JDBC assignment. I remember saying at the time that I'd post some tips for working with with SQL/MySQL/databases. Weeks (months?) later, here's the kind of post I had in mind.
When working on the JDBC assignments, I'm sure each of us has a set of test cases we want in our data (probably hashed out during the
servlets assignments). I have in mind things like making sure to have a video for each of the types (action, drama, etc.) or making sure to have a "Y" and "N" each for VHS and DVD fields and so forth. Sometimes the point of the testing is to actually insert these test cases using the servlet. But other times the point is just to display the base set of test cases (or make them available for update or delete). Also, when you're developing code that inserts into a database, there's always the chance that you've inserted some bad data. Subsequent rewrites of your code shouldn't have to deal with bad data that never should have been created to begin with. And sometimes getting rid of bad data while keeping good test data can be tricky.
In these cases it can be very convenient to have a quick and dirty way of restoring your test data to a known good state.
The technique I'm proposing is to maintain basic test data in a separate table and to refresh the "real" videos table with that basic test data whenever necessary. (I believe you can also shuttle data to and from an external file in MySQL, but I haven't investigated the commands for doing this and using a table for this is pretty handy anyway.)
The "real" table that we create for the JDBC assignment is called "videos". Use
insert into ... values ... (and maybe even some
delete from ... where ...) statements to populate videos with your basic test cases if it's not already in that state.
Next we want to create a table with an identical structure to videos and populate that table with all the data in videos. We've all used the basic SQL
create table command to create the videos table in the first place, but there's a handier version of create that suits our immediate purpose better:
create table videos_data select * from videos ;
This version of the create statement (
create table ... select ...) creates a new table (in our case called "videos_data"), but instead of our having to specify the field names, types, and sizes, we supply a select statement and it creates a table with a column for every field listed in the select list. (Remember that "*" in SQL means "all columns.") And it also populates the new table with the values returned by the select statement. So in one simple statement we've copied the structure and contents of videos into videos_data, which we'll use to hold our basic test cases.
Now pretend we've written some more servlet code and tested and made a royal mess out of the data in videos.
Let's get it back to how it was when we started -- full of our basic test cases.
We could
recreate videos based on videos_data, by first dropping the table then using the
create table ... select ... statement again but with the table names reversed, but I don't like the idea of constantly dropping and recreating our videos table. Instead let's just delete all the data from videos and repopulate it with the data from videos_data.
Deleting all the data from videos is easy enough:
delete from videos ;
Now in the past we've always used
insert into ... values ... to insert individual rows into a table. Is there an SQL command for doing bulk inserts? Yes, of course. It's
insert into ... select ..., which lets us insert into one table whatever we select from another table (or tables). In our case, it's simply:
insert into videos select * from videos_data ;
(We can use "*" in the select because the columns in videos and videos_data match.)
And there we have it. Videos is back to how we want it -- full of clean test data.
(Note that since the defaul behavior of MySQL is autocommit, we don't have to issue "commit" statements after updates, inserts, or deletes.)
Now after you've used this technique a handful of times, you start to wonder since you're issuing the same two commands over and over whether there's a way to further automate this. (I wouldn't have brought it up it there wasn't...)
So let's create a file, call it "reset.sql" and put it somewhere convenient. It should have our two commands as the contents:
delete from videos ;
insert into videos select * from videos_data ;
We can execute this file from the "SQL>" prompt two different ways: we can use the "source" command plus the filename or "\." plus the filename.
mysql> source c:\Michael\mysql\reset.sql
mysql> \. c:\Michael\mysql\reset.sql
(Of course the "mysql> " is there already so you don't type that part, and substitute your own path and filename.)
Fun stuff, no?