Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @ 372:159c628527c4

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Optimize SQL query generation by eliminating "WHERE 1=1" conditions in getTables(), getColumns(), getTablePrivileges(), getColumnPrivileges() and getIndexInfo().
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Wed, 23 Sep 2020 13:02:17 +0200 (2020-09-23)
parents 67fa5c6147d7
children a229ac4e275c
comparison
equal deleted inserted replaced
371:67fa5c6147d7 372:159c628527c4
1885 } 1885 }
1886 query.append("JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" "); 1886 query.append("JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" ");
1887 if (useCommentsTable) { 1887 if (useCommentsTable) {
1888 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON t.\"id\" = cm.\"id\" "); 1888 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON t.\"id\" = cm.\"id\" ");
1889 } 1889 }
1890 query.append("WHERE 1=1"); 1890
1891 1891 boolean needWhere = true;
1892 if (catalog != null && !catalog.isEmpty()) { 1892 if (catalog != null && !catalog.isEmpty()) {
1893 // non-empty catalog selection. 1893 // non-empty catalog selection.
1894 // as we do not support catalogs this always results in no rows returned 1894 // as we do not support catalogs this always results in no rows returned
1895 query.append(" AND 1=0"); 1895 query.append("WHERE 1=0");
1896 needWhere = false;
1896 } else { 1897 } else {
1897 if (schemaPattern != null && !schemaPattern.equals("%")) { 1898 if (schemaPattern != null && !schemaPattern.equals("%")) {
1898 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern)); 1899 query.append("WHERE s.\"name\" ").append(composeMatchPart(schemaPattern));
1900 needWhere = false;
1899 } 1901 }
1900 if (tableNamePattern != null && !tableNamePattern.equals("%")) { 1902 if (tableNamePattern != null && !tableNamePattern.equals("%")) {
1901 query.append(" AND t.\"name\" ").append(composeMatchPart(tableNamePattern)); 1903 query.append(needWhere ? "WHERE" : " AND")
1904 .append(" t.\"name\" ").append(composeMatchPart(tableNamePattern));
1905 needWhere = false;
1902 } 1906 }
1903 } 1907 }
1904 1908
1905 if (types != null && types.length > 0) { 1909 if (types != null && types.length > 0) {
1906 if (preJul2015) { 1910 if (preJul2015) {
1907 query.append(") AS \"getTables\" WHERE \"TABLE_TYPE\" IN ("); 1911 query.append(") AS \"getTables\" WHERE \"TABLE_TYPE\" IN (");
1908 } else { 1912 } else {
1909 query.append(" AND tt.\"table_type_name\" IN ("); 1913 query.append(needWhere ? "WHERE" : " AND")
1914 .append(" tt.\"table_type_name\" IN (");
1910 } 1915 }
1911 for (int i = 0; i < types.length; i++) { 1916 for (int i = 0; i < types.length; i++) {
1912 if (i > 0) { 1917 if (i > 0) {
1913 query.append(", "); 1918 query.append(", ");
1914 } 1919 }
1948 throws SQLException 1953 throws SQLException
1949 { 1954 {
1950 final StringBuilder query = new StringBuilder(170); 1955 final StringBuilder query = new StringBuilder(170);
1951 query.append("SELECT \"name\" AS \"TABLE_SCHEM\", " + 1956 query.append("SELECT \"name\" AS \"TABLE_SCHEM\", " +
1952 "cast(null as char(1)) AS \"TABLE_CATALOG\" " + 1957 "cast(null as char(1)) AS \"TABLE_CATALOG\" " +
1953 "FROM \"sys\".\"schemas\""); 1958 "FROM \"sys\".\"schemas\" ");
1954 1959
1955 if (catalog != null && !catalog.isEmpty()) { 1960 if (catalog != null && !catalog.isEmpty()) {
1956 // non-empty catalog selection. 1961 // non-empty catalog selection.
1957 // as we do not support catalogs this always results in no rows returned 1962 // as we do not support catalogs this always results in no rows returned
1958 query.append(" WHERE 1=0"); 1963 query.append("WHERE 1=0");
1959 } else { 1964 } else {
1960 if (schemaPattern != null && !schemaPattern.equals("%")) { 1965 if (schemaPattern != null && !schemaPattern.equals("%")) {
1961 query.append(" WHERE \"name\" ").append(composeMatchPart(schemaPattern)); 1966 query.append("WHERE \"name\" ").append(composeMatchPart(schemaPattern));
1962 } 1967 }
1963 } 1968 }
1964 query.append(" ORDER BY \"TABLE_SCHEM\""); 1969 query.append(" ORDER BY \"TABLE_SCHEM\"");
1965 1970
1966 return executeMetaDataQuery(query.toString()); 1971 return executeMetaDataQuery(query.toString());
2132 "JOIN \"sys\".\"tables\" t ON c.\"table_id\" = t.\"id\" " + 2137 "JOIN \"sys\".\"tables\" t ON c.\"table_id\" = t.\"id\" " +
2133 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" "); 2138 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" ");
2134 if (useCommentsTable) { 2139 if (useCommentsTable) {
2135 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON c.\"id\" = cm.\"id\" "); 2140 query.append("LEFT OUTER JOIN \"sys\".\"comments\" cm ON c.\"id\" = cm.\"id\" ");
2136 } 2141 }
2137 query.append("WHERE 1=1");
2138 2142
2139 if (catalog != null && !catalog.isEmpty()) { 2143 if (catalog != null && !catalog.isEmpty()) {
2140 // non-empty catalog selection. 2144 // non-empty catalog selection.
2141 // as we do not support catalogs this always results in no rows returned 2145 // as we do not support catalogs this always results in no rows returned
2142 query.append(" AND 1=0"); 2146 query.append("WHERE 1=0");
2143 } else { 2147 } else {
2148 boolean needWhere = true;
2144 if (schemaPattern != null && !schemaPattern.equals("%")) { 2149 if (schemaPattern != null && !schemaPattern.equals("%")) {
2145 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern)); 2150 query.append("WHERE s.\"name\" ").append(composeMatchPart(schemaPattern));
2151 needWhere = false;
2146 } 2152 }
2147 if (tableNamePattern != null && !tableNamePattern.equals("%")) { 2153 if (tableNamePattern != null && !tableNamePattern.equals("%")) {
2148 query.append(" AND t.\"name\" ").append(composeMatchPart(tableNamePattern)); 2154 query.append(needWhere ? "WHERE" : " AND")
2155 .append(" t.\"name\" ").append(composeMatchPart(tableNamePattern));
2156 needWhere = false;
2149 } 2157 }
2150 if (columnNamePattern != null && !columnNamePattern.equals("%")) { 2158 if (columnNamePattern != null && !columnNamePattern.equals("%")) {
2151 query.append(" AND c.\"name\" ").append(composeMatchPart(columnNamePattern)); 2159 query.append(needWhere ? "WHERE" : " AND")
2160 .append(" c.\"name\" ").append(composeMatchPart(columnNamePattern));
2152 } 2161 }
2153 } 2162 }
2154 2163
2155 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\""); 2164 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"ORDINAL_POSITION\"");
2156 2165
2226 "JOIN \"sys\".\"auths\" grantors ON p.\"grantor\" = grantors.\"id\" " + 2235 "JOIN \"sys\".\"auths\" grantors ON p.\"grantor\" = grantors.\"id\" " +
2227 "JOIN \"sys\".\"auths\" grantees ON p.\"auth_id\" = grantees.\"id\" "); 2236 "JOIN \"sys\".\"auths\" grantees ON p.\"auth_id\" = grantees.\"id\" ");
2228 if (usePrivilege_codesTable) { 2237 if (usePrivilege_codesTable) {
2229 query.append("JOIN \"sys\".\"privilege_codes\" pc ON p.\"privileges\" = pc.\"privilege_code_id\" "); 2238 query.append("JOIN \"sys\".\"privilege_codes\" pc ON p.\"privileges\" = pc.\"privilege_code_id\" ");
2230 } 2239 }
2231 query.append("WHERE 1=1");
2232 2240
2233 if (catalog != null && !catalog.isEmpty()) { 2241 if (catalog != null && !catalog.isEmpty()) {
2234 // non-empty catalog selection. 2242 // non-empty catalog selection.
2235 // as we do not support catalogs this always results in no rows returned 2243 // as we do not support catalogs this always results in no rows returned
2236 query.append(" AND 1=0"); 2244 query.append("WHERE 1=0");
2237 } else { 2245 } else {
2246 boolean needWhere = true;
2238 if (schemaPattern != null && !schemaPattern.equals("%")) { 2247 if (schemaPattern != null && !schemaPattern.equals("%")) {
2239 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern)); 2248 query.append("WHERE s.\"name\" ").append(composeMatchPart(schemaPattern));
2249 needWhere = false;
2240 } 2250 }
2241 if (tableNamePattern != null && !tableNamePattern.equals("%")) { 2251 if (tableNamePattern != null && !tableNamePattern.equals("%")) {
2242 query.append(" AND t.\"name\" ").append(composeMatchPart(tableNamePattern)); 2252 query.append(needWhere ? "WHERE" : " AND")
2253 .append(" t.\"name\" ").append(composeMatchPart(tableNamePattern));
2254 needWhere = false;
2243 } 2255 }
2244 if (columnNamePattern != null && !columnNamePattern.equals("%")) { 2256 if (columnNamePattern != null && !columnNamePattern.equals("%")) {
2245 query.append(" AND c.\"name\" ").append(composeMatchPart(columnNamePattern)); 2257 query.append(needWhere ? "WHERE" : " AND")
2258 .append(" c.\"name\" ").append(composeMatchPart(columnNamePattern));
2246 } 2259 }
2247 } 2260 }
2248 2261
2249 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\", \"PRIVILEGE\""); 2262 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"COLUMN_NAME\", \"PRIVILEGE\"");
2250 2263
2315 "JOIN \"sys\".\"auths\" grantors ON p.\"grantor\" = grantors.\"id\" " + 2328 "JOIN \"sys\".\"auths\" grantors ON p.\"grantor\" = grantors.\"id\" " +
2316 "JOIN \"sys\".\"auths\" grantees ON p.\"auth_id\" = grantees.\"id\" "); 2329 "JOIN \"sys\".\"auths\" grantees ON p.\"auth_id\" = grantees.\"id\" ");
2317 if (usePrivilege_codesTable) { 2330 if (usePrivilege_codesTable) {
2318 query.append("JOIN \"sys\".\"privilege_codes\" pc ON p.\"privileges\" = pc.\"privilege_code_id\" "); 2331 query.append("JOIN \"sys\".\"privilege_codes\" pc ON p.\"privileges\" = pc.\"privilege_code_id\" ");
2319 } 2332 }
2320 query.append("WHERE 1=1");
2321 2333
2322 if (catalog != null && !catalog.isEmpty()) { 2334 if (catalog != null && !catalog.isEmpty()) {
2323 // non-empty catalog selection. 2335 // non-empty catalog selection.
2324 // as we do not support catalogs this always results in no rows returned 2336 // as we do not support catalogs this always results in no rows returned
2325 query.append(" AND 1=0"); 2337 query.append("WHERE 1=0");
2326 } else { 2338 } else {
2339 boolean needWhere = true;
2327 if (schemaPattern != null && !schemaPattern.equals("%")) { 2340 if (schemaPattern != null && !schemaPattern.equals("%")) {
2328 query.append(" AND s.\"name\" ").append(composeMatchPart(schemaPattern)); 2341 query.append("WHERE s.\"name\" ").append(composeMatchPart(schemaPattern));
2342 needWhere = false;
2329 } 2343 }
2330 if (tableNamePattern != null && !tableNamePattern.equals("%")) { 2344 if (tableNamePattern != null && !tableNamePattern.equals("%")) {
2331 query.append(" AND t.\"name\" ").append(composeMatchPart(tableNamePattern)); 2345 query.append(needWhere ? "WHERE" : " AND")
2346 .append(" t.\"name\" ").append(composeMatchPart(tableNamePattern));
2332 } 2347 }
2333 } 2348 }
2334 2349
2335 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"PRIVILEGE\""); 2350 query.append(" ORDER BY \"TABLE_SCHEM\", \"TABLE_NAME\", \"PRIVILEGE\"");
2336 2351
3045 "FROM \"sys\".\"idxs\" i " + 3060 "FROM \"sys\".\"idxs\" i " +
3046 "JOIN \"sys\".\"tables\" t ON i.\"table_id\" = t.\"id\" " + 3061 "JOIN \"sys\".\"tables\" t ON i.\"table_id\" = t.\"id\" " +
3047 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " + 3062 "JOIN \"sys\".\"schemas\" s ON t.\"schema_id\" = s.\"id\" " +
3048 "JOIN \"sys\".\"objects\" o ON i.\"id\" = o.\"id\" " + 3063 "JOIN \"sys\".\"objects\" o ON i.\"id\" = o.\"id\" " +
3049 "JOIN \"sys\".\"columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " + 3064 "JOIN \"sys\".\"columns\" c ON (t.\"id\" = c.\"table_id\" AND o.\"name\" = c.\"name\") " +
3050 "LEFT OUTER JOIN \"sys\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0,1)) " + // primary (0) and unique keys (1) only 3065 "LEFT OUTER JOIN \"sys\".\"keys\" k ON (i.\"name\" = k.\"name\" AND i.\"table_id\" = k.\"table_id\" AND k.\"type\" IN (0,1)) "); // primary (0) and unique keys (1) only
3051 "WHERE 1=1");
3052 3066
3053 if (catalog != null && !catalog.isEmpty()) { 3067 if (catalog != null && !catalog.isEmpty()) {
3054 // non-empty catalog selection. 3068 // non-empty catalog selection.
3055 // as we do not support catalogs this always results in no rows returned 3069 // as we do not support catalogs this always results in no rows returned
3056 query.append(" AND 1=0"); 3070 query.append("WHERE 1=0");
3057 } else { 3071 } else {
3072 boolean needWhere = true;
3058 if (schema != null && !schema.equals("%")) { 3073 if (schema != null && !schema.equals("%")) {
3059 query.append(" AND s.\"name\" ").append(composeMatchPart(schema)); 3074 query.append("WHERE s.\"name\" ").append(composeMatchPart(schema));
3075 needWhere = false;
3060 } 3076 }
3061 if (table != null && !table.equals("%")) { 3077 if (table != null && !table.equals("%")) {
3062 query.append(" AND t.\"name\" ").append(composeMatchPart(table)); 3078 query.append(needWhere ? "WHERE" : " AND")
3079 .append(" t.\"name\" ").append(composeMatchPart(table));
3080 needWhere = false;
3063 } 3081 }
3064 if (unique) { 3082 if (unique) {
3065 query.append(" AND k.\"name\" IS NOT NULL"); 3083 query.append(needWhere ? "WHERE" : " AND")
3084 .append(" k.\"name\" IS NOT NULL");
3066 } 3085 }
3067 } 3086 }
3068 3087
3069 query.append(" ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\""); 3088 query.append(" ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\"");
3070 3089
3921 3940
3922 return executeMetaDataQuery(query.toString()); 3941 return executeMetaDataQuery(query.toString());
3923 } 3942 }
3924 3943
3925 //== 1.7 methods (JDBC 4.1) 3944 //== 1.7 methods (JDBC 4.1)
3926 3945
3927 /** 3946 /**
3928 * Retrieves a description of the pseudo or hidden columns available 3947 * Retrieves a description of the pseudo or hidden columns available
3929 * in a given table within the specified catalog and schema. Pseudo 3948 * in a given table within the specified catalog and schema. Pseudo
3930 * or hidden columns may not always be stored within a table and are 3949 * or hidden columns may not always be stored within a table and are
3931 * not visible in a ResultSet unless they are specified in the 3950 * not visible in a ResultSet unless they are specified in the