def upgrade():
exec_sql_file('bd7c504431e9-upgrade.sql')
op.create_table('registry_item',
sa.Column('id', postgresql.UUID, nullable=False, server_default=sa.text('uuid_generate_v4()')),
sa.Column('registry_id', postgresql.UUID, nullable=False),
sa.Column('meta', postgresql.JSONB(), nullable=True),
sa.Column('data', postgresql.JSONB(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.ForeignKeyConstraint(name='fkey_item_registry', columns=['registry_id'], refcolumns=['datastore.registry.id']),
schema='datastore'
)
create_json_exists('meta', 'status', 'registry_item', 'datastore')
create_json_index('meta', 'status', 'registry_item', 'datastore')
create_json_exists('data', 'itemTitle', 'registry_item', 'datastore')
create_json_index('data', 'itemTitle', 'registry_item', 'datastore')
def upgrade():
op.create_table('registry_status',
sa.Column('id', postgresql.UUID, nullable=False, server_default=sa.text('uuid_generate_v4()')),
sa.Column('data', postgresql.JSONB(), nullable=False),
sa.PrimaryKeyConstraint('id'),
schema='datastore'
)
create_json_exists('data', 'name', 'registry_status', 'datastore')
create_json_exists('data', 'description', 'registry_status', 'datastore')
create_json_index(
'data', 'name', 'registry_status', 'datastore', unique=True)
op.execute(
'INSERT INTO datastore.registry_status (data) VALUES '
'(\'{"name": "new", "description": "this registry is not yet open to others"}\'), '
'(\'{"name": "open", "description": "this registry is open to others"}\'), '
'(\'{"name": "closed", "description": "this registry has been closed"}\')'
)
def upgrade():
op.execute('CREATE SCHEMA datastore')
op.create_table('data_element',
sa.Column('id', postgresql.UUID, nullable=False, server_default=sa.text('uuid_generate_v4()')),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('data', postgresql.JSONB(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name'),
schema='datastore'
)
op.create_table('object_schema',
sa.Column('id', postgresql.UUID, nullable=False, server_default=sa.text('uuid_generate_v4()')),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('display_name', sa.Text(), nullable=True),
sa.Column('data', postgresql.JSONB(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name'),
schema='datastore'
)
op.create_table('registry',
sa.Column('id', postgresql.UUID, nullable=False, server_default=sa.text('uuid_generate_v4()')),
sa.Column('user', postgresql.UUID, nullable=False),
sa.Column('meta', postgresql.JSONB(), nullable=True),
sa.Column('data', postgresql.JSONB(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.ForeignKeyConstraint(name='fkey_registry_user', columns=['user'], refcolumns=['auth._user.id']),
schema='datastore'
)
create_json_exists('meta', 'created', 'registry', 'datastore')
create_json_exists('meta', 'status', 'registry', 'datastore')
create_json_exists('data', 'itemTitle', 'registry', 'datastore')
create_json_index('meta', 'status', 'registry', 'datastore')
create_json_index('data', 'itemTitle', 'registry', 'datastore')
exec_sql_file('a2af8d8c9676-populate-schema-elements.sql')
def upgrade():
op.execute("CREATE SCHEMA auth")
op.create_table(
"_user",
sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
sa.Column("meta", postgresql.JSONB(), nullable=True),
sa.Column("data", postgresql.JSONB(), nullable=True),
sa.PrimaryKeyConstraint("id"),
schema="auth",
)
create_json_exists("meta", "created", "_user", "auth")
create_json_index("data", "displayName", "_user", "auth", unique=True)
create_json_index("data", "emailAddress", "_user", "auth", unique=True)
create_json_exists("data", "displayName", "_user", "auth")
create_json_minlen("data", "displayName", "_user", "auth", 4)
create_json_exists("data", "emailAddress", "_user", "auth")
create_json_exists("data", "firstName", "_user", "auth")
create_json_exists("data", "lastName", "_user", "auth")
create_json_exists("data", "city", "_user", "auth")
create_json_exists("data", "state", "_user", "auth")
create_json_minlen("data", "state", "_user", "auth", 2)
create_json_maxlen("data", "state", "_user", "auth", 2)
create_json_exists("meta", "hash", "_user", "auth")
create_json_minlen("meta", "hash", "_user", "auth", 8)
op.create_table(
"_roles",
sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
sa.Column("created", TIMESTAMP, server_default=func.now(), nullable=True),
sa.Column("name", sa.Text(), nullable=False),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("name"),
schema="auth",
)
op.create_table(
"_permissions",
sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
sa.Column("created", TIMESTAMP, server_default=func.now(), nullable=True),
sa.Column("name", sa.Text(), nullable=False),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("name"),
schema="auth",
)
op.create_table(
"_role_permissions",
sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
sa.Column("role", postgresql.UUID, nullable=False),
sa.Column("permission", postgresql.UUID, nullable=False),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(name="fkey_roleperms_role", columns=["role"], refcolumns=["auth._roles.id"]),
sa.ForeignKeyConstraint(
name="fkey_roleperms_perms", columns=["permission"], refcolumns=["auth._permissions.id"]
),
schema="auth",
)
op.create_table(
"_user_roles",
sa.Column("id", postgresql.UUID, nullable=False, server_default=sa.text("uuid_generate_v4()")),
sa.Column("user_id", postgresql.UUID, nullable=False),
sa.Column("role", postgresql.UUID, nullable=False),
sa.PrimaryKeyConstraint("id"),
sa.ForeignKeyConstraint(name="fkey_userroles_role", columns=["role"], refcolumns=["auth._roles.id"]),
sa.ForeignKeyConstraint(name="fkey_userroles_user", columns=["user_id"], refcolumns=["auth._user.id"]),
schema="auth",
)
exec_sql_file("e4b39da215a9-populate-auth-tables.sql")
