# MYSQL sessions

A MYSQL database will be created for every application that gets installed in a
portal. The database name will be of the following format

    <provider-code>_<app-code>

For example, an application with provider code 'aalam' and app code 'base' the
database name will be `aalam_base`. The database name of the application be
obtained from the api

    aalam_common.sqa.get_database_name()

These databases are unique for the application. No application can access the
database of another application. The database connection details can be in the
.ini config file which can accessed from `aalam_common.config.cfg`. The database
connection will be present in `sqlalchemy` section under the name `url`.

See [configs](configs.md)

SQLAlchemy
----------
This framework uses [sqlalchemy](http://docs.sqlalchemy.org/en/latest/index.html)
for managing relational database access. It is not mandatory for the application
to use sqlalchemy. We *might* use other relational databases in the future.
So, by using SQLAlchemy, it offers tremendous flexibility in such cases.

SQLAlchemy is widely used and there are lots of sqlalchemy questions answered
in sites like stackoverflow.


Engine Initialization
---------------------
If an application chooses to use sqlalchemy, it should initialize the database
[engine][].

The database engine is created automatically by 

    aalam_common.sqa.init_engine(db_name, declarative_base)

Parameters `db_name` is the database name, `declarative_base` is the [base][]
on which the table classes are defined. The declarative base should be
instantiated by the application before declaring the tables, for example 

```
import aalam_common

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class TableName1(Base):
    __tablename__ = "table-1"
    __table_args__ = {"schema": aalam_common.sqa.get_database_name()}

    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.VARCHAR(255), nullable=False)
```

The init_engine should be called before accessing any session. Application's
`entry_point` is the ideal place to call `init_engine`.

[engine]: http://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=engine#module-sqlalchemy.engine
[base]: http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html

Sessions
--------
For every request a
[scoped session](http://docs.sqlalchemy.org/en/latest/orm/contextual.html) will
be instantiated. The session can be accessed from the request object with the 
attribute sqa_session, like

    request.sqa_session

After every request ends, the framework is responsible to cleanup the session.
If there are an pending transactions in the session after the request, based
upon the response status code of the handler, the frameworks commits or
disposes the transactions.

If the status set by the handler is in the range of `200 - 399`, the transactions
will be commited. Whereas the transaction will be rolled back if the status
code is in the range of `400 - 599`.


New session
-----------
If an application desires to use a session outside the scope of a request, it
can do so by calling `aalam_common.sqa.Session(db_name)`.
If a session is used in this manner the client of this session is responsible
in cleaning it up. After the session is used, the client must issue
`session.remove()` to close the connection after commit/rollback of the session.