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