melp.nl

I have quite often seen people wrestle with easy problems. The trouble is, they don't really know the ins and outs of systems they work with. Very few times this was unresolvable by a bit more knowledge of how the software is used at a very basic, low level.

The most basic example of this is MySQL. Lots of people don't really learn SQL, they just get some GUI tool that does the "thinking" (mind the air-quotes) and generate all ALTER and CREATE queries for them in case of a structural change in the database. For productivity, this is fine, sure. However, if the availability of these tools is for some reason jeopardized, becoming somewhat helpless is inevitable.

I'm sometimes considered a bit of a geek for accessing my local MySQL databases through the command line MySQL tool. That black console just seems, short of a green font, "Matrixy". However, since I do, I know how to write a CREATE or ALTER statement, or even write patch scripts solely in MySQL. And this is where the advantage kicks in. Writing simple SQL patch files that can be loaded through a mysql command line interface, modifying the entire database to fit the code patches that are put live is a convenience, unsurpassed by any GUI tool I have ever seen.

So, I'd like give some pointers to get you started gaining the same experience, so you do not need to resort to patch scripts in another scripting language, unless you really have to.

Basic operations

I assume you know how to write select, update and delete statements, since you probably use them on a nearly daily basis. If you don't already master them, learn ALTER and CREATE. They are much simpler than you might think, considering they mostly consist of field and key definitions, which are really easy to master. I'll run into them soon.

The tricks

There are only four tricks you need to know to be able to write a lot of patch SQL without using any other tool or language:

• First is temporary tables. Temporary tables exists for the span of your connection. As soon as your connection is gone, the table is deleted. This is awesome, since you can now consider the table an "in-memory variable".
• Second is variables. You can have variables that contain basic scalar values (like numeric and string values).
• Third is INSERT INTO ... SELECT statements. You can insert values into a table by selecting values from a query.
• Last is a joined UPDATE. You can add JOINs to the table clause in an UPDATE statement to easily transfer data from one table in the clause to another.

Example

You need to copy a row from the category table. You know the name of the category you want to copy, not the ID, and you want to duplicate it.

#!sql
-- retrieve the ID of the row we want to copy
SELECT @copy_id:=id FROM category WHERE title="Copy me";

-- create a temporary table to temporarily store the values in
CREATE TEMPORARY TABLE tmp_category LIKE category;

-- remove the primary key to set it NULL, 
-- so it inserts as a new autoincrement value later
ALTER TABLE tmp_category 
    DROP PRIMARY KEY,
    CHANGE id id INT NULL;

-- copy the row into the temporary table
INSERT INTO tmp_category SELECT * FROM category WHERE id=@copy_id;

-- update the primary key to null so it inserts a new autoincrement
-- and mark the new category a copy in it's title
UPDATE tmp_category SET id=NULL, title=CONCAT(title, " (copy)");

-- copy the row back
INSERT INTO category SELECT * FROM tmp_category;

-- store the id of the new category
SELECT @new_id:=LAST_INSERT_ID();

Now we are going to change the structure to remove the n:1 reference in the product table to the category table, and put them in a link table, so n:m relations are now possible¹. We will put all the products of the first category in the new category too.

#!sql
-- create the new table
CREATE TABLE category_has_products (
    category_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    PRIMARY KEY(category_id, product_id)
);

-- copy the relations
INSERT INTO 
    category_has_products(category_id, product_id) 
SELECT 
    category_id, id 
FROM 
    product;

INSERT INTO
    category_has_products(category_id, product_id)
SELECT
    @new_id, id
FROM
    product
WHERE
    category_id=@copy_id;

Lastly, for the sake of the example, we are going to add a page_id field to the category table and let page_id's point to the page with the same title as the category. Let's just assume this is a sensible thing to do.

#!sql
ALTER TABLE category
    ADD page_id INT NULL AFTER category_id, 
    ADD KEY(page_id)
;
UPDATE
    category
        INNER JOIN page ON(
            page.title=category.title
        )
    SET
        category.page_id=page.page_id
;

Of course, the ON clause can contain other cool stuff, or you could use cross joins and write the conditions in a WHERE clause, whatever your preference.

Now, we can put this script in a patch file. I use a naming conventing where I put the current date, the name of the database and a short description of the script in the filename, and, if available, a bug ID from my issue tracker, so I'll call this one 2009.11.02-webshop-issue123.sql. It's a good habit so you can easily track back your patch SQL's. Of course, commit them to your version control system too.

I usually load a live dump into a development server and run the patch file as such:

#!shell
mysqldump -Q --opt my_db > 2009.11.02-local.sql
mysql my_db < ./2009.11.02-live.sql
mysql my_db < ./2009.11.02-webshop-issue123.sql

Get off the habit of tab-completion in a shell for these tasks, because there will be a time when you were sorry you knew tab-completion ;) Note the similarity in file names between the local and the live db dumps, for instance.

After testing your new local database, your patch script is ready to run live (or maybe at a staging server first, whatever you're used to), now you're good to go :)

Note I didn't drop the category_id column in the product table yet. It does no harm for now, and you should only drop it on your test and staging servers to track down possibly related bugs. You might consider just renaming it and dropping it after a few weeks.

Read the original post & comments at drm.tweakblogs.net.