def upgrade():
op.create_table(
"role",
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("deleted_at", sa.DateTime(), nullable=True),
sa.Column("foreign_id", sa.Unicode(length=2048), nullable=False),
sa.Column("name", sa.Unicode(), nullable=False),
sa.Column("email", sa.Unicode(), nullable=True),
sa.Column("api_key", sa.Unicode(), nullable=True),
sa.Column("is_admin", sa.Boolean(), nullable=False),
sa.Column("is_muted", sa.Boolean(), nullable=False),
sa.Column("type",
sa.Enum("user", "group", "system", name="role_type"),
nullable=False),
sa.Column("password_digest", sa.Unicode(), nullable=True),
sa.Column("reset_token", sa.Unicode(), nullable=True),
sa.Column("notified_at", sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("foreign_id"),
)
op.create_table(
"alert",
sa.Column("created_at", sa.DateTime(), nullable=True),
sa.Column("updated_at", sa.DateTime(), nullable=True),
sa.Column("deleted_at", sa.DateTime(), nullable=True),
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("query", sa.Unicode(), nullable=True),
sa.Column("notified_at", sa.DateTime(), nullable=True),
sa.Column("role_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["role_id"],
["role.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_alert_role_id"),
"alert", ["role_id"],
unique=False)
op.create_table(
"collection",
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("deleted_at", sa.DateTime(), nullable=True),
sa.Column("label", sa.Unicode(), nullable=True),
sa.Column("summary", sa.Unicode(), nullable=True),
sa.Column("category", sa.Unicode(), nullable=True),
sa.Column("countries", postgresql.ARRAY(sa.Unicode()), nullable=True),
sa.Column("languages", postgresql.ARRAY(sa.Unicode()), nullable=True),
sa.Column("foreign_id", sa.Unicode(), nullable=False),
sa.Column("publisher", sa.Unicode(), nullable=True),
sa.Column("publisher_url", sa.Unicode(), nullable=True),
sa.Column("info_url", sa.Unicode(), nullable=True),
sa.Column("data_url", sa.Unicode(), nullable=True),
sa.Column("casefile", sa.Boolean(), nullable=True),
sa.Column("creator_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["creator_id"],
["role.id"],
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("foreign_id"),
)
op.create_table(
"notification",
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("event", sa.String(length=255), nullable=False),
sa.Column("channels",
postgresql.ARRAY(sa.String(length=255)),
nullable=True),
sa.Column("params",
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column("actor_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["actor_id"],
["role.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_notification_channels"),
"notification", ["channels"],
unique=False)
op.create_table(
"permission",
sa.Column("created_at", sa.DateTime(), nullable=True),
sa.Column("updated_at", sa.DateTime(), nullable=True),
sa.Column("deleted_at", sa.DateTime(), nullable=True),
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("role_id", sa.Integer(), nullable=True),
sa.Column("read", sa.Boolean(), nullable=True),
sa.Column("write", sa.Boolean(), nullable=True),
sa.Column("collection_id", sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
["role_id"],
["role.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_permission_role_id"),
"permission", ["role_id"],
unique=False)
op.create_table(
"query_log",
sa.Column("id", sa.BigInteger(), nullable=False),
sa.Column("query", sa.Unicode(), nullable=True),
sa.Column("session_id", sa.Unicode(), nullable=True),
sa.Column("role_id", sa.Integer(), nullable=True),
sa.Column("created_at", sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
["role_id"],
["role.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_query_log_role_id"),
"query_log", ["role_id"],
unique=False)
op.create_table(
"role_membership",
sa.Column("group_id", sa.Integer(), nullable=True),
sa.Column("member_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["group_id"],
["role.id"],
),
sa.ForeignKeyConstraint(
["member_id"],
["role.id"],
),
)
op.create_table(
"document",
sa.Column("created_at", sa.DateTime(), nullable=True),
sa.Column("updated_at", sa.DateTime(), nullable=True),
sa.Column("id", sa.BigInteger(), nullable=False),
sa.Column("content_hash", sa.Unicode(length=65), nullable=True),
sa.Column("foreign_id", sa.Unicode(), nullable=True),
sa.Column("schema", sa.String(length=255), nullable=False),
sa.Column("meta",
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column("uploader_id", sa.Integer(), nullable=True),
sa.Column("parent_id", sa.BigInteger(), nullable=True),
sa.Column("collection_id", sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
["collection_id"],
["collection.id"],
),
sa.ForeignKeyConstraint(
["parent_id"],
["document.id"],
),
sa.ForeignKeyConstraint(
["uploader_id"],
["role.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_document_collection_id"),
"document", ["collection_id"],
unique=False)
op.create_index(op.f("ix_document_content_hash"),
"document", ["content_hash"],
unique=False)
op.create_index(op.f("ix_document_foreign_id"),
"document", ["foreign_id"],
unique=False)
op.create_index(op.f("ix_document_parent_id"),
"document", ["parent_id"],
unique=False)
op.create_table(
"entity",
sa.Column("created_at", sa.DateTime(), nullable=True),
sa.Column("updated_at", sa.DateTime(), nullable=True),
sa.Column("deleted_at", sa.DateTime(), nullable=True),
sa.Column("id", sa.String(length=128), nullable=False),
sa.Column("name", sa.Unicode(), nullable=True),
sa.Column("schema", sa.String(length=255), nullable=True),
sa.Column("foreign_id", sa.Unicode(), nullable=True),
sa.Column("data",
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column("collection_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["collection_id"],
["collection.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_entity_collection_id"),
"entity", ["collection_id"],
unique=False)
op.create_index(op.f("ix_entity_schema"),
"entity", ["schema"],
unique=False)
op.create_table(
"match",
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("entity_id", sa.String(length=128), nullable=True),
sa.Column("collection_id", sa.Integer(), nullable=True),
sa.Column("match_id", sa.String(length=128), nullable=True),
sa.Column("match_collection_id", sa.Integer(), nullable=True),
sa.Column("score", sa.Float(), nullable=True),
sa.ForeignKeyConstraint(
["collection_id"],
["collection.id"],
),
sa.ForeignKeyConstraint(
["match_collection_id"],
["collection.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_match_collection_id"),
"match", ["collection_id"],
unique=False)
op.create_index(
op.f("ix_match_match_collection_id"),
"match",
["match_collection_id"],
unique=False,
)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'deployment', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('hostnames', postgresql.ARRAY(sa.String()), nullable=False),
sa.Column('allow_observer_submission_edit',
sa.Boolean(),
nullable=True), sa.Column('logo', sa.String(),
nullable=True),
sa.Column('include_rejected_in_votes', sa.Boolean(), nullable=True),
sa.Column('is_initialized', sa.Boolean(), nullable=True),
sa.Column('dashboard_full_locations', sa.Boolean(), nullable=True),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'phone', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('number', sa.String(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('number'))
op.create_table(
'form_set', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('slug', sa.String(), nullable=True),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'location_set', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('slug', sa.String(), nullable=True),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'permission', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('description', sa.String(), nullable=True),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'resource', sa.Column('resource_id', sa.Integer(), nullable=False),
sa.Column('resource_type', sa.String(), nullable=False),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('resource_id'))
op.create_table(
'role', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('description', sa.String(), nullable=True),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('deployment_id', 'name'))
op.create_table(
'user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(), nullable=False),
sa.Column('username', sa.String(), nullable=False),
sa.Column('password', sa.String(), nullable=False),
sa.Column('last_name', sa.String(), nullable=True),
sa.Column('first_name', sa.String(), nullable=True),
sa.Column('active', sa.Boolean(), nullable=True),
sa.Column('confirmed_at', sa.DateTime(), nullable=True),
sa.Column('current_login_at', sa.DateTime(), nullable=True),
sa.Column('last_login_at', sa.DateTime(), nullable=True),
sa.Column('current_login_ip', sa.String(), nullable=True),
sa.Column('last_login_ip', sa.String(), nullable=True),
sa.Column('login_count', sa.Integer(), nullable=True),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'form', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('prefix', sa.String(), nullable=False),
sa.Column('form_type',
sqlalchemy_utils.types.choice.ChoiceType(Form.FORM_TYPES),
nullable=False),
sa.Column('require_exclamation', sa.Boolean(), nullable=True),
sa.Column('data',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column('version_identifier', sa.String(), nullable=True),
sa.Column('form_set_id', sa.Integer(), nullable=False),
sa.Column('resource_id', sa.Integer(), nullable=True),
sa.Column('quality_checks',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column('party_mappings',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column('calculate_moe', sa.Boolean(), nullable=True),
sa.Column('accredited_voters_tag', sa.String(), nullable=True),
sa.Column('quality_checks_enabled', sa.Boolean(), nullable=True),
sa.Column('invalid_votes_tag', sa.String(), nullable=True),
sa.Column('registered_voters_tag', sa.String(), nullable=True),
sa.Column('blank_votes_tag', sa.String(), nullable=True),
sa.ForeignKeyConstraint(
['form_set_id'],
['form_set.id'],
),
sa.ForeignKeyConstraint(['resource_id'], ['resource.resource_id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'location_data_field', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('location_set_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('label', sa.String(), nullable=False),
sa.Column('visible_in_lists', sa.Boolean(), nullable=True),
sa.Column('resource_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['location_set_id'], ['location_set.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['resource_id'], ['resource.resource_id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'location_type', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('is_administrative', sa.Boolean(), nullable=True),
sa.Column('is_political', sa.Boolean(), nullable=True),
sa.Column('has_registered_voters', sa.Boolean(), nullable=True),
sa.Column('slug', sa.String(), nullable=True),
sa.Column('location_set_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['location_set_id'], ['location_set.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'participant_set', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('slug', sa.String(), nullable=True),
sa.Column('location_set_id', sa.Integer(), nullable=False),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['location_set_id'], ['location_set.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'role_resource_permissions',
sa.Column('role_id', sa.Integer(), nullable=False),
sa.Column('resource_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['resource_id'], ['resource.resource_id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['role_id'], ['role.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('role_id', 'resource_id'))
op.create_table(
'roles_permissions', sa.Column('role_id', sa.Integer(),
nullable=False),
sa.Column('permission_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['permission_id'], ['permission.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['role_id'], ['role.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('role_id', 'permission_id'))
op.create_table(
'roles_users', sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('role_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['role_id'], ['role.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('user_id', 'role_id'))
op.create_table(
'sample', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('location_set_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['location_set_id'], ['location_set.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'user_resource_permissions',
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('resource_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['resource_id'], ['resource.resource_id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('user_id', 'resource_id'))
op.create_table(
'user_upload', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('upload_filename', sa.String(), nullable=True),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'users_permissions', sa.Column('user_id', sa.Integer(),
nullable=False),
sa.Column('permission_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['permission_id'], ['permission.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('user_id', 'permission_id'))
op.create_table(
'event', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('start', sa.DateTime(), nullable=False),
sa.Column('end', sa.DateTime(), nullable=False),
sa.Column('form_set_id', sa.Integer(), nullable=True),
sa.Column('resource_id', sa.Integer(), nullable=True),
sa.Column('location_set_id', sa.Integer(), nullable=True),
sa.Column('participant_set_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['form_set_id'], ['form_set.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['location_set_id'], ['location_set.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['participant_set_id'], ['participant_set.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['resource_id'], ['resource.resource_id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'location', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('code', sa.String(), nullable=False),
sa.Column('registered_voters', sa.Integer(), nullable=True),
sa.Column('location_set_id', sa.Integer(), nullable=False),
sa.Column('location_type_id', sa.Integer(), nullable=False),
sa.Column('lat', sa.Float(), nullable=True),
sa.Column('lon', sa.Float(), nullable=True),
sa.Column('extra_data',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['location_set_id'], ['location_set.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['location_type_id'], ['location_type.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('location_set_id', 'code'))
op.create_index(op.f('ix_location_code'),
'location', ['code'],
unique=False)
op.create_table(
'location_type_path',
sa.Column('location_set_id', sa.Integer(), nullable=False),
sa.Column('ancestor_id', sa.Integer(), nullable=False),
sa.Column('descendant_id', sa.Integer(), nullable=False),
sa.Column('depth', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['ancestor_id'], ['location_type.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['descendant_id'], ['location_type.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['location_set_id'], ['location_set.id']),
sa.PrimaryKeyConstraint('ancestor_id', 'descendant_id'))
op.create_index('location_type_paths_ancestor_idx',
'location_type_path', ['ancestor_id'],
unique=False)
op.create_index('location_type_paths_descendant_idx',
'location_type_path', ['descendant_id'],
unique=False)
op.create_table(
'participant_data_field', sa.Column('id', sa.Integer(),
nullable=False),
sa.Column('participant_set_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('label', sa.String(), nullable=False),
sa.Column('visible_in_lists', sa.Boolean(), nullable=True),
sa.Column('resource_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['participant_set_id'], ['participant_set.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['resource_id'], ['resource.resource_id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'participant_group_type', sa.Column('id', sa.Integer(),
nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('participant_set_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['participant_set_id'], ['participant_set.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'participant_partner', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('participant_set_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['participant_set_id'], ['participant_set.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'participant_role', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('participant_set_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['participant_set_id'], ['participant_set.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'location_path',
sa.Column('location_set_id', sa.Integer(), nullable=False),
sa.Column('ancestor_id', sa.Integer(), nullable=False),
sa.Column('descendant_id', sa.Integer(), nullable=False),
sa.Column('depth', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['ancestor_id'], ['location.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['descendant_id'], ['location.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['location_set_id'], ['location_set.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('ancestor_id', 'descendant_id'))
op.create_index('location_paths_ancestor_idx',
'location_path', ['ancestor_id'],
unique=False)
op.create_index('location_paths_descendant_idx',
'location_path', ['descendant_id'],
unique=False)
op.create_table(
'participant', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('participant_id', sa.String(), nullable=True),
sa.Column('role_id', sa.Integer(), nullable=True),
sa.Column('partner_id', sa.Integer(), nullable=True),
sa.Column('supervisor_id', sa.Integer(), nullable=True),
sa.Column('gender',
sqlalchemy_utils.types.choice.ChoiceType(Participant.GENDER),
nullable=True), sa.Column('email',
sa.String(),
nullable=True),
sa.Column('location_id', sa.Integer(), nullable=True),
sa.Column('participant_set_id', sa.Integer(), nullable=False),
sa.Column('message_count', sa.Integer(), nullable=True),
sa.Column('accurate_message_count', sa.Integer(), nullable=True),
sa.Column('completion_rating', sa.Float(), nullable=True),
sa.Column('device_id', sa.String(), nullable=True),
sa.Column('password', sa.String(), nullable=True),
sa.Column('extra_data',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['location_id'], ['location.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['participant_set_id'], ['participant_set.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['partner_id'], ['participant_partner.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['role_id'], ['participant_role.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['supervisor_id'], ['participant.id'],
ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'participant_group', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('group_type_id', sa.Integer(), nullable=False),
sa.Column('participant_set_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['group_type_id'],
['participant_group_type.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['participant_set_id'], ['participant_set.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'samples_locations',
sa.Column('sample_id', sa.Integer(), nullable=False),
sa.Column('location_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['location_id'], ['location.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['sample_id'], ['sample.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('sample_id', 'location_id'))
op.create_table(
'participant_groups_participants',
sa.Column('group_id', sa.Integer(), nullable=False),
sa.Column('participant_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['group_id'], ['participant_group.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['participant_id'], ['participant.id'],
ondelete='CASCADE'))
op.create_table(
'participant_phone',
sa.Column('participant_id', sa.Integer(), nullable=False),
sa.Column('phone_id', sa.Integer(), nullable=False),
sa.Column('last_seen', sa.DateTime(), nullable=True),
sa.Column('verified', sa.Boolean(), nullable=True),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['participant_id'], ['participant.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['phone_id'], ['phone.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('participant_id', 'phone_id'))
op.create_table(
'submission', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('event_id', sa.Integer(), nullable=False),
sa.Column('form_id', sa.Integer(), nullable=False),
sa.Column('participant_id', sa.Integer(), nullable=True),
sa.Column('location_id', sa.Integer(), nullable=False),
sa.Column('data',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column('extra_data',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column('submission_type',
sqlalchemy_utils.types.choice.ChoiceType(
Submission.SUBMISSION_TYPES),
nullable=True),
sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('updated', sa.DateTime(), nullable=True),
sa.Column('sender_verified', sa.Boolean(), nullable=True),
sa.Column('quarantine_status',
sqlalchemy_utils.types.choice.ChoiceType(
Submission.QUARANTINE_STATUSES),
nullable=True),
sa.Column('verification_status',
sqlalchemy_utils.types.choice.ChoiceType(
Submission.VERIFICATION_STATUSES),
nullable=True),
sa.Column('incident_description', sa.String(), nullable=True),
sa.Column('incident_status',
sqlalchemy_utils.types.choice.ChoiceType(
Submission.INCIDENT_STATUSES),
nullable=True),
sa.Column('overridden_fields',
postgresql.ARRAY(sa.String()),
nullable=True),
sa.Column('conflicts',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['event_id'], ['event.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['form_id'], ['form.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['location_id'], ['location.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['participant_id'], ['participant.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_index('submission_data_idx',
'submission', ['data'],
unique=False,
postgresql_using='gin')
op.create_table(
'message', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('direction',
sqlalchemy_utils.types.choice.ChoiceType(Message.DIRECTIONS),
nullable=False),
sa.Column('recipient', sa.String(), nullable=True),
sa.Column('sender', sa.String(), nullable=True),
sa.Column('text', sa.String(), nullable=True),
sa.Column('received', sa.DateTime(), nullable=True),
sa.Column('delivered', sa.DateTime(), nullable=True),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('event_id', sa.Integer(), nullable=False),
sa.Column('submission_id', sa.Integer(), nullable=True),
sa.Column('participant_id', sa.Integer(), nullable=True),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['event_id'], ['event.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['participant_id'], ['participant.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['submission_id'], ['submission.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_message_received'),
'message', ['received'],
unique=False)
op.create_table(
'submission_comment', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('submission_id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('comment', sa.String(), nullable=True),
sa.Column('submit_date', sa.DateTime(), nullable=True),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['submission_id'], ['submission.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'submission_version', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('submission_id', sa.Integer(), nullable=False),
sa.Column('data',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('channel',
sqlalchemy_utils.types.choice.ChoiceType(
SubmissionVersion.CHANNEL_CHOICES),
nullable=True),
sa.Column('deployment_id', sa.Integer(), nullable=False),
sa.Column('identity', sa.String(), nullable=False),
sa.Column('uuid', postgresql.UUID(as_uuid=True), nullable=False),
sa.ForeignKeyConstraint(['deployment_id'], ['deployment.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['submission_id'], ['submission.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
# ----- initial fixtures -----
conn = op.get_bind()
conn.execute(text("""INSERT INTO deployment (
id, name, hostnames, allow_observer_submission_edit,
include_rejected_in_votes, is_initialized, dashboard_full_locations,
uuid)
VALUES (1, 'Default', '{\"localhost\"}', 't', 'f', 'f', 't',
:uuid)"""),
uuid=uuid4().hex)
op.execute("""
SELECT nextval(pg_get_serial_sequence('deployment', 'id'))
""")
conn.execute(text("""INSERT INTO resource (
resource_id, resource_type, deployment_id, uuid)
VALUES (1, 'event', 1, :uuid)"""),
uuid=uuid4().hex)
op.execute("""
SELECT nextval(pg_get_serial_sequence('resource', 'resource_id'))
""")
timestamp = datetime(1970, 1, 1, tzinfo=tzutc())
conn.execute(text("""INSERT INTO event (
id, name, start, \"end\", resource_id)
VALUES (1, 'Default', :ts, :ts, 1)
"""),
ts=timestamp)
op.execute("""
SELECT nextval(pg_get_serial_sequence('event', 'id'))
""")
conn.execute(text("""INSERT INTO role (
id, deployment_id, name, uuid) VALUES (1, 1, 'admin', :uuid)"""),
uuid=uuid4().hex)
op.execute("""
SELECT nextval(pg_get_serial_sequence('role', 'id'))
""")
conn.execute(text("""INSERT INTO role (
id, deployment_id, name, uuid) VALUES (2, 1, 'analyst', :uuid)"""),
uuid=uuid4().hex)
op.execute("""
SELECT nextval(pg_get_serial_sequence('role', 'id'))
""")
conn.execute(text("""INSERT INTO role (
id, deployment_id, name, uuid) VALUES (3, 1, 'manager', :uuid)"""),
uuid=uuid4().hex)
op.execute("""
SELECT nextval(pg_get_serial_sequence('role', 'id'))
""")
conn.execute(text("""INSERT INTO role (
id, deployment_id, name, uuid) VALUES (4, 1, 'clerk', :uuid)"""),
uuid=uuid4().hex)
op.execute("""
SELECT nextval(pg_get_serial_sequence('role', 'id'))
""")
conn.execute(text("""INSERT INTO role (
id, deployment_id, name, uuid) VALUES (5, 1, 'field-coordinator', :uuid)"""
),
uuid=uuid4().hex)
op.execute("""
SELECT nextval(pg_get_serial_sequence('role', 'id'))
""")
password = hash_password('admin')
conn.execute(text("""INSERT INTO \"user\" (
id, deployment_id, email, username, password, active, uuid)
VALUES (1, 1, '*****@*****.**', 'admin',
:password, 't', :uuid)"""),
password=password,
uuid=uuid4().hex)
op.execute("INSERT INTO roles_users (user_id, role_id) VALUES (1, 1)")
op.execute("""
SELECT nextval(pg_get_serial_sequence('user', 'id'))
""")
for name in dir(permissions):
item = getattr(permissions, name, None)
if isinstance(item, Permission):
for need in item.needs:
if need.method == 'action':
conn.execute(text(
"""INSERT INTO permission (name, deployment_id, uuid)
VALUES (:value, 1, :uuid)"""),
uuid=uuid4().hex,
value=need.value)
# clerk
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='clerk',
permission='add_submission')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='clerk',
permission='edit_both_submissions')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='clerk',
permission='edit_submission')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='clerk',
permission='view_messages')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='clerk',
permission='view_participants')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='clerk',
permission='view_events')
# manager
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='add_submission')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='edit_both_submissions')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='edit_submission')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='edit_submission_quarantine_status')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='edit_submission_verification_status')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='send_messages')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='view_events')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='view_messages')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='view_participants')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='manager',
permission='view_quality_assurance')
# analyst
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='add_submission')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='edit_both_submissions')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='edit_participant')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='edit_submission')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='edit_submission_quarantine_status')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='edit_submission_verification_status')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='export_messages')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='export_participants')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='export_submissions')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='send_messages')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='view_events')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='view_messages')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='view_participants')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='view_quality_assurance')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='view_process_analysis')
conn.execute(text("""INSERT INTO roles_permissions (role_id, permission_id)
VALUES ((SELECT id FROM role WHERE deployment_id=1 AND name=:role),
(SELECT id FROM permission WHERE deployment_id=1 AND name=:permission))
ON CONFLICT DO NOTHING"""),
role='analyst',
permission='view_result_analysis')
def upgrade():
op.create_table(
'itemsequence',
sa.Column('parent_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('value', sa.Integer(), server_default='0', nullable=False),
sa.PrimaryKeyConstraint('parent_id', 'value'))
op.execute(NEXT_ITEM_VALUE_FUNCTION)
op.create_table(
'itemoption', sa.Column('id',
zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('item_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=False),
sa.Column('value', sa.Text(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('item_id', 'name', name='unq_itemoption_name'))
op.create_table(
'itemstat', sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('item_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=False),
sa.Column('value', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('item_id', 'name', name='unq_itemstat_name'))
op.create_table(
'organization', sa.Column('name',
sa.String(length=200),
nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name'))
op.create_table(
'user', sa.Column('email', sa.String(length=128), nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email'))
op.create_table(
'author', sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('email', sa.String(length=128), nullable=True),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('organization_id',
'email',
name='unq_author_email'))
op.create_index(op.f('ix_author_organization_id'),
'author', ['organization_id'],
unique=False)
op.create_table(
'identity',
sa.Column('user_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('external_id', sa.String(length=64), nullable=False),
sa.Column('provider', sa.String(length=32), nullable=False),
sa.Column('config',
zeus.db.types.json.JSONEncodedDict(),
nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('external_id'),
sa.UniqueConstraint('user_id', 'provider', name='unq_identity_user'))
op.create_index(op.f('ix_identity_user_id'),
'identity', ['user_id'],
unique=False)
op.create_table(
'organization_access',
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('user_id', zeus.db.types.guid.GUID(), nullable=False),
sa.ForeignKeyConstraint(
['organization_id'],
['organization.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('organization_id', 'user_id'))
op.create_table(
'repository',
sa.Column('provider', zeus.db.types.enum.StrEnum(), nullable=False),
sa.Column('external_id', sa.String(length=64), nullable=True),
sa.Column('url', sa.String(length=200), nullable=False),
sa.Column('backend', zeus.db.types.enum.Enum(), nullable=False),
sa.Column('status', zeus.db.types.enum.Enum(), nullable=False),
sa.Column('data', zeus.db.types.json.JSONEncodedDict(), nullable=True),
sa.Column('last_update', sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column('last_update_attempt',
sa.TIMESTAMP(timezone=True),
nullable=True),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('organization_id',
'provider',
'external_id',
name='unq_external_id'))
op.create_index(op.f('ix_repository_organization_id'),
'repository', ['organization_id'],
unique=False)
op.create_table(
'project', sa.Column('name', sa.String(length=200), nullable=False),
sa.Column('repository_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('organization_id', 'name',
name='unq_project_name'))
op.create_index(op.f('ix_project_organization_id'),
'project', ['organization_id'],
unique=False)
op.create_index(op.f('ix_project_repository_id'),
'project', ['repository_id'],
unique=False)
op.create_table(
'repository_access',
sa.Column('repository_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('user_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['repository_id'],
['repository.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('repository_id', 'user_id'))
op.create_index(op.f('ix_repository_access_organization_id'),
'repository_access', ['organization_id'],
unique=False)
op.create_table(
'revision', sa.Column('sha', sa.String(length=40), nullable=False),
sa.Column('author_id', zeus.db.types.guid.GUID(), nullable=True),
sa.Column('committer_id', zeus.db.types.guid.GUID(), nullable=True),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('parents',
postgresql.ARRAY(sa.String(length=40)),
nullable=True),
sa.Column('branches',
postgresql.ARRAY(sa.String(length=128)),
nullable=True),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.Column('date_committed',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.Column('repository_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.ForeignKeyConstraint(
['author_id'],
['author.id'],
), sa.ForeignKeyConstraint(
['committer_id'],
['author.id'],
),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('sha'),
sa.UniqueConstraint('repository_id', 'sha', name='unq_revision'))
op.create_index(op.f('ix_revision_author_id'),
'revision', ['author_id'],
unique=False)
op.create_index(op.f('ix_revision_committer_id'),
'revision', ['committer_id'],
unique=False)
op.create_index(op.f('ix_revision_organization_id'),
'revision', ['organization_id'],
unique=False)
op.create_index(op.f('ix_revision_repository_id'),
'revision', ['repository_id'],
unique=False)
op.create_table(
'hook', sa.Column('token', sa.LargeBinary(length=64), nullable=False),
sa.Column('provider', sa.String(length=64), nullable=False),
sa.Column('project_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['project_id'], ['project.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('token'))
op.create_index(op.f('ix_hook_organization_id'),
'hook', ['organization_id'],
unique=False)
op.create_index(op.f('ix_hook_project_id'),
'hook', ['project_id'],
unique=False)
op.create_table(
'patch',
sa.Column('parent_revision_sha', sa.String(length=40), nullable=False),
sa.Column('diff', sa.Text(), nullable=False),
sa.Column('repository_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['repository_id', 'parent_revision_sha'],
['revision.repository_id', 'revision.sha'],
),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_index('idx_repo_sha',
'patch', ['repository_id', 'parent_revision_sha'],
unique=False)
op.create_index(op.f('ix_patch_organization_id'),
'patch', ['organization_id'],
unique=False)
op.create_index(op.f('ix_patch_repository_id'),
'patch', ['repository_id'],
unique=False)
op.create_table(
'source',
sa.Column('patch_id', zeus.db.types.guid.GUID(), nullable=True),
sa.Column('revision_sha', sa.String(length=40), nullable=False),
sa.Column('data', zeus.db.types.json.JSONEncodedDict(), nullable=True),
sa.Column('author_id', zeus.db.types.guid.GUID(), nullable=True),
sa.Column('repository_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.ForeignKeyConstraint(
['author_id'],
['author.id'],
),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['patch_id'],
['patch.id'],
),
sa.ForeignKeyConstraint(
['repository_id', 'revision_sha'],
['revision.repository_id', 'revision.sha'],
),
sa.ForeignKeyConstraint(['repository_id'], ['repository.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('patch_id'),
sa.UniqueConstraint('repository_id',
'revision_sha',
'patch_id',
name='unq_source_revision'))
op.create_index('idx_source_repo_sha',
'source', ['repository_id', 'revision_sha'],
unique=False)
op.create_index(op.f('ix_source_author_id'),
'source', ['author_id'],
unique=False)
op.create_index(op.f('ix_source_organization_id'),
'source', ['organization_id'],
unique=False)
op.create_index(op.f('ix_source_repository_id'),
'source', ['repository_id'],
unique=False)
op.create_table(
'build',
sa.Column('source_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('number', sa.Integer(), nullable=False),
sa.Column('label', sa.String(), nullable=False),
sa.Column('status', zeus.db.types.enum.Enum(), nullable=False),
sa.Column('result', zeus.db.types.enum.Enum(), nullable=False),
sa.Column('date_started', sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column('date_finished', sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column('data', zeus.db.types.json.JSONEncodedDict(), nullable=True),
sa.Column('provider', sa.String(), nullable=True),
sa.Column('external_id', sa.String(length=64), nullable=True),
sa.Column('project_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['project_id'], ['project.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['source_id'], ['source.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('project_id', 'number', name='unq_build_number'),
sa.UniqueConstraint('project_id',
'provider',
'external_id',
name='unq_build_provider'))
op.create_index(op.f('ix_build_organization_id'),
'build', ['organization_id'],
unique=False)
op.create_index(op.f('ix_build_project_id'),
'build', ['project_id'],
unique=False)
op.create_index(op.f('ix_build_source_id'),
'build', ['source_id'],
unique=False)
op.create_table(
'job', sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('build_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('number', sa.Integer(), nullable=False),
sa.Column('label', sa.String(), nullable=True),
sa.Column('status', zeus.db.types.enum.Enum(), nullable=False),
sa.Column('result', zeus.db.types.enum.Enum(), nullable=False),
sa.Column('date_started', sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column('date_finished', sa.TIMESTAMP(timezone=True), nullable=True),
sa.Column('data', zeus.db.types.json.JSONEncodedDict(), nullable=True),
sa.Column('provider', sa.String(), nullable=True),
sa.Column('external_id', sa.String(length=64), nullable=True),
sa.Column('project_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.ForeignKeyConstraint(['build_id'], ['build.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['project_id'], ['project.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('build_id', 'number', name='unq_job_number'),
sa.UniqueConstraint('build_id',
'provider',
'external_id',
name='unq_job_provider'))
op.create_index(op.f('ix_job_build_id'), 'job', ['build_id'], unique=False)
op.create_index(op.f('ix_job_organization_id'),
'job', ['organization_id'],
unique=False)
op.create_index(op.f('ix_job_project_id'),
'job', ['project_id'],
unique=False)
op.create_table(
'filecoverage',
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('job_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('filename', sa.String(length=256), nullable=False),
sa.Column('data', sa.Text(), nullable=False),
sa.Column('lines_covered', sa.Integer(), nullable=False),
sa.Column('lines_uncovered', sa.Integer(), nullable=False),
sa.Column('diff_lines_covered', sa.Integer(), nullable=False),
sa.Column('diff_lines_uncovered', sa.Integer(), nullable=False),
sa.Column('project_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.ForeignKeyConstraint(['job_id'], ['job.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['project_id'], ['project.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id', 'filename'),
sa.UniqueConstraint('job_id', 'filename', name='unq_job_filname'))
op.create_index(op.f('ix_filecoverage_organization_id'),
'filecoverage', ['organization_id'],
unique=False)
op.create_index(op.f('ix_filecoverage_project_id'),
'filecoverage', ['project_id'],
unique=False)
op.create_table(
'testcase', sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('job_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('hash', sa.String(length=40), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('result', zeus.db.types.enum.Enum(), nullable=False),
sa.Column('duration', sa.Integer(), nullable=True),
sa.Column('message', sa.Text(), nullable=True),
sa.Column('project_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.ForeignKeyConstraint(['job_id'], ['job.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['project_id'], ['project.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('job_id', 'hash', name='unq_testcase_hash'))
op.create_index(op.f('ix_testcase_organization_id'),
'testcase', ['organization_id'],
unique=False)
op.create_index(op.f('ix_testcase_project_id'),
'testcase', ['project_id'],
unique=False)
op.create_table(
'artifact',
sa.Column('job_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('testcase_id', zeus.db.types.guid.GUID(), nullable=True),
sa.Column('name', sa.String(length=256), nullable=False),
sa.Column('type',
zeus.db.types.enum.Enum(),
server_default='0',
nullable=False),
sa.Column('file', zeus.db.types.file.File(), nullable=False),
sa.Column('project_id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('organization_id', zeus.db.types.guid.GUID(),
nullable=False),
sa.Column('id', zeus.db.types.guid.GUID(), nullable=False),
sa.Column('date_created',
sa.TIMESTAMP(timezone=True),
server_default=sa.text('now()'),
nullable=False),
sa.ForeignKeyConstraint(['job_id'], ['job.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['project_id'], ['project.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['testcase_id'], ['testcase.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_artifact_organization_id'),
'artifact', ['organization_id'],
unique=False)
op.create_index(op.f('ix_artifact_project_id'),
'artifact', ['project_id'],
unique=False)
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('recipes',
'utilities',
existing_type=postgresql.ARRAY(sa.TEXT()),
nullable=True)
class ImplicitSpanMention(Context, TemporaryImplicitSpanMention):
"""A span of characters that may not appear verbatim in the source text.
It is identified by Context id, character-index start and end (inclusive),
as well as a key representing what 'expander' function drew the ImplicitSpanMention
from an existing SpanMention, and a position (where position=0 corresponds to the
first ImplicitSpanMention produced from the expander function).
The character-index start and end point to the segment of text that was
expanded to produce the ImplicitSpanMention.
"""
__tablename__ = "implicit_span_mention"
#: The unique id of the ``ImplicitSpanMention``.
id = Column(Integer,
ForeignKey("context.id", ondelete="CASCADE"),
primary_key=True)
#: The id of the parent ``Sentence``.
sentence_id = Column(Integer,
ForeignKey("context.id", ondelete="CASCADE"),
primary_key=True)
#: The parent ``Sentence``.
sentence = relationship("Context", foreign_keys=sentence_id)
#: The starting character-index of the ``ImplicitSpanMention``.
char_start = Column(Integer, nullable=False)
#: The ending character-index of the ``ImplicitSpanMention`` (inclusive).
char_end = Column(Integer, nullable=False)
#: The key representing the expander function which produced this
# ``ImplicitSpanMention``.
expander_key = Column(String, nullable=False)
#: The position of the ``ImplicitSpanMention`` where position=0 is the first
#: ``ImplicitSpanMention`` produced by the expander.
position = Column(Integer, nullable=False)
#: The raw text of the ``ImplicitSpanMention``.
text = Column(String)
#: A list of the words in the ``ImplicitSpanMention``.
words = Column(postgresql.ARRAY(String), nullable=False)
#: A list of the lemmas for each word in the ``ImplicitSpanMention``.
lemmas = Column(postgresql.ARRAY(String))
#: A list of the POS tags for each word in the ``ImplicitSpanMention``.
pos_tags = Column(postgresql.ARRAY(String))
#: A list of the NER tags for each word in the ``ImplicitSpanMention``.
ner_tags = Column(postgresql.ARRAY(String))
#: A list of the dependency parents for each word in the ``ImplicitSpanMention``.
dep_parents = Column(postgresql.ARRAY(Integer))
#: A list of the dependency labels for each word in the ``ImplicitSpanMention``.
dep_labels = Column(postgresql.ARRAY(String))
#: A list of the page number each word in the ``ImplicitSpanMention``.
page = Column(postgresql.ARRAY(Integer))
#: A list of each word's TOP bounding box coordinate in the
# ``ImplicitSpanMention``.
top = Column(postgresql.ARRAY(Integer))
#: A list of each word's LEFT bounding box coordinate in the
# ``ImplicitSpanMention``.
left = Column(postgresql.ARRAY(Integer))
#: A list of each word's BOTTOM bounding box coordinate in the
# ``ImplicitSpanMention``.
bottom = Column(postgresql.ARRAY(Integer))
#: A list of each word's RIGHT bounding box coordinate in the
# ``ImplicitSpanMention``.
right = Column(postgresql.ARRAY(Integer))
#: Pickled metadata about the ``ImplicitSpanMention``.
meta = Column(PickleType)
__table_args__ = (UniqueConstraint(sentence_id, char_start, char_end,
expander_key, position), )
__mapper_args__ = {
"polymorphic_identity": "implicit_span_mention",
"inherit_condition": (id == Context.id),
}
def _get_instance(self, **kwargs: Any) -> "ImplicitSpanMention":
return ImplicitSpanMention(**kwargs)
# We redefine these to use default semantics, overriding the operators
# inherited from TemporarySpan
def __eq__(self, other: object) -> bool:
if not isinstance(other, ImplicitSpanMention):
return NotImplemented
return self is other
def __ne__(self, other: object) -> bool:
if not isinstance(other, ImplicitSpanMention):
return NotImplemented
return self is not other
def __hash__(self) -> int:
return id(self)
class TestResult(db.Model, BaseModel):
LOG_TYPE = LogMessage.RUN_TEST
STATUS_QUEUED = 'Queued'
STATUS_IMPORTING = 'Importing'
STATUS_IMPORTED = 'Imported'
STATUS_IN_PROGRESS = 'In Progress'
STATUS_STORING = 'Storing'
STATUS_COMPLETED = 'Completed'
STATUS_ERROR = 'Error'
STATUSES = [
STATUS_QUEUED, STATUS_IMPORTING, STATUS_IMPORTED, STATUS_IN_PROGRESS,
STATUS_STORING, STATUS_COMPLETED, STATUS_ERROR
]
TEST_STATUSES = [
STATUS_QUEUED, STATUS_IMPORTING, STATUS_IMPORTED, STATUS_IN_PROGRESS,
STATUS_STORING
]
__tablename__ = 'test_result'
name = db.Column(db.String(200), nullable=False)
status = db.Column(db.Enum(*STATUSES, name='test_statuses'))
error = db.Column(db.String(300))
model_id = db.Column(db.Integer, db.ForeignKey('model.id'))
model = relationship(Model, backref=backref('tests', cascade='all,delete'))
model_name = db.Column(db.String(200))
data_set_id = db.Column(db.Integer,
db.ForeignKey('data_set.id', ondelete='SET NULL'))
dataset = relationship(DataSet, foreign_keys=[data_set_id])
examples_count = db.Column(db.Integer)
examples_fields = db.Column(postgresql.ARRAY(db.String))
examples_size = db.Column(db.Float)
parameters = db.Column(JSONType)
classes_set = db.Column(postgresql.ARRAY(db.String))
accuracy = db.Column(db.Float)
roc_auc = db.Column(JSONType)
metrics = db.Column(JSONType)
memory_usage = db.Column(db.Integer)
vect_data = deferred(db.Column(S3File))
fill_weights = db.Column(db.Boolean, default=False)
def __repr__(self):
return '<TestResult {0}>'.format(self.name)
def get_vect_data(self, num, segment):
from pickle import loads
data = loads(self.vect_data)
offset = 0
for k, v in data.items():
offset += v.shape[0]
if k == segment:
break
import numpy
if isinstance(data[segment], numpy.ndarray):
return data[num - offset]
return data[segment].getrow(num - offset).todense().tolist()[0]
def set_error(self, error, commit=True):
self.error = str(error)[:299]
self.status = TestResult.STATUS_ERROR
if commit:
self.save()
@property
def exports(self):
from api.async_tasks.models import AsyncTask
return AsyncTask.get_current_by_object(
self,
'api.model_tests.tasks.get_csv_results',
)
@property
def db_exports(self):
from api.async_tasks.models import AsyncTask
return AsyncTask.get_current_by_object(
self,
'api.model_tests.tasks.export_results_to_db',
)
@property
def confusion_matrix_calculations(self):
from api.async_tasks.models import AsyncTask
return AsyncTask.get_current_by_object(
self,
'api.model_tests.tasks.calculate_confusion_matrix',
statuses=AsyncTask.STATUSES)
@property
def can_edit(self):
if not self.model.can_edit:
self.reason_msg = self.model.reason_msg
return False
return super(TestResult, self).can_edit
@property
def can_delete(self):
if not self.model.can_delete:
self.reason_msg = self.model.reason_msg
return False
return super(TestResult, self).can_delete
def delete(self):
ds = self.dataset
super(TestResult, self).delete()
ds.unlock()
@property
def test_in_progress(self):
return self.status in self.TEST_STATUSES
class Item(Base):
__tablename__ = 'item'
item_id = Column(Integer, primary_key=True, autoincrement=False)
location = Column(Geography('POINT', spatial_index=True), nullable=False)
enwiki = Column(String, index=True)
entity = Column(postgresql.JSON)
categories = Column(postgresql.ARRAY(String))
old_tags = Column(postgresql.ARRAY(String))
qid = column_property('Q' + cast(item_id, String))
ewkt = column_property(func.ST_AsEWKT(location), deferred=True)
query_label = Column(String, index=True)
# extract = Column(String)
extract_names = Column(postgresql.ARRAY(String))
db_tags = relationship('ItemTag',
collection_class=set,
cascade='save-update, merge, delete, delete-orphan',
backref='item')
tags = association_proxy('db_tags', 'tag_or_key')
isa = relationship('IsA', secondary='item_isa')
wiki_extracts = relationship('Extract',
collection_class=attribute_mapped_collection('site'),
cascade='save-update, merge, delete, delete-orphan',
backref='item')
extracts = association_proxy('wiki_extracts', 'extract')
@property
def extract(self):
return self.extracts.get('enwiki')
@extract.setter
def extract(self, value):
self.extracts['enwiki'] = value
@property
def labels(self):
if not self.entity:
return None
return {l['language']: l['value']
for l in self.entity['labels'].values()}
def lang_text(self, field_name, lang='en'):
field_values = self.entity.get(field_name)
if not field_values:
return
if lang not in field_values:
lang = 'en' if 'en' in field_values else list(field_values.keys())[0]
return field_values[lang]
def label(self, lang='en'):
if not self.entity:
return self.enwiki or self.query_label or None
l = self.lang_text('labels', lang=lang)
if l:
return l['value']
def label_detail(self, lang='en'):
return self.lang_text('labels', lang=lang)
def description(self, lang='en'):
if not self.entity:
return
return self.lang_text('descriptions', lang=lang)
def label_and_description(self, languages):
labels = self.entity['labels']
descriptions = self.entity['descriptions']
for lang in languages:
code = lang.wikimedia_language_code
if code not in labels:
continue
description = descriptions[code]['value'] if code in descriptions else None
return {
'lang': lang,
'label': labels[code]['value'],
'description': description,
}
def label_best_language(self, languages):
if not languages:
return self.label()
labels = self.entity['labels']
for lang in languages:
code = lang if isinstance(lang, str) else lang.wikimedia_language_code
if code in labels:
return labels[code]['value']
return self.label()
def languages(self):
entity = self.entity
labels = {lang for lang in entity['labels'].keys() if '-' not in lang}
sitelinks = {i[:-4] for i in entity['sitelinks'].keys() if i.endswith('wiki')}
return labels | sitelinks
def more_endings_from_isa(self):
endings = set()
langs = self.languages()
# avoid trimming "cottage", it produces too many mismatches
skip_isa = {
5783996, # cottage
}
for isa in self.isa:
if isa.item_id in skip_isa or not isa.entity or 'missing' in isa.entity:
continue
for lang, label in isa.entity.get('labels', {}).items():
if lang in langs:
endings.add(label['value'])
return endings
@classmethod
def get_by_qid(cls, qid):
if qid and len(qid) > 1 and qid[0].upper() == 'Q' and qid[1:].isdigit():
return cls.query.get(qid[1:])
def label_and_qid(self, lang='en'):
label = self.label(lang=lang)
if label:
return '{label} ({item.qid})'.format(label=label, item=self)
else:
return self.qid
@property
def wikidata_uri(self):
return 'https://www.wikidata.org/wiki/Q{}'.format(self.item_id)
def get_lat_lon(self):
return session.query(func.ST_Y(self.location),
func.ST_X(self.location)).one()
def get_osm_url(self, zoom=18):
lat, lon = self.get_lat_lon()
return f'https://www.openstreetmap.org/#map={zoom}/{lat}/{lon}'
def get_extra_tags(self):
tags = set()
for qid in self.instanceof():
for tag in wikidata.extra_keys.get(qid, []):
if tag:
tags.add(tag[4:])
return tags
@property
def ref_keys(self):
return {f'ref:nrhp={v}' for v in (self.ref_nrhp() or [])}
def disused_tags(self):
tags = set()
prefixes = ('disused', 'was', 'abandoned', 'demolished',
'destroyed', 'ruins', 'historic')
for i in self.tags:
if i == 'amenity': # too generic
continue
if i == 'shop' and self.is_shopping_street():
continue
key = i.split('=')[0] if '=' in i else i
if key in disused_prefix_key:
tags |= {prefix + ':' + i for prefix in prefixes}
return tags
def calculate_tags(self, ignore_tags=None):
ignore_tags = set(ignore_tags or [])
# Ignore some overly generic tags from Wikidata objects:
# facility (Q13226383) - osm tag: amenity
# geographic location (Q2221906) - osm tag: location
# artificial entity (Q16686448) - osm tag: man_made
ignore_tags.update('amenity', 'location', 'man_made')
instanceof = self.instanceof()
tags = (self.get_extra_tags() | set(self.tags)) - ignore_tags
if matcher.could_be_building(tags, instanceof):
tags.add('building')
if any(n.lower().endswith(' church') for n in self.names().keys()):
tags.update({'amenity=place_of_worship', 'building=church'})
if 'shop' in tags and self.is_shopping_street():
tags.discard('shop')
tags |= self.ref_keys | self.disused_tags()
tags -= ignore_tags
return tags
def instanceof(self):
if self.entity and 'claims' not in self.entity:
subject = f'missing claims: {self.qid}'
body = f'''
Wikidata entity is missing claims
https://www.wikidata.org/wiki/{self.qid}
'''
mail.send_mail(subject, body)
if not self.entity or 'claims' not in self.entity:
return []
return [i['mainsnak']['datavalue']['value']['id']
for i in self.entity['claims'].get('P31', [])
if 'datavalue' in i['mainsnak']]
def identifiers(self):
ret = set()
for v in self.get_item_identifiers().values():
ret.update(v)
return ret
def identifier_values(self):
ret = defaultdict(set)
for osm_key, wikidata_values in self.get_item_identifiers().items():
for values, _ in wikidata_values:
ret[osm_key].update(values)
return ret
def get_item_identifiers(self):
if not self.entity:
return {}
property_map = [
('P238', ['iata'], 'IATA airport code'),
('P239', ['icao'], 'ICAO airport code'),
('P240', ['faa', 'ref'], 'FAA airport code'),
# ('P281', ['addr:postcode', 'postal_code'], 'postal code'),
('P296', ['ref', 'ref:train', 'railway:ref'], 'station code'),
('P300', ['ISO3166-2'], 'ISO 3166-2 code'),
('P590', ['ref:gnis', 'GNISID', 'gnis:id', 'gnis:feature_id'], 'USGS GNIS ID'),
('P649', ['ref:nrhp'], 'NRHP reference number'),
('P722', ['uic_ref'], 'UIC station code'),
('P782', ['ref'], 'LAU (local administrative unit)'),
('P836', ['ref:gss'], 'UK Government Statistical Service code'),
('P856', ['website', 'contact:website', 'url'], 'website'),
('P882', ['nist:fips_code'], 'FIPS 6-4 (US counties)'),
('P883', ['state_code', 'ref', 'nist:fips_code'], 'FIPS 5-2 (code for US states)'),
# A UIC id can be a IBNR, but not every IBNR is an UIC id
('P954', ['uic_ref'], 'IBNR ID'),
('P1216', ['HE_ref'], 'National Heritage List for England number'),
('P2253', ['ref:edubase'], 'EDUBase URN'),
('P2815', ['esr:user', 'ref', 'ref:train'], 'ESR station code'),
('P3425', ['ref', 'ref:SIC'], 'Natura 2000 site ID'),
('P3562', ['seamark:light:reference'], 'Admiralty number'),
('P4755', ['ref', 'ref:train', 'ref:crs', 'crs', 'nat_ref'], 'UK railway station code'),
('P4803', ['ref', 'ref:train'], 'Amtrak station code'),
('P6082', ['nycdoitt:bin'], 'NYC Building Identification Number'),
]
tags = defaultdict(list)
for claim, osm_keys, label in property_map:
values = [i['mainsnak']['datavalue']['value']
for i in self.entity['claims'].get(claim, [])
if 'datavalue' in i['mainsnak']]
if not values:
continue
if claim == 'P782':
values += [m.group(1) for m in (re_lau_code.match(v) for v in values) if m]
for osm_key in osm_keys:
tags[osm_key].append((tuple(values), label))
return tags
def ref_nrhp(self):
if self.entity:
return [i['mainsnak']['datavalue']['value']
for i in self.entity['claims'].get('P649', [])]
else:
return []
def is_cricket_ground(self):
return any('cricket' in name.lower() for name in self.names())
def names(self):
part_of_names = set()
if self.entity and 'claims' in self.entity:
for p361 in self.entity['claims'].get('P361', []):
try:
part_of_id = p361['mainsnak']['datavalue']['value']['numeric-id']
except KeyError:
continue
# TODO: download item if it doesn't exist
part_of_item = Item.query.get(part_of_id)
if part_of_item:
names = part_of_item.names()
if names:
part_of_names |= names.keys()
d = wikidata.names_from_entity(self.entity) or defaultdict(list)
for name in self.extract_names or []:
d[name].append(('extract', 'enwiki'))
for name, sources in list(d.items()):
if len(sources) == 1 and sources[0][0] == 'image':
continue
for part_of_name in part_of_names:
if not name.startswith(part_of_name):
continue
prefix_removed = name[len(part_of_name):].strip()
if prefix_removed not in d:
d[prefix_removed] = sources
# A terrace of buildings can be illustrated with a photo of a single building.
# We try to determine if this is the case and avoid using the filename of the
# single building photo as a name for matching.
def has_digit(s):
return any(c.isdigit() for c in s)
image_names = {name for name, sources in d.items()
if len(sources) == 1 and
sources[0][0] == 'image' and
has_digit(name)}
if not image_names:
return dict(d) or None
other_names = {n for n in d.keys() if n not in image_names and has_digit(n)}
for image_name in image_names:
for other in other_names:
if not utils.is_in_range(other, image_name):
continue
del d[image_name]
break
return dict(d) or None
def refresh_extract_names(self):
self.extract_names = wikipedia.html_names(self.extract)
def get_oql(self):
lat, lon = session.query(func.ST_Y(self.location), func.ST_X(self.location)).one()
union = []
for tag in self.tags:
osm_filter = 'around:1000,{:f},{:f}'.format(lat, lon)
union += oql_from_tag(tag, False, osm_filter)
return union
def coords(self):
return session.query(func.ST_Y(self.location), func.ST_X(self.location)).one()
def image_filenames(self):
return [i['mainsnak']['datavalue']['value']
for i in self.entity['claims'].get('P18', [])]
def defunct_cats(self):
words = {'demolish', 'disestablishment', 'defunct', 'abandon', 'mothballed',
'decommission', 'former', 'dismantled', 'disused', 'disassembled',
'abandoned', 'disband', 'scrapped', 'unused', 'closed', 'condemned',
'redundant'}
exclude = {'Defunct baseball venues in the United States',
'Defunct National Football League venues',
'Enclosed roller coasters',
'Former civil parishes in England',
'Capitals of former nations',
'Former state capitals in the United States'}
found = []
for item_cat in self.categories or []:
if item_cat in exclude:
continue
if item_cat.startswith('Former') and item_cat.endswith('Railway stations'):
# Category:Railway stations in the United Kingdom by former operator
# contains subcategories named 'Former ... Railway stations.'
# Most of the stations in these subcategories still exist.
# If a station doesn't exist it'll be in other defunct categories.
continue
lc_item_cat = item_cat.lower()
found += [item_cat for i in words if i in lc_item_cat]
return found
def get_claim(self, pid):
return [i['mainsnak']['datavalue']['value']
for i in self.entity['claims'].get(pid, [])]
@property
def criteria(self):
return {('Tag:' if '=' in t else 'Key:') + t for t in self.tags or []}
@property
def category_map(self):
if self.categories:
return matcher.categories_to_tags_map(self.categories)
def sitelinks(self):
if self.entity:
return self.entity.get('sitelinks')
def is_hamlet(self):
return ('Q5084' in self.instanceof() or
any(cat.startswith('Hamlets ')
for cat in self.categories or []))
def is_shopping_street(self):
return any(cat.startswith('Shopping street ')
for cat in self.categories or [])
def is_farm_house(self):
return 'Q489357' in self.instanceof()
def is_mountain_range(self):
return 'Q46831' in self.instanceof()
def is_farmhouse(self):
return 'Q489357' in self.instanceof()
def is_church_building(self):
return 'Q16970' in self.instanceof()
def is_reservoir(self):
return 'Q131681' in self.instanceof()
def is_proposed(self):
'''is this item a proposed building or structure?'''
cats = self.categories or []
if any(cat.startswith('Disused ') for cat in cats):
# disused stations that might be reopened could be in OSM
return False
if any(cat.startswith('Proposed ') for cat in cats):
return True
# proposed building or structure (Q811683)
return 'Q811683' in (self.instanceof() or [])
def is_a_historic_district(self):
cats = self.categories or []
return (('Q15243209' in (self.instanceof() or []) or
any(cat.startswith('Historic district') for cat in cats)) and
not any(cat.startswith('Historic district contributing properties') or
cat.startswith('Churches ') or
cat.startswith('Towers ') or
cat.startswith('Educational institutions ') or
cat.startswith('Schools ') or
cat.startswith('Houses ') or
cat.startswith('Historic house ') or
cat.startswith('Museums ') or
' buildings ' in cat or
cat.startswith('Buildings and structures ') for cat in cats))
def is_a_station(self):
stations = {
'Q55488', # railway station
'Q928830', # metro station
'Q4663385', # former railway station
}
if set(self.instanceof()) & stations:
return True
cats = {'railway stations', 'railroad stations', 'train stations',
'metro stations', 'subway stations'}
return any(any(cat in item_cat.lower() for cat in cats)
for item_cat in (self.categories or []))
def is_a_stadium(self):
isa = {
'Q483110', # stadium
'Q641226', # arena
'Q1076486', # sports venue
}
if set(self.instanceof()) & isa:
return True
cats = {'football venues', 'ice rinks', 'stadiums', 'velodromes',
'cycling venues', 'grounds'}
return any(any(cat in item_cat.lower() for cat in cats)
for item_cat in (self.categories or []))
def is_a_school(self):
return 'amenity=school' in self.tags
def skip_item_during_match(self):
''' cebwiki and svwiki contain lots of poor quality stubs
best to skip items that are only cebwiki or cebwiki + svwiki
'''
if self.is_proposed(): # skip proposed building or structure
return True
if not self.entity:
return False
item_isa_set = set(self.instanceof())
skip_isa = {
'Q21561328', # English unitary authority council
'Q21451686', # Scottish unitary authority council
'Q21451695', # Scottish local authority council
'Q1160920', # unitary authority
}
if item_isa_set & skip_isa:
return True
isa = {
'Q349084', # district of England
'Q1002812', # metropolitan borough
'Q1006876', # borough in the United Kingdom
'Q1187580', # non-metropolitan district
'Q1136601', # unitary authority of England
}
if item_isa_set & isa:
return False
sitelinks = self.entity.get('sitelinks')
if not sitelinks:
return False
sites = set(sitelinks.keys())
return sites == {'cebwiki'} or sites == {'cebwiki', 'svwiki'}
def get_names(self):
item = self.entity
if not item:
return
names = defaultdict(list)
skip_lang = {'ar', 'arc', 'pl'}
# only include aliases if there are less than 6 other names
if len(item.get('sitelinks', {})) < 6 and len(item['labels']) < 6:
for k, v in item.get('aliases', {}).items():
if k in skip_lang:
continue
if len(v) > 3:
continue
for name in v:
names[name].append(('alias', k))
for k, v in item['labels'].items():
if k in skip_lang:
continue
names[v].append(('label', k))
for k, v in item.get('sitelinks', {}).items():
if k + 'wiki' in skip_lang:
continue
names[v].append(('sitelink', k))
return names
def first_paragraph_all(self, languages):
for lang in languages:
if not lang:
continue
extract = self.first_paragraph_language(lang.site_name)
if extract:
yield {'lang': lang, 'extract': extract}
def first_paragraph(self, languages=None):
if languages is None:
languages = [Language.get_by_code('en')]
for lang in languages:
extract = self.first_paragraph_language(lang.site_name)
if extract:
return {'lang': lang, 'extract': extract}
def first_paragraph_language(self, lang):
extract = self.extracts.get(lang)
if not extract:
return
empty_list = ['<p><span></span></p>',
'<p><span></span>\n</p>',
'<p><span></span>\n\n</p>',
'<p>\n<span></span>\n</p>',
'<p>\n\n<span></span>\n</p>',
'<p>.\n</p>',
'<p class="mw-empty-elt">\n</p>',
'<p class="mw-empty-elt">\n\n</p>',
'<p class="mw-empty-elt">\n\n\n</p>']
text = extract.strip()
while True:
found_empty = False
for empty in empty_list:
if text.startswith(empty):
text = text[len(empty):].strip()
found_empty = True
if not found_empty:
break
close_tag = '</p>'
first_end_p_tag = text.find(close_tag)
if first_end_p_tag == -1:
# FIXME: e-mail admin
return text
return text[:first_end_p_tag + len(close_tag)]
def place_names(self):
names = set()
for place in self.places:
if not isinstance(place.address, list):
continue
names.update({i['name'] for i in place.address
if i['type'] != 'country_code'})
start = 'Isle of '
trimmed = {utils.drop_start(n, start) for n in names if n.startswith(start)}
return names | trimmed
def set_country_code(self):
for place in self.places:
if place.country_code:
g.country_code = place.country_code
return
@property
def is_nhle(self):
'''Is this a National Heritage List for England item?'''
return self.entity and 'P1216' in self.entity.get('claims', {})
def is_instance_of(self, isa_filter):
for isa in self.isa:
if isa.qid in isa_filter:
return True
for claim in isa.entity['claims'].get('P279', []):
if claim['mainsnak']['datavalue']['value']['id'] in isa_filter:
return True
def place_languages(self):
found = {}
for place in self.places:
for l in place.languages():
code = l['code']
if code not in found:
found[code] = {
'wikidata': l['wikidata'],
'osm': l['osm'] or 0,
'code': code,
}
else:
for key in 'wikidata', 'osm':
found[code][key] += l[key] or 0
top = sorted(found.items(),
key=lambda i: i[1]['wikidata'],
reverse=True)[:10]
return [v for k, v in top]
None,
ForeignKey(DATASET.c.id),
nullable=False),
PrimaryKeyConstraint('dataset_ref', 'classifier'),
UniqueConstraint('source_dataset_ref', 'dataset_ref'),
)
# --- S3-driver specific Tables ---
S3_DATASET = Table(
's3_dataset', _core.S3_METADATA,
Column('id', postgres.UUID(as_uuid=True), primary_key=True),
Column('base_name', String, nullable=False),
Column('band', String, nullable=False),
Column('bucket', String, nullable=False),
Column('macro_shape',
postgres.ARRAY(Integer, dimensions=1),
nullable=False),
Column('chunk_size', postgres.ARRAY(Integer, dimensions=1),
nullable=False), Column('numpy_type', String, nullable=False),
Column('dimensions', postgres.ARRAY(String, dimensions=1), nullable=False),
Column('regular_dims',
postgres.ARRAY(Boolean, dimensions=1),
nullable=False),
Column('regular_index',
postgres.ARRAY(Float(), dimensions=2),
nullable=False),
Column('irregular_index',
postgres.ARRAY(Float(), dimensions=2),
nullable=False))
'''An S3 dataset: an N-dimensional single band
An example record may look like:
from sqlalchemy.dialects import postgresql
from auth import models
from its_on.utils import AwareDateTime
metadata = sa.MetaData()
switches = sa.Table(
'switches',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('is_active', sa.Boolean, default=True),
sa.Column('is_hidden', sa.Boolean, default=False),
sa.Column('name', sa.String(255), unique=True),
sa.Column('group', sa.String(255)),
sa.Column('groups', postgresql.ARRAY(sa.String(255))),
sa.Column('version', sa.Integer, nullable=True),
sa.Column('ttl',
sa.Integer,
nullable=False,
default=lambda: settings.FLAG_TTL_DAYS),
sa.Column('comment', sa.Text),
sa.Column('created_at',
AwareDateTime,
default=lambda: datetime.datetime.utcnow(),
nullable=True),
sa.Column(
'updated_at',
AwareDateTime,
default=lambda: datetime.datetime.utcnow(),
onupdate=lambda: datetime.datetime.utcnow(),
from builtins import object
from sqlalchemy import Column, String, Integer, Text, ForeignKey, UniqueConstraint
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.orm import relationship, backref
from sqlalchemy.sql import text
from sqlalchemy.types import PickleType
from fonduer.snorkel.models.context import Context
from fonduer.snorkel.models.context import Document
from fonduer.snorkel.models.context import TemporaryContext
from fonduer.snorkel.models.context import TemporarySpan
from fonduer.snorkel.models.context import split_stable_id
from fonduer.snorkel.models.meta import snorkel_postgres
INT_ARRAY_TYPE = postgresql.ARRAY(Integer) if snorkel_postgres else PickleType
STR_ARRAY_TYPE = postgresql.ARRAY(String) if snorkel_postgres else PickleType
class Webpage(Document):
"""
Declares name for storage table.
"""
__tablename__ = 'webpage'
id = Column(Integer,
ForeignKey('document.id', ondelete='CASCADE'),
primary_key=True)
# Connects NewType records to generic Context records
url = Column(String)
host = Column(String)
page_type = Column(String)
class ImplicitSpan(Context, TemporaryImplicitSpan):
"""A span of characters that may not have appeared verbatim in the source text.
It is identified by Context id, character-index start and end (inclusive),
as well as a key representing what 'expander' function drew the ImplicitSpan
from an existing Span, and a position (where position=0 corresponds to the
first ImplicitSpan produced from the expander function).
The character-index start and end point to the segment of text that was
expanded to produce the ImplicitSpan.
"""
__tablename__ = 'implicit_span'
id = Column(Integer,
ForeignKey('context.id', ondelete='CASCADE'),
primary_key=True)
sentence_id = Column(Integer,
ForeignKey('context.id', ondelete='CASCADE'),
primary_key=True)
char_start = Column(Integer, nullable=False)
char_end = Column(Integer, nullable=False)
expander_key = Column(String, nullable=False)
position = Column(Integer, nullable=False)
text = Column(String)
if snorkel_postgres:
words = Column(postgresql.ARRAY(String), nullable=False)
lemmas = Column(postgresql.ARRAY(String))
pos_tags = Column(postgresql.ARRAY(String))
ner_tags = Column(postgresql.ARRAY(String))
dep_parents = Column(postgresql.ARRAY(Integer))
dep_labels = Column(postgresql.ARRAY(String))
page = Column(postgresql.ARRAY(Integer))
top = Column(postgresql.ARRAY(Integer))
left = Column(postgresql.ARRAY(Integer))
bottom = Column(postgresql.ARRAY(Integer))
right = Column(postgresql.ARRAY(Integer))
else:
words = Column(PickleType, nullable=False)
lemmas = Column(PickleType)
pos_tags = Column(PickleType)
ner_tags = Column(PickleType)
dep_parents = Column(PickleType)
dep_labels = Column(PickleType)
page = Column(PickleType)
top = Column(PickleType)
left = Column(PickleType)
bottom = Column(PickleType)
right = Column(PickleType)
meta = Column(PickleType)
__table_args__ = (UniqueConstraint(sentence_id, char_start, char_end,
expander_key, position), )
__mapper_args__ = {
'polymorphic_identity': 'implicit_span',
'inherit_condition': (id == Context.id)
}
sentence = relationship('Context',
backref=backref('implicit_spans',
cascade='all, delete-orphan'),
foreign_keys=sentence_id)
def _get_instance(self, **kwargs):
return ImplicitSpan(**kwargs)
# We redefine these to use default semantics, overriding the operators inherited from TemporarySpan
def __eq__(self, other):
return self is other
def __ne__(self, other):
return self is not other
def __hash__(self):
return id(self)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
"datasets",
sa.Column("dataset", sa.String(), nullable=False),
sa.Column(
"metadata",
postgresql.JSONB(astext_type=sa.Text()),
nullable=True,
server_default=sa.text("'{}'"),
),
sa.Column("created_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.Column("updated_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.PrimaryKeyConstraint("dataset"),
)
op.create_table(
"geostore",
sa.Column("gfw_geostore_id", postgresql.UUID(), nullable=False),
sa.Column("gfw_geojson", sa.TEXT(), nullable=True),
sa.Column("gfw_area__ha", sa.Numeric(), nullable=True),
sa.Column("gfw_bbox", postgresql.ARRAY(sa.Numeric()), nullable=True),
sa.Column("created_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.Column("updated_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.PrimaryKeyConstraint("gfw_geostore_id"),
)
op.create_index(
"geostore_gfw_geostore_id_idx",
"geostore",
["gfw_geostore_id"],
unique=False,
postgresql_using="hash",
)
op.create_table(
"userareas",
sa.Column("gfw_geostore_id", postgresql.UUID(), nullable=False),
sa.Column("gfw_geojson", sa.TEXT(), nullable=True),
sa.Column("gfw_area__ha", sa.Numeric(), nullable=True),
sa.Column("gfw_bbox", postgresql.ARRAY(sa.Numeric()), nullable=True),
sa.Column("created_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.Column("updated_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.PrimaryKeyConstraint("gfw_geostore_id"),
)
op.create_index(
"userarea_gfw_geostore_id_idx",
"userareas",
["gfw_geostore_id"],
unique=False,
postgresql_using="hash",
)
op.create_table(
"versions",
sa.Column("dataset", sa.String(), nullable=False),
sa.Column("version", sa.String(), nullable=False),
sa.Column("is_latest",
sa.Boolean(),
nullable=False,
server_default=sa.text("false")),
sa.Column("is_mutable",
sa.Boolean(),
nullable=False,
server_default=sa.text("false")),
sa.Column("status",
sa.String(),
nullable=False,
server_default=sa.text("'pending'")),
sa.Column(
"metadata",
postgresql.JSONB(astext_type=sa.Text()),
nullable=True,
server_default=sa.text("'{}'"),
),
sa.Column(
"change_log",
postgresql.ARRAY(postgresql.JSONB(astext_type=sa.Text())),
nullable=True,
server_default=sa.text("array[]::jsonb[]"),
),
sa.Column("created_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.Column("updated_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.ForeignKeyConstraint(
["dataset"],
["datasets.dataset"],
name="fk",
onupdate="CASCADE",
ondelete="CASCADE",
),
sa.PrimaryKeyConstraint("dataset", "version"),
)
op.create_table(
"assets",
sa.Column(
"asset_id",
postgresql.UUID(),
nullable=False,
server_default=sa.text("uuid_generate_v4()"),
),
sa.Column("dataset", sa.String(), nullable=False),
sa.Column("version", sa.String(), nullable=False),
sa.Column("asset_type", sa.String(), nullable=False),
sa.Column("asset_uri", sa.String(), nullable=False),
sa.Column("status",
sa.String(),
nullable=False,
server_default=sa.text("'pending'")),
sa.Column("is_managed",
sa.Boolean(),
nullable=False,
server_default=sa.text("true")),
sa.Column("is_default",
sa.Boolean(),
nullable=False,
server_default=sa.text("false")),
sa.Column(
"creation_options",
postgresql.JSONB(astext_type=sa.Text()),
nullable=False,
server_default=sa.text("'{}'"),
),
sa.Column(
"metadata",
postgresql.JSONB(astext_type=sa.Text()),
nullable=False,
server_default=sa.text("'{}'"),
),
sa.Column(
"fields",
postgresql.JSONB(astext_type=sa.Text()),
nullable=False,
server_default=sa.text("'[]'"),
),
sa.Column("extent",
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column("stats",
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column(
"change_log",
postgresql.ARRAY(postgresql.JSONB(astext_type=sa.Text())),
nullable=False,
server_default=sa.text("array[]::jsonb[]"),
),
sa.Column("created_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.Column("updated_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.ForeignKeyConstraint(
["dataset", "version"],
["versions.dataset", "versions.version"],
name="fk",
onupdate="CASCADE",
ondelete="CASCADE",
),
sa.PrimaryKeyConstraint("asset_id"),
sa.UniqueConstraint("asset_uri", name="uq_asset_uri"),
)
op.create_table(
"tasks",
sa.Column("task_id", postgresql.UUID(), nullable=False),
sa.Column("asset_id", postgresql.UUID(), nullable=False),
sa.Column("status", sa.String(), nullable=False),
sa.Column(
"change_log",
postgresql.ARRAY(postgresql.JSONB(astext_type=sa.Text())),
nullable=True,
),
sa.Column("created_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.Column("updated_on",
sa.DateTime(),
server_default=sa.text("now()"),
nullable=True),
sa.ForeignKeyConstraint(
["asset_id"],
["assets.asset_id"],
name="fk",
onupdate="CASCADE",
ondelete="CASCADE",
),
sa.PrimaryKeyConstraint("task_id"),
)
op.execute("""
CREATE TRIGGER latest_version
BEFORE INSERT OR UPDATE
ON public.versions
FOR EACH ROW
EXECUTE PROCEDURE public.reset_latest();
""")
op.execute("""
ALTER TABLE
public.userareas
INHERIT geostore
""")
def upgrade():
if context.is_offline_mode():
raise Exception('This upgrade is only possible in online mode')
op.create_table(
'room_principals',
sa.Column('read_access', sa.Boolean(), nullable=False),
sa.Column('full_access', sa.Boolean(), nullable=False),
sa.Column('permissions', postgresql.ARRAY(sa.String()),
nullable=False),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('room_id', sa.Integer(), nullable=False, index=True),
sa.Column('local_group_id', sa.Integer(), nullable=True, index=True),
sa.Column('mp_group_provider', sa.String(), nullable=True),
sa.Column('mp_group_name', sa.String(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True, index=True),
sa.Column('type',
PyIntEnum(PrincipalType,
exclude_values={
PrincipalType.email, PrincipalType.network,
PrincipalType.event_role
}),
nullable=False),
sa.CheckConstraint('NOT read_access', name='no_read_access'),
sa.CheckConstraint(
'read_access OR full_access OR array_length(permissions, 1) IS NOT NULL',
name='has_privs'),
sa.CheckConstraint(
'type != 1 OR (local_group_id IS NULL AND mp_group_name IS NULL AND '
'mp_group_provider IS NULL AND user_id IS NOT NULL)',
name='valid_user'),
sa.CheckConstraint(
'type != 2 OR (mp_group_name IS NULL AND mp_group_provider IS NULL AND user_id 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 mp_group_name IS NOT NULL AND '
'mp_group_provider IS NOT NULL)',
name='valid_multipass_group'),
sa.ForeignKeyConstraint(['local_group_id'], ['users.groups.id']),
sa.ForeignKeyConstraint(['room_id'], ['roombooking.rooms.id']),
sa.ForeignKeyConstraint(['user_id'], ['users.users.id']),
sa.PrimaryKeyConstraint('id'),
schema='roombooking')
op.create_index(None,
'room_principals', ['mp_group_provider', 'mp_group_name'],
schema='roombooking')
op.create_index('ix_uq_room_principals_user',
'room_principals', ['user_id', 'room_id'],
unique=True,
schema='roombooking',
postgresql_where=sa.text('type = 1'))
op.create_index('ix_uq_room_principals_local_group',
'room_principals', ['local_group_id', 'room_id'],
unique=True,
schema='roombooking',
postgresql_where=sa.text('type = 2'))
op.create_index('ix_uq_room_principals_mp_group',
'room_principals',
['mp_group_provider', 'mp_group_name', 'room_id'],
unique=True,
schema='roombooking',
postgresql_where=sa.text('type = 3'))
op.add_column('rooms',
sa.Column(
'protection_mode',
PyIntEnum(ProtectionMode,
exclude_values={ProtectionMode.inheriting}),
nullable=False,
server_default=str(ProtectionMode.protected.value)),
schema='roombooking')
_upgrade_permissions()
op.alter_column('rooms',
'protection_mode',
server_default=None,
schema='roombooking')
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(
'companies', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('currency', sa.String(length=10), nullable=False),
sa.Column('logo_url', sa.String(length=4096), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'clients', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('company_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=False),
sa.Column('contacts', postgresql.ARRAY(sa.String()), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(
['company_id'],
['companies.id'],
), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name', 'company_id'))
op.create_table(
'users', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('company_id', sa.Integer(), nullable=False),
sa.Column('username', sa.String(length=64), nullable=False),
sa.Column('email', sa.String(length=255), nullable=False),
sa.Column('password', postgresql.BYTEA(length=255), nullable=False),
sa.Column('is_admin', sa.Boolean(), nullable=False),
sa.Column('activation_token', postgresql.BYTEA(), nullable=False),
sa.Column('reset_password_token', postgresql.BYTEA(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(
['company_id'],
['companies.id'],
), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('email'))
op.create_table(
'proposals', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('company_id', sa.Integer(), nullable=False),
sa.Column('client_id', sa.Integer(), nullable=True),
sa.Column('share_uid', sa.String(length=10), nullable=False),
sa.Column('title', sa.String(length=100), nullable=False),
sa.Column('tags', postgresql.ARRAY(sa.String()), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('changed_status_at', sa.DateTime(), nullable=False),
sa.Column('status',
sa.Enum('draft',
'sent',
'won',
'lost',
'trash',
name='ProposalStatusEnum'),
nullable=False),
sa.Column('cover_image_url', sa.String(length=2048), nullable=False),
sa.ForeignKeyConstraint(
['client_id'],
['clients.id'],
), sa.ForeignKeyConstraint(
['company_id'],
['companies.id'],
), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('share_uid'))
op.create_table(
'blocks', sa.Column('uid', postgresql.UUID(), nullable=False),
sa.Column('proposal_id', sa.Integer(), nullable=True),
sa.Column('type',
sa.Enum('section',
'paragraph',
'subtitle',
'uli',
'oli',
'image',
'cost_table',
'quote',
'embed',
name='BlockTypeEnum'),
nullable=False),
sa.Column('data', postgresql.JSONB(), nullable=False),
sa.Column('ordering', sa.Integer(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(
['proposal_id'],
['proposals.id'],
), sa.PrimaryKeyConstraint('uid'))
op.create_table(
'proposal_last_seen', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('proposal_id', sa.Integer(), nullable=False),
sa.Column('seen_at', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(
['proposal_id'],
['proposals.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('user_id', 'proposal_id'))
op.create_table(
'shared_proposals', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('proposal_id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(), nullable=False),
sa.Column('cover_image_url', sa.String(length=2048), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.Column('sent_to', postgresql.ARRAY(sa.String()), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(
['proposal_id'],
['proposals.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'comment_threads', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('proposal_id', sa.Integer(), nullable=False),
sa.Column('block_uid', postgresql.UUID(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('resolved', sa.Boolean(), nullable=False),
sa.ForeignKeyConstraint(['block_uid'], ['blocks.uid'],
ondelete='CASCADE',
initially='DEFERRED',
deferrable=True),
sa.ForeignKeyConstraint(['proposal_id'], ['proposals.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'import_section_search',
sa.Column('company_id', sa.Integer(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('section_uid', postgresql.UUID(), nullable=False),
sa.Column('proposal_title', sa.Unicode(), nullable=False),
sa.Column('title', sa.Unicode(), nullable=False),
sa.Column('ts_title', postgresql.TSVECTOR(), nullable=False),
sa.Column('client', sa.Unicode(), nullable=False),
sa.Column('all_content', sa.Unicode(), nullable=False),
sa.Column('ts_all_content', postgresql.TSVECTOR(), nullable=False),
sa.ForeignKeyConstraint(
['company_id'],
['companies.id'],
),
sa.ForeignKeyConstraint(['section_uid'], ['blocks.uid'],
ondelete='CASCADE',
initially='DEFERRED',
deferrable=True),
sa.PrimaryKeyConstraint('section_uid'))
op.create_table(
'shared_blocks', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('uid', postgresql.UUID(), nullable=False),
sa.Column('shared_proposal_id', sa.Integer(), nullable=False),
sa.Column('type',
sa.Enum('section',
'paragraph',
'subtitle',
'uli',
'oli',
'image',
'cost_table',
'quote',
'embed',
name='BlockTypeEnum'),
nullable=False),
sa.Column('data', postgresql.JSONB(), nullable=False),
sa.Column('ordering', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['shared_proposal_id'],
['shared_proposals.id'],
), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('shared_proposal_id', 'uid'))
op.create_table(
'comments', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('thread_id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('comment', sa.Unicode(), nullable=False),
sa.ForeignKeyConstraint(['thread_id'], ['comment_threads.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'shared_comment_threads', sa.Column('id', sa.Integer(),
nullable=False),
sa.Column('shared_proposal_id', sa.Integer(), nullable=False),
sa.Column('block_id', sa.Integer(), nullable=False),
sa.Column('block_uid', postgresql.UUID(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('resolved', sa.Boolean(), nullable=False),
sa.ForeignKeyConstraint(
['block_id'],
['shared_blocks.id'],
),
sa.ForeignKeyConstraint(
['shared_proposal_id'],
['shared_proposals.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'shared_comments', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('username', sa.String(), nullable=False),
sa.Column('from_client', sa.Boolean(), nullable=False),
sa.Column('thread_id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=False),
sa.Column('comment', sa.Unicode(), nullable=False),
sa.ForeignKeyConstraint(
['thread_id'],
['shared_comment_threads.id'],
), sa.PrimaryKeyConstraint('id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'canceled_formal_offer_signal',
sa.Column('inserted_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=False),
sa.Column('payee_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('offer_id', sa.BigInteger(), nullable=False),
sa.PrimaryKeyConstraint('payee_creditor_id', 'offer_id'))
op.create_table(
'created_formal_offer_signal',
sa.Column('inserted_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=False),
sa.Column('payee_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('offer_id', sa.BigInteger(), nullable=False),
sa.Column('offer_announcement_id', sa.BigInteger(), nullable=False),
sa.Column('offer_secret', postgresql.BYTEA(), nullable=False),
sa.Column('offer_created_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=False),
sa.PrimaryKeyConstraint('payee_creditor_id', 'offer_id'))
op.create_table(
'failed_payment_signal',
sa.Column('inserted_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=False),
sa.Column('payee_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('offer_id', sa.BigInteger(), nullable=False),
sa.Column('payer_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('payer_payment_order_seqnum', sa.Integer(), nullable=False),
sa.Column('details',
postgresql.JSON(astext_type=sa.Text()),
nullable=False),
sa.PrimaryKeyConstraint('payee_creditor_id', 'offer_id',
'payer_creditor_id',
'payer_payment_order_seqnum'))
op.create_table(
'failed_reciprocal_payment_signal',
sa.Column('inserted_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=False),
sa.Column('payee_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('offer_id', sa.BigInteger(), nullable=False),
sa.Column('details',
postgresql.JSON(astext_type=sa.Text()),
nullable=False),
sa.PrimaryKeyConstraint('payee_creditor_id', 'offer_id'))
op.create_table(
'finalize_prepared_transfer_signal',
sa.Column('inserted_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=False),
sa.Column('payee_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('signal_id',
sa.BigInteger(),
autoincrement=True,
nullable=False),
sa.Column('debtor_id', sa.BigInteger(), nullable=False),
sa.Column('sender_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('transfer_id', sa.BigInteger(), nullable=False),
sa.Column('committed_amount', sa.BigInteger(), nullable=False),
sa.Column('transfer_info',
postgresql.JSON(astext_type=sa.Text()),
nullable=False), sa.CheckConstraint('committed_amount >= 0'),
sa.PrimaryKeyConstraint('payee_creditor_id', 'signal_id'))
op.create_table(
'formal_offer',
sa.Column(
'payee_creditor_id',
sa.BigInteger(),
nullable=False,
comment=
'The payee, also the one that is responsible to supply the goods or services.'
),
sa.Column('offer_id',
sa.BigInteger(),
autoincrement=True,
nullable=False),
sa.Column(
'offer_secret',
postgresql.BYTEA(),
nullable=False,
comment=
'A random sequence of bytes that the potential payer should know in order to view the offer or make a payment.'
),
sa.Column(
'debtor_ids',
postgresql.ARRAY(sa.BigInteger(), dimensions=1),
nullable=False,
comment=
'The payment should go through one of these debtors. Each element in this array must have a corresponding element in the `debtor_amounts` array. Note thatthe database schema allows some or all of the elements to be `NULL`, which should be handled with care.'
),
sa.Column(
'debtor_amounts',
postgresql.ARRAY(sa.BigInteger(), dimensions=1),
nullable=False,
comment=
'Each element in this array must have a corresponding element in the `debtor_ids` array. Note that the database schema allows one debtor ID to occur more than once in the `debtor_ids` array, each time with a different corresponding amount. The payer is expected to transfer one of the amounts corresponding to the chosen debtor. Also note that the database schema allows some or all of the `debtor_amounts` elements to be `NULL` or negative numbers, which should be handled as if they were zeros.'
),
sa.Column(
'description',
postgresql.JSON(astext_type=sa.Text()),
nullable=True,
comment=
'A more or less detailed description of the goods or services that will be supplied if a payment is made to the offer. `NULL` means that the payee has no responsibilities whatsoever.'
),
sa.Column(
'reciprocal_payment_debtor_id',
sa.BigInteger(),
nullable=True,
comment=
'The ID of the debtor through which the reciprocal payment will go.If this is not NULL, when a payment is made to the offer, an automatic reciprocal payment will be made from the payee to the payer.'
),
sa.Column(
'reciprocal_payment_amount',
sa.BigInteger(),
server_default=sa.text('0'),
nullable=False,
comment='The amount to be transferred in the reciprocal payment.'),
sa.Column('valid_until_ts',
sa.TIMESTAMP(timezone=True),
nullable=False,
comment='The offer will not be valid after this deadline.'),
sa.Column('created_at_ts', sa.TIMESTAMP(timezone=True),
nullable=False),
sa.CheckConstraint('array_ndims(debtor_amounts) = 1'),
sa.CheckConstraint('array_ndims(debtor_ids) = 1'),
sa.CheckConstraint(
'cardinality(debtor_ids) = cardinality(debtor_amounts)'),
sa.CheckConstraint('reciprocal_payment_amount >= 0'),
sa.CheckConstraint(
'reciprocal_payment_debtor_id IS NOT NULL OR reciprocal_payment_amount = 0'
),
sa.PrimaryKeyConstraint('payee_creditor_id', 'offer_id'),
comment=
'Represents an offer to supply some goods or services for a stated price.'
)
op.create_table(
'payment_order',
sa.Column('payee_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('offer_id', sa.BigInteger(), nullable=False),
sa.Column('payer_creditor_id',
sa.BigInteger(),
nullable=False,
comment='The payer.'),
sa.Column(
'payer_payment_order_seqnum',
sa.Integer(),
nullable=False,
comment=
'A number generated by the payer. It is used to distinguish between several payment orders issued against one offer.'
),
sa.Column(
'debtor_id',
sa.BigInteger(),
nullable=False,
comment=
'The ID of the debtor through which the payment should go. Must be one of the values in the `formal_offer.debtor_ids` array.'
),
sa.Column(
'amount',
sa.BigInteger(),
nullable=False,
comment=
'The amount to be transferred in the payment. Must be equal to the corresponding value in the `formal_offer.debtor_amounts` array.'
),
sa.Column(
'reciprocal_payment_debtor_id',
sa.BigInteger(),
nullable=True,
comment=
'A copy of the corresponding `formal_offer.reciprocal_payment_debtor_id`.'
),
sa.Column(
'reciprocal_payment_amount',
sa.BigInteger(),
nullable=False,
comment=
'A copy of the corresponding `formal_offer.reciprocal_payment_amount`.'
),
sa.Column(
'payer_note',
postgresql.JSON(astext_type=sa.Text()),
nullable=True,
comment=
'A note from the payer. Can be anything that the payer wants the payee to see.If the payment is successful, the content of this column will be copied over to the `payment_proof.payer_note` column. After that, the value can be set to NULL.'
),
sa.Column(
'proof_secret',
postgresql.BYTEA(),
nullable=True,
comment=
'A random sequence of bytes that the interested party should know in order to view the payment proof. If the payment is successful, the content of this column will be copied over to the `payment_proof.proof_secret` column. After that, the value can be set to NULL.'
),
sa.Column(
'payment_coordinator_request_id',
sa.BigInteger(),
server_default=sa.text(
"nextval('payment_coordinator_request_id_seq')"),
nullable=False,
comment=
'This is the value of the `coordinator_request_id` parameter, which has been sent with the `prepare_transfer` message for the payment. The value of `payee_creditor_id` is sent as the `coordinator_id` parameter. `coordinator_type` is "payment".'
),
sa.Column(
'payment_transfer_id',
sa.BigInteger(),
nullable=True,
comment=
'This value, along with `debtor_id` and `payer_creditor_id` uniquely identifies the prepared transfer for the payment.'
),
sa.Column(
'reciprocal_payment_transfer_id',
sa.BigInteger(),
nullable=True,
comment=
'When a reciprocal payment is required, this value along with `reciprocal_payment_debtor_id` and `payee_creditor_id` uniquely identifiesthe prepared transfer for the reciprocal payment. The reciprocal payment should be initiated only after the primary payment has been prepared successfully. The value of the `coordinator_request_id` parameter for the reciprocal payment should be `-payment_coordinator_request_id` (always a negative number). `coordinator_id` should be `payee_creditor_id`. `coordinator_type` should be "payment".'
),
sa.Column(
'finalized_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=True,
comment=
'The moment at which the payment order was finalized. NULL means that the payment order has not been finalized yet.'
),
sa.CheckConstraint('amount >= 0'),
sa.CheckConstraint(
'finalized_at_ts IS NOT NULL OR payer_note IS NOT NULL AND proof_secret IS NOT NULL'
),
sa.CheckConstraint('payment_coordinator_request_id > 0'),
sa.CheckConstraint('reciprocal_payment_amount >= 0'),
sa.CheckConstraint(
'reciprocal_payment_debtor_id IS NOT NULL OR reciprocal_payment_amount = 0'
),
sa.PrimaryKeyConstraint('payee_creditor_id', 'offer_id',
'payer_creditor_id',
'payer_payment_order_seqnum'),
comment=
'Represents a recent order from a payer to make a payment to an offer. Note that finalized payment orders (failed or successful) must not be deleted right away. Instead, after they have been finalized, they should stay in the database for at least few days. This is necessary in order to prevent problems caused by message re-delivery.'
)
op.create_index('idx_payment_coordinator_request_id',
'payment_order',
['payee_creditor_id', 'payment_coordinator_request_id'],
unique=True)
op.create_table(
'payment_proof',
sa.Column('payee_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('proof_id',
sa.BigInteger(),
autoincrement=True,
nullable=False),
sa.Column('proof_secret', postgresql.BYTEA(), nullable=False),
sa.Column('payer_creditor_id', sa.BigInteger(), nullable=False),
sa.Column(
'debtor_id',
sa.BigInteger(),
nullable=False,
comment='The ID of the debtor through which the payment went.'),
sa.Column('amount', sa.BigInteger(), nullable=False),
sa.Column('payer_note',
postgresql.JSON(astext_type=sa.Text()),
nullable=False),
sa.Column('paid_at_ts', sa.TIMESTAMP(timezone=True), nullable=False),
sa.Column('reciprocal_payment_debtor_id',
sa.BigInteger(),
nullable=True),
sa.Column('reciprocal_payment_amount', sa.BigInteger(),
nullable=False),
sa.Column('offer_id', sa.BigInteger(), nullable=False),
sa.Column('offer_created_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=False),
sa.Column('offer_description',
postgresql.JSON(astext_type=sa.Text()),
nullable=True),
sa.CheckConstraint('amount >= 0'),
sa.CheckConstraint('reciprocal_payment_amount >= 0'),
sa.CheckConstraint(
'reciprocal_payment_debtor_id IS NOT NULL OR reciprocal_payment_amount = 0'
),
sa.PrimaryKeyConstraint('payee_creditor_id', 'proof_id'),
comment=
'Represents an evidence that a payment has been made to an offer. (The corresponding offer has been deleted.)'
)
op.create_table(
'prepare_transfer_signal',
sa.Column('inserted_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=False),
sa.Column('payee_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('coordinator_request_id', sa.BigInteger(), nullable=False),
sa.Column('min_amount', sa.BigInteger(), nullable=False),
sa.Column('max_amount', sa.BigInteger(), nullable=False),
sa.Column('debtor_id', sa.BigInteger(), nullable=False),
sa.Column('sender_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('recipient_creditor_id', sa.BigInteger(), nullable=False),
sa.CheckConstraint('max_amount >= min_amount'),
sa.CheckConstraint('min_amount > 0'),
sa.PrimaryKeyConstraint('payee_creditor_id', 'coordinator_request_id'))
op.create_table(
'successful_payment_signal',
sa.Column('inserted_at_ts',
sa.TIMESTAMP(timezone=True),
nullable=False),
sa.Column('payee_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('offer_id', sa.BigInteger(), nullable=False),
sa.Column('payer_creditor_id', sa.BigInteger(), nullable=False),
sa.Column('payer_payment_order_seqnum', sa.Integer(), nullable=False),
sa.Column('debtor_id', sa.BigInteger(), nullable=False),
sa.Column('amount', sa.BigInteger(), nullable=False),
sa.Column('payer_note',
postgresql.JSON(astext_type=sa.Text()),
nullable=False),
sa.Column('paid_at_ts', sa.TIMESTAMP(timezone=True), nullable=False),
sa.Column('reciprocal_payment_debtor_id',
sa.BigInteger(),
nullable=True),
sa.Column('reciprocal_payment_amount', sa.BigInteger(),
nullable=False),
sa.Column('proof_id', sa.BigInteger(), nullable=False),
sa.CheckConstraint('amount >= 0'),
sa.CheckConstraint('reciprocal_payment_amount >= 0'),
sa.CheckConstraint(
'reciprocal_payment_debtor_id IS NOT NULL OR reciprocal_payment_amount = 0'
),
sa.PrimaryKeyConstraint('payee_creditor_id', 'offer_id',
'payer_creditor_id',
'payer_payment_order_seqnum'))
class Filterset(BaseScopedNameMixin, db.Model):
"""
Store filters to display a filtered set of jobs scoped by a board on SEO friendly URLs
Eg: `https://hasjob.co/f/machine-learning-jobs-in-bangalore`
"""
__tablename__ = 'filterset'
board_id = db.Column(None,
db.ForeignKey('board.id'),
nullable=False,
index=True)
board = db.relationship(Board)
parent = db.synonym('board')
#: Welcome text
description = db.Column(db.UnicodeText, nullable=False, default=u'')
#: Associated job types
types = db.relationship(JobType, secondary=filterset_jobtype_table)
#: Associated job categories
categories = db.relationship(JobCategory,
secondary=filterset_jobcategory_table)
tags = db.relationship(Tag, secondary=filterset_tag_table)
auto_tags = association_proxy('tags',
'title',
creator=lambda t: Tag.get(t, create=True))
domains = db.relationship(Domain, secondary=filterset_domain_table)
auto_domains = association_proxy('domains',
'name',
creator=lambda d: Domain.get(d))
geonameids = db.Column(postgresql.ARRAY(db.Integer(), dimensions=1),
default=[],
nullable=False)
remote_location = db.Column(db.Boolean,
default=False,
nullable=False,
index=True)
pay_currency = db.Column(db.CHAR(3), nullable=True, index=True)
pay_cash = db.Column(db.Integer, nullable=True, index=True)
equity = db.Column(db.Boolean, nullable=False, default=False, index=True)
keywords = db.Column(db.Unicode(250),
nullable=False,
default=u'',
index=True)
def __repr__(self):
return '<Filterset %s "%s">' % (self.board.title, self.title)
@classmethod
def get(cls, board, name):
return cls.query.filter(cls.board == board,
cls.name == name).one_or_none()
def url_for(self, action='view', _external=True, **kwargs):
kwargs.setdefault('subdomain',
self.board.name if self.board.not_root else None)
return super(Filterset, self).url_for(action,
name=self.name,
_external=_external,
**kwargs)
def to_filters(self, translate_geonameids=False):
location_names = []
if translate_geonameids and self.geonameids:
location_dict = location_geodata(self.geonameids)
for geonameid in self.geonameids:
# location_geodata returns related geonames as well
# so we prune it down to our original list
location_names.append(location_dict[geonameid]['name'])
return {
't': [jobtype.name for jobtype in self.types],
'c': [jobcategory.name for jobcategory in self.categories],
'k': [tag.name for tag in self.tags],
'd': [domain.name for domain in self.domains],
'l': location_names if translate_geonameids else self.geonameids,
'currency': self.pay_currency,
'pay': self.pay_cash,
'equity': self.equity,
'anywhere': self.remote_location,
'q': self.keywords
}
@classmethod
def from_filters(cls, board, filters):
basequery = cls.query.filter(cls.board == board)
if filters.get('t'):
basequery = basequery.join(filterset_jobtype_table).join(
JobType).filter(JobType.name.in_(filters['t'])).group_by(
Filterset.id).having(
db.func.count(filterset_jobtype_table.c.filterset_id)
== len(filters['t']))
else:
basequery = basequery.filter(~db.exists(
db.select([1]).where(
Filterset.id == filterset_jobtype_table.c.filterset_id)))
if filters.get('c'):
basequery = basequery.join(filterset_jobcategory_table).join(
JobCategory).filter(JobCategory.name.in_(
filters['c'])).group_by(Filterset.id).having(
db.func.count(filterset_jobcategory_table.c.
filterset_id) == len(filters['c']))
else:
basequery = basequery.filter(~db.exists(
db.select([1]).where(
Filterset.id ==
filterset_jobcategory_table.c.filterset_id)))
if filters.get('k'):
basequery = basequery.join(filterset_tag_table).join(Tag).filter(
Tag.name.in_(filters['k'])).group_by(Filterset.id).having(
db.func.count(filterset_tag_table.c.filterset_id) == len(
filters['k']))
else:
basequery = basequery.filter(~db.exists(
db.select([1]).where(
Filterset.id == filterset_tag_table.c.filterset_id)))
if filters.get('d'):
basequery = basequery.join(filterset_domain_table).join(
Domain).filter(Domain.name.in_(filters['d'])).group_by(
Filterset.id).having(
db.func.count(filterset_domain_table.c.filterset_id) ==
len(filters['d']))
else:
basequery = basequery.filter(~db.exists(
db.select([1]).where(
Filterset.id == filterset_domain_table.c.filterset_id)))
if filters.get('l'):
basequery = basequery.filter(
cls.geonameids == sorted(filters['l']))
else:
basequery = basequery.filter(cls.geonameids == [])
if filters.get('equity'):
basequery = basequery.filter(cls.equity == True) # NOQA
else:
basequery = basequery.filter(cls.equity == False) # NOQA
if filters.get('pay') and filters.get('currency'):
basequery = basequery.filter(
cls.pay_cash == filters['pay'],
cls.pay_currency == filters['currency'])
else:
basequery = basequery.filter(cls.pay_cash == None,
cls.pay_currency == None) # NOQA
if filters.get('q'):
basequery = basequery.filter(cls.keywords == filters['q'])
else:
basequery = basequery.filter(cls.keywords == '')
if filters.get('anywhere'):
basequery = basequery.filter(cls.remote_location == True) # NOQA
else:
basequery = basequery.filter(cls.remote_location == False) # NOQA
return basequery.one_or_none()
class TestExample(db.Model, BaseModel):
__tablename__ = 'test_example'
NONAME = 'noname'
NOT_FILED_ID = '-1'
example_id = db.Column(db.String(100))
name = db.Column(db.String(100))
label = db.Column(db.String(100))
pred_label = db.Column(db.String(100))
num = db.Column(db.Integer)
prob = db.Column(postgresql.ARRAY(db.Float))
data_input = db.Column(JSONType)
weighted_data_input = db.Column(JSONType)
test_result_id = db.Column(db.Integer, db.ForeignKey('test_result.id'))
test_result = relationship('TestResult',
backref=backref('examples',
cascade='all,delete'))
test_name = db.Column(db.String(200))
model_id = db.Column(db.Integer, db.ForeignKey('model.id'))
model = relationship('Model')
model_name = db.Column(db.String(200))
def __repr__(self):
return '<TestExample {0}>'.format(self.name)
@property
def parameters_weights(self):
res = []
def sort_by_weight(val):
return -val['weight']
def go_tree(params, prefix=''):
for name, val in params.iteritems():
if 'weight' in val and val['weight'] != 0:
if prefix:
val['name'] = '{0}->{1}'.format(prefix, name)
else:
val['name'] = name
res.append(val)
if 'weights' in val:
go_tree(val['weights'], prefix=name)
return res
go_tree(self.weighted_data_input)
res.sort(key=sort_by_weight)
return res
@property
def is_weights_calculated(self):
return self.weighted_data_input and self.weighted_data_input != {}
def calc_weighted_data(self):
if not self.data_input:
return None
from api.ml_models.helpers.features import get_features_vect_data
model = self.model
trainer = model.get_trainer()
feature_model = trainer._feature_model
segment = 'default'
if len(trainer.with_segmentation) > 0:
ndata = dict([(key.replace('->', '.'), val)
for key, val in self.data_input.iteritems()])
data = trainer._apply_feature_types(ndata)
segment = "_".join([
str(data[feature_name])
for feature_name in trainer._feature_model.group_by
])
features = trainer.features[segment]
for feature_name in trainer._feature_model.group_by:
features.pop(feature_name)
else:
try:
features = trainer.features[segment]
except:
features = feature_model.features
ndata = dict([(key.replace('->', '.'), val)
for key, val in self.data_input.iteritems()])
trainer._prepare_data(iter([
ndata,
]),
callback=None,
save_raw=False,
is_predict=True)
vect_data1 = trainer._get_vectorized_data(
segment, trainer._test_prepare_feature)
vect = scipy.sparse.hstack(vect_data1)
vect_data = vect.todense().tolist()[0]
data = get_features_vect_data(vect_data, features.items(),
feature_model.target_variable)
from api.ml_models.helpers.weights import get_example_params
segment = Segment.query.filter(Segment.name == segment,
Segment.model == model)[0]
model_weights = Weight.query.with_entities(
Weight.name, Weight.value).filter(Weight.segment_id == segment.id)
weighted_data = dict(
get_example_params(model_weights, self.data_input, data))
self.weighted_data_input = weighted_data
self.save()
del trainer
gc.collect()
@classmethod
def get_grouped(cls, field, model_id, test_result_id):
cursor = cls.query.filter_by(
model_id=model_id, test_result_id=test_result_id
).with_entities(
cls.pred_label,
cls.label,
cls.prob,
# Selecting field from json object isn't supported by alchemy,
# using literal column instead
expression.literal_column("data_input->>'{!s}'".format(field)
).label('group'))
groups = defaultdict(list)
for row in cursor.all():
groups[row[3]].append({
'label': row[0],
'pred': row[1],
'prob': row[2],
})
return [{
field: key,
'list': value
} for key, value in groups.iteritems()]
@classmethod
def get_data(cls, test_result_id, fields):
db_fields = []
for field in fields:
if field == 'id':
field = 'example_id'
db_field = getattr(cls, field, None)
if db_field:
db_fields.append(db_field)
else:
# Selecting field from json object isn't supported by alchemy,
# using literal column instead
db_fields.append(
expression.literal_column("data_input->>'{!s}'".format(
field.replace('data_input.', ''))).label(field))
cursor = cls.query.filter_by(
test_result_id=test_result_id).with_entities(*db_fields)
for row in cursor.all():
yield dict(zip(row.keys(), row))
def __init__(self, arg, default=None, **kw):
self.type = postgresql.ARRAY(arg.type)
self.default = default
GenericFunction.__init__(self, arg, **kw)
class _HStoreValsFunction(safunc.GenericFunction):
__return_type__ = pgdialect.ARRAY(satypes.Text)
def __init__(self, store, **kwargs):
safunc.GenericFunction.__init__(self, args=[store], **kwargs)
self.name = 'avals'
def upgrade():
# In snapshots < 4.5.5 failed_logins_counter may be null, from 4.5.5
# we want to make sure all null values will be replaced with zeros.
op.execute("""
UPDATE users
SET failed_logins_counter = 0
WHERE failed_logins_counter IS NULL;
""")
# Return the null constraint to the `failed_logins_counter` column.
op.alter_column('users', 'failed_logins_counter', nullable=False)
# server_default accepts string or SQL element only
op.add_column(
'executions',
sa.Column('is_dry_run',
sa.Boolean(),
nullable=False,
server_default='f'))
op.add_column('executions',
sa.Column('scheduled_for', UTCDateTime(), nullable=True))
op.execute('COMMIT')
# Add new execution status
op.execute("alter type execution_status add value 'scheduled'")
op.add_column(
'deployments',
sa.Column('capabilities', sa.PickleType(comparator=lambda *a: False)))
op.add_column('events', sa.Column('source_id', sa.Text(), nullable=True))
op.add_column('events', sa.Column('target_id', sa.Text(), nullable=True))
op.add_column('logs', sa.Column('source_id', sa.Text(), nullable=True))
op.add_column('logs', sa.Column('target_id', sa.Text(), nullable=True))
# Create the agents table
visibility_enum = postgresql.ENUM(*VisibilityState.STATES,
name='visibility_states',
create_type=False)
agent_states_enum = postgresql.ENUM(*AgentState.STATES,
name='agent_states')
op.create_table(
'agents', sa.Column('_storage_id', sa.Integer(), nullable=False),
sa.Column('id', sa.Text(), nullable=True),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('ip', sa.Text(), nullable=True),
sa.Column('install_method', sa.Text(), nullable=False),
sa.Column('system', sa.Text(), nullable=True),
sa.Column('version', sa.Text(), nullable=False),
sa.Column('state', agent_states_enum, nullable=False),
sa.Column('visibility', visibility_enum, nullable=True),
sa.Column('rabbitmq_username', sa.Text(), nullable=True),
sa.Column('rabbitmq_password', sa.Text(), nullable=True),
sa.Column('rabbitmq_exchange', sa.Text(), nullable=False),
sa.Column('created_at', UTCDateTime(), nullable=False),
sa.Column('updated_at', UTCDateTime(), nullable=True),
sa.Column('_node_instance_fk', sa.Integer(), nullable=False),
sa.Column('_tenant_id', sa.Integer(), nullable=False),
sa.Column('_creator_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['_creator_id'], [u'users.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['_node_instance_fk'],
[u'node_instances._storage_id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['_tenant_id'], [u'tenants.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('_storage_id'))
op.create_index(op.f('agents__tenant_id_idx'),
'agents', ['_tenant_id'],
unique=False)
op.create_index(op.f('agents_created_at_idx'),
'agents', ['created_at'],
unique=False)
op.create_index(op.f('agents_id_idx'), 'agents', ['id'], unique=False)
# Remove the deprecated column private_resource from all the
# resources tables
for table_name in resource_tables:
op.drop_column(table_name, 'private_resource')
op.create_table(
'tasks_graphs',
sa.Column('_storage_id',
sa.Integer(),
autoincrement=True,
nullable=False), sa.Column('id', sa.Text(), nullable=True),
sa.Column('visibility', visibility_enum, nullable=True),
sa.Column('name', sa.Text(), nullable=True),
sa.Column('created_at', UTCDateTime(), nullable=False),
sa.Column('_execution_fk', sa.Integer(), nullable=False),
sa.Column('_tenant_id', sa.Integer(), nullable=False),
sa.Column('_creator_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['_creator_id'], [u'users.id'],
name=op.f('tasks_graphs__creator_id_fkey'),
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['_execution_fk'], [u'executions._storage_id'],
name=op.f('tasks_graphs__execution_fk_fkey'),
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['_tenant_id'], [u'tenants.id'],
name=op.f('tasks_graphs__tenant_id_fkey'),
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('_storage_id', name=op.f('tasks_graphs_pkey')))
op.create_index(op.f('tasks_graphs__tenant_id_idx'),
'tasks_graphs', ['_tenant_id'],
unique=False)
op.create_index(op.f('tasks_graphs_created_at_idx'),
'tasks_graphs', ['created_at'],
unique=False)
op.create_index(op.f('tasks_graphs_id_idx'),
'tasks_graphs', ['id'],
unique=False)
op.create_table(
'operations',
sa.Column('_storage_id',
sa.Integer(),
autoincrement=True,
nullable=False), sa.Column('id', sa.Text(), nullable=True),
sa.Column('visibility', visibility_enum, nullable=True),
sa.Column('name', sa.Text(), nullable=True),
sa.Column('state', sa.Text(), nullable=False),
sa.Column('created_at', UTCDateTime(), nullable=False),
sa.Column('dependencies', postgresql.ARRAY(sa.Text()), nullable=True),
sa.Column('type', sa.Text(), nullable=True),
sa.Column('parameters', JSONString(), nullable=True),
sa.Column('_tasks_graph_fk', sa.Integer(), nullable=False),
sa.Column('_tenant_id', sa.Integer(), nullable=False),
sa.Column('_creator_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['_creator_id'], [u'users.id'],
name=op.f('operations__creator_id_fkey'),
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['_tasks_graph_fk'],
[u'tasks_graphs._storage_id'],
name=op.f('operations__tasks_graph_fk_fkey'),
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['_tenant_id'], [u'tenants.id'],
name=op.f('operations__tenant_id_fkey'),
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('_storage_id', name=op.f('operations_pkey')))
op.create_index(op.f('operations__tenant_id_idx'),
'operations', ['_tenant_id'],
unique=False)
op.create_index(op.f('operations_created_at_idx'),
'operations', ['created_at'],
unique=False)
op.create_index(op.f('operations_id_idx'),
'operations', ['id'],
unique=False)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
'Venue',
sa.Column('genres', postgresql.ARRAY(sa.String()), nullable=True))
class GoldLabel(AnnotationMixin, _meta.Base):
"""A separate class for labels from human annotators or other gold standards."""
values = Column(postgresql.ARRAY(Integer), nullable=False)
class Annotation(Base):
"""Model class representing a single annotation."""
__tablename__ = "annotation"
__table_args__ = (
# Tags are stored in an array-type column, and indexed using a
# generalised inverted index. For more information on the use of GIN
# indices for array columns, see:
#
# http://www.databasesoup.com/2015/01/tag-all-things.html
# http://www.postgresql.org/docs/9.5/static/gin-intro.html
#
sa.Index("ix__annotation_tags", "tags", postgresql_using="gin"),
sa.Index("ix__annotation_updated", "updated"),
# This is a functional index on the *first* of the annotation's
# references, pointing to the top-level annotation it refers to. We're
# using 1 here because Postgres uses 1-based array indexing.
sa.Index("ix__annotation_thread_root", sa.text('("references"[1])')),
)
#: Annotation ID: these are stored as UUIDs in the database, and mapped
#: transparently to a URL-safe Base64-encoded string.
id = sa.Column(types.URLSafeUUID,
server_default=sa.func.uuid_generate_v1mc(),
primary_key=True)
#: The timestamp when the annotation was created.
created = sa.Column(
sa.DateTime,
default=datetime.datetime.utcnow,
server_default=sa.func.now(),
nullable=False,
)
#: The timestamp when the user edited the annotation last.
updated = sa.Column(
sa.DateTime,
server_default=sa.func.now(),
default=datetime.datetime.utcnow,
nullable=False,
)
#: The full userid (e.g. 'acct:[email protected]') of the owner of this
#: annotation.
userid = sa.Column(sa.UnicodeText, nullable=False, index=True)
#: The string id of the group in which this annotation is published.
#: Defaults to the global public group, "__world__".
groupid = sa.Column(
sa.UnicodeText,
default="__world__",
server_default="__world__",
nullable=False,
index=True,
)
group = sa.orm.relationship(
Group,
primaryjoin=(Group.pubid == groupid),
foreign_keys=[groupid],
lazy="select",
)
#: The textual body of the annotation.
_text = sa.Column("text", sa.UnicodeText)
#: The Markdown-rendered and HTML-sanitized textual body of the annotation.
_text_rendered = sa.Column("text_rendered", sa.UnicodeText)
#: The tags associated with the annotation.
tags = sa.Column(
MutableList.as_mutable(pg.ARRAY(sa.UnicodeText, zero_indexes=True)))
#: A boolean indicating whether this annotation is shared with members of
#: the group it is published in. "Private"/"Only me" annotations have
#: shared=False.
shared = sa.Column(
sa.Boolean,
nullable=False,
default=False,
server_default=sa.sql.expression.false(),
)
#: The URI of the annotated page, as provided by the client.
_target_uri = sa.Column("target_uri", sa.UnicodeText)
#: The URI of the annotated page in normalized form.
_target_uri_normalized = sa.Column("target_uri_normalized", sa.UnicodeText)
#: The serialized selectors for the annotation on the annotated page.
target_selectors = sa.Column(types.AnnotationSelectorJSONB,
default=list,
server_default=sa.func.jsonb("[]"))
#: An array of annotation IDs which are ancestors of this annotation.
references = sa.Column(
pg.ARRAY(types.URLSafeUUID, zero_indexes=True),
default=list,
server_default=sa.text("ARRAY[]::uuid[]"),
)
#: Any additional serialisable data provided by the client.
extra = sa.Column(
MutableDict.as_mutable(pg.JSONB),
default=dict,
server_default=sa.func.jsonb("{}"),
nullable=False,
)
#: Has the annotation been deleted?
deleted = sa.Column(
sa.Boolean,
nullable=False,
default=False,
server_default=sa.sql.expression.false(),
)
document_id = sa.Column(sa.Integer,
sa.ForeignKey("document.id"),
nullable=False)
document = sa.orm.relationship("Document", backref="annotations")
thread = sa.orm.relationship(
"Annotation",
primaryjoin=(sa.orm.foreign(id) == sa.orm.remote(references[0])),
viewonly=True,
uselist=True,
)
@hybrid_property
def target_uri(self):
return self._target_uri
@target_uri.setter
def target_uri(self, value):
self._target_uri = value
self._target_uri_normalized = uri.normalize(value)
@hybrid_property
def target_uri_normalized(self):
return self._target_uri_normalized
@property
def target(self):
target = {"source": self.target_uri}
if self.target_selectors:
target["selector"] = self.target_selectors
return [target]
@hybrid_property
def text(self):
return self._text
@text.setter
def text(self, value):
self._text = value
# N.B. We MUST take care here of appropriately escaping the user
# input. Code elsewhere will assume that the content of the
# `text_rendered` field is safe for printing without further escaping.
#
# `markdown.render` does the hard work for now.
self._text_rendered = markdown_render.render(value)
@hybrid_property
def text_rendered(self):
return self._text_rendered
@property
def thread_ids(self):
return [thread_annotation.id for thread_annotation in self.thread]
@property
def is_reply(self):
return bool(self.references)
@property
def parent_id(self):
"""
Return the ID of the annotation that this annotation is a reply to.
Return None if this annotation is not a reply.
"""
if not self.references:
return None
return self.references[-1]
@property
def thread_root_id(self):
"""
Return the ID of the root annotation of this annotation's thread.
Return the ID of the root annotation of the thread to which this
annotation belongs. May be this annotation's own ID if it is the root
annotation of its thread.
"""
if self.references:
return self.references[0]
return self.id
@property
def authority(self):
"""
Return the authority of the user and group this annotation belongs to.
For example, returns "hypothes.is" for Hypothesis first-party
annotations, or "elifesciences.org" for eLife third-party annotations.
If this annotation doesn't have a userid (which is possible for
annotations that haven't been saved to the DB yet) then return None.
:raises ValueError: if the annotation's userid is invalid
"""
if self.userid is None:
return None
return split_user(self.userid)["domain"]
@property
def is_hidden(self):
"""Check if this annotation id is hidden."""
return self.moderation is not None
def __repr__(self):
return f"<Annotation {self.id}>"
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column('venue',
'genres',
existing_type=postgresql.ARRAY(sa.VARCHAR()),
nullable=True)
def SQLARRAY(item_type):
return postgresql.ARRAY(item_type)\
.with_variant(DefaultARRAY(item_type), "sqlite")
# (breaking, eg, product deletion scripts)
# - they may be in a separate database.
PRODUCT = Table(
"product",
METADATA,
Column("id", SmallInteger, primary_key=True),
Column("name", String, unique=True, nullable=False),
Column("dataset_count", Integer, nullable=False),
Column(
"last_refresh",
DateTime(timezone=True),
nullable=False,
server_default=func.now(),
comment="Last refresh of this product in the dataset_spatial table",
),
Column("source_product_refs", postgres.ARRAY(SmallInteger)),
Column("derived_product_refs", postgres.ARRAY(SmallInteger)),
Column("time_earliest", DateTime(timezone=True)),
Column("time_latest", DateTime(timezone=True)),
)
TIME_OVERVIEW = Table(
"time_overview",
METADATA,
# Uniquely identified by three values:
Column("product_ref", None, ForeignKey(PRODUCT.c.id)),
Column("period_type",
Enum("all", "year", "month", "day", name="overviewperiod")),
Column("start_day", Date),
Column("dataset_count", Integer, nullable=False),
# Time range (if there's at least one dataset)
Column("time_earliest", DateTime(timezone=True)),
import loguru
from collections import defaultdict
from itertools import chain
import argparse
from fonduer import Meta
from fonduer.parser.models import Document, Sentence
import json
from fonduer.meta import Meta as Mt
from sqlalchemy import Column, Integer, String, Text, ForeignKey
from sqlalchemy.dialects import postgresql
from os.path import join
import re
STR_ARRAY_TYPE = postgresql.ARRAY(String)
_meta = Mt.init()
class Latex(_meta.Base):
"""Latex representation of sentences"""
__tablename__ = "latexsentence"
id = Column(Integer, primary_key=True)
name = Column(String, unique=False, nullable=True)
#: The id of the parent ``Document``.
document_id = Column(Integer)
#: The id of the parent ``Section``.
class NumpyArray(sa.types.TypeDecorator):
impl = psql.ARRAY(sa.Float)
def process_result_value(self, value, dialect):
return np.array(value)
sa.DateTime(timezone=True),
server_default=sa.func.now(),
onupdate=sa.func.current_timestamp()),
#: Field for synchronization with external services.
sa.Column('integration_id', sa.String(length=512)),
sa.Column('domain_name',
sa.String(length=64),
sa.ForeignKey('domains.name',
onupdate='CASCADE',
ondelete='CASCADE'),
nullable=False,
index=True),
# TODO: separate resource-related fields with new domain resource policy table when needed.
sa.Column('total_resource_slots', ResourceSlotColumn(), default='{}'),
sa.Column('allowed_vfolder_hosts',
pgsql.ARRAY(sa.String),
nullable=False,
default='{}'),
sa.UniqueConstraint('name',
'domain_name',
name='uq_groups_name_domain_name'),
# dotfiles column, \x90 means empty list in msgpack
sa.Column('dotfiles',
sa.LargeBinary(length=MAXIMUM_DOTFILE_SIZE),
nullable=False,
default=b'\x90'),
)
async def resolve_group_name_or_id(
db_conn: SAConnection, domain_name: str,
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('ratings',
sa.Column('creation_date', sa.Integer(), nullable=False),
sa.Column('modification_date', sa.Integer(), nullable=True),
sa.Column('id', postgresql.UUID(), nullable=False),
sa.Column('version', sa.Integer(), nullable=True),
sa.Column('tags', postgresql.ARRAY(sa.String()), nullable=True),
sa.Column('creator', sa.String(), nullable=True),
sa.Column('modifier', sa.String(), nullable=True),
sa.Column('movie_id', postgresql.UUID(), nullable=False),
sa.Column('person_id', postgresql.UUID(), nullable=False),
sa.Column('overall_rate', sa.Float(), nullable=True),
sa.Column('comment', sa.String(), nullable=True),
sa.Column('novel', sa.Float(), nullable=True),
sa.Column('character', sa.Float(), nullable=True),
sa.Column('reason', sa.Float(), nullable=True),
sa.Column('directing', sa.Float(), nullable=True),
sa.Column('acting', sa.Float(), nullable=True),
sa.Column('editing', sa.Float(), nullable=True),
sa.Column('visualization', sa.Float(), nullable=True),
sa.Column('sound', sa.Float(), nullable=True),
sa.Column('music', sa.Float(), nullable=True),
sa.Column('violence_range', sa.Float(), nullable=True),
sa.Column('insulting_range', sa.Float(), nullable=True),
sa.Column('sexual_content', sa.Float(), nullable=True),
sa.Column('unsuitable_wearing', sa.Float(), nullable=True),
sa.Column('addiction_promotion', sa.Float(), nullable=True),
sa.Column('horror_content', sa.Float(), nullable=True),
sa.Column('suicide_encouragement', sa.Float(), nullable=True),
sa.Column('breaking_law_encouragement', sa.Float(), nullable=True),
sa.Column('gambling_promotion', sa.Float(), nullable=True),
sa.Column('alcoholic_promotion', sa.Float(), nullable=True),
sa.Column('family_subject', sa.Float(), nullable=True),
sa.Column('individual_social_behavior', sa.Float(), nullable=True),
sa.Column('feminism_exposure', sa.Float(), nullable=True),
sa.Column('justice_seeking', sa.Float(), nullable=True),
sa.Column('theism', sa.Float(), nullable=True),
sa.Column('bright_future_exposure', sa.Float(), nullable=True),
sa.Column('hope', sa.Float(), nullable=True),
sa.Column('iranian_life_style', sa.Float(), nullable=True),
sa.Column('true_vision_of_enemy', sa.Float(), nullable=True),
sa.Column('true_historiography', sa.Float(), nullable=True),
sa.Column('question_1', sa.String(), nullable=True),
sa.Column('question_2', sa.String(), nullable=True),
sa.Column('question_3', sa.String(), nullable=True),
sa.Column('question_4', sa.String(), nullable=True),
sa.Column('question_5', sa.String(), nullable=True),
sa.Column('question_6', sa.String(), nullable=True),
sa.Column('question_7', sa.String(), nullable=True),
sa.Column('question_8', sa.String(), nullable=True),
sa.Column('question_9', sa.String(), nullable=True),
sa.Column('question_10', sa.String(), nullable=True),
sa.ForeignKeyConstraint(['movie_id'], ['movies.id'], name=op.f('fk_tb_ratings_col_movie_id')),
sa.ForeignKeyConstraint(['person_id'], ['persons.id'], name=op.f('fk_tb_ratings_col_person_id')),
sa.PrimaryKeyConstraint('id', name=op.f('pk_tb_ratings')),
sa.UniqueConstraint('id', name=op.f('ratings_id_key'))
)
op.create_unique_constraint(op.f('movies_id_key'), 'movies', ['id'])
op.drop_constraint('users_id_key', 'users', type_='unique')
op.create_unique_constraint(op.f('users_id_key'), 'users', ['id'])
