1
# GNU Enterprise Common - MaxDB/SAP-DB Driver - Schema Introspection
3
# Copyright 2001-2005 Free Software Foundation
5
# This file is part of GNU Enterprise
7
# GNU Enterprise is free software; you can redistribute it
8
# and/or modify it under the terms of the GNU General Public
9
# License as published by the Free Software Foundation; either
10
# version 2, or (at your option) any later version.
12
# GNU Enterprise is distributed in the hope that it will be
13
# useful, but WITHOUT ANY WARRANTY; without even the implied
14
# warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
15
# PURPOSE. See the GNU General Public License for more details.
17
# You should have received a copy of the GNU General Public
18
# License along with program; see the file COPYING. If not,
19
# write to the Free Software Foundation, Inc., 59 Temple Place
20
# - Suite 330, Boston, MA 02111-1307, USA.
22
# $Id: Introspection.py 6851 2005-01-03 20:59:28Z jcater $
24
__all__ = ['Introspection']
28
from gnue.common.datasources import GIntrospection
31
# =============================================================================
32
# This class implements schema introspection for MaxDB/SAP-DB backends
33
# =============================================================================
35
class Introspection (GIntrospection.Introspection):
37
# list of the types of Schema objects this driver provides
38
types =[ ('table', _('Tables'), 1),
39
('view', _('Views'), 1),
40
('synonym', _('Synonyms'), 1),
41
('result', _('Result Table'), 1) ]
43
# ---------------------------------------------------------------------------
44
# Find a schema element by name or type
45
# ---------------------------------------------------------------------------
47
def find (self, name = None, type = None):
49
This function searches the schema for an element by name and/or type. If no
50
name and no type is given, all elements will be retrieved.
52
@param name: look for an element with this name
53
@param type: look for an element of this type
54
@return: A sequence of schema instances, one per element found, or None if
55
no element could be found.
59
cond = ["TYPE <> 'SYSTEM'"]
62
cond.append ("TABLENAME = '%s'" % name.upper ())
65
cond.append ("TABLETYPE = '%s'" % type.upper ())
67
cmd = u"SELECT tableid, tablename, tabletype, owner FROM DOMAIN.TABLES " \
68
"WHERE %s ORDER BY tablename" % string.join (cond, " AND ")
70
cursor = self._connection.makecursor (cmd)
72
for rs in cursor.fetchall ():
73
attrs = {'id' : rs [0],
77
'indices' : self.__getIndices (rs [1]),
78
'primarykey': self.__getPrimaryKey (rs [1], rs [2])}
80
result.append (GIntrospection.Schema (attrs, self._getChildSchema))
85
return len (result) and result or None
88
# ---------------------------------------------------------------------------
89
# Get all fields of a relation
90
# ---------------------------------------------------------------------------
92
def _getChildSchema (self, parent):
94
This function returns a list of all child elements of a given parent
97
@param parent: schema instance to fetch child elements for
98
@return: sequence of schema instances, one per element found
101
cmd = u"SELECT columnname, mode, datatype, len, dec, nullable, " \
102
"\"DEFAULT\", \"DEFAULTFUNCTION\", pos, keypos " \
103
"FROM DOMAIN.COLUMNS " \
104
"WHERE tablename = '%s' AND owner = '%s' AND tabletype = '%s' " \
105
"ORDER BY columnname" \
106
% (parent.name.upper (), parent.owner, parent.type)
109
cursor = self._connection.makecursor (cmd)
112
for rs in cursor.fetchall ():
114
attrs = {'id' : "%s.%s" % (parent.name, rs [0]),
117
'nativetype': nativetype,
118
'required' : rs [5] == 'NO',
122
if nativetype in ['DATE', 'TIME', 'TIMESTAMP']:
123
attrs ['datatype'] = 'date'
125
elif nativetype in ['FIXED', 'FLOAT', 'INTEGER', 'SMALLINT']:
126
attrs ['datatype'] = 'number'
127
attrs ['length'] = rs [3]
129
if nativetype == 'FIXED':
130
attrs ['precision'] = rs [4]
132
elif nativetype in ['BOOLEAN']:
133
attrs ['datatype'] = 'number'
136
attrs ['datatype'] = 'text'
137
attrs ['length'] = rs [3]
139
if rs [6] is not None:
140
attrs ['defaulttype'] = 'constant'
141
attrs ['defaultval'] = rs [6]
143
elif rs [7] is not None:
144
attrs ['defaulttype'] = 'system'
145
attrs ['defaultval'] = rs [7]
147
result.append (GIntrospection.Schema (attrs))
155
# ---------------------------------------------------------------------------
156
# Get the primary key of a relation (if it has one)
157
# ---------------------------------------------------------------------------
159
def __getPrimaryKey (self, tablename, tabletype):
161
This function returns a sequence with the primary key fields or None if the
162
given relation has no primary key.
164
@param tablename: name of the table to fetch the primary key for
165
@param tabletype: type of the table to fetch the primary key for
167
@return: sequence of primary key fields or None
170
cmd = u"SELECT columnname FROM DOMAIN.COLUMNS " \
171
"WHERE tablename = '%s' AND tabletype = '%s' AND mode = 'KEY'" \
172
"ORDER BY keypos" % (tablename.upper (), tabletype)
174
cursor = self._connection.makecursor (cmd)
177
result = [rs [0] for rs in cursor.fetchall ()]
182
return len (result) and result or None
185
# ---------------------------------------------------------------------------
186
# Get a dictionary of all indices defined for a relation
187
# ---------------------------------------------------------------------------
189
def __getIndices (self, tablename):
191
This function returns a dictionary with indices of the given table. The
192
keys are the index names and the values are dictionaries describing the
195
@param tablename: name of the table to fetch indices for.
196
@return: dictionary with indices or None
199
cmd = u"SELECT indexname, type FROM INDEXES WHERE tablename = '%s'" \
203
cursor = self._connection.makecursor (cmd)
206
for rs in cursor.fetchall ():
207
result [rs [0]] = {'unique' : rs [1] == 'UNIQUE',
211
cmd = u"SELECT columnname FROM INDEXCOLUMNS " \
212
"WHERE tablename = '%s' AND indexname = '%s' " \
213
"ORDER BY columnno" % (tablename.upper (), rs [0])
215
fcursor = self._connection.makecursor (cmd)
217
result [rs [0]]['fields'] = [fr [0] for fr in fcursor.fetchall ()]
225
return len (result.keys ()) and result or None