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 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 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_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 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_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_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_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_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")
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")
