Customizing SlashDB API with declarative models

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

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

How it works

SlashDB loads manually defined model if database configuration has attribute autoload: false. Otherwise SlashDB reflects database by itself. This is defined in GUI /dbdef or in the backend in file /etc/slashdb/databases.cfg

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: []

When loading manually defined models SlashDB iterates through group of entry points named 'sqlalchemy_sdb_module'. Then looks for an entry point which, name matches the Database ID (in below example it's "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 your own plugin for SlashDB

Basic plugin example

mypluginroot
    car_db
    └── __init__.py
    setup.py

Important: The module name should reflect the actual Database ID used in SlashDB-s configuration.

cat custom_models/__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)

setup

cat 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

while in mypluginroot do:

(sudo) python setup.py install

Example API customizations using models

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 your 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))

and the resulting example 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 your 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)

and the resulting example 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'
    }
]