1 # $Id: back_sqlite.py,v 1.15 2004-03-12 04:08:59 richard Exp $
2 '''Implements a backend for SQLite.
4 See https://pysqlite.sourceforge.net/ for pysqlite info
5 '''
6 __docformat__ = 'restructuredtext'
8 import os, base64, marshal
10 from roundup import hyperdb
11 from roundup.backends import rdbms_common
12 from roundup.backends import locking
13 import sqlite
15 class Database(rdbms_common.Database):
16 # char to use for positional arguments
17 arg = '%s'
19 def sql_open_connection(self):
20 # ensure files are group readable and writable
21 os.umask(0002)
22 db = os.path.join(self.config.DATABASE, 'db')
24 # lock it
25 lockfilenm = db[:-3] + 'lck'
26 self.lockfile = locking.acquire_lock(lockfilenm)
27 self.lockfile.write(str(os.getpid()))
28 self.lockfile.flush()
30 self.conn = sqlite.connect(db=db)
31 self.cursor = self.conn.cursor()
32 try:
33 self.load_dbschema()
34 except sqlite.DatabaseError, error:
35 if str(error) != 'no such table: schema':
36 raise
37 self.init_dbschema()
38 self.cursor.execute('create table schema (schema varchar)')
39 self.cursor.execute('create table ids (name varchar, num integer)')
40 self.cursor.execute('create index ids_name_idx on ids(name)')
41 self.create_version_2_tables()
43 def create_version_2_tables(self):
44 self.cursor.execute('create table otks (otk_key varchar, '
45 'otk_value varchar, otk_time varchar)')
46 self.cursor.execute('create index otks_key_idx on otks(otk_key)')
47 self.cursor.execute('create table sessions (s_key varchar, '
48 's_last_use varchar, s_user varchar)')
49 self.cursor.execute('create index sessions_key_idx on sessions(s_key)')
51 def sql_close(self):
52 ''' Squash any error caused by us already having closed the
53 connection.
54 '''
55 try:
56 self.conn.close()
57 except sqlite.ProgrammingError, value:
58 if str(value) != 'close failed - Connection is closed.':
59 raise
61 def sql_rollback(self):
62 ''' Squash any error caused by us having closed the connection (and
63 therefore not having anything to roll back)
64 '''
65 try:
66 self.conn.rollback()
67 except sqlite.ProgrammingError, value:
68 if str(value) != 'rollback failed - Connection is closed.':
69 raise
71 def __repr__(self):
72 return '<roundlite 0x%x>'%id(self)
74 def sql_commit(self):
75 ''' Actually commit to the database.
77 Ignore errors if there's nothing to commit.
78 '''
79 try:
80 self.conn.commit()
81 except sqlite.DatabaseError, error:
82 if str(error) != 'cannot commit - no transaction is active':
83 raise
85 def sql_index_exists(self, table_name, index_name):
86 self.cursor.execute('pragma index_list(%s)'%table_name)
87 for entry in self.cursor.fetchall():
88 if entry[1] == index_name:
89 return 1
90 return 0
92 class sqliteClass:
93 def filter(self, search_matches, filterspec, sort=(None,None),
94 group=(None,None)):
95 ''' If there's NO matches to a fetch, sqlite returns NULL
96 instead of nothing
97 '''
98 return filter(None, rdbms_common.Class.filter(self, search_matches,
99 filterspec, sort=sort, group=group))
101 class Class(sqliteClass, rdbms_common.Class):
102 pass
104 class IssueClass(sqliteClass, rdbms_common.IssueClass):
105 pass
107 class FileClass(sqliteClass, rdbms_common.FileClass):
108 pass