--- /dev/null
+import datetime as D
+
+import sqlalchemy as A
+__import__('sqlalchemy', fromlist = ['orm'])
+__import__('sqlalchemy.ext',
+ fromlist = ['declarative', 'hybrid', 'compiler'])
+import flask as F
+import config as C
+G = F.g
+
+APP = F.Flask(__name__)
+APP.config.from_object(C)
+
+ENG = A.create_engine(C.DATABASE, echo = False)
+META = A.MetaData()
+
+S = A.orm.scoped_session(A.orm.sessionmaker(autocommit = False,
+ autoflush = False,
+ bind = ENG))
+
+Base = A.ext.declarative.declarative_base(metadata = META)
+Base.query = S.query_property()
+
+class PrintableObject (object):
+ POSATTRS = []
+ KWATTRS = []
+ def __init__(me, *args, **kw):
+ done = set()
+ attrs = set(me.POSATTRS + me.KWATTRS)
+ if len(args) > len(me.POSATTRS):
+ raise ValueError, 'Too many positional arguments'
+ for k, a in zip(me.POSATTRS, args):
+ setattr(me, k, a)
+ done.add(k)
+ for k, v in kw.items():
+ if k in done:
+ raise ValueError, "Attribute `%s' already set" % k
+ elif k not in attrs:
+ raise ValueError, "Unknown attribute `%s'" % k
+ setattr(me, k, v)
+ done.add(k)
+ for k in attrs:
+ if k not in done:
+ setattr(me, k, None)
+ def __repr__(me):
+ return '#<%s%s%s>' % (
+ me.__class__.__name__,
+ ''.join([' %r' % (getattr(me, a),) for a in me.POSATTRS]),
+ ''.join([' %s=%r' % (a, getattr(me, a)) for a in me.KWATTRS]))
+
+class Volume (PrintableObject, Base):
+ __tablename__ = 'volume'
+ name = A.Column(A.String, primary_key = True)
+ state = A.Column('state', A.String(16), nullable = False)
+ _custodian = A.Column('custodian', A.String,
+ A.ForeignKey('custodian.name'),
+ nullable = True)
+ custodian = A.orm.relationship(
+ 'Custodian', lazy = 'joined',
+ backref = A.orm.backref('volumes', lazy = 'joined'))
+ since = A.Column('since', A.DateTime,
+ server_default = A.func.current_timestamp())
+ __mapper_args__ = dict(order_by = since.asc())
+
+ STATES = set(['OFFSITE', 'READY', 'LIVE', 'MISSING', 'RETIRED'])
+ POSATTRS = ['name', 'state']
+ KWATTRS = ['since', 'custodian']
+
+ def set_state(me, newstate, custodian = None, when = None):
+ if when is None: when = D.datetime.now()
+ if newstate not in me.STATES:
+ raise ValueError, "Unknown state `%s'" % newstate
+ if (newstate == 'OFFSITE') != (custodian is not None):
+ raise ValueError, 'State/custodian mismatch'
+ S.add(Event(me, newstate, when, custodian = custodian))
+ me.state = newstate
+ me.since = when
+ me.custodian = custodian
+
+ @classmethod
+ def byname(cls, name):
+ return cls.query.get(name)
+
+ @classmethod
+ def bystate(cls, state):
+ return cls.query.filter(cls.state == state)
+
+class Custodian (PrintableObject, Base):
+ __tablename__ = 'custodian'
+ name = A.Column(A.String, primary_key = True)
+ POSATTRS = ['name']
+
+ @classmethod
+ def byname(cls, name):
+ return cls.query.get(name)
+
+class Event (PrintableObject, Base):
+ __tablename__ = 'event'
+ _volume = A.Column(A.String, A.ForeignKey('volume.name'),
+ primary_key = True)
+ volume = A.orm.relationship(
+ 'Volume',
+ backref = A.orm.backref('events', lazy = 'dynamic'))
+ state = A.Column(A.String(16), nullable = False)
+ when = A.Column(A.DateTime, primary_key = True)
+ _custodian = A.Column(A.String, A.ForeignKey('custodian.name'),
+ nullable = True)
+ custodian = A.orm.relationship(
+ 'Custodian',
+ backref = A.orm.backref('events', lazy = 'dynamic'))
+ __mapper_args__ = dict(order_by = when.desc())
+
+ POSATTRS = ['volume', 'state', 'when']
+ KWATTRS = ['custodian']
+
+def setup_db():
+ Base.metadata.create_all(ENG)
+
+ cust = {}
+ for c in ['mdw', 'ih', 'marcus', 'jbp']:
+ cust[c] = Custodian(c)
+ S.add(cust[c])
+ for v, s, c in [('2012-10-08.1', 'OFFSITE', 'mdw'),
+ ('2012-10-08.2', 'OFFSITE', 'ih'),
+ ('2012-10-08.3', 'OFFSITE', 'marcus'),
+ ('2012-10-08.4', 'LIVE', None)]:
+ vol = Volume(v, s)
+ if c: vol.custodian = cust[c]
+ S.add(vol)
+ S.commit()
+
+class Action (PrintableObject):
+ POSATTRS = ['volume', 'newstate']
+ KWATTRS = ['custodian', 'early', 'late']
+ def perform(me):
+ me.volume.set_state(me.newstate, custodian = me.custodian)
+
+class DoubleListItem (object):
+ __slots__ = ['data', 'list', 'next', 'prev']
+ def __init__(me, data, list, next = None, prev = None):
+ me.data = data
+ me.next = next
+ me.prev = prev
+ me.list = list
+
+class DoubleList (object):
+ def __init__(me, things):
+ me._head = me._tail = None
+ for i in things: me.linktail(i)
+ me._map = dict()
+ def _link(me, data, next = None, prev = None):
+ item = me._map[data] = DoubleListItem(data, me, next, prev)
+ if item.next: item.next.prev = item
+ else: me._tail = item
+ if item.prev: item.prev.next = item
+ else: me._head = item
+ def linkhead(me, data):
+ me._link(data, next = me.head)
+ def linktail(me, data):
+ me._link(data, prev = me.tail)
+ def linkbefore(me, data, item):
+ me._link(data, next = item, prev = item.prev)
+ def linkafter(me, data, item):
+ me._link(data, prev = item, next = item.next)
+ def _unlink(me, item):
+ if item.next: item.next.prev = item.prev
+ else: me._tail = item.prev
+ if item.prev: item.prev.next = item.next
+ else: me._head = item.next
+ def unlink(me, data):
+ item = me._map[data]
+ me._unlink(item)
+ del me._map[data]
+ @property
+ def head(me): return me._head.data
+ @property
+ def tail(me): return me._tail.data
+ def next(me, data): return me._map[data].next.data
+ def prev(me, data): return me._map[data].prev.data
+ def __iter__(me):
+ i = me._head
+ while i:
+ yield i.data
+ i = i.next
+
+class NullsOrder (A.sql.expression.ColumnElement):
+ """
+ SQLite
+ """
+ def __init__(me, elt, pos):
+ me._elt = elt
+ me._pos = pos.upper()
+ me.type = elt.type
+
+@A.ext.compiler.compiles(NullsOrder)
+def compile_nullsorder(elt, compiler, **kw):
+ return '%s NULLS %s' % (compiler.process(elt._elt), elt._pos)
+
+@A.ext.compiler.compiles(NullsOrder, 'sqlite')
+def compile_nullsorder(elt, compiler, **kw):
+ if isinstance(elt._elt, A.sql.expression._UnaryExpression) and \
+ (elt._elt.modifier, elt._pos) in [(A.sql.operators.asc_op, 'FIRST'),
+ (A.sql.operators.desc_op, 'LAST')]:
+ return compiler.process(elt._elt)
+ else:
+ raise ValueError, \
+ "SQLite doesn't have `NULLS FIRST/LAST' and the behaviour " \
+ "you actually get doesn't match what you seem to want."
+
+def next_actions():
+ live = set(Volume.bystate('LIVE'))
+ ready = set(Volume.bystate('READY'))
+
+ ## For each volume find out who most recently took it offsite. This turns
+ ## out to be remarkably difficult. Thanks to Sally Vernon for a good hint
+ ## on how to write the SQL.
+ ##
+ ## Start by finding the time of the most recent offsite event for each
+ ## volume.
+ q = S.query(A.func.max(Event.when).label('evtime'),
+ Event._volume.label('volume')) \
+ .join(Volume) \
+ .filter(Event.state == 'OFFSITE') \
+ .group_by(Event._volume) \
+ .subquery()
+
+ ## Now match that back against the events table, so that we can find out
+ ## who took the volume offsite.
+ last_holder = dict()
+ for ev in S.query(Event) \
+ .join(q, A.and_(q.c.volume == Event._volume,
+ q.c.evtime == Event.when)) \
+ .options(A.orm.joinedload(Event.volume),
+ A.orm.joinedload(Event.custodian)):
+ last_holder[ev.volume] = ev.custodian
+
+ ## Next, work out how many volumes each custodian has a claim on. We'll
+ ## use this to hand out unclaimed volumes; at that point we'll want this as
+ ## a sorted list, but it's too annoying to maintain like that.
+ nclaimed = dict()
+ for c in Custodian.query: nclaimed[c] = 0
+ for v, c in last_holder.iteritems(): nclaimed[c] += 1
+
+ ## Now we want the volumes, with those which haven't been live for longest
+ ## listed first.
+ vol_byage = DoubleList(S.query(Volume) \
+ .outerjoin(Event,
+ A.and_(Volume.name == Event._volume,
+ Event.state == 'LIVE')) \
+ .group_by(Volume) \
+ .having(Volume.state != 'RETIRED') \
+ .order_by(NullsOrder(A.func.max(Event.when).asc(),
+ 'FIRST')))
+
+ ## Now we're in reasonable shape to make a start.
+
+
+
+ def grab(list):
+ vol, list[:] = list[0], list[1:]
+ return vol
+
+ def ensure_ready():
+ acts = []
+ if len(ready):
+ vol = grab(ready)
+ elif len(offsite) >= 2:
+ vol = grab(offsite)
+ acts.append(Action(vol, 'READY'))
+ else:
+ raise ValueError, 'no volumes available'
+ return vol, acts
+
+ if len(live) == 0:
+ ## Ensure a volume is ready.
+ ## Make a ready volume be live.
+ vol, acts = ensure_ready()
+ for a in acts: yield a
+ yield Action(vol, 'LIVE')
+ elif len(live) == 1:
+ ## Arrange for a volume to be ready.
+ ## Take current live volume offline.
+ ## Make ready volume be live.
+ ## Take old live volume offsite (in custody of whomever brought in the
+ ## new live volume).
+ vol, acts = ensure_ready()
+ for a in acts: yield a
+ old, = live
+ keeper = Custodian.query \
+ .join(Event) \
+ .filter(Event.volume == vol, Event.state == 'OFFSITE') \
+ .group_by(Custodian) \
+ .order_by(A.func.max(Event.when)) \
+ [:1]
+ if len(keeper) == 0:
+ ## find a custodian with a minimal number of volumes.
+ keeper = Custodian.query \
+ .outerjoin(Volume) \
+ .group_by(Custodian) \
+ .order_by(A.func.count(Volume.name)) \
+ [:1]
+ if len(keeper) == 0:
+ raise ValueError, 'no keepers available'
+
+ yield Action(old, 'READY')
+ yield Action(vol, 'LIVE')
+ yield Action(old, 'OFFSITE', custodian = keeper[0])
+ else:
+ raise ValueError, 'multiple live volumes'
+
+@APP.teardown_request
+def shutdown_session(exc = None):
+ S.remove()
+
+@APP.route('/')
+def root():
+ return F.render_template('root.html')
+
+@APP.route('/where')
+def where():
+ wrong
+ return F.render_template('where.html', volumes = Volume.query)
+
+@APP.route('/custodian/<name>')
+def custodian(name):
+ return F.render_template('custodian.html',
+ cust = Custodian.byname(name))
+
+if __name__ == '__main__':
+ APP.run(use_evalex = False)
~mdw / backup-rotation / commitdiff