Class BC3::DBconverter

  1. bin/bc3_db_compare.rb
Parent: Object

Class to support comparison of database content.

Define a converter for Beyond Compare 3.

Constants

VERSION = '0.2.0'
EXITCODES = { # => 1 normal ruby errors (syntax error) :sourcefile_missing => 2, :configuration_error => 3, :configuration_incomplete => 4, :db_connection_error => 10, :adapter_unknown => 11, :sequel_error => 12, :db_table_missing => 20, :dataset_error => 30, :argv_wrong => 90, }   Exitcodes
OBLIGATORY_KEYS = %w{adapter db tab}
VALID_KEYS = OBLIGATORY_KEYS + %w{ server password username header schema data headline select filter order sql show_sql }

Attributes

filter [RW] (re)-define where-clause
header [RW] add header information
headline [RW] add heading data to csv
order [RW] (re)-define order-clause
select [RW] (re)-define select-clause (what fields are needed)
sql [W] (re)-define raw sql -clause. This inactivates select, filter and order

Public class methods

new ( source_filename = nil, target_filename = nil, log_filename = nil )

Expect a configuration file and a filename to save the result.

Without target file name, an empty object is created.

[show source]
# File bin/bc3_db_compare.rb, line 109
  def initialize( source_filename = nil, target_filename = nil, log_filename = nil )
    if target_filename
      
      if log_filename
        $log.outputters << Log4r::FileOutputter.new('log', :filename => log_filename ) 
      end
      
      @header = true  #Default
      
      $log.info("Start conversion (%s)%s => %s" % [DBconverter::VERSION, source_filename, target_filename]) if $log.info?
      #set attributes
      $log.debug("Open %s" % source_filename) if $log.debug?
      check_configuration_file(source_filename)
      analyse_configuration_file(File.read(source_filename), File.dirname(source_filename))
      
      prepare_dataset()
      save_target(target_filename)
    end
  end

Public instance methods

analyse_configuration_file (configuration, dirname = nil)

Open and analyse configuration file

Examples for configuration files

Compare data of table ‘languages’ in sqlite data base file ‘test.db’ in a csv-file.

adapter: sqlite
db: test.db
tab: languages
data: csv_tab

Obligatory configuration settings

adapter

Defines the type of DB-connection.

Supported:

  • ado (server required, username and password optional)
  • sqlite

db

Name of the @db.

  • ado: name of the database.
  • sqlite: filename

Warning! BC3 calls the script from bc3-folder. The filename should be an absolute path.

tab

Table name for comparison.

Optional configuration settings

schema

if true, the table schema (meta data of table) is added on top.

Adapt the selection

select

Restrict columns. See Sequel::@datasetselect sequel.rubyforge.org/rdoc/classes/Sequel/@dataset.html#M000439

filter

Where-clause. Restrict rows. See Sequel::@datasetfilter

order

See Sequel::@datasetorder sequel.rubyforge.org/rdoc/classes/Sequel/@dataset.html#M000429

List of column names.

sql

You can define a sql-command.

This sql-command will ignore all other selection commands (select, filter, order)

[show source]
# File bin/bc3_db_compare.rb, line 208
  def analyse_configuration_file(configuration, dirname = nil)
    
    $log.debug("Analyse Configuration") if $log.debug?
    options = {}
    begin
      options = YAML.load(configuration)
    rescue ArgumentError => err
      bc3_exit(:configuration_error, "Configuration file has error #{err}")
    end
    # check obligatory parameters
    OBLIGATORY_KEYS.each{|opt|
      if ! options[opt]
        bc3_exit(:configuration_incomplete,"Option %s missing" % opt)
      end
    }
    options.each{|key, value|
      if ! VALID_KEYS.include?(key)
        $log.warn("Undefined option %s" % key) if $log.warn?
      end
    }
    
    @dbname = options['db']
    case @adapter = options['adapter']
      when 'ado'
          if ! @server = options['server']
            bc3_exit(:db_connection_error, "No Server given" % @dbname)
          end
          if ! @dbname
            bc3_exit(:db_connection_error, "No @db given" % @dbname)
          end
            @username = options['username'] #optional, not needed for all logins (single sign on...)
            @password = options['password'] #optional, not needed for all logins (single sign on...)
      when 'sqlite'
        #set the path relative to configuration file
        @dbname = File.join(dirname, @dbname) if dirname and ! File.exist?(@dbname)
        if ! File.exist?(@dbname)
          bc3_exit(:db_connection_error, "DB-File %s not found in %s" % [@dbname, Dir.pwd])
        end
    else
      bc3_exit(:adapter_unknown, "Adapter %s not defined " % @adapter.inspect)
    end
    @tabsym = options['tab'].downcase.to_sym

    @select = [options['select']].flatten if options['select']
    @filter  = options['filter']
    @order  = options['order']
    @sql  = options['sql']

    @header = options['header']  if options.has_key?('header') #add header (information about DB, table,,,)
    @show_schema = options['schema']
    @show_sql = options['show_sql']
    @headline  = options['headline']  #add headline for csv
    
    case @show_data = options['data']
      when nil  #no data required
      when 'inspect', 'csv_tab'
        $log.info("Define data conversion routine #{@show_data}") if $log.info?
      else  
        $log.error("Data conversion routine #{@show_data} not found") if $log.error?
    end
  end
bc3_exit ( exitcode, description)

Leave the script after an error.

For unit test, this may be redefined to throw an exception or similar.

[show source]
# File bin/bc3_db_compare.rb, line 134
  def bc3_exit( exitcode, description)
      $log.error(description) if $log.error?
      exit EXITCODES[exitcode]
  end
build_csv_line (line, sep, dataset = @dataset)

Build one character separated value-line.

[show source]
# File bin/bc3_db_compare.rb, line 440
  def build_csv_line(line, sep, dataset = @dataset)
    vals = []
    dataset.columns.each{|col|
      if line[col].respond_to?(:include?) and line[col].include?(sep)
        $log.warn("Data contain separator #{sep} (#{line[col]})") if $log.warn?
        vals <<  "\"#{line[col]}\""
      else
        vals <<  line[col].to_s
      end
      
    }
    vals.join(sep)
  end
build_target ()

Build content for target file

[show source]
# File bin/bc3_db_compare.rb, line 366
  def build_target()

      bc3_exit(:db_connection_error, "Database missing (forgot #prepare_dataset ?)") unless @db

      txt = []
      if @header
        txt <<  "Check table %s.%s" % [@dbname, @tabsym]
      end

      
      if ! @db.table_exists?(@tabsym)
        txt <<  "Table #{@tabsym} not found"
        bc3_exit(:db_table_missing, "Table #{@tabsym} not found")
      end

??
      if @show_schema
        $log.debug("Get table schema #{@tabsym}") if $log.debug?
        txt <<  "Table schema #{@tabsym}" if @header
        txt <<  @db.schema(@tabsym).to_yaml
      end

??
      if @show_sql
        $log.debug("Write SQL-command (%s)" % (@sql ? 'raw sql' : 'dataset')) if $log.debug?
        
        txt <<  "SQL for selection:" if @header
        txt <<  ( @sql ? @sql : @dataset.sql )
      end

??
      if @show_data
        $log.debug("Get data from #{@tabsym}") if $log.debug?
        txt <<  "Data content of table #{@tabsym}:" if @header
        
        first_line = true
        @dataset.each{|line|
          case @show_data
            when 'inspect'
              txt <<  line.inspect
            when 'csv_tab'
              case line
                when Array  #@sql defined
                txt << line.join("\t")
                when Hash #Sequel::Dataset
                  if first_line and @headline
                    txt << @dataset.columns.map{|x|x.to_s}.join("\t") 
                  end
                  txt << build_csv_line(line, "\t")
                else
                  bc3_exit(:dataset_error , "Type of dataset is undefined #{line.inspect}")
              end #case line
            else  
              $log.error("Data conversion routine #{@show_data} not found") if $log.error?
              txt <<  "Data conversion routine #{@show_data} not found"
          end
          first_line = false
        } 
      else#ok, maybe you only want to compare the scheme.
        $log.info("No data output") if $log.debug?  
      end
    txt
  end
check_configuration_file (filename)

Check existence of configuration file.

[show source]
# File bin/bc3_db_compare.rb, line 141
  def check_configuration_file(filename)
    if ! filename or ! File.exist?(filename)
      bc3_exit(:sourcefile_missing, "Open %s" % filename.inspect)
    end
  end
connect ()

Connect DB

[show source]
# File bin/bc3_db_compare.rb, line 287
  def connect()
    $log.debug("Connect DB %s" % @dbname) if $log.debug?
    begin
      case @adapter
        when 'ado'
          @db = Sequel.connect(
            :adapter=>'ado', 
            :host     =>@server, 
            :database=>@dbname, 
            :user       => @username, 
            :password=>@password 
          )    
        when 'sqlite'
          @db = Sequel.sqlite(@dbname)
        else
          bc3_exit(:adapter_unknown, "Adapter %s not defined " % @adapter.inspect)
      end #@adapter
      #Test connection
      if @db.test_connection
        $log.info("Connected to DB %s" % @dbname) if $log.info?
      else
        bc3_exit(:db_connection_error, "DB-Connection error")
      end
    rescue Sequel::DatabaseError, Sequel::DatabaseConnectionError => err
      bc3_exit(:sequel_error, "DB-Connection error: %s" % err.inspect)
    end
    @db
  end
prepare_dataset (tabsym = @tabsym, select = @select, filter = @filter, order = @order )

Prepare @dataset.

Defines only the accessor. There is no check, if the data are correct.

[show source]
# File bin/bc3_db_compare.rb, line 321
  def prepare_dataset(tabsym = @tabsym, select = @select, filter = @filter, order = @order )
    @db = connect
    
    if ! tabsym
      $log.error("No table name available") if $log.error?
      return {}
    elsif ! @db.table_exists?(tabsym)
      bc3_exit(:db_table_missing, "DB error: Table #{tabsym} not defined")
    end
    
    if @sql
      $log.info("Use raw sql for dataset (#{@sql.inspect})") if $log.info?
      return @dataset = @db.execute(@sql)
    end
    
    @dataset = @db[tabsym]
    if select
      $log.debug("Restrict rows to #{select.inspect}") if $log.debug?
      @select.each{|row|
        $log.warn("Rows definition for filter is no symbol: #{row.inspect}") unless row.is_a?(Symbol)
      }
      @dataset = @dataset.select(*select)
    end
    if filter
      $log.debug("Restrict columns to #{filter.inspect}") if $log.debug?
      @dataset = @dataset.filter(filter)
    end
    if order
      $log.debug("Order columns by #{order.inspect}") if $log.debug?
      @dataset = @dataset.order(order)
    end    
    @dataset
  end
save_target (filename = @target_filename)

Build target file

[show source]
# File bin/bc3_db_compare.rb, line 357
  def save_target(filename = @target_filename)
    $log.info("Save %s" % [filename]) if $log.info?
    File.open(filename, 'w'){|f|
      f << build_target.join("\n")
    }
  end