def _create_tables(self, schema):
version = schema['versions'][-1]
subpaths = version.get('subpaths', {})
resource_type = schema['type']
files = version.get('files', [])
# Define main table
main_table = sa.Table(
chop_long_name(resource_type), self.metadata,
sa.Column('id', sa.String(46), primary_key=True),
sa.Column('revision', sa.String(46)),
sa.Column('search', JSONB, nullable=False),
sa.Column('data', JSONB, nullable=False),
*(sa.Column('data_' + subpath, JSONB, nullable=True)
for subpath in sorted(subpaths.keys())))
self.tables[resource_type] = main_table
# Define gin index for EXACT searches
self._add_index(chop_long_name('gin_idx_' + resource_type),
main_table.name, main_table.c.search)
# Define auxiliary tables and gin indexes for all nested lists.
aux_table = sa.Table(
chop_long_name(resource_type + '__aux'),
self.metadata,
sa.Column('id',
sa.ForeignKey(main_table.c.id, ondelete='CASCADE'),
index=True),
sa.Column('data', JSONB, nullable=False),
)
self.aux_tables[resource_type] = aux_table
# Define files table if needed.
if files:
files_table = sa.Table(
chop_long_name(resource_type + '__files'), self.metadata,
sa.Column('id',
sa.ForeignKey(main_table.c.id, ondelete='CASCADE'),
index=True),
sa.Column('subpath', sa.String(128), nullable=False),
sa.Column('blob', sa.LargeBinary()),
sa.UniqueConstraint(
'id',
'subpath',
name=self._get_file_unique_idx_name(resource_type)))
self.files_tables[resource_type] = files_table
def metadata() -> sa.MetaData:
result = sa.MetaData()
sa.Table(
'AccountRolesLog', result,
sa.Column('id',
sa.Integer,
index=True,
nullable=False,
primary_key=True),
sa.Column('created_at',
sa.DateTime,
server_default=sa_functions.now(),
index=True,
nullable=False),
sa.Column('created_by', sa.Unicode, index=True, nullable=False),
sa.Column('request_info', sa.UnicodeText, nullable=None),
sa.Column('account_id', sa.String, index=True, nullable=False),
sa.Column('action', sa.String(1), index=True, nullable=False),
sa.Column('role_ids', postgresql.ARRAY(sa.String(32)), nullable=False),
sa.Index('idx_arl_role_ids', 'role_ids', postgresql_using='gin'))
sa.Table(
'AccountRoles', result,
sa.Column('account_id',
sa.String,
index=True,
nullable=False,
primary_key=True),
sa.Column('role_ids', postgresql.ARRAY(sa.String(32)), nullable=False),
sa.Column('log_id',
sa.Integer,
sa.ForeignKey('AccountRolesLog.id'),
index=True,
nullable=False,
unique=True),
sa.Index('idx_ar_role_ids', 'role_ids', postgresql_using='gin'))
return result
sa.Column("url_thumb", sa.String(), nullable=False),
sa.Column(
"indexed_at",
sa.DateTime(),
nullable=False,
server_default=sa.text("(now() at time zone 'utc')"),
),
)
image_color = sa.Table(
"image_color",
METADATA,
sa.Column("id", sa.Integer(), primary_key=True),
sa.Column("image_id",
sa.Integer(),
sa.ForeignKey("image.id"),
nullable=False),
sa.Column("L", sa.Float(), nullable=False),
sa.Column("a", sa.Float(), nullable=False),
sa.Column("b", sa.Float(), nullable=False),
sa.Column("percentage", sa.Float(), nullable=False),
sa.Column("name", sa.String(), nullable=True),
sa.Column("name_distance", sa.Float(), nullable=True),
)
async def setup(app: Application):
engine = await aiopg.sa.create_engine(config.postgres.dsn, echo=True)
app["db"] = engine
yield
app["db"].close()
meta = sa.MetaData()
class DrawSourceResource(enum.Enum):
empty = 'empty'
low = 'low'
half = 'half'
full = 'full'
user_draw_source_relationship = sa.Table(
'users_draw_sources',
meta,
sa.Column('user_id',
UUID(as_uuid=True),
sa.ForeignKey('users.id'),
primary_key=True),
sa.Column('draw_source_id',
UUID(as_uuid=True),
sa.ForeignKey('draw_sources.id'),
primary_key=True),
sa.Column('resource',
sa.Enum(DrawSourceResource, name='draw_source_resource'),
nullable=True,
default=DrawSourceResource.full),
sa.Column('quantity', sa.Integer, nullable=False, default=1),
)
udsr = user_draw_source_relationship
user = sa.Table(
paste = sa.Table(
'pastes', meta,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('contents', sa.Text(), nullable=False),
sa.Column('created_on', sa.DateTime, default=datetime.datetime.utcnow),
sa.Column('sha', sa.String(64), nullable=False),
sa.Column('lexer', sa.String(60), nullable=True),
sa.Column('lexer_guessed', sa.Boolean, default=False),
)
comment = sa.Table(
'comments', meta,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('paste_id', sa.Integer,
sa.ForeignKey("pastes.id", ondelete="CASCADE"), nullable=False),
sa.Column('line', sa.Integer, nullable=False),
sa.Column('contents', sa.Text(), nullable=False),
sa.Column('created_on', sa.DateTime, default=datetime.datetime.utcnow),
)
class Paste:
def __init__(self, record, comments=None):
self._record = record
self.comments = collections.defaultdict(list)
if not comments:
return
for comment in comments:
self.comments[comment.line].append(comment.contents)
users = sa.Table(
'users', metadata, sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('name', sa.String(255), unique=True),
sa.Column('role', sa.Enum(Role), default=Role.SENIOR),
sa.Column('password', sa.String(255), nullable=False),
sa.Column('created_on', sa.DateTime(), default=datetime.now),
sa.Column('updated_on',
sa.DateTime(),
default=datetime.now,
onupdate=datetime.now),
sa.Column('deleted_on', sa.DateTime(), nullable=True))
projects = sa.Table(
'projects', metadata, sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('name', sa.String(255), unique=True),
sa.Column('user_id', sa.ForeignKey('users.id')),
sa.Column('created_on', sa.DateTime(), default=datetime.now),
sa.Column('updated_on',
sa.DateTime(),
default=datetime.now,
onupdate=datetime.now),
sa.Column('deleted_on', sa.DateTime(), nullable=True))
projects_staff = sa.Table(
'projects_staff', metadata,
sa.Column('project_id', sa.ForeignKey('projects.id')),
sa.Column('user_id', sa.ForeignKey('users.id')),
sa.Column('created_on', sa.DateTime(), default=datetime.now),
sa.Column('deleted_on', sa.DateTime(), nullable=True),
sa.Index('ix_projects_staff', 'project_id', 'user_id', unique=True))