def autoinc_handler(sa_table, sa_table_name, id_column_name, id_seq_name, insert_dict): # Specify that all insert operations (whether through SQLAlchemy or # externally launched) must use the sequence for DBs that support # sequences sa.event.listen( sa_table, 'after_create', sa.DDL( "ALTER TABLE %s ALTER COLUMN %s SET DEFAULT nextval('%s');" % (sa_table_name, id_column_name, id_seq_name)).execute_if( dialect=('postgresql', 'mysql'))) # SQLite does not support sequences - insert a dummy row with id=-1 so # that the next insert operation will use id=0 keys_list = [] values_list = [] for key, val in insert_dict.iteritems(): keys_list.append(key) # if None, generate random string values_list.append(val if val else ("'%s'" % (str(uuid.uuid4())))) sa.event.listen( sa_table, 'after_create', sa.DDL(("INSERT INTO %s ( " + ",".join([str(x) for x in keys_list]) + " ) VALUES ( " + ",".join([str(x) for x in values_list]) + " );") % (sa_table_name)).execute_if(dialect='sqlite'))
def define_tables(cls, metadata): from sqlalchemy.testing import config dblink = config.file_config.get('sqla_testing', 'postgres_test_db_link') testtable = Table('testtable', metadata, Column('id', Integer, primary_key=True), Column('data', String(30))) for ddl in [ "CREATE SERVER test_server FOREIGN DATA WRAPPER postgres_fdw " "OPTIONS (dbname 'test', host '%s')" % dblink, "CREATE USER MAPPING FOR public \ SERVER test_server options (user 'scott', password 'tiger')", "CREATE FOREIGN TABLE test_foreigntable ( " " id INT, " " data VARCHAR(30) " ") SERVER test_server OPTIONS (table_name 'testtable')", ]: sa.event.listen(metadata, "after_create", sa.DDL(ddl)) for ddl in [ 'DROP FOREIGN TABLE test_foreigntable', 'DROP USER MAPPING FOR public SERVER test_server', "DROP SERVER test_server" ]: sa.event.listen(metadata, "before_drop", sa.DDL(ddl))
def downgrade(): op.add_column( u'user', sa.Column('username', sa.VARCHAR(length=80), autoincrement=False, nullable=True)) op.create_unique_constraint(u'user_username_key', 'user', ['username']) op.add_column( u'organization', sa.Column('name', sa.VARCHAR(length=80), autoincrement=False, nullable=True)) op.create_unique_constraint(u'organization_name_key', 'organization', ['name']) op.drop_index(op.f('ix_name_name_lower'), table_name='name') op.execute( sa.DDL( '''CREATE INDEX ix_user_username_lower ON "user" (lower(username) varchar_pattern_ops);''' )) op.execute( sa.DDL(''' UPDATE "user" SET (username) = (SELECT name FROM "name" WHERE "name".user_id = "user".id) ''')) op.execute( sa.DDL(''' UPDATE "organization" SET (name) = (SELECT name FROM "name" WHERE "name".org_id = "organization".id) ''')) op.drop_index(op.f('ix_name_reserved'), table_name='name') op.drop_table('name')
def downgrade(): op.execute( sa.DDL( dedent(''' DROP TRIGGER update_search_vector_trigger ON update; DROP FUNCTION update_search_vector_update(); '''))) op.execute( sa.DDL( dedent(''' CREATE FUNCTION post_search_vector_update() RETURNS trigger AS $$ BEGIN NEW.search_vector := setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') || setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') || setweight(to_tsvector('english', COALESCE(NEW.body_text, '')), 'B'); RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER post_search_vector_trigger BEFORE INSERT OR UPDATE ON update FOR EACH ROW EXECUTE PROCEDURE post_search_vector_update(); '''))) for old, new in renamed_constraints: op.execute( sa.DDL(f'ALTER TABLE update RENAME CONSTRAINT "{new}" TO "{old}"')) op.alter_column('update', 'project_id', existing_type=sa.INTEGER(), nullable=True)
def downgrade(): # 1. Add primary columns op.add_column('userphone', sa.Column('primary', sa.BOOLEAN(), autoincrement=False, nullable=False, server_default=expression.false())) op.add_column('useremail', sa.Column('primary', sa.BOOLEAN(), autoincrement=False, nullable=False, server_default=expression.false())) op.alter_column('userphone', 'primary', server_default=None) op.alter_column('useremail', 'primary', server_default=None) # 2. Update primary flags op.execute(sa.DDL(''' UPDATE useremail SET "primary" = true FROM user_useremail_primary WHERE useremail.id = user_useremail_primary.useremail_id; ''')) op.execute(sa.DDL(''' UPDATE userphone SET "primary" = true FROM user_userphone_primary WHERE userphone.id = user_userphone_primary.userphone_id; ''')) # 3. Drop primary tables op.execute(sa.DDL(''' DROP TRIGGER user_userphone_primary_trigger ON user_userphone_primary; DROP FUNCTION user_userphone_primary_validate(); ''')) op.execute(sa.DDL(''' DROP TRIGGER user_useremail_primary_trigger ON user_useremail_primary; DROP FUNCTION user_useremail_primary_validate(); ''')) op.drop_table('user_userphone_primary') op.drop_table('user_useremail_primary')
def downgrade(): # rename columns op.alter_column("comp_pipeline", "state", new_column_name="deprecated_state") op.alter_column("comp_tasks", "state", new_column_name="deprecated_state") # create the old columns again op.add_column("comp_pipeline", sa.Column("state", sa.String(), nullable=True)) op.add_column("comp_tasks", sa.Column("state", sa.Integer(), nullable=True)) # migrate the columns migration_map = { "NOT_STARTED": UNKNOWN, "PUBLISHED": UNKNOWN, "PENDING": PENDING, "RUNNING": RUNNING, "SUCCESS": SUCCESS, "FAILED": FAILED, } [ op.execute( sa.DDL(f""" UPDATE comp_pipeline SET state='{new}' WHERE comp_pipeline.deprecated_state = '{old}' """)) for old, new in migration_map.items() ] [ op.execute( sa.DDL(f""" UPDATE comp_tasks SET state='{new}' WHERE comp_tasks.deprecated_state = '{old}' """)) for old, new in migration_map.items() ] # replace trigger to remove dependency on old state replace_trigger_query = sa.DDL(f""" DROP TRIGGER IF EXISTS {DB_TRIGGER_NAME} on comp_tasks; CREATE TRIGGER {DB_TRIGGER_NAME} AFTER UPDATE OF outputs ON comp_tasks FOR EACH ROW WHEN (OLD.outputs::jsonb IS DISTINCT FROM NEW.outputs::jsonb AND NEW.node_class <> 'FRONTEND') EXECUTE PROCEDURE {DB_PROCEDURE_NAME}(); """) op.execute(replace_trigger_query) # drop the columns op.drop_column("comp_tasks", "deprecated_state") op.drop_column("comp_pipeline", "deprecated_state") state_type = postgresql.ENUM( "NOT_STARTED", "PUBLISHED", "PENDING", "RUNNING", "SUCCESS", "FAILED", name="statetype", ) state_type.drop(op.get_bind())
def define_tables(cls, metadata): testtable = Table( 'testtable', metadata, Column('id', Integer, primary_key=True), Column('data', String(30))) # insert data before we create the view @sa.event.listens_for(testtable, "after_create") def insert_data(target, connection, **kw): connection.execute( target.insert(), {"id": 89, "data": 'd1'} ) materialized_view = sa.DDL( "CREATE MATERIALIZED VIEW test_mview AS " "SELECT * FROM testtable") plain_view = sa.DDL( "CREATE VIEW test_regview AS " "SELECT * FROM testtable") sa.event.listen(testtable, 'after_create', plain_view) sa.event.listen(testtable, 'after_create', materialized_view) sa.event.listen( testtable, 'before_drop', sa.DDL("DROP MATERIALIZED VIEW test_mview") ) sa.event.listen( testtable, 'before_drop', sa.DDL("DROP VIEW test_regview") )
def downgrade(): op.execute( sa.DDL('ALTER TABLE team_membership ALTER COLUMN created_at ' 'TYPE TIMESTAMP WITHOUT TIME ZONE')) op.add_column('team_membership', sa.Column('updated_at', sa.DateTime(), nullable=True)) op.execute(sa.DDL('UPDATE team_membership SET updated_at = created_at')) op.alter_column('team_membership', 'updated_at', nullable=False)
def downgrade(): op.execute( sa.DDL( ''' DROP TRIGGER user_useremail_primary_trigger ON user_useremail_primary; DROP FUNCTION user_useremail_primary_validate(); ''' ) ) op.execute( sa.DDL( ''' DROP TRIGGER user_userphone_primary_trigger ON user_userphone_primary; DROP FUNCTION user_userphone_primary_validate(); ''' ) ) op.execute( sa.DDL( ''' CREATE FUNCTION user_useremail_primary_validate() RETURNS TRIGGER AS $$ DECLARE target RECORD; BEGIN SELECT user_id INTO target FROM useremail WHERE id = NEW.useremail_id; IF (target.user_id != NEW.user_id) THEN RAISE foreign_key_violation USING MESSAGE = 'The target is not affiliated with this parent'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER user_useremail_primary_trigger BEFORE INSERT OR UPDATE ON user_useremail_primary FOR EACH ROW EXECUTE PROCEDURE user_useremail_primary_validate(); ''' ) ) op.execute( sa.DDL( ''' CREATE FUNCTION user_userphone_primary_validate() RETURNS TRIGGER AS $$ DECLARE target RECORD; BEGIN SELECT user_id INTO target FROM userphone WHERE id = NEW.userphone_id; IF (target.user_id != NEW.user_id) THEN RAISE foreign_key_violation USING MESSAGE = 'The target is not affiliated with this parent'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER user_userphone_primary_trigger BEFORE INSERT OR UPDATE ON user_userphone_primary FOR EACH ROW EXECUTE PROCEDURE user_userphone_primary_validate(); ''' ) )
def reset_schema(engine, schema_name): """Execute drop and declaration for schema space.""" schema = pg.Schema(schema_name) drop_statement = sa.DDL(schema.drop_statement()) create_statement = sa.DDL(schema.create_statement()) with engine.begin() as conn: conn.execute(drop_statement) conn.execute(create_statement)
def test_inspect_enums_case_sensitive_from_table(self): sa.event.listen( self.metadata, "before_create", sa.DDL('create schema "TestSchema"'), ) sa.event.listen( self.metadata, "after_drop", sa.DDL('drop schema "TestSchema" cascade'), ) counter = itertools.count() for enum in "lower_case", "UpperCase", "Name.With.Dot": for schema in None, "test_schema", "TestSchema": enum_type = postgresql.ENUM( "CapsOne", "CapsTwo", name=enum, metadata=self.metadata, schema=schema, ) Table( "t%d" % next(counter), self.metadata, Column("q", enum_type), ) self.metadata.create_all(testing.db) inspector = inspect(testing.db) counter = itertools.count() for enum in "lower_case", "UpperCase", "Name.With.Dot": for schema in None, "test_schema", "TestSchema": cols = inspector.get_columns("t%d" % next(counter)) cols[0]["type"] = ( cols[0]["type"].schema, cols[0]["type"].name, cols[0]["type"].enums, ) eq_( cols, [ { "name": "q", "type": (schema, enum, ["CapsOne", "CapsTwo"]), "nullable": True, "default": None, "autoincrement": False, "comment": None, } ], )
def rebuild_views(): # This import is kinda kludgy (and would be circular outside of this function) but our model engine is tied up with # the Flask framework (for now) from bafs import db sess = db.session # @UndefinedVariable sess.execute(_v_daily_scores_create) sess.execute(sa.DDL("drop view if exists _build_ride_daylight;")) sess.execute(_v_buid_ride_daylight) sess.execute(sa.DDL("drop view if exists ride_daylight;")) sess.execute(_v_ride_daylight) sess.execute(sa.DDL("drop view if exists lbd_athletes;")) sess.execute(_v_leaderboard_athletes)
def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.drop_constraint( "user_to_projects_user_id_fkey", "user_to_projects", type_="foreignkey" ) op.drop_constraint( "user_to_projects_project_id_fkey", "user_to_projects", type_="foreignkey" ) op.create_foreign_key( "fk_user_to_projects_id_projects", "user_to_projects", "projects", ["project_id"], ["id"], onupdate="CASCADE", ondelete="CASCADE", ) op.create_foreign_key( "fk_user_to_projects_id_users", "user_to_projects", "users", ["user_id"], ["id"], onupdate="CASCADE", ondelete="CASCADE", ) # change contents in projects access_rights # NOTE: this does not need to be reversed as it was not used before op.execute( sa.DDL( "UPDATE projects SET access_rights = (regexp_replace(access_rights::text, '\"rwx\"', '{\"read\":true, \"write\":false, \"delete\":false}')::jsonb) WHERE access_rights != '{}'" ) ) # add prj_owner into access rights column # NOTE: this dows not need to be reversed op.execute( sa.DDL( """ WITH user_project as ( SELECT projects.id AS pid, projects.access_rights AS current_rights, '{"' || users.primary_gid || '"}' AS json_key FROM projects INNER JOIN users ON (projects.prj_owner = users.id) ) UPDATE projects SET access_rights = jsonb_insert(current_rights::jsonb,json_key::text[], '{"read":true, "write":true, "delete":true}'::jsonb, true) FROM user_project WHERE projects.id = pid """ ) )
def upgrade(): op.drop_index('ix_post_profile_id', 'post') op.drop_constraint('post_owner_check', 'post', type_='check') op.drop_column('post', 'profile_id') op.rename_table('post', 'update') op.execute(sa.DDL('ALTER SEQUENCE post_id_seq RENAME TO update_id_seq')) for old, new in renamed_constraints: op.execute( sa.DDL(f'ALTER TABLE update RENAME CONSTRAINT "{old}" TO "{new}"')) for old, new in renamed_indexes: op.execute(sa.DDL(f'ALTER INDEX "{old}" RENAME TO "{new}"'))
def upgrade(): op.create_table( 'name', sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False), sa.Column('name', sa.Unicode(length=63), nullable=False), sa.Column('user_id', sa.Integer(), nullable=True), sa.Column('org_id', sa.Integer(), nullable=True), sa.Column('reserved', sa.Boolean(), nullable=False), sa.Column('id', UUIDType(binary=False), nullable=False), sa.CheckConstraint( 'CASE WHEN (user_id IS NOT NULL) THEN 1 ELSE 0 END + CASE WHEN (org_id IS NOT NULL) THEN 1 ELSE 0 END + CASE WHEN (reserved = true) THEN 1 ELSE 0 END = 1', name='username_owner_check', ), sa.ForeignKeyConstraint(['org_id'], ['organization.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'), sa.UniqueConstraint('org_id'), sa.UniqueConstraint('user_id'), ) op.create_index(op.f('ix_name_reserved'), 'name', ['reserved'], unique=False) op.execute( sa.DDL(''' INSERT INTO "name" (reserved, id, created_at, updated_at, name, user_id) SELECT False, uuid, created_at, updated_at, username, id FROM "user" WHERE username IS NOT null ORDER BY created_at; ''')) op.execute( sa.DDL(''' INSERT INTO "name" (reserved, id, created_at, updated_at, name, org_id) SELECT False, uuid, created_at, updated_at, name, id FROM "organization" WHERE name IS NOT null ORDER BY created_at; ''')) op.drop_index(op.f('ix_user_username_lower'), table_name='user') op.execute( sa.DDL( '''CREATE UNIQUE INDEX ix_name_name_lower ON "name" (lower(name) varchar_pattern_ops);''' )) op.drop_constraint('organization_name_key', 'organization', type_='unique') op.drop_column('organization', 'name') op.drop_constraint('user_username_key', 'user', type_='unique') op.drop_column('user', 'username')
def test_inspect_enums_case_sensitive(self): sa.event.listen( self.metadata, "before_create", sa.DDL('create schema "TestSchema"'), ) sa.event.listen( self.metadata, "after_drop", sa.DDL('drop schema "TestSchema" cascade'), ) for enum in "lower_case", "UpperCase", "Name.With.Dot": for schema in None, "test_schema", "TestSchema": postgresql.ENUM( "CapsOne", "CapsTwo", name=enum, schema=schema, metadata=self.metadata, ) self.metadata.create_all(testing.db) inspector = inspect(testing.db) for schema in None, "test_schema", "TestSchema": eq_( sorted( inspector.get_enums(schema=schema), key=itemgetter("name") ), [ { "visible": schema is None, "labels": ["CapsOne", "CapsTwo"], "name": "Name.With.Dot", "schema": "public" if schema is None else schema, }, { "visible": schema is None, "labels": ["CapsOne", "CapsTwo"], "name": "UpperCase", "schema": "public" if schema is None else schema, }, { "visible": schema is None, "labels": ["CapsOne", "CapsTwo"], "name": "lower_case", "schema": "public" if schema is None else schema, }, ], )
def downgrade(): # set the ABORTED value to NOT_STARTED and rename the statetype op.execute( sa.DDL( """ UPDATE comp_tasks SET state = 'NOT_STARTED' WHERE state = 'ABORTED'; UPDATE comp_pipeline SET state = 'NOT_STARTED' WHERE state = 'ABORTED'; ALTER TYPE statetype RENAME TO statetype_old; """ ) ) # create the statetype state_type = postgresql.ENUM( "NOT_STARTED", "PUBLISHED", "PENDING", "RUNNING", "SUCCESS", "FAILED", name="statetype", ) state_type.create(op.get_bind()) # update all the columns, trigger depending on it op.execute( sa.DDL( f""" DROP TRIGGER IF EXISTS {DB_TRIGGER_NAME} on comp_tasks; ALTER TABLE comp_tasks ALTER COLUMN state DROP DEFAULT; ALTER TABLE comp_tasks ALTER COLUMN state TYPE statetype USING state::text::statetype; ALTER TABLE comp_tasks ALTER COLUMN state SET DEFAULT 'NOT_STARTED'; ALTER TABLE comp_pipeline ALTER COLUMN state DROP DEFAULT; ALTER TABLE comp_pipeline ALTER COLUMN state TYPE statetype USING state::text::statetype; ALTER TABLE comp_pipeline ALTER COLUMN state SET DEFAULT 'NOT_STARTED'; DROP TYPE statetype_old; """ ) ) op.execute( sa.DDL( f""" DROP TRIGGER IF EXISTS {DB_TRIGGER_NAME} on comp_tasks; CREATE TRIGGER {DB_TRIGGER_NAME} AFTER UPDATE OF outputs,state ON comp_tasks FOR EACH ROW WHEN ((OLD.outputs::jsonb IS DISTINCT FROM NEW.outputs::jsonb OR OLD.state IS DISTINCT FROM NEW.state) AND NEW.node_class <> 'FRONTEND') EXECUTE PROCEDURE {DB_PROCEDURE_NAME}(); """ ) )
def downgrade(): # ### commands auto generated by Alembic - please adjust! ### op.execute( sa.DDL("DROP TRIGGER IF EXISTS cluster_modification on clusters;")) op.execute(sa.DDL("DROP FUNCTION set_cluster_to_owner_group()")) op.alter_column("cluster_to_groups", "read", new_column_name="read_access") op.alter_column("cluster_to_groups", "write", new_column_name="write_access") op.alter_column("cluster_to_groups", "delete", new_column_name="delete_access") # ### end Alembic commands ### op.execute(assign_cluster_access_rights_to_owner_group_procedure_old) op.execute(new_cluster_trigger)
def create_triggers(self, schema: str, tables: Optional[List[str]] = None) -> None: """Create a database triggers.""" self.execute( CREATE_TRIGGER_TEMPLATE.replace(MATERIALIZED_VIEW, f"{schema}.{MATERIALIZED_VIEW}")) views = sa.inspect(self.engine).get_view_names(schema) queries = [] for table in self.tables(schema): schema, table = self._get_schema(schema, table) if (tables and table not in tables) or (table in views): continue logger.debug(f"Creating trigger on table: {schema}.{table}") for name, for_each, tg_op in [ ("notify", "ROW", ["INSERT", "UPDATE", "DELETE"]), ("truncate", "STATEMENT", ["TRUNCATE"]), ]: self.drop_triggers(schema, [table]) queries.append( sa.DDL( f'CREATE TRIGGER "{table}_{name}" ' f'AFTER {" OR ".join(tg_op)} ON "{schema}"."{table}" ' f"FOR EACH {for_each} EXECUTE PROCEDURE " f"{TRIGGER_FUNC}()", )) for query in queries: self.execute(query)
def downgrade(): # ### commands auto generated by Alembic - please adjust! ### op.drop_constraint("fk_projects_prj_owner_users", "projects", type_="foreignkey") set_prj_owner_conversion_fct = sa.DDL(f""" CREATE OR REPLACE FUNCTION convert_prj_owner_fct(current BIGINT) RETURNS varchar AS $BODY$ SELECT email FROM users WHERE "id" = current $BODY$ LANGUAGE 'sql' IMMUTABLE STRICT; """) op.execute(set_prj_owner_conversion_fct) op.alter_column( "projects", "prj_owner", type_=sa.String, nullable=False, postgresql_using="convert_prj_owner_fct(prj_owner)", # postgresql_using="prj_owner::bigint", ) op.execute("DROP FUNCTION convert_prj_owner_fct(current BIGINT)") op.alter_column("projects", "prj_owner", type_=sa.String) op.alter_column( "projects", "last_change_date", server_default=None, # onupdate=None, ) op.alter_column("projects", "creation_date", server_default=None) op.drop_column("projects", "access_rights")
def upgrade(): op.add_column('domain', sa.Column('title', sa.Unicode(length=250), nullable=True)) op.add_column( 'domain', sa.Column('legal_title', sa.Unicode(length=250), nullable=True)) op.add_column('domain', sa.Column('description', sa.UnicodeText(), nullable=True)) op.add_column('domain', sa.Column('logo_url', sa.Unicode(length=250), nullable=True)) op.add_column( 'domain', sa.Column('search_vector', postgresql.TSVECTOR(), nullable=True)) op.execute( sa.DDL(''' CREATE FUNCTION domain_search_vector_update() RETURNS TRIGGER AS $$ BEGIN NEW.search_vector = to_tsvector('english', COALESCE(NEW.name, '') || ' ' || COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.legal_title, '') || ' ' || COALESCE(NEW.description, '')); RETURN NEW; END $$ LANGUAGE 'plpgsql'; CREATE TRIGGER domain_search_vector_trigger BEFORE INSERT OR UPDATE ON domain FOR EACH ROW EXECUTE PROCEDURE domain_search_vector_update(); CREATE INDEX ix_domain_search_vector ON domain USING gin(search_vector); UPDATE domain SET search_vector = to_tsvector('english', COALESCE(name, '') || ' ' || COALESCE(title, '') || ' ' || COALESCE(legal_title, '') || ' ' || COALESCE(description, '')); '''))
def upgrade(): op.add_column( 'job_view_session', sa.Column('cointoss', sa.Boolean(), nullable=False, server_default='0'), ) op.add_column( 'job_view_session', sa.Column('crosstoss', sa.Boolean(), nullable=False, server_default='0'), ) op.alter_column('job_view_session', 'cointoss', server_default=None) op.alter_column('job_view_session', 'crosstoss', server_default=None) # Update the cointoss and crosstoss columns for existing data op.execute( sa.DDL(''' UPDATE job_view_session SET cointoss=jvs.cointoss, crosstoss=jvs.crosstoss FROM ( SELECT job_view_session.event_session_id, job_view_session.jobpost_id, TRUE as cointoss, CASE WHEN job_impression.bgroup != job_view_session.bgroup AND job_view_session.bgroup IS NOT NULL THEN TRUE ELSE FALSE END AS crosstoss FROM job_view_session, job_impression WHERE job_view_session.event_session_id = job_impression.event_session_id AND job_view_session.jobpost_id = job_impression.jobpost_id ) AS jvs WHERE job_view_session.event_session_id = jvs.event_session_id AND job_view_session.jobpost_id = jvs.jobpost_id; '''))
def downgrade(): op.add_column('proposal', sa.Column('preview_video', sa.UnicodeText(), nullable=True)) conn = op.get_bind() proposals = conn.execute(proposal.select().where( proposal.c.video_id.isnot(None))) for prop in proposals: conn.execute( sa.update(proposal).where(proposal.c.id == prop['id']).values( preview_video=make_video_url(prop['video_source'], prop['video_id']))) conn.execute( sa.update(proposal).where(proposal.c.preview_video.isnot(None)).values( video_source=None, video_id=None)) op.execute( sa.DDL( dedent(''' UPDATE proposal SET search_vector = setweight(to_tsvector('english', COALESCE(title, '')), 'A') || setweight(to_tsvector('english', COALESCE(abstract_text, '')), 'B') || setweight(to_tsvector('english', COALESCE(outline_text, '')), 'B') || setweight(to_tsvector('english', COALESCE(requirements_text, '')), 'B') || setweight(to_tsvector('english', COALESCE(slides, '')), 'B') || setweight(to_tsvector('english', COALESCE(preview_video, '')), 'C') || setweight(to_tsvector('english', COALESCE(links, '')), 'B') || setweight(to_tsvector('english', COALESCE(bio_text, '')), 'B'); DROP TRIGGER proposal_search_vector_trigger ON proposal; DROP FUNCTION proposal_search_vector_update(); CREATE FUNCTION proposal_search_vector_update() RETURNS trigger AS $$ BEGIN NEW.search_vector := setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') || setweight(to_tsvector('english', COALESCE(NEW.abstract_text, '')), 'B') || setweight(to_tsvector('english', COALESCE(NEW.outline_text, '')), 'B') || setweight(to_tsvector('english', COALESCE(NEW.requirements_text, '')), 'B') || setweight(to_tsvector('english', COALESCE(NEW.slides, '')), 'B') || setweight(to_tsvector('english', COALESCE(NEW.preview_video, '')), 'C') || setweight(to_tsvector('english', COALESCE(NEW.links, '')), 'B') || setweight(to_tsvector('english', COALESCE(NEW.bio_text, '')), 'B'); RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER proposal_search_vector_trigger BEFORE INSERT OR UPDATE ON proposal FOR EACH ROW EXECUTE PROCEDURE proposal_search_vector_update(); ''')))
def create_triggers(self, schema, tables=None): """Create a database triggers.""" self.execute(CREATE_TRIGGER_TEMPLATE) views = sa.inspect(self.engine).get_view_names(schema) queries = [] for table in self.tables(schema): schema, table = self._get_schema(schema, table) if (tables and table not in tables) or (table in views): continue logger.debug(f'Creating trigger on table: {schema}.{table}') for name, for_each, tg_op in [ ('notify', 'ROW', ['INSERT', 'UPDATE', 'DELETE']), ('truncate', 'STATEMENT', ['TRUNCATE']), ]: queries.append( sa.DDL( f'CREATE TRIGGER {table}_{name} ' f'AFTER {" OR ".join(tg_op)} ON "{schema}"."{table}" ' f'FOR EACH {for_each} EXECUTE PROCEDURE ' f'{TRIGGER_FUNC}()', ) ) for query in queries: self.execute(query)
def upgrade(): op.add_column('jobpost', sa.Column('search_vector', postgresql.TSVECTOR(), nullable=True)) op.execute(sa.DDL( ''' CREATE FUNCTION jobpost_search_vector_update() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' THEN NEW.search_vector = to_tsvector('english', COALESCE(NEW.company_name, '') || ' ' || COALESCE(NEW.headline, '') || ' ' || COALESCE(NEW.headlineb, '') || ' ' || COALESCE(NEW.description, '') || ' ' || COALESCE(NEW.perks, '')); END IF; IF TG_OP = 'UPDATE' THEN IF NEW.headline <> OLD.headline OR COALESCE(NEW.headlineb, '') <> COALESCE(OLD.headlineb, '') OR NEW.description <> OLD.description OR NEW.perks <> OLD.perks THEN NEW.search_vector = to_tsvector('english', COALESCE(NEW.company_name, '') || ' ' || COALESCE(NEW.headline, '') || ' ' || COALESCE(NEW.headlineb, '') || ' ' || COALESCE(NEW.description, '') || ' ' || COALESCE(NEW.perks, '')); END IF; END IF; RETURN NEW; END $$ LANGUAGE 'plpgsql'; CREATE TRIGGER jobpost_search_vector_trigger BEFORE INSERT OR UPDATE ON jobpost FOR EACH ROW EXECUTE PROCEDURE jobpost_search_vector_update(); CREATE INDEX ix_jobpost_search_vector ON jobpost USING gin(search_vector); UPDATE jobpost SET search_vector = to_tsvector('english', COALESCE(company_name, '') || ' ' || COALESCE(headline, '') || ' ' || COALESCE(headlineb, '') || ' ' || COALESCE(description, '') || ' ' || COALESCE(perks, '')); '''))
def upgrade(): op.execute( sa.DDL(""" UPDATE projects SET workbench = (regexp_replace(workbench::text, '"state": ("[^"]*")', '"state": {"currentStatus": \\1}', 'g'))::json WHERE workbench::text LIKE '%%state%%' """))
def downgrade(): # ### commands auto generated by Alembic - please adjust! ### op.execute( sa.DDL("UPDATE projects SET thumbnail = '' WHERE thumbnail IS NULL")) op.alter_column("projects", "thumbnail", existing_type=sa.VARCHAR(), nullable=False) op.execute( sa.DDL( "UPDATE projects SET description = '' WHERE description IS NULL")) op.alter_column("projects", "description", existing_type=sa.VARCHAR(), nullable=False)
def downgrade(): op.execute( sa.DDL(''' DROP TRIGGER project_venue_primary_trigger ON project_venue_primary; DROP FUNCTION project_venue_primary_validate(); ''')) op.drop_table('project_venue_primary')
def downgrade(): op.add_column( 'user_notification', sa.Column( 'is_revoked', sa.BOOLEAN(), autoincrement=False, nullable=False, server_default=sa.sql.expression.false(), ), ) op.execute( sa.DDL('UPDATE user_notification SET is_revoked = TRUE' ' WHERE revoked_at IS NOT NULL;')) op.alter_column('user_notification', 'is_revoked', server_default=None) op.create_index( 'ix_user_notification_is_revoked', 'user_notification', ['is_revoked'], unique=False, ) op.drop_index(op.f('ix_user_notification_revoked_at'), table_name='user_notification') op.drop_column('user_notification', 'revoked_at')
def upgrade(): # Remove duplicate entries before dropping the project_id column op.execute( sa.DDL( ''' DELETE FROM contact_exchange WHERE (user_id, project_id, participant_id) IN ( SELECT user_id, project_id, participant_id FROM (SELECT user_id, project_id, participant_id, row_number() OVER (partition by user_id, participant_id ORDER BY created_at) FROM contact_exchange) AS duplicates WHERE row_number != 1); ''' ) ) # Index by participant id op.create_index( op.f('ix_contact_exchange_participant_id'), 'contact_exchange', ['participant_id'], unique=False, ) # Drop the primary key op.drop_constraint('contact_exchange_pkey', 'contact_exchange', type_='primary') # Recreate primary key without project_id op.create_primary_key( 'contact_exchange_pkey', 'contact_exchange', ['user_id', 'participant_id'] ) # Finally, drop the project_id foreign key constraint and then the column itself op.drop_constraint( 'contact_exchange_project_id_fkey', 'contact_exchange', type_='foreignkey' ) op.drop_column('contact_exchange', 'project_id')