Mercurial > p > roundup > code
annotate roundup/backends/back_sqlite.py @ 5543:bc3e00a3d24b
MySQL backend fixes for Python 3.
With Python 2, text sent to and from MySQL is treated as bytes in
Python. The database may be recorded by MySQL as having some other
encoding (latin1 being the default in some MySQL versions - Roundup
does not set an encoding explicitly, unlike in back_postgresql), but
as long as MySQL's notion of the connection encoding agrees with its
notion of the database encoding, no conversions actually take place
and the bytes are stored and returned as-is.
With Python 3, text sent to and from MySQL is treated as Python
Unicode strings. When the database and connection encoding is latin1,
that means the bytes stored in the database under Python 2 are
interpreted as latin1 and converted from that to Unicode, producing
incorrect results for any non-ASCII characters; furthermore, if trying
to store new non-ASCII data in the database under Python 3, any
non-latin1 characters produce errors.
This patch arranges for both the connection and database character
sets to be UTF-8 when using Python 3, and documents a need to export
and import the database when moving from Python 2 to Python 3 with
this backend.
| author | Joseph Myers <jsm@polyomino.org.uk> |
|---|---|
| date | Sun, 16 Sep 2018 16:19:20 +0000 |
| parents | 56c9bcdea47f |
| children | ee2e8f8d6648 |
| rev | line source |
|---|---|
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
1 """Implements a backend for SQLite. |
|
2005
fc52d57c6c3e
documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents:
1911
diff
changeset
|
2 |
| 1165 | 3 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
|
4 |
|
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 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
|
7 for the columns, but sqlite IGNORES these specifications. |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
8 """ |
|
2005
fc52d57c6c3e
documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents:
1911
diff
changeset
|
9 __docformat__ = 'restructuredtext' |
|
fc52d57c6c3e
documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents:
1911
diff
changeset
|
10 |
|
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
|
11 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
|
12 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
13 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
|
14 from roundup.backends import rdbms_common |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
15 from roundup.backends.sessions_dbm import Sessions, OneTimeKeys |
|
5416
56c9bcdea47f
Python 3 preparation: unicode.
Joseph Myers <jsm@polyomino.org.uk>
parents:
5319
diff
changeset
|
16 from roundup.anypy.strings import uany2s |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
17 |
|
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
|
18 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
|
19 try: |
|
4231
23ba17d01a24
re-order sqlite imports to handle multiple installed versions (issue 2550570)
Richard Jones <richard@users.sourceforge.net>
parents:
4091
diff
changeset
|
20 import sqlite3 as sqlite |
|
23ba17d01a24
re-order sqlite imports to handle multiple installed versions (issue 2550570)
Richard Jones <richard@users.sourceforge.net>
parents:
4091
diff
changeset
|
21 sqlite_version = 3 |
|
3654
f5201bbbc505
update for latest version of pysqlite ([SF#1487098]; patch 1534227)
Richard Jones <richard@users.sourceforge.net>
parents:
3609
diff
changeset
|
22 except ImportError: |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
23 try: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
24 from pysqlite2 import dbapi2 as sqlite |
|
3794
f5ddd1639a15
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
3752
diff
changeset
|
25 if sqlite.version_info < (2,1,0): |
|
f5ddd1639a15
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
3752
diff
changeset
|
26 raise ValueError('pysqlite2 minimum version is 2.1.0+ ' |
|
f5ddd1639a15
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
3752
diff
changeset
|
27 '- %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
|
28 sqlite_version = 2 |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
29 except ImportError: |
|
4231
23ba17d01a24
re-order sqlite imports to handle multiple installed versions (issue 2550570)
Richard Jones <richard@users.sourceforge.net>
parents:
4091
diff
changeset
|
30 import sqlite |
|
23ba17d01a24
re-order sqlite imports to handle multiple installed versions (issue 2550570)
Richard Jones <richard@users.sourceforge.net>
parents:
4091
diff
changeset
|
31 sqlite_version = 1 |
| 1165 | 32 |
|
2633
a9e1fff1e793
I thought I committed this last night. Ho hum.
Richard Jones <richard@users.sourceforge.net>
parents:
2514
diff
changeset
|
33 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
|
34 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
|
35 |
|
a9e1fff1e793
I thought I committed this last night. Ho hum.
Richard Jones <richard@users.sourceforge.net>
parents:
2514
diff
changeset
|
36 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
|
37 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
|
38 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
39 class Database(rdbms_common.Database): |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
40 """Sqlite DB backend implementation |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
41 |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
42 attributes: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
43 dbtype: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
44 holds the value for the type of db. It is used by indexer to |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
45 identify the database type so it can import the correct indexer |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
46 module when using native text search mode. |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
47 """ |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
48 |
| 1165 | 49 # 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
|
50 if sqlite_version in (2,3): |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
51 arg = '?' |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
52 else: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
53 arg = '%s' |
|
3048
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3008
diff
changeset
|
54 |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
55 dbtype = "sqlite" |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
56 |
|
3048
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3008
diff
changeset
|
57 # 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
|
58 implements_intersect = 1 |
|
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3008
diff
changeset
|
59 |
|
5175
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
60 # used in generic backend to determine if db supports |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
61 # 'DOUBLE PRECISION' for floating point numbers. Note that sqlite |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
62 # already has double precision as its standard 'REAL' type. So this |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
63 # is set to False here. |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
64 |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
65 implements_double_precision = False |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
66 |
|
2168
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
67 hyperdb_to_sql_datatypes = { |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
68 hyperdb.String : 'VARCHAR(255)', |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
69 hyperdb.Date : 'VARCHAR(30)', |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
70 hyperdb.Link : 'INTEGER', |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
71 hyperdb.Interval : 'VARCHAR(255)', |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
72 hyperdb.Password : 'VARCHAR(255)', |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
73 hyperdb.Boolean : 'BOOLEAN', |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
74 hyperdb.Number : 'REAL', |
|
5067
e424987d294a
Add support for an integer type to join the existing number type.
John Rouillard <rouilj@ieee.org>
parents:
4774
diff
changeset
|
75 hyperdb.Integer : 'INTEGER', |
|
2168
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
76 } |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
77 hyperdb_to_sql_value = { |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
78 hyperdb.String : str, |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
79 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
|
80 hyperdb.Link : int, |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
81 hyperdb.Interval : str, |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
82 hyperdb.Password : str, |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
83 hyperdb.Boolean : int, |
|
5067
e424987d294a
Add support for an integer type to join the existing number type.
John Rouillard <rouilj@ieee.org>
parents:
4774
diff
changeset
|
84 hyperdb.Integer : int, |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
85 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
|
86 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
|
87 } |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
88 sql_to_hyperdb_value = { |
|
5416
56c9bcdea47f
Python 3 preparation: unicode.
Joseph Myers <jsm@polyomino.org.uk>
parents:
5319
diff
changeset
|
89 hyperdb.String : uany2s, |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
90 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
|
91 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
|
92 hyperdb.Interval : date.Interval, |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
93 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
|
94 hyperdb.Boolean : int, |
|
5067
e424987d294a
Add support for an integer type to join the existing number type.
John Rouillard <rouilj@ieee.org>
parents:
4774
diff
changeset
|
95 hyperdb.Integer : int, |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
96 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
|
97 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
|
98 } |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
99 |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
100 # We're using DBM for managing session info and one-time keys: |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
101 # For SQL database storage of this info we would need two concurrent |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
102 # connections to the same database which SQLite doesn't support |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
103 def getSessionManager(self): |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
104 if not self.Session: |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
105 self.Session = Sessions(self) |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
106 return self.Session |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
107 |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
108 def getOTKManager(self): |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
109 if not self.Otk: |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
110 self.Otk = OneTimeKeys(self) |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
111 return self.Otk |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
112 |
|
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
|
113 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
|
114 """invoked whenever SQLite tries to access a database that is locked""" |
|
4415
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
115 now = time.time() |
|
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
|
116 if count == 1: |
|
4415
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
117 # Timeout for handling locked database (default 30s) |
|
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
118 self._busy_handler_endtime = now + self.config.RDBMS_SQLITE_TIMEOUT |
|
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
119 elif now > self._busy_handler_endtime: |
|
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
|
120 # 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
|
121 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
|
122 # 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
|
123 # 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
|
124 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
|
125 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
|
126 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
|
127 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
128 def sql_open_connection(self): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
129 """Open a standard, non-autocommitting connection. |
|
2374
31cb1014300c
Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents:
2273
diff
changeset
|
130 |
|
31cb1014300c
Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents:
2273
diff
changeset
|
131 pysqlite will automatically BEGIN TRANSACTION for us. |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
132 """ |
|
2764
d3b3f1b3d59e
sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2736
diff
changeset
|
133 # make sure the database directory exists |
|
d3b3f1b3d59e
sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2736
diff
changeset
|
134 # 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
|
135 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
|
136 os.makedirs(self.config.DATABASE) |
|
d3b3f1b3d59e
sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2736
diff
changeset
|
137 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
138 db = os.path.join(self.config.DATABASE, 'db') |
|
4420
9655a1b65974
- more logger fixes -- use correct hierarchical logger names...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4415
diff
changeset
|
139 logging.getLogger('roundup.hyperdb').info('open database %r'%db) |
|
4415
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
140 # set timeout (30 second default is extraordinarily generous) |
|
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
141 # for handling 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
|
142 if sqlite_version == 1: |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
143 conn = sqlite.connect(db=db) |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
144 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
|
145 else: |
|
4415
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
146 conn = sqlite.connect(db, timeout=self.config.RDBMS_SQLITE_TIMEOUT) |
|
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
|
147 conn.row_factory = sqlite.Row |
|
4091
09e79cbeb827
force sqlite3 in py2.6+ to treat our stored text as UTF-8
Richard Jones <richard@users.sourceforge.net>
parents:
4076
diff
changeset
|
148 |
|
4240
289f249ba192
compatibilty fix for pysqlite2 / unicode
Richard Jones <richard@users.sourceforge.net>
parents:
4231
diff
changeset
|
149 # pysqlite2 / sqlite3 want us to store Unicode in the db but |
|
289f249ba192
compatibilty fix for pysqlite2 / unicode
Richard Jones <richard@users.sourceforge.net>
parents:
4231
diff
changeset
|
150 # that's not what's been done historically and it's definitely |
|
289f249ba192
compatibilty fix for pysqlite2 / unicode
Richard Jones <richard@users.sourceforge.net>
parents:
4231
diff
changeset
|
151 # not what the other backends do, so we'll stick with UTF-8 |
|
289f249ba192
compatibilty fix for pysqlite2 / unicode
Richard Jones <richard@users.sourceforge.net>
parents:
4231
diff
changeset
|
152 if sqlite_version in (2, 3): |
|
4091
09e79cbeb827
force sqlite3 in py2.6+ to treat our stored text as UTF-8
Richard Jones <richard@users.sourceforge.net>
parents:
4076
diff
changeset
|
153 conn.text_factory = str |
|
09e79cbeb827
force sqlite3 in py2.6+ to treat our stored text as UTF-8
Richard Jones <richard@users.sourceforge.net>
parents:
4076
diff
changeset
|
154 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
155 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
|
156 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
|
157 |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
158 def open_connection(self): |
| 1165 | 159 # 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
|
160 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
|
161 |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
162 (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
|
163 |
| 1165 | 164 try: |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
165 self.load_dbschema() |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5175
diff
changeset
|
166 except sqlite.DatabaseError as error: |
| 1165 | 167 if str(error) != 'no such table: schema': |
| 168 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
|
169 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
|
170 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
|
171 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
|
172 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
|
173 self.create_version_2_tables() |
|
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
174 |
|
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
175 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
|
176 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
|
177 '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
|
178 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
|
179 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
|
180 '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
|
181 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
|
182 'sessions(session_key)') |
| 1165 | 183 |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2082
diff
changeset
|
184 # 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
|
185 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
|
186 '_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
|
187 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
|
188 '_textid integer)') |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2374
diff
changeset
|
189 self.sql('CREATE INDEX words_word_ids ON __words(_word)') |
|
3858
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3794
diff
changeset
|
190 self.sql('CREATE INDEX words_by_id ON __words (_textid)') |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3794
diff
changeset
|
191 self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3794
diff
changeset
|
192 '__textids (_class, _itemid, _prop)') |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2082
diff
changeset
|
193 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
|
194 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
|
195 |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
196 def add_new_columns_v2(self): |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
197 # update existing tables to have the new actor column |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
198 tables = self.database_schema['tables'] |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
199 for classname, spec in self.classes.items(): |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4240
diff
changeset
|
200 if classname in tables: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
201 dbspec = tables[classname] |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
202 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
|
203 # 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
|
204 tables[classname] = spec.schema() |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
205 |
|
2721
1cd01cf106e1
extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents:
2645
diff
changeset
|
206 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
|
207 # 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
|
208 pass |
|
1cd01cf106e1
extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents:
2645
diff
changeset
|
209 |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
210 def update_class(self, spec, old_spec, force=0, adding_v2=0): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
211 """ Determine the differences between the current spec and the |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
212 database version of the spec, and update where necessary. |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
213 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
214 If 'force' is true, update the database anyway. |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
215 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
216 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
|
217 regenerate the tables with the new schema. |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
218 """ |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
219 new_spec = spec.schema() |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
220 new_spec[1].sort() |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
221 old_spec[1].sort() |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
222 if not force and new_spec == old_spec: |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
223 # no changes |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
224 return 0 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
225 |
|
4421
67bef70ab9b9
- more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4420
diff
changeset
|
226 logging.getLogger('roundup.hyperdb').info( |
|
67bef70ab9b9
- more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4420
diff
changeset
|
227 'update_class %s'%spec.classname) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
228 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
229 # 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
|
230 old_has = {} |
|
2137
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
231 for name, prop in old_spec[1]: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
232 old_has[name] = 1 |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4240
diff
changeset
|
233 if name in spec.properties or not isinstance(prop, hyperdb.Multilink): |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
234 continue |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
235 # 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
|
236 # table. First drop indexes. |
|
3347
9020fe58c51c
more errors. backport candidate
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents:
3310
diff
changeset
|
237 self.drop_multilink_table_indexes(spec.classname, name) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
238 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
|
239 self.sql(sql) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
240 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
241 # 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
|
242 if adding_v2: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
243 fetch = ['_activity', '_creation', '_creator'] |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
244 else: |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
245 fetch = ['_actor', '_activity', '_creation', '_creator'] |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
246 properties = spec.getprops() |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
247 for propname,x in new_spec[1]: |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
248 prop = properties[propname] |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
249 if isinstance(prop, hyperdb.Multilink): |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4240
diff
changeset
|
250 if propname not in old_has: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
251 # we need to create the new table |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
252 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
|
253 elif force: |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
254 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
|
255 # grabe the current values |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
256 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
|
257 self.sql(sql) |
|
2137
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
258 rows = self.cursor.fetchall() |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
259 |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
260 # drop the old table |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
261 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
|
262 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
|
263 self.sql(sql) |
|
2137
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
264 |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
265 # 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
|
266 self.create_multilink_table(spec, propname) |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
267 sql = """insert into %s (linkid, nodeid) values |
|
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
268 (%s, %s)"""%(tn, self.arg, self.arg) |
|
2137
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
269 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
|
270 self.sql(sql, (int(linkid), int(nodeid))) |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4240
diff
changeset
|
271 elif propname in old_has: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
272 # we copy this col over from the old table |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
273 fetch.append('_'+propname) |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
274 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
275 # select the data out of the old table |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
276 fetch.append('id') |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
277 fetch.append('__retired__') |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
278 fetchcols = ','.join(fetch) |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
279 cn = spec.classname |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
280 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
|
281 self.sql(sql) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
282 olddata = self.cursor.fetchall() |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
283 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
284 # TODO: update all the other index dropping code |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
285 self.drop_class_table_indexes(cn, old_spec[0]) |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
286 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
287 # 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
|
288 self.sql('drop table _%s'%cn) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
289 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
290 # create the new table |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
291 self.create_class_table(spec) |
|
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 if olddata: |
|
4774
3adff0fb0207
Fixed issue2550595: Allow migrating from roundup 0.x to 1.4
Thomas Arendsen Hein <thomas@intevation.de>
parents:
4421
diff
changeset
|
294 inscols = ['id', '_actor', '_activity', '_creation', '_creator', '__retired__'] |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
295 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
|
296 prop = properties[propname] |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
297 if isinstance(prop, hyperdb.Multilink): |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
298 continue |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
299 elif isinstance(prop, hyperdb.Interval): |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
300 inscols.append('_'+propname) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
301 inscols.append('__'+propname+'_int__') |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4240
diff
changeset
|
302 elif propname in old_has: |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
303 # 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
|
304 inscols.append('_'+propname) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
305 |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
306 # 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
|
307 # NULL values |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
308 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
|
309 cols = ','.join(inscols) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
310 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
|
311 for entry in olddata: |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
312 d = [] |
|
4774
3adff0fb0207
Fixed issue2550595: Allow migrating from roundup 0.x to 1.4
Thomas Arendsen Hein <thomas@intevation.de>
parents:
4421
diff
changeset
|
313 retired_id = None |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
314 for name in inscols: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
315 # 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
|
316 if name.endswith('_int__'): |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
317 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
|
318 if sqlite_version in (2,3): |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
319 try: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
320 v = hyperdb.Interval(entry[name]).as_seconds() |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
321 except IndexError: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
322 v = None |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4240
diff
changeset
|
323 elif name in entry: |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
324 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
|
325 else: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
326 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
|
327 elif sqlite_version in (2,3): |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
328 try: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
329 v = entry[name] |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
330 except IndexError: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
331 v = None |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4240
diff
changeset
|
332 elif (sqlite_version == 1 and name in entry): |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
333 v = entry[name] |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
334 else: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
335 v = None |
|
4774
3adff0fb0207
Fixed issue2550595: Allow migrating from roundup 0.x to 1.4
Thomas Arendsen Hein <thomas@intevation.de>
parents:
4421
diff
changeset
|
336 if name == 'id': |
|
3adff0fb0207
Fixed issue2550595: Allow migrating from roundup 0.x to 1.4
Thomas Arendsen Hein <thomas@intevation.de>
parents:
4421
diff
changeset
|
337 retired_id = v |
|
3adff0fb0207
Fixed issue2550595: Allow migrating from roundup 0.x to 1.4
Thomas Arendsen Hein <thomas@intevation.de>
parents:
4421
diff
changeset
|
338 elif name == '__retired__' and retired_id and v not in ['0', 0]: |
|
3adff0fb0207
Fixed issue2550595: Allow migrating from roundup 0.x to 1.4
Thomas Arendsen Hein <thomas@intevation.de>
parents:
4421
diff
changeset
|
339 v = retired_id |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
340 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
|
341 self.sql(sql, tuple(d)) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
342 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
343 return 1 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
344 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
345 def sql_close(self): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
346 """ Squash any error caused by us already having closed the |
| 1236 | 347 connection. |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
348 """ |
| 1236 | 349 try: |
|
2374
31cb1014300c
Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents:
2273
diff
changeset
|
350 self.conn.close() |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5175
diff
changeset
|
351 except sqlite.ProgrammingError as value: |
|
2374
31cb1014300c
Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents:
2273
diff
changeset
|
352 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
|
353 raise |
| 1236 | 354 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
355 def sql_rollback(self): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
356 """ Squash any error caused by us having closed the connection (and |
| 1236 | 357 therefore not having anything to roll back) |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
358 """ |
| 1236 | 359 try: |
| 360 self.conn.rollback() | |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5175
diff
changeset
|
361 except sqlite.ProgrammingError as value: |
| 1236 | 362 if str(value) != 'rollback failed - Connection is closed.': |
| 363 raise | |
| 364 | |
| 1165 | 365 def __repr__(self): |
| 366 return '<roundlite 0x%x>'%id(self) | |
| 367 | |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
368 def sql_commit(self): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
369 """ Actually commit to the database. |
| 1165 | 370 |
| 371 Ignore errors if there's nothing to commit. | |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
372 """ |
| 1165 | 373 try: |
| 374 self.conn.commit() | |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5175
diff
changeset
|
375 except sqlite.DatabaseError as error: |
| 1165 | 376 if str(error) != 'cannot commit - no transaction is active': |
| 377 raise | |
|
2374
31cb1014300c
Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents:
2273
diff
changeset
|
378 # 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
|
379 self.cursor = self.conn.cursor() |
| 1165 | 380 |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
381 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
|
382 self.sql('pragma index_list(%s)'%table_name) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
383 for entry in self.cursor.fetchall(): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
384 if entry[1] == index_name: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
385 return 1 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
386 return 0 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
387 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
388 # old-skool id generation |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
389 def newid(self, classname): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
390 """ Generate a new id for the given class |
|
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
391 """ |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
392 |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
393 # Prevent other processes from reading while we increment. |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
394 # Otherwise multiple processes can end up with the same |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
395 # new id and hilarity results. |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
396 # |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
397 # Defeat pysqlite's attempts to do locking by setting |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
398 # isolation_level to None. Pysqlite can commit |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
399 # on it's own even if we don't want it to end the transaction. |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
400 # If we rewrite to use another sqlite library like apsw we |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
401 # don't have to deal with this autocommit/autotransact foolishness. |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
402 self.conn.isolation_level = None; |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
403 # Manage the transaction locks manually. |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
404 self.sql("BEGIN IMMEDIATE"); |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
405 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
406 # get the next ID |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
407 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
|
408 self.sql(sql, (classname, )) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
409 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
|
410 |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
411 # leave the next larger number as the next newid |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
412 sql = 'update ids set num=num+1 where name=%s'%self.arg |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
413 vals = (classname,) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2374
diff
changeset
|
414 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
415 |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
416 # reset pysqlite's auto transact stuff to default since the |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
417 # rest of the code expects it. |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
418 self.conn.isolation_level = ''; |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
419 # commit writing the data, clearing locks for other processes |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
420 # and create a new cursor to the database. |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
421 self.sql_commit(); |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
422 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
423 # return as string |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
424 return str(newid) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
425 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
426 def setid(self, classname, setid): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
427 """ Set the id counter: used during import of database |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
428 |
|
3306
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
429 We add one to make it behave like the sequences in postgres. |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
430 """ |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
431 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
|
432 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
|
433 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
434 |
|
3306
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
435 def clear(self): |
|
3310
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3306
diff
changeset
|
436 rdbms_common.Database.clear(self) |
|
3306
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
437 # 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
|
438 for cn in self.classes.keys(): |
|
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
439 self.setid(cn, 0) |
|
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
440 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
441 def create_class(self, spec): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
442 rdbms_common.Database.create_class(self, spec) |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
443 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
|
444 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
|
445 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
446 |
|
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
|
447 if sqlite_version in (2,3): |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
448 def load_journal(self, classname, cols, nodeid): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
449 """We need to turn the sqlite3.Row into a tuple so it can be |
|
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
450 unpacked""" |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
451 l = rdbms_common.Database.load_journal(self, |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
452 classname, cols, nodeid) |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
453 cols = range(5) |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
454 return [[row[col] for col in cols] for row in l] |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
455 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
456 class sqliteClass: |
|
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
457 def filter(self, search_matches, filterspec, sort=(None,None), |
|
5318
506c7ee9a385
Add a 'retired' parameter to Class.filter
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5248
diff
changeset
|
458 group=(None,None), retired=False): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
459 """ If there's NO matches to a fetch, sqlite returns NULL |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
460 instead of nothing |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
461 """ |
|
4357
13b3155869e0
Beginnings of a big code cleanup / modernisation to make 2to3 happy
Richard Jones <richard@users.sourceforge.net>
parents:
4240
diff
changeset
|
462 return [f for f in rdbms_common.Class.filter(self, search_matches, |
|
5318
506c7ee9a385
Add a 'retired' parameter to Class.filter
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5248
diff
changeset
|
463 filterspec, sort=sort, group=group, retired=retired) if f] |
| 1165 | 464 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
465 class Class(sqliteClass, rdbms_common.Class): |
|
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
466 pass |
|
1168
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
467 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
468 class IssueClass(sqliteClass, rdbms_common.IssueClass): |
|
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
469 pass |
|
1168
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
470 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
471 class FileClass(sqliteClass, rdbms_common.FileClass): |
|
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
472 pass |
|
1168
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
473 |
|
2645
8250c63c3963
remove space at EOL, add vim modeline
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2633
diff
changeset
|
474 # vim: set et sts=4 sw=4 : |
