Last Updated: February 25, 2016
·
338
· desertlynx

Don't nuke important tables while developing

If you're like me and do continuous integration to a production server, some of the tables on the server need to be preserved during deployment. Others need also to be replaced.

Rather than running the sql either directly, or via make or whatever, I usually throw up a quick display of what the SQL file being used will do. This can be accomplished with a trivial bash one-liner.

This way I can continue about developing the database without worrying about having to keep too much track of what is already in the build.

Bash executed in makefile:

#!/bin/bash
echo This will drop and recreate the following tables:
echo "============================================="
grep 'create ' -i  example.sql | cut -f3 -d' '
echo enter the mysql password
read P 
mysql -u root -p$P database < example.sql

Example SQL:

-- {{{  Users roles 
drop table if exists roles;
CREATE TABLE roles (
     roleid int not null primary key auto_increment,
     name varchar(100) not null, 
     created int, 
     modified int
);
-- }}}
-- {{{  Users - Actions
drop table if exists actions;
CREATE TABLE actions (
     actionid int not null primary key auto_increment,
     action varchar(100) not null, 
     description TEXT not null,
     created int, 
     modified int
);
-- }}} 

Example output

This will drop and recreate the following tables 
=============================================
roles
actions
=============================================