Author: shawkins Date: 2011-03-23 07:45:46 -0400 (Wed, 23 Mar 2011) New Revision: 3024 Modified: trunk/runtime/src/main/java/org/teiid/deployers/PgCatalogMetadataStore.java trunk/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java trunk/runtime/src/main/java/org/teiid/transport/PgBackendProtocol.java Log: forward merge from 7.1.1 Modified: trunk/runtime/src/main/java/org/teiid/deployers/PgCatalogMetadataStore.java =================================================================== --- trunk/runtime/src/main/java/org/teiid/deployers/PgCatalogMetadataStore.java 2011-03-23 02:07:04 UTC (rev 3023) +++ trunk/runtime/src/main/java/org/teiid/deployers/PgCatalogMetadataStore.java 2011-03-23 11:45:46 UTC (rev 3024) @@ -148,12 +148,18 @@ "pt.typlen as attlen, " + //$NON-NLS-1$ "convert(t1.Position, short) as attnum, " + //$NON-NLS-1$ "t1.Length as atttypmod, " + //$NON-NLS-1$ - "false as attnotnull, " + //$NON-NLS-1$ + "CASE WHEN (t1.NullType = 'No Nulls') THEN true ELSE false END as attnotnull, " + //$NON-NLS-1$ "false as attisdropped, " + //$NON-NLS-1$ "false as atthasdef " + //$NON-NLS-1$ "FROM SYS.Columns as t1 LEFT OUTER JOIN " + //$NON-NLS-1$ "SYS.Tables st ON (st.Name = t1.TableName AND st.SchemaName = t1.SchemaName) LEFT OUTER JOIN " + //$NON-NLS-1$ - "pg_catalog.pg_type pt ON (CASE WHEN (t1.DataType = 'clob' OR t1.DataType = 'blob') THEN 'lo' ELSE t1.DataType END = pt.typname)"; //$NON-NLS-1$ + "pg_catalog.pg_type pt ON (CASE " +//$NON-NLS-1$ + "WHEN (t1.DataType = 'clob' OR t1.DataType = 'blob') THEN 'lo' " +//$NON-NLS-1$ + "WHEN (t1.DataType = 'byte' ) THEN 'short' " + //$NON-NLS-1$ + "WHEN (t1.DataType = 'time' ) THEN 'datetime' " + //$NON-NLS-1$ + "WHEN (t1.DataType = 'biginteger' ) THEN 'decimal' " + //$NON-NLS-1$ + "WHEN (t1.DataType = 'bigdecimal' ) THEN 'decimal' " + //$NON-NLS-1$ + "ELSE t1.DataType END = pt.typname)"; //$NON-NLS-1$ t.setSelectTransformation(transformation); t.setMaterialized(true); return t; Modified: trunk/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java =================================================================== --- trunk/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java 2011-03-23 02:07:04 UTC (rev 3023) +++ trunk/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java 2011-03-23 11:45:46 UTC (rev 3024) @@ -53,10 +53,12 @@ private static Pattern pkPattern = Pattern.compile("select ta.attname, ia.attnum, ic.relname, n.nspname, tc.relname " +//$NON-NLS-1$ "from pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class tc, pg_catalog.pg_index i, " +//$NON-NLS-1$ - "pg_catalog.pg_namespace n, pg_catalog.pg_class ic where tc.relname = E'(\\w+)' AND n.nspname = E'(\\w+)'.*" );//$NON-NLS-1$ + "pg_catalog.pg_namespace n, pg_catalog.pg_class ic where tc.relname = E?'(\\w+)' AND n.nspname = E?'(\\w+)'.*" );//$NON-NLS-1$ - private static Pattern pkKeyPattern = Pattern.compile("select ta.attname, ia.attnum, ic.relname, n.nspname, NULL .*"); //$NON-NLS-1$ + private static Pattern pkKeyPattern = Pattern.compile("select ta.attname, ia.attnum, ic.relname, n.nspname, NULL from " + //$NON-NLS-1$ + "pg_catalog.pg_attribute ta, pg_catalog.pg_attribute ia, pg_catalog.pg_class ic, pg_catalog.pg_index i, " + //$NON-NLS-1$ + "pg_catalog.pg_namespace n where ic.relname = E?'(\\w+)' AND n.nspname = E?'(\\w+)' .*"); //$NON-NLS-1$ private Pattern fkPattern = Pattern.compile("select\\s+'(\\w+)'::name as PKTABLE_CAT," + //$NON-NLS-1$ "\\s+n2.nspname as PKTABLE_SCHEM," + //$NON-NLS-1$ @@ -101,9 +103,9 @@ "\\s+pg_catalog.pg_namespace n" + //$NON-NLS-1$ "\\s+where contype = 'f' " + //$NON-NLS-1$ "\\s+and conrelid = c.oid" + //$NON-NLS-1$ - "\\s+and relname = E'(\\w+)'" + //$NON-NLS-1$ + "\\s+and relname = E?'(\\w+)'" + //$NON-NLS-1$ "\\s+and n.oid = c.relnamespace" + //$NON-NLS-1$ - "\\s+and n.nspname = E'(\\w+)'" + //$NON-NLS-1$ + "\\s+and n.nspname = E?'(\\w+)'" + //$NON-NLS-1$ "\\s+\\) ref" + //$NON-NLS-1$ "\\s+inner join pg_catalog.pg_class c1" + //$NON-NLS-1$ "\\s+on c1.oid = ref.conrelid\\)" + //$NON-NLS-1$ @@ -128,12 +130,10 @@ "nspname, p.oid, atttypid, attname, proargnames, proargmodes, proallargtypes from ((pg_catalog.pg_namespace n inner join " + //$NON-NLS-1$ "pg_catalog.pg_proc p on p.pronamespace = n.oid) inner join pg_type t on t.oid = p.prorettype) left outer join " + //$NON-NLS-1$ "pg_attribute a on a.attrelid = t.typrelid and attnum > 0 and not attisdropped " + //$NON-NLS-1$ - "where has_function_privilege(p.oid, 'EXECUTE') and nspname like E'(\\w+)' " + //$NON-NLS-1$ - "and proname like E'(\\w+)' " + //$NON-NLS-1$ + "where has_function_privilege(p.oid, 'EXECUTE') and nspname like E?'(\\w+)' " + //$NON-NLS-1$ + "and proname like E?'(\\w+)' " + //$NON-NLS-1$ "order by nspname, proname, p.oid, attnum"); //$NON-NLS-1$ - - private static Pattern deallocatePattern = Pattern.compile("DEALLOCATE \"(\\w+\\d+_*)\""); //$NON-NLS-1$ private static Pattern releasePattern = Pattern.compile("RELEASE (\\w+\\d+_*)"); //$NON-NLS-1$ private static Pattern savepointPattern = Pattern.compile("SAVEPOINT (\\w+\\d+_*)"); //$NON-NLS-1$ @@ -310,13 +310,25 @@ modified = new StringBuffer("SELECT k.Name AS attname, convert(Position, short) AS attnum, TableName AS relname, SchemaName AS nspname, TableName AS relname") //$NON-NLS-1$ .append(" FROM SYS.KeyColumns k") //$NON-NLS-1$ .append(" WHERE ") //$NON-NLS-1$ - .append(" UCASE(SchemaName)").append(" LIKE '").append(m.group(2)).append("'")//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ - .append(" AND UCASE(TableName)") .append(" LIKE '").append(m.group(1)).append("'")//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + .append(" UCASE(SchemaName)").append(" LIKE UCASE('").append(m.group(2)).append("')")//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + .append(" AND UCASE(TableName)") .append(" LIKE UCASE('").append(m.group(1)).append("')")//$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ .append(" AND KeyType LIKE 'Primary'") //$NON-NLS-1$ .append(" ORDER BY attnum").toString(); //$NON-NLS-1$ } else if ((m = pkKeyPattern.matcher(modified)).matches()) { - modified = "SELECT NULL, NULL, NULL, NULL, NULL FROM (SELECT 1) as X WHERE 0=1"; //$NON-NLS-1$ + String tableName = m.group(1); + if (tableName.endsWith("_pkey")) { //$NON-NLS-1$ + tableName = tableName.substring(0, tableName.length()-5); + modified = "select ia.attname, ia.attnum, ic.relname, n.nspname, NULL "+ //$NON-NLS-1$ + "from pg_catalog.pg_attribute ia, pg_catalog.pg_class ic, pg_catalog.pg_namespace n, Sys.KeyColumns kc "+ //$NON-NLS-1$ + "where ic.relname = '"+tableName+"' AND n.nspname = '"+m.group(2)+"' AND "+ //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + "n.oid = ic.relnamespace AND ia.attrelid = ic.oid AND kc.SchemaName = n.nspname " +//$NON-NLS-1$ + "AND kc.TableName = ic.relname AND kc.KeyType = 'Primary' AND kc.Name = ia.attname order by ia.attnum";//$NON-NLS-1$ + } + else { + modified = "SELECT NULL, NULL, NULL, NULL, NULL FROM (SELECT 1) as X WHERE 0=1"; //$NON-NLS-1$ + } + } else if ((m = fkPattern.matcher(modified)).matches()){ modified = "SELECT PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_CAT, FKTABLE_SCHEM, "+//$NON-NLS-1$ Modified: trunk/runtime/src/main/java/org/teiid/transport/PgBackendProtocol.java =================================================================== --- trunk/runtime/src/main/java/org/teiid/transport/PgBackendProtocol.java 2011-03-23 02:07:04 UTC (rev 3023) +++ trunk/runtime/src/main/java/org/teiid/transport/PgBackendProtocol.java 2011-03-23 11:45:46 UTC (rev 3024) @@ -649,25 +649,26 @@ */ private static int convertType(final int type) { switch (type) { + case Types.BIT: case Types.BOOLEAN: - return PG_TYPE_BOOL; + return PG_TYPE_BOOL; case Types.VARCHAR: return PG_TYPE_VARCHAR; case Types.CHAR: - return PG_TYPE_BPCHAR; + return PG_TYPE_BPCHAR; case Types.TINYINT: case Types.SMALLINT: - return PG_TYPE_INT2; + return PG_TYPE_INT2; case Types.INTEGER: - return PG_TYPE_INT4; + return PG_TYPE_INT4; case Types.BIGINT: - return PG_TYPE_INT8; + return PG_TYPE_INT8; case Types.NUMERIC: case Types.DECIMAL: - return PG_TYPE_NUMERIC; + return PG_TYPE_NUMERIC; case Types.FLOAT: case Types.REAL: - return PG_TYPE_FLOAT4; + return PG_TYPE_FLOAT4; case Types.DOUBLE: return PG_TYPE_FLOAT8; case Types.TIME: @@ -679,7 +680,7 @@ case Types.BLOB: case Types.BINARY: - case Types.VARBINARY: + case Types.VARBINARY: case Types.LONGVARBINARY: return PG_TYPE_BYTEA;