Mercurial > p > roundup > code

annotate roundup/backends/back_mysql.py @ 6002:3175bb92ca28

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Cleanups for bandit Set bandit to ignore use of md5. Treat schema from database as trusted to eval. We wrote it based on the schema.py file. Replace some bare except: with a proper exception. mode 775 for index directory is correct. Allows indices to be written by email and web interfaces that run as different users but can have the roundup group in common.
author John Rouillard <rouilj@ieee.org>
date Thu, 26 Dec 2019 21:44:50 -0500
parents d789c28db503
children 8497bf3f23a1
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
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
36 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
37 from roundup.backends import rdbms_common
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
38 import MySQLdb
5543
bc3e00a3d24b MySQL backend fixes for Python 3.
Joseph Myers <jsm@polyomino.org.uk>
parents: 5507
diff changeset
39 import os, shutil, sys
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
40 from MySQLdb.constants import ER
3155
57b60bda9473 Python 2.3 minimum version - bye bye roundup.rlog, you had a short life.
Richard Jones <richard@users.sourceforge.net>
parents: 3147
diff changeset
41 import logging
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
42
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4466
diff changeset
43 isolation_levels = \
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4466
diff changeset
44 { 'read uncommitted': 'READ UNCOMMITTED'
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4466
diff changeset
45 , 'read committed': 'READ COMMITTED'
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4466
diff changeset
46 , 'repeatable read': 'REPEATABLE READ'
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4466
diff changeset
47 , 'serializable': 'SERIALIZABLE'
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4466
diff changeset
48 }
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4466
diff changeset
49
2693
59ea85d47d34 add dbnamestr argument to connection_dict...
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2679
diff changeset
50 def connection_dict(config, dbnamestr=None):
59ea85d47d34 add dbnamestr argument to connection_dict...
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2679
diff changeset
51 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
52 if 'password' in d:
2679
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
53 d['passwd'] = d['password']
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
54 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
55 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
56 d['port'] = int(d['port'])
5543
bc3e00a3d24b MySQL backend fixes for Python 3.
Joseph Myers <jsm@polyomino.org.uk>
parents: 5507
diff changeset
57 if sys.version_info[0] > 2:
bc3e00a3d24b MySQL backend fixes for Python 3.
Joseph Myers <jsm@polyomino.org.uk>
parents: 5507
diff changeset
58 d['charset'] = 'utf8'
2679
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
59 return d
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
60
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
61 def db_nuke(config):
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
62 """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
63 if db_exists(config):
2679
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
64 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
65 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
66 try:
2634
f47ca4541770 Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents: 2617
diff changeset
67 conn.select_db(config.RDBMS_NAME)
6002
3175bb92ca28 Cleanups for bandit
John Rouillard <rouilj@ieee.org>
parents: 5919
diff changeset
68 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
69 # 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
70 pass
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
71 else:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
72 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
73 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
74 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
75 # 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
76 for table in tables:
3603
f7399e2aa610 escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents: 3585
diff changeset
77 command = 'DROP TABLE `%s`'%table[0]
4085
04843a029ea1 Fix some broken logging.
Richard Jones <richard@users.sourceforge.net>
parents: 4060
diff changeset
78 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
79 cursor.execute(command)
2634
f47ca4541770 Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents: 2617
diff changeset
80 command = "DROP DATABASE %s"%config.RDBMS_NAME
4085
04843a029ea1 Fix some broken logging.
Richard Jones <richard@users.sourceforge.net>
parents: 4060
diff changeset
81 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
82 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
83 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
84 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
85
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
86 if os.path.exists(config.DATABASE):
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
87 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
88
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
89 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
90 """Create the database."""
2679
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
91 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
92 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
93 cursor = conn.cursor()
2634
f47ca4541770 Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents: 2617
diff changeset
94 command = "CREATE DATABASE %s"%config.RDBMS_NAME
5543
bc3e00a3d24b MySQL backend fixes for Python 3.
Joseph Myers <jsm@polyomino.org.uk>
parents: 5507
diff changeset
95 if sys.version_info[0] > 2:
bc3e00a3d24b MySQL backend fixes for Python 3.
Joseph Myers <jsm@polyomino.org.uk>
parents: 5507
diff changeset
96 command += ' CHARACTER SET utf8'
4085
04843a029ea1 Fix some broken logging.
Richard Jones <richard@users.sourceforge.net>
parents: 4060
diff changeset
97 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
98 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
99 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
100 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
101
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
102 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
103 """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
104 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
105 conn = MySQLdb.connect(**kwargs)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
106 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
107 try:
2634
f47ca4541770 Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents: 2617
diff changeset
108 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
109 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
110 return 0
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
111 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
112 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
113 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
114
1434
b953750bdc04 trackers on mysql can be initialised
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1433
diff changeset
115
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
116 class Database(rdbms_common.Database):
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 5075
diff changeset
117 """ Mysql DB backend implementation
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 5075
diff changeset
118
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 5075
diff changeset
119 attributes:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 5075
diff changeset
120 dbtype:
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 5075
diff changeset
121 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
122 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
123 module when using native text search mode.
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 5075
diff changeset
124 """
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 5075
diff changeset
125
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
126 arg = '%s'
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
127
5096
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 5075
diff changeset
128 dbtype = "mysql"
e74c3611b138 - issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents: 5075
diff changeset
129
3048
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 3025
diff changeset
130 # 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
131 implements_intersect = 0
d9b4224f955c merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 3025
diff changeset
132
1912
2b0ab61db194 fixes for [SF#818339]
Richard Jones <richard@users.sourceforge.net>
parents: 1911
diff changeset
133 # 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
134 # 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
135 # 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
136 mysql_backend = 'InnoDB'
d929c31a3620 clarified minimum version required for MySQL backend (4.0.16)
Richard Jones <richard@users.sourceforge.net>
parents: 1912
diff changeset
137 #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
138
2099
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
139 hyperdb_to_sql_datatypes = {
3421
0d9406d14186 MySQL now creates String columns using the TEXT column type
Richard Jones <richard@users.sourceforge.net>
parents: 3383
diff changeset
140 hyperdb.String : 'TEXT',
2099
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
141 hyperdb.Date : 'DATETIME',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
142 hyperdb.Link : 'INTEGER',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
143 hyperdb.Interval : 'VARCHAR(255)',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
144 hyperdb.Password : 'VARCHAR(255)',
2166
cd42c3c7173a MySQL and Postgresql use BOOL/BOOLEAN for Boolean types
Richard Jones <richard@users.sourceforge.net>
parents: 2100
diff changeset
145 hyperdb.Boolean : 'BOOL',
2099
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
146 hyperdb.Number : 'REAL',
5067
e424987d294a Add support for an integer type to join the existing number type.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
147 hyperdb.Integer : 'INTEGER',
2099
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
148 }
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
149
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
150 hyperdb_to_sql_value = {
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
151 hyperdb.String : str,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
152 # no fractional seconds for MySQL
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
153 hyperdb.Date : lambda x: x.formal(sep=' '),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
154 hyperdb.Link : int,
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
155 hyperdb.Interval : str,
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
156 hyperdb.Password : str,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
157 hyperdb.Boolean : int,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
158 hyperdb.Number : lambda x: x,
5067
e424987d294a Add support for an integer type to join the existing number type.
John Rouillard <rouilj@ieee.org>
parents: 4887
diff changeset
159 hyperdb.Integer : int,
2244
ac4f295499a4 fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents: 2240
diff changeset
160 hyperdb.Multilink : lambda x: x, # used in journal marshalling
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
161 }
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
162
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
163 def sql_open_connection(self):
2693
59ea85d47d34 add dbnamestr argument to connection_dict...
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2679
diff changeset
164 kwargs = connection_dict(self.config, 'db')
4059
ef0b4396888a Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents: 3969
diff changeset
165 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
166 try:
2634
f47ca4541770 Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents: 2617
diff changeset
167 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
168 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
169 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
170 cursor = conn.cursor()
3216
27aba1d8fccc merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents: 3155
diff changeset
171 cursor.execute("SET AUTOCOMMIT=0")
4887
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4466
diff changeset
172 lvl = isolation_levels [self.config.RDBMS_ISOLATION_LEVEL]
05c857e5dbed New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents: 4466
diff changeset
173 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
174 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
175 return (conn, cursor)
2679
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
176
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
177 def 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
178 # 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
179 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
180 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
181
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
182 self.conn, self.cursor = self.sql_open_connection()
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
183
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
184 try:
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
185 self.load_dbschema()
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5096
diff changeset
186 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
187 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
188 raise
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5096
diff changeset
189 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
190 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
191 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
192 self.init_dbschema()
3929
a472391156ae mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3858
diff changeset
193 self.sql("CREATE TABLE `schema` (`schema` TEXT) ENGINE=%s"%
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
194 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
195 self.sql('''CREATE TABLE ids (name VARCHAR(255),
3929
a472391156ae mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3858
diff changeset
196 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
197 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
198 self.create_version_2_tables()
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
199
3458
931dc7ed25c7 handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents: 3457
diff changeset
200 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
201 ''' 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
202 '''
931dc7ed25c7 handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents: 3457
diff changeset
203 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
204 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
205 if schema:
6002
3175bb92ca28 Cleanups for bandit
John Rouillard <rouilj@ieee.org>
parents: 5919
diff changeset
206 # bandit - schema is trusted
3175bb92ca28 Cleanups for bandit
John Rouillard <rouilj@ieee.org>
parents: 5919
diff changeset
207 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
208 else:
931dc7ed25c7 handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents: 3457
diff changeset
209 self.database_schema = {}
931dc7ed25c7 handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents: 3457
diff changeset
210
3603
f7399e2aa610 escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents: 3585
diff changeset
211 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
212 ''' 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
213 '''
f7399e2aa610 escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents: 3585
diff changeset
214 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
215 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
216 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
217
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
218 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
219 # OTK store
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2508
diff changeset
220 self.sql('''CREATE TABLE otks (otk_key VARCHAR(255),
2721
1cd01cf106e1 extend OTK and session table value cols to TEXT [SF#1031271]
Richard Jones <richard@users.sourceforge.net>
parents: 2720
diff changeset
221 otk_value TEXT, otk_time FLOAT(20))
3929
a472391156ae mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3858
diff changeset
222 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
223 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
224
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
225 # Sessions store
2727
93e2e5b55a3c new Interval props weren't created properly in rdbms
Richard Jones <richard@users.sourceforge.net>
parents: 2721
diff changeset
226 self.sql('''CREATE TABLE sessions (session_key VARCHAR(255),
93e2e5b55a3c new Interval props weren't created properly in rdbms
Richard Jones <richard@users.sourceforge.net>
parents: 2721
diff changeset
227 session_time FLOAT(20), session_value TEXT)
3929
a472391156ae mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3858
diff changeset
228 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
229 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
230 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
231
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
232 # 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
233 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
234 _itemid VARCHAR(255), _prop VARCHAR(255), _textid INT)
3929
a472391156ae mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3858
diff changeset
235 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
236 self.sql('''CREATE TABLE __words (_word VARCHAR(30),
3929
a472391156ae mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3858
diff changeset
237 _textid INT) 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
238 self.sql('CREATE INDEX words_word_ids ON __words(_word)')
3858
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3687
diff changeset
239 self.sql('CREATE INDEX words_by_id ON __words (_textid)')
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3687
diff changeset
240 self.sql('CREATE UNIQUE INDEX __textids_by_props ON '
bb30bbfc7cdd Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3687
diff changeset
241 '__textids (_class, _itemid, _prop)')
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
242 sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg)
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(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
244
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
245 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
246 '''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
247 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
248 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
249 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
250 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
251 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
252
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
253 # 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
254 propnames = ['activity', 'creation', 'creator']
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
255
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
256 # 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
257 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
258 # 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
259 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
260 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
261 propnames.append(name)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
262 continue
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
263 tn = '%s_%s'%(cn, name)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
264
5381
0942fe89e82e Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents: 5378
diff changeset
265 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
266 # grabe the current values
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
267 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
268 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
269 rows = self.cursor.fetchall()
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
270
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
271 # 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
272 self.drop_multilink_table_indexes(cn, name)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
273 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
274 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
275
5381
0942fe89e82e Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents: 5378
diff changeset
276 if name in properties:
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
277 # 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
278 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
279 sql = '''insert into %s (linkid, nodeid) values
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
280 (%s, %s)'''%(tn, self.arg, self.arg)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
281 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
282 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
283
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
284 # figure the column names to fetch
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
285 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
286
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
287 # 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
288 fetch.append('id')
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
289 fetch.append('__retired__')
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
290 fetchcols = ','.join(fetch)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
291 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
292 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
293
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
294 # 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
295 olddata = []
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
296 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
297 cols = []
2617
33fffbf7ae68 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 2578
diff changeset
298 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
299 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
300 l = []
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
301 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
302 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
303 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
304 v = entry[i]
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
305
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
306 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
307 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
308 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
309 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
310 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
311 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
312 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
313 prop = properties[name]
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
314 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
315 v = date.Date(v)
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
316 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
317 v = date.Interval(v)
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
318 elif isinstance(prop, hyperdb.Password) 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
319 v = password.Password(encrypted=v)
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
320 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
321 v = int(v)
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
322 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
323 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
324 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
325
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
326 # 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
327 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
328 if v is not None:
4060
2a68d7494bbc Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents: 4059
diff changeset
329 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
330 else:
12fb2aa3c6d2 fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
331 e = None
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
332 l.append(e)
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
333
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
334 # 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
335 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
336 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
337 cols.append('__' + name + '_int__')
2217
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
338 if v is not None:
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
339 l.append(v.as_seconds())
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
340 else:
98d3bf8ffb19 store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents: 2166
diff changeset
341 l.append(e)
2617
33fffbf7ae68 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 2578
diff changeset
342 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
343
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
344 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
345
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
346 # drop the old table
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2508
diff changeset
347 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
348
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
349 # 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
350 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
351
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
352 # 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
353 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
354 cols = ','.join(cols)
1cd69db95b23 fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents: 2431
diff changeset
355 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
356 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
357 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
358
2244
ac4f295499a4 fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents: 2240
diff changeset
359 # 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
360 cols = ','.join('nodeid date tag action params'.split())
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
361 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
362 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
363
2244
ac4f295499a4 fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents: 2240
diff changeset
364 # 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
365 olddata = []
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
366 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
367 self.cursor.fetchall():
2431
12fb2aa3c6d2 fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
368 #nodeid = int(nodeid)
3140
2487b070043b slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
369 journaldate = date.Date(journaldate)
2431
12fb2aa3c6d2 fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents: 2424
diff changeset
370 #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
371 olddata.append((nodeid, journaldate, journaltag, action,
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
372 params))
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
373
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
374 # 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
375 self.drop_journal_table_indexes(cn)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
376 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
377 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
378
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
379 # 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
380 self.create_journal_table(klass)
4060
2a68d7494bbc Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents: 4059
diff changeset
381 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
382 for nodeid, journaldate, journaltag, action, params in olddata:
3140
2487b070043b slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents: 3088
diff changeset
383 self.save_journal(cn, cols, nodeid, dc(journaldate),
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
384 journaltag, action, params)
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 # 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
387 # 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
388 self.database_schema['tables'][cn] = klass.schema()
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
389
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
390 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
391 # 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
392 self._convert_journal_tables()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
393
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
394 # 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
395 self._convert_string_properties()
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
396
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
397 def __repr__(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
398 return '<myroundsql 0x%x>'%id(self)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
399
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
400 def sql_fetchone(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
401 return self.cursor.fetchone()
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
402
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
403 def sql_fetchall(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
404 return self.cursor.fetchall()
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
405
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
406 def sql_index_exists(self, table_name, index_name):
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2508
diff changeset
407 self.sql('show index from %s'%table_name)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
408 for index in self.cursor.fetchall():
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
409 if index[2] == index_name:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
410 return 1
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
411 return 0
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
412
2424
74474ec41050 argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents: 2413
diff changeset
413 def create_class_table(self, spec, create_sequence=1):
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
414 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
415
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
416 # add on our special columns
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
417 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
418 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
419
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
420 # create the base table
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
421 scols = ','.join(['%s %s'%x for x in cols])
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
422 sql = 'create table _%s (%s) ENGINE=%s'%(spec.classname, scols,
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
423 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
424 self.sql(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
425
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
426 self.create_class_table_indexes(spec)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
427 return cols, mls
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
428
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
429 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
430 ''' 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
431 '''
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
432 # create __retired__ index
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
433 index_sql2 = 'create index _%s_retired_idx on _%s(__retired__)'%(
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
434 spec.classname, spec.classname)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
435 self.sql(index_sql2)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
436
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
437 # create index for key property
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
438 if spec.key:
5075
04a8022ae0de Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents: 5067
diff changeset
439 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
440 idx = spec.key + '(255)'
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
441 else:
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
442 idx = spec.key
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
443 index_sql3 = 'create index _%s_%s_idx on _%s(_%s)'%(
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
444 spec.classname, spec.key,
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
445 spec.classname, idx)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
446 self.sql(index_sql3)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
447
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
448 # 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
449 # 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
450
3969
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
451 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
452 # mysql requires sizes on TEXT indexes
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
453 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
454 if isinstance(prop, hyperdb.String):
3969
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
455 sql = '''create unique index _%s_key_retired_idx
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
456 on _%s(__retired__, _%s(255))'''%(cn, cn, key)
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
457 else:
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
458 sql = '''create unique index _%s_key_retired_idx
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
459 on _%s(__retired__, _%s)'''%(cn, cn, key)
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
460 self.sql(sql)
905faf52a51f fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents: 3934
diff changeset
461
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
462 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
463 # 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
464 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
465 if isinstance(prop, hyperdb.String):
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
466 sql = 'create index _%s_%s_idx on _%s(_%s(255))'%(cn, key, cn, key)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
467 else:
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
468 sql = 'create index _%s_%s_idx on _%s(_%s)'%(cn, key, cn, key)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
469 self.sql(sql)
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
470
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
471 def drop_class_table_indexes(self, cn, key):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
472 # drop the old table indexes first
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
473 l = ['_%s_id_idx'%cn, '_%s_retired_idx'%cn]
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
474 if key:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
475 l.append('_%s_%s_idx'%(cn, key))
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
476
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
477 table_name = '_%s'%cn
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
478 for index_name in l:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
479 if not self.sql_index_exists(table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
480 continue
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
481 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
482 self.sql(index_sql)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
483
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
484 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
485 ''' 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
486 already-determined cols
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
487 '''
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
488 # journal table
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
489 cols = ','.join(['%s varchar'%x
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
490 for x in 'nodeid date tag action params'.split()])
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
491 sql = '''create table %s__journal (
2413
7d0bb6601809 fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents: 2379
diff changeset
492 nodeid integer, date datetime, tag varchar(255),
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
493 action varchar(255), params text) ENGINE=%s'''%(
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
494 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
495 self.sql(sql)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
496 self.create_journal_table_indexes(spec)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
497
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
498 def drop_journal_table_indexes(self, classname):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
499 index_name = '%s_journ_idx'%classname
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
500 if not self.sql_index_exists('%s__journal'%classname, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
501 return
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
502 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
503 self.sql(index_sql)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
504
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
505 def create_multilink_table(self, spec, ml):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
506 sql = '''CREATE TABLE `%s_%s` (linkid VARCHAR(255),
3929
a472391156ae mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents: 3858
diff changeset
507 nodeid VARCHAR(255)) ENGINE=%s'''%(spec.classname, ml,
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
508 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
509 self.sql(sql)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
510 self.create_multilink_table_indexes(spec, ml)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
511
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
512 def drop_multilink_table_indexes(self, classname, ml):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
513 l = [
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
514 '%s_%s_l_idx'%(classname, ml),
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
515 '%s_%s_n_idx'%(classname, ml)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
516 ]
2089
93f03c6714d8 A few big changes in this commit:
Richard Jones <richard@users.sourceforge.net>
parents: 2082
diff changeset
517 table_name = '%s_%s'%(classname, ml)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
518 for index_name in l:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
519 if not self.sql_index_exists(table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
520 continue
2514
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2508
diff changeset
521 sql = 'drop index %s on %s'%(index_name, table_name)
091711fb2f8c Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents: 2508
diff changeset
522 self.sql(sql)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
523
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
524 def drop_class_table_key_index(self, cn, key):
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
525 table_name = '_%s'%cn
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
526 index_name = '_%s_%s_idx'%(cn, key)
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
527 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
528 return
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
529 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(sql)
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
531
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
532 # old-skool id generation
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
533 def newid(self, classname):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
534 ''' 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
535 '''
2717
ca2ee58c8310 use row locking in MySQL newid() [SF#1034211]
Richard Jones <richard@users.sourceforge.net>
parents: 2693
diff changeset
536 # get the next ID - "FOR UPDATE" will lock the row for us
ca2ee58c8310 use row locking in MySQL newid() [SF#1034211]
Richard Jones <richard@users.sourceforge.net>
parents: 2693
diff changeset
537 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
538 self.sql(sql, (classname, ))
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
539 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
540
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
541 # update the counter
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
542 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
543 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
544 self.sql(sql, vals)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
545
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
546 # return as string
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
547 return str(newid)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
548
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
549 def setid(self, classname, setid):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
550 ''' 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
551
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
552 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
553 '''
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
554 sql = 'update ids set num=%s where name=%s'%(self.arg, self.arg)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
555 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
556 self.sql(sql, vals)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
557
3310
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
558 def clear(self):
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
559 rdbms_common.Database.clear(self)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
560
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
561 # 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
562 for cn in self.classes.keys():
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
563 self.setid(cn, 0)
3518d1ffd940 merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents: 3216
diff changeset
564
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
565 def create_class(self, spec):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
566 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
567 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
568 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
569 self.sql(sql, vals)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
570
5319
62de601bdf6f Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5248
diff changeset
571 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
572 ''' 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
573 '''
4059
ef0b4396888a Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents: 3969
diff changeset
574 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
575
ff9f4ca42454 Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents: 3635
diff changeset
576 # 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
577 # 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
578 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
579
1cd69db95b23 fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents: 2431
diff changeset
580 # 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
581 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
582
1cd69db95b23 fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents: 2431
diff changeset
583 # 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
584 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
585 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
586
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
587 def sql_close(self):
4059
ef0b4396888a Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents: 3969
diff changeset
588 self.log_info('close')
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
589 try:
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
590 self.conn.close()
5917
3a6114d377f4 issue2551025: try handling operational error exception in close
John Rouillard <rouilj@ieee.org>
parents: 5867
diff changeset
591 # 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
592 # 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
593 # raised for closing a closed handle.
3a6114d377f4 issue2551025: try handling operational error exception in close
John Rouillard <rouilj@ieee.org>
parents: 5867
diff changeset
594 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
595 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
596 raise
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5096
diff changeset
597 except MySQLdb.ProgrammingError as message:
3457
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
598 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
599 raise
88f0a5030404 Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents: 3455
diff changeset
600
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
601 class MysqlClass:
5867
ee2e8f8d6648 Implement exact string search
Ralf Schlatterbeck <rsc@runtux.com>
parents: 5543
diff changeset
602 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
603
f1fe6fd0aa61 Multilinks can be filtered by combining elements with AND, OR and NOT now.
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4285
diff changeset
604 def supports_subselects(self):
f1fe6fd0aa61 Multilinks can be filtered by combining elements with AND, OR and NOT now.
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4285
diff changeset
605 # TODO: AFAIK its version dependent for MySQL
f1fe6fd0aa61 Multilinks can be filtered by combining elements with AND, OR and NOT now.
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4285
diff changeset
606 return False
f1fe6fd0aa61 Multilinks can be filtered by combining elements with AND, OR and NOT now.
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents: 4285
diff changeset
607
3635
53987aa153d2 Transitive-property support.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3633
diff changeset
608 def _subselect(self, classname, multilink_table):
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
609 ''' "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
610 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
611 '''
3635
53987aa153d2 Transitive-property support.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3633
diff changeset
612 self.db.sql('select nodeid from %s'%multilink_table)
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
613 s = ','.join([x[0] for x in self.db.sql_fetchall()])
3635
53987aa153d2 Transitive-property support.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents: 3633
diff changeset
614 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
615
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
616 def create_inner(self, **propvalues):
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
617 try:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
618 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
619 except MySQLdb.IntegrityError as e:
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
620 self._handle_integrity_error(e, propvalues)
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
621
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
622 def set_inner(self, nodeid, **propvalues):
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
623 try:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
624 return rdbms_common.Class.set_inner(self, nodeid,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
625 **propvalues)
5248
198b6e810c67 Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents: 5096
diff changeset
626 except MySQLdb.IntegrityError as e:
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
627 self._handle_integrity_error(e, propvalues)
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
628
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
629 def _handle_integrity_error(self, e, propvalues):
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
630 ''' Handle a MySQL IntegrityError.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
631
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
632 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
633 alternative exception. Otherwise, it is raised unchanged from
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
634 this function.'''
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
635
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
636 # 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
637 # is being created with the same key as an existing node.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
638 # But, there is a race condition -- we may pass those checks,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
639 # only to find out that a parallel session has created the
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
640 # 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
641 # create the node. Fortunately, MySQL gives us a unique error
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
642 # 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
643 # it appropriately.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
644 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
645 # The details of the race condition are as follows, where
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
646 # "X" is a classname, and the term "thread" is meant to
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
647 # refer generically to both threads and processes:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
648 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
649 # Thread A Thread B
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
650 # -------- --------
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
651 # read table for X
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
652 # create new X object
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
653 # commit
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
654 # create new X object
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
655 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
656 # 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
657 # 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
658 # A because MySQL's default "consistent nonlocking read"
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
659 # behavior means that Thread B sees a snapshot of the database
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
660 # at the point at which its transaction began -- which was
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
661 # before Thread A created the object. However, the attempt
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
662 # to *write* to the table for X, creating a duplicate entry,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
663 # triggers an error at the point of the write.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
664 #
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
665 # 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
666 # object, then this bug cannot occur because creating the
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
667 # object requires getting a new ID, and newid() locks the id
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
668 # table until the transaction is committed or rolledback. So,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
669 # 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
670 # actually get its snapshot until A's transaction completes.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
671 # But, if the transaction has begun prior to calling newid,
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
672 # then the snapshot has already been established.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
673 if e[0] == ER.DUP_ENTRY:
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
674 key = propvalues[self.key]
5378
35ea9b1efc14 Python 3 preparation: "raise" syntax.
Joseph Myers <jsm@polyomino.org.uk>
parents: 5319
diff changeset
675 raise ValueError('node with key "%s" exists' % key)
4285
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
676 # We don't know what this exception is; reraise it.
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
677 raise
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
678
783053bd466c Fix race condition.
Stefan Seefeld <stefan@seefeld.name>
parents: 4085
diff changeset
679
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
680 class Class(MysqlClass, rdbms_common.Class):
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
681 pass
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
682 class IssueClass(MysqlClass, rdbms_common.IssueClass):
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
683 pass
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
684 class FileClass(MysqlClass, rdbms_common.FileClass):
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
685 pass
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
686
2679
b3f0b7b9d20d for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents: 2634
diff changeset
687 # vim: set et sts=4 sw=4 :
Roundup Issue Tracker: http://roundup-tracker.org/