query.py

from sqlalchemy import Column, Integer, ForeignKey, String, DateTime, desc
from sqlalchemy.orm import relationship, joinedload
from base import Base
from user import User  # noqa


class Query(Base):
    __tablename__ = 'query'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    title = Column(String(1024))
    last_touched = Column(DateTime)
    parent_id = Column(Integer)
    latest_rev_id = Column(Integer, ForeignKey('query_revision.id'))

    # Stick with "rev" as common language.
    revs = relationship('QueryRevision', lazy='dynamic', primaryjoin='Query.id == QueryRevision.query_id', backref='query')
    latest_rev = relationship('QueryRevision', primaryjoin='Query.latest_rev_id == QueryRevision.id', uselist=False)


class QueryRepository:
    def __init__(self, session):
        self.session = session

    def save(self, query):
        self.session.add(query)

        # Persist the new user immediately.
        self.session.commit()

    def get_latest(self, limit):
        # Eagerly load the associated query revision, query, and user.
        return self.session.query(Query).limit(limit)

    def get_by_id(self, id):
        return self.session.query(Query).filter_by(id=id).first()

queryrevision.py

from sqlalchemy import Column, Integer, String, ForeignKey, DateTime, desc
from sqlalchemy.orm import relationship
from base import Base
from query import Query  # noqa


class QueryRevision(Base):
    __tablename__ = 'query_revision'

    id = Column(Integer, primary_key=True)
    text = Column(String(4096))
    query_id = Column(Integer, ForeignKey('query.id'))
    timestamp = Column(DateTime)
    latest_run_id = Column(Integer, ForeignKey('query_run.id'))

    latest_run = relationship('QueryRun', primaryjoin='QueryRevision.latest_run_id == QueryRun.id', uselist=False)
    def is_allowed(self):
        """Check if given SQL is ok to execute.
        Super minimal and stupid right now, and should never
        be considered 'authoritative'. Will probably always be
        easily cirumventible by dedicated trolls, but should keep
        the merely clueless out

        returns tuple of (actual_reason, public_reason_string)
        """
        if 'information_schema' in self.text.lower():
            # According to springle hitting this db can fuck
            # things up for everyone, and it isn't easy to
            # restrict access to this from mysql
            return ("Hitting information_schema", "Unauthorized access to restricted database")
        return True


class QueryRevisionRepository:
    def __init__(self, session):
        self.session = session

    def save(self, query_revision):
        self.session.add(query_revision)

        # Persist the query revision immediately.
        self.session.commit()

    def get_latest_by_query(self, query):
        return self.session.query(QueryRevision) \
            .filter_by(query_id=query.id) \
            .order_by(desc(QueryRevision.timestamp)) \
            .first()

queryrun.py

from sqlalchemy import Column, Integer, ForeignKey, DateTime, String, desc
from sqlalchemy.orm import joinedload
from base import Base
from queryrevision import QueryRevision  # noqa


class QueryRun(Base):
    STATUS_QUEUED = 0
    STATUS_FAILED = 1
    STATUS_RUNNING = 2
    STATUS_KILLED = 3
    STATUS_COMPLETE = 4
    STATUS_SUPERSEDED = 5

    # TODO (phuedx, 2014/08/08): Make this translatable.
    STATUS_MESSAGES = [
        'queued',
        'failed',
        'running',
        'killed',
        'complete',
        'superseded'
    ]

    __tablename__ = 'query_run'

    id = Column(Integer, primary_key=True)
    query_rev_id = Column(Integer, ForeignKey('query_revision.id'))
    status = Column(Integer)
    timestamp = Column(DateTime)
    task_id = Column(String)

    @property
    def status_message(self):
        return QueryRun.STATUS_MESSAGES[self.status]

    # Stick with "augmented" as common language.
    @property
    def augmented_sql(self):
        return "/* Run by Quarry for User %s as qrun id %s */ %s" % (
            self.rev.query.user.username,
            self.id,
            self.rev.text
        )


class QueryRunRepository:
    def __init__(self, session):
        self.session = session

    def get_latest_by_rev(self, rev):
        if rev is None:
            return None

        return self.session.query(QueryRun).filter_by(query_rev_id=rev.id).first()

    def save(self, query_run):
        self.session.add(query_run)

        # Persist the query run immediately.
        self.session.commit()

    def get_latest(self, limit):
        # Eagerly load the associated query revision, query, and user.
        return self.session.query(QueryRun) \
            .options(
                joinedload('rev')
                .joinedload('query')
                .joinedload('user')
            ) \
            .filter(QueryRun.status != QueryRun.STATUS_SUPERSEDED) \
            .order_by(desc(QueryRun.timestamp)) \
            .limit(limit)

    def get_by_id(self, id):
        return self.session.query(QueryRun).filter_by(id=id).first()