Here are the examples of the python api sqlalchemy.dialects.postgresql.ARRAY taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
42 Examples
3
Example 1
Project: pygotham Source File: 5d619660cfa7_switch_navbar_section_to_navbar_path.py
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column(
'about_pages',
sa.Column(
'navbar_path',
postgresql.ARRAY(sa.String()),
nullable=False,
server_default='{}',
),
)
op.create_unique_constraint('ix_about_pages_navbar_path_slug_event_id', 'about_pages', ['navbar_path', 'slug', 'event_id'])
op.execute(
'UPDATE about_pages '
'SET navbar_path=ARRAY[navbar_section, title]'
)
op.drop_constraint('ix_about_pages_navbar_section_slug_event_id', 'about_pages', type_='unique')
op.drop_column('about_pages', 'navbar_section')
3
Example 2
Project: pybossa Source File: 4f12d8650050_add_results_table.py
def upgrade():
op.create_table(
'result',
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('created', sa.Text, default=make_timestamp),
sa.Column('project_id', sa.Integer, sa.ForeignKey('project.id'), nullable=False),
sa.Column('task_id', sa.Integer, sa.ForeignKey('task.id'), nullable=False),
sa.Column('task_run_ids', ARRAY(sa.Integer), nullable=False),
sa.Column('last_version', sa.Boolean, default=True),
sa.Column('info', JSON)
)
3
Example 3
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('signature',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('pgp_keyid', sa.String(length=16), nullable=False),
sa.Column('sigtype', sa.Integer(), nullable=True),
sa.Column('key_sfp_for', sa.String(length=16), nullable=True),
sa.Column('key_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['key_id'], ['key.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.add_column('key', sa.Column('subkeys', postgresql.ARRAY(sa.String(length=255)), nullable=True))
op.alter_column('key', 'armored',
existing_type=sa.TEXT(),
nullable=True)
3
Example 4
Project: pittsburgh-purchasing-suite Source File: 3c8dbc9ebf7e_more_robust_opportunities.py
Function: downgrade
Function: downgrade
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column(u'opportunity', sa.Column('created_by', sa.INTEGER(), autoincrement=False, nullable=False, server_default=sa.schema.DefaultClause('1')))
op.add_column(u'opportunity', sa.Column('docuement', sa.VARCHAR(length=255), autoincrement=False, nullable=True))
op.add_column(u'opportunity', sa.Column('docuements_needed', postgresql.ARRAY(sa.INTEGER()), autoincrement=False, nullable=True))
op.add_column(u'opportunity', sa.Column('docuement_href', sa.VARCHAR(length=255), autoincrement=False, nullable=True))
op.drop_constraint('opportunity_created_by_id_user_id_fkey', 'opportunity', type_='foreignkey')
op.create_foreign_key(u'opportunity_created_by_user_id_fkey', 'opportunity', 'users', ['created_by'], ['id'])
op.alter_column(u'opportunity', 'planned_open',
existing_type=postgresql.TIMESTAMP(),
nullable=True)
op.alter_column(u'opportunity', 'planned_deadline',
existing_type=postgresql.TIMESTAMP(),
nullable=True)
op.drop_column(u'opportunity', 'vendor_docuements_needed')
op.drop_column(u'opportunity', 'planned_advertise')
op.drop_column(u'opportunity', 'created_by_id')
op.drop_index(op.f('ix_opportunity_docuement_id'), table_name='opportunity_docuement')
op.drop_table('opportunity_docuement')
3
Example 5
def upgrade():
op.create_table(
'command',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('jobstep_id', sa.GUID(), nullable=False),
sa.Column('label', sa.String(length=128), nullable=False),
sa.Column('status', sa.Enum(), nullable=False),
sa.Column('return_code', sa.Integer(), nullable=False),
sa.Column('script', sa.Text(), nullable=False),
sa.Column('env', sa.String(length=2048), nullable=True),
sa.Column('cwd', sa.String(length=256), nullable=True),
sa.Column('artifacts', postgresql.ARRAY(sa.String(length=256)), nullable=True),
sa.Column('date_started', sa.DateTime(), nullable=True),
sa.Column('date_finished', sa.DateTime(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('data', sa.JSONEncodedDict(), nullable=True),
sa.ForeignKeyConstraint(['jobstep_id'], ['jobstep.id'], ),
sa.PrimaryKeyConstraint('id')
)
3
Example 6
@has_management_role.expression
def has_management_role(cls, role=None, explicit=False):
if role is None:
if explicit:
raise ValueError('role must be specified if explicit=True')
return cls.full_access
valid_roles = get_available_roles(cls.principal_for_obj).viewkeys()
if role == 'ANY':
crit = (cls.roles.op('&&')(db.func.cast(valid_roles, ARRAY(db.String))))
else:
assert role in valid_roles, "invalid role '{}' for object '{}'".format(role, cls.principal_for_obj)
crit = (cls.roles.op('&&')(db.func.cast([role], ARRAY(db.String))))
if explicit:
return crit
else:
return cls.full_access | crit
3
Example 7
Project: indico Source File: 201505271351_55583215f6fb_add_event_reminders_tables.py
Function: upgrade
Function: upgrade
def upgrade():
op.create_table('reminders',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('event_id', sa.Integer(), nullable=False),
sa.Column('creator_id', sa.Integer(), nullable=False, index=True),
sa.Column('created_dt', UTCDateTime, nullable=False),
sa.Column('scheduled_dt', UTCDateTime, nullable=False),
sa.Column('is_sent', sa.Boolean(), nullable=False),
sa.Column('event_start_delta', sa.Interval(), nullable=True),
sa.Column('recipients', postgresql.ARRAY(sa.String()), nullable=False),
sa.Column('send_to_participants', sa.Boolean(), nullable=False),
sa.Column('include_summary', sa.Boolean(), nullable=False),
sa.Column('reply_to_address', sa.String(), nullable=False),
sa.Column('message', sa.String(), nullable=False),
sa.ForeignKeyConstraint(['creator_id'], ['users.users.id']),
sa.PrimaryKeyConstraint('id'),
schema='events')
op.create_index(None, 'reminders', ['scheduled_dt'], schema='events', postgresql_where=sa.text('not is_sent'))
3
Example 8
Project: indico Source File: 201607011725_23ef6a49ae28_add_registration_requests_table.py
Function: upgrade
Function: upgrade
def upgrade():
op.create_table(
'registration_requests',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(), nullable=False, unique=True, index=True),
sa.Column('extra_emails', postgresql.ARRAY(sa.String()), nullable=False),
sa.Column('comment', sa.Text(), nullable=False),
sa.Column('user_data', postgresql.JSON(), nullable=False),
sa.Column('identity_data', postgresql.JSON(), nullable=False),
sa.Column('settings', postgresql.JSON(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.CheckConstraint("email = lower(email)", name='lowercase_email'),
schema='users'
)
3
Example 9
def columns(self):
cols = [ Column('symbol', ARRAY(String))
]
for c in CONF.difficulty_map:
cols.append(Column(c, Boolean))
for c in CONF.piste_type:
cols.append(Column(c, Boolean))
return cols
3
Example 10
def columns(self):
return (Column('class', Integer),
Column('network', String(length=2)),
Column('style', Integer),
Column('inrshields', ARRAY(String)),
Column('allshields', ARRAY(String)),
Column('rels', ARRAY(BigInteger))
)
3
Example 11
Project: pushkin Source File: 61e52d26ebea_message_blacklist.py
def upgrade():
context = op.get_context()
connection = op.get_bind()
op.create_table('message_blacklist',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('login_id', sa.BigInteger(), nullable=False),
sa.Column('blacklist', postgresql.ARRAY(sa.Integer)),
sa.ForeignKeyConstraint(['login_id'], ['login.id'], ondelete='CASCADE', name="ref_message_blacklist_login_id_to_login"),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('idx_message_blacklist_login_id'), 'message_blacklist', ['login_id'], unique=True)
3
Example 12
Project: fuel-ostf Source File: 54904076d82d_list_of_excl_testset.py
def upgrade():
op.add_column('test_sets', sa.Column('exclusive_testsets',
postgresql.ARRAY(
sa.String(length=128)
),
nullable=True))
0
Example 13
Project: aiorest-ws Source File: serializers.py
def build_standard_field(self, *args, **kwargs):
"""
Create regular model fields.
"""
field_name, model_field, model_class = args
field_mapping = ClassLookupDict(self.serializer_field_mapping)
field_class = field_mapping[model_field.type]
field_kwargs = get_field_kwargs(field_name, model_field, model_class)
if 'choices' in field_kwargs:
# Fields with choices get coerced into `ChoiceField`
# instead of using their regular typed field.
field_class = self.serializer_choice_field
# Some model fields may introduce kwargs that would not be valid
# for the choice field. We need to strip these out.
# Eg. Column('race', Enum('one', 'two', 'three'))
valid_kwargs = {
'read_only', 'write_only',
'required', 'default', 'initial', 'source',
'label', 'help_text', 'style',
'error_messages', 'validators', 'allow_null', 'allow_blank',
'choices'
}
for key in list(field_kwargs.keys()):
if key not in valid_kwargs:
field_kwargs.pop(key)
if not issubclass(field_class, ModelField):
# `model_field` is only valid for the fallback case of
# `ModelField`, which is used when no other typed field
# matched to the model field.
field_kwargs.pop('model_field', None)
if not issubclass(field_class, CharField) and \
not issubclass(field_class, EnumField):
# `allow_blank` is only valid for textual fields.
field_kwargs.pop('allow_blank', None)
if isinstance(model_field.type, postgresql.ARRAY):
# Populate the `child` argument on `ListField` instances generated
# for the PostgreSQL specific `ArrayField`.
child_field_name = '%s.%s' % (field_name, 'child')
child_model_field = model_field.type.item_type
# Because our model_field, which passed to the build_standard_field
# function is a Column, then wrap this sub-field and invoke this
# method one more time
child_field_class, child_field_kwargs = self.build_standard_field(
child_field_name, Column(child_model_field), model_class
)
field_kwargs['child'] = child_field_class(**child_field_kwargs)
return field_class, field_kwargs
0
Example 14
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('key',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('uid', sa.String(length=255), nullable=False),
sa.Column('fingerprint', sa.String(length=40), nullable=False),
sa.Column('key_fp_id', sa.String(length=8), nullable=False),
sa.Column('keyalgo', sa.Integer(), nullable=False),
sa.Column('created', sa.DateTime(), nullable=False),
sa.Column('expires', sa.DateTime(), nullable=False),
sa.Column('length', sa.Integer(), nullable=False),
sa.Column('armored', sa.Text(), nullable=False),
sa.Column('subkeys', postgresql.ARRAY(sa.String(length=255)), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('signature',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('pgp_keyid', sa.String(length=16), nullable=False),
sa.Column('sigtype', sa.String(length=16), nullable=True),
sa.Column('key_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['key_id'], ['key.id'], ),
sa.PrimaryKeyConstraint('id')
)
0
Example 15
Project: alembic Source File: test_postgresql.py
def test_compare_array_of_integer_text(self):
self._compare_default_roundtrip(
ARRAY(Integer),
text("(ARRAY[]::integer[])")
)
0
Example 16
Project: sqlalchemy Source File: test_reflection.py
@testing.provide_metadata
def test_index_reflection_with_access_method(self):
"""reflect indexes with storage options set"""
metadata = self.metadata
Table(
't', metadata,
Column('id', Integer, primary_key=True),
Column('x', ARRAY(Integer))
)
metadata.create_all()
with testing.db.connect().execution_options(autocommit=True) as conn:
conn.execute("CREATE INDEX idx1 ON t USING gin (x)")
ind = testing.db.dialect.get_indexes(conn, "t", None)
eq_(ind, [{'unique': False, 'column_names': ['x'], 'name': 'idx1',
'dialect_options': {'postgresql_using': 'gin'}}])
m = MetaData()
t1 = Table('t', m, autoload_with=conn)
eq_(
list(t1.indexes)[0].dialect_options['postgresql']['using'],
'gin'
)
0
Example 17
Project: ibis Source File: compiler.py
def _find_in_set(t, expr):
# postgresql 9.5 has array_position, but the code below works on any
# version of postgres with generate_subscripts
# TODO: could make it even more generic by using generate_series
# TODO: this works with *any* type, not just strings. should the operation
# itself also have this property?
arg, haystack = expr.op().args
needle = t.translate(arg)
haystack = sa.select([sa.literal(
[element._arg.value for element in haystack],
type_=sa.dialects.postgresql.ARRAY(needle.type)
).label('haystack')]).cte()
subscripts = sa.select([
sa.func.generate_subscripts(haystack.c.haystack, 1).label('i')
]).cte()
# return a zero based index
return sa.select([subscripts.c.i - 1]).where(
~_distinct_from(haystack.c.haystack[subscripts.c.i], needle)
).order_by(subscripts.c.i).limit(1)
0
Example 18
Project: pittsburgh-purchasing-suite Source File: 22427b19886b_.py
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('company',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('company_name', sa.String(length=255), nullable=False),
sa.Column('contact_first_name', sa.String(length=255), nullable=True),
sa.Column('contact_second_name', sa.String(length=255), nullable=True),
sa.Column('contact_addr1', sa.String(length=255), nullable=True),
sa.Column('contact_addr2', sa.String(length=255), nullable=True),
sa.Column('contact_city', sa.String(length=255), nullable=True),
sa.Column('contact_state', sa.String(length=255), nullable=True),
sa.Column('contact_zip', sa.Integer(), nullable=True),
sa.Column('contact_phone', sa.String(length=255), nullable=True),
sa.Column('contact_email', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('stage',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('flow',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('flow_name', sa.Text(), nullable=True),
sa.Column('stage_order', postgresql.ARRAY(sa.Integer()), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('flow_name')
)
op.create_table('contract',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('contract_type', sa.String(length=255), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('current_stage_id', sa.Integer(), nullable=True),
sa.Column('flow_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['current_stage_id'], ['stage.id'], ondelete='SET NULL'),
sa.ForeignKeyConstraint(['flow_id'], ['flow.id'], ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id')
)
op.create_table('stage_property',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('stage_id', sa.Integer(), nullable=False),
sa.Column('key', sa.String(length=255), nullable=True),
sa.Column('value', sa.String(length=255), nullable=True),
sa.ForeignKeyConstraint(['stage_id'], ['stage.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
op.create_table('company_contract_association',
sa.Column('company_id', sa.Integer(), nullable=True),
sa.Column('contract_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['company_id'], ['company.id'], ondelete='SET NULL'),
sa.ForeignKeyConstraint(['contract_id'], ['contract.id'], ondelete='SET NULL')
)
op.create_table('contract_property',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('contract_id', sa.Integer(), nullable=False),
sa.Column('key', sa.String(length=255), nullable=True),
sa.Column('value', sa.String(length=255), nullable=True),
sa.ForeignKeyConstraint(['contract_id'], ['contract.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
0
Example 19
Project: pittsburgh-purchasing-suite Source File: 3c8dbc9ebf7e_more_robust_opportunities.py
Function: upgrade
Function: upgrade
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('opportunity_docuement',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('opportunity_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=255), nullable=True),
sa.Column('href', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(['opportunity_id'], ['opportunity.id'], ondelete='cascade'),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_opportunity_docuement_id'), 'opportunity_docuement', ['id'], unique=False)
op.add_column(u'opportunity', sa.Column('created_by_id', sa.Integer(), nullable=False, server_default=sa.schema.DefaultClause('1')))
op.add_column(u'opportunity', sa.Column('planned_advertise', sa.DateTime(), nullable=False, server_default=sa.schema.DefaultClause('2015-01-01 00:00:00')))
op.add_column(u'opportunity', sa.Column('vendor_docuements_needed', postgresql.ARRAY(sa.Integer()), nullable=True))
op.alter_column(u'opportunity', 'planned_deadline',
existing_type=postgresql.TIMESTAMP(),
nullable=False)
op.alter_column(u'opportunity', 'planned_open',
existing_type=postgresql.TIMESTAMP(),
nullable=False)
op.drop_constraint(u'opportunity_created_by_user_id_fkey', 'opportunity', type_='foreignkey')
op.create_foreign_key('opportunity_created_by_id_user_id_fkey', 'opportunity', 'users', ['created_by_id'], ['id'])
op.drop_column(u'opportunity', 'docuement_href')
op.drop_column(u'opportunity', 'docuements_needed')
op.drop_column(u'opportunity', 'docuement')
op.drop_column(u'opportunity', 'created_by')
0
Example 20
Project: pittsburgh-purchasing-suite Source File: 3de73bde77e_fix_opportunities_model.py
Function: upgrade
Function: upgrade
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('category_opportunity_association',
sa.Column('category_id', sa.Integer(), nullable=True),
sa.Column('opportunity_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['category_id'], ['category.id'], ondelete='SET NULL'),
sa.ForeignKeyConstraint(['opportunity_id'], ['opportunity.id'], ondelete='SET NULL')
)
op.create_index(op.f('ix_category_opportunity_association_category_id'), 'category_opportunity_association', ['category_id'], unique=False)
op.create_index(op.f('ix_category_opportunity_association_opportunity_id'), 'category_opportunity_association', ['opportunity_id'], unique=False)
op.create_table('docuement',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('display_name', sa.String(length=255), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('form_href', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_docuements_id'), 'docuement', ['id'], unique=False)
op.add_column(u'opportunity', sa.Column('contact_id', sa.Integer(), nullable=False))
op.add_column(u'opportunity', sa.Column('planned_deadline', sa.DateTime(), nullable=True))
op.add_column(u'opportunity', sa.Column('planned_open', sa.DateTime(), nullable=True))
op.add_column(u'opportunity', sa.Column('created_by', sa.Integer(), nullable=False))
op.add_column(u'opportunity', sa.Column('docuement', sa.String(length=255), nullable=True))
op.add_column(u'opportunity', sa.Column('docuement_href', sa.String(length=255), nullable=True))
op.add_column(u'opportunity', sa.Column('created_from_id', sa.Integer(), nullable=True))
op.add_column(u'opportunity', sa.Column('is_public', sa.Boolean(), nullable=True))
op.add_column(u'opportunity', sa.Column('docuements_needed', postgresql.ARRAY(sa.Integer()), nullable=True))
op.create_foreign_key('opportunity_created_by_user_id_fkey', 'opportunity', 'users', ['created_by'], ['id'])
op.drop_constraint(u'opportunity_category_id_fkey', 'opportunity', type_='foreignkey')
op.create_foreign_key('opportunity_user_id_fkey', 'opportunity', 'users', ['contact_id'], ['id'])
op.drop_column(u'opportunity', 'category_id')
op.drop_column(u'opportunity', 'bid_open')
op.drop_column(u'opportunity', 'contract_id')
0
Example 21
Project: sqlalchemy_postgresql_json Source File: mutable.py
def monkey_patch_array():
MutableList.associate_with(postgresql.ARRAY)
0
Example 22
Project: changes Source File: 2b8459f1e2d6_initial_schema.py
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('repository',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('url', sa.String(length=200), nullable=False),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('url')
)
op.create_table('node',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('label', sa.String(length=128), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('author',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('email', sa.String(length=128), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email'),
sa.UniqueConstraint('name')
)
op.create_table('remoteentity',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('type', sa.String(), nullable=False),
sa.Column('provider', sa.String(length=128), nullable=False),
sa.Column('remote_id', sa.String(length=128), nullable=False),
sa.Column('internal_id', sa.GUID(), nullable=False),
sa.Column('data', sa.JSONEncodedDict(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('provider','remote_id','type', name='remote_identifier')
)
op.create_table('project',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('slug', sa.String(length=64), nullable=False),
sa.Column('repository_id', sa.GUID(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('avg_build_time', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('slug')
)
op.create_table('revision',
sa.Column('repository_id', sa.GUID(), nullable=False),
sa.Column('sha', sa.String(length=40), nullable=False),
sa.Column('author_id', sa.GUID(), nullable=True),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('parents', postgresql.ARRAY(sa.String(length=40)), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['author_id'], ['author.id'], ),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'], ),
sa.PrimaryKeyConstraint('repository_id', 'sha')
)
op.create_table('change',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('hash', sa.String(length=40), nullable=False),
sa.Column('repository_id', sa.GUID(), nullable=False),
sa.Column('project_id', sa.GUID(), nullable=False),
sa.Column('author_id', sa.GUID(), nullable=True),
sa.Column('label', sa.String(length=128), nullable=False),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_modified', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['author_id'], ['author.id'], ),
sa.ForeignKeyConstraint(['project_id'], ['project.id'], ),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('hash')
)
op.create_table('patch',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('change_id', sa.GUID(), nullable=True),
sa.Column('repository_id', sa.GUID(), nullable=False),
sa.Column('project_id', sa.GUID(), nullable=False),
sa.Column('parent_revision_sha', sa.String(length=40), nullable=False),
sa.Column('label', sa.String(length=64), nullable=False),
sa.Column('url', sa.String(length=200), nullable=True),
sa.Column('diff', sa.Text(), nullable=True),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['change_id'], ['change.id'], ),
sa.ForeignKeyConstraint(['project_id'], ['project.id'], ),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('build',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('change_id', sa.GUID(), nullable=True),
sa.Column('repository_id', sa.GUID(), nullable=False),
sa.Column('project_id', sa.GUID(), nullable=False),
sa.Column('parent_revision_sha', sa.String(length=40), nullable=True),
sa.Column('patch_id', sa.GUID(), nullable=True),
sa.Column('author_id', sa.GUID(), nullable=True),
sa.Column('label', sa.String(length=128), nullable=False),
sa.Column('status', sa.Enum(), nullable=False),
sa.Column('result', sa.Enum(), nullable=False),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('duration', sa.Integer(), nullable=True),
sa.Column('date_started', sa.DateTime(), nullable=True),
sa.Column('date_finished', sa.DateTime(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('date_modified', sa.DateTime(), nullable=True),
sa.Column('data', sa.JSONEncodedDict(), nullable=True),
sa.ForeignKeyConstraint(['author_id'], ['author.id'], ),
sa.ForeignKeyConstraint(['change_id'], ['change.id'], ),
sa.ForeignKeyConstraint(['patch_id'], ['patch.id'], ),
sa.ForeignKeyConstraint(['project_id'], ['project.id'], ),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('filecoverage',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('build_id', sa.GUID(), nullable=False),
sa.Column('filename', sa.String(length=256), nullable=False),
sa.Column('project_id', sa.Integer(), nullable=False),
sa.Column('data', sa.Text(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['build_id'], ['build.id'], ),
sa.PrimaryKeyConstraint('id', 'filename')
)
op.create_table('test',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('build_id', sa.GUID(), nullable=False),
sa.Column('project_id', sa.GUID(), nullable=False),
sa.Column('group_sha', sa.String(length=40), nullable=False),
sa.Column('label_sha', sa.String(length=40), nullable=False),
sa.Column('group', sa.Text(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('package', sa.Text(), nullable=True),
sa.Column('result', sa.Enum(), nullable=True),
sa.Column('duration', sa.Integer(), nullable=True),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['build_id'], ['build.id'], ),
sa.ForeignKeyConstraint(['project_id'], ['project.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('build_id','group_sha','label_sha', name='_test_key')
)
op.create_table('phase',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('build_id', sa.GUID(), nullable=False),
sa.Column('repository_id', sa.GUID(), nullable=False),
sa.Column('project_id', sa.GUID(), nullable=False),
sa.Column('label', sa.String(length=128), nullable=False),
sa.Column('status', sa.Enum(), nullable=False),
sa.Column('result', sa.Enum(), nullable=False),
sa.Column('date_started', sa.DateTime(), nullable=True),
sa.Column('date_finished', sa.DateTime(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['build_id'], ['build.id'], ),
sa.ForeignKeyConstraint(['project_id'], ['project.id'], ),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('step',
sa.Column('id', sa.GUID(), nullable=False),
sa.Column('build_id', sa.GUID(), nullable=False),
sa.Column('phase_id', sa.GUID(), nullable=False),
sa.Column('repository_id', sa.GUID(), nullable=False),
sa.Column('project_id', sa.GUID(), nullable=False),
sa.Column('label', sa.String(length=128), nullable=False),
sa.Column('status', sa.Enum(), nullable=False),
sa.Column('result', sa.Enum(), nullable=False),
sa.Column('node_id', sa.GUID(), nullable=True),
sa.Column('date_started', sa.DateTime(), nullable=True),
sa.Column('date_finished', sa.DateTime(), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['build_id'], ['build.id'], ),
sa.ForeignKeyConstraint(['node_id'], ['node.id'], ),
sa.ForeignKeyConstraint(['phase_id'], ['phase.id'], ),
sa.ForeignKeyConstraint(['project_id'], ['project.id'], ),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'], ),
sa.PrimaryKeyConstraint('id')
)
0
Example 23
def upgrade():
op.add_column('revision', sa.Column('branches', postgresql.ARRAY(sa.String(length=128)), nullable=True))
0
Example 24
def upgrade():
op.add_column('build', sa.Column('tags', postgresql.ARRAY(sa.String(length=16)), nullable=True))
0
Example 25
Project: changes Source File: 460741ff1212_add_project_permissions_column_to_user.py
Function: upgrade
Function: upgrade
def upgrade():
op.add_column('user', sa.Column('project_permissions', postgresql.ARRAY(sa.String(length=256)), nullable=True))
0
Example 26
def create_sql_filter(self, data_list):
return RegistrationData.data.has_any(db.func.cast(data_list, ARRAY(db.String)))
0
Example 27
Project: indico Source File: 201508271620_3fcf833adc2d_add_survey_tables.py
def upgrade():
op.execute(CreateSchema('event_surveys'))
op.create_table(
'surveys',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('event_id', sa.Integer(), nullable=False, index=True),
sa.Column('title', sa.String(), nullable=False),
sa.Column('introduction', sa.Text(), nullable=False),
sa.Column('anonymous', sa.Boolean(), nullable=False),
sa.Column('require_user', sa.Boolean(), nullable=False),
sa.Column('submission_limit', sa.Integer(), nullable=True),
sa.Column('start_dt', UTCDateTime, nullable=True),
sa.Column('end_dt', UTCDateTime, nullable=True),
sa.Column('is_deleted', sa.Boolean(), nullable=False),
sa.Column('start_notification_sent', sa.Boolean(), nullable=False),
sa.Column('notifications_enabled', sa.Boolean(), nullable=False),
sa.Column('notify_participants', sa.Boolean(), nullable=False),
sa.Column('start_notification_emails', postgresql.ARRAY(sa.String()), nullable=False),
sa.Column('new_submission_emails', postgresql.ARRAY(sa.String()), nullable=False),
sa.CheckConstraint('anonymous OR require_user', name='valid_anonymous_user'),
sa.ForeignKeyConstraint(['event_id'], ['events.events.id']),
sa.PrimaryKeyConstraint('id'),
schema='event_surveys'
)
op.create_table(
'items',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('survey_id', sa.Integer(), nullable=False, index=True),
sa.Column('parent_id', sa.Integer(), nullable=True, index=True),
sa.Column('position', sa.Integer(), nullable=False),
sa.Column('type', PyIntEnum(SurveyItemType), nullable=False),
sa.Column('title', sa.String(), nullable=True),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('is_required', sa.Boolean(), nullable=True),
sa.Column('field_type', sa.String(), nullable=True),
sa.Column('field_data', postgresql.JSON(), nullable=False),
sa.Column('display_as_section', sa.Boolean(), nullable=True),
sa.CheckConstraint("type != 1 OR (title IS NOT NULL AND is_required IS NOT NULL AND field_type IS NOT NULL AND "
"parent_id IS NOT NULL AND display_as_section IS NULL)",
name='valid_question'),
sa.CheckConstraint("type != 2 OR (title IS NOT NULL AND is_required IS NULL AND field_type IS NULL AND "
"field_data::text = '{}' AND parent_id IS NULL AND display_as_section IS NOT NULL)",
name='valid_section'),
sa.CheckConstraint("type != 3 OR (title IS NULL AND is_required IS NULL AND field_type IS NULL "
"AND field_data::text = '{}' AND parent_id IS NOT NULL AND display_as_section IS NULL)",
name='valid_text'),
sa.ForeignKeyConstraint(['survey_id'], ['event_surveys.surveys.id']),
sa.ForeignKeyConstraint(['parent_id'], ['event_surveys.items.id']),
sa.PrimaryKeyConstraint('id'),
schema='event_surveys'
)
op.create_table(
'submissions',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('survey_id', sa.Integer(), nullable=False, index=True),
sa.Column('user_id', sa.Integer(), nullable=True, index=True),
sa.Column('submitted_dt', UTCDateTime, nullable=False),
sa.ForeignKeyConstraint(['survey_id'], ['event_surveys.surveys.id']),
sa.ForeignKeyConstraint(['user_id'], ['users.users.id']),
sa.PrimaryKeyConstraint('id'),
schema='event_surveys'
)
op.create_table(
'answers',
sa.Column('submission_id', sa.Integer(), nullable=False),
sa.Column('question_id', sa.Integer(), nullable=False),
sa.Column('data', postgresql.JSON(), nullable=False),
sa.ForeignKeyConstraint(['question_id'], ['event_surveys.items.id']),
sa.ForeignKeyConstraint(['submission_id'], ['event_surveys.submissions.id']),
sa.PrimaryKeyConstraint('submission_id', 'question_id'),
schema='event_surveys'
)
0
Example 28
def upgrade():
op.create_table(
'principals',
sa.Column('mp_group_provider', sa.String(), nullable=True),
sa.Column('mp_group_name', sa.String(), nullable=True),
sa.Column('read_access', sa.Boolean(), nullable=False),
sa.Column('full_access', sa.Boolean(), nullable=False),
sa.Column('roles', postgresql.ARRAY(sa.String()), nullable=False),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('event_id', sa.Integer(), nullable=False, index=True),
sa.Column('user_id', sa.Integer(), nullable=True, index=True),
sa.Column('local_group_id', sa.Integer(), nullable=True, index=True),
sa.Column('type', PyIntEnum(PrincipalType), nullable=True),
sa.Column('email', sa.String(), nullable=True, index=True),
sa.Index('ix_uq_principals_user', 'user_id', 'event_id', unique=True, postgresql_where=sa.text('type = 1')),
sa.Index('ix_uq_principals_local_group', 'local_group_id', 'event_id', unique=True,
postgresql_where=sa.text('type = 2')),
sa.Index('ix_uq_principals_mp_group', 'mp_group_provider', 'mp_group_name', 'event_id', unique=True,
postgresql_where=sa.text('type = 3')),
sa.Index('ix_uq_principals_email', 'email', 'event_id', unique=True, postgresql_where=sa.text('type = 4')),
sa.CheckConstraint('type != 1 OR (local_group_id IS NULL AND mp_group_provider IS NULL AND email IS NULL AND '
'mp_group_name IS NULL AND user_id IS NOT NULL)',
name='valid_user'),
sa.CheckConstraint('type != 2 OR (user_id IS NULL AND mp_group_provider IS NULL AND email IS NULL AND '
'mp_group_name IS NULL AND local_group_id IS NOT NULL)',
name='valid_local_group'),
sa.CheckConstraint('type != 3 OR (local_group_id IS NULL AND user_id IS NULL AND email IS NULL AND '
'mp_group_provider IS NOT NULL AND mp_group_name IS NOT NULL)',
name='valid_multipass_group'),
sa.CheckConstraint('type != 4 OR (local_group_id IS NULL AND mp_group_provider IS NULL AND '
'mp_group_name IS NULL AND user_id IS NULL AND email IS NOT NULL)',
name='valid_email'),
sa.CheckConstraint('email IS NULL OR email = lower(email)', name='lowercase_email'),
sa.CheckConstraint('read_access OR full_access OR array_length(roles, 1) IS NOT NULL', name='has_privs'),
sa.ForeignKeyConstraint(['event_id'], ['events.events.id']),
sa.ForeignKeyConstraint(['local_group_id'], ['users.groups.id']),
sa.ForeignKeyConstraint(['user_id'], ['users.users.id']),
sa.PrimaryKeyConstraint('id'),
schema='events'
)
op.create_index(None, 'principals', ['mp_group_provider', 'mp_group_name'], schema='events')
op.add_column('events', sa.Column('protection_mode', PyIntEnum(ProtectionMode), nullable=False,
server_default=str(ProtectionMode.inheriting.value)), schema='events')
op.alter_column('events', 'protection_mode', server_default=None, schema='events')
0
Example 29
Project: indico Source File: 201509301444_f9bb2a03ceb_add_registration_tables.py
def upgrade():
op.execute(CreateSchema('event_registration'))
op.create_table(
'forms',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('event_id', sa.Integer(), nullable=False, index=True),
sa.Column('title', sa.String(), nullable=False),
sa.Column('introduction', sa.Text(), nullable=False),
sa.Column('contact_info', sa.String(), nullable=False),
sa.Column('start_dt', UTCDateTime, nullable=True),
sa.Column('end_dt', UTCDateTime, nullable=True),
sa.Column('modification_mode', PyIntEnum(ModificationMode), nullable=False),
sa.Column('modification_end_dt', UTCDateTime, nullable=True),
sa.Column('is_deleted', sa.Boolean(), nullable=False),
sa.Column('require_login', sa.Boolean(), nullable=False),
sa.Column('require_user', sa.Boolean(), nullable=False),
sa.Column('tickets_enabled', sa.Boolean(), nullable=False),
sa.Column('ticket_on_email', sa.Boolean(), nullable=False),
sa.Column('ticket_on_event_page', sa.Boolean(), nullable=False),
sa.Column('ticket_on_summary_page', sa.Boolean(), nullable=False),
sa.Column('registration_limit', sa.Integer(), nullable=True),
sa.Column('publish_registrations_enabled', sa.Boolean(), nullable=False),
sa.Column('moderation_enabled', sa.Boolean(), nullable=False),
sa.Column('base_price', sa.Numeric(8, 2), nullable=False),
sa.Column('notification_sender_address', sa.String(), nullable=True),
sa.Column('message_pending', sa.Text(), nullable=False),
sa.Column('message_unpaid', sa.Text(), nullable=False),
sa.Column('message_complete', sa.Text(), nullable=False),
sa.Column('manager_notifications_enabled', sa.Boolean(), nullable=False),
sa.Column('manager_notification_recipients', postgresql.ARRAY(sa.String()), nullable=False),
sa.ForeignKeyConstraint(['event_id'], ['events.events.id']),
sa.UniqueConstraint('id', 'event_id'),
sa.PrimaryKeyConstraint('id'),
schema='event_registration'
)
op.create_table(
'form_items',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('registration_form_id', sa.Integer(), nullable=False, index=True),
sa.Column('type', PyIntEnum(RegistrationFormItemType), nullable=False),
sa.Column('personal_data_type', PyIntEnum(PersonalDataType), nullable=True),
sa.Column('parent_id', sa.Integer(), nullable=True, index=True),
sa.Column('position', sa.Integer(), nullable=False),
sa.Column('title', sa.String(), nullable=False),
sa.Column('description', sa.String(), nullable=False),
sa.Column('is_enabled', sa.Boolean(), nullable=False),
sa.Column('is_deleted', sa.Boolean(), nullable=False),
sa.Column('is_required', sa.Boolean(), nullable=False),
sa.Column('is_manager_only', sa.Boolean(), nullable=False),
sa.Column('input_type', sa.String(), nullable=True),
sa.Column('data', postgresql.JSON(), nullable=False),
sa.Column('current_data_id', sa.Integer(), nullable=True, index=True),
sa.CheckConstraint("(input_type IS NULL) = (type NOT IN (2, 5))", name='valid_input'),
sa.CheckConstraint("NOT is_manager_only OR type = 1", name='valid_manager_only'),
sa.CheckConstraint("(type IN (1, 4)) = (parent_id IS NULL)", name='top_level_sections'),
sa.CheckConstraint("(type != 5) = (personal_data_type IS NULL)", name='pd_field_type'),
sa.CheckConstraint("NOT is_deleted OR (type NOT IN (4, 5))", name='pd_not_deleted'),
sa.CheckConstraint("is_enabled OR type != 4", name='pd_section_enabled'),
sa.CheckConstraint("is_enabled OR type != 5 OR personal_data_type NOT IN (1, 2, 3)", name='pd_field_enabled'),
sa.CheckConstraint("is_required OR type != 5 OR personal_data_type NOT IN (1, 2, 3)", name='pd_field_required'),
sa.CheckConstraint("current_data_id IS NULL OR type IN (2, 5)", name='current_data_id_only_field'),
sa.Index('ix_uq_form_items_pd_section', 'registration_form_id', unique=True,
postgresql_where=sa.text('type = 4')),
sa.Index('ix_uq_form_items_pd_field', 'registration_form_id', 'personal_data_type', unique=True,
postgresql_where=sa.text('type = 5')),
sa.ForeignKeyConstraint(['parent_id'], ['event_registration.form_items.id']),
sa.ForeignKeyConstraint(['registration_form_id'], ['event_registration.forms.id']),
sa.PrimaryKeyConstraint('id'),
schema='event_registration'
)
op.create_table(
'form_field_data',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('field_id', sa.Integer(), nullable=False, index=True),
sa.Column('versioned_data', postgresql.JSON(), nullable=False),
sa.ForeignKeyConstraint(['field_id'], ['event_registration.form_items.id']),
sa.PrimaryKeyConstraint('id'),
schema='event_registration'
)
op.create_foreign_key(None,
'form_items', 'form_field_data',
['current_data_id'], ['id'],
source_schema='event_registration', referent_schema='event_registration')
op.create_table(
'registrations',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('friendly_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID, nullable=False, index=True, unique=True),
sa.Column('event_id', sa.Integer(), nullable=False, index=True),
sa.Column('registration_form_id', sa.Integer(), nullable=False, index=True),
sa.Column('user_id', sa.Integer(), nullable=True, index=True),
sa.Column('transaction_id', sa.Integer(), nullable=True, index=True, unique=True),
sa.Column('state', PyIntEnum(RegistrationState), nullable=False),
sa.Column('base_price', sa.Numeric(8, 2), nullable=False),
sa.Column('price_adjustment', sa.Numeric(8, 2), nullable=False),
sa.Column('submitted_dt', UTCDateTime, nullable=False),
sa.Column('email', sa.String(), nullable=False),
sa.Column('first_name', sa.String(), nullable=False),
sa.Column('last_name', sa.String(), nullable=False),
sa.Column('is_deleted', sa.Boolean(), nullable=False),
sa.Column('ticket_uuid', postgresql.UUID(), nullable=False, index=True, unique=True),
sa.Column('checked_in', sa.Boolean(), nullable=False),
sa.Column('checked_in_dt', UTCDateTime, nullable=True),
sa.ForeignKeyConstraint(['registration_form_id'], ['event_registration.forms.id']),
sa.ForeignKeyConstraint(['event_id', 'registration_form_id'],
['event_registration.forms.event_id', 'event_registration.forms.id']),
sa.ForeignKeyConstraint(['user_id'], ['users.users.id']),
sa.CheckConstraint('email = lower(email)', name='lowercase_email'),
sa.Index(None, 'friendly_id', 'event_id', unique=True),
sa.Index(None, 'registration_form_id', 'user_id', unique=True,
postgresql_where=sa.text('NOT is_deleted AND (state NOT IN (3, 4))')),
sa.Index(None, 'registration_form_id', 'email', unique=True,
postgresql_where=sa.text('NOT is_deleted AND (state NOT IN (3, 4))')),
sa.PrimaryKeyConstraint('id'),
schema='event_registration'
)
op.create_table(
'registration_data',
sa.Column('registration_id', sa.Integer(), nullable=False),
sa.Column('field_data_id', sa.Integer(), nullable=False),
sa.Column('data', postgresql.JSONB(), nullable=False),
sa.Column('filename', sa.String(), nullable=True),
sa.Column('content_type', sa.String(), nullable=True),
sa.Column('size', sa.BigInteger(), nullable=True),
sa.Column('storage_backend', sa.String(), nullable=True),
sa.Column('storage_file_id', sa.String(), nullable=True),
sa.ForeignKeyConstraint(['field_data_id'], ['event_registration.form_field_data.id']),
sa.ForeignKeyConstraint(['registration_id'], ['event_registration.registrations.id']),
sa.PrimaryKeyConstraint('registration_id', 'field_data_id'),
schema='event_registration'
)
op.create_table(
'legacy_registration_map',
sa.Column('event_id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('legacy_registrant_id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('legacy_registrant_key', sa.String(), nullable=False),
sa.Column('registration_id', sa.Integer(), nullable=False, index=True),
sa.ForeignKeyConstraint(['event_id'], ['events.events.id']),
sa.ForeignKeyConstraint(['registration_id'], ['event_registration.registrations.id']),
sa.PrimaryKeyConstraint('event_id', 'legacy_registrant_id'),
schema='event_registration'
)
op.create_table(
'invitations',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(), nullable=False, index=True, unique=True),
sa.Column('registration_form_id', sa.Integer(), nullable=False, index=True),
sa.Column('registration_id', sa.Integer(), nullable=True, index=True, unique=True),
sa.Column('state', PyIntEnum(InvitationState), nullable=False),
sa.Column('skip_moderation', sa.Boolean(), nullable=False),
sa.Column('email', sa.String(), nullable=False),
sa.Column('first_name', sa.String(), nullable=False),
sa.Column('last_name', sa.String(), nullable=False),
sa.Column('affiliation', sa.String(), nullable=False),
sa.CheckConstraint('(state = 1) OR (registration_id IS NULL)', name='registration_state'),
sa.UniqueConstraint('registration_form_id', 'email'),
sa.ForeignKeyConstraint(['registration_form_id'], ['event_registration.forms.id']),
sa.ForeignKeyConstraint(['registration_id'], ['event_registration.registrations.id']),
sa.PrimaryKeyConstraint('id'),
schema='event_registration')
op.add_column('events',
sa.Column('last_friendly_registration_id', sa.Integer(), nullable=False, server_default='0'),
schema='events')
op.alter_column('events', 'last_friendly_registration_id', server_default=None, schema='events')
0
Example 30
Project: metrique Source File: sqlalchemy.py
def _sqla_postgresql(self, uri, version=None,
isolation_level="READ COMMITTED"):
'''
expected uri form:
postgresql+psycopg2://%s:%s@%s:%s/%s' % (
username, password, host, port, db)
'''
isolation_level = isolation_level or "READ COMMITTED"
kwargs = dict(isolation_level=isolation_level)
# FIXME: version of postgresql < 9.2 don't have pg.JSON!
# check and use JSONTypedLite instead
# override default dict and list column types
types = {list: pg.ARRAY, tuple: pg.ARRAY, set: pg.ARRAY,
dict: JSONDict, datetime: UTCEpoch}
self.type_map.update(types)
bs = self.config['batch_size']
# 999 batch_size is default for sqlite, postgres handles more at once
self.config['batch_size'] = 5000 if bs == 999 else bs
self._lock_required = False
# default schema name is 'public' for postgres
dsn = self.config['db_schema']
self.config['db_schema'] = dsn or 'public'
return uri, kwargs
0
Example 31
Project: metrique Source File: sqlalchemy.py
def schema2table(name, schema, Base=None, type_map=None, exclude_keys=None):
is_defined(name, "table name must be defined!")
is_defined(schema, "schema must be defined!")
logger.debug('Reusing existing Base (%s)' % Base) if Base else None
Base = Base or declarative_base()
schema = copy(schema)
type_map = copy(type_map or TYPE_MAP)
logger.debug("Attempting to create Table class: %s..." % name)
logger.debug(" ... Schema: %s" % schema)
logger.debug(" ... Type Map: %s" % type_map)
def __repr__(s):
return '%s(%s)' % (
s.__tablename__,
', '.join(['%s=%s' % (k, v) for k, v in s.__dict__.iteritems()
if k != '_sa_instance_state']))
# always exclude the following 'system' keys, as they are
# hard coded and should remain consistent across containers
exclude_keys = list(exclude_keys or [])
exclude_keys.extend(['id', '_id', '_hash', '_start',
'_end', '_v', '__v__', '_e'])
exclude_keys = sorted(set(exclude_keys))
defaults = {
'__tablename__': name,
'__table_args__': ({'extend_existing': True}),
'id': Column('id', Integer, primary_key=True),
'_id': Column(CoerceUTF8, nullable=False, unique=True, index=True),
'_oid': Column(BigInteger, nullable=False, index=True,
unique=False),
'_hash': Column(CoerceUTF8, nullable=False, index=True),
'_start': Column(type_map[datetime], index=True,
nullable=False),
'_end': Column(type_map[datetime], index=True),
'_v': Column(Integer, default=0, nullable=False),
'__v__': Column(CoerceUTF8, default=__version__, nullable=False),
'_e': Column(type_map[dict]),
'__repr__': __repr__,
}
for k, v in schema.items():
if k in exclude_keys:
warnings.warn(
'restricted schema key detected %s; ignoring!' % k)
continue
__type = v.get('type')
if __type is None:
__type = type(None)
_type = type_map.get(__type)
if v.get('container', False):
_list_type = type_map[list]
if _list_type is pg.ARRAY:
_list_type = _list_type(_type)
defaults[k] = Column(_list_type)
elif k == '_oid':
# in case _oid is defined in the schema,
# make sure we index it and it's unique
defaults[k] = Column(_type, nullable=False, index=True,
unique=False)
else:
defaults[k] = Column(_type, name=k)
logger.debug(" ... Table Schema Final: %s" % defaults)
_table = type(str(name), (Base,), defaults)
return _table
0
Example 32
def __init__(self, arg, default=None, **kw):
self.type = postgresql.ARRAY(arg.type)
self.default = default
GenericFunction.__init__(self, arg, **kw)
0
Example 33
def ancestor_of(self, other):
if isinstance(other, list):
return self.op('@>')(expression.cast(other, ARRAY(LtreeType)))
else:
return self.op('@>')(other)
0
Example 34
def descendant_of(self, other):
if isinstance(other, list):
return self.op('<@')(expression.cast(other, ARRAY(LtreeType)))
else:
return self.op('<@')(other)
0
Example 35
Project: sqlalchemy-utils Source File: ltree.py
def lquery(self, other):
if isinstance(other, list):
return self.op('?')(expression.cast(other, ARRAY(LQUERY)))
else:
return self.op('~')(other)
0
Example 36
@pytest.fixture()
def User(Base):
class User(Base):
__tablename__ = 'user'
id = sa.Column('_id', sa.Integer, primary_key=True)
name = sa.Column('_name', sa.String(20))
age = sa.Column('_age', sa.Integer, nullable=False)
email = sa.Column(
'_email', sa.String(200), nullable=False, unique=True
)
fav_numbers = sa.Column('_fav_numbers', ARRAY(sa.Integer))
__table_args__ = (
sa.CheckConstraint(sa.and_(age >= 0, age <= 150)),
sa.CheckConstraint(
sa.and_(
sa.func.array_length(fav_numbers, 1) <= 8
)
)
)
return User
0
Example 37
def test_type(self):
assert isinstance(
sa.func.array_agg(sa.text('u.name')).type,
postgresql.ARRAY
)
0
Example 38
Project: socorro Source File: 36abe253f8b3_fixes_902142_partiti.py
def upgrade():
op.add_column(u'report_partition_info', sa.Column(u'partition_column', sa.TEXT()))
# Drop FK constraints on parent tables
op.drop_constraint('signature_summary_architecture_product_version_id_fkey', 'signature_summary_architecture')
op.drop_constraint('signature_summary_architecture_signature_id_fkey', 'signature_summary_architecture')
op.drop_constraint('signature_summary_flash_version_product_version_id_fkey', 'signature_summary_flash_version')
op.drop_constraint('signature_summary_flash_version_signature_id_fkey', 'signature_summary_flash_version')
op.drop_constraint('signature_summary_installations_signature_id_fkey', 'signature_summary_installations')
op.drop_constraint('signature_summary_os_product_version_id_fkey', 'signature_summary_os')
op.drop_constraint('signature_summary_os_signature_id_fkey', 'signature_summary_os')
op.drop_constraint('signature_summary_process_type_product_version_id_fkey', 'signature_summary_process_type')
op.drop_constraint('signature_summary_process_type_signature_id_fkey', 'signature_summary_process_type')
op.drop_constraint('signature_summary_products_product_version_id_fkey', 'signature_summary_products')
op.drop_constraint('signature_summary_products_signature_id_fkey', 'signature_summary_products')
op.drop_constraint('signature_summary_uptime_product_version_id_fkey', 'signature_summary_uptime')
op.drop_constraint('signature_summary_uptime_signature_id_fkey', 'signature_summary_uptime')
app_path=os.getcwd()
procs = ['weekly_report_partitions.sql',
'backfill_weekly_report_partitions.sql']
for myfile in [app_path + '/socorro/external/postgresql/raw_sql/procs/' + line for line in procs]:
proc = open(myfile, 'r').read()
op.execute(proc)
# Now run this against the raw_crashes table
op.execute("""
UPDATE report_partition_info
SET partition_column = 'date_processed'
""")
report_partition_info = table(u'report_partition_info',
column(u'build_order', sa.INTEGER()),
column(u'fkeys', postgresql.ARRAY(sa.TEXT())),
column(u'indexes', postgresql.ARRAY(sa.TEXT())),
column(u'keys', postgresql.ARRAY(sa.TEXT())),
column(u'table_name', CITEXT()),
column(u'partition_column', sa.TEXT()),
)
op.bulk_insert(report_partition_info, [
{'table_name': u'signature_summary_installations',
'build_order': 5,
'fkeys': ["(signature_id) REFERENCES signatures(signature_id)"],
'partition_column': 'report_date',
'keys': ["signature_id,product_name,version_string,report_date"],
'indexes': ["report_date"],
},
{'table_name': u'signature_summary_architecture',
'build_order': 6,
'fkeys': ["(signature_id) REFERENCES signatures(signature_id)", "(product_version_id) REFERENCES product_versions(product_version_id)"],
'partition_column': 'report_date',
'keys': ["signature_id, architecture, product_version_id, report_date"],
'indexes': ["report_date"],
},
{'table_name': u'signature_summary_flash_version',
'build_order': 7,
'fkeys': ["(signature_id) REFERENCES signatures(signature_id)", "(product_version_id) REFERENCES product_versions(product_version_id)"],
'partition_column': 'report_date',
'keys': ["signature_id, flash_version, product_version_id, report_date"],
'indexes': ["report_date"],
},
{'table_name': u'signature_summary_os',
'build_order': 8,
'fkeys': ["(signature_id) REFERENCES signatures(signature_id)", "(product_version_id) REFERENCES product_versions(product_version_id)"],
'partition_column': 'report_date',
'keys': ["signature_id, os_version_string, product_version_id, report_date"],
'indexes': ["report_date"],
},
{'table_name': u'signature_summary_process_type',
'build_order': 9,
'fkeys': ["(signature_id) REFERENCES signatures(signature_id)", "(product_version_id) REFERENCES product_versions(product_version_id)"],
'partition_column': 'report_date',
'keys': ["signature_id, process_type, product_version_id, report_date"],
'indexes': ["report_date"],
},
{'table_name': u'signature_summary_products',
'build_order': 10,
'fkeys': ["(signature_id) REFERENCES signatures(signature_id)", "(product_version_id) REFERENCES product_versions(product_version_id)"],
'partition_column': 'report_date',
'keys': ["signature_id, product_version_id, report_date"],
'indexes': ["report_date"],
},
{'table_name': u'signature_summary_uptime',
'build_order': 11,
'fkeys': ["(signature_id) REFERENCES signatures(signature_id)", "(product_version_id) REFERENCES product_versions(product_version_id)"],
'partition_column': 'report_date',
'keys': ["signature_id, uptime_string, product_version_id, report_date"],
'indexes': ["report_date"],
},
])
op.alter_column(u'report_partition_info', u'partition_column',
existing_type=sa.TEXT(),
nullable=False)
op.execute("""
SELECT backfill_weekly_report_partitions('2013-06-03', '2013-08-12', 'signature_summary_architecture')
""")
op.execute("""
SELECT backfill_weekly_report_partitions('2013-06-03', '2013-08-12', 'signature_summary_flash_version')
""")
op.execute("""
SELECT backfill_weekly_report_partitions('2013-06-03', '2013-08-12', 'signature_summary_installations')
""")
op.execute("""
SELECT backfill_weekly_report_partitions('2013-06-03', '2013-08-12', 'signature_summary_os')
""")
op.execute("""
SELECT backfill_weekly_report_partitions('2013-06-03', '2013-08-12', 'signature_summary_process_type')
""")
op.execute("""
SELECT backfill_weekly_report_partitions('2013-06-03', '2013-08-12', 'signature_summary_products')
""")
op.execute("""
SELECT backfill_weekly_report_partitions('2013-06-03', '2013-08-12', 'signature_summary_uptime')
""")
0
Example 39
def downgrade():
op.create_table(u'daily_hangs',
sa.Column(u'browser_signature_id', sa.INTEGER(), autoincrement=False, nullable=False),
sa.Column(u'duplicates', postgresql.ARRAY(sa.TEXT()), autoincrement=False, nullable=True),
sa.Column(u'flash_version_id', sa.INTEGER(), autoincrement=False, nullable=True),
sa.Column(u'hang_id', sa.TEXT(), autoincrement=False, nullable=False),
sa.Column(u'plugin_signature_id', sa.INTEGER(), autoincrement=False, nullable=False),
sa.Column(u'plugin_uuid', sa.TEXT(), autoincrement=False, nullable=False),
sa.Column(u'product_version_id', sa.INTEGER(), autoincrement=False, nullable=False),
sa.Column(u'report_date', sa.DATE(), autoincrement=False, nullable=True),
sa.Column(u'url', CITEXT(), autoincrement=False, nullable=True),
sa.Column(u'uuid', sa.TEXT(), autoincrement=False, nullable=False),
sa.PrimaryKeyConstraint(u'plugin_uuid', name=u'daily_hangs_pkey')
)
op.execute("""
CREATE VIEW hang_report AS
SELECT product_versions.product_name AS product,
product_versions.version_string AS version,
browser_signatures.signature AS browser_signature,
plugin_signatures.signature AS plugin_signature,
daily_hangs.hang_id AS browser_hangid,
flash_versions.flash_version,
daily_hangs.url, daily_hangs.uuid,
daily_hangs.duplicates,
daily_hangs.report_date AS report_day
FROM ((((daily_hangs JOIN product_versions USING (product_version_id))
JOIN signatures browser_signatures ON
((daily_hangs.browser_signature_id = browser_signatures.signature_id)))
JOIN signatures plugin_signatures ON
((daily_hangs.plugin_signature_id = plugin_signatures.signature_id)))
LEFT JOIN flash_versions USING (flash_version_id))
""")
0
Example 40
def upgrade():
op.create_table(
u'crontabber',
sa.Column(u'app_name', sa.TEXT(), nullable=False),
sa.Column(u'next_run', sa.TIMESTAMP(timezone=True)),
sa.Column(u'first_run', sa.TIMESTAMP(timezone=True)),
sa.Column(u'last_run', sa.TIMESTAMP(timezone=True)),
sa.Column(u'last_success', sa.TIMESTAMP(timezone=True)),
sa.Column(u'error_count', sa.INTEGER(), nullable=False,
server_default='0'),
sa.Column(u'depends_on', postgresql.ARRAY(sa.TEXT())),
sa.Column(u'last_error', jsontype.JsonType()),
sa.PrimaryKeyConstraint(u'app_name')
)
op.create_index('crontabber_app_name_idx', 'crontabber', ['app_name'])
op.create_table(
u'crontabber_log',
sa.Column(u'id', sa.INTEGER(), nullable=False),
sa.Column(u'app_name', sa.TEXT(), nullable=False),
sa.Column(u'log_time', sa.TIMESTAMP(timezone=True), nullable=False,
server_default=sa.text('NOW()')),
sa.Column(u'success', sa.TIMESTAMP(timezone=True)),
sa.Column(u'exc_type', sa.TEXT()),
sa.Column(u'exc_value', sa.TEXT()),
sa.Column(u'exc_traceback', sa.TEXT()),
sa.PrimaryKeyConstraint(u'id')
)
op.create_index('crontabber_log_app_name_idx',
'crontabber_log', ['app_name'])
op.create_index('crontabber_log_log_time_idx',
'crontabber_log', ['log_time'])
0
Example 41
Project: koschei Source File: 18d4f8beaba6_store_dependencies_as_compressed_many_.py
def upgrade():
op.execute("""\
CREATE INDEX tmp ON dependency(name,epoch,version,release,arch);
CREATE TABLE deptmp AS SELECT DISTINCT build_id, min(id) OVER (PARTITION BY name, epoch, version, release, arch) AS newid FROM dependency;
ALTER TABLE build ADD COLUMN dependency_array integer[];
UPDATE build SET dependency_array = a FROM (SELECT build_id, array_agg(newid ORDER BY newid) AS a FROM deptmp GROUP BY build_id) AS q WHERE q.build_id = build.id;
DROP INDEX tmp;
DROP INDEX ix_dependency_build_id;
CREATE TABLE deptmp2 AS SELECT DISTINCT newid FROM deptmp;
DELETE FROM dependency WHERE NOT EXISTS (SELECT 1 FROM deptmp2 WHERE deptmp2.newid=dependency.id);
ALTER TABLE dependency DROP COLUMN build_id;
ALTER TABLE dependency DROP COLUMN distance;
DROP TABLE deptmp;
DROP TABLE deptmp2;
ALTER TABLE build ADD COLUMN dependency_keys bytea;
CREATE UNIQUE INDEX ix_dependency_composite ON dependency(name, epoch, version, release, arch);
""")
connection = op.get_bind()
build_table = sa.Table('build', sa.MetaData(),
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('dependency_array', ARRAY(sa.Integer)),
sa.Column('dependency_keys', CompressedKeyArray))
updated = []
def persist():
connection.execute(build_table.update().where(build_table.c.id == bindparam('i'))\
.values({'id': bindparam('i'), 'dependency_keys': bindparam('d')}),
updated)
updated[:] = []
for row in connection.execution_options(stream_results=True)\
.execute("SELECT id, dependency_array FROM build WHERE dependency_array IS NOT NULL"):
updated.append({'i': row.id, 'd': row.dependency_array})
if len(updated) > 1000:
persist()
if updated:
persist()
op.execute("ALTER TABLE build DROP COLUMN dependency_array")
0
Example 42
def upgrade():
op.create_table(
'cluster_state',
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('deployment_tags', postgresql.ARRAY(sa.String(length=64)),
nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table(
'test_sets',
sa.Column('id', sa.String(length=128), nullable=False),
sa.Column('description', sa.String(length=256), nullable=True),
sa.Column('test_path', sa.String(length=256), nullable=True),
sa.Column('driver', sa.String(length=128), nullable=True),
sa.Column('additional_arguments', fields.ListField(), nullable=True),
sa.Column('cleanup_path', sa.String(length=128), nullable=True),
sa.Column('meta', fields.JsonField(), nullable=True),
sa.Column('deployment_tags', postgresql.ARRAY(sa.String(length=64)),
nullable=True),
sa.Column('test_runs_ordering_priority', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table(
'cluster_testing_pattern',
sa.Column('cluster_id', sa.Integer(), nullable=False),
sa.Column('test_set_id', sa.String(length=128), nullable=False),
sa.Column('tests', postgresql.ARRAY(sa.String(length=512)),
nullable=True),
sa.ForeignKeyConstraint(['cluster_id'], ['cluster_state.id'], ),
sa.ForeignKeyConstraint(['test_set_id'], ['test_sets.id'], ),
sa.PrimaryKeyConstraint('cluster_id', 'test_set_id')
)
op.create_table(
'test_runs',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('status',
sa.Enum('running', 'finished', name='test_run_states'),
nullable=False),
sa.Column('meta', fields.JsonField(), nullable=True),
sa.Column('started_at', sa.DateTime(), nullable=True),
sa.Column('ended_at', sa.DateTime(), nullable=True),
sa.Column('test_set_id', sa.String(length=128), nullable=True),
sa.Column('cluster_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['test_set_id', 'cluster_id'],
['cluster_testing_pattern.test_set_id',
'cluster_testing_pattern.cluster_id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
op.create_table(
'tests',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=512), nullable=True),
sa.Column('title', sa.String(length=512), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('duration', sa.String(length=512), nullable=True),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('traceback', sa.Text(), nullable=True),
sa.Column('status', sa.Enum('wait_running', 'running', 'failure',
'success', 'error', 'stopped',
'disabled', 'skipped', name='test_states'),
nullable=True),
sa.Column('step', sa.Integer(), nullable=True),
sa.Column('time_taken', sa.Float(), nullable=True),
sa.Column('meta', fields.JsonField(), nullable=True),
sa.Column('deployment_tags', postgresql.ARRAY(sa.String(length=64)),
nullable=True),
sa.Column('test_run_id', sa.Integer(), nullable=True),
sa.Column('test_set_id', sa.String(length=128), nullable=True),
sa.ForeignKeyConstraint(['test_run_id'], ['test_runs.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['test_set_id'], ['test_sets.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)