Example rake file for applying schema scripts and running sql

a_readme.md

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

3. Add required gems.

• gem install albacore

4. Run Rake to build and run unit tests.

• rake

rake -T will list all available tasks

license.sql

/* 
// 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>');

rakefile.rb

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