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

Constant Summary collapse

ADOPTION_SQL_FILE = 
"adopt.sql"

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.



38
39
40
 
# File 'lib/mkxms/mssql/database_handler.rb', line 38

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.



42
43
44
 
# File 'lib/mkxms/mssql/database_handler.rb', line 42

def schema_dir
  @schema_dir
end

Instance Method Details

#create_adoption_script ⇒ Object 



331
332
333
334
335
336
337
 
# File 'lib/mkxms/mssql/database_handler.rb', line 331

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 



263
264
265
266
267
268
269
270
 
# File 'lib/mkxms/mssql/database_handler.rb', line 263

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 



119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
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
 
# File 'lib/mkxms/mssql/database_handler.rb', line 119

def create_source_files
  dbinfo_path = @schema_dir.join(XMigra::SchemaManipulator::DBINFO_FILE)
  
  if dbinfo_path.exist?
    raise ProgramArgumentError.new("#{@schema_dir} already contains an XMigra schema")
  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}"
  end
  
  # TODO: Create migration to check required filegroups and files
  
  # 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
  )
  
  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
  )
  
  # 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
  
  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, {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 



87
88
89
 
# File 'lib/mkxms/mssql/database_handler.rb', line 87

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

#handle_database_element(parse) ⇒ Object 



48
49
 
# File 'lib/mkxms/mssql/database_handler.rb', line 48

def handle_database_element(parse)
end

#handle_default_constraint_element(parse) ⇒ Object 



71
72
73
 
# File 'lib/mkxms/mssql/database_handler.rb', line 71

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

#handle_denied_element(parse) ⇒ Object 



115
116
117
 
# File 'lib/mkxms/mssql/database_handler.rb', line 115

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

#handle_filegroup_element(parse) ⇒ Object 



51
52
53
 
# File 'lib/mkxms/mssql/database_handler.rb', line 51

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

#handle_foreign_key_element(parse) ⇒ Object 



79
80
81
 
# File 'lib/mkxms/mssql/database_handler.rb', line 79

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

#handle_fulltext_document_type_element(parse) ⇒ Object 



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

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

#handle_granted_element(parse) ⇒ Object 



111
112
113
 
# File 'lib/mkxms/mssql/database_handler.rb', line 111

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

#handle_index_element(parse) ⇒ Object 



91
92
93
 
# File 'lib/mkxms/mssql/database_handler.rb', line 91

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

#handle_primary_key_element(parse) ⇒ Object 



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

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

#handle_role_element(parse) ⇒ Object 



63
64
65
 
# File 'lib/mkxms/mssql/database_handler.rb', line 63

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

#handle_schema_element(parse) ⇒ Object 



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

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

#handle_statistics_element(parse) ⇒ Object 



95
96
97
 
# File 'lib/mkxms/mssql/database_handler.rb', line 95

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

#handle_stored_procedure_element(parse) ⇒ Object 



103
104
105
 
# File 'lib/mkxms/mssql/database_handler.rb', line 103

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

#handle_table_element(parse) ⇒ Object 



67
68
69
 
# File 'lib/mkxms/mssql/database_handler.rb', line 67

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

#handle_unique_constraint_element(parse) ⇒ Object 



83
84
85
 
# File 'lib/mkxms/mssql/database_handler.rb', line 83

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

#handle_user_defined_function_element(parse) ⇒ Object 



107
108
109
 
# File 'lib/mkxms/mssql/database_handler.rb', line 107

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

#handle_view_element(parse) ⇒ Object 



99
100
101
 
# File 'lib/mkxms/mssql/database_handler.rb', line 99

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

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



272
273
274
 
# File 'lib/mkxms/mssql/database_handler.rb', line 272

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

#migration_chain ⇒ Object 



259
260
261
 
# File 'lib/mkxms/mssql/database_handler.rb', line 259

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

#write_access_def(access_obj, obj_type) ⇒ Object 



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
 
# File 'lib/mkxms/mssql/database_handler.rb', line 276

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:



305
306
307
308
309
310
311
312
313
314
315
316
317
 
# File 'lib/mkxms/mssql/database_handler.rb', line 305

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 



319
320
321
322
323
324
325
326
327
328
329
 
# File 'lib/mkxms/mssql/database_handler.rb', line 319

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