Mercurial > p > roundup > code
annotate roundup/backends/back_mysql.py @ 8241:741ea8a86012
fix: issue2551374. Error handling for filter expressions.
Errors in filter expressions are now reported. The UI needs some work
but even the current code is helpful when debugging filter
expressions.
mlink_expr:
defines/raises ExpressionError(error string template,
context=dict())
raises ExpressionError when it detects errors when popping arguments
off stack
raises ExpressionError when more than one element left on the stack
before returning
also ruff fix to group boolean expression with parens
back_anydbm.py, rdbms_common.py:
catches ExpressionError, augments context with class and
attribute being searched. raises the exception
for both link and multilink relations
client.py
catches ExpressionError returning a basic error page. The page is a
dead end. There are no links or anything for the user to move
forward. The user has to go back, possibly refresh the page (because
the submit button may be disalbled) re-enter the query and try
again.
This needs to be improved.
test_liveserver.py
test the error page generated by client.py
db_test_base
unit tests for filter with too few arguments, too many arguments,
check all repr and str formats.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Mon, 30 Dec 2024 20:22:55 -0500 |
| parents | 3d7292d222d1 |
| children |
| rev | line source |
|---|---|
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
1 # |
|
1433
8429095241d7
mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1415
diff
changeset
|
2 # Copyright (c) 2003 Martynas Sklyzmantas, Andrey Lebedev <andrey@micro.lt> |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
3 # |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
4 # This module is free software, and you may redistribute it and/or modify |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
5 # under the same terms as Python, so long as this copyright message and |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
6 # disclaimer are retained in their original form. |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
7 # |
|
2005
fc52d57c6c3e
documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents:
1988
diff
changeset
|
8 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
9 '''This module defines a backend implementation for MySQL. |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
10 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
11 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
12 How to implement AUTO_INCREMENT: |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
13 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
14 mysql> create table foo (num integer auto_increment primary key, name |
|
3934
8c75e8fce5dc
Fix a couple more old instances of "type" instead of "ENGINE" for mysql backend.
Richard Jones <richard@users.sourceforge.net>
parents:
3929
diff
changeset
|
15 varchar(255)) AUTO_INCREMENT=1 ENGINE=InnoDB; |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
16 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
17 ql> insert into foo (name) values ('foo5'); |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
18 Query OK, 1 row affected (0.00 sec) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
19 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
20 mysql> SELECT num FROM foo WHERE num IS NULL; |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
21 +-----+ |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
22 | num | |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
23 +-----+ |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
24 | 4 | |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
25 +-----+ |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
26 1 row in set (0.00 sec) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
27 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
28 mysql> SELECT num FROM foo WHERE num IS NULL; |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
29 Empty set (0.00 sec) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
30 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
31 NOTE: we don't need an index on the id column if it's PRIMARY KEY |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
32 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
33 ''' |
|
2005
fc52d57c6c3e
documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents:
1988
diff
changeset
|
34 __docformat__ = 'restructuredtext' |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
35 |
| 6931 | 36 import logging |
| 37 import os | |
| 38 import shutil | |
| 39 import sys | |
| 40 | |
| 41 import MySQLdb | |
| 42 from MySQLdb.constants import ER | |
| 43 | |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
44 from roundup import date, hyperdb, password |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
45 from roundup.backends import rdbms_common |
| 6931 | 46 |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
47 |
| 6931 | 48 isolation_levels = { |
| 49 'read uncommitted': 'READ UNCOMMITTED', | |
| 50 'read committed': 'READ COMMITTED', | |
| 51 'repeatable read': 'REPEATABLE READ', | |
| 52 'serializable': 'SERIALIZABLE' | |
| 53 } | |
| 54 | |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4466
diff
changeset
|
55 |
|
2693
59ea85d47d34
add dbnamestr argument to connection_dict...
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2679
diff
changeset
|
56 def connection_dict(config, dbnamestr=None): |
|
59ea85d47d34
add dbnamestr argument to connection_dict...
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2679
diff
changeset
|
57 d = rdbms_common.connection_dict(config, dbnamestr) |
|
5381
0942fe89e82e
Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents:
5378
diff
changeset
|
58 if 'password' in d: |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
59 d['passwd'] = d['password'] |
|
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
60 del d['password'] |
|
5381
0942fe89e82e
Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents:
5378
diff
changeset
|
61 if 'port' in d: |
|
3025
7032b500b7e0
fix port number as int in mysql connection info [SF#1082530]
Richard Jones <richard@users.sourceforge.net>
parents:
2727
diff
changeset
|
62 d['port'] = int(d['port']) |
|
6152
546763f4ce44
Make the charset configurable for mysql
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6149
diff
changeset
|
63 charset = config.RDBMS_MYSQL_CHARSET |
|
546763f4ce44
Make the charset configurable for mysql
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6149
diff
changeset
|
64 if charset != 'default': |
|
546763f4ce44
Make the charset configurable for mysql
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6149
diff
changeset
|
65 d['charset'] = charset |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
66 return d |
|
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
67 |
| 6931 | 68 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
69 def db_nuke(config): |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
70 """Clear all database contents and drop database itself""" |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
71 if db_exists(config): |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
72 kwargs = connection_dict(config) |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
73 conn = MySQLdb.connect(**kwargs) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
74 try: |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
75 conn.select_db(config.RDBMS_NAME) |
| 6002 | 76 except MySQLdb.Error: |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
77 # no, it doesn't exist |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
78 pass |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
79 else: |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
80 cursor = conn.cursor() |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
81 cursor.execute("SHOW TABLES") |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
82 tables = cursor.fetchall() |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
83 # stupid MySQL bug requires us to drop all the tables first |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
84 for table in tables: |
| 6931 | 85 command = 'DROP TABLE `%s`' % table[0] |
|
4085
04843a029ea1
Fix some broken logging.
Richard Jones <richard@users.sourceforge.net>
parents:
4060
diff
changeset
|
86 logging.debug(command) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
87 cursor.execute(command) |
| 6931 | 88 command = "DROP DATABASE %s" % config.RDBMS_NAME |
|
4085
04843a029ea1
Fix some broken logging.
Richard Jones <richard@users.sourceforge.net>
parents:
4060
diff
changeset
|
89 logging.info(command) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
90 cursor.execute(command) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
91 conn.commit() |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
92 conn.close() |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
93 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
94 if os.path.exists(config.DATABASE): |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
95 shutil.rmtree(config.DATABASE) |
|
1839
06f5b36b201b
Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
96 |
| 6931 | 97 |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
98 def db_create(config): |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
99 """Create the database.""" |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
100 kwargs = connection_dict(config) |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
101 conn = MySQLdb.connect(**kwargs) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
102 cursor = conn.cursor() |
|
7860
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
103 command = "CREATE DATABASE %s COLLATE %s" % (config.RDBMS_NAME, |
|
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
104 config.RDBMS_MYSQL_COLLATION) |
|
5543
bc3e00a3d24b
MySQL backend fixes for Python 3.
Joseph Myers <jsm@polyomino.org.uk>
parents:
5507
diff
changeset
|
105 if sys.version_info[0] > 2: |
|
7860
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
106 charset = config.RDBMS_MYSQL_CHARSET |
|
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
107 if charset == 'default': |
|
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
108 charset = 'utf8mb4' # use full utf set. |
|
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
109 command += ' CHARACTER SET %s' % charset |
|
4085
04843a029ea1
Fix some broken logging.
Richard Jones <richard@users.sourceforge.net>
parents:
4060
diff
changeset
|
110 logging.info(command) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
111 cursor.execute(command) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
112 conn.commit() |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
113 conn.close() |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
114 |
| 6931 | 115 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
116 def db_exists(config): |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
117 """Check if database already exists.""" |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
118 kwargs = connection_dict(config) |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
119 conn = MySQLdb.connect(**kwargs) |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
120 try: |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
121 try: |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
122 conn.select_db(config.RDBMS_NAME) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
123 except MySQLdb.OperationalError: |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
124 return 0 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
125 finally: |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
126 conn.close() |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
127 return 1 |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
128 |
|
1434
b953750bdc04
trackers on mysql can be initialised
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1433
diff
changeset
|
129 |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
130 class Database(rdbms_common.Database): |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
131 """ Mysql DB backend implementation |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
132 |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
133 attributes: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
134 dbtype: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
135 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:
5075
diff
changeset
|
136 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:
5075
diff
changeset
|
137 module when using native text search mode. |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
138 """ |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
139 |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
140 arg = '%s' |
|
1839
06f5b36b201b
Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
141 |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
142 dbtype = "mysql" |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
143 |
|
3048
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3025
diff
changeset
|
144 # used by some code to switch styles of query |
|
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3025
diff
changeset
|
145 implements_intersect = 0 |
|
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3025
diff
changeset
|
146 |
|
1912
2b0ab61db194
fixes for [SF#818339]
Richard Jones <richard@users.sourceforge.net>
parents:
1911
diff
changeset
|
147 # Backend for MySQL to use. |
|
1913
d929c31a3620
clarified minimum version required for MySQL backend (4.0.16)
Richard Jones <richard@users.sourceforge.net>
parents:
1912
diff
changeset
|
148 mysql_backend = 'InnoDB' |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
149 |
|
2099
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
150 hyperdb_to_sql_datatypes = { |
| 6931 | 151 hyperdb.String: 'TEXT', |
| 152 hyperdb.Date: 'DATETIME', | |
| 153 hyperdb.Link: 'INTEGER', | |
| 154 hyperdb.Interval: 'VARCHAR(255)', | |
| 155 hyperdb.Password: 'VARCHAR(255)', | |
| 156 hyperdb.Boolean: 'BOOL', | |
| 157 hyperdb.Number: 'REAL', | |
| 158 hyperdb.Integer: 'INTEGER', | |
|
2099
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
159 } |
|
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
160 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
161 hyperdb_to_sql_value = { |
| 6931 | 162 hyperdb.String: str, |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
163 # no fractional seconds for MySQL |
| 6931 | 164 hyperdb.Date: lambda x: x.formal(sep=' '), # noqa: E272 |
| 165 hyperdb.Link: int, | |
| 166 hyperdb.Interval: str, | |
| 167 hyperdb.Password: str, | |
| 168 hyperdb.Boolean: int, | |
| 169 hyperdb.Number: lambda x: x, # noqa: E272 | |
| 170 hyperdb.Integer: int, | |
| 171 hyperdb.Multilink: lambda x: x, # used in journal marshalling, noqa: E272 | |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
172 } |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
173 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
174 def sql_open_connection(self): |
|
2693
59ea85d47d34
add dbnamestr argument to connection_dict...
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2679
diff
changeset
|
175 kwargs = connection_dict(self.config, 'db') |
| 6931 | 176 self.log_info('open database %r' % (kwargs['db'],)) |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
177 try: |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
178 conn = MySQLdb.connect(**kwargs) |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5096
diff
changeset
|
179 except MySQLdb.OperationalError as message: |
|
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5096
diff
changeset
|
180 raise hyperdb.DatabaseError(message) |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
181 cursor = conn.cursor() |
|
3216
27aba1d8fccc
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
182 cursor.execute("SET AUTOCOMMIT=0") |
| 6931 | 183 lvl = isolation_levels[self.config.RDBMS_ISOLATION_LEVEL] |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4466
diff
changeset
|
184 cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL %s" % lvl) |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
185 cursor.execute("START TRANSACTION") |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
186 return (conn, cursor) |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
187 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
188 def open_connection(self): |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
189 # make sure the database actually exists |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
190 if not db_exists(self.config): |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
191 db_create(self.config) |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
192 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
193 self.conn, self.cursor = self.sql_open_connection() |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
194 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
195 try: |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
196 self.load_dbschema() |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5096
diff
changeset
|
197 except MySQLdb.OperationalError as message: |
|
5507
0db3779702d9
access exception args in a Python 3 compatible way
Christof Meerwald <cmeerw@cmeerw.org>
parents:
5381
diff
changeset
|
198 if message.args[0] != ER.NO_DB_ERROR: |
|
1433
8429095241d7
mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1415
diff
changeset
|
199 raise |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5096
diff
changeset
|
200 except MySQLdb.ProgrammingError as message: |
|
5507
0db3779702d9
access exception args in a Python 3 compatible way
Christof Meerwald <cmeerw@cmeerw.org>
parents:
5381
diff
changeset
|
201 if message.args[0] != ER.NO_SUCH_TABLE: |
|
5378
35ea9b1efc14
Python 3 preparation: "raise" syntax.
Joseph Myers <jsm@polyomino.org.uk>
parents:
5319
diff
changeset
|
202 raise hyperdb.DatabaseError(message) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
203 self.init_dbschema() |
| 6931 | 204 self.sql("CREATE TABLE `schema` (`schema` TEXT) ENGINE=%s" % |
| 205 self.mysql_backend) | |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
206 self.sql('''CREATE TABLE ids (name VARCHAR(255), |
| 6931 | 207 num INTEGER) ENGINE=%s''' % self.mysql_backend) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
208 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
|
209 self.create_version_2_tables() |
|
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
210 |
|
3458
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
211 def load_dbschema(self): |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
212 ''' Load the schema definition that the database currently implements |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
213 ''' |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
214 self.cursor.execute('select `schema` from `schema`') |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
215 schema = self.cursor.fetchone() |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
216 if schema: |
| 6002 | 217 # bandit - schema is trusted |
| 218 self.database_schema = eval(schema[0]) # nosec | |
|
3458
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
219 else: |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
220 self.database_schema = {} |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
221 |
|
3603
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
222 def save_dbschema(self): |
|
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
223 ''' Save the schema definition that the database currently implements |
|
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
224 ''' |
|
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
225 s = repr(self.database_schema) |
|
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
226 self.sql('delete from `schema`') |
|
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
227 self.sql('insert into `schema` values (%s)', (s,)) |
|
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
228 |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
229 def create_version_2_tables(self): |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
230 # OTK store |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
231 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255), |
|
6806
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6760
diff
changeset
|
232 otk_value TEXT, otk_time DOUBLE) |
| 6931 | 233 ENGINE=%s''' % self.mysql_backend) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
234 self.sql('CREATE INDEX otks_key_idx ON otks(otk_key)') |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
235 |
|
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
236 # Sessions store |
|
2727
93e2e5b55a3c
new Interval props weren't created properly in rdbms
Richard Jones <richard@users.sourceforge.net>
parents:
2721
diff
changeset
|
237 self.sql('''CREATE TABLE sessions (session_key VARCHAR(255), |
|
6806
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6760
diff
changeset
|
238 session_time DOUBLE, session_value TEXT) |
| 6931 | 239 ENGINE=%s''' % self.mysql_backend) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
240 self.sql('''CREATE INDEX sessions_key_idx ON |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
241 sessions(session_key)''') |
|
1839
06f5b36b201b
Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
242 |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
243 # full-text indexing store |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
244 self.sql('''CREATE TABLE __textids (_class VARCHAR(255), |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
245 _itemid VARCHAR(255), _prop VARCHAR(255), _textid INT) |
| 6931 | 246 ENGINE=%s''' % self.mysql_backend) |
|
6593
e70e2789bc2c
issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents:
6433
diff
changeset
|
247 self.sql('''CREATE TABLE __words (_word VARCHAR(%s), |
|
e70e2789bc2c
issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents:
6433
diff
changeset
|
248 _textid INT) ENGINE=%s''' % ((self.indexer.maxlength + 5), |
| 6931 | 249 self.mysql_backend)) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
250 self.sql('CREATE INDEX words_word_ids ON __words(_word)') |
|
3858
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3687
diff
changeset
|
251 self.sql('CREATE INDEX words_by_id ON __words (_textid)') |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3687
diff
changeset
|
252 self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3687
diff
changeset
|
253 '__textids (_class, _itemid, _prop)') |
| 6931 | 254 sql = 'insert into ids (name, num) values (%s,%s)' % (self.arg, |
| 255 self.arg) | |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
256 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:
2089
diff
changeset
|
257 |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
258 def add_new_columns_v2(self): |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
259 '''While we're adding the actor column, we need to update the |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
260 tables to have the correct datatypes.''' |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
261 for klass in self.classes.values(): |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
262 cn = klass.classname |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
263 properties = klass.getprops() |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
264 old_spec = self.database_schema['tables'][cn] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
265 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
266 # figure the non-Multilink properties to copy over |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
267 propnames = ['activity', 'creation', 'creator'] |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
268 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
269 # figure actions based on data type |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
270 for name, s_prop in old_spec[1]: |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
271 # s_prop is a repr() string of a hyperdb type object |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
272 if s_prop.find('Multilink') == -1: |
|
5381
0942fe89e82e
Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents:
5378
diff
changeset
|
273 if name in properties: |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
274 propnames.append(name) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
275 continue |
| 6931 | 276 tn = '%s_%s' % (cn, name) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
277 |
|
5381
0942fe89e82e
Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents:
5378
diff
changeset
|
278 if name in properties: |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
279 # grabe the current values |
| 6931 | 280 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:
2508
diff
changeset
|
281 self.sql(sql) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
282 rows = self.cursor.fetchall() |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
283 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
284 # drop the old table |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
285 self.drop_multilink_table_indexes(cn, name) |
| 6931 | 286 sql = 'drop table %s' % tn |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
287 self.sql(sql) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
288 |
|
5381
0942fe89e82e
Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents:
5378
diff
changeset
|
289 if name in properties: |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
290 # re-create and populate the new table |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
291 self.create_multilink_table(klass, name) |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
292 sql = '''insert into %s (linkid, nodeid) values |
| 6931 | 293 (%s, %s)''' % (tn, self.arg, self.arg) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
294 for linkid, nodeid in rows: |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
295 self.sql(sql, (int(linkid), int(nodeid))) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
296 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
297 # figure the column names to fetch |
| 6931 | 298 fetch = ['_%s' % name for name in propnames] |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
299 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
300 # select the data out of the old table |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
301 fetch.append('id') |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
302 fetch.append('__retired__') |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
303 fetchcols = ','.join(fetch) |
| 6931 | 304 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:
2508
diff
changeset
|
305 self.sql(sql) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
306 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
307 # unserialise the old data |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
308 olddata = [] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
309 propnames = propnames + ['id', '__retired__'] |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
310 cols = [] |
|
2617
33fffbf7ae68
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
2578
diff
changeset
|
311 first = 1 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
312 for entry in self.cursor.fetchall(): |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
313 l = [] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
314 olddata.append(l) |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
315 for i in range(len(propnames)): |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
316 name = propnames[i] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
317 v = entry[i] |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
318 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
319 if name in ('id', '__retired__'): |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
320 if first: |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
321 cols.append(name) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
322 l.append(int(v)) |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
323 continue |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
324 if first: |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
325 cols.append('_' + name) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
326 prop = properties[name] |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
327 if isinstance(prop, hyperdb.Date) and v is not None: |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
328 v = date.Date(v) |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
329 elif isinstance(prop, hyperdb.Interval) and v is not None: |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
330 v = date.Interval(v) |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
331 elif isinstance(prop, hyperdb.Password) and v is not None: |
|
7211
506c86823abb
Add config argument to more password.Password invocations.
John Rouillard <rouilj@ieee.org>
parents:
6931
diff
changeset
|
332 v = password.Password(encrypted=v, config=self.config) |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
333 elif isinstance(prop, hyperdb.Integer) and v is not None: |
|
5067
e424987d294a
Add support for an integer type to join the existing number type.
John Rouillard <rouilj@ieee.org>
parents:
4887
diff
changeset
|
334 v = int(v) |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
335 elif (isinstance(prop, hyperdb.Boolean) or |
|
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
336 isinstance(prop, hyperdb.Number)) and v is not None: |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
337 v = float(v) |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
338 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
339 # convert to new MySQL data type |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
340 prop = properties[name] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
341 if v is not None: |
|
4060
2a68d7494bbc
Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents:
4059
diff
changeset
|
342 e = self.to_sql_value(prop.__class__)(v) |
|
2431
12fb2aa3c6d2
fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
343 else: |
|
12fb2aa3c6d2
fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
344 e = None |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
345 l.append(e) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
346 |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
347 # Intervals store the seconds value too |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
348 if isinstance(prop, hyperdb.Interval): |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
349 if first: |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
350 cols.append('__' + name + '_int__') |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
351 if v is not None: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
352 l.append(v.as_seconds()) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
353 else: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
354 l.append(e) |
|
2617
33fffbf7ae68
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
2578
diff
changeset
|
355 first = 0 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
356 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
357 self.drop_class_table_indexes(cn, old_spec[0]) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
358 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
359 # drop the old table |
| 6931 | 360 self.sql('drop table _%s' % cn) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
361 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
362 # create the new table |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
363 self.create_class_table(klass) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
364 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
365 # do the insert of the old data |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
366 args = ','.join([self.arg for x in cols]) |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
367 cols = ','.join(cols) |
| 6931 | 368 sql = 'insert into _%s (%s) values (%s)' % (cn, cols, args) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
369 for entry in olddata: |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
370 self.sql(sql, tuple(entry)) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
371 |
|
2244
ac4f295499a4
fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents:
2240
diff
changeset
|
372 # now load up the old journal data to migrate it |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
373 cols = ','.join('nodeid date tag action params'.split()) |
| 6931 | 374 sql = 'select %s from %s__journal' % (cols, cn) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
375 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
376 |
|
2244
ac4f295499a4
fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents:
2240
diff
changeset
|
377 # data conversions |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
378 olddata = [] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
379 for nodeid, journaldate, journaltag, action, params in \ |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
380 self.cursor.fetchall(): |
| 6931 | 381 # nodeid = int(nodeid) |
|
3140
2487b070043b
slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
382 journaldate = date.Date(journaldate) |
| 6931 | 383 # params = eval(params) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
384 olddata.append((nodeid, journaldate, journaltag, action, |
| 6931 | 385 params)) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
386 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
387 # drop journal table and indexes |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
388 self.drop_journal_table_indexes(cn) |
| 6931 | 389 sql = 'drop table %s__journal' % cn |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
390 self.sql(sql) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
391 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
392 # re-create journal table |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
393 self.create_journal_table(klass) |
|
4060
2a68d7494bbc
Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents:
4059
diff
changeset
|
394 dc = self.to_sql_value(hyperdb.Date) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
395 for nodeid, journaldate, journaltag, action, params in olddata: |
|
3140
2487b070043b
slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
396 self.save_journal(cn, cols, nodeid, dc(journaldate), |
| 6931 | 397 journaltag, action, params) |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
398 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
399 # make sure the normal schema update code doesn't try to |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
400 # change things |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
401 self.database_schema['tables'][cn] = klass.schema() |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
402 |
|
2413
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
403 def fix_version_2_tables(self): |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
404 # Convert journal date column to TIMESTAMP, params column to TEXT |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
405 self._convert_journal_tables() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
406 |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
407 # Convert all String properties to TEXT |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
408 self._convert_string_properties() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
409 |
|
6433
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
410 def fix_version_5_tables(self): |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
411 # A bug caused the _<class>_key_retired_idx to be missing |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
412 # unless the database was upgraded from version 4 to 5. |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
413 # If it was created at version 5, the index is missing. |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
414 # The user class is always present and has a key. |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
415 # Check it for the index. If missing, add index to all |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
416 # classes by rerunning self.fix_version_4_tables(). |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
417 |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
418 # if this fails abort. Probably means no user class |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
419 # so we should't be doing anything. |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
420 if not self.sql_index_exists("_user", "_user_key_retired_idx"): |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
421 self.fix_version_4_tables() |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
422 else: |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
423 self.log_info('No changes needed.') |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
424 |
|
6599
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
425 def fix_version_6_tables(self): |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
426 # Modify length for __words._word column. |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
427 c = self.cursor |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
428 sql = "alter table __words change column _word _word varchar(%s)" % ( |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
429 self.arg) |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
430 # Why magic number 5? It was the original offset between |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
431 # column length and maxlength. |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
432 c.execute(sql, (self.indexer.maxlength + 5,)) |
|
39189dd94f2c
issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents:
6593
diff
changeset
|
433 |
|
6806
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6760
diff
changeset
|
434 def fix_version_7_tables(self): |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6760
diff
changeset
|
435 # Modify type for session.session_time/otk.otk_time column. |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6760
diff
changeset
|
436 sql = "alter table sessions modify session_time double" |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6760
diff
changeset
|
437 self.sql(sql) |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6760
diff
changeset
|
438 sql = "alter table otks modify otk_time double" |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6760
diff
changeset
|
439 self.sql(sql) |
|
bdd28b244839
- issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents:
6760
diff
changeset
|
440 |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
441 def __repr__(self): |
| 6931 | 442 return '<myroundsql 0x%x>' % id(self) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
443 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
444 def sql_fetchone(self): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
445 return self.cursor.fetchone() |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
446 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
447 def sql_fetchall(self): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
448 return self.cursor.fetchall() |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
449 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
450 def sql_index_exists(self, table_name, index_name): |
| 6931 | 451 self.sql('show index from %s' % table_name) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
452 for index in self.cursor.fetchall(): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
453 if index[2] == index_name: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
454 return 1 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
455 return 0 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
456 |
|
2424
74474ec41050
argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents:
2413
diff
changeset
|
457 def create_class_table(self, spec, create_sequence=1): |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
458 cols, mls = self.determine_columns(spec.properties.items()) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
459 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
460 # add on our special columns |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
461 cols.append(('id', 'INTEGER PRIMARY KEY')) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
462 cols.append(('__retired__', 'INTEGER DEFAULT 0')) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
463 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
464 # create the base table |
| 6931 | 465 scols = ','.join(['%s %s' % x for x in cols]) |
| 466 sql = 'create table _%s (%s) ENGINE=%s' % (spec.classname, scols, | |
| 467 self.mysql_backend) | |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
468 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
469 |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
470 self.create_class_table_indexes(spec) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
471 return cols, mls |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
472 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
473 def create_class_table_indexes(self, spec): |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
474 ''' create the class table for the given spec |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
475 ''' |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
476 # create __retired__ index |
| 6931 | 477 index_sql2 = 'create index _%s_retired_idx on _%s(__retired__)' % ( |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
478 spec.classname, spec.classname) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
479 self.sql(index_sql2) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
480 |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
481 # create index for key property |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
482 if spec.key: |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
483 if isinstance(spec.properties[spec.key], hyperdb.String): |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
484 idx = spec.key + '(255)' |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
485 else: |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
486 idx = spec.key |
| 6931 | 487 index_sql3 = 'create index _%s_%s_idx on _%s(_%s)' % ( |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
488 spec.classname, spec.key, |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
489 spec.classname, idx) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
490 self.sql(index_sql3) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
491 |
|
6433
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
492 # and the unique index for key / retired(id) |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
493 self.add_class_key_required_unique_constraint(spec.classname, |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
494 spec.key) |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
495 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
496 # TODO: create indexes on (selected?) Link property columns, as |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
497 # they're more likely to be used for lookup |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
498 |
|
3969
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
499 def add_class_key_required_unique_constraint(self, cn, key): |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
500 # mysql requires sizes on TEXT indexes |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
501 prop = self.classes[cn].getprops()[key] |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
502 if isinstance(prop, hyperdb.String): |
|
3969
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
503 sql = '''create unique index _%s_key_retired_idx |
| 6931 | 504 on _%s(__retired__, _%s(255))''' % (cn, cn, key) |
|
3969
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
505 else: |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
506 sql = '''create unique index _%s_key_retired_idx |
| 6931 | 507 on _%s(__retired__, _%s)''' % (cn, cn, key) |
|
3969
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
508 self.sql(sql) |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
509 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
510 def create_class_table_key_index(self, cn, key): |
|
3969
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
511 # mysql requires sizes on TEXT indexes |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
512 prop = self.classes[cn].getprops()[key] |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
513 if isinstance(prop, hyperdb.String): |
| 6931 | 514 sql = 'create index _%s_%s_idx on _%s(_%s(255))' % (cn, key, |
| 515 cn, key) | |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
516 else: |
| 6931 | 517 sql = 'create index _%s_%s_idx on _%s(_%s)' % (cn, key, cn, key) |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
518 self.sql(sql) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
519 |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
520 def drop_class_table_indexes(self, cn, key): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
521 # drop the old table indexes first |
| 6931 | 522 l = ['_%s_id_idx' % cn, '_%s_retired_idx' % cn] |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
523 if key: |
| 6931 | 524 l.append('_%s_%s_idx' % (cn, key)) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
525 |
| 6931 | 526 table_name = '_%s' % cn |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
527 for index_name in l: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
528 if not self.sql_index_exists(table_name, index_name): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
529 continue |
| 6931 | 530 index_sql = 'drop index %s on %s' % (index_name, table_name) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
531 self.sql(index_sql) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
532 |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
533 def create_journal_table(self, spec): |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
534 ''' create the journal table for a class given the spec and |
|
2413
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
535 already-determined cols |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
536 ''' |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
537 # journal table |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
538 sql = '''create table %s__journal ( |
| 6931 | 539 nodeid integer, date datetime, tag varchar(255), |
| 540 action varchar(255), params text) ENGINE=%s''' % ( | |
| 541 spec.classname, self.mysql_backend) | |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
542 self.sql(sql) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
543 self.create_journal_table_indexes(spec) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
544 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
545 def drop_journal_table_indexes(self, classname): |
| 6931 | 546 index_name = '%s_journ_idx' % classname |
| 547 if not self.sql_index_exists('%s__journal' % classname, index_name): | |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
548 return |
| 6931 | 549 index_sql = 'drop index %s on %s__journal' % (index_name, classname) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
550 self.sql(index_sql) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
551 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
552 def create_multilink_table(self, spec, ml): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
553 sql = '''CREATE TABLE `%s_%s` (linkid VARCHAR(255), |
| 6931 | 554 nodeid VARCHAR(255)) ENGINE=%s''' % (spec.classname, ml, |
| 555 self.mysql_backend) | |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
556 self.sql(sql) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
557 self.create_multilink_table_indexes(spec, ml) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
558 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
559 def drop_multilink_table_indexes(self, classname, ml): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
560 l = [ |
| 6931 | 561 '%s_%s_l_idx' % (classname, ml), |
| 562 '%s_%s_n_idx' % (classname, ml) | |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
563 ] |
| 6931 | 564 table_name = '%s_%s' % (classname, ml) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
565 for index_name in l: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
566 if not self.sql_index_exists(table_name, index_name): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
567 continue |
| 6931 | 568 sql = 'drop index %s on %s' % (index_name, table_name) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
569 self.sql(sql) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
570 |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
571 def drop_class_table_key_index(self, cn, key): |
| 6931 | 572 table_name = '_%s' % cn |
| 573 index_name = '_%s_%s_idx' % (cn, key) | |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
574 if not self.sql_index_exists(table_name, index_name): |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
575 return |
| 6931 | 576 sql = 'drop index %s on %s' % (index_name, table_name) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
577 self.sql(sql) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
578 |
|
6433
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
579 # and now the retired unique index too |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
580 index_name = '_%s_key_retired_idx' % cn |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
581 if self.sql_index_exists(table_name, index_name): |
| 6931 | 582 sql = 'drop index %s on _%s' % (index_name, cn) |
|
6433
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
583 self.sql(sql) |
|
c1d3fbcdbfbd
issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents:
6396
diff
changeset
|
584 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
585 # old-skool id generation |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
586 def newid(self, classname): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
587 ''' Generate a new id for the given class |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
588 ''' |
|
2717
ca2ee58c8310
use row locking in MySQL newid() [SF#1034211]
Richard Jones <richard@users.sourceforge.net>
parents:
2693
diff
changeset
|
589 # get the next ID - "FOR UPDATE" will lock the row for us |
| 6931 | 590 sql = 'select num from ids where name=%s FOR UPDATE' % self.arg |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
591 self.sql(sql, (classname, )) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
592 newid = int(self.cursor.fetchone()[0]) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
593 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
594 # update the counter |
| 6931 | 595 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:
2093
diff
changeset
|
596 vals = (int(newid)+1, classname) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
597 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
598 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
599 # return as string |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
600 return str(newid) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
601 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
602 def setid(self, classname, setid): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
603 ''' Set the id counter: used during import of database |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
604 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
605 We add one to make it behave like the seqeunces in postgres. |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
606 ''' |
| 6931 | 607 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:
2093
diff
changeset
|
608 vals = (int(setid)+1, classname) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
609 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
610 |
|
3310
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
611 def clear(self): |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
612 rdbms_common.Database.clear(self) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
613 |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
614 # set the id counters to 0 (setid adds one) so we start at 1 |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
615 for cn in self.classes.keys(): |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
616 self.setid(cn, 0) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
617 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
618 def create_class(self, spec): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
619 rdbms_common.Database.create_class(self, spec) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
620 sql = 'insert into ids (name, num) values (%s, %s)' |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
621 vals = (spec.classname, 1) |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
622 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
623 |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5248
diff
changeset
|
624 def sql_commit(self): |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
625 ''' Actually commit to the database. |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
626 ''' |
|
4059
ef0b4396888a
Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents:
3969
diff
changeset
|
627 self.log_info('commit') |
|
3687
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3635
diff
changeset
|
628 |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3635
diff
changeset
|
629 # MySQL commits don't seem to ever fail, the latest update winning. |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3635
diff
changeset
|
630 # makes you wonder why they have transactions... |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
631 self.conn.commit() |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
632 |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
633 # open a new cursor for subsequent work |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
634 self.cursor = self.conn.cursor() |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
635 |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
636 # make sure we're in a new transaction and not autocommitting |
|
3216
27aba1d8fccc
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
637 self.sql("SET AUTOCOMMIT=0") |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
638 self.sql("START TRANSACTION") |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
639 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
640 def sql_close(self): |
|
4059
ef0b4396888a
Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents:
3969
diff
changeset
|
641 self.log_info('close') |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
642 try: |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
643 self.conn.close() |
|
5917
3a6114d377f4
issue2551025: try handling operational error exception in close
John Rouillard <rouilj@ieee.org>
parents:
5867
diff
changeset
|
644 # issue2551025: with revision 1.3.14 of mysqlclient. |
|
3a6114d377f4
issue2551025: try handling operational error exception in close
John Rouillard <rouilj@ieee.org>
parents:
5867
diff
changeset
|
645 # It looks like you can get an OperationalError 2006 |
|
3a6114d377f4
issue2551025: try handling operational error exception in close
John Rouillard <rouilj@ieee.org>
parents:
5867
diff
changeset
|
646 # raised for closing a closed handle. |
|
3a6114d377f4
issue2551025: try handling operational error exception in close
John Rouillard <rouilj@ieee.org>
parents:
5867
diff
changeset
|
647 except MySQLdb.OperationalError as message: |
|
5918
10fb641f5152
issue2551025: try handling operational error exception in close #2
John Rouillard <rouilj@ieee.org>
parents:
5917
diff
changeset
|
648 if str(message) != "(2006, '')": # close connection |
|
5917
3a6114d377f4
issue2551025: try handling operational error exception in close
John Rouillard <rouilj@ieee.org>
parents:
5867
diff
changeset
|
649 raise |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5096
diff
changeset
|
650 except MySQLdb.ProgrammingError as message: |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
651 if str(message) != 'closing a closed connection': |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
652 raise |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
653 |
| 6931 | 654 |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
655 class MysqlClass: |
|
7860
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
656 |
|
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
657 case_sensitive_equal = None # defined by self.get_case_sensitive_equal() |
|
4466
f1fe6fd0aa61
Multilinks can be filtered by combining elements with AND, OR and NOT now.
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4285
diff
changeset
|
658 |
|
6396
75a53956cf13
Multilink expressions with simple "or"
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6179
diff
changeset
|
659 # TODO: AFAIK its version dependent for MySQL |
|
75a53956cf13
Multilink expressions with simple "or"
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6179
diff
changeset
|
660 supports_subselects = False |
|
4466
f1fe6fd0aa61
Multilinks can be filtered by combining elements with AND, OR and NOT now.
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4285
diff
changeset
|
661 |
|
7860
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
662 def get_case_sensitive_equal(self): |
|
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
663 return 'COLLATE %s =' % self.db.config.RDBMS_MYSQL_BINARY_COLLATION |
|
8b31893f5930
issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents:
7211
diff
changeset
|
664 |
|
6179
a701c9c81597
Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6152
diff
changeset
|
665 def _subselect(self, proptree): |
|
3633
a292054b4393
The whole filter method was replicated in back_mysql.py from rdbms_common.py
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3603
diff
changeset
|
666 ''' "I can't believe it's not a toy RDBMS" |
|
a292054b4393
The whole filter method was replicated in back_mysql.py from rdbms_common.py
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3603
diff
changeset
|
667 see, even toy RDBMSes like gadfly and sqlite can do sub-selects... |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
668 ''' |
| 6931 | 669 classname = proptree.parent.classname # noqa: E221 |
| 670 multilink_table = proptree.propclass.table_name # noqa: E221 | |
| 671 nodeid_name = proptree.propclass.nodeid_name # noqa: E221 | |
| 672 linkid_name = proptree.propclass.linkid_name # noqa: E221 | |
|
6179
a701c9c81597
Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6152
diff
changeset
|
673 |
|
a701c9c81597
Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6152
diff
changeset
|
674 w = '' |
|
a701c9c81597
Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6152
diff
changeset
|
675 if proptree.need_retired: |
| 6931 | 676 w = ' where %s.__retired__=0' % (multilink_table) |
|
6179
a701c9c81597
Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6152
diff
changeset
|
677 if proptree.need_child_retired: |
|
a701c9c81597
Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6152
diff
changeset
|
678 tn1 = multilink_table |
|
a701c9c81597
Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6152
diff
changeset
|
679 tn2 = '_' + proptree.classname |
| 6931 | 680 w = ', %s where %s.%s=%s.id and %s.__retired__=0' % ( |
| 681 tn2, tn1, linkid_name, tn2, tn2) | |
| 682 self.db.sql('select %s from %s%s' % (nodeid_name, multilink_table, w)) | |
|
6148
8497bf3f23a1
Allow to define reverse Multilinks
Ralf Schlatterbeck <rsc@runtux.com>
parents:
6002
diff
changeset
|
683 s = ','.join([str(x[0]) for x in self.db.sql_fetchall()]) |
| 6931 | 684 return '_%s.id not in (%s)' % (classname, s) |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
685 |
| 4285 | 686 def create_inner(self, **propvalues): |
| 687 try: | |
| 688 return rdbms_common.Class.create_inner(self, **propvalues) | |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5096
diff
changeset
|
689 except MySQLdb.IntegrityError as e: |
| 4285 | 690 self._handle_integrity_error(e, propvalues) |
| 691 | |
| 692 def set_inner(self, nodeid, **propvalues): | |
| 693 try: | |
| 694 return rdbms_common.Class.set_inner(self, nodeid, | |
| 695 **propvalues) | |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5096
diff
changeset
|
696 except MySQLdb.IntegrityError as e: |
| 4285 | 697 self._handle_integrity_error(e, propvalues) |
| 698 | |
| 699 def _handle_integrity_error(self, e, propvalues): | |
| 700 ''' Handle a MySQL IntegrityError. | |
| 701 | |
| 702 If the error is recognized, then it may be converted into an | |
| 703 alternative exception. Otherwise, it is raised unchanged from | |
| 704 this function.''' | |
| 705 | |
| 706 # There are checks in create_inner/set_inner to see if a node | |
| 707 # is being created with the same key as an existing node. | |
| 708 # But, there is a race condition -- we may pass those checks, | |
| 709 # only to find out that a parallel session has created the | |
| 710 # node by by the time we actually issue the SQL command to | |
| 711 # create the node. Fortunately, MySQL gives us a unique error | |
| 712 # code for this situation, so we can detect it here and handle | |
| 713 # it appropriately. | |
| 6931 | 714 # |
| 4285 | 715 # The details of the race condition are as follows, where |
| 716 # "X" is a classname, and the term "thread" is meant to | |
| 717 # refer generically to both threads and processes: | |
| 718 # | |
| 719 # Thread A Thread B | |
| 720 # -------- -------- | |
| 721 # read table for X | |
| 722 # create new X object | |
| 723 # commit | |
| 724 # create new X object | |
| 725 # | |
| 726 # In Thread B, the check in create_inner does not notice that | |
| 727 # the new X object is a duplicate of that committed in Thread | |
| 728 # A because MySQL's default "consistent nonlocking read" | |
| 729 # behavior means that Thread B sees a snapshot of the database | |
| 730 # at the point at which its transaction began -- which was | |
| 731 # before Thread A created the object. However, the attempt | |
| 732 # to *write* to the table for X, creating a duplicate entry, | |
| 733 # triggers an error at the point of the write. | |
| 734 # | |
| 735 # If both A and B's transaction begins with creating a new X | |
| 736 # object, then this bug cannot occur because creating the | |
| 737 # object requires getting a new ID, and newid() locks the id | |
| 738 # table until the transaction is committed or rolledback. So, | |
| 739 # B will block until A's commit is complete, and will not | |
| 740 # actually get its snapshot until A's transaction completes. | |
| 741 # But, if the transaction has begun prior to calling newid, | |
| 742 # then the snapshot has already been established. | |
| 743 if e[0] == ER.DUP_ENTRY: | |
| 744 key = propvalues[self.key] | |
|
5378
35ea9b1efc14
Python 3 preparation: "raise" syntax.
Joseph Myers <jsm@polyomino.org.uk>
parents:
5319
diff
changeset
|
745 raise ValueError('node with key "%s" exists' % key) |
| 4285 | 746 # We don't know what this exception is; reraise it. |
| 747 raise | |
| 6931 | 748 |
| 4285 | 749 |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
750 class Class(MysqlClass, rdbms_common.Class): |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
751 pass |
| 6931 | 752 |
| 753 | |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
754 class IssueClass(MysqlClass, rdbms_common.IssueClass): |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
755 pass |
| 6931 | 756 |
| 757 | |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
758 class FileClass(MysqlClass, rdbms_common.FileClass): |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
759 pass |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
760 |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
761 # vim: set et sts=4 sw=4 : |
