تحويلات قاعدة البيانات وتدفق git

مطور
7

@pfaffman although it’s not a very common approach, one can use database migrations not only for schema changes but for content editing as well. I’m a big fan of that, using ansible for almost everything, so I can script and automate instead of click my way through a web interface.

@mschekotov for this approach you don’t need to use Ruby specifically. You will be using this next to (or on top of) the Discourse setup, you’ll just be writing to the same database. If you’re familiar with any other kind of programming language or deployment system, just use that.

Below is something simple that I sometimes use. It’s just a bash script with a subdirectory called migrations. All you need to do is fill the migrations subdirectory with files and run the script. Or check it into git and use it on your production server :slight_smile:

Rules (as with any migrations concept)

  • to maintain the correct order of excecution, name your files YYYYMMDDHHMM-description.sql
  • never ever modify an existing file after it has been checked in and deployed, always create a new migration

It uses mysql to store the migrations. You can even keep it like that, or change it to postgresql. If you use the same database as your Discourse installation, make sure you use a table name that will never be used in Discourse. If you don’t use the same database or database middleware, make sure you flush it whenever you reinstall your Discourse.

#!/bin/bash
mysql << EOF

CREATE DATABASE IF NOT EXISTS meta DEFAULT CHARACTER SET=utf8;

CREATE TABLE IF NOT EXISTS meta.migrations (
  migration varchar(255) NOT NULL,
  executed_at datetime NOT NULL,
  UNIQUE KEY migration (migration)
) DEFAULT CHARSET=utf8 ;

EOF

FILES=$(ls migrations/*.sql | sort)
for FILE in $FILES
do
    NUM=$(echo "SELECT migration FROM meta.migrations WHERE migration='$FILE';"  | mysql -N|wc -l)
    if [ "$NUM" == "0" ] 
    then
        echo "Running migration $FILE"
        LOG=log/$(basename $FILE).log
        echo "Running migration $FILE @ $DATE" > $LOG
        mysql -v < $FILE  >> $LOG 2> >(tee -a $LOG >&2)
        if [ $? -eq 0 ] 
        then
           echo "INSERT INTO meta.migrations VALUES('$FILE', NOW());" | mysql
        else 
           exit $?
        fi
    fi
done