Add alembic database migration

Fixes issue #19 (closed)

This branch adds alembic for database migration. Some refactoring of the python backend package has been done (.env file has been moved to the backend folder).

Please read the new README carefully!

backend/alembic/versions/9b1fabdaa344_init.py

+"""Init
+
+Revision ID: 9b1fabdaa344
+Revises: 
+Create Date: 2023-03-15 18:04:12.205139
+
+"""
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision = '9b1fabdaa344'
+down_revision = None
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table('courses',
+    sa.Column('name', sa.String(), nullable=True),
+    sa.Column('id', sa.String(), nullable=False),
+    sa.Column('semester', sa.String(), nullable=True),
+    sa.Column('responsible', sa.String(), nullable=True),
+    sa.Column('website', sa.String(), nullable=True),
+    sa.PrimaryKeyConstraint('id')
+    )
+    op.create_table('questions',
+    sa.Column('id', sa.Integer(), nullable=False),
+    sa.Column('question', sa.String(), nullable=True),
+    sa.Column('comment', sa.String(), nullable=True),
+    sa.PrimaryKeyConstraint('id')
+    )
+    op.create_table('users',
+    sa.Column('email', sa.String(), nullable=False),
+    sa.PrimaryKeyConstraint('email'),
+    sa.UniqueConstraint('email')
+    )
+    op.create_table('course_question',
+    sa.Column('course_id', sa.String(), nullable=False),
+    sa.Column('question_id', sa.Integer(), nullable=False),
+    sa.ForeignKeyConstraint(['course_id'], ['courses.id'], ),
+    sa.ForeignKeyConstraint(['question_id'], ['questions.id'], ),
+    sa.PrimaryKeyConstraint('course_id', 'question_id')
+    )
+    op.create_table('enrollment',
+    sa.Column('user_email', sa.String(), nullable=False),
+    sa.Column('course_id', sa.String(), nullable=False),
+    sa.Column('role', sa.Enum('lecturer', 'teaching assistant', 'student', name='enrollment_roles'), nullable=False),
+    sa.ForeignKeyConstraint(['course_id'], ['courses.id'], ),
+    sa.ForeignKeyConstraint(['user_email'], ['users.email'], ),
+    sa.PrimaryKeyConstraint('user_email', 'course_id', 'role')
+    )
+    op.create_table('units',
+    sa.Column('id', sa.Integer(), nullable=False),
+    sa.Column('seq_no', sa.Integer(), nullable=True),
+    sa.Column('title', sa.String(), nullable=True),
+    sa.Column('date_available', sa.Date(), nullable=True),
+    sa.Column('course_id', sa.String(), nullable=True),
+    sa.ForeignKeyConstraint(['course_id'], ['courses.id'], ),
+    sa.PrimaryKeyConstraint('id')
+    )
+    op.create_table('reflections',
+    sa.Column('id', sa.Integer(), nullable=False),
+    sa.Column('body', sa.String(), nullable=True),
+    sa.Column('timestamp', sa.Date(), nullable=True),
+    sa.Column('category', sa.String(), nullable=True),
+    sa.Column('is_interesting', sa.Boolean(), nullable=True),
+    sa.Column('is_problematic', sa.Boolean(), nullable=True),
+    sa.Column('is_sorted', sa.Boolean(), nullable=True),
+    sa.Column('user_id', sa.String(), nullable=True),
+    sa.Column('unit_id', sa.Integer(), nullable=True),
+    sa.Column('question_id', sa.Integer(), nullable=True),
+    sa.ForeignKeyConstraint(['question_id'], ['questions.id'], ),
+    sa.ForeignKeyConstraint(['unit_id'], ['units.id'], ),
+    sa.ForeignKeyConstraint(['user_id'], ['users.email'], ),
+    sa.PrimaryKeyConstraint('id')
+    )
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.drop_table('reflections')
+    op.drop_table('units')
+    op.drop_table('enrollment')
+    op.drop_table('course_question')
+    op.drop_table('users')
+    op.drop_table('questions')
+    op.drop_table('courses')
+    # ### end Alembic commands ###