Class: Mkxms::Mssql::DatabaseHandler

Inherits:
Object
  • Object
show all
Extended by:
Utils::InitializedAttributes
Includes:
ExtendedProperties, PropertyHandler::ElementHandler
Defined in:
lib/mkxms/mssql/database_handler.rb

Defined Under Namespace

Classes: IgnoreText

Constant Summary collapse

ADOPTION_SQL_FILE = 
"adopt.sql"
DRY_RUN_MARKER = 
"for dry run"

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Utils::InitializedAttributes

attr_init

Methods included from ExtendedProperties

#extended_properties

Methods included from PropertyHandler::ElementHandler

#handle_property_element

Constructor Details

#initialize(**kwargs) ⇒ DatabaseHandler

Returns a new instance of DatabaseHandler.



56
57
58
 
# File 'lib/mkxms/mssql/database_handler.rb', line 56

def initialize(**kwargs)
  @schema_dir = kwargs[:schema_dir] || Pathname.pwd
end

Instance Attribute Details

#schema_dir ⇒ Object (readonly)

Returns the value of attribute schema_dir.



60
61
62
 
# File 'lib/mkxms/mssql/database_handler.rb', line 60

def schema_dir
  @schema_dir
end

Instance Method Details

#create_adoption_script ⇒ Object 



452
453
454
455
456
457
458
 
# File 'lib/mkxms/mssql/database_handler.rb', line 452

def create_adoption_script
  adoption_script_path = @schema_dir.join(ADOPTION_SQL_FILE)
  
  writer = AdoptionScriptWriter.new(self)
  
  writer.create_script adoption_script_path
end

#create_migration(summary, description, sql, change_targets) ⇒ Object 



384
385
386
387
388
389
390
391
 
# File 'lib/mkxms/mssql/database_handler.rb', line 384

def create_migration(summary, description, sql, change_targets)
  migration_chain.add_migration(
    summary,
    description: description,
    sql: sql,
    changes: change_targets
  )
end

#create_source_files ⇒ Object 



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
269
270
271
272
273
274
275
276
277
278
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
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
 
# File 'lib/mkxms/mssql/database_handler.rb', line 181

def create_source_files
  dbinfo_path = @schema_dir.join(XMigra::SchemaManipulator::DBINFO_FILE)
  
  if dbinfo_path.exist?
    if dbinfo_path.open {|f| YAML.load(f)[DRY_RUN_MARKER]}
      # Delete everything in the source files, so we can do a dry run over
      @schema_dir.each_child {|e| e.rmtree}
    else
      raise ProgramArgumentError.new("#{@schema_dir} already contains an XMigra schema")
    end
  end
  
  # TODO: Sort dependencies of triggers, views, user defined functions, and
  # stored procedures to determine which ones must be incorporated into a
  # migration (all the ones depended on by any triggers).
  
  # Create schema_dir if it does not exist
  @schema_dir.mkpath
  
  # Create and populate @schema_dir + XMigra::SchemaManipulator::DBINFO_FILE
  dbinfo_path.open('w') do |dbi|
    dbi.puts "system: #{XMigra::MSSQLSpecifics::SYSTEM_NAME}"
    if Utils.dry_run?
      dbi.puts "#{DRY_RUN_MARKER}: true"
    end
  end
  
  # TODO: Create migration to check required filegroups and files
  
  # Migration: Check CLR assemblies
  create_migration(
    "check-clr-assemblies",
    "Check expected CLR assemblies have been created.",
    ClrAssembly.setup_sql + "\n" + joined_modobj_sql(clr_assemblies),
    clr_assemblies.map(&:name).sort
  )
  
  # Migration: Create roles
  create_migration(
    "create-roles",
    "Create roles for accessing the database.",
    (roles.map(&:definition_sql) + roles.map(&:authorization_sql).compact + roles.map(&:membership_sql)).join("\n"),
    roles.map(&:name).sort
  )
  
  # Migration: Create schemas
  create_migration(
    "create-schemas",
    "Create schemas for containing database objects and controlling access.",
    joined_modobj_sql(schemas, sep: "\nGO\n"),
    schemas.map(&:name).sort
  )
  
  # Migration: Create scalar types
  create_migration(
    "create-scalar-types",
    "Create user-defined scalar types.",
    joined_modobj_sql(types),
    types.map {|t| [t.schema, t.qualified_name]}.flatten.uniq.sort
  )
  
  # Migration: Create synonyms
  create_migration(
    "create-synonyms",
    "Create synonyms for other objects in the database.",
    joined_modobj_sql(synonyms),
    synonyms.map {|s| [s.schema, s.qualified_name]}.flatten
  )
  
  # Migration: Create CLR types that don't exist
  create_migration(
    "create-clr-types",
    "Create CLR types (unless already existing).",
    ClrType.setup_sql + "\n" + joined_modobj_sql(clr_types),
    clr_types.map(&:qualified_name).sort
  )
  
  tables.each do |table|
    # Migration: Create table
    qual_name = [table.schema, table.name].join('.')
    create_migration(
      "create-table #{qual_name}",
      "Create #{qual_name} table.",
      table.to_sql,
      [table.schema, qual_name]
    )
  end
  
  # Migration: Add column defaults
  create_migration(
    "add-column-defaults",
    "Add default constraints to table columns.",
    joined_modobj_sql(column_defaults),
    column_defaults.map {|d| [d.schema, d.qualified_table, d.qualified_column, d.qualified_name].compact}.flatten.uniq.sort
  )
  
  # Migration: Add primary key and unique constraints
  create_migration(
    "add-primary-key-and-unique-constraints",
    "Add primary key and unique constraints.",
    joined_modobj_sql(pku_constraints),
    pku_constraints.map {|c| [c.schema, c.qualified_table, c.qualified_name].compact}.flatten.uniq.sort
  )
  
  # Migration: Add foreign key constraints
  create_migration(
    "add-foreign-key-constraints",
    "Add foreign key constraints.",
    joined_modobj_sql(foreign_keys),
    foreign_keys.map {|c| [c.schema, c.qualified_table, c.qualified_name].compact}.flatten.uniq.sort
  )
  
  # Migration: Add check constraints
  create_migration(
    "add-check-constraints",
    "Add check constraints.",
    joined_modobj_sql(check_constraints),
    check_constraints.map {|c| [c.schema, c.qualified_table, c.qualified_name].compact}.flatten.uniq.sort
  )
  
  # Migration: Add DML triggers
  create_migration(
    "add-triggers",
    "Add triggers.",
    joined_modobj_sql(dml_triggers, sep: DmlTriggerHandler.ddl_block_separator) + "\n",
    dml_triggers.map do |t|
      [t.schema, t.table.qualified_name, t.qualified_name].compact
    end.flatten.uniq.sort
  ) unless dml_triggers.empty?
  
  # Check that no super-permissions reference a view, user-defined function, or stored procedure
  access_object_names = (views + udfs + procedures).map {|ao| ao.qualified_name}
  permissions.map {|p| p.super_permissions}.flatten.select do |p|
    access_object_names.include?(p.target)
  end.group_by {|p| p.target}.tap do |problems|
    raise UnsupportedFeatureError.new(
      "#{problems[0].target} cannot be granted the required permission(s)."
    ) if problems.length == 1
    
    raise UnsupportedFeatureError.new(
      (
        ["The required permissions cannot be granted on:"] +
        problems.map {|p| '    ' + p.target}
      ).join("\n")
    ) unless problems.empty?
  end
  
  # Write a migration with all super-permissions
  super_permissions = permissions.map {|p| p.super_permissions_sql}.inject([], :concat)
  create_migration(
    "add-super-permissions",
    "Add permissions that confound the normal GRANT model.",
    super_permissions.join("\n"),
    permissions.map {|p| p.super_permissions.map(&:unscoped_target)}.flatten.uniq.sort
  ) unless super_permissions.empty?
  
  indexes.each do |index|
    write_index_def(index)
  end
  
  write_statistics
  
  aggregates.each do |agg|
    create_migration(
      "register-#{agg.qualified_name}-aggregate",
      "Register the CLR aggregate function #{agg.qualified_name}",
      agg.to_sql.join("\nGO\n"),
      [agg.schema, agg.qualified_name]
    )
  end
  
  views.each do |view|
    write_access_def(view, 'view')
  end
  
  udfs.each do |udf|
    write_access_def(udf, 'function')
  end
  
  procedures.each do |procedure|
    write_access_def(procedure, 'stored procedure')
  end
  
  @schema_dir.join(XMigra::SchemaManipulator::PERMISSIONS_FILE).open('w') do |p_file|
    YAML.dump(
      permissions.map do |p|
        p.regular_permissions_graph.map do |k, v|
          [k, {XMigra::MSSQLSpecifics.strip_identifier_quoting(p.subject) => v}]
        end.to_h
      end.inject({}) do |r, n|
        r.update(n) {|k, lv, rv| lv.merge rv}
      end,
      p_file
    )
  end
  
  create_adoption_script
end

#handle_check_constraint_element(parse) ⇒ Object 



121
122
123
 
# File 'lib/mkxms/mssql/database_handler.rb', line 121

def handle_check_constraint_element(parse)
  parse.delegate_to CheckConstraintHandler, check_constraints
end

#handle_clr_aggregate_element(parse) ⇒ Object 



153
154
155
 
# File 'lib/mkxms/mssql/database_handler.rb', line 153

def handle_clr_aggregate_element(parse)
  parse.delegate_to ClrArggregateHandler, aggregates
end

#handle_clr_assembly_element(parse) ⇒ Object 



165
166
167
 
# File 'lib/mkxms/mssql/database_handler.rb', line 165

def handle_clr_assembly_element(parse)
  parse.delegate_to ClrAssemblyHandler, clr_assemblies
end

#handle_clr_function_element(parse) ⇒ Object 



149
150
151
 
# File 'lib/mkxms/mssql/database_handler.rb', line 149

def handle_clr_function_element(parse)
  parse.delegate_to ClrFunctionHandler, udfs
end

#handle_clr_stored_procedure_element(parse) ⇒ Object 



141
142
143
 
# File 'lib/mkxms/mssql/database_handler.rb', line 141

def handle_clr_stored_procedure_element(parse)
  parse.delegate_to ClrStoredProcedureHandler, procedures
end

#handle_clr_type_element(parse) ⇒ Object 



169
170
171
 
# File 'lib/mkxms/mssql/database_handler.rb', line 169

def handle_clr_type_element(parse)
  parse.delegate_to ClrTypeHandler, clr_types
end

#handle_database_element(parse) ⇒ Object 



74
75
 
# File 'lib/mkxms/mssql/database_handler.rb', line 74

def handle_database_element(parse)
end

#handle_default_constraint_element(parse) ⇒ Object 



105
106
107
 
# File 'lib/mkxms/mssql/database_handler.rb', line 105

def handle_default_constraint_element(parse)
  parse.delegate_to DefaultConstraintHandler, column_defaults
end

#handle_denied_element(parse) ⇒ Object 



161
162
163
 
# File 'lib/mkxms/mssql/database_handler.rb', line 161

def handle_denied_element(parse)
  parse.delegate_to PermissionHandler, permissions
end

#handle_dml_trigger_element(parse) ⇒ Object 



173
174
175
 
# File 'lib/mkxms/mssql/database_handler.rb', line 173

def handle_dml_trigger_element(parse)
  parse.delegate_to DmlTriggerHandler, dml_triggers
end

#handle_filegroup_element(parse) ⇒ Object 



77
78
79
 
# File 'lib/mkxms/mssql/database_handler.rb', line 77

def handle_filegroup_element(parse)
  parse.delegate_to FilegroupHandler, filegroups
end

#handle_foreign_key_element(parse) ⇒ Object 



113
114
115
 
# File 'lib/mkxms/mssql/database_handler.rb', line 113

def handle_foreign_key_element(parse)
  parse.delegate_to ForeignKeyHandler, foreign_keys
end

#handle_fulltext_document_type_element(parse) ⇒ Object 



81
82
83
 
# File 'lib/mkxms/mssql/database_handler.rb', line 81

def handle_fulltext_document_type_element(parse)
  # TODO: Check that these types are registered in the target instance
end

#handle_granted_element(parse) ⇒ Object 



157
158
159
 
# File 'lib/mkxms/mssql/database_handler.rb', line 157

def handle_granted_element(parse)
  parse.delegate_to PermissionHandler, permissions
end

#handle_index_element(parse) ⇒ Object 



125
126
127
 
# File 'lib/mkxms/mssql/database_handler.rb', line 125

def handle_index_element(parse)
  parse.delegate_to IndexHandler, indexes
end

#handle_primary_key_element(parse) ⇒ Object 



109
110
111
 
# File 'lib/mkxms/mssql/database_handler.rb', line 109

def handle_primary_key_element(parse)
  parse.delegate_to PrimaryKeyHandler, pku_constraints
end

#handle_role_element(parse) ⇒ Object 



97
98
99
 
# File 'lib/mkxms/mssql/database_handler.rb', line 97

def handle_role_element(parse)
  parse.delegate_to RoleHandler, roles
end

#handle_schema_element(parse) ⇒ Object 



85
86
87
 
# File 'lib/mkxms/mssql/database_handler.rb', line 85

def handle_schema_element(parse)
  parse.delegate_to SchemaHandler, schemas
end

#handle_statistics_element(parse) ⇒ Object 



129
130
131
 
# File 'lib/mkxms/mssql/database_handler.rb', line 129

def handle_statistics_element(parse)
  parse.delegate_to StatisticsHandler, statistics
end

#handle_stored_procedure_element(parse) ⇒ Object 



137
138
139
 
# File 'lib/mkxms/mssql/database_handler.rb', line 137

def handle_stored_procedure_element(parse)
  parse.delegate_to StoredProcedureHandler, procedures
end

#handle_synonym_element(parse) ⇒ Object 



177
178
179
 
# File 'lib/mkxms/mssql/database_handler.rb', line 177

def handle_synonym_element(parse)
  parse.delegate_to SynonymHandler, synonyms
end

#handle_table_element(parse) ⇒ Object 



101
102
103
 
# File 'lib/mkxms/mssql/database_handler.rb', line 101

def handle_table_element(parse)
  parse.delegate_to TableHandler, tables
end

#handle_table_type_element(parse) ⇒ Object 



93
94
95
 
# File 'lib/mkxms/mssql/database_handler.rb', line 93

def handle_table_type_element(parse)
  parse.delegate_to TableTypeHandler, types
end

#handle_type_element(parse) ⇒ Object 



89
90
91
 
# File 'lib/mkxms/mssql/database_handler.rb', line 89

def handle_type_element(parse)
  parse.delegate_to ScalarTypeHandler, types
end

#handle_unique_constraint_element(parse) ⇒ Object 



117
118
119
 
# File 'lib/mkxms/mssql/database_handler.rb', line 117

def handle_unique_constraint_element(parse)
  parse.delegate_to UniqueConstraintHandler, pku_constraints
end

#handle_user_defined_function_element(parse) ⇒ Object 



145
146
147
 
# File 'lib/mkxms/mssql/database_handler.rb', line 145

def handle_user_defined_function_element(parse)
  parse.delegate_to FunctionHandler, udfs
end

#handle_view_element(parse) ⇒ Object 



133
134
135
 
# File 'lib/mkxms/mssql/database_handler.rb', line 133

def handle_view_element(parse)
  parse.delegate_to ViewHandler, views
end

#joined_modobj_sql(ary, sep: "\n") ⇒ Object 



393
394
395
 
# File 'lib/mkxms/mssql/database_handler.rb', line 393

def joined_modobj_sql(ary, sep: "\n")
  ary.map(&:to_sql).join(sep)
end

#migration_chain ⇒ Object 



380
381
382
 
# File 'lib/mkxms/mssql/database_handler.rb', line 380

def migration_chain
  @migration_chain ||= XMigra::NewMigrationAdder.new(@schema_dir)
end

#write_access_def(access_obj, obj_type) ⇒ Object 



397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
 
# File 'lib/mkxms/mssql/database_handler.rb', line 397

def write_access_def(access_obj, obj_type)
  # Use Psych mid-level emitting API to specify literal syntax for SQL
  def_tree = Psych::Nodes::Mapping.new
  ["define", obj_type, "sql"].each do |s|
    def_tree.children << Psych::Nodes::Scalar.new(s)
  end
  def_tree.children << Psych::Nodes::Scalar.new(access_obj.to_sql, nil, nil, false, true,
                                                Psych::Nodes::Scalar::LITERAL)
  unless (references = access_obj.respond_to?(:references) ? access_obj.references : []).empty?
    def_tree.children << Psych::Nodes::Scalar.new('referencing')
    def_tree.children << (ref_seq = Psych::Nodes::Sequence.new)
    references.each do |r|
      ref_seq.children << Psych::Nodes::Scalar.new(r)
    end
  end
  
  def_doc = Psych::Nodes::Document.new
  def_doc.children << def_tree
  def_stream = Psych::Nodes::Stream.new
  def_stream.children << def_doc
  
  access_dir = @schema_dir.join(XMigra::SchemaManipulator::ACCESS_SUBDIR)
  access_dir.mkpath
  access_dir.join(access_obj.qualified_name + '.yaml').open('w') do |ao_file|
    def_str = def_stream.to_yaml(nil, line_width: -1)
    ao_file.puts(def_str)
  end
end

#write_index_def(index) ⇒ Object

Raises:



426
427
428
429
430
431
432
433
434
435
436
437
438
 
# File 'lib/mkxms/mssql/database_handler.rb', line 426

def write_index_def(index)
  indexes_dir = @schema_dir.join(XMigra::SchemaManipulator::INDEXES_SUBDIR)
  indexes_dir.mkpath
  index_path = indexes_dir.join(index.name + '.yaml')
  
  raise UnsupportedFeatureError.new(
    "Index file #{index_path} already exists."
  ) if index_path.exist?
  
  index_path.open('w') do |index_file|
    YAML.dump({'sql' => index.to_sql}, index_file, line_width: -1)
  end
end

#write_statistics ⇒ Object 



440
441
442
443
444
445
446
447
448
449
450
 
# File 'lib/mkxms/mssql/database_handler.rb', line 440

def write_statistics
  statistics_path = @schema_dir.join(XMigra::MSSQLSpecifics::STATISTICS_FILE)
  
  statistics_path.open('w') do |stats_file|
    YAML.dump(
      Hash[statistics.map(&:name_params_pair)],
      stats_file,
      line_width: -1
    )
  end
end