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 import os, shutil
15 from MySQLdb.constants import ER
17 class Maintenance:
18 """ Database maintenance functions """
19 def db_nuke(self, config):
20 """Clear all database contents and drop database itself"""
21 db = Database(config, 'admin')
22 db.sql("DROP DATABASE %s" % config.MYSQL_DBNAME)
23 db.sql("CREATE DATABASE %s" % config.MYSQL_DBNAME)
24 if os.path.exists(config.DATABASE):
25 shutil.rmtree(config.DATABASE)
27 def db_exists(self, config):
28 """Check if database already exists"""
29 # Yes, this is a hack, but we must must open connection without
30 # selecting a database to prevent creation of some tables
31 config.MYSQL_DATABASE = (config.MYSQL_DBHOST, config.MYSQL_DBUSER, config.MYSQL_DBPASSWORD)
32 db = Database(config, 'admin')
33 db.conn.select_db(config.MYSQL_DBNAME)
34 config.MYSQL_DATABASE = (config.MYSQL_DBHOST, config.MYSQL_DBUSER, config.MYSQL_DBPASSWORD, config.MYSQL_DBNAME)
35 db.sql("SHOW TABLES")
36 tables = db.sql_fetchall()
37 if tables or os.path.exists(config.DATABASE):
38 return 1
39 return 0
41 class Database(Database):
42 arg = '%s'
44 def open_connection(self):
45 db = getattr(self.config, 'MYSQL_DATABASE')
46 try:
47 self.conn = MySQLdb.connect(*db)
48 except MySQLdb.OperationalError, message:
49 raise DatabaseError, message
51 self.cursor = self.conn.cursor()
52 # start transaction
53 self.sql("SET AUTOCOMMIT=0")
54 self.sql("BEGIN")
55 try:
56 self.database_schema = self.load_dbschema()
57 except MySQLdb.OperationalError, message:
58 if message[0] != ER.NO_DB_ERROR:
59 raise
60 except MySQLdb.ProgrammingError, message:
61 if message[0] != ER.NO_SUCH_TABLE:
62 raise DatabaseError, message
63 self.database_schema = {}
64 self.sql("CREATE TABLE schema (schema TEXT) TYPE=BDB")
65 self.sql("CREATE TABLE ids (name varchar(255), num INT) TYPE=BDB")
67 def close(self):
68 try:
69 self.conn.close()
70 except MySQLdb.OperationalError, message:
71 raise
73 def __repr__(self):
74 return '<myroundsql 0x%x>'%id(self)
76 def sql_fetchone(self):
77 return self.cursor.fetchone()
79 def sql_fetchall(self):
80 return self.cursor.fetchall()
82 def save_dbschema(self, schema):
83 s = repr(self.database_schema)
84 self.sql('INSERT INTO schema VALUES (%s)', (s,))
86 def load_dbschema(self):
87 self.cursor.execute('SELECT schema FROM schema')
88 schema = self.cursor.fetchone()
89 if schema:
90 return eval(schema[0])
91 return None
93 def save_journal(self, classname, cols, nodeid, journaldate,
94 journaltag, action, params):
95 params = repr(params)
96 entry = (nodeid, journaldate, journaltag, action, params)
98 a = self.arg
99 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(classname,
100 cols, a, a, a, a, a)
101 if __debug__:
102 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
103 self.cursor.execute(sql, entry)
105 def load_journal(self, classname, cols, nodeid):
106 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
107 self.arg)
108 if __debug__:
109 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
110 self.cursor.execute(sql, (nodeid,))
111 res = []
112 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
113 params = eval(params)
114 res.append((nodeid, date.Date(date_stamp), user, action, params))
115 return res
117 def create_class_table(self, spec):
118 cols, mls = self.determine_columns(spec.properties.items())
119 cols.append('id')
120 cols.append('__retired__')
121 scols = ',' . join(['`%s` VARCHAR(255)'%x for x in cols])
122 sql = 'CREATE TABLE `_%s` (%s) TYPE=BDB'%(spec.classname, scols)
123 if __debug__:
124 print >>hyperdb.DEBUG, 'create_class', (self, sql)
125 self.cursor.execute(sql)
126 return cols, mls
128 def create_journal_table(self, spec):
129 cols = ',' . join(['`%s` VARCHAR(255)'%x
130 for x in 'nodeid date tag action params' . split()])
131 sql = 'CREATE TABLE `%s__journal` (%s) TYPE=BDB'%(spec.classname, cols)
132 if __debug__:
133 print >>hyperdb.DEBUG, 'create_class', (self, sql)
134 self.cursor.execute(sql)
136 def create_multilink_table(self, spec, ml):
137 sql = '''CREATE TABLE `%s_%s` (linkid VARCHAR(255),
138 nodeid VARCHAR(255)) TYPE=BDB'''%(spec.classname, ml)
139 if __debug__:
140 print >>hyperdb.DEBUG, 'create_class', (self, sql)
141 self.cursor.execute(sql)
143 # Static methods
144 nuke = Maintenance().db_nuke
145 exists = Maintenance().db_exists
147 class MysqlClass:
148 def find(self, **propspec):
149 '''Get the ids of nodes in this class which link to the given nodes.
151 Since MySQL < 4.0.0 does not support unions, so we overrideg this
152 method without using this keyword
154 '''
155 if __debug__:
156 print >>hyperdb.DEBUG, 'find', (self, propspec)
158 # shortcut
159 if not propspec:
160 return []
162 # validate the args
163 props = self.getprops()
164 propspec = propspec.items()
165 for propname, nodeids in propspec:
166 # check the prop is OK
167 prop = props[propname]
168 if not isinstance(prop, Link) and not isinstance(prop, Multilink):
169 raise TypeError, "'%s' not a Link/Multilink property"%propname
171 # first, links
172 l = []
173 where = []
174 allvalues = ()
175 a = self.db.arg
176 for prop, values in propspec:
177 if not isinstance(props[prop], hyperdb.Link):
178 continue
179 if type(values) is type(''):
180 allvalues += (values,)
181 where.append('_%s = %s'%(prop, a))
182 else:
183 allvalues += tuple(values.keys())
184 where.append('_%s in (%s)'%(prop, ','.join([a]*len(values))))
185 tables = []
186 if where:
187 self.db.sql('select id as nodeid from _%s where %s' % (self.classname, ' and '.join(where)), allvalues)
188 l += [x[0] for x in self.db.sql_fetchall()]
190 # now multilinks
191 for prop, values in propspec:
192 vals = ()
193 if not isinstance(props[prop], hyperdb.Multilink):
194 continue
195 if type(values) is type(''):
196 vals = (values,)
197 s = a
198 else:
199 vals = tuple(values.keys())
200 s = ','.join([a]*len(values))
201 query = 'select nodeid from %s_%s where linkid in (%s)'%(
202 self.classname, prop, s)
203 self.db.sql(query, vals)
204 l += [x[0] for x in self.db.sql_fetchall()]
205 if __debug__:
206 print >>hyperdb.DEBUG, 'find ... ', l
208 # Remove duplicated ids
209 d = {}
210 for k in l:
211 d[k] = 1
212 return d.keys()
214 return l
216 class Class(MysqlClass, rdbms_common.Class):
217 pass
218 class IssueClass(MysqlClass, rdbms_common.IssueClass):
219 pass
220 class FileClass(MysqlClass, rdbms_common.FileClass):
221 pass
223 #vim: set et