[teiid-commits] teiid SVN: r3004 - in branches/7.1.x: runtime/src/main/java/org/teiid/odbc and 1 other directories.

Author: rareddy Date: 2011-03-17 17:20:12 -0400 (Thu, 17 Mar 2011) New Revision: 3004 Modified: branches/7.1.x/runtime/src/main/java/org/teiid/deployers/PgCatalogMetadataStore.java branches/7.1.x/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java branches/7.1.x/test-integration/common/src/test/resources/TestODBCSchema/test_PG_ATTRIBUTE.expected Log: TEIID-1515: Primary key reporting is fixed along with NOT NULL on the key metadata. Modified: branches/7.1.x/runtime/src/main/java/org/teiid/deployers/PgCatalogMetadataStore.java =================================================================== --- branches/7.1.x/runtime/src/main/java/org/teiid/deployers/PgCatalogMetadataStore.java 2011-03-17 19:51:18 UTC (rev 3003) +++ branches/7.1.x/runtime/src/main/java/org/teiid/deployers/PgCatalogMetadataStore.java 2011-03-17 21:20:12 UTC (rev 3004) @@ -148,7 +148,7 @@ "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$ Modified: branches/7.1.x/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java =================================================================== --- branches/7.1.x/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java 2011-03-17 19:51:18 UTC (rev 3003) +++ branches/7.1.x/runtime/src/main/java/org/teiid/odbc/ODBCServerRemoteImpl.java 2011-03-17 21:20:12 UTC (rev 3004) @@ -56,7 +56,9 @@ "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$ @@ -132,8 +134,6 @@ "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: branches/7.1.x/test-integration/common/src/test/resources/TestODBCSchema/test_PG_ATTRIBUTE.expected =================================================================== --- branches/7.1.x/test-integration/common/src/test/resources/TestODBCSchema/test_PG_ATTRIBUTE.expected 2011-03-17 19:51:18 UTC (rev 3003) +++ branches/7.1.x/test-integration/common/src/test/resources/TestODBCSchema/test_PG_ATTRIBUTE.expected 2011-03-17 21:20:12 UTC (rev 3004) @@ -1,25 +1,25 @@ integer integer string integer short short integer boolean boolean boolean oid attrelid attname atttypid attlen attnum atttypmod attnotnull attisdropped atthasdef -0 0 PART_ID 1043 -1 1 4 false false false +0 0 PART_ID 1043 -1 1 4 true false false 1 0 PART_NAME 1043 -1 2 255 false false false 2 0 PART_COLOR 1043 -1 3 30 false false false 3 0 PART_WEIGHT 1043 -1 4 255 false false false -4 1 SHIPPER_ID 21 2 1 0 false false false +4 1 SHIPPER_ID 21 2 1 0 true false false 5 1 SHIPPER_NAME 1043 -1 2 30 false false false -6 2 STATUS_ID 21 2 1 0 false false false +6 2 STATUS_ID 21 2 1 0 true false false 7 2 STATUS_NAME 1043 -1 2 30 false false false -8 3 SUPPLIER_ID 1043 -1 1 10 false false false -9 3 PART_ID 1043 -1 2 4 false false false +8 3 SUPPLIER_ID 1043 -1 1 10 true false false +9 3 PART_ID 1043 -1 2 4 true false false 10 3 QUANTITY 21 2 3 0 false false false 11 3 SHIPPER_ID 21 2 4 0 false false false -12 4 SUPPLIER_ID 1043 -1 1 10 false false false +12 4 SUPPLIER_ID 1043 -1 1 10 true false false 13 4 SUPPLIER_NAME 1043 -1 2 30 false false false 14 4 SUPPLIER_STATUS 21 2 3 0 false false false 15 4 SUPPLIER_CITY 1043 -1 4 30 false false false 16 4 SUPPLIER_STATE 1043 -1 5 2 false false false -17 5 VDBName 1043 -1 1 255 false false false -18 5 SchemaName 1043 -1 2 255 false false false -19 5 Name 1043 -1 3 255 false false false +17 5 VDBName 1043 -1 1 255 true false false +18 5 SchemaName 1043 -1 2 255 true false false +19 5 Name 1043 -1 3 255 true false false 20 5 TargetSchemaName 1043 -1 4 255 false false false 21 5 TargetName 1043 -1 5 4000 false false false 22 5 Valid 16 1 6 0 false false false @@ -28,103 +28,103 @@ 25 5 Cardinality 23 4 9 10 false false false 26 6 resourcePath 1043 -1 1 255 false false false 27 6 contents 14939 -1 2 0 false false false -28 7 VDBName 1043 -1 1 255 false false false +28 7 VDBName 1043 -1 1 255 true false false 29 7 SchemaName 1043 -1 2 255 false false false -30 7 TableName 1043 -1 3 255 false false false -31 7 Name 1043 -1 4 255 false false false -32 7 Position 23 4 5 10 false false false +30 7 TableName 1043 -1 3 255 true false false +31 7 Name 1043 -1 4 255 true false false +32 7 Position 23 4 5 10 true false false 33 7 NameInSource 1043 -1 6 255 false false false -34 7 DataType 1043 -1 7 100 false false false -35 7 Scale 23 4 8 10 false false false -36 7 Length 23 4 9 10 false false false -37 7 IsLengthFixed 16 1 10 1 false false false -38 7 SupportsSelect 16 1 11 1 false false false -39 7 SupportsUpdates 16 1 12 1 false false false -40 7 IsCaseSensitive 16 1 13 1 false false false -41 7 IsSigned 16 1 14 1 false false false -42 7 IsCurrency 16 1 15 1 false false false -43 7 IsAutoIncremented 16 1 16 1 false false false -44 7 NullType 1043 -1 17 20 false false false +34 7 DataType 1043 -1 7 100 true false false +35 7 Scale 23 4 8 10 true false false +36 7 Length 23 4 9 10 true false false +37 7 IsLengthFixed 16 1 10 1 true false false +38 7 SupportsSelect 16 1 11 1 true false false +39 7 SupportsUpdates 16 1 12 1 true false false +40 7 IsCaseSensitive 16 1 13 1 true false false +41 7 IsSigned 16 1 14 1 true false false +42 7 IsCurrency 16 1 15 1 true false false +43 7 IsAutoIncremented 16 1 16 1 true false false +44 7 NullType 1043 -1 17 20 true false false 45 7 MinRange 1043 -1 18 50 false false false 46 7 MaxRange 1043 -1 19 50 false false false -47 7 SearchType 1043 -1 20 20 false false false +47 7 SearchType 1043 -1 20 20 true false false 48 7 Format 1043 -1 21 255 false false false 49 7 DefaultValue 1043 -1 22 255 false false false -50 7 JavaClass 1043 -1 23 500 false false false -51 7 Precision 23 4 24 10 false false false +50 7 JavaClass 1043 -1 23 500 true false false +51 7 Precision 23 4 24 10 true false false 52 7 CharOctetLength 23 4 25 10 false false false -53 7 Radix 23 4 26 10 false false false -54 7 UID 1043 -1 27 50 false false false +53 7 Radix 23 4 26 10 true false false +54 7 UID 1043 -1 27 50 true false false 55 7 Description 1043 -1 28 255 false false false -56 7 OID 23 4 29 10 false false false -57 8 Name 1043 -1 1 100 false false false +56 7 OID 23 4 29 10 true false false +57 8 Name 1043 -1 1 100 true false false 58 8 IsStandard 16 1 2 1 false false false 59 8 IsPhysical 16 1 3 1 false false false -60 8 TypeName 1043 -1 4 100 false false false -61 8 JavaClass 1043 -1 5 500 false false false +60 8 TypeName 1043 -1 4 100 true false false +61 8 JavaClass 1043 -1 5 500 true false false 62 8 Scale 23 4 6 10 false false false -63 8 TypeLength 23 4 7 10 false false false -64 8 NullType 1043 -1 8 20 false false false -65 8 IsSigned 16 1 9 1 false false false -66 8 IsAutoIncremented 16 1 10 1 false false false -67 8 IsCaseSensitive 16 1 11 1 false false false -68 8 Precision 23 4 12 10 false false false +63 8 TypeLength 23 4 7 10 true false false +64 8 NullType 1043 -1 8 20 true false false +65 8 IsSigned 16 1 9 1 true false false +66 8 IsAutoIncremented 16 1 10 1 true false false +67 8 IsCaseSensitive 16 1 11 1 true false false +68 8 Precision 23 4 12 10 true false false 69 8 Radix 23 4 13 10 false false false -70 8 SearchType 1043 -1 14 20 false false false -71 8 UID 1043 -1 15 50 false false false +70 8 SearchType 1043 -1 14 20 true false false +71 8 UID 1043 -1 15 50 true false false 72 8 RuntimeType 1043 -1 16 64 false false false 73 8 BaseType 1043 -1 17 64 false false false 74 8 Description 1043 -1 18 255 false false false -75 8 OID 23 4 19 10 false false false -76 9 VDBName 1043 -1 1 255 false false false +75 8 OID 23 4 19 10 true false false +76 9 VDBName 1043 -1 1 255 true false false 77 9 SchemaName 1043 -1 2 255 false false false -78 9 TableName 1043 -1 3 2048 false false false -79 9 Name 1043 -1 4 255 false false false +78 9 TableName 1043 -1 3 2048 true false false +79 9 Name 1043 -1 4 255 true false false 80 9 KeyName 1043 -1 5 255 false false false -81 9 KeyType 1043 -1 6 20 false false false +81 9 KeyType 1043 -1 6 20 true false false 82 9 RefKeyUID 1043 -1 7 50 false false false -83 9 UID 1043 -1 8 50 false false false +83 9 UID 1043 -1 8 50 true false false 84 9 Position 23 4 9 10 false false false -85 9 OID 23 4 10 10 false false false -86 10 VDBName 1043 -1 1 255 false false false +85 9 OID 23 4 10 10 true false false +86 10 VDBName 1043 -1 1 255 true false false 87 10 SchemaName 1043 -1 2 255 false false false -88 10 TableName 1043 -1 3 2048 false false false -89 10 Name 1043 -1 4 255 false false false +88 10 TableName 1043 -1 3 2048 true false false +89 10 Name 1043 -1 4 255 true false false 90 10 Description 1043 -1 5 255 false false false 91 10 NameInSource 1043 -1 6 255 false false false -92 10 Type 1043 -1 7 20 false false false -93 10 IsIndexed 16 1 8 1 false false false +92 10 Type 1043 -1 7 20 true false false +93 10 IsIndexed 16 1 8 1 true false false 94 10 RefKeyUID 1043 -1 9 50 false false false -95 10 UID 1043 -1 10 50 false false false -96 10 OID 23 4 11 10 false false false -97 11 VDBName 1043 -1 1 255 false false false +95 10 UID 1043 -1 10 50 true false false +96 10 OID 23 4 11 10 true false false +97 11 VDBName 1043 -1 1 255 true false false 98 11 SchemaName 1043 -1 2 255 false false false -99 11 ProcedureName 1043 -1 3 255 false false false -100 11 Name 1043 -1 4 255 false false false -101 11 DataType 1043 -1 5 25 false false false -102 11 Position 23 4 6 10 false false false -103 11 Type 1043 -1 7 100 false false false -104 11 Optional 16 1 8 1 false false false -105 11 Precision 23 4 9 10 false false false -106 11 TypeLength 23 4 10 10 false false false -107 11 Scale 23 4 11 10 false false false -108 11 Radix 23 4 12 10 false false false -109 11 NullType 1043 -1 13 10 false false false +99 11 ProcedureName 1043 -1 3 255 true false false +100 11 Name 1043 -1 4 255 true false false +101 11 DataType 1043 -1 5 25 true false false +102 11 Position 23 4 6 10 true false false +103 11 Type 1043 -1 7 100 true false false +104 11 Optional 16 1 8 1 true false false +105 11 Precision 23 4 9 10 true false false +106 11 TypeLength 23 4 10 10 true false false +107 11 Scale 23 4 11 10 true false false +108 11 Radix 23 4 12 10 true false false +109 11 NullType 1043 -1 13 10 true false false 110 11 UID 1043 -1 14 50 false false false 111 11 Description 1043 -1 15 255 false false false -112 11 OID 23 4 16 10 false false false -113 12 VDBName 1043 -1 1 255 false false false +112 11 OID 23 4 16 10 true false false +113 12 VDBName 1043 -1 1 255 true false false 114 12 SchemaName 1043 -1 2 255 false false false -115 12 Name 1043 -1 3 255 false false false +115 12 Name 1043 -1 3 255 true false false 116 12 NameInSource 1043 -1 4 255 false false false -117 12 ReturnsResults 16 1 5 1 false false false -118 12 UID 1043 -1 6 50 false false false +117 12 ReturnsResults 16 1 5 1 true false false +118 12 UID 1043 -1 6 50 true false false 119 12 Description 1043 -1 7 255 false false false -120 12 OID 23 4 8 10 false false false -121 13 Name 1043 -1 1 255 false false false -122 13 Value 1043 -1 2 255 false false false -123 13 UID 1043 -1 3 50 false false false -124 13 OID 23 4 4 10 false false false +120 12 OID 23 4 8 10 true false false +121 13 Name 1043 -1 1 255 true false false +122 13 Value 1043 -1 2 255 true false false +123 13 UID 1043 -1 3 50 true false false +124 13 OID 23 4 4 10 true false false 125 14 PKTABLE_CAT 1043 -1 1 255 false false false 126 14 PKTABLE_SCHEM 1043 -1 2 255 false false false 127 14 PKTABLE_NAME 1043 -1 3 255 false false false @@ -141,26 +141,26 @@ 138 14 DEFERRABILITY 23 4 14 10 false false false 139 15 VDBName 1043 -1 1 255 false false false 140 15 Name 1043 -1 2 255 false false false -141 15 IsPhysical 16 1 3 1 false false false -142 15 UID 1043 -1 4 50 false false false +141 15 IsPhysical 16 1 3 1 true false false +142 15 UID 1043 -1 4 50 true false false 143 15 Description 1043 -1 5 255 false false false -144 15 PrimaryMetamodelURI 1043 -1 6 255 false false false -145 15 OID 23 4 7 10 false false false +144 15 PrimaryMetamodelURI 1043 -1 6 255 true false false +145 15 OID 23 4 7 10 true false false 146 16 VDBName 1043 -1 1 255 false false false 147 16 SchemaName 1043 -1 2 255 false false false -148 16 Name 1043 -1 3 255 false false false -149 16 Type 1043 -1 4 20 false false false +148 16 Name 1043 -1 3 255 true false false +149 16 Type 1043 -1 4 20 true false false 150 16 NameInSource 1043 -1 5 255 false false false -151 16 IsPhysical 16 1 6 1 false false false -152 16 SupportsUpdates 16 1 7 1 false false false -153 16 UID 1043 -1 8 50 false false false -154 16 Cardinality 23 4 9 10 false false false +151 16 IsPhysical 16 1 6 1 true false false +152 16 SupportsUpdates 16 1 7 1 true false false +153 16 UID 1043 -1 8 50 true false false +154 16 Cardinality 23 4 9 10 true false false 155 16 Description 1043 -1 10 255 false false false 156 16 IsSystem 16 1 11 1 false false false -157 16 IsMaterialized 16 1 12 0 false false false -158 16 OID 23 4 13 10 false false false -159 17 Name 1043 -1 1 255 false false false -160 17 Version 1043 -1 2 50 false false false +157 16 IsMaterialized 16 1 12 0 true false false +158 16 OID 23 4 13 10 true false false +159 17 Name 1043 -1 1 255 true false false +160 17 Version 1043 -1 2 50 true false false 161 18 oid 23 4 1 0 false false false 162 18 nspname 1043 -1 2 0 false false false 163 19 oid 23 4 1 0 false false false