gmisclib.sqlbase

1 #! python 2 # -*- coding: utf-8 -*- 3 4 5 """This is a module for using a SQLlite database as a collection of python objects. 6 7 For each SQL table, you derive a class from L{DBx}. Each instance of that class corresponds to 8 one row of the table. 9 """ 10 11 import re 12 import sqlite3 13 import weakref 14 connect = sqlite3.connect 15 16 import die 17 18 DEBUG = 0

19 20 -class DBMetaClass(type):

21 """This is used by all the SQL classes, and initializes 22 the class. Specifically, it makes sure that each class has 23 a C{_cache} dictionary to cache instances of the class, and 24 a C{idx} counter to be the integer index into the SQL database. 25 """ 26

27 - def __new__(meta, classname, bases, cdict):

28 cdict['idx'] = 0 29 cdict['_cache'] = weakref.WeakValueDictionary() 30 if 'SQL_name' not in cdict: 31 cdict['SQL_name'] = classname 32 # print 'Meta col types', cdict.get('SQL_name', '?'), "col-types=", cdict.get('COL_types', '?') 33 if 'COL_types' in cdict: 34 colpat = ','.join( ['?'] * (1+len(cdict['COL_types'])) ) 35 cdict['_ipat'] = "insert into %s values (%s)" % (cdict['SQL_name'], colpat) 36 setpat = ','.join(["%s = ?"%c for (c,t) in cdict['COL_types']]) 37 cdict['_upat'] = "update %s set %s where id = ?" % (cdict['SQL_name'], setpat) 38 return type.__new__(meta, classname, bases, cdict)

39

40 41 42 -class SQLError(Exception):

43 """Any errors raised by this module. This does not include errors raised by sqlite itself."""

44 - def __init__(self, *s):

45 Exception.__init__(self, *s)

46

47 -class NoSuchTable(SQLError):

48 - def __init__(self, *s):

49 SQLError.__init__(self, *s)

50

51 -class ColumnMismatchError(SQLError):

52 - def __init__(self, *s):

53 SQLError.__init__(self, *s)

54

55 56 57 58 -class DB(object):

59 """Base class for all persistent objects in a sqlite database. Not the normal API. 60 Derived classes need to define a L{write}() method. 61 """ 62 __metaclass__ = DBMetaClass 63 64 S_notinDB = 0 65 S_inDB = 1 66 S_modified = 2 67

68 - def __init__(self, connection):

69 """ 70 @type connection: sqlite3.Connection 71 """ 72 try: 73 ct = self.COL_types 74 except AttributeError: 75 raise AttributeError, "Your class must define COL_types" 76 if len(ct)<=0 or len(ct[0])!=2 or not isinstance(ct[0][0], str) or not isinstance(ct[0][1], str): 77 raise TypeError, "Your class must define COL_types as a tuple of (column_name, data_type_name) pairs." 78 assert connection is not None and isinstance(connection, sqlite3.Connection) 79 self.w = connection 80 self.id = self.bump_id(1) 81 self.state = self.S_notinDB

82 83 @classmethod

84 - def bump_id(cls, delta):

85 """You can call this manually if you want to introduce a gap between ID numbers.""" 86 cls.idx += delta 87 return cls.idx

88 89

90 - def _write(self, *stuff):

91 """Called by C{write} in the derived class. This actually does the writing. 92 This function gets called to insert new rows and also to update old rows. 93 @param stuff: whatever should get written into the database table (neglecting "id"). 94 @type stuff: a L{tuple} of anything that L{sqlite3} can interpret. 95 @rtype: int 96 @return: the unique id number for the row that was just written. 97 """ 98 assert self.SQL_name is not None 99 assert len(stuff) == len(self.COL_types), "len(stuff)=%d col_types=%s" % (len(stuff), self.COL_types) 100 # die.info("Inserting: %s (%s) (%s)" % (self.SQL_name, col_pat, unicode((self.i,)+stuff))) 101 if self.state == self.S_notinDB: 102 if DEBUG > 1: 103 die.info("sqlbase: %s %s" % (self._ipat, unicode((self.id,)+stuff))) 104 self.w.cursor().execute(self._ipat, (self.id,) + stuff) 105 else: 106 if DEBUG > 1: 107 die.info("sqlbase: %s %s" % (self._upat, unicode((self.id,)+stuff))) 108 self.w.cursor().execute(self._upat, stuff + (self.id,)) 109 self.state = self.S_inDB 110 self._cache[self.id] = self 111 return self.id

112

113 - def write(self):

114 """This method writes an instance out to its database. 115 This method should assemble its attributes to form an argument list 116 for L{_write}, and then call L{_write} with that argument list. It should be 117 used like this:: 118 119 def write(self): 120 return self._write(self.something, self.other, self.stuff, self.misc) 121 122 @note: C{write()} forces an immediate write or an update of the database, whether or not you have 123 called L{make_dirty}(). L{make_dirty}() simply arranges for a write to occur 124 when the object is eventually destroyed. 125 @return: the return value of L{_write}. 126 @rtype: int 127 @note: The instance remembers the database from which it came. 128 @note: Do not pass C{self.id} to C{self.}L{_write()}. That's handled automatically. 129 """ 130 raise RuntimeError, "Virtual method"

131

132 - def make_dirty(self):

133 """Changes to an instance are not written to the database unless you call 134 L{make_dirty}() or L{write}(). C{write()} forces an immediate write, 135 whereas C{make_dirty}() simply marks the instance so that it will eventually 136 be written out when the destructor is called. 137 @note: Global variables are destroyed very late, possibly after other necessary objects 138 have already been destroyed, so you may not want to 139 trust that C{make_Dirty()} will behave well on global variables. 140 """ 141 if self.state == self.S_inDB: 142 self.state = self.S_modified

143

144 - def is_dirty(self):

145 return self.state != self.S_inDB

146

147 - def __del__(self):

148 if self.state != self.S_inDB: 149 self.write()

150 151 @classmethod

152 - def confirm(cls, connection):

153 """This can be called to confirm that the column names in the database matches what the class expects. 154 @raise ColumnMismatchError: When something is wrong. 155 @raise NoSuchTable: when the table doesn't exist. 156 @return: None 157 @rtype: None 158 @param connection: A connection to a database. 159 @type connection: L{sqlite3.Connection} 160 """ 161 assert isinstance(connection, sqlite3.Connection) 162 dbc = connection.cursor() 163 try: 164 dbc.execute("select * from %s where id=0" % cls.SQL_name) 165 except sqlite3.OperationalError, ex: 166 if 'no such table' in str(ex): 167 raise NoSuchTable(str(ex)) 168 raise 169 ct = (('id', 'integer'),) + tuple(cls.COL_types) 170 if len(dbc.description) != len(ct): 171 raise ColumnMismatchError("Wrong number of columns: saw %d expected %d" % (len(dbc.description), len(ct)), [x[0] for x in dbc.description], ct ) 172 for (i,(x, ct)) in enumerate(zip(sorted(dbc.description), sorted(ct))): 173 if x[0] != ct[0]: 174 raise ColumnMismatchError( "Mismatched name column %d: saw %s expected %s" % (i, x[0], ct[0]), [x[0] for x in dbc.description] )

175 176 @classmethod

177 - def create(cls, connection, if_not_exists=True):

178 """This creates the table in the database. It is harmless if the table already exists. 179 @return: None 180 @rtype: None 181 @param connection: A connection to a database. 182 @type connection: L{sqlite3.Connection} 183 """ 184 assert cls.SQL_name is not None 185 ct = [ ('id', 'INTEGER PRIMARY KEY ASC') ] 186 cc = [] 187 for (nm,ty) in cls.COL_types: 188 if ty.lower().startswith('fkey'): 189 fk, table, col = ty.split() 190 ct.append( (nm, 'INTEGER') ) 191 cc.append( ('FOREIGN KEY(%s)' % nm, 192 'REFERENCES %s(%s)' % (table, col)) ) 193 else: 194 ct.append( (nm, ty) ) 195 cstr = ','.join( [ "%s %s" % q for q in ct + cc] ) 196 ine = "if not exists" if if_not_exists else "" 197 if DEBUG: 198 die.info("sqlbase: create table %s %s (%s)" % (ine, cls.SQL_name, cstr)) 199 connection.execute("create table %s %s (%s)" % (ine, cls.SQL_name, cstr))

200 201 202 @classmethod

203 - def set_ID(cls, connection):

204 """This should be called when you first open an existing database when 205 you have the intent to write. It sets the initial ID number 206 to be larger than the largest ID in the database. 207 """ 208 tmp = connection.execute("select MAX(id) from %s" % cls.SQL_name) 209 idx = tmp.fetchone()[0] 210 if idx is not None: 211 cls.idx = idx + 1

212 213 @classmethod

214 - def index_unique(cls, connection, *columns):

215 cls._index_guts(connection, "UNIQUE", columns)

216 217 @classmethod

218 - def index_nonunique(cls, connection, *columns):

219 cls._index_guts(connection, "", columns)

220 221 @classmethod

222 - def _index_guts(cls, connection, u, columns):

223 idx2 = ','.join(columns) 224 idx1 = '_'.join([re.sub("_", "__", q) for q in columns]) 225 if DEBUG: 226 die.info("sqlbase: %s INDEX if not exists %s_index_%s on %s (%s)" 227 % (u, cls.SQL_name, idx1, cls.SQL_name, idx2) 228 ) 229 connection.execute("create %s INDEX if not exists %s_index_%s on %s (%s)" 230 % (u, cls.SQL_name, idx1, cls.SQL_name, idx2) 231 )

232 233 234 @classmethod

235 - def flush_cache(cls):

236 cls._cache.clear()

237

238 239 240 241 -class DBx(DB):

242 """This class is the main interface. Normally, you derive a class from this to represent a table 243 in the database. Each of your derived classes MUST contain an attribute C{COL_type} which 244 specifies what columns are in that table. 245 Each derived class will have a unique ID number called C{id}; this will be a column in the database, 246 and it will be stored in each instance object. The L{DBx} class manages that ID number for you. 247 248 Beyond that, a derived class MUST redefine L{_fromtuple}. 249 250 @cvar COL_type: a sequence of C{(column_name, data_type_name)} pairs. Those name the columns of the table and tell what 251 type of data is in each. C{data_type_name} must be the name of a legal sqlite3 data type OR, 252 it must be C{"FKEY tablename column_name"} where I{tablename} is the name of the table into which the 253 foreign key points and I{column_name} is the name of the column in that table. 254 Typical tuples in C{COL_type} are C{("Number_of_dogs", "INTEGER")} or C{("dog_id", "FKEY dog_info id")}. 255 Note that C{COL_type} should not include the ID number, which is always column C{id}; it is added automatically. 256 257 @cvar SQL_name: the name of the corresponding table in the database. This defaults to the name of the leaf class. 258 @ivar id: the instance's ID number. 259 Note that an ID number is allocated for every instance you create. Instances you look up with C{select} 260 won't necesarily be new: they bay just be a pointer to an existing, perhaps cached instance. 261 The ID number of an instance is persistant and is written into the database. 262 @cvar idx: the lowest unused ID number. 263 """

264 - def __init__(self, connection):

265 """@param connection: a database connection. 266 @type connection: L{sqlite3.Connection} 267 """ 268 DB.__init__(self, connection)

269 270

271 - def get_id(self):

272 if self.state == self.S_notinDB: 273 self.write() 274 return self.id

275 276 @classmethod

277 - def select(cls, db, where, *args):

278 assert isinstance(db, sqlite3.Connection) 279 dbc = db.cursor() 280 cln = cls.SQL_name 281 if DEBUG > 2: 282 die.info("sqlbase: select * from %s %s (%s)" % (cln, where, unicode(args))) 283 dbc.execute("select * from %s %s" % (cln, where), args) 284 for x in dbc.fetchall(): 285 idx = x[0] 286 try: 287 # The intent here is that there should be only one 288 # in-memory copy of each object in the database. 289 # Unfortunately, this means we will not see 290 # externally-caused changes to the database, 291 # but that seems better than having two objects in 292 # memory where you might accidentally change one and 293 # look at the other. 294 yield cls._cache[idx] 295 except KeyError: 296 rv = cls._fromtuple_id(db, *x) 297 cls._cache[idx] = rv 298 yield rv

299 300 301 302 @classmethod

303 - def get_by_id(cls, db, idx):

304 assert isinstance(db, sqlite3.Connection) 305 dbc = db.cursor() 306 if idx is None: 307 return None 308 assert isinstance(idx, int) 309 310 try: 311 return cls._cache[idx] 312 except KeyError: 313 pass 314 315 # die.info("select * from %s where id = %s" % (cls.SQL_name, idx)) 316 if DEBUG > 2: 317 die.info("select * from %s where id = ? (%s)" % (cls.SQL_name, unicode(idx))) 318 dbc.execute("select * from %s where id = ?" % cls.SQL_name, (idx,)) 319 x = dbc.fetchone() 320 if x is None: 321 raise KeyError, "No %s with id=%d" % (cls.SQL_name, idx) 322 rv = cls._fromtuple_id(db, *x) 323 cls._cache[idx] = rv 324 return rv

325 326 @classmethod

327 - def _fromtuple(cls, db, *rest):

328 """This method takes a database connection and a tuple of data from a row of the database and must 329 return an instance of the class. Normally, it checks its input arguments, converts them from 330 one format to another, and then calls the constructor and returns its result. 331 @param db: connection to a sqlite3 database. 332 @type db: L{sqlite3.Connection} 333 @param rest: a tuple derived by reading a row of data from the database. 334 This tuple does NOT contain the row's unique ID number, which is the first column in the database. 335 @type rest: tuple of stuff. 336 @return: a constructed instance of the class. 337 @rtype: an instance of a suitable class derived from L{DBx}. 338 """ 339 raise RuntimeError, "Virtual Method"

340 341 @classmethod

342 - def _fromtuple_id(cls, db, idx, *rest):

343 """This wraps L{_fromtuple} and adds in the ID number information. 344 """ 345 rv = cls._fromtuple(db, *rest) 346 rv.state = cls.S_inDB 347 rv.id = idx 348 # Somewhere inside the call to cls._fromtuple(), we called the constructor which 349 # called bump_id(). But, this isn't a new entry: it's one that already exists in 350 # the database, so we don't actually want idx incremented. Thus, we bump it back down. 351 cls.bump_id(-1) 352 return rv

353

354 - def __repr__(self):

355 contents = [] 356 for (nm, ty) in self.__class__.COL_types: 357 try: 358 v = getattr(self, nm) 359 except AttributeError: 360 continue 361 if ty.startswith('FKEY'): 362 fkey, ttype, rest = ty.split(None, 2) 363 contents.append("%s=<%s %d>" % (nm, ttype, v.id)) 364 else: 365 contents.append('%s=%s' % (nm, v)) 366 return "<%s %s>" % (self.__class__.SQL_name, ', '.join(contents))

367

368 369 370 -def multiselect(cll, db, where, *args):

371 """This is used for SQL select operations on a join of several tables. 372 @type cll: a sequence of C{(class, str)} where C{class} is derived from L{DBx} 373 @param cll: This a sequence of C{(class, name)} pairs, where C{class} matches 374 a table in the database, and C{name} is used to refer to that table in 375 the SQL select statement. 376 @type db: C{sqlite3.Connection} 377 @type where: str 378 @param where: This is the body of the select statement. Everything after 379 C{select * from tablename}. 380 @param args: All the arguments for the C{select} statement. 381 @note: As an example, if you have a table C{X} in the database and a class C{x}, 382 and C{x.SQL_name=="X"}, and each row in C{X} has an C{id} number, and 383 also the ID number of the C{next} item, 384 then 385 you can find pairs of adjacent items with this call:: 386 387 multiselect(((x, "x1"), (x, "x2")), db, "x1.next = x2.id") 388 """ 389 assert isinstance(db, sqlite3.Connection) 390 dbc = db.cursor() 391 cn = [] 392 frt = [] 393 ioff = 0 394 for c in cll: 395 if isinstance(c, tuple): 396 if len(c) != 2: 397 raise TypeError, "cll is ((class, 'name_in_select'),...)" 398 cn.append("%s %s" % (c[0].SQL_name, c[1])) 399 c = c[0] 400 else: 401 cn.append(c.SQL_name) 402 frt.append((c, ioff)) 403 ioff += len(c.COL_types)+1 404 # print 'cn=', cn 405 if DEBUG > 2: 406 die.info("select * from %s %s (%s)" % (', '.join(cn), where, unicode(args))) 407 try: 408 dbc.execute("select * from %s %s" % (', '.join(cn), where), args) 409 except sqlite3.OperationalError, ex: 410 die.info("multiselect: from: %s" % ', '.join(cn)) 411 die.info("multiselect: where: %s" % where) 412 die.info("multiselect: args: %s" % unicode(args)) 413 die.warn("multiselect: sqlite3.OperationalError: %s" % ex) 414 raise 415 416 for x in dbc.fetchall(): 417 # print "x=", x 418 rv = [] 419 assert len(x) == ioff 420 for (cls, i) in frt: 421 # print "cls=", cls, "i=", i 422 idx = x[i] 423 try: 424 tmp = cls._cache[idx] 425 # print "cached=", tmp 426 except KeyError: 427 tmp = cls._fromtuple_id(db, *(x[i:i+len(cls.COL_types)+1])) 428 cls._cache[idx] = tmp 429 # print "created=", tmp 430 rv.append(tmp) 431 # print "rv=", rv 432 yield tuple(rv)

433

434 435 -def get_version(db_connection, list_of_classes):

436 """This finds which of several variants of a table actually exists in the database. 437 The intent is to let you upgrade from one version of table format to another. 438 @param db_connection: A L{sqlite3.Connection} to a database. 439 @param list_of_classes: Possible classes to try. Each will be checked for consistency with the database. 440 @type list_of_classes: sequence of some class derived from sqlbase.L{DBx}. These are the classes themselves, 441 not class instances. 442 @rtype: One of the classes in C{list_of_classes}. 443 @return: The first class in the list that is consistent with the structure of the database. 444 """ 445 errs = [] 446 evtype = None 447 for tmp in list_of_classes: 448 try: 449 tmp.confirm(db_connection) 450 evtype = tmp 451 break 452 except (ColumnMismatchError, NoSuchTable), ex: 453 errs.append( "%s: %s" % (str(tmp), str(ex)) ) 454 pass 455 if evtype is None: 456 raise NoSuchTable("None of the supplied tables match.", *errs) 457 return evtype

458

459 460 -def multiclass_get_by_id(possible_classes, db, idx):

461 """This reads in a row from the database when you don't know exactly what information is available 462 The idea is that there is a one of several possible types of objects stored in the database, and 463 you will be happy to take whichever one is available. This makes most sense when they are 464 different versions of the same class. You access the objects by their ID number. 465 @type possible_classes: a sequence of classes, where each class is derived from L{DBx} 466 @param possible_classes: possible classes to match a table in the database. 467 Classes must be derived from L{DBx}, must have a name that matches a table in the database, 468 must have the right number of columns, and the raw data must be convertible to an object 469 of that class. 470 @type db: a L{sqlite3.Connection} to a database. 471 @type idx: int 472 @param idx: the C{id} of a row in the database. 473 @note: Ideally, there should be a 1:1 relationship between the names of tables in the database and 474 the C{SQL_name} attributes of classes. So, when you have a new version of a class, you 475 really should have a new name for the corresponding table in the database. However, if 476 you wish to have several classes that have the same C{SQL_name} attribute, a class can raise 477 C{ColumnMismatchError} if the class and the data row are incompatible. 478 """ 479 errs = [] 480 for c in possible_classes: 481 try: 482 return c.get_by_id(db, idx) 483 except sqlite3.OperationalError, ex: 484 if 'no such table' not in str(ex): 485 raise 486 errs.append("%s: %s" % (str(c), str(ex))) 487 except ColumnMismatchError, ex: 488 errs.append("%s: %s" % (str(c), str(ex))) 489 raise NoSuchTable("Cannot find a matching class", *errs)

490

491 492 493 -class _SampleTable(DBx):

494 """Used for L{test}().""" 495 SQL_name = 'SampleTable' 496 COL_types = (('name', 'text'), ('some_value', 'integer')) 497

498 - def __init__(self, name, some_value, db=None):

499 assert name is not None 500 DBx.__init__(self, db) 501 self.name = name 502 self.some_value = int(some_value)

503

504 - def write(self):

505 return self._write(self.name, self.some_value)

506 507 @classmethod

508 - def _fromtuple(cls, db, name, some_value):

509 return cls(name, some_value, db=db)

510

511 512 -class _InconsistentTable(DBx):

513 """Used for L{test}().""" 514 SQL_name = 'SampleTable' 515 COL_types = (('name', 'text'), ('foo', 'text')) 516

517 - def __init__(self, name, some_value, db=None):

518 assert name is not None 519 DBx.__init__(self, db) 520 self.name = name 521 self.some_value = some_value

522

523 - def write(self):

524 return self._write(self.name, self.some_value)

525 526 @classmethod

527 - def _fromtuple(cls, db, name, some_value):

528 return cls(name, some_value, db=db)

529

530 531 -def test():

532 global DEBUG 533 # DEBUG=10 534 db = sqlite3.connect(":memory:") 535 _SampleTable.create(db) 536 _SampleTable.confirm(db) 537 _SampleTable.index_nonunique(db, 'name') 538 ok = False 539 try: 540 _InconsistentTable.confirm(db) 541 except ColumnMismatchError: 542 ok = True 543 assert ok 544 _SampleTable.set_ID(db); 545 assert _SampleTable.idx == 0, "Expected idx=0, got %d" % _SampleTable.idx 546 # Create rows 547 _SampleTable('my name', 3, db=db).write() 548 assert _SampleTable.idx == 1, "Expected idx=1, got %d" % _SampleTable.idx 549 _SampleTable('another name', 2, db=db).write() 550 assert _SampleTable.idx == 2, "Expected idx=2, got %d" % _SampleTable.idx 551 # Select a row: 552 n = 0 553 for x in _SampleTable.select(db, "where name = ?", "my name"): 554 n += 1 555 assert x.some_value == 3 556 assert n == 1 557 assert _SampleTable.idx == 2, "Expected idx=2, got %d" % _SampleTable.idx 558 # Get an object and modify it: 559 tmp = list(_SampleTable.select(db, "where name = ?", "my name"))[0] 560 tmp.some_value = 7 561 # Check that the value is correct: 562 tmp2 = list(_SampleTable.select(db, "where name = ?", "my name"))[0] 563 assert tmp2.some_value == 7 564 # Force a write to the database: 565 tmp.make_dirty() 566 tmp.write() 567 del tmp 568 del tmp2 569 # Check that the value is correct: 570 tmp2 = list(_SampleTable.select(db, "where name = ?", "my name"))[0] 571 assert tmp2.some_value == 7

572 573 if __name__ == '__main__': 574 test() 575

Source Code for Module gmisclib.sqlbase