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     """
 108     Remove the is_demon field from the creature model.  We don't need
 109     it!
 110     """
 111     metadata = MetaData(bind=db_conn.engine)
 112     creature_table = Table(
 113         'creature', metadata,
 114         autoload=True, autoload_with=db_conn.engine)
 115     creature_table.drop_column('is_demon')
 116
 117
 118 @RegisterMigration(2, FULL_MIGRATIONS)
 119 def creature_powers_new_table(db_conn):
 120     """
 121     Add a new table for creature powers.  Nothing needs to go in it
 122     yet though as there wasn't anything that previously held this
 123     information
 124     """
 125     metadata = MetaData(bind=db_conn.engine)
 126     creature_powers = Table(
 127         'creature_power', metadata,
 128         Column('id', Integer, primary_key=True),
 129         Column('creature',
 130                Integer, ForeignKey('creature.id'), nullable=False),
 131         Column('name', Unicode),
 132         Column('description', Unicode),
 133         Column('hitpower', Integer, nullable=False))
 134     metadata.create_all(db_conn.engine)
 135
 136
 137 @RegisterMigration(3, FULL_MIGRATIONS)
 138 def level_exits_new_table(db_conn):
 139     """
 140     Make a new table for level exits and move the previously pickled
 141     stuff over to here (then drop the old unneeded table)
 142     """
 143     # First, create the table
 144     # -----------------------
 145     metadata = MetaData(bind=db_conn.engine)
 146     level_exits = Table(
 147         'level_exit', metadata,
 148         Column('id', Integer, primary_key=True),
 149         Column('name', Unicode),
 150         Column('from_level',
 151                Integer, ForeignKey('level.id'), nullable=False),
 152         Column('to_level',
 153                Integer, ForeignKey('level.id'), nullable=False))
 154     metadata.create_all(db_conn.engine)
 155
 156     # And now, convert all the old exit pickles to new level exits
 157     # ------------------------------------------------------------
 158
 159     # Minimal representation of level table.
 160     # Not auto-introspecting here because of pickle table.  I'm not
 161     # sure sqlalchemy can auto-introspect pickle columns.
 162     levels = Table(
 163         'level', metadata,
 164         Column('id', Integer, primary_key=True),
 165         Column('exits', PickleType))
 166
 167     # query over and insert
 168     result = db_conn.execute(
 169         select([levels], levels.c.exits!=None))
 170
 171     for level in result:
 172         this_exit = level['exits']
 173
 174         # Insert the level exit
 175         db_conn.execute(
 176             level_exits.insert().values(
 177                 name=this_exit['name'],
 178                 from_level=this_exit['from_level'],
 179                 to_level=this_exit['to_level']))
 180
 181     # Finally, drop the old level exits pickle table
 182     # ----------------------------------------------
 183     levels.drop_column('exits')
 184
 185
 186 # A hack!  At this point we freeze-fame and get just a partial list of
 187 # migrations
 188
 189 SET2_MIGRATIONS = copy.copy(FULL_MIGRATIONS)
 190
 191 #######################################################
 192 # Migration set 3: Final migrations
 193 #######################################################
 194
 195 Base3 = declarative_base(cls=GMGTableBase)
 196
 197 class Creature3(Base3):
 198     __tablename__ = "creature"
 199
 200     id = Column(Integer, primary_key=True)
 201     name = Column(Unicode, unique=True, nullable=False, index=True)
 202     num_limbs= Column(Integer, nullable=False)
 203
 204 class CreaturePower3(Base3):
 205     __tablename__ = "creature_power"
 206
 207     id = Column(Integer, primary_key=True)
 208     creature = Column(
 209         Integer, ForeignKey('creature.id'), nullable=False, index=True)
 210     name = Column(Unicode)
 211     description = Column(Unicode)
 212     hitpower = Column(Float, nullable=False)
 213     magical_powers = relationship("CreaturePower3")
 214
 215 class Level3(Base3):
 216     __tablename__ = "level"
 217
 218     id = Column(Unicode, primary_key=True)
 219     name = Column(Unicode)
 220     description = Column(UnicodeText)
 221
 222 class LevelExit3(Base3):
 223     __tablename__ = "level_exit"
 224
 225     id = Column(Integer, primary_key=True)
 226     name = Column(Unicode)
 227     from_level = Column(
 228         Unicode, ForeignKey('level.id'), nullable=False, index=True)
 229     to_level = Column(
 230         Unicode, ForeignKey('level.id'), nullable=False, index=True)
 231
 232
 233 SET3_MODELS = [Creature3, CreaturePower3, Level3, LevelExit3]
 234
 235
 236 @RegisterMigration(4, FULL_MIGRATIONS)
 237 def creature_num_legs_to_num_limbs(db_conn):
 238     metadata = MetaData(bind=db_conn.engine)
 239     creature_table = Table(
 240         'creature', metadata,
 241         autoload=True, autoload_with=db_conn.engine)
 242     creature_table.c.num_legs.alter(name="num_limbs")
 243
 244
 245 @RegisterMigration(5, FULL_MIGRATIONS)
 246 def level_exit_index_from_and_to_level(db_conn):
 247     metadata = MetaData(bind=db_conn.engine)
 248     level_exit = Table(
 249         'level_exit', metadata,
 250         autoload=True, autoload_with=db_conn.engine)
 251     Index('ix_from_level', level_exit.c.from_level).create(engine)
 252     Index('ix_to_exit', level_exit.c.to_exit).create(engine)
 253
 254
 255 @RegisterMigration(6, FULL_MIGRATIONS)
 256 def creature_power_index_creature(db_conn):
 257     metadata = MetaData(bind=db_conn.engine)
 258     creature_power = Table(
 259         'creature_power', metadata,
 260         autoload=True, autoload_with=db_conn.engine)
 261     Index('ix_creature', creature_power.c.creature).create(engine)
 262
 263
 264 @RegisterMigration(7, FULL_MIGRATIONS)
 265 def creature_power_hitpower_to_float(db_conn):
 266     metadata = MetaData(bind=db_conn.engine)
 267     creature_power = Table(
 268         'creature_power', metadata,
 269         autoload=True, autoload_with=db_conn.engine)
 270     creature_power.c.hitpower.alter(type=Float)
 271
 272
 273 def _insert_migration1_objects(session):
 274     """
 275     Test objects to insert for the first set of things
 276     """
 277     # Insert creatures
 278     session.add_all(
 279         [Creature1(name='centipede',
 280                    num_legs=100,
 281                    is_demon=False),
 282          Creature1(name='wolf',
 283                    num_legs=4,
 284                    is_demon=False),
 285          # don't ask me what a wizardsnake is.
 286          Creature1(name='wizardsnake',
 287                    num_legs=0,
 288                    is_demon=True)])
 289
 290     # Insert levels
 291     session.add_all(
 292         [Level1(id='necroplex',
 293                 name='The Necroplex',
 294                 description='A complex full of pure deathzone.',
 295                 exits={
 296                     'deathwell': 'evilstorm',
 297                     'portal': 'central_park'}),
 298          Level1(id='evilstorm',
 299                 name='Evil Storm',
 300                 description='A storm full of pure evil.',
 301                 exits={}), # you can't escape the evilstorm
 302          Level1(id='central_park'
 303                 name='Central Park, NY, NY',
 304                 description="New York's friendly Central Park.",
 305                 exits={
 306                     'portal': 'necroplex'})])
 307
 308     session.commit()
 309
 310
 311 def _insert_migration2_objects(session):
 312     """
 313     Test objects to insert for the second set of things
 314     """
 315     # Insert creatures
 316     session.add_all(
 317         [Creature2(
 318                 name='centipede',
 319                 num_legs=100),
 320          Creature2(
 321                 name='wolf',
 322                 num_legs=4,
 323                 magical_powers = [
 324                     CreaturePower2(
 325                         name="ice breath",
 326                         description="A blast of icy breath!",
 327                         hitpower=20),
 328                     CreaturePower2(
 329                         name="death stare",
 330                         description="A frightening stare, for sure!",
 331                         hitpower=45)]),
 332          Creature2(
 333                 name='wizardsnake',
 334                 num_legs=0,
 335                 magical_powers=[
 336                     CreaturePower2(
 337                         name='death_rattle',
 338                         description='A rattle... of DEATH!',
 339                         hitpower=1000),
 340                     CreaturePower2(
 341                         name='sneaky_stare',
 342                         description="The sneakiest stare you've ever seen!"
 343                         hitpower=300),
 344                     CreaturePower2(
 345                         name='slithery_smoke',
 346                         description="A blast of slithery, slithery smoke.",
 347                         hitpower=10),
 348                     CreaturePower2(
 349                         name='treacherous_tremors',
 350                         description="The ground shakes beneath footed animals!",
 351                         hitpower=0)])])
 352
 353     # Insert levels
 354     session.add_all(
 355         [Level2(id='necroplex',
 356                 name='The Necroplex',
 357                 description='A complex full of pure deathzone.'),
 358          Level2(id='evilstorm',
 359                 name='Evil Storm',
 360                 description='A storm full of pure evil.',
 361                 exits=[]), # you can't escape the evilstorm
 362          Level2(id='central_park'
 363                 name='Central Park, NY, NY',
 364                 description="New York's friendly Central Park.")])
 365
 366     # necroplex exits
 367     session.add_all(
 368         [LevelExit2(name='deathwell',
 369                     from_level='necroplex',
 370                     to_level='evilstorm'),
 371          LevelExit2(name='portal',
 372                     from_level='necroplex',
 373                     to_level='central_park')])
 374
 375     # there are no evilstorm exits because there is no exit from the
 376     # evilstorm
 377
 378     # central park exits
 379     session.add_all(
 380         [LevelExit2(name='portal',
 381                     from_level='central_park',
 382                     to_level='necroplex')]
 383
 384     session.commit()