Mercurial > p > roundup > code

annotate roundup/backends/back_sqlite.py @ 3854:f4e8dc583256

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Restored subject parser regexp to the string it was before the... ...implementation of customization of it, i.e., the version from CVS revision 1.184 of mailgw.py. This makes 'testFollowupTitleMatchMultiRe' work again.
author Erik Forsberg <forsberg@users.sourceforge.net>
date Sat, 12 May 2007 16:14:54 +0000
parents f5ddd1639a15
children bb30bbfc7cdd
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
3794
f5ddd1639a15 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 3752
diff changeset
1 # $Id: back_sqlite.py,v 1.50 2006-12-19 03:01:37 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
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
5
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
6
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
7 NOTE: we use the rdbms_common table creation methods which define datatypes
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
8 for the columns, but sqlite IGNORES these specifications.
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
9 '''
2005
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1911
diff changeset
10 __docformat__ = 'restructuredtext'
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1911
diff changeset
11
3155
57b60bda9473 Python 2.3 minimum version - bye bye roundup.rlog, you had a short life.
Richard Jones <richard@users.sourceforge.net>
parents: 3048
diff changeset
12 import os, base64, marshal, shutil, time, logging
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
13
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
14 from roundup import hyperdb, date, password
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
15 from roundup.backends import rdbms_common
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
16 sqlite_version = None
3654
f5201bbbc505 update for latest version of pysqlite ([SF#1487098]; patch 1534227)
Richard Jones <richard@users.sourceforge.net>
parents: 3609
diff changeset
17 try:
f5201bbbc505 update for latest version of pysqlite ([SF#1487098]; patch 1534227)
Richard Jones <richard@users.sourceforge.net>
parents: 3609
diff changeset
18 import sqlite
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
19 sqlite_version = 1
3654
f5201bbbc505 update for latest version of pysqlite ([SF#1487098]; patch 1534227)
Richard Jones <richard@users.sourceforge.net>
parents: 3609
diff changeset
20 except ImportError:
3718
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
21 try:
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
22 from pysqlite2 import dbapi2 as sqlite
3794
f5ddd1639a15 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 3752
diff changeset
23 if sqlite.version_info < (2,1,0):
f5ddd1639a15 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 3752
diff changeset
24 raise ValueError('pysqlite2 minimum version is 2.1.0+ '
f5ddd1639a15 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 3752
diff changeset
25 '- %s found'%sqlite.version)
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
26 sqlite_version = 2
3718
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
27 except ImportError:
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
28 import sqlite3 as sqlite
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
29 sqlite_version = 3
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
30
2633
a9e1fff1e793 I thought I committed this last night. Ho hum.
Richard Jones <richard@users.sourceforge.net>
parents: 2514
diff changeset
31 def db_exists(config):
2736
402d6d556558 postgres backend open doesn't hide corruption in schema [SF#956375]
Richard Jones <richard@users.sourceforge.net>
parents: 2721
diff changeset
32 return os.path.exists(os.path.join(config.DATABASE, 'db'))
2633
a9e1fff1e793 I thought I committed this last night. Ho hum.
Richard Jones <richard@users.sourceforge.net>
parents: 2514
diff changeset
33
a9e1fff1e793 I thought I committed this last night. Ho hum.
Richard Jones <richard@users.sourceforge.net>
parents: 2514
diff changeset
34 def db_nuke(config):
2736
402d6d556558 postgres backend open doesn't hide corruption in schema [SF#956375]
Richard Jones <richard@users.sourceforge.net>
parents: 2721
diff changeset
35 shutil.rmtree(config.DATABASE)
2633
a9e1fff1e793 I thought I committed this last night. Ho hum.
Richard Jones <richard@users.sourceforge.net>
parents: 2514
diff changeset
36
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
37 class Database(rdbms_common.Database):
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
38 # char to use for positional arguments
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
39 if sqlite_version in (2,3):
3718
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
40 arg = '?'
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
41 else:
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
42 arg = '%s'
3048
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 3008
diff changeset
43
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 3008
diff changeset
44 # used by some code to switch styles of query
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 3008
diff changeset
45 implements_intersect = 1
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 3008
diff changeset
46
2168
7e595abb781e better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents: 2144
diff changeset
47 hyperdb_to_sql_datatypes = {
7e595abb781e better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents: 2144
diff changeset
48 hyperdb.String : 'VARCHAR(255)',
7e595abb781e better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents: 2144
diff changeset
49 hyperdb.Date : 'VARCHAR(30)',
7e595abb781e better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents: 2144
diff changeset
50 hyperdb.Link : 'INTEGER',
7e595abb781e better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents: 2144
diff changeset
51 hyperdb.Interval : 'VARCHAR(255)',
7e595abb781e better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents: 2144
diff changeset
52 hyperdb.Password : 'VARCHAR(255)',
7e595abb781e better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents: 2144
diff changeset
53 hyperdb.Boolean : 'BOOLEAN',
7e595abb781e better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents: 2144
diff changeset
54 hyperdb.Number : 'REAL',
7e595abb781e better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents: 2144
diff changeset
55 }
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
56 hyperdb_to_sql_value = {
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
57 hyperdb.String : str,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
58 hyperdb.Date : lambda x: x.serialise(),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
59 hyperdb.Link : int,
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
60 hyperdb.Interval : str,
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
61 hyperdb.Password : str,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
62 hyperdb.Boolean : int,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
63 hyperdb.Number : lambda x: x,
2244
ac4f295499a4 fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents: 2224
diff changeset
64 hyperdb.Multilink : lambda x: x, # used in journal marshalling
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
65 }
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
66 sql_to_hyperdb_value = {
3752
c92687dce135 unicode / sqlite 3 problem [SF#1589292]
Richard Jones <richard@users.sourceforge.net>
parents: 3728
diff changeset
67 hyperdb.String : lambda x: isinstance(x, unicode) and x.encode('utf8') or str(x),
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
68 hyperdb.Date : lambda x: date.Date(str(x)),
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
69 hyperdb.Link : str, # XXX numeric ids
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
70 hyperdb.Interval : date.Interval,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
71 hyperdb.Password : lambda x: password.Password(encrypted=x),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
72 hyperdb.Boolean : int,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
73 hyperdb.Number : rdbms_common._num_cvt,
2244
ac4f295499a4 fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents: 2224
diff changeset
74 hyperdb.Multilink : lambda x: x, # used in journal marshalling
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
75 }
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
76
2847
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
77 def sqlite_busy_handler(self, data, table, count):
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
78 """invoked whenever SQLite tries to access a database that is locked"""
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
79 if count == 1:
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
80 # use a 30 second timeout (extraordinarily generous)
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
81 # for handling locked database
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
82 self._busy_handler_endtime = time.time() + 30
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
83 elif time.time() > self._busy_handler_endtime:
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
84 # timeout expired - no more retries
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
85 return 0
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
86 # sleep adaptively as retry count grows,
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
87 # starting from about half a second
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
88 time_to_sleep = 0.01 * (2 << min(5, count))
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
89 time.sleep(time_to_sleep)
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
90 return 1
fddbbeb581bc sqlite_busy_timeout is not supported in pysqlite 1.1 (SQLite 3.x).
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2764
diff changeset
91
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
92 def sql_open_connection(self):
2374
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
93 '''Open a standard, non-autocommitting connection.
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
94
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
95 pysqlite will automatically BEGIN TRANSACTION for us.
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
96 '''
2764
d3b3f1b3d59e sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2736
diff changeset
97 # make sure the database directory exists
d3b3f1b3d59e sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2736
diff changeset
98 # database itself will be created by sqlite if needed
d3b3f1b3d59e sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2736
diff changeset
99 if not os.path.isdir(self.config.DATABASE):
d3b3f1b3d59e sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2736
diff changeset
100 os.makedirs(self.config.DATABASE)
d3b3f1b3d59e sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2736
diff changeset
101
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
102 db = os.path.join(self.config.DATABASE, 'db')
3155
57b60bda9473 Python 2.3 minimum version - bye bye roundup.rlog, you had a short life.
Richard Jones <richard@users.sourceforge.net>
parents: 3048
diff changeset
103 logging.getLogger('hyperdb').info('open database %r'%db)
2374
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
104 # set a 30 second timeout (extraordinarily generous) for handling
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
105 # locked database
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
106 if sqlite_version == 1:
3718
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
107 conn = sqlite.connect(db=db)
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
108 conn.db.sqlite_busy_handler(self.sqlite_busy_handler)
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
109 else:
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
110 conn = sqlite.connect(db, timeout=30)
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
111 conn.row_factory = sqlite.Row
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
112 cursor = conn.cursor()
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
113 return (conn, cursor)
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
114
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
115 def open_connection(self):
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
116 # ensure files are group readable and writable
3609
f2fda3e6fc8b umask is now configurable (with the same 0002 default)
Richard Jones <richard@users.sourceforge.net>
parents: 3347
diff changeset
117 os.umask(self.config.UMASK)
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
118
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
119 (self.conn, self.cursor) = self.sql_open_connection()
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
120
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
121 try:
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
122 self.load_dbschema()
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
123 except sqlite.DatabaseError, error:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
124 if str(error) != 'no such table: schema':
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
125 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
126 self.init_dbschema()
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
127 self.sql('create table schema (schema varchar)')
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
128 self.sql('create table ids (name varchar, num integer)')
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
129 self.sql('create index ids_name_idx on ids(name)')
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
130 self.create_version_2_tables()
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
131
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
132 def create_version_2_tables(self):
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
133 self.sql('create table otks (otk_key varchar, '
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
134 'otk_value varchar, otk_time integer)')
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
135 self.sql('create index otks_key_idx on otks(otk_key)')
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
136 self.sql('create table sessions (session_key varchar, '
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
137 'session_time integer, session_value varchar)')
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
138 self.sql('create index sessions_key_idx on '
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
139 'sessions(session_key)')
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
140
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2082
diff changeset
141 # full-text indexing store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
142 self.sql('CREATE TABLE __textids (_class varchar, '
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
143 '_itemid varchar, _prop varchar, _textid integer primary key) ')
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
144 self.sql('CREATE TABLE __words (_word varchar, '
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2082
diff changeset
145 '_textid integer)')
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
146 self.sql('CREATE INDEX words_word_ids ON __words(_word)')
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2082
diff changeset
147 sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
148 self.sql(sql, ('__textids', 1))
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2082
diff changeset
149
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
150 def add_new_columns_v2(self):
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
151 # update existing tables to have the new actor column
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
152 tables = self.database_schema['tables']
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
153 for classname, spec in self.classes.items():
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
154 if tables.has_key(classname):
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
155 dbspec = tables[classname]
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
156 self.update_class(spec, dbspec, force=1, adding_v2=1)
2137
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
157 # we've updated - don't try again
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
158 tables[classname] = spec.schema()
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
159
2721
1cd01cf106e1 extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents: 2645
diff changeset
160 def fix_version_3_tables(self):
1cd01cf106e1 extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents: 2645
diff changeset
161 # NOOP - no restriction on column length here
1cd01cf106e1 extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents: 2645
diff changeset
162 pass
1cd01cf106e1 extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents: 2645
diff changeset
163
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
164 def update_class(self, spec, old_spec, force=0, adding_v2=0):
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
165 ''' Determine the differences between the current spec and the
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
166 database version of the spec, and update where necessary.
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
167
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
168 If 'force' is true, update the database anyway.
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
169
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
170 SQLite doesn't have ALTER TABLE, so we have to copy and
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
171 regenerate the tables with the new schema.
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
172 '''
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
173 new_has = spec.properties.has_key
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
174 new_spec = spec.schema()
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
175 new_spec[1].sort()
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
176 old_spec[1].sort()
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
177 if not force and new_spec == old_spec:
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
178 # no changes
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
179 return 0
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
180
3155
57b60bda9473 Python 2.3 minimum version - bye bye roundup.rlog, you had a short life.
Richard Jones <richard@users.sourceforge.net>
parents: 3048
diff changeset
181 logging.getLogger('hyperdb').info('update_class %s'%spec.classname)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
182
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
183 # detect multilinks that have been removed, and drop their table
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
184 old_has = {}
2137
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
185 for name, prop in old_spec[1]:
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
186 old_has[name] = 1
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
187 if new_has(name) or not isinstance(prop, hyperdb.Multilink):
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
188 continue
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
189 # it's a multilink, and it's been removed - drop the old
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
190 # table. First drop indexes.
3347
9020fe58c51c more errors. backport candidate
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents: 3310
diff changeset
191 self.drop_multilink_table_indexes(spec.classname, name)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
192 sql = 'drop table %s_%s'%(spec.classname, prop)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
193 self.sql(sql)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
194 old_has = old_has.has_key
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
195
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
196 # now figure how we populate the new table
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
197 if adding_v2:
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
198 fetch = ['_activity', '_creation', '_creator']
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
199 else:
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
200 fetch = ['_actor', '_activity', '_creation', '_creator']
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
201 properties = spec.getprops()
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
202 for propname,x in new_spec[1]:
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
203 prop = properties[propname]
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
204 if isinstance(prop, hyperdb.Multilink):
2137
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
205 if not old_has(propname):
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
206 # we need to create the new table
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
207 self.create_multilink_table(spec, propname)
2137
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
208 elif force:
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
209 tn = '%s_%s'%(spec.classname, propname)
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
210 # grabe the current values
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
211 sql = 'select linkid, nodeid from %s'%tn
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
212 self.sql(sql)
2137
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
213 rows = self.cursor.fetchall()
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
214
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
215 # drop the old table
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
216 self.drop_multilink_table_indexes(spec.classname, propname)
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
217 sql = 'drop table %s'%tn
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
218 self.sql(sql)
2137
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
219
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
220 # re-create and populate the new table
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
221 self.create_multilink_table(spec, propname)
2645
8250c63c3963 remove space at EOL, add vim modeline
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2633
diff changeset
222 sql = '''insert into %s (linkid, nodeid) values
2137
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
223 (%s, %s)'''%(tn, self.arg, self.arg)
c49495585c44 Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
224 for linkid, nodeid in rows:
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
225 self.sql(sql, (int(linkid), int(nodeid)))
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
226 elif old_has(propname):
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
227 # we copy this col over from the old table
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
228 fetch.append('_'+propname)
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
229
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
230 # select the data out of the old table
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
231 fetch.append('id')
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
232 fetch.append('__retired__')
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
233 fetchcols = ','.join(fetch)
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
234 cn = spec.classname
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
235 sql = 'select %s from _%s'%(fetchcols, cn)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
236 self.sql(sql)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
237 olddata = self.cursor.fetchall()
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
238
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
239 # TODO: update all the other index dropping code
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
240 self.drop_class_table_indexes(cn, old_spec[0])
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
241
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
242 # drop the old table
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
243 self.sql('drop table _%s'%cn)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
244
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
245 # create the new table
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
246 self.create_class_table(spec)
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
247
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
248 if olddata:
2273
c77483d2cda4 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 2244
diff changeset
249 inscols = ['id', '_actor', '_activity', '_creation', '_creator']
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
250 for propname,x in new_spec[1]:
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
251 prop = properties[propname]
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
252 if isinstance(prop, hyperdb.Multilink):
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
253 continue
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
254 elif isinstance(prop, hyperdb.Interval):
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
255 inscols.append('_'+propname)
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
256 inscols.append('__'+propname+'_int__')
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
257 elif old_has(propname):
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
258 # we copy this col over from the old table
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
259 inscols.append('_'+propname)
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
260
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
261 # do the insert of the old data - the new columns will have
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
262 # NULL values
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
263 args = ','.join([self.arg for x in inscols])
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
264 cols = ','.join(inscols)
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
265 sql = 'insert into _%s (%s) values (%s)'%(cn, cols, args)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
266 for entry in olddata:
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
267 d = []
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
268 for name in inscols:
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
269 # generate the new value for the Interval int column
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
270 if name.endswith('_int__'):
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
271 name = name[2:-6]
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
272 if sqlite_version in (2,3):
3718
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
273 try:
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
274 v = hyperdb.Interval(entry[name]).as_seconds()
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
275 except IndexError:
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
276 v = None
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
277 elif entry.has_key(name):
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
278 v = hyperdb.Interval(entry[name]).as_seconds()
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
279 else:
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
280 v = None
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
281 elif sqlite_version in (2,3):
3718
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
282 try:
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
283 v = entry[name]
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
284 except IndexError:
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
285 v = None
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
286 elif (sqlite_version == 1 and entry.has_key(name)):
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
287 v = entry[name]
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
288 else:
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
289 v = None
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2191
diff changeset
290 d.append(v)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
291 self.sql(sql, tuple(d))
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
292
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
293 return 1
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
294
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
295 def sql_close(self):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
296 ''' Squash any error caused by us already having closed the
1236
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
297 connection.
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
298 '''
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
299 try:
2374
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
300 self.conn.close()
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
301 except sqlite.ProgrammingError, value:
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
302 if str(value) != 'close failed - Connection is closed.':
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
303 raise
1236
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
304
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
305 def sql_rollback(self):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
306 ''' 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
307 therefore not having anything to roll back)
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
308 '''
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
309 try:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
310 self.conn.rollback()
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
311 except sqlite.ProgrammingError, value:
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
312 if str(value) != 'rollback failed - Connection is closed.':
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
313 raise
dd52bf10f934 Bug fixes.
Richard Jones <richard@users.sourceforge.net>
parents: 1195
diff changeset
314
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
315 def __repr__(self):
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
316 return '<roundlite 0x%x>'%id(self)
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
317
3693
a775afeeee8b Database interface fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3654
diff changeset
318 def sql_commit(self, fail_ok=False):
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
319 ''' Actually commit to the database.
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
320
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
321 Ignore errors if there's nothing to commit.
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
322 '''
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
323 try:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
324 self.conn.commit()
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
325 except sqlite.DatabaseError, error:
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
326 if str(error) != 'cannot commit - no transaction is active':
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
327 raise
2374
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
328 # open a new cursor for subsequent work
31cb1014300c Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents: 2273
diff changeset
329 self.cursor = self.conn.cursor()
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
330
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
331 def sql_index_exists(self, table_name, index_name):
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
332 self.sql('pragma index_list(%s)'%table_name)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
333 for entry in self.cursor.fetchall():
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
334 if entry[1] == index_name:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
335 return 1
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
336 return 0
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
337
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
338 # old-skool id generation
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
339 def newid(self, classname):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
340 ''' Generate a new id for the given class
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
341 '''
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
342 # get the next ID
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
343 sql = 'select num from ids where name=%s'%self.arg
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
344 self.sql(sql, (classname, ))
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
345 newid = int(self.cursor.fetchone()[0])
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
346
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
347 # update the counter
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
348 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
349 vals = (int(newid)+1, classname)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
350 self.sql(sql, vals)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
351
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
352 # return as string
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
353 return str(newid)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
354
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
355 def setid(self, classname, setid):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
356 ''' Set the id counter: used during import of database
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
357
3306
ae1093684ef5 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3163
diff changeset
358 We add one to make it behave like the sequences in postgres.
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
359 '''
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
360 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
361 vals = (int(setid)+1, classname)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
362 self.sql(sql, vals)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
363
3306
ae1093684ef5 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3163
diff changeset
364 def clear(self):
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3306
diff changeset
365 rdbms_common.Database.clear(self)
3306
ae1093684ef5 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3163
diff changeset
366 # set the id counters to 0 (setid adds one) so we start at 1
ae1093684ef5 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3163
diff changeset
367 for cn in self.classes.keys():
ae1093684ef5 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3163
diff changeset
368 self.setid(cn, 0)
ae1093684ef5 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3163
diff changeset
369
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
370 def create_class(self, spec):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
371 rdbms_common.Database.create_class(self, spec)
3718
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
372 sql = 'insert into ids (name, num) values (%s, %s)'%(self.arg, self.arg)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
373 vals = (spec.classname, 1)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2374
diff changeset
374 self.sql(sql, vals)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2094
diff changeset
375
3728
b476fef16ccc fixed support for pysqlite2 (version 2.1.0 is the minimum version supported)
Richard Jones <richard@users.sourceforge.net>
parents: 3718
diff changeset
376 if sqlite_version in (2,3):
3718
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
377 def load_journal(self, classname, cols, nodeid):
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
378 '''We need to turn the sqlite3.Row into a tuple so it can be
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
379 unpacked'''
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
380 l = rdbms_common.Database.load_journal(self,
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
381 classname, cols, nodeid)
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
382 cols = range(5)
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
383 return [[row[col] for col in cols] for row in l]
0d561b24ceff support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents: 3693
diff changeset
384
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
385 class sqliteClass:
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
386 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
387 group=(None,None)):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
388 ''' 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
389 instead of nothing
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
390 '''
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
391 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
392 filterspec, sort=sort, group=group))
1165
14467c765167 sqlite backend!
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
393
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
394 class Class(sqliteClass, rdbms_common.Class):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
395 pass
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
396
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
397 class IssueClass(sqliteClass, rdbms_common.IssueClass):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
398 pass
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
399
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
400 class FileClass(sqliteClass, rdbms_common.FileClass):
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
401 pass
1168
94620e088e3a fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents: 1165
diff changeset
402
2645
8250c63c3963 remove space at EOL, add vim modeline
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2633
diff changeset
403 # vim: set et sts=4 sw=4 :
Roundup Issue Tracker: http://roundup-tracker.org/