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

Attributes

files[RW]

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
makedynamic[RW]

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.

variables[RW]

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

version[RW]

Optional Version of SQL Server’s sqlcmd to use. Defaults to SQL Server 2008.

Public Class Methods

new(parameters = :task) click to toggle source
Calls superclass method 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

generatetempfilename() click to toggle source
    # File lib/sqlcmd.rb
149 def Sqlcmd.generatetempfilename
150   "sqlload_"+DateTime.now.strftime("%Y%m%d%H%M%S") +".sql"
151 end
getdir(directory) click to toggle source
    # 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

credentials() click to toggle source
   # File lib/sqlcmd.rb
51 def credentials
52   @credentials || :general
53 end
credentials=(value) click to toggle source

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

connect() click to toggle source
    # 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
createtempfile() click to toggle source
    # 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
credentialsString() click to toggle source
   # File lib/sqlcmd.rb
57 def credentialsString
58   credentials.to_s
59 end
exectask() click to toggle source
   # 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
path() click to toggle source
    # File lib/sqlcmd.rb
153 def path
154   p = @version || "100"
155   sql_tool p
156 end
processdynamic() click to toggle source
    # 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
removetempfile() click to toggle source
    # File lib/sqlcmd.rb
145 def removetempfile
146   rm_safe @tempfile
147 end
variables_flat() click to toggle source
    # 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