Mercurial > p > roundup > code
annotate roundup/backends/back_mysql.py @ 5543:bc3e00a3d24b
MySQL backend fixes for Python 3.
With Python 2, text sent to and from MySQL is treated as bytes in
Python. The database may be recorded by MySQL as having some other
encoding (latin1 being the default in some MySQL versions - Roundup
does not set an encoding explicitly, unlike in back_postgresql), but
as long as MySQL's notion of the connection encoding agrees with its
notion of the database encoding, no conversions actually take place
and the bytes are stored and returned as-is.
With Python 3, text sent to and from MySQL is treated as Python
Unicode strings. When the database and connection encoding is latin1,
that means the bytes stored in the database under Python 2 are
interpreted as latin1 and converted from that to Unicode, producing
incorrect results for any non-ASCII characters; furthermore, if trying
to store new non-ASCII data in the database under Python 3, any
non-latin1 characters produce errors.
This patch arranges for both the connection and database character
sets to be UTF-8 when using Python 3, and documents a need to export
and import the database when moving from Python 2 to Python 3 with
this backend.
| author | Joseph Myers <jsm@polyomino.org.uk> |
|---|---|
| date | Sun, 16 Sep 2018 16:19:20 +0000 |
| parents | 0db3779702d9 |
| children | ee2e8f8d6648 |
| 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) |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
68 except: |
|
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: |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
206 self.database_schema = eval(schema[0]) |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
207 else: |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
208 self.database_schema = {} |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
209 |
|
3603
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
210 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
|
211 ''' 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
|
212 ''' |
|
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
213 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
|
214 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
|
215 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
|
216 |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
217 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
|
218 # OTK store |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
219 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
|
220 otk_value TEXT, otk_time FLOAT(20)) |
|
3929
a472391156ae
mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3858
diff
changeset
|
221 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
|
222 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
|
223 |
|
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
224 # Sessions store |
|
2727
93e2e5b55a3c
new Interval props weren't created properly in rdbms
Richard Jones <richard@users.sourceforge.net>
parents:
2721
diff
changeset
|
225 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
|
226 session_time FLOAT(20), session_value TEXT) |
|
3929
a472391156ae
mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3858
diff
changeset
|
227 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
|
228 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
|
229 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
|
230 |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
231 # 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
|
232 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
|
233 _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
|
234 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
|
235 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
|
236 _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
|
237 self.sql('CREATE INDEX words_word_ids ON __words(_word)') |
|
3858
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3687
diff
changeset
|
238 self.sql('CREATE INDEX words_by_id ON __words (_textid)') |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3687
diff
changeset
|
239 self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3687
diff
changeset
|
240 '__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
|
241 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
|
242 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
|
243 |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
244 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
|
245 '''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
|
246 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
|
247 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
|
248 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
|
249 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
|
250 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
|
251 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
252 # 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
|
253 propnames = ['activity', 'creation', 'creator'] |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
254 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
255 # 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
|
256 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
|
257 # 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
|
258 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
|
259 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
|
260 propnames.append(name) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
261 continue |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
262 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
|
263 |
|
5381
0942fe89e82e
Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents:
5378
diff
changeset
|
264 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
|
265 # 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
|
266 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
|
267 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
|
268 rows = self.cursor.fetchall() |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
269 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
270 # 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
|
271 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
|
272 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
|
273 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
|
274 |
|
5381
0942fe89e82e
Python 3 preparation: change "x.has_key(y)" to "y in x".
Joseph Myers <jsm@polyomino.org.uk>
parents:
5378
diff
changeset
|
275 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
|
276 # 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
|
277 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
|
278 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
|
279 (%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
|
280 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
|
281 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
|
282 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
283 # 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
|
284 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
|
285 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
286 # 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
|
287 fetch.append('id') |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
288 fetch.append('__retired__') |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
289 fetchcols = ','.join(fetch) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
290 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
|
291 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
|
292 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
293 # 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
|
294 olddata = [] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
295 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
|
296 cols = [] |
|
2617
33fffbf7ae68
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
2578
diff
changeset
|
297 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
|
298 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
|
299 l = [] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
300 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
|
301 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
|
302 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
|
303 v = entry[i] |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
304 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
305 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
|
306 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
|
307 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
|
308 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
|
309 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
|
310 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
|
311 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
|
312 prop = properties[name] |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
313 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
|
314 v = date.Date(v) |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
315 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
|
316 v = date.Interval(v) |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
317 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
|
318 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
|
319 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
|
320 v = int(v) |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
321 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
|
322 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
|
323 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
|
324 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
325 # 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
|
326 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
|
327 if v is not None: |
|
4060
2a68d7494bbc
Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents:
4059
diff
changeset
|
328 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
|
329 else: |
|
12fb2aa3c6d2
fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
330 e = None |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
331 l.append(e) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
332 |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
333 # 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
|
334 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
|
335 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
|
336 cols.append('__' + name + '_int__') |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
337 if v is not None: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
338 l.append(v.as_seconds()) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
339 else: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
340 l.append(e) |
|
2617
33fffbf7ae68
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
2578
diff
changeset
|
341 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
|
342 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
343 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
|
344 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
345 # 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
|
346 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
|
347 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
348 # 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
|
349 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
|
350 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
351 # 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
|
352 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
|
353 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
|
354 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
|
355 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
|
356 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
|
357 |
|
2244
ac4f295499a4
fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents:
2240
diff
changeset
|
358 # 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
|
359 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
|
360 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
|
361 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
362 |
|
2244
ac4f295499a4
fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents:
2240
diff
changeset
|
363 # 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
|
364 olddata = [] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
365 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
|
366 self.cursor.fetchall(): |
|
2431
12fb2aa3c6d2
fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
367 #nodeid = int(nodeid) |
|
3140
2487b070043b
slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
368 journaldate = date.Date(journaldate) |
|
2431
12fb2aa3c6d2
fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
369 #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
|
370 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
|
371 params)) |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
372 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
373 # 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
|
374 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
|
375 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
|
376 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
|
377 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
378 # 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
|
379 self.create_journal_table(klass) |
|
4060
2a68d7494bbc
Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents:
4059
diff
changeset
|
380 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
|
381 for nodeid, journaldate, journaltag, action, params in olddata: |
|
3140
2487b070043b
slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
382 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
|
383 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
|
384 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
385 # 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
|
386 # 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
|
387 self.database_schema['tables'][cn] = klass.schema() |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
388 |
|
2413
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
389 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
|
390 # 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
|
391 self._convert_journal_tables() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
392 |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
393 # 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
|
394 self._convert_string_properties() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
395 |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
396 def __repr__(self): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
397 return '<myroundsql 0x%x>'%id(self) |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
398 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
399 def sql_fetchone(self): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
400 return self.cursor.fetchone() |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
401 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
402 def sql_fetchall(self): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
403 return self.cursor.fetchall() |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
404 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
405 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
|
406 self.sql('show index from %s'%table_name) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
407 for index in self.cursor.fetchall(): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
408 if index[2] == index_name: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
409 return 1 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
410 return 0 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
411 |
|
2424
74474ec41050
argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents:
2413
diff
changeset
|
412 def create_class_table(self, spec, create_sequence=1): |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
413 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
|
414 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
415 # add on our special columns |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
416 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
|
417 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
|
418 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
419 # create the base table |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
420 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
|
421 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
|
422 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
|
423 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
424 |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
425 self.create_class_table_indexes(spec) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
426 return cols, mls |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
427 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
428 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
|
429 ''' 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
|
430 ''' |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
431 # create __retired__ index |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
432 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
|
433 spec.classname, spec.classname) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
434 self.sql(index_sql2) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
435 |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
436 # create index for key property |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
437 if spec.key: |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
438 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
|
439 idx = spec.key + '(255)' |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
440 else: |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
441 idx = spec.key |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
442 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
|
443 spec.classname, spec.key, |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
444 spec.classname, idx) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
445 self.sql(index_sql3) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
446 |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
447 # 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
|
448 # 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
|
449 |
|
3969
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
450 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
|
451 # mysql requires sizes on TEXT indexes |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
452 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
|
453 if isinstance(prop, hyperdb.String): |
|
3969
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
454 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
|
455 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
|
456 else: |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
457 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
|
458 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
|
459 self.sql(sql) |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
460 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
461 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
|
462 # 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
|
463 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
|
464 if isinstance(prop, hyperdb.String): |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
465 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
|
466 else: |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
467 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
|
468 self.sql(sql) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
469 |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
470 def drop_class_table_indexes(self, cn, key): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
471 # drop the old table indexes first |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
472 l = ['_%s_id_idx'%cn, '_%s_retired_idx'%cn] |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
473 if key: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
474 l.append('_%s_%s_idx'%(cn, key)) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
475 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
476 table_name = '_%s'%cn |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
477 for index_name in l: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
478 if not self.sql_index_exists(table_name, index_name): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
479 continue |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
480 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
|
481 self.sql(index_sql) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
482 |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
483 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
|
484 ''' 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
|
485 already-determined cols |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
486 ''' |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
487 # journal table |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
488 cols = ','.join(['%s varchar'%x |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
489 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
|
490 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
|
491 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
|
492 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
|
493 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
|
494 self.sql(sql) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
495 self.create_journal_table_indexes(spec) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
496 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
497 def drop_journal_table_indexes(self, classname): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
498 index_name = '%s_journ_idx'%classname |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
499 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
|
500 return |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
501 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
|
502 self.sql(index_sql) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
503 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
504 def create_multilink_table(self, spec, ml): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
505 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
|
506 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
|
507 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
|
508 self.sql(sql) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
509 self.create_multilink_table_indexes(spec, ml) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
510 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
511 def drop_multilink_table_indexes(self, classname, ml): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
512 l = [ |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
513 '%s_%s_l_idx'%(classname, ml), |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
514 '%s_%s_n_idx'%(classname, ml) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
515 ] |
|
2089
93f03c6714d8
A few big changes in this commit:
Richard Jones <richard@users.sourceforge.net>
parents:
2082
diff
changeset
|
516 table_name = '%s_%s'%(classname, ml) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
517 for index_name in l: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
518 if not self.sql_index_exists(table_name, index_name): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
519 continue |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
520 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
|
521 self.sql(sql) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
522 |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
523 def drop_class_table_key_index(self, cn, key): |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
524 table_name = '_%s'%cn |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
525 index_name = '_%s_%s_idx'%(cn, key) |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
526 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
|
527 return |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
528 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
|
529 self.sql(sql) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
530 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
531 # old-skool id generation |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
532 def newid(self, classname): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
533 ''' 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
|
534 ''' |
|
2717
ca2ee58c8310
use row locking in MySQL newid() [SF#1034211]
Richard Jones <richard@users.sourceforge.net>
parents:
2693
diff
changeset
|
535 # 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
|
536 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
|
537 self.sql(sql, (classname, )) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
538 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
|
539 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
540 # update the counter |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
541 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
|
542 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
|
543 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
544 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
545 # return as string |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
546 return str(newid) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
547 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
548 def setid(self, classname, setid): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
549 ''' 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
|
550 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
551 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
|
552 ''' |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
553 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
|
554 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
|
555 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
556 |
|
3310
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
557 def clear(self): |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
558 rdbms_common.Database.clear(self) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
559 |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
560 # 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
|
561 for cn in self.classes.keys(): |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
562 self.setid(cn, 0) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
563 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
564 def create_class(self, spec): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
565 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
|
566 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
|
567 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
|
568 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
569 |
|
5319
62de601bdf6f
Fix commits although a Reject exception is raised
Ralf Schlatterbeck <rsc@runtux.com>
parents:
5248
diff
changeset
|
570 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
|
571 ''' 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
|
572 ''' |
|
4059
ef0b4396888a
Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents:
3969
diff
changeset
|
573 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
|
574 |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3635
diff
changeset
|
575 # 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
|
576 # 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
|
577 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
|
578 |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
579 # 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
|
580 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
|
581 |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
582 # 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
|
583 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
|
584 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
|
585 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
586 def sql_close(self): |
|
4059
ef0b4396888a
Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents:
3969
diff
changeset
|
587 self.log_info('close') |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
588 try: |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
589 self.conn.close() |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5096
diff
changeset
|
590 except MySQLdb.ProgrammingError as message: |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
591 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
|
592 raise |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
593 |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
594 class MysqlClass: |
|
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
|
595 |
|
f1fe6fd0aa61
Multilinks can be filtered by combining elements with AND, OR and NOT now.
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4285
diff
changeset
|
596 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
|
597 # 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
|
598 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
|
599 |
|
3635
53987aa153d2
Transitive-property support.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3633
diff
changeset
|
600 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
|
601 ''' "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
|
602 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
|
603 ''' |
|
3635
53987aa153d2
Transitive-property support.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3633
diff
changeset
|
604 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
|
605 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
|
606 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
|
607 |
| 4285 | 608 def create_inner(self, **propvalues): |
| 609 try: | |
| 610 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
|
611 except MySQLdb.IntegrityError as e: |
| 4285 | 612 self._handle_integrity_error(e, propvalues) |
| 613 | |
| 614 def set_inner(self, nodeid, **propvalues): | |
| 615 try: | |
| 616 return rdbms_common.Class.set_inner(self, nodeid, | |
| 617 **propvalues) | |
|
5248
198b6e810c67
Use Python-3-compatible 'as' syntax for except statements
Eric S. Raymond <esr@thyrsus.com>
parents:
5096
diff
changeset
|
618 except MySQLdb.IntegrityError as e: |
| 4285 | 619 self._handle_integrity_error(e, propvalues) |
| 620 | |
| 621 def _handle_integrity_error(self, e, propvalues): | |
| 622 ''' Handle a MySQL IntegrityError. | |
| 623 | |
| 624 If the error is recognized, then it may be converted into an | |
| 625 alternative exception. Otherwise, it is raised unchanged from | |
| 626 this function.''' | |
| 627 | |
| 628 # There are checks in create_inner/set_inner to see if a node | |
| 629 # is being created with the same key as an existing node. | |
| 630 # But, there is a race condition -- we may pass those checks, | |
| 631 # only to find out that a parallel session has created the | |
| 632 # node by by the time we actually issue the SQL command to | |
| 633 # create the node. Fortunately, MySQL gives us a unique error | |
| 634 # code for this situation, so we can detect it here and handle | |
| 635 # it appropriately. | |
| 636 # | |
| 637 # The details of the race condition are as follows, where | |
| 638 # "X" is a classname, and the term "thread" is meant to | |
| 639 # refer generically to both threads and processes: | |
| 640 # | |
| 641 # Thread A Thread B | |
| 642 # -------- -------- | |
| 643 # read table for X | |
| 644 # create new X object | |
| 645 # commit | |
| 646 # create new X object | |
| 647 # | |
| 648 # In Thread B, the check in create_inner does not notice that | |
| 649 # the new X object is a duplicate of that committed in Thread | |
| 650 # A because MySQL's default "consistent nonlocking read" | |
| 651 # behavior means that Thread B sees a snapshot of the database | |
| 652 # at the point at which its transaction began -- which was | |
| 653 # before Thread A created the object. However, the attempt | |
| 654 # to *write* to the table for X, creating a duplicate entry, | |
| 655 # triggers an error at the point of the write. | |
| 656 # | |
| 657 # If both A and B's transaction begins with creating a new X | |
| 658 # object, then this bug cannot occur because creating the | |
| 659 # object requires getting a new ID, and newid() locks the id | |
| 660 # table until the transaction is committed or rolledback. So, | |
| 661 # B will block until A's commit is complete, and will not | |
| 662 # actually get its snapshot until A's transaction completes. | |
| 663 # But, if the transaction has begun prior to calling newid, | |
| 664 # then the snapshot has already been established. | |
| 665 if e[0] == ER.DUP_ENTRY: | |
| 666 key = propvalues[self.key] | |
|
5378
35ea9b1efc14
Python 3 preparation: "raise" syntax.
Joseph Myers <jsm@polyomino.org.uk>
parents:
5319
diff
changeset
|
667 raise ValueError('node with key "%s" exists' % key) |
| 4285 | 668 # We don't know what this exception is; reraise it. |
| 669 raise | |
| 670 | |
| 671 | |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
672 class Class(MysqlClass, rdbms_common.Class): |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
673 pass |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
674 class IssueClass(MysqlClass, rdbms_common.IssueClass): |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
675 pass |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
676 class FileClass(MysqlClass, rdbms_common.FileClass): |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
677 pass |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
678 |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
679 # vim: set et sts=4 sw=4 : |
