Mercurial > p > roundup > code

annotate roundup/backends/back_mysql.py @ 7800:2d4684e4702d

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