def migrate(schema: Schema):
if schema.has_table('points'):
return
with schema.create('points') as table:
table.big_increments('id')
table.string('agency')
table.string('account').nullable()
table.string('account_id').nullable()
table.big_integer('campaign_id').nullable()
table.big_integer('ad_group_id').nullable()
table.big_integer('creative_id').nullable()
table.big_integer('aid').nullable()
table.string('cname')
table.big_integer('total_cost')
table.big_integer('view_count')
table.big_integer('click_count')
table.small_integer('status').default(0)
table.decimal('sy_cost', 12, 2).default(0.00)
# 二跳
table.big_integer('1day_action_step').default(0)
# 预约量
table.big_integer('1day_action_reversion').default(0)
# 激活
table.big_integer('1day_action_activate_app').default(0)
# 下单
table.big_integer('1day_action_complete_order').default(0)
# 下单金额
table.big_integer('1day_action_complete_order_amount').default(0)
table.timestamp('update_time')
table.timestamps()
def migrate(schema: Schema):
if schema.has_table('users'):
return
with schema.create('users') as table:
table.big_increments('id')
table.string('name')
table.string('username')
table.string('password')
table.timestamps()
def create_Group(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("Group"):
with schema.create("Group") as table:
table.increments("GroupId")
table.string("MD5").unique()
table.datetime("Created")
def create_User(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("User"):
with schema.create("User") as table:
table.increments("UserId")
table.string("Name", 50).unique()
table.string("Description").nullable()
table.datetime("Created")
def create_Iota(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("Iota"):
with schema.create("Iota") as table:
table.big_increments("IotaId")
table.string("Key")
table.binary("Value")
table.datetime("Created")
table.unique(["Key", "Value"])
def migrate(schema: Schema):
if schema.has_table('accounts'):
return
with schema.create('accounts') as table:
table.big_increments('id')
table.string('name')
table.string('agency_id').nullable()
table.string('username').nullable()
table.string('password').nullable()
table.string('phone').nullable()
table.string('access_key').nullable()
table.string('secret_key').nullable()
table.timestamps()
def create_Algorithm(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("Algorithm"):
with schema.create("Algorithm") as table:
table.increments("AlgorithmId")
table.string("Name")
table.string("Description").nullable()
table.string("Version")
table.datetime("Created")
table.unique(["Name", "Version"])
def migrate(schema: Schema):
if schema.has_table('agencies'):
return
with schema.create('agencies') as table:
table.big_increments('id')
table.string('name')
table.string('display_name')
table.timestamps()
table.unique('name')
schema.db.table('agencies').insert([{
'name': 'wxext',
'display_name': '微信插件'
}])
def create_Annotation(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("Annotation"):
with schema.create("Annotation") as table:
table.increments("AnnotationId")
table.integer("UserId").unsigned()
table.string("Value")
table.datetime("Created")
table.foreign("UserId") \
.references("UserId") \
.on("User")
def create_Dataset(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("Dataset"):
with schema.create("Dataset") as table:
table.increments("DatasetId")
table.string("Name").unique()
table.text("Description").nullable()
table.string("Introspector")
table.string("MD5").unique()
table.string("SHA256").unique()
table.datetime("Created")
def create_File(self, schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("File"):
with schema.create("File") as table:
table.string("FileId")
table.string("OriginalFilepath")
table.string("FileType").nullable()
table.string("ReadPath")
table.string("MD5").unique()
table.string("SHA256").unique()
table.string("Metadata").nullable()
table.datetime("Created")
def create_RunOutput(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("RunOutput"):
with schema.create("RunOutput") as table:
table.increments("RunOutputId")
table.integer("RunId").unsigned()
table.integer("DatasetId").unsigned()
table.datetime("Created")
table.foreign("RunId") \
.references("RunId") \
.on("Run")
table.foreign("DatasetId") \
.references("DatasetId") \
.on("Dataset")
def create_IotaGroup(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("IotaGroup"):
with schema.create("IotaGroup") as table:
table.increments("IotaGroupId")
table.integer("IotaId").unsigned()
table.integer("GroupId").unsigned()
table.datetime("Created")
table.unique(["IotaId", "GroupId"])
table.foreign("IotaId") \
.references("IotaId") \
.on("Iota")
table.foreign("GroupId") \
.references("GroupId") \
.on("Group")
def create_GroupDataset(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("GroupDataset"):
with schema.create("GroupDataset") as table:
table.increments("GroupDatasetId")
table.integer("GroupId").unsigned()
table.integer("DatasetId").unsigned()
table.string("Label")
table.datetime("Created")
table.unique(["GroupId", "DatasetId", "Label"])
table.foreign("GroupId") \
.references("GroupId") \
.on("Group")
table.foreign("DatasetId") \
.references("DatasetId") \
.on("Dataset")
def create_Run(schema: orator.Schema):
# enforce types
checks.check_types(schema, orator.Schema)
# create table
if not schema.has_table("Run"):
with schema.create("Run") as table:
table.increments("RunId")
table.integer("AlgorithmId").unsigned()
table.integer("UserId").unsigned()
table.string("Name").nullable()
table.string("Description").nullable()
table.integer("AlgorithmParameters").unsigned()
table.datetime("Begin")
table.datetime("End")
table.foreign("AlgorithmId") \
.references("AlgorithmId") \
.on("Algorithm")
table.foreign("UserId") \
.references("UserId") \
.on("User")
table.foreign("AlgorithmParameters") \
.references("DatasetId") \
.on("Dataset")
class KBCDatabaseOperatorClass(KBCMySQLConfigureClass):
'''
TODO:
Creating MySQL table
'''
def __init__(self, database):
# initializing the configuration
super(self.__class__, self).__init__()
self.config['mysql']['database'] = database
db = DatabaseManager(self.config)
# define the orator schema builder
self.schema = Schema(db)
self.db = db
self.database = database
def create_table(self,
table_name,
df_data,
string_size=100,
is_sequence=False):
if len(df_data) < 1:
return False
columns_list = df_data.columns.values
self.schema.drop_if_exists(table_name)
with self.schema.create(table_name) as table:
# Creating new table based on the number of column and type of column.
for column_name in columns_list:
cell_value = df_data[column_name][0]
if is_sequence is True and column_name == 'Sequence':
table.text(column_name)
elif isinstance(cell_value, int):
table.integer(column_name)
elif isinstance(cell_value, float):
table.double(column_name, 15, 8)
elif isinstance(cell_value, str):
table.string(column_name, string_size)
def update_table_condition_two(self, table_name, condition_name1, value1,
condition_name2, value2, dict_data):
self.db.table(table_name).where(condition_name1,
value1).where(condition_name2,
value2).update(dict_data)
def insert_table(self, table_name, df_data={}, dict_data={}):
if len(df_data) < 1 and len(dict_data) < 1:
return False
if len(df_data) > 0:
# Changing pandas data frame to dictionary type
df_data_dict = df_data.to_dict(orient='records')
else:
df_data_dict = dict_data
if self.schema.has_table(table_name):
print(table_name)
self.db.table(table_name).insert(df_data_dict)
else:
return False
def naming_table(self, organism, data_type, version=''):
if version == '':
return organism + '_' + data_type
else:
return organism + '_' + data_type + '_' + version
def pipeline(self,
job_dict,
organism,
version='',
string_size=100,
no_version_table_list=[],
sequence_table_list=[]):
for data_type in job_dict:
df_data = job_dict[data_type]
if data_type not in no_version_table_list:
table_name = self.naming_table(organism, data_type, version)
else:
table_name = self.naming_table(organism, data_type)
if data_type in sequence_table_list:
self.create_table(table_name, df_data, is_sequence=True)
else:
self.create_table(table_name, df_data, string_size)
# Inerting data into database
self.insert_table(table_name, df_data=df_data)
def test(self):
print("DB:" + self.db)
print("test")
def migrate(schema: Schema):
if not schema.has_table('trim_strategies'):
with schema.create('trim_strategies') as table:
table.big_increments('id')
table.big_integer('account_id')
table.string('name')
table.string('worktime')
table.small_integer('is_repeat') # worktime内是否重复生效
table.timestamps()
'''
对当前Point进行过滤
'''
if not schema.has_table('trim_filters'):
with schema.create('trim_filters') as table:
table.big_increments('id')
table.big_integer('strategy_id')
table.string('type').default('string') # 类型 string/int/double/
table.string('key')
table.string('op')
table.string('value')
table.text('extra')
table.timestamps()
'''
需要历史数据介入进行过滤
'''
if not schema.has_table('trim_history_filters'):
with schema.create('trim_history_filters') as table:
table.big_increments('id')
table.big_integer('strategy_id')
table.string('type').default('string') # 类型 string/int/double/
table.small_integer('day') # 参与过滤的天数数据
table.string(
'aggregate').nullable() # 聚合函数 支持sum／avg／latest／earliest
table.string('key')
table.string('op')
table.string('value')
table.text('extra')
table.timestamps()
'''
触发相应条件时需要执行的对应修改操作
'''
if not schema.has_table('trims'):
with schema.create('trims') as table:
table.big_increments('id')
table.big_integer('strategy_id')
table.string('type').default('string') # 类型
table.string('level').default(
'campaign') # 操作级别 campaign/adgroup/ad
table.string('key') # key
table.string('action')
table.string('value')
table.text('extra1').nullable()
table.text('extra2').nullable()
table.text('extra3').nullable()
table.timestamps()
'''
策略对应的修改动作，以及执行结果的反馈
'''
if not schema.has_table('trim_actions'):
with schema.create('trim_actions') as table:
table.big_increments('id')
table.big_integer('account_id')
table.big_integer('campaign_id')
table.text('action') # 调整动作
table.text('value').nullable() # 调整值
table.text('extra').nullable() # 额外参数
table.text('triggered_point').nullable() # 触发该action的记录
table.text('resp_cnt').nullable() # 执行结果内容
table.string('resp_status_code').nullable() # 执行结果状态
table.timestamps()
def League(self):
return League
#open config
config = open("config.json", "r")
credentials = json.load(config)
config.close()
#connect to database, put connection into models/schema for creation. Settings in config.json
db = DatabaseManager(credentials['database'])
Model.set_connection_resolver(db)
schema = Schema(db)
#Create Database is not exists
if not schema.has_table('leagues'):
print("Creating Leagues Table")
with schema.create('leagues') as table:
table.timestamps()
table.soft_deletes()
table.increments('id')
table.string('leagueName')
table.string('channelId')
table.json('roles').default('[]')
settings = {}
settings['rounds'] = 3
settings['roundTiming'] = {}
settings['roundTiming']['1'] = 5
settings['roundTiming']['2'] = 3
settings['roundTiming']['3'] = 2
table.json('settings').default(json.dumps(settings))