conduit.gtkui.Database

34 """ 35 gtk.TreeModel implementation that saves and stores data directly 36 to and from a sqlite database. A simple LRU cache is included to 37 lower the number of required SQL queries. 38 """ 39 40 OID_CACHE = True 41

42 - def __init__(self, table, genericDB):

43 """ 44 Creates a new GenericDBListStore. 45 46 Parameters: 47 filename -- the filename of the sqlite database. 48 table -- the name of the table to manage. 49 """ 50 gtk.GenericTreeModel.__init__(self) 51 genericDB.connect("row-inserted",self._on_inserted) 52 genericDB.connect("row-deleted",self._on_deleted) 53 genericDB.connect("row-modified",self._on_modified) 54 55 self.table = table 56 self.db = genericDB 57 self.oidcache = [] 58 self.columns = self._get_columns()

59

60 - def _on_inserted(self, db, oid):

61 self.oidcache = [] 62 offset = self._get_offset(oid) 63 rowref = self.get_iter(offset) 64 path = self.get_path(rowref) 65 self.row_inserted(path, rowref)

66

67 - def _on_modified(self, db, oid):

68 self.oidcache = [] 69 offset = self._get_offset(oid) 70 rowref = self.get_iter(offset) 71 path = self.get_path(rowref) 72 self.row_changed(path, rowref)

73

74 - def _on_deleted(self, db, oid):

75 self.oidcache = [] 76 offset = self._get_offset(oid) 77 rowref = self.get_iter(offset) 78 path = self.get_path(rowref) 79 self.row_deleted(path)

80

81 - def _get_n_rows(self):

82 """ 83 Returns the number of rows found in our loaded table inside 84 the sqlite database. 85 """ 86 (rows,) = self.db.select_one("SELECT COUNT(oid) FROM %s" % self.table) 87 return rows

88

89 - def _get_columns(self):

90 """ 91 Returns the number of columns found in our sqlite table. 92 """ 93 return ('oid',) + tuple(self.db.get_fields(self.table))

94 95 @DB.lru_cache(0)

96 - def _get_oid(self, offset):

97 """ 98 Returns the oid of the row at offset. 99 100 Parameters: 101 offset -- the rows offset from 0. 102 """ 103 (oid,) = self.db.select_one("SELECT oid FROM %s LIMIT 1 OFFSET %d" % (self.table, offset)) 104 return oid

105 106 @DB.lru_cache(0)

107 - def _get_value(self, oid, index):

108 """ 109 Returns the value for a column in the table with a row id 110 of oid. 111 112 Parameters: 113 oid -- the rows internal oid. 114 column -- the column index. 115 """ 116 (value,) = self.db.select_one("SELECT %s FROM %s WHERE oid = %d" % (self.columns[index], self.table, oid)) 117 return value

118 119 @DB.lru_cache(0)

120 - def _get_next_oid(self, oid):

121 """ 122 Returns the next oid after passed oid. 123 124 Note: for some reason unknown to me, gtk.TreeView or 125 perhaps the GenericTreeModel finds it neccessary to 126 iterate through every iter from the root node through 127 n_children. Because of this, we will fetch row ids in 128 sets of 1024 and cache them to speed things up. 129 130 Parameters: 131 oid -- the current oid. 132 """ 133 #first call is when oid=None 134 if not oid: 135 oid = -1 136 137 if GenericDBListStore.OID_CACHE: 138 try: 139 index = self.oidcache.index(oid) 140 return self.oidcache[index+1] 141 except (ValueError, IndexError): 142 sql = "SELECT oid FROM %s WHERE oid >= %d LIMIT 1024" % (self.table, oid) 143 oids = [oid for (oid,) in self.db.select(sql)] 144 self.oidcache.extend(oids) 145 self.oidcache = Utils.unique_list(self.oidcache) 146 #if we can only get one result, we must be the last oid 147 if len(oids) > 1: 148 oid = oids[1] 149 else: 150 oid = None 151 else: 152 try: 153 (oid,) = self.db.select_one("SELECT oid FROM %s WHERE oid > %d LIMIT 1" % (self.table, oid)) 154 except TypeError: 155 oid = None 156 157 return oid

158

159 - def _get_offset(self, oid):

160 """ 161 Returns the offset of oid in the sqlite table. 162 163 Parameters: 164 oid -- the oid of the row to check 165 """ 166 (offset,) = self.db.select_one("SELECT COUNT(oid) FROM %s WHERE oid < %d" % (self.table, oid)) 167 return offset

168

169 - def on_get_flags(self):

170 """ 171 Returns the gtk.TreeModelFlags for the gtk.TreeModel 172 implementation. The gtk.TreeIter data is derived from 173 the database oids for records and therefore is persistant 174 across row deletion and inserts. 175 """ 176 return gtk.TREE_MODEL_LIST_ONLY | gtk.TREE_MODEL_ITERS_PERSIST

177

178 - def on_get_n_columns(self):

179 """ 180 Returns the number of columns found in the table metadata. 181 """ 182 return len(self.columns)

183

184 - def on_get_column_type(self, index):

185 """ 186 All columns in sqlite are accessed via (char*). Therefore, 187 all of our column types will pass that right along and 188 allow the consumers to typecast as needed. 189 """ 190 return gobject.TYPE_STRING

191

192 - def on_get_iter(self, path):

193 """ 194 Traslates a gtk.TreePath to a gtk.TreeIter. This is done by 195 finding the oid for the row in the database at the same 196 offset as the path. 197 """ 198 if len(path) > 1: 199 return None #We are a list not a tree 200 try: 201 return self._get_oid(path[0]) 202 except TypeError: 203 return None #DB is empty

204

205 - def on_get_path(self, rowref):

206 """ 207 Returns the rowrefs offset in the table which is used to 208 generate the gtk.TreePath. 209 """ 210 return self._get_offset(rowref)

211

212 - def on_get_value(self, rowref, column):

213 """ 214 Returns the data for a rowref at the givin column. 215 216 Parameters: 217 rowref -- the rowref passed back in the on_get_iter 218 method. 219 column -- the integer offset of the column desired. 220 """ 221 if column > len(self.columns): 222 return None 223 if column == 0: 224 return rowref 225 return self._get_value(rowref, column)

226

227 - def on_iter_next(self, rowref):

228 """ 229 Returns the next oid found in the sqlite table. 230 231 Parameters: 232 rowref -- the oid of the current iter. 233 """ 234 return self._get_next_oid(rowref)

235

236 - def on_iter_children(self, rowref):

237 """ 238 Retruns children for a given rowref. This will always be 239 None unless the rowref is None, which is our root node. 240 241 Parameters: 242 rowref -- the oid of the desired row. 243 """ 244 if rowref: 245 return None 246 return self._get_next_oid(-1)

247

248 - def on_iter_has_child(self, rowref):

249 """ 250 Always returns False as List based TreeModels do not have 251 children. 252 """ 253 return False

254

255 - def on_iter_n_children(self, rowref):

256 """ 257 Returns the number of children a row has. Since only the 258 root node may have children, we return 0 unless the request 259 is made for the count of all rows. Requesting the row count 260 is done by passing None as the rowref. 261 """ 262 if rowref: 263 return 0 264 return self._get_n_rows()

265

266 - def on_iter_nth_child(self, rowref, n):

267 """ 268 Returns the oid of the nth child from rowref. This will 269 only return a value if rowref is None, which is the 270 root node. 271 272 Parameters: 273 rowref -- the oid of the row. 274 n -- the row offset to retrieve. 275 """ 276 if rowref: 277 return None 278 return self._get_oid(n)

279

280 - def on_iter_parent(self, child):

281 """ 282 Always returns None as lists do not have parent nodes. 283 """ 284 return None

Source Code for Module conduit.gtkui.Database