Mercurial > p > roundup > code

annotate roundup/backends/back_sqlite.py @ 1911:f5c804379c85

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