Skip to content

Instantly share code, notes, and snippets.

@KevM
Created January 30, 2012 17:11
Show Gist options
  • Save KevM/1705486 to your computer and use it in GitHub Desktop.
Save KevM/1705486 to your computer and use it in GitHub Desktop.
Example rake file for applying schema scripts and running sql

Dovetail Automation

Running the rake script below will

  1. Apply all schema scripts ending in .schemascript.xml found in the child schema directory.
  2. Execute all .sql scripts found in the child database directory.

Setup

  1. Edit the rakefile.rb to have your correct database configuration.
  2. If you've installed Dovetail Schema Editor to a non default path you'll need to edit SCHEMAEDITOR_PATH.

Rake

If you do not have rake already installed.

  1. Download the ruby installer for Windows.
  2. Go to a command prompt.
  • gem install rake
  1. Add required gems.
  • gem install albacore
  1. Run Rake to build and run unit tests.
  • rake

rake -T will list all available tasks

/*
// you'll need to copy your license keys out of your .fclic license key file you received with our products
// Create a insert row for each license you own. Don't forget to increment the objid (they simply need to be unique)
*/
DELETE FROM table_fc_licensing;
INSERT INTO table_fc_licensing (objid, licensekey) VALUES(0, '<first license key>');
INSERT INTO table_fc_licensing (objid, licensekey) VALUES(1, '<second license key>');
INSERT INTO table_fc_licensing (objid, licensekey) VALUES(2, '<third license key>');
require 'albacore'
include REXML
include Rake::DSL
# configuration edit as necessary
SCHEMAEDITOR_PATH = "#{Rake::Win32::normalize(ENV['PROGRAMFILES'])}/Dovetail Software/SchemaEditor/SchemaEditor.exe"
DATABASE_HOST="."
DATABASE = "mobilecl125"
DATABASE_USER = "sa"
DATABASE_PASSWORD = "sa"
DATABASE_TYPE = "mssql"
DATABASE_CONNECTION = "Data Source=#{DATABASE_HOST};User Id=#{DATABASE_USER};Password=#{DATABASE_PASSWORD}"
task :default => [:apply_schemascripts, :apply_sql]
desc "Execute all sql scripts in the database directory"
task :apply_sql do
apply_oracle_sql if DATABASE_TYPE == "oracle"
Rake::Task["apply_sqlserver_sql"].execute() if DATABASE_TYPE == "mssql"
end
sqlcmd :apply_sqlserver_sql do |cmd|
puts "Execute sql scripts in #{File.absolute_path("database")} directory"
cmd.server = DATABASE_HOST
cmd.database = DATABASE
cmd.username = DATABASE_USER
cmd.password = DATABASE_PASSWORD
scripts = Array.new
scripts = scripts.concat(FileList["database/*.sql"])
cmd.scripts = scripts
end
#desc "Execute all sql scripts in the database directory using SqlPlus"
def apply_oracle_sql
puts "Execute sql scripts in #{File.absolute_path("database")} directory"
Dir.glob(File.join('database', "*.sql")) do |sql_script|
sqlFile = File.absolute_path(sql_script).gsub('/','\\')
puts "\n\nApplying sql from file #{sqlFile}\n\n"
sh "sqlplus #{DATABASE_USER}/#{DATABASE_PASSWORD}@#{DATABASE_HOST} @#{sqlFile}"
end
end
desc "Apply all schema scripts in the schema directory"
task :apply_schemascripts do
puts "Applying scripts from #{File.absolute_path('schema')} to database #{DATABASE}"
seConfig = 'Default.SchemaEditor'
seReport = 'SchemaDifferenceReport.txt'
puts "Generating Schema Editor configuraiton file"
sh "\"#{SCHEMAEDITOR_PATH}\" -g"
#SchemaEditor has different (more verbose) database type configuration than Dovetail SDK
databaseType = (DATABASE_TYPE == 'mssql') ? 'MsSqlServer2005' : 'Oracle9'
Dir.glob(File.join('schema', "*schemascript.xml")) do |schema_script|
puts "\n\nConfiguring Schema Editor for #{schema_script}"
File.open(seConfig) do |schema_editor_config_file|
doc = Document.new(schema_editor_config_file)
doc.root.elements['database/type'].text = databaseType
doc.root.elements['database/connectionString'].text = DATABASE_CONNECTION
doc.root.elements['inputFilePath'].text = schema_script.gsub('/','\\')
formatter = REXML::Formatters::Default.new
File.open(seConfig, 'w') do |result|
formatter.write(doc, result)
end
end
puts "\n\nApplying schemascript #{schema_script}"
sh "\"#{SCHEMAEDITOR_PATH}\" -a"
if File.exists? seReport
sh "type #{seReport}\n\n\n"
File.delete seReport
end
end
File.delete(seConfig)
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment