Mercurial > p > roundup > code

annotate roundup/backends/back_sqlite.py @ 2075:b1704ba7be41

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
author Richard Jones <richard@users.sourceforge.net>
date Fri, 12 Mar 2004 04:09:00 +0000
parents 261c2e6ceb1e
children 3e0961d6d44d
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
1 # $Id: back_sqlite.py,v 1.15 2004-03-12 04:08:59 richard Exp $
2005
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:
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
33 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
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
37 self.init_dbschema()
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)')
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
41 self.create_version_2_tables()
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
42
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
43 def create_version_2_tables(self):
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
44 self.cursor.execute('create table otks (otk_key varchar, '
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
45 'otk_value varchar, otk_time varchar)')
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
46 self.cursor.execute('create index otks_key_idx on otks(otk_key)')
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
47 self.cursor.execute('create table sessions (s_key varchar, '
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
48 's_last_use varchar, s_user varchar)')
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
49 self.cursor.execute('create index sessions_key_idx on sessions(s_key)')
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
50
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
51 def sql_close(self):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
52 ''' Squash any error caused by us already having closed the
1236
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
53 connection.
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
54 '''
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
55 try:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
56 self.conn.close()
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
57 except sqlite.ProgrammingError, value:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
58 if str(value) != 'close failed - Connection is closed.':
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
59 raise
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
60
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
61 def sql_rollback(self):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
62 ''' 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
63 therefore not having anything to roll back)
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
64 '''
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
65 try:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
66 self.conn.rollback()
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
67 except sqlite.ProgrammingError, value:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
68 if str(value) != 'rollback failed - Connection is closed.':
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
69 raise
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
70
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
71 def __repr__(self):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
72 return '<roundlite 0x%x>'%id(self)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
73
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
74 def sql_commit(self):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
75 ''' Actually commit to the database.
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
76
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
77 Ignore errors if there's nothing to commit.
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
78 '''
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
79 try:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
80 self.conn.commit()
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
81 except sqlite.DatabaseError, error:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
82 if str(error) != 'cannot commit - no transaction is active':
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
83 raise
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
84
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
85 def sql_index_exists(self, table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
86 self.cursor.execute('pragma index_list(%s)'%table_name)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
87 for entry in self.cursor.fetchall():
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
88 if entry[1] == index_name:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
89 return 1
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
90 return 0
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
91
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
92 class sqliteClass:
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
93 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
94 group=(None,None)):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
95 ''' 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
96 instead of nothing
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
97 '''
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
98 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
99 filterspec, sort=sort, group=group))
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
100
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
101 class Class(sqliteClass, rdbms_common.Class):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
102 pass
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
103
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
104 class IssueClass(sqliteClass, rdbms_common.IssueClass):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
105 pass
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
106
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
107 class FileClass(sqliteClass, rdbms_common.FileClass):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
108 pass
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
109
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
110
Roundup Issue Tracker: http://roundup-tracker.org/