It looks like Beaker supports two database backends, ext:database and ext:sqla, but what is the difference between them?
Answer: The main difference between SQL and MS SQL is that SQL is a query language that is used in relation databases whereas MS SQL Server is itself a relational database management system (RDBMS) developed by Microsoft.
SQL is the most common programming language used to interact with databases on the back-end. It is a standard back-end language used to create and maintain relational databases.
A back-end database is a database that is accessed by users indirectly through an external application rather than by application programming stored within the database itself or by low level manipulation of the data (e.g. through SQL commands).
SQL, or Structured Query Language, is used to manage data found on a database. MySQL is an open source data management system that's widely used in Back End development. There are other Back End languages, such as Java or ASP.NET, that are used in different industries.
Behind the simple two-field window, there is a complex process of storing, retrieving, and changing user data. All these operations are possible with the knowledge of the Structured Query Language (SQL), an essential tool for every backend developer.
Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments.
Looking at the source code (Beaker-1.6.4-py2.7.egg), the backend initialization is different and the database schema are slightly different.
The key difference seems to be whether you want to use a preexisting SQLAlchemy connection (ext:sqla) or create a brand new connection (ext:database).
Additionally, ext:database can wholly be configured within ini configuration files while ext:sqla cannot.
In the configuration file, ext:database needs at least session.url
defined to point at the database. You can specify session.table_name
to point at the table (if you used something other than the default beaker_cache) as well as session.schema_name
if you like to toy with extra settings. Finally session.sa_opts
may be specified with a dictionary of options for the SQLAlchemy engine.
ext:sqla only needs a bind object (SQLAlchemy Engine or Connection object) and a bound SQLAlchemy Table object. It is easy enough to dynamically set these values when calling Pyramid's Configurator. Since the configuration file can only accept strings, neither ext:sqla field can be set in an ini configuration file.
The table schema are ever so slightly different as well. ext:database schema followed by ext:sqla schema:
cache = sa.Table(table_name, meta,
sa.Column('id', types.Integer, primary_key=True),
sa.Column('namespace', types.String(255), nullable=False),
sa.Column('accessed', types.DateTime, nullable=False),
sa.Column('created', types.DateTime, nullable=False),
sa.Column('data', types.PickleType, nullable=False),
sa.UniqueConstraint('namespace'),
schema=schema_name if schema_name else meta.schema
)
sa.Table(table_name, metadata,
sa.Column('namespace', sa.String(255), primary_key=True),
sa.Column('accessed', sa.DateTime, nullable=False),
sa.Column('created', sa.DateTime, nullable=False),
sa.Column('data', sa.PickleType, nullable=False),
schema=schema_name if schema_name else metadata.schema)
The ext:database schema will error if used as-is, because id needs to have a default value. In Postgres, simply create the type as Serial instead of Integer to automatically generate default values.
The ext:sqla is a complete subset of the ext:database schema, even though the Primary Keys are different. The PK for ext:sqla is namespace, but since the schema for ext:database makes namespace UNIQUE and NOT NULL, all requirements are met to treat it as a primary key. It would make sense to always implement the ext:database schema in case one wishes to change between ext:sqla and ext:database. ext:sqla makes use of auto-pickling by using SQLAlchemy PickleType for the data column. Creating the table in the backend by hand, rather than allowing ext:sqla to create it, seems to prevent this auto-pickling from taking place.
Put something like this into the config file:
sqlalchemy.url = postgresql://[email protected]/particulardb
...
session.type = ext:database
session.url = postgresql://[email protected]/particulardb
Even though the ext:database session.url and sqlalchemy.url are the same database, two connections will be made from the Pyramid instance.
ext:sqla will rectify the creation of two connections; once the sqlalchemy.url is bound to a SQLAlchemy Engine, that Engine may be used by ext:sqla instead of creating a new connection.
I'd think this is a common enough use case (ext:sqla SQLAlchemy Engine = pyramid SQLAlchemy Engine) to have special handling in the static config file. If such special handling exists, I haven't found it.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With