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 from mediagoblin.db.sql.util import MigrationManager, RegisterMigration
  30
  31
  32 # This one will get filled with local migrations
  33 FULL_MIGRATIONS = {}
  34
  35
  36 #######################################################
  37 # Migration set 1: Define initial models, no migrations
  38 #######################################################
  39
  40 Base1 = declarative_base(cls=GMGTableBase)
  41
  42 class Creature1(Base1):
  43     __tablename__ = "creature"
  44
  45     id = Column(Integer, primary_key=True)
  46     name = Column(Unicode, unique=True, nullable=False, index=True)
  47     num_legs = Column(Integer, nullable=False)
  48     is_demon = Column(Boolean)
  49
  50 class Level1(Base1):
  51     __tablename__ = "level"
  52
  53     id = Column(Unicode, primary_key=True)
  54     name = Column(Unicode)
  55     description = Column(Unicode)
  56     exits = Column(PickleType)
  57
  58 SET1_MODELS = [Creature1, Level1]
  59
  60 SET1_MIGRATIONS = {}
  61
  62 #######################################################
  63 # Migration set 2: A few migrations and new model
  64 #######################################################
  65
  66 Base2 = declarative_base(cls=GMGTableBase)
  67
  68 class Creature2(Base2):
  69     __tablename__ = "creature"
  70
  71     id = Column(Integer, primary_key=True)
  72     name = Column(Unicode, unique=True, nullable=False, index=True)
  73     num_legs = Column(Integer, nullable=False)
  74     magical_powers = relationship("CreaturePower2")
  75
  76 class CreaturePower2(Base2):
  77     __tablename__ = "creature_power"
  78
  79     id = Column(Integer, primary_key=True)
  80     creature = Column(
  81         Integer, ForeignKey('creature.id'), nullable=False)
  82     name = Column(Unicode)
  83     description = Column(Unicode)
  84     hitpower = Column(Integer, nullable=False)
  85
  86 class Level2(Base2):
  87     __tablename__ = "level"
  88
  89     id = Column(Unicode, primary_key=True)
  90     name = Column(Unicode)
  91     description = Column(Unicode)
  92
  93 class LevelExit2(Base2):
  94     __tablename__ = "level_exit"
  95
  96     id = Column(Integer, primary_key=True)
  97     name = Column(Unicode)
  98     from_level = Column(
  99         Unicode, ForeignKey('level.id'), nullable=False)
 100     to_level = Column(
 101         Unicode, ForeignKey('level.id'), nullable=False)
 102
 103 SET2_MODELS = [Creature2, CreaturePower2, Level2, LevelExit2]
 104
 105
 106 @RegisterMigration(1, FULL_MIGRATIONS)
 107 def creature_remove_is_demon(db_conn):
 108     """
 109     Remove the is_demon field from the creature model.  We don't need
 110     it!
 111     """
 112     metadata = MetaData(bind=db_conn.engine)
 113     creature_table = Table(
 114         'creature', metadata,
 115         autoload=True, autoload_with=db_conn.engine)
 116     creature_table.drop_column('is_demon')
 117
 118
 119 @RegisterMigration(2, FULL_MIGRATIONS)
 120 def creature_powers_new_table(db_conn):
 121     """
 122     Add a new table for creature powers.  Nothing needs to go in it
 123     yet though as there wasn't anything that previously held this
 124     information
 125     """
 126     metadata = MetaData(bind=db_conn.engine)
 127     creature_powers = Table(
 128         'creature_power', metadata,
 129         Column('id', Integer, primary_key=True),
 130         Column('creature',
 131                Integer, ForeignKey('creature.id'), nullable=False),
 132         Column('name', Unicode),
 133         Column('description', Unicode),
 134         Column('hitpower', Integer, nullable=False))
 135     metadata.create_all(db_conn.engine)
 136
 137
 138 @RegisterMigration(3, FULL_MIGRATIONS)
 139 def level_exits_new_table(db_conn):
 140     """
 141     Make a new table for level exits and move the previously pickled
 142     stuff over to here (then drop the old unneeded table)
 143     """
 144     # First, create the table
 145     # -----------------------
 146     metadata = MetaData(bind=db_conn.engine)
 147     level_exits = Table(
 148         'level_exit', metadata,
 149         Column('id', Integer, primary_key=True),
 150         Column('name', Unicode),
 151         Column('from_level',
 152                Integer, ForeignKey('level.id'), nullable=False),
 153         Column('to_level',
 154                Integer, ForeignKey('level.id'), nullable=False))
 155     metadata.create_all(db_conn.engine)
 156
 157     # And now, convert all the old exit pickles to new level exits
 158     # ------------------------------------------------------------
 159
 160     # Minimal representation of level table.
 161     # Not auto-introspecting here because of pickle table.  I'm not
 162     # sure sqlalchemy can auto-introspect pickle columns.
 163     levels = Table(
 164         'level', metadata,
 165         Column('id', Integer, primary_key=True),
 166         Column('exits', PickleType))
 167
 168     # query over and insert
 169     result = db_conn.execute(
 170         select([levels], levels.c.exits!=None))
 171
 172     for level in result:
 173         this_exit = level['exits']
 174
 175         # Insert the level exit
 176         db_conn.execute(
 177             level_exits.insert().values(
 178                 name=this_exit['name'],
 179                 from_level=this_exit['from_level'],
 180                 to_level=this_exit['to_level']))
 181
 182     # Finally, drop the old level exits pickle table
 183     # ----------------------------------------------
 184     levels.drop_column('exits')
 185
 186
 187 # A hack!  At this point we freeze-fame and get just a partial list of
 188 # migrations
 189
 190 SET2_MIGRATIONS = copy.copy(FULL_MIGRATIONS)
 191
 192 #######################################################
 193 # Migration set 3: Final migrations
 194 #######################################################
 195
 196 Base3 = declarative_base(cls=GMGTableBase)
 197
 198 class Creature3(Base3):
 199     __tablename__ = "creature"
 200
 201     id = Column(Integer, primary_key=True)
 202     name = Column(Unicode, unique=True, nullable=False, index=True)
 203     num_limbs= Column(Integer, nullable=False)
 204     magical_powers = relationship("CreaturePower3")
 205
 206 class CreaturePower3(Base3):
 207     __tablename__ = "creature_power"
 208
 209     id = Column(Integer, primary_key=True)
 210     creature = Column(
 211         Integer, ForeignKey('creature.id'), nullable=False, index=True)
 212     name = Column(Unicode)
 213     description = Column(Unicode)
 214     hitpower = Column(Float, nullable=False)
 215
 216 class Level3(Base3):
 217     __tablename__ = "level"
 218
 219     id = Column(Unicode, primary_key=True)
 220     name = Column(Unicode)
 221     description = Column(Unicode)
 222
 223 class LevelExit3(Base3):
 224     __tablename__ = "level_exit"
 225
 226     id = Column(Integer, primary_key=True)
 227     name = Column(Unicode)
 228     from_level = Column(
 229         Unicode, ForeignKey('level.id'), nullable=False, index=True)
 230     to_level = Column(
 231         Unicode, ForeignKey('level.id'), nullable=False, index=True)
 232
 233
 234 SET3_MODELS = [Creature3, CreaturePower3, Level3, LevelExit3]
 235 SET3_MIGRATIONS = FULL_MIGRATIONS
 236
 237
 238 @RegisterMigration(4, FULL_MIGRATIONS)
 239 def creature_num_legs_to_num_limbs(db_conn):
 240     """
 241     Turns out we're tracking all sorts of limbs, not "legs"
 242     specifically.  Humans would be 4 here, for instance.  So we
 243     renamed the column.
 244     """
 245     metadata = MetaData(bind=db_conn.engine)
 246     creature_table = Table(
 247         'creature', metadata,
 248         autoload=True, autoload_with=db_conn.engine)
 249     creature_table.c.num_legs.alter(name=u"num_limbs")
 250
 251
 252 @RegisterMigration(5, FULL_MIGRATIONS)
 253 def level_exit_index_from_and_to_level(db_conn):
 254     """
 255     Index the from and to levels of the level exit table.
 256     """
 257     metadata = MetaData(bind=db_conn.engine)
 258     level_exit = Table(
 259         'level_exit', metadata,
 260         autoload=True, autoload_with=db_conn.engine)
 261     Index('ix_from_level', level_exit.c.from_level).create(db_conn.engine)
 262     Index('ix_to_exit', level_exit.c.to_exit).create(db_conn.engine)
 263
 264
 265 @RegisterMigration(6, FULL_MIGRATIONS)
 266 def creature_power_index_creature(db_conn):
 267     """
 268     Index our foreign key relationship to the creatures
 269     """
 270     metadata = MetaData(bind=db_conn.engine)
 271     creature_power = Table(
 272         'creature_power', metadata,
 273         autoload=True, autoload_with=db_conn.engine)
 274     Index('ix_creature', creature_power.c.creature).create(db_conn.engine)
 275
 276
 277 @RegisterMigration(7, FULL_MIGRATIONS)
 278 def creature_power_hitpower_to_float(db_conn):
 279     """
 280     Convert hitpower column on creature power table from integer to
 281     float.
 282
 283     Turns out we want super precise values of how much hitpower there
 284     really is.
 285     """
 286     metadata = MetaData(bind=db_conn.engine)
 287     creature_power = Table(
 288         'creature_power', metadata,
 289         autoload=True, autoload_with=db_conn.engine)
 290     creature_power.c.hitpower.alter(type=Float)
 291
 292
 293 def _insert_migration1_objects(session):
 294     """
 295     Test objects to insert for the first set of things
 296     """
 297     # Insert creatures
 298     session.add_all(
 299         [Creature1(name=u'centipede',
 300                    num_legs=100,
 301                    is_demon=False),
 302          Creature1(name=u'wolf',
 303                    num_legs=4,
 304                    is_demon=False),
 305          # don't ask me what a wizardsnake is.
 306          Creature1(name=u'wizardsnake',
 307                    num_legs=0,
 308                    is_demon=True)])
 309
 310     # Insert levels
 311     session.add_all(
 312         [Level1(id=u'necroplex',
 313                 name=u'The Necroplex',
 314                 description=u'A complex full of pure deathzone.',
 315                 exits={
 316                     'deathwell': 'evilstorm',
 317                     'portal': 'central_park'}),
 318          Level1(id=u'evilstorm',
 319                 name=u'Evil Storm',
 320                 description=u'A storm full of pure evil.',
 321                 exits={}), # you can't escape the evilstorm
 322          Level1(id=u'central_park',
 323                 name=u'Central Park, NY, NY',
 324                 description=u"New York's friendly Central Park.",
 325                 exits={
 326                     'portal': 'necroplex'})])
 327
 328     session.commit()
 329
 330
 331 def _insert_migration2_objects(session):
 332     """
 333     Test objects to insert for the second set of things
 334     """
 335     # Insert creatures
 336     session.add_all(
 337         [Creature2(
 338                 name=u'centipede',
 339                 num_legs=100),
 340          Creature2(
 341                 name=u'wolf',
 342                 num_legs=4,
 343                 magical_powers = [
 344                     CreaturePower2(
 345                         name=u"ice breath",
 346                         description=u"A blast of icy breath!",
 347                         hitpower=20),
 348                     CreaturePower2(
 349                         name=u"death stare",
 350                         description=u"A frightening stare, for sure!",
 351                         hitpower=45)]),
 352          Creature2(
 353                 name=u'wizardsnake',
 354                 num_legs=0,
 355                 magical_powers=[
 356                     CreaturePower2(
 357                         name=u'death_rattle',
 358                         description=u'A rattle... of DEATH!',
 359                         hitpower=1000),
 360                     CreaturePower2(
 361                         name=u'sneaky_stare',
 362                         description=u"The sneakiest stare you've ever seen!",
 363                         hitpower=300),
 364                     CreaturePower2(
 365                         name=u'slithery_smoke',
 366                         description=u"A blast of slithery, slithery smoke.",
 367                         hitpower=10),
 368                     CreaturePower2(
 369                         name=u'treacherous_tremors',
 370                         description=u"The ground shakes beneath footed animals!",
 371                         hitpower=0)])])
 372
 373     # Insert levels
 374     session.add_all(
 375         [Level2(id=u'necroplex',
 376                 name=u'The Necroplex',
 377                 description=u'A complex full of pure deathzone.'),
 378          Level2(id=u'evilstorm',
 379                 name=u'Evil Storm',
 380                 description=u'A storm full of pure evil.',
 381                 exits=[]), # you can't escape the evilstorm
 382          Level2(id=u'central_park',
 383                 name=u'Central Park, NY, NY',
 384                 description=u"New York's friendly Central Park.")])
 385
 386     # necroplex exits
 387     session.add_all(
 388         [LevelExit2(name=u'deathwell',
 389                     from_level=u'necroplex',
 390                     to_level=u'evilstorm'),
 391          LevelExit2(name=u'portal',
 392                     from_level=u'necroplex',
 393                     to_level=u'central_park')])
 394
 395     # there are no evilstorm exits because there is no exit from the
 396     # evilstorm
 397
 398     # central park exits
 399     session.add_all(
 400         [LevelExit2(name=u'portal',
 401                     from_level=u'central_park',
 402                     to_level=u'necroplex')])
 403
 404     session.commit()
 405
 406
 407 def _insert_migration3_objects(session):
 408     """
 409     Test objects to insert for the third set of things
 410     """
 411     # Insert creatures
 412     session.add_all(
 413         [Creature3(
 414                 name=u'centipede',
 415                 num_limbs=100),
 416          Creature3(
 417                 name=u'wolf',
 418                 num_limbs=4,
 419                 magical_powers = [
 420                     CreaturePower3(
 421                         name=u"ice breath",
 422                         description=u"A blast of icy breath!",
 423                         hitpower=20.0),
 424                     CreaturePower3(
 425                         name=u"death stare",
 426                         description=u"A frightening stare, for sure!",
 427                         hitpower=45.0)]),
 428          Creature3(
 429                 name=u'wizardsnake',
 430                 num_limbs=0,
 431                 magical_powers=[
 432                     CreaturePower3(
 433                         name=u'death_rattle',
 434                         description=u'A rattle... of DEATH!',
 435                         hitpower=1000.0),
 436                     CreaturePower3(
 437                         name=u'sneaky_stare',
 438                         description=u"The sneakiest stare you've ever seen!",
 439                         hitpower=300.0),
 440                     CreaturePower3(
 441                         name=u'slithery_smoke',
 442                         description=u"A blast of slithery, slithery smoke.",
 443                         hitpower=10.0),
 444                     CreaturePower3(
 445                         name=u'treacherous_tremors',
 446                         description=u"The ground shakes beneath footed animals!",
 447                         hitpower=0.0)])],
 448         # annnnnd one more to test a floating point hitpower
 449         Creature3(
 450                 name=u'deity',
 451                 numb_limbs=30,
 452                 magical_powers=[
 453                     CreaturePower3(
 454                         name=u'smite',
 455                         description=u'Smitten by holy wrath!',
 456                         hitpower=9999.9)]))
 457
 458     # Insert levels
 459     session.add_all(
 460         [Level3(id=u'necroplex',
 461                 name=u'The Necroplex',
 462                 description=u'A complex full of pure deathzone.'),
 463          Level3(id=u'evilstorm',
 464                 name=u'Evil Storm',
 465                 description=u'A storm full of pure evil.',
 466                 exits=[]), # you can't escape the evilstorm
 467          Level3(id=u'central_park',
 468                 name=u'Central Park, NY, NY',
 469                 description=u"New York's friendly Central Park.")])
 470
 471     # necroplex exits
 472     session.add_all(
 473         [LevelExit3(name=u'deathwell',
 474                     from_level=u'necroplex',
 475                     to_level=u'evilstorm'),
 476          LevelExit3(name=u'portal',
 477                     from_level=u'necroplex',
 478                     to_level=u'central_park')])
 479
 480     # there are no evilstorm exits because there is no exit from the
 481     # evilstorm
 482
 483     # central park exits
 484     session.add_all(
 485         [LevelExit3(name=u'portal',
 486                     from_level=u'central_park',
 487                     to_level=u'necroplex')])
 488
 489     session.commit()
 490
 491
 492 class CollectingPrinter(object):
 493     def __init__(self):
 494         self.collection = []
 495
 496     def __call__(self, string):
 497         self.collection.append(string)
 498
 499     @property
 500     def combined_string(self):
 501         return u''.join(self.collection)
 502
 503
 504 def create_test_engine():
 505     from sqlalchemy import create_engine
 506     engine = create_engine('sqlite:///:memory:', echo=False)
 507     Session = sessionmaker(bind=engine)
 508     return engine, Session
 509
 510
 511 def assert_col_type(column, this_class):
 512     assert isinstance(column.type, this_class)
 513
 514
 515 def _get_level3_exits(session, level):
 516     return dict(
 517         [(level_exit.name, level_exit.to_level)
 518          for level_exit in
 519          session.query(LevelExit3).filter_by(from_level=level.id)])
 520
 521
 522 def test_set1_to_set3():
 523     # Create / connect to database
 524     # ----------------------------
 525
 526     engine, Session = create_test_engine()
 527
 528     # Create tables by migrating on empty initial set
 529     # -----------------------------------------------
 530
 531     printer = CollectingPrinter()
 532     migration_manager = MigrationManager(
 533         '__main__', SET1_MODELS, SET1_MIGRATIONS, Session(),
 534         printer)
 535
 536     # Check latest migration and database current migration
 537     assert migration_manager.latest_migration == 0
 538     assert migration_manager.database_current_migration == None
 539
 540     result = migration_manager.init_or_migrate()
 541
 542     # Make sure output was "inited"
 543     assert result == u'inited'
 544     # Check output
 545     assert printer.combined_string == (
 546         "-> Initializing main mediagoblin tables... done.\n")
 547     # Check version in database
 548     assert migration_manager.latest_migration == 0
 549     assert migration_manager.database_current_migration == 0
 550
 551     # Install the initial set
 552     # -----------------------
 553
 554     _insert_migration1_objects(Session())
 555
 556     # Try to "re-migrate" with same manager settings... nothing should happen
 557     migration_manager = MigrationManager(
 558         '__main__', SET1_MODELS, SET1_MIGRATIONS, Session(),
 559         printer)
 560     assert migration_manager.init_or_migrate() == None
 561
 562     # Check version in database
 563     assert migration_manager.latest_migration == 0
 564     assert migration_manager.database_current_migration == 0
 565
 566     # Sanity check a few things in the database...
 567     metadata = MetaData(bind=engine)
 568
 569     # Check the structure of the creature table
 570     creature_table = Table(
 571         'creature', metadata,
 572         autoload=True, autoload_with=engine)
 573     assert set(creature_table.c.keys()) == set(
 574         ['id', 'name', 'num_legs', 'is_demon'])
 575     assert_col_type(creature_table.c.id, Integer)
 576     assert_col_type(creature_table.c.name, Unicode)
 577     assert creature_table.c.name.nullable is False
 578     assert creature_table.c.name.index is True
 579     assert creature_table.c.name.unique is True
 580     assert_col_type(creature_table.c.num_legs, Integer)
 581     assert creature_table.c.num_legs.nullable is False
 582     assert_col_type(creature_table.c.is_demon, Boolean)
 583
 584     # Check the structure of the level table
 585     level_table = Table(
 586         'level', metadata,
 587         autoload=True, autoload_with=engine)
 588     assert set(level_table.c.keys()) == set(
 589         ['id', 'name', 'description', 'exits'])
 590     assert_col_type(level_table.c.id, Unicode)
 591     assert level_table.c.id.primary_key is True
 592     assert_col_type(level_table.c.name, Unicode)
 593     assert_col_type(level_table.c.description, Unicode)
 594     # Skipping exits... Not sure if we can detect pickletype, not a
 595     # big deal regardless.
 596
 597     # Now check to see if stuff seems to be in there.
 598     session = Session()
 599
 600     creature = session.query(Creature1).filter_by(
 601         name=u'centipede').one()
 602     assert creature.num_legs == 100
 603     assert creature.is_demon == False
 604
 605     creature = session.query(Creature1).filter_by(
 606         name=u'wolf').one()
 607     assert creature.num_legs == 4
 608     assert creature.is_demon == False
 609
 610     creature = session.query(Creature1).filter_by(
 611         name=u'wizardsnake').one()
 612     assert creature.num_legs == 0
 613     assert creature.is_demon == True
 614
 615     level = session.query(Level1).filter_by(
 616         id=u'necroplex').one()
 617     assert level.name == u'The Necroplex'
 618     assert level.description == u'A complex of pure deathzone.'
 619     assert level.exits == {
 620         'deathwell': 'evilstorm',
 621         'portal': 'central_park'}
 622
 623     level = session.query(Level1).filter_by(
 624         id=u'evilstorm').one()
 625     assert level.name == u'Evil Storm'
 626     assert level.description == u'A storm full of pure evil.'
 627     assert level.exits == {}  # You still can't escape the evilstorm!
 628
 629     level = session.query(Level1).filter_by(
 630         id=u'central_park').one()
 631     assert level.name == u'Central Park, NY, NY'
 632     assert level.description == u"New York's friendly Central Park."
 633     assert level.exits == {
 634         'portal': 'necroplex'}
 635
 636     # Create new migration manager, but make sure the db migration
 637     # isn't said to be updated yet
 638     printer = CollectingPrinter()
 639     migration_manager = MigrationManager(
 640         '__main__', SET3_MODELS, SET3_MIGRATIONS, Session(),
 641         printer)
 642
 643     assert migration_manager.latest_migration == 3
 644     assert migration_manager.database_current_migration == 0
 645
 646     # Migrate
 647     result = migration_manager.init_or_migrate()
 648
 649     # Make sure result was "migrated"
 650     assert result == u'migrated'
 651
 652     # TODO: Check output to user
 653     assert printer.combined_string == """\
 654 -> Updating main mediagoblin tables...
 655    + Running migration 1, "creature_remove_is_demon"... done.
 656    + Running migration 2, "creature_powers_new_table"... done.
 657    + Running migration 3, "level_exits_new_table"... done."""
 658
 659     # Make sure version matches expected
 660     migration_manager = MigrationManager(
 661         '__main__', SET3_MODELS, SET3_MIGRATIONS, Session(),
 662         printer)
 663     assert migration_manager.latest_migration == 3
 664     assert migration_manager.database_current_migration == 3
 665
 666     # Check all things in database match expected
 667
 668     # Check the creature table
 669     creature_table = Table(
 670         'creature', metadata,
 671         autoload=True, autoload_with=engine)
 672     assert set(creature_table.c.keys()) == set(
 673         ['id', 'name', 'num_limbs'])
 674     assert_col_type(creature_table.c.id, Integer)
 675     assert_col_type(creature_table.c.name, Unicode)
 676     assert creature_table.c.name.nullable is False
 677     assert creature_table.c.name.index is True
 678     assert creature_table.c.name.unique is True
 679     assert_col_type(creature_table.c.num_legs, Integer)
 680     assert creature_table.c.num_legs.nullable is False
 681
 682     # Check the CreaturePower table
 683     creature_power_table = Table(
 684         'creature_power', metadata,
 685         autoload=True, autoload_with=engine)
 686     assert set(creature_power_table.c.keys()) == set(
 687         ['id', 'creature', 'name', 'description', 'hitpower'])
 688     assert_col_type(creature_power_table.c.id, Integer)
 689     assert_col_type(creature_power_table.c.creature, Integer)
 690     assert creature_power_table.c.creature.nullable is False
 691     assert_col_type(creature_power_table.c.name, Unicode)
 692     assert_col_type(creature_power_table.c.description, Unicode)
 693     assert_col_type(creature_power_table.c.hitpower, Float)
 694     assert creature_power_table.c.hitpower.nullable is False
 695
 696     # Check the structure of the level table
 697     level_table = Table(
 698         'level', metadata,
 699         autoload=True, autoload_with=engine)
 700     assert set(level_table.c.keys()) == set(
 701         ['id', 'name', 'description'])
 702     assert_col_type(level_table.c.id, Unicode)
 703     assert level_table.c.id.primary_key is True
 704     assert_col_type(level_table.c.name, Unicode)
 705     assert_col_type(level_table.c.description, Unicode)
 706
 707     # Check the structure of the level_exits table
 708     level_exit_table = Table(
 709         'level_exit', metadata,
 710         autoload=True, autoload_with=engine)
 711     assert set(level_exit_table.c.keys()) == set(
 712         ['id', 'name', 'from_level', 'to_level'])
 713     assert_col_type(level_exit_table.c.id, Integer)
 714     assert_col_type(level_exit_table.c.name, Unicode)
 715     assert_col_type(level_exit_table.c.from_level, Unicode)
 716     assert level_exit_table.c.from_level.nullable is False
 717     assert level_exit_table.c.from_level.indexed is True
 718     assert_col_type(level_exit_table.c.to_level, Unicode)
 719     assert level_exit_table.c.to_level.nullable is False
 720     assert level_exit_table.c.to_level.indexed is True
 721
 722     # Now check to see if stuff seems to be in there.
 723     session = Session()
 724     creature = session.query(Creature3).filter_by(
 725         name=u'centipede').one()
 726     assert creature.num_limbs == 100.0
 727     assert creature.creature_powers == []
 728
 729     creature = session.query(Creature3).filter_by(
 730         name=u'wolf').one()
 731     assert creature.num_limbs == 4.0
 732     assert creature.creature_powers == []
 733
 734     creature = session.query(Creature3).filter_by(
 735         name=u'wizardsnake').one()
 736     assert creature.num_limbs == 0.0
 737     assert creature.creature_powers == []
 738
 739     level = session.query(Level3).filter_by(
 740         id=u'necroplex').one()
 741     assert level.name == u'The Necroplex'
 742     assert level.description == u'A complex of pure deathzone.'
 743     level_exits = _get_level3_exits(session, level)
 744     assert level_exits == {
 745         u'deathwell': u'evilstorm',
 746         u'portal': u'central_park'}
 747
 748     level = session.query(Level3).filter_by(
 749         id=u'evilstorm').one()
 750     assert level.name == u'Evil Storm'
 751     assert level.description == u'A storm full of pure evil.'
 752     level_exits = _get_level3_exits(session, level)
 753     assert level_exits == {}  # You still can't escape the evilstorm!
 754
 755     level = session.query(Level3).filter_by(
 756         id=u'central_park').one()
 757     assert level.name == u'Central Park, NY, NY'
 758     assert level.description == u"New York's friendly Central Park."
 759     level_exits = _get_level3_exits(session, level)
 760     assert level_exits == {
 761         'portal': 'necroplex'}
 762
 763
 764 def test_set2_to_set3():
 765     # Create / connect to database
 766     # Create tables by migrating on empty initial set
 767
 768     # Install the initial set
 769     # Check version in database
 770     # Sanity check a few things in the database
 771
 772     # Migrate
 773     # Make sure version matches expected
 774     # Check all things in database match expected
 775     pass
 776
 777
 778 def test_set1_to_set2_to_set3():
 779     # Create / connect to database
 780     # Create tables by migrating on empty initial set
 781
 782     # Install the initial set
 783     # Check version in database
 784     # Sanity check a few things in the database
 785
 786     # Migrate
 787     # Make sure version matches expected
 788     # Check all things in database match expected
 789
 790     # Migrate again
 791     # Make sure version matches expected again
 792     # Check all things in database match expected again
 793
 794     ##### Set2
 795     # creature_table = Table(
 796     #     'creature', metadata,
 797     #     autoload=True, autoload_with=db_conn.engine)
 798     # assert set(creature_table.c.keys()) == set(
 799     #     ['id', 'name', 'num_legs'])
 800     # assert_col_type(creature_table.c.id, Integer)
 801     # assert_col_type(creature_table.c.name, Unicode)
 802     # assert creature_table.c.name.nullable is False
 803     # assert creature_table.c.name.index is True
 804     # assert creature_table.c.name.unique is True
 805     # assert_col_type(creature_table.c.num_legs, Integer)
 806     # assert creature_table.c.num_legs.nullable is False
 807
 808     # # Check the CreaturePower table
 809     # creature_power_table = Table(
 810     #     'creature_power', metadata,
 811     #     autoload=True, autoload_with=db_conn.engine)
 812     # assert set(creature_power_table.c.keys()) == set(
 813     #     ['id', 'creature', 'name', 'description', 'hitpower'])
 814     # assert_col_type(creature_power_table.c.id, Integer)
 815     # assert_col_type(creature_power_table.c.creature, Integer)
 816     # assert creature_power_table.c.creature.nullable is False
 817     # assert_col_type(creature_power_table.c.name, Unicode)
 818     # assert_col_type(creature_power_table.c.description, Unicode)
 819     # assert_col_type(creature_power_table.c.hitpower, Integer)
 820     # assert creature_power_table.c.hitpower.nullable is False
 821
 822     # # Check the structure of the level table
 823     # level_table = Table(
 824     #     'level', metadata,
 825     #     autoload=True, autoload_with=db_conn.engine)
 826     # assert set(level_table.c.keys()) == set(
 827     #     ['id', 'name', 'description'])
 828     # assert_col_type(level_table.c.id, Unicode)
 829     # assert level_table.c.id.primary_key is True
 830     # assert_col_type(level_table.c.name, Unicode)
 831     # assert_col_type(level_table.c.description, Unicode)
 832
 833     # # Check the structure of the level_exits table
 834     # level_exit_table = Table(
 835     #     'level_exit', metadata,
 836     #     autoload=True, autoload_with=db_conn.engine)
 837     # assert set(level_exit_table.c.keys()) == set(
 838     #     ['id', 'name', 'from_level', 'to_level'])
 839     # assert_col_type(level_exit_table.c.id, Integer)
 840     # assert_col_type(level_exit_table.c.name, Unicode)
 841     # assert_col_type(level_exit_table.c.from_level, Unicode)
 842     # assert level_exit_table.c.from_level.nullable is False
 843     # assert_col_type(level_exit_table.c.to_level, Unicode)
 844
 845     pass