mysqlsqitch

Verify table does NOT exist with mysql query in sqitch

I'm using sqitch to track database changes, and one of the ways it works is to verify changes with a sql query. If the query returns an error then the change is considered unsuccessful.

I have a change where I am permanently dropping a table from my mysql database. To verify the deployment my verification needs to return an error if the table exists. While it's straightforward to return an error if a table doesn't exist, how do I get mysql to return an error only when a specific table does exist?

Solution

  • Use the checkit function that comes with Sqitch, as described in the MySQL tutorial:

    SELECT checkit(COUNT(*) = 0, 'Table "foo" exists but should not')
  FROM information_schema.tables
 WHERE table_schema = 'your_db'
   AND table_name = 'dropped_table';