Class: BarkestCore::MsSqlDbDefinition

Inherits:
Object
  • Object
show all
Defined in:
app/models/barkest_core/ms_sql_db_definition.rb

Overview

This class can be used to conditionally update a target SQL database with custom code.

The updater will hunt through the SQL files found in the sql_sources passed in. For each source, the updater calculates the new version and compares it against the existing version. If the new version is actually newer, the updater will update the object in the database.

Defined Under Namespace

Classes: Conn

Constant Summary collapse

UpdateError =

The base error for errors raised by the updater class.

Class.new(StandardError)
NeedFullAccess =

The error raised when the provided connection does not provide a user with full control over the database.

Class.new(UpdateError)
ObjectTypeMismatch =

The error raised when an object type doesn’t match the previous type for the object with the specified name.

Class.new(UpdateError)
VERSION_TABLE_NAME =

The name of the table holding the object versions.

'zz_barkest__versions'

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(options = {}) ⇒ MsSqlDbDefinition

Defines a new updater.

Options can include source_paths, before_update, and after_update.

The before_update and after_update options define a callback to be run before or after the database update is performed. This can be a string referencing a method or it can be a Proc.

MsSqlDbDefinition.new(
  :before_update => 'MyClass.my_method(db_conn,user)',
  :after_update => Proc.new do |db_conn, user|
                     ...
                   end
)

If you use the string option, note that the db_conn and user variables are available. In the example above they are being passed to the method as arguments.



51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 51

def initialize(options = {})
  options = {
      table_name_prefix: 'zz_barkest_'
  }.merge(options || {}).symbolize_keys

  @table_prefix = options[:table_name_prefix].to_s
  valid_regex = /^[a-z][a-z0-9_]*$/im
  raise 'invalid table prefix' unless valid_regex.match(@table_prefix)

  @sources = [ ]
  @source_paths = [ ]
  @pre_update = options.delete(:before_update) || options.delete(:pre_update)
  @post_update = options.delete(:after_update) || options.delete(:post_update)

  # and any other paths provided via options.
  if options[:source_paths]
    if options[:source_paths].is_a?(String)
      add_source_path options[:source_paths]
    elsif options[:source_paths].respond_to?(:each)
      options[:source_paths].each do |path|
        add_source_path path.to_s
      end
    else
      add_source_path options[:source_paths].to_s
    end
  end

end

Instance Attribute Details

#table_prefix ⇒ Object (readonly)

Returns the value of attribute table_prefix.



28
29
30
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 28

def table_prefix
  @table_prefix
end

Class Method Details

.[](name) ⇒ Object

Gets a DB updater by name.



335
336
337
338
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 335

def self.[](name)
  name = symbolize_name name
  registered[name]
end

.each ⇒ Object

Iterates through the registered DB updaters.

Yields the db_name and the db_updater to the block.



350
351
352
353
354
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 350

def self.each
  registered.each do |k,v|
    yield k, v if block_given?
  end
end

.keys ⇒ Object

Gets a list of all the DB updaters currently registered.



342
343
344
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 342

def self.keys
  registered.keys
end

.register(name, options = {}) ⇒ Object

Registers a DB updater and tells BarkestCore that the named database exists and could use a configuration.

The options will be passed to the MsSqlDbDefinition constructor, except for the extra_params key. If this key is provided, it is pulled out and used for the defaults for the database configuration.

Ideally this is to provide the extra__name, extra__type, and extra__value parameters, but you can also use it to provide reasonable defaults for host, database, or even credentials.



279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 279

def self.register(name, options={})
  name = symbolize_name name

  raise 'already registered' if registered.include?(name)

  options = (options || {}).symbolize_keys

  extra_params = options.delete(:extra_params)
  if extra_params.is_a?(Hash)
    repeat = true
    while repeat
      repeat = false
      extra_params.dup.each do |k,v|
        if v.is_a?(Hash)
          extra_params.delete(k)
          v.each do |subk,subv|
            extra_params[:"#{k}_#{subk}"] = subv
          end
          repeat = true
        end
      end
    end
  end

  options[:table_name_prefix] ||=
      if name.to_s.index('barkest') == 0
        "zz_#{name}_"
      else
        "zz_barkest_#{name}_"
      end

  updater = MsSqlDbDefinition.new(options)

  registered[name] = updater

  # Register with DatabaseConfig to enable the config page for this DB.
  DatabaseConfig.register name

  cfg_def = (extra_params.is_a?(Hash) ? extra_params : {})
                .merge(
                    {
                        adapter: 'sqlserver',
                        pool: 5,
                        timeout: 30000,
                        port: 1433,
                    }
                )

  # Register with BarkestCore so that the default configuration is somewhat appropriate.
  BarkestCore.register_db_config_defaults name, cfg_def

  updater
end

Instance Method Details

#add_source(sql) ⇒ Object

Adds a source using a specific timestamp.

The first line of the SQL should be a comment specifying the timestamp for the source.

-- 2016-12-19 15:45
-- 2016-12-19
-- 201612191545
-- 20161219

The timestamp will be converted into a 12-digit number, if time is not specified it will be right-padded with zeroes to get to the 12-digit number.

The sql should be a valid create/alter table/view/function statement.



113
114
115
116
117
118
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 113

def add_source(sql)
  sql_def = BarkestCore::MsSqlDefinition.new(sql, '')
  sql_def.instance_variable_set(:@source_location, "::#{sql_def.name}::")
  add_sql_def sql_def
  nil
end

#add_source_definition(definition) ⇒ Object

Adds a MsSqlDefinition object to the sources for this updater.

The definition should be a previously created MsSqlDefinition object.



124
125
126
127
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 124

def add_source_definition(definition)
  add_sql_def definition
  nil
end

#add_source_path(path) ⇒ Object

Adds all SQL files found in the specified directory to the sources for this updater.

The path should contain the SQL files. If there are subdirectories, you should include them individually.

The source files should specify a timestamp in the first comment.

-- 2016-12-19 15:45
-- 2016-12-19
-- 201612191545
-- 20161219

The timestamp will be converted into a 12-digit number, if time is not specified it will be right-padded with zeroes to get to the 12-digit number.



144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 144

def add_source_path(path)
  raise 'path must be a string' unless path.is_a?(String)

  path = File.expand_path(path)
  raise 'cannot add root path' if path == '/'
  path = path[0...-1] if path[-1] == '/'

  unless @source_paths.include?(path)
    @source_paths << path

    if Dir.exist?(path)
      Dir.glob("#{path}/*.sql").each do |source|
        add_sql_def BarkestCore::MsSqlDefinition.new(File.read(source), source, File.mtime(source))
      end
    end
  end

  nil
end

#object_name(unprefixed_name) ⇒ Object

Gets an object’s name according to this DB updater.



82
83
84
85
86
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 82

def object_name(unprefixed_name)
  name = unprefixed_name.to_s
  return name if name.index(table_prefix) == 0
  "#{table_prefix}#{name}"
end

#source_paths ⇒ Object

Gets all of the source paths that have currently been searched.



90
91
92
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 90

def source_paths
  @source_paths.dup.freeze
end

#sources ⇒ Object

Gets all of the sources currently loaded.



96
97
98
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 96

def sources
  @sources.dup.map{|t| t.name_prefix = table_prefix; t}.freeze
end

#update_db(config, options = {}) ⇒ Object

Performs the database update using the specified configuration.

A warning will be logged if the runtime user has full access to the database.

An error will be raised if there is the runtime user does not have full access and no update_user is provided, or if an update_user is provided who also does not have full access to the database.



171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
 
# File 'app/models/barkest_core/ms_sql_db_definition.rb', line 171

def update_db(config, options = {})

  begin
    options ||= {}

    runtime_user = config[:username]

    Conn.remove_connection
    Conn.establish_connection config

    if have_db_control?
      warn "WARNING: Runtime user '#{runtime_user}' has full access to the database. (this is not recommended)" unless Rails.env.test?
    else
      raise NeedFullAccess, 'please provide update_username and update_password for a user with full access to the database' unless config[:update_username]

      use_config = config.dup
      use_config[:username] = config[:update_username]
      use_config[:password] = config[:update_password]

      Conn.remove_connection
      Conn.establish_connection use_config

      raise NeedFullAccess, 'provided update user does not have full access to the database' unless have_db_control?
    end

    unless Conn.object_exists?(VERSION_TABLE_NAME)
      debug 'Creating version tracking table...'
      db_connection.execute <<-EOSQL
CREATE TABLE [#{VERSION_TABLE_NAME}] (
  [object_name] VARCHAR(120) NOT NULL PRIMARY KEY,
  [object_type] VARCHAR(40) NOT NULL,
  [object_version] VARCHAR(40) NOT NULL,
  [created] DATETIME NOT NULL,
  [updated] DATETIME NOT NULL,
  [created_by] VARCHAR(120),
  [updated_by] VARCHAR(120)
)
      EOSQL
    end

    if (proc = (options[:before_update] || options[:pre_update] || @pre_update))
      if proc.is_a?(String)
        code = proc
        proc = Proc.new { |db_conn, user| eval code }
      end
      if proc.respond_to?(:call)
        debug 'Running pre-update code...'
        proc.call db_connection, runtime_user
      end
    end

    debug 'Processing source list...'
    sources.each do |src|
      src.name_prefix = table_prefix

      cur_ver = get_version src.prefixed_name

      if cur_ver
        raise ObjectTypeMismatch, "object type mismatch for #{src.prefixed_name}" unless src.type.upcase == cur_ver['object_type'].upcase
        if cur_ver['object_version'].to_i >= src.version.to_i
          debug " > Preserving #{src.prefixed_name}..."
          next  # source
        else
          debug " > Updating #{src.prefixed_name}..."
          if src.is_create?
            db_connection.execute src.drop_sql
          end
        end
      else
        debug " > Creating #{src.prefixed_name}..."
      end

      db_connection.execute src.update_sql
      db_connection.execute src.grant_sql(runtime_user)
      set_version src.prefixed_name, src.type, src.version

      src.name_prefix = ''
    end

    if (proc = (options[:after_update] || options[:post_update] || @post_update))
      if proc.is_a?(String)
        code = proc
        proc = Proc.new { |db_conn, user| eval code }
      end
      if proc.respond_to?(:call)
        debug 'Running post-update code...'
        proc.call db_connection, runtime_user
      end
    end

    yield db_connection, runtime_user if block_given?

  ensure
    Conn.remove_connection
  end

  true
end