Script 3a035889852c_add_copr_fulltext_py
[hide private]
[frames] | no frames]

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 19 -class Tsvector(types.UnicodeText):
20 pass
21
22 23 @compiler.compiles(Tsvector, 'postgresql') 24 -def compile_tsvector(element, compiler, **kw):
25 return 'tsvector'
26
27 28 @compiler.compiles(Tsvector, 'sqlite') 29 -def compile_tsvector(element, compiler, **kw):
30 return 'text'
31
32 33 -def upgrade():
34 ### commands auto generated by Alembic - please adjust! ### 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 # no need to coalesce here, the trigger doesn't need it 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 # two triggers for sqlite... 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 ### end Alembic commands ###
72 73 74 -def downgrade():
75 ### commands auto generated by Alembic - please adjust! ### 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 ### end Alembic commands ### 80