Mercurial > p > roundup > code

annotate roundup/backends/back_mysql.py @ 1403:6eb8aae6d14e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added users' timezone support (typo fix)
author Andrey Lebedev <kedder@users.sourceforge.net>
date Mon, 27 Jan 2003 16:40:37 +0000
parents faf93d3fbf2f
children 6883852e9b15
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1375
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
1 from roundup.backends.rdbms_common import *
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
2 import MySQLdb
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
3 from MySQLdb.constants import ER
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
4
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
5 class Database(Database):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
6 arg = '%s'
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
7
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
8 def open_connection(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
9 db = getattr(self.config, 'MYSQL_DATABASE')
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
10 try:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
11 self.conn = MySQLdb.connect(*db)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
12 except MySQLdb.OperationalError, message:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
13 raise DatabaseError, message
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
14
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
15 self.cursor = self.conn.cursor()
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
16 try:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
17 self.database_schema = self.load_dbschema()
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
18 except MySQLdb.ProgrammingError, message:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
19 if message[0] != ER.NO_SUCH_TABLE:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
20 raise DatabaseError, message
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
21 self.database_schema = {}
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
22 self.cursor.execute("CREATE TABLE schema (schema TEXT)")
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
23 self.cursor.execute("CREATE TABLE ids (name varchar(255), num INT)")
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
24
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
25 def close(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
26 try:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
27 self.conn.close()
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
28 except MySQLdb.OperationalError, message:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
29 raise
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
30
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
31 def __repr__(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
32 return '<myroundsql 0x%x>'%id(self)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
33
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
34 def sql_fetchone(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
35 return self.cursor.fetchone()
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
36
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
37 def sql_fetchall(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
38 return self.cursor.fetchall()
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
39
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
40 def save_dbschema(self, schema):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
41 s = repr(self.database_schema)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
42 self.sql('INSERT INTO schema VALUES (%s)', (s,))
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
43
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
44 def load_dbschema(self):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
45 self.cursor.execute('SELECT schema FROM schema')
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
46 return eval(self.cursor.fetchone()[0])
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
47
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
48 def save_journal(self, classname, cols, nodeid, journaldate,
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
49 journaltag, action, params):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
50 params = repr(params)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
51 entry = (nodeid, journaldate, journaltag, action, params)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
52
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
53 a = self.arg
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
54 sql = 'insert into %s__journal (%s) values (%s,%s,%s,%s,%s)'%(classname,
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
55 cols, a, a, a, a, a)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
56 if __debug__:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
57 print >>hyperdb.DEBUG, 'addjournal', (self, sql, entry)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
58 self.cursor.execute(sql, entry)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
59
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
60 def load_journal(self, classname, cols, nodeid):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
61 sql = 'select %s from %s__journal where nodeid=%s'%(cols, classname,
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
62 self.arg)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
63 if __debug__:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
64 print >>hyperdb.DEBUG, 'getjournal', (self, sql, nodeid)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
65 self.cursor.execute(sql, (nodeid,))
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
66 res = []
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
67 for nodeid, date_stamp, user, action, params in self.cursor.fetchall():
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
68 params = eval(params)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
69 res.append((nodeid, date.Date(date_stamp), user, action, params))
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
70 return res
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
71
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
72 def create_class_table(self, spec):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
73 cols, mls = self.determine_columns(spec.properties.items())
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
74 cols.append('id')
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
75 cols.append('__retired__')
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
76 scols = ',' . join(['`%s` VARCHAR(255)'%x for x in cols])
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
77 sql = 'CREATE TABLE `_%s` (%s)'%(spec.classname, scols)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
78 if __debug__:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
79 print >>hyperdb.DEBUG, 'create_class', (self, sql)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
80 self.cursor.execute(sql)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
81 return cols, mls
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
82
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
83 def create_journal_table(self, spec):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
84 cols = ',' . join(['`%s` VARCHAR(255)'%x
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
85 for x in 'nodeid date tag action params' . split()])
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
86 sql = 'CREATE TABLE `%s__journal` (%s)'%(spec.classname, cols)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
87 if __debug__:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
88 print >>hyperdb.DEBUG, 'create_class', (self, sql)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
89 self.cursor.execute(sql)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
90
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
91 def create_multilink_table(self, spec, ml):
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
92 sql = '''CREATE TABLE `%s_%s` (linkid VARCHAR(255),
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
93 nodeid VARCHAR(255))'''%(spec.classname, ml)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
94 if __debug__:
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
95 print >>hyperdb.DEBUG, 'create_class', (self, sql)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
96 self.cursor.execute(sql)
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
97
faf93d3fbf2f added mysql backend
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
98
Roundup Issue Tracker: http://roundup-tracker.org/