Mercurial > p > roundup > code

annotate roundup/backends/back_mysql.py @ 8071:a4cb4e75d4e9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
final changes for 2.4.0 release
author John Rouillard <rouilj@ieee.org>
date Fri, 12 Jul 2024 20:32:20 -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/