Module: Rutema::Elements::SQLServer

Defined in:
lib/rutema/elements/win32.rb

Overview

Elements to drive Microsoft’s SQLServer

Instance Method Summary collapse

Instance Method Details

#element_sqlcmd(step) ⇒ Object

Calls sqlcmd.

Requires the script attribute pointing to the SQL script to execute. Path can be relative to the specification file.

Configuration

Requires a configuration.tool entry with :name=>“sqlcmd” and a :configuration entry pointing to a hash containing the configuration parameters. Configuration parameters are: :host - the host to run the command against (sqlcmd -H) :server - the SQLServer (named instance) (sqlcmd -S) :username - the SQLServer user (sqlcmd -U) :password - (sqlcmd -P) :script_root - The path relative to which pathnames for scripts are calculated. If it’s missing paths are relative to the specification file. Optional

Example Configuration Entry

configuration.tool=:name=>“sqlcmd”,:configuration=>{:host=>“guineapig”,:server=>“DB”,:user=>“foo”,:password=>“bar”}

Extras

Not defining any options (e.g. not defining the configuration.tool entry) results in the script running locally without options

The configuration options can be overriden by element attributes (host,server,username etc.). Additionally the following attributes can be defined: database - the database to run the script against ( sqlcmd -d ) level - Sets the errorlevel for the script (sqlcmd -V)

Example Elements

<sqlcmd script=“some.sql”/> <sqlcmd script=“some.sql” host=“localhost”/> - overriding host <sqlcmd script=“some.sql” database=“MyDB” level=“11”/> - hypersensitive error checking and explicitly executed on MyDB

Raises:

  • (Rutema::ParserError) 


37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/rutema/elements/win32.rb', line 37

def element_sqlcmd step
  raise Rutema::ParserError,"Missing required script attribute in sqlcmd step" unless step.has_script?
  cfg=@configuration.tools.sqlcmd[:configuration].dup if @configuration.tools.sqlcmd && @configuration.tools.sqlcmd[:configuration]
  cfg||=Hash.new
  root_path=script_root_path(cfg,step)
  cfg[:script]=adjust_with_root_path(step.script,root_path,step)
  #check for overrides
  cfg[:host] = step.host if step.has_host?
  cfg[:server] = step.host if step.has_server?
  cfg[:username] = step.host if step.has_username?
  cfg[:password] = step.host if step.has_password?
  #add the optional attributes
  cfg[:database] = step.database if step.has_database?
  cfg[:level] = step.level if step.has_level?
  #get the command object
  step.cmd=sqlcmd_command(cfg)
  return step
end

#element_vsdbcmd(step) ⇒ Object

Calls vsdbcmd to deploy a database schema

Requires the dbschema attribute pointing to the dbschema file to deploy.

Paths can be relative to the specification file

Configuration

Requires a configuration.tool entry with :name=>“vsdbcmd” and a :configuration entry pointing to a hash containing the configuration parameters. Configuration parameters are: :path - the path to the vsdbcmd.exe :cs - the connection string to use :manifest - path to the depploymanifest file to use. If not present then a manifest attribute is expected. :overrides - Optional. Should be a string containing parameters that override vsdbcmd parameters in the format expected by vsdbcmd :script_root - The path relative to which pathnames for scripts are calculated. If it’s missing, paths are relative to the specification file. Optional all configuration options apart from :path can be overriden in the element

Example Configuration Entry

configuration.tool=Source=(local);;Initial Catalog=YourDB;Integrated Security=True”:overrides=>“/p:AlwaysCreateNewDatabase=True”}

Extras

When overriding the :manifest configuration the path to the file can be relative to the specification file or the :script_root path if defined

A .deploymanifest is required because of the number of possible parameters that can be defined in it and it’s dependent files. Making every parameter available in the configuration will result in nothing but a mess. In every case a database project in Visual Studio will create a manifest file.

Use the :overrides key to override parameter values and provide extra command line parameters. The :overrides value must be in valid vsdbcmd format (see examples)

Defining paths in :overrides and handling paths defined in the .deploymanifest can be tricky: All paths will be relative to the specification file.

Example Elements

<vsdbcmd dbschema=“../yourdb.dbschema”/> <vsdbcmd dbschema=“../yourdb.dbschema” overrides=“/p:AlwaysCreateNewDatabase=False /p:BlockIncrementalDeploymentIfDataLoss=True”/> <vsdbcmd dbschema=“../yourdb.dbschema” overrides=“/p:AlwaysCreateNewDatabase=False /DeploymentScriptFile:"somefile.sql"”/>

Raises:

  • (Rutema::ParserError) 


85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/rutema/elements/win32.rb', line 85

def element_vsdbcmd step
  raise Rutema::ParserError,"Missing tool configuration for vsdbcmd (no configuration.tool entry)" unless @configuration.tools.vsdbcmd && @configuration.tools.vsdbcmd[:configuration]
  raise Rutema::ParserError,"Missing required dbschema attribute in vsdbcmd step" unless step.has_dbschema?
  cfg=@configuration.tools.vsdbcmd[:configuration].dup
  path_to_util=File.expand_path(cfg[:path])
  raise Rutema::ParserError,"Cannot find vsdbcmd in '#{path_to_util}'" unless File.exists?(path_to_util)
  cfg[:dsp]||="sql"
  root_path=script_root_path(cfg,step)
  cfg[:dbschema]=adjust_with_root_path(step.dbschema,root_path,step)
  #check the manifest and handle also the value from a possible attribute.
  #if both are missing than it's an error
  manifest=cfg[:manifest]
  manifest=step.manifest if step.has_manifest?
  raise Rutema::ParserError,"No manifest file defined for #{step.step_type} (wether in the configuration or as an attribute)" unless manifest
  cfg[:manifest]=adjust_with_root_path(manifest,root_path,step)
  #do the same for connection string
  connection_string=cfg[:cs]
  connection_string=step.connection_string if step.has_connection_string?
  raise Rutema::ParserError,"No connection string defined for #{step.step_type} (wether in the configuration or as an attribute)" unless connection_string
  cfg[:cs]=connection_string
  #optional overrides
  if step.has_overrides?
    cfg[:overrides]=step.overrides
  end
  #assign the command
  step.cmd=vsdbcmd_command(cfg)
  return step
end