def upgrade():
op.create_table('rnode',
sa.Column('id', guid.GUID(), nullable=False),
sa.Column('survey_id', guid.GUID(), nullable=False),
sa.Column('assessment_id', guid.GUID(), nullable=False),
sa.Column('qnode_id', guid.GUID(), nullable=False),
sa.Column('n_submitted', sa.Integer(), nullable=False),
sa.Column('n_reviewed', sa.Integer(), nullable=False),
sa.Column('n_approved', sa.Integer(), nullable=False),
sa.Column('score', sa.Float(), nullable=False),
sa.ForeignKeyConstraint(
['assessment_id'],
['assessment.id']),
sa.ForeignKeyConstraint(
['qnode_id', 'survey_id'],
['qnode.id', 'qnode.survey_id']),
sa.ForeignKeyConstraint(
['survey_id'],
['survey.id']),
sa.PrimaryKeyConstraint('id')
)
op.add_column('assessment', sa.Column(
'title', sa.Text(), nullable=True))
op.add_column('response', sa.Column(
'attachments', postgresql.JSON(), nullable=False))
op.add_column('response_history', sa.Column(
'attachments', postgresql.JSON(), nullable=False))
op.alter_column('assessment', 'approval', type_=sa.Enum(
'draft', 'final', 'reviewed', 'approved', native_enum=False))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('attachment',
sa.Column('submeasure_id', guid.GUID(), nullable=True))
op.add_column('measure', sa.Column('measure_id',
guid.GUID(),
nullable=True))
op.add_column('measure',
sa.Column('submeasure_seq', sa.Integer(), nullable=True))
op.add_column('qnode', sa.Column('group', sa.Text(), nullable=True))
def upgrade():
op.add_column('function', sa.Column('survey_id', guid.GUID(), nullable=False))
op.create_foreign_key(None, 'function', 'survey', ['survey_id'], ['id'])
op.drop_column('function', 'branch')
op.add_column('function_history', sa.Column('survey_id', guid.GUID(), nullable=False))
op.drop_column('function_history', 'branch')
op.add_column('measure', sa.Column('survey_id', guid.GUID(), nullable=False))
op.create_foreign_key(None, 'measure', 'survey', ['survey_id'], ['id'])
op.add_column('measure_history', sa.Column('survey_id', guid.GUID(), nullable=False))
op.drop_column('measureset', 'branch')
op.add_column('measureset_measure_link', sa.Column('survey_id', guid.GUID(), nullable=False))
op.create_foreign_key(None, 'measureset_measure_link', 'survey', ['survey_id'], ['id'])
op.drop_column('measureset_measure_link', 'version')
op.drop_column('measureset_measure_link', 'branch')
op.add_column('process', sa.Column('survey_id', guid.GUID(), nullable=False))
op.create_foreign_key(None, 'process', 'survey', ['survey_id'], ['id'])
op.drop_column('process', 'branch')
op.add_column('process_history', sa.Column('survey_id', guid.GUID(), nullable=False))
op.drop_column('process_history', 'branch')
op.add_column('subprocess', sa.Column('survey_id', guid.GUID(), nullable=False))
op.create_foreign_key(None, 'subprocess', 'survey', ['survey_id'], ['id'])
op.drop_column('subprocess', 'branch')
op.add_column('subprocess_history', sa.Column('survey_id', guid.GUID(), nullable=False))
op.drop_column('subprocess_history', 'branch')
op.drop_column('survey', 'branch')
op.drop_column('survey_history', 'branch')
op.alter_column('measureset_measure_link', 'measureset_id', nullable=False)
op.alter_column('measureset_measure_link', 'measure_id', nullable=False)
op.alter_column('response', 'user_id', nullable=False)
op.alter_column('response', 'measure_id', nullable=False)
op.alter_column('response', 'assessment_id', nullable=False)
op.alter_column('assessment', 'organisation_id', nullable=False)
op.alter_column('assessment', 'survey_id', nullable=False)
op.alter_column('assessment', 'measureset_id', nullable=False)
def upgrade():
op.create_table('custom_query_history',
sa.Column('id', guid.GUID(), nullable=False),
sa.Column('modified', sa.DateTime(), nullable=False),
sa.Column('user_id', guid.GUID(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('text', sa.Text(), nullable=False),
sa.Column('description', sa.Text()),
sa.Column('deleted', sa.Boolean(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.Column('changed', sa.DateTime()),
sa.ForeignKeyConstraint(['user_id'], ['appuser.id'], ),
sa.PrimaryKeyConstraint('id', 'version')
)
op.create_table('custom_query',
sa.Column('id', guid.GUID(), nullable=False),
sa.Column('modified', sa.DateTime(), nullable=False),
sa.Column('user_id', guid.GUID(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('text', sa.Text(), nullable=False),
sa.Column('description', sa.Text()),
sa.Column('deleted', sa.Boolean(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['user_id'], ['appuser.id'], ),
sa.PrimaryKeyConstraint('id')
)
ob_types = array([
'organisation', 'user',
'program', 'survey', 'qnode', 'measure', 'response_type',
'submission', 'rnode', 'response',
'custom_query'], type_=TEXT)
op.drop_constraint('activity_ob_type_check', 'activity', type_='check')
op.create_check_constraint(
'activity_ob_type_check', 'activity',
cast(column('ob_type'), TEXT) == func.any(ob_types))
op.drop_constraint('subscription_ob_type_check', 'subscription', type_='check')
op.create_check_constraint(
'subscription_ob_type_check', 'subscription',
cast(column('ob_type'), TEXT) == func.any(ob_types))
op.drop_constraint('activity_verbs_check', 'activity', type_='check')
op.create_check_constraint(
'activity_verbs_check', 'activity',
"""verbs <@ ARRAY[
'broadcast',
'create', 'update', 'state', 'delete', 'undelete',
'relation', 'reorder_children',
'report'
]::varchar[]""")
def upgrade():
op.create_table(
'purchased_survey', sa.Column('survey_id', guid.GUID(),
nullable=False),
sa.Column('hierarchy_id', guid.GUID(), nullable=False),
sa.Column('organisation_id', guid.GUID(), nullable=False),
sa.Column('open_date', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(
['hierarchy_id', 'survey_id'],
['hierarchy.id', 'hierarchy.survey_id'],
), sa.ForeignKeyConstraint(
['organisation_id'],
['organisation.id'],
),
sa.PrimaryKeyConstraint('survey_id', 'hierarchy_id',
'organisation_id'))
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('attachment', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('organisation_id', sa.Text(), nullable=False),
sa.Column('response_id', sa.Text(), nullable=True),
sa.Column('url', sa.Text(), nullable=True),
sa.Column('blob', sa.Binary(), nullable=True),
sa.PrimaryKeyConstraint('id'))
def upgrade():
op.create_table(
'appuser_history', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('email', sa.Text(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('password', sa.Text(), nullable=False),
sa.Column('role', sa.Text(), nullable=False),
sa.Column('organisation_id', guid.GUID(), nullable=True),
sa.Column('created', sa.Date(), nullable=False),
sa.Column('version', sa.Integer(), autoincrement=False,
nullable=False),
sa.Column('changed', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id', 'version'))
op.create_table(
'assessment_history', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('organisation_id', guid.GUID(), nullable=True),
sa.Column('survey_id', guid.GUID(), nullable=True),
sa.Column('measureset_id', guid.GUID(), nullable=True),
sa.Column('approval', sa.Text(), nullable=False),
sa.Column('created', sa.Date(), nullable=False),
sa.Column('version', sa.Integer(), autoincrement=False,
nullable=False),
sa.Column('changed', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id', 'version'))
op.create_table('function', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('seq', sa.Integer(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'function_history', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('seq', sa.Integer(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), autoincrement=False,
nullable=False),
sa.Column('changed', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id', 'version'))
op.create_table(
'measure_history', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('subprocess_id', guid.GUID(), nullable=True),
sa.Column('seq', sa.Integer(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('weight', sa.Float(), nullable=False),
sa.Column('intent', sa.Text(), nullable=False),
sa.Column('inputs', sa.Text(), nullable=False),
sa.Column('scenario', sa.Text(), nullable=False),
sa.Column('questions', sa.Text(), nullable=False),
sa.Column('response_type', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), autoincrement=False,
nullable=False),
sa.Column('changed', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id', 'version'))
op.create_table(
'organisation', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('url', sa.Text(), nullable=True),
sa.Column('region', sa.Text(), nullable=False),
sa.Column('number_of_customers', sa.Integer(), nullable=False),
sa.Column('created', sa.Date(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
op.create_table(
'organisation_history', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('url', sa.Text(), nullable=True),
sa.Column('region', sa.Text(), nullable=False),
sa.Column('number_of_customers', sa.Integer(), nullable=False),
sa.Column('created', sa.Date(), nullable=False),
sa.Column('version', sa.Integer(), autoincrement=False,
nullable=False),
sa.Column('changed', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id', 'version'))
op.create_table(
'process_history', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('function_id', guid.GUID(), nullable=True),
sa.Column('seq', sa.Integer(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), autoincrement=False,
nullable=False),
sa.Column('changed', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id', 'version'))
op.create_table(
'response_history', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('user_id', guid.GUID(), nullable=True),
sa.Column('assessment_id', guid.GUID(), nullable=True),
sa.Column('measure_id', guid.GUID(), nullable=True),
sa.Column('comment', sa.Text(), nullable=False),
sa.Column('not_relevant', sa.Boolean(), nullable=False),
sa.Column('response_parts', sa.Text(), nullable=False),
sa.Column('audit_reason', sa.Text(), nullable=True),
sa.Column('version', sa.Integer(), autoincrement=False,
nullable=False),
sa.Column('changed', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id', 'version'))
op.create_table(
'subprocess_history', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('process_id', guid.GUID(), nullable=True),
sa.Column('seq', sa.Integer(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), autoincrement=False,
nullable=False),
sa.Column('changed', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id', 'version'))
op.create_table('survey', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('created', sa.Date(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'survey_history', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('created', sa.Date(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), autoincrement=False,
nullable=False),
sa.Column('changed', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id', 'version'))
op.create_table('systemconfig',
sa.Column('name', sa.String(), nullable=False),
sa.Column('value', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('name'))
op.create_table(
'appuser', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('email', sa.Text(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('password', sa.Text(), nullable=False),
sa.Column('role', sa.Text(), nullable=False),
sa.Column('organisation_id', guid.GUID(), nullable=True),
sa.Column('created', sa.Date(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['organisation_id'],
['organisation.id'],
), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('email'))
op.create_table('measureset', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('survey_id', guid.GUID(), nullable=True),
sa.Column('title', sa.Text(), nullable=False),
sa.ForeignKeyConstraint(
['survey_id'],
['survey.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'process', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('function_id', guid.GUID(), nullable=True),
sa.Column('seq', sa.Integer(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['function_id'],
['function.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'assessment', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('organisation_id', guid.GUID(), nullable=True),
sa.Column('survey_id', guid.GUID(), nullable=True),
sa.Column('measureset_id', guid.GUID(), nullable=True),
sa.Column('approval', sa.Text(), nullable=False),
sa.Column('created', sa.Date(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['measureset_id'],
['measureset.id'],
), sa.ForeignKeyConstraint(
['organisation_id'],
['organisation.id'],
), sa.ForeignKeyConstraint(
['survey_id'],
['survey.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('subprocess', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('process_id', guid.GUID(), nullable=True),
sa.Column('seq', sa.Integer(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('description', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['process_id'],
['process.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'measure', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('subprocess_id', guid.GUID(), nullable=True),
sa.Column('seq', sa.Integer(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('weight', sa.Float(), nullable=False),
sa.Column('intent', sa.Text(), nullable=False),
sa.Column('inputs', sa.Text(), nullable=False),
sa.Column('scenario', sa.Text(), nullable=False),
sa.Column('questions', sa.Text(), nullable=False),
sa.Column('response_type', sa.Text(), nullable=False),
sa.Column('version', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['subprocess_id'],
['subprocess.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'measureset_measure_link', sa.Column('id',
sa.Integer(),
nullable=False),
sa.Column('measureset_id', guid.GUID(), nullable=True),
sa.Column('measure_id', guid.GUID(), nullable=True),
sa.Column('version', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['measure_id'],
['measure.id'],
), sa.ForeignKeyConstraint(
['measureset_id'],
['measureset.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'response', sa.Column('id', guid.GUID(), nullable=False),
sa.Column('user_id', guid.GUID(), nullable=True),
sa.Column('assessment_id', guid.GUID(), nullable=True),
sa.Column('measure_id', guid.GUID(), nullable=True),
sa.Column('comment', sa.Text(), nullable=False),
sa.Column('not_relevant', sa.Boolean(), nullable=False),
sa.Column('response_parts', sa.Text(), nullable=False),
sa.Column('audit_reason', sa.Text(), nullable=True),
sa.Column('version', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['assessment_id'],
['assessment.id'],
), sa.ForeignKeyConstraint(
['measure_id'],
['measure.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['appuser.id'],
), sa.PrimaryKeyConstraint('id'))
def upgrade():
op.create_table('hierarchy',
sa.Column('id', guid.GUID(), nullable=False),
sa.Column('survey_id', guid.GUID(), nullable=False),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('description', sa.Text()),
sa.Column('structure', postgresql.JSON(), nullable=False),
sa.ForeignKeyConstraint(
['survey_id'],
['survey.id'],
name='hierarchy_survey_id_fkey'),
sa.PrimaryKeyConstraint('id', 'survey_id')
)
op.create_table('qnode',
sa.Column('id', guid.GUID(), nullable=False),
sa.Column('survey_id', guid.GUID(), nullable=False),
sa.Column('hierarchy_id', guid.GUID()),
sa.Column('parent_id', guid.GUID()),
sa.Column('seq', sa.Integer()),
sa.Column('title', sa.Text(), nullable=False),
sa.Column('description', sa.Text()),
sa.ForeignKeyConstraint(
['hierarchy_id', 'survey_id'],
['hierarchy.id', 'hierarchy.survey_id'],
name='qnode_hierarchy_id_fkey'),
sa.ForeignKeyConstraint(
['parent_id', 'survey_id'],
['qnode.id', 'qnode.survey_id'],
name='qnode_parent_id_fkey'),
sa.ForeignKeyConstraint(
['survey_id'],
['survey.id'],
name='qnode_survey_id_fkey'),
sa.CheckConstraint(
'(parent_id IS NULL AND hierarchy_id IS NOT NULL) OR '
'(parent_id IS NOT NULL AND hierarchy_id IS NULL)',
name='qnode_root_check'),
sa.PrimaryKeyConstraint('id', 'survey_id')
)
op.create_table('qnode_measure_link',
sa.Column('survey_id', guid.GUID(), nullable=False),
sa.Column('qnode_id', guid.GUID(), nullable=False),
sa.Column('measure_id', guid.GUID(), nullable=False),
sa.Column('seq', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['measure_id', 'survey_id'],
['measure.id', 'measure.survey_id'],
name='qnode_measure_link_measure_id_fkey'),
sa.ForeignKeyConstraint(
['qnode_id', 'survey_id'],
['qnode.id', 'qnode.survey_id'],
name='qnode_measure_link_qnode_id_fkey'),
sa.ForeignKeyConstraint(
['survey_id'],
['survey.id'],
name='qnode_measure_link_survey_id_fkey'),
sa.PrimaryKeyConstraint('survey_id', 'qnode_id', 'measure_id')
)
op.add_column(
'assessment', sa.Column('hierarchy_id', guid.GUID(), nullable=False))
op.drop_constraint(
'assessment_measureset_id_fkey', 'assessment', type_='foreignkey')
op.create_foreign_key(
'assessment_hierarchy_id_fkey', 'assessment', 'hierarchy',
['hierarchy_id', 'survey_id'],
['id', 'survey_id'])
op.drop_column('assessment', 'measureset_id')
op.drop_constraint(
'measure_subprocess_id_fkey', 'measure', type_='foreignkey')
op.create_foreign_key(
'measure_survey_id_fkey', 'measure', 'survey',
['survey_id'],
['id'])
op.drop_column('measure', 'seq')
op.drop_column('measure', 'subprocess_id')
op.add_column(
'survey', sa.Column('description', sa.Text()))
op.drop_table('measureset_measure_link')
op.drop_table('measureset')
op.drop_table('subprocess')
op.drop_table('process')
op.drop_table('function')