Persistence

Persistence

Let's keep this short: This page introduces how to use sqlalchemy within eiSoil.

Value objects

As mentioned in the GENI page, please make sure that your Resource Manager's methods take and return only value objects. This means, that all the data required to configure resources shall be in your own (proprietary) format. As consequence, there shall be no RPC-specific specification (e.g. RSpec) languages coming in or out of the Resource Manager.

Bootstrap

What we want is to get something that enables us to create tables (declarative_base) and something that manages a connection with the database so we can execute queries (session).
So, let's see what we need to work with a database:

    from sqlalchemy import create_engine
    from sqlalchemy.orm import scoped_session, sessionmaker, mapper
    from sqlalchemy.ext.declarative import declarative_base

    from eisoil.config import expand_eisoil_path

    # prepare some constants
    WORKERDB_PATH = expand_eisoil_path("deploy/my.db") # this gives you the path relative to eiSoil's root folder
    WORKERDB_ENGINE = "sqlite:///%s" % (WORKERDB_PATH,)
    # do initialization
    db_engine = create_engine(WORKERDB_ENGINE)
    db_session_factory = sessionmaker(autoflush=True, bind=db_engine, expire_on_commit=False)
    db_session = scoped_session(db_session_factory)
    Base = declarative_base()

What these things mean:

• engine (db_engine) It defines the location and type of database and it contains the metadata.
• metadata (db_engine.metadata) Contains all table definitions (see Create a table).
• session factory (db_session_factory) This an object which can be used to create a sessions (see factory pattern).
• scoped session (db_session) This still a session creator, but it will only create one session per thread and delegate all method calls to it (see below for usage).
• declarative base (declarative_base) This is a base class from which all table definitions need to derive from.

Note: This is only one way to do this, please see the More info section for other options.

Create a table

In order to save our data, we need a database table. For a database table to be created we need to tell SQLAlchemy what name and columns the table shall have:

    from sqlalchemy import Column, Integer, String, DateTime, create_engine
    from sqlalchemy.orm import scoped_session, sessionmaker, mapper
    
    class SomeEntry(Base):
        __tablename__ = 'my_table'
        id = Column(Integer, primary_key=True)
        value1 = Column(String)
        value2 = Column(Integer)
        value3 = Column(DateTime)
        
        # please add your additional methods here

    Base.metadata.create_all(db_engine) # create the tables if they are not there yet

In order to actually create the table consider the last line. You only need to call this once, but it doesn't hurt to call it multiple times (existing tables left as they are). Usually, you would put this line package level so it gets executed once when the package is imported.

Pitfall: If you make changes to the database's metadata (e.g. table definitions), the changes are performed for existing tables. The easiest way - while developing - is to remove the database file altogether and let the sqlalchemy recreate it. For production mode, you should think about handling migrations in a more sophisticated way.

For more supported Colum Types see the Generic Types page.

Query and changes

Now we have a table and we are ready to save some data:

    from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound
    
    # query all
    records = db_session.query(SomeEntry).all()
    for record in records:
      print record.value1
    # query with condition
    records = db_session.query(SomeEntry).filter(SomeEntry.value2 == 77).all()
    # query one and get error MultipleResultsFound or NoResultFound if there is not exactly one record
    record = db_session.query(SomeEntry).filter(SomeEntry.value3 == datetime.utcnow()).one()

    # create an entry
    e = SomeEntry(value1="ABC", value2=77) # a named-parameter constructor is the provided by the declarative_base class
    e.value3 = datetime.utcnow() # the e object is not related with the database yet (not saved anywhere)
    db_session.add(e) # now SQLAlchemy knows about the object and adds it to the session (no SQL yet)
    db_session.commit() # here the actual SQL gets executed
    # change an entry
    e.value1 = "CDE" # changes are not commited yet
    db_session.commit() # now the entry changed
    # delete an entry
    db_session.delete(e) # the entry is still in the database
    db_session.commit() # now it is gone.

Please notice, that db_session handles all the connection establishment for us. Since we use a scoped_session we do not have to care about creating the actual session and we have exactly one session per thread.

Objects in sessions

It is important to understand, that before we do add() the object will not be persisted on commit(). Please consider this example:

    e = SomeEntry(value1="ABC", value2=77)
    db_session.commit()   # the database is still empty
    db_session.add(e)
    db_session.commit()   # now the entry is in the database
    e.value1 = "CDE"      # the database still has "ABC"
    db_session.commit()   # now it's "CDE"
    db_session.expunge(e) # let's remove e from the session
    e.value1 = "FGH"
    db_session.commit()   # it is still "CDE" because we removed e from the session before.

Expunge

Consider the following scenario: Your Resource Manager queries an object from the database and returns it to the method caller. Not the caller changes an attribute on the retuned object (recordA). Then the caller instructs the Resource Manager to perform some other task and recordB is changed. In order to change recordB, the Resource Manager commits. Now, recordA is also changed because it was still part of the session. Therefore, always expunge the objects you return to the caller, because only the Resource Manager should deal with database management (If you are lazy you can also call expunge_all()). When receiving an expunged object from the caller, look it up in the database and change that object:

    class MyResourceManager(object):
      # ...
      def get_the_resource(uuid):
        record = db_session.query(SomeEntry).filter(SomeEntry.uuid == uuid).one()
        db_session.expunge(record) # remove from the session, so the user can not change attributes
        return record
      
      def extend_the_resource(some_entry, new_time):
        record = db_session.query(SomeEntry).filter(SomeEntry.id == some_entry.id).one() # get a record representation within the session
        record.value3 = new_time
        db_session.commit()
        db_session.expunge(record)
        return record

More info

If this was a to fast or not enough please read the SQLAlchemy pages: ORM Tutorial (including the part about scoped_sessions).

If you are a little more hard-core (or a control freak) read SQL Expression Tutorial.