245240f878142323f84a37c9b297581ff4714c9d
1 """Implements a backend for SQLite.
3 See https://pysqlite.sourceforge.net/ for pysqlite info
6 NOTE: we use the rdbms_common table creation methods which define datatypes
7 for the columns, but sqlite IGNORES these specifications.
8 """
9 __docformat__ = 'restructuredtext'
11 import os, base64, marshal, shutil, time, logging
13 from roundup import hyperdb, date, password
14 from roundup.backends import rdbms_common
15 sqlite_version = None
16 try:
17 import sqlite3 as sqlite
18 sqlite_version = 3
19 except ImportError:
20 try:
21 from pysqlite2 import dbapi2 as sqlite
22 if sqlite.version_info < (2,1,0):
23 raise ValueError('pysqlite2 minimum version is 2.1.0+ '
24 '- %s found'%sqlite.version)
25 sqlite_version = 2
26 except ImportError:
27 import sqlite
28 sqlite_version = 1
30 def db_exists(config):
31 return os.path.exists(os.path.join(config.DATABASE, 'db'))
33 def db_nuke(config):
34 shutil.rmtree(config.DATABASE)
36 class Database(rdbms_common.Database):
37 # char to use for positional arguments
38 if sqlite_version in (2,3):
39 arg = '?'
40 else:
41 arg = '%s'
43 # used by some code to switch styles of query
44 implements_intersect = 1
46 hyperdb_to_sql_datatypes = {
47 hyperdb.String : 'VARCHAR(255)',
48 hyperdb.Date : 'VARCHAR(30)',
49 hyperdb.Link : 'INTEGER',
50 hyperdb.Interval : 'VARCHAR(255)',
51 hyperdb.Password : 'VARCHAR(255)',
52 hyperdb.Boolean : 'BOOLEAN',
53 hyperdb.Number : 'REAL',
54 }
55 hyperdb_to_sql_value = {
56 hyperdb.String : str,
57 hyperdb.Date : lambda x: x.serialise(),
58 hyperdb.Link : int,
59 hyperdb.Interval : str,
60 hyperdb.Password : str,
61 hyperdb.Boolean : int,
62 hyperdb.Number : lambda x: x,
63 hyperdb.Multilink : lambda x: x, # used in journal marshalling
64 }
65 sql_to_hyperdb_value = {
66 hyperdb.String : lambda x: isinstance(x, unicode) and x.encode('utf8') or str(x),
67 hyperdb.Date : lambda x: date.Date(str(x)),
68 hyperdb.Link : str, # XXX numeric ids
69 hyperdb.Interval : date.Interval,
70 hyperdb.Password : lambda x: password.Password(encrypted=x),
71 hyperdb.Boolean : int,
72 hyperdb.Number : rdbms_common._num_cvt,
73 hyperdb.Multilink : lambda x: x, # used in journal marshalling
74 }
76 def sqlite_busy_handler(self, data, table, count):
77 """invoked whenever SQLite tries to access a database that is locked"""
78 if count == 1:
79 # use a 30 second timeout (extraordinarily generous)
80 # for handling locked database
81 self._busy_handler_endtime = time.time() + 30
82 elif time.time() > self._busy_handler_endtime:
83 # timeout expired - no more retries
84 return 0
85 # sleep adaptively as retry count grows,
86 # starting from about half a second
87 time_to_sleep = 0.01 * (2 << min(5, count))
88 time.sleep(time_to_sleep)
89 return 1
91 def sql_open_connection(self):
92 """Open a standard, non-autocommitting connection.
94 pysqlite will automatically BEGIN TRANSACTION for us.
95 """
96 # make sure the database directory exists
97 # database itself will be created by sqlite if needed
98 if not os.path.isdir(self.config.DATABASE):
99 os.makedirs(self.config.DATABASE)
101 db = os.path.join(self.config.DATABASE, 'db')
102 logging.getLogger('hyperdb').info('open database %r'%db)
103 # set a 30 second timeout (extraordinarily generous) for handling
104 # locked database
105 if sqlite_version == 1:
106 conn = sqlite.connect(db=db)
107 conn.db.sqlite_busy_handler(self.sqlite_busy_handler)
108 else:
109 conn = sqlite.connect(db, timeout=30)
110 conn.row_factory = sqlite.Row
112 # pysqlite2 / sqlite3 want us to store Unicode in the db but
113 # that's not what's been done historically and it's definitely
114 # not what the other backends do, so we'll stick with UTF-8
115 if sqlite_version in (2, 3):
116 conn.text_factory = str
118 cursor = conn.cursor()
119 return (conn, cursor)
121 def open_connection(self):
122 # ensure files are group readable and writable
123 os.umask(self.config.UMASK)
125 (self.conn, self.cursor) = self.sql_open_connection()
127 try:
128 self.load_dbschema()
129 except sqlite.DatabaseError, error:
130 if str(error) != 'no such table: schema':
131 raise
132 self.init_dbschema()
133 self.sql('create table schema (schema varchar)')
134 self.sql('create table ids (name varchar, num integer)')
135 self.sql('create index ids_name_idx on ids(name)')
136 self.create_version_2_tables()
138 def create_version_2_tables(self):
139 self.sql('create table otks (otk_key varchar, '
140 'otk_value varchar, otk_time integer)')
141 self.sql('create index otks_key_idx on otks(otk_key)')
142 self.sql('create table sessions (session_key varchar, '
143 'session_time integer, session_value varchar)')
144 self.sql('create index sessions_key_idx on '
145 'sessions(session_key)')
147 # full-text indexing store
148 self.sql('CREATE TABLE __textids (_class varchar, '
149 '_itemid varchar, _prop varchar, _textid integer primary key) ')
150 self.sql('CREATE TABLE __words (_word varchar, '
151 '_textid integer)')
152 self.sql('CREATE INDEX words_word_ids ON __words(_word)')
153 self.sql('CREATE INDEX words_by_id ON __words (_textid)')
154 self.sql('CREATE UNIQUE INDEX __textids_by_props ON '
155 '__textids (_class, _itemid, _prop)')
156 sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg)
157 self.sql(sql, ('__textids', 1))
159 def add_new_columns_v2(self):
160 # update existing tables to have the new actor column
161 tables = self.database_schema['tables']
162 for classname, spec in self.classes.items():
163 if tables.has_key(classname):
164 dbspec = tables[classname]
165 self.update_class(spec, dbspec, force=1, adding_v2=1)
166 # we've updated - don't try again
167 tables[classname] = spec.schema()
169 def fix_version_3_tables(self):
170 # NOOP - no restriction on column length here
171 pass
173 def update_class(self, spec, old_spec, force=0, adding_v2=0):
174 """ Determine the differences between the current spec and the
175 database version of the spec, and update where necessary.
177 If 'force' is true, update the database anyway.
179 SQLite doesn't have ALTER TABLE, so we have to copy and
180 regenerate the tables with the new schema.
181 """
182 new_has = spec.properties.has_key
183 new_spec = spec.schema()
184 new_spec[1].sort()
185 old_spec[1].sort()
186 if not force and new_spec == old_spec:
187 # no changes
188 return 0
190 logging.getLogger('hyperdb').info('update_class %s'%spec.classname)
192 # detect multilinks that have been removed, and drop their table
193 old_has = {}
194 for name, prop in old_spec[1]:
195 old_has[name] = 1
196 if new_has(name) or not isinstance(prop, hyperdb.Multilink):
197 continue
198 # it's a multilink, and it's been removed - drop the old
199 # table. First drop indexes.
200 self.drop_multilink_table_indexes(spec.classname, name)
201 sql = 'drop table %s_%s'%(spec.classname, prop)
202 self.sql(sql)
203 old_has = old_has.has_key
205 # now figure how we populate the new table
206 if adding_v2:
207 fetch = ['_activity', '_creation', '_creator']
208 else:
209 fetch = ['_actor', '_activity', '_creation', '_creator']
210 properties = spec.getprops()
211 for propname,x in new_spec[1]:
212 prop = properties[propname]
213 if isinstance(prop, hyperdb.Multilink):
214 if not old_has(propname):
215 # we need to create the new table
216 self.create_multilink_table(spec, propname)
217 elif force:
218 tn = '%s_%s'%(spec.classname, propname)
219 # grabe the current values
220 sql = 'select linkid, nodeid from %s'%tn
221 self.sql(sql)
222 rows = self.cursor.fetchall()
224 # drop the old table
225 self.drop_multilink_table_indexes(spec.classname, propname)
226 sql = 'drop table %s'%tn
227 self.sql(sql)
229 # re-create and populate the new table
230 self.create_multilink_table(spec, propname)
231 sql = """insert into %s (linkid, nodeid) values
232 (%s, %s)"""%(tn, self.arg, self.arg)
233 for linkid, nodeid in rows:
234 self.sql(sql, (int(linkid), int(nodeid)))
235 elif old_has(propname):
236 # we copy this col over from the old table
237 fetch.append('_'+propname)
239 # select the data out of the old table
240 fetch.append('id')
241 fetch.append('__retired__')
242 fetchcols = ','.join(fetch)
243 cn = spec.classname
244 sql = 'select %s from _%s'%(fetchcols, cn)
245 self.sql(sql)
246 olddata = self.cursor.fetchall()
248 # TODO: update all the other index dropping code
249 self.drop_class_table_indexes(cn, old_spec[0])
251 # drop the old table
252 self.sql('drop table _%s'%cn)
254 # create the new table
255 self.create_class_table(spec)
257 if olddata:
258 inscols = ['id', '_actor', '_activity', '_creation', '_creator']
259 for propname,x in new_spec[1]:
260 prop = properties[propname]
261 if isinstance(prop, hyperdb.Multilink):
262 continue
263 elif isinstance(prop, hyperdb.Interval):
264 inscols.append('_'+propname)
265 inscols.append('__'+propname+'_int__')
266 elif old_has(propname):
267 # we copy this col over from the old table
268 inscols.append('_'+propname)
270 # do the insert of the old data - the new columns will have
271 # NULL values
272 args = ','.join([self.arg for x in inscols])
273 cols = ','.join(inscols)
274 sql = 'insert into _%s (%s) values (%s)'%(cn, cols, args)
275 for entry in olddata:
276 d = []
277 for name in inscols:
278 # generate the new value for the Interval int column
279 if name.endswith('_int__'):
280 name = name[2:-6]
281 if sqlite_version in (2,3):
282 try:
283 v = hyperdb.Interval(entry[name]).as_seconds()
284 except IndexError:
285 v = None
286 elif entry.has_key(name):
287 v = hyperdb.Interval(entry[name]).as_seconds()
288 else:
289 v = None
290 elif sqlite_version in (2,3):
291 try:
292 v = entry[name]
293 except IndexError:
294 v = None
295 elif (sqlite_version == 1 and entry.has_key(name)):
296 v = entry[name]
297 else:
298 v = None
299 d.append(v)
300 self.sql(sql, tuple(d))
302 return 1
304 def sql_close(self):
305 """ Squash any error caused by us already having closed the
306 connection.
307 """
308 try:
309 self.conn.close()
310 except sqlite.ProgrammingError, value:
311 if str(value) != 'close failed - Connection is closed.':
312 raise
314 def sql_rollback(self):
315 """ Squash any error caused by us having closed the connection (and
316 therefore not having anything to roll back)
317 """
318 try:
319 self.conn.rollback()
320 except sqlite.ProgrammingError, value:
321 if str(value) != 'rollback failed - Connection is closed.':
322 raise
324 def __repr__(self):
325 return '<roundlite 0x%x>'%id(self)
327 def sql_commit(self, fail_ok=False):
328 """ Actually commit to the database.
330 Ignore errors if there's nothing to commit.
331 """
332 try:
333 self.conn.commit()
334 except sqlite.DatabaseError, error:
335 if str(error) != 'cannot commit - no transaction is active':
336 raise
337 # open a new cursor for subsequent work
338 self.cursor = self.conn.cursor()
340 def sql_index_exists(self, table_name, index_name):
341 self.sql('pragma index_list(%s)'%table_name)
342 for entry in self.cursor.fetchall():
343 if entry[1] == index_name:
344 return 1
345 return 0
347 # old-skool id generation
348 def newid(self, classname):
349 """ Generate a new id for the given class
350 """
351 # get the next ID
352 sql = 'select num from ids where name=%s'%self.arg
353 self.sql(sql, (classname, ))
354 newid = int(self.cursor.fetchone()[0])
356 # update the counter
357 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
358 vals = (int(newid)+1, classname)
359 self.sql(sql, vals)
361 # return as string
362 return str(newid)
364 def setid(self, classname, setid):
365 """ Set the id counter: used during import of database
367 We add one to make it behave like the sequences in postgres.
368 """
369 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
370 vals = (int(setid)+1, classname)
371 self.sql(sql, vals)
373 def clear(self):
374 rdbms_common.Database.clear(self)
375 # set the id counters to 0 (setid adds one) so we start at 1
376 for cn in self.classes.keys():
377 self.setid(cn, 0)
379 def create_class(self, spec):
380 rdbms_common.Database.create_class(self, spec)
381 sql = 'insert into ids (name, num) values (%s, %s)'%(self.arg, self.arg)
382 vals = (spec.classname, 1)
383 self.sql(sql, vals)
385 if sqlite_version in (2,3):
386 def load_journal(self, classname, cols, nodeid):
387 """We need to turn the sqlite3.Row into a tuple so it can be
388 unpacked"""
389 l = rdbms_common.Database.load_journal(self,
390 classname, cols, nodeid)
391 cols = range(5)
392 return [[row[col] for col in cols] for row in l]
394 class sqliteClass:
395 def filter(self, search_matches, filterspec, sort=(None,None),
396 group=(None,None)):
397 """ If there's NO matches to a fetch, sqlite returns NULL
398 instead of nothing
399 """
400 return filter(None, rdbms_common.Class.filter(self, search_matches,
401 filterspec, sort=sort, group=group))
403 class Class(sqliteClass, rdbms_common.Class):
404 pass
406 class IssueClass(sqliteClass, rdbms_common.IssueClass):
407 pass
409 class FileClass(sqliteClass, rdbms_common.FileClass):
410 pass
412 # vim: set et sts=4 sw=4 :