mediagoblin/tests/test_sql_migrations.py

   1 # GNU MediaGoblin -- federated, autonomous media hosting
   2 # Copyright (C) 2012, 2012 MediaGoblin contributors.  See AUTHORS.
   3 #
   4 # This program is free software: you can redistribute it and/or modify
   5 # it under the terms of the GNU Affero General Public License as published by
   6 # the Free Software Foundation, either version 3 of the License, or
   7 # (at your option) any later version.
   8 #
   9 # This program is distributed in the hope that it will be useful,
  10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 # GNU Affero General Public License for more details.
  13 #
  14 # You should have received a copy of the GNU Affero General Public License
  15 # along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16
  17 import copy
  18
  19 from sqlalchemy import (
  20     Table, Column, MetaData,
  21     Integer, Float, Unicode, UnicodeText, DateTime, Boolean,
  22     ForeignKey, UniqueConstraint, PickleType)
  23 from sqlalchemy.ext.declarative import declarative_base
  24 from sqlalchemy.sql import select, insert
  25 from migrate import changeset
  26
  27 from mediagoblin.db.sql.base import GMGTableBase
  28
  29
  30 # This one will get filled with local migrations
  31 FULL_MIGRATIONS = {}
  32
  33
  34 #######################################################
  35 # Migration set 1: Define initial models, no migrations
  36 #######################################################
  37
  38 Base1 = declarative_base(cls=GMGTableBase)
  39
  40 class Creature1(Base1):
  41     __tablename__ = "creature"
  42
  43     id = Column(Integer, primary_key=True)
  44     name = Column(Unicode, unique=True, nullable=False, index=True)
  45     num_legs = Column(Integer, nullable=False)
  46     is_demon = Column(Boolean)
  47
  48 class Level1(Base1):
  49     __tablename__ = "level"
  50
  51     id = Column(Integer, primary_key=True)
  52     name = Column(Unicode, unique=True, nullable=False, index=True)
  53     description = Column(UnicodeText)
  54     exits = Column(PickleType)
  55
  56 SET1_MODELS = [Creature1, Level1]
  57
  58 SET1_MIGRATIONS = []
  59
  60 #######################################################
  61 # Migration set 2: A few migrations and new model
  62 #######################################################
  63
  64 Base2 = declarative_base(cls=GMGTableBase)
  65
  66 class Creature2(Base2):
  67     __tablename__ = "creature"
  68
  69     id = Column(Integer, primary_key=True)
  70     name = Column(Unicode, unique=True, nullable=False, index=True)
  71     num_legs = Column(Integer, nullable=False)
  72
  73 class CreaturePower2(Base2):
  74     __tablename__ = "creature_power"
  75
  76     id = Column(Integer, primary_key=True)
  77     creature = Column(
  78         Integer, ForeignKey('creature.id'), nullable=False)
  79     name = Column(Unicode)
  80     description = Column(Unicode)
  81     hitpower = Column(Integer, nullable=False)
  82
  83 class Level2(Base2):
  84     __tablename__ = "level"
  85
  86     id = Column(Integer, primary_key=True)
  87     name = Column(Unicode)
  88     description = Column(UnicodeText)
  89
  90 class LevelExit2(Base2):
  91     __tablename__ = "level_exit"
  92
  93     id = Column(Integer, primary_key=True)
  94     name = Column(Unicode)
  95     from_level = Column(
  96         Integer, ForeignKey('level.id'), nullable=False)
  97     to_level = Column(
  98         Integer, ForeignKey('level.id'), nullable=False)
  99
 100 SET2_MODELS = [Creature2, CreaturePower2, Level2, LevelExit2]
 101
 102
 103 @RegisterMigration(1, FULL_MIGRATIONS)
 104 def creature_remove_is_demon(db_conn):
 105     metadata = MetaData(bind=db_conn.engine)
 106     creature_table = Table(
 107         'creature', metadata,
 108         autoload=True, autoload_with=db_conn.engine)
 109     creature_table.drop_column('is_demon')
 110
 111
 112 @RegisterMigration(2, FULL_MIGRATIONS)
 113 def creature_powers_new_table(db_conn):
 114     metadata = MetaData(bind=db_conn.engine)
 115     creature_powers = Table(
 116         'creature_power', metadata,
 117         Column('id', Integer, primary_key=True),
 118         Column('creature',
 119                Integer, ForeignKey('creature.id'), nullable=False),
 120         Column('name', Unicode),
 121         Column('description', Unicode),
 122         Column('hitpower', Integer, nullable=False))
 123     metadata.create_all(db_conn.engine)
 124
 125
 126 @RegisterMigration(3, FULL_MIGRATIONS)
 127 def level_exits_new_table(db_conn):
 128     # First, create the table
 129     # -----------------------
 130     metadata = MetaData(bind=db_conn.engine)
 131     level_exits = Table(
 132         'level_exit', metadata,
 133         Column('id', Integer, primary_key=True),
 134         Column('name', Unicode),
 135         Column('from_level',
 136                Integer, ForeignKey('level.id'), nullable=False),
 137         Column('to_level',
 138                Integer, ForeignKey('level.id'), nullable=False))
 139     metadata.create_all(db_conn.engine)
 140
 141     # And now, convert all the old exit pickles to new level exits
 142     # ------------------------------------------------------------
 143
 144     # Minimal representation of level table.
 145     # Not auto-introspecting here because of pickle table.  I'm not
 146     # sure sqlalchemy can auto-introspect pickle columns.
 147     levels = Table(
 148         'level', metadata,
 149         Column('id', Integer, primary_key=True),
 150         Column('exits', PickleType))
 151
 152     # query over and insert
 153     result = db_conn.execute(
 154         select([levels], levels.c.exits!=None))
 155
 156     for level in result:
 157         this_exit = level['exits']
 158
 159         # Insert the level exit
 160         db_conn.execute(
 161             level_exits.insert().values(
 162                 name=this_exit['name'],
 163                 from_level=this_exit['from_level'],
 164                 to_level=this_exit['to_level']))
 165
 166     # Finally, drop the old level exits pickle table
 167     # ----------------------------------------------
 168     levels.drop_column('exits')
 169
 170
 171 # A hack!  At this point we freeze-fame and get just a partial list of
 172 # migrations
 173
 174 SET2_MIGRATIONS = copy.copy(FULL_MIGRATIONS)
 175
 176 #######################################################
 177 # Migration set 3: Final migrations
 178 #######################################################
 179
 180 Base3 = declarative_base(cls=GMGTableBase)
 181
 182 class Creature3(Base3):
 183     __tablename__ = "creature"
 184
 185     id = Column(Integer, primary_key=True)
 186     name = Column(Unicode, unique=True, nullable=False, index=True)
 187     num_limbs= Column(Integer, nullable=False)
 188
 189 class CreaturePower3(Base3):
 190     __tablename__ = "creature_power"
 191
 192     id = Column(Integer, primary_key=True)
 193     creature = Column(
 194         Integer, ForeignKey('creature.id'), nullable=False, index=True)
 195     name = Column(Unicode)
 196     description = Column(Unicode)
 197     hitpower = Column(Float, nullable=False)
 198
 199 class Level3(Base3):
 200     __tablename__ = "level"
 201
 202     id = Column(Integer, primary_key=True)
 203     name = Column(Unicode)
 204     description = Column(UnicodeText)
 205
 206 class LevelExit3(Base3):
 207     __tablename__ = "level_exit"
 208
 209     id = Column(Integer, primary_key=True)
 210     name = Column(Unicode)
 211     from_level = Column(
 212         Integer, ForeignKey('level.id'), nullable=False, index=True)
 213     to_level = Column(
 214         Integer, ForeignKey('level.id'), nullable=False, index=True)
 215
 216
 217 SET3_MODELS = [Creature3, CreaturePower3, Level3, LevelExit3]
 218
 219
 220 @RegisterMigration(4, FULL_MIGRATIONS)
 221 def creature_num_legs_to_num_limbs(db_conn):
 222     pass
 223
 224 @RegisterMigration(5, FULL_MIGRATIONS)
 225 def level_exit_index_from_and_to_level(db_conn):
 226     pass
 227
 228 @RegisterMigration(6, FULL_MIGRATIONS)
 229 def creature_power_index_creature(db_conn):
 230     pass
 231
 232 @RegisterMigration(7, FULL_MIGRATIONS)
 233 def creature_power_hitpower_to_float(db_conn):
 234     pass