Super Soft Cafe

Title says it all. I have googled myself to death and not found a concrete answer to this one. There are two answers.

This of course is easy, just drop and re-create the schema. This is really simple and works, but I’m a determined person and wanted a solution that didn’t involve dropping the schema, or even knowledge of which schema is the default for the authenticated user. No, I wanted something that just worked given an open DB connection.

The approach I went for has three distinct phases:

1. Discover the current schema from PROCESSLIST.
2. Discover foreign keys, and drop by executing generated statements.
3. Discover tables, and drop by executing generated statements.

So here it is. Enjoy. Comment. Reproduce…   No, I mean the code!!!  Yeash.

    <target name=”-java-deploy”>
        <sql driver=”com.mysql.jdbc.Driver”
             url=”jdbc:mysql://localhost:3306/jdo”
             userid=”username”
             password=”password”
             print=”true” delimiter=”$”>
            <classpath>
                <pathelement path=”${javac.classpath}”/>
                <pathelement location=”${build.classes.dir}”/>
            </classpath>
            <transaction>
DROP PROCEDURE IF EXISTS `drop_tables` $
CREATE PROCEDURE `drop_tables` ()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE sn CHAR(255);
  DECLARE tn CHAR(255);
  DECLARE cn CHAR(255);
  DECLARE cur1 CURSOR FOR SELECT table_name, constraint_name FROM information_schema.table_constraints t WHERE t.constraint_type=”FOREIGN KEY” AND t.table_schema=sn;
  DECLARE cur2 CURSOR FOR SELECT table_name FROM information_schema.`tables` t WHERE t.table_schema=sn;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SELECT db INTO sn FROM information_schema.`PROCESSLIST` p WHERE p.info like ‘%select db from information_schema%’;

  OPEN cur1;
  REPEAT
    FETCH cur1 INTO tn, cn;
    IF NOT done THEN
      SET @stmt = CONCAT(’ALTER TABLE `’, sn, ‘`.`’, tn, ‘` DROP FOREIGN KEY `’, cn, ‘`’);

    	PREPARE pstmt FROM @stmt;
    	EXECUTE pstmt;
    	DEALLOCATE PREPARE pstmt;

    END IF;
  UNTIL done END REPEAT;
  CLOSE cur1;

  SET done = 0;
  OPEN cur2;
  REPEAT
    FETCH cur2 INTO tn;
    IF NOT done THEN
      SET @stmt = CONCAT(’DROP TABLE `’, tn, ‘`’);

    	PREPARE pstmt FROM @stmt;
    	EXECUTE pstmt;
    	DEALLOCATE PREPARE pstmt;

    END IF;
  UNTIL done END REPEAT;
  CLOSE cur2;
END $$
CALL drop_tables() $$
DROP PROCEDURE IF EXISTS `drop_tables` $$
            </transaction>
        </sql>
    </target>

This entry was posted on Saturday, June 27th, 2009 at 10:56 pm and is filed under computing, java. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.