Commit | Line | Data |
---|---|---|
d03bd7cd MW |
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) |