1
2
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
39
43 """Any errors raised by this module. This does not include errors raised by sqlite itself."""
46
50
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
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
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
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
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
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
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
146
150
151 @classmethod
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
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
216
217 @classmethod
220
221 @classmethod
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
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 """
265 """@param connection: a database connection.
266 @type connection: L{sqlite3.Connection}
267 """
268 DB.__init__(self, connection)
269
270
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
288
289
290
291
292
293
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
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
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
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
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
349
350
351 cls.bump_id(-1)
352 return rv
353
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
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
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
418 rv = []
419 assert len(x) == ioff
420 for (cls, i) in frt:
421
422 idx = x[i]
423 try:
424 tmp = cls._cache[idx]
425
426 except KeyError:
427 tmp = cls._fromtuple_id(db, *(x[i:i+len(cls.COL_types)+1]))
428 cls._cache[idx] = tmp
429
430 rv.append(tmp)
431
432 yield tuple(rv)
433
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
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
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):
503
505 return self._write(self.name, self.some_value)
506
507 @classmethod
509 return cls(name, some_value, db=db)
510
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):
522
524 return self._write(self.name, self.some_value)
525
526 @classmethod
528 return cls(name, some_value, db=db)
529
532 global DEBUG
533
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
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
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
559 tmp = list(_SampleTable.select(db, "where name = ?", "my name"))[0]
560 tmp.some_value = 7
561
562 tmp2 = list(_SampleTable.select(db, "where name = ?", "my name"))[0]
563 assert tmp2.some_value == 7
564
565 tmp.make_dirty()
566 tmp.write()
567 del tmp
568 del tmp2
569
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