Mercurial > p > roundup > code
annotate roundup/backends/back_sqlite.py @ 1836:94e430ad4fdb
make the RDBMS common backend and the SQLite and MYsql backend create...
...and drop indexes for the basic columns - index multilinks, index id and
retired columns of all class tables.
| author | Anthony Baxter <anthonybaxter@users.sourceforge.net> |
|---|---|
| date | Tue, 07 Oct 2003 07:17:54 +0000 |
| parents | 2fc7d4a8c9e7 |
| children | f255363e6d97 |
| rev | line source |
|---|---|
|
1836
94e430ad4fdb
make the RDBMS common backend and the SQLite and MYsql backend create...
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents:
1492
diff
changeset
|
1 # $Id: back_sqlite.py,v 1.10 2003-10-07 07:17:54 anthonybaxter Exp $ |
| 1165 | 2 __doc__ = ''' |
| 3 See https://pysqlite.sourceforge.net/ for pysqlite info | |
| 4 ''' | |
| 5 import base64, marshal | |
| 6 from roundup.backends.rdbms_common import * | |
|
1333
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
7 from roundup.backends import locking |
| 1165 | 8 import sqlite |
| 9 | |
| 10 class Database(Database): | |
| 11 # char to use for positional arguments | |
| 12 arg = '%s' | |
| 13 | |
| 14 def open_connection(self): | |
| 15 # ensure files are group readable and writable | |
| 16 os.umask(0002) | |
| 17 db = os.path.join(self.config.DATABASE, 'db') | |
|
1333
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
18 |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
19 # lock it |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
20 lockfilenm = db[:-3] + 'lck' |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
21 self.lockfile = locking.acquire_lock(lockfilenm) |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
22 self.lockfile.write(str(os.getpid())) |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
23 self.lockfile.flush() |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
24 |
| 1165 | 25 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
|
26 self.cursor = self.conn.cursor() |
| 1165 | 27 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
|
28 self.database_schema = self.load_dbschema() |
| 1165 | 29 except sqlite.DatabaseError, error: |
| 30 if str(error) != 'no such table: schema': | |
| 31 raise | |
| 32 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
|
33 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
|
34 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
|
35 self.cursor.execute('create index ids_name_idx on ids(name)') |
| 1165 | 36 |
| 1236 | 37 def close(self): |
| 38 ''' Close off the connection. | |
| 39 | |
| 40 Squash any error caused by us already having closed the | |
| 41 connection. | |
| 42 ''' | |
| 43 try: | |
| 44 self.conn.close() | |
| 45 except sqlite.ProgrammingError, value: | |
| 46 if str(value) != 'close failed - Connection is closed.': | |
| 47 raise | |
| 48 | |
|
1333
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
49 # release the lock too |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
50 if self.lockfile is not None: |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
51 locking.release_lock(self.lockfile) |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
52 if self.lockfile is not None: |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
53 self.lockfile.close() |
|
80d27b7d6db5
implemented whole-database locking
Richard Jones <richard@users.sourceforge.net>
parents:
1252
diff
changeset
|
54 self.lockfile = None |
| 1236 | 55 |
| 56 def rollback(self): | |
| 57 ''' Reverse all actions from the current transaction. | |
| 58 | |
| 59 Undo all the changes made since the database was opened or the | |
| 60 last commit() or rollback() was performed. | |
| 61 | |
| 62 Squash any error caused by us having closed the connection (and | |
| 63 therefore not having anything to roll back) | |
| 64 ''' | |
| 65 if __debug__: | |
| 66 print >>hyperdb.DEBUG, 'rollback', (self,) | |
| 67 | |
| 68 # roll back | |
| 69 try: | |
| 70 self.conn.rollback() | |
| 71 except sqlite.ProgrammingError, value: | |
| 72 if str(value) != 'rollback failed - Connection is closed.': | |
| 73 raise | |
| 74 | |
| 75 # roll back "other" transaction stuff | |
| 76 for method, args in self.transactions: | |
| 77 # delete temporary files | |
| 78 if method == self.doStoreFile: | |
| 79 self.rollbackStoreFile(*args) | |
| 80 self.transactions = [] | |
| 81 | |
|
1492
2fc7d4a8c9e7
fixed sqlite rollback/caching bug [SF#689383]
Richard Jones <richard@users.sourceforge.net>
parents:
1333
diff
changeset
|
82 # clear the cache |
|
2fc7d4a8c9e7
fixed sqlite rollback/caching bug [SF#689383]
Richard Jones <richard@users.sourceforge.net>
parents:
1333
diff
changeset
|
83 self.clearCache() |
|
2fc7d4a8c9e7
fixed sqlite rollback/caching bug [SF#689383]
Richard Jones <richard@users.sourceforge.net>
parents:
1333
diff
changeset
|
84 |
| 1165 | 85 def __repr__(self): |
| 86 return '<roundlite 0x%x>'%id(self) | |
| 87 | |
|
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 def sql_fetchone(self): |
| 1165 | 89 ''' Fetch a single row. If there's nothing to fetch, return None. |
| 90 ''' | |
|
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
|
91 return self.cursor.fetchone() |
| 1165 | 92 |
|
1195
e0032f4ab334
added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents:
1183
diff
changeset
|
93 def sql_fetchall(self): |
|
e0032f4ab334
added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents:
1183
diff
changeset
|
94 ''' 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
|
95 ''' |
|
e0032f4ab334
added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents:
1183
diff
changeset
|
96 return self.cursor.fetchall() |
|
e0032f4ab334
added missing stringFind to sql backends
Richard Jones <richard@users.sourceforge.net>
parents:
1183
diff
changeset
|
97 |
| 1165 | 98 def sql_commit(self): |
| 99 ''' Actually commit to the database. | |
| 100 | |
| 101 Ignore errors if there's nothing to commit. | |
| 102 ''' | |
| 103 try: | |
| 104 self.conn.commit() | |
| 105 except sqlite.DatabaseError, error: | |
| 106 if str(error) != 'cannot commit - no transaction is active': | |
| 107 raise | |
| 108 | |
|
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
|
109 def save_dbschema(self, schema): |
| 1165 | 110 ''' Save the schema definition that the database currently implements |
| 111 ''' | |
| 112 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
|
113 self.sql('insert into schema values (%s)', (s,)) |
| 1165 | 114 |
|
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
|
115 def load_dbschema(self): |
| 1165 | 116 ''' Load the schema definition that the database currently implements |
| 117 ''' | |
|
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
|
118 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
|
119 return eval(self.cursor.fetchone()[0]) |
| 1165 | 120 |
|
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
|
121 def save_journal(self, classname, cols, nodeid, journaldate, |
| 1165 | 122 journaltag, action, params): |
| 123 ''' Save the journal entry to the database | |
| 124 ''' | |
| 125 # make the params db-friendly | |
| 126 params = repr(params) | |
| 127 entry = (nodeid, journaldate, journaltag, action, params) | |
| 128 | |
| 129 # do the insert | |
| 130 a = self.arg | |
| 131 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(classname, | |
| 132 cols, a, a, a, a, a) | |
| 133 if __debug__: | |
| 134 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
|
135 self.cursor.execute(sql, entry) |
| 1165 | 136 |
|
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
|
137 def load_journal(self, classname, cols, nodeid): |
| 1165 | 138 ''' Load the journal from the database |
| 139 ''' | |
| 140 # now get the journal entries | |
| 141 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname, | |
| 142 self.arg) | |
| 143 if __debug__: | |
| 144 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
|
145 self.cursor.execute(sql, (nodeid,)) |
| 1165 | 146 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
|
147 for nodeid, date_stamp, user, action, params in self.cursor.fetchall(): |
| 1165 | 148 params = eval(params) |
| 149 res.append((nodeid, date.Date(date_stamp), user, action, params)) | |
| 150 return res | |
| 151 | |
|
1168
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
152 def unserialise(self, classname, node): |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
153 ''' Decode the marshalled node data |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
154 |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
155 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
|
156 Booleans and Numbers. |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
157 ''' |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
158 if __debug__: |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
159 print >>hyperdb.DEBUG, 'unserialise', classname, node |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
160 properties = self.getclass(classname).getprops() |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
161 d = {} |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
162 for k, v in node.items(): |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
163 # 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
|
164 # it won't be in the properties dict |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
165 if not properties.has_key(k): |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
166 d[k] = v |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
167 continue |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
168 |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
169 # get the property spec |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
170 prop = properties[k] |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
171 |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
172 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
|
173 d[k] = date.Date(v) |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
174 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
|
175 d[k] = date.Interval(v) |
|
1252
209a47ede743
allow blank passwords again [SF#619714]
Richard Jones <richard@users.sourceforge.net>
parents:
1236
diff
changeset
|
176 elif isinstance(prop, Password) and v is not None: |
|
1168
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
177 p = password.Password() |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
178 p.unpack(v) |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
179 d[k] = p |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
180 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
|
181 d[k] = int(v) |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
182 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
|
183 # 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
|
184 try: |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
185 d[k] = int(v) |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
186 except ValueError: |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
187 d[k] = float(v) |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
188 else: |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
189 d[k] = v |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
190 return d |
|
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
191 |