PK m?>|9F F commands.pyimport sys
import inspect
import os
import glob
import re
import subprocess
try:
from play.utils import *
except:
pass
MODULE = 'migrate'
COMMANDS = ['migrate','migrate:help','migrate:init','migrate:up','migrate:version','migrate:drop-rebuild','migrate:create','migrate:drop']
app = None
# Migrate - database migration and creation
def getFormatString():
return app.readConf('migrate.module.file.format').replace("[[eq]]","=")
def getDbArg():
grab_next = False
for arg in sys.argv:
if arg.strip() == "--db":
grab_next = True
elif arg.startswith("--db=") or grab_next == True:
spec_db = arg.replace("--db","")
print "~ Processing specified database: " + spec_db
return spec_db
return None
# ~~~~~~~~~~~~~~~~~~~~~~ getUpToVersion() is to look up the desired migration version number, to which we'd like to migrate
def getUpToVersion():
grab_next = False
for arg in sys.argv:
if arg.strip() == "--to":
grab_next = True
elif arg.startswith("--to=") or grab_next == True:
try:
to_version = int(arg.replace("--to=",""))
print "~ Migrating to version: %(tv)s" % {'tv': to_version}
return to_version
except TypeError:
print "~ ERROR: unable to parse --to argument: '%(ta)s'" % { 'ta': arg }
return None
return None
# ~~~~~~~~~~~~~~~~~~~~~~ getVersion(dbname) is to look up the version number of the database
def getVersion(dbname):
[tmp_path,f] = createTempFile('migrate.module/check_version.sql')
f.write("select %(version)s, %(status)s from patchlevel" %{ 'version':"version", 'status': "status" })
f.close()
# The format string for running commands through a file
db_format_string = getFormatString()
command_strings = getCommandStrings()
command_strings['filename'] = tmp_path
command_strings['dbname'] = dbname
db_cmd = db_format_string % command_strings
[code, response] = runDBCommand(db_cmd)
if code <> 0:
print "Failure " + response
sys.exit(-1)
parts = response.split()
return [parts[0]," ".join(parts[1:])]
# ~~~~~~~~~~~~~~~~~~~~~~ updateVersionTo(dbname,version) updates the version number in the passed database
def updateVersionTo(dbname,version):
[tmp_path,f] = createTempFile('migrate.module/update_version.sql')
f.write("update patchlevel set version = %(version)s, status = '%(status)s'" %{ 'version':version, 'status': "Successful" })
f.close()
# The format string for running commands through a file
db_format_string = getFormatString()
command_strings = getCommandStrings()
command_strings['filename'] = tmp_path
command_strings['dbname'] = dbname
db_cmd = db_format_string % command_strings
[code, response] = runDBCommand(db_cmd)
if code <> 0:
print "~ ERROR updating version number: "
print " " + response
sys.exit(-1)
# ~~~~~~~~~~~~~~~~~~~~~~ updateStatusTo(dbname,status) updates the status in the passed database
def updateStatusTo(dbname,status):
[tmp_path,f] = createTempFile('migrate.module/update_status.sql')
f.write("update patchlevel set status = '%(status)s'" %{'status': status })
f.close()
# The format string for running commands through a file
db_format_string = getFormatString()
command_strings = getCommandStrings()
command_strings['filename'] = tmp_path
command_strings['dbname'] = dbname
db_cmd = db_format_string % command_strings
[code, response] = runDBCommand(db_cmd)
if code <> 0:
print "~ ERROR updating status: "
print "~ " + response
sys.exit(-1)
# Constructs a temporary file for use in running SQL commands
def createTempFile(relative_path):
tmp_path = os.path.normpath(os.path.join(app.path, 'tmp/' + relative_path))
pathdir = os.path.dirname(tmp_path)
if not os.path.exists(pathdir):
os.makedirs(pathdir)
return [tmp_path, open(tmp_path,'w')]
# ~~~~~~~~~~~~~~~~~~~~~~ getCommandStrings() retrieves the command parameters for running a DB command from the command line
def getCommandStrings():
db_create_user = app.readConf('migrate.module.username')
db_create_pwd = app.readConf('migrate.module.password')
db_port = app.readConf('migrate.module.port')
db_host = app.readConf('migrate.module.host')
return {'username': db_create_user, 'password': db_create_pwd, \
'host': db_host, 'port': db_port, 'filename': "", 'dbname': "" }
# ~~~~~~~~~~~~~~~~~~~~~ Runs the specified command, returning the returncode and the text (if any)
def runDBCommand(command):
returncode = None
line = ""
try:
create_process = subprocess.Popen(command, env=os.environ, shell=True, stderr=subprocess.STDOUT, stdout=subprocess.PIPE)
while True:
returncode = create_process.poll()
line += create_process.stdout.readline()
if returncode != None:
break;
except OSError:
print "Could not execute the database create script: "
print " " + command
returncode = -1
return [returncode,line]
# ~~~~~~~~~~~~~~~~~~~~~~ Retrieves the list of migration files for the specified database. The files live in the
# ~~~~~~~~~~~~~~~~~~~~~~ {playapp}/db/migrate/{dbname} folder and follow a naming convention: {number}.{up|down}.{whatever}.sql
def getMigrateFiles(dbname, exclude_before):
search_path = os.path.join(app.path, 'db/migrate/',dbname + '/*.up*.sql')
initial_list = glob.glob(search_path)
return_obj = {}
collisions = []
# Filter the list to only the specified pattern
pat = re.compile('(\d+)\.up.*\.sql\Z')
maxindex = 0
for file in initial_list:
match = re.search(pat,file)
index = int(match.group(1))
if index in return_obj:
collisions.append("" + return_obj[index] + " <==> " + file)
if match != None and index > exclude_before:
return_obj[index] = file
if match != None and index > maxindex:
maxindex = index
# Check for collisions
if len(collisions) > 0:
print "~"
print "~ ======================================================================================================"
print "~ "
print "~ ERROR: Migrate collisions detected. Please resolve these, then try again"
print "~"
print "~ Collision list:"
for item in collisions:
print "~ " + item
print "~"
print "~"
sys.exit(-1)
# Check for gaps
missed = []
for idx in range((exclude_before + 1),maxindex):
if idx not in return_obj:
missed.append(idx)
if len(missed) > 0:
print "~"
print "~ ======================================================================================================"
print "~ "
print "~ ERROR: Migrate file gaps detected. Please resolve these, then try again"
print "~"
print "~ Files at the following levels are missing:"
for idx in missed:
print "~ %s" % idx
print "~"
print "~"
sys.exit(-1)
return [maxindex, return_obj]
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ extracts the database and its alias from the passed database name.
def extractDatabaseAndAlias(db):
db = db.strip()
# See if there's an alias.
match = re.search('(\w+)\[(\w+)]',db);
if match == None:
db_alias = db;
db_alias_name = 'None';
else:
db_alias = match.group(2);
db_alias_name = db_alias;
db = match.group(1);
return [db,db_alias,db_alias_name]
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ interpolates the creation file with the passed database name.
def interpolateDBFile(db, createpath):
[tmp_path,f] = createTempFile('migrate.module/temp_create_%(db)s.sql' % {'db': db})
print "~ Creating temp file: %(tf)s" % {'tf':tmp_path}
for line in open(createpath).readlines():
f.write(line.replace("${db}",db))
f.close()
return tmp_path
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Runs the creation script
def runCreateScript(createpath, createname):
db_format_string = getFormatString()
db_commands = getCommandStrings()
db_commands['filename'] = createpath
db_commands['dbname'] = ""
db_create_cmd = db_format_string %db_commands
print "~ Running script %(cs)s..." % {'cs': createname}
[code,response] = runDBCommand(db_create_cmd)
if code <> 0:
print "~ " + str(code)
print "~ ERROR: could not execute the database create script: "
print "~ " + db_create_cmd
print "~ "
print "~ Process response: " + response
print "~ "
print "~ Check your credentials and your script syntax and try again"
print "~ "
sys.exit(-1)
# ~~~~~~~~~~~~~~~~~~~~~~ Runs the database creation script
def create():
try:
is_generic = False
if app.path:
createpath = os.path.join(app.path, 'db/migrate/generic_create.sql')
if not os.path.exists(createpath):
createpath = os.path.join(app.path, 'db/migrate/create.sql')
if not os.path.exists(createpath):
print "~ "
print "~ Unable to find create script"
print "~ Please place your database creation script in db/migrate/create.sql or db/migrate/generic_create.sql"
print "~ "
sys.exit(-1)
else:
is_generic = True
else:
print "~ Unable to find create script"
sys.exit(-1)
if is_generic:
print "~ Using generic create script, replacing parameter ${db} with each database name..."
print "~ "
db_list = app.readConf('migrate.module.dbs').split(',')
db_arg = getDbArg()
for db in db_list:
db = db.strip()
# Extract the database name, trimming any whitespace.
[db, db_alias, db_alias_name] = extractDatabaseAndAlias(db)
# Skip the database if a specified was given
if db_arg != None and db != db_arg:
continue
print "~ Database: %(db)s" % {'db': db}
# interpolate the generic file to contain the database name.
interpolated = interpolateDBFile(db, createpath)
# run the interpolated creation script
runCreateScript(interpolated,'generic_created.sql (%(db)s)' % {'db': db})
else:
# Run the create script
runCreateScript(createpath, 'create.sql')
except getopt.GetoptError, err:
print "~ %s" % str(err)
print "~ "
sys.exit(-1)
print "~ "
print "~ Database creation script(s) completed."
print "~ "
# ~~~~~~~~~~~~~~~~~~~~~~ Performs the up migration task
def up():
# The format string we'll use to run DB commands
db_format_string = getFormatString()
# Find the databases to iterat
db_list = app.readConf('migrate.module.dbs').split(',')
db_arg = getDbArg()
to_version = getUpToVersion()
print "~ Database migration:"
print "~ "
for db in db_list:
# Extract the database name, trimming any whitespace.
[db, db_alias, db_alias_name] = extractDatabaseAndAlias(db)
# Skip the database if a specified was given
if db_arg != None and db != db_arg:
continue
print "~ Database: %(db)s (Alias:%(alias)s)" % {'db':db, 'alias': db_alias_name }
[version,status] = getVersion(db)
print "~ Version: %(version)s" % {'version': version}
print "~ Status: %(status)s" % {'status': status}
[maxindex, files_obj] = getMigrateFiles(db_alias,int(version))
print "~ Max patch version: " + str(maxindex)
print "~ "
if maxindex <= int(version):
print "~ " + db + " is up to date."
print "~ "
print "~ "
continue
print "~ Migrating..."
command_strings = getCommandStrings()
if to_version == None or to_version > maxindex:
to_version = maxindex
for i in range(int(version) + 1, to_version + 1):
# Skip missed files
if files_obj[i] == None:
print "~ Patch " + str(i) + " is missing...skipped"
continue
command_strings['filename'] = files_obj[i]
command_strings['dbname'] = db
db_cmd = db_format_string % command_strings
[code, response] = runDBCommand(db_cmd)
if code <> 0:
print "~ Migration failed on patch " + str(i) + "!"
print "~ ERRROR message: " + response
updateStatusTo(db,response)
sys.exit(-1)
print "~ " + str(i) + "..."
updateVersionTo(db,i)
print "~ "
print "~ Migration completed successfully"
print "~ "
print "~ ------------------------------------"
print "~ "
# ~~~~~~~~~~~~~~~~~~~~~~ Drops all databases
def dropAll():
db_list = app.readConf('migrate.module.dbs').split(',')
db_arg = getDbArg()
print "~ "
print "~ Dropping databases..."
for db in db_list:
[db, db_alias, db_alias_name] = extractDatabaseAndAlias(db)
# Skip the database if a specified was given
if db_arg != None and db != db_arg:
continue
print "~ drop %(db)s" % {'db':db}
[tmp_path,f] = createTempFile('migrate.module/drop_db.sql')
f.write("drop database if exists %(db)s;" %{ 'db':db })
f.close()
# The format string for running commands through a file
db_format_string = getFormatString()
command_strings = getCommandStrings()
command_strings['filename'] = tmp_path
command_strings['dbname'] = ""
db_cmd = db_format_string % command_strings
[code, response] = runDBCommand(db_cmd)
if code <> 0:
print "Failure " + response
sys.exit(-1)
print "~ "
print "~ Database drop completed"
print "~ "
def execute(**kargs):
global app
play_command = kargs.get("command")
app = kargs.get("app")
# ~~~~~~~~~~~~~~~~~~~~~~ [migrate:create] Create the initial database
if play_command == 'migrate:create':
create()
sys.exit(0)
# ~~~~~~~~~~~~~~~~~~~~~~ [migrate:up] Migrate the database from it's current version to another version
if play_command == 'migrate:up':
up()
sys.exit(0)
# ~~~~~~~~~~~~~~~~~~~~~~ [migrate:version] Output the current version(s) of the datbase(s)
if play_command == 'migrate:version':
db_list = app.readConf('migrate.module.dbs').split(',')
db_arg = getDbArg()
print "~ Database version check:"
print "~ "
for db in db_list:
[db, db_alias, db_alias_name] = extractDatabaseAndAlias(db)
# Skip the database if a specified was given
if db_arg != None and db != db_arg:
continue
[version, status] = getVersion(db)
format = "%(dbname)-20s version %(version)s, status: %(status)s" % {'dbname':db, 'version':version, 'status': status}
print "~ " + format
print "~ "
sys.exit(0)
# ~~~~~~~~~~~~~~~~~~~~~~ [migrate:init] Build the initial / example files for the migrate module
if play_command == 'migrate:init':
app.override('db/migrate/generic_create.sql', 'db/migrate/generic_create.sql')
app.override('db/migrate/db1/1.up.create_user.sql', 'db/migrate/db1/1.up.create_user.sql')
print "~ "
sys.exit(0)
# ~~~~~~~~~~~~~~~~~~~~~~ [migrate:drop-rebuild] drop then rebuild databases
if play_command == 'migrate:drop-rebuild':
dropAll()
create()
up()
sys.exit(0)
# ~~~~~~~~~~~~~~~~~~~~~~ [migrate:drop] drop databases
if play_command == 'migrate:drop':
dropAll()
sys.exit(0)
if play_command.startswith('migrate:') or play_command == 'migrate':
print "~ Database migration module "
print "~ "
print "~ Use: migrate:create to create your initial database"
print "~ migrate:up to migrate your database up"
print "~ migrate:version to read the current version of the database"
print "~ migrate:init to set up some initial database migration files"
print "~ migrate:drop-rebuild to drop and then rebuild all databases (use with caution!)"
print "~ migrate:help to show this message"
print "~ "
print "~ Add --db={database name} to any command to only affect that database"
print "~ Add --to={version number} to any command to only migrate to the specified version number"
print "~ "
sys.exit(0)
class MockPlayApp:
override = None
readConf = None
path = None
# 1.0 version code
try:
cmd_10 = play_command
app = MockPlayApp()
app.override = override
app.readConf = readConf
app.path = application_path
execute(command=play_command, app=app)
except NameError:
pass
PK \B>!0 0 manifestversion=1.4
frameworkVersions=1.0.1 or higher
PK dG?>b READMEMigrate module for the Play! framework. This module allows you to easily maintain database versions for your project.
Copyright 2010
David Cardon - dcardon@enticelabs.com
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
PK V?>JcV V db/migrate/generic_create.sqlCREATE DATABASE ${db};
-- A migratable database has to have a 'patchlevel' table, which stores the version and the status of the last update.
CREATE TABLE ${db}.patchlevel (version int(10) unsigned NOT NULL, status varchar(255) default NULL, PRIMARY KEY (`version`));
insert into ${db}.patchlevel (version, status) values (0,'Successful');PK V?>uo # db/migrate/db1/1.up.create_user.sql-- The user table
CREATE TABLE user (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255),
`password` VARCHAR(255),
PRIMARY KEY (`id`)
);
PK \B>NƧ{% {% ! documentation/manual/home.textileh1. Migrate module
The migrate module helps you manage versions of your database.
If you're familiar with "Ruby On Rails data migration":http://api.rubyonrails.org/classes/ActiveRecord/Migration.html, this module is similar to that. Essentially, the purpose of this module is to provide a useful way to track and manage the version of one or more databases during the development process.
h2. Enable the migrate module for the application
In the **/conf/application.conf** file, you need a line to add the module to your project:
bc. # The migrate module
module.migrate=${play.path}/modules/migrate
h3. Migrate vs. jpa.ddl=update
In addition to enabling the migrate module, you might want to consider check your **jpa.ddl** configuration setting and make sure it is set to either **jpa.ddl=validate** or **jpa.ddl=none**; otherwise, JPA will modify your database schema based on your models.
Generally, if you're just at the start of a project, it's probably best to just use the **jpa.ddl=update** setting and not bother with the migrate module. However, migrate really offers a lot of advantages over **jpa.ddl** once your project matures a little (e.g., populating your database with metadata, transforming existing operational data into new structures, etc.)
h2. Configuration
h3. Configure database credentials
You need to specify connection and credential parameters for the migrate module.
Make sure that the database user you specify (username and password) has the rights needed to create a new database as well as modify tables.
The connection parameters (port and host) are those used when connecting to your database.
bc. # The parameters for running migration steps.
migrate.module.username=root
migrate.module.password=root_password
migrate.module.port=3306
migrate.module.host=localhost
h3. List your databases
The module can migrate multiple databases, which are accessible through the same connection and credentials. List your databases by name, separated by commas.
bc. # The comma delimited list of databases that migrate should update.
migrate.module.dbs=db1,db2
h3. Choose or write your command-line format string
In order to run database modifications, the module uses command line tools to execute migration files on its databases.
The command line is different for each particular database, so the module uses a format string as a template for the command. The example configuration lines show the template for MySQL and PostgreSQL databases.
bc. # migrate.module.file.format is used to run commands directly from a file via the command-line
# on a database. This format is populated with the appropriate fields in order to perform
# creation and migration scripts on the database.
#
# The MySQL version:
# migrate.module.file.format=mysql -u%(username)s --password=%(password)s -h %(host)s -P%(port)s --skip-column-names %(dbname)s < %(filename)s
# The PostgreSQL version:
# migrate.module.file.format=psql -U %(username)s -h %(host)s -P%(port)s -t -d %(dbname)s -f %(filename)s
Make sure that the command (i.e., 'mysql' or 'psql') is available on your system's path or the module will encounter errors.
*NOTE:* Play version 1.1 has issues parsing configuration properties with multiple '=' symbols in the line. As a result, the mysql file format will NOT work. I've coded a workaround into the migrate module, which replaces the string '[[eq]]' with an equals symbol. Therefore, in 1.1 the MySQL format string should read:
bc. migrate.module.file.format=mysql -u%(username)s --password[[eq]]%(password)s -h %(host)s -P%(port)s --skip-column-names %(dbname)s < %(filename)s
h2. Writing patch files
Migrate processes two types of files: a single **create** script and multiple **patch** scripts. The create script is named **create.sql** and is only run to generate your database(s) initially. The patch scripts are named according to this pattern: [version number].['up' or 'down'].[description].sql
These scripts are stored in your play application's folder like this:
bc. playapp/
|- + db/
|- + migrate/
|- create.sql
|- + db1/
|- 1.up.create_user.sql
|- 1.down.remove_user.sql
|- 2.up.create_address.sql
|- 3.up.add_password_to_user.sql
|- + db2/
|- 1.up.create_foobar.sql
Run **play migrate:init** from the command line to build a skeletal directory structure and some example scripts.
The **create.sql** file contains the instructions to create all of your databases, as well as a single table called **patchlevel**, which the migrate module uses to keep track of each databases current version. This special table needs two fields called **version** and **status**, which hold the database version number and it's last reported migration status, respectively. See the __create.sql__ file generated by the **migrate:init** command for example code to create this table.
The patch scripts identify a version number for their contents as well as the direction of the patch. The **up** patches are used to bring the database version from its previous version up to the version of the patch (e.g., if the database's current version is 12, patch 13.up.xxx.sql will bring the database to version 13). The **down** patches remove the changes of the corresponding __up__ patch, bringing the database to the previous version.
NOTE: code to run 'down' patches is not implemented in this version of the migrate module! However, this feature has not proven very useful in practice, while migrating up is definitely vital.
h2. Database Aliases - Multiple Databases with the Same Schema
Sometimes in development or maintenance of a web application, it is useful to create multiple copies of the same database; for example, an active development copy, a staging copy and a production copy. Often these are hosted on separate machines, but in certain cases (like smaller projects) they are not. Because the migration module uses the database name as the sub-folder name containing its patch files, it is a little difficult to simply change the name of the database the module creates and migrates.
To overcome this issue, this version of the module (1.3 and higher) supports "aliases", which allow for the easy modification of the database name parameter, as well as simpler synchronized migration of multiple databases on the same machine. To introduce this feature, let's start with our example database configuration:
bc. # The comma delimited list of databases that migrate should update.
migrate.module.dbs=db1,db2
Now, suppose that we wish to split db1 into three synchronized databases: db1_dev, db1_staging and db1_prod. In older migrate module versions, this would require us to maintain three copies of the same patch scripts--definitely not desirable. With database aliases, we can now avoid unnecessary redundancy:
bc. # The comma delimited list of databases that migrate should update.
migrate.module.dbs=db1_dev[db1],db1_staging[db1],db1_prod[db1],db2
As you well may have guessed, each database is now an alias for the set of patch files located in 'db1'. Thus, in this example 'db1' is more of a name we assign to the set of patch files, rather than the name of a database. Note that if the alias syntax is NOT used, then the migrate module works just as it did before this new feature.
WARNING!! With this format, it is extremely important that your patch files DO NOT contain any references to database / schema names. If there are any such references, only the database explicitly referred to will be modified.
h2. Generic Creation Script Interpolation
If you've used the migration module before with multiple databases, you may have observed something annoying about the 'create.sql' script: duplicated code. For each database, you essentially run the same commands: create the database by name, and then add the patchlevel table to it.
To avoid this unnecessary duplication version 1.3 and higher of the migrate module supports a **generic creation script**, which is run and interpolated for each database defined in the module's 'migrate.module.dbs' configuration.
If the 'generic_create.sql' file is present in the db/migrate folder, it's contents will be executed for each database registered with the module. Within this file the string '${db}' is replaced with the name of the database being operated upon.
If the 'generic_create.sql' file is NOT present, the normal 'create.sql' file will be run when a 'play migrate:create' is run and this feature is not activated.
h2. Running the patch files
To run your database creation script, use the **play migrate:create** command. This just runs the __create.sql__ or interpolated generic_create.sql script (depending on configuration).
To bring your database(s) to their most recent patch version(s), use the **play migrate:up** command.
h2. Starting fresh
Sometimes during early development, it's useful to start with a clean database (this is also often nice during tests). You can drop all of your databases, create new ones and migrate to the most recent database version in one command: **play migrate:drop-rebuild** command.
h2. Other commands
You can see your database(s) current version number(s) and status(es) with the **play migrate:version** command.
You can create a skeleton directory structure (with example files) with the **play migrate:init** command.
You can just drop all of your databases with the **play migrate:drop** command.PK m?>|9F F commands.pyPK \B>!0 0 F manifestPK dG?>b 2G READMEPK V?>JcV V J db/migrate/generic_create.sqlPK V?>uo # K db/migrate/db1/1.up.create_user.sqlPK \B>NƧ{% {% ! L documentation/manual/home.textilePK Ar