Mercurial > p > roundup > code
annotate roundup/backends/back_sqlite.py @ 6588:91ab3e0ffcd0
Summary: Add test cases for sqlite fts
Add support for using the FTS5 full text query engine for sqlite.
Also stubbed out some sections for adding postgresql FTS support as
well.
Added nee indexer type native-fts. It is not selected by default. The
indexer=native is used if no indexer is set. This prevents an upgrade
from seeming to wipe out the native index if upgraded and
indexer=native is not explicitly set.
Docs updated. Also changed section headers to sentence case for the
current release notes.
Indexing backend can control if the full text search phrase is broken
into a list of words or passed intact. For backends with query
languages (sqlite and can be enabled for whoosh and xapian) we do not
want the phrase "tokenized" on whitespace.
This also updates the rdbms database version to version 7 to add FTS
table. I will be using the same version when I add postgresql. If
somebody runs this version on postgresql, they will have to manually
add the fts tables for postgresql if they want to use it.
Added a new renderError method to client. This allows errors to be
reported still using page.html rather than raw html. It also supports
templates for any error code. If no template for the error code
(e.g. 400) is found, the error in raw html with no page frame is
shown.
New IndexerQueryError exception to pass back message about query syntax
errors.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Sun, 23 Jan 2022 18:57:45 -0500 |
| parents | 19a58adf5d1f |
| children | 39189dd94f2c |
| 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 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
11 import os, 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 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
25 if sqlite.version_info < (2, 1, 0): |
|
3794
f5ddd1639a15
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
3752
diff
changeset
|
26 raise ValueError('pysqlite2 minimum version is 2.1.0+ ' |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
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 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
33 |
|
2633
a9e1fff1e793
I thought I committed this last night. Ho hum.
Richard Jones <richard@users.sourceforge.net>
parents:
2514
diff
changeset
|
34 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
|
35 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
|
36 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
37 |
|
2633
a9e1fff1e793
I thought I committed this last night. Ho hum.
Richard Jones <richard@users.sourceforge.net>
parents:
2514
diff
changeset
|
38 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
|
39 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
|
40 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
41 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
42 class Database(rdbms_common.Database): |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
43 """Sqlite DB backend implementation |
|
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 attributes: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
46 dbtype: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
47 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
|
48 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
|
49 module when using native text search mode. |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
50 """ |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
51 |
| 1165 | 52 # char to use for positional arguments |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
53 if sqlite_version in (2, 3): |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
54 arg = '?' |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
55 else: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
56 arg = '%s' |
|
3048
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3008
diff
changeset
|
57 |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
58 dbtype = "sqlite" |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5067
diff
changeset
|
59 |
|
3048
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3008
diff
changeset
|
60 # 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
|
61 implements_intersect = 1 |
|
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3008
diff
changeset
|
62 |
|
5175
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
63 # used in generic backend to determine if db supports |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
64 # 'DOUBLE PRECISION' for floating point numbers. Note that sqlite |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
65 # 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
|
66 # is set to False here. |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
67 |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
68 implements_double_precision = False |
|
e1e40674a0bc
Implement double-precision Number
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5143
diff
changeset
|
69 |
|
2168
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
70 hyperdb_to_sql_datatypes = { |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
71 hyperdb.String : 'VARCHAR(255)', |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
72 hyperdb.Date : 'VARCHAR(30)', |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
73 hyperdb.Link : 'INTEGER', |
|
2168
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
74 hyperdb.Interval : 'VARCHAR(255)', |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
75 hyperdb.Password : 'VARCHAR(255)', |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
76 hyperdb.Boolean : 'BOOLEAN', |
|
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
77 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
|
78 hyperdb.Integer : 'INTEGER', |
|
2168
7e595abb781e
better sqlite columns
Richard Jones <richard@users.sourceforge.net>
parents:
2144
diff
changeset
|
79 } |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
80 hyperdb_to_sql_value = { |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
81 hyperdb.String : str, |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
82 hyperdb.Date : lambda x: x.serialise(), |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
83 hyperdb.Link : int, |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
84 hyperdb.Interval : str, |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
85 hyperdb.Password : str, |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
86 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
|
87 hyperdb.Integer : int, |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
88 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
|
89 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
|
90 } |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
91 sql_to_hyperdb_value = { |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
92 hyperdb.String : uany2s, |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
93 hyperdb.Date : lambda x: date.Date(str(x)), |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
94 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
|
95 hyperdb.Interval : date.Interval, |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
96 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
|
97 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
|
98 hyperdb.Integer : int, |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
99 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
|
100 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
|
101 } |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
102 |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
103 # 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
|
104 # 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
|
105 # 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
|
106 def getSessionManager(self): |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
107 if not self.Session: |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
108 self.Session = Sessions(self) |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
109 return self.Session |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
110 |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
111 def getOTKManager(self): |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
112 if not self.Otk: |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
113 self.Otk = OneTimeKeys(self) |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
114 return self.Otk |
|
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
115 |
|
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 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
|
117 """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
|
118 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
|
119 if count == 1: |
|
4415
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
120 # Timeout for handling locked database (default 30s) |
|
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
121 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
|
122 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
|
123 # 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
|
124 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
|
125 # 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
|
126 # 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
|
127 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
|
128 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
|
129 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
|
130 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
131 def sql_open_connection(self): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
132 """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
|
133 |
|
31cb1014300c
Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents:
2273
diff
changeset
|
134 pysqlite will automatically BEGIN TRANSACTION for us. |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
135 """ |
|
2764
d3b3f1b3d59e
sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2736
diff
changeset
|
136 # make sure the database directory exists |
|
d3b3f1b3d59e
sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2736
diff
changeset
|
137 # 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
|
138 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
|
139 os.makedirs(self.config.DATABASE) |
|
d3b3f1b3d59e
sqlite database creation requires existing directory.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2736
diff
changeset
|
140 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
141 db = os.path.join(self.config.DATABASE, 'db') |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
142 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
|
143 # 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
|
144 # 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
|
145 if sqlite_version == 1: |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
146 conn = sqlite.connect(db=db) |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
147 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
|
148 else: |
|
4415
3e35233ea93c
new rdbms config item sqlite_timeout...
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4357
diff
changeset
|
149 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
|
150 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
|
151 |
|
4240
289f249ba192
compatibilty fix for pysqlite2 / unicode
Richard Jones <richard@users.sourceforge.net>
parents:
4231
diff
changeset
|
152 # 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
|
153 # 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
|
154 # 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
|
155 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
|
156 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
|
157 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
158 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
|
159 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
|
160 |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
161 def open_connection(self): |
| 1165 | 162 # 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
|
163 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
|
164 |
|
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
165 (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
|
166 |
| 1165 | 167 try: |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
168 self.load_dbschema() |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5175
diff
changeset
|
169 except sqlite.DatabaseError as error: |
| 1165 | 170 if str(error) != 'no such table: schema': |
| 171 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
|
172 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
|
173 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
|
174 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
|
175 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
|
176 self.create_version_2_tables() |
|
6588
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
177 self._add_fts5_table() |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
178 |
|
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
179 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
|
180 self.sql('create table otks (otk_key varchar, ' |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
181 '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
|
182 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
|
183 self.sql('create table sessions (session_key varchar, ' |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
184 '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
|
185 self.sql('create index sessions_key_idx on ' |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
186 'sessions(session_key)') |
| 1165 | 187 |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2082
diff
changeset
|
188 # 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
|
189 self.sql('CREATE TABLE __textids (_class varchar, ' |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
190 '_itemid varchar, _prop varchar, _textid' |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
191 ' 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
|
192 self.sql('CREATE TABLE __words (_word varchar, ' |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
193 '_textid integer)') |
|
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('CREATE INDEX words_word_ids ON __words(_word)') |
|
3858
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3794
diff
changeset
|
195 self.sql('CREATE INDEX words_by_id ON __words (_textid)') |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3794
diff
changeset
|
196 self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3794
diff
changeset
|
197 '__textids (_class, _itemid, _prop)') |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
198 sql = 'insert into ids (name, num) values (%s,%s)' % ( |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
199 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
|
200 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
|
201 |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
202 def add_new_columns_v2(self): |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
203 # update existing tables to have the new actor column |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
204 tables = self.database_schema['tables'] |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
205 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
|
206 if classname in tables: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
207 dbspec = tables[classname] |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
208 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
|
209 # 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
|
210 tables[classname] = spec.schema() |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
211 |
|
2721
1cd01cf106e1
extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents:
2645
diff
changeset
|
212 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
|
213 # 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
|
214 pass |
|
1cd01cf106e1
extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents:
2645
diff
changeset
|
215 |
|
6588
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
216 def _add_fts5_table(self): |
|
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
217 self.sql('CREATE virtual TABLE __fts USING fts5(_class, ' |
|
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
218 '_itemid, _prop, _textblob)' |
|
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
219 ) |
|
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
220 |
|
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
221 def fix_version_6_tables(self): |
|
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
222 # Add native full-text indexing table |
|
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
223 self._add_fts5_table() |
|
91ab3e0ffcd0
Summary: Add test cases for sqlite fts
John Rouillard <rouilj@ieee.org>
parents:
6050
diff
changeset
|
224 |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
225 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
|
226 """ 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
|
227 database version of the spec, and update where necessary. |
|
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 If 'force' is true, update the database anyway. |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
230 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
231 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
|
232 regenerate the tables with the new schema. |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
233 """ |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
234 new_spec = spec.schema() |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
235 new_spec[1].sort() |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
236 old_spec[1].sort() |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
237 if not force and new_spec == old_spec: |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
238 # no changes |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
239 return 0 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
240 |
|
4421
67bef70ab9b9
- more logger fixes, sorry for the noise.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
4420
diff
changeset
|
241 logging.getLogger('roundup.hyperdb').info( |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
242 'update_class %s' % spec.classname) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
243 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
244 # 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
|
245 old_has = {} |
|
2137
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
246 for name, prop in old_spec[1]: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
247 old_has[name] = 1 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
248 if name in spec.properties or not \ |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
249 isinstance(prop, hyperdb.Multilink): |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
250 continue |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
251 # 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
|
252 # table. First drop indexes. |
|
3347
9020fe58c51c
more errors. backport candidate
Anthony Baxter <anthonybaxter@users.sourceforge.net>
parents:
3310
diff
changeset
|
253 self.drop_multilink_table_indexes(spec.classname, name) |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
254 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
|
255 self.sql(sql) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
256 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
257 # 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
|
258 if adding_v2: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
259 fetch = ['_activity', '_creation', '_creator'] |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
260 else: |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
261 fetch = ['_actor', '_activity', '_creation', '_creator'] |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
262 properties = spec.getprops() |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
263 for propname, _x in new_spec[1]: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
264 prop = properties[propname] |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
265 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
|
266 if propname not in old_has: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
267 # we need to create the new table |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
268 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
|
269 elif force: |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
270 tn = '%s_%s' % (spec.classname, propname) |
|
2137
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
271 # grabe the current values |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
272 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
|
273 self.sql(sql) |
|
2137
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
274 rows = self.cursor.fetchall() |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
275 |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
276 # drop the old table |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
277 self.drop_multilink_table_indexes(spec.classname, propname) |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
278 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
|
279 self.sql(sql) |
|
2137
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
280 |
|
c49495585c44
Fix for sqlite backend migration. Change Cookie -> SimpleCookie
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
281 # 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
|
282 self.create_multilink_table(spec, propname) |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
283 sql = """insert into %s (linkid, nodeid) values |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
284 (%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
|
285 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
|
286 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
|
287 elif propname in old_has: |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
288 # we copy this col over from the old table |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
289 fetch.append('_'+propname) |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
290 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
291 # select the data out of the old table |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
292 fetch.append('id') |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
293 fetch.append('__retired__') |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
294 fetchcols = ','.join(fetch) |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
295 cn = spec.classname |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
296 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
|
297 self.sql(sql) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
298 olddata = self.cursor.fetchall() |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
299 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
300 # TODO: update all the other index dropping code |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
301 self.drop_class_table_indexes(cn, old_spec[0]) |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
302 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
303 # drop the old table |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
304 self.sql('drop table _%s' % cn) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
305 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
306 # create the new table |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
307 self.create_class_table(spec) |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
308 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
309 if olddata: |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
310 inscols = ['id', '_actor', '_activity', '_creation', |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
311 '_creator', '__retired__'] |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
312 for propname, _x in new_spec[1]: |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
313 prop = properties[propname] |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
314 if isinstance(prop, hyperdb.Multilink): |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
315 continue |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
316 elif isinstance(prop, hyperdb.Interval): |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
317 inscols.append('_'+propname) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
318 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
|
319 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
|
320 # 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
|
321 inscols.append('_'+propname) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
322 |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
323 # 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
|
324 # NULL values |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
325 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
|
326 cols = ','.join(inscols) |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
327 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
|
328 for entry in olddata: |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
329 d = [] |
|
4774
3adff0fb0207
Fixed issue2550595: Allow migrating from roundup 0.x to 1.4
Thomas Arendsen Hein <thomas@intevation.de>
parents:
4421
diff
changeset
|
330 retired_id = None |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
331 for name in inscols: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
332 # 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
|
333 if name.endswith('_int__'): |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
334 name = name[2:-6] |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
335 if sqlite_version in (2, 3): |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
336 try: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
337 v = hyperdb.Interval(entry[name]).as_seconds() |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
338 except IndexError: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
339 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
|
340 elif name in entry: |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
341 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
|
342 else: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
343 v = None |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
344 elif sqlite_version in (2, 3): |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
345 try: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
346 v = entry[name] |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
347 except IndexError: |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
348 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
|
349 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
|
350 v = entry[name] |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
351 else: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
352 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
|
353 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
|
354 retired_id = v |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
355 elif name == '__retired__' and retired_id and \ |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
356 v not in ['0', 0]: |
|
4774
3adff0fb0207
Fixed issue2550595: Allow migrating from roundup 0.x to 1.4
Thomas Arendsen Hein <thomas@intevation.de>
parents:
4421
diff
changeset
|
357 v = retired_id |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2191
diff
changeset
|
358 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
|
359 self.sql(sql, tuple(d)) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
360 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
361 return 1 |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
362 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
363 def sql_close(self): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
364 """ Squash any error caused by us already having closed the |
| 1236 | 365 connection. |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
366 """ |
| 1236 | 367 try: |
|
2374
31cb1014300c
Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents:
2273
diff
changeset
|
368 self.conn.close() |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5175
diff
changeset
|
369 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
|
370 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
|
371 raise |
| 1236 | 372 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
373 def sql_rollback(self): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
374 """ Squash any error caused by us having closed the connection (and |
| 1236 | 375 therefore not having anything to roll back) |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
376 """ |
| 1236 | 377 try: |
| 378 self.conn.rollback() | |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5175
diff
changeset
|
379 except sqlite.ProgrammingError as value: |
| 1236 | 380 if str(value) != 'rollback failed - Connection is closed.': |
| 381 raise | |
| 382 | |
| 1165 | 383 def __repr__(self): |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
384 return '<roundlite 0x%x>' % id(self) |
| 1165 | 385 |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5318
diff
changeset
|
386 def sql_commit(self): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
387 """ Actually commit to the database. |
| 1165 | 388 |
| 389 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
|
390 """ |
| 1165 | 391 try: |
| 392 self.conn.commit() | |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5175
diff
changeset
|
393 except sqlite.DatabaseError as error: |
| 1165 | 394 if str(error) != 'cannot commit - no transaction is active': |
| 395 raise | |
|
2374
31cb1014300c
Switch to using sqlite's own locking mechanisms...
Richard Jones <richard@users.sourceforge.net>
parents:
2273
diff
changeset
|
396 # 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
|
397 self.cursor = self.conn.cursor() |
| 1165 | 398 |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
399 def sql_index_exists(self, table_name, index_name): |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
400 self.sql('pragma index_list(%s)' % table_name) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
401 for entry in self.cursor.fetchall(): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
402 if entry[1] == index_name: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
403 return 1 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
404 return 0 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
405 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
406 # old-skool id generation |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
407 def newid(self, classname): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
408 """ Generate a new id for the given class |
|
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
409 """ |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
410 |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
411 # 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
|
412 # 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
|
413 # new id and hilarity results. |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
414 # |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
415 # 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
|
416 # isolation_level to None. Pysqlite can commit |
|
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
417 # 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
|
418 # 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
|
419 # don't have to deal with this autocommit/autotransact foolishness. |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
420 self.conn.isolation_level = None |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
421 # Manage the transaction locks manually. |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
422 self.sql("BEGIN IMMEDIATE") |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
423 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
424 # get the next ID |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
425 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
|
426 self.sql(sql, (classname, )) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
427 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
|
428 |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
429 # leave the next larger number as the next newid |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
430 sql = 'update ids set num=num+1 where name=%s' % self.arg |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
431 vals = (classname,) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2374
diff
changeset
|
432 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
433 |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
434 # 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
|
435 # rest of the code expects it. |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
436 self.conn.isolation_level = '' |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
437 # 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
|
438 # and create a new cursor to the database. |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
439 self.sql_commit() |
|
5143
9cebf686c552
issue2550727: db.newid is broken with sqlite.
John Rouillard <rouilj@ieee.org>
parents:
5096
diff
changeset
|
440 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
441 # return as string |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
442 return str(newid) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
443 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
444 def setid(self, classname, setid): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
445 """ 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
|
446 |
|
3306
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
447 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
|
448 """ |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
449 sql = 'update ids set num=%s where name=%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
|
450 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
|
451 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
452 |
|
3306
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
453 def clear(self): |
|
3310
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3306
diff
changeset
|
454 rdbms_common.Database.clear(self) |
|
3306
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
455 # 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
|
456 for cn in self.classes.keys(): |
|
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
457 self.setid(cn, 0) |
|
ae1093684ef5
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3163
diff
changeset
|
458 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
459 def create_class(self, spec): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
460 rdbms_common.Database.create_class(self, spec) |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
461 sql = 'insert into ids (name, num) values (%s, %s)' %( |
|
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
462 self.arg, self.arg) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
463 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
|
464 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2094
diff
changeset
|
465 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
466 if sqlite_version in (2, 3): |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
467 def load_journal(self, classname, cols, nodeid): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
468 """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
|
469 unpacked""" |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
470 l = rdbms_common.Database.load_journal(self, |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
471 classname, cols, nodeid) |
|
3718
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
472 cols = range(5) |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
473 return [[row[col] for col in cols] for row in l] |
|
0d561b24ceff
support sqlite3
Richard Jones <richard@users.sourceforge.net>
parents:
3693
diff
changeset
|
474 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
475 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
476 class sqliteClass: |
|
5869
16e1255b16cf
Implement limit and offset for filter
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5867
diff
changeset
|
477 def filter(self, *args, **kw): |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
478 """ 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
|
479 instead of nothing |
|
4076
59d02edfaa9c
Uniformly use """...""" instead of '''...''' for comments.
Stefan Seefeld <stefan@seefeld.name>
parents:
3858
diff
changeset
|
480 """ |
|
5869
16e1255b16cf
Implement limit and offset for filter
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5867
diff
changeset
|
481 return [f for f in rdbms_common.Class.filter(self, *args, **kw) if f] |
| 1165 | 482 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
483 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
484 class Class(sqliteClass, rdbms_common.Class): |
|
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
485 pass |
|
1168
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
486 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
487 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
488 class IssueClass(sqliteClass, rdbms_common.IssueClass): |
|
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
489 pass |
|
1168
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
490 |
|
6050
19a58adf5d1f
Flake8: whitepace; don't import base64; unused loop var gets leading _
John Rouillard <rouilj@ieee.org>
parents:
5869
diff
changeset
|
491 |
|
1911
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
492 class FileClass(sqliteClass, rdbms_common.FileClass): |
|
f5c804379c85
fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents:
1906
diff
changeset
|
493 pass |
|
1168
94620e088e3a
fixes to the rdbms backends
Richard Jones <richard@users.sourceforge.net>
parents:
1165
diff
changeset
|
494 |
|
2645
8250c63c3963
remove space at EOL, add vim modeline
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2633
diff
changeset
|
495 # vim: set et sts=4 sw=4 : |
