Customizing Database Models

You can easily modify your API with custom SQLAlchemy models and a mixin class provided by SlashDB.

All you need to do is declare your models and wrap them into a setuptools compatible module.

How It Works

SlashDB loads a manually defined model if the database configuration has the attribute autoload: false. Otherwise, SlashDB reflects the database automatically.

This attribute can be set from the database configuration screen (Auto Discover) or in the /etc/slashdb/databases.cfg file.

MyThings:
  alternate_key: {}
  autoload: false
  autoload_user: {dbuser: someUser, dbpass: somePassword}
  connect_status: Disconnected
  connection: /somepath/mythings.sqlite
  creator: admin
  db_encoding: utf-8
  db_id: MyThings
  db_schema: null
  db_type: sqlite
  desc: 'Sample database of my things'
  excluded_columns: {}
  execute: []
  foreign_keys: {}
  autoconnect: false
  owners: [admin]
  read: []
  write: []

Info

When loading manually defined models, SlashDB iterates through a group of entry points named sqlalchemy_sdb_module.

It then looks for an entry point whose name matches the Database ID (in the example below, "MyThings") and executes a function attached to the entry point.

This function takes the database ID, database config object and the initial settings object, returning a list of model classes that represent the database.

Creating a Plugin

Basic plugin example

File layout for a basic plugin:

mypluginroot
    car_db
    └── __init__.py
    setup.py

Important

The module name should reflect the actual Database ID used in the SlashDB configuration. E.g for a database with ID car_db, the module should be named car_db.

Contents of car_db/__init__.py:

import sys

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

from slashdb.models.sqlalchemy_sdb import Model

Base = declarative_base()


class Brand(Base, Model):
    u"""Simple declarative-style SQLAlchemy model."""
    __tablename__ = 'brands'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    cars = relationship('Car', backref='brand')


class Car(Base, Model):
    u"""Simple declarative-style SQLAlchemy model - with relations."""
    __tablename__ = 'cars'

    id = Column(Integer, primary_key=True)
    brand_id = Column(Integer, ForeignKey('brands.id'))


def get_models(db_id, db_config, ini_settings):
    """Returns list of database model classes for certain database.

    :param db_id: Database id which SlashDB is configuring.
    :param db_config: Database config detail object.
    :param db_config: Parsed *.ini file used to setup SlashDB.

    """
    # registration of classes is deferred
    # pick from the above classes the ones to expose
    return (Brand, Car)

Contents of setup.py:

#!/usr/bin/env python

from setuptools import setup

VERSION = '0.0.1'

setup(
    name='cardbplugin',
    version=VERSION,
    license='',
    description='My car database plugin for SlashDB',
    author='VTE',
    author_email='',
    url='',
    download_url='',
    keywords='cars database plugin slashdb',
    classifiers=[],
    platforms='All',
    install_requires=['slashdb>=0.9.0'],
    packages=['car_db'],
    entry_points={
        'sqlalchemy_sdb_module': ['CarDB=car_db:get_models']
    },
    zip_safe=False,
    tests_require=['nose >= 0.11'],
)

Installing your plugin

From the mypluginroot directory, execute:

(sudo) python setup.py install

Example Customizations

Adding new tables with relations:

from slashdb.models.sqlalchemy_sdb import Model
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Thing(Base, Model):
    u"""Simple declarative-style SQLAlchemy model."""
    __tablename__ = 'things'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    otherthings = relationship('OtherThing', back_populates='thing')


class OtherThing(Base, Model):
    u"""Other simple declarative-style SQLAlchemy model - with relations to Thing."""
    __tablename__ = 'otherthings'

    id = Column(Integer, primary_key=True)
    thing_id = Column(Integer, ForeignKey('things.id'))
    thing = relationship('Thing', back_populates='otherthings')

Hiding columns on an existing table:

from slashdb.models.sqlalchemy_sdb import Model
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
engine = create_engine('sqlite:///path/to/your/Chinook.sqlite'


class EmployeeWithHiddenFields(Base, Model):
    u"""Show only 'EmployeeId', 'FirstName', 'ReportsTo' columns."""
    __table__ = Table(
        'Employee', Base.metadata, include_columns=('EmployeeId', 'FirstName', 'ReportsTo'),
        autoload=True, autoload_with=engine, schema=Base.metadata.schema
    )
    __tablename__ = 'Employee'

Change how the SlashDB API displays column names in your custom models:

from slashdb.models.sqlalchemy_sdb import Model
from sqlalchemy import Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class AwesomeDeclarativeArtist(Base, Model):
    u"""A declarative model with aliased column names."""
    __tablename__ = 'awesome_artist'

    artist_ID = Column('id', Integer, primary_key=True)
    ArtistName = Column('artist_name', Unicode(120))

The resulting JSON output:

curl http://myslashdb/db/SQLiteChinook/AwesomeDeclarativeArtist.json

// id -> artist_ID
// artist_name -> artist_name
[
    {
        'artist_ID': 1,
        'ArtistName': 'Some Artist',
        '__href': '/db/test/AwesomeDeclarativeArtist/artist_ID/1.json'
    }
]

Change how the SlashDB API displays column names in existing tables:

from slashdb.models.sqlalchemy_sdb import Model
from sqlalchemy import Integer, Text, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
engine = create_engine('sqlite:///path/to/your/Chinook.sqlite')


class ArtistWithAliasedColumns(Base, Model):
    u"""Show only 'ArtistId', 'Name' and do some aliasing."""
    __tablename__ = 'Artist'
    __table_args__ = {
        'include_columns': ('ArtistId', 'Name'), 'extend_existing': True,
        'autoload': True, 'autoload_with': engine, 'schema': Base.metadata.schema
    }

    artist_id = Column('ArtistId', Integer, primary_key=True)
    artist_name = Column('Name', Text)

The resulting JSON output:

curl http://myslashdb/db/SQLiteChinook/ArtistWithAliasedColumns/artist_id/1..3.json

// ArtistId -> artist_id
// Name -> artist_name
[
    {
        'artist_id': 1,
        'artist_name': 'AC/DC',
        '__href': '/db/test/ArtistWithAliasedColumns/artist_id/1.json'
    },
    {
        'artist_id': 2,
        'artist_name': 'Accept',
        '__href': '/db/test/ArtistWithAliasedColumns/artist_id/2.json'
    },
    {
        'artist_id': 3,
        'artist_name': 'Aerosmith',
        '__href': '/db/test/ArtistWithAliasedColumns/artist_id/3.json'
    }
]