'''
Base class for sqlalchemy
'''
[docs]__author__ = 'Elisha Yadgaran'
import logging
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, DateTime, func, event, MetaData, DDL
from sqlalchemy_mixins import AllFeaturesMixin
[docs]Base = declarative_base()
[docs]LOGGER = logging.getLogger(__name__)
[docs]class BaseSQLAlchemy(Base, AllFeaturesMixin):
'''
Base class for all SimpleML database objects. Defaults to PostgreSQL
but can be swapped out for any supported SQLAlchemy backend.
Takes advantage of sqlalchemy-mixins to enable active record operations
(TableModel.save(), create(), where(), destroy())
Added some inheritable convenience methods
-------
Schema
-------
created_timestamp: Server time on insert
modified_timestamp: Server time on update
'''
[docs] created_timestamp = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
[docs] modified_timestamp = Column(DateTime(timezone=True), server_onupdate=func.now())
@classmethod
[docs] def filter(cls, *filters):
return cls._session.query(cls).filter(*filters)
@classmethod
[docs] def query_by(cls, *queries):
return cls._session.query(*queries)
@event.listens_for(BaseSQLAlchemy, 'before_update', propagate=True)
[docs]def _receive_before_update(mapper, connection, target):
"""Listen for updates and update `modified_timestamp` column."""
target.modified_timestamp = func.now()
'''
Metadata bases specific to each session (subclasses represent tables affected by the
same session -- ie base.metadata.create_all()/drop_all()/upgrade())
'''
[docs]class SimplemlCoreSqlalchemy(BaseSQLAlchemy):
'''
Shared metadata for all tables that live in the main schema
'''
# Uses main (public) schema
[docs]class BinaryStorageSqlalchemy(BaseSQLAlchemy):
'''
Shared metadata for all tables that live in the binary storage schema
'''
# Store binary data in its own schema
@event.listens_for(metadata, 'before_create', propagate=True)
[docs] def _receive_before_create(target, connection, **kwargs):
"""
Listen for and creates a new schema for datasets
"""
# SQLite supports schemas as "Attached Databases"
# https://www.sqlite.org/lang_attach.html
if connection.dialect.name == 'postgresql':
LOGGER.debug('Issuing create schema if not exists')
DDL('''CREATE SCHEMA IF NOT EXISTS "{}";'''.format(target.schema)).execute(connection)
# elif connection.dialect.name == 'sqlite':
# raise NotImplementedError('SQLite does not support multiple schemas right now')
# TODO: Figure out a mechanism to pass in a dynamic schema so testing
# doesnt mess with existing ones
# DDL('''ATTACH DATABASE "{}";'''.format(target.schema))
else:
raise NotImplementedError('Schemas not supported on {dialect}'.format(dialect=connection.dialect.name))
[docs]class DatasetStorageSqlalchemy(BaseSQLAlchemy):
'''
Shared metadata for all tables that live in the dataset storage schema
'''
# Use different schemas/databases for storage optionality (dataframes are big in size)
@event.listens_for(metadata, 'before_create', propagate=True)
[docs] def _receive_before_create(target, connection, **kwargs):
"""
Listen for and creates a new schema for datasets
"""
# SQLite supports schemas as "Attached Databases"
# https://www.sqlite.org/lang_attach.html
if connection.dialect.name == 'postgresql':
LOGGER.debug('Issuing create schema if not exists')
DDL('''CREATE SCHEMA IF NOT EXISTS "{}";'''.format(target.schema)).execute(connection)
# elif connection.dialect.name == 'sqlite':
# raise NotImplementedError('SQLite does not support multiple schemas right now')
# TODO: Figure out a mechanism to pass in a dynamic schema so testing
# doesnt mess with existing ones
# DDL('''ATTACH DATABASE "{}";'''.format(target.schema))
else:
raise NotImplementedError('Schemas not supported on {dialect}'.format(dialect=connection.dialect.name))