def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('info_category',
sa.Column('create_time', sa.DateTime(), nullable=True),
sa.Column('update_time', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('info_user',
sa.Column('create_time', sa.DateTime(), nullable=True),
sa.Column('update_time', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('nick_name', sa.String(length=32), nullable=False),
sa.Column('password_hash', sa.String(length=128), nullable=False),
sa.Column('mobile', sa.String(length=11), nullable=False),
sa.Column('avatar_url', sa.String(length=256), nullable=True),
sa.Column('last_login', sa.DateTime(), nullable=True),
sa.Column('is_admin', sa.Boolean(), nullable=True),
sa.Column('signature', sa.String(length=512), nullable=True),
sa.Column('gender', sa.Enum('MAN', 'WOMAN'), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('mobile'),
sa.UniqueConstraint('nick_name')
)
op.create_table('info_news',
sa.Column('create_time', sa.DateTime(), nullable=True),
sa.Column('update_time', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(length=256), nullable=False),
sa.Column('source', sa.String(length=64), nullable=False),
sa.Column('digest', sa.String(length=512), nullable=False),
sa.Column('content', sa.Text(), nullable=False),
sa.Column('clicks', sa.Integer(), nullable=True),
sa.Column('comments_count', sa.Integer(), nullable=True),
sa.Column('index_image_url', sa.String(length=256), nullable=True),
sa.Column('category_id', sa.Integer(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('status', sa.Integer(), nullable=True),
sa.Column('reason', sa.String(length=256), nullable=True),
sa.ForeignKeyConstraint(['category_id'], ['info_category.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['info_user.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('info_user_fans',
sa.Column('follower_id', sa.Integer(), nullable=False),
sa.Column('followed_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['followed_id'], ['info_user.id'], ),
sa.ForeignKeyConstraint(['follower_id'], ['info_user.id'], ),
sa.PrimaryKeyConstraint('follower_id', 'followed_id')
)
op.create_table('info_comment',
sa.Column('create_time', sa.DateTime(), nullable=True),
sa.Column('update_time', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('news_id', sa.Integer(), nullable=False),
sa.Column('content', sa.Text(), nullable=False),
sa.Column('parent_id', sa.Integer(), nullable=True),
sa.Column('like_count', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['news_id'], ['info_news.id'], ),
sa.ForeignKeyConstraint(['parent_id'], ['info_comment.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['info_user.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_table('info_user_collection',
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('news_id', sa.Integer(), nullable=False),
sa.Column('create_time', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['news_id'], ['info_news.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['info_user.id'], ),
sa.PrimaryKeyConstraint('user_id', 'news_id')
)
op.create_table('info_comment_like',
sa.Column('create_time', sa.DateTime(), nullable=True),
sa.Column('update_time', sa.DateTime(), nullable=True),
sa.Column('comment_id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['comment_id'], ['info_comment.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['info_user.id'], ),
sa.PrimaryKeyConstraint('comment_id', 'user_id')
)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('roles', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=64), nullable=True),
sa.Column('default', sa.Boolean(), nullable=True),
sa.Column('permissions', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
op.create_index(op.f('ix_roles_default'),
'roles', ['default'],
unique=False)
op.create_table(
'user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('role_id', sa.Integer(), nullable=True),
sa.Column('username', sa.String(length=20), nullable=False),
sa.Column('email', sa.String(length=120), nullable=False),
sa.Column('image_file', sa.String(length=20), nullable=False),
sa.Column('password', sa.String(length=60), nullable=False),
sa.Column('about_me', sa.String(length=140), nullable=True),
sa.Column('gender', sa.String(length=5), nullable=True),
sa.Column('contact_number', sa.Integer(), nullable=True),
sa.Column('education_level', sa.String(length=60), nullable=True),
sa.Column('date_of_birth', sa.DateTime(), nullable=True),
sa.Column('last_seen', sa.DateTime(), nullable=True),
sa.Column('member_since', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['role_id'],
['roles.id'],
), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('email'))
op.create_index(op.f('ix_user_username'),
'user', ['username'],
unique=True)
op.create_table('achievements',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('medal_count', sa.Integer(), nullable=True),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_achievements_timestamp'),
'achievements', ['timestamp'],
unique=False)
op.create_table('address', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('street', sa.Text(), nullable=True),
sa.Column('city', sa.Text(), nullable=True),
sa.Column('country', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('follows',
sa.Column('follower_id', sa.Integer(), nullable=False),
sa.Column('followed_id', sa.Integer(), nullable=False),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['followed_id'],
['user.id'],
), sa.ForeignKeyConstraint(
['follower_id'],
['user.id'],
), sa.PrimaryKeyConstraint('follower_id', 'followed_id'))
op.create_table('intrest', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('intrest_type', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('language', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('language', sa.String(), nullable=True),
sa.Column('language_accuracy', sa.String(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('links', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('facebook_id', sa.Text(), nullable=True),
sa.Column('twitter_id', sa.Text(), nullable=True),
sa.Column('instagram_id', sa.Text(), nullable=True),
sa.Column('snapchat_id', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('media', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('media_name', sa.String(), nullable=True),
sa.Column('media_format', sa.String(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'occupation', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('occupation_name', sa.Text(), nullable=True),
sa.Column('occupation_description', sa.Text(), nullable=True),
sa.Column('occupation_start_date', sa.DateTime(), nullable=True),
sa.Column('occupation_end_date', sa.DateTime(), nullable=True),
sa.Column('ocumpation_compay', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_occupation_occupation_end_date'),
'occupation', ['occupation_end_date'],
unique=False)
op.create_index(op.f('ix_occupation_occupation_start_date'),
'occupation', ['occupation_start_date'],
unique=False)
op.create_table(
'post', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('city', sa.String(length=50), nullable=False),
sa.Column('category', sa.String(length=50), nullable=False),
sa.Column('story_line', sa.String(length=500), nullable=False),
sa.Column('story_text', sa.Text(), nullable=False),
sa.Column('youtube_link', sa.String(length=500), nullable=True),
sa.Column('date_posted', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'special_event', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('life_event', sa.String(), nullable=True),
sa.Column('life_event_start_date', sa.DateTime(), nullable=True),
sa.Column('life_event_end_date', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_special_event_life_event_end_date'),
'special_event', ['life_event_end_date'],
unique=False)
op.create_index(op.f('ix_special_event_life_event_start_date'),
'special_event', ['life_event_start_date'],
unique=False)
op.create_table(
'sponsership', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('sponser_type', sa.String(), nullable=True),
sa.Column('sponser_name', sa.String(), nullable=True),
sa.Column('sponsership_start_date', sa.DateTime(), nullable=True),
sa.Column('sponsership_end_date', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_sponsership_sponsership_end_date'),
'sponsership', ['sponsership_end_date'],
unique=False)
op.create_index(op.f('ix_sponsership_sponsership_start_date'),
'sponsership', ['sponsership_start_date'],
unique=False)
op.create_table('travel', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('place', sa.String(), nullable=True),
sa.Column('start_date', sa.DateTime(), nullable=True),
sa.Column('end_date', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_travel_end_date'),
'travel', ['end_date'],
unique=False)
op.create_index(op.f('ix_travel_start_date'),
'travel', ['start_date'],
unique=False)
op.create_table('comments', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('body', sa.Text(), nullable=True),
sa.Column('body_html', sa.Text(), nullable=True),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('disabled', sa.Boolean(), nullable=True),
sa.Column('author_id', sa.Integer(), nullable=True),
sa.Column('post_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['author_id'],
['user.id'],
), sa.ForeignKeyConstraint(
['post_id'],
['post.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_comments_timestamp'),
'comments', ['timestamp'],
unique=False)
op.create_table(
'events', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('event_name', sa.String(), nullable=True),
sa.Column('event_description', sa.String(), nullable=True),
sa.Column('event_location', sa.String(), nullable=True),
sa.Column('event_start_date', sa.DateTime(), nullable=True),
sa.Column('event_end_date', sa.DateTime(), nullable=True),
sa.Column('event_status', sa.Boolean(), nullable=True),
sa.Column('media_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['media_id'],
['media.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_events_event_end_date'),
'events', ['event_end_date'],
unique=False)
op.create_index(op.f('ix_events_event_start_date'),
'events', ['event_start_date'],
unique=False)
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(
'alternative_version',
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('text', sa.String(), autoincrement=False, nullable=True),
sa.Column('correct', sa.Boolean(), autoincrement=False, nullable=True),
sa.Column('question_id',
sa.Integer(),
autoincrement=False,
nullable=True),
sa.Column('transaction_id',
sa.BigInteger(),
autoincrement=False,
nullable=False),
sa.Column('end_transaction_id', sa.BigInteger(), nullable=True),
sa.Column('operation_type', sa.SmallInteger(), nullable=False),
sa.PrimaryKeyConstraint('id', 'transaction_id'))
op.create_index(op.f('ix_alternative_version_end_transaction_id'),
'alternative_version', ['end_transaction_id'],
unique=False)
op.create_index(op.f('ix_alternative_version_operation_type'),
'alternative_version', ['operation_type'],
unique=False)
op.create_index(op.f('ix_alternative_version_transaction_id'),
'alternative_version', ['transaction_id'],
unique=False)
op.create_table('course', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('code', sa.String(length=80), nullable=False),
sa.Column('name', sa.String(length=120), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('code'))
op.create_table(
'course_version',
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('code',
sa.String(length=80),
autoincrement=False,
nullable=True),
sa.Column('name',
sa.String(length=120),
autoincrement=False,
nullable=True),
sa.Column('transaction_id',
sa.BigInteger(),
autoincrement=False,
nullable=False),
sa.Column('end_transaction_id', sa.BigInteger(), nullable=True),
sa.Column('operation_type', sa.SmallInteger(), nullable=False),
sa.PrimaryKeyConstraint('id', 'transaction_id'))
op.create_index(op.f('ix_course_version_end_transaction_id'),
'course_version', ['end_transaction_id'],
unique=False)
op.create_index(op.f('ix_course_version_operation_type'),
'course_version', ['operation_type'],
unique=False)
op.create_index(op.f('ix_course_version_transaction_id'),
'course_version', ['transaction_id'],
unique=False)
op.create_table(
'exam_version',
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('name', sa.String(), autoincrement=False, nullable=True),
sa.Column('course_id',
sa.Integer(),
autoincrement=False,
nullable=True),
sa.Column('transaction_id',
sa.BigInteger(),
autoincrement=False,
nullable=False),
sa.Column('end_transaction_id', sa.BigInteger(), nullable=True),
sa.Column('operation_type', sa.SmallInteger(), nullable=False),
sa.PrimaryKeyConstraint('id', 'transaction_id'))
op.create_index(op.f('ix_exam_version_end_transaction_id'),
'exam_version', ['end_transaction_id'],
unique=False)
op.create_index(op.f('ix_exam_version_operation_type'),
'exam_version', ['operation_type'],
unique=False)
op.create_index(op.f('ix_exam_version_transaction_id'),
'exam_version', ['transaction_id'],
unique=False)
op.create_table(
'question_version',
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('text', sa.String(), autoincrement=False, nullable=True),
sa.Column('image', sa.String(), autoincrement=False, nullable=True),
sa.Column('exam_id', sa.Integer(), autoincrement=False, nullable=True),
sa.Column('reason', sa.String(), autoincrement=False, nullable=True),
sa.Column('type',
sqlalchemy_utils.types.choice.ChoiceType(
models.Question.TYPES),
autoincrement=False,
nullable=True),
sa.Column('correct', sa.Boolean(), autoincrement=False, nullable=True),
sa.Column('transaction_id',
sa.BigInteger(),
autoincrement=False,
nullable=False),
sa.Column('end_transaction_id', sa.BigInteger(), nullable=True),
sa.Column('operation_type', sa.SmallInteger(), nullable=False),
sa.PrimaryKeyConstraint('id', 'transaction_id'))
op.create_index(op.f('ix_question_version_end_transaction_id'),
'question_version', ['end_transaction_id'],
unique=False)
op.create_index(op.f('ix_question_version_operation_type'),
'question_version', ['operation_type'],
unique=False)
op.create_index(op.f('ix_question_version_transaction_id'),
'question_version', ['transaction_id'],
unique=False)
op.create_table(
'user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('username', sa.String(), nullable=True),
sa.Column('password',
sqlalchemy_utils.types.password.PasswordType(
schemes=['pbkdf2_sha512']),
nullable=True),
sa.Column('registered', sa.Boolean(), nullable=True),
sa.Column('admin', sa.Boolean(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('username'))
op.create_table(
'user_version',
sa.Column('id', sa.Integer(), autoincrement=False, nullable=False),
sa.Column('name', sa.String(), autoincrement=False, nullable=True),
sa.Column('username', sa.String(), autoincrement=False, nullable=True),
sa.Column('password',
sqlalchemy_utils.types.password.PasswordType(
schemes=['pbkdf2_sha512']),
autoincrement=False,
nullable=True),
sa.Column('registered',
sa.Boolean(),
autoincrement=False,
nullable=True),
sa.Column('admin', sa.Boolean(), autoincrement=False, nullable=True),
sa.Column('transaction_id',
sa.BigInteger(),
autoincrement=False,
nullable=False),
sa.Column('end_transaction_id', sa.BigInteger(), nullable=True),
sa.Column('operation_type', sa.SmallInteger(), nullable=False),
sa.PrimaryKeyConstraint('id', 'transaction_id'))
op.create_index(op.f('ix_user_version_end_transaction_id'),
'user_version', ['end_transaction_id'],
unique=False)
op.create_index(op.f('ix_user_version_operation_type'),
'user_version', ['operation_type'],
unique=False)
op.create_index(op.f('ix_user_version_transaction_id'),
'user_version', ['transaction_id'],
unique=False)
op.create_table('exam', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('course_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['course_id'],
['course.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'transaction', sa.Column('issued_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('remote_addr', sa.String(length=50), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_transaction_user_id'),
'transaction', ['user_id'],
unique=False)
op.create_table(
'question', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('text', sa.String(), nullable=True),
sa.Column('image', sa.String(), nullable=True),
sa.Column('exam_id', sa.Integer(), nullable=True),
sa.Column('reason', sa.String(), nullable=True),
sa.Column('type',
sqlalchemy_utils.types.choice.ChoiceType(
models.Question.TYPES),
nullable=True),
sa.Column('correct', sa.Boolean(), nullable=True),
sa.ForeignKeyConstraint(
['exam_id'],
['exam.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'alternative', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('text', sa.String(), nullable=True),
sa.Column('correct', sa.Boolean(), nullable=True),
sa.Column('question_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['question_id'],
['question.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'stats', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('question_id', sa.Integer(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('correct', sa.Boolean(), nullable=True),
sa.Column('reset', sa.Boolean(), nullable=True),
sa.ForeignKeyConstraint(
['question_id'],
['question.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'classes', sa.Column('class_id', sa.Integer(), nullable=False),
sa.Column('class_name', sa.String(length=128), nullable=True),
sa.Column('class_token', sa.String(length=128), nullable=True),
sa.Column('class_teacher', sa.Integer(), nullable=True),
sa.Column('password_hash', sa.String(length=128), nullable=True),
sa.Column('last_seen', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('class_id'),
sa.UniqueConstraint('class_token'))
op.create_index(op.f('ix_classes_class_name'),
'classes', ['class_name'],
unique=False)
op.create_index(op.f('ix_classes_class_teacher'),
'classes', ['class_teacher'],
unique=False)
op.create_table(
'students', sa.Column('student_id', sa.Integer(), nullable=False),
sa.Column('rfid_id', sa.String(length=64), nullable=True),
sa.Column('student_number', sa.String(length=64), nullable=True),
sa.Column('student_name', sa.String(length=64), nullable=True),
sa.Column('student_nickname', sa.String(length=64), nullable=False),
sa.Column('curr_active', sa.Boolean(), nullable=False),
sa.PrimaryKeyConstraint('student_id'),
sa.UniqueConstraint('student_id'))
op.create_index(op.f('ix_students_rfid_id'),
'students', ['rfid_id'],
unique=True)
op.create_index(op.f('ix_students_student_name'),
'students', ['student_name'],
unique=True)
op.create_index(op.f('ix_students_student_number'),
'students', ['student_number'],
unique=True)
op.create_table(
'timetable', sa.Column('time_id', sa.Integer(), nullable=False),
sa.Column('time_class_name', sa.String(length=128), nullable=True),
sa.Column('time_class_id', sa.Integer(), nullable=True),
sa.Column('time_student_id', sa.Integer(), nullable=True),
sa.Column('time_time', sa.DateTime(), nullable=True),
sa.Column('active', sa.Boolean(), nullable=False),
sa.PrimaryKeyConstraint('time_id'), sa.UniqueConstraint('time_id'))
op.create_index(op.f('ix_timetable_time_class_id'),
'timetable', ['time_class_id'],
unique=False)
op.create_index(op.f('ix_timetable_time_class_name'),
'timetable', ['time_class_name'],
unique=False)
op.create_index(op.f('ix_timetable_time_student_id'),
'timetable', ['time_student_id'],
unique=False)
op.create_table(
'student_identifier', sa.Column('class_id',
sa.Integer(),
nullable=True),
sa.Column('student_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['class_id'],
['classes.class_id'],
), sa.ForeignKeyConstraint(
['student_id'],
['students.student_id'],
))
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_index(u'ix_user_email', table_name='user')
op.create_table(
u'committee_meeting_report',
sa.Column(u'id',
sa.INTEGER(),
server_default=sa.text(
u"nextval('committee_meeting_report_id_seq'::regclass)"),
nullable=False),
sa.Column(u'body', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column(u'summary', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column(u'event_id',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.PrimaryKeyConstraint(u'id', name=u'committee_meeting_report_pkey'))
op.create_table(
u'featured_committee_meeting_report_join',
sa.Column(u'featured_id',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.Column(u'committee_meeting_report_id',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.ForeignKeyConstraint(
[u'committee_meeting_report_id'], [u'committee_meeting_report.id'],
name=
u'featured_committee_meeting_rep_committee_meeting_report_id_fkey')
)
op.create_table(
u'briefing',
sa.Column(
u'id',
sa.INTEGER(),
server_default=sa.text(u"nextval('briefing_id_seq'::regclass)"),
nullable=False),
sa.Column(u'title',
sa.VARCHAR(length=255),
autoincrement=False,
nullable=False),
sa.Column(u'briefing_date',
sa.DATE(),
autoincrement=False,
nullable=True),
sa.Column(u'summary', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column(u'minutes', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column(u'presentation',
sa.TEXT(),
autoincrement=False,
nullable=True),
sa.Column(u'start_date', sa.DATE(), autoincrement=False,
nullable=True),
sa.PrimaryKeyConstraint(u'id', name=u'briefing_pkey'))
op.create_table(
u'book_file_join',
sa.Column(u'book_id', sa.INTEGER(), autoincrement=False,
nullable=True),
sa.Column(u'file_id', sa.INTEGER(), autoincrement=False,
nullable=True),
sa.ForeignKeyConstraint([u'book_id'], [u'book.id'],
name=u'book_file_join_book_id_fkey'))
op.create_table(
u'tabled_committee_report_committee_join',
sa.Column(u'tabled_committee_report_id',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.Column(u'committee_id',
sa.INTEGER(),
autoincrement=False,
nullable=True))
op.create_table(
u'committee_info', sa.Column(u'id', sa.INTEGER(), nullable=False),
sa.Column(u'about',
sa.VARCHAR(length=1500),
autoincrement=False,
nullable=True),
sa.Column(u'contact_details',
sa.VARCHAR(length=1500),
autoincrement=False,
nullable=True),
sa.Column(u'organization_id',
sa.INTEGER(),
autoincrement=False,
nullable=False),
sa.PrimaryKeyConstraint(u'id', name=u'committee_info_pkey'))
op.create_table(
u'featured_committee_meeting_join',
sa.Column(u'featured_id',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.Column(u'committee_meeting_id',
sa.INTEGER(),
autoincrement=False,
nullable=True))
op.create_table(
u'briefing_file_join',
sa.Column(u'briefing_id',
sa.INTEGER(),
autoincrement=False,
nullable=True),
sa.Column(u'file_id', sa.INTEGER(), autoincrement=False,
nullable=True),
sa.ForeignKeyConstraint([u'briefing_id'], [u'briefing.id'],
name=u'briefing_file_join_briefing_id_fkey'))
op.create_table(
u'book', sa.Column(u'id', sa.INTEGER(), nullable=False),
sa.Column(u'title',
sa.VARCHAR(length=255),
autoincrement=False,
nullable=True),
sa.Column(u'summary', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column(u'body', sa.TEXT(), autoincrement=False, nullable=True),
sa.Column(u'start_date', sa.DATE(), autoincrement=False,
nullable=True),
sa.Column(u'nid', sa.INTEGER(), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint(u'id', name=u'book_pkey'))
op.create_table(
u'hansard', sa.Column(u'id', sa.INTEGER(), nullable=False),
sa.Column(u'title',
sa.VARCHAR(length=255),
autoincrement=False,
nullable=True),
sa.Column(u'meeting_date',
sa.DATE(),
autoincrement=False,
nullable=True),
sa.Column(u'start_date', sa.DATE(), autoincrement=False,
nullable=True),
sa.Column(u'body', sa.TEXT(), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint(u'id', name=u'hansard_pkey'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('sponsorracevbl',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('variable', sa.String(length=32), nullable=True),
sa.Column('value', sa.String(length=128), nullable=True),
sa.Column('race_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['race_id'],
['sponsorrace.id'],
), sa.PrimaryKeyConstraint('id'))
op.alter_column('course',
'isStandard',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('event',
'isOnCalendar',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('service',
'isCalendarBlocked',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('sponsor',
'isLogoReceived',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('sponsor',
'isRegSiteUpdated',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('sponsor',
'isSponsorThankedFB',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('sponsor',
'isWebsiteUpdated',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.add_column('sponsorbenefit',
sa.Column('race_id', sa.Integer(), nullable=True))
op.create_foreign_key(None, 'sponsorbenefit', 'sponsorrace', ['race_id'],
['id'])
op.alter_column('sponsorlevel',
'display',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('sponsorrace',
'isRDCertified',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.add_column(
'sponsorracedate',
sa.Column('beneficiary', sa.String(length=128), nullable=True))
op.add_column('sponsorracedate',
sa.Column('raceloc', sa.String(length=128), nullable=True))
op.alter_column('tag',
'isBuiltIn',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
op.alter_column('user',
'active',
existing_type=mysql.TINYINT(display_width=1),
type_=sa.Boolean(),
existing_nullable=True)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('board', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=32), nullable=False),
sa.Column('rules', sa.String(), nullable=True),
sa.Column('max_threads', sa.Integer(), nullable=True),
sa.Column('mimetypes', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'media', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('ext', sa.String(length=4), nullable=False),
sa.Column('mimetype', sa.String(length=255), nullable=False),
sa.Column('is_animated', sa.Boolean(), nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_table('slip', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=20), nullable=False),
sa.Column('pass_hash', sa.String(), nullable=False),
sa.Column('is_admin', sa.Boolean(), nullable=False),
sa.Column('is_mod', sa.Boolean(), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
op.create_table('tag', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('bg_style', sa.String(), nullable=True),
sa.Column('text_style', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table('session',
sa.Column('id', sa.String(length=32), nullable=False),
sa.Column('slip_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['slip_id'],
['slip.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('thread', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('board', sa.Integer(), nullable=False),
sa.Column('views', sa.Integer(), nullable=False),
sa.Column('last_updated', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['board'],
['board.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'poster', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('hex_string', sa.String(length=4), nullable=False),
sa.Column('ip_address', sa.String(length=15), nullable=False),
sa.Column('thread', sa.Integer(), nullable=False),
sa.Column('slip', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['slip'],
['slip.id'],
),
sa.ForeignKeyConstraint(['thread'], ['thread.id'], ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table('tags', sa.Column('tag_id', sa.Integer(), nullable=False),
sa.Column('thread_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['tag_id'],
['tag.id'],
), sa.ForeignKeyConstraint(
['thread_id'],
['thread.id'],
), sa.PrimaryKeyConstraint('tag_id', 'thread_id'))
op.create_table(
'post', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('body', sa.String(length=4096), nullable=False),
sa.Column('subject', sa.String(length=64), nullable=True),
sa.Column('thread', sa.Integer(), nullable=False),
sa.Column('datetime', sa.DateTime(), nullable=False),
sa.Column('poster', sa.Integer(), nullable=False),
sa.Column('media', sa.Integer(), nullable=True),
sa.Column('spoiler', sa.Boolean(), nullable=True),
sa.ForeignKeyConstraint(['media'], ['media.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(
['poster'],
['poster.id'],
), sa.ForeignKeyConstraint(
['thread'],
['thread.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('reply',
sa.Column('reply_from', sa.Integer(), nullable=False),
sa.Column('reply_to', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['reply_from'],
['post.id'],
), sa.ForeignKeyConstraint(
['reply_to'],
['post.id'],
), sa.PrimaryKeyConstraint('reply_from', 'reply_to'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
distribution_code_table = op.create_table(
'distribution_code',
sa.Column('created_on', sa.DateTime(), nullable=False),
sa.Column('updated_on', sa.DateTime(), nullable=True),
sa.Column('distribution_code_id',
sa.Integer(),
autoincrement=True,
nullable=False),
sa.Column('memo_name', sa.String(length=50), nullable=True),
sa.Column('service_fee_memo_name', sa.String(length=50),
nullable=True),
sa.Column('client', sa.String(length=50), nullable=True),
sa.Column('responsibility_centre', sa.String(length=50),
nullable=True),
sa.Column('service_line', sa.String(length=50), nullable=True),
sa.Column('stob', sa.String(length=50), nullable=True),
sa.Column('project_code', sa.String(length=50), nullable=True),
sa.Column('service_fee_client', sa.String(length=50), nullable=True),
sa.Column('service_fee_responsibility_centre',
sa.String(length=50),
nullable=True),
sa.Column('service_fee_line', sa.String(length=50), nullable=True),
sa.Column('service_fee_stob', sa.String(length=50), nullable=True),
sa.Column('service_fee_project_code',
sa.String(length=50),
nullable=True),
sa.Column('start_date', sa.Date(), nullable=False),
sa.Column('end_date', sa.Date(), nullable=True),
sa.Column('created_by', sa.String(length=50), nullable=False),
sa.Column('created_name', sa.String(length=100), nullable=True),
sa.Column('updated_by', sa.String(length=50), nullable=True),
sa.Column('updated_name', sa.String(length=50), nullable=True),
sa.PrimaryKeyConstraint('distribution_code_id'))
distribution_code_link_table = op.create_table(
'distribution_code_link',
sa.Column('distribution_link_id', sa.Integer(), nullable=False),
sa.Column('fee_schedule_id', sa.Integer(), nullable=True),
sa.Column('distribution_code_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['distribution_code_id'],
['distribution_code.distribution_code_id'],
),
sa.ForeignKeyConstraint(
['fee_schedule_id'],
['fee_schedule.fee_schedule_id'],
), sa.PrimaryKeyConstraint('distribution_link_id'))
op.add_column(
'fee_schedule',
sa.Column('service_fee_code', sa.String(length=10), nullable=True))
op.create_foreign_key('service_fee_code_fk', 'fee_schedule', 'fee_code',
['service_fee_code'], ['code'])
op.add_column(
'payment_line_item',
sa.Column('fee_distribution_id', sa.Integer(), nullable=True))
op.add_column('payment_line_item',
sa.Column('service_fees', sa.Float(), nullable=True))
op.create_foreign_key('fee_distribution_id_fk', 'payment_line_item',
'distribution_code', ['fee_distribution_id'],
['distribution_code_id'])
# Insert records from corp_type to distribution_code and create a link with fee_schedule
op.bulk_insert(distribution_code_table, [{
'distribution_code_id': 1,
'created_by': 'Alembic',
'created_on': date.today(),
'memo_name': 'CO-OP Filing',
'service_fee_memo_name': None,
'start_date': date.today(),
}, {
'distribution_code_id': 2,
'created_by': 'Alembic',
'created_on': date.today(),
'memo_name': 'Benefit Companies',
'service_fee_memo_name': 'SBC Modernization Service Charge',
'start_date': date.today(),
}, {
'distribution_code_id': 3,
'created_by': 'Alembic',
'created_on': date.today(),
'memo_name': 'Benefit Companies',
'service_fee_memo_name': 'SBC Modernization Service Charge',
'start_date': date.today(),
}, {
'distribution_code_id': 4,
'created_by': 'Alembic',
'created_on': date.today(),
'memo_name': 'TODO-VS',
'service_fee_memo_name': 'SBC Modernization Service Charge',
'start_date': date.today(),
}, {
'distribution_code_id': 5,
'created_by': 'Alembic',
'created_on': date.today(),
'memo_name': 'TODO-PPR',
'service_fee_memo_name': 'SBC Modernization Service Charge',
'start_date': date.today(),
}])
conn = op.get_bind()
res = conn.execute(
'select fee_schedule_id, corp_type_code from fee_schedule;')
results = res.fetchall()
for result in results:
distribution_code_id = None
corp_type_code = result[1]
if corp_type_code == 'CP':
distribution_code_id = 1
elif corp_type_code == 'BC':
distribution_code_id = 2
elif corp_type_code == 'NRO':
distribution_code_id = 3
elif corp_type_code == 'VS':
distribution_code_id = 4
elif corp_type_code == 'PPR':
distribution_code_id = 5
op.bulk_insert(distribution_code_link_table,
[{
'distribution_code_id': distribution_code_id,
'fee_schedule_id': result[0]
}])
# Update all payment line item which used this fee schedule with distribution_code_id
op.execute(
'update payment_line_item set fee_distribution_id={} where fee_schedule_id={};'
.format(distribution_code_id, result[0]))
# Update service fee codes for fee schedule
op.execute(
"update fee_schedule set service_fee_code='TRF01' where corp_type_code in ('BC', 'VS', 'PPR', 'NRO')"
)
# Above script creates 5 default entries, so reset the sequence to 6.
op.execute(
"ALTER SEQUENCE distribution_code_distribution_code_id_seq RESTART WITH 6"
)
## Insert complete
op.drop_constraint('corp_type_transaction_fee_code_fkey',
'corp_type',
type_='foreignkey')
op.drop_column('corp_type', 'gl_memo')
op.drop_column('corp_type', 'service_fee_code')
op.drop_column('corp_type', 'service_gl_memo')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('customizations',
sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('code', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('amount', sa.DECIMAL(), nullable=False),
sa.Column('setup_time', sa.Time(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('code')
)
op.create_index(op.f('ix_customizations_id'), 'customizations', ['id'], unique=True)
op.create_table('flavors',
sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('code', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('setup_time', sa.Time(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('code')
)
op.create_index(op.f('ix_flavors_id'), 'flavors', ['id'], unique=True)
op.create_table('sizes',
sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('code', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('amount', sa.DECIMAL(), nullable=False),
sa.Column('setup_time', sa.Time(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('code')
)
op.create_index(op.f('ix_sizes_id'), 'sizes', ['id'], unique=True)
op.create_table('orders',
sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('stage', sa.Enum('choice', 'customization', 'done', name='orderstage'), nullable=False),
sa.Column('size_id', postgresql.UUID(as_uuid=True), nullable=False),
sa.Column('flavor_id', postgresql.UUID(as_uuid=True), nullable=False),
sa.Column('setup_time', sa.Time(), nullable=False),
sa.Column('amount', sa.DECIMAL(), nullable=False),
sa.ForeignKeyConstraint(['flavor_id'], ['flavors.id'], ),
sa.ForeignKeyConstraint(['size_id'], ['sizes.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_orders_id'), 'orders', ['id'], unique=True)
op.create_table('order_customization',
sa.Column('id', postgresql.UUID(as_uuid=True), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('order_id', postgresql.UUID(), nullable=False),
sa.Column('customization_id', postgresql.UUID(), nullable=False),
sa.ForeignKeyConstraint(['customization_id'], ['customizations.id'], ),
sa.ForeignKeyConstraint(['order_id'], ['orders.id'], ),
sa.PrimaryKeyConstraint('id', 'order_id', 'customization_id')
)
op.create_index(op.f('ix_order_customization_id'), 'order_customization', ['id'], unique=True)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_time', sa.DateTime(), nullable=True),
sa.Column('updated_time', sa.DateTime(), nullable=True),
sa.Column('role', sa.SmallInteger(), nullable=True),
sa.Column('name', sa.String(length=32), nullable=False),
sa.Column('email', sa.String(length=128), nullable=False),
sa.Column('password', sa.String(length=128), nullable=False),
sa.Column('phone', sa.String(length=11), nullable=True),
sa.Column('is_disable', sa.Boolean(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_user_email'), 'user', ['email'], unique=True)
op.create_index(op.f('ix_user_name'), 'user', ['name'], unique=True)
op.create_table(
'company', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_time', sa.DateTime(), nullable=True),
sa.Column('updated_time', sa.DateTime(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('name', sa.String(length=128), nullable=True),
sa.Column('website', sa.String(length=256), nullable=True),
sa.Column('logo_uri', sa.String(length=256), nullable=True),
sa.Column('introduction', sa.String(length=256), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('domain', sa.String(length=128), nullable=True),
sa.Column('finance', sa.String(length=128), nullable=True),
sa.Column('city', sa.String(length=128), nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'job', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_time', sa.DateTime(), nullable=True),
sa.Column('updated_time', sa.DateTime(), nullable=True),
sa.Column('company_id', sa.Integer(), nullable=True),
sa.Column('title', sa.String(length=128), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.Column('address', sa.String(length=256), nullable=True),
sa.Column('salary_max', sa.Integer(), nullable=True),
sa.Column('salary_min', sa.Integer(), nullable=True),
sa.Column('location', sa.String(length=128), nullable=True),
sa.Column('experience', sa.String(length=128), nullable=True),
sa.Column('education', sa.String(length=128), nullable=True),
sa.Column('tags', sa.String(length=128), nullable=True),
sa.Column('is_disable', sa.Boolean(), nullable=True),
sa.ForeignKeyConstraint(['company_id'], ['user.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'seeker', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_time', sa.DateTime(), nullable=True),
sa.Column('updated_time', sa.DateTime(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('name', sa.String(length=128), nullable=True),
sa.Column('work_year', sa.Integer(), nullable=True),
sa.Column('resume_uri', sa.String(length=256), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'delivery', sa.Column('created_time', sa.DateTime(), nullable=True),
sa.Column('updated_time', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('job_id', sa.Integer(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('company_id', sa.Integer(), nullable=True),
sa.Column('status', sa.SmallInteger(), nullable=True),
sa.Column('response', sa.String(length=256), nullable=True),
sa.ForeignKeyConstraint(['job_id'], ['job.id'], ondelete='SET NULL'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'user_job', sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('job_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['job_id'], ['job.id'], ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ondelete='CASCADE'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'customer', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('last_update', sa.DateTime(), nullable=True),
sa.Column('username', sa.String(length=32), nullable=True),
sa.Column('password_hash', sa.String(length=128), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_customer_created_at'),
'customer', ['created_at'],
unique=False)
op.create_index(op.f('ix_customer_last_update'),
'customer', ['last_update'],
unique=False)
op.create_index(op.f('ix_customer_username'),
'customer', ['username'],
unique=False)
op.create_table(
'data_source', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('last_update', sa.DateTime(), nullable=True),
sa.Column('customer_id', sa.Integer(), nullable=True),
sa.Column('upload_code', sa.String(), nullable=True),
sa.Column('type',
sa.Enum('FILESYSTEM', 'BLOBSTORE', name='uploadtypes'),
nullable=True),
sa.Column('location', sa.String(), nullable=True),
sa.Column('filename', sa.String(), nullable=False),
sa.Column('start_date', sa.DateTime(), nullable=True),
sa.Column('end_date', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['customer_id'],
['customer.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_data_source_created_at'),
'data_source', ['created_at'],
unique=False)
op.create_index(op.f('ix_data_source_end_date'),
'data_source', ['end_date'],
unique=False)
op.create_index(op.f('ix_data_source_last_update'),
'data_source', ['last_update'],
unique=False)
op.create_index(op.f('ix_data_source_location'),
'data_source', ['location'],
unique=False)
op.create_index(op.f('ix_data_source_start_date'),
'data_source', ['start_date'],
unique=False)
op.create_index(op.f('ix_data_source_type'),
'data_source', ['type'],
unique=False)
op.create_index(op.f('ix_data_source_upload_code'),
'data_source', ['upload_code'],
unique=False)
op.create_table(
'prediction_task', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('last_update', sa.DateTime(), nullable=True),
sa.Column('customer_id', sa.Integer(), nullable=True),
sa.Column('task_code', sa.String(length=60), nullable=False),
sa.Column('datasource_id', sa.Integer(), nullable=False),
sa.Column('prediction_request', sa.JSON(), nullable=True),
sa.ForeignKeyConstraint(
['customer_id'],
['customer.id'],
), sa.ForeignKeyConstraint(
['datasource_id'],
['data_source.id'],
), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('task_code'))
op.create_index(op.f('ix_prediction_task_created_at'),
'prediction_task', ['created_at'],
unique=False)
op.create_index(op.f('ix_prediction_task_last_update'),
'prediction_task', ['last_update'],
unique=False)
op.create_table(
'prediction_result', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('last_update', sa.DateTime(), nullable=True),
sa.Column('customer_id', sa.Integer(), nullable=True),
sa.Column('task_code', sa.String(length=60), nullable=True),
sa.Column('result', sa.JSON(), nullable=True),
sa.Column('prediction_task_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['customer_id'],
['customer.id'],
),
sa.ForeignKeyConstraint(
['prediction_task_id'],
['prediction_task.id'],
), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('task_code'))
op.create_index(op.f('ix_prediction_result_created_at'),
'prediction_result', ['created_at'],
unique=False)
op.create_index(op.f('ix_prediction_result_last_update'),
'prediction_result', ['last_update'],
unique=False)
op.create_table(
'task_status', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('last_update', sa.DateTime(), nullable=True),
sa.Column('prediction_task_id', sa.Integer(), nullable=True),
sa.Column('state', sa.String(length=10), nullable=True),
sa.Column('message', sa.JSON(), nullable=True),
sa.ForeignKeyConstraint(
['prediction_task_id'],
['prediction_task.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_task_status_created_at'),
'task_status', ['created_at'],
unique=False)
op.create_index(op.f('ix_task_status_last_update'),
'task_status', ['last_update'],
unique=False)
op.create_index(op.f('ix_task_status_state'),
'task_status', ['state'],
unique=False)
def upgrade():
users = op.create_table(
"users",
sa.Column("id", sa.Text(), nullable=False),
sa.Column("created_at", sa.Integer(), nullable=False),
sa.Column("profile",
postgresql.JSONB(astext_type=sa.Text()),
nullable=False),
sa.PrimaryKeyConstraint("id"),
)
root_id = f"usr:{generate_token(42)}"
op.execute(users.insert().values(id=root_id,
created_at=int(time.time()),
profile=dict(name="root")).returning(
users.c.id))
clients = op.create_table(
"clients",
sa.Column("user_id", sa.Text(), nullable=False),
sa.Column("client_id", sa.Text(), nullable=False),
sa.Column("client_secret", sa.Text(), nullable=True),
sa.Column("_client_metadata",
postgresql.JSONB(astext_type=sa.Text()),
nullable=True),
sa.Column("is_active", sa.Boolean(), nullable=False),
sa.Column("client_id_issued_at", sa.Integer(), nullable=False),
sa.Column("client_secret_expires_at", sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
["user_id"],
["users.id"],
),
sa.PrimaryKeyConstraint("client_id"),
)
op.execute(clients.insert().values(
user_id=root_id,
client_id=f"cli:swagger-ui",
_client_metadata=dict(
redirect_uris=["http://localhost:8000/docs/oauth2-redirect"],
token_endpoint_auth_method="none",
grant_types=["authorization_code", "refresh_token"],
response_types=["code"],
client_name="Swagger UI",
scope="openid admin",
),
is_active=True,
client_id_issued_at=int(time.time()),
client_secret_expires_at=0,
))
op.create_table(
"identities",
sa.Column("id", sa.BigInteger(), nullable=False),
sa.Column("sub", sa.String(), nullable=False),
sa.Column("idp", sa.String(), nullable=False),
sa.Column("user_id", sa.Text(), nullable=False),
sa.Column("created_at", sa.Integer(), nullable=False),
sa.Column("profile",
postgresql.JSONB(astext_type=sa.Text()),
nullable=False),
sa.ForeignKeyConstraint(
["user_id"],
["users.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index("identities_idp_sub_idx",
"identities", ["sub", "idp"],
unique=True)
op.create_table(
"authorization_codes",
sa.Column("client_id", sa.Text(), nullable=False),
sa.Column("user_id", sa.Text(), nullable=False),
sa.Column("code", sa.Text(), nullable=False),
sa.Column("code_challenge_method", sa.Text(), nullable=True),
sa.Column("used", sa.Boolean(), nullable=True),
sa.Column("redirect_uri", sa.Text(), nullable=True),
sa.Column("response_type", sa.Text(), nullable=True),
sa.Column("scope", sa.Text(), nullable=True),
sa.Column("nonce", sa.Text(), nullable=True),
sa.Column("auth_time", sa.Integer(), nullable=False),
sa.Column("code_challenge", sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
["client_id"],
["clients.client_id"],
),
sa.ForeignKeyConstraint(
["user_id"],
["users.id"],
),
sa.PrimaryKeyConstraint("code"),
)
op.create_index(
"auth_code_nonce_index",
"authorization_codes",
["nonce", "auth_time"],
unique=False,
postgresql_where=sa.text("not used"),
)
op.create_table(
"sessions",
sa.Column("id", sa.Text(), nullable=False),
sa.Column("client_id", sa.Text(), nullable=False),
sa.Column("user_id", sa.Text(), nullable=False),
sa.Column("scope", sa.Text(), nullable=False),
sa.Column("created_at", sa.Integer(), nullable=False),
sa.Column("terminated_at", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["client_id"],
["clients.client_id"],
),
sa.ForeignKeyConstraint(
["user_id"],
["users.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_table(
"bearer_tokens",
sa.Column("session_id", sa.Text(), nullable=False),
sa.Column("client_id", sa.Text(), nullable=False),
sa.Column("user_id", sa.Text(), nullable=False),
sa.Column("refresh_token", sa.Text(), nullable=False),
sa.Column("access_token", sa.Text(), nullable=False),
sa.Column("token_type", sa.Text(), nullable=True),
sa.Column("revoked_at", sa.Integer(), nullable=True),
sa.Column("scope", sa.Text(), nullable=True),
sa.Column("issued_at", sa.Integer(), nullable=False),
sa.Column("expires_in", sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
["client_id"],
["clients.client_id"],
),
sa.ForeignKeyConstraint(
["session_id"],
["sessions.id"],
),
sa.ForeignKeyConstraint(
["user_id"],
["users.id"],
),
sa.PrimaryKeyConstraint("refresh_token"),
)
def upgrade():
op.create_table(
'networktemplates',
sa.Column('id', sa.String(length=36), primary_key=True),
sa.Column('body', sa.Text(), nullable=False),
sa.Column('name', sa.String(255), nullable=False, unique=True))
op.create_table(
'networktemplateassignments',
sa.Column('id', sa.String(length=36), primary_key=True),
sa.Column('tenant_id', sa.String(255), nullable=False, unique=True),
sa.Column('stack_id', sa.String(255), nullable=False),
sa.Column('template_id',
sa.String(length=36),
sa.ForeignKey('networktemplates.id'),
nullable=False),
)
op.create_table(
'reachabilitytest',
sa.Column('id', sa.String(length=36), primary_key=True),
sa.Column('tenant_id', sa.String(255), nullable=False),
sa.Column('name', sa.String(64), nullable=False, unique=True),
sa.Column('src_tenant_name', sa.String(255), nullable=False),
sa.Column('src_segment_name', sa.String(255), nullable=False),
sa.Column('src_ip', sa.String(16), nullable=False),
sa.Column('dst_ip', sa.String(16), nullable=False),
sa.Column('expected_result',
Enum("reached destination",
"dropped by route",
"dropped by policy",
"dropped due to private segment",
"packet in",
"forwarded",
"dropped",
"multiple sources",
"unsupported",
"invalid input",
name="expected_result"),
nullable=False),
sa.Column('test_time', TIMESTAMP(timezone=True), nullable=True),
sa.Column('test_result',
Enum("pass", "fail", "pending"),
nullable=False,
default="pending"),
sa.Column('detail', JSONEncodedDict(8192), nullable=True),
sa.Column('run_test', sa.Boolean, nullable=False, default=False))
op.create_table(
'reachabilityquicktest',
sa.Column('id', sa.String(length=36), primary_key=True),
sa.Column('tenant_id', sa.String(255), nullable=False),
sa.Column('name', sa.String(64), nullable=False, unique=True),
sa.Column('src_tenant_name', sa.String(255), nullable=False),
sa.Column('src_segment_name', sa.String(255), nullable=False),
sa.Column('src_ip', sa.String(16), nullable=False),
sa.Column('dst_ip', sa.String(16), nullable=False),
sa.Column('expected_result',
Enum("reached destination",
"dropped by route",
"dropped by policy",
"dropped due to private segment",
"packet in",
"forwarded",
"dropped",
"multiple sources",
"unsupported",
"invalid input",
name="expected_result"),
nullable=False),
sa.Column('test_time', TIMESTAMP(timezone=True), nullable=True),
sa.Column('test_result',
Enum("pass", "fail", "pending"),
nullable=False,
default="pending"),
sa.Column('detail', JSONEncodedDict(8192), nullable=True),
sa.Column('run_test', sa.Boolean, nullable=False, default=False),
sa.Column('save_test', sa.Boolean, nullable=False, default=False))
op.create_table(
'bsn_routerrules', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('priority', sa.Integer(), nullable=False),
sa.Column('source', sa.String(length=64), nullable=False),
sa.Column('destination', sa.String(length=64), nullable=False),
sa.Column('action', sa.String(length=10), nullable=False),
sa.Column('router_id', sa.String(length=36), nullable=True),
sa.ForeignKeyConstraint(['router_id'], ['routers.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('priority', 'router_id', name='unique_prio_rid'))
op.create_table(
'bsn_nexthops', sa.Column('rule_id', sa.Integer(), nullable=False),
sa.Column('nexthop', sa.String(length=64), nullable=False),
sa.ForeignKeyConstraint(['rule_id'], ['bsn_routerrules.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('rule_id', 'nexthop'))
comment='自我介绍'),
sa.Column('created_at',
LocalDateTime,
nullable=False,
server_default=sasql.text('CURRENT_TIMESTAMP'),
comment='创建时间'),
sa.Column(
'updated_at',
LocalDateTime,
nullable=True,
server_default=sasql.text('DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP'),
comment='更新时间'),
sa.Index('idx_username', 'username', unique=True),
sa.Index('idx_mobile', 'mobile', unique=True),
sa.ForeignKeyConstraint(['avatar_id'], ['file.id'],
ondelete='SET NULL',
onupdate='CASCADE'),
comment='用户',
)
class UserRepo(Repository):
def __init__(self, db: Engine):
super().__init__(db, user_table)
user_follow_table = sa.Table(
'user_follow',
metadata,
sa.Column('id', sa.Integer, nullable=False, primary_key=True,
comment='ID'),
def upgrade():
"""Upgrade database."""
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'oarepo_references_classname',
sa.Column('created',
sa.DateTime().with_variant(mysql.DATETIME(fsp=6), 'mysql'),
nullable=False),
sa.Column('updated',
sa.DateTime().with_variant(mysql.DATETIME(fsp=6), 'mysql'),
nullable=False), sa.Column('id',
sa.Integer(),
nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id',
name=op.f('pk_oarepo_references_classname')))
op.create_index(op.f('ix_oarepo_references_classname_name'),
'oarepo_references_classname', ['name'],
unique=True)
op.create_table(
'oarepo_references_version',
sa.Column('created',
sa.DateTime().with_variant(mysql.DATETIME(fsp=6), 'mysql'),
autoincrement=False,
nullable=True),
sa.Column('updated',
sa.DateTime().with_variant(mysql.DATETIME(fsp=6), 'mysql'),
autoincrement=False,
nullable=True),
sa.Column('id',
sqlalchemy_utils.types.uuid.UUIDType(),
autoincrement=False,
nullable=False),
sa.Column('record_id',
sa.Integer(),
autoincrement=False,
nullable=True),
sa.Column('reference',
sa.String(length=255),
autoincrement=False,
nullable=True),
sa.Column('reference_uuid',
sqlalchemy_utils.types.uuid.UUIDType(),
autoincrement=False,
nullable=True),
sa.Column('inline',
sa.Boolean(name='ck_oarepo_references_inline'),
autoincrement=False,
nullable=True),
sa.Column('version_id',
sa.Integer(),
autoincrement=False,
nullable=True),
sa.Column('transaction_id',
sa.BigInteger(),
autoincrement=False,
nullable=False),
sa.Column('end_transaction_id', sa.BigInteger(), nullable=True),
sa.Column('operation_type', sa.SmallInteger(), nullable=False),
sa.PrimaryKeyConstraint('id',
'transaction_id',
name=op.f('pk_oarepo_references_version')))
op.create_index(op.f('ix_oarepo_references_version_end_transaction_id'),
'oarepo_references_version', ['end_transaction_id'],
unique=False)
op.create_index(op.f('ix_oarepo_references_version_operation_type'),
'oarepo_references_version', ['operation_type'],
unique=False)
op.create_index(op.f('ix_oarepo_references_version_reference'),
'oarepo_references_version', ['reference'],
unique=False)
op.create_index(op.f('ix_oarepo_references_version_reference_uuid'),
'oarepo_references_version', ['reference_uuid'],
unique=False)
op.create_index(op.f('ix_oarepo_references_version_transaction_id'),
'oarepo_references_version', ['transaction_id'],
unique=False)
op.create_table(
'oarepo_references_referencing_record',
sa.Column('created',
sa.DateTime().with_variant(mysql.DATETIME(fsp=6), 'mysql'),
nullable=False),
sa.Column('updated',
sa.DateTime().with_variant(mysql.DATETIME(fsp=6), 'mysql'),
nullable=False), sa.Column('id',
sa.Integer(),
nullable=False),
sa.Column('record_uuid',
sqlalchemy_utils.types.uuid.UUIDType(),
nullable=True),
sa.Column('class_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['class_id'],
['oarepo_references_classname.id'],
name='fk_oarepo_references_class_id_classname',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint(
'id', name=op.f('pk_oarepo_references_referencing_record')))
op.create_index(
op.f('ix_oarepo_references_referencing_record_record_uuid'),
'oarepo_references_referencing_record', ['record_uuid'],
unique=True)
op.create_table(
'oarepo_references',
sa.Column('created',
sa.DateTime().with_variant(mysql.DATETIME(fsp=6), 'mysql'),
nullable=False),
sa.Column('updated',
sa.DateTime().with_variant(mysql.DATETIME(fsp=6), 'mysql'),
nullable=False),
sa.Column('id', sqlalchemy_utils.types.uuid.UUIDType(),
nullable=False),
sa.Column('record_id', sa.Integer(), nullable=True),
sa.Column('reference', sa.String(length=255), nullable=False),
sa.Column('reference_uuid',
sqlalchemy_utils.types.uuid.UUIDType(),
nullable=True),
sa.Column('inline',
sa.Boolean(name='ck_oarepo_references_inline'),
nullable=True),
sa.Column('version_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['record_id'],
['oarepo_references_referencing_record.id'],
name='fk_oarepo_references_record_id_record',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id', name=op.f('pk_oarepo_references')),
sa.UniqueConstraint('record_id',
'reference',
name='uq_oarepo_references_record_id_reference'))
op.create_index(op.f('ix_oarepo_references_reference'),
'oarepo_references', ['reference'],
unique=False)
op.create_index(op.f('ix_oarepo_references_reference_uuid'),
'oarepo_references', ['reference_uuid'],
unique=False)
def upgrade(op, tables, tester):
op.create_table(
"repomirrorrule",
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("uuid", sa.String(length=36), nullable=False),
sa.Column("repository_id", sa.Integer(), nullable=False),
sa.Column("creation_date", sa.DateTime(), nullable=False),
sa.Column("rule_type", sa.Integer(), nullable=False),
sa.Column("rule_value", sa.Text(), nullable=False),
sa.Column("left_child_id", sa.Integer(), nullable=True),
sa.Column("right_child_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["left_child_id"],
["repomirrorrule.id"],
name=op.f("fk_repomirrorrule_left_child_id_repomirrorrule"),
),
sa.ForeignKeyConstraint(
["repository_id"],
["repository.id"],
name=op.f("fk_repomirrorrule_repository_id_repository"),
),
sa.ForeignKeyConstraint(
["right_child_id"],
["repomirrorrule.id"],
name=op.f("fk_repomirrorrule_right_child_id_repomirrorrule"),
),
sa.PrimaryKeyConstraint("id", name=op.f("pk_repomirrorrule")),
)
op.create_index(
"repomirrorrule_left_child_id", "repomirrorrule", ["left_child_id"], unique=False
)
op.create_index(
"repomirrorrule_repository_id", "repomirrorrule", ["repository_id"], unique=False
)
op.create_index(
"repomirrorrule_right_child_id", "repomirrorrule", ["right_child_id"], unique=False
)
op.create_index("repomirrorrule_rule_type", "repomirrorrule", ["rule_type"], unique=False)
op.create_index("repomirrorrule_uuid", "repomirrorrule", ["uuid"], unique=True)
op.create_table(
"repomirrorconfig",
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("repository_id", sa.Integer(), nullable=False),
sa.Column("creation_date", sa.DateTime(), nullable=False),
sa.Column("is_enabled", sa.Boolean(), nullable=False),
sa.Column("mirror_type", sa.Integer(), nullable=False),
sa.Column("internal_robot_id", sa.Integer(), nullable=False),
sa.Column("external_registry", sa.String(length=255), nullable=False),
sa.Column("external_namespace", sa.String(length=255), nullable=False),
sa.Column("external_repository", sa.String(length=255), nullable=False),
sa.Column("external_registry_username", sa.String(length=2048), nullable=True),
sa.Column("external_registry_password", sa.String(length=2048), nullable=True),
sa.Column("external_registry_config", sa.Text(), nullable=False),
sa.Column("sync_interval", sa.Integer(), nullable=False, server_default="60"),
sa.Column("sync_start_date", sa.DateTime(), nullable=True),
sa.Column("sync_expiration_date", sa.DateTime(), nullable=True),
sa.Column("sync_retries_remaining", sa.Integer(), nullable=False, server_default="3"),
sa.Column("sync_status", sa.Integer(), nullable=False),
sa.Column("sync_transaction_id", sa.String(length=36), nullable=True),
sa.Column("root_rule_id", sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
["repository_id"],
["repository.id"],
name=op.f("fk_repomirrorconfig_repository_id_repository"),
),
sa.ForeignKeyConstraint(
["root_rule_id"],
["repomirrorrule.id"],
name=op.f("fk_repomirrorconfig_root_rule_id_repomirrorrule"),
),
sa.ForeignKeyConstraint(
["internal_robot_id"],
["user.id"],
name=op.f("fk_repomirrorconfig_internal_robot_id_user"),
),
sa.PrimaryKeyConstraint("id", name=op.f("pk_repomirrorconfig")),
)
op.create_index(
"repomirrorconfig_mirror_type", "repomirrorconfig", ["mirror_type"], unique=False
)
op.create_index(
"repomirrorconfig_repository_id", "repomirrorconfig", ["repository_id"], unique=True
)
op.create_index(
"repomirrorconfig_root_rule_id", "repomirrorconfig", ["root_rule_id"], unique=False
)
op.create_index(
"repomirrorconfig_sync_status", "repomirrorconfig", ["sync_status"], unique=False
)
op.create_index(
"repomirrorconfig_sync_transaction_id",
"repomirrorconfig",
["sync_transaction_id"],
unique=False,
)
op.create_index(
"repomirrorconfig_internal_robot_id",
"repomirrorconfig",
["internal_robot_id"],
unique=False,
)
op.add_column(
"repository", sa.Column("state", sa.Integer(), nullable=False, server_default="0")
)
op.create_index("repository_state", "repository", ["state"], unique=False)
op.bulk_insert(
tables.logentrykind,
[
{"name": "repo_mirror_enabled"},
{"name": "repo_mirror_disabled"},
{"name": "repo_mirror_config_changed"},
{"name": "repo_mirror_sync_started"},
{"name": "repo_mirror_sync_failed"},
{"name": "repo_mirror_sync_success"},
{"name": "repo_mirror_sync_now_requested"},
{"name": "repo_mirror_sync_tag_success"},
{"name": "repo_mirror_sync_tag_failed"},
{"name": "repo_mirror_sync_test_success"},
{"name": "repo_mirror_sync_test_failed"},
{"name": "repo_mirror_sync_test_started"},
{"name": "change_repo_state"},
],
)
tester.populate_table(
"repomirrorrule",
[
("uuid", tester.TestDataType.String),
("repository_id", tester.TestDataType.Foreign("repository")),
("creation_date", tester.TestDataType.DateTime),
("rule_type", tester.TestDataType.Integer),
("rule_value", tester.TestDataType.String),
],
)
tester.populate_table(
"repomirrorconfig",
[
("repository_id", tester.TestDataType.Foreign("repository")),
("creation_date", tester.TestDataType.DateTime),
("is_enabled", tester.TestDataType.Boolean),
("mirror_type", tester.TestDataType.Constant(1)),
("internal_robot_id", tester.TestDataType.Foreign("user")),
("external_registry", tester.TestDataType.String),
("external_namespace", tester.TestDataType.String),
("external_repository", tester.TestDataType.String),
("external_registry_username", tester.TestDataType.String),
("external_registry_password", tester.TestDataType.String),
("external_registry_config", tester.TestDataType.JSON),
("sync_start_date", tester.TestDataType.DateTime),
("sync_expiration_date", tester.TestDataType.DateTime),
("sync_retries_remaining", tester.TestDataType.Integer),
("sync_status", tester.TestDataType.Constant(0)),
("sync_transaction_id", tester.TestDataType.String),
("root_rule_id", tester.TestDataType.Foreign("repomirrorrule")),
],
)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('menu', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('date', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('date'))
op.create_table('revoked_tokens',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('token', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_revoked_tokens_token'),
'revoked_tokens', ['token'],
unique=False)
op.create_table('user', sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(), nullable=False),
sa.Column('username', sa.String(), nullable=False),
sa.Column('password_hash', sa.String(), nullable=False),
sa.Column('admin', sa.Boolean(), nullable=True),
sa.Column('super_user', sa.Boolean(), nullable=True),
sa.PrimaryKeyConstraint('user_id'),
sa.UniqueConstraint('email'),
sa.UniqueConstraint('username'))
op.create_table(
'meal', sa.Column('meal_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=40), nullable=False),
sa.Column('price', sa.Float(), nullable=False),
sa.Column('description', sa.String(length=250), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.user_id'],
), sa.PrimaryKeyConstraint('meal_id'))
op.create_table('order', sa.Column('order_id',
sa.Integer(),
nullable=False),
sa.Column('time_ordered', sa.Float(), nullable=True),
sa.Column('due_time', sa.DateTime(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.user_id'],
), sa.PrimaryKeyConstraint('order_id'))
op.create_table(
'meals_assoc', sa.Column('meal_id', sa.Integer(), nullable=False),
sa.Column('order_id', sa.Integer(), nullable=False),
sa.Column('quantity', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['meal_id'],
['meal.meal_id'],
), sa.ForeignKeyConstraint(
['order_id'],
['order.order_id'],
), sa.PrimaryKeyConstraint('meal_id', 'order_id'))
op.create_table('menu_meals',
sa.Column('menu_id', sa.Integer(), nullable=True),
sa.Column('meal_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['meal_id'],
['meal.meal_id'],
), sa.ForeignKeyConstraint(
['menu_id'],
['menu.id'],
))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'addresses', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('address1', sa.String(length=255), nullable=True),
sa.Column('address2', sa.String(length=255), nullable=True),
sa.Column('city', sa.String(length=100), nullable=True),
sa.Column('state', sa.String(length=100), nullable=True),
sa.Column('country', sa.String(length=100), nullable=True),
sa.Column('postal_code', sa.String(length=25), nullable=True),
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('updated_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_table(
'users', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('first_name', sa.String(length=45), nullable=False),
sa.Column('last_name', sa.String(length=45), nullable=False),
sa.Column('email', sa.String(length=100), nullable=False),
sa.Column('password', sa.String(length=255), nullable=False),
sa.Column('approval_id',
sa.Integer(),
server_default='1',
nullable=True),
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('updated_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_table(
'items', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('category', sa.String(length=255), nullable=False),
sa.Column('name', sa.String(length=45), nullable=False),
sa.Column('description', sa.String(length=255), nullable=False),
sa.Column('price', sa.Integer(), nullable=False),
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('updated_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('fkey_item_user_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['fkey_item_user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'orders', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('updated_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('fkey_order_user_id', sa.Integer(), nullable=False),
sa.Column('fkey_order_address_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['fkey_order_address_id'],
['addresses.id'],
), sa.ForeignKeyConstraint(
['fkey_order_user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'tours', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('venue_name', sa.String(length=45), nullable=False),
sa.Column('event_date', sa.DateTime(), nullable=True),
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('updated_at',
sa.DateTime(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('fkey_tour_user_id', sa.Integer(), nullable=False),
sa.Column('fkey_tour_address_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['fkey_tour_address_id'],
['addresses.id'],
), sa.ForeignKeyConstraint(
['fkey_tour_user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('orders_items',
sa.Column('order_id', sa.Integer(), nullable=False),
sa.Column('item_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['item_id'],
['items.id'],
), sa.ForeignKeyConstraint(
['order_id'],
['orders.id'],
), sa.PrimaryKeyConstraint('order_id', 'item_id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('student',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('firstname', sa.String(length=64), nullable=True),
sa.Column('lastname', sa.String(length=64), nullable=True),
sa.Column('email', sa.String(length=100), nullable=True),
sa.Column('address_1', sa.String(length=200), nullable=True),
sa.Column('address_2', sa.String(length=200), nullable=True),
sa.Column('city', sa.String(length=200), nullable=True),
sa.Column('state', sa.String(length=200), nullable=True),
sa.Column('zip_code', sa.String(length=200), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_student_email'), 'student', ['email'], unique=True)
op.create_table('users',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('firstname', sa.String(length=64), nullable=True),
sa.Column('lastname', sa.String(length=64), nullable=True),
sa.Column('email', sa.String(length=100), nullable=True),
sa.Column('address_1', sa.String(length=200), nullable=True),
sa.Column('address_2', sa.String(length=200), nullable=True),
sa.Column('city', sa.String(length=200), nullable=True),
sa.Column('state', sa.String(length=200), nullable=True),
sa.Column('zip_code', sa.String(length=200), nullable=True),
sa.Column('password_hash', sa.String(length=128), nullable=True),
sa.Column('date_created', sa.DateTime(), nullable=True),
sa.Column('last_seen', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=True)
op.create_table('studentprofile',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('student_id', sa.Integer(), nullable=True),
sa.Column('school', sa.String(length=64), nullable=True),
sa.Column('sex', sa.String(length=64), nullable=True),
sa.Column('age', sa.String(length=64), nullable=True),
sa.Column('address', sa.String(length=225), nullable=True),
sa.Column('famsize', sa.String(length=64), nullable=True),
sa.Column('pstatus', sa.String(length=64), nullable=True),
sa.Column('medu', sa.String(length=64), nullable=True),
sa.Column('fedu', sa.String(length=64), nullable=True),
sa.Column('mjob', sa.String(length=64), nullable=True),
sa.Column('fjob', sa.String(length=64), nullable=True),
sa.Column('reason', sa.String(length=64), nullable=True),
sa.Column('guardian', sa.String(length=64), nullable=True),
sa.Column('traveltime', sa.String(length=64), nullable=True),
sa.Column('studytime', sa.String(length=64), nullable=True),
sa.Column('failures', sa.String(length=64), nullable=True),
sa.Column('schoolsup', sa.String(length=64), nullable=True),
sa.Column('famsup', sa.String(length=64), nullable=True),
sa.Column('paid', sa.String(length=64), nullable=True),
sa.Column('activities', sa.String(length=64), nullable=True),
sa.Column('nursery', sa.String(length=64), nullable=True),
sa.Column('higher', sa.String(length=64), nullable=True),
sa.Column('internet', sa.String(length=64), nullable=True),
sa.Column('romantic', sa.String(length=64), nullable=True),
sa.Column('famrel', sa.String(length=64), nullable=True),
sa.Column('freetime', sa.String(length=64), nullable=True),
sa.Column('goout', sa.String(length=64), nullable=True),
sa.Column('dalc', sa.String(length=64), nullable=True),
sa.Column('walc', sa.String(length=64), nullable=True),
sa.Column('health', sa.String(length=64), nullable=True),
sa.Column('absences', sa.String(length=64), nullable=True),
sa.ForeignKeyConstraint(['student_id'], ['student.id'], ),
sa.PrimaryKeyConstraint('id')
)
def upgrade():
trade_lot_states = op.create_table(
'trade_lot_states',
sa.Column('identifier', sa.Unicode(), nullable=False),
sa.PrimaryKeyConstraint('identifier')
)
op.bulk_insert(trade_lot_states, [
{'identifier': 'draft'},
{'identifier': 'proposed'},
{'identifier': 'accepted'},
{'identifier': 'rejected'}
])
op.create_table(
'trade_lots',
sa.Column('id', sa.Integer, nullable=False, primary_key=True),
sa.Column('trade_id', sa.Integer, sa.ForeignKey('trades.id'),
nullable=False),
sa.Column('in_exchange_for_id', sa.Integer,
sa.ForeignKey('trade_lots.id'), nullable=True),
sa.Column('sender_id', sa.Integer, sa.ForeignKey('trainers.id'),
nullable=True),
sa.Column('recipient_id', sa.Integer, sa.ForeignKey('trainers.id'),
nullable=True),
sa.Column('state', sa.Unicode,
sa.ForeignKey('trade_lot_states.identifier'),
nullable=False),
sa.Column('money', sa.Integer, nullable=True),
sa.Column('notify_recipient', sa.Boolean, nullable=False),
sa.UniqueConstraint('id', 'trade_id', 'sender_id', 'recipient_id'),
sa.ForeignKeyConstraint(
['in_exchange_for_id', 'trade_id', 'recipient_id', 'sender_id'],
['trade_lots.id', 'trade_lots.trade_id', 'trade_lots.sender_id',
'trade_lots.recipient_id']
)
)
op.create_table(
'trade_lot_pokemon',
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('trade_lot_id', sa.Integer, sa.ForeignKey('trade_lots.id'),
nullable=False),
sa.Column('pokemon_id', sa.Integer, sa.ForeignKey('pokemon.id'),
nullable=True)
)
op.create_table(
'trade_lot_items',
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('trade_lot_id', sa.Integer, sa.ForeignKey('trade_lots.id'),
nullable=False),
sa.Column('trainer_item_id', sa.Integer,
sa.ForeignKey('trainer_items.id'), nullable=True),
sa.Column('item_id', sa.Integer, sa.ForeignKey('items.id'),
nullable=False)
)
op.drop_constraint('pokemon_trader_fkey', 'pokemon')
op.drop_constraint('pokemon_trade_id_fkey', 'pokemon')
op.drop_column('pokemon', 'trade_id')
op.drop_constraint('item_trader_fkey', 'trainer_items')
op.drop_constraint('trainer_items_trade_id_fkey', 'trainer_items')
op.drop_column('trainer_items', 'trade_id')
op.drop_table('trade_trainers')
op.add_column('trades', sa.Column('completed', sa.Boolean, nullable=False))
op.add_column('trades', sa.Column('reveal_date', sa.Date(), nullable=True))
op.drop_column('trades', 'approved')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'Artist', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('city', sa.String(length=120), nullable=True),
sa.Column('state', sa.String(length=120), nullable=True),
sa.Column('phone', sa.String(length=120), nullable=True),
sa.Column('image_link', sa.String(length=500), nullable=True),
sa.Column('facebook_link', sa.String(length=120), nullable=True),
sa.Column('website', sa.String(length=120), nullable=True),
sa.Column('seeking_venue', sa.Boolean(), nullable=True),
sa.Column('seeking_description', sa.String(length=120), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table('Genre', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'Venue', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('city', sa.String(length=120), nullable=True),
sa.Column('state', sa.String(length=120), nullable=True),
sa.Column('address', sa.String(length=120), nullable=True),
sa.Column('phone', sa.String(length=120), nullable=True),
sa.Column('image_link', sa.String(length=500), nullable=True),
sa.Column('facebook_link', sa.String(length=120), nullable=True),
sa.Column('website', sa.String(length=120), nullable=True),
sa.Column('seeking_talent', sa.Boolean(), nullable=True),
sa.Column('seeking_description', sa.String(length=120), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table('Show', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('start_time', sa.DateTime(), nullable=False),
sa.Column('artist_id', sa.Integer(), nullable=False),
sa.Column('venue_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['artist_id'],
['Artist.id'],
), sa.ForeignKeyConstraint(
['venue_id'],
['Venue.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('artist_genre_table',
sa.Column('genre_id', sa.Integer(), nullable=False),
sa.Column('artist_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['artist_id'],
['Artist.id'],
), sa.ForeignKeyConstraint(
['genre_id'],
['Genre.id'],
), sa.PrimaryKeyConstraint('genre_id', 'artist_id'))
op.create_table('venue_genre_table',
sa.Column('genre_id', sa.Integer(), nullable=False),
sa.Column('venue_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['genre_id'],
['Genre.id'],
), sa.ForeignKeyConstraint(
['venue_id'],
['Venue.id'],
), sa.PrimaryKeyConstraint('genre_id', 'venue_id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('adsense_type',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_table('image', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('path', sa.Unicode(length=128), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'role', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=80), nullable=True),
sa.Column('description', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
op.create_table(
'siteconfiguration', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=False),
sa.Column('tagline', sa.String(), nullable=False),
sa.Column('show_registration_menu', sa.Boolean(), nullable=True),
sa.Column('youtube_link', sa.String(), nullable=True),
sa.Column('ga_tracking_code', sa.String(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(length=255), nullable=True),
sa.Column('password', sa.String(length=255), nullable=True),
sa.Column('active', sa.Boolean(), nullable=True),
sa.Column('confirmed_at', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('email'))
op.create_table(
'adsense_code', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('code', sa.String(), nullable=True),
sa.Column('adstype_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['adstype_id'],
['adsense_type.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('page', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(), nullable=False),
sa.Column('excerpt', sa.String(), nullable=True),
sa.Column('content', sa.String(), nullable=True),
sa.Column('tag', sa.String(), nullable=True),
sa.Column('keyword', sa.String(), nullable=True),
sa.Column('is_homepage', sa.Boolean(), nullable=True),
sa.Column('subtype', sa.String(), nullable=True),
sa.Column('subtype_data', sa.String(), nullable=True),
sa.Column('stamp', sa.DateTime(), nullable=True),
sa.Column('category', sa.String(), nullable=True),
sa.Column('url', sa.String(), nullable=False),
sa.Column('image_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['image_id'],
['image.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('roles_users',
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('role_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['role_id'],
['role.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
))
op.create_table('menu', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(), nullable=False),
sa.Column('order', sa.Integer(), nullable=False),
sa.Column('page_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['page_id'],
['page.id'],
), sa.PrimaryKeyConstraint('id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
"group",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("name", sa.String(length=32), nullable=False),
sa.Column(
"capabilities",
postgresql.ARRAY(sa.Text()),
server_default="{}",
nullable=False,
),
sa.Column("private", sa.Boolean(), nullable=True),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_group_name"), "group", ["name"], unique=True)
op.create_table(
"metakey_definition",
sa.Column("key", sa.String(length=64), nullable=False),
sa.Column("label", sa.String(length=64), nullable=True),
sa.Column("description", sa.Text(), nullable=True),
sa.Column("url_template", sa.Text(), nullable=True),
sa.Column("hidden", sa.Boolean(), nullable=False),
sa.PrimaryKeyConstraint("key"),
)
op.create_table(
"object",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("type", sa.String(length=50), nullable=True),
sa.Column("dhash", sa.String(length=64), nullable=True),
sa.Column("upload_time", sa.DateTime(), nullable=False),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_object_dhash"), "object", ["dhash"], unique=True)
op.create_index(
op.f("ix_object_upload_time"), "object", ["upload_time"], unique=False
)
op.create_table(
"tag",
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("tag", sa.String(), nullable=False),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_tag_tag"), "tag", ["tag"], unique=True)
op.create_table(
"user",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("login", sa.String(length=32), nullable=False),
sa.Column("email", sa.String(length=128), nullable=True),
sa.Column("password_hash", sa.String(length=128), nullable=True),
sa.Column("version_uid", sa.String(length=16), nullable=True),
sa.Column("password_ver", sa.String(length=16), nullable=True),
sa.Column("identity_ver", sa.String(length=16), nullable=True),
sa.Column("additional_info", sa.String(), nullable=True),
sa.Column("disabled", sa.Boolean(), nullable=False),
sa.Column("pending", sa.Boolean(), nullable=False),
sa.Column("requested_on", sa.DateTime(), nullable=True),
sa.Column("registered_on", sa.DateTime(), nullable=True),
sa.Column("registered_by", sa.Integer(), nullable=True),
sa.Column("logged_on", sa.DateTime(), nullable=True),
sa.Column("set_password_on", sa.DateTime(), nullable=True),
sa.Column(
"feed_quality", sa.String(length=32), server_default="high", nullable=True
),
sa.ForeignKeyConstraint(
["registered_by"],
["user.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_user_login"), "user", ["login"], unique=True)
op.create_table(
"api_key",
sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
sa.Column("user_id", sa.Integer(), nullable=False),
sa.Column("issued_on", sa.DateTime(), nullable=True),
sa.Column("issued_by", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["issued_by"],
["user.id"],
),
sa.ForeignKeyConstraint(
["user_id"],
["user.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_table(
"comment",
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("comment", sa.String(), nullable=False),
sa.Column("timestamp", sa.DateTime(), nullable=False),
sa.Column("object_id", sa.Integer(), nullable=True),
sa.Column("user_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["object_id"],
["object.id"],
),
sa.ForeignKeyConstraint(
["user_id"],
["user.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_table(
"file",
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("file_name", sa.String(), nullable=True),
sa.Column("file_size", sa.Integer(), nullable=False),
sa.Column("file_type", sa.Text(), nullable=True),
sa.Column("md5", sa.String(length=32), nullable=False),
sa.Column("crc32", sa.String(length=8), nullable=False),
sa.Column("sha1", sa.String(length=40), nullable=False),
sa.Column("sha256", sa.String(length=64), nullable=False),
sa.Column("sha512", sa.String(length=128), nullable=False),
sa.Column("humanhash", sa.String(), nullable=False),
sa.Column("ssdeep", sa.String(length=255), nullable=True),
sa.ForeignKeyConstraint(
["id"],
["object.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(op.f("ix_file_crc32"), "file", ["crc32"], unique=False)
op.create_index(op.f("ix_file_file_name"), "file", ["file_name"], unique=False)
op.create_index(op.f("ix_file_file_size"), "file", ["file_size"], unique=False)
op.create_index(op.f("ix_file_file_type"), "file", ["file_type"], unique=False)
op.create_index(op.f("ix_file_humanhash"), "file", ["humanhash"], unique=False)
op.create_index(op.f("ix_file_md5"), "file", ["md5"], unique=False)
op.create_index(op.f("ix_file_sha1"), "file", ["sha1"], unique=False)
op.create_index(op.f("ix_file_sha256"), "file", ["sha256"], unique=True)
op.create_index(op.f("ix_file_sha512"), "file", ["sha512"], unique=False)
op.create_index(op.f("ix_file_ssdeep"), "file", ["ssdeep"], unique=False)
op.create_table(
"member",
sa.Column("user_id", sa.Integer(), nullable=True),
sa.Column("group_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["group_id"],
["group.id"],
),
sa.ForeignKeyConstraint(
["user_id"],
["user.id"],
),
)
op.create_table(
"metakey",
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("object_id", sa.Integer(), nullable=True),
sa.Column("key", sa.String(length=64), nullable=True),
sa.Column("value", sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
["key"],
["metakey_definition.key"],
),
sa.ForeignKeyConstraint(
["object_id"],
["object.id"],
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("object_id", "key", "value"),
)
op.create_index(op.f("ix_metakey_key"), "metakey", ["key"], unique=False)
op.create_index(op.f("ix_metakey_value"), "metakey", ["value"], unique=False)
op.create_table(
"metakey_permission",
sa.Column("key", sa.String(length=64), nullable=False),
sa.Column("group_id", sa.Integer(), autoincrement=False, nullable=False),
sa.Column("can_read", sa.Boolean(), nullable=False),
sa.Column("can_set", sa.Boolean(), nullable=False),
sa.ForeignKeyConstraint(
["group_id"],
["group.id"],
),
sa.ForeignKeyConstraint(
["key"],
["metakey_definition.key"],
),
sa.PrimaryKeyConstraint("key", "group_id"),
)
op.create_index(
op.f("ix_metakey_permission_group_id"),
"metakey_permission",
["group_id"],
unique=False,
)
op.create_index(
op.f("ix_metakey_permission_key"), "metakey_permission", ["key"], unique=False
)
op.create_index(
"ix_metakey_permission_metakey_group",
"metakey_permission",
["key", "group_id"],
unique=True,
)
op.create_table(
"object_tag",
sa.Column("object_id", sa.Integer(), nullable=True),
sa.Column("tag_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["object_id"],
["object.id"],
),
sa.ForeignKeyConstraint(
["tag_id"],
["tag.id"],
),
)
op.create_index(
"ix_object_tag_object_child", "object_tag", ["object_id", "tag_id"], unique=True
)
op.create_index(
op.f("ix_object_tag_object_id"), "object_tag", ["object_id"], unique=False
)
op.create_index(
op.f("ix_object_tag_tag_id"), "object_tag", ["tag_id"], unique=False
)
op.create_table(
"permission",
sa.Column("object_id", sa.Integer(), autoincrement=False, nullable=False),
sa.Column("group_id", sa.Integer(), autoincrement=False, nullable=False),
sa.Column("access_time", sa.DateTime(), nullable=False),
sa.Column("reason_type", sa.String(length=32), nullable=True),
sa.Column("related_object_id", sa.Integer(), nullable=True),
sa.Column("related_user_id", sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
["group_id"],
["group.id"],
),
sa.ForeignKeyConstraint(
["object_id"],
["object.id"],
),
sa.ForeignKeyConstraint(
["related_object_id"],
["object.id"],
),
sa.ForeignKeyConstraint(
["related_user_id"],
["user.id"],
),
sa.PrimaryKeyConstraint("object_id", "group_id"),
)
op.create_index(
op.f("ix_permission_access_time"), "permission", ["access_time"], unique=False
)
op.create_index(
op.f("ix_permission_group_id"), "permission", ["group_id"], unique=False
)
op.create_index(
"ix_permission_group_object",
"permission",
["object_id", "group_id"],
unique=True,
)
op.create_index(
op.f("ix_permission_object_id"), "permission", ["object_id"], unique=False
)
op.create_index(
op.f("ix_permission_related_user_id"),
"permission",
["related_user_id"],
unique=False,
)
op.create_table(
"relation",
sa.Column("parent_id", sa.Integer(), nullable=True),
sa.Column("child_id", sa.Integer(), nullable=True),
sa.Column("creation_time", sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
["child_id"],
["object.id"],
),
sa.ForeignKeyConstraint(
["parent_id"],
["object.id"],
),
)
op.create_index(
op.f("ix_relation_child_id"), "relation", ["child_id"], unique=False
)
op.create_index(
"ix_relation_parent_child", "relation", ["parent_id", "child_id"], unique=True
)
op.create_index(
op.f("ix_relation_parent_id"), "relation", ["parent_id"], unique=False
)
op.create_table(
"static_config",
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("family", sa.String(length=32), nullable=True),
sa.Column(
"config_type", sa.String(length=32), server_default="static", nullable=False
),
sa.Column("cfg", postgresql.JSON(astext_type=sa.Text()), nullable=False),
sa.ForeignKeyConstraint(
["id"],
["object.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(
op.f("ix_static_config_config_type"),
"static_config",
["config_type"],
unique=False,
)
op.create_index(
op.f("ix_static_config_family"), "static_config", ["family"], unique=False
)
op.create_table(
"text_blob",
sa.Column("id", sa.Integer(), nullable=False),
sa.Column("blob_name", sa.String(), nullable=True),
sa.Column("blob_size", sa.Integer(), nullable=False),
sa.Column("blob_type", sa.String(length=32), nullable=True),
sa.Column("content", sa.String(), nullable=False),
sa.Column("last_seen", sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(
["id"],
["object.id"],
),
sa.PrimaryKeyConstraint("id"),
)
op.create_index(
op.f("ix_text_blob_blob_name"), "text_blob", ["blob_name"], unique=False
)
op.create_index(
op.f("ix_text_blob_blob_size"), "text_blob", ["blob_size"], unique=False
)
op.create_index(
op.f("ix_text_blob_blob_type"), "text_blob", ["blob_type"], unique=False
)
op.create_index(
op.f("ix_text_blob_last_seen"), "text_blob", ["last_seen"], unique=False
)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('categories', sa.Column('id', sa.Integer(),
nullable=False),
sa.Column('name', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'users', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('username', sa.String(length=255), nullable=True),
sa.Column('email', sa.String(length=225), nullable=True),
sa.Column('pass_secure', sa.String(length=255), nullable=True),
sa.Column('bio', sa.String(length=255), nullable=True),
sa.Column('profile_pic_path', sa.String(), nullable=True),
sa.Column('password_hash', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_users_email'), 'users', ['email'], unique=True)
op.create_index(op.f('ix_users_username'),
'users', ['username'],
unique=False)
op.create_table('photoprofiles',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('pic_path', sa.String(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('pitches', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=255), nullable=True),
sa.Column('title', sa.String(length=255), nullable=True),
sa.Column('content', sa.String(length=255), nullable=True),
sa.Column('category', sa.Integer(), nullable=True),
sa.Column('vote', sa.Integer(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['category'],
['categories.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('comments', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('pitch_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['pitch_id'],
['pitches.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('votes', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=225), nullable=True),
sa.Column('vote', sa.Integer(), nullable=True),
sa.Column('pitch_id', sa.Integer(), nullable=True),
sa.Column('comment_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['comment_id'],
['comments.id'],
), sa.ForeignKeyConstraint(
['pitch_id'],
['pitches.id'],
), sa.PrimaryKeyConstraint('id'))
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table('groups', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(), nullable=True),
sa.Column('shared_computer', sa.Boolean(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
op.create_table(
'simulations', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(length=100), nullable=True),
sa.Column('folder_name', sa.String(length=50), nullable=True),
sa.Column('preview_image_filename',
sa.String(length=50),
nullable=True),
sa.Column('preview_image_credit', sa.String(length=200),
nullable=True),
sa.Column('desc', sa.String(length=500), nullable=True),
sa.Column('first_page_id', sa.Integer(), nullable=True),
sa.Column('order', sa.Integer(), nullable=True),
sa.Column('password', sa.String(length=50), nullable=True),
sa.Column('enabled', sa.Boolean(), nullable=True),
sa.Column('show_library', sa.Boolean(), nullable=True),
sa.Column('library', sa.String(length=5000), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table('pages', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('title', sa.String(length=100), nullable=True),
sa.Column('sim_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['sim_id'],
['simulations.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('actions', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=200), nullable=True),
sa.Column('value', sa.String(length=200), nullable=True),
sa.Column('page_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['page_id'],
['pages.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'choices', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('type', sa.String(length=200), nullable=True),
sa.Column('text', sa.String(length=200), nullable=True),
sa.Column('destination', sa.String(length=200), nullable=True),
sa.Column('tag', sa.String(length=200), nullable=True),
sa.Column('page_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['page_id'],
['pages.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'links', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('order', sa.Integer(), nullable=True),
sa.Column('show', sa.String(length=1000), nullable=True),
sa.Column('button_text', sa.String(length=200), nullable=True),
sa.Column('top_text', sa.String(length=100), nullable=True),
sa.Column('show_well', sa.Boolean(), nullable=True),
sa.Column('page_src_id', sa.Integer(), nullable=True),
sa.Column('page_dest_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['page_dest_id'],
['pages.id'],
), sa.ForeignKeyConstraint(
['page_src_id'],
['pages.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('page_modifiers',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=200), nullable=True),
sa.Column('value', sa.String(length=200), nullable=True),
sa.Column('page_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['page_id'],
['pages.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'sections', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('content', sa.String(length=10000), nullable=True),
sa.Column('order', sa.Integer(), nullable=True),
sa.Column('show', sa.String(length=1000), nullable=True),
sa.Column('page_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['page_id'],
['pages.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('users', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=100), nullable=True),
sa.Column('last_page', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['last_page'],
['pages.id'],
), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name'))
op.create_table('group_users', sa.Column('id',
sa.Integer(),
nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('group_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['group_id'],
['groups.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'linkactions', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('type', sa.Integer(), nullable=True),
sa.Column('icon', sa.String(length=50), nullable=True),
sa.Column('action_string', sa.String(length=5000), nullable=True),
sa.Column('link_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['link_id'],
['links.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('logged_in_users',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'logs', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('content', sa.String(length=2000), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table(
'notes', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('content', sa.String(length=2000), nullable=True),
sa.Column('icon', sa.String(length=20), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('tag', sa.String(length=200), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('prompts', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('order', sa.Integer(), nullable=True),
sa.Column('prompt', sa.String(length=300), nullable=True),
sa.Column('required', sa.Boolean(), nullable=True),
sa.Column('text_area', sa.Boolean(), nullable=True),
sa.Column('var_name', sa.String(length=50), nullable=True),
sa.Column('link_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['link_id'],
['links.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('sim_users', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('sim_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['sim_id'],
['simulations.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['users.id'],
), sa.PrimaryKeyConstraint('id'))
def upgrade():
op.create_table(
"kirby_mcq",
sa.Column(
"patient_id",
sa.Integer(),
nullable=False,
comment="(TASK) Foreign key to patient.id (for this device/era)",
),
sa.Column(
"when_created",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=False,
comment=(
"(TASK) Date/time this task instance was created (ISO 8601)"),
),
sa.Column(
"when_firstexit",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=True,
comment=(
"(TASK) Date/time of the first exit from this task (ISO 8601)"
),
),
sa.Column(
"firstexit_is_finish",
sa.Boolean(),
nullable=True,
comment=("(TASK) Was the first exit from the task because it was"
" finished (1)?"),
),
sa.Column(
"firstexit_is_abort",
sa.Boolean(),
nullable=True,
comment=("(TASK) Was the first exit from this task because it was"
" aborted (1)?"),
),
sa.Column(
"editing_time_s",
sa.Float(),
nullable=True,
comment="(TASK) Time spent editing (s)",
),
sa.Column(
"_pk",
sa.Integer(),
autoincrement=True,
nullable=False,
comment="(SERVER) Primary key (on the server)",
),
sa.Column(
"_device_id",
sa.Integer(),
nullable=False,
comment="(SERVER) ID of the source tablet device",
),
sa.Column(
"_era",
sa.String(length=32),
nullable=False,
comment=(
"(SERVER) 'NOW', or when this row was preserved and removed"
" from the source device (UTC ISO 8601)"),
),
sa.Column(
"_current",
sa.Boolean(),
nullable=False,
comment="(SERVER) Is the row current (1) or not (0)?",
),
sa.Column(
"_when_added_exact",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=True,
comment="(SERVER) Date/time this row was added (ISO 8601)",
),
sa.Column(
"_when_added_batch_utc",
sa.DateTime(),
nullable=True,
comment=(
"(SERVER) Date/time of the upload batch that added this row"
" (DATETIME in UTC)"),
),
sa.Column(
"_adding_user_id",
sa.Integer(),
nullable=True,
comment="(SERVER) ID of user that added this row",
),
sa.Column(
"_when_removed_exact",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=True,
comment=("(SERVER) Date/time this row was removed, i.e. made not"
" current (ISO 8601)"),
),
sa.Column(
"_when_removed_batch_utc",
sa.DateTime(),
nullable=True,
comment=(
"(SERVER) Date/time of the upload batch that removed this row"
" (DATETIME in UTC)"),
),
sa.Column(
"_removing_user_id",
sa.Integer(),
nullable=True,
comment="(SERVER) ID of user that removed this row",
),
sa.Column(
"_preserving_user_id",
sa.Integer(),
nullable=True,
comment="(SERVER) ID of user that preserved this row",
),
sa.Column(
"_forcibly_preserved",
sa.Boolean(),
nullable=True,
comment=("(SERVER) Forcibly preserved by superuser (rather than"
" normally preserved by tablet)?"),
),
sa.Column(
"_predecessor_pk",
sa.Integer(),
nullable=True,
comment="(SERVER) PK of predecessor record, prior to modification",
),
sa.Column(
"_successor_pk",
sa.Integer(),
nullable=True,
comment=(
"(SERVER) PK of successor record (after modification) or NULL"
" (whilst live, or after deletion)"),
),
sa.Column(
"_manually_erased",
sa.Boolean(),
nullable=True,
comment="(SERVER) Record manually erased (content destroyed)?",
),
sa.Column(
"_manually_erased_at",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=True,
comment="(SERVER) Date/time of manual erasure (ISO 8601)",
),
sa.Column(
"_manually_erasing_user_id",
sa.Integer(),
nullable=True,
comment="(SERVER) ID of user that erased this row manually",
),
sa.Column(
"_camcops_version",
SemanticVersionColType(length=147),
nullable=True,
comment="(SERVER) CamCOPS version number of the uploading device",
),
sa.Column(
"_addition_pending",
sa.Boolean(),
nullable=False,
comment="(SERVER) Addition pending?",
),
sa.Column(
"_removal_pending",
sa.Boolean(),
nullable=True,
comment="(SERVER) Removal pending?",
),
sa.Column(
"_group_id",
sa.Integer(),
nullable=False,
comment="(SERVER) ID of group to which this record belongs",
),
sa.Column(
"id",
sa.Integer(),
nullable=False,
comment="(TASK) Primary key (task ID) on the tablet device",
),
sa.Column(
"when_last_modified",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=True,
comment=(
"(STANDARD) Date/time this row was last modified on the source"
" tablet device (ISO 8601)"),
),
sa.Column(
"_move_off_tablet",
sa.Boolean(),
nullable=True,
comment="(SERVER/TABLET) Record-specific preservation pending?",
),
sa.ForeignKeyConstraint(
["_adding_user_id"],
["_security_users.id"],
name=op.f("fk_kirby_mcq__adding_user_id"),
),
sa.ForeignKeyConstraint(
["_device_id"],
["_security_devices.id"],
name=op.f("fk_kirby_mcq__device_id"),
),
sa.ForeignKeyConstraint(
["_group_id"],
["_security_groups.id"],
name=op.f("fk_kirby_mcq__group_id"),
),
sa.ForeignKeyConstraint(
["_manually_erasing_user_id"],
["_security_users.id"],
name=op.f("fk_kirby_mcq__manually_erasing_user_id"),
),
sa.ForeignKeyConstraint(
["_preserving_user_id"],
["_security_users.id"],
name=op.f("fk_kirby_mcq__preserving_user_id"),
),
sa.ForeignKeyConstraint(
["_removing_user_id"],
["_security_users.id"],
name=op.f("fk_kirby_mcq__removing_user_id"),
),
sa.PrimaryKeyConstraint("_pk", name=op.f("pk_kirby_mcq")),
mysql_charset="utf8mb4 COLLATE utf8mb4_unicode_ci",
mysql_engine="InnoDB",
mysql_row_format="DYNAMIC",
)
with op.batch_alter_table("kirby_mcq", schema=None) as batch_op:
batch_op.create_index(batch_op.f("ix_kirby_mcq__current"),
["_current"],
unique=False)
batch_op.create_index(batch_op.f("ix_kirby_mcq__device_id"),
["_device_id"],
unique=False)
batch_op.create_index(batch_op.f("ix_kirby_mcq__era"), ["_era"],
unique=False)
batch_op.create_index(batch_op.f("ix_kirby_mcq__group_id"),
["_group_id"],
unique=False)
batch_op.create_index(batch_op.f("ix_kirby_mcq__pk"), ["_pk"],
unique=False)
batch_op.create_index(batch_op.f("ix_kirby_mcq_id"), ["id"],
unique=False)
batch_op.create_index(batch_op.f("ix_kirby_mcq_patient_id"),
["patient_id"],
unique=False)
batch_op.create_index(
batch_op.f("ix_kirby_mcq_when_last_modified"),
["when_last_modified"],
unique=False,
)
op.create_table(
"kirby_mcq_trials",
sa.Column(
"kirby_mcq_id",
sa.Integer(),
nullable=False,
comment="FK to kirby_mcq",
),
sa.Column(
"trial",
sa.Integer(),
nullable=False,
comment="Trial number (1-based)",
),
sa.Column(
"sir",
sa.Integer(),
nullable=True,
comment="Small immediate reward",
),
sa.Column("ldr",
sa.Integer(),
nullable=True,
comment="Large delayed reward"),
sa.Column("delay_days",
sa.Integer(),
nullable=True,
comment="Delay in days"),
sa.Column(
"currency",
sa.Unicode(length=3),
nullable=True,
comment="Currency symbol",
),
sa.Column(
"currency_symbol_first",
sa.Boolean(),
nullable=True,
comment="Does the currency symbol come before the amount?",
),
sa.Column(
"chose_ldr",
sa.Boolean(),
nullable=True,
comment="Did the subject choose the large delayed reward?",
),
sa.Column(
"_pk",
sa.Integer(),
autoincrement=True,
nullable=False,
comment="(SERVER) Primary key (on the server)",
),
sa.Column(
"_device_id",
sa.Integer(),
nullable=False,
comment="(SERVER) ID of the source tablet device",
),
sa.Column(
"_era",
sa.String(length=32),
nullable=False,
comment=(
"(SERVER) 'NOW', or when this row was preserved and removed"
" from the source device (UTC ISO 8601)"),
),
sa.Column(
"_current",
sa.Boolean(),
nullable=False,
comment="(SERVER) Is the row current (1) or not (0)?",
),
sa.Column(
"_when_added_exact",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=True,
comment="(SERVER) Date/time this row was added (ISO 8601)",
),
sa.Column(
"_when_added_batch_utc",
sa.DateTime(),
nullable=True,
comment=(
"(SERVER) Date/time of the upload batch that added this row"
" (DATETIME in UTC)"),
),
sa.Column(
"_adding_user_id",
sa.Integer(),
nullable=True,
comment="(SERVER) ID of user that added this row",
),
sa.Column(
"_when_removed_exact",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=True,
comment=("(SERVER) Date/time this row was removed, i.e. made not"
" current (ISO 8601)"),
),
sa.Column(
"_when_removed_batch_utc",
sa.DateTime(),
nullable=True,
comment=(
"(SERVER) Date/time of the upload batch that removed this row"
" (DATETIME in UTC)"),
),
sa.Column(
"_removing_user_id",
sa.Integer(),
nullable=True,
comment="(SERVER) ID of user that removed this row",
),
sa.Column(
"_preserving_user_id",
sa.Integer(),
nullable=True,
comment="(SERVER) ID of user that preserved this row",
),
sa.Column(
"_forcibly_preserved",
sa.Boolean(),
nullable=True,
comment=("(SERVER) Forcibly preserved by superuser (rather than"
" normally preserved by tablet)?"),
),
sa.Column(
"_predecessor_pk",
sa.Integer(),
nullable=True,
comment="(SERVER) PK of predecessor record, prior to modification",
),
sa.Column(
"_successor_pk",
sa.Integer(),
nullable=True,
comment=(
"(SERVER) PK of successor record (after modification) or NULL"
" (whilst live, or after deletion)"),
),
sa.Column(
"_manually_erased",
sa.Boolean(),
nullable=True,
comment="(SERVER) Record manually erased (content destroyed)?",
),
sa.Column(
"_manually_erased_at",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=True,
comment="(SERVER) Date/time of manual erasure (ISO 8601)",
),
sa.Column(
"_manually_erasing_user_id",
sa.Integer(),
nullable=True,
comment="(SERVER) ID of user that erased this row manually",
),
sa.Column(
"_camcops_version",
SemanticVersionColType(length=147),
nullable=True,
comment="(SERVER) CamCOPS version number of the uploading device",
),
sa.Column(
"_addition_pending",
sa.Boolean(),
nullable=False,
comment="(SERVER) Addition pending?",
),
sa.Column(
"_removal_pending",
sa.Boolean(),
nullable=True,
comment="(SERVER) Removal pending?",
),
sa.Column(
"_group_id",
sa.Integer(),
nullable=False,
comment="(SERVER) ID of group to which this record belongs",
),
sa.Column(
"id",
sa.Integer(),
nullable=False,
comment="(TASK) Primary key (task ID) on the tablet device",
),
sa.Column(
"when_last_modified",
PendulumDateTimeAsIsoTextColType(length=32),
nullable=True,
comment=(
"(STANDARD) Date/time this row was last modified on the source"
" tablet device (ISO 8601)"),
),
sa.Column(
"_move_off_tablet",
sa.Boolean(),
nullable=True,
comment="(SERVER/TABLET) Record-specific preservation pending?",
),
sa.ForeignKeyConstraint(
["_adding_user_id"],
["_security_users.id"],
name=op.f("fk_kirby_mcq_trials__adding_user_id"),
),
sa.ForeignKeyConstraint(
["_device_id"],
["_security_devices.id"],
name=op.f("fk_kirby_mcq_trials__device_id"),
),
sa.ForeignKeyConstraint(
["_group_id"],
["_security_groups.id"],
name=op.f("fk_kirby_mcq_trials__group_id"),
),
sa.ForeignKeyConstraint(
["_manually_erasing_user_id"],
["_security_users.id"],
name=op.f("fk_kirby_mcq_trials__manually_erasing_user_id"),
),
sa.ForeignKeyConstraint(
["_preserving_user_id"],
["_security_users.id"],
name=op.f("fk_kirby_mcq_trials__preserving_user_id"),
),
sa.ForeignKeyConstraint(
["_removing_user_id"],
["_security_users.id"],
name=op.f("fk_kirby_mcq_trials__removing_user_id"),
),
sa.PrimaryKeyConstraint("_pk", name=op.f("pk_kirby_mcq_trials")),
mysql_charset="utf8mb4 COLLATE utf8mb4_unicode_ci",
mysql_engine="InnoDB",
mysql_row_format="DYNAMIC",
)
with op.batch_alter_table("kirby_mcq_trials", schema=None) as batch_op:
batch_op.create_index(
batch_op.f("ix_kirby_mcq_trials__current"),
["_current"],
unique=False,
)
batch_op.create_index(
batch_op.f("ix_kirby_mcq_trials__device_id"),
["_device_id"],
unique=False,
)
batch_op.create_index(batch_op.f("ix_kirby_mcq_trials__era"), ["_era"],
unique=False)
batch_op.create_index(
batch_op.f("ix_kirby_mcq_trials__group_id"),
["_group_id"],
unique=False,
)
batch_op.create_index(batch_op.f("ix_kirby_mcq_trials__pk"), ["_pk"],
unique=False)
batch_op.create_index(batch_op.f("ix_kirby_mcq_trials_id"), ["id"],
unique=False)
batch_op.create_index(
batch_op.f("ix_kirby_mcq_trials_when_last_modified"),
["when_last_modified"],
unique=False,
)
def upgrade():
battles_id_seq = sa.Sequence('battles_id_seq')
battle_trainers_id_seq = sa.Sequence('battle_trainers_id_seq')
battle_pokemon_id_seq = sa.Sequence('battle_pokemon_id_seq')
connection = op.get_bind()
battles_id_seq.create(bind=connection)
battle_trainers_id_seq.create(bind=connection)
battle_pokemon_id_seq.create(bind=connection)
op.create_table(
'battles', sa.Column('id',
sa.Integer(),
battles_id_seq,
nullable=False),
sa.Column('identifier', sa.Unicode(), nullable=False),
sa.Column('name', sa.Unicode(), nullable=False),
sa.Column('start_date', sa.Date(), nullable=False),
sa.Column('end_date', sa.Date(), nullable=True),
sa.Column('tcodf_thread_id', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('identifier'),
sa.UniqueConstraint('identifier'), sa.UniqueConstraint('name'),
sa.UniqueConstraint('name'))
op.create_table(
'battle_teams', sa.Column('battle_id', sa.Integer(), nullable=False),
sa.Column('team_number',
sa.Integer(),
autoincrement=False,
nullable=False),
sa.Column('won', sa.Boolean(), nullable=False),
sa.ForeignKeyConstraint(
['battle_id'],
['battles.id'],
), sa.PrimaryKeyConstraint('battle_id', 'team_number'))
op.create_table(
'battle_referees', sa.Column('battle_id', sa.Integer(),
nullable=False),
sa.Column('trainer_id', sa.Integer(), nullable=False),
sa.Column('is_emergency_ref', sa.Boolean(), nullable=False),
sa.ForeignKeyConstraint(
['battle_id'],
['battles.id'],
), sa.ForeignKeyConstraint(
['trainer_id'],
['trainers.id'],
), sa.PrimaryKeyConstraint('battle_id', 'trainer_id'))
op.create_table(
'battle_trainers',
sa.Column('id', sa.Integer(), battle_trainers_id_seq, nullable=False),
sa.Column('battle_id', sa.Integer(), nullable=False),
sa.Column('trainer_id', sa.Integer(), nullable=False),
sa.Column('name', sa.Unicode(), nullable=False),
sa.Column('team_number',
sa.Integer(),
autoincrement=False,
nullable=True),
sa.ForeignKeyConstraint(
['battle_id', 'team_number'],
['battle_teams.battle_id', 'battle_teams.team_number'],
name='battle_trainer_team_fkey'),
sa.ForeignKeyConstraint(
['battle_id'],
['battles.id'],
), sa.ForeignKeyConstraint(
['trainer_id'],
['trainers.id'],
), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('battle_id', 'trainer_id'))
op.create_table(
'battle_pokemon',
sa.Column('id', sa.Integer(), battle_pokemon_id_seq, nullable=False),
sa.Column('pokemon_id', sa.Integer(), nullable=True),
sa.Column('battle_trainer_id', sa.Integer(), nullable=False),
sa.Column('name', sa.Unicode(), nullable=False),
sa.Column('pokemon_form_id', sa.Integer(), nullable=False),
sa.Column('ability_slot', sa.Integer(), nullable=False),
sa.Column('item_id', sa.Integer(), nullable=True),
sa.Column('gender_id', sa.Integer(), nullable=True),
sa.Column('experience', sa.Integer(), nullable=False),
sa.Column('happiness', sa.Integer(), nullable=False),
sa.Column('participated', sa.Boolean(), nullable=False),
sa.ForeignKeyConstraint(['battle_trainer_id'], ['battle_trainers.id']),
sa.ForeignKeyConstraint(
['item_id'],
['items.id'],
), sa.ForeignKeyConstraint(['pokemon_form_id'], ['pokemon_forms.id']),
sa.ForeignKeyConstraint(
['pokemon_id'],
['pokemon.id'],
), sa.ForeignKeyConstraint(
['gender_id'],
['genders.id'],
),
sa.ForeignKeyConstraint(['pokemon_form_id', 'ability_slot'], [
'pokemon_form_abilities.pokemon_form_id',
'pokemon_form_abilities.slot'
], 'battle_pokemon_ability_fkey'), sa.PrimaryKeyConstraint('id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'crop', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('crop_name', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('crop_name'))
op.create_table(
'field', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('location', sa.String(length=255), nullable=True),
sa.Column('size', sa.Integer(), nullable=True),
sa.Column('field_name', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('field_name'))
op.create_table(
'images', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('image_path', sa.String(length=255), nullable=True),
sa.Column('year', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'map', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('coordinates', sa.String(length=255), nullable=True),
sa.Column('field', sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint('id'))
op.create_table(
'user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('first_name', sa.String(length=255), nullable=True),
sa.Column('last_name', sa.String(length=255), nullable=True),
sa.Column('admin', sa.Integer(), server_default='0', nullable=True),
sa.Column('email', sa.String(length=255), nullable=True),
sa.Column('password', sa.String(length=255), nullable=True),
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text('(CURRENT_TIMESTAMP)'),
nullable=True),
sa.Column('updated_at',
sa.DateTime(),
server_default=sa.text('(CURRENT_TIMESTAMP)'),
nullable=True), sa.PrimaryKeyConstraint('id'))
op.create_table(
'harvest', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('year', sa.Integer(), nullable=True),
sa.Column('harvest_yield', sa.String(length=255), nullable=True),
sa.Column('crop_id', sa.Integer(), nullable=False),
sa.Column('field_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['crop_id'],
['crop.id'],
), sa.ForeignKeyConstraint(
['field_id'],
['field.id'],
), sa.PrimaryKeyConstraint('id'))
op.create_table('crop_harvest',
sa.Column('crop_id', sa.Integer(), nullable=False),
sa.Column('harvest_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['crop_id'],
['crop.id'],
), sa.ForeignKeyConstraint(
['harvest_id'],
['harvest.id'],
), sa.PrimaryKeyConstraint('crop_id', 'harvest_id'))
op.create_table('field_harvest',
sa.Column('field_id', sa.Integer(), nullable=False),
sa.Column('harvest_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(
['field_id'],
['field.id'],
), sa.ForeignKeyConstraint(
['harvest_id'],
['harvest.id'],
), sa.PrimaryKeyConstraint('field_id', 'harvest_id'))
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'category', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('category', sa.String(length=200), nullable=True),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('post_id', sa.Integer(), nullable=True),
sa.Column('software_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['post_id'], ['post.id'],
name=op.f('fk_category_post_id_post')),
sa.ForeignKeyConstraint(['software_id'], ['software.id'],
name=op.f('fk_category_software_id_software')),
sa.PrimaryKeyConstraint('id', name=op.f('pk_category')))
with op.batch_alter_table('category', schema=None) as batch_op:
batch_op.create_index(batch_op.f('ix_category_category'), ['category'],
unique=False)
batch_op.create_index(batch_op.f('ix_category_timestamp'),
['timestamp'],
unique=False)
op.create_table(
'report', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=200), nullable=True),
sa.Column('description', sa.String(length=500), nullable=True),
sa.Column('type', sa.String(length=200), nullable=True),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('post_id', sa.Integer(), nullable=True),
sa.Column('software_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['post_id'], ['post.id'],
name=op.f('fk_report_post_id_post')),
sa.ForeignKeyConstraint(['software_id'], ['software.id'],
name=op.f('fk_report_software_id_software')),
sa.PrimaryKeyConstraint('id', name=op.f('pk_report')))
with op.batch_alter_table('report', schema=None) as batch_op:
batch_op.create_index(batch_op.f('ix_report_description'),
['description'],
unique=False)
batch_op.create_index(batch_op.f('ix_report_name'), ['name'],
unique=False)
batch_op.create_index(batch_op.f('ix_report_timestamp'), ['timestamp'],
unique=False)
batch_op.create_index(batch_op.f('ix_report_type'), ['type'],
unique=False)
op.drop_table('categoria')
with op.batch_alter_table('denuncia', schema=None) as batch_op:
batch_op.drop_index('ix_denuncia_descricao')
batch_op.drop_index('ix_denuncia_tipo')
op.drop_table('denuncia')
with op.batch_alter_table('comment', schema=None) as batch_op:
batch_op.add_column(
sa.Column('software_id', sa.Integer(), nullable=True))
batch_op.create_foreign_key(
batch_op.f('fk_comment_software_id_software'), 'software',
['software_id'], ['id'])
with op.batch_alter_table('post', schema=None) as batch_op:
batch_op.drop_index('ix_post_categorie')
batch_op.drop_index('ix_post_tag')
batch_op.drop_column('tag')
batch_op.drop_column('categorie')
with op.batch_alter_table('software', schema=None) as batch_op:
batch_op.drop_index('ix_software_categorie')
batch_op.drop_index('ix_software_tag')
batch_op.drop_column('tag')
batch_op.drop_column('categorie')
with op.batch_alter_table('tag', schema=None) as batch_op:
batch_op.add_column(sa.Column('post_id', sa.Integer(), nullable=True))
batch_op.add_column(
sa.Column('software_id', sa.Integer(), nullable=True))
batch_op.add_column(
sa.Column('tag', sa.String(length=200), nullable=True))
batch_op.add_column(
sa.Column('timestamp', sa.DateTime(), nullable=True))
batch_op.create_index(batch_op.f('ix_tag_tag'), ['tag'], unique=False)
batch_op.create_index(batch_op.f('ix_tag_timestamp'), ['timestamp'],
unique=False)
batch_op.drop_index('ix_tag_palavraChave')
batch_op.create_foreign_key(batch_op.f('fk_tag_software_id_software'),
'software', ['software_id'], ['id'])
batch_op.create_foreign_key(batch_op.f('fk_tag_post_id_post'), 'post',
['post_id'], ['id'])
batch_op.drop_column('palavraChave')
def upgrade():
# Rename score table to answer_criterion_score
try:
# expected foreign key to follow naming conventions
with op.batch_alter_table('score',
naming_convention=convention) as batch_op:
# drop the fk before altering the column
batch_op.drop_constraint('fk_score_assignment_id_assignment',
'foreignkey')
batch_op.drop_constraint('fk_score_answer_id_answer', 'foreignkey')
batch_op.drop_constraint('fk_score_criterion_id_criterion',
'foreignkey')
except sa.exc.InternalError:
# if not, it is likely this name
with op.batch_alter_table('score') as batch_op:
# drop the fk before altering the column
batch_op.drop_constraint('score_ibfk_1', 'foreignkey')
batch_op.drop_constraint('score_ibfk_2', 'foreignkey')
batch_op.drop_constraint('score_ibfk_3', 'foreignkey')
op.rename_table('score', 'answer_criterion_score')
with op.batch_alter_table('answer_criterion_score',
naming_convention=convention) as batch_op:
# create the fk
batch_op.create_foreign_key(
'fk_answer_criterion_score_assignment_id_assignment',
'assignment', ['assignment_id'], ['id'],
ondelete="CASCADE")
batch_op.create_foreign_key(
'fk_answer_criterion_score_answer_id_answer',
'answer', ['answer_id'], ['id'],
ondelete="CASCADE")
batch_op.create_foreign_key(
'fk_answer_criterion_score_criterion_id_criterion',
'criterion', ['criterion_id'], ['id'],
ondelete="CASCADE")
# create new answer_score table
answer_score_table = op.create_table(
'answer_score',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('assignment_id', sa.Integer(), nullable=False),
sa.Column('answer_id', sa.Integer(), nullable=False),
sa.Column('scoring_algorithm',
EnumType(ScoringAlgorithm),
nullable=True),
sa.Column('score', sa.Float(), nullable=False),
sa.Column('variable1', sa.Float(), nullable=True),
sa.Column('variable2', sa.Float(), nullable=True),
sa.Column('rounds', sa.Integer(), nullable=False),
sa.Column('wins', sa.Integer(), nullable=False),
sa.Column('loses', sa.Integer(), nullable=False),
sa.Column('opponents', sa.Integer(), nullable=False),
sa.Column('modified_user_id', sa.Integer(), nullable=True),
sa.Column('modified', sa.DateTime(), nullable=False),
sa.Column('created_user_id', sa.Integer(), nullable=True),
sa.Column('created', sa.DateTime(), nullable=False),
sa.ForeignKeyConstraint(['answer_id'], ['answer.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['assignment_id'], ['assignment.id'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['created_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.ForeignKeyConstraint(['modified_user_id'], ['user.id'],
ondelete='SET NULL'),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('answer_id'),
mysql_charset='utf8',
mysql_collate='utf8_unicode_ci',
mysql_engine='InnoDB')
op.create_index(op.f('ix_answer_score_score'),
'answer_score', ['score'],
unique=False)
# migrate aggregate data from answer_criteria_score to answer_score
answer_criterion_score_table = sa.table(
'answer_criterion_score', sa.column('id', sa.Integer),
sa.Column('assignment_id', sa.Integer),
sa.Column('answer_id', sa.Integer),
sa.Column('criterion_id', sa.Integer),
sa.Column('scoring_algorithm', EnumType(ScoringAlgorithm)),
sa.Column('score', sa.Float), sa.Column('variable1', sa.Float),
sa.Column('variable2', sa.Float), sa.Column('rounds', sa.Integer),
sa.Column('wins', sa.Integer), sa.Column('loses', sa.Integer),
sa.Column('opponents', sa.Integer))
connection = op.get_bind()
answer_scores = {}
for answer_criterion_score in connection.execute(
answer_criterion_score_table.select()):
answer_scores.setdefault(
answer_criterion_score.answer_id,
{
'assignment_id': answer_criterion_score.assignment_id,
'answer_id': answer_criterion_score.answer_id,
'scoring_algorithm': answer_criterion_score.scoring_algorithm,
'rounds': answer_criterion_score.rounds,
'wins': answer_criterion_score.wins,
'loses': answer_criterion_score.loses,
'opponents': answer_criterion_score.opponents,
# to be averaged
'score': [],
'variable1': [],
'variable2': []
})
answer_scores[answer_criterion_score.answer_id]['score'].append(
answer_criterion_score.score)
if answer_criterion_score.variable1:
answer_scores[
answer_criterion_score.answer_id]['variable1'].append(
answer_criterion_score.variable1)
if answer_criterion_score.variable2:
answer_scores[
answer_criterion_score.answer_id]['variable2'].append(
answer_criterion_score.variable2)
for answer_id, score in answer_scores.items():
average_score = sum(score['score']) / float(len(
score['score'])) if len(score['score']) else 0
average_variable1 = sum(score['variable1']) / float(
len(score['variable1'])) if len(score['variable1']) else None
average_variable2 = sum(score['variable2']) / float(
len(score['variable2'])) if len(score['variable2']) else None
connection.execute(answer_score_table.insert().values(
assignment_id=score['assignment_id'],
answer_id=score['answer_id'],
scoring_algorithm=score['scoring_algorithm'],
rounds=score['rounds'],
wins=score['wins'],
loses=score['loses'],
opponents=score['opponents'],
score=average_score,
variable1=average_variable1,
variable2=average_variable2,
modified=datetime.utcnow(),
created=datetime.utcnow()))
