def test_add_char(self):
sql = "alter table test add name varchar(20) not null after id"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(ret, ("test", "alter table test.test add column name String"))
sql = "alter table test.test add name varchar(20) not null after id"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(ret, ("test", "alter table test.test add column name String"))
def test_drop(self):
sql = "alter table test drop column name"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(ret, ("test", "alter table test.test drop column name"))
sql = "alter table test drop name"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(ret, ("test", "alter table test.test drop column name"))
def test_drop_column(self):
ret = SqlConvert.get_parse_ret("alter table test drop column name")
self.assertEqual(ret.statement_type, "ALTER")
self.assertEqual(ret.table_name, "test")
self.assertEqual(ret.alter_action, "DROP COLUMN")
self.assertEqual(ret.column_name, "name")
self.assertEqual(ret.data_type, "")
self.assertEqual(ret.null, "")
self.assertEqual(ret.column_position, "")
def test_change_column(self):
sql = "alter table test change `column` column2 int null"
ret = SqlConvert.get_parse_ret(sql)
self.assertEqual(ret.statement_type, "ALTER")
self.assertEqual(ret.table_name, "test")
self.assertEqual(ret.alter_action, "CHANGE COLUMN")
self.assertEqual(ret.column_name, "`column`")
self.assertEqual(str(ret.data_type), "['INT']")
self.assertEqual(ret.null, True)
self.assertEqual(ret.new_column_name, "column2")
def test_add_char_olumn(self):
sql = "alter table test add name varchar(20) not null after id"
ret = SqlConvert.get_parse_ret(sql)
self.assertEqual(ret.statement_type, "ALTER")
self.assertEqual(ret.table_name, "test")
self.assertEqual(ret.alter_action, "ADD COLUMN")
self.assertEqual(ret.column_name, "name")
self.assertEqual(str(ret.data_type), "['VARCHAR', '20']")
self.assertEqual(ret.null, False)
self.assertEqual(ret.column_position, "id")
def test_modify_column_with_default(self):
sql = "alter table test modify column `giving_money` decimal(16,2) not null default 0 comment '赠送金额'"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(
ret,
(
"test",
"alter table test.test modify column `giving_money` Decimal(16,2) default '0' comment '赠送金额'",
),
)
def _binlog_reading(
self,
only_tables,
only_schemas,
log_file,
log_pos,
server_id,
skip_dmls,
skip_delete_tables,
skip_update_tables,
) -> Generator:
stream = BinLogStreamReader(
connection_settings=dict(
host=self.settings.mysql_host,
port=self.settings.mysql_port,
user=self.settings.mysql_user,
passwd=self.settings.mysql_password,
),
resume_stream=True,
blocking=True,
server_id=server_id,
only_tables=only_tables,
only_schemas=only_schemas,
only_events=self.only_events,
log_file=log_file,
log_pos=log_pos,
fail_on_table_metadata_unavailable=True,
slave_heartbeat=10,
)
for binlog_event in stream:
if isinstance(binlog_event, QueryEvent):
schema = binlog_event.schema.decode()
query = binlog_event.query.lower()
if "alter" not in query:
continue
try:
convent_sql = SqlConvert.to_clickhouse(schema, query)
except Exception as e:
convent_sql = ""
logger.error(f"query convert to clickhouse error, error: {e}, query: {query}")
if not convent_sql:
continue
event = {
"table": None,
"schema": schema,
"action": "query",
"values": {"query": convent_sql},
"event_unixtime": int(time.time() * 10 ** 6),
"action_core": "0",
}
yield schema, None, event, stream.log_file, stream.log_pos
else:
schema = binlog_event.schema
table = binlog_event.table
skip_dml_table_name = f"{schema}.{table}"
for row in binlog_event.rows:
if isinstance(binlog_event, WriteRowsEvent):
event = {
"table": table,
"schema": schema,
"action": "insert",
"values": row["values"],
"event_unixtime": int(time.time() * 10 ** 6),
"action_core": "2",
}
elif isinstance(binlog_event, UpdateRowsEvent):
if "update" in skip_dmls or skip_dml_table_name in skip_update_tables:
continue
delete_event = {
"table": table,
"schema": schema,
"action": "delete",
"values": row["before_values"],
"event_unixtime": int(time.time() * 10 ** 6),
"action_core": "1",
}
yield binlog_event.schema, binlog_event.table, delete_event, stream.log_file, stream.log_pos
event = {
"table": table,
"schema": schema,
"action": "insert",
"values": row["after_values"],
"event_unixtime": int(time.time() * 10 ** 6),
"action_core": "2",
}
elif isinstance(binlog_event, DeleteRowsEvent):
if "delete" in skip_dmls or skip_dml_table_name in skip_delete_tables:
continue
event = {
"table": table,
"schema": schema,
"action": "delete",
"values": row["values"],
"event_unixtime": int(time.time() * 10 ** 6),
"action_core": "1",
}
else:
return
yield binlog_event.schema, binlog_event.table, event, stream.log_file, stream.log_pos
def test_change_column(self):
sql = "alter table test change `column` column2 int null"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(
ret, "alter table test.test rename column `column` to column2")
def test_add_datetime(self):
sql = "alter table test add name datetime not null after id"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(
ret, "alter table test.test add column name DateTime after id")
def test_add_decimal(self):
sql = "alter table test add name decimal(10,2) not null after id"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(
ret,
"alter table test.test add column name Decimal(10,2) after id")
def test_add_int(self):
sql = "alter table test add name int not null after id"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(
ret, "alter table test.test add column name Int32 after id")
def test_add_bool(self):
sql = "alter table test add name bool not null after id"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(
ret, "alter table test.test add column name UInt8 after id")
def test_modify_column(self):
sql = "alter table test modify column `channel` varchar(20) not null comment '渠道'"
ret = SqlConvert.to_clickhouse("test", sql)
self.assertEqual(
ret, ("test", "alter table test.test modify column `channel` String comment '渠道'")
)
