class BradyW::Sqlcmd
Runs SQLcmd to run supplied SQL scripts. This task will roll up all supplied SQL script files into 1 SQL file before running it in order to speed up the tasks. The “-e” flag is used so that all statements are echo’ed to stdout.
Constants
- CONNECT_STRING_SQLAUTH
- CONNECT_STRING_WINAUTH
- HEADER
Attributes
Required Which SQL scripts do you want to run? Everything in this path will be run with this script. It’s recommended that you arrange your structure like the one below. If you do, the generated meta script will have nice comments that indicate what it’s currently running
somedirectory 01-tables 01-table1.sql 02-table2.sql 02-indexes
Optional Setting this to true will NOT execute sqlcmd at all, but instead will go through the source files supplied and replace any hard coded host names, database names, or any other variables with sqlcmd style $(variable)s to make scripts more dynamic. It’s useful when taking scripts creating on a single developer machine and prepping them for checkin. Default is false.
Optional By default, several variables are passed into SQLCMD based on the config file. Add yours in here as key value pairs if you want to send more. The following will be set by default:
-
dbname - all credentials
-
sqlserverdatadirectory - only when using :system credentials
-
dbuser -> general user, all credentials
-
dbpassword - > general password, only when using :system credentials
Optional Version of SQL Server’s sqlcmd to use. Defaults to SQL Server 2008.
Public Class Methods
BradyW::BaseTask::new
# File lib/sqlcmd.rb 65 def initialize (parameters = :task) 66 super parameters 67 @dbprops = Database.new 68 @config = Config.instance.values 69 # We don't want the temp file/time changing on us during the run 70 @tempfile = Sqlcmd.generatetempfilename 71 end
Private Class Methods
# File lib/sqlcmd.rb 149 def Sqlcmd.generatetempfilename 150 "sqlload_"+DateTime.now.strftime("%Y%m%d%H%M%S") +".sql" 151 end
# File lib/sqlcmd.rb 106 def Sqlcmd.getdir directory 107 parentDir = File.dirname directory 108 directory[parentDir.length+1..-1] 109 end
Public Instance Methods
# File lib/sqlcmd.rb 51 def credentials 52 @credentials || :general 53 end
Optional Which set of DB credentials should be used? :system - for creation/deletion of databases :objectcreation - for adding/creating objects within a database :general - DEFAULT - for adding/deleting rows within a database (use this for code)
# File lib/sqlcmd.rb 46 def credentials=(value) 47 BaseTask.validate value, "credentials", Database::CREDENTIALS 48 @credentials = value 49 end
Private Instance Methods
# File lib/sqlcmd.rb 158 def connect 159 # TODO: Use better class structure in the config file 160 prefix = "db_#{credentialsString}_" 161 authMode = @config.send("#{prefix}authmode") 162 if authMode == :winauth 163 CONNECT_STRING_WINAUTH % [@dbprops.host] 164 else 165 CONNECT_STRING_SQLAUTH % [@config.send("#{prefix}user"), 166 @config.send("#{prefix}password"), 167 @dbprops.host] 168 end 169 end
# File lib/sqlcmd.rb 111 def createtempfile 112 File.open(@tempfile, "w") do |file| 113 file.puts HEADER 114 file.puts "-- BEGIN BATCH SQL RUN" 115 file.puts HEADER 116 file.puts 117 118 @files.each do |input| 119 if File.directory? input 120 containingdir = Sqlcmd.getdir input 121 122 file.puts 123 file.puts 124 file.puts HEADER 125 file.puts "-- Directory: #{containingdir}..." 126 file.puts HEADER 127 file.puts 128 file.puts 129 130 else 131 file.puts "-- Script: #{input}" 132 file.puts ":r #{input}" 133 end 134 end 135 136 file.puts 137 file.puts 138 file.puts 139 file.puts HEADER 140 file.puts "-- COMPLETED BATCH SQL RUN" 141 file.puts HEADER 142 end 143 end
# File lib/sqlcmd.rb 57 def credentialsString 58 credentials.to_s 59 end
# File lib/sqlcmd.rb 73 def exectask 74 if makedynamic 75 processdynamic 76 return 77 end 78 79 createtempfile 80 exe = "\"#{path}sqlcmd.exe\"" 81 args = "#{connect} -e -b#{variables_flat} -i #{@tempfile}" 82 cmd = "#{exe} #{args}" 83 shell cmd do |ok,status| 84 # We want to clean up our temp file in case we fail 85 removetempfile 86 ok or 87 fail "Command failed with status (#{status.exitstatus}):" 88 end 89 end
# File lib/sqlcmd.rb 153 def path 154 p = @version || "100" 155 sql_tool p 156 end
# File lib/sqlcmd.rb 91 def processdynamic 92 vars = variables 93 @files.each do |fileName| 94 next if File.directory? fileName 95 text = File.read(fileName) 96 vars.each do |setting,value| 97 text.gsub!(value, 98 "$(#{setting})") 99 end 100 File.open fileName, "w" do |newFile| 101 newFile.puts text 102 end 103 end 104 end
# File lib/sqlcmd.rb 145 def removetempfile 146 rm_safe @tempfile 147 end
# File lib/sqlcmd.rb 171 def variables_flat 172 keyvalue = [] 173 variables.each do |variable, setting| 174 setting = setting.include?(' ') ? "\"#{setting}\"" : setting 175 keyvalue << "#{variable}=#{setting}" 176 end 177 " -v " + keyvalue.join(" ") unless variables.empty? 178 end