Models

Models (database objects, schemas, mapping) are sadly in flux. But the flux is towards using SQLAlchemy.

New Modeling

New modeling is powered by SQLAlchemy, which has lots of great documentation. The goal is to have the database operations taken care of (to whatever extent possible) by SQLAlchemy, and building our models on top of its ORM layer.

Object Mapping

Currently object mapping is defined in giveaminute/models.py.

Transitional Object Mapping

In order to map to old models that are already defined in the database, the following sort of definition will create the object mapping:

class Project (Base):
    __tablename__ = 'project'
    __table_args__ = {'autoload': True}

Though this type of mapping (autoload) is convenient, it requires connecting to the database when the module is loaded and the classes are created. This inhibits out ability to do things like use a test database for integration tests. It is better to explicitly list the fields in a given model's table, and not rely on autoload in this instance. For example:

class Place (Base):
    __tablename__ = 'project_place'

    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    street = Column(String(256))
    city = Column(String(256))

For more examples, see the giveaminute/models.py module, or the SQLAlchemy documentation.

Install Schema

See installation (Mac or Ubuntu) for details. But overall, run the following to get the schema installed:

python giveaminute/models.py

Migrations

See the Data and Schema Migrations information for details, but generally, run:

python manage.py upgrade

Querying

Each instance of the Controller class has a property named orm that contains the SQLAlchemy ORM session object. Using this object, we can query for instances of a given model (from within a Controller) like:

orm = self.orm
instances = session.query(ModelName).all()

If you are not querying from within the Controller class, you can get the SQLAlchemy session directly from the OrmHolder:

from framework.orm_holder import OrmHolder

orm = OrmHolder().orm
instances = session.query(ModelName).all()

This is the same ORM session that is used by Controller. It is stored on the web.ctx object, which is persistent in a web.py session.

Old Modeling

Schema

Schemas are actual SQL table definitions found in sql/models.sql. (There are some updates in sql/migrations/)

Updating Schema

To create a new models.sql, use a command similar to:

mysqldump --no-data -u USER_NAME -p DB_NAME | sed 's/\(.*ENGINE.*AUTO_INCREMENT=\).*/\10;/g' > sql/models-temp.sql

Make sure to create migration files in sql/migrations/ and update CHANGLOG.txt and UPGRADE.txt.

Object Modeling

Object modeling happens in multiple files in the giveaminute/ directory. There is a file for each major object.

Querying

Querying is done with web.py's database methods. For instance:

sql = 'select * from user where user_id = $userId'
data = db.query(sql, { 'userId': some_id })

The db object is created in the base controller (framework/controller.py) which is passed around, or retrieved with Controller.get_db().