1、建立连接
2、检查表结构变化,如果结构已变则新建,后同步;如果未变则同步;
require 'rubygems'
require 'date'
require 'time'
$database = 'a17'
$aiyadb='aop'
def synchronization(table)
$memaxid=0
$maxid=0
$tablename = table
$fieldslist = []
$fieldslist_me = []
$fieldshash_type = Hash.new
$fieldshash_nullable = Hash.new
check_table_0($tablename)
check_table_1($tablename)
check_record_0($tablename)
synchronization_0($tablename)
from_0 = false #和本地比较
if from_0 then
$memaxid = 0
sql ="delete from #{$tablename} where 1=1;"
$myclient.query(sql)
end
eval ("check_table_#{$tablename}()")
eval ("check_table2_#{$tablename}()")
while true
eval("check_record_#{$tablename}()")
if $maxid>$memaxid then
eval("synchronization_#{$tablename}()")
else
break
end
end
end
def check_table_0(tablename)
eval ("def check_table_#{tablename}()
#puts (\" check_table_\#{$tablename} \");
sql = \"select column_name,column_type,is_nullable,column_default,character_set_name,collation_name,column_comment,column_key,extra from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='\#{$aiyadb}' and TABLE_NAME='\#{$tablename}';\";
result = $aiyashop_client.query(sql)
result.each do |row|
$fieldslist.push(row['column_name'])
$fieldshash_type[row['column_name']] = row['column_type']
$fieldshash_nullable[row['column_name']] = row['is_nullable']
end
#puts( $fieldslist );
#puts( $fieldshash_type);
#puts( $fieldshash_nullable);
sql = \"select column_name,column_type,is_nullable,column_default,character_set_name,collation_name,column_comment,column_key,extra from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='\#{$database}' and TABLE_NAME='\#{$tablename}';\";
result = $myclient.query(sql)
result.each do |row|
$fieldslist_me.push(row['column_name'])
end
$gsql = \"insert into `\#{$tablename}`(\";
for f in $fieldslist
$gsql += \"`\" + f + \"`,\";
end
$gsql = $gsql.chop ; ###chop:去掉字符串末尾的最后一个字符
$gsql += \")VALUES(\";
############################如果表结构已经变化,则重命名本地表,重新拉新表
na= $fieldslist - $fieldslist_me
if na.length != 0 and result.size>0 then
sql=\"ALTER TABLE \#{$tablename} RENAME \#{$tablename}\#{DateTime.now.strftime(\"%Y%m%d%H%M%S\")};\";
result = $myclient.query(sql)
end
end")
end
def check_table_1(tablename)
eval ("def check_table2_#{tablename}()
sql = \"select * from information_schema.TABLES where table_schema ='\#{$database}' and table_name='\#{$tablename}';\"
result = $myclient.query(sql)
if result.size < 1 then
sql = \"show create table `\#{$aiyadb}`.`\#{$tablename}`;\"
result = $aiyashop_client.query(sql)
result.each do |row|
sql = row['Create Table']
end
end
# puts sql
# 重置 自增
sql = sql.gsub(/AUTO_INCREMENT=[0-9]+/,'AUTO_INCREMENT=1 ')
# 去除外键
sql = sql.gsub(/,\\s+CONSTRAINT \(.+\\n\)+\\) ENGINE=InnoDB/,\" \\r\\n\\) ENGINE=InnoDB\")
# sql = sql.gsub(/,\\s+CONSTRAINT \\S+ FOREIGN KEY \(.+\\n\)+\\) ENGINE=InnoDB/,\" \\r\\n) ENGINE=InnoDB\")
result = $myclient.query(sql)
end")
end
def check_record_0(tablename)
eval("def check_record_#{tablename}()
sql = \"select id from \#{$tablename} order by id desc limit 1;\"
result = $myclient.query(sql)
if result.size > 0 then
result.each do |row|
$memaxid = row['id']
end
end
result = $aiyashop_client.query(sql)
result.each do |row|
$maxid = row['id']
end
# - 45 \ 45+47=92 - 92-47=45 / 45+2=47 - 47-2=45
#print (\"$maxid=\#{$maxid},$memaxid=\#{$memaxid} \");
print (\"................\#{$asciiflag[$step%4]} [\#{$memaxid*100/$maxid}%] \\r\") if $maxid != 0;
$step += 1
#print (\"\#{$memaxid} \");
end")
end
####如果字段类型是bit(1),当为0时取回的数据为空
#####<Mysql2::Error: Incorrect datetime value: '2017-11-28 16:51:58 +0800' for column 'create_date' at row 1>
def synchronization_0(tablename)
eval("def synchronization_#{tablename}()
sql = \"select * from `\#{$tablename}` where id>'\#{$memaxid}' order by id limit 60;\";
result = $aiyashop_client.query(sql)
result.each do |row|
$memaxid = row[\"id\"] ;
# print(\"\#{row['id']} \"); ## eval 内打印语句需加分号表示结束,否则解释器可能会把下一行解释为打印内容。
sql = $gsql;
for f in $fieldslist
# puts(\"row['\#{f}'] =\#{row[\"\#{f}\"]}, \#{$fieldshash_type[\"\#{f}\"]}\");
fv ='' ;
if $fieldshash_type[\"\#{f}\"] == 'bit(1)' then
fv = checkfield_ex_bit(row[\"\#{f}\"],$fieldshash_nullable[\"\#{f}\"]) ;
elsif $fieldshash_type[\"\#{f}\"] == 'datetime' then
fv = checkfield_ex_datetime(row[\"\#{f}\"],$fieldshash_nullable[\"\#{f}\"]) ;
else
fv = checkfield_ex(row[\"\#{f}\"],$fieldshash_nullable[\"\#{f}\"]) ;
end
sql += fv ;
end
sql = sql.chop ;
sql += \");\" ;
# puts(sql );
$myclient.query(sql)
end
end")
end