def update_project_list():
sql = """ALTER TABLE `events`.`project_list`
ADD COLUMN `access_control_threshold_sum` int(11) NULL COMMENT '接入控制的全局缺省值' AFTER `enable_scheduler`;"""
do_tidb_exe(sql=sql)
sql = """ALTER TABLE `events`.`project_list`
ADD COLUMN `access_control_threshold_event` int(11) NULL COMMENT '接入控制的单项缺省值' AFTER `access_control_threshold_sum`;"""
do_tidb_exe(sql=sql)
print('project_list加字段完成')
def update_properties():
sql = """select project_name from project_list"""
result = do_tidb_select(sql=sql)
for project in result[0]:
sql2="""ALTER TABLE `events`.`{project}_properties`
ADD COLUMN `access_control_threshold` int(10) NULL AFTER `total_count`;""".format(project=project[0])
do_tidb_exe(sql=sql2)
print('properities加字段完成')
def insert_shortcut_history(short_url,
result,
cost_time,
ip,
user_agent,
accept_language,
ua_platform,
ua_browser,
ua_version,
ua_language,
created_at=None):
timenow = int(time.time())
sql = """insert HIGH_PRIORITY shortcut_history (`short_url`,`result`,`cost_time`,`ip`,`created_at`,`user_agent`,`accept_language`,`ua_platform`,`ua_browser`,`ua_version`,`ua_language`) values ('{short_url}','{result}',{cost_time},'{ip}',{created_at},'{user_agent}','{accept_language}','{ua_platform}','{ua_browser}','{ua_version}','{ua_language}')""".format(
short_url=short_url,
result=result,
cost_time=cost_time,
ip=ip,
created_at=created_at if created_at else timenow,
user_agent=user_agent,
accept_language=accept_language,
ua_platform=ua_platform,
ua_browser=ua_browser,
ua_version=ua_version,
ua_language=ua_language).replace("'None'",
"Null").replace("None", "Null")
result, count = do_tidb_exe(sql)
print('已插入解析记录' + str(count))
def insert_properties(project,
lib,
remark,
event,
properties,
properties_len,
created_at=None,
updated_at=None):
if created_at is None:
created_at = int(time.time())
if updated_at is None:
updated_at = int(time.time())
sql = """set @@tidb_disable_txn_auto_retry = 0;
set @@tidb_retry_limit = 10;
insert HIGH_PRIORITY into `{table}_properties` (`lib`,`remark`,`event`,`properties`,`properties_len`,`created_at`,`updated_at`,`total_count`,`lastinsert_at`) values ( %(lib)s,%(remark)s,%(event)s,%(properties)s,%(properties_len)s,%(created_at)s,%(updated_at)s,1,%(updated_at)s) ON DUPLICATE KEY UPDATE `properties`=if(properties_len<%(properties_len)s,%(properties)s,properties),`properties_len`=if(properties_len<%(properties_len)s,%(properties_len)s,properties_len),updated_at=if(properties_len<%(properties_len)s,%(updated_at)s,updated_at),total_count=total_count+1,lastinsert_at=%(updated_at)s;""".format(
table=project)
key = {
'lib': lib,
'remark': remark,
'event': event,
'properties': properties,
'properties_len': properties_len,
'created_at': created_at,
'updated_at': updated_at
}
result, count = do_tidb_exe(sql=sql, args=key)
def create_new_talbe():
sql = """CREATE TABLE `access_control` (
`project` varchar(255) NOT NULL COMMENT '项目名',
`key` varchar(255) NOT NULL COMMENT 'status_code里pid=56',
`type` int(4) NOT NULL COMMENT 'key类型',
`event` varchar(255) NOT NULL COMMENT 'event类型',
`status` int(4) DEFAULT NULL COMMENT 'status_code里pid=59',
`date` date NOT NULL COMMENT '日期',
`hour` int(4) NOT NULL COMMENT '小时',
`pv` int(10) DEFAULT NULL COMMENT '事件量',
`updated_at` int(10) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`project`,`key`,`type`,`event`,`date`,`hour`) /*T![clustered_index] NONCLUSTERED */,
KEY `hour_key` (`date`,`hour`,`key`),
KEY `key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
do_tidb_exe(sql=sql)
def insert_noti_temple(project,name,args,content,temple_desc=None):
created_at = int(time.time())
updated_at = int(time.time())
sql = """insert {project}_noti_temple (name,args,temple_desc,content,created_at,updated_at ) values (%(name)s,%(args)s,%(temple_desc)s,%(content)s,%(created_at)s,%(updated_at)s)""".format(project=project)
key={'name':name,'args':args,'temple_desc':temple_desc,'content':content,'created_at':created_at,'updated_at':updated_at}
result = do_tidb_exe(sql=sql,args=key)
return result
def insert_shortcut(project, short_url, long_url, expired_at, src,
src_short_url, submitter, utm_source, utm_medium,
utm_campaign, utm_content, utm_term):
timenow = int(time.time())
sql = """insert into shortcut (`project`,`short_url`,`long_url`,`expired_at`,`created_at`,`src`,`src_short_url`,`submitter`,`utm_source`,`utm_medium`,`utm_campaign`,`utm_content`,`utm_term`) values ('{project}','{short_url}','{long_url}',{expired_at},{created_at},'{src}','{src_short_url}','{submitter}','{utm_source}','{utm_medium}','{utm_campaign}','{utm_content}','{utm_term}')""".format(
project=project,
short_url=short_url,
long_url=long_url,
expired_at=expired_at,
created_at=timenow,
src=src,
src_short_url=src_short_url,
submitter=submitter,
utm_source=utm_source,
utm_medium=utm_medium,
utm_campaign=utm_campaign,
utm_content=utm_content,
utm_term=utm_term).replace("'None'", "Null").replace("None", "Null")
result, count = do_tidb_exe(sql)
# print(sql,count)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',
defname='insert_shortcut',
result=result + sql)
return count
def insert_mobile_ad_list(project,
url,
src,
src_url,
submitter,
utm_source,
utm_medium,
utm_campaign,
utm_content,
utm_term,
expired_at=2147483647):
timenow = int(time.time())
sql = """insert mobile_ad_list (`project`,`url`,`expired_at`,`created_at`,`src`,`src_url`,`submitter`,`utm_source`,`utm_medium`,`utm_campaign`,`utm_content`,`utm_term`) values ('{project}','{url}',{expired_at},{created_at},'{src}','{src_url}','{submitter}','{utm_source}','{utm_medium}','{utm_campaign}','{utm_content}','{utm_term}')""".format(
project=project,
url=url,
expired_at=expired_at,
created_at=timenow,
src=src,
src_url=src_url,
submitter=submitter,
utm_source=utm_source,
utm_medium=utm_medium,
utm_campaign=utm_campaign,
utm_content=utm_content,
utm_term=utm_term).replace("'None'", "Null").replace("None", "Null")
result, count = do_tidb_exe(sql)
if count == 0:
# print(result,sql)
write_to_log(filename='db_func',
defname='insert_mobile_ad_list',
result=str(result) + sql)
return '', 0
return result, count
def insert_noti_group(project,plan_id,list_id,data_id,temple_id,owner=None,send_at=0,sent=0,total=0,priority=13,status=8,created_at=None,updated_at=None):
created_at=created_at if created_at else int(time.time())
updated_at=created_at if created_at else int(time.time())
sql = """insert {project}_noti_group (plan_id,list_id,data_id,temple_id,priority,status,owner,send_at,sent,total,created_at,updated_at ) values (%(plan_id)s,%(list_id)s,%(data_id)s,%(temple_id)s,%(priority)s,%(status)s,%(owner)s,%(send_at)s,%(sent)s,%(total)s,%(created_at)s,%(updated_at)s)""".format(project=project)
key={'plan_id':plan_id,'list_id':list_id,'data_id':data_id,'temple_id':temple_id,'priority':priority,'status':status,'owner':owner,'send_at':send_at,'sent':sent,'total':total,'created_at':created_at if created_at else int(time.time()),'updated_at':updated_at if updated_at else int(time.time())}
result = do_tidb_exe(sql=sql,args=key)
return result
def update_table_history(project_name):
#升级user表支持多个设备绑定。2019-12-11
sql="""CREATE TABLE IF NOT EXISTS `{project_name}_user2` (
`distinct_id` varchar(255) NOT NULL,
`lib` varchar(255) NOT NULL,
`map_id` varchar(255) NOT NULL,
`original_id` varchar(255) NOT NULL,
`user_id` varchar(255) DEFAULT NULL,
`all_user_profile` json DEFAULT NULL,
`created_at` int(11) DEFAULT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`distinct_id`,`lib`,`map_id`,`original_id`),
KEY `distinct_id` (`distinct_id`),
KEY `map_id` (`map_id`),
KEY `original_id` (`original_id`),
KEY `distinct_id_lib` (`distinct_id`,`lib`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
set @@session.tidb_batch_insert = 1;
INSERT into {project_name}_user2 (
SELECT * FROM `{project_name}_user`);
set @@session.tidb_batch_insert = 0;
ALTER table {project_name}_user RENAME to {project_name}_user3;
ALTER table {project_name}_user2 RENAME to {project_name}_user;""".format(project_name=project_name)
result=do_tidb_exe(sql)
print(result)
def insert_mobile_ad_src(src,src_name,src_args,utm_source,utm_medium,utm_campaign,utm_content,utm_term):
timenow = int(time.time())
sql = """insert mobile_ad_src (`src`,`src_name`,`src_args`,`created_at`,`updated_at`,`utm_source`,`utm_medium`,`utm_campaign`,`utm_content`,`utm_term`) values ('{src}','{src_name}','{src_args}',{created_at},{updated_at},'{utm_source}','{utm_medium}','{utm_campaign}','{utm_content}','{utm_term}') ON DUPLICATE KEY UPDATE `src_name`='{src_name}',`src_args`='{src_args}',`updated_at`={updated_at},`utm_source`='{utm_source}',`utm_medium`='{utm_medium}',`utm_campaign`='{utm_campaign}',`utm_content`='{utm_content}',`utm_term`='{utm_term}' """.format(src=src,src_name=src_name,src_args=src_args,created_at=timenow,updated_at=timenow,utm_source=utm_source,utm_medium=utm_medium,utm_campaign=utm_campaign,utm_content=utm_content,utm_term=utm_term).replace("'None'","Null").replace("None","Null")
result = do_tidb_exe(sql)
if result[1] == 0:
write_to_log(filename='db_func',defname='insert_mobile_ad_src',result=str(result)+sql)
return '',0
return result[0],result[1]
def check_next_scheduler_job(priority=13,current_time=None):
current_time=int(time.time()) if not current_time else current_time
if priority == 13 :
priorities = '13,14,15'
elif priority == 14 :
priorities = '14,15'
sql = """select id,project,group_id,datetime,data from scheduler_jobs where priority={priority} and datetime<={current_time} and `status` = 16 order by id limit 1""".format(priority=priority,current_time=current_time)
result = do_tidb_exe(sql=sql)
return result[0],result[1]
def check_lastest_usergroup_list_index(project,group_id):
sql = """select max(group_list_index) from {project}_usergroup_list where group_id={group_id}""".format(project=project,group_id=group_id)
result = do_tidb_exe(sql=sql)
if result[1]<1:
max_count = 0
elif result[0][0][0] is None:
max_count = 0
else:
max_count = result[0][0][0]
return max_count
def update_usergroup_plan(project,plan_id,latest_data_list_index=0,latest_apply_temple_time=0,latest_apply_temple_id=0,updated_at=None,repeat_times_add=0,latest_data_time=0):
timenow = int(time.time())
sql = """update {project}_usergroup_plan set
`latest_data_list_index`=if({latest_data_list_index}=0,`latest_data_list_index`,{latest_data_list_index}),
`latest_data_time`=if(`latest_data_time`=0,`latest_data_time`,{latest_data_time}),
`repeat_times`=if(`repeat_times` is null,0+{repeat_times_add},`repeat_times`+{repeat_times_add}),
`latest_apply_temple_id`=if({latest_apply_temple_id}=0,`latest_apply_temple_id`,{latest_apply_temple_id}),
`latest_apply_temple_time`={latest_apply_temple_time},updated_at={updated_at}
where id ={plan_id}""".format(project=project,plan_id=plan_id,latest_data_list_index=latest_data_list_index,latest_apply_temple_time=latest_apply_temple_time,latest_apply_temple_id=latest_apply_temple_id,updated_at=updated_at if updated_at else timenow,repeat_times_add=repeat_times_add,latest_data_time=latest_data_time)
result = do_tidb_exe(sql=sql)
return result[0],result[1]
def insert_devicedb(table,distinct_id,device_id,manufacturer,model,os,os_version,screen_width,screen_height,network_type,user_agent,accept_language,ip,ip_city,ip_asn,wifi,app_version,carrier,referrer,referrer_host,bot_name,browser,browser_version,is_login_id,screen_orientation,gps_latitude,gps_longitude,first_visit_time,first_referrer,first_referrer_host,first_browser_language,first_browser_charset,first_search_keyword,first_traffic_source_type,utm_content,utm_campaign,utm_medium,utm_term,utm_source,latest_utm_content,latest_utm_campaign,latest_utm_medium,latest_utm_term,latest_utm_source,latest_referrer,latest_referrer_host,latest_search_keyword,latest_traffic_source_type,update_content,ua_platform,ua_browser,ua_version,ua_language,lib,created_at=None,updated_at=None):
if created_at is None:
timenow = int(time.time())
date = time.strftime("%Y-%m-%d", time.localtime())
hour = int(time.strftime("%H", time.localtime()))
else:
timenow = created_at
date = time.strftime("%Y-%m-%d", time.localtime(created_at))
hour = int(time.strftime("%H", time.localtime(created_at)))
sql = """set @@tidb_disable_txn_auto_retry = 0;
set @@tidb_retry_limit = 10;
insert HIGH_PRIORITY into `{table}_device` (`distinct_id`,`device_id`,`manufacturer`,`model`,`os`,`os_version`,`screen_width`,`screen_height`,`network_type`,`user_agent`,`accept_language`,`ip`,`ip_city`,`ip_asn`,`wifi`,`app_version`,`carrier`,`referrer`,`referrer_host`,`bot_name`,`browser`,`browser_version`,`is_login_id`,`screen_orientation`,`gps_latitude`,`gps_longitude`,`first_visit_time`,`first_referrer`,`first_referrer_host`,`first_browser_language`,`first_browser_charset`,`first_search_keyword`,`first_traffic_source_type`,`utm_content`,`utm_campaign`,`utm_medium`,`utm_term`,`utm_source`,`latest_utm_content`,`latest_utm_campaign`,`latest_utm_medium`,`latest_utm_term`,`latest_utm_source`,`latest_referrer`,`latest_referrer_host`,`latest_search_keyword`,`latest_traffic_source_type`,`created_at`,`updated_at`,`ua_platform`,`ua_browser`,`ua_version`,`ua_language`,`lib`) values ( %(distinct_id)s,%(device_id)s,%(manufacturer)s,%(model)s,%(os)s,%(os_version)s,%(screen_width)s,%(screen_height)s,%(network_type)s,%(user_agent)s,%(accept_language)s,%(ip)s,%(ip_city)s,%(ip_asn)s,%(wifi)s,%(app_version)s,%(carrier)s,%(referrer)s,%(referrer_host)s,%(bot_name)s,%(browser)s,%(browser_version)s,%(is_login_id)s,%(screen_orientation)s,%(gps_latitude)s,%(gps_longitude)s,%(first_visit_time)s,%(first_referrer)s,%(first_referrer_host)s,%(first_browser_language)s,%(first_browser_charset)s,%(first_search_keyword)s,%(first_traffic_source_type)s,%(utm_content)s,%(utm_campaign)s,%(utm_medium)s,%(utm_term)s,%(utm_source)s,%(latest_utm_content)s,%(latest_utm_campaign)s,%(latest_utm_medium)s,%(latest_utm_term)s,%(latest_utm_source)s,%(latest_referrer)s,%(latest_referrer_host)s,%(latest_search_keyword)s,%(latest_traffic_source_type)s,%(created_at)s,%(updated_at)s,%(ua_platform)s,%(ua_browser)s,%(ua_version)s,%(ua_language)s,%(lib)s) ON DUPLICATE KEY UPDATE `updated_at`={updated_at}{update_content};""".format(table=table,updated_at=timenow,update_content=update_content)
key = {'distinct_id':distinct_id,'device_id':device_id,'manufacturer':manufacturer,'model':model,'os':os,'os_version':os_version,'screen_width':screen_width,'screen_height':screen_height,'network_type':network_type,'user_agent':user_agent,'accept_language':accept_language,'ip':ip,'ip_city':ip_city,'ip_asn':ip_asn,'wifi':wifi,'app_version':app_version,'carrier':carrier,'referrer':referrer,'referrer_host':referrer_host,'bot_name':bot_name,'browser':browser,'browser_version':browser_version,'is_login_id':is_login_id,'screen_orientation':screen_orientation,'gps_latitude':gps_latitude,'gps_longitude':gps_longitude,'first_visit_time':first_visit_time,'first_referrer':first_referrer,'first_referrer_host':first_referrer_host,'first_browser_language':first_browser_language,'first_browser_charset':first_browser_charset,'first_search_keyword':first_search_keyword,'first_traffic_source_type':first_traffic_source_type,'utm_content':utm_content,'utm_campaign':utm_campaign,'utm_medium':utm_medium,'utm_term':utm_term,'utm_source':utm_source,'latest_utm_content':latest_utm_content,'latest_utm_campaign':latest_utm_campaign,'latest_utm_medium':latest_utm_medium,'latest_utm_term':latest_utm_term,'latest_utm_source':latest_utm_source,'latest_referrer':latest_referrer,'latest_referrer_host':latest_referrer_host,'latest_search_keyword':latest_search_keyword,'latest_traffic_source_type':latest_traffic_source_type,'created_at':timenow,'updated_at':timenow,'ua_platform':ua_platform,'ua_browser':ua_browser,'ua_version':ua_version,'ua_language':ua_language,'lib':lib}
result,count = do_tidb_exe(sql=sql, args=key)
return count
def insert_user_db(project,distinct_id,lib,map_id,original_id,user_id,all_user_profile,update_params,created_at=None,updated_at=None):
if created_at is None:
timenow = int(time.time())
date = time.strftime("%Y-%m-%d", time.localtime())
hour = int(time.strftime("%H", time.localtime()))
else:
timenow = created_at
date = time.strftime("%Y-%m-%d", time.localtime(created_at))
hour = int(time.strftime("%H", time.localtime(created_at)))
sql = """set @@tidb_disable_txn_auto_retry = 0;
set @@tidb_retry_limit = 10;
insert HIGH_PRIORITY into `{table}_user` (`distinct_id`,`lib`,`map_id`,`original_id`,`user_id`,`all_user_profile`,`created_at`,`updated_at`) values (%(distinct_id)s,%(lib)s,%(map_id)s,%(original_id)s,%(user_id)s,%(all_user_profile)s,%(created_at)s,%(updated_at)s) ON DUPLICATE KEY UPDATE `updated_at`={updated_at}{update_params}""".format(table=project,update_params=update_params,updated_at=timenow)
key={'distinct_id':distinct_id,'lib':lib,'map_id':map_id,'original_id':original_id,'user_id':user_id,'all_user_profile':all_user_profile,'created_at':timenow,'updated_at':timenow}
result = do_tidb_exe(sql=sql, args=key)
return result[1]
def insert_event(table,alljson,track_id,distinct_id,lib,event,type_1,User_Agent,Host,Connection,Pragma,Cache_Control,Accept,Accept_Encoding,Accept_Language,ip,ip_city,ip_asn,url,referrer,remark,ua_platform,ua_browser,ua_version,ua_language,created_at=None):
if created_at is None:
timenow = int(time.time())
date = time.strftime("%Y-%m-%d", time.localtime())
hour = int(time.strftime("%H", time.localtime()))
else:
timenow = created_at
date = time.strftime("%Y-%m-%d", time.localtime(created_at))
hour = int(time.strftime("%H", time.localtime(created_at)))
sql = """insert HIGH_PRIORITY into `{table}` (`all_json`,`track_id`,`distinct_id`,`lib`,`event`,`type`,`created_at`,`date`,`hour`,`user_agent`,`host`,`connection`,`pragma`,`cache_control`,`accept`,`accept_encoding`,`accept_language`,`ip`,`ip_city`,`ip_asn`,`url`,`referrer`,`remark`,`ua_platform`,`ua_browser`,`ua_version`,`ua_language`) values (%(alljson)s,%(track_id)s,%(distinct_id)s,%(lib)s,%(event)s,%(type)s,%(created_at)s,%(date)s,%(hour)s,%(User_Agent)s,%(Host)s,%(Connection)s,%(Pragma)s,%(Cache_Control)s,%(Accept)s,%(Accept_Encoding)s,%(Accept_Language)s,%(ip)s,%(ip_city)s,%(ip_asn)s,%(url)s,%(referrer)s,%(remark)s,%(ua_platform)s,%(ua_browser)s,%(ua_version)s,%(ua_language)s)""".format(table=table)
key = {'alljson':alljson,'track_id':track_id,'distinct_id':distinct_id,'lib':lib,'event':event,'type':type_1,'created_at':timenow,'date':date,'hour':hour,'User_Agent':User_Agent,'Host':Host,'Connection':Connection,'Pragma':Pragma,'Cache_Control':Cache_Control,'Accept':Accept,'Accept_Encoding':Accept_Encoding,'Accept_Language':Accept_Language,'ip':ip,'ip_city':ip_city,'ip_asn':ip_asn,'url':url,'referrer':referrer,'remark':remark,'ua_platform':ua_platform,'ua_browser':ua_browser,'ua_version':ua_version,'ua_language':ua_language}
result,count = do_tidb_exe(sql=sql, args=key)
if count == 0:
write_to_log(filename='db_func',defname='insert_event',result=result+sql+str(key))
return count
def duplicate_scheduler_jobs_sql(project,list_id):
sql=f"""insert INTO scheduler_jobs (project,
group_id,
list_index,
datetime,
`data`,
priority,
`status`,
created_at,
updated_at
)
SELECT
`scheduler_jobs`.project,
`scheduler_jobs`.group_id,
null,
UNIX_TIMESTAMP(CURRENT_TIME()),
`scheduler_jobs`.`data`,
`scheduler_jobs`.priority,
16,
UNIX_TIMESTAMP(CURRENT_TIME()),
UNIX_TIMESTAMP(CURRENT_TIME())
from {project}_usergroup_list join scheduler_jobs on {project}_usergroup_list.jobs_id=scheduler_jobs.id where {project}_usergroup_list.id={list_id}"""
result = do_tidb_exe(sql=sql)
return result
def update():
sql_alter_update = """ALTER TABLE `project_list` ADD COLUMN `enable_scheduler` int(4) NULL DEFAULT 1 COMMENT '是否启动定时器支持' AFTER `user_count`;"""
do_tidb_exe(sql_alter_update)
print('项目表已更新')
sql_insert_status_code = """CREATE TABLE IF NOT EXISTS `status_code` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`desc` varchar(255) DEFAULT NULL COMMENT '含义',
`p_id` int(11) DEFAULT NULL COMMENT '父id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;"""
do_tidb_exe(sql_insert_status_code)
print('状态码表创建完')
status_codes = [
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (1, '分群列表状态', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (2, '创建列表开始', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (3, '分群信息写入中', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (4, '分群写入完成并包含错误', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (5, '分群写入完成', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (6, '分群写入失败', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (7, '生效策略', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (8, '自动', 7);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (9, '手动', 7);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (10, '禁用', 7);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (11, '进入分群队列', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (12, '优先级', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (13, '普通', 12);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (14, '高', 12);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (15, '最高', 12);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (16, '已添加任务队列', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (17, '任务已被选取', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (18, '任务方法加载完', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (19, '任务执行成功', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (20, '分群ETL失败', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (21, '任务执行失败', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (22, '通知方式', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (23, 'email', 22);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (24, '自动分群但不自动应用模板', 7);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (25, '推送状态', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (26, '推送成功', 25);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (27, '推送失败', 25);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (28, '自动分群自动应用模板但不自动发送', 7);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (29,'微信公众号',22);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (30,'黑名单修改原因',0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (31,'用户自助退订',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (32,'用户自助取消退订',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (33,'客服投诉退订',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (34,'客服取消退订',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (35,'接收地址错误',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (36,'接收地址判定为垃圾邮件',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (37,'导入第三方黑名单',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (38,'第三方白名单覆盖',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (39,'黑名单状态',0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (40,'全部禁用',39);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (41,'推广类禁用',39);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (42,'通知类禁用',39);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (43,'拟加入黑名单待确认（如等待运营确认）',39);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (44,'已解禁',39);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (45,'不允许解禁',39);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (46,'误判人工解除',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (47,'客服主观退订',30);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (48,'消息级别',0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (49,'紧急广播（忽略一切退订限制）',48);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (50,'IM',48);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (51,'通知',48);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (52,'运营',48);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (53,'推广',48);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (54,'运营类禁用',39);"
]
for code in status_codes:
do_tidb_exe(code)
print('状态码添加完毕')
blacklist_sql_1 = """CREATE TABLE `recall_blacklist` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`project` varchar(255) NOT NULL COMMENT '项目名',
`distinct_id` varchar(255) DEFAULT NULL,
`key` varchar(255) NOT NULL COMMENT '渠道key',
`type_id` int(11) NOT NULL COMMENT '渠道类型',
`reason_id` int(11) DEFAULT NULL COMMENT '原因id',
`owner` varchar(255) DEFAULT NULL COMMENT '第一次操作所属人',
`latest_owner` varchar(255) DEFAULT NULL COMMENT '最后一次操作所属人',
`status` int(11) DEFAULT NULL COMMENT '状态',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `anti_copy` (`key`,`type_id`,`project`),
KEY `check_blacklist` (`status`,`key`,`type_id`,`project`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;"""
blacklist_sql_2 = """CREATE TABLE `recall_blacklist_history` (
`rbid` int(11) NOT NULL COMMENT 'recall_blacklist的id',
`checker` varchar(255) DEFAULT NULL COMMENT '查询者的名字',
`result_status_id` int(11) DEFAULT NULL COMMENT '返回的status_code里pid是39的状态',
`result_reason_id` int(11) DEFAULT NULL COMMENT '返回的status_code里pid是30的理由',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
KEY `rbid` (`rbid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
blacklist_sql_3 = """CREATE TABLE `recall_blacklist_reason` (
`rbid` int(11) NOT NULL COMMENT 'recall_blacklist的id',
`reason_id` int(11) DEFAULT NULL COMMENT 'status_code里pid是30的状态',
`reason_owner` varchar(255) DEFAULT NULL COMMENT '修改人',
`reason_comment` varchar(255) DEFAULT NULL COMMENT '修改的备注',
`final_status_id` int(11) DEFAULT NULL COMMENT '最后写入recall_blacklist的status_code里pid是39的状态',
`created_at` varchar(255) DEFAULT NULL COMMENT '创建的时间',
KEY `rbid` (`rbid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
do_tidb_exe(blacklist_sql_1)
print('黑名单表添加完毕')
do_tidb_exe(blacklist_sql_2)
print('黑名单查询历史表添加完毕')
do_tidb_exe(blacklist_sql_3)
print('黑名单修改历史表添加完毕')
sql_scheduler_jobs = """CREATE TABLE IF NOT EXISTS `scheduler_jobs` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '任务id',
`project` varchar(255) DEFAULT NULL COMMENT '项目id',
`group_id` int(11) DEFAULT NULL COMMENT 'group_plan的id',
`list_index` int(11) DEFAULT NULL COMMENT 'group_index任务完成后，补充',
`datetime` int(11) DEFAULT NULL COMMENT '执行的日期，即要执行的那个任务的时间（不是任务执行时间，是要执行的时间。如周三时执行周一的任务。也用来防止任务重复添加）',
`data` json DEFAULT NULL COMMENT '其他附带的参数',
`priority` int(4) DEFAULT NULL COMMENT '优先级',
`status` int(4) DEFAULT NULL COMMENT '状态',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `ind_task` (`project`,`group_id`,`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;"""
do_tidb_exe(sql_scheduler_jobs)
print('任务计划表添加完毕')
project_list, project_count = select_all_project()
for project in project_list:
insert_data = """CREATE TABLE IF NOT EXISTS `{project_name}_usergroup_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`group_list_id` int(11) DEFAULT NULL COMMENT '分群列表id',
`data_index` int(11) DEFAULT NULL COMMENT '最新一组数据的index_id',
`data_key` varchar(255) DEFAULT NULL COMMENT '数据的唯一识别id',
`data_json` json DEFAULT NULL COMMENT '数据包',
`enable` int(11) DEFAULT NULL COMMENT '生效策略。参考status_code，p_id=7',
`created_at` int(11) DEFAULT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `group_list_id` (`group_list_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(
project_name=project[0])
do_tidb_exe(insert_data)
insert_list = """CREATE TABLE IF NOT EXISTS `{project_name}_usergroup_list` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分群列表id',
`group_id` int(11) DEFAULT NULL COMMENT '分群id',
`group_list_index` int(11) DEFAULT NULL COMMENT '分群列表顺位',
`list_init_date` int(11) DEFAULT NULL COMMENT '触发时间',
`list_desc` varchar(255) DEFAULT NULL COMMENT '清单所描述的',
`jobs_id` int(4) DEFAULT NULL COMMENT 'scheduler_jbos的id',
`item_count` int(11) DEFAULT NULL COMMENT '分组条目数',
`status` int(4) DEFAULT NULL COMMENT '分群状态。参考status_code,p_id=1',
`complete_at` int(11) DEFAULT NULL COMMENT '分群完成时间',
`apply_temple_times` int(2) DEFAULT 0 COMMENT '被套用模板的次数',
`created_at` int(11) DEFAULT NULL COMMENT '条目创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '条目更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `unique_key` (`group_id`,`group_list_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(
project_name=project[0])
do_tidb_exe(insert_list)
insert_plan = """CREATE TABLE IF NOT EXISTS `{project_name}_usergroup_plan` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分群id',
`group_title` varchar(255) DEFAULT NULL COMMENT '分群标题',
`group_desc` varchar(255) DEFAULT NULL COMMENT '分群描述',
`func` json DEFAULT NULL COMMENT '分群执行方法参考/scheduler_jobs/scheduler_job_creator.py',
`latest_data_list_index` int(11) DEFAULT NULL COMMENT '最新一组数据的id',
`repeatable` varchar(20) DEFAULT NULL COMMENT '定时器，分，时，日，月，周。不填的用*代替。跟crontab一个逻辑，不支持1-10的方式表达，多日的需要1,2,3,4,5,6,7,8这样的形式填',
`priority` int(4) DEFAULT NULL COMMENT '任务执行优先级',
`latest_data_time` int(11) DEFAULT NULL COMMENT '最新一组数据的完成时间',
`repeat_times` int(11) DEFAULT 0 COMMENT '分群完成次数',
`enable_policy` int(11) DEFAULT NULL COMMENT '生效策略。参考status_code，p_id=7',
`latest_apply_temple_id` int(11) DEFAULT NULL COMMENT '最后一次执行的模板类型',
`latest_apply_temple_time` int(11) DEFAULT NULL COMMENT '最后一次执行的模型时间',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(
project_name=project[0])
do_tidb_exe(insert_plan)
print(project[0] + '的分群附加表表已添加完')
do_tidb_exe(insert_list)
insert_noti = """CREATE TABLE IF NOT EXISTS `{project_name}_noti` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`plan_id` int(11) DEFAULT NULL COMMENT '计划id',
`list_id` int(11) DEFAULT NULL COMMENT '列表id',
`data_id` int(11) DEFAULT NULL COMMENT '数据id',
`temple_id` int(4) DEFAULT NULL COMMENT '模板id',
`noti_group_id` int(11) DEFAULT NULL COMMENT '消息群组id',
`distinct_id` varchar(512) DEFAULT NULL COMMENT '用户识别id',
`priority` int(4) DEFAULT NULL COMMENT '优先级',
`status` int(4) DEFAULT NULL COMMENT '状态',
`owner` varchar(255) DEFAULT NULL COMMENT '添加人',
`level` int(4) DEFAULT NULL COMMENT '消息级别',
`type` int(4) DEFAULT NULL COMMENT '消息类型',
`key` varchar(255) DEFAULT NULL COMMENT '消息接受方式key',
`content` json DEFAULT NULL COMMENT '消息内容',
`send_at` int(11) DEFAULT NULL COMMENT '计划发送时间',
`recall_result` text DEFAULT NULL COMMENT '发送结果',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `distinct_id` (`distinct_id`),
KEY `send_plan` (`status`,`priority`,`send_at`),
KEY `key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(
project_name=project[0])
do_tidb_exe(insert_noti)
insert_noti_group = """CREATE TABLE IF NOT EXISTS `{project_name}_noti_group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`plan_id` int(11) DEFAULT NULL COMMENT '分群计划id',
`list_id` int(11) DEFAULT NULL COMMENT '分群列表id',
`data_id` int(11) DEFAULT NULL COMMENT '分群数据id',
`temple_id` int(11) DEFAULT NULL COMMENT '应用模板id',
`priority` int(4) DEFAULT NULL COMMENT '优先级id',
`status` int(4) DEFAULT NULL COMMENT '状态id',
`owner` varchar(255) DEFAULT NULL COMMENT '添加人',
`send_at` int(11) DEFAULT NULL COMMENT '计划发送时间',
`sent` int(11) DEFAULT NULL COMMENT '已发送数目',
`total` int(11) DEFAULT NULL COMMENT '该计划总数目',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1001;""".format(
project_name=project[0])
do_tidb_exe(insert_noti_group)
insert_noti_temple = """CREATE TABLE IF NOT EXISTS `{project_name}_noti_temple` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL COMMENT '模板名称',
`temple_desc` varchar(255) DEFAULT NULL COMMENT '模板描述',
`args` json DEFAULT NULL COMMENT '模板参数',
`content` json DEFAULT NULL COMMENT '模板内容',
`apply_times` int(11) DEFAULT 0 COMMENT '应用次数',
`lastest_apply_time` int(11) DEFAULT NULL COMMENT '最后一次应用时间',
`lastest_apply_list` int(11) DEFAULT NULL COMMENT '最后一次应用列表',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1001;""".format(
project_name=project[0])
do_tidb_exe(insert_noti_temple)
print(project[0] + '的消息附加表表已添加完')
def insert_scheduler_job(project,group_id,datetime,data={},priority=13,status=11):
import json
sql = """insert ignore scheduler_jobs (`project`,`group_id`,`datetime`,`data`,`priority`,`status`,`created_at`,`updated_at`) values ('{project}',{group_id},{datetime},'{data}',{priority},{status},{created_at},{updated_at})""".format(project=project,group_id=group_id,datetime=datetime,data=json.dumps(data),priority=priority,status=status,created_at=int(time.time()),updated_at=int(time.time()))
result = do_tidb_exe(sql=sql)
return result[0],result[1]
def create_project(project_name,expired=None):
#创建新项目，project_name是项目名，expired是过期时间，字串输入 2019-01-01 格式
create_project_list = """CREATE TABLE IF NOT EXISTS `project_list` (
`project_name` varchar(255) DEFAULT NULL COMMENT '项目名称',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`expired_at` int(11) DEFAULT NULL COMMENT '过期时间',
`event_count` bigint(20) DEFAULT NULL COMMENT '事件量',
`device_count` bigint(20) DEFAULT NULL COMMENT '设备数',
`user_count` bigint(20) DEFAULT NULL COMMENT '用户数',
`enable_scheduler` int(4) DEFAULT 1 COMMENT '是否启动定时器支持'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
create_shortcut = """CREATE TABLE if not EXISTS `shortcut` (
`project` varchar(255) DEFAULT NULL COMMENT '项目名',
`short_url` varchar(255) DEFAULT NULL COMMENT '短链地址',
`long_url` varchar(768) DEFAULT NULL COMMENT '长链地址',
`expired_at` int(11) DEFAULT NULL COMMENT '过期时间',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`src` varchar(10) DEFAULT NULL COMMENT '使用的第三方创建源',
`src_short_url` varchar(255) DEFAULT NULL COMMENT '创建源返回的短地址',
`submitter` varchar(255) DEFAULT NULL COMMENT '由谁提交',
`utm_source` varchar(2048) DEFAULT NULL COMMENT 'utm_source',
`utm_medium` varchar(2048) DEFAULT NULL COMMENT 'utm_medium',
`utm_campaign` varchar(2048) DEFAULT NULL COMMENT 'utm_campaign',
`utm_content` varchar(2048) DEFAULT NULL COMMENT 'utm_content',
`utm_term` varchar(2048) DEFAULT NULL COMMENT 'utm_term',
KEY `short_url` (`short_url`),
KEY `long_url` (`long_url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
create_shortcut_history = """CREATE TABLE IF NOT EXISTS `shortcut_history` (
`short_url` varchar(255) DEFAULT NULL COMMENT '解析短链',
`result` varchar(255) DEFAULT NULL COMMENT '解析的结果',
`cost_time` int(11) DEFAULT NULL COMMENT '耗费时间',
`ip` varchar(255) DEFAULT NULL,
`created_at` int(11) DEFAULT NULL COMMENT '解析时间',
`user_agent` text DEFAULT NULL,
`accept_language` text DEFAULT NULL,
`ua_platform` varchar(255) DEFAULT NULL,
`ua_browser` varchar(255) DEFAULT NULL,
`ua_version` varchar(255) DEFAULT NULL,
`ua_language` varchar(255) DEFAULT NULL,
KEY `created_at` (`created_at`),
KEY `short_url` (`short_url`),
KEY `short_url_result` (`short_url`,`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
create_mobile_ad_src ="""CREATE TABLE if not EXISTS `mobile_ad_src` (
`src` varchar(255) NOT NULL COMMENT '创建源名称',
`src_name` varchar(255) DEFAULT NULL COMMENT '创建源的中文名字',
`src_args` varchar(1024) DEFAULT NULL COMMENT '创建源自带参数',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '维护时间',
`utm_source` varchar(255) DEFAULT NULL COMMENT '预制的utm_source',
`utm_medium` varchar(255) DEFAULT NULL COMMENT '预制的utm_medium',
`utm_campaign` varchar(255) DEFAULT NULL COMMENT '预制的utm_campaign',
`utm_content` varchar(255) DEFAULT NULL COMMENT '预制的utm_content',
`utm_term` varchar(255) DEFAULT NULL COMMENT '预制的utm_term',
PRIMARY KEY (`src`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
create_mobile_ad_list ="""CREATE TABLE if not EXISTS `mobile_ad_list` (
`project` varchar(255) DEFAULT NULL COMMENT '项目名',
`url` varchar(768) NOT NULL COMMENT '监测地址',
`expired_at` int(11) DEFAULT NULL COMMENT '过期时间',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`src` varchar(255) DEFAULT NULL COMMENT '使用的检测原id',
`src_url` varchar(1024) DEFAULT NULL COMMENT '使用的检测模板',
`submitter` varchar(255) DEFAULT NULL COMMENT '由谁提交',
`utm_source` varchar(2048) DEFAULT NULL COMMENT 'utm_source',
`utm_medium` varchar(2048) DEFAULT NULL COMMENT 'utm_medium',
`utm_campaign` varchar(2048) DEFAULT NULL COMMENT 'utm_campaign',
`utm_content` varchar(2048) DEFAULT NULL COMMENT 'utm_content',
`utm_term` varchar(2048) DEFAULT NULL COMMENT 'utm_term',
PRIMARY KEY (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
created_shortcut_read="""CREATE TABLE if not EXISTS `shortcut_read` (
`short_url` varchar(255) NOT NULL COMMENT '短链地址',
`ip` varchar(20) DEFAULT NULL COMMENT 'ip',
`created_at` int(11) DEFAULT NULL COMMENT '时间',
`user_agent` text DEFAULT NULL COMMENT 'ua',
`accept_language` text DEFAULT NULL COMMENT '语言',
`ua_platform` varchar(255) DEFAULT NULL COMMENT '平台',
`ua_browser` varchar(255) DEFAULT NULL COMMENT '浏览器',
`ua_version` varchar(255) DEFAULT NULL COMMENT '版本号',
`ua_language` varchar(255) DEFAULT NULL COMMENT '语言',
`referrer` text DEFAULT NULL COMMENT '页面',
KEY `short_url` (`short_url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
blacklist_sql_1="""CREATE TABLE IF NOT EXISTS `recall_blacklist` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`project` varchar(255) NOT NULL COMMENT '项目名',
`distinct_id` varchar(255) DEFAULT NULL,
`key` varchar(255) NOT NULL COMMENT '渠道key',
`type_id` int(11) NOT NULL COMMENT '渠道类型',
`reason_id` int(11) DEFAULT NULL COMMENT '原因id',
`owner` varchar(255) DEFAULT NULL COMMENT '第一次操作所属人',
`latest_owner` varchar(255) DEFAULT NULL COMMENT '最后一次操作所属人',
`status` int(11) DEFAULT NULL COMMENT '状态',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `anti_copy` (`key`,`type_id`,`project`),
KEY `check_blacklist` (`status`,`key`,`type_id`,`project`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;"""
blacklist_sql_2="""CREATE TABLE IF NOT EXISTS `recall_blacklist_history` (
`rbid` int(11) NOT NULL COMMENT 'recall_blacklist的id',
`checker` varchar(255) DEFAULT NULL COMMENT '查询者的名字',
`result_status_id` int(11) DEFAULT NULL COMMENT '返回的status_code里pid是39的状态',
`result_reason_id` int(11) DEFAULT NULL COMMENT '返回的status_code里pid是30的理由',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
KEY `rbid` (`rbid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
blacklist_sql_3="""CREATE TABLE IF NOT EXISTS `recall_blacklist_reason` (
`rbid` int(11) NOT NULL COMMENT 'recall_blacklist的id',
`reason_id` int(11) DEFAULT NULL COMMENT 'status_code里pid是30的状态',
`reason_owner` varchar(255) DEFAULT NULL COMMENT '修改人',
`reason_comment` varchar(255) DEFAULT NULL COMMENT '修改的备注',
`final_status_id` int(11) DEFAULT NULL COMMENT '最后写入recall_blacklist的status_code里pid是39的状态',
`created_at` varchar(255) DEFAULT NULL COMMENT '创建的时间',
KEY `rbid` (`rbid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
do_tidb_exe(create_project_list)
do_tidb_exe(create_shortcut)
do_tidb_exe(create_shortcut_history)
do_tidb_exe(create_mobile_ad_src)
do_tidb_exe(create_mobile_ad_list)
do_tidb_exe(created_shortcut_read)
do_tidb_exe(blacklist_sql_1)
do_tidb_exe(blacklist_sql_2)
do_tidb_exe(blacklist_sql_3)
# print('project_list已生成')
check_sql = "show tables"
check_result,check_count = do_tidb_select(check_sql)
tables_name = []
for line in check_result:
tables_name.append(line[0])
# print(tables_name)
check_project_list_sql = """SELECT count(*) FROM `project_list` where project_name='{project_name}'""".format(project_name=project_name)
check_project_list_result,check_project_list_count = do_tidb_select(check_project_list_sql)
# print(check_project_list_result[0][0])
if project_name in tables_name or check_project_list_result[0][0]>0:
print(project_name+'项目表单已存在')
else:
table_sql="""CREATE TABLE `{project_name}` (
`track_id` bigint(17) DEFAULT NULL,
`distinct_id` varchar(64) DEFAULT NULL,
`lib` varchar(255) DEFAULT NULL,
`event` varchar(255) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`all_json` json DEFAULT NULL,
`host` varchar(255) DEFAULT NULL,
`user_agent` varchar(2048) DEFAULT NULL,
`ua_platform` varchar(1024) DEFAULT NULL,
`ua_browser` varchar(1024) DEFAULT NULL,
`ua_version` varchar(1024) DEFAULT NULL,
`ua_language` varchar(1024) DEFAULT NULL,
`connection` varchar(255) DEFAULT NULL,
`pragma` varchar(255) DEFAULT NULL,
`cache_control` varchar(255) DEFAULT NULL,
`accept` varchar(255) DEFAULT NULL,
`accept_encoding` varchar(255) DEFAULT NULL,
`accept_language` varchar(255) DEFAULT NULL,
`ip` varchar(512) DEFAULT NULL,
`ip_city` json DEFAULT NULL,
`ip_asn` json DEFAULT NULL,
`url` text DEFAULT NULL,
`referrer` varchar(2048) DEFAULT NULL,
`remark` varchar(255) DEFAULT NULL,
`created_at` int(11) DEFAULT NULL,
`date` date DEFAULT NULL,
`hour` int(2) DEFAULT NULL,
KEY `date` (`date`),
KEY `distinct_id` (`distinct_id`),
KEY `event` (`event`),
KEY `date_hour` (`date`,`hour`),
KEY `event_date` (`event`,`date`),
KEY `event_remark_date` (`event`,`remark`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;""".format(project_name=project_name)
table_device_sql ="""CREATE TABLE `{project_name}_device` (
`distinct_id` varchar(255) NOT NULL,
`lib` varchar(255) DEFAULT NULL,
`device_id` varchar(255) DEFAULT NULL,
`manufacturer` varchar(255) DEFAULT NULL,
`model` varchar(255) DEFAULT NULL,
`os` varchar(255) DEFAULT NULL,
`os_version` varchar(255) DEFAULT NULL,
`ua_platform` varchar(1024) DEFAULT NULL,
`ua_browser` varchar(1024) DEFAULT NULL,
`ua_version` varchar(1024) DEFAULT NULL,
`ua_language` varchar(1024) DEFAULT NULL,
`screen_width` int(11) DEFAULT NULL,
`screen_height` int(11) DEFAULT NULL,
`network_type` varchar(255) DEFAULT NULL,
`user_agent` varchar(2048) DEFAULT NULL,
`accept_language` varchar(255) DEFAULT NULL,
`ip` varchar(255) DEFAULT NULL,
`ip_city` json DEFAULT NULL,
`ip_asn` json DEFAULT NULL,
`wifi` varchar(20) DEFAULT NULL,
`app_version` varchar(255) DEFAULT NULL,
`carrier` varchar(255) DEFAULT NULL,
`referrer` text DEFAULT NULL,
`referrer_host` varchar(2048) DEFAULT NULL,
`bot_name` varchar(255) DEFAULT NULL,
`browser` varchar(255) DEFAULT NULL,
`browser_version` varchar(255) DEFAULT NULL,
`is_login_id` varchar(255) DEFAULT NULL,
`screen_orientation` varchar(255) DEFAULT NULL,
`gps_latitude` decimal(11,7) DEFAULT NULL,
`gps_longitude` decimal(11,7) DEFAULT NULL,
`first_visit_time` datetime DEFAULT NULL,
`first_referrer` text DEFAULT NULL,
`first_referrer_host` varchar(768) DEFAULT NULL,
`first_browser_language` varchar(768) DEFAULT NULL,
`first_browser_charset` varchar(768) DEFAULT NULL,
`first_search_keyword` varchar(768) DEFAULT NULL,
`first_traffic_source_type` varchar(768) DEFAULT NULL,
`utm_content` varchar(768) DEFAULT NULL,
`utm_campaign` varchar(768) DEFAULT NULL,
`utm_medium` varchar(768) DEFAULT NULL,
`utm_term` varchar(768) DEFAULT NULL,
`utm_source` varchar(768) DEFAULT NULL,
`latest_utm_content` varchar(768) DEFAULT NULL,
`latest_utm_campaign` varchar(768) DEFAULT NULL,
`latest_utm_medium` varchar(768) DEFAULT NULL,
`latest_utm_term` varchar(768) DEFAULT NULL,
`latest_utm_source` varchar(768) DEFAULT NULL,
`latest_referrer` varchar(2048) DEFAULT NULL,
`latest_referrer_host` varchar(2048) DEFAULT NULL,
`latest_search_keyword` varchar(768) DEFAULT NULL,
`latest_traffic_source_type` varchar(255) DEFAULT NULL,
`created_at` int(11) DEFAULT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`distinct_id`),
KEY `utm_campaign` (`utm_campaign`),
KEY `utm_source` (`utm_source`),
KEY `utm_medium` (`utm_medium`),
KEY `utm_term` (`utm_term`),
KEY `utm_content` (`utm_content`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;""".format(project_name=project_name)
table_user_sql = """CREATE TABLE `{project_name}_user` (
`distinct_id` varchar(200) NOT NULL,
`lib` varchar(127) NOT NULL,
`map_id` varchar(200) NOT NULL,
`original_id` varchar(200) NOT NULL,
`user_id` varchar(255) DEFAULT NULL,
`all_user_profile` json DEFAULT NULL,
`created_at` int(11) DEFAULT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`distinct_id`,`lib`,`map_id`,`original_id`),
KEY `distinct_id` (`distinct_id`),
KEY `map_id` (`map_id`),
KEY `original_id` (`original_id`),
KEY `distinct_id_lib` (`distinct_id`,`lib`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;""".format(project_name=project_name)
table_properties_sql = """CREATE TABLE `{project_name}_properties` (
`lib` varchar(255) NOT NULL,
`remark` varchar(255) NOT NULL,
`event` varchar(255) NOT NULL,
`properties` json DEFAULT NULL,
`properties_len` int(10) DEFAULT NULL,
`created_at` int(10) DEFAULT NULL,
`updated_at` int(10) DEFAULT NULL,
`lastinsert_at` int(10) DEFAULT NULL,
`total_count` bigint(20) DEFAULT NULL,
PRIMARY KEY (`lib`,`remark`,`event`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;;""".format(project_name=project_name)
do_tidb_exe(table_sql)
print(project_name+'table表单已插入')
do_tidb_exe(table_device_sql)
print(project_name+'device表单已插入')
do_tidb_exe(table_user_sql)
print(project_name+'user表单已插入')
do_tidb_exe(table_properties_sql)
print(project_name+'properties表单已插入')
sql_insert_status_code = """CREATE TABLE IF NOT EXISTS `status_code` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`desc` varchar(255) DEFAULT NULL COMMENT '含义',
`p_id` int(11) DEFAULT NULL COMMENT '父id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;"""
do_tidb_exe(sql_insert_status_code)
print('状态码表创建完')
status_codes = ["INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (1, '分群列表状态', 0);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (2, '创建列表开始', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (3, '分群信息写入中', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (4, '分群写入完成并包含错误', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (5, '分群写入完成', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (6, '分群写入失败', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (7, '生效策略', 0);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (8, '自动', 7);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (9, '手动', 7);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (10, '禁用', 7);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (11, '进入分群队列', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (12, '优先级', 0);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (13, '普通', 12);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (14, '高', 12);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (15, '最高', 12);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (16, '已添加任务队列', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (17, '任务已被选取', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (18, '任务方法加载完', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (19, '任务执行成功', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (20, '分群ETL失败', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (21, '任务执行失败', 1);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (22, '通知方式', 0);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (23, 'email', 22);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (24, '自动分群但不自动应用模板', 7);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (25, '推送状态', 0);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (26, '推送成功', 25);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (27, '推送失败', 25);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (28, '自动分群自动应用模板但不自动发送', 7);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (29,'微信公众号',22);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (30,'黑名单修改原因',0);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (31,'用户自助退订',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (32,'用户自助取消退订',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (33,'客服投诉退订',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (34,'客服取消退订',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (35,'接收地址错误',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (36,'接收地址判定为垃圾邮件',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (37,'导入第三方黑名单',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (38,'第三方白名单覆盖',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (39,'黑名单状态',0);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (40,'全部禁用',39);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (41,'推广类禁用',39);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (42,'通知类禁用',39);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (43,'拟加入黑名单待确认（如等待运营确认）',39);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (44,'已解禁',39);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (45,'不允许解禁',39);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (46,'误判人工解除',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (47,'客服主观退订',30);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (48,'消息级别',0);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (49,'紧急广播（忽略一切退订限制）',48);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (50,'IM',48);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (51,'通知',48);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (52,'运营',48);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (53,'推广',48);","INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (54,'运营类禁用',39);"]
for code in status_codes:
do_tidb_exe(code)
print('状态码添加完毕')
sql_scheduler_jobs = """CREATE TABLE IF NOT EXISTS `scheduler_jobs` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '任务id',
`project` varchar(255) DEFAULT NULL COMMENT '项目id',
`group_id` int(11) DEFAULT NULL COMMENT 'group_plan的id',
`list_index` int(11) DEFAULT NULL COMMENT 'group_index任务完成后，补充',
`datetime` int(11) DEFAULT NULL COMMENT '执行的日期，即要执行的那个任务的时间（不是任务执行时间，是要执行的时间。如周三时执行周一的任务。也用来防止任务重复添加）',
`data` json DEFAULT NULL COMMENT '其他附带的参数',
`priority` int(4) DEFAULT NULL COMMENT '优先级',
`status` int(4) DEFAULT NULL COMMENT '状态',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `ind_task` (`project`,`group_id`,`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;"""
do_tidb_exe(sql_scheduler_jobs)
print('任务计划表添加完毕')
insert_data = """CREATE TABLE IF NOT EXISTS `{project_name}_usergroup_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`group_list_id` int(11) DEFAULT NULL COMMENT '分群列表id',
`data_index` int(11) DEFAULT NULL COMMENT '最新一组数据的index_id',
`data_key` varchar(255) DEFAULT NULL COMMENT '数据的唯一识别id',
`data_json` json DEFAULT NULL COMMENT '数据包',
`enable` int(11) DEFAULT NULL COMMENT '生效策略。参考status_code，p_id=7',
`created_at` int(11) DEFAULT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `group_list_id` (`group_list_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(project_name=project_name)
do_tidb_exe(insert_data)
insert_list = """CREATE TABLE IF NOT EXISTS `{project_name}_usergroup_list` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分群列表id',
`group_id` int(11) DEFAULT NULL COMMENT '分群id',
`group_list_index` int(11) DEFAULT NULL COMMENT '分群列表顺位',
`list_init_date` int(11) DEFAULT NULL COMMENT '触发时间',
`list_desc` varchar(255) DEFAULT NULL COMMENT '清单所描述的',
`jobs_id` int(4) DEFAULT NULL COMMENT 'scheduler_jbos的id',
`item_count` int(11) DEFAULT NULL COMMENT '分组条目数',
`status` int(4) DEFAULT NULL COMMENT '分群状态。参考status_code,p_id=1',
`complete_at` int(11) DEFAULT NULL COMMENT '分群完成时间',
`apply_temple_times` int(2) DEFAULT 0 COMMENT '被套用模板的次数',
`created_at` int(11) DEFAULT NULL COMMENT '条目创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '条目更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `unique_key` (`group_id`,`group_list_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(project_name=project_name)
do_tidb_exe(insert_list)
insert_plan = """CREATE TABLE IF NOT EXISTS `{project_name}_usergroup_plan` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分群id',
`group_title` varchar(255) DEFAULT NULL COMMENT '分群标题',
`group_desc` varchar(255) DEFAULT NULL COMMENT '分群描述',
`func` json DEFAULT NULL COMMENT '分群执行方法参考/scheduler_jobs/scheduler_job_creator.py',
`latest_data_list_index` int(11) DEFAULT NULL COMMENT '最新一组数据的id',
`repeatable` varchar(20) DEFAULT NULL COMMENT '定时器，分，时，日，月，周。不填的用*代替。跟crontab一个逻辑，不支持1-10的方式表达，多日的需要1,2,3,4,5,6,7,8这样的形式填',
`priority` int(4) DEFAULT NULL COMMENT '任务执行优先级',
`latest_data_time` int(11) DEFAULT NULL COMMENT '最新一组数据的完成时间',
`repeat_times` int(11) DEFAULT 0 COMMENT '分群完成次数',
`enable_policy` int(11) DEFAULT NULL COMMENT '生效策略。参考status_code，p_id=7',
`latest_apply_temple_id` int(11) DEFAULT NULL COMMENT '最后一次执行的模板类型',
`latest_apply_temple_time` int(11) DEFAULT NULL COMMENT '最后一次执行的模型时间',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(project_name=project_name)
do_tidb_exe(insert_plan)
print(project_name+'的分群附加表表已添加完')
do_tidb_exe(insert_list)
insert_noti = """CREATE TABLE IF NOT EXISTS `{project_name}_noti` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`plan_id` int(11) DEFAULT NULL COMMENT '计划id',
`list_id` int(11) DEFAULT NULL COMMENT '列表id',
`data_id` int(11) DEFAULT NULL COMMENT '数据id',
`temple_id` int(4) DEFAULT NULL COMMENT '模板id',
`noti_group_id` int(11) DEFAULT NULL COMMENT '消息群组id',
`distinct_id` varchar(512) DEFAULT NULL COMMENT '用户识别id',
`priority` int(4) DEFAULT NULL COMMENT '优先级',
`status` int(4) DEFAULT NULL COMMENT '状态',
`owner` varchar(255) DEFAULT NULL COMMENT '添加人',
`level` int(4) DEFAULT NULL COMMENT '消息级别',
`type` int(4) DEFAULT NULL COMMENT '消息类型',
`key` varchar(255) DEFAULT NULL COMMENT '消息接受方式key',
`content` json DEFAULT NULL COMMENT '消息内容',
`send_at` int(11) DEFAULT NULL COMMENT '计划发送时间',
`recall_result` text DEFAULT NULL COMMENT '发送结果',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `distinct_id` (`distinct_id`),
KEY `send_plan` (`status`,`priority`,`send_at`),
KEY `key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(project_name=project_name)
do_tidb_exe(insert_noti)
insert_noti_group = """CREATE TABLE IF NOT EXISTS `{project_name}_noti_group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`plan_id` int(11) DEFAULT NULL COMMENT '分群计划id',
`list_id` int(11) DEFAULT NULL COMMENT '分群列表id',
`data_id` int(11) DEFAULT NULL COMMENT '分群数据id',
`temple_id` int(11) DEFAULT NULL COMMENT '应用模板id',
`priority` int(4) DEFAULT NULL COMMENT '优先级id',
`status` int(4) DEFAULT NULL COMMENT '状态id',
`owner` varchar(255) DEFAULT NULL COMMENT '添加人',
`send_at` int(11) DEFAULT NULL COMMENT '计划发送时间',
`sent` int(11) DEFAULT NULL COMMENT '已发送数目',
`total` int(11) DEFAULT NULL COMMENT '该计划总数目',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1001;""".format(project_name=project_name)
do_tidb_exe(insert_noti_group)
insert_noti_temple = """CREATE TABLE IF NOT EXISTS `{project_name}_noti_temple` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL COMMENT '模板名称',
`temple_desc` varchar(255) DEFAULT NULL COMMENT '模板描述',
`args` json DEFAULT NULL COMMENT '模板参数',
`content` json DEFAULT NULL COMMENT '模板内容',
`apply_times` int(11) DEFAULT 0 COMMENT '应用次数',
`lastest_apply_time` int(11) DEFAULT NULL COMMENT '最后一次应用时间',
`lastest_apply_list` int(11) DEFAULT NULL COMMENT '最后一次应用列表',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1001;""".format(project_name=project_name)
do_tidb_exe(insert_noti_temple)
print(project_name+'的消息附加表表已添加完')
if expired == None:
expired_at = 2147483647
else:
expired_at = int(time.mktime(time.strptime(expired, "%Y-%m-%d")))
timenow = int(time.time())
insert_project_list = """insert project_list (`project_name`,`created_at`,`expired_at`) values ('{project_name}',{created_at},{expired_at})""".format(project_name=project_name,created_at=timenow,expired_at=expired_at)
do_tidb_exe(insert_project_list)
print(project_name+'project列表已插入')
def update_noti(project,noti_id,updated_at,status=0,recall_result=None):
sql = """update {project}_noti set status = if({status}=0,status,{status}) , recall_result = %(recall_result)s , updated_at = {updated_at} where id = {noti_id}""".format(project=project,noti_id=noti_id,updated_at = updated_at if updated_at else int(time.time()),status=status,recall_result=recall_result)
key = {"recall_result":recall_result}
result = do_tidb_exe(sql=sql,args=key)
return result[0],result[1]
def insert_noti(project,type_1,created_at,updated_at,distinct_id,content,send_at,plan_id=0,list_id=0,data_id=0,temple_id=0,noti_group_id=0,priority=13,status=9,owner='noti',recall_result=None):
import json
sql = """insert ignore {project}_noti (`distinct_id`,`plan_id`,`list_id`,`data_id`,`temple_id`,`noti_group_id`,`priority`,`status`,`owner`,`type`,`content`,`send_at`,`recall_result`,`created_at`,`updated_at`) values (%(distinct_id)s,%(plan_id)s,%(list_id)s,%(data_id)s,%(temple_id)s,%(noti_group_id)s,%(priority)s,%(status)s,%(owner)s,%(type)s,%(content)s,%(send_at)s,%(recall_result)s,%(created_at)s,%(updated_at)s)""".format(project=project)
key = {'distinct_id':distinct_id,'type':type_1,'plan_id':plan_id,'list_id':list_id,'data_id':data_id,'temple_id':temple_id,'noti_group_id':noti_group_id,'priority':priority,'status':status,'owner':owner,'content':json.dumps(content),'recall_result':recall_result,'send_at':send_at if send_at else int(time.time()),'created_at':created_at if created_at else int(time.time()),'updated_at':updated_at if updated_at else int(time.time())}
result = do_tidb_exe(sql=sql, args=key)
return result[0],result[1]
def update_noti_group(project,noti_group_id,sent=0,total=0,status=0):
updated_at=int(time.time())
sql = f"""update {project}_noti_group set sent=if({sent}=0,sent,sent+1),total=if({total}=0,total,{total}),status=if({status}=0,status,{status}),updated_at={updated_at} where id={noti_group_id}"""
result = do_tidb_exe(sql=sql)
return result
def update_noti_temple(project,temple_id,apply_times=0,lastest_apply_time=0,lastest_apply_list=0):
updated_at = int(time.time())
sql = """update {project}_noti_temple set apply_times=if({apply_times}=0,apply_times,apply_times+{apply_times}),lastest_apply_time=if({lastest_apply_time}=0,lastest_apply_time,{lastest_apply_time}),lastest_apply_list=if({lastest_apply_list}=0,lastest_apply_list,{lastest_apply_list}),updated_at={updated_at} where id={temple_id}""".format(project=project,temple_id=temple_id,apply_times=apply_times,lastest_apply_time=lastest_apply_time,lastest_apply_list=lastest_apply_list,updated_at=updated_at)
result = do_tidb_exe(sql=sql)
return result
def update():
sql_alter_update = """ALTER TABLE `project_list` ADD COLUMN `enable_scheduler` int(4) NULL DEFAULT 1 COMMENT '是否启动定时器支持' AFTER `user_count`;"""
do_tidb_exe(sql_alter_update)
print('项目表已更新')
sql_insert_status_code = """CREATE TABLE IF NOT EXISTS `status_code` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`desc` varchar(255) DEFAULT NULL COMMENT '含义',
`p_id` int(11) DEFAULT NULL COMMENT '父id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;"""
do_tidb_exe(sql_insert_status_code)
print('状态码表创建完')
status_codes = [
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (1, '分群列表状态', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (2, '创建列表开始', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (3, '分群信息写入中', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (4, '分群写入完成并包含错误', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (5, '分群写入完成', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (6, '分群写入失败', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (7, '生效策略', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (8, '自动', 7);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (9, '手动', 7);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (10, '禁用', 7);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (11, '进入分群队列', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (12, '优先级', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (13, '普通', 12);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (14, '高', 12);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (15, '最高', 12);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (16, '已添加任务队列', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (17, '任务已被选取', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (18, '任务方法加载完', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (19, '任务执行成功', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (20, '分群ETL失败', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (21, '任务执行失败', 1);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (22, '通知方式', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (23, 'email', 22);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (24, '自动分群但不自动应用模板', 7);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (25, '推送状态', 0);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (26, '推送成功', 25);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (27, '推送失败', 25);",
"INSERT IGNORE INTO `events`.`status_code`(`id`, `desc`, `p_id`) VALUES (28, '自动分群自动应用模板但不自动发送', 7);"
]
for code in status_codes:
do_tidb_exe(code)
print('状态码添加完毕')
sql_scheduler_jobs = """CREATE TABLE IF NOT EXISTS `scheduler_jobs` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '任务id',
`project` varchar(255) DEFAULT NULL COMMENT '项目id',
`group_id` int(11) DEFAULT NULL COMMENT 'group_plan的id',
`list_index` int(11) DEFAULT NULL COMMENT 'group_index任务完成后，补充',
`datetime` int(11) DEFAULT NULL COMMENT '执行的日期，即要执行的那个任务的时间（不是任务执行时间，是要执行的时间。如周三时执行周一的任务。也用来防止任务重复添加）',
`data` json DEFAULT NULL COMMENT '其他附带的参数',
`priority` int(4) DEFAULT NULL COMMENT '优先级',
`status` int(4) DEFAULT NULL COMMENT '状态',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `ind_task` (`project`,`group_id`,`datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;"""
do_tidb_exe(sql_scheduler_jobs)
print('任务计划表添加完毕')
project_list, project_count = select_all_project()
for project in project_list:
insert_data = """CREATE TABLE IF NOT EXISTS `{project_name}_usergroup_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`group_list_id` int(11) DEFAULT NULL COMMENT '分群列表id',
`data_index` int(11) DEFAULT NULL COMMENT '最新一组数据的index_id',
`data_key` varchar(255) DEFAULT NULL COMMENT '数据的唯一识别id',
`data_json` json DEFAULT NULL COMMENT '数据包',
`enable` int(11) DEFAULT NULL COMMENT '生效策略。参考status_code，p_id=7',
`created_at` int(11) DEFAULT NULL,
`updated_at` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `group_list_id` (`group_list_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(
project_name=project[0])
do_tidb_exe(insert_data)
insert_list = """CREATE TABLE IF NOT EXISTS `{project_name}_usergroup_list` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分群列表id',
`group_id` int(11) DEFAULT NULL COMMENT '分群id',
`group_list_index` int(11) DEFAULT NULL COMMENT '分群列表顺位',
`list_init_date` int(11) DEFAULT NULL COMMENT '触发时间',
`list_desc` varchar(255) DEFAULT NULL COMMENT '清单所描述的',
`jobs_id` int(4) DEFAULT NULL COMMENT 'scheduler_jbos的id',
`item_count` int(11) DEFAULT NULL COMMENT '分组条目数',
`status` int(4) DEFAULT NULL COMMENT '分群状态。参考status_code,p_id=1',
`complete_at` int(11) DEFAULT NULL COMMENT '分群完成时间',
`apply_temple_times` int(2) DEFAULT 0 COMMENT '被套用模板的次数',
`created_at` int(11) DEFAULT NULL COMMENT '条目创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '条目更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `unique_key` (`group_id`,`group_list_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(
project_name=project[0])
do_tidb_exe(insert_list)
insert_plan = """CREATE TABLE IF NOT EXISTS `{project_name}_usergroup_plan` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分群id',
`group_title` varchar(255) DEFAULT NULL COMMENT '分群标题',
`group_desc` varchar(255) DEFAULT NULL COMMENT '分群描述',
`func` json DEFAULT NULL COMMENT '分群执行方法参考/scheduler_jobs/scheduler_job_creator.py',
`latest_data_list_index` int(11) DEFAULT NULL COMMENT '最新一组数据的id',
`repeatable` varchar(20) DEFAULT NULL COMMENT '定时器，分，时，日，月，周。不填的用*代替。跟crontab一个逻辑，不支持1-10的方式表达，多日的需要1,2,3,4,5,6,7,8这样的形式填',
`priority` int(4) DEFAULT NULL COMMENT '任务执行优先级',
`latest_data_time` int(11) DEFAULT NULL COMMENT '最新一组数据的完成时间',
`repeat_times` int(11) DEFAULT 0 COMMENT '分群完成次数',
`enable_policy` int(11) DEFAULT NULL COMMENT '生效策略。参考status_code，p_id=7',
`latest_apply_temple_id` int(11) DEFAULT NULL COMMENT '最后一次执行的模板类型',
`latest_apply_temple_time` int(11) DEFAULT NULL COMMENT '最后一次执行的模型时间',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;""".format(
project_name=project[0])
do_tidb_exe(insert_plan)
print(project[0] + '的分群附加表表已添加完')
do_tidb_exe(insert_list)
insert_noti = """CREATE TABLE IF NOT EXISTS `{project_name}_noti` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`plan_id` int(11) DEFAULT NULL COMMENT '计划id',
`list_id` int(11) DEFAULT NULL COMMENT '列表id',
`data_id` int(11) DEFAULT NULL COMMENT '数据id',
`temple_id` int(4) DEFAULT NULL COMMENT '模板id',
`noti_group_id` int(11) DEFAULT NULL COMMENT '消息群组id',
`distinct_id` varchar(512) DEFAULT NULL COMMENT '用户识别id',
`priority` int(4) DEFAULT NULL COMMENT '优先级',
`status` int(4) DEFAULT NULL COMMENT '状态',
`owner` varchar(255) DEFAULT NULL COMMENT '添加人',
`type` int(4) DEFAULT NULL COMMENT '消息类型',
`content` json DEFAULT NULL COMMENT '消息内容',
`send_at` int(11) DEFAULT NULL COMMENT '计划发送时间',
`recall_result` text DEFAULT NULL COMMENT '发送结果',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `distinct_id` (`distinct_id`),
KEY `send_plan` (`status`,`priority`,`send_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1001;""".format(
project_name=project[0])
do_tidb_exe(insert_noti)
insert_noti_group = """CREATE TABLE IF NOT EXISTS `{project_name}_noti_group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`plan_id` int(11) DEFAULT NULL COMMENT '分群计划id',
`list_id` int(11) DEFAULT NULL COMMENT '分群列表id',
`data_id` int(11) DEFAULT NULL COMMENT '分群数据id',
`temple_id` int(11) DEFAULT NULL COMMENT '应用模板id',
`priority` int(4) DEFAULT NULL COMMENT '优先级id',
`status` int(4) DEFAULT NULL COMMENT '状态id',
`owner` varchar(255) DEFAULT NULL COMMENT '添加人',
`send_at` int(11) DEFAULT NULL COMMENT '计划发送时间',
`sent` int(11) DEFAULT NULL COMMENT '已发送数目',
`total` int(11) DEFAULT NULL COMMENT '该计划总数目',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1001;""".format(
project_name=project[0])
do_tidb_exe(insert_noti_group)
insert_noti_temple = """CREATE TABLE IF NOT EXISTS `{project_name}_noti_temple` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL COMMENT '模板名称',
`temple_desc` varchar(255) DEFAULT NULL COMMENT '模板描述',
`args` json DEFAULT NULL COMMENT '模板参数',
`content` json DEFAULT NULL COMMENT '模板内容',
`apply_times` int(11) DEFAULT 0 COMMENT '应用次数',
`lastest_apply_time` int(11) DEFAULT NULL COMMENT '最后一次应用时间',
`lastest_apply_list` int(11) DEFAULT NULL COMMENT '最后一次应用列表',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1001;""".format(
project_name=project[0])
do_tidb_exe(insert_noti_temple)
print(project[0] + '的消息附加表表已添加完')
def insert_usergroup_plan(project,group_title,group_desc,repeatable,priority,enable_policy,func_args):
sql = """insert {project}_usergroup_plan (group_title,group_desc,func,repeatable,priority,enable_policy,created_at,updated_at) values (%(group_title)s,%(group_desc)s,%(func_args)s,%(repeatable)s,%(priority)s,%(enable_policy)s,%(created_at)s,%(updated_at)s)""".format(project=project)
timenow = int(time.time())
key={'group_title':group_title,'group_desc':group_desc,'repeatable':repeatable,'priority':priority,'enable_policy':enable_policy,'func_args':func_args,'created_at':timenow,'updated_at':timenow}
result = do_tidb_exe(sql=sql,args=key)
return result
def update():
status_codes = ["INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (1, '分群列表状态', 0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (2, '创建列表开始', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (3, '分群信息写入中', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (4, '分群写入完成并包含错误', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (5, '分群写入完成', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (6, '分群写入失败', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (7, '生效策略', 0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (8, '自动', 7);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (9, '手动', 7);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (10, '禁用', 7);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (11, '进入分群队列', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (12, '优先级', 0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (13, '普通', 12);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (14, '高', 12);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (15, '最高', 12);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (16, '已添加任务队列', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (17, '任务已被选取', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (18, '任务方法加载完', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (19, '任务执行成功', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (20, '分群ETL失败', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (21, '任务执行失败', 1);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (22, '通知方式', 0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (23, 'email', 22);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (24, '自动分群但不自动应用模板', 7);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (25, '推送状态', 0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (26, '推送成功', 25);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (27, '推送失败', 25);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (28, '自动分群自动应用模板但不自动发送', 7);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (29,'微信公众号',22);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (30,'黑名单修改原因',0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (31,'用户自助退订',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (32,'用户自助取消退订',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (33,'客服投诉退订',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (34,'客服取消退订',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (35,'接收地址错误',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (36,'接收地址判定为垃圾邮件',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (37,'导入第三方黑名单',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (38,'第三方白名单覆盖',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (39,'黑名单状态',0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (40,'全部禁用',39);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (41,'推广类禁用',39);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (42,'通知类禁用',39);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (43,'拟加入黑名单待确认（如等待运营确认）',39);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (44,'已解禁',39);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (45,'不允许解禁',39);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (46,'误判人工解除',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (47,'客服主观退订',30);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (48,'消息级别',0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (49,'紧急广播（忽略一切退订限制）',48);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (50,'IM',48);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (51,'通知',48);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (52,'运营',48);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (53,'推广',48);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (54,'运营类禁用',39);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (55,'接入控制状态',0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (56,'取消黑名单',55);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (57,'临时黑名单',55);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (58,'永久黑名单',55);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (59,'接入控制类型',0);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (60,'ip',59);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (61,'ip_group',59);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (62,'distinct_id',59);","INSERT IGNORE INTO `status_code`(`id`, `desc`, `p_id`) VALUES (63,'add_on_key',59);"]
for code in status_codes:
do_tidb_exe(code)
print('状态码添加完毕')
blacklist_sql_1="""CREATE TABLE `recall_blacklist` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`project` varchar(255) NOT NULL COMMENT '项目名',
`distinct_id` varchar(255) DEFAULT NULL,
`key` varchar(255) NOT NULL COMMENT '渠道key',
`type_id` int(11) NOT NULL COMMENT '渠道类型',
`reason_id` int(11) DEFAULT NULL COMMENT '原因id',
`owner` varchar(255) DEFAULT NULL COMMENT '第一次操作所属人',
`latest_owner` varchar(255) DEFAULT NULL COMMENT '最后一次操作所属人',
`status` int(11) DEFAULT NULL COMMENT '状态',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
`updated_at` int(11) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `anti_copy` (`key`,`type_id`,`project`),
KEY `check_blacklist` (`status`,`key`,`type_id`,`project`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;"""
blacklist_sql_2="""CREATE TABLE `recall_blacklist_history` (
`rbid` int(11) NOT NULL COMMENT 'recall_blacklist的id',
`checker` varchar(255) DEFAULT NULL COMMENT '查询者的名字',
`result_status_id` int(11) DEFAULT NULL COMMENT '返回的status_code里pid是39的状态',
`result_reason_id` int(11) DEFAULT NULL COMMENT '返回的status_code里pid是30的理由',
`created_at` int(11) DEFAULT NULL COMMENT '创建时间',
KEY `rbid` (`rbid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
blacklist_sql_3="""CREATE TABLE `recall_blacklist_reason` (
`rbid` int(11) NOT NULL COMMENT 'recall_blacklist的id',
`reason_id` int(11) DEFAULT NULL COMMENT 'status_code里pid是30的状态',
`reason_owner` varchar(255) DEFAULT NULL COMMENT '修改人',
`reason_comment` varchar(255) DEFAULT NULL COMMENT '修改的备注',
`final_status_id` int(11) DEFAULT NULL COMMENT '最后写入recall_blacklist的status_code里pid是39的状态',
`created_at` varchar(255) DEFAULT NULL COMMENT '创建的时间',
KEY `rbid` (`rbid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"""
do_tidb_exe(blacklist_sql_1)
print('黑名单表添加完毕')
do_tidb_exe(blacklist_sql_2)
print('黑名单查询历史表添加完毕')
do_tidb_exe(blacklist_sql_3)
print('黑名单修改历史表添加完毕')
project_list,project_count = select_all_project()
for project in project_list:
sql1="""ALTER TABLE `events`.`{project_name}_noti`
ADD COLUMN `key` varchar(255) NULL COMMENT '消息接受方式key' AFTER `type`;
""".format(project_name=project[0])
do_tidb_exe(sql1)
sql2="""ALTER TABLE `events`.`{project_name}_noti`
ADD INDEX `key`(`key`) USING BTREE;""".format(project_name=project[0])
do_tidb_exe(sql2)
sql3="""ALTER TABLE `events`.`{project_name}_noti`
ADD COLUMN `level` int(4) NULL COMMENT '消息级别' AFTER `owner`;""".format(project_name=project[0])
do_tidb_exe(sql3)
print(project[0],'表升级完成')
sql4="""ALTER TABLE `events`.`{project}_properties`
ADD COLUMN `access_control_threshold` int(10) NULL AFTER `total_count`;""".format(project=project[0])
do_tidb_exe(sql=sql4)
print('properities加字段完成')
def disable_usergroup_data_db(project,data_id):
sql = f"""update {project}_usergroup_data set `enable`=10 where `id`={data_id}"""
result = do_tidb_exe(sql=sql)
return result
def disable_noti_db(project,noti_id):
sql=f"""update {project}_noti set `status`=10 where id={noti_id} and `status` != 26 """
result = do_tidb_exe(sql=sql)
return result