def create_table(cls, database):
schema = Schema(database)
schema.drop_if_exists("users")
with schema.create("users") as table:
table.increments("id").unsigned()
table.string("username", 32).unique()
table.string("full_name", 64).nullable()
table.string("email", 128).unique()
table.boolean("email_verified").default(False)
table.boolean("subscribed").default(False)
table.boolean("email_public").default(False)
table.string("password", 128)
table.integer("reported").default(0)
table.boolean("spammer").default(False)
table.datetime("created_at")
table.datetime("updated_at")
table.string("preferred_sort", 10).default("trending")
table.text("bio").nullable()
table.text("url").nullable()
table.integer("feed_subs").default(0)
# preferences
table.string("p_show_images", 1).default("y")
table.integer("p_min_link_score").default(-3)
# indexes
table.index("username")
table.index("email")
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 create_table(cls):
schema = Schema(db)
schema.drop_if_exists(cls.__table__)
with schema.create(cls.__table__) as table:
table.raw("inet")
table.integer("user_id").unsigned()
table.foreign("user_id").references("id").on("users").on_delete(
"cascade")
table.datetime("created_at")
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_table(cls):
schema = Schema(db)
schema.drop_if_exists("saved_links")
with schema.create("saved_links") as table:
table.big_integer("link_id").unsigned()
table.integer("user_id").unsigned()
table.datetime("created_at")
table.datetime("updated_at")
table.index("link_id")
table.index("user_id")
table.primary(["link_id", "user_id"])
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_table(cls):
schema = Schema(db)
schema.drop_if_exists("comment_votes")
with schema.create("comment_votes") as table:
table.integer("user_id").unsigned()
table.foreign("user_id").references("id").on("users").on_delete("cascade")
table.big_integer("comment_id").unsigned()
table.foreign("comment_id").references("id").on("comments").on_delete(
"cascade"
)
table.integer("vote_type")
table.primary(["user_id", "comment_id"])
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 create_table(cls):
schema = Schema(db)
schema.drop_if_exists(cls.__table__)
with schema.create(cls.__table__) as table:
table.boolean("god").default(False)
table.integer("user_id").unsigned()
table.foreign("user_id").references("id").on("users").on_delete("cascade")
table.integer("feed_id").unsigned()
table.foreign("feed_id").references("id").on("feeds").on_delete("cascade")
table.datetime("created_at")
table.datetime("updated_at")
table.primary(["user_id", "feed_id"])
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_table(cls):
schema = Schema(db)
schema.drop_if_exists("actions")
with schema.create("actions") as table:
table.increments("id").unsigned()
table.string("action", 16)
table.text("data")
table.integer("feed_id").unsigned()
table.integer("user_id").unsigned()
table.datetime("created_at")
table.datetime("updated_at")
table.index("user_id")
table.index("feed_id")
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 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_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 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_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_subscriptions_table():
"""
Create table for feed subscriptions
"""
schema = Schema(db)
schema.drop_if_exists("feeds_users")
with schema.create("feeds_users") as table:
table.integer("feed_id").unsigned()
table.foreign("feed_id").references("id").on("feeds").ondelete(
"cascade")
table.integer("user_id").unsigned()
table.foreign("user_id").references("id").on("users").ondelete(
"cascade")
table.index("feed_id")
table.index("user_id")
table.primary(["user_id", "feed_id"])
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_table(cls):
"""
Create table for bans
"""
schema = Schema(db)
schema.drop_if_exists("bans")
with schema.create("bans") as table:
table.string("reason")
table.integer("user_id").unsigned()
table.foreign("user_id").references("id").on("users").on_delete(
"cascade")
table.integer("feed_id").unsigned()
table.foreign("feed_id").references("id").on("feeds").on_delete(
"cascade")
table.datetime("created_at")
table.datetime("updated_at")
table.datetime("until")
table.primary(["feed_id", "user_id"])
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_table(cls):
schema = Schema(db)
schema.drop_if_exists(cls.__table__)
with schema.create(cls.__table__) as table:
table.increments("id").unsigned()
table.string("name", 64)
table.string("slug", 80).unique()
table.text("description").nullable()
table.text("rules").nullable()
table.text("img").nullable()
table.integer("subscribers_count").default(0)
table.string("default_sort", 12).default("trending")
table.datetime("created_at")
table.datetime("updated_at")
table.string("lang", 12).default("en")
table.boolean("over_18").default(False)
table.string("logo", 128).nullable()
table.boolean("reported").default(False)
table.index("slug")
def create_table(cls):
"""
Create table for reports
"""
schema = Schema(db)
schema.drop_if_exists("reports")
with schema.create("reports") as table:
table.increments("id").unsigned()
table.string("reason", 16)
table.text("comment")
table.integer("feed_id").unsigned()
table.foreign("feed_id").references("id").on("feeds").ondelete(
"cascade")
table.integer("user_id").unsigned()
table.foreign("user_id").references("id").on("users").ondelete(
"cascade")
table.integer("reportable_id").unsigned()
table.text("reportable_type")
table.datetime("created_at")
table.datetime("updated_at")
table.index("user_id")
table.index("feed_id")
def main():
"""
usersテーブルを作成する
| カラム | データ型 |
| ------ | -------- |
| id | VARCHAR |
| name | VARCHAR |
| age | INTEGER |
"""
db = DatabaseManager(DB_CONFIG)
schema = Schema(db)
TABLE_NAME = 'users'
schema.drop_if_exists(TABLE_NAME)
with schema.create(TABLE_NAME) as table:
table: Blueprint
table.string('id').unique()
table.string('name')
table.integer('age')
def create_table(cls):
schema = Schema(db)
schema.drop_if_exists("links")
with schema.create("links") as table:
table.big_increments("id").unsigned()
table.string("title", 128)
table.string("slug", 150)
table.text("text").nullable()
table.text("image").nullable()
table.text("url")
table.integer("user_id").unsigned()
table.datetime("created_at")
table.datetime("updated_at")
table.foreign("user_id").references("id").on("users")
table.integer("feed_id").unsigned()
table.foreign("feed_id").references("id").on("feeds").ondelete("cascade")
table.integer("ups").default(0)
table.integer("downs").default(0)
table.integer("comments_count").default(0)
table.boolean("archived").default(False)
table.integer("reported").default(0)
table.boolean("spam").default(False)
def create_table(cls):
"""
Creates database table for comments
"""
schema = Schema(db)
schema.drop_if_exists("comments")
with schema.create("comments") as table:
table.big_increments("id").unsigned()
table.big_integer("parent_id").unsigned().nullable()
table.foreign("parent_id").references("id").on("comments").on_delete(
"cascade"
)
table.text("text")
table.integer("user_id").unsigned()
table.foreign("user_id").references("id").on("users")
table.integer("link_id").unsigned()
table.foreign("link_id").references("id").on("links").on_delete("cascade")
table.integer("reported").default(0)
table.boolean("spam").default(False)
table.integer("ups").default(0)
table.integer("downs").default(0)
table.datetime("created_at")
table.datetime("updated_at")
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 create_table(cls):
schema = Schema(db)
schema.drop_if_exists("tokens")
with schema.create("tokens") as table:
table.string("id", 40)
table.primary("id")
