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?
Another way I have discovered to back up the data is to look in the data folder for a folder with the same name as your database. You can copy that folder somewhere else and if you need to restore to that configuration simply delete the original folder and replace it with your copy.
Yes, it was I! Not only have I deleted all records by mistake, but I also edited all records so that they were all identical. One hundred copies of Popeye. What a ghastly thought! Thanks for the tips. I think I like SQL, now that I've monkeyed around with it for a bit. It actually almost seems to make sense! [ November 14, 2002: Message edited by: Carol Murphy ]