def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('users',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('email_address', sa.Unicode(length=255), nullable=False),
sa.Column('first_name', sa.Unicode(length=255), nullable=False),
sa.Column('last_name', sa.Unicode(length=255), nullable=True),
sa.Column('password', sa.Unicode(length=128), nullable=False),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('created_ip', postgresql.INET(), nullable=True),
sa.Column('login_time', sa.DateTime(), nullable=True),
sa.Column('login_ip', postgresql.INET(), nullable=True),
sa.Column('recover_key', sa.Integer(), nullable=True),
sa.Column('recover_time', sa.DateTime(), nullable=True),
sa.Column('recover_ip', postgresql.INET(), nullable=True),
sa.Column('admin', sa.Boolean(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('urls',
sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('short_url', sa.Unicode(length=15), nullable=False),
sa.Column('long_url', sa.Unicode(length=255), nullable=False),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('expires_at', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
op.create_index('urls_short_url_idx', 'urls', ['short_url'], unique=True)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'asn',
sa.Column('asn', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('as_name', sa.String(length=255), nullable=True),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('asn'))
op.create_table(
'community',
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('name', sa.String(length=255), nullable=True),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'prefix', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('asn_id', sa.Integer(), nullable=False),
sa.Column('asn_path',
postgresql.ARRAY(sa.Integer(), dimensions=1),
nullable=True),
sa.Column('prefix', postgresql.CIDR(), nullable=False),
sa.Column('next_hops',
postgresql.ARRAY(postgresql.INET(), dimensions=1),
nullable=True),
sa.Column('neighbor', postgresql.INET(), nullable=True),
sa.Column('ixp',
sa.String(length=255),
server_default='N/A',
nullable=True),
sa.Column('last_seen', sa.DateTime(), nullable=True),
sa.Column('age', sa.DateTime(), nullable=True),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['asn_id'],
['asn.asn'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'prefix_communities',
sa.Column('prefix_id', sa.Integer(), nullable=False),
sa.Column('community_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['community_id'],
['community.id'],
), sa.ForeignKeyConstraint(
['prefix_id'],
['prefix.id'],
), sa.PrimaryKeyConstraint('prefix_id', 'community_id'))
class Request(db.Model):
__tablename__ = 'requests'
id = db.Column(db.Integer(), primary_key=True)
headers = db.Column(db.Unicode())
body = db.Column(db.Unicode())
ip = db.Column(postgresql.INET())
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(
'apiaccesstok',
sa.Column('id',
sa.INTEGER(),
server_default="nextval('apiaccesstok_id_seq'::regclass)",
nullable=False),
sa.Column('token',
sa.VARCHAR(length=32),
autoincrement=False,
nullable=False),
sa.Column('label',
sa.VARCHAR(length=256),
autoincrement=False,
nullable=True),
sa.Column('remote_addr',
postgresql.INET(),
autoincrement=False,
nullable=True),
sa.Column('expire_date',
postgresql.TIMESTAMP(),
autoincrement=False,
nullable=True),
sa.PrimaryKeyConstraint('id', name='apiaccesstok_pkey'))
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('host_service_mapping',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('service', sa.String(), nullable=True),
sa.Column('pattern', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('user',
sa.Column('username', sa.String(), nullable=False),
sa.Column('password', sa.String(), nullable=True),
sa.Column('authenticated', sa.Boolean(), nullable=True),
sa.Column('role', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('username')
)
op.create_table('access_log',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('username', sa.String(), nullable=True),
sa.Column('created_at', sa.DateTime(), server_default=sa.text(u"timezone('utc'::text, now())"), nullable=True),
sa.Column('hostname', sa.String(), nullable=True),
sa.Column('protocol', sa.String(), nullable=True),
sa.ForeignKeyConstraint(['username'], ['user.username'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('connect_log',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('username', sa.String(), nullable=True),
sa.Column('created_at', sa.DateTime(), server_default=sa.text(u"timezone('utc'::text, now())"), nullable=True),
sa.Column('status', sa.String(length=30), nullable=True),
sa.Column('interface', sa.String(length=30), nullable=True),
sa.Column('local_ip', postgresql.INET(), nullable=True),
sa.Column('remote_ip', postgresql.INET(), nullable=True),
sa.ForeignKeyConstraint(['username'], ['user.username'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('sessions',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('username', sa.String(), nullable=True),
sa.Column('started_at', sa.DateTime(), nullable=True),
sa.Column('ended_at', sa.DateTime(), nullable=True),
sa.Column('interface', sa.String(length=30), nullable=True),
sa.Column('local_ip', postgresql.INET(), nullable=True),
sa.Column('remote_ip', postgresql.INET(), nullable=True),
sa.ForeignKeyConstraint(['username'], ['user.username'], ),
sa.PrimaryKeyConstraint('id')
)
class CIDR(types.TypeDecorator):
"""An SQLAlchemy type representing a CIDR definition."""
impl = types.String(43).with_variant(postgresql.INET(), 'postgresql')
def process_bind_param(self, value, dialect):
"""Process/Formats the value before insert it into the db."""
# NOTE(sdague): normalize all the inserts
if utils.is_valid_ipv6_cidr(value):
return utils.get_shortened_ipv6_cidr(value)
return value
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(
'update_request', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('platform', sa.String(), nullable=True),
sa.Column('channel', sa.String(), nullable=True),
sa.Column('version', sa.String(), nullable=True),
sa.Column('query_time', sa.DateTime(), nullable=True),
sa.Column('ip', postgresql.INET(), nullable=True),
sa.Column('install_id', sa.String(length=36), nullable=True),
sa.PrimaryKeyConstraint('id'))
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(
'ip_bans', sa.Column('address', postgresql.INET(), nullable=False),
sa.Column('date', sa.DateTime(), nullable=False),
sa.Column('creator_id', sa.Integer(), nullable=False),
sa.Column('reason', sa.Unicode(length=255), nullable=False),
sa.ForeignKeyConstraint(
['creator_id'],
['users.id'],
), sa.PrimaryKeyConstraint('address'))
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column(
'node',
sa.Column('is_active',
sa.Boolean(),
server_default='1',
nullable=False))
op.add_column('node', sa.Column('last_ip',
postgresql.INET(),
nullable=True))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
"searches",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("time", sa.DateTime(), nullable=False),
sa.Column("description", sa.String(), nullable=True),
sa.Column("location", sa.String(), nullable=True),
sa.Column("full_time", sa.Boolean(), nullable=True),
sa.Column("ip_address", postgresql.INET(), nullable=True),
sa.PrimaryKeyConstraint("id"),
)
class Prefix(db.Model):
id = db.Column(db.Integer, primary_key=True)
asn_id = db.Column(db.Integer, db.ForeignKey('asn.asn'), nullable=False)
asn_path = db.Column(postgresql.ARRAY(db.Integer, dimensions=1),
nullable=True)
prefix = db.Column(postgresql.CIDR(), nullable=False)
next_hops = db.Column(postgresql.ARRAY(postgresql.INET(), dimensions=1),
nullable=True)
neighbor = db.Column(postgresql.INET(), nullable=True)
ixp = db.Column(db.String(255), default='N/A', server_default='N/A')
last_seen = db.Column(db.DateTime, nullable=True)
age = db.Column(db.DateTime, nullable=True)
communities = db.relationship('Community',
secondary=prefix_communities,
lazy='subquery',
backref=db.backref('prefixes', lazy=True))
created_at = db.Column(db.DateTime(), default=datetime.datetime.utcnow)
updated_at = db.Column(db.DateTime(),
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow)
def upgrade():
op.add_column('tokens',
sa.Column('last_used_ip', postgresql.INET(), nullable=True),
schema='users')
op.add_column('tokens',
sa.Column('last_used_ip', postgresql.INET(), nullable=True),
schema='oauth')
op.add_column('tokens',
sa.Column('use_count',
sa.Integer(),
nullable=False,
server_default='0'),
schema='users')
op.add_column('tokens',
sa.Column('use_count',
sa.Integer(),
nullable=False,
server_default='0'),
schema='oauth')
op.alter_column('tokens', 'use_count', server_default=None, schema='users')
op.alter_column('tokens', 'use_count', server_default=None, schema='oauth')
class IPAddress(types.TypeDecorator):
"""An SQLAlchemy type representing an IP-address."""
impl = types.String(39).with_variant(postgresql.INET(), 'postgresql')
def process_bind_param(self, value, dialect):
"""Process/Formats the value before insert it into the db."""
if dialect.name == 'postgresql':
return value
# NOTE(maurosr): The purpose here is to convert ipv6 to the shortened
# form, not validate it.
elif utils.is_valid_ipv6(value):
return utils.get_shortened_ipv6(value)
return value
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'session_data', sa.Column('id_', sa.Integer(), nullable=False),
sa.Column('id_user', sa.Integer(), nullable=False),
sa.Column('user_agent', sa.Text(), nullable=True),
sa.Column('ip', postgresql.INET(), nullable=False),
sa.Column('time', sa.DateTime(), nullable=False),
sa.Column('expire', sa.DateTime(), nullable=False),
sa.Column('session_id', sa.String(length=128), nullable=False),
sa.ForeignKeyConstraint(
['id_user'],
['user_data.id_'],
), sa.PrimaryKeyConstraint('id_'), sa.UniqueConstraint('session_id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'ip_address', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('authorising_user_id', sa.Integer(), nullable=True),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('updated', sa.DateTime(), nullable=True),
sa.Column('_ip', postgresql.INET(), nullable=True),
sa.Column('country', sa.String(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
"sia_node",
sa.Column("id", postgresql.UUID(), nullable=False),
sa.Column("accepting_contracts", sa.Boolean(), nullable=True),
sa.Column("max_download_batch_size", sa.BigInteger(), nullable=True),
sa.Column("max_duration", sa.BigInteger(), nullable=True),
sa.Column("max_revise_batch_size", sa.BigInteger(), nullable=True),
sa.Column("net_address", sa.String(), nullable=False),
sa.Column("remaining_storage", sa.BigInteger(), nullable=True),
sa.Column("sector_size", sa.BigInteger(), nullable=True),
sa.Column("total_storage", sa.BigInteger(), nullable=True),
sa.Column("unlock_hash", sa.String(), nullable=True),
sa.Column("window_size", sa.BigInteger(), nullable=True),
sa.Column("collateral", sa.String(), nullable=True),
sa.Column("max_collateral", sa.String(), nullable=True),
sa.Column("contract_price", sa.String(), nullable=True),
sa.Column("download_bandwidth_price", sa.String(), nullable=True),
sa.Column("storage_price", sa.String(), nullable=True),
sa.Column("upload_bandwidth_price", sa.String(), nullable=True),
sa.Column("revision_number", sa.BigInteger(), nullable=True),
sa.Column("version", sa.String(), nullable=True),
sa.Column("first_seen", sa.BigInteger(), nullable=True),
sa.Column("historic_downtime", sa.BigInteger(), nullable=True),
sa.Column("historic_uptime", sa.BigInteger(), nullable=True),
sa.Column("historic_failed_interactions",
sa.BigInteger(),
nullable=True),
sa.Column("historic_successful_interactions",
sa.BigInteger(),
nullable=True),
sa.Column("recent_failed_interactions", sa.BigInteger(),
nullable=True),
sa.Column("recent_successful_interactions",
sa.BigInteger(),
nullable=True),
sa.Column("last_historic_update", sa.BigInteger(), nullable=True),
sa.Column("public_key_string", sa.String(), nullable=False),
sa.Column("ipnets", postgresql.ARRAY(postgresql.INET()),
nullable=True),
sa.Column("country", sa.String(), nullable=True),
sa.Column("city", sa.String(), nullable=True),
sa.Column("latitude", sa.Float(), nullable=True),
sa.Column("longitude", sa.Float(), nullable=True),
sa.PrimaryKeyConstraint("id"),
)
class Sales(Base):
__tablename__ = 'sales'
id = sa.Column(
postgresql_types.BIGINT(),
primary_key=True,
unique=True,
nullable=False,
autoincrement=True,
doc="testtest",
)
book_id = sa.Column(
postgresql_types.UUID(),
sa.ForeignKey(column="book.id", ondelete="CASCADE"),
primary_key=False,
unique=False,
nullable=False,
doc="testtest",
)
sold = sa.Column(
postgresql_types.TIMESTAMP(),
primary_key=False,
unique=False,
nullable=False,
doc="testtest",
)
reservation = sa.Column(
postgresql_types.INTERVAL(),
primary_key=False,
unique=False,
nullable=True,
doc="testtest",
)
source = sa.Column(
postgresql_types.INET(),
primary_key=False,
unique=False,
nullable=True,
doc="testtest",
)
book = sa.orm.relationship(
'Book',
foreign_keys="[sales.c.book_id]",
remote_side=None,
backref="sales",
)
def upgrade():
op.create_table('identities',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('provider', sa.String(), nullable=False),
sa.Column('identifier', sa.String(), nullable=False),
sa.Column('multipass_data',
postgresql.JSON(),
nullable=False),
sa.Column('data', postgresql.JSON(), nullable=False),
sa.Column('password_hash', sa.String(), nullable=True),
sa.Column('last_login_dt', UTCDateTime, nullable=True),
sa.Column('last_login_ip',
postgresql.INET(),
nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['users.users.id']),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('provider', 'identifier'),
schema='users')
def upgrade():
op.create_table('api_keys',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('token', postgresql.UUID(), nullable=False),
sa.Column('secret', postgresql.UUID(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False, index=True),
sa.Column('is_active', sa.Boolean(), nullable=False),
sa.Column('is_blocked', sa.Boolean(), nullable=False),
sa.Column('is_persistent_allowed', sa.Boolean(), nullable=False),
sa.Column('created_dt', UTCDateTime, nullable=False),
sa.Column('last_used_dt', UTCDateTime, nullable=True),
sa.Column('last_used_ip', postgresql.INET(), nullable=True),
sa.Column('last_used_uri', sa.String(), nullable=True),
sa.Column('last_used_auth', sa.Boolean(), nullable=True),
sa.Column('use_count', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('token'),
schema='indico')
op.create_index(None, 'api_keys', ['user_id'], unique=True, schema='indico', postgresql_where=sa.text('is_active'))
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(
'user_log', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('ipaddr', postgresql.INET(), nullable=False),
sa.Column('timestamp', sa.DateTime(), nullable=False),
sa.Column('uid', sa.String(length=32), nullable=False),
sa.Column('method',
sa.Enum('GET',
'HEAD',
'POST',
'PUT',
'DELETE',
'CONNECT',
'OPTIONS',
'TRACE',
'PATCH',
name='http_enum'),
nullable=True),
sa.Column('blueprint', sa.String(length=32), nullable=False),
sa.Column('path', sa.String(length=128), nullable=False),
sa.Column('description', sa.String(length=128), nullable=False),
sa.PrimaryKeyConstraint('id'))
def load_dialect_impl(self, dialect):
if dialect.name == 'postgresql':
return dialect.type_descriptor(postgresql.INET())
else:
return dialect.type_descriptor(types.String(43))
def upgrade():
op.execute('CREATE EXTENSION IF NOT EXISTS "pgcrypto";')
op.create_table(
'database_status',
sa.Column('pk',
postgresql.UUID(as_uuid=True),
server_default=sa.text('gen_random_uuid()'),
nullable=False),
sa.Column('source', sa.String(), nullable=False),
sa.Column('serial_oldest_seen', sa.Integer(), nullable=True),
sa.Column('serial_newest_seen', sa.Integer(), nullable=True),
sa.Column('serial_oldest_journal', sa.Integer(), nullable=True),
sa.Column('serial_newest_journal', sa.Integer(), nullable=True),
sa.Column('serial_last_dump', sa.Integer(), nullable=True),
sa.Column('force_reload', sa.Boolean(), nullable=False),
sa.Column('last_error', sa.Text(), nullable=True),
sa.Column('last_error_timestamp',
sa.DateTime(timezone=True),
nullable=True),
sa.Column('created',
sa.DateTime(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.Column('updated',
sa.DateTime(timezone=True),
server_default=sa.text('now()'),
nullable=False), sa.PrimaryKeyConstraint('pk'))
op.create_index(op.f('ix_database_status_source'),
'database_status', ['source'],
unique=True)
op.create_table(
'rpsl_database_journal',
sa.Column('pk',
postgresql.UUID(as_uuid=True),
server_default=sa.text('gen_random_uuid()'),
nullable=False),
sa.Column('rpsl_pk', sa.String(), nullable=False),
sa.Column('source', sa.String(), nullable=False),
sa.Column('serial_nrtm', sa.Integer(), nullable=False),
sa.Column('operation',
sa.Enum('add_or_update', 'delete', name='databaseoperation'),
nullable=False),
sa.Column('object_class', sa.String(), nullable=False),
sa.Column('object_text', sa.Text(), nullable=False),
sa.Column('timestamp',
sa.DateTime(timezone=True),
server_default=sa.text('now()'),
nullable=False), sa.PrimaryKeyConstraint('pk'),
sa.UniqueConstraint(
'serial_nrtm',
'source',
name='rpsl_objects_history_serial_nrtm_source_unique'))
op.create_index(op.f('ix_rpsl_database_journal_object_class'),
'rpsl_database_journal', ['object_class'],
unique=False)
op.create_index(op.f('ix_rpsl_database_journal_rpsl_pk'),
'rpsl_database_journal', ['rpsl_pk'],
unique=False)
op.create_index(op.f('ix_rpsl_database_journal_serial_nrtm'),
'rpsl_database_journal', ['serial_nrtm'],
unique=False)
op.create_index(op.f('ix_rpsl_database_journal_source'),
'rpsl_database_journal', ['source'],
unique=False)
op.create_table(
'rpsl_objects',
sa.Column('pk',
postgresql.UUID(as_uuid=True),
server_default=sa.text('gen_random_uuid()'),
nullable=False),
sa.Column('rpsl_pk', sa.String(), nullable=False),
sa.Column('source', sa.String(), nullable=False),
sa.Column('object_class', sa.String(), nullable=False),
sa.Column('parsed_data',
postgresql.JSONB(astext_type=sa.Text()),
nullable=False),
sa.Column('object_text', sa.Text(), nullable=False),
sa.Column('ip_version', sa.Integer(), nullable=True),
sa.Column('ip_first', postgresql.INET(), nullable=True),
sa.Column('ip_last', postgresql.INET(), nullable=True),
sa.Column('ip_size', sa.DECIMAL(scale=0), nullable=True),
sa.Column('asn_first', sa.BigInteger(), nullable=True),
sa.Column('asn_last', sa.BigInteger(), nullable=True),
sa.Column('created',
sa.DateTime(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.Column('updated',
sa.DateTime(timezone=True),
server_default=sa.text('now()'),
nullable=False), sa.PrimaryKeyConstraint('pk'),
sa.UniqueConstraint('rpsl_pk',
'source',
name='rpsl_objects_rpsl_pk_source_unique'))
op.create_index(op.f('ix_rpsl_objects_ip_first'),
'rpsl_objects', ['ip_first'],
unique=False)
op.create_index(op.f('ix_rpsl_objects_ip_last'),
'rpsl_objects', ['ip_last'],
unique=False)
op.create_index(op.f('ix_rpsl_objects_asn_first'),
'rpsl_objects', ['asn_first'],
unique=False)
op.create_index(op.f('ix_rpsl_objects_asn_last'),
'rpsl_objects', ['asn_last'],
unique=False)
op.create_index(op.f('ix_rpsl_objects_ip_version'),
'rpsl_objects', ['ip_version'],
unique=False)
op.create_index(op.f('ix_rpsl_objects_object_class'),
'rpsl_objects', ['object_class'],
unique=False)
op.create_index(op.f('ix_rpsl_objects_rpsl_pk'),
'rpsl_objects', ['rpsl_pk'],
unique=False)
op.create_index(op.f('ix_rpsl_objects_source'),
'rpsl_objects', ['source'],
unique=False)
op.create_index('ix_rpsl_objects_asn_first_asn_last',
'rpsl_objects', ['asn_first', 'asn_last'],
unique=False)
op.create_index('ix_rpsl_objects_ip_first_ip_last',
'rpsl_objects', ['ip_first', 'ip_last'],
unique=False)
op.create_index('ix_rpsl_objects_ip_last_ip_first',
'rpsl_objects', ['ip_last', 'ip_first'],
unique=False)
# Manually added
op.create_index(op.f('ix_rpsl_objects_parsed_data_admin_c'),
'rpsl_objects', [sa.text("((parsed_data->'admin-c'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_tech_c'),
'rpsl_objects', [sa.text("((parsed_data->'tech-c'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_zone_c'),
'rpsl_objects', [sa.text("((parsed_data->'zone-c'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_role'),
'rpsl_objects', [sa.text("((parsed_data->'role'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_members'),
'rpsl_objects', [sa.text("((parsed_data->'members'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_person'),
'rpsl_objects', [sa.text("((parsed_data->'person'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_mnt_by'),
'rpsl_objects', [sa.text("((parsed_data->'mnt-by'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_member_of'),
'rpsl_objects', [sa.text("((parsed_data->'member-of'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_mp_members'),
'rpsl_objects', [sa.text("((parsed_data->'mp-members'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_origin'),
'rpsl_objects', [sa.text("((parsed_data->'origin'))")],
unique=False,
postgresql_using='gin')
op.create_index(op.f('ix_rpsl_objects_parsed_data_mbrs_by_ref'),
'rpsl_objects',
[sa.text("((parsed_data->'mbrs-by-ref'))")],
unique=False,
postgresql_using='gin')
def bind_processor(self, dialect):
def process(value):
return self.python_type(b"" if not value else utils.ip2bin(value))
return process
@staticmethod
def result_processor(dialect, coltype):
def process(value):
return None if not value else utils.bin2ip(value)
return process
SQLINET = postgresql.INET().with_variant(DefaultINET(), "sqlite")
class Point(UserDefinedType):
# pylint: disable=no-self-use
def get_col_spec(self):
return "POINT"
def bind_expression(self, bindvalue):
return func.Point_In(bindvalue, type_=self)
@staticmethod
def bind_processor(dialect):
def process(value):
if value is None:
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('project',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('description', sa.String(), nullable=True),
sa.Column('date_from', sa.Date(), nullable=False),
sa.Column('date_to', sa.Date(), nullable=False),
sa.Column('host_history', sa.Integer(), nullable=True),
sa.Column('retro_delete', sa.Boolean(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table('user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_name', sa.String(), nullable=False),
sa.Column('email', sa.String(), nullable=False),
sa.Column('pass_hash', sa.String(), nullable=False),
sa.Column('user_role', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email')
)
op.create_table('host',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('project_id', sa.Integer(), nullable=True),
sa.Column('ip', postgresql.INET(), nullable=True),
sa.Column('domain', sa.String(), nullable=True),
sa.ForeignKeyConstraint(['project_id'], ['project.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
op.create_table('vulnerability',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('project_id', sa.Integer(), nullable=True),
sa.Column('object', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('full_path', sa.String(), nullable=False),
sa.Column('criticality', sa.Integer(), nullable=True),
sa.Column('probability', sa.Integer(), nullable=True),
sa.Column('final_criticality', sa.Integer(), nullable=True),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('risk', sa.Text(), nullable=False),
sa.Column('details', sa.Text(), nullable=False),
sa.Column('recommendation', sa.Text(), nullable=False),
sa.Column('creator', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['creator'], ['user.id'], ),
sa.ForeignKeyConstraint(['project_id'], ['project.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
op.create_table('attachment',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('description', sa.String(), nullable=True),
sa.Column('path', sa.String(), nullable=False),
sa.Column('filename', sa.String(), nullable=False),
sa.Column('vuln_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['vuln_id'], ['vulnerability.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
op.create_table('vuln_ref',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('host_id', sa.Integer(), nullable=True),
sa.Column('vuln_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['host_id'], ['host.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['vuln_id'], ['vulnerability.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id')
)
def upgrade():
op.create_table(
'lab_product',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('enabled', sa.Boolean(), nullable=True),
sa.Column('tower_template_name_create', sa.String(length=128), nullable=False),
sa.Column('tower_template_name_delete', sa.String(length=128), nullable=False),
sa.Column('parameters', sa.JSON(), nullable=False),
sa.Column('flavors', sa.JSON(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table(
'lab_quota',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('num_vcpus', sa.Integer(), nullable=True),
sa.Column('ram_mb', sa.Integer(), nullable=True),
sa.Column('num_volumes', sa.Integer(), nullable=True),
sa.Column('volumes_gb', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table(
'policies',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=256), nullable=False),
sa.Column('department', sa.Text(), nullable=False),
sa.Column('constraint_sched_avail', sa.ARRAY(sa.Text()), nullable=True),
sa.Column('constraint_serv_avail', sa.Numeric(), nullable=True),
sa.Column('constraint_limit', sa.JSON(), nullable=True),
sa.Column('constraint_density', sa.Text(), nullable=True),
sa.Column('constraint_tag', sa.ARRAY(sa.Text()), nullable=True),
sa.Column('constraint_cost', sa.Numeric(), nullable=True),
sa.Column('constraint_location', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_table(
'scheduler_cron',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('enabled', sa.Boolean(), nullable=True),
sa.Column('time_expr', sa.String(length=128), nullable=False),
sa.Column('job_name', sa.Text(), nullable=False),
sa.Column('job_params', sa.JSON(), nullable=True),
sa.Column('last_run', sa.DateTime(timezone=True), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table(
'tower_server',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=32), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('enabled', sa.Boolean(), nullable=True),
sa.Column('url', sa.String(length=256), nullable=False),
sa.Column('verify_ssl', sa.Boolean(), nullable=True),
sa.Column('credentials', sa.String(length=256), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table(
'lab_region',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=32), nullable=False),
sa.Column('location', sa.String(length=32), nullable=True),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('banner', sa.Text(), nullable=False),
sa.Column('enabled', sa.Boolean(), nullable=True),
sa.Column('user_quota_id', sa.Integer(), nullable=True),
sa.Column('total_quota_id', sa.Integer(), nullable=True),
sa.Column('lifespan_length', sa.Integer(), nullable=True),
sa.Column('reservations_enabled', sa.Boolean(), nullable=True),
sa.Column('reservation_expiration_max', sa.Integer(), nullable=True),
sa.Column('owner_group', postgresql.UUID(), nullable=False),
sa.Column('users_group', postgresql.UUID(), nullable=True),
sa.Column('tower_id', sa.Integer(), nullable=True),
sa.Column('openstack_url', sa.String(length=256), nullable=False),
sa.Column('openstack_credentials', sa.String(length=256), nullable=False),
sa.Column('openstack_project', sa.String(length=64), nullable=False),
sa.Column('openstack_domain_name', sa.String(length=64), nullable=False),
sa.Column('openstack_domain_id', sa.String(length=64), nullable=False),
sa.Column('openstack_networks', sa.ARRAY(sa.String(length=64)), nullable=False),
sa.Column('openstack_keyname', sa.String(length=64), nullable=False),
sa.Column('satellite_hostname', sa.String(length=256), nullable=False),
sa.Column('satellite_insecure', sa.Boolean(), nullable=False),
sa.Column('satellite_credentials', sa.String(length=256), nullable=False),
sa.Column('dns_server_hostname', sa.String(length=256), nullable=False),
sa.Column('dns_server_zone', sa.String(length=256), nullable=False),
sa.Column('dns_server_key', sa.String(length=256), nullable=False),
sa.Column('vault_server', sa.String(length=256), nullable=False),
sa.Column('download_server', sa.String(length=256), nullable=False),
sa.ForeignKeyConstraint(['total_quota_id'], ['lab_quota.id']),
sa.ForeignKeyConstraint(['tower_id'], ['tower_server.id']),
sa.ForeignKeyConstraint(['user_quota_id'], ['lab_quota.id']),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_index(
op.f('ix_lab_region_location'),
'lab_region',
['location'],
unique=False
)
op.create_index(
op.f('ix_lab_region_users_group'),
'lab_region',
['users_group'],
unique=False
)
op.create_table(
'tower_template',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=256), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('server_id', sa.Integer(), nullable=False),
sa.Column('tower_template_id', sa.Integer(), nullable=False),
sa.Column('tower_template_is_workflow', sa.Boolean(), nullable=False),
sa.ForeignKeyConstraint(['server_id'], ['tower_server.id']),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table(
'lab_cluster',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=32), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('user_id', postgresql.UUID(), nullable=False),
sa.Column('group_id', postgresql.UUID(), nullable=True),
sa.Column('created', sa.DateTime(timezone=True), nullable=True),
sa.Column('region_id', sa.Integer(), nullable=False),
sa.Column('reservation_expiration', sa.DateTime(timezone=True), nullable=True),
sa.Column('lifespan_expiration', sa.DateTime(timezone=True), nullable=True),
sa.Column('status', clusterstatus_enum, nullable=True),
sa.Column('product_id', sa.Integer(), nullable=False),
sa.Column('product_params', sa.JSON(), nullable=False),
sa.ForeignKeyConstraint(['product_id'], ['lab_product.id']),
sa.ForeignKeyConstraint(['region_id'], ['lab_region.id']),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table(
'lab_region_product',
sa.Column('region_id', sa.Integer(), nullable=False),
sa.Column('product_id', sa.Integer(), nullable=False),
sa.Column('enabled', sa.Boolean(), nullable=True),
sa.ForeignKeyConstraint(['product_id'], ['lab_product.id']),
sa.ForeignKeyConstraint(['region_id'], ['lab_region.id']),
sa.PrimaryKeyConstraint('region_id', 'product_id')
)
op.create_table(
'tower_job',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('template_id', sa.Integer(), nullable=False),
sa.Column('tower_job_id', sa.Integer(), nullable=False),
sa.Column('launched_by', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['template_id'], ['tower_template.id']),
sa.PrimaryKeyConstraint('id')
)
op.create_index(
op.f('ix_tower_job_launched_by'),
'tower_job',
['launched_by'],
unique=False
)
op.create_table(
'lab_cluster_event',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('type', clustereventtype_enum, nullable=True),
sa.Column('date', sa.DateTime(timezone=True), nullable=True),
sa.Column('user_id', postgresql.UUID(), nullable=True),
sa.Column('cluster_id', sa.Integer(), nullable=False),
sa.Column('tower_id', sa.Integer(), nullable=True),
sa.Column('tower_job_id', sa.Integer(), nullable=True),
sa.Column('status', clusterstatus_enum, nullable=True),
sa.Column('status_old', clusterstatus_enum, nullable=True),
sa.Column('status_new', clusterstatus_enum, nullable=True),
sa.Column('expiration_old', sa.DateTime(timezone=True), nullable=True),
sa.Column('expiration_new', sa.DateTime(timezone=True), nullable=True),
sa.ForeignKeyConstraint(['cluster_id'], ['lab_cluster.id']),
sa.ForeignKeyConstraint(['tower_id'], ['tower_server.id']),
sa.PrimaryKeyConstraint('id')
)
op.create_table(
'lab_cluster_host',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('cluster_id', sa.Integer(), nullable=False),
sa.Column('fqdn', sa.String(length=256), nullable=False),
sa.Column('ipaddr', sa.ARRAY(postgresql.INET()), nullable=True),
sa.Column('num_vcpus', sa.Integer(), nullable=True),
sa.Column('ram_mb', sa.Integer(), nullable=True),
sa.Column('num_volumes', sa.Integer(), nullable=True),
sa.Column('volumes_gb', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['cluster_id'], ['lab_cluster.id']),
sa.PrimaryKeyConstraint('id')
)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
"entities",
sa.Column("entity", sa.Integer(), nullable=False),
sa.Column("identity", sa.String(length=50), nullable=True),
sa.PrimaryKeyConstraint("entity"),
)
op.create_index(op.f("ix_entities_entity"), "entities", ["entity"], unique=False)
op.create_index(
op.f("ix_entities_identity"), "entities", ["identity"], unique=False
)
op.create_table(
"accounts",
sa.Column("_entity", sa.Integer(), nullable=False),
sa.Column("key", acmetk.models.account.JWKType(), nullable=True),
sa.Column("kid", sa.String(), nullable=False),
sa.Column(
"status",
sa.Enum("VALID", "DEACTIVATED", "REVOKED", name="accountstatus"),
nullable=True,
),
sa.Column("contact", sa.JSON(), nullable=True),
sa.ForeignKeyConstraint(
["_entity"],
["entities.entity"],
),
sa.PrimaryKeyConstraint("kid"),
)
op.create_index(op.f("ix_accounts__entity"), "accounts", ["_entity"], unique=False)
op.create_index(op.f("ix_accounts_key"), "accounts", ["key"], unique=False)
op.create_table(
"changes",
sa.Column("change", sa.Integer(), nullable=False),
sa.Column("_entity", sa.Integer(), nullable=False),
sa.Column("timestamp", sa.DateTime(timezone=True), nullable=False),
sa.Column("remote_host", postgresql.INET(), nullable=True),
sa.Column("data", sa.JSON(), nullable=False),
sa.ForeignKeyConstraint(
["_entity"],
["entities.entity"],
),
sa.PrimaryKeyConstraint("change"),
)
op.create_index(op.f("ix_changes__entity"), "changes", ["_entity"], unique=False)
op.create_index(op.f("ix_changes_change"), "changes", ["change"], unique=False)
op.create_index(
op.f("ix_changes_remote_host"), "changes", ["remote_host"], unique=False
)
op.create_index(
op.f("ix_changes_timestamp"), "changes", ["timestamp"], unique=False
)
op.create_table(
"orders",
sa.Column("_entity", sa.Integer(), nullable=False),
sa.Column("order_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("proxied_url", sa.String(), nullable=True),
sa.Column("proxied_error", acmetk.models.base.AcmeErrorType(), nullable=True),
sa.Column(
"status",
sa.Enum(
"PENDING", "READY", "PROCESSING", "VALID", "INVALID", name="orderstatus"
),
nullable=False,
),
sa.Column("expires", sa.DateTime(timezone=True), nullable=False),
sa.Column("notBefore", sa.DateTime(timezone=True), nullable=True),
sa.Column("notAfter", sa.DateTime(timezone=True), nullable=True),
sa.Column("account_kid", sa.String(), nullable=False),
sa.Column("csr", acmetk.models.order.CSRType(), nullable=True),
sa.ForeignKeyConstraint(
["_entity"],
["entities.entity"],
),
sa.ForeignKeyConstraint(
["account_kid"],
["accounts.kid"],
),
sa.PrimaryKeyConstraint("order_id"),
)
op.create_index(op.f("ix_orders__entity"), "orders", ["_entity"], unique=False)
op.create_table(
"certificates",
sa.Column("_entity", sa.Integer(), nullable=False),
sa.Column("certificate_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column(
"status",
sa.Enum("VALID", "REVOKED", name="certificatestatus"),
nullable=False,
),
sa.Column("order_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("cert", acmetk.models.certificate.x509Certificate(), nullable=True),
sa.Column("full_chain", sa.Text(), nullable=True),
sa.Column(
"reason",
sa.Enum(
"unspecified",
"keyCompromise",
"cACompromise",
"affiliationChanged",
"superseded",
"cessationOfOperation",
"certificateHold",
"removeFromCRL",
"privilegeWithdrawn",
"aACompromise",
name="revocationreason",
),
nullable=True,
),
sa.CheckConstraint(
"cert is not NULL or full_chain is not NULL",
name="check_cert_or_full_chain",
),
sa.ForeignKeyConstraint(
["_entity"],
["entities.entity"],
),
sa.ForeignKeyConstraint(
["order_id"],
["orders.order_id"],
),
sa.PrimaryKeyConstraint("certificate_id"),
)
op.create_index(
op.f("ix_certificates__entity"), "certificates", ["_entity"], unique=False
)
op.create_index(
op.f("ix_certificates_cert"), "certificates", ["cert"], unique=False
)
op.create_index(
op.f("ix_certificates_order_id"), "certificates", ["order_id"], unique=True
)
op.create_table(
"identifiers",
sa.Column("_entity", sa.Integer(), nullable=False),
sa.Column("identifier_id", sa.Integer(), nullable=False),
sa.Column("type", sa.Enum("DNS", name="identifiertype"), nullable=True),
sa.Column("value", sa.String(), nullable=True),
sa.Column("order_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(
["_entity"],
["entities.entity"],
),
sa.ForeignKeyConstraint(
["order_id"],
["orders.order_id"],
),
sa.PrimaryKeyConstraint("identifier_id"),
)
op.create_index(
op.f("ix_identifiers__entity"), "identifiers", ["_entity"], unique=False
)
op.create_table(
"authorizations",
sa.Column("_entity", sa.Integer(), nullable=False),
sa.Column("authorization_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("identifier_id", sa.Integer(), nullable=False),
sa.Column(
"status",
sa.Enum(
"PENDING",
"VALID",
"INVALID",
"DEACTIVATED",
"EXPIRED",
"REVOKED",
name="authorizationstatus",
),
nullable=False,
),
sa.Column("expires", sa.DateTime(timezone=True), nullable=True),
sa.Column("wildcard", sa.Boolean(), nullable=False),
sa.ForeignKeyConstraint(
["_entity"],
["entities.entity"],
),
sa.ForeignKeyConstraint(
["identifier_id"],
["identifiers.identifier_id"],
),
sa.PrimaryKeyConstraint("authorization_id"),
)
op.create_index(
op.f("ix_authorizations__entity"), "authorizations", ["_entity"], unique=False
)
op.create_index(
op.f("ix_authorizations_identifier_id"),
"authorizations",
["identifier_id"],
unique=True,
)
op.create_table(
"challenges",
sa.Column("_entity", sa.Integer(), nullable=False),
sa.Column("challenge_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("authorization_id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column(
"type",
sa.Enum("HTTP_01", "DNS_01", "TLS_ALPN_01", name="challengetype"),
nullable=False,
),
sa.Column(
"status",
sa.Enum(
"PENDING", "PROCESSING", "VALID", "INVALID", name="challengestatus"
),
nullable=False,
),
sa.Column("validated", sa.DateTime(timezone=True), nullable=True),
sa.Column("token", postgresql.UUID(as_uuid=True), nullable=True),
sa.Column("error", acmetk.models.base.AcmeErrorType(), nullable=True),
sa.ForeignKeyConstraint(
["_entity"],
["entities.entity"],
),
sa.ForeignKeyConstraint(
["authorization_id"],
["authorizations.authorization_id"],
),
sa.PrimaryKeyConstraint("challenge_id"),
sa.UniqueConstraint("token"),
)
op.create_index(
op.f("ix_challenges__entity"), "challenges", ["_entity"], unique=False
)
def test_convert_INET(self, converter):
prop = make_property(postgresql.INET())
field = converter.property2field(prop)
assert type(field) == fields.Str
def load_dialect_impl(self, dialect):
if dialect.name == 'postgresql':
return dialect.type_descriptor(postgresql.INET())
return dialect.type_descriptor(types.CHAR(39))
def upgrade_app():
op.create_table(
'account', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('apikey', sa.String(), nullable=False),
sa.Column('mbuser', sa.String(), nullable=True),
sa.Column('anonymous',
sa.Boolean(),
server_default=sa.text('false'),
nullable=True),
sa.Column('created',
sa.DateTime(timezone=True),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=False),
sa.Column('lastlogin', sa.DateTime(timezone=True), nullable=True),
sa.Column('submission_count',
sa.Integer(),
server_default=sa.text('0'),
nullable=False),
sa.Column('application_id', sa.Integer(), nullable=True),
sa.Column('application_version', sa.String(), nullable=True),
sa.Column('created_from', postgresql.INET(), nullable=True),
sa.Column('is_admin',
sa.Boolean(),
server_default=sa.text('false'),
nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('account_pkey')))
op.create_index('account_idx_apikey', 'account', ['apikey'], unique=True)
op.create_index('account_idx_mbuser', 'account', ['mbuser'], unique=True)
op.create_table(
'account_google',
sa.Column('google_user_id', sa.String(), nullable=False),
sa.Column('account_id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('google_user_id',
name=op.f('account_google_pkey')))
op.create_index('account_google_idx_account_id',
'account_google', ['account_id'],
unique=False)
op.create_table(
'account_openid', sa.Column('openid', sa.String(), nullable=False),
sa.Column('account_id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('openid', name=op.f('account_openid_pkey')))
op.create_index('account_openid_idx_account_id',
'account_openid', ['account_id'],
unique=False)
op.create_table(
'application', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('version', sa.String(), nullable=False),
sa.Column('apikey', sa.String(), nullable=False),
sa.Column('created',
sa.DateTime(timezone=True),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=False),
sa.Column('active',
sa.Boolean(),
server_default=sa.text('true'),
nullable=True),
sa.Column('account_id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(), nullable=True),
sa.Column('website', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id', name=op.f('application_pkey')))
op.create_index('application_idx_apikey',
'application', ['apikey'],
unique=True)
op.create_table('format', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('format_pkey')))
op.create_index('format_idx_name', 'format', ['name'], unique=True)
op.create_table('source', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('application_id', sa.Integer(), nullable=False),
sa.Column('account_id', sa.Integer(), nullable=False),
sa.Column('version', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id', name=op.f('source_pkey')))
op.create_index('source_idx_uniq',
'source', ['application_id', 'account_id', 'version'],
unique=True)
op.create_table(
'stats', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('date',
sa.Date(),
server_default=sa.text('CURRENT_DATE'),
nullable=False),
sa.Column('value', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('stats_pkey')))
op.create_index('stats_idx_date', 'stats', ['date'], unique=False)
op.create_index('stats_idx_name_date',
'stats', ['name', 'date'],
unique=False)
op.create_table(
'stats_lookups', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('date', sa.Date(), nullable=False),
sa.Column('hour', sa.Integer(), nullable=False),
sa.Column('application_id', sa.Integer(), nullable=False),
sa.Column('count_nohits',
sa.Integer(),
server_default=sa.text('0'),
nullable=False),
sa.Column('count_hits',
sa.Integer(),
server_default=sa.text('0'),
nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('stats_lookups_pkey')))
op.create_index('stats_lookups_idx_date',
'stats_lookups', ['date'],
unique=False)
op.create_table(
'stats_top_accounts', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('account_id', sa.Integer(), nullable=False),
sa.Column('count', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('stats_top_accounts_pkey')))
op.create_table(
'stats_user_agents', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('date', sa.Date(), nullable=False),
sa.Column('application_id', sa.Integer(), nullable=False),
sa.Column('user_agent', sa.String(), nullable=False),
sa.Column('ip', sa.String(), nullable=False),
sa.Column('count',
sa.Integer(),
server_default=sa.text('0'),
nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('stats_user_agents_pkey')))
op.create_index('stats_user_agents_idx_date',
'stats_user_agents', ['date'],
unique=False)
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('group',
sa.Column('uuid',
postgresql.UUID(as_uuid=True),
nullable=True),
sa.Column('created_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.Column('updated_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.Column('group_data', postgresql.JSONB(), nullable=True),
sa.Column('description', sa.UnicodeText(), nullable=True),
sa.Column('name', sa.Unicode(length=50), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id', name=op.f('pk_group')),
sa.UniqueConstraint('name', name=op.f('uq_group_name')),
mysql_charset='utf8',
mysql_engine='InnoDB')
op.create_table(
'question', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=True),
sa.Column('question_text', sa.String(length=256), nullable=True),
sa.Column('published_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.PrimaryKeyConstraint('id', name=op.f('pk_question')))
op.create_table('user_activation',
sa.Column('created_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.Column('updated_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.Column('expires_at',
websauna.system.model.columns.UTCDateTime(),
nullable=False),
sa.Column('created_by',
sa.Unicode(length=30),
nullable=False),
sa.Column('valid_until', sa.DateTime(), nullable=False),
sa.Column('code', sa.Unicode(length=30), nullable=False),
sa.Column('id', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id',
name=op.f('pk_user_activation')),
sa.UniqueConstraint('code',
name=op.f('uq_user_activation_code')),
mysql_charset='utf8',
mysql_engine='InnoDB')
op.create_table(
'choice', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=True),
sa.Column('choice_text', sa.String(length=256), nullable=True),
sa.Column('votes', sa.Integer(), nullable=True),
sa.Column('question_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['question_id'], ['question.id'],
name=op.f('fk_choice_question_id_question')),
sa.PrimaryKeyConstraint('id', name=op.f('pk_choice')))
op.create_table('users',
sa.Column('username', sa.String(length=256),
nullable=True),
sa.Column('password', sa.String(length=256),
nullable=True),
sa.Column('salt', sa.String(length=256), nullable=True),
sa.Column('created_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.Column('updated_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.Column('uuid',
postgresql.UUID(as_uuid=True),
nullable=True),
sa.Column('activated_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.Column('enabled', sa.Boolean(), nullable=True),
sa.Column('last_login_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.Column('last_login_ip',
postgresql.INET(),
nullable=True),
sa.Column('user_data', postgresql.JSONB(), nullable=True),
sa.Column('last_auth_sensitive_operation_at',
websauna.system.model.columns.UTCDateTime(),
nullable=True),
sa.Column('registered_date',
sa.TIMESTAMP(),
server_default=sa.text('now()'),
nullable=False),
sa.Column('email', sa.Unicode(length=100), nullable=False),
sa.Column('status', sa.Integer(), nullable=True),
sa.Column('security_code',
sa.Unicode(length=256),
nullable=True),
sa.Column('activation_id', sa.Integer(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['activation_id'], ['user_activation.id'],
name=op.f('fk_users_activation_id_user_activation')),
sa.PrimaryKeyConstraint('id', name=op.f('pk_users')),
sa.UniqueConstraint('email', name=op.f('uq_users_email')),
sa.UniqueConstraint('security_code',
name=op.f('uq_users_security_code')),
sa.UniqueConstraint('username',
name=op.f('uq_users_username')),
mysql_charset='utf8',
mysql_engine='InnoDB')
op.create_table(
'usergroup',
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('group_id', sa.Integer(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['group_id'], ['group.id'],
name=op.f('fk_usergroup_group_id_group')),
sa.ForeignKeyConstraint(['user_id'], ['users.id'],
name=op.f('fk_usergroup_user_id_users'),
onupdate='CASCADE',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id', name=op.f('pk_usergroup')),
mysql_charset='utf8',
mysql_engine='InnoDB')