docs/source/pluginwriter/database.rst

   1 .. MediaGoblin Documentation
   2
   3    Written in 2013 by MediaGoblin contributors
   4
   5    To the extent possible under law, the author(s) have dedicated all
   6    copyright and related and neighboring rights to this software to
   7    the public domain worldwide. This software is distributed without
   8    any warranty.
   9
  10    You should have received a copy of the CC0 Public Domain
  11    Dedication along with this software. If not, see
  12    <http://creativecommons.org/publicdomain/zero/1.0/>.
  13
  14
  15 .. _plugin-database-chapter:
  16
  17
  18 ===========================
  19 Database models for plugins
  20 ===========================
  21
  22
  23 Accessing Existing Data
  24 =======================
  25
  26 If your plugin wants to access existing data, this is quite
  27 straight forward. Just import the appropiate models and use
  28 the full power of SQLAlchemy. Take a look at the (upcoming)
  29 database section in the Developer's Chapter.
  30
  31
  32 Creating new Tables
  33 ===================
  34
  35 If your plugin needs some new space to store data, you
  36 should create a new table.  Please do not modify core
  37 tables.  Not doing so might seem inefficient and possibly
  38 is.  It will help keep things sane and easier to upgrade
  39 versions later.
  40
  41 So if you create a new plugin and need new tables, create a
  42 file named ``models.py`` in your plugin directory. You
  43 might take a look at the core's db.models for some ideas.
  44 Here's a simple one:
  45
  46 .. code-block:: python
  47
  48     from mediagoblin.db.base import Base
  49     from sqlalchemy import Column, Integer, Unicode, ForeignKey
  50
  51     class MediaSecurity(Base):
  52         __tablename__ = "yourplugin__media_security"
  53
  54         # The primary key *and* reference to the main media_entry
  55         media_entry = Column(Integer, ForeignKey('core__media_entries.id'),
  56             primary_key=True)
  57         get_media_entry = relationship("MediaEntry",
  58             backref=backref("security_rating", cascade="all, delete-orphan"))
  59
  60         rating = Column(Unicode)
  61
  62     MODELS = [MediaSecurity]
  63
  64 That's it.
  65
  66 Some notes:
  67
  68 * Make sure all your ``__tablename__`` start with your
  69   plugin's name so the tables of various plugins can't
  70   conflict in the database. (Conflicts in python naming are
  71   much easier to fix later).
  72 * Try to get your database design as good as possible in
  73   the first attempt.  Changing the database design later,
  74   when people already have data using the old design, is
  75   possible (see next chapter), but it's not easy.
  76
  77
  78 Changing the Database Schema Later
  79 ==================================
  80
  81 If your plugin is in use and instances use it to store some
  82 data, changing the database design is a tricky thing.
  83
  84 1. Make up your mind how the new schema should look like.
  85 2. Change ``models.py`` to contain the new schema. Keep a
  86    copy of the old version around for your personal
  87    reference later.
  88 3. Now make up your mind (possibly using your old and new
  89    ``models.py``) what steps in SQL are needed to convert
  90    the old schema to the new one.
  91    This is called a "migration".
  92 4. Create a file ``migrations.py`` that will contain all
  93    your migrations and add your new migration.
  94
  95 Take a look at the core's ``db/migrations.py`` for some
  96 good examples on what you might be able to do. Here's a
  97 simple one to add one column:
  98
  99 .. code-block:: python
 100
 101     from mediagoblin.db.migration_tools import RegisterMigration, inspect_table
 102     from sqlalchemy import MetaData, Column, Integer
 103
 104     MIGRATIONS = {}
 105
 106     @RegisterMigration(1, MIGRATIONS)
 107     def add_license_preference(db):
 108         metadata = MetaData(bind=db.bind)
 109
 110         security_table = inspect_table(metadata, 'yourplugin__media_security')
 111
 112         col = Column('security_level', Integer)
 113         col.create(security_table)
 114         db.commit()