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