Mercurial > p > roundup > code
annotate roundup/backends/back_mysql.py @ 5096:e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
Also adds new config.ini setting called indexer to select
indexer. See ``doc/upgrading.txt`` for details. Initial patch
done by David Wolever. Patch modified (see ticket or below for
changes), docs updated and committed.
I have an outstanding issue with test/test_indexer.py. I have to
comment out all imports and tests for indexers I don't have (i.e.
mysql, postgres) otherwise no tests run.
With that change made, dbm, sqlite (rdbms), xapian and whoosh indexes
are all passing the indexer tests.
Changes summary:
1) support native back ends dbm and rdbms. (original patch only fell
through to dbm)
2) Developed whoosh stopfilter to not index stopwords or words outside
the the maxlength and minlength limits defined in index_common.py.
Required to pass the extremewords test_indexer test. Also I
removed a call to .lower on the input text as the tokenizer I chose
automatically does the lowercase.
3) Added support for max/min length to find. This was needed to pass
extremewords test.
4) Added back a call to save_index in add_text. This allowed all but
two tests to pass.
5) Fixed a call to:
results = searcher.search(query.Term("identifier", identifier))
which had an extra parameter that is an error under current whoosh.
6) Set limit=None in search call for find() otherwise it only return
10 items. This allowed it to pass manyresults test
Also due to changes in the roundup code removed the call in
indexer_whoosh to
from roundup.anypy.sets_ import set
since we use the python builtin set.
| author | John Rouillard <rouilj@ieee.org> |
|---|---|
| date | Sat, 25 Jun 2016 20:10:03 -0400 |
| parents | 04a8022ae0de |
| children | 198b6e810c67 |
| 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 |
|
1434
b953750bdc04
trackers on mysql can be initialised
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1433
diff
changeset
|
39 import os, shutil |
|
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) |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
52 if d.has_key('password'): |
|
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'] |
|
3025
7032b500b7e0
fix port number as int in mysql connection info [SF#1082530]
Richard Jones <richard@users.sourceforge.net>
parents:
2727
diff
changeset
|
55 if d.has_key('port'): |
|
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']) |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
57 return d |
|
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
58 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
59 def db_nuke(config): |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
60 """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
|
61 if db_exists(config): |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
62 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
|
63 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
|
64 try: |
|
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.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
|
66 except: |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
67 # 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
|
68 pass |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
69 else: |
|
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
70 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
|
71 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
|
72 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
|
73 # 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
|
74 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
|
75 command = 'DROP TABLE `%s`'%table[0] |
|
4085
04843a029ea1
Fix some broken logging.
Richard Jones <richard@users.sourceforge.net>
parents:
4060
diff
changeset
|
76 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
|
77 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
|
78 command = "DROP DATABASE %s"%config.RDBMS_NAME |
|
4085
04843a029ea1
Fix some broken logging.
Richard Jones <richard@users.sourceforge.net>
parents:
4060
diff
changeset
|
79 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
|
80 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
|
81 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
|
82 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
|
83 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
84 if os.path.exists(config.DATABASE): |
|
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
85 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
|
86 |
|
2075
b1704ba7be41
make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents:
2073
diff
changeset
|
87 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
|
88 """Create the database.""" |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
89 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
|
90 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
|
91 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
|
92 command = "CREATE DATABASE %s"%config.RDBMS_NAME |
|
4085
04843a029ea1
Fix some broken logging.
Richard Jones <richard@users.sourceforge.net>
parents:
4060
diff
changeset
|
93 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
|
94 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
|
95 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
|
96 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
|
97 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
98 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
|
99 """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
|
100 kwargs = connection_dict(config) |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
101 conn = MySQLdb.connect(**kwargs) |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
102 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
|
103 try: |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
104 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
|
105 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
|
106 return 0 |
|
1873
f63aa57386b0
Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
1839
diff
changeset
|
107 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
|
108 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
|
109 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
|
110 |
|
1434
b953750bdc04
trackers on mysql can be initialised
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1433
diff
changeset
|
111 |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
112 class Database(rdbms_common.Database): |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
113 """ Mysql DB backend implementation |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
114 |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
115 attributes: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
116 dbtype: |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
117 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
|
118 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
|
119 module when using native text search mode. |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
120 """ |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
121 |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
122 arg = '%s' |
|
1839
06f5b36b201b
Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents:
1836
diff
changeset
|
123 |
|
5096
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
124 dbtype = "mysql" |
|
e74c3611b138
- issue2550636, issue2550909: Added support for Whoosh indexer.
John Rouillard <rouilj@ieee.org>
parents:
5075
diff
changeset
|
125 |
|
3048
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3025
diff
changeset
|
126 # 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
|
127 implements_intersect = 0 |
|
d9b4224f955c
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3025
diff
changeset
|
128 |
|
1912
2b0ab61db194
fixes for [SF#818339]
Richard Jones <richard@users.sourceforge.net>
parents:
1911
diff
changeset
|
129 # 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
|
130 # 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
|
131 # 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
|
132 mysql_backend = 'InnoDB' |
|
d929c31a3620
clarified minimum version required for MySQL backend (4.0.16)
Richard Jones <richard@users.sourceforge.net>
parents:
1912
diff
changeset
|
133 #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
|
134 |
|
2099
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
135 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
|
136 hyperdb.String : 'TEXT', |
|
2099
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
137 hyperdb.Date : 'DATETIME', |
|
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
138 hyperdb.Link : 'INTEGER', |
|
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
139 hyperdb.Interval : 'VARCHAR(255)', |
|
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
140 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
|
141 hyperdb.Boolean : 'BOOL', |
|
2099
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
142 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
|
143 hyperdb.Integer : 'INTEGER', |
|
2099
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
144 } |
|
3837257ca9a5
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2098
diff
changeset
|
145 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
146 hyperdb_to_sql_value = { |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
147 hyperdb.String : str, |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
148 # no fractional seconds for MySQL |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
149 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
|
150 hyperdb.Link : int, |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
151 hyperdb.Interval : str, |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
152 hyperdb.Password : str, |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
153 hyperdb.Boolean : int, |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
154 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
|
155 hyperdb.Integer : int, |
|
2244
ac4f295499a4
fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents:
2240
diff
changeset
|
156 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
|
157 } |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
158 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
159 def sql_open_connection(self): |
|
2693
59ea85d47d34
add dbnamestr argument to connection_dict...
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2679
diff
changeset
|
160 kwargs = connection_dict(self.config, 'db') |
|
4059
ef0b4396888a
Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents:
3969
diff
changeset
|
161 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
|
162 try: |
|
2634
f47ca4541770
Both RDBMS backends now use the same config.ini section, [rdbms].
Richard Jones <richard@users.sourceforge.net>
parents:
2617
diff
changeset
|
163 conn = MySQLdb.connect(**kwargs) |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
164 except MySQLdb.OperationalError, message: |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
165 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
|
166 cursor = conn.cursor() |
|
3216
27aba1d8fccc
merge from maint-0-8
Richard Jones <richard@users.sourceforge.net>
parents:
3155
diff
changeset
|
167 cursor.execute("SET AUTOCOMMIT=0") |
|
4887
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4466
diff
changeset
|
168 lvl = isolation_levels [self.config.RDBMS_ISOLATION_LEVEL] |
|
05c857e5dbed
New rdbms configuration option 'isolation_level'
Ralf Schlatterbeck <rsc@runtux.com>
parents:
4466
diff
changeset
|
169 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
|
170 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
|
171 return (conn, cursor) |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
172 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
173 def 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
|
174 # 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
|
175 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
|
176 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
|
177 |
|
2082
c091cacdc505
Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents:
2077
diff
changeset
|
178 self.conn, self.cursor = self.sql_open_connection() |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
179 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
180 try: |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
181 self.load_dbschema() |
|
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
|
182 except MySQLdb.OperationalError, message: |
|
8429095241d7
mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1415
diff
changeset
|
183 if message[0] != ER.NO_DB_ERROR: |
|
8429095241d7
mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1415
diff
changeset
|
184 raise |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
185 except MySQLdb.ProgrammingError, message: |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
186 if message[0] != ER.NO_SUCH_TABLE: |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
187 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
|
188 self.init_dbschema() |
|
3929
a472391156ae
mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3858
diff
changeset
|
189 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
|
190 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
|
191 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
|
192 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
|
193 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
|
194 self.create_version_2_tables() |
|
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
195 |
|
3458
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
196 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
|
197 ''' 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
|
198 ''' |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
199 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
|
200 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
|
201 if schema: |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
202 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
|
203 else: |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
204 self.database_schema = {} |
|
931dc7ed25c7
handle "schema" being reserved word in MySQL 5+ [SF#1397569]
Richard Jones <richard@users.sourceforge.net>
parents:
3457
diff
changeset
|
205 |
|
3603
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
206 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
|
207 ''' 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
|
208 ''' |
|
f7399e2aa610
escape *all* uses of "schema" in mysql backend [SF#1472120]
Richard Jones <richard@users.sourceforge.net>
parents:
3585
diff
changeset
|
209 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
|
210 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
|
211 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
|
212 |
|
2073
261c2e6ceb1e
*** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents:
2005
diff
changeset
|
213 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
|
214 # OTK store |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
215 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
|
216 otk_value TEXT, otk_time FLOAT(20)) |
|
3929
a472391156ae
mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3858
diff
changeset
|
217 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
|
218 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
|
219 |
|
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
220 # Sessions store |
|
2727
93e2e5b55a3c
new Interval props weren't created properly in rdbms
Richard Jones <richard@users.sourceforge.net>
parents:
2721
diff
changeset
|
221 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
|
222 session_time FLOAT(20), session_value TEXT) |
|
3929
a472391156ae
mysql table creation syntax change
Justus Pendleton <jpend@users.sourceforge.net>
parents:
3858
diff
changeset
|
223 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
|
224 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
|
225 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
|
226 |
|
2093
3f6024ab2c7a
That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents:
2089
diff
changeset
|
227 # 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
|
228 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
|
229 _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
|
230 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
|
231 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
|
232 _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
|
233 self.sql('CREATE INDEX words_word_ids ON __words(_word)') |
|
3858
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3687
diff
changeset
|
234 self.sql('CREATE INDEX words_by_id ON __words (_textid)') |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3687
diff
changeset
|
235 self.sql('CREATE UNIQUE INDEX __textids_by_props ON ' |
|
bb30bbfc7cdd
Indexing fixes.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3687
diff
changeset
|
236 '__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
|
237 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
|
238 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
|
239 |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
240 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
|
241 '''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
|
242 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
|
243 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
|
244 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
|
245 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
|
246 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
|
247 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
248 # 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
|
249 propnames = ['activity', 'creation', 'creator'] |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
250 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
251 # 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
|
252 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
|
253 # 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
|
254 if s_prop.find('Multilink') == -1: |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
255 if properties.has_key(name): |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
256 propnames.append(name) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
257 continue |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
258 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
|
259 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
260 if properties.has_key(name): |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
261 # 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
|
262 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
|
263 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
|
264 rows = self.cursor.fetchall() |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
265 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
266 # 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
|
267 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
|
268 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
|
269 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
|
270 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
271 if properties.has_key(name): |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
272 # 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
|
273 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
|
274 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
|
275 (%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
|
276 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
|
277 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
|
278 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
279 # 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
|
280 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
|
281 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
282 # 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
|
283 fetch.append('id') |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
284 fetch.append('__retired__') |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
285 fetchcols = ','.join(fetch) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
286 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
|
287 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
|
288 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
289 # 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
|
290 olddata = [] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
291 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
|
292 cols = [] |
|
2617
33fffbf7ae68
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
2578
diff
changeset
|
293 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
|
294 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
|
295 l = [] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
296 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
|
297 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
|
298 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
|
299 v = entry[i] |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
300 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
301 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
|
302 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
|
303 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
|
304 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
|
305 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
|
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 prop = properties[name] |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
309 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
|
310 v = date.Date(v) |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
311 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
|
312 v = date.Interval(v) |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
313 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
|
314 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
|
315 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
|
316 v = int(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.Boolean) or |
|
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
318 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
|
319 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
|
320 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
321 # 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
|
322 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
|
323 if v is not None: |
|
4060
2a68d7494bbc
Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents:
4059
diff
changeset
|
324 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
|
325 else: |
|
12fb2aa3c6d2
fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
326 e = None |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
327 l.append(e) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
328 |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
329 # 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
|
330 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
|
331 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
|
332 cols.append('__' + name + '_int__') |
|
2217
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
333 if v is not None: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
334 l.append(v.as_seconds()) |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
335 else: |
|
98d3bf8ffb19
store Intervals as two columns (and other fixes
Richard Jones <richard@users.sourceforge.net>
parents:
2166
diff
changeset
|
336 l.append(e) |
|
2617
33fffbf7ae68
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
2578
diff
changeset
|
337 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
|
338 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
339 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
|
340 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
341 # 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
|
342 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
|
343 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
344 # 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
|
345 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
|
346 |
|
2100
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
347 # 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
|
348 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
|
349 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
|
350 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
|
351 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
|
352 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
|
353 |
|
2244
ac4f295499a4
fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents:
2240
diff
changeset
|
354 # 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
|
355 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
|
356 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
|
357 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
358 |
|
2244
ac4f295499a4
fixed journal marshalling in RDBMS backends [SF#943627]
Richard Jones <richard@users.sourceforge.net>
parents:
2240
diff
changeset
|
359 # 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
|
360 olddata = [] |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
361 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
|
362 self.cursor.fetchall(): |
|
2431
12fb2aa3c6d2
fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
363 #nodeid = int(nodeid) |
|
3140
2487b070043b
slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
364 journaldate = date.Date(journaldate) |
|
2431
12fb2aa3c6d2
fixes to mysql upgrade code
Richard Jones <richard@users.sourceforge.net>
parents:
2424
diff
changeset
|
365 #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
|
366 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
|
367 params)) |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
368 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
369 # 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
|
370 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
|
371 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
|
372 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
|
373 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
374 # 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
|
375 self.create_journal_table(klass) |
|
4060
2a68d7494bbc
Robustify SQL<->HyperDB data type conversion.
Stefan Seefeld <stefan@seefeld.name>
parents:
4059
diff
changeset
|
376 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
|
377 for nodeid, journaldate, journaltag, action, params in olddata: |
|
3140
2487b070043b
slightly better fix
Richard Jones <richard@users.sourceforge.net>
parents:
3088
diff
changeset
|
378 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
|
379 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
|
380 |
|
62ed6505cbec
MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents:
2099
diff
changeset
|
381 # 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
|
382 # 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
|
383 self.database_schema['tables'][cn] = klass.schema() |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
384 |
|
2413
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
385 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
|
386 # 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
|
387 self._convert_journal_tables() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
388 |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
389 # 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
|
390 self._convert_string_properties() |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
391 |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
392 def __repr__(self): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
393 return '<myroundsql 0x%x>'%id(self) |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
394 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
395 def sql_fetchone(self): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
396 return self.cursor.fetchone() |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
397 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
398 def sql_fetchall(self): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
399 return self.cursor.fetchall() |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
400 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
401 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
|
402 self.sql('show index from %s'%table_name) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
403 for index in self.cursor.fetchall(): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
404 if index[2] == index_name: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
405 return 1 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
406 return 0 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
407 |
|
2424
74474ec41050
argh! backwards compat
Richard Jones <richard@users.sourceforge.net>
parents:
2413
diff
changeset
|
408 def create_class_table(self, spec, create_sequence=1): |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
409 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
|
410 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
411 # add on our special columns |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
412 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
|
413 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
|
414 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
415 # create the base table |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
416 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
|
417 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
|
418 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
|
419 self.sql(sql) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
420 |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
421 self.create_class_table_indexes(spec) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
422 return cols, mls |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
423 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
424 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
|
425 ''' 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
|
426 ''' |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
427 # create __retired__ index |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
428 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
|
429 spec.classname, spec.classname) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
430 self.sql(index_sql2) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
431 |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
432 # create index for key property |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
433 if spec.key: |
|
5075
04a8022ae0de
Remove 'import *' statement from dist/backends/back_mysql.py
John Kristensen <john@jerrykan.com>
parents:
5067
diff
changeset
|
434 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
|
435 idx = spec.key + '(255)' |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
436 else: |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
437 idx = spec.key |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
438 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
|
439 spec.classname, spec.key, |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
440 spec.classname, idx) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
441 self.sql(index_sql3) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
442 |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
443 # 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
|
444 # 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
|
445 |
|
3969
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
446 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
|
447 # mysql requires sizes on TEXT indexes |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
448 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
|
449 if isinstance(prop, hyperdb.String): |
|
3969
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
450 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
|
451 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
|
452 else: |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
453 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
|
454 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
|
455 self.sql(sql) |
|
905faf52a51f
fix mysql breakage in 1.4.2
Richard Jones <richard@users.sourceforge.net>
parents:
3934
diff
changeset
|
456 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
457 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
|
458 # 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
|
459 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
|
460 if isinstance(prop, hyperdb.String): |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
461 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
|
462 else: |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
463 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
|
464 self.sql(sql) |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
465 |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
466 def drop_class_table_indexes(self, cn, key): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
467 # drop the old table indexes first |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
468 l = ['_%s_id_idx'%cn, '_%s_retired_idx'%cn] |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
469 if key: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
470 l.append('_%s_%s_idx'%(cn, key)) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
471 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
472 table_name = '_%s'%cn |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
473 for index_name in l: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
474 if not self.sql_index_exists(table_name, index_name): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
475 continue |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
476 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
|
477 self.sql(index_sql) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
478 |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
479 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
|
480 ''' 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
|
481 already-determined cols |
|
7d0bb6601809
fix some column datatypes in postgresql and mysql
Richard Jones <richard@users.sourceforge.net>
parents:
2379
diff
changeset
|
482 ''' |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
483 # journal table |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
484 cols = ','.join(['%s varchar'%x |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
485 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
|
486 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
|
487 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
|
488 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
|
489 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
|
490 self.sql(sql) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
491 self.create_journal_table_indexes(spec) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
492 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
493 def drop_journal_table_indexes(self, classname): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
494 index_name = '%s_journ_idx'%classname |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
495 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
|
496 return |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
497 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
|
498 self.sql(index_sql) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
499 |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
500 def create_multilink_table(self, spec, ml): |
|
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
501 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
|
502 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
|
503 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
|
504 self.sql(sql) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
505 self.create_multilink_table_indexes(spec, ml) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
506 |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
507 def drop_multilink_table_indexes(self, classname, ml): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
508 l = [ |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
509 '%s_%s_l_idx'%(classname, ml), |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
510 '%s_%s_n_idx'%(classname, ml) |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
511 ] |
|
2089
93f03c6714d8
A few big changes in this commit:
Richard Jones <richard@users.sourceforge.net>
parents:
2082
diff
changeset
|
512 table_name = '%s_%s'%(classname, ml) |
|
1906
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
513 for index_name in l: |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
514 if not self.sql_index_exists(table_name, index_name): |
|
f255363e6d97
PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents:
1873
diff
changeset
|
515 continue |
|
2514
091711fb2f8c
Initial logging integration: replace all debug prints with logging calls...
Richard Jones <richard@users.sourceforge.net>
parents:
2508
diff
changeset
|
516 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
|
517 self.sql(sql) |
|
1375
faf93d3fbf2f
added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff
changeset
|
518 |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
519 def drop_class_table_key_index(self, cn, key): |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
520 table_name = '_%s'%cn |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
521 index_name = '_%s_%s_idx'%(cn, key) |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
522 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
|
523 return |
|
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
524 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
|
525 self.sql(sql) |
|
2077
3e0961d6d44d
Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents:
2075
diff
changeset
|
526 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
527 # old-skool id generation |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
528 def newid(self, classname): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
529 ''' 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
|
530 ''' |
|
2717
ca2ee58c8310
use row locking in MySQL newid() [SF#1034211]
Richard Jones <richard@users.sourceforge.net>
parents:
2693
diff
changeset
|
531 # 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
|
532 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
|
533 self.sql(sql, (classname, )) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
534 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
|
535 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
536 # update the counter |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
537 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
|
538 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
|
539 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
540 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
541 # return as string |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
542 return str(newid) |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
543 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
544 def setid(self, classname, setid): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
545 ''' 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
|
546 |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
547 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
|
548 ''' |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
549 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
|
550 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
|
551 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
552 |
|
3310
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
553 def clear(self): |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
554 rdbms_common.Database.clear(self) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
555 |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
556 # 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
|
557 for cn in self.classes.keys(): |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
558 self.setid(cn, 0) |
|
3518d1ffd940
merge from maint-0-7
Richard Jones <richard@users.sourceforge.net>
parents:
3216
diff
changeset
|
559 |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
560 def create_class(self, spec): |
|
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
561 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
|
562 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
|
563 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
|
564 self.sql(sql, vals) |
|
2098
18addf2a8596
Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents:
2093
diff
changeset
|
565 |
|
3687
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3635
diff
changeset
|
566 def sql_commit(self, fail_ok=False): |
|
2456
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
567 ''' 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
|
568 ''' |
|
4059
ef0b4396888a
Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents:
3969
diff
changeset
|
569 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
|
570 |
|
ff9f4ca42454
Postgres backend allows transaction collisions to be ignored when...
Richard Jones <richard@users.sourceforge.net>
parents:
3635
diff
changeset
|
571 # 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
|
572 # 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
|
573 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
|
574 |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
575 # 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
|
576 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
|
577 |
|
1cd69db95b23
fixed some more mysql 0.6->0.7 upgrade bugs [SF#950410]
Richard Jones <richard@users.sourceforge.net>
parents:
2431
diff
changeset
|
578 # 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
|
579 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
|
580 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
|
581 |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
582 def sql_close(self): |
|
4059
ef0b4396888a
Enhance and simplify logging.
Stefan Seefeld <stefan@seefeld.name>
parents:
3969
diff
changeset
|
583 self.log_info('close') |
|
3457
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
584 try: |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
585 self.conn.close() |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
586 except MySQLdb.ProgrammingError, message: |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
587 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
|
588 raise |
|
88f0a5030404
Fix invalid date spec in db tests
Richard Jones <richard@users.sourceforge.net>
parents:
3455
diff
changeset
|
589 |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
590 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
|
591 |
|
f1fe6fd0aa61
Multilinks can be filtered by combining elements with AND, OR and NOT now.
Bernhard Reiter <Bernhard.Reiter@intevation.de>
parents:
4285
diff
changeset
|
592 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
|
593 # 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
|
594 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
|
595 |
|
3635
53987aa153d2
Transitive-property support.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3633
diff
changeset
|
596 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
|
597 ''' "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
|
598 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
|
599 ''' |
|
3635
53987aa153d2
Transitive-property support.
Ralf Schlatterbeck <schlatterbeck@users.sourceforge.net>
parents:
3633
diff
changeset
|
600 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
|
601 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
|
602 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
|
603 |
| 4285 | 604 def create_inner(self, **propvalues): |
| 605 try: | |
| 606 return rdbms_common.Class.create_inner(self, **propvalues) | |
| 607 except MySQLdb.IntegrityError, e: | |
| 608 self._handle_integrity_error(e, propvalues) | |
| 609 | |
| 610 def set_inner(self, nodeid, **propvalues): | |
| 611 try: | |
| 612 return rdbms_common.Class.set_inner(self, nodeid, | |
| 613 **propvalues) | |
| 614 except MySQLdb.IntegrityError, e: | |
| 615 self._handle_integrity_error(e, propvalues) | |
| 616 | |
| 617 def _handle_integrity_error(self, e, propvalues): | |
| 618 ''' Handle a MySQL IntegrityError. | |
| 619 | |
| 620 If the error is recognized, then it may be converted into an | |
| 621 alternative exception. Otherwise, it is raised unchanged from | |
| 622 this function.''' | |
| 623 | |
| 624 # There are checks in create_inner/set_inner to see if a node | |
| 625 # is being created with the same key as an existing node. | |
| 626 # But, there is a race condition -- we may pass those checks, | |
| 627 # only to find out that a parallel session has created the | |
| 628 # node by by the time we actually issue the SQL command to | |
| 629 # create the node. Fortunately, MySQL gives us a unique error | |
| 630 # code for this situation, so we can detect it here and handle | |
| 631 # it appropriately. | |
| 632 # | |
| 633 # The details of the race condition are as follows, where | |
| 634 # "X" is a classname, and the term "thread" is meant to | |
| 635 # refer generically to both threads and processes: | |
| 636 # | |
| 637 # Thread A Thread B | |
| 638 # -------- -------- | |
| 639 # read table for X | |
| 640 # create new X object | |
| 641 # commit | |
| 642 # create new X object | |
| 643 # | |
| 644 # In Thread B, the check in create_inner does not notice that | |
| 645 # the new X object is a duplicate of that committed in Thread | |
| 646 # A because MySQL's default "consistent nonlocking read" | |
| 647 # behavior means that Thread B sees a snapshot of the database | |
| 648 # at the point at which its transaction began -- which was | |
| 649 # before Thread A created the object. However, the attempt | |
| 650 # to *write* to the table for X, creating a duplicate entry, | |
| 651 # triggers an error at the point of the write. | |
| 652 # | |
| 653 # If both A and B's transaction begins with creating a new X | |
| 654 # object, then this bug cannot occur because creating the | |
| 655 # object requires getting a new ID, and newid() locks the id | |
| 656 # table until the transaction is committed or rolledback. So, | |
| 657 # B will block until A's commit is complete, and will not | |
| 658 # actually get its snapshot until A's transaction completes. | |
| 659 # But, if the transaction has begun prior to calling newid, | |
| 660 # then the snapshot has already been established. | |
| 661 if e[0] == ER.DUP_ENTRY: | |
| 662 key = propvalues[self.key] | |
| 663 raise ValueError, 'node with key "%s" exists' % key | |
| 664 # We don't know what this exception is; reraise it. | |
| 665 raise | |
| 666 | |
| 667 | |
|
1415
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
668 class Class(MysqlClass, rdbms_common.Class): |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
669 pass |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
670 class IssueClass(MysqlClass, rdbms_common.IssueClass): |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
671 pass |
|
6883852e9b15
mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents:
1375
diff
changeset
|
672 class FileClass(MysqlClass, rdbms_common.FileClass): |
|
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 |
|
2679
b3f0b7b9d20d
for MySQL connection, password argument is called 'passwd'.
Alexander Smishlajev <a1s@users.sourceforge.net>
parents:
2634
diff
changeset
|
675 # vim: set et sts=4 sw=4 : |
