Mercurial > p > roundup > code

annotate roundup/backends/back_sqlite.py @ 2005:fc52d57c6c3e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
documentation cleanup
author Richard Jones <richard@users.sourceforge.net>
date Wed, 11 Feb 2004 23:55:10 +0000
parents f5c804379c85
children 261c2e6ceb1e
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
2005
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1911
diff changeset
1 # $Id: back_sqlite.py,v 1.13 2004-02-11 23:55:09 richard Exp $
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1911
diff changeset
2 '''Implements a backend for SQLite.
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1911
diff changeset
3
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
4 See https://pysqlite.sourceforge.net/ for pysqlite info
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
5 '''
2005
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1911
diff changeset
6 __docformat__ = 'restructuredtext'
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1911
diff changeset
7
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
8 import os, base64, marshal
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
9
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
10 from roundup import hyperdb
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
11 from roundup.backends import rdbms_common
1333
80d27b7d6db5 implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents: 1252
diff changeset
12 from roundup.backends import locking
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
13 import sqlite
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
14
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
15 class Database(rdbms_common.Database):
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
16 # char to use for positional arguments
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
17 arg = '%s'
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
18
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
19 def sql_open_connection(self):
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
20 # ensure files are group readable and writable
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
21 os.umask(0002)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
22 db = os.path.join(self.config.DATABASE, 'db')
1333
80d27b7d6db5 implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents: 1252
diff changeset
23
80d27b7d6db5 implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents: 1252
diff changeset
24 # lock it
80d27b7d6db5 implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents: 1252
diff changeset
25 lockfilenm = db[:-3] + 'lck'
80d27b7d6db5 implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents: 1252
diff changeset
26 self.lockfile = locking.acquire_lock(lockfilenm)
80d27b7d6db5 implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents: 1252
diff changeset
27 self.lockfile.write(str(os.getpid()))
80d27b7d6db5 implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents: 1252
diff changeset
28 self.lockfile.flush()
80d27b7d6db5 implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents: 1252
diff changeset
29
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
30 self.conn = sqlite.connect(db=db)
1183
08a13a84ed43 Some speedups - both of the SQL backends can handle using only one cursor.
Richard Jones <richard@users.sourceforge.net>
parents: 1170
diff changeset
31 self.cursor = self.conn.cursor()
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
32 try:
1183
08a13a84ed43 Some speedups - both of the SQL backends can handle using only one cursor.
Richard Jones <richard@users.sourceforge.net>
parents: 1170
diff changeset
33 self.database_schema = self.load_dbschema()
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
34 except sqlite.DatabaseError, error:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
35 if str(error) != 'no such table: schema':
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
36 raise
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
37 self.database_schema = {}
1183
08a13a84ed43 Some speedups - both of the SQL backends can handle using only one cursor.
Richard Jones <richard@users.sourceforge.net>
parents: 1170
diff changeset
38 self.cursor.execute('create table schema (schema varchar)')
08a13a84ed43 Some speedups - both of the SQL backends can handle using only one cursor.
Richard Jones <richard@users.sourceforge.net>
parents: 1170
diff changeset
39 self.cursor.execute('create table ids (name varchar, num integer)')
1836
94e430ad4fdb make the RDBMS common backend and the SQLite and MYsql backend create...
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 1492
diff changeset
40 self.cursor.execute('create index ids_name_idx on ids(name)')
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
41
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
42 def sql_close(self):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
43 ''' Squash any error caused by us already having closed the
1236
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
44 connection.
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
45 '''
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
46 try:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
47 self.conn.close()
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
48 except sqlite.ProgrammingError, value:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
49 if str(value) != 'close failed - Connection is closed.':
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
50 raise
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
51
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
52 def sql_rollback(self):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
53 ''' Squash any error caused by us having closed the connection (and
1236
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
54 therefore not having anything to roll back)
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
55 '''
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
56 try:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
57 self.conn.rollback()
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
58 except sqlite.ProgrammingError, value:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
59 if str(value) != 'rollback failed - Connection is closed.':
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
60 raise
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
61
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
62 def __repr__(self):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
63 return '<roundlite 0x%x>'%id(self)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
64
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
65 def sql_commit(self):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
66 ''' Actually commit to the database.
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
67
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
68 Ignore errors if there's nothing to commit.
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
69 '''
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
70 try:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
71 self.conn.commit()
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
72 except sqlite.DatabaseError, error:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
73 if str(error) != 'cannot commit - no transaction is active':
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
74 raise
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
75
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
76 def sql_index_exists(self, table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
77 self.cursor.execute('pragma index_list(%s)'%table_name)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
78 for entry in self.cursor.fetchall():
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
79 if entry[1] == index_name:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
80 return 1
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
81 return 0
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
82
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
83 class sqliteClass:
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
84 def filter(self, search_matches, filterspec, sort=(None,None),
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
85 group=(None,None)):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
86 ''' If there's NO matches to a fetch, sqlite returns NULL
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
87 instead of nothing
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
88 '''
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
89 return filter(None, rdbms_common.Class.filter(self, search_matches,
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
90 filterspec, sort=sort, group=group))
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
91
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
92 class Class(sqliteClass, rdbms_common.Class):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
93 pass
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
94
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
95 class IssueClass(sqliteClass, rdbms_common.IssueClass):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
96 pass
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
97
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
98 class FileClass(sqliteClass, rdbms_common.FileClass):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
99 pass
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
100
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
101
Roundup Issue Tracker: http://roundup-tracker.org/