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