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     """
 239     Turns out we're tracking all sorts of limbs, not "legs"
 240     specifically.  Humans would be 4 here, for instance.  So we
 241     renamed the column.
 242     """
 243     metadata = MetaData(bind=db_conn.engine)
 244     creature_table = Table(
 245         'creature', metadata,
 246         autoload=True, autoload_with=db_conn.engine)
 247     creature_table.c.num_legs.alter(name="num_limbs")
 248
 249
 250 @RegisterMigration(5, FULL_MIGRATIONS)
 251 def level_exit_index_from_and_to_level(db_conn):
 252     """
 253     Index the from and to levels of the level exit table.
 254     """
 255     metadata = MetaData(bind=db_conn.engine)
 256     level_exit = Table(
 257         'level_exit', metadata,
 258         autoload=True, autoload_with=db_conn.engine)
 259     Index('ix_from_level', level_exit.c.from_level).create(engine)
 260     Index('ix_to_exit', level_exit.c.to_exit).create(engine)
 261
 262
 263 @RegisterMigration(6, FULL_MIGRATIONS)
 264 def creature_power_index_creature(db_conn):
 265     """
 266     Index our foreign key relationship to the creatures
 267     """
 268     metadata = MetaData(bind=db_conn.engine)
 269     creature_power = Table(
 270         'creature_power', metadata,
 271         autoload=True, autoload_with=db_conn.engine)
 272     Index('ix_creature', creature_power.c.creature).create(engine)
 273
 274
 275 @RegisterMigration(7, FULL_MIGRATIONS)
 276 def creature_power_hitpower_to_float(db_conn):
 277     """
 278     Convert hitpower column on creature power table from integer to
 279     float.
 280
 281     Turns out we want super precise values of how much hitpower there
 282     really is.
 283     """
 284     metadata = MetaData(bind=db_conn.engine)
 285     creature_power = Table(
 286         'creature_power', metadata,
 287         autoload=True, autoload_with=db_conn.engine)
 288     creature_power.c.hitpower.alter(type=Float)
 289
 290
 291 def _insert_migration1_objects(session):
 292     """
 293     Test objects to insert for the first set of things
 294     """
 295     # Insert creatures
 296     session.add_all(
 297         [Creature1(name='centipede',
 298                    num_legs=100,
 299                    is_demon=False),
 300          Creature1(name='wolf',
 301                    num_legs=4,
 302                    is_demon=False),
 303          # don't ask me what a wizardsnake is.
 304          Creature1(name='wizardsnake',
 305                    num_legs=0,
 306                    is_demon=True)])
 307
 308     # Insert levels
 309     session.add_all(
 310         [Level1(id='necroplex',
 311                 name='The Necroplex',
 312                 description='A complex full of pure deathzone.',
 313                 exits={
 314                     'deathwell': 'evilstorm',
 315                     'portal': 'central_park'}),
 316          Level1(id='evilstorm',
 317                 name='Evil Storm',
 318                 description='A storm full of pure evil.',
 319                 exits={}), # you can't escape the evilstorm
 320          Level1(id='central_park'
 321                 name='Central Park, NY, NY',
 322                 description="New York's friendly Central Park.",
 323                 exits={
 324                     'portal': 'necroplex'})])
 325
 326     session.commit()
 327
 328
 329 def _insert_migration2_objects(session):
 330     """
 331     Test objects to insert for the second set of things
 332     """
 333     # Insert creatures
 334     session.add_all(
 335         [Creature2(
 336                 name='centipede',
 337                 num_legs=100),
 338          Creature2(
 339                 name='wolf',
 340                 num_legs=4,
 341                 magical_powers = [
 342                     CreaturePower2(
 343                         name="ice breath",
 344                         description="A blast of icy breath!",
 345                         hitpower=20),
 346                     CreaturePower2(
 347                         name="death stare",
 348                         description="A frightening stare, for sure!",
 349                         hitpower=45)]),
 350          Creature2(
 351                 name='wizardsnake',
 352                 num_legs=0,
 353                 magical_powers=[
 354                     CreaturePower2(
 355                         name='death_rattle',
 356                         description='A rattle... of DEATH!',
 357                         hitpower=1000),
 358                     CreaturePower2(
 359                         name='sneaky_stare',
 360                         description="The sneakiest stare you've ever seen!"
 361                         hitpower=300),
 362                     CreaturePower2(
 363                         name='slithery_smoke',
 364                         description="A blast of slithery, slithery smoke.",
 365                         hitpower=10),
 366                     CreaturePower2(
 367                         name='treacherous_tremors',
 368                         description="The ground shakes beneath footed animals!",
 369                         hitpower=0)])])
 370
 371     # Insert levels
 372     session.add_all(
 373         [Level2(id='necroplex',
 374                 name='The Necroplex',
 375                 description='A complex full of pure deathzone.'),
 376          Level2(id='evilstorm',
 377                 name='Evil Storm',
 378                 description='A storm full of pure evil.',
 379                 exits=[]), # you can't escape the evilstorm
 380          Level2(id='central_park'
 381                 name='Central Park, NY, NY',
 382                 description="New York's friendly Central Park.")])
 383
 384     # necroplex exits
 385     session.add_all(
 386         [LevelExit2(name='deathwell',
 387                     from_level='necroplex',
 388                     to_level='evilstorm'),
 389          LevelExit2(name='portal',
 390                     from_level='necroplex',
 391                     to_level='central_park')])
 392
 393     # there are no evilstorm exits because there is no exit from the
 394     # evilstorm
 395
 396     # central park exits
 397     session.add_all(
 398         [LevelExit2(name='portal',
 399                     from_level='central_park',
 400                     to_level='necroplex')])
 401
 402     session.commit()
 403
 404
 405 def _insert_migration3_objects(session):
 406     """
 407     Test objects to insert for the third set of things
 408     """
 409     # Insert creatures
 410     session.add_all(
 411         [Creature3(
 412                 name='centipede',
 413                 num_limbs=100),
 414          Creature3(
 415                 name='wolf',
 416                 num_limbs=4,
 417                 magical_powers = [
 418                     CreaturePower3(
 419                         name="ice breath",
 420                         description="A blast of icy breath!",
 421                         hitpower=20.0),
 422                     CreaturePower3(
 423                         name="death stare",
 424                         description="A frightening stare, for sure!",
 425                         hitpower=45.0)]),
 426          Creature3(
 427                 name='wizardsnake',
 428                 num_limbs=0,
 429                 magical_powers=[
 430                     CreaturePower3(
 431                         name='death_rattle',
 432                         description='A rattle... of DEATH!',
 433                         hitpower=1000.0),
 434                     CreaturePower3(
 435                         name='sneaky_stare',
 436                         description="The sneakiest stare you've ever seen!"
 437                         hitpower=300.0),
 438                     CreaturePower3(
 439                         name='slithery_smoke',
 440                         description="A blast of slithery, slithery smoke.",
 441                         hitpower=10.0),
 442                     CreaturePower3(
 443                         name='treacherous_tremors',
 444                         description="The ground shakes beneath footed animals!",
 445                         hitpower=0.0)])],
 446         # annnnnd one more to test a floating point hitpower
 447         Creature3(
 448                 name='deity',
 449                 numb_limbs=30,
 450                 magical_powers[
 451                     CreaturePower3(
 452                         name='smite',
 453                         description='Smitten by holy wrath!',
 454                         hitpower=9999.9))))
 455
 456     # Insert levels
 457     session.add_all(
 458         [Level3(id='necroplex',
 459                 name='The Necroplex',
 460                 description='A complex full of pure deathzone.'),
 461          Level3(id='evilstorm',
 462                 name='Evil Storm',
 463                 description='A storm full of pure evil.',
 464                 exits=[]), # you can't escape the evilstorm
 465          Level3(id='central_park'
 466                 name='Central Park, NY, NY',
 467                 description="New York's friendly Central Park.")])
 468
 469     # necroplex exits
 470     session.add_all(
 471         [LevelExit3(name='deathwell',
 472                     from_level='necroplex',
 473                     to_level='evilstorm'),
 474          LevelExit3(name='portal',
 475                     from_level='necroplex',
 476                     to_level='central_park')])
 477
 478     # there are no evilstorm exits because there is no exit from the
 479     # evilstorm
 480
 481     # central park exits
 482     session.add_all(
 483         [LevelExit3(name='portal',
 484                     from_level='central_park',
 485                     to_level='necroplex')])
 486
 487     session.commit()
 488
 489
 490 def create_test_engine():
 491     from sqlalchemy import create_engine
 492     engine = create_engine('sqlite:///:memory:', echo=False)
 493     return engine
 494
 495
 496 def test_set1_to_set3():
 497     # Create / connect to database
 498     # Create tables by migrating on empty initial set
 499
 500     # Install the initial set
 501     # Check version in database
 502     # Sanity check a few things in the database
 503
 504     # Migrate
 505     # Make sure version matches expected
 506     # Check all things in database match expected
 507     pass
 508
 509
 510 def test_set2_to_set3():
 511     # Create / connect to database
 512     # Create tables by migrating on empty initial set
 513
 514     # Install the initial set
 515     # Check version in database
 516     # Sanity check a few things in the database
 517
 518     # Migrate
 519     # Make sure version matches expected
 520     # Check all things in database match expected
 521     pass
 522
 523
 524 def test_set1_to_set2_to_set3():
 525     # Create / connect to database
 526     # Create tables by migrating on empty initial set
 527
 528     # Install the initial set
 529     # Check version in database
 530     # Sanity check a few things in the database
 531
 532     # Migrate
 533     # Make sure version matches expected
 534     # Check all things in database match expected
 535
 536     # Migrate again
 537     # Make sure version matches expected again
 538     # Check all things in database match expected again
 539     pass