1 #
2 # Copyright (c) 2003 Martynas Sklyzmantas, Andrey Lebedev <andrey@micro.lt>
3 #
4 # This module is free software, and you may redistribute it and/or modify
5 # under the same terms as Python, so long as this copyright message and
6 # disclaimer are retained in their original form.
7 #
8 # Mysql backend for roundup
9 #
11 from roundup.backends.rdbms_common import *
12 from roundup.backends import rdbms_common
13 import MySQLdb
14 from MySQLdb.constants import ER
16 class Database(Database):
17 arg = '%s'
19 def open_connection(self):
20 db = getattr(self.config, 'MYSQL_DATABASE')
21 try:
22 self.conn = MySQLdb.connect(*db)
23 except MySQLdb.OperationalError, message:
24 raise DatabaseError, message
26 self.cursor = self.conn.cursor()
27 # start transaction
28 self.sql("SET AUTOCOMMIT=0")
29 self.sql("BEGIN")
30 try:
31 self.database_schema = self.load_dbschema()
32 except MySQLdb.OperationalError, message:
33 if message[0] != ER.NO_DB_ERROR:
34 raise
35 except MySQLdb.ProgrammingError, message:
36 if message[0] != ER.NO_SUCH_TABLE:
37 raise DatabaseError, message
38 self.database_schema = {}
39 self.sql("CREATE TABLE schema (schema TEXT) TYPE=BDB")
40 self.sql("CREATE TABLE ids (name varchar(255), num INT) TYPE=BDB")
42 def close(self):
43 try:
44 self.conn.close()
45 except MySQLdb.OperationalError, message:
46 raise
48 def __repr__(self):
49 return '<myroundsql 0x%x>'%id(self)
51 def sql_fetchone(self):
52 return self.cursor.fetchone()
54 def sql_fetchall(self):
55 return self.cursor.fetchall()
57 def save_dbschema(self, schema):
58 s = repr(self.database_schema)
59 self.sql('INSERT INTO schema VALUES (%s)', (s,))
61 def load_dbschema(self):
62 self.cursor.execute('SELECT schema FROM schema')
63 schema = self.cursor.fetchone()
64 if schema:
65 return eval(schema[0])
66 return None
68 def save_journal(self, classname, cols, nodeid, journaldate,
69 journaltag, action, params):
70 params = repr(params)
71 entry = (nodeid, journaldate, journaltag, action, params)
73 a = self.arg
74 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(classname,
75 cols, a, a, a, a, a)
76 if __debug__:
77 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
78 self.cursor.execute(sql, entry)
80 def load_journal(self, classname, cols, nodeid):
81 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
82 self.arg)
83 if __debug__:
84 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
85 self.cursor.execute(sql, (nodeid,))
86 res = []
87 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
88 params = eval(params)
89 res.append((nodeid, date.Date(date_stamp), user, action, params))
90 return res
92 def create_class_table(self, spec):
93 cols, mls = self.determine_columns(spec.properties.items())
94 cols.append('id')
95 cols.append('__retired__')
96 scols = ',' . join(['`%s` VARCHAR(255)'%x for x in cols])
97 sql = 'CREATE TABLE `_%s` (%s) TYPE=BDB'%(spec.classname, scols)
98 if __debug__:
99 print >>hyperdb.DEBUG, 'create_class', (self, sql)
100 self.cursor.execute(sql)
101 return cols, mls
103 def create_journal_table(self, spec):
104 cols = ',' . join(['`%s` VARCHAR(255)'%x
105 for x in 'nodeid date tag action params' . split()])
106 sql = 'CREATE TABLE `%s__journal` (%s) TYPE=BDB'%(spec.classname, cols)
107 if __debug__:
108 print >>hyperdb.DEBUG, 'create_class', (self, sql)
109 self.cursor.execute(sql)
111 def create_multilink_table(self, spec, ml):
112 sql = '''CREATE TABLE `%s_%s` (linkid VARCHAR(255),
113 nodeid VARCHAR(255)) TYPE=BDB'''%(spec.classname, ml)
114 if __debug__:
115 print >>hyperdb.DEBUG, 'create_class', (self, sql)
116 self.cursor.execute(sql)
118 class MysqlClass:
119 def find(self, **propspec):
120 '''Get the ids of nodes in this class which link to the given nodes.
122 Since MySQL < 4.0.0 does not support unions, so we overrideg this
123 method without using this keyword
125 '''
126 if __debug__:
127 print >>hyperdb.DEBUG, 'find', (self, propspec)
129 # shortcut
130 if not propspec:
131 return []
133 # validate the args
134 props = self.getprops()
135 propspec = propspec.items()
136 for propname, nodeids in propspec:
137 # check the prop is OK
138 prop = props[propname]
139 if not isinstance(prop, Link) and not isinstance(prop, Multilink):
140 raise TypeError, "'%s' not a Link/Multilink property"%propname
142 # first, links
143 l = []
144 where = []
145 allvalues = ()
146 a = self.db.arg
147 for prop, values in propspec:
148 if not isinstance(props[prop], hyperdb.Link):
149 continue
150 if type(values) is type(''):
151 allvalues += (values,)
152 where.append('_%s = %s'%(prop, a))
153 else:
154 allvalues += tuple(values.keys())
155 where.append('_%s in (%s)'%(prop, ','.join([a]*len(values))))
156 tables = []
157 if where:
158 self.db.sql('select id as nodeid from _%s where %s' % (self.classname, ' and '.join(where)), allvalues)
159 l += [x[0] for x in self.db.sql_fetchall()]
161 # now multilinks
162 for prop, values in propspec:
163 vals = ()
164 if not isinstance(props[prop], hyperdb.Multilink):
165 continue
166 if type(values) is type(''):
167 vals = (values,)
168 s = a
169 else:
170 vals = tuple(values.keys())
171 s = ','.join([a]*len(values))
172 query = 'select nodeid from %s_%s where linkid in (%s)'%(
173 self.classname, prop, s)
174 self.db.sql(query, vals)
175 l += [x[0] for x in self.db.sql_fetchall()]
176 if __debug__:
177 print >>hyperdb.DEBUG, 'find ... ', l #db.sql("DROP DATABASE %s" % config.MYSQL_DBNAME)
179 # Remove duplicated ids
180 d = {}
181 for k in l:
182 d[k] = 1
183 return d.keys()
185 return l
187 class Class(MysqlClass, rdbms_common.Class):
188 pass
189 class IssueClass(MysqlClass, rdbms_common.IssueClass):
190 pass
191 class FileClass(MysqlClass, rdbms_common.FileClass):
192 pass
194 def nuke(config):
195 """ Clear all database contents and drop database itself"""
196 # Connect to db
197 db = Database(config, 'admin')
198 db.sql("DROP DATABASE %s" % config.MYSQL_DBNAME)
199 db.sql("CREATE DATABASE %s" % config.MYSQL_DBNAME)