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, Index
  21     Integer, Float, Unicode, UnicodeText, DateTime, Boolean,
  22     ForeignKey, UniqueConstraint, PickleType)
  23 from sqlalchemy.orm import sessionmaker, relationship
  24 from sqlalchemy.ext.declarative import declarative_base
  25 from sqlalchemy.sql import select, insert
  26 from migrate import changeset
  27
  28 from mediagoblin.db.sql.base import GMGTableBase
  29
  30
  31 # This one will get filled with local migrations
  32 FULL_MIGRATIONS = {}
  33
  34
  35 #######################################################
  36 # Migration set 1: Define initial models, no migrations
  37 #######################################################
  38
  39 Base1 = declarative_base(cls=GMGTableBase)
  40
  41 class Creature1(Base1):
  42     __tablename__ = "creature"
  43
  44     id = Column(Integer, primary_key=True)
  45     name = Column(Unicode, unique=True, nullable=False, index=True)
  46     num_legs = Column(Integer, nullable=False)
  47     is_demon = Column(Boolean)
  48
  49 class Level1(Base1):
  50     __tablename__ = "level"
  51
  52     id = Column(Unicode, primary_key=True)
  53     name = Column(Unicode, unique=True, nullable=False, index=True)
  54     description = Column(UnicodeText)
  55     exits = Column(PickleType)
  56
  57 SET1_MODELS = [Creature1, Level1]
  58
  59 SET1_MIGRATIONS = []
  60
  61 #######################################################
  62 # Migration set 2: A few migrations and new model
  63 #######################################################
  64
  65 Base2 = declarative_base(cls=GMGTableBase)
  66
  67 class Creature2(Base2):
  68     __tablename__ = "creature"
  69
  70     id = Column(Integer, primary_key=True)
  71     name = Column(Unicode, unique=True, nullable=False, index=True)
  72     num_legs = Column(Integer, nullable=False)
  73     magical_powers = relationship("CreaturePower2")
  74
  75 class CreaturePower2(Base2):
  76     __tablename__ = "creature_power"
  77
  78     id = Column(Integer, primary_key=True)
  79     creature = Column(
  80         Integer, ForeignKey('creature.id'), nullable=False)
  81     name = Column(Unicode)
  82     description = Column(Unicode)
  83     hitpower = Column(Integer, nullable=False)
  84
  85 class Level2(Base2):
  86     __tablename__ = "level"
  87
  88     id = Column(Unicode, primary_key=True)
  89     name = Column(Unicode)
  90     description = Column(UnicodeText)
  91
  92 class LevelExit2(Base2):
  93     __tablename__ = "level_exit"
  94
  95     id = Column(Integer, primary_key=True)
  96     name = Column(Unicode)
  97     from_level = Column(
  98         Unicode, ForeignKey('level.id'), nullable=False)
  99     to_level = Column(
 100         Unicode, ForeignKey('level.id'), nullable=False)
 101
 102 SET2_MODELS = [Creature2, CreaturePower2, Level2, LevelExit2]
 103
 104
 105 @RegisterMigration(1, FULL_MIGRATIONS)
 106 def creature_remove_is_demon(db_conn):
 107     metadata = MetaData(bind=db_conn.engine)
 108     creature_table = Table(
 109         'creature', metadata,
 110         autoload=True, autoload_with=db_conn.engine)
 111     creature_table.drop_column('is_demon')
 112
 113
 114 @RegisterMigration(2, FULL_MIGRATIONS)
 115 def creature_powers_new_table(db_conn):
 116     metadata = MetaData(bind=db_conn.engine)
 117     creature_powers = Table(
 118         'creature_power', metadata,
 119         Column('id', Integer, primary_key=True),
 120         Column('creature',
 121                Integer, ForeignKey('creature.id'), nullable=False),
 122         Column('name', Unicode),
 123         Column('description', Unicode),
 124         Column('hitpower', Integer, nullable=False))
 125     metadata.create_all(db_conn.engine)
 126
 127
 128 @RegisterMigration(3, FULL_MIGRATIONS)
 129 def level_exits_new_table(db_conn):
 130     # First, create the table
 131     # -----------------------
 132     metadata = MetaData(bind=db_conn.engine)
 133     level_exits = Table(
 134         'level_exit', metadata,
 135         Column('id', Integer, primary_key=True),
 136         Column('name', Unicode),
 137         Column('from_level',
 138                Integer, ForeignKey('level.id'), nullable=False),
 139         Column('to_level',
 140                Integer, ForeignKey('level.id'), nullable=False))
 141     metadata.create_all(db_conn.engine)
 142
 143     # And now, convert all the old exit pickles to new level exits
 144     # ------------------------------------------------------------
 145
 146     # Minimal representation of level table.
 147     # Not auto-introspecting here because of pickle table.  I'm not
 148     # sure sqlalchemy can auto-introspect pickle columns.
 149     levels = Table(
 150         'level', metadata,
 151         Column('id', Integer, primary_key=True),
 152         Column('exits', PickleType))
 153
 154     # query over and insert
 155     result = db_conn.execute(
 156         select([levels], levels.c.exits!=None))
 157
 158     for level in result:
 159         this_exit = level['exits']
 160
 161         # Insert the level exit
 162         db_conn.execute(
 163             level_exits.insert().values(
 164                 name=this_exit['name'],
 165                 from_level=this_exit['from_level'],
 166                 to_level=this_exit['to_level']))
 167
 168     # Finally, drop the old level exits pickle table
 169     # ----------------------------------------------
 170     levels.drop_column('exits')
 171
 172
 173 # A hack!  At this point we freeze-fame and get just a partial list of
 174 # migrations
 175
 176 SET2_MIGRATIONS = copy.copy(FULL_MIGRATIONS)
 177
 178 #######################################################
 179 # Migration set 3: Final migrations
 180 #######################################################
 181
 182 Base3 = declarative_base(cls=GMGTableBase)
 183
 184 class Creature3(Base3):
 185     __tablename__ = "creature"
 186
 187     id = Column(Integer, primary_key=True)
 188     name = Column(Unicode, unique=True, nullable=False, index=True)
 189     num_limbs= Column(Integer, nullable=False)
 190
 191 class CreaturePower3(Base3):
 192     __tablename__ = "creature_power"
 193
 194     id = Column(Integer, primary_key=True)
 195     creature = Column(
 196         Integer, ForeignKey('creature.id'), nullable=False, index=True)
 197     name = Column(Unicode)
 198     description = Column(Unicode)
 199     hitpower = Column(Float, nullable=False)
 200     magical_powers = relationship("CreaturePower3")
 201
 202 class Level3(Base3):
 203     __tablename__ = "level"
 204
 205     id = Column(Unicode, primary_key=True)
 206     name = Column(Unicode)
 207     description = Column(UnicodeText)
 208
 209 class LevelExit3(Base3):
 210     __tablename__ = "level_exit"
 211
 212     id = Column(Integer, primary_key=True)
 213     name = Column(Unicode)
 214     from_level = Column(
 215         Unicode, ForeignKey('level.id'), nullable=False, index=True)
 216     to_level = Column(
 217         Unicode, ForeignKey('level.id'), nullable=False, index=True)
 218
 219
 220 SET3_MODELS = [Creature3, CreaturePower3, Level3, LevelExit3]
 221
 222
 223 @RegisterMigration(4, FULL_MIGRATIONS)
 224 def creature_num_legs_to_num_limbs(db_conn):
 225     metadata = MetaData(bind=db_conn.engine)
 226     creature_table = Table(
 227         'creature', metadata,
 228         autoload=True, autoload_with=db_conn.engine)
 229     creature_table.c.num_legs.alter(name="num_limbs")
 230
 231
 232 @RegisterMigration(5, FULL_MIGRATIONS)
 233 def level_exit_index_from_and_to_level(db_conn):
 234     metadata = MetaData(bind=db_conn.engine)
 235     level_exit = Table(
 236         'level_exit', metadata,
 237         autoload=True, autoload_with=db_conn.engine)
 238     Index('ix_from_level', level_exit.c.from_level).create(engine)
 239     Index('ix_to_exit', level_exit.c.to_exit).create(engine)
 240
 241
 242 @RegisterMigration(6, FULL_MIGRATIONS)
 243 def creature_power_index_creature(db_conn):
 244     metadata = MetaData(bind=db_conn.engine)
 245     creature_power = Table(
 246         'creature_power', metadata,
 247         autoload=True, autoload_with=db_conn.engine)
 248     Index('ix_creature', creature_power.c.creature).create(engine)
 249
 250
 251 @RegisterMigration(7, FULL_MIGRATIONS)
 252 def creature_power_hitpower_to_float(db_conn):
 253     metadata = MetaData(bind=db_conn.engine)
 254     creature_power = Table(
 255         'creature_power', metadata,
 256         autoload=True, autoload_with=db_conn.engine)
 257     creature_power.c.hitpower.alter(type=Float)
 258
 259
 260 def _insert_migration1_objects(session):
 261     # Insert creatures
 262     session.add_all(
 263         [Creature1(name='centipede',
 264                    num_legs=100,
 265                    is_demon=False),
 266          Creature1(name='wolf',
 267                    num_legs=4,
 268                    is_demon=False),
 269          # don't ask me what a wizardsnake is.
 270          Creature1(name='wizardsnake',
 271                    num_legs=0,
 272                    is_demon=True)])
 273
 274     # Insert levels
 275     session.add_all(
 276         [Level1(id='necroplex',
 277                 name='The Necroplex',
 278                 description='A complex full of pure deathzone.',
 279                 exits={
 280                     'deathwell': 'evilstorm',
 281                     'portal': 'central_park'}),
 282          Level1(id='evilstorm',
 283                 name='Evil Storm',
 284                 description='A storm full of pure evil.',
 285                 exits={}), # you can't escape the evilstorm
 286          Level1(id='central_park'
 287                 name='Central Park, NY, NY',
 288                 description="New York's friendly Central Park.",
 289                 exits={
 290                     'portal': 'necroplex'})])
 291
 292     session.commit()
 293
 294
 295 def _insert_migration2_objects(session):
 296     # Insert creatures
 297     session.add_all(
 298         [Creature2(
 299                 name='centipede',
 300                 num_legs=100),
 301          Creature2(
 302                 name='wolf',
 303                 num_legs=4,
 304                 magical_powers = [
 305                     CreaturePower2(
 306                         name="ice breath",
 307                         description="A blast of icy breath!",
 308                         hitpower=20),
 309                     CreaturePower2(
 310                         name="death stare",
 311                         description="A frightening stare, for sure!",
 312                         hitpower=45)]),
 313          Creature2(
 314                 name='wizardsnake',
 315                 num_legs=0,
 316                 magical_powers=[
 317                     CreaturePower2(
 318                         name='death_rattle',
 319                         description='A rattle... of DEATH!',
 320                         hitpower=1000),
 321                     CreaturePower2(
 322                         name='sneaky_stare',
 323                         description="The sneakiest stare you've ever seen!"
 324                         hitpower=300),
 325                     CreaturePower2(
 326                         name='slithery_smoke',
 327                         description="A blast of slithery, slithery smoke.",
 328                         hitpower=10),
 329                     CreaturePower2(
 330                         name='treacherous_tremors',
 331                         description="The ground shakes beneath footed animals!",
 332                         hitpower=0)])])
 333
 334     # Insert levels
 335     session.add_all(
 336         [Level2(id='necroplex',
 337                 name='The Necroplex',
 338                 description='A complex full of pure deathzone.'),
 339          Level2(id='evilstorm',
 340                 name='Evil Storm',
 341                 description='A storm full of pure evil.',
 342                 exits=[]), # you can't escape the evilstorm
 343          Level2(id='central_park'
 344                 name='Central Park, NY, NY',
 345                 description="New York's friendly Central Park.")])
 346
 347     # necroplex exits
 348     session.add_all(
 349         [LevelExit2(name='deathwell',
 350                     from_level='necroplex',
 351                     to_level='evilstorm'),
 352          LevelExit2(name='portal',
 353                     from_level='necroplex',
 354                     to_level='central_park')])
 355
 356     # there are no evilstorm exits because there is no exit from the
 357     # evilstorm
 358
 359     # central park exits
 360     session.add_all(
 361         [LevelExit2(name='portal',
 362                     from_level='central_park',
 363                     to_level='necroplex')]
 364
 365     session.commit()