Models and migrations¶

POPPy is built around SQLAlchemy , an Object-Relational Mapper (ORM) well known by Python developers.

The model integration needs some code to work, located in every __init__.py of the models/ directory of each plugin. This code is automatically generated when you call the command poppy create plugin. If for some reason you do not have the code, you can find it in the POPPyCore source code at POPPyCore/poppy/core/management/templates/plugin_template/plugin_namespace/plugin_name/models/__init__.py-tpl

Write models for the main database¶

First, create a file in your plugin’s model directory and add these imports:

from poppy.pop.models.non_null_column import NonNullColumn
from poppy.core.db.base import Base

NonNullColumn is an improvement on SQLAlchemy’s Column class. It is non null by default and allows us to autogenerate the documentation correponding to the model we are writing.
Base is the base class for any SQLAlchemy model and the one located in poppy.core.db.base is the one corresponding to the main database.

Then, you can create your model class inheriting from the base class, as you would any SQLAlchemy model:

class Dictionary(Base):

    id_dictionary = NonNullColumn(INTEGER(), primary_key=True, unique=True)
    word = NonNullColumn(String(16), descr='The word', unique=True, comment='Must be an english word')

    __tablename__ = 'dictionary'

In this class you will define every column of your table including their type (either general or dialect specific types). If you want to generate your database documentation automatically (see chapter TBC), you should use poppy’s custom column class NonNullColumn instead of sqlalchemy.Column.

The fields description and comment are used to generate the documentation.

Note

Do not use double quotes in your description or comment, but only escaped single quotes.

Write models for a secondary database¶

You might want to use SQLAlchemy to interface an already existing secondary database. In this case, you don’t need to detail every column of the table. You only need to create a class that inherits from Base and DeferredReflection, and give the table name. You don’t even need a primary key.

from sqlalchemy.ext.declarative import DeferredReflection

class PacketType(DeferredReflection, Base):
    __tablename__ = "packettype"

Generate the migration¶

The migration generation is integrated in the POPPy framework through calls to alembic. It is able to generate distinct migrations for every plugin.

python manage.py db makemigrations tuto.texter

You can use any argument of the alembic revision command as follows

python manage.py db makemigrations tuto.texter --message='First tuto.texter migration' --depends_on='poppy.pop' --head=poppy.pop

Edit the migration¶

Alembic migration generation is not perfect and you have to manually review and correct the migrations that alembic produces. Furthermore, alembic is not yet fully integrated to the POPPy framework and some modifications has to be done in order for the migration to be registered by the framework. See (POPPy improvements) chapter.

First add this import

from poppy.pop.alembic.helpers import create_table, execute, user, drop_table

Then, replace every op.create_table with the poppy wrapper function create_table. It is needed to grant access to the pipeline user and log what is happening. Do the same with drop_table. At this point you can also edit the alembic revision identifiers, for example add a branch label for convenience and the migration this one depends on (remember you can do all of this with the command arguments)

Finally, you should check the migration for any mistake that alembic may have made. See this alembic documentation page to know what alembic can and cannot detect.

Note

Alembic does not take in charge the creation of the Postgres database schemas and the user grant. Make sure that these commands have been added correctly in the migration script generated by Alembic.
Make sure that the branch_labels and depends_on keywords are well defined in the migration script before running the migration.

Manage your migration branches¶

You can manage your branches as explained in the alembic documentation about branches

You can call alembic commands through the pipeline with this command, so you don’t have to worry about users and databases or even alembic configuration:

python manage.py db alembic ...

Run the migrations¶

You can either run migrations one by one by giving their branch label:

$ python manage.py db upgrade poppy.pop
$ python manage.py db upgrade tuto.texter

or all in one go with:

$ python manage.py db upgrade heads

Use postgresql schemas¶

If you want to use postgres schemas, you need to specify it in the models, in the __table_args__ field:

class Dictionary(Base):

...

__table_args__ = {'
    schema': 'my_schema'
}

Then, when editing the first migration you need to manually add the creation and deletion of the schema :

from poppy.pop.alembic.helpers import create_schema, execute, drop_schema

def upgrade():
    create_schema('my_schema')

...

def downgrade():
    drop_schema('my_schema', cascade=True)

Generate the documentation of your database¶

Using this command :

poppy gen_doc your.plugin

You can generate a reST document containing the description of every database table of the given plugin. There is one document per plugin. The document is generated Here is an example of rst code generated and what it looks like once compiled.

The table dictionary
~~~~~~~~~~~~~~~~~~~~
The "dictionary" table contains the dictionary

.. csv-table:: dictionary
   :header: "Column name", "Data type", "Description", "Priority", "Comment"

   "id_dictionary", "INTEGER", "Primary key", "PK", ""
   "identifier", "INTEGER", "Identifier of the word", "NN", "Must be unique. "
   "word", "VARCHAR(16)", "The word", "NN", "Must be unique. Must be an english word."

The tuple of columns (identifier,word) must be unique.

The table dictionary¶

The “dictionary” table contains the dictionary

Tab. 1 dictionary¶
Column name	Data type	Description	Priority	Comment
id_dictionary	INTEGER	Primary key	PK
identifier	INTEGER	Identifier of the word	NN	Must be unique.
word	VARCHAR(16)	The word	NN	Must be unique. Must be an english word.

The tuple of columns (identifier,word) must be unique.

Note

The code responsible for the documentation generation is located in poppy.core.management.commands in the function gen_doc()