Dropping those pesky foreign keys

So, we have always been annoyed with dropping those MySQL foreign keys. They required manual SQL and some thinkin’ to delete. (You couldn’t just write a common DDL statement to do it that will not fail in some environments, since different environments may have different names for the key. Hibernate has tools to generate schemas, but the generated foreign key names are different than the ones developers used to seed the key originally.) All of this meant that Operations has to do manual SQL which takes time, isn’t automated, and has the risks of doing manual SQL in production.

We developers kept saying “wait until we upgrade to MySQL 5, we will be able to do it then!” Well, we upgraded a while back, and we finally got around to doing it.

Simple answer for youDevise developers:

When writing DB upgrades, just use the DropForeignKey class. It will do it for you, and has the SQL magic built-in.

Long answer for the curious:

We use some clever SQL to get it to work:

select @keytodrop := CONCAT(
"ALTER TABLE --table_name-- DROP FOREIGN KEY ", CONSTRAINT_NAME)
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_SCHEMA = (select database())
and TABLE_NAME='--table_name--' and COLUMN_NAME='--column_name--';

PREPARE stmt FROM @keytodrop;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Note: The –table_name– and –column_name– are inserted by the DropForeignKey class. If you want to run this by hand, you will have insert the names manually.

Dissecting this a bit, in the first SQL statement, we are setting the MySQL variable @keytodrop to the value of an alter table statement that will drop the foreign key.

We get the name of the foreign key by querying the INFORMATION_SCHEMA database. This INFORMATION_SCHEMA database is a “meta-database” of sorts that stores all of the database schema information about all of the other databases. We can query the metadata tables just like normal tables, and use it in further queries. (For those out there used to the USER_* tables in Oracle, then you will be right at home.) From this metadata, we can query the foreign key name by only knowing the table name and the column name that the foreign key is attached.

At this point, we have a MySQL variable that is holding the value of an alter table statement that will drop the foreign key. Now, how to run it?

On the MySQL variable page above, they mention all sorts of neat tricks to do with them like row numbers or temporary calculations. But in the article, they mention “Dynamic SQL” and suggest using the PREPARE, EXECUTE, and DEALLOCATE series of steps to run the variable content.

Now, for the YouDevise developers out there, this is all bundled into the DropForeignKey class, so you do not even have to think about it. Rest of the world: we can’t share our code on this blog, but you shouldn’t have much trouble writing a similar class once you have the nifty SQL above.

One thing to keep in mind is that this is very hard-coded to MySQL 5, so don’t go switchin’ your local DB to Oracle or anything.

Arc on Windows for Web Apps

Paul Graham is one of the two bloggers whose posts I read regularly – an eccentric venture capitalist, successful entrepreneur, hacker, and painter.

Paul’s been working a new language called Arc for a long time, claiming it will be a powerful variant of Lisp suitable for web applications – this surely has nothing to do with one of his companies, Reddit, rewriting their app in Python when they found Lisp didn’t have scalable HTTP libraries. After running his news site on Arc for a long time as a sort of soak test, he’s finally released an alpha version.

Since it’s always a good idea to try new languages (and I have a personal web-application itch to scratch), I thought I’d try Arc on my long commute. But since Paul runs Unix and I run Windows, I had a few challenges. To help others who might want to try it, here’s what I did to run Arc on Windows, including steps to execute a simple web application from within it.

(N.B.: Some steps, particularly Cygwin and copying the [mzscheme]\collects folder, might not be necessary – I didn’t test for a minimal installation method.)

MzScheme
1. Visit http://download.plt-scheme.org/mzscheme/. Click [other versions] and get version 352.
2. Run the installer. By default, it will install MzScheme in C:\Program Files\MzScheme, but put it anywhere you like. We’ll refer to the installation folder as [mzscheme] hereafter.
3. Run [mzscheme]\Setup PLT.exe.
4. In the output, look for the line
setup-plt: C:\[some-directories]\Application Data\PLT Scheme\352\collects
This folder didn’t exist for me; I created it and copied [mzscheme]\collects\* to it.

Cygwin
1. Visit http://www.cygwin.com/ and follow instructions to install Cygwin. By default, it will install in C:\Cygwin; we’ll call the install directory [cygwin].

OpenSSL
1. Visit http://www.openssl.org/related/binaries.html and follow instructions to install OpenSSL. By default, it’s installed in C:\OpenSSL; we’ll call the install directory [openssl].

Path
1. Add [mzscheme];[openssl]\bin;[cygwin]\bin to your Windows PATH environment variable. (Click [Start], then right-click My Computer and click Properties, then click Advanced, then click Environment Variables, then choose Path and click [Edit]. We love Bill!)
2. To verify this worked, type these commands in a DOS prompt – you should get sensible output from each:
mzscheme -v
openssl version
cygcheck -V

Arc
1. Create arc directory in a convenient place, hereafter [arc].
2. Visit http://ycombinator.com/arc/arc0.tar and save the file in [arc].
3. Untar the file (use 7Zip if you don’t already have it). You now have a folder [arc]\arc0, hereafter [arc0].
4. Create folders C:\tmp, [arc0]\arc, [arc0]\arc\posts, and [arc0]\arc\logs.
5. Create batch file [arc0]\arc.bat:

@echo off
mzscheme.exe -m -d as.scm

Run Arc
1. Double-click [arc0]\arc.bat.
2. Type "hello world"
3. Type (defop hello req (pr "hello world")), then (asv).
4. Visit http://localhost:8080/hello. You should get an exciting web page. (Strangely, it always seems to hang on first visit for me. But subsequent visits work fine.)
5. Type Ctrl-C to stop the server, then double-click [arc0]\arc.bat again. (There may be a cleaner way to halt an Arc webserver, but I haven’t found it yet.)
6. Type (load "blog.arc"), then (bsv).
7. Visit http://localhost:8080/blog to start using the blog.

Thanks to these posts from which the instructions above are drawn:
http://lispsamik.blogspot.com/2008/01/arc-in-windows.html
http://arclanguage.org/item?id=160
http://arclanguage.org/item?id=190