class Dbsketch::Automation::DatabaseImporter
Public Class Methods
new(db)
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 15 def initialize db ### Preconditions raise ArgumentError, "database is not a Dbsketch::Automation::DatabaseProxy" unless db.is_a? DatabaseProxy ### @db = db @table_importer = TableImporter.new @db end
Public Instance Methods
import(options: {})
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 23 def import options: {} options = { :index_white_list => [], :index_black_list => [], :operation_white_list => [], :operation_black_list => [], :table_white_list => [], :table_black_list => [], :view_white_list => [], :view_black_list => [] }.merge options options[:index_white_list] = (options[:index_white_list].is_a? Array) ? options[:index_white_list] : [options[:index_white_list]] options[:index_black_list] = (options[:index_black_list].is_a? Array) ? options[:index_black_list] : [options[:index_black_list]] options[:operation_white_list] = (options[:operation_white_list].is_a? Array) ? options[:operation_white_list] : [options[:operation_white_list]] options[:operation_black_list] = (options[:operation_black_list].is_a? Array) ? options[:operation_black_list] : [options[:operation_black_list]] options[:table_white_list] = (options[:table_white_list].is_a? Array) ? options[:table_white_list] : [options[:table_white_list]] options[:table_black_list] = (options[:table_black_list].is_a? Array) ? options[:table_black_list] : [options[:table_black_list]] options[:view_white_list] = (options[:view_white_list].is_a? Array) ? options[:view_white_list] : [options[:view_white_list]] options[:view_black_list] = (options[:view_black_list].is_a? Array) ? options[:view_black_list] : [options[:view_black_list]] ### Preconditions raise ArgumentError, "options[:operation_white_list] and options[:operation_black_list] are exclusive." if not options[:operation_white_list].empty? and not options[:operation_black_list].empty? raise ArgumentError, "options[:table_white_list] and options[:table_black_list] are exclusive." if not options[:table_white_list].empty? and not options[:table_black_list].empty? raise ArgumentError, "options[:view_white_list] and options[:view_black_list] are exclusive." if not options[:view_white_list].empty? and not options[:view_black_list].empty? ### imported_database = Dbsketch::Model::Database.new @db.fetch("select * from sys.tables").all.each do |db_table| if (options[:table_white_list].empty? or name_included_in_list? db_table[:name], options[:table_white_list]) and not name_included_in_list? db_table[:name], options[:table_black_list] table = import_table(db_table[:name]) imported_database.add table import_indexes imported_database, table, db_table, options import_triggers imported_database, table, db_table end end imported_database.tables.each do |table| import_foreign_keys imported_database, table end import_views imported_database, options import_functions imported_database, options import_procedures imported_database, options imported_database end
import_table(table_name)
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 67 def import_table table_name @table_importer.import table_name end
Private Instance Methods
import_foreign_keys(database, table)
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 78 def import_foreign_keys database, table db_table = @db.fetch("select object_id from sys.tables where name = '#{table.name}'").all.first @db.fetch("select object_id, name from sys.foreign_keys where parent_object_id = #{db_table[:object_id]}").all.each do |db_key| db_key_column = @db.fetch("select constraint_column_id, referenced_object_id, referenced_column_id from sys.foreign_key_columns where constraint_object_id = #{db_key[:object_id]}").all.first db_constricted_column = @db.fetch("select name from sys.columns where object_id = #{db_table[:object_id]} and column_id = #{db_key_column[:constraint_column_id]}").all.first db_referenced_table = @db.fetch("select object_id, name from sys.tables where object_id = #{db_key_column[:referenced_object_id]}").all.first db_referenced_column = @db.fetch("select name from sys.columns where object_id = #{db_referenced_table[:object_id]} and column_id = #{db_key_column[:referenced_column_id]}").all.first name = db_key[:name] constricted_column = table[db_constricted_column[:name]] referenced_table = database[db_referenced_table[:name]] referenced_column = referenced_table[db_referenced_column[:name]] table.add Dbsketch::Model::ForeignKey.new name, constricted_column, referenced_table, referenced_column end end
import_functions(database, options)
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 94 def import_functions database, options @db.fetch("select name, object_definition(object_id) as definition from sys.objects where type in ('FN', 'FT')").all.each do |db_function| if (options[:operation_white_list].empty? or name_included_in_list? db_function[:name], options[:operation_white_list]) and not name_included_in_list? db_function[:name], options[:operation_black_list] name = db_function[:name] definition = db_function[:definition].strip begin_index = definition.index("begin") raw_arguments_line = definition[0..begin_index - 1].sub(/^create function [\[\w\.\]]+\s*\(/, '').sub(/\s*as\s*$/, '') returns_index = raw_arguments_line.index("returns") raw_arguments = raw_arguments_line[0..returns_index - 1].strip returns = raw_arguments_line[returns_index, (raw_arguments_line.length - raw_arguments.length)].sub(/^returns\s*/, '') raw_arguments = raw_arguments.sub(/\)$/, '').strip.split(/,\s?@/) arguments = raw_arguments.map { |arg| arg.start_with?('@') ? arg : "@#{arg}" } matches = definition.match /begin(.*)end/m algo = matches[1].strip.gsub(/\r\n/m, "\n") database.add Dbsketch::Model::Function.new(name, :arguments => arguments, :returns => returns, :algo => algo) end end end
import_indexes(database, table, db_table, options)
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 118 def import_indexes database, table, db_table, options @db.fetch("select index_id, name from sys.indexes where object_id = #{db_table[:object_id]} and is_primary_key = 0 and is_unique_constraint = 0").all.each do |db_index| if (options[:index_white_list].empty? or name_included_in_list? db_index[:name], options[:index_white_list]) and not name_included_in_list? db_index[:name], options[:index_black_list] name = db_index[:name] columns = [] if nil != name @db.fetch("select column_id from sys.index_columns where object_id = #{db_table[:object_id]} and index_id = #{db_index[:index_id]}").all.each do |db_index_col| db_column = @db.fetch("select name from sys.columns where object_id = #{db_table[:object_id]} and column_id = #{db_index_col[:column_id]}").all.first columns << table[db_column[:name]] end database.add Dbsketch::Model::Index.new name, table, columns end end end end
import_procedures(database, options)
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 134 def import_procedures database, options @db.fetch("select name, object_definition(object_id) as definition from sys.procedures").all.each do |db_proc| if (options[:operation_white_list].empty? or name_included_in_list? db_proc[:name], options[:operation_white_list]) and not name_included_in_list? db_proc[:name], options[:operation_black_list] name = db_proc[:name] definition = db_proc[:definition].strip.gsub(/\r\n/m, "\n") begin_index = definition.index("begin") raw_arguments = definition[0..begin_index - 1].sub(/^create procedure [\[\w\.\]]+/, '').sub(/\s*as\s*$/, '') raw_arguments = raw_arguments.strip.split(/,\s?@/) arguments = raw_arguments.map { |arg| arg.start_with?('@') ? arg : "@#{arg}" } matches = definition.match /begin(.*)end/m algo = matches[1].strip database.add Dbsketch::Model::Procedure.new(name, :arguments => arguments, :algo => algo) end end end
import_triggers(database, table, db_table)
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 153 def import_triggers database, table, db_table @db.fetch("select name, object_definition(object_id) as definition from sys.triggers where parent_id = #{db_table[:object_id]}").all.each do |db_trigger| name = db_trigger[:name] definition = db_trigger[:definition].strip matches = definition.match /^create trigger (\[?[\w.]*\]?) on (\[?[\w.]*\]?) (for|after|instead of) (insert)?,? ?(update)?,? ?(delete)?/ if nil != matches and matches.captures.count > 5 activation_time = matches[3] + " " + matches[4..6].compact.join(", ") else activation_time = "for ???" end matches = definition.match /begin(.*)end/m if nil != matches and matches.captures.count > 0 algo = matches[1].strip.gsub(/\r\n/m, "\n") else algo = "???" end database.add Dbsketch::Model::Trigger.new name, table, activation_time, :algo => algo, :dependencies => table end end
import_views(database, options)
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 173 def import_views database, options @db.fetch("select table_name, view_definition from information_schema.views").all.each do |db_view| if (options[:view_white_list].empty? or name_included_in_list? db_view[:table_name], options[:view_white_list]) and not name_included_in_list? db_view[:table_name], options[:view_black_list] database.add Dbsketch::Model::View.new db_view[:table_name], db_view[:view_definition].strip.sub(/^create view \w* as (.*);$/, '\1') end end end
name_included_in_list?(name, list)
click to toggle source
# File lib/dbsketch/automation/database_importer.rb, line 73 def name_included_in_list? name, list included = nil != list.find { |element| (element.is_a? Regexp and name.match(element)) or element == name } included end