| 1 | import datetime as D |
| 2 | |
| 3 | import sqlalchemy as A |
| 4 | __import__('sqlalchemy', fromlist = ['orm']) |
| 5 | __import__('sqlalchemy.ext', |
| 6 | fromlist = ['declarative', 'hybrid', 'compiler']) |
| 7 | import flask as F |
| 8 | import config as C |
| 9 | G = F.g |
| 10 | |
| 11 | APP = F.Flask(__name__) |
| 12 | APP.config.from_object(C) |
| 13 | |
| 14 | ENG = A.create_engine(C.DATABASE, echo = False) |
| 15 | META = A.MetaData() |
| 16 | |
| 17 | S = A.orm.scoped_session(A.orm.sessionmaker(autocommit = False, |
| 18 | autoflush = False, |
| 19 | bind = ENG)) |
| 20 | |
| 21 | Base = A.ext.declarative.declarative_base(metadata = META) |
| 22 | Base.query = S.query_property() |
| 23 | |
| 24 | class PrintableObject (object): |
| 25 | POSATTRS = [] |
| 26 | KWATTRS = [] |
| 27 | def __init__(me, *args, **kw): |
| 28 | done = set() |
| 29 | attrs = set(me.POSATTRS + me.KWATTRS) |
| 30 | if len(args) > len(me.POSATTRS): |
| 31 | raise ValueError, 'Too many positional arguments' |
| 32 | for k, a in zip(me.POSATTRS, args): |
| 33 | setattr(me, k, a) |
| 34 | done.add(k) |
| 35 | for k, v in kw.items(): |
| 36 | if k in done: |
| 37 | raise ValueError, "Attribute `%s' already set" % k |
| 38 | elif k not in attrs: |
| 39 | raise ValueError, "Unknown attribute `%s'" % k |
| 40 | setattr(me, k, v) |
| 41 | done.add(k) |
| 42 | for k in attrs: |
| 43 | if k not in done: |
| 44 | setattr(me, k, None) |
| 45 | def __repr__(me): |
| 46 | return '#<%s%s%s>' % ( |
| 47 | me.__class__.__name__, |
| 48 | ''.join([' %r' % (getattr(me, a),) for a in me.POSATTRS]), |
| 49 | ''.join([' %s=%r' % (a, getattr(me, a)) for a in me.KWATTRS])) |
| 50 | |
| 51 | class Volume (PrintableObject, Base): |
| 52 | __tablename__ = 'volume' |
| 53 | name = A.Column(A.String, primary_key = True) |
| 54 | state = A.Column('state', A.String(16), nullable = False) |
| 55 | _custodian = A.Column('custodian', A.String, |
| 56 | A.ForeignKey('custodian.name'), |
| 57 | nullable = True) |
| 58 | custodian = A.orm.relationship( |
| 59 | 'Custodian', lazy = 'joined', |
| 60 | backref = A.orm.backref('volumes', lazy = 'joined')) |
| 61 | since = A.Column('since', A.DateTime, |
| 62 | server_default = A.func.current_timestamp()) |
| 63 | __mapper_args__ = dict(order_by = since.asc()) |
| 64 | |
| 65 | STATES = set(['OFFSITE', 'READY', 'LIVE', 'MISSING', 'RETIRED']) |
| 66 | POSATTRS = ['name', 'state'] |
| 67 | KWATTRS = ['since', 'custodian'] |
| 68 | |
| 69 | def set_state(me, newstate, custodian = None, when = None): |
| 70 | if when is None: when = D.datetime.now() |
| 71 | if newstate not in me.STATES: |
| 72 | raise ValueError, "Unknown state `%s'" % newstate |
| 73 | if (newstate == 'OFFSITE') != (custodian is not None): |
| 74 | raise ValueError, 'State/custodian mismatch' |
| 75 | S.add(Event(me, newstate, when, custodian = custodian)) |
| 76 | me.state = newstate |
| 77 | me.since = when |
| 78 | me.custodian = custodian |
| 79 | |
| 80 | @classmethod |
| 81 | def byname(cls, name): |
| 82 | return cls.query.get(name) |
| 83 | |
| 84 | @classmethod |
| 85 | def bystate(cls, state): |
| 86 | return cls.query.filter(cls.state == state) |
| 87 | |
| 88 | class Custodian (PrintableObject, Base): |
| 89 | __tablename__ = 'custodian' |
| 90 | name = A.Column(A.String, primary_key = True) |
| 91 | POSATTRS = ['name'] |
| 92 | |
| 93 | @classmethod |
| 94 | def byname(cls, name): |
| 95 | return cls.query.get(name) |
| 96 | |
| 97 | class Event (PrintableObject, Base): |
| 98 | __tablename__ = 'event' |
| 99 | _volume = A.Column(A.String, A.ForeignKey('volume.name'), |
| 100 | primary_key = True) |
| 101 | volume = A.orm.relationship( |
| 102 | 'Volume', |
| 103 | backref = A.orm.backref('events', lazy = 'dynamic')) |
| 104 | state = A.Column(A.String(16), nullable = False) |
| 105 | when = A.Column(A.DateTime, primary_key = True) |
| 106 | _custodian = A.Column(A.String, A.ForeignKey('custodian.name'), |
| 107 | nullable = True) |
| 108 | custodian = A.orm.relationship( |
| 109 | 'Custodian', |
| 110 | backref = A.orm.backref('events', lazy = 'dynamic')) |
| 111 | __mapper_args__ = dict(order_by = when.desc()) |
| 112 | |
| 113 | POSATTRS = ['volume', 'state', 'when'] |
| 114 | KWATTRS = ['custodian'] |
| 115 | |
| 116 | def setup_db(): |
| 117 | Base.metadata.create_all(ENG) |
| 118 | |
| 119 | cust = {} |
| 120 | for c in ['mdw', 'ih', 'marcus', 'jbp']: |
| 121 | cust[c] = Custodian(c) |
| 122 | S.add(cust[c]) |
| 123 | for v, s, c in [('2012-10-08.1', 'OFFSITE', 'mdw'), |
| 124 | ('2012-10-08.2', 'OFFSITE', 'ih'), |
| 125 | ('2012-10-08.3', 'OFFSITE', 'marcus'), |
| 126 | ('2012-10-08.4', 'LIVE', None)]: |
| 127 | vol = Volume(v, s) |
| 128 | if c: vol.custodian = cust[c] |
| 129 | S.add(vol) |
| 130 | S.commit() |
| 131 | |
| 132 | class Action (PrintableObject): |
| 133 | POSATTRS = ['volume', 'newstate'] |
| 134 | KWATTRS = ['custodian', 'early', 'late'] |
| 135 | def perform(me): |
| 136 | me.volume.set_state(me.newstate, custodian = me.custodian) |
| 137 | |
| 138 | class DoubleListItem (object): |
| 139 | __slots__ = ['data', 'list', 'next', 'prev'] |
| 140 | def __init__(me, data, list, next = None, prev = None): |
| 141 | me.data = data |
| 142 | me.next = next |
| 143 | me.prev = prev |
| 144 | me.list = list |
| 145 | |
| 146 | class DoubleList (object): |
| 147 | def __init__(me, things): |
| 148 | me._head = me._tail = None |
| 149 | for i in things: me.linktail(i) |
| 150 | me._map = dict() |
| 151 | def _link(me, data, next = None, prev = None): |
| 152 | item = me._map[data] = DoubleListItem(data, me, next, prev) |
| 153 | if item.next: item.next.prev = item |
| 154 | else: me._tail = item |
| 155 | if item.prev: item.prev.next = item |
| 156 | else: me._head = item |
| 157 | def linkhead(me, data): |
| 158 | me._link(data, next = me.head) |
| 159 | def linktail(me, data): |
| 160 | me._link(data, prev = me.tail) |
| 161 | def linkbefore(me, data, item): |
| 162 | me._link(data, next = item, prev = item.prev) |
| 163 | def linkafter(me, data, item): |
| 164 | me._link(data, prev = item, next = item.next) |
| 165 | def _unlink(me, item): |
| 166 | if item.next: item.next.prev = item.prev |
| 167 | else: me._tail = item.prev |
| 168 | if item.prev: item.prev.next = item.next |
| 169 | else: me._head = item.next |
| 170 | def unlink(me, data): |
| 171 | item = me._map[data] |
| 172 | me._unlink(item) |
| 173 | del me._map[data] |
| 174 | @property |
| 175 | def head(me): return me._head.data |
| 176 | @property |
| 177 | def tail(me): return me._tail.data |
| 178 | def next(me, data): return me._map[data].next.data |
| 179 | def prev(me, data): return me._map[data].prev.data |
| 180 | def __iter__(me): |
| 181 | i = me._head |
| 182 | while i: |
| 183 | yield i.data |
| 184 | i = i.next |
| 185 | |
| 186 | class NullsOrder (A.sql.expression.ColumnElement): |
| 187 | """ |
| 188 | SQLite |
| 189 | """ |
| 190 | def __init__(me, elt, pos): |
| 191 | me._elt = elt |
| 192 | me._pos = pos.upper() |
| 193 | me.type = elt.type |
| 194 | |
| 195 | @A.ext.compiler.compiles(NullsOrder) |
| 196 | def compile_nullsorder(elt, compiler, **kw): |
| 197 | return '%s NULLS %s' % (compiler.process(elt._elt), elt._pos) |
| 198 | |
| 199 | @A.ext.compiler.compiles(NullsOrder, 'sqlite') |
| 200 | def compile_nullsorder(elt, compiler, **kw): |
| 201 | if isinstance(elt._elt, A.sql.expression._UnaryExpression) and \ |
| 202 | (elt._elt.modifier, elt._pos) in [(A.sql.operators.asc_op, 'FIRST'), |
| 203 | (A.sql.operators.desc_op, 'LAST')]: |
| 204 | return compiler.process(elt._elt) |
| 205 | else: |
| 206 | raise ValueError, \ |
| 207 | "SQLite doesn't have `NULLS FIRST/LAST' and the behaviour " \ |
| 208 | "you actually get doesn't match what you seem to want." |
| 209 | |
| 210 | def next_actions(): |
| 211 | live = set(Volume.bystate('LIVE')) |
| 212 | ready = set(Volume.bystate('READY')) |
| 213 | |
| 214 | ## For each volume find out who most recently took it offsite. This turns |
| 215 | ## out to be remarkably difficult. Thanks to Sally Vernon for a good hint |
| 216 | ## on how to write the SQL. |
| 217 | ## |
| 218 | ## Start by finding the time of the most recent offsite event for each |
| 219 | ## volume. |
| 220 | q = S.query(A.func.max(Event.when).label('evtime'), |
| 221 | Event._volume.label('volume')) \ |
| 222 | .join(Volume) \ |
| 223 | .filter(Event.state == 'OFFSITE') \ |
| 224 | .group_by(Event._volume) \ |
| 225 | .subquery() |
| 226 | |
| 227 | ## Now match that back against the events table, so that we can find out |
| 228 | ## who took the volume offsite. |
| 229 | last_holder = dict() |
| 230 | for ev in S.query(Event) \ |
| 231 | .join(q, A.and_(q.c.volume == Event._volume, |
| 232 | q.c.evtime == Event.when)) \ |
| 233 | .options(A.orm.joinedload(Event.volume), |
| 234 | A.orm.joinedload(Event.custodian)): |
| 235 | last_holder[ev.volume] = ev.custodian |
| 236 | |
| 237 | ## Next, work out how many volumes each custodian has a claim on. We'll |
| 238 | ## use this to hand out unclaimed volumes; at that point we'll want this as |
| 239 | ## a sorted list, but it's too annoying to maintain like that. |
| 240 | nclaimed = dict() |
| 241 | for c in Custodian.query: nclaimed[c] = 0 |
| 242 | for v, c in last_holder.iteritems(): nclaimed[c] += 1 |
| 243 | |
| 244 | ## Now we want the volumes, with those which haven't been live for longest |
| 245 | ## listed first. |
| 246 | vol_byage = DoubleList(S.query(Volume) \ |
| 247 | .outerjoin(Event, |
| 248 | A.and_(Volume.name == Event._volume, |
| 249 | Event.state == 'LIVE')) \ |
| 250 | .group_by(Volume) \ |
| 251 | .having(Volume.state != 'RETIRED') \ |
| 252 | .order_by(NullsOrder(A.func.max(Event.when).asc(), |
| 253 | 'FIRST'))) |
| 254 | |
| 255 | ## Now we're in reasonable shape to make a start. |
| 256 | |
| 257 | |
| 258 | |
| 259 | def grab(list): |
| 260 | vol, list[:] = list[0], list[1:] |
| 261 | return vol |
| 262 | |
| 263 | def ensure_ready(): |
| 264 | acts = [] |
| 265 | if len(ready): |
| 266 | vol = grab(ready) |
| 267 | elif len(offsite) >= 2: |
| 268 | vol = grab(offsite) |
| 269 | acts.append(Action(vol, 'READY')) |
| 270 | else: |
| 271 | raise ValueError, 'no volumes available' |
| 272 | return vol, acts |
| 273 | |
| 274 | if len(live) == 0: |
| 275 | ## Ensure a volume is ready. |
| 276 | ## Make a ready volume be live. |
| 277 | vol, acts = ensure_ready() |
| 278 | for a in acts: yield a |
| 279 | yield Action(vol, 'LIVE') |
| 280 | elif len(live) == 1: |
| 281 | ## Arrange for a volume to be ready. |
| 282 | ## Take current live volume offline. |
| 283 | ## Make ready volume be live. |
| 284 | ## Take old live volume offsite (in custody of whomever brought in the |
| 285 | ## new live volume). |
| 286 | vol, acts = ensure_ready() |
| 287 | for a in acts: yield a |
| 288 | old, = live |
| 289 | keeper = Custodian.query \ |
| 290 | .join(Event) \ |
| 291 | .filter(Event.volume == vol, Event.state == 'OFFSITE') \ |
| 292 | .group_by(Custodian) \ |
| 293 | .order_by(A.func.max(Event.when)) \ |
| 294 | [:1] |
| 295 | if len(keeper) == 0: |
| 296 | ## find a custodian with a minimal number of volumes. |
| 297 | keeper = Custodian.query \ |
| 298 | .outerjoin(Volume) \ |
| 299 | .group_by(Custodian) \ |
| 300 | .order_by(A.func.count(Volume.name)) \ |
| 301 | [:1] |
| 302 | if len(keeper) == 0: |
| 303 | raise ValueError, 'no keepers available' |
| 304 | |
| 305 | yield Action(old, 'READY') |
| 306 | yield Action(vol, 'LIVE') |
| 307 | yield Action(old, 'OFFSITE', custodian = keeper[0]) |
| 308 | else: |
| 309 | raise ValueError, 'multiple live volumes' |
| 310 | |
| 311 | @APP.teardown_request |
| 312 | def shutdown_session(exc = None): |
| 313 | S.remove() |
| 314 | |
| 315 | @APP.route('/') |
| 316 | def root(): |
| 317 | return F.render_template('root.html') |
| 318 | |
| 319 | @APP.route('/where') |
| 320 | def where(): |
| 321 | wrong |
| 322 | return F.render_template('where.html', volumes = Volume.query) |
| 323 | |
| 324 | @APP.route('/custodian/<name>') |
| 325 | def custodian(name): |
| 326 | return F.render_template('custodian.html', |
| 327 | cust = Custodian.byname(name)) |
| 328 | |
| 329 | if __name__ == '__main__': |
| 330 | APP.run(use_evalex = False) |