Source Code for Script 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  # revision identifiers, used by Alembic. 
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 
17   
18 -class Tsvector(types.UnicodeText): 
19      pass 
20   
21  @compiler.compiles(Tsvector, 'postgresql') 
22 -def compile_tsvector(element, compiler, **kw): 
23      return 'tsvector' 
24   
25  @compiler.compiles(Tsvector, 'sqlite') 
26 -def compile_tsvector(element, compiler, **kw): 
27      return 'text' 
28   
29 -def upgrade(): 
30      ### commands auto generated by Alembic - please adjust! ### 
31      op.add_column('copr', sa.Column('copr_ts_col', Tsvector(), nullable=True)) 
32      op.create_index('copr_ts_idx', 'copr', ['copr_ts_col'], postgresql_using='gin') 
33   
34      session = sa.orm.sessionmaker(bind=op.get_bind())() 
35      metadata = sa.MetaData() 
36      if op.get_bind().dialect.name == 'postgresql': 
37          op.execute("UPDATE copr \ 
38                      SET copr_ts_col = to_tsvector('pg_catalog.english', coalesce(name, '') || ' ' || \ 
39                          coalesce(description, '') || ' ' || coalesce(instructions, ''))") 
40          # no need to coalesce here, the trigger doesn't need it 
41          op.execute("CREATE TRIGGER copr_ts_update BEFORE INSERT OR UPDATE \ 
42                      ON copr \ 
43                      FOR EACH ROW EXECUTE PROCEDURE \ 
44                      tsvector_update_trigger(copr_ts_col, 'pg_catalog.english', name, description, instructions);") 
45      elif op.get_bind().dialect.name == 'sqlite': 
46          op.execute("UPDATE copr \ 
47                      SET copr_ts_col = coalesce(name, '') || ' ' || \ 
48                          coalesce(description, '') || ' ' || coalesce(instructions, '')") 
49          # two triggers for sqlite... 
50          op.execute("CREATE TRIGGER copr_ts_update \ 
51                      AFTER UPDATE OF name, description, instructions \ 
52                      ON copr \ 
53                      FOR EACH ROW \ 
54                      BEGIN \ 
55                      UPDATE copr SET copr_ts_col = coalesce(name, '') || ' ' || \ 
56                                                    coalesce(description, '') || ' ' || coalesce(instructions, ''); \ 
57                      END;") 
58          op.execute("CREATE TRIGGER copr_ts_insert \ 
59                      AFTER INSERT \ 
60                      ON copr \ 
61                      FOR EACH ROW \ 
62                      BEGIN \ 
63                      UPDATE copr SET copr_ts_col = coalesce(name, '') || ' ' || \ 
64                                                    coalesce(description, '') || ' ' || coalesce(instructions, ''); \ 
65                      END;") 
66      ### end Alembic commands ### 
67   
68   
69 -def downgrade(): 
70      ### commands auto generated by Alembic - please adjust! ### 
71      op.drop_column('copr', 'copr_ts_col') 
72      if op.get_bind().dialect.name == 'postgresql': 
73          op.execute("DROP TRIGGER copr_ts_update ON copr") 
74      ### end Alembic commands ### 
75