Mercurial > hg > monetdb-java

comparison src/main/java/nl/cwi/monetdb/jdbc/MonetDatabaseMetaData.java @ 332:e8962bdaa206

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved utility methods and associated caches from DatabaseMetaData to Connection object. This allows the cached values to be shared across multiple DatabaseMetaData objects and thereby reduce the number of queries sent to the server and the number of caches. For instance SQuirreL instantiates many DatabaseMetaData objects and each time the caches needed to be refilled by getting values from the server. This is now optimized. Also made sure that in MonetConnection the internal queryTempl[] and commandTempl[] array entries never contain null. This eleviates the need to test on null when composing the marked-up command string which is sent to the server via mapi.
author Martin van Dinther <martin.van.dinther@monetdbsolutions.com>
date Thu, 19 Sep 2019 16:40:33 +0200 (2019-09-19)
parents 6ed8f5b1f9ed
children 98018e38994b
comparison
equal deleted inserted replaced
331:6ed8f5b1f9ed 332:e8962bdaa206
20 /** 20 /**
21 * A DatabaseMetaData object suitable for the MonetDB database. 21 * A DatabaseMetaData object suitable for the MonetDB database.
22 * 22 *
23 * @author Fabian Groffen 23 * @author Fabian Groffen
24 * @author Martin van Dinther 24 * @author Martin van Dinther
25 * @version 0.8 25 * @version 0.9
26 */ 26 */
27 public class MonetDatabaseMetaData 27 public class MonetDatabaseMetaData
28 extends MonetWrapper 28 extends MonetWrapper
29 implements DatabaseMetaData 29 implements DatabaseMetaData
30 { 30 {
31 private final Connection con; 31 private final MonetConnection con;
32 32
33 // Internal cache for 3 server environment values 33 public MonetDatabaseMetaData(final MonetConnection parent) {
34 private String env_current_user;
35 private String env_monet_version;
36 private String env_max_clients;
37
38 public MonetDatabaseMetaData(final Connection parent) {
39 con = parent; 34 con = parent;
40 }
41
42 /**
43 * Utility method to fetch some server environment values combined in one query for efficiency.
44 * We currently fetch the env values of: current_user, monet_version and max_clients.
45 * We cache them locally such that we do not need to query the server again and again.
46 */
47 private synchronized void getEnvValues() throws SQLException {
48 Statement st = null;
49 ResultSet rs = null;
50 try {
51 st = con.createStatement();
52 rs = st.executeQuery(
53 "SELECT \"name\", \"value\" FROM \"sys\".\"env\"()" +
54 " WHERE \"name\" IN ('monet_version', 'max_clients')" +
55 " UNION SELECT 'current_user' as \"name\", current_user as \"value\"");
56 if (rs != null) {
57 while (rs.next()) {
58 String prop = rs.getString("name");
59 String value = rs.getString("value");
60 if ("current_user".equals(prop)) {
61 env_current_user = value;
62 } else
63 if ("monet_version".equals(prop)) {
64 env_monet_version = value;
65 } else
66 if ("max_clients".equals(prop)) {
67 env_max_clients = value;
68 }
69 }
70 }
71 /* do not catch SQLException here, as we want to know it when it fails */
72 } finally {
73 MonetConnection.closeResultsetStatement(rs, st);
74 }
75 // for debug: System.out.println("Read: env_current_user: " + env_current_user + " env_monet_version: " + env_monet_version + " env_max_clients: " + env_max_clients);
76 }
77
78 /**
79 * Internal utility method to create a Statement object, execute a query and return the ResulSet object which allows scrolling.
80 * As the Statement object is created internally (the caller does not see it and thus can not close it),
81 * we set it to close (and free server resources) when the ResultSet object is closed by the caller.
82 */
83 private ResultSet executeMetaDataQuery(final String query) throws SQLException {
84 final Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
85 ResultSet rs = null;
86 if (stmt != null) {
87 // for debug: System.out.println("SQL (len " + query.length() + "): " + query);
88 rs = stmt.executeQuery(query);
89 if (rs != null) {
90 /* we want the statement object to be closed also when the resultset is closed by the caller */
91 stmt.closeOnCompletion();
92 } else {
93 /* failed to produce a resultset, so release resources for created statement object now */
94 stmt.close();
95 }
96 }
97 return rs;
98 } 35 }
99 36
100 /** 37 /**
101 * Retrieves whether the current user can call all the procedures 38 * Retrieves whether the current user can call all the procedures
102 * returned by the method getProcedures. 39 * returned by the method getProcedures.
127 * @return a reconstructed connection string 64 * @return a reconstructed connection string
128 * @throws SQLException if a database access error occurs 65 * @throws SQLException if a database access error occurs
129 */ 66 */
130 @Override 67 @Override
131 public String getURL() throws SQLException { 68 public String getURL() throws SQLException {
132 return ((MonetConnection)con).getJDBCURL(); 69 return con.getJDBCURL();
133 } 70 }
134 71
135 /** 72 /**
136 * What is our user name as known to the database? 73 * What is our user name as known to the database?
137 * 74 *
138 * @return sql user 75 * @return sql user
139 * @throws SQLException if a database access error occurs 76 * @throws SQLException if a database access error occurs
140 */ 77 */
141 @Override 78 @Override
142 public String getUserName() throws SQLException { 79 public String getUserName() throws SQLException {
143 if (env_current_user == null) 80 return con.getUserName();
144 getEnvValues();
145 return env_current_user;
146 } 81 }
147 82
148 /** 83 /**
149 * Is the database in read-only mode? 84 * Is the database in read-only mode?
150 * 85 *
221 * @return the mserver5 version number string 156 * @return the mserver5 version number string
222 * @throws SQLException if a database access error occurs 157 * @throws SQLException if a database access error occurs
223 */ 158 */
224 @Override 159 @Override
225 public String getDatabaseProductVersion() throws SQLException { 160 public String getDatabaseProductVersion() throws SQLException {
226 if (env_monet_version == null) 161 return con.getDatabaseProductVersion();
227 getEnvValues();
228 if (env_monet_version != null)
229 return env_monet_version;
230 // always return a valid String to prevent NPE in getTables() and getTableTypes()
231 return "";
232 } 162 }
233 163
234 /** 164 /**
235 * What is the name of this JDBC driver? 165 * What is the name of this JDBC driver?
236 * 166 *
1416 public int getMaxColumnsInTable() { 1346 public int getMaxColumnsInTable() {
1417 return 100*1000; // soft limit it to 100 thousand 1347 return 100*1000; // soft limit it to 100 thousand
1418 } 1348 }
1419 1349
1420 /** 1350 /**
1421 * How many active connections can we have at a time to this 1351 * Retrieves the maximum number of concurrent connections to this database that are possible.
1422 * database? Well, since it depends on Mserver, which just listens 1352 *
1423 * for new connections and creates a new thread for each connection, 1353 * @return the maximum number of active connections possible at one time; a result of zero means that there is no limit or the limit is not known
1424 * this number can be very high, and theoretically till the system
1425 * runs out of resources. However, knowing MonetDB is knowing that you
1426 * should handle it a little bit with care, so I give a very minimalistic
1427 * number here.
1428 *
1429 * @return the maximum number of connections
1430 */ 1354 */
1431 @Override 1355 @Override
1432 public int getMaxConnections() throws SQLException { 1356 public int getMaxConnections() throws SQLException {
1433 if (env_max_clients == null) 1357 return con.getMaxConnections();
1434 getEnvValues();
1435
1436 int max_clients = 16;
1437 if (env_max_clients != null) {
1438 try {
1439 max_clients = Integer.parseInt(env_max_clients);
1440 } catch (NumberFormatException nfe) { /* ignore */ }
1441 }
1442 return max_clients;
1443 } 1358 }
1444 1359
1445 /** 1360 /**
1446 * What is the maximum cursor name length 1361 * What is the maximum cursor name length
1447 * Actually we do not do named cursors, so I keep the value small as 1362 * Actually we do not do named cursors, so I keep the value small as
1711 final String catalog, 1626 final String catalog,
1712 final String schemaPattern, 1627 final String schemaPattern,
1713 final String procedureNamePattern 1628 final String procedureNamePattern
1714 ) throws SQLException 1629 ) throws SQLException
1715 { 1630 {
1716 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); 1631 final boolean useCommentsTable = con.commentsTableExists();
1717 final StringBuilder query = new StringBuilder(980); 1632 final StringBuilder query = new StringBuilder(980);
1718 query.append("SELECT cast(null as char(1)) AS \"PROCEDURE_CAT\", " + 1633 query.append("SELECT cast(null as char(1)) AS \"PROCEDURE_CAT\", " +
1719 "s.\"name\" AS \"PROCEDURE_SCHEM\", " + 1634 "s.\"name\" AS \"PROCEDURE_SCHEM\", " +
1720 "f.\"name\" AS \"PROCEDURE_NAME\", " + 1635 "f.\"name\" AS \"PROCEDURE_NAME\", " +
1721 "cast(null as char(1)) AS \"Field4\", " + 1636 "cast(null as char(1)) AS \"Field4\", " +
1926 // as of Jul2015 release we also have a new table: sys.table_types with names for the new table types 1841 // as of Jul2015 release we also have a new table: sys.table_types with names for the new table types
1927 // for correct behavior we need to know if the server is using the old (pre Jul2015) or new sys.tables.type values 1842 // for correct behavior we need to know if the server is using the old (pre Jul2015) or new sys.tables.type values
1928 final boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0); 1843 final boolean preJul2015 = ("11.19.15".compareTo(getDatabaseProductVersion()) >= 0);
1929 // for debug: System.out.println("getDatabaseProductVersion() is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015); 1844 // for debug: System.out.println("getDatabaseProductVersion() is " + getDatabaseProductVersion() + " preJul2015 is " + preJul2015);
1930 1845
1931 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); 1846 final boolean useCommentsTable = con.commentsTableExists();
1932 final StringBuilder query = new StringBuilder(1600); 1847 final StringBuilder query = new StringBuilder(1600);
1933 if (preJul2015 && types != null && types.length > 0) { 1848 if (preJul2015 && types != null && types.length > 0) {
1934 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM 1849 // we need to filter on the constructed "TABLE_TYPE" expression, this is only possible when we use a subquery in the FROM
1935 query.append("SELECT * FROM ("); 1850 query.append("SELECT * FROM (");
1936 } 1851 }
2175 final String schemaPattern, 2090 final String schemaPattern,
2176 final String tableNamePattern, 2091 final String tableNamePattern,
2177 final String columnNamePattern 2092 final String columnNamePattern
2178 ) throws SQLException 2093 ) throws SQLException
2179 { 2094 {
2180 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); 2095 final boolean useCommentsTable = con.commentsTableExists();
2181 final StringBuilder query = new StringBuilder(2450); 2096 final StringBuilder query = new StringBuilder(2450);
2182 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 2097 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2183 "s.\"name\" AS \"TABLE_SCHEM\", " + 2098 "s.\"name\" AS \"TABLE_SCHEM\", " +
2184 "t.\"name\" AS \"TABLE_NAME\", " + 2099 "t.\"name\" AS \"TABLE_NAME\", " +
2185 "c.\"name\" AS \"COLUMN_NAME\", " + 2100 "c.\"name\" AS \"COLUMN_NAME\", " +
2272 final String schemaPattern, 2187 final String schemaPattern,
2273 final String tableNamePattern, 2188 final String tableNamePattern,
2274 final String columnNamePattern 2189 final String columnNamePattern
2275 ) throws SQLException 2190 ) throws SQLException
2276 { 2191 {
2277 final boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists(); 2192 final boolean usePrivilege_codesTable = con.privilege_codesTableExists();
2278 final StringBuilder query = new StringBuilder(1100); 2193 final StringBuilder query = new StringBuilder(1100);
2279 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 2194 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2280 "s.\"name\" AS \"TABLE_SCHEM\", " + 2195 "s.\"name\" AS \"TABLE_SCHEM\", " +
2281 "t.\"name\" AS \"TABLE_NAME\", " + 2196 "t.\"name\" AS \"TABLE_NAME\", " +
2282 "c.\"name\" AS \"COLUMN_NAME\", " + 2197 "c.\"name\" AS \"COLUMN_NAME\", " +
2363 final String catalog, 2278 final String catalog,
2364 final String schemaPattern, 2279 final String schemaPattern,
2365 final String tableNamePattern 2280 final String tableNamePattern
2366 ) throws SQLException 2281 ) throws SQLException
2367 { 2282 {
2368 final boolean usePrivilege_codesTable = ((MonetConnection)con).privilege_codesTableExists(); 2283 final boolean usePrivilege_codesTable = con.privilege_codesTableExists();
2369 final StringBuilder query = new StringBuilder(1000); 2284 final StringBuilder query = new StringBuilder(1000);
2370 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " + 2285 query.append("SELECT cast(null as char(1)) AS \"TABLE_CAT\", " +
2371 "s.\"name\" AS \"TABLE_SCHEM\", " + 2286 "s.\"name\" AS \"TABLE_SCHEM\", " +
2372 "t.\"name\" AS \"TABLE_NAME\", " + 2287 "t.\"name\" AS \"TABLE_NAME\", " +
2373 "grantors.\"name\" AS \"GRANTOR\", " + 2288 "grantors.\"name\" AS \"GRANTOR\", " +
3617 * @return the underlying database's major version 3532 * @return the underlying database's major version
3618 * @throws SQLException if a database access error occurs 3533 * @throws SQLException if a database access error occurs
3619 */ 3534 */
3620 @Override 3535 @Override
3621 public int getDatabaseMajorVersion() throws SQLException { 3536 public int getDatabaseMajorVersion() throws SQLException {
3622 if (env_monet_version == null) 3537 return con.getDatabaseMajorVersion();
3623 getEnvValues();
3624 int major = 0;
3625 if (env_monet_version != null) {
3626 try {
3627 int start = env_monet_version.indexOf('.');
3628 major = Integer.parseInt((start >= 0) ? env_monet_version.substring(0, start) : env_monet_version);
3629 } catch (NumberFormatException e) {
3630 // ignore
3631 }
3632 }
3633 return major;
3634 } 3538 }
3635 3539
3636 /** 3540 /**
3637 * Retrieves the minor version number of the underlying database. 3541 * Retrieves the minor version number of the underlying database.
3638 * 3542 *
3639 * @return underlying database's minor version 3543 * @return underlying database's minor version
3640 * @throws SQLException if a database access error occurs 3544 * @throws SQLException if a database access error occurs
3641 */ 3545 */
3642 @Override 3546 @Override
3643 public int getDatabaseMinorVersion() throws SQLException { 3547 public int getDatabaseMinorVersion() throws SQLException {
3644 if (env_monet_version == null) 3548 return con.getDatabaseMinorVersion();
3645 getEnvValues();
3646 int minor = 0;
3647 if (env_monet_version != null) {
3648 try {
3649 int start = env_monet_version.indexOf('.');
3650 if (start >= 0) {
3651 start++;
3652 int end = env_monet_version.indexOf('.', start);
3653 minor = Integer.parseInt((end > 0) ? env_monet_version.substring(start, end) : env_monet_version.substring(start));
3654 }
3655 } catch (NumberFormatException e) {
3656 // ignore
3657 }
3658 }
3659 return minor;
3660 } 3549 }
3661 3550
3662 /** 3551 /**
3663 * Retrieves the major JDBC version number for this driver. 3552 * Retrieves the major JDBC version number for this driver.
3664 * 3553 *
3866 final String catalog, 3755 final String catalog,
3867 final String schemaPattern, 3756 final String schemaPattern,
3868 final String functionNamePattern 3757 final String functionNamePattern
3869 ) throws SQLException 3758 ) throws SQLException
3870 { 3759 {
3871 final boolean useCommentsTable = ((MonetConnection)con).commentsTableExists(); 3760 final boolean useCommentsTable = con.commentsTableExists();
3872 final StringBuilder query = new StringBuilder(800); 3761 final StringBuilder query = new StringBuilder(800);
3873 query.append("SELECT cast(null as char(1)) AS \"FUNCTION_CAT\", " + 3762 query.append("SELECT cast(null as char(1)) AS \"FUNCTION_CAT\", " +
3874 "s.\"name\" AS \"FUNCTION_SCHEM\", " + 3763 "s.\"name\" AS \"FUNCTION_SCHEM\", " +
3875 "f.\"name\" AS \"FUNCTION_NAME\", ") 3764 "f.\"name\" AS \"FUNCTION_NAME\", ")
3876 .append(useCommentsTable ? "COALESCE(cm.\"remark\", cast(f.\"func\" as varchar(9999)))" : "cast(f.\"func\" as varchar(9999))").append(" AS \"REMARKS\", " + 3765 .append(useCommentsTable ? "COALESCE(cm.\"remark\", cast(f.\"func\" as varchar(9999)))" : "cast(f.\"func\" as varchar(9999))").append(" AS \"REMARKS\", " +
4136 } 4025 }
4137 4026
4138 //== end methods interface DatabaseMetaData 4027 //== end methods interface DatabaseMetaData
4139 4028
4140 4029
4141 //== this is a helper method which does not belong to the interface 4030 //== internal helper methods which do not belong to the JDBC interface
4031
4032 /**
4033 * Internal utility method to create a Statement object, execute a query and return the ResulSet object which allows scrolling.
4034 * As the Statement object is created internally (the caller does not see it and thus can not close it),
4035 * we set it to close (and free server resources) when the ResultSet object is closed by the caller.
4036 */
4037 private ResultSet executeMetaDataQuery(final String query) throws SQLException {
4038 final Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
4039 ResultSet rs = null;
4040 if (stmt != null) {
4041 // for debug: System.out.println("SQL (len " + query.length() + "): " + query);
4042 rs = stmt.executeQuery(query);
4043 if (rs != null) {
4044 /* we want the statement object to be closed also when the resultset is closed by the caller */
4045 stmt.closeOnCompletion();
4046 } else {
4047 /* failed to produce a resultset, so release resources for created statement object now */
4048 stmt.close();
4049 }
4050 }
4051 return rs;
4052 }
4142 4053
4143 /** 4054 /**
4144 * Returns a SQL match part string where depending on the input value we 4055 * Returns a SQL match part string where depending on the input value we
4145 * compose an exact match (use =) or match with wildcards (use LIKE) or IS NULL 4056 * compose an exact match (use =) or match with wildcards (use LIKE) or IS NULL
4146 * 4057 *