001: /*
002: * PostgresProcedureReader.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.postgres;
013:
014: import java.sql.PreparedStatement;
015: import java.sql.ResultSet;
016: import java.sql.SQLException;
017: import java.sql.Savepoint;
018: import java.sql.Statement;
019: import java.sql.Types;
020: import java.util.Collections;
021: import java.util.HashMap;
022: import java.util.List;
023: import java.util.Map;
024: import workbench.db.JdbcProcedureReader;
025: import workbench.db.JdbcUtils;
026: import workbench.db.ProcedureReader;
027: import workbench.db.WbConnection;
028: import workbench.log.LogMgr;
029: import workbench.resource.Settings;
030: import workbench.storage.DataStore;
031: import workbench.util.SqlUtil;
032: import workbench.util.StringUtil;
033:
034: /**
035: * @author support@sql-workbench.net
036: */
037: public class PostgresProcedureReader extends JdbcProcedureReader {
038: // Maps PG type names to Java types.
039: private Map<String, Integer> pgTypeMap;
040: private Map<Integer, PGType> pgTypes;
041: private PGType voidType;
042:
043: public PostgresProcedureReader(WbConnection conn) {
044: super (conn);
045: try {
046: this .useSavepoint = conn.supportsSavepoints();
047: } catch (Throwable th) {
048: this .useSavepoint = false;
049: }
050: }
051:
052: private Map<String, Integer> getJavaTypeMapping() {
053: if (pgTypeMap == null) {
054: // This mapping has been copied from the JDBC driver.
055: // This map is deeply hidden in the driver and even
056: // if I hard-coded references to the driver into the
057: // class I wouldn't know how to retrieve them.
058: pgTypeMap = new HashMap<String, Integer>();
059: pgTypeMap.put("int2", new Integer(Types.SMALLINT));
060: pgTypeMap.put("int4", new Integer(Types.INTEGER));
061: pgTypeMap.put("oid", new Integer(Types.INTEGER));
062: pgTypeMap.put("int8", new Integer(Types.BIGINT));
063: pgTypeMap.put("money", new Integer(Types.DOUBLE));
064: pgTypeMap.put("numeric", new Integer(Types.NUMERIC));
065: pgTypeMap.put("float4", new Integer(Types.REAL));
066: pgTypeMap.put("float8", new Integer(Types.DOUBLE));
067: pgTypeMap.put("bpchar", new Integer(Types.CHAR));
068: pgTypeMap.put("varchar", new Integer(Types.VARCHAR));
069: pgTypeMap.put("text", new Integer(Types.VARCHAR));
070: pgTypeMap.put("name", new Integer(Types.VARCHAR));
071: pgTypeMap.put("bytea", new Integer(Types.BINARY));
072: pgTypeMap.put("bool", new Integer(Types.BIT));
073: pgTypeMap.put("bit", new Integer(Types.BIT));
074: pgTypeMap.put("date", new Integer(Types.DATE));
075: pgTypeMap.put("time", new Integer(Types.TIME));
076: pgTypeMap.put("timetz", new Integer(Types.TIME));
077: pgTypeMap.put("timestamp", new Integer(Types.TIMESTAMP));
078: pgTypeMap.put("timestamptz", new Integer(Types.TIMESTAMP));
079: }
080: return pgTypeMap;
081: }
082:
083: private Integer getJavaType(String pgType) {
084: Integer i = getJavaTypeMapping().get(pgType);
085: if (i == null)
086: return new Integer(Types.OTHER);
087: return i;
088: }
089:
090: private Map<Integer, PGType> getPGTypes() {
091: if (pgTypes == null) {
092: pgTypes = new HashMap<Integer, PGType>(300);
093: Statement stmt = null;
094: ResultSet rs = null;
095: Savepoint sp = null;
096: try {
097: sp = connection.setSavepoint();
098: stmt = connection.createStatement();
099: rs = stmt
100: .executeQuery("select oid, typname, format_type(oid, null) from pg_type");
101: while (rs.next()) {
102: Integer oid = new Integer(rs.getInt(1));
103: PGType typ = new PGType();
104: typ.rawType = rs.getString(2);
105: typ.formattedType = StringUtil.trimQuotes(rs
106: .getString(3));
107: typ.oid = oid.intValue();
108: pgTypes.put(oid, typ);
109: if ("void".equals(typ.rawType)) {
110: voidType = typ;
111: }
112: }
113: connection.releaseSavepoint(sp);
114: } catch (SQLException e) {
115: connection.rollback(sp);
116: LogMgr.logError("PostgresProcedureReqder.getPGTypes()",
117: "Could not read postgres data types", e);
118: pgTypes = Collections.emptyMap();
119: } finally {
120: SqlUtil.closeAll(rs, stmt);
121: }
122: }
123: return pgTypes;
124: }
125:
126: private String getRawTypeNameFromOID(int oid) {
127: PGType typ = getPGTypes().get(new Integer(oid));
128: return typ.rawType;
129: }
130:
131: private String getFormattedTypeFromOID(int oid) {
132: PGType typ = getPGTypes().get(new Integer(oid));
133: return typ.formattedType;
134: }
135:
136: public StringBuilder getProcedureHeader(String aCatalog,
137: String aSchema, String aProcname, int procType) {
138: StringBuilder source = new StringBuilder();
139:
140: String nl = Settings.getInstance()
141: .getInternalEditorLineEnding();
142: Savepoint sp = null;
143: try {
144: sp = this .connection.setSavepoint();
145: DataStore ds = this .getProcedureColumns(aCatalog, aSchema,
146: aProcname);
147: source.append("CREATE OR REPLACE FUNCTION ");
148:
149: source.append(aProcname);
150: source.append(" (");
151: String retType = null;
152: int count = ds.getRowCount();
153: int added = 0;
154: for (int i = 0; i < count; i++) {
155: String varname = ds
156: .getValueAsString(
157: i,
158: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_COL_NAME);
159: String mode = ds
160: .getValueAsString(
161: i,
162: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_RESULT_TYPE);
163: String vartype = ds
164: .getValueAsString(
165: i,
166: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_DATA_TYPE);
167: String ret = ds
168: .getValueAsString(
169: i,
170: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_RESULT_TYPE);
171: if ("RETURN".equals(ret)) {
172: retType = vartype;
173: } else {
174: if (added > 0)
175: source.append(',');
176: source.append(mode);
177: source.append(' ');
178: if (varname != null) {
179: source.append(varname);
180: source.append(' ');
181: }
182: source.append(vartype);
183: added++;
184: }
185: }
186: source.append(')');
187: if (retType != null) {
188: source.append(nl + "RETURNS ");
189: source.append(retType);
190: }
191: source.append(nl + "AS" + nl);
192: this .connection.releaseSavepoint(sp);
193: } catch (Exception e) {
194: this .connection.rollback(sp);
195: LogMgr.logError(
196: "PostgresProcedureReader.getProcedureHeader()",
197: "Error retrieving header", e);
198: source = StringUtil.emptyBuffer();
199: }
200: return source;
201: }
202:
203: public DataStore getProcedureColumns(String catalog, String schema,
204: String procname) throws SQLException {
205: // String version = connection.getMetadata().getDbVersion();
206: // int majorVersion = 0;
207: // int minorVersion = 0;
208: //
209: // try
210: // {
211: // String[] parts = version.split("\\.");
212: // majorVersion = (parts.length > 0 ? Integer.valueOf(parts[0]) : 0);
213: // minorVersion = (parts.length > 1 ? Integer.valueOf(parts[1]) : 0);
214: // }
215: // catch (Throwable th)
216: // {
217: // majorVersion = 0;
218: // minorVersion = 0;
219: // }
220: //
221: if (Settings.getInstance().getBoolProperty(
222: "workbench.db.postgresql.fixproctypes", true)
223: && JdbcUtils.hasMinimumServerVersion(connection, "8.1")) {
224: return getColumns(catalog, schema, procname);
225: } else {
226: return super .getProcedureColumns(catalog, schema, procname);
227: }
228: }
229:
230: /**
231: * A workaround for pre 8.3 drivers so that argument names are retrieved properly
232: * from the database. This was mainly inspired by the source code of pgAdmin III
233: * and the current 8.3 driver sources
234: *
235: * @param catalog
236: * @param schema
237: * @param procname
238: * @return a DataStore with the argumens of the procedure
239: * @throws java.sql.SQLException
240: */
241: private DataStore getColumns(String catalog, String schema,
242: String procname) throws SQLException {
243: String sql = "SELECT format_type(p.prorettype, NULL) as formatted_type, \n"
244: + " t.typname as pg_type, \n"
245: + " array_to_string(p.proargtypes, ';') as argtypes, \n"
246: + " array_to_string(p.proallargtypes, ';') as allargtypes, \n"
247: + " array_to_string(p.proargnames, ';') as argnames, \n"
248: + " array_to_string(p.proargmodes, ';') as modes, \n"
249: + " t.typtype "
250: + "FROM pg_catalog.pg_proc p, \n"
251: + " pg_catalog.pg_namespace n, \n "
252: + " pg_catalog.pg_type t "
253: + "WHERE p.pronamespace = n.oid \n"
254: + "AND n.nspname = ? \n "
255: + "AND p.prorettype = t.oid \n"
256: + "AND p.proname = ?";
257:
258: DataStore result = createProcColsDataStore();
259:
260: Savepoint sp = null;
261: PreparedStatement stmt = null;
262: ResultSet rs = null;
263: try {
264: sp = this .connection.setSavepoint();
265:
266: stmt = this .connection.getSqlConnection().prepareStatement(
267: sql);
268: stmt.setString(1, schema);
269: stmt.setString(2, procname);
270:
271: rs = stmt.executeQuery();
272: if (rs.next()) {
273: String typeName = rs.getString("formatted_type");
274: String pgType = rs.getString("pg_type");
275: String types = rs.getString("argtypes");
276: String allTypes = rs.getString("allargtypes");
277: String names = rs.getString("argnames");
278: String modes = rs.getString("modes");
279: String returnTypeType = rs.getString("typtype");
280:
281: // pgAdmin II distinguishes functions from procedures using only the "modes" information
282: // the driver uses the returnTypeType as well
283: boolean isFunction = (returnTypeType.equals("b")
284: || returnTypeType.equals("d") || (returnTypeType
285: .equals("p") && modes == null));
286:
287: if (isFunction) {
288: int row = result.addRow();
289: result
290: .setValue(
291: row,
292: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_COL_NAME,
293: "returnValue");
294: result
295: .setValue(
296: row,
297: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_RESULT_TYPE,
298: "RETURN");
299: result
300: .setValue(
301: row,
302: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_JDBC_DATA_TYPE,
303: getJavaType(pgType));
304: result
305: .setValue(
306: row,
307: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_DATA_TYPE,
308: StringUtil.trimQuotes(typeName));
309: }
310:
311: List<String> argNames = null;
312: List<String> argTypes = null;
313: List<String> argModes = StringUtil.stringToList(modes,
314: ";", true, true);
315: if (allTypes != null) {
316: argTypes = StringUtil.stringToList(allTypes, ";",
317: true, true);
318: argNames = StringUtil.stringToList(names, ";",
319: true, true);
320: } else {
321: argTypes = StringUtil.stringToList(types, ";",
322: true, true);
323: }
324:
325: for (int i = 0; i < argTypes.size(); i++) {
326: int row = result.addRow();
327: int typeOid = -1;
328: try {
329: typeOid = Integer.valueOf(argTypes.get(i));
330: } catch (Exception e) {
331: // 2278 = void
332: typeOid = (voidType != null ? voidType.oid
333: : 2278);
334: }
335: String pgt = getRawTypeNameFromOID(typeOid);
336:
337: String nm = "$" + (i + 1);
338: if (argNames != null && i < argNames.size()) {
339: nm = argNames.get(i);
340: }
341: result
342: .setValue(
343: row,
344: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_COL_NAME,
345: nm);
346:
347: String md = "IN";
348: if (argModes != null && i < argModes.size()) {
349: String m = argModes.get(i);
350: if ("o".equals(m)) {
351: md = "OUT";
352: } else if ("b".equals(m)) {
353: md = "INOUT";
354: }
355: }
356:
357: result
358: .setValue(
359: row,
360: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_RESULT_TYPE,
361: md);
362: result
363: .setValue(
364: row,
365: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_JDBC_DATA_TYPE,
366: getJavaType(pgt));
367: result
368: .setValue(
369: row,
370: ProcedureReader.COLUMN_IDX_PROC_COLUMNS_DATA_TYPE,
371: getFormattedTypeFromOID(typeOid));
372: }
373:
374: } else {
375: LogMgr.logWarning(
376: "PostgreProcedureReader.getProcedureHeader()",
377: "Could not retrieve columns", null);
378: return super .getProcedureColumns(catalog, schema,
379: procname);
380: }
381:
382: this .connection.releaseSavepoint(sp);
383: } catch (Exception e) {
384: this .connection.rollback(sp);
385: LogMgr.logError(
386: "PostgresProcedureReader.getProcedureHeader()",
387: "Error retrieving header", e);
388: return super .getProcedureColumns(catalog, schema, procname);
389: } finally {
390: SqlUtil.closeAll(rs, stmt);
391: }
392: return result;
393: }
394: }
395:
396: class PGType {
397: String rawType;
398: String formattedType;
399: int oid;
400:
401: public PGType() {
402: }
403: }
|