Mercurial > p > roundup > code

annotate roundup/backends/back_mysql.py @ 2163:791c66a3b738

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fixed CSV export and CGI actions returning results
author Richard Jones <richard@users.sourceforge.net>
date Tue, 30 Mar 2004 06:43:08 +0000
parents 62ed6505cbec
children cd42c3c7173a
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
1 #
1433
8429095241d7 mysql tests will not be run if there is no chance of passing.
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1415
diff changeset
2 # Copyright (c) 2003 Martynas Sklyzmantas, Andrey Lebedev <andrey@micro.lt>
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
3 #
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
4 # This module is free software, and you may redistribute it and/or modify
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
5 # under the same terms as Python, so long as this copyright message and
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
6 # disclaimer are retained in their original form.
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
7 #
2005
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
8
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
9 '''This module defines a backend implementation for MySQL.
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
10
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
11
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
12 How to implement AUTO_INCREMENT:
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
13
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
14 mysql> create table foo (num integer auto_increment primary key, name
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
15 varchar(255)) AUTO_INCREMENT=1 type=InnoDB;
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
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
36 from roundup.backends.rdbms_common import *
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
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
41
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
42
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
43 def db_nuke(config):
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
44 """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
45 if db_exists(config):
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
46 conn = MySQLdb.connect(config.MYSQL_DBHOST, config.MYSQL_DBUSER,
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
47 config.MYSQL_DBPASSWORD)
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
48 try:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
49 conn.select_db(config.MYSQL_DBNAME)
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
50 except:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
51 # 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
52 pass
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
53 else:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
54 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
55 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
56 tables = cursor.fetchall()
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
57 for table in tables:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
58 if __debug__:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
59 print >>hyperdb.DEBUG, 'DROP TABLE %s'%table[0]
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
60 cursor.execute("DROP TABLE %s"%table[0])
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 __debug__:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
62 print >>hyperdb.DEBUG, "DROP DATABASE %s"%config.MYSQL_DBNAME
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
63 cursor.execute("DROP DATABASE %s"%config.MYSQL_DBNAME)
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
64 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
65 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
66
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
67 if os.path.exists(config.DATABASE):
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
68 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
69
2075
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
70 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
71 """Create the database."""
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
72 conn = MySQLdb.connect(config.MYSQL_DBHOST, config.MYSQL_DBUSER,
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
73 config.MYSQL_DBPASSWORD)
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
74 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
75 if __debug__:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
76 print >>hyperdb.DEBUG, "CREATE DATABASE %s"%config.MYSQL_DBNAME
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
77 cursor.execute("CREATE DATABASE %s"%config.MYSQL_DBNAME)
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
78 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
79 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
80
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
81 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
82 """Check if database already exists."""
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
83 conn = MySQLdb.connect(config.MYSQL_DBHOST, config.MYSQL_DBUSER,
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
84 config.MYSQL_DBPASSWORD)
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
85 # tables = None
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
86 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
87 try:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
88 conn.select_db(config.MYSQL_DBNAME)
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
89 # 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
90 # 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
91 # tables = cursor.fetchall()
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
92 # if __debug__:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
93 # print >>hyperdb.DEBUG, "tables %s"%(tables,)
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
94 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
95 if __debug__:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
96 print >>hyperdb.DEBUG, "no database '%s'"%config.MYSQL_DBNAME
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
97 return 0
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents: 1839
diff changeset
98 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
99 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
100 if __debug__:
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
101 print >>hyperdb.DEBUG, "database '%s' exists"%config.MYSQL_DBNAME
b1704ba7be41 make mysql / postgresql work again. beginnings of otk/session store in rdbmses
Richard Jones <richard@users.sourceforge.net>
parents: 2073
diff changeset
102 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
103
1434
b953750bdc04 trackers on mysql can be initialised
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1433
diff changeset
104
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
105 class Database(Database):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
106 arg = '%s'
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
107
1912
2b0ab61db194 fixes for [SF#818339]
Richard Jones <richard@users.sourceforge.net>
parents: 1911
diff changeset
108 # 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
109 # 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
110 # 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
111 mysql_backend = 'InnoDB'
d929c31a3620 clarified minimum version required for MySQL backend (4.0.16)
Richard Jones <richard@users.sourceforge.net>
parents: 1912
diff changeset
112 #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
113
2099
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
114 hyperdb_to_sql_datatypes = {
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
115 hyperdb.String : 'VARCHAR(255)',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
116 hyperdb.Date : 'DATETIME',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
117 hyperdb.Link : 'INTEGER',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
118 hyperdb.Interval : 'VARCHAR(255)',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
119 hyperdb.Password : 'VARCHAR(255)',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
120 hyperdb.Boolean : 'INTEGER',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
121 hyperdb.Number : 'REAL',
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
122 }
3837257ca9a5 *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2098
diff changeset
123
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
124 hyperdb_to_sql_value = {
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
125 hyperdb.String : str,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
126 # no fractional seconds for MySQL
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
127 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
128 hyperdb.Link : int,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
129 hyperdb.Interval : lambda x: x.serialise(),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
130 hyperdb.Password : str,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
131 hyperdb.Boolean : int,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
132 hyperdb.Number : lambda x: x,
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
133 }
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
134
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
135 def sql_open_connection(self):
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
136 db = getattr(self.config, 'MYSQL_DATABASE')
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
137 try:
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
138 conn = MySQLdb.connect(*db)
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
139 except MySQLdb.OperationalError, message:
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
140 raise DatabaseError, message
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
141 cursor = conn.cursor()
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
142 cursor.execute("SET AUTOCOMMIT=0")
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
143 cursor.execute("BEGIN")
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
144 return (conn, cursor)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
145
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
146 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
147 # 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
148 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
149 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
150
2082
c091cacdc505 Finished implementation of session and one-time-key stores for RDBMS backends.
Richard Jones <richard@users.sourceforge.net>
parents: 2077
diff changeset
151 self.conn, self.cursor = self.sql_open_connection()
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
152
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
153 try:
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
154 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
155 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
156 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
157 raise
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
158 except MySQLdb.ProgrammingError, message:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
159 if message[0] != ER.NO_SUCH_TABLE:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
160 raise 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
161 self.init_dbschema()
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
162 self.sql("CREATE TABLE schema (schema TEXT) TYPE=%s"%
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
163 self.mysql_backend)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
164 self.cursor.execute('''CREATE TABLE ids (name VARCHAR(255),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
165 num INTEGER) TYPE=%s'''%self.mysql_backend)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
166 self.cursor.execute('create index ids_name_idx on ids(name)')
2073
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
167 self.create_version_2_tables()
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
168
261c2e6ceb1e *** empty log message ***
Richard Jones <richard@users.sourceforge.net>
parents: 2005
diff changeset
169 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
170 # OTK store
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
171 self.cursor.execute('''CREATE TABLE otks (otk_key VARCHAR(255),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
172 otk_value VARCHAR(255), otk_time FLOAT(20))
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
173 TYPE=%s'''%self.mysql_backend)
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 self.cursor.execute('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
175
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
176 # Sessions store
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
177 self.cursor.execute('''CREATE TABLE sessions (
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
178 session_key VARCHAR(255), session_time FLOAT(20),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
179 session_value VARCHAR(255)) TYPE=%s'''%self.mysql_backend)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
180 self.cursor.execute('''CREATE INDEX sessions_key_idx ON
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
181 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
182
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
183 # full-text indexing store
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
184 self.cursor.execute('''CREATE TABLE __textids (_class VARCHAR(255),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
185 _itemid VARCHAR(255), _prop VARCHAR(255), _textid INT)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
186 TYPE=%s'''%self.mysql_backend)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
187 self.cursor.execute('''CREATE TABLE __words (_word VARCHAR(30),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
188 _textid INT) TYPE=%s'''%self.mysql_backend)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
189 self.cursor.execute('CREATE INDEX words_word_ids ON __words(_word)')
2093
3f6024ab2c7a That's the last of the RDBMS migration steps done! Yay!
Richard Jones <richard@users.sourceforge.net>
parents: 2089
diff changeset
190 sql = 'insert into ids (name, num) values (%s,%s)'%(self.arg, self.arg)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
191 self.cursor.execute(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
192
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
193 def add_actor_column(self):
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
194 '''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
195 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
196 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
197 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
198 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
199 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
200
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
201 execute = self.cursor.execute
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
202
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
203 # 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
204 propnames = ['activity', 'creation', 'creator']
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
205
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
206 # 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
207 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
208 # 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
209 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
210 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
211 propnames.append(name)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
212 continue
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
213 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
214
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
215 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
216 # 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
217 sql = 'select linkid, nodeid from %s'%tn
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
218 if __debug__:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
219 print >>hyperdb.DEBUG, 'migration', (self, sql)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
220 execute(sql)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
221 rows = self.cursor.fetchall()
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
222
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
223 # 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
224 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
225 sql = 'drop table %s'%tn
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
226 if __debug__:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
227 print >>hyperdb.DEBUG, 'migration', (self, sql)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
228 execute(sql)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
229
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
230 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
231 # 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
232 self.create_multilink_table(klass, name)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
233 sql = '''insert into %s (linkid, nodeid) values
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
234 (%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
235 for linkid, nodeid in rows:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
236 execute(sql, (int(linkid), int(nodeid)))
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
237
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
238 # 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
239 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
240
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
241 # 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
242 fetch.append('id')
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
243 fetch.append('__retired__')
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
244 fetchcols = ','.join(fetch)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
245 sql = 'select %s from _%s'%(fetchcols, cn)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
246 if __debug__:
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
247 print >>hyperdb.DEBUG, 'migration', (self, sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
248 self.cursor.execute(sql)
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
249
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
250 # 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
251 olddata = []
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
252 propnames = propnames + ['id', '__retired__']
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
253 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
254 l = []
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
255 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
256 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
257 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
258 v = entry[i]
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
259
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
260 if name in ('id', '__retired__'):
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
261 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
262 continue
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
263 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
264 if isinstance(prop, Date) and v is not None:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
265 v = date.Date(v)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
266 elif isinstance(prop, Interval) and v is not None:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
267 v = date.Interval(v)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
268 elif isinstance(prop, Password) and v is not None:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
269 v = password.Password(encrypted=v)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
270 elif (isinstance(prop, Boolean) or
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
271 isinstance(prop, Number)) and v is not None:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
272 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
273
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
274 # 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
275 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
276 if v is not None:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
277 v = self.hyperdb_to_sql_value[prop.__class__](v)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
278 l.append(v)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
279
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
280 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
281
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
282 # drop the old table
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
283 execute('drop table _%s'%cn)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
284
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
285 # 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
286 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
287
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
288 # do the insert of the old data
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
289 args = ','.join([self.arg for x in fetch])
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
290 sql = 'insert into _%s (%s) values (%s)'%(cn, fetchcols, args)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
291 if __debug__:
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
292 print >>hyperdb.DEBUG, 'migration', (self, sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
293 for entry in olddata:
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
294 if __debug__:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
295 print >>hyperdb.DEBUG, '... data', entry
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
296 execute(sql, tuple(entry))
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
297
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
298 # now load up the old journal data
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
299 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
300 sql = 'select %s from %s__journal'%(cols, cn)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
301 if __debug__:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
302 print >>hyperdb.DEBUG, 'migration', (self, sql)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
303 execute(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
304
2100
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
305 olddata = []
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
306 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
307 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
308 nodeid = int(nodeid)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
309 journaldate = date.Date(journaldate)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
310 params = eval(params)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
311 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
312 params))
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
313
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
314 # 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
315 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
316 sql = 'drop table %s__journal'%cn
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
317 if __debug__:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
318 print >>hyperdb.DEBUG, 'migration', (self, sql)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
319 execute(sql)
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 # 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
322 self.create_journal_table(klass)
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
323 for nodeid, journaldate, journaltag, action, params in olddata:
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
324 self.save_journal(cn, cols, nodeid, journaldate,
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
325 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
326
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
327 # make sure the normal schema update code doesn't try to
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
328 # change things
62ed6505cbec MySQL migration of old backend database to new, typed database complete.
Richard Jones <richard@users.sourceforge.net>
parents: 2099
diff changeset
329 self.database_schema['tables'][cn] = klass.schema()
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
330
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
331 def __repr__(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
332 return '<myroundsql 0x%x>'%id(self)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
333
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
334 def sql_fetchone(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
335 return self.cursor.fetchone()
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
336
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
337 def sql_fetchall(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
338 return self.cursor.fetchall()
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
339
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
340 def sql_index_exists(self, table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
341 self.cursor.execute('show index from %s'%table_name)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
342 for index in self.cursor.fetchall():
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
343 if index[2] == index_name:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
344 return 1
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
345 return 0
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
346
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
347 def save_dbschema(self, schema):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
348 s = repr(self.database_schema)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
349 self.sql('INSERT INTO schema VALUES (%s)', (s,))
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
350
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
351 def create_class_table(self, spec):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
352 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
353
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
354 # add on our special columns
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
355 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
356 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
357
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
358 # create the base table
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
359 scols = ','.join(['%s %s'%x for x in cols])
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
360 sql = 'create table _%s (%s) type=%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
361 self.mysql_backend)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
362 if __debug__:
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
363 print >>hyperdb.DEBUG, 'create_class', (self, sql)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
364 self.cursor.execute(sql)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
365
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
366 self.create_class_table_indexes(spec)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
367 return cols, mls
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
368
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
369 def drop_class_table_indexes(self, cn, key):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
370 # drop the old table indexes first
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
371 l = ['_%s_id_idx'%cn, '_%s_retired_idx'%cn]
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
372 if key:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
373 l.append('_%s_%s_idx'%(cn, key))
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
374
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
375 table_name = '_%s'%cn
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
376 for index_name in l:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
377 if not self.sql_index_exists(table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
378 continue
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
379 index_sql = 'drop index %s on %s'%(index_name, table_name)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
380 if __debug__:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
381 print >>hyperdb.DEBUG, 'drop_index', (self, index_sql)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
382 self.cursor.execute(index_sql)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
383
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
384 def create_journal_table(self, spec):
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
385 # journal table
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
386 cols = ','.join(['%s varchar'%x
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
387 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
388 sql = '''create table %s__journal (
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
389 nodeid integer, date timestamp, tag varchar(255),
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
390 action varchar(255), params varchar(255)) type=%s'''%(
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
391 spec.classname, self.mysql_backend)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
392 if __debug__:
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
393 print >>hyperdb.DEBUG, 'create_journal_table', (self, sql)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
394 self.cursor.execute(sql)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
395 self.create_journal_table_indexes(spec)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
396
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
397 def drop_journal_table_indexes(self, classname):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
398 index_name = '%s_journ_idx'%classname
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
399 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
400 return
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
401 index_sql = 'drop index %s on %s__journal'%(index_name, classname)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
402 if __debug__:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
403 print >>hyperdb.DEBUG, 'drop_index', (self, index_sql)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
404 self.cursor.execute(index_sql)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
405
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
406 def create_multilink_table(self, spec, ml):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
407 sql = '''CREATE TABLE `%s_%s` (linkid VARCHAR(255),
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
408 nodeid VARCHAR(255)) TYPE=%s'''%(spec.classname, ml,
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
409 self.mysql_backend)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
410 if __debug__:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
411 print >>hyperdb.DEBUG, 'create_class', (self, sql)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
412 self.cursor.execute(sql)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
413 self.create_multilink_table_indexes(spec, ml)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
414
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
415 def drop_multilink_table_indexes(self, classname, ml):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
416 l = [
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
417 '%s_%s_l_idx'%(classname, ml),
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
418 '%s_%s_n_idx'%(classname, ml)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
419 ]
2089
93f03c6714d8 A few big changes in this commit:
Richard Jones <richard@users.sourceforge.net>
parents: 2082
diff changeset
420 table_name = '%s_%s'%(classname, ml)
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
421 for index_name in l:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
422 if not self.sql_index_exists(table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
423 continue
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
424 index_sql = 'drop index %s on %s'%(index_name, table_name)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
425 if __debug__:
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
426 print >>hyperdb.DEBUG, 'drop_index', (self, index_sql)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
427 self.cursor.execute(index_sql)
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
428
2077
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
429 def drop_class_table_key_index(self, cn, key):
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
430 table_name = '_%s'%cn
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
431 index_name = '_%s_%s_idx'%(cn, key)
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
432 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
433 return
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
434 sql = 'drop index %s on %s'%(index_name, table_name)
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
435 if __debug__:
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
436 print >>hyperdb.DEBUG, 'drop_index', (self, sql)
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
437 self.cursor.execute(sql)
3e0961d6d44d Added the "actor" property.
Richard Jones <richard@users.sourceforge.net>
parents: 2075
diff changeset
438
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
439 # old-skool id generation
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
440 def newid(self, classname):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
441 ''' 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
442 '''
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
443 # get the next ID
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
444 sql = 'select num from ids where name=%s'%self.arg
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
445 if __debug__:
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
446 print >>hyperdb.DEBUG, 'newid', (self, sql, classname)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
447 self.cursor.execute(sql, (classname, ))
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
448 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
449
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
450 # update the counter
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
451 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
452 vals = (int(newid)+1, classname)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
453 if __debug__:
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
454 print >>hyperdb.DEBUG, 'newid', (self, sql, vals)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
455 self.cursor.execute(sql, vals)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
456
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
457 # return as string
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
458 return str(newid)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
459
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
460 def setid(self, classname, setid):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
461 ''' 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
462
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
463 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
464 '''
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
465 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
466 vals = (int(setid)+1, classname)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
467 if __debug__:
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
468 print >>hyperdb.DEBUG, 'setid', (self, sql, vals)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
469 self.cursor.execute(sql, vals)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
470
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
471 def create_class(self, spec):
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
472 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
473 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
474 vals = (spec.classname, 1)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
475 if __debug__:
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
476 print >>hyperdb.DEBUG, 'create_class', (self, sql, vals)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
477 self.cursor.execute(sql, vals)
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
478
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
479 class MysqlClass:
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
480 # we're overriding this method for ONE missing bit of functionality.
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
481 # look for "I can't believe it's not a toy RDBMS" below
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
482 def filter(self, search_matches, filterspec, sort=(None,None),
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
483 group=(None,None)):
2005
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
484 '''Return a list of the ids of the active nodes in this class that
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
485 match the 'filter' spec, sorted by the group spec and then the
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
486 sort spec
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
487
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
488 "filterspec" is {propname: value(s)}
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
489
2005
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
490 "sort" and "group" are (dir, prop) where dir is '+', '-' or None
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
491 and prop is a prop name or None
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
492
2005
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
493 "search_matches" is {nodeid: marker}
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
494
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
495 The filter must match all properties specificed - but if the
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
496 property value to match is a list, any one of the values in the
fc52d57c6c3e documentation cleanup
Richard Jones <richard@users.sourceforge.net>
parents: 1988
diff changeset
497 list may match for that property to match.
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
498 '''
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
499 # just don't bother if the full-text search matched diddly
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
500 if search_matches == {}:
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
501 return []
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
502
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
503 cn = self.classname
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
504
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
505 timezone = self.db.getUserTimezone()
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
506
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
507 # figure the WHERE clause from the filterspec
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
508 props = self.getprops()
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
509 frum = ['_'+cn]
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
510 where = []
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
511 args = []
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
512 a = self.db.arg
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
513 for k, v in filterspec.items():
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
514 propclass = props[k]
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
515 # now do other where clause stuff
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
516 if isinstance(propclass, Multilink):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
517 tn = '%s_%s'%(cn, k)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
518 if v in ('-1', ['-1']):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
519 # only match rows that have count(linkid)=0 in the
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
520 # corresponding multilink table)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
521
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
522 # "I can't believe it's not a toy RDBMS"
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
523 # see, even toy RDBMSes like gadfly and sqlite can do
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
524 # sub-selects...
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
525 self.db.sql('select nodeid from %s'%tn)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
526 s = ','.join([x[0] for x in self.db.sql_fetchall()])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
527
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
528 where.append('id not in (%s)'%s)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
529 elif isinstance(v, type([])):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
530 frum.append(tn)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
531 s = ','.join([a for x in v])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
532 where.append('id=%s.nodeid and %s.linkid in (%s)'%(tn,tn,s))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
533 args = args + v
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
534 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
535 frum.append(tn)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
536 where.append('id=%s.nodeid and %s.linkid=%s'%(tn, tn, a))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
537 args.append(v)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
538 elif k == 'id':
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
539 if isinstance(v, type([])):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
540 s = ','.join([a for x in v])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
541 where.append('%s in (%s)'%(k, s))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
542 args = args + v
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
543 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
544 where.append('%s=%s'%(k, a))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
545 args.append(v)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
546 elif isinstance(propclass, String):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
547 if not isinstance(v, type([])):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
548 v = [v]
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
549
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
550 # Quote the bits in the string that need it and then embed
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
551 # in a "substring" search. Note - need to quote the '%' so
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
552 # they make it through the python layer happily
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
553 v = ['%%'+self.db.sql_stringquote(s)+'%%' for s in v]
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
554
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
555 # now add to the where clause
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
556 where.append(' or '.join(["_%s LIKE '%s'"%(k, s) for s in v]))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
557 # note: args are embedded in the query string now
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
558 elif isinstance(propclass, Link):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
559 if isinstance(v, type([])):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
560 if '-1' in v:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
561 v = v[:]
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
562 v.remove('-1')
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
563 xtra = ' or _%s is NULL'%k
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
564 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
565 xtra = ''
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
566 if v:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
567 s = ','.join([a for x in v])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
568 where.append('(_%s in (%s)%s)'%(k, s, xtra))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
569 args = args + v
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
570 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
571 where.append('_%s is NULL'%k)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
572 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
573 if v == '-1':
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
574 v = None
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
575 where.append('_%s is NULL'%k)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
576 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
577 where.append('_%s=%s'%(k, a))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
578 args.append(v)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
579 elif isinstance(propclass, Date):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
580 if isinstance(v, type([])):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
581 s = ','.join([a for x in v])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
582 where.append('_%s in (%s)'%(k, s))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
583 args = args + [date.Date(x).serialise() for x in v]
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
584 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
585 try:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
586 # Try to filter on range of dates
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
587 date_rng = Range(v, date.Date, offset=timezone)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
588 if (date_rng.from_value):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
589 where.append('_%s >= %s'%(k, a))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
590 args.append(date_rng.from_value.serialise())
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
591 if (date_rng.to_value):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
592 where.append('_%s <= %s'%(k, a))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
593 args.append(date_rng.to_value.serialise())
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
594 except ValueError:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
595 # If range creation fails - ignore that search parameter
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
596 pass
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
597 elif isinstance(propclass, Interval):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
598 if isinstance(v, type([])):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
599 s = ','.join([a for x in v])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
600 where.append('_%s in (%s)'%(k, s))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
601 args = args + [date.Interval(x).serialise() for x in v]
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
602 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
603 try:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
604 # Try to filter on range of intervals
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
605 date_rng = Range(v, date.Interval)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
606 if (date_rng.from_value):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
607 where.append('_%s >= %s'%(k, a))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
608 args.append(date_rng.from_value.serialise())
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
609 if (date_rng.to_value):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
610 where.append('_%s <= %s'%(k, a))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
611 args.append(date_rng.to_value.serialise())
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
612 except ValueError:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
613 # If range creation fails - ignore that search parameter
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
614 pass
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
615 #where.append('_%s=%s'%(k, a))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
616 #args.append(date.Interval(v).serialise())
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
617 else:
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
618 if isinstance(v, type([])):
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
619 s = ','.join([a for x in v])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
620 where.append('_%s in (%s)'%(k, s))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
621 args = args + v
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
622 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
623 where.append('_%s=%s'%(k, a))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
624 args.append(v)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
625
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
626 # don't match retired nodes
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
627 where.append('__retired__ <> 1')
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
628
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
629 # add results of full text search
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
630 if search_matches is not None:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
631 v = search_matches.keys()
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
632 s = ','.join([a for x in v])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
633 where.append('id in (%s)'%s)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
634 args = args + v
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
635
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
636 # "grouping" is just the first-order sorting in the SQL fetch
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
637 # can modify it...)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
638 orderby = []
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
639 ordercols = []
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
640 if group[0] is not None and group[1] is not None:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
641 if group[0] != '-':
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
642 orderby.append('_'+group[1])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
643 ordercols.append('_'+group[1])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
644 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
645 orderby.append('_'+group[1]+' desc')
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
646 ordercols.append('_'+group[1])
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
647
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
648 # now add in the sorting
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
649 group = ''
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
650 if sort[0] is not None and sort[1] is not None:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
651 direction, colname = sort
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
652 if direction != '-':
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
653 if colname == 'id':
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
654 orderby.append(colname)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
655 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
656 orderby.append('_'+colname)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
657 ordercols.append('_'+colname)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
658 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
659 if colname == 'id':
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
660 orderby.append(colname+' desc')
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
661 ordercols.append(colname)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
662 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
663 orderby.append('_'+colname+' desc')
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
664 ordercols.append('_'+colname)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
665
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
666 # construct the SQL
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
667 frum = ','.join(frum)
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
668 if where:
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
669 where = ' where ' + (' and '.join(where))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
670 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
671 where = ''
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
672 cols = ['id']
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
673 if orderby:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
674 cols = cols + ordercols
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
675 order = ' order by %s'%(','.join(orderby))
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
676 else:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
677 order = ''
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
678 cols = ','.join(cols)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
679 sql = 'select %s from %s %s%s%s'%(cols, frum, where, group, order)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
680 args = tuple(args)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
681 if __debug__:
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
682 print >>hyperdb.DEBUG, 'filter', (self, sql, args)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
683 self.db.cursor.execute(sql, args)
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
684 l = self.db.cursor.fetchall()
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
685
1839
06f5b36b201b Fix a couple of failures in mysql backend unit tests.
Richard Jones <richard@users.sourceforge.net>
parents: 1836
diff changeset
686 # return the IDs (the first column)
2098
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
687 # XXX numeric ids
18addf2a8596 Implemented proper datatypes in mysql and postgresql backends...
Richard Jones <richard@users.sourceforge.net>
parents: 2093
diff changeset
688 return [str(row[0]) for row in l]
1415
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
689
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
690 class Class(MysqlClass, rdbms_common.Class):
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
691 pass
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
692 class IssueClass(MysqlClass, rdbms_common.IssueClass):
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
693 pass
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
694 class FileClass(MysqlClass, rdbms_common.FileClass):
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
695 pass
6883852e9b15 mysql backend passes all tests (at last!)
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1375
diff changeset
696
1434
b953750bdc04 trackers on mysql can be initialised
Andrey Lebedev <kedder@users.sourceforge.net>
parents: 1433
diff changeset
697 #vim: set et
Roundup Issue Tracker: http://roundup-tracker.org/