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