Mercurial > p > roundup > code

annotate roundup/backends/back_mysql.py @ 7965:6763813d9d34

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
issue2551350 - Python changes for 3.12 with roundup 2.3.0 cgitb.py Fix change in pydoc.html.header() signature. It dropped foreground and background color arguments in 3.11 and newer. Also enable test code for the html function.
author John Rouillard <rouilj@ieee.org>
date Tue, 14 May 2024 21:27:28 -0400
parents 8b31893f5930
children 3d7292d222d1
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 # InnoDB is faster, but if you're running <4.0.16 then you'll need to
d929c31a3620 clarified minimum version required for MySQL backend (4.0.16)
Richard Jones <richard@users.sourceforge.net>
parents: 1912
diff changeset
149 # use BDB to pass all unit tests.
d929c31a3620 clarified minimum version required for MySQL backend (4.0.16)
Richard Jones <richard@users.sourceforge.net>
parents: 1912
diff changeset
150 mysql_backend = 'InnoDB'
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
151 # mysql_backend = 'BDB'
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
152
2099
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
153 hyperdb_to_sql_datatypes = {
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
154 hyperdb.String: 'TEXT',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
155 hyperdb.Date: 'DATETIME',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
156 hyperdb.Link: 'INTEGER',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
157 hyperdb.Interval: 'VARCHAR(255)',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
158 hyperdb.Password: 'VARCHAR(255)',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
159 hyperdb.Boolean: 'BOOL',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
160 hyperdb.Number: 'REAL',
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
161 hyperdb.Integer: 'INTEGER',
2099
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
162 }
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
163
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
164 hyperdb_to_sql_value = {
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
165 hyperdb.String: str,
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
166 # no fractional seconds for MySQL
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
167 hyperdb.Date: lambda x: x.formal(sep=' '), # noqa: E272
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
168 hyperdb.Link: int,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
169 hyperdb.Interval: str,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
170 hyperdb.Password: str,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
171 hyperdb.Boolean: int,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
172 hyperdb.Number: lambda x: x, # noqa: E272
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
173 hyperdb.Integer: int,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
174 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
175 }
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
176
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 def sql_open_connection(self):
2693
59ea85d47d34 add dbnamestr argument to connection_dict...
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2679
diff changeset
178 kwargs = connection_dict(self.config, 'db')
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
179 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
180 try:
2634
f47ca4541770 Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents: 2617
diff changeset
181 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
182 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
183 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
184 cursor = conn.cursor()
3216
27aba1d8fccc merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
185 cursor.execute("SET AUTOCOMMIT=0")
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
186 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
187 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
188 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
189 return (conn, cursor)
2679
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
190
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
191 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
192 # 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
193 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
194 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
195
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
196 self.conn, self.cursor = self.sql_open_connection()
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
197
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
198 try:
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
199 self.load_dbschema()
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5096
diff changeset
200 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
201 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
202 raise
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5096
diff changeset
203 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
204 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
205 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
206 self.init_dbschema()
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
207 self.sql("CREATE TABLE `schema` (`schema` TEXT) ENGINE=%s" %
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
208 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
209 self.sql('''CREATE TABLE ids (name VARCHAR(255),
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
210 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
211 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
212 self.create_version_2_tables()
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
213
3458
931dc7ed25c7 handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents: 3457
diff changeset
214 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
215 ''' 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
216 '''
931dc7ed25c7 handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents: 3457
diff changeset
217 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
218 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
219 if schema:
6002
3175bb92ca28 Cleanups for bandit
John Rouillard <rouilj@ieee.org>
parents: 5919
diff changeset
220 # bandit - schema is trusted
3175bb92ca28 Cleanups for bandit
John Rouillard <rouilj@ieee.org>
parents: 5919
diff changeset
221 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
222 else:
931dc7ed25c7 handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents: 3457
diff changeset
223 self.database_schema = {}
931dc7ed25c7 handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents: 3457
diff changeset
224
3603
f7399e2aa610 escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents: 3585
diff changeset
225 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
226 ''' 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
227 '''
f7399e2aa610 escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents: 3585
diff changeset
228 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
229 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
230 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
231
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
232 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
233 # OTK store
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 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
235 otk_value TEXT, otk_time DOUBLE)
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
236 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
237 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
238
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
239 # Sessions store
2727
93e2e5b55a3c new Interval props weren't created properly in rdbms
Richard Jones <richard@users.sourceforge.net>
parents: 2721
diff changeset
240 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
241 session_time DOUBLE, session_value TEXT)
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
242 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
243 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
244 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
245
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
246 # 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
247 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
248 _itemid VARCHAR(255), _prop VARCHAR(255), _textid INT)
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
249 ENGINE=%s''' % self.mysql_backend)
6593
e70e2789bc2c issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents: 6433
diff changeset
250 self.sql('''CREATE TABLE __words (_word VARCHAR(%s),
e70e2789bc2c issue2551189 - increase text search maxlength
John Rouillard <rouilj@ieee.org>
parents: 6433
diff changeset
251 _textid INT) ENGINE=%s''' % ((self.indexer.maxlength + 5),
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
252 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
253 self.sql('CREATE INDEX words_word_ids ON __words(_word)')
3858
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3687
diff changeset
254 self.sql('CREATE INDEX words_by_id ON __words (_textid)')
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3687
diff changeset
255 self.sql('CREATE UNIQUE INDEX __textids_by_props ON '
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3687
diff changeset
256 '__textids (_class, _itemid, _prop)')
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
257 sql = 'insert into ids (name, num) values (%s,%s)' % (self.arg,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
258 self.arg)
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2508
diff changeset
259 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
260
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
261 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
262 '''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
263 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
264 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
265 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
266 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
267 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
268
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
269 # 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
270 propnames = ['activity', 'creation', 'creator']
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
271
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
272 # 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
273 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
274 # 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
275 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
276 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
277 propnames.append(name)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
278 continue
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
279 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
280
5381
0942fe89e82e Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents: 5378
diff changeset
281 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
282 # grabe the current values
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
283 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
284 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
285 rows = self.cursor.fetchall()
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
286
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
287 # 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
288 self.drop_multilink_table_indexes(cn, name)
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
289 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
290 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
291
5381
0942fe89e82e Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents: 5378
diff changeset
292 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
293 # 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
294 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
295 sql = '''insert into %s (linkid, nodeid) values
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
296 (%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
297 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
298 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
299
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
300 # figure the column names to fetch
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
301 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
302
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
303 # 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
304 fetch.append('id')
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
305 fetch.append('__retired__')
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
306 fetchcols = ','.join(fetch)
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
307 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
308 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
309
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
310 # 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
311 olddata = []
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
312 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
313 cols = []
2617
33fffbf7ae68 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 2578
diff changeset
314 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
315 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
316 l = []
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
317 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
318 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
319 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
320 v = entry[i]
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
321
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
322 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
323 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
324 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
325 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
326 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
327 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
328 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
329 prop = properties[name]
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
330 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
331 v = date.Date(v)
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
332 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
333 v = date.Interval(v)
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
334 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
335 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
336 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
337 v = int(v)
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
338 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
339 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
340 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
341
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
342 # 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
343 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
344 if v is not None:
4060
2a68d7494bbc Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents: 4059
diff changeset
345 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
346 else:
12fb2aa3c6d2 fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
347 e = None
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
348 l.append(e)
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
349
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
350 # 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
351 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
352 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
353 cols.append('__' + name + '_int__')
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
354 if v is not None:
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
355 l.append(v.as_seconds())
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
356 else:
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
357 l.append(e)
2617
33fffbf7ae68 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 2578
diff changeset
358 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
359
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
360 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
361
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
362 # drop the old table
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
363 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
364
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
365 # 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
366 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
367
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
368 # 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
369 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
370 cols = ','.join(cols)
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
371 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
372 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
373 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
374
2244
ac4f295499a4 fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents: 2240
diff changeset
375 # 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
376 cols = ','.join('nodeid date tag action params'.split())
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
377 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
378 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
379
2244
ac4f295499a4 fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents: 2240
diff changeset
380 # 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
381 olddata = []
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
382 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
383 self.cursor.fetchall():
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
384 # nodeid = int(nodeid)
3140
2487b070043b slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
385 journaldate = date.Date(journaldate)
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
386 # 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
387 olddata.append((nodeid, journaldate, journaltag, action,
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
388 params))
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
389
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
390 # 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
391 self.drop_journal_table_indexes(cn)
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
392 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
393 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
394
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
395 # 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
396 self.create_journal_table(klass)
4060
2a68d7494bbc Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents: 4059
diff changeset
397 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
398 for nodeid, journaldate, journaltag, action, params in olddata:
3140
2487b070043b slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
399 self.save_journal(cn, cols, nodeid, dc(journaldate),
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
400 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
401
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
402 # 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
403 # 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
404 self.database_schema['tables'][cn] = klass.schema()
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
405
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
406 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
407 # 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
408 self._convert_journal_tables()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
409
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
410 # 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
411 self._convert_string_properties()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
412
6433
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
413 def fix_version_5_tables(self):
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
414 # 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
415 # 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
416 # 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
417 # 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
418 # 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
419 # 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
420
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
421 # 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
422 # 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
423 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
424 self.fix_version_4_tables()
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
425 else:
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
426 self.log_info('No changes needed.')
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
427
6599
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
428 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
429 # 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
430 c = self.cursor
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
431 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
432 self.arg)
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
433 # 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
434 # column length and maxlength.
39189dd94f2c issue2551189 - increase size of words in full text index.
John Rouillard <rouilj@ieee.org>
parents: 6593
diff changeset
435 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
436
6806
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6760
diff changeset
437 def fix_version_7_tables(self):
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6760
diff changeset
438 # 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
439 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
440 self.sql(sql)
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6760
diff changeset
441 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
442 self.sql(sql)
bdd28b244839 - issue2551223 - fix timestamp truncation in mysql and postgresql
John Rouillard <rouilj@ieee.org>
parents: 6760
diff changeset
443
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
444 def __repr__(self):
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
445 return '<myroundsql 0x%x>' % id(self)
1375
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_fetchone(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
448 return self.cursor.fetchone()
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
449
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
450 def sql_fetchall(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
451 return self.cursor.fetchall()
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
452
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
453 def sql_index_exists(self, table_name, index_name):
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
454 self.sql('show index from %s' % table_name)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
455 for index in self.cursor.fetchall():
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
456 if index[2] == index_name:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
457 return 1
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
458 return 0
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
459
2424
74474ec41050 argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents: 2413
diff changeset
460 def create_class_table(self, spec, create_sequence=1):
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
461 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
462
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
463 # add on our special columns
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
464 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
465 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
466
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
467 # create the base table
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
468 scols = ','.join(['%s %s' % x for x in cols])
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
469 sql = 'create table _%s (%s) ENGINE=%s' % (spec.classname, scols,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
470 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
471 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
472
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
473 self.create_class_table_indexes(spec)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
474 return cols, mls
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
475
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
476 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
477 ''' 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
478 '''
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
479 # create __retired__ index
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
480 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
481 spec.classname, spec.classname)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
482 self.sql(index_sql2)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
483
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
484 # create index for key property
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
485 if spec.key:
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
486 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
487 idx = spec.key + '(255)'
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
488 else:
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
489 idx = spec.key
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
490 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
491 spec.classname, spec.key,
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
492 spec.classname, idx)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
493 self.sql(index_sql3)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
494
6433
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
495 # 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
496 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
497 spec.key)
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
498
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
499 # 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
500 # 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
501
3969
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
502 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
503 # mysql requires sizes on TEXT indexes
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
504 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
505 if isinstance(prop, hyperdb.String):
3969
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(255))''' % (cn, cn, key)
3969
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
508 else:
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
509 sql = '''create unique index _%s_key_retired_idx
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
510 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
511 self.sql(sql)
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
512
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
513 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
514 # 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
515 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
516 if isinstance(prop, hyperdb.String):
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
517 sql = 'create index _%s_%s_idx on _%s(_%s(255))' % (cn, key,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
518 cn, key)
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
519 else:
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
520 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
521 self.sql(sql)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
522
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
523 def drop_class_table_indexes(self, cn, key):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
524 # drop the old table indexes first
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
525 l = ['_%s_id_idx' % cn, '_%s_retired_idx' % cn]
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
526 if key:
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
527 l.append('_%s_%s_idx' % (cn, key))
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
528
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
529 table_name = '_%s' % cn
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
530 for index_name in l:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
531 if not self.sql_index_exists(table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
532 continue
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
533 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
534 self.sql(index_sql)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
535
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
536 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
537 ''' 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
538 already-determined cols
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
539 '''
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
540 # journal table
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
541 sql = '''create table %s__journal (
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
542 nodeid integer, date datetime, tag varchar(255),
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
543 action varchar(255), params text) ENGINE=%s''' % (
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
544 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
545 self.sql(sql)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
546 self.create_journal_table_indexes(spec)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
547
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
548 def drop_journal_table_indexes(self, classname):
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
549 index_name = '%s_journ_idx' % classname
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
550 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
551 return
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
552 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
553 self.sql(index_sql)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
554
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
555 def create_multilink_table(self, spec, ml):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
556 sql = '''CREATE TABLE `%s_%s` (linkid VARCHAR(255),
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
557 nodeid VARCHAR(255)) ENGINE=%s''' % (spec.classname, ml,
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
558 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
559 self.sql(sql)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
560 self.create_multilink_table_indexes(spec, ml)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
561
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
562 def drop_multilink_table_indexes(self, classname, ml):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
563 l = [
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
564 '%s_%s_l_idx' % (classname, ml),
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
565 '%s_%s_n_idx' % (classname, ml)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
566 ]
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
567 table_name = '%s_%s' % (classname, ml)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
568 for index_name in l:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
569 if not self.sql_index_exists(table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
570 continue
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
571 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
572 self.sql(sql)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
573
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
574 def drop_class_table_key_index(self, cn, key):
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
575 table_name = '_%s' % cn
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
576 index_name = '_%s_%s_idx' % (cn, key)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
577 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
578 return
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
579 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
580 self.sql(sql)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
581
6433
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
582 # 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
583 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
584 if self.sql_index_exists(table_name, index_name):
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
585 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
586 self.sql(sql)
c1d3fbcdbfbd issue2551142 - Import of retired node ... unique constraint failure.
John Rouillard <rouilj@ieee.org>
parents: 6396
diff changeset
587
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
588 # old-skool id generation
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
589 def newid(self, classname):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
590 ''' 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
591 '''
2717
ca2ee58c8310 use row locking in MySQL newid() [SF#1034211]
Richard Jones <richard@users.sourceforge.net>
parents: 2693
diff changeset
592 # 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
593 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
594 self.sql(sql, (classname, ))
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
595 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
596
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
597 # update the counter
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
598 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
599 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
600 self.sql(sql, vals)
2098
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 # return as string
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
603 return str(newid)
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 def setid(self, classname, setid):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
606 ''' 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
607
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
608 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
609 '''
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
610 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
611 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
612 self.sql(sql, vals)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
613
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
614 def clear(self):
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
615 rdbms_common.Database.clear(self)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
616
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
617 # 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
618 for cn in self.classes.keys():
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
619 self.setid(cn, 0)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
620
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
621 def create_class(self, spec):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
622 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
623 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
624 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
625 self.sql(sql, vals)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
626
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
627 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
628 ''' 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
629 '''
4059
ef0b4396888a Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents: 3969
diff changeset
630 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
631
ff9f4ca42454 Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents: 3635
diff changeset
632 # 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
633 # 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
634 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
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 # 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
637 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
638
1cd69db95b23 fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents: 2431
diff changeset
639 # 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
640 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
641 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
642
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
643 def sql_close(self):
4059
ef0b4396888a Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents: 3969
diff changeset
644 self.log_info('close')
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
645 try:
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
646 self.conn.close()
5917
3a6114d377f4 issue2551025: try handling operational error exception in close
John Rouillard <rouilj@ieee.org>
parents: 5867
diff changeset
647 # 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
648 # 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
649 # raised for closing a closed handle.
3a6114d377f4 issue2551025: try handling operational error exception in close
John Rouillard <rouilj@ieee.org>
parents: 5867
diff changeset
650 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
651 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
652 raise
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5096
diff changeset
653 except MySQLdb.ProgrammingError as message:
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
654 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
655 raise
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
656
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
657
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
658 class MysqlClass:
7860
8b31893f5930 issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents: 7211
diff changeset
659
8b31893f5930 issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents: 7211
diff changeset
660 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
661
6396
75a53956cf13 Multilink expressions with simple "or"
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6179
diff changeset
662 # TODO: AFAIK its version dependent for MySQL
75a53956cf13 Multilink expressions with simple "or"
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6179
diff changeset
663 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
664
7860
8b31893f5930 issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents: 7211
diff changeset
665 def get_case_sensitive_equal(self):
8b31893f5930 issue2551115/issue2551282 - utf8mb4 support in roundup
John Rouillard <rouilj@ieee.org>
parents: 7211
diff changeset
666 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
667
6179
a701c9c81597 Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6152
diff changeset
668 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
669 ''' "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
670 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
671 '''
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
672 classname = proptree.parent.classname # noqa: E221
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
673 multilink_table = proptree.propclass.table_name # noqa: E221
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
674 nodeid_name = proptree.propclass.nodeid_name # noqa: E221
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
675 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
676
a701c9c81597 Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6152
diff changeset
677 w = ''
a701c9c81597 Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6152
diff changeset
678 if proptree.need_retired:
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
679 w = ' where %s.__retired__=0' % (multilink_table)
6179
a701c9c81597 Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6152
diff changeset
680 if proptree.need_child_retired:
a701c9c81597 Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6152
diff changeset
681 tn1 = multilink_table
a701c9c81597 Fix rev_multilink properties search/retrieval
Ralf Schlatterbeck <rsc@runtux.com>
parents: 6152
diff changeset
682 tn2 = '_' + proptree.classname
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
683 w = ', %s where %s.%s=%s.id and %s.__retired__=0' % (
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
684 tn2, tn1, linkid_name, tn2, tn2)
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
685 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
686 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
687 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
688
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
689 def create_inner(self, **propvalues):
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
690 try:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
691 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
692 except MySQLdb.IntegrityError as e:
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
693 self._handle_integrity_error(e, propvalues)
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
694
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
695 def set_inner(self, nodeid, **propvalues):
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
696 try:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
697 return rdbms_common.Class.set_inner(self, nodeid,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
698 **propvalues)
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5096
diff changeset
699 except MySQLdb.IntegrityError as e:
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
700 self._handle_integrity_error(e, propvalues)
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 def _handle_integrity_error(self, e, propvalues):
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
703 ''' Handle a MySQL IntegrityError.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
704
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
705 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
706 alternative exception. Otherwise, it is raised unchanged from
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
707 this function.'''
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
708
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
709 # 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
710 # is being created with the same key as an existing node.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
711 # But, there is a race condition -- we may pass those checks,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
712 # only to find out that a parallel session has created the
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
713 # 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
714 # create the node. Fortunately, MySQL gives us a unique error
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
715 # 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
716 # it appropriately.
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
717 #
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
718 # The details of the race condition are as follows, where
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
719 # "X" is a classname, and the term "thread" is meant to
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
720 # refer generically to both threads and processes:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
721 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
722 # Thread A Thread B
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
723 # -------- --------
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
724 # read table for X
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
725 # create new X object
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
726 # commit
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
727 # create new X object
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
728 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
729 # 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
730 # 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
731 # A because MySQL's default "consistent nonlocking read"
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
732 # behavior means that Thread B sees a snapshot of the database
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
733 # at the point at which its transaction began -- which was
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
734 # before Thread A created the object. However, the attempt
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
735 # to *write* to the table for X, creating a duplicate entry,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
736 # triggers an error at the point of the write.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
737 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
738 # 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
739 # object, then this bug cannot occur because creating the
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
740 # object requires getting a new ID, and newid() locks the id
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
741 # table until the transaction is committed or rolledback. So,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
742 # 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
743 # actually get its snapshot until A's transaction completes.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
744 # But, if the transaction has begun prior to calling newid,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
745 # then the snapshot has already been established.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
746 if e[0] == ER.DUP_ENTRY:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
747 key = propvalues[self.key]
5378
35ea9b1efc14 Python 3 preparation: "raise" syntax.
Joseph Myers <jsm@polyomino.org.uk>
parents: 5319
diff changeset
748 raise ValueError('node with key "%s" exists' % key)
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
749 # We don't know what this exception is; reraise it.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
750 raise
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
751
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
752
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
753 class Class(MysqlClass, rdbms_common.Class):
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
754 pass
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
755
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
756
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
757 class IssueClass(MysqlClass, rdbms_common.IssueClass):
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
758 pass
6931
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
759
83fa81f084bc flake8 fixes
John Rouillard <rouilj@ieee.org>
parents: 6806
diff changeset
760
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
761 class FileClass(MysqlClass, rdbms_common.FileClass):
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
762 pass
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
763
2679
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
764 # vim: set et sts=4 sw=4 :
Roundup Issue Tracker: http://roundup-tracker.org/