4 __import__('sqlalchemy', fromlist
= ['orm'])
5 __import__('sqlalchemy.ext',
6 fromlist
= ['declarative', 'hybrid', 'compiler'])
11 APP
= F
.Flask(__name__
)
12 APP
.config
.from_object(C
)
14 ENG
= A
.create_engine(C
.DATABASE
, echo
= False)
17 S
= A
.orm
.scoped_session(A
.orm
.sessionmaker(autocommit
= False,
21 Base
= A
.ext
.declarative
.declarative_base(metadata
= META
)
22 Base
.query
= S
.query_property()
24 class PrintableObject (object):
27 def __init__(me
, *args
, **kw
):
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
):
35 for k
, v
in kw
.items():
37 raise ValueError, "Attribute `%s' already set" % k
39 raise ValueError, "Unknown attribute `%s'" % k
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
]))
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'),
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())
65 STATES
= set(['OFFSITE', 'READY', 'LIVE', 'MISSING', 'RETIRED'])
66 POSATTRS
= ['name', 'state']
67 KWATTRS
= ['since', 'custodian']
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
))
78 me
.custodian
= custodian
81 def byname(cls
, name
):
82 return cls
.query
.get(name
)
85 def bystate(cls
, state
):
86 return cls
.query
.filter(cls
.state
== state
)
88 class Custodian (PrintableObject
, Base
):
89 __tablename__
= 'custodian'
90 name
= A
.Column(A
.String
, primary_key
= True)
94 def byname(cls
, name
):
95 return cls
.query
.get(name
)
97 class Event (PrintableObject
, Base
):
98 __tablename__
= 'event'
99 _volume
= A
.Column(A
.String
, A
.ForeignKey('volume.name'),
101 volume
= A
.orm
.relationship(
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'),
108 custodian
= A
.orm
.relationship(
110 backref
= A
.orm
.backref('events', lazy
= 'dynamic'))
111 __mapper_args__
= dict(order_by
= when
.desc())
113 POSATTRS
= ['volume', 'state', 'when']
114 KWATTRS
= ['custodian']
117 Base
.metadata
.create_all(ENG
)
120 for c
in ['mdw', 'ih', 'marcus', 'jbp']:
121 cust
[c
] = Custodian(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)]:
128 if c
: vol
.custodian
= cust
[c
]
132 class Action (PrintableObject
):
133 POSATTRS
= ['volume', 'newstate']
134 KWATTRS
= ['custodian', 'early', 'late']
136 me
.volume
.set_state(me
.newstate
, custodian
= me
.custodian
)
138 class DoubleListItem (object):
139 __slots__
= ['data', 'list', 'next', 'prev']
140 def __init__(me
, data
, list, next
= None, prev
= None):
146 class DoubleList (object):
147 def __init__(me
, things
):
148 me
._head
= me
._tail
= None
149 for i
in things
: me
.linktail(i
)
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
):
175 def head(me
): return me
._head
.data
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
186 class NullsOrder (A
.sql
.expression
.ColumnElement
):
190 def __init__(me
, elt
, pos
):
192 me
._pos
= pos
.upper()
195 @A.ext
.compiler
.compiles(NullsOrder
)
196 def compile_nullsorder(elt
, compiler
, **kw
):
197 return '%s NULLS %s' %
(compiler
.process(elt
._elt
), elt
._pos
)
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
)
207 "SQLite doesn't have `NULLS FIRST/LAST' and the behaviour " \
208 "you actually get doesn't match what you seem to want."
211 live
= set(Volume
.bystate('LIVE'))
212 ready
= set(Volume
.bystate('READY'))
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.
218 ## Start by finding the time of the most recent offsite event for each
220 q
= S
.query(A
.func
.max(Event
.when
).label('evtime'),
221 Event
._volume
.label('volume')) \
223 .filter(Event
.state
== 'OFFSITE') \
224 .group_by(Event
._volume
) \
227 ## Now match that back against the events table, so that we can find out
228 ## who took the volume offsite.
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
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.
241 for c
in Custodian
.query
: nclaimed
[c
] = 0
242 for v
, c
in last_holder
.iteritems(): nclaimed
[c
] += 1
244 ## Now we want the volumes, with those which haven't been live for longest
246 vol_byage
= DoubleList(S
.query(Volume
) \
248 A
.and_(Volume
.name
== Event
._volume
,
249 Event
.state
== 'LIVE')) \
251 .having(Volume
.state
!= 'RETIRED') \
252 .order_by(NullsOrder(A
.func
.max(Event
.when
).asc(),
255 ## Now we're in reasonable shape to make a start.
260 vol
, list[:] = list[0], list[1:]
267 elif len(offsite
) >= 2:
269 acts
.append(Action(vol
, 'READY'))
271 raise ValueError, 'no volumes available'
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')
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
286 vol
, acts
= ensure_ready()
287 for a
in acts
: yield a
289 keeper
= Custodian
.query \
291 .filter(Event
.volume
== vol
, Event
.state
== 'OFFSITE') \
292 .group_by(Custodian
) \
293 .order_by(A
.func
.max(Event
.when
)) \
296 ## find a custodian with a minimal number of volumes.
297 keeper
= Custodian
.query \
299 .group_by(Custodian
) \
300 .order_by(A
.func
.count(Volume
.name
)) \
303 raise ValueError, 'no keepers available'
305 yield Action(old
, 'READY')
306 yield Action(vol
, 'LIVE')
307 yield Action(old
, 'OFFSITE', custodian
= keeper
[0])
309 raise ValueError, 'multiple live volumes'
311 @APP.teardown_request
312 def shutdown_session(exc
= None):
317 return F
.render_template('root.html')
322 return F
.render_template('where.html', volumes
= Volume
.query
)
324 @APP.route('/custodian/<name>')
326 return F
.render_template('custodian.html',
327 cust
= Custodian
.byname(name
))
329 if __name__
== '__main__':
330 APP
.run(use_evalex
= False)