Script 3a035889852c_add_copr_fulltext_py
|
|
1 """add_copr_fulltext
2
3 Revision ID: 3a035889852c
4 Revises: 3c3cce7a5fe0
5 Create Date: 2013-02-01 10:06:37.034495
6
7 """
8
9
10 revision = '3a035889852c'
11 down_revision = '3c3cce7a5fe0'
12
13 from alembic import op
14 import sqlalchemy as sa
15 from sqlalchemy import types
16 from sqlalchemy.ext import compiler
21
26
31
34
35 op.add_column('copr', sa.Column('copr_ts_col', Tsvector(), nullable=True))
36 op.create_index(
37 'copr_ts_idx', 'copr', ['copr_ts_col'], postgresql_using='gin')
38
39 session = sa.orm.sessionmaker(bind=op.get_bind())()
40 metadata = sa.MetaData()
41 if op.get_bind().dialect.name == 'postgresql':
42 op.execute("UPDATE copr \
43 SET copr_ts_col = to_tsvector('pg_catalog.english', coalesce(name, '') || ' ' || \
44 coalesce(description, '') || ' ' || coalesce(instructions, ''))")
45
46 op.execute("CREATE TRIGGER copr_ts_update BEFORE INSERT OR UPDATE \
47 ON copr \
48 FOR EACH ROW EXECUTE PROCEDURE \
49 tsvector_update_trigger(copr_ts_col, 'pg_catalog.english', name, description, instructions);")
50 elif op.get_bind().dialect.name == 'sqlite':
51 op.execute("UPDATE copr \
52 SET copr_ts_col = coalesce(name, '') || ' ' || \
53 coalesce(description, '') || ' ' || coalesce(instructions, '')")
54
55 op.execute("CREATE TRIGGER copr_ts_update \
56 AFTER UPDATE OF name, description, instructions \
57 ON copr \
58 FOR EACH ROW \
59 BEGIN \
60 UPDATE copr SET copr_ts_col = coalesce(name, '') || ' ' || \
61 coalesce(description, '') || ' ' || coalesce(instructions, ''); \
62 END;")
63 op.execute("CREATE TRIGGER copr_ts_insert \
64 AFTER INSERT \
65 ON copr \
66 FOR EACH ROW \
67 BEGIN \
68 UPDATE copr SET copr_ts_col = coalesce(name, '') || ' ' || \
69 coalesce(description, '') || ' ' || coalesce(instructions, ''); \
70 END;")
71
75
76 op.drop_column('copr', 'copr_ts_col')
77 if op.get_bind().dialect.name == 'postgresql':
78 op.execute("DROP TRIGGER copr_ts_update ON copr")
79
80