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
  24 from sqlalchemy.ext.declarative import declarative_base
  25 from sqlalchemy.sql import select, insert
  26 from migrate import changeset
  27
  28 from mediagoblin.db.sql.base import GMGTableBase
  29
  30
  31 # This one will get filled with local migrations
  32 FULL_MIGRATIONS = {}
  33
  34
  35 #######################################################
  36 # Migration set 1: Define initial models, no migrations
  37 #######################################################
  38
  39 Base1 = declarative_base(cls=GMGTableBase)
  40
  41 class Creature1(Base1):
  42     __tablename__ = "creature"
  43
  44     id = Column(Integer, primary_key=True)
  45     name = Column(Unicode, unique=True, nullable=False, index=True)
  46     num_legs = Column(Integer, nullable=False)
  47     is_demon = Column(Boolean)
  48
  49 class Level1(Base1):
  50     __tablename__ = "level"
  51
  52     id = Column(Unicode, primary_key=True)
  53     name = Column(Unicode, unique=True, nullable=False, index=True)
  54     description = Column(UnicodeText)
  55     exits = Column(PickleType)
  56
  57 SET1_MODELS = [Creature1, Level1]
  58
  59 SET1_MIGRATIONS = []
  60
  61 #######################################################
  62 # Migration set 2: A few migrations and new model
  63 #######################################################
  64
  65 Base2 = declarative_base(cls=GMGTableBase)
  66
  67 class Creature2(Base2):
  68     __tablename__ = "creature"
  69
  70     id = Column(Integer, primary_key=True)
  71     name = Column(Unicode, unique=True, nullable=False, index=True)
  72     num_legs = Column(Integer, nullable=False)
  73
  74 class CreaturePower2(Base2):
  75     __tablename__ = "creature_power"
  76
  77     id = Column(Integer, primary_key=True)
  78     creature = Column(
  79         Integer, ForeignKey('creature.id'), nullable=False)
  80     name = Column(Unicode)
  81     description = Column(Unicode)
  82     hitpower = Column(Integer, nullable=False)
  83
  84 class Level2(Base2):
  85     __tablename__ = "level"
  86
  87     id = Column(Unicode, primary_key=True)
  88     name = Column(Unicode)
  89     description = Column(UnicodeText)
  90
  91 class LevelExit2(Base2):
  92     __tablename__ = "level_exit"
  93
  94     id = Column(Integer, primary_key=True)
  95     name = Column(Unicode)
  96     from_level = Column(
  97         Unicode, ForeignKey('level.id'), nullable=False)
  98     to_level = Column(
  99         Unicode, ForeignKey('level.id'), nullable=False)
 100
 101 SET2_MODELS = [Creature2, CreaturePower2, Level2, LevelExit2]
 102
 103
 104 @RegisterMigration(1, FULL_MIGRATIONS)
 105 def creature_remove_is_demon(db_conn):
 106     metadata = MetaData(bind=db_conn.engine)
 107     creature_table = Table(
 108         'creature', metadata,
 109         autoload=True, autoload_with=db_conn.engine)
 110     creature_table.drop_column('is_demon')
 111
 112
 113 @RegisterMigration(2, FULL_MIGRATIONS)
 114 def creature_powers_new_table(db_conn):
 115     metadata = MetaData(bind=db_conn.engine)
 116     creature_powers = Table(
 117         'creature_power', metadata,
 118         Column('id', Integer, primary_key=True),
 119         Column('creature',
 120                Integer, ForeignKey('creature.id'), nullable=False),
 121         Column('name', Unicode),
 122         Column('description', Unicode),
 123         Column('hitpower', Integer, nullable=False))
 124     metadata.create_all(db_conn.engine)
 125
 126
 127 @RegisterMigration(3, FULL_MIGRATIONS)
 128 def level_exits_new_table(db_conn):
 129     # First, create the table
 130     # -----------------------
 131     metadata = MetaData(bind=db_conn.engine)
 132     level_exits = Table(
 133         'level_exit', metadata,
 134         Column('id', Integer, primary_key=True),
 135         Column('name', Unicode),
 136         Column('from_level',
 137                Integer, ForeignKey('level.id'), nullable=False),
 138         Column('to_level',
 139                Integer, ForeignKey('level.id'), nullable=False))
 140     metadata.create_all(db_conn.engine)
 141
 142     # And now, convert all the old exit pickles to new level exits
 143     # ------------------------------------------------------------
 144
 145     # Minimal representation of level table.
 146     # Not auto-introspecting here because of pickle table.  I'm not
 147     # sure sqlalchemy can auto-introspect pickle columns.
 148     levels = Table(
 149         'level', metadata,
 150         Column('id', Integer, primary_key=True),
 151         Column('exits', PickleType))
 152
 153     # query over and insert
 154     result = db_conn.execute(
 155         select([levels], levels.c.exits!=None))
 156
 157     for level in result:
 158         this_exit = level['exits']
 159
 160         # Insert the level exit
 161         db_conn.execute(
 162             level_exits.insert().values(
 163                 name=this_exit['name'],
 164                 from_level=this_exit['from_level'],
 165                 to_level=this_exit['to_level']))
 166
 167     # Finally, drop the old level exits pickle table
 168     # ----------------------------------------------
 169     levels.drop_column('exits')
 170
 171
 172 # A hack!  At this point we freeze-fame and get just a partial list of
 173 # migrations
 174
 175 SET2_MIGRATIONS = copy.copy(FULL_MIGRATIONS)
 176
 177 #######################################################
 178 # Migration set 3: Final migrations
 179 #######################################################
 180
 181 Base3 = declarative_base(cls=GMGTableBase)
 182
 183 class Creature3(Base3):
 184     __tablename__ = "creature"
 185
 186     id = Column(Integer, primary_key=True)
 187     name = Column(Unicode, unique=True, nullable=False, index=True)
 188     num_limbs= Column(Integer, nullable=False)
 189
 190 class CreaturePower3(Base3):
 191     __tablename__ = "creature_power"
 192
 193     id = Column(Integer, primary_key=True)
 194     creature = Column(
 195         Integer, ForeignKey('creature.id'), nullable=False, index=True)
 196     name = Column(Unicode)
 197     description = Column(Unicode)
 198     hitpower = Column(Float, nullable=False)
 199
 200 class Level3(Base3):
 201     __tablename__ = "level"
 202
 203     id = Column(Unicode, primary_key=True)
 204     name = Column(Unicode)
 205     description = Column(UnicodeText)
 206
 207 class LevelExit3(Base3):
 208     __tablename__ = "level_exit"
 209
 210     id = Column(Integer, primary_key=True)
 211     name = Column(Unicode)
 212     from_level = Column(
 213         Unicode, ForeignKey('level.id'), nullable=False, index=True)
 214     to_level = Column(
 215         Unicode, ForeignKey('level.id'), nullable=False, index=True)
 216
 217
 218 SET3_MODELS = [Creature3, CreaturePower3, Level3, LevelExit3]
 219
 220
 221 @RegisterMigration(4, FULL_MIGRATIONS)
 222 def creature_num_legs_to_num_limbs(db_conn):
 223     metadata = MetaData(bind=db_conn.engine)
 224     creature_table = Table(
 225         'creature', metadata,
 226         autoload=True, autoload_with=db_conn.engine)
 227     creature_table.c.num_legs.alter(name="num_limbs")
 228
 229
 230 @RegisterMigration(5, FULL_MIGRATIONS)
 231 def level_exit_index_from_and_to_level(db_conn):
 232     metadata = MetaData(bind=db_conn.engine)
 233     level_exit = Table(
 234         'level_exit', metadata,
 235         autoload=True, autoload_with=db_conn.engine)
 236     Index('ix_from_level', level_exit.c.from_level).create(engine)
 237     Index('ix_to_exit', level_exit.c.to_exit).create(engine)
 238
 239
 240 @RegisterMigration(6, FULL_MIGRATIONS)
 241 def creature_power_index_creature(db_conn):
 242     metadata = MetaData(bind=db_conn.engine)
 243     creature_power = Table(
 244         'creature_power', metadata,
 245         autoload=True, autoload_with=db_conn.engine)
 246     Index('ix_creature', creature_power.c.creature).create(engine)
 247
 248
 249 @RegisterMigration(7, FULL_MIGRATIONS)
 250 def creature_power_hitpower_to_float(db_conn):
 251     metadata = MetaData(bind=db_conn.engine)
 252     creature_power = Table(
 253         'creature_power', metadata,
 254         autoload=True, autoload_with=db_conn.engine)
 255     creature_power.c.hitpower.alter(type=Float)
 256
 257
 258 def _insert_migration1_objects(session):
 259     # Insert creatures
 260     session.add_all(
 261         [Creature1(name='centipede',
 262                    num_legs=100,
 263                    is_demon=False),
 264          Creature1(name='wolf',
 265                    num_legs=4,
 266                    is_demon=False),
 267          # don't ask me what a wizardsnake is.
 268          Creature1(name='wizardsnake',
 269                    num_legs=0,
 270                    is_demon=True)])
 271
 272     session.add_all(
 273         [Level1(id='necroplex',
 274                 name='The Necroplex',
 275                 description='A complex full of pure deathzone.',
 276                 exits={
 277                     'deathwell': 'evilstorm',
 278                     'portal': 'central_park'}),
 279          Level1(id='evilstorm',
 280                 name='Evil Storm',
 281                 description='A storm full of pure evil.',
 282                 exits={}), # you can't escape the evilstorm
 283          Level1(id='central_park'
 284                 name='Central Park, NY, NY',
 285                 description="New York's friendly Central Park.",
 286                 exits={
 287                     'portal': 'necroplex'})])
 288
 289     session.commit()
 290