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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
36 import logging
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
37 import os
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
38 import shutil
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
39 import sys
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
40
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
41 import MySQLdb
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
42 from MySQLdb.constants import ER
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
46
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
47
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
48 isolation_levels = {
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
49 'read uncommitted': 'READ UNCOMMITTED',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
50 'read committed': 'READ COMMITTED',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
51 'repeatable read': 'REPEATABLE READ',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
52 'serializable': 'SERIALIZABLE'
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
53 }
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
3175bb92ca28 Cleanups for bandit
John Rouillard <rouilj@ieee.org>
parents: 5919
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
151 hyperdb.String: 'TEXT',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
152 hyperdb.Date: 'DATETIME',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
153 hyperdb.Link: 'INTEGER',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
154 hyperdb.Interval: 'VARCHAR(255)',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
155 hyperdb.Password: 'VARCHAR(255)',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
156 hyperdb.Boolean: 'BOOL',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
157 hyperdb.Number: 'REAL',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
164 hyperdb.Date: lambda x: x.formal(sep=' '), # noqa: E272
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
165 hyperdb.Link: int,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
166 hyperdb.Interval: str,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
167 hyperdb.Password: str,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
168 hyperdb.Boolean: int,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
169 hyperdb.Number: lambda x: x, # noqa: E272
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
170 hyperdb.Integer: int,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
204 self.sql("CREATE TABLE `schema` (`schema` TEXT) ENGINE=%s" %
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
3175bb92ca28 Cleanups for bandit
John Rouillard <rouilj@ieee.org>
parents: 5919
diff changeset
217 # bandit - schema is trusted
3175bb92ca28 Cleanups for bandit
John Rouillard <rouilj@ieee.org>
parents: 5919
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
254 sql = 'insert into ids (name, num) values (%s,%s)' % (self.arg,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
465 scols = ','.join(['%s %s' % x for x in cols])
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
466 sql = 'create table _%s (%s) ENGINE=%s' % (spec.classname, scols,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
514 sql = 'create index _%s_%s_idx on _%s(_%s(255))' % (cn, key,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
539 nodeid integer, date datetime, tag varchar(255),
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
540 action varchar(255), params text) ENGINE=%s''' % (
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
546 index_name = '%s_journ_idx' % classname
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
554 nodeid VARCHAR(255)) ENGINE=%s''' % (spec.classname, ml,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
561 '%s_%s_l_idx' % (classname, ml),
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
562 '%s_%s_n_idx' % (classname, ml)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
563 ]
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
572 table_name = '_%s' % cn
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
669 classname = proptree.parent.classname # noqa: E221
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
670 multilink_table = proptree.propclass.table_name # noqa: E221
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
671 nodeid_name = proptree.propclass.nodeid_name # noqa: E221
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
680 w = ', %s where %s.%s=%s.id and %s.__retired__=0' % (
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
681 tn2, tn1, linkid_name, tn2, tn2)
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
686 def create_inner(self, **propvalues):
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
687 try:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
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
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
690 self._handle_integrity_error(e, propvalues)
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
691
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
692 def set_inner(self, nodeid, **propvalues):
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
693 try:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
694 return rdbms_common.Class.set_inner(self, nodeid,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
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
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
697 self._handle_integrity_error(e, propvalues)
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
698
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
699 def _handle_integrity_error(self, e, propvalues):
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
700 ''' Handle a MySQL IntegrityError.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
701
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
702 If the error is recognized, then it may be converted into an
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
703 alternative exception. Otherwise, it is raised unchanged from
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
704 this function.'''
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
705
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
706 # There are checks in create_inner/set_inner to see if a node
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
707 # is being created with the same key as an existing node.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
708 # But, there is a race condition -- we may pass those checks,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
709 # only to find out that a parallel session has created the
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
710 # node by by the time we actually issue the SQL command to
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
711 # create the node. Fortunately, MySQL gives us a unique error
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
712 # code for this situation, so we can detect it here and handle
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
713 # it appropriately.
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
714 #
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
715 # The details of the race condition are as follows, where
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
716 # "X" is a classname, and the term "thread" is meant to
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
717 # refer generically to both threads and processes:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
718 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
719 # Thread A Thread B
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
720 # -------- --------
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
721 # read table for X
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
722 # create new X object
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
723 # commit
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
724 # create new X object
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
725 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
726 # In Thread B, the check in create_inner does not notice that
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
727 # the new X object is a duplicate of that committed in Thread
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
728 # A because MySQL's default "consistent nonlocking read"
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
729 # behavior means that Thread B sees a snapshot of the database
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
730 # at the point at which its transaction began -- which was
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
731 # before Thread A created the object. However, the attempt
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
732 # to *write* to the table for X, creating a duplicate entry,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
733 # triggers an error at the point of the write.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
734 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
735 # If both A and B's transaction begins with creating a new X
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
736 # object, then this bug cannot occur because creating the
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
737 # object requires getting a new ID, and newid() locks the id
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
738 # table until the transaction is committed or rolledback. So,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
739 # B will block until A's commit is complete, and will not
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
740 # actually get its snapshot until A's transaction completes.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
741 # But, if the transaction has begun prior to calling newid,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
742 # then the snapshot has already been established.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
743 if e[0] == ER.DUP_ENTRY:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
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
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
746 # We don't know what this exception is; reraise it.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
747 raise
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
748
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
752
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
756
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
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 :

Roundup Issue Tracker: http://roundup-tracker.org/