def add_search_view(): # from sqlalchemy import DDL # search_view = DDL(""" engine.execute("""DROP TABLE search;""") engine.execute(""" CREATE OR REPLACE VIEW search AS SELECT product.text AS text, product.long_name AS title, product.search_vector AS search_vector, 'product' as object, product.id as object_id, product.id as id FROM product UNION ALL SELECT project.text AS text, project.long_name AS title, project.search_vector AS search_vector, 'project' as object, project.id as object_id, project.id as id FROM project UNION ALL SELECT member.bio AS text, member.first_name || ' ' || member.last_name AS title, member.search_vector AS search_vector, 'member' as object, member.id as object_id, member.id as id FROM member ; """)
def init_db(): from obstructures.work.models import Base try: engine.execute('DROP VIEW search') except: pass try: engine.execute('DROP TABLE search') except: pass Base.metadata.drop_all(bind=engine) Base.metadata.create_all(bind=engine) from obstructures.work.models import ProjectType, ProjectCategory from obstructures.constants import PROJECT_TYPES, PROJECT_CATEGORIES for category in PROJECT_CATEGORIES: cat = ProjectCategory( name=category[0], order=category[1], ) db_session.add(cat) db_session.commit() for category_id, project_type in PROJECT_TYPES: pt = ProjectType( category_id=category_id, name=project_type, ) db_session.add(pt) db_session.commit() # db_session.flush() db_session.commit()
def add_search_vectors(): engine.execute('ALTER TABLE project ADD COLUMN search_vector tsvector') engine.execute('ALTER TABLE product ADD COLUMN search_vector tsvector') engine.execute('ALTER TABLE member ADD COLUMN search_vector tsvector') engine.execute('CREATE INDEX project_search_index ON project USING gin(search_vector)') engine.execute('CREATE INDEX product_search_index ON product USING gin(search_vector)') engine.execute('CREATE INDEX member_search_index ON member USING gin(search_vector)') engine.execute('CREATE TRIGGER project_search_update BEFORE UPDATE OR INSERT ON project FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger("search_vector", "pg_catalog.english", "text", "long_name")') engine.execute('CREATE TRIGGER product_search_update BEFORE UPDATE OR INSERT ON product FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger("search_vector", "pg_catalog.english", "text", "long_name")') engine.execute('CREATE TRIGGER member_search_update BEFORE UPDATE OR INSERT ON member FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger("search_vector", "pg_catalog.english", "bio")')