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 now = time.time()
79 if count == 1:
80 # Timeout for handling locked database (default 30s)
81 self._busy_handler_endtime = now + self.config.RDBMS_SQLITE_TIMEOUT
82 elif now > 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('roundup.hyperdb').info('open database %r'%db)
103 # set timeout (30 second default is extraordinarily generous)
104 # for handling 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=self.config.RDBMS_SQLITE_TIMEOUT)
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 classname in tables:
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_spec = spec.schema()
183 new_spec[1].sort()
184 old_spec[1].sort()
185 if not force and new_spec == old_spec:
186 # no changes
187 return 0
189 logging.getLogger('roundup.hyperdb').info(
190 '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 name in spec.properties 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)
204 # now figure how we populate the new table
205 if adding_v2:
206 fetch = ['_activity', '_creation', '_creator']
207 else:
208 fetch = ['_actor', '_activity', '_creation', '_creator']
209 properties = spec.getprops()
210 for propname,x in new_spec[1]:
211 prop = properties[propname]
212 if isinstance(prop, hyperdb.Multilink):
213 if propname not in old_has:
214 # we need to create the new table
215 self.create_multilink_table(spec, propname)
216 elif force:
217 tn = '%s_%s'%(spec.classname, propname)
218 # grabe the current values
219 sql = 'select linkid, nodeid from %s'%tn
220 self.sql(sql)
221 rows = self.cursor.fetchall()
223 # drop the old table
224 self.drop_multilink_table_indexes(spec.classname, propname)
225 sql = 'drop table %s'%tn
226 self.sql(sql)
228 # re-create and populate the new table
229 self.create_multilink_table(spec, propname)
230 sql = """insert into %s (linkid, nodeid) values
231 (%s, %s)"""%(tn, self.arg, self.arg)
232 for linkid, nodeid in rows:
233 self.sql(sql, (int(linkid), int(nodeid)))
234 elif propname in old_has:
235 # we copy this col over from the old table
236 fetch.append('_'+propname)
238 # select the data out of the old table
239 fetch.append('id')
240 fetch.append('__retired__')
241 fetchcols = ','.join(fetch)
242 cn = spec.classname
243 sql = 'select %s from _%s'%(fetchcols, cn)
244 self.sql(sql)
245 olddata = self.cursor.fetchall()
247 # TODO: update all the other index dropping code
248 self.drop_class_table_indexes(cn, old_spec[0])
250 # drop the old table
251 self.sql('drop table _%s'%cn)
253 # create the new table
254 self.create_class_table(spec)
256 if olddata:
257 inscols = ['id', '_actor', '_activity', '_creation', '_creator']
258 for propname,x in new_spec[1]:
259 prop = properties[propname]
260 if isinstance(prop, hyperdb.Multilink):
261 continue
262 elif isinstance(prop, hyperdb.Interval):
263 inscols.append('_'+propname)
264 inscols.append('__'+propname+'_int__')
265 elif propname in old_has:
266 # we copy this col over from the old table
267 inscols.append('_'+propname)
269 # do the insert of the old data - the new columns will have
270 # NULL values
271 args = ','.join([self.arg for x in inscols])
272 cols = ','.join(inscols)
273 sql = 'insert into _%s (%s) values (%s)'%(cn, cols, args)
274 for entry in olddata:
275 d = []
276 for name in inscols:
277 # generate the new value for the Interval int column
278 if name.endswith('_int__'):
279 name = name[2:-6]
280 if sqlite_version in (2,3):
281 try:
282 v = hyperdb.Interval(entry[name]).as_seconds()
283 except IndexError:
284 v = None
285 elif name in entry:
286 v = hyperdb.Interval(entry[name]).as_seconds()
287 else:
288 v = None
289 elif sqlite_version in (2,3):
290 try:
291 v = entry[name]
292 except IndexError:
293 v = None
294 elif (sqlite_version == 1 and name in entry):
295 v = entry[name]
296 else:
297 v = None
298 d.append(v)
299 self.sql(sql, tuple(d))
301 return 1
303 def sql_close(self):
304 """ Squash any error caused by us already having closed the
305 connection.
306 """
307 try:
308 self.conn.close()
309 except sqlite.ProgrammingError, value:
310 if str(value) != 'close failed - Connection is closed.':
311 raise
313 def sql_rollback(self):
314 """ Squash any error caused by us having closed the connection (and
315 therefore not having anything to roll back)
316 """
317 try:
318 self.conn.rollback()
319 except sqlite.ProgrammingError, value:
320 if str(value) != 'rollback failed - Connection is closed.':
321 raise
323 def __repr__(self):
324 return '<roundlite 0x%x>'%id(self)
326 def sql_commit(self, fail_ok=False):
327 """ Actually commit to the database.
329 Ignore errors if there's nothing to commit.
330 """
331 try:
332 self.conn.commit()
333 except sqlite.DatabaseError, error:
334 if str(error) != 'cannot commit - no transaction is active':
335 raise
336 # open a new cursor for subsequent work
337 self.cursor = self.conn.cursor()
339 def sql_index_exists(self, table_name, index_name):
340 self.sql('pragma index_list(%s)'%table_name)
341 for entry in self.cursor.fetchall():
342 if entry[1] == index_name:
343 return 1
344 return 0
346 # old-skool id generation
347 def newid(self, classname):
348 """ Generate a new id for the given class
349 """
350 # get the next ID
351 sql = 'select num from ids where name=%s'%self.arg
352 self.sql(sql, (classname, ))
353 newid = int(self.cursor.fetchone()[0])
355 # update the counter
356 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
357 vals = (int(newid)+1, classname)
358 self.sql(sql, vals)
360 # return as string
361 return str(newid)
363 def setid(self, classname, setid):
364 """ Set the id counter: used during import of database
366 We add one to make it behave like the sequences in postgres.
367 """
368 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
369 vals = (int(setid)+1, classname)
370 self.sql(sql, vals)
372 def clear(self):
373 rdbms_common.Database.clear(self)
374 # set the id counters to 0 (setid adds one) so we start at 1
375 for cn in self.classes.keys():
376 self.setid(cn, 0)
378 def create_class(self, spec):
379 rdbms_common.Database.create_class(self, spec)
380 sql = 'insert into ids (name, num) values (%s, %s)'%(self.arg, self.arg)
381 vals = (spec.classname, 1)
382 self.sql(sql, vals)
384 if sqlite_version in (2,3):
385 def load_journal(self, classname, cols, nodeid):
386 """We need to turn the sqlite3.Row into a tuple so it can be
387 unpacked"""
388 l = rdbms_common.Database.load_journal(self,
389 classname, cols, nodeid)
390 cols = range(5)
391 return [[row[col] for col in cols] for row in l]
393 class sqliteClass:
394 def filter(self, search_matches, filterspec, sort=(None,None),
395 group=(None,None)):
396 """ If there's NO matches to a fetch, sqlite returns NULL
397 instead of nothing
398 """
399 return [f for f in rdbms_common.Class.filter(self, search_matches,
400 filterspec, sort=sort, group=group) if f]
402 class Class(sqliteClass, rdbms_common.Class):
403 pass
405 class IssueClass(sqliteClass, rdbms_common.IssueClass):
406 pass
408 class FileClass(sqliteClass, rdbms_common.FileClass):
409 pass
411 # vim: set et sts=4 sw=4 :