Database

The clld database models are declared using SQLAlchemy’s declarative extension. In particular we follow the approach of mixins and custom base class, to provide building blocks with enough shared commonality for custom data models.

Declarative base and mixins

class clld.db.meta.Base(jsondata=None, **kwargs)

The declarative base for all our models.

classmethod get(value, key=None, default=<NoDefault>, session=None)

Convenience method to query a model where exactly one result is expected.

e.g. to retrieve an instance by primary key or id.

Parameters:

• value – The value used in the filter expression of the query.
• key (str) – The key or attribute name to be used in the filter expression. If None is passed, defaults to pk if value is int otherwise to id.

history()

return result proxy to iterate over previous versions of a record.

jsondata = Column(None, JSONEncodedDict(), table=None)

To allow storage of arbitrary key,value pairs with typed values, each model provides a column to store JSON encoded dicts.

jsondatadict

Deprecated convenience function.

Use jsondata directly instead, which is guaranteed to be a dictionary.

pk = Column(None, Integer(), table=None, primary_key=True, nullable=False)

All our models have an integer primary key which has nothing to do with the kind of data stored in a table. ‘Natural’ candidates for primary keys should be marked with unique constraints instead. This adds flexibility when it comes to database changes.

update_jsondata(**kw)

Convenience function.

Since we use the simple JSON encoded dict recipe without mutation tracking, we provide a convenience method to update

class clld.db.meta.CustomModelMixin

Mixin for customized classes in our joined table inheritance scheme.

Note

With this scheme there can be only one specialized mapper class per inheritable base class.

class clld.db.models.common.IdNameDescriptionMixin

Mixin for ‘visible’ objects, i.e. anything that has to be displayed.

In particular all Resources fall into this category.

Note

Only one of clld.db.models.common.IdNameDescriptionMixin.description or clld.db.models.common.IdNameDescriptionMixin.markup_description should be supplied, since these are used mutually exclusively.

description = Column(None, Unicode(), table=None)

A description of the object.

id = Column(None, String(), table=None)

A str identifier of an object which can be used for sorting and as part of a URL path; thus should be limited to characters valid in URLs, and should not contain ‘.’ or ‘/’ since this may trip up route matching.

markup_description = Column(None, Unicode(), table=None)

A description of the object containing HTML markup.

name = Column(None, Unicode(), table=None)

A human readable ‘identifier’ of the object.

While the above mixin only adds columns to a model, the following mixins do also add relations between models, thus have to be used in combination, tied together by naming conventions.

class clld.db.models.common.DataMixin

Provide a simple way to attach key-value pairs to a model class given by name.

class clld.db.models.common.HasDataMixin

Adds a convenience method to retrieve the key-value pairs from data as dict.

Note

It is the responsibility of the programmer to make sure conversion to a dict makes sense, i.e. the keys in data are actually unique, thus usable as dictionary keys.

datadict()

return dict of associated key-value pairs.

class clld.db.models.common.FilesMixin

This mixin provides a way to associate files with instances of another model class.

Note

The file itself is not stored in the database but must be created in the filesystem, e.g. using the create method.

create(dir_, content)

Write content to a file using dir_ as file-system directory.

Returns:File-system path of the file that was created.

mime_type = Column(None, String(), table=None)

Mime-type of the file content.

ord = Column(None, Integer(), table=None, default=ColumnDefault(1))

Ordinal to control sorting of files associated with one db object.

relpath

OS file path of the file relative to the application’s file-system dir.

class clld.db.models.common.HasFilesMixin

Mixin for model classes which may have associated files.

files

return dict of associated files keyed by id.

Typical usage looks like

class MyModel_data(Base, Versioned, DataMixin):
    pass

class MyModel_files(Base, Versioned, FilesMixin):
    pass

class MyModel(Base, HasDataMixin, HasFilesMixin):
    pass

Core models

The CLLD data model includes the following entities commonly found in linguistic databases and publications:

class clld.db.models.common.Dataset(jsondata=None, **kwargs)

Represents a database.

Each project (e.g. WALS, APiCS) is regarded as one dataset; thus, each app will have exactly one Dataset object.

pk

primary key

published

date of publication

publisher_name

publisher

publisher_place

place of publication

class clld.db.models.common.Language(jsondata=None, **kwargs)

Languages are the main objects of discourse.

We attach a geo-coordinate to them to be able to put them on maps.

latitude

geographical latitude in WGS84

longitude

geographical longitude in WGS84

pk

primary key

class clld.db.models.common.Parameter(jsondata=None, **kwargs)

A measurable attribute of a language.

pk

primary key

class clld.db.models.common.ValueSet(jsondata=None, **kwargs)

The intersection of Language, Parameter, and optionally Contribution.

pk

primary key

source

textual description of the source for the valueset

class clld.db.models.common.Value(jsondata=None, **kwargs)

A measurement of a parameter for a particular language.

confidence

textual assessment of the reliability of the value assignment

frequency

Languages may have multiple values for the same parameter. Their relative frequency can be stored here.

class clld.db.models.common.Contribution(jsondata=None, **kwargs)

A set of data contributed within the same context by the same contributors.

pk

primary key

class clld.db.models.common.Contributor(jsondata=None, **kwargs)

Creator of a contribution.

pk

primary key

class clld.db.models.common.Source(jsondata=None, **kwargs)

A bibliographic record, cited as source for some statement.

pk

primary key

class clld.db.models.common.Unit(jsondata=None, **kwargs)

A linguistic unit of a language.

pk

primary key

class clld.db.models.common.UnitParameter(jsondata=None, **kwargs)

A measurable attribute of a unit.

pk

primary key

class clld.db.models.common.UnitValue(jsondata=None, **kwargs)

pk

primary key

validate_parameter_pk(key, unitparameter_pk)

Validator to sync related parameter.

We have to make sure, the parameter a value is tied to and the parameter a possible domainelement is tied to stay in sync.

Versioning

Versioned model objects are supported via the clld.db.versioned.Versioned mixin, implemented following the corresponding SQLAlchemy ORM Example.

Migrations

Migrations provide a mechanism to update the database model (or the data) in a controlled and repeatable way. clld apps use Alembic to implement migrations.

Since a migration may change the database schema, it is generally not possible to fully use ORM mechanisms in migration scripts. Instead, migration scripts typically construct SQL to be sent to the database “by hand”, or using SQLAlchemy’s SQL expression language. Now dropping down to these lower levels of database access makes scripts verbose and error prone. Thus, clld provides a module with helpers for Alembic migration scripts.

Functionality for alembic scripts.

This module provides

• basic crud functionality within alembic migration scripts,
• advanced helpers for special tasks, like merging sources.

Note

Using the functionality provided in this module is not possible for Alembic scripts supposed to be run in offline mode.

class clld.db.migration.Connection(conn)

A wrapper around an SQLAlchemy connection.

This wrapper provides the convenience of allowing typical CRUD operations to be called passing model classes.

Additionally, it implements more complicated clld domain specific database operations.

A Connection will typically be instantiated in an Alembic migration script as follows:

from alembic import op
conn = Connection(op.get_bind())

all(model, **where)

return all results of a select statement.

delete(model, **where)

Run a delete statement.

execute(*args, **kw)

Provide access to the underlying connection’s execute method.

first(model, **where)

return first result of a select statement or None.

get(model, pk)

return row specified by primary key.

insert(model, **values)

Run an insert statement.

Returns:primary key of the inserted row.

pk(model, id_, attr='id')

Get the primary key of an object specified by a unique property.

Parameters:

• model – model class.
• id – Value to be used when filtering.
• attr – Column to be used for filtering.

Returns:

primary key of (first) matching row.

select(model, **where)

Run a select statement and return a ResultProxy.

set_glottocode(lid, gc, gcid=None)

assign a unique glottocode to a language.

i.e. alternative glottocodes will be deleted.

Parameters:

• lid – id of the language.
• gc – Glottocode to be assigned.
• gcid – id of the Identifier instance if one has to be created; defaults to gc.

update(model, values, **where)

Run an update statement.