Mercurial > pylearn
annotate pylearn/dbdict/api0.py @ 629:4e7781972999
merge
| author | James Bergstra <bergstrj@iro.umontreal.ca> |
|---|---|
| date | Mon, 19 Jan 2009 18:35:43 -0500 |
| parents | 739720896d61 123ca7751a80 |
| children | e242c12eb30d |
| rev | line source |
|---|---|
| 538 | 1 from sqlalchemy import create_engine, desc |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
2 import sqlalchemy.pool |
| 538 | 3 from sqlalchemy.orm import sessionmaker |
| 4 from sqlalchemy import Table, Column, MetaData, ForeignKey | |
| 5 from sqlalchemy import Integer, String, Float, Boolean, DateTime, Text, Binary | |
|
625
123ca7751a80
Added hash value for each dict in Trial table.
desjagui@atchoum.iro.umontreal.ca
parents:
624
diff
changeset
|
6 from sqlalchemy.databases import postgres |
| 538 | 7 from sqlalchemy.orm import mapper, relation, backref, eagerload |
| 8 from sqlalchemy.sql import operators, select | |
| 9 from sql_commands import crazy_sql_command | |
| 10 | |
| 11 class Todo(Exception): """Replace this with some working code!""" | |
| 12 | |
| 13 class DbHandle (object): | |
| 14 """ | |
| 15 This class also provides filtering shortcuts that hide the names of the | |
| 16 DbHandle internal databases. | |
| 17 | |
| 18 Attributes: | |
| 19 dict_table | |
| 20 pair_table | |
| 21 link_table | |
| 22 | |
| 23 | |
| 24 | |
| 25 dict_table | |
| 26 | |
| 27 An SqlAlchemy-mapped class corresponding to database table with the | |
| 28 following schema: | |
| 29 | |
| 30 Column('id', Integer, primary_key=True) | |
| 31 Column('create', DateTime) | |
| 32 Column('write', DateTime) | |
| 33 Column('read', DateTime) | |
| 34 | |
| 35 #TODO: reconsider create/read/write | |
| 36 | |
| 37 pair_table | |
| 38 | |
| 39 An SqlAlchemy-mapped class corresponding to database table with the | |
| 40 following schema: | |
| 41 | |
| 42 Column('id', Integer, primary_key=True) | |
| 43 Column('name', String(128)) | |
| 44 Column('ntype', Boolean) | |
| 45 Column('fval', Double) | |
| 46 Column('sval', Text)) | |
| 47 Column('bval', Blob) | |
| 48 | |
| 49 #TODO: Consider difference between text and binary | |
| 50 #TODO: Consider adding a 'type' column | |
| 51 #TODO: Consider union? | |
| 52 #TODO: Are there stanard ways of doing this kind of thing? | |
| 53 | |
| 54 link_table | |
| 55 | |
| 56 An SqlAlchemy-mapped class corresponding to database table with the | |
| 57 following schema: | |
| 58 | |
| 59 Column('dict_id', Integer, ForeignKey('%s.id' % t_trial), primary_key=True), | |
| 60 Column('keyval_id', Integer, ForeignKey('%s.id' % t_keyval), primary_key=True)) | |
| 61 | |
| 62 """ | |
| 63 | |
| 64 e_bad_table = 'incompatible columns in table' | |
| 65 | |
| 66 def __init__(h_self, Session, engine, dict_table, pair_table, link_table): | |
| 67 h_self._engine = engine; | |
| 68 h_self._dict_table = dict_table | |
| 69 h_self._pair_table = pair_table | |
| 70 h_self._link_table = link_table | |
| 71 | |
| 72 #TODO: replace this crude algorithm (ticket #17) | |
|
625
123ca7751a80
Added hash value for each dict in Trial table.
desjagui@atchoum.iro.umontreal.ca
parents:
624
diff
changeset
|
73 if ['id', 'create', 'write', 'read', 'status', 'priority','hash'] != [c.name for c in dict_table.c]: |
| 538 | 74 raise ValueError(h_self.e_bad_table, dict_table) |
| 75 if ['id', 'name', 'ntype', 'fval', 'sval', 'bval'] != [c.name for c in pair_table.c]: | |
| 76 raise ValueError(h_self.e_bad_table, pair_table) | |
| 77 if ['dict_id', 'pair_id'] != [c.name for c in link_table.c]: | |
| 78 raise ValueError(h_self.e_bad_table, pair_table) | |
| 79 | |
| 80 h_self._session_fn = Session | |
| 81 | |
| 82 class KeyVal (object): | |
| 83 """KeyVal interfaces between python types and the database. | |
| 84 | |
| 85 It encapsulates heuristics for type conversion. | |
| 86 """ | |
| 87 def __init__(k_self, name, val): | |
| 88 k_self.name = name | |
| 89 k_self.val = val | |
| 90 def __repr__(k_self): | |
| 91 return "<Param(%s,'%s', %s)>" % (k_self.id, k_self.name, repr(k_self.val)) | |
| 92 def __get_val(k_self): | |
| 93 val = None | |
| 94 if k_self.fval is not None: val = [int, float][k_self.ntype](k_self.fval) | |
| 95 if k_self.bval is not None: val = eval(str(k_self.bval)) | |
| 96 if k_self.sval is not None: val = k_self.sval | |
| 97 return val | |
| 98 def __set_val(k_self, val): | |
| 99 if isinstance(val, (str,unicode)): | |
| 100 k_self.fval = None | |
| 101 k_self.bval = None | |
| 102 k_self.sval = val | |
| 103 else: | |
| 104 k_self.sval = None | |
| 105 try: | |
| 106 f = float(val) | |
| 107 except (TypeError, ValueError): | |
| 108 f = None | |
| 109 if f is None: #binary data | |
| 110 k_self.bval = repr(val) | |
| 111 assert eval(k_self.bval) == val | |
| 112 k_self.fval = None | |
| 113 k_self.ntype = None | |
| 114 else: | |
| 115 k_self.bval = None | |
| 116 k_self.fval = f | |
| 117 k_self.ntype = isinstance(val,float) | |
| 118 val = property(__get_val, __set_val) | |
| 119 | |
| 120 mapper(KeyVal, pair_table) | |
| 121 | |
| 122 class Dict (object): | |
| 123 """ | |
| 124 Instances are dict-like objects with additional features for | |
| 125 communicating with an active database. | |
| 126 | |
| 127 This class will be mapped by SqlAlchemy to the dict_table. | |
| 128 | |
| 129 Attributes: | |
| 130 handle - reference to L{DbHandle} (creator) | |
| 131 | |
| 132 """ | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
133 def __init__(d_self, session=None): |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
134 if session is None: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
135 s = h_self._session_fn() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
136 s.add(d_self) #d_self transient -> pending |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
137 s.commit() #d_self -> persistent |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
138 s.close() #d_self -> detached |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
139 else: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
140 s = session |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
141 s.save(d_self) |
| 538 | 142 |
| 143 _forbidden_keys = set(['session']) | |
| 144 | |
| 145 # | |
| 146 # dictionary interface | |
| 147 # | |
| 148 | |
| 149 def __contains__(d_self, key): | |
| 150 for a in d_self._attrs: | |
| 151 if a.name == key: | |
| 152 return True | |
| 153 return False | |
| 154 | |
| 155 def __eq__(self, other): | |
| 156 return dict(self) == dict(other) | |
| 157 def __neq__(self, other): | |
| 158 return dict(self) != dict(other) | |
| 159 | |
| 160 def __getitem__(d_self, key): | |
| 161 for a in d_self._attrs: | |
| 162 if a.name == key: | |
| 163 return a.val | |
| 164 raise KeyError(key) | |
| 165 | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
166 def __setitem__(d_self, key, val, session=None): |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
167 if session is None: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
168 s = h_self._session_fn() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
169 s.add(d_self) |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
170 d_self._set_in_session(key, val, s) |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
171 s.commit() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
172 s.close() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
173 else: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
174 s = session |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
175 s.add(d_self) |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
176 d_self._set_in_session(key, val, s) |
| 538 | 177 |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
178 def __delitem__(d_self, key, session=None): |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
179 if session is None: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
180 s = h_self._session_fn() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
181 commit_close = True |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
182 else: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
183 s = session |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
184 commit_close = False |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
185 s.add(d_self) |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
186 |
| 538 | 187 #find the item to delete in d_self._attrs |
| 188 to_del = None | |
| 189 for i,a in enumerate(d_self._attrs): | |
| 190 if a.name == key: | |
| 191 assert to_del is None | |
| 192 to_del = (i,a) | |
| 193 if to_del is None: | |
| 194 raise KeyError(key) | |
| 195 else: | |
| 196 i, a = to_del | |
| 197 s.delete(a) | |
| 198 del d_self._attrs[i] | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
199 if commit_close: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
200 s.commit() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
201 s.close() |
| 538 | 202 |
|
568
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
538
diff
changeset
|
203 def iteritems(d_self): |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
538
diff
changeset
|
204 return d_self.items() |
|
1f036d934ad9
improvements to dbdict interface
Olivier Breuleux <breuleuo@iro.umontreal.ca>
parents:
538
diff
changeset
|
205 |
| 538 | 206 def items(d_self): |
| 207 return [(kv.name, kv.val) for kv in d_self._attrs] | |
| 208 | |
| 209 def keys(d_self): | |
| 210 return [kv.name for kv in d_self._attrs] | |
| 211 | |
| 212 def values(d_self): | |
| 213 return [kv.val for kv in d_self._attrs] | |
| 214 | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
215 def update(d_self, dct, session=None, **kwargs): |
| 538 | 216 """Like dict.update(), set keys from kwargs""" |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
217 if session is None: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
218 s = h_self._session_fn() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
219 commit_close = True |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
220 else: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
221 s = session |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
222 commit_close = False |
|
624
25d9f91f1afa
fixing session management in db dict update
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
623
diff
changeset
|
223 s.add(d_self) |
| 538 | 224 for k, v in dct.items(): |
| 225 d_self._set_in_session(k, v, s) | |
| 226 for k, v in kwargs.items(): | |
| 227 d_self._set_in_session(k, v, s) | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
228 |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
229 if commit_close: |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
230 s.commit() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
231 s.close() |
| 538 | 232 |
| 233 def get(d_self, key, default): | |
| 234 try: | |
| 235 return d_self[key] | |
| 236 except KeyError: | |
| 237 return default | |
| 238 | |
| 589 | 239 def __str__(self): |
| 240 return 'Dict'+ str(dict(self)) | |
| 241 | |
| 538 | 242 # |
| 243 # database stuff | |
| 244 # | |
| 245 | |
| 246 def refresh(d_self, session=None): | |
| 247 """Sync key-value pairs from database to self | |
| 248 | |
| 249 @param session: use the given session, and do not commit. | |
| 250 | |
| 251 """ | |
| 252 if session is None: | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
253 session = h_self._session_fn() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
254 session.add(d_self) #so session knows about us |
| 538 | 255 session.refresh(d_self) |
| 256 session.commit() | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
257 session.close() |
| 538 | 258 else: |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
259 session.add(d_self) #so session knows about us |
| 538 | 260 session.refresh(self.dbrow) |
| 261 | |
| 262 def delete(d_self, session=None): | |
| 263 """Delete this dictionary from the database | |
| 264 | |
| 265 @param session: use the given session, and do not commit. | |
| 266 """ | |
| 267 if session is None: | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
268 session = h_self._session_fn() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
269 session.add(d_self) #so session knows about us |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
270 session.delete(d_self) #mark for deletion |
| 538 | 271 session.commit() |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
272 session.close() |
| 538 | 273 else: |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
274 session.add(d_self) #so session knows about us |
| 538 | 275 session.delete(d_self) |
| 276 | |
| 277 # helper routine by update() and __setitem__ | |
| 278 def _set_in_session(d_self, key, val, session): | |
| 279 """Modify an existing key or create a key to hold val""" | |
|
625
123ca7751a80
Added hash value for each dict in Trial table.
desjagui@atchoum.iro.umontreal.ca
parents:
624
diff
changeset
|
280 |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
281 #FIRST SOME MIRRORING HACKS |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
282 if key == 'dbdict.status': |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
283 ival = int(val) |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
284 d_self.status = ival |
|
624
25d9f91f1afa
fixing session management in db dict update
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
623
diff
changeset
|
285 if key == 'dbdict.sql.priority': |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
286 fval = float(val) |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
287 d_self.priority = fval |
|
625
123ca7751a80
Added hash value for each dict in Trial table.
desjagui@atchoum.iro.umontreal.ca
parents:
624
diff
changeset
|
288 if key == 'dbdict.hash': |
|
123ca7751a80
Added hash value for each dict in Trial table.
desjagui@atchoum.iro.umontreal.ca
parents:
624
diff
changeset
|
289 ival = int(val) |
|
123ca7751a80
Added hash value for each dict in Trial table.
desjagui@atchoum.iro.umontreal.ca
parents:
624
diff
changeset
|
290 d_self.hash = ival |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
291 |
| 538 | 292 if key in d_self._forbidden_keys: |
| 293 raise KeyError(key) | |
| 294 created = None | |
| 295 for i,a in enumerate(d_self._attrs): | |
| 296 if a.name == key: | |
| 297 assert created == None | |
| 298 created = h_self._KeyVal(key, val) | |
| 299 d_self._attrs[i] = created | |
| 300 if not created: | |
| 301 created = h_self._KeyVal(key, val) | |
| 302 d_self._attrs.append(created) | |
| 303 session.save(created) | |
| 304 | |
| 305 mapper(Dict, dict_table, | |
| 306 properties = { | |
| 307 '_attrs': relation(KeyVal, | |
| 308 secondary=link_table, | |
| 309 cascade="all, delete-orphan") | |
| 310 }) | |
| 311 | |
| 312 class _Query (object): | |
| 313 """ | |
| 314 Attributes: | |
| 315 _query - SqlAlchemy.Query object | |
| 316 """ | |
| 317 | |
| 318 def __init__(q_self, query): | |
| 319 q_self._query = query | |
| 320 | |
| 321 def __iter__(q_self): | |
| 322 return q_self.all().__iter__() | |
| 323 | |
| 324 def __getitem__(q_self, item): | |
| 325 return q_self._query.__getitem__(item) | |
| 326 | |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
327 def filter_eq(q_self, kw, arg): |
| 538 | 328 """Return a Query object that restricts to dictionaries containing |
| 329 the given kwargs""" | |
| 330 | |
| 331 #Note: when we add new types to the key columns, add them here | |
| 332 q = q_self._query | |
| 333 T = h_self._Dict | |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
334 if isinstance(arg, (str,unicode)): |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
335 q = q.filter(T._attrs.any(name=kw, sval=arg)) |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
336 else: |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
337 try: |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
338 f = float(arg) |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
339 except (TypeError, ValueError): |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
340 f = None |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
341 if f is None: |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
342 q = q.filter(T._attrs.any(name=kw, bval=repr(arg))) |
| 538 | 343 else: |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
344 q = q.filter(T._attrs.any(name=kw, fval=f)) |
| 538 | 345 |
| 346 return h_self._Query(q) | |
| 347 | |
|
619
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
348 def filter_eq_dct(q_self, dct): |
|
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
349 rval = q_self |
|
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
350 for key, val in dct.items(): |
|
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
351 rval = rval.filter_eq(key,val) |
|
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
352 return rval |
|
fa6585609e01
added api0.filter_eq_dct
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
615
diff
changeset
|
353 |
| 538 | 354 def all(q_self): |
| 355 """Return an iterator over all matching dictionaries. | |
| 356 | |
| 357 See L{SqlAlchemy.Query} | |
| 358 """ | |
| 359 return q_self._query.all() | |
| 360 | |
| 361 def count(q_self): | |
| 362 """Return the number of matching dictionaries. | |
| 363 | |
| 364 See L{SqlAlchemy.Query} | |
| 365 """ | |
| 366 return q_self._query.count() | |
| 367 | |
| 368 def first(q_self): | |
| 369 """Return some matching dictionary, or None | |
| 370 See L{SqlAlchemy.Query} | |
| 371 """ | |
| 372 return q_self._query.first() | |
| 373 | |
| 374 def all_ordered_by(q_self, key, desc=False): | |
| 375 """Return query results, sorted. | |
| 376 | |
| 377 @type key: string or tuple of string or list of string | |
| 378 @param: keys by which to sort the results. | |
| 379 | |
| 380 @rtype: list of L{DbHandle._Dict} instances | |
| 381 @return: query results, sorted by given keys | |
| 382 """ | |
| 383 | |
| 384 # order_by is not easy to do in SQL based on the data structures we're | |
| 385 # using. Considering we support different data types, it may not be | |
| 386 # possible at all. | |
| 387 # | |
| 388 # It would be easy if 'pivot' or 'crosstab' were provided as part of the | |
| 389 # underlying API, but they are not. For example, read this: | |
| 390 # http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/ | |
| 391 | |
| 392 # load query results | |
| 393 results = list(q_self.all()) | |
| 394 | |
| 395 if isinstance(key, (tuple, list)): | |
| 396 val_results = [([d[k] for k in key], d) for d in results] | |
| 397 else: | |
| 398 val_results = [(d[key], d) for d in results] | |
| 399 | |
| 400 val_results.sort() #interesting: there is an optional key parameter | |
| 401 if desc: | |
| 402 val_results.reverse() | |
| 403 return [vr[-1] for vr in val_results] | |
| 404 | |
| 405 h_self._KeyVal = KeyVal | |
| 406 h_self._Dict = Dict | |
| 407 h_self._Query = _Query | |
| 408 | |
| 409 def __iter__(h_self): | |
|
623
ae954c27fd11
looping works, eagerload of booked jobs works by str() HACK
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
622
diff
changeset
|
410 s = h_self.session() |
|
ae954c27fd11
looping works, eagerload of booked jobs works by str() HACK
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
622
diff
changeset
|
411 rval = list(h_self.query(s).__iter__()) |
|
ae954c27fd11
looping works, eagerload of booked jobs works by str() HACK
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
622
diff
changeset
|
412 s.close() |
|
ae954c27fd11
looping works, eagerload of booked jobs works by str() HACK
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
622
diff
changeset
|
413 return rval.__iter__() |
| 538 | 414 |
|
622
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
415 def insert_kwargs(h_self, session=None, **dct): |
| 538 | 416 """ |
| 417 @rtype: DbHandle with reference to self | |
| 418 @return: a DbHandle initialized as a copy of dct | |
| 419 | |
| 420 @type dct: dict-like instance whose keys are strings, and values are | |
| 421 either strings, integers, floats | |
| 422 | |
| 423 @param dct: dictionary to insert | |
| 424 | |
| 425 """ | |
|
622
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
426 return h_self.insert(dct, session=session) |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
427 |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
428 def insert(h_self, dct, session=None): |
| 538 | 429 """ |
| 430 @rtype: DbHandle with reference to self | |
| 431 @return: a DbHandle initialized as a copy of dct | |
| 432 | |
| 433 @type dct: dict-like instance whose keys are strings, and values are | |
| 434 either strings, integers, floats | |
| 435 | |
| 436 @param dct: dictionary to insert | |
| 437 | |
| 438 """ | |
|
622
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
439 if session is None: |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
440 s = h_self.session() |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
441 rval = h_self._Dict(s) |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
442 if dct: rval.update(dct, session=s) |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
443 s.commit() |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
444 s.close() |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
445 else: |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
446 rval = h_self._Dict(session) |
|
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
447 if dct: rval.update(dct, session=session) |
| 538 | 448 return rval |
| 449 | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
450 def query(h_self, session): |
| 538 | 451 """Construct an SqlAlchemy query, which can be subsequently filtered |
| 452 using the instance methods of DbQuery""" | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
453 return h_self._Query(session.query(h_self._Dict)\ |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
454 .options(eagerload('_attrs'))) |
| 538 | 455 |
| 456 def createView(h_self, view): | |
| 457 | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
458 s = h_self.session() |
|
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
459 cols = [] |
| 538 | 460 |
| 461 for col in view.columns: | |
| 462 if col.name is "id": | |
| 463 continue; | |
| 464 elif isinstance(col.type, (Integer,Float)): | |
| 465 cols.append([col.name,'fval']); | |
| 466 elif isinstance(col.type,String): | |
| 467 cols.append([col.name,'sval']); | |
| 468 elif isinstance(col.type,Binary): | |
| 469 cols.append([col.name,'bval']); | |
| 470 else: | |
| 471 assert "Error: wrong column type in view",view.name; | |
| 472 | |
| 473 # generate raw sql command string | |
| 474 viewsql = crazy_sql_command(view.name, cols, \ | |
| 475 h_self._pair_table.name, \ | |
| 476 h_self._link_table.name); | |
| 477 | |
| 478 #print 'Creating sql view with command:\n', viewsql; | |
| 479 | |
| 480 h_self._engine.execute(viewsql); | |
| 481 s.commit(); | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
482 s.close() |
| 538 | 483 |
| 484 class MappedClass(object): | |
| 485 pass | |
| 486 | |
| 487 mapper(MappedClass, view) | |
| 488 | |
| 489 return MappedClass | |
| 490 | |
| 491 def session(h_self): | |
| 492 return h_self._session_fn() | |
|
628
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
493 |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
494 def get(h_self, id): |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
495 s = h_self.session() |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
496 rval = s.query(h_self._Dict).get(id) |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
497 if rval: |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
498 #eagerload hack |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
499 str(rval) |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
500 rval.id |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
501 s.close() |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
502 return rval |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
503 |
|
739720896d61
added get by id to api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
624
diff
changeset
|
504 |
| 538 | 505 |
| 506 | |
| 507 def db_from_engine(engine, | |
| 508 table_prefix='DbHandle_default_', | |
| 509 trial_suffix='trial', | |
| 510 keyval_suffix='keyval', | |
| 511 link_suffix='link'): | |
| 512 """Create a DbHandle instance | |
| 513 | |
| 514 @type engine: sqlalchemy engine (e.g. from create_engine) | |
| 515 @param engine: connect to this database for transactions | |
| 516 | |
| 517 @type table_prefix: string | |
| 518 @type trial_suffix: string | |
| 519 @type keyval_suffix: string | |
| 520 @type link_suffix: string | |
| 521 | |
| 522 @rtype: DbHandle instance | |
| 523 | |
| 524 @note: The returned DbHandle will use three tables to implement the | |
| 525 many-to-many pattern that it needs: | |
| 526 - I{table_prefix + trial_suffix}, | |
| 527 - I{table_prefix + keyval_suffix} | |
| 528 - I{table_prefix + link_suffix} | |
| 529 | |
| 530 """ | |
| 531 Session = sessionmaker(autoflush=True, autocommit=False) | |
| 532 | |
| 533 metadata = MetaData() | |
| 534 | |
| 535 t_trial = Table(table_prefix+trial_suffix, metadata, | |
| 536 Column('id', Integer, primary_key=True), | |
| 537 Column('create', DateTime), | |
| 538 Column('write', DateTime), | |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
539 Column('read', DateTime), |
|
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
540 Column('status', Integer), |
|
625
123ca7751a80
Added hash value for each dict in Trial table.
desjagui@atchoum.iro.umontreal.ca
parents:
624
diff
changeset
|
541 Column('priority', Float(53)), |
|
123ca7751a80
Added hash value for each dict in Trial table.
desjagui@atchoum.iro.umontreal.ca
parents:
624
diff
changeset
|
542 Column('hash', postgres.PGBigInteger) |
|
615
a88ead1a1537
updated query to support new dot syntax, and hacked api0 to mirror dbdict.status and dbdict.priority to true columns
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
589
diff
changeset
|
543 ) |
| 538 | 544 |
| 545 t_keyval = Table(table_prefix+keyval_suffix, metadata, | |
| 546 Column('id', Integer, primary_key=True), | |
| 547 Column('name', String(128), nullable=False), #name of attribute | |
| 548 Column('ntype', Boolean), | |
| 549 Column('fval', Float(53)), | |
| 550 Column('sval', Text), | |
| 551 Column('bval', Binary)) | |
| 552 | |
| 553 t_link = Table(table_prefix+link_suffix, metadata, | |
| 554 Column('dict_id', Integer, ForeignKey('%s.id' % t_trial), | |
| 555 primary_key=True), | |
| 556 Column('pair_id', Integer, ForeignKey('%s.id' % t_keyval), | |
| 557 primary_key=True)) | |
| 558 | |
| 559 metadata.bind = engine | |
| 560 metadata.create_all() # no-op when tables already exist | |
| 561 #warning: tables can exist, but have incorrect schema | |
| 562 # see bug mentioned in DbHandle constructor | |
| 563 | |
| 564 return DbHandle(Session, engine, t_trial, t_keyval, t_link) | |
| 565 | |
| 566 def sqlite_memory_db(echo=False, **kwargs): | |
| 567 """Return a DbHandle backed by a memory-based database""" | |
| 568 engine = create_engine('sqlite:///:memory:', echo=False) | |
| 569 return db_from_engine(engine, **kwargs) | |
| 570 | |
| 571 def sqlite_file_db(filename, echo=False, **kwargs): | |
| 572 """Return a DbHandle backed by a file-based database""" | |
| 573 engine = create_engine('sqlite:///%s' % filename, echo=False) | |
| 574 return db_from_engine(engine, **kwargs) | |
| 575 | |
|
621
7041749cf804
untested changes to close sessions in api0
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
619
diff
changeset
|
576 def postgres_db(user, password, host, database, echo=False, poolclass=sqlalchemy.pool.NullPool, **kwargs): |
| 538 | 577 """Create an engine to access a postgres_dbhandle |
| 578 """ | |
| 579 db_str ='postgres://%(user)s:%(password)s@%(host)s/%(database)s' % locals() | |
| 580 | |
|
622
d2d582bcf7dc
api0 and sql seem to work with session closing
James Bergstra <bergstrj@iro.umontreal.ca>
parents:
621
diff
changeset
|
581 engine = create_engine(db_str, echo=echo, poolclass=poolclass) |
| 538 | 582 |
| 583 return db_from_engine(engine, **kwargs) | |
| 584 |