1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
"""Create ConstExpenseGroup and migrate ConstExpense
Revision ID: 283aad3fa60b
Revises: 27880ab1f050
Create Date: 2016-01-10 15:20:48.661353
"""
# revision identifiers, used by Alembic.
revision = '283aad3fa60b'
down_revision = '27880ab1f050'
from alembic import op
import sqlalchemy as sa
from sqlalchemy import Column, Integer, Unicode, Date, ForeignKey
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import sessionmaker, relationship, backref
class Base (declarative_base()):
__abstract__ = True
id = Column(Integer, primary_key = True)
@declared_attr
def __tablename__ (cls):
return cls.__name__.lower()
class User(Base):
pass
class Category(Base):
pass
class ConstExpenseGroup (Base):
description = Column(Unicode(50), nullable=False)
start = Column(Date, nullable=False)
end = Column(Date, nullable=False)
user_id = Column(Integer, ForeignKey(User.id), nullable=False)
category_id = Column(Integer, ForeignKey(Category.id), nullable=False)
class ConstExpense (Base):
description = Column(Unicode(50))
start = Column(Date)
end = Column(Date)
user_id = Column(Integer)
category_id = Column(Integer)
group_id = Column(Integer, ForeignKey(ConstExpenseGroup.id))
group = relationship(ConstExpenseGroup, uselist = False)
prev_id = Column(Integer, ForeignKey('constexpense.id'))
prev = relationship('ConstExpense',
remote_side = 'ConstExpense.id', uselist = False,
backref=backref('next', uselist = False, lazy = 'immediate'))
def migrate_data(session):
expenses = []
for exp in session.query(ConstExpense).filter(ConstExpense.prev_id == None).order_by(ConstExpense.id):
group = ConstExpenseGroup(
description = exp.description,
start = exp.start,
end = exp.end,
user_id = exp.user_id,
category_id = exp.category_id)
exp.description = None
exp.group = group
session.add(group)
session.add(exp)
if exp.next is not None:
expenses.append(exp)
session.flush()
for exp in expenses:
group = exp.group
while exp.next is not None:
exp = exp.next
if exp.description == group.description:
exp.description = None
exp.group = group
if exp.start < group.start:
group.start = exp.start
if exp.end > group.end:
group.end = exp.end
session.add(exp)
session.add(group)
def upgrade():
bind = op.get_bind()
session = sessionmaker()(bind=bind)
# new table
ConstExpenseGroup.__table__.create(bind)
with op.batch_alter_table('constexpense') as batch_op:
batch_op.add_column(sa.Column('group_id', sa.Integer(), nullable=True))
batch_op.create_foreign_key('fk_constexpense_group', 'constexpensegroup', ['group_id'], ['id'])
# migrate data
migrate_data(session)
session.commit()
# new indexes
op.create_index(op.f('ix_constexpensegroup_end'), 'constexpensegroup', ['end'], unique=False)
op.create_index(op.f('ix_constexpensegroup_start'), 'constexpensegroup', ['start'], unique=False)
op.create_index(op.f('ix_constexpense_group_id'), 'constexpense', ['group_id'], unique=False)
def downgrade():
raise Exception("Irreversible migration")
|
