Mercurial > p > roundup > code

annotate roundup/backends/back_postgresql.py @ 1911:f5c804379c85

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fixed ZRoundup - mostly changes to classic template
author Richard Jones <richard@users.sourceforge.net>
date Wed, 12 Nov 2003 01:00:59 +0000
parents f255363e6d97
children f9316d2cd5ba
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
1 #
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
2 # Copyright (c) 2003 Martynas Sklyzmantas, Andrey Lebedev <andrey@micro.lt>
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
3 #
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
4 # This module is free software, and you may redistribute it and/or modify
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
5 # under the same terms as Python, so long as this copyright message and
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
6 # disclaimer are retained in their original form.
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
7 #
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
8 # psycopg backend for roundup
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
9 #
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
10
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
11 from roundup import hyperdb, date
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
12 from roundup.backends import rdbms_common
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
13 import psycopg
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
14 import os, shutil, popen2
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
15
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
16 class Database(rdbms_common.Database):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
17 arg = '%s'
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
18
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
19 def sql_open_connection(self):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
20 db = getattr(self.config, 'POSTGRESQL_DATABASE')
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
21 try:
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
22 self.conn = psycopg.connect(**db)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
23 except psycopg.OperationalError, message:
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
24 raise DatabaseError, message
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
25
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
26 self.cursor = self.conn.cursor()
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
27
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
28 try:
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
29 self.database_schema = self.load_dbschema()
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
30 except:
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
31 self.rollback()
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
32 self.database_schema = {}
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
33 self.sql("CREATE TABLE schema (schema TEXT)")
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
34 self.sql("CREATE TABLE ids (name VARCHAR(255), num INT4)")
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
35
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
36 def __repr__(self):
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
37 return '<roundpsycopgsql 0x%x>' % id(self)
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
38
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
39 def sql_stringquote(self, value):
1906
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
40 ''' psycopg.QuotedString returns a "buffer" object with the
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
41 single-quotes around it... '''
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
42 return str(psycopg.QuotedString(str(value)))[1:-1]
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
43
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
44 def sql_index_exists(self, table_name, index_name):
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
45 sql = 'select count(*) from pg_indexes where ' \
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
46 'tablename=%s and indexname=%s'%(self.arg, self.arg)
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
47 self.cursor.execute(sql, (table_name, index_name))
f255363e6d97 PostgreSQL backend lands.
Richard Jones <richard@users.sourceforge.net>
parents: 1873
diff changeset
48 return self.cursor.fetchone()[0]
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
49
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
50 def create_class_table(self, spec):
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
51 cols, mls = self.determine_columns(spec.properties.items())
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
52 cols.append('id')
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
53 cols.append('__retired__')
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
54 scols = ',' . join(['"%s" VARCHAR(255)' % x for x in cols])
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
55 sql = 'CREATE TABLE "_%s" (%s)' % (spec.classname, scols)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
56
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
57 if __debug__:
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
58 print >>hyperdb.DEBUG, 'create_class', (self, sql)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
59
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
60 self.cursor.execute(sql)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
61 return cols, mls
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
62
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
63 def create_journal_table(self, spec):
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
64 cols = ',' . join(['"%s" VARCHAR(255)' % x
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
65 for x in 'nodeid date tag action params' . split()])
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
66 sql = 'CREATE TABLE "%s__journal" (%s)'%(spec.classname, cols)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
67
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
68 if __debug__:
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
69 print >>hyperdb.DEBUG, 'create_class', (self, sql)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
70
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
71 self.cursor.execute(sql)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
72
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
73 def create_multilink_table(self, spec, ml):
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
74 sql = '''CREATE TABLE "%s_%s" (linkid VARCHAR(255),
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
75 nodeid VARCHAR(255))''' % (spec.classname, ml)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
76
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
77 if __debug__:
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
78 print >>hyperdb.DEBUG, 'create_class', (self, sql)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
79
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
80 self.cursor.execute(sql)
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
81
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
82 class Class(rdbms_common.Class):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
83 pass
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
84 class IssueClass(rdbms_common.IssueClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
85 pass
1911
f5c804379c85 fixed ZRoundup - mostly changes to classic template
Richard Jones <richard@users.sourceforge.net>
parents: 1906
diff changeset
86 class FileClass(rdbms_common.FileClass):
1873
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
87 pass
f63aa57386b0 Backend improvements.
Richard Jones <richard@users.sourceforge.net>
parents:
diff changeset
88
Roundup Issue Tracker: http://roundup-tracker.org/