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