Mercurial > p > roundup > code

annotate roundup/backends/back_sqlite.py @ 1195:e0032f4ab334

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added missing stringFind to sql backends
author Richard Jones <richard@users.sourceforge.net>
date Tue, 24 Sep 2002 01:59:28 +0000
parents 08a13a84ed43
children dd52bf10f934
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1195
e0032f4ab334 added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents: 1183
diff changeset
1 # $Id: back_sqlite.py,v 1.5 2002-09-24 01:59:28 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 '''
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
5 import base64, marshal
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
6 from roundup.backends.rdbms_common import *
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
7 import sqlite
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
8
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
9 class Database(Database):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
10 # char to use for positional arguments
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
11 arg = '%s'
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
12
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
13 def open_connection(self):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
14 # ensure files are group readable and writable
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
15 os.umask(0002)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
16 db = os.path.join(self.config.DATABASE, 'db')
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
17 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
18 self.cursor = self.conn.cursor()
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
19 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
20 self.database_schema = self.load_dbschema()
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
21 except sqlite.DatabaseError, error:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
22 if str(error) != 'no such table: schema':
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
23 raise
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
24 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
25 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
26 self.cursor.execute('create table ids (name varchar, num integer)')
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
27
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
28 def __repr__(self):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
29 return '<roundlite 0x%x>'%id(self)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
30
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 def sql_fetchone(self):
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
32 ''' Fetch a single row. If there's nothing to fetch, return None.
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
33 '''
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
34 return self.cursor.fetchone()
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
35
1195
e0032f4ab334 added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents: 1183
diff changeset
36 def sql_fetchall(self):
e0032f4ab334 added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents: 1183
diff changeset
37 ''' Fetch a single row. If there's nothing to fetch, return [].
e0032f4ab334 added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents: 1183
diff changeset
38 '''
e0032f4ab334 added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents: 1183
diff changeset
39 return self.cursor.fetchall()
e0032f4ab334 added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents: 1183
diff changeset
40
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
41 def sql_commit(self):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
42 ''' Actually commit to the database.
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
43
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
44 Ignore errors if there's nothing to commit.
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
45 '''
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
46 try:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
47 self.conn.commit()
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
48 except sqlite.DatabaseError, error:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
49 if str(error) != 'cannot commit - no transaction is active':
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
50 raise
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
51
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
52 def save_dbschema(self, schema):
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
53 ''' Save the schema definition that the database currently implements
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
54 '''
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
55 s = repr(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
56 self.sql('insert into schema values (%s)', (s,))
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
57
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
58 def load_dbschema(self):
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
59 ''' Load the schema definition that the database currently implements
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
60 '''
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
61 self.cursor.execute('select schema from schema')
08a13a84ed43 Some speedups - both of the SQL backends can handle using only one cursor.
Richard Jones <richard@users.sourceforge.net>
parents: 1170
diff changeset
62 return eval(self.cursor.fetchone()[0])
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
63
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
64 def save_journal(self, classname, cols, nodeid, journaldate,
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
65 journaltag, action, params):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
66 ''' Save the journal entry 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 # make the params db-friendly
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
69 params = repr(params)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
70 entry = (nodeid, journaldate, journaltag, action, params)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
71
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
72 # do the insert
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
73 a = self.arg
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
74 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(classname,
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
75 cols, a, a, a, a, a)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
76 if __debug__:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
77 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
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
78 self.cursor.execute(sql, entry)
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
79
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
80 def load_journal(self, classname, cols, nodeid):
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
81 ''' Load the journal from the database
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
82 '''
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
83 # now get the journal entries
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
84 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
85 self.arg)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
86 if __debug__:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
87 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
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
88 self.cursor.execute(sql, (nodeid,))
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
89 res = []
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
90 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
91 params = eval(params)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
92 res.append((nodeid, date.Date(date_stamp), user, action, params))
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
93 return res
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
94
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
95 def unserialise(self, classname, node):
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
96 ''' Decode the marshalled node data
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
97
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
98 SQLite stringifies _everything_... so we need to re-numberificate
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
99 Booleans and Numbers.
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
100 '''
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
101 if __debug__:
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
102 print >>hyperdb.DEBUG, 'unserialise', classname, node
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
103 properties = self.getclass(classname).getprops()
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
104 d = {}
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
105 for k, v in node.items():
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
106 # if the property doesn't exist, or is the "retired" flag then
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
107 # it won't be in the properties dict
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
108 if not properties.has_key(k):
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
109 d[k] = v
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
110 continue
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
111
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
112 # get the property spec
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
113 prop = properties[k]
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
114
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
115 if isinstance(prop, Date) and v is not None:
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
116 d[k] = date.Date(v)
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
117 elif isinstance(prop, Interval) and v is not None:
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
118 d[k] = date.Interval(v)
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
119 elif isinstance(prop, Password):
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
120 p = password.Password()
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
121 p.unpack(v)
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
122 d[k] = p
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
123 elif isinstance(prop, Boolean) and v is not None:
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
124 d[k] = int(v)
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
125 elif isinstance(prop, Number) and v is not None:
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
126 # try int first, then assume it's a float
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
127 try:
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
128 d[k] = int(v)
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
129 except ValueError:
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
130 d[k] = float(v)
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
131 else:
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
132 d[k] = v
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
133 return d
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
134
Roundup Issue Tracker: http://roundup-tracker.org/