class Dbsketch::Automation::TableImporter

Public Class Methods

new(db) click to toggle source
# File lib/dbsketch/automation/table_importer.rb, line 13
def initialize db
        ### Preconditions
        raise ArgumentError, "database is not a Dbsketch::Automation::DatabaseProxy" unless db.is_a? DatabaseProxy
        ###
        @db = db
end

Public Instance Methods

import(table_name) click to toggle source
# File lib/dbsketch/automation/table_importer.rb, line 20
def import table_name
        ### Preconditions
        raise ArgumentError, "table_name '#{table_name}' is not a Symbol or a String" unless table_name.is_a? String or table_name.is_a? Symbol
        ###
        db_table = @db.fetch("select object_id, name from sys.tables where name = '#{table_name.to_s}'").all.first
        ### Preconditions
        raise AutomationError, "table '#{table_symbol}' not found in database" if nil == db_table
        ###
        table = Dbsketch::Model::Table.new db_table[:name]
        @db.fetch("select * from information_schema.columns where table_name = '#{db_table[:name]}'").all.each do |db_column|
                table.add(parse_column db_table, db_column)
        end

        primary_key = import_primary_key db_table, table
        table.add primary_key if nil != primary_key

        @db.fetch("select name, definition from sys.check_constraints where parent_object_id = '#{db_table[:object_id]}'").all.each do |db_constraint|
                table.add(Dbsketch::Model::CheckConstraint.new db_constraint[:name], db_constraint[:definition])
        end

        @db.fetch("select name from sys.key_constraints where parent_object_id = #{db_table[:object_id]} and type = 'UQ'").all.each do |db_constraint|
                table.add(import_unique_constraint db_table, table, db_constraint[:name])
        end

        table
end

Private Instance Methods

import_primary_key(db_table, table) click to toggle source
# File lib/dbsketch/automation/table_importer.rb, line 90
def import_primary_key db_table, table
        primary_key = nil
        db_sys_key = @db.fetch("select name from sys.key_constraints where parent_object_id = #{db_table[:object_id]} and type = 'PK'").all.first
        if nil != db_sys_key
                name = db_sys_key[:name]
                pkey_columns = []
                @db.fetch("select column_name from information_schema.constraint_column_usage where constraint_name = '#{name}'").all.each do |db_col|
                        pkey_columns << table[db_col[:column_name]]
                end
                primary_key = Dbsketch::Model::PrimaryKey.new name, pkey_columns
        end
        primary_key
end
import_unique_constraint(db_table, table, constraint_name) click to toggle source
# File lib/dbsketch/automation/table_importer.rb, line 104
def import_unique_constraint db_table, table, constraint_name
        columns = []
        @db.fetch("select column_name from information_schema.constraint_column_usage where constraint_name = '#{constraint_name}'").all.each do |db_col|
                columns << table[db_col[:column_name]]
        end
        Dbsketch::Model::UniqueConstraint.new constraint_name, columns
end
parse_column(db_table, db_column) click to toggle source
# File lib/dbsketch/automation/table_importer.rb, line 72
def parse_column db_table, db_column
        column_name = db_column[:column_name].to_s.strip
        db_sys_column = @db.fetch("select is_nullable, is_computed, is_identity from sys.columns where object_id = #{db_table[:object_id]} and name = '#{column_name}'").all.first
        nullable = db_sys_column[:is_nullable]
        order = db_column[:ordinal_position]
        computed = db_sys_column[:is_computed]
        if computed
                db_sys_computed_column = @db.fetch("select definition, is_persisted from sys.computed_columns where object_id = #{db_table[:object_id]} and name = '#{column_name}'").all.first
                query = db_sys_computed_column[:definition]
                Dbsketch::Model::ComputedColumn.new column_name, query, :nullable => nullable, :order => order, :persisted => db_sys_computed_column[:is_persisted]
        else
                type = parse_type db_column
                identity = db_sys_column[:is_identity]
                default = parse_column_default db_column[:column_default], type
                Dbsketch::Model::Column.new column_name, type, :nullable => nullable, :order => order, :identity => identity, :default => default
        end
end
parse_column_default(db_default, type) click to toggle source
# File lib/dbsketch/automation/table_importer.rb, line 60
def parse_column_default db_default, type
        default = db_default
        if nil != default
                if :numeric == type.category or (:boolean == type.category and default.match(/\(\([01]\)\)/))
                        default = default.gsub(/[\D]*/, '').to_i
                elsif default.is_a? String
                        default = "'#{default.gsub(/^\('(.*)'\)$/, '\1')}'"
                end
        end
        default
end
parse_type(db_column) click to toggle source
# File lib/dbsketch/automation/table_importer.rb, line 49
def parse_type db_column
        sizes = []
        if nil != db_column[:character_maximum_length]
                sizes << db_column[:character_maximum_length]
        else
                sizes << db_column[:numeric_precision] if 'int' != db_column[:data_type] and nil != db_column[:numeric_precision]
                sizes << db_column[:numeric_scale] if 'int' != db_column[:data_type] and nil != db_column[:numeric_scale] and 0 != db_column[:numeric_scale]
        end
        Dbsketch::Model::Type.new db_column[:data_type], sizes
end