Paul Gallagher avatar Paul Gallagher committed 658875b

add basic integration test framework, and db setup notes in README

Comments (0)

Files changed (4)

 Indexed table friendships
 Table creation 0 min, loading 1 min, indexing 0 min, total 1 min
 
+== Running tests
+
+
+Integration tests require suitable mysql and postgres databases to be setup in advance:
+
+mysql on localhost:3306
+- database created called "mysql2psql_test"
+- user setup for "mysql2psql" with no password
+- e.g. 
+  mysqladmin -uroot -p create mysql2psql_test
+  mysql -uroot -p -e "grant all on mysql2psql_test.* to 'mysql2psql'@'localhost';"
+  # verify connecction:
+  mysql -umysql2psql -e "select database(),'yes' as connected" mysql2psql_test
+  
+postgres on localhost:5432
+- database created called "mysql2psql_test" 
+- role (user) access setup for "mysql2psql" with no password
+- e.g.
+  psql postgres -c "create role mysql2psql with login"
+  psql postgres -c "create database mysql2psql_test with owner mysql2psql encoding='UTF8'"
+  # verify connection:
+  psql mysql2psql_test -U mysql2psql -c "\c"
+
+==
 # note from mgkimsal
 I'm still having trouble with bit(1)/boolean fields
 workaround I've found is to put output in file

lib/mysql2psql/mysqlreader.rb

 
 class Mysql2psql
 
-class MysqlReader
-  class Field
-  end
+  class MysqlReader
+    class Field
+    end
   
-  class Table
-    attr_reader :name
+    class Table
+      attr_reader :name
     
-    def initialize(reader, name)
-      @reader = reader
-      @name = name
-    end
+      def initialize(reader, name)
+        @reader = reader
+        @name = name
+      end
     
-    @@types = %w(tiny enum decimal short long float double null timestamp longlong int24 date time datetime year set blob string var_string char).inject({}) do |list, type|
-      list[eval("::Mysql::Field::TYPE_#{type.upcase}")] = type
-      list
-    end
+      @@types = %w(tiny enum decimal short long float double null timestamp longlong int24 date time datetime year set blob string var_string char).inject({}) do |list, type|
+        list[eval("::Mysql::Field::TYPE_#{type.upcase}")] = type
+        list
+      end
     
-    @@types[246] = "decimal"
+      @@types[246] = "decimal"
     
-    def columns
-      @columns ||= load_columns
-    end
+      def columns
+        @columns ||= load_columns
+      end
     
-    def convert_type(type)
-      case type
-      when /int.* unsigned/
-        "bigint"
-      when /bigint/
-        "bigint"
-      when "bit(1)"
-        "boolean"
-      when "tinyint(1)"
-        "boolean"
-      when /tinyint/
-        "tinyint"
-      when /int/
-        "integer"
-      when /varchar/
-        "varchar"
-      when /char/
-        "char"
-      when /(float|decimal)/
-        "decimal"
-      when /double/
-         "double precision"
-      else
-        type
-      end 
-    end
+      def convert_type(type)
+        case type
+        when /int.* unsigned/
+          "bigint"
+        when /bigint/
+          "bigint"
+        when "bit(1)"
+          "boolean"
+        when "tinyint(1)"
+          "boolean"
+        when /tinyint/
+          "tinyint"
+        when /int/
+          "integer"
+        when /varchar/
+          "varchar"
+        when /char/
+          "char"
+        when /(float|decimal)/
+          "decimal"
+        when /double/
+           "double precision"
+        else
+          type
+        end 
+      end
     
-    def load_columns
-      @reader.reconnect
-      result = @reader.mysql.list_fields(name)
-      mysql_flags = ::Mysql::Field.constants.select {|c| c =~ /FLAG/}
-      fields = []
-      @reader.mysql.query("EXPLAIN `#{name}`") do |res|
-        while field = res.fetch_row do
-          length = field[1][/\((\d+)\)/, 1] if field[1] =~ /\((\d+)\)/
-          length = field[1][/\((\d+),(\d+)\)/, 1] if field[1] =~ /\((\d+),(\d+)\)/
-          desc = {
-            :name => field[0],
-            :table_name => name,
-            :type => convert_type(field[1]),
-            :length => length && length.to_i,
-            :decimals => field[1][/\((\d+),(\d+)\)/, 2],
-            :null => field[2] == "YES",
-            :primary_key => field[3] == "PRI",
-            :auto_increment => field[5] == "auto_increment"
-            }
-          desc[:default] = field[4] unless field[4].nil?
-          fields << desc
+      def load_columns
+        @reader.reconnect
+        result = @reader.mysql.list_fields(name)
+        mysql_flags = ::Mysql::Field.constants.select {|c| c =~ /FLAG/}
+        fields = []
+        @reader.mysql.query("EXPLAIN `#{name}`") do |res|
+          while field = res.fetch_row do
+            length = field[1][/\((\d+)\)/, 1] if field[1] =~ /\((\d+)\)/
+            length = field[1][/\((\d+),(\d+)\)/, 1] if field[1] =~ /\((\d+),(\d+)\)/
+            desc = {
+              :name => field[0],
+              :table_name => name,
+              :type => convert_type(field[1]),
+              :length => length && length.to_i,
+              :decimals => field[1][/\((\d+),(\d+)\)/, 2],
+              :null => field[2] == "YES",
+              :primary_key => field[3] == "PRI",
+              :auto_increment => field[5] == "auto_increment"
+              }
+            desc[:default] = field[4] unless field[4].nil?
+            fields << desc
+          end
         end
-      end
  
-      fields.select {|field| field[:auto_increment]}.each do |field|
-        @reader.mysql.query("SELECT max(`#{field[:name]}`) FROM `#{name}`") do |res|
-          field[:maxval] = res.fetch_row[0].to_i
+        fields.select {|field| field[:auto_increment]}.each do |field|
+          @reader.mysql.query("SELECT max(`#{field[:name]}`) FROM `#{name}`") do |res|
+            field[:maxval] = res.fetch_row[0].to_i
+          end
         end
+        fields
       end
-      fields
-    end
     
     
-    def indexes
-      load_indexes unless @indexes
-      @indexes 
-    end
+      def indexes
+        load_indexes unless @indexes
+        @indexes 
+      end
  
-    def foreign_keys
-      load_indexes unless @foreign_keys
-      @foreign_keys
-    end
+      def foreign_keys
+        load_indexes unless @foreign_keys
+        @foreign_keys
+      end
     
-    def load_indexes
-      @indexes = []
-      @foreign_keys = []
+      def load_indexes
+        @indexes = []
+        @foreign_keys = []
       
-      @reader.mysql.query("SHOW CREATE TABLE `#{name}`") do |result|
-        explain = result.fetch_row[1]
-        explain.split(/\n/).each do |line|
-          next unless line =~ / KEY /
-          index = {}
-          if match_data = /CONSTRAINT `(\w+)` FOREIGN KEY \(`(\w+)`\) REFERENCES `(\w+)` \(`(\w+)`\)/.match(line)
-            index[:name] = match_data[1]
-            index[:column] = match_data[2]
-            index[:ref_table] = match_data[3]
-            index[:ref_column] = match_data[4]
-            @foreign_keys << index
-          elsif match_data = /KEY `(\w+)` \((.*)\)/.match(line)
-            index[:name] = match_data[1]
-            index[:columns] = match_data[2].split(",").map {|col| col[/`(\w+)`/, 1]}
-            index[:unique] = true if line =~ /UNIQUE/
-            @indexes << index
-          elsif match_data = /PRIMARY KEY .*\((.*)\)/.match(line)
-            index[:primary] = true
-            index[:columns] = match_data[1].split(",").map {|col| col.strip.gsub(/`/, "")}
-            @indexes << index
+        @reader.mysql.query("SHOW CREATE TABLE `#{name}`") do |result|
+          explain = result.fetch_row[1]
+          explain.split(/\n/).each do |line|
+            next unless line =~ / KEY /
+            index = {}
+            if match_data = /CONSTRAINT `(\w+)` FOREIGN KEY \(`(\w+)`\) REFERENCES `(\w+)` \(`(\w+)`\)/.match(line)
+              index[:name] = match_data[1]
+              index[:column] = match_data[2]
+              index[:ref_table] = match_data[3]
+              index[:ref_column] = match_data[4]
+              @foreign_keys << index
+            elsif match_data = /KEY `(\w+)` \((.*)\)/.match(line)
+              index[:name] = match_data[1]
+              index[:columns] = match_data[2].split(",").map {|col| col[/`(\w+)`/, 1]}
+              index[:unique] = true if line =~ /UNIQUE/
+              @indexes << index
+            elsif match_data = /PRIMARY KEY .*\((.*)\)/.match(line)
+              index[:primary] = true
+              index[:columns] = match_data[1].split(",").map {|col| col.strip.gsub(/`/, "")}
+              @indexes << index
+            end
           end
         end
       end
-    end
     
-    def count_rows
-      @reader.mysql.query("SELECT COUNT(*) FROM `#{name}`")  do |res|
-        return res.fetch_row[0].to_i
+      def count_rows
+        @reader.mysql.query("SELECT COUNT(*) FROM `#{name}`")  do |res|
+          return res.fetch_row[0].to_i
+        end
       end
-    end
     
-    def has_id?
-      !!columns.find {|col| col[:name] == "id"} 
-    end
+      def has_id?
+        !!columns.find {|col| col[:name] == "id"} 
+      end
     
-    def count_for_pager
-      query = has_id? ? 'MAX(id)' : 'COUNT(*)'
-      @reader.mysql.query("SELECT #{query} FROM `#{name}`") do |res|
-        return res.fetch_row[0].to_i
+      def count_for_pager
+        query = has_id? ? 'MAX(id)' : 'COUNT(*)'
+        @reader.mysql.query("SELECT #{query} FROM `#{name}`") do |res|
+          return res.fetch_row[0].to_i
+        end
       end
-    end
  
-    def query_for_pager
-      query = has_id? ? 'WHERE id >= ? AND id < ?' : 'LIMIT ?,?'
-      "SELECT #{columns.map{|c| "`"+c[:name]+"`"}.join(", ")} FROM `#{name}` #{query}"
+      def query_for_pager
+        query = has_id? ? 'WHERE id >= ? AND id < ?' : 'LIMIT ?,?'
+        "SELECT #{columns.map{|c| "`"+c[:name]+"`"}.join(", ")} FROM `#{name}` #{query}"
+      end
     end
-  end
   
-  def connect
-    @mysql = ::Mysql.connect(@host, @user, @passwd, @db, @port, @sock, @flag)
-    @mysql.query("SET NAMES utf8")
-    @mysql.query("SET SESSION query_cache_type = OFF")
-  end
+    def connect
+      @mysql = ::Mysql.connect(@host, @user, @passwd, @db, @port, @sock, @flag)
+      @mysql.query("SET NAMES utf8")
+      @mysql.query("SET SESSION query_cache_type = OFF")
+    end
   
-  def reconnect
-    @mysql.close rescue false
-    connect
-  end
+    def reconnect
+      @mysql.close rescue false
+      connect
+    end
   
-  def initialize(host = nil, user = nil, passwd = nil, db = nil, port = nil, sock = nil, flag = nil)
-    @host, @user, @passwd, @db, @port, @sock, @flag = host, user, passwd, db, port, sock, flag
-    connect
-  end
+    def initialize(host = nil, user = nil, passwd = nil, db = nil, port = nil, sock = nil, flag = nil)
+      @host, @user, @passwd, @db, @port, @sock, @flag = host, user, passwd, db, port, sock, flag
+      connect
+    end
   
-  attr_reader :mysql
+    attr_reader :mysql
   
-  def tables
-    @tables ||= @mysql.list_tables.map {|table| Table.new(self, table)}
-  end
+    def tables
+      @tables ||= @mysql.list_tables.map {|table| Table.new(self, table)}
+    end
   
-  def paginated_read(table, page_size)
-    count = table.count_for_pager
-    return if count < 1
-    statement = @mysql.prepare(table.query_for_pager)
-    counter = 0
-    0.upto((count + page_size)/page_size) do |i|
-      statement.execute(i*page_size, table.has_id? ? (i+1)*page_size : page_size)
-      while row = statement.fetch
-        counter += 1
-        yield(row, counter)
+    def paginated_read(table, page_size)
+      count = table.count_for_pager
+      return if count < 1
+      statement = @mysql.prepare(table.query_for_pager)
+      counter = 0
+      0.upto((count + page_size)/page_size) do |i|
+        statement.execute(i*page_size, table.has_id? ? (i+1)*page_size : page_size)
+        while row = statement.fetch
+          counter += 1
+          yield(row, counter)
+        end
       end
+      counter
     end
-    counter
   end
-end
 
 end

test/fixtures/config_localmysql_to_file.yml

+mysql:
+ hostname: localhost
+ port: 3306
+ socket: /tmp/mysql.sock
+ username: mysql2psql
+ password: 
+ database: mysql2psql_test
+
+destination:
+ # if file is given, output goes to file, else postgres
+ file: test/fixtures/mysql2psql_test_output.sql
+ postgres:
+  hostname: localhost
+  port: 5432
+  username: mysql2psql
+  password: 
+  database: mysql2psql_test
+
+# if tables is given, only the listed tables will be converted.  leave empty to convert all tables.
+tables:
+- table1
+- table2
+- table3
+- table4
+
+# if exclude_tables is given, exclude the listed tables from the conversion.
+exclude_tables:
+- table5
+- table6
+
+# if supress_data is true, only the schema definition will be exported/migrated, and not the data
+supress_data: true
+
+# if supress_ddl is true, only the data will be exported/imported, and not the schema
+supress_ddl: false
+
+# if force_truncate is true, forces a table truncate before table loading
+force_truncate: false

test/integration/mysqlreader_base_test.rb

+require 'test_helper'
+
+require 'mysql2psql'
+
+class MysqlreaderBaseTest < Test::Unit::TestCase
+  attr_accessor :options
+  def setup
+    @options = Mysql2psql::ConfigBase.new( "#{File.dirname(__FILE__)}/../fixtures/config_localmysql_to_file.yml" )
+  end
+  def teardown
+    
+  end
+  def test_db_connection
+    assert_nothing_raised do
+      reader = Mysql2psql::MysqlReader.new(
+        options.mysqlhostname('localhost'), options.mysqlusername, options.mysqlpassword, 
+        options.mysqldatabase, options.mysqlport, options.mysqlsocket )
+    end
+  end
+end
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.