Add Storage Service

The storage service is conceptually a little different to other services in the Peek Platform.

Peek Storage connects to a database server, provides each plugin it’s own schema, and provides much of the boilerplate code required to make this work.

Only two Peek Services are able to access the database, these are the Worker and Server services.

The Storage schema upgrades are managed by the Server service.

Note

The Server service must be enabled to use the Storage service.

Storage File Structure

Add Package _private/storage

Package _private/storage will contain the database ORM classes. These define the schema for the database and are used for data manipulation and retrieval.


Create the peek_plugin_tutorial._private/storage Package. Commands:

mkdir -p peek_plugin_tutorial/_private/storage
touch peek_plugin_tutorial/_private/storage/__init__.py

Add File DeclarativeBase.py

The DeclarativeBase.py file defines an SQLAlchemy declarative base class. All Table classes inheriting this base class belong together, you can have multiple declarative bases.

See SQLALchemy for more details.

In this declarative base, we define a metadata with a schema name for this plugin, pl_tutorial.

All the table classes in the plugin will be loaded in this method.


Create a file peek_plugin_tutorial/_private/storage/DeclarativeBase.py and populate it with the following contents:

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.schema import MetaData

metadata = MetaData(schema="pl_tutorial")
DeclarativeBase = declarative_base(metadata=metadata)


def loadStorageTuples():
    """ Load Storage Tables

    This method should be called from the "load()" method of the agent, server, worker
    and client entry hook classes.

    This will register the ORM classes as tuples, allowing them to be serialised and
    deserialized by the vortex.

    """

Add Package alembic

Alembic is the database upgrade library Peek uses. The alembic package is where the alembic configuration will be kept.

Read more about Alembic here


Create directory peek_plugin_tutorial/_private/alembic Create the empty package file peek_plugin_tutorial/_private/alembic/__init__.py

Command:

mkdir peek_plugin_tutorial/_private/alembic
touch peek_plugin_tutorial/_private/alembic/__init__.py

Add Package versions

The versions package is where the Alembic database upgrade scripts are kept.


Create directory peek_plugin_tutorial/_private/alembic/versions Create the empty package file peek_plugin_tutorial/_private/alembic/versions/__init__.py

Command:

mkdir peek_plugin_tutorial/_private/alembic/versions
touch peek_plugin_tutorial/_private/alembic/versions/__init__.py

Add File env.py

The env.py is loaded by Alembic to get it’s configuration and environment.

Notice that that loadStorageTuples() is called? Alembic needs the table classes loaded to create the version control scripts.


Create a file peek_plugin_tutorial/_private/alembic/env.py and populate it with the following contents:

from peek_plugin_base.storage.AlembicEnvBase import AlembicEnvBase

from peek_plugin_tutorial._private.storage import DeclarativeBase

DeclarativeBase.loadStorageTuples()

alembicEnv = AlembicEnvBase(DeclarativeBase.metadata)
alembicEnv.run()

Add File script.py.mako

The script.py.mako file is a template that is used by Alembic to create new database version scripts.

Out of interest, Alembic uses Mako to compile the template into a new script.


Create a file peek_plugin_tutorial/_private/alembic/script.py.mako and populate it with the following contents:

"""${message}

Peek Plugin Database Migration Script

Revision ID: ${up_revision}
Revises: ${down_revision | comma,n}
Create Date: ${create_date}

"""

# revision identifiers, used by Alembic.
revision = ${repr(up_revision)}
down_revision = ${repr(down_revision)}
branch_labels = ${repr(branch_labels)}
depends_on = ${repr(depends_on)}

from alembic import op
import sqlalchemy as sa
import geoalchemy2
${imports if imports else ""}

def upgrade():
    ${upgrades if upgrades else "pass"}


def downgrade():
    ${downgrades if downgrades else "pass"}

Edit File plugin_package.json

For more details about the plugin_package.json, see About plugin_package.json.


Edit the file peek_plugin_tutorial/plugin_package.json :

  1. Add “storage” to the requiresServices section so it looks like

    "requiresServices": [
        "storage"
    ]
    
  2. Add the storage section after requiresServices section:

    "storage": {
        "alembicDir": "_private/alembic"
    }
    
  3. Ensure your JSON is still valid (Your IDE may help here)

Here is an example

{
    ...
    "requiresServices": [
        ...
        "storage"
    ],
    ...
    "storage": {
    }
}

Edit File ServerEntryHook.py

The ServerEntryHook.py file needs to be updated to do the following:

  • Implement the PluginServerStorageEntryHookABC abstract base class. Including implementing dbMetadata property.
  • Ensure that the storage Tables are loaded on plugin load.

Edit the file peek_plugin_tutorial/_private/server/ServerEntryHook.py

  1. Add the following import up the top of the file

    from peek_plugin_tutorial._private.storage import DeclarativeBase
    from peek_plugin_tutorial._private.storage.DeclarativeBase import loadStorageTuples
    from peek_plugin_base.server.PluginServerStorageEntryHookABC import PluginServerStorageEntryHookABC
    
  2. Add PluginServerStorageEntryHookABC to the list of classes “ServerEntryHook” inherits

    class ServerEntryHook(PluginServerEntryHookABC, PluginServerStorageEntryHookABC):
    
  3. Add the following method from the load(self): method

    def load(self) -> None:
        loadStorageTuples() # <-- Add this line
        logger.debug("Loaded")
    
  4. Implement the dbMetadata(self): property

    @property
    def dbMetadata(self):
        return DeclarativeBase.metadata
    

When you’re finished, You should have a file like this:

# Added imports, step 1
from peek_plugin_tutorial._private.storage import DeclarativeBase
from peek_plugin_tutorial._private.storage.DeclarativeBase import loadStorageTuples
from peek_plugin_base.server.PluginServerStorageEntryHookABC import \
    PluginServerStorageEntryHookABC


# Added inherited class, step2
class ServerEntryHook(PluginServerEntryHookABC, PluginServerStorageEntryHookABC):


    def load(self) -> None:
        # Added call to loadStorageTables, step 3
        loadStorageTuples()
        logger.debug("Loaded")

    # Added implementation for dbMetadata, step 4
    @property
    def dbMetadata(self):
        return DeclarativeBase.metadata

Edit File ClientEntryHook.py

This step applies if you’re plugin is using the Client service.

The ClientEntryHook.py file needs to be updated to do the following:

  • Ensure that the storage Tables are loaded on plugin load.

Edit the file peek_plugin_tutorial/_private/client/ClientEntryHook.py

  1. Add the following import up the top of the file

    from peek_core_device._private.storage.DeclarativeBase import loadStorageTuples
    
  2. Add the following method from the load(self): method

    def load(self) -> None:
        loadStorageTuples() # <-- Add this line
        logger.debug("Loaded")
    

When you’re finished, You should have a file like this:

# Added imports, step 1
from peek_core_device._private.storage.DeclarativeBase import loadStorageTuples

...

    def load(self) -> None:
        # Added call to loadStorageTables, step 2
        loadStorageTuples()
        logger.debug("Loaded")

Edit File AgentEntryHook.py

This step applies if you’re plugin is using the Agent service.

Edit file peek_plugin_tutorial/_private/agent/AgentEntryHook.py file, apply the same edits from step Edit File ClientEntryHook.py.

Edit File WorkerEntryHook.py

This step applies if you’re plugin is using the Worker service.

Edit file peek_plugin_tutorial/_private/worker/WorkerEntryHook.py file, apply the same edits from step Edit File ClientEntryHook.py.

Add File alembic.ini

The alembic.ini file is the first file Alembic laods, it tells Alembic how to connect to the database and where it’s “alembic” directory is.


Create a file peek_plugin_tutorial/_private/alembic.ini and populate it with the following contents, make sure to update the sqlalchemy.url line.

Note

The database connection string is only used when creating database upgrade scripts.

MS Sql Server:mssql+pymssql://peek:PASSWORD@127.0.0.1/peek
PostGreSQL:postgresql://peek:PASSWORD@127.0.0.1/peek
[alembic]
script_location = alembic
sqlalchemy.url = postgresql://peek:PASSWORD@127.0.0.1/peek

Finally, run the peek server, it should load with out error.

The hard parts done, adding the tables is much easier.

Adding a StringInt Table

This section adds a simple table, For lack of a better idea, lets have a table of strings and Integers.

Add File StringIntTuple.py

The StringIntTuple.py python file defines a database Table class. This database Table class describes a table in the database.

Most of this is straight from the SQLAlchemy Object Relational Tutorial


Create the file peek_plugin_tutorial/_private/storage/StringIntTuple.py and populate it with the following contents.

from sqlalchemy import Column
from sqlalchemy import Integer, String
from vortex.Tuple import Tuple, addTupleType

from peek_plugin_tutorial._private.PluginNames import tutorialTuplePrefix
from peek_plugin_tutorial._private.storage.DeclarativeBase import DeclarativeBase


@addTupleType
class StringIntTuple(Tuple, DeclarativeBase):
    __tupleType__ = tutorialTuplePrefix + 'StringIntTuple'
    __tablename__ = 'StringIntTuple'

    id = Column(Integer, primary_key=True, autoincrement=True)
    string1 = Column(String(50))
    int1 = Column(Integer)

The remainder is from VortexPY, which allows the object to be serialised, and reconstructed as the proper python class. VortexPY is present in these three lines

@addTupleType
class StringIntTuple(Tuple, DeclarativeBase):
    __tupleType__ = tutorialTuplePrefix + 'StringIntTuple'

Edit File storage/DeclarativeBase.py

Add imports to our load storage tuples method. Load the tuples for Alembic to generate database update script. Allows vortex to reconstruct the tuples.


Edit the file peek_plugin_tutorial/_private/storage/DeclarativeBase.py

  1. Add the lines to the loadStorageTuples(): method

    from . import StringIntTuple
    StringIntTuple.__unused = False
    

Create New Alembic Version

Now we need create a database upgrade script, this allows Peek to automatically upgrade the plugins schema. Peek uses Alembic to handle this.

Read more about Alembic here

Alembic will load the schema from the database, then load the schema defined by the SQLALchemy Table classes.

Alembic then works out the differences and create an upgrade script. The upgrade script will modify the database to match the schema defined by the python SQLAlchemy Table classes.


  1. Open a bash window

  2. CD to the _private directory of the plugin

    # Root dir of plugin project
    cd peek-plugin-tutorial
    
    # CD to where alembic.ini is
    cd peek_plugin_tutorial/_private
    
  3. Run the alembic upgrade command.

    alembic revision --autogenerate -m "Added StringInt Table"
    

    it should look like

    peek@peek:~/project/peek-plugin-tutorial/peek_plugin_tutorial/_private$ alembic revision --autogenerate -m "Added StringInt Table"
    LOAD TABLES
    19-Mar-2017 20:59:42 INFO alembic.runtime.migration:Context impl PostgresqlImpl.
    19-Mar-2017 20:59:42 INFO alembic.runtime.migration:Will assume transactional DDL.
    19-Mar-2017 20:59:42 INFO alembic.autogenerate.compare:Detected added table 'pl_tutorial.StringIntTuple'
    /home/peek/cpython-3.5.2/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py:2705: SAWarning: Skipped unsupported reflection of expression-based index place_lookup_name_idx
      % idx_name)
    /home/peek/cpython-3.5.2/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py:2705: SAWarning: Skipped unsupported reflection of expression-based index countysub_lookup_name_idx
      % idx_name)
    /home/peek/cpython-3.5.2/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py:2705: SAWarning: Skipped unsupported reflection of expression-based index county_lookup_name_idx
      % idx_name)
    /home/peek/cpython-3.5.2/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py:2705: SAWarning: Skipped unsupported reflection of expression-based index idx_tiger_featnames_lname
      % idx_name)
    /home/peek/cpython-3.5.2/lib/python3.5/site-packages/sqlalchemy/dialects/postgresql/base.py:2705: SAWarning: Skipped unsupported reflection of expression-based index idx_tiger_featnames_snd_name
      % idx_name)
      Generating /home/peek/project/peek-plugin-tutorial/peek_plugin_tutorial/_private/alembic/versions/6c3b8cf5dd77_added_stringint_table.py ... done
    
  4. Now check that Alembic has added a new version file in the peek_plugin_tutorial/_private/alembic/versions directory.

Tip

You can add any kind of SQL you want to this script, if you want default data, then this is the place to add it.


Now the database needs to be upgraded, run the upgrade script created in the last step, with the following command:

alembic upgrade head

You should see output similar to:

peek@peek MINGW64 ~/peek-plugin-tutorial/peek_plugin_tutorial/_private
$ alembic upgrade head
21-Mar-2017 02:06:27 INFO alembic.runtime.migration:Context impl PostgresqlImpl.
21-Mar-2017 02:06:27 INFO alembic.runtime.migration:Will assume transactional DDL.
21-Mar-2017 02:06:27 INFO alembic.runtime.migration:Running upgrade  -> 0b12f40fadba, Added StringInt Table
21-Mar-2017 02:06:27 DEBUG alembic.runtime.migration:new branch insert 0b12f40fadba

Adding a Settings Table

The Noop plugin has special Settings and SettingsProperty tables that is usefully for storing plugin settings.

This section sets this up for the Tutorial plugin. It’s roughly the same process used to Adding a StringInt Table.

Add File Setting.py

Download the Setting.py file to peek_plugin_tutorial/_private/storage from https://bitbucket.org/synerty/peek-plugin-noop/raw/master/peek_plugin_noop/_private/storage/Setting.py


Edit peek_plugin_tutorial/_private/storage/Setting.py

  1. Find peek_plugin_noop and replace it with peek_plugin_tutorial.
  2. Find noopTuplePrefix and replace it with tutorialTuplePrefix.

Edit File DeclarativeBase.py

Edit peek_plugin_tutorial/_private/storage/DeclarativeBase.py

Add the following lines to the loadStorageTuples(): method

from . import Setting
Setting.__unused = False

Create New Alembic Version

Open a bash window, run the alembic upgrade

# Root dir of plugin project
cd peek-plugin-tutorial/peek_plugin_tutorial/_private

# Run the alembic command
alembic revision --autogenerate -m "Added Setting Table"

Note

Remember to check the file generated, and add it to git.


Run the upgrade script created in the last step with the following command:

alembic upgrade head

Settings Table Examples

Here is some example code for using the settings table.

Note

This is only example code, you should not leave it in.


Edit the file peek_plugin_tutorial/_private/server/ServerEntryHook.py

Add the following import up the top of the file:

from peek_plugin_pof_events._private.storage.Setting import globalSetting, PROPERTY1

To Place this code in the start(): method:

# session = self.dbSessionCreator()
#
# # This will retrieve all the settings
# allSettings = globalSetting(session)
# logger.debug(allSettings)
#
# # This will retrieve the value of property1
# value1 = globalSetting(session, key=PROPERTY1)
# logger.debug("value1 = %s" % value1)
#
# # This will set property1
# globalSetting(session, key=PROPERTY1, value="new value 1")
# session.commit()
#
# session.close()