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