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