博主最近写了一个大数据的项目,数据库采用的是greenplum,他是基于postgresql的,里面有几千张数据库表,每张表都有上亿的数据,在用Hibernat启动时连接postgresql的时候很慢,大概需要半小时的时间,这让博主怎么受的了,就 一步一步追踪代码,最后发现问题出在postgresql驱动的PgDatabaseMetaData类里面,他的源码是这样的:
/*
* Copyright (c) 2004, PostgreSQL Global Development Group
* See the LICENSE file in the project root for more information.
*/
package org.postgresql.jdbc;
import org.postgresql.core.BaseStatement;
import org.postgresql.core.Field;
import org.postgresql.core.Oid;
import org.postgresql.core.ServerVersion;
import org.postgresql.util.GT;
import org.postgresql.util.JdbcBlackHole;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.RowIdLifetime;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
public class PgDatabaseMetaData implements DatabaseMetaData {
public PgDatabaseMetaData(PgConnection conn) {
this.connection = conn;
}
private static final String keywords = "abort,acl,add,aggregate,append,archive,"
+ "arch_store,backward,binary,boolean,change,cluster,"
+ "copy,database,delimiter,delimiters,do,extend,"
+ "explain,forward,heavy,index,inherits,isnull,"
+ "light,listen,load,merge,nothing,notify,"
+ "notnull,oids,purge,rename,replace,retrieve,"
+ "returns,rule,recipe,setof,stdin,stdout,store,"
+ "vacuum,verbose,version";
protected final PgConnection connection; // The connection association
private int NAMEDATALEN = 0; // length for name datatype
private int INDEX_MAX_KEYS = 0; // maximum number of keys in an index.
public ResultSet getTypeInfo() throws SQLException {
Field f[] = new Field[18];
List<byte[][]> v = new ArrayList<byte[][]>(); // The new ResultSet tuple stuff
f[0] = new Field("TYPE_NAME", Oid.VARCHAR);
f[1] = new Field("DATA_TYPE", Oid.INT2);
f[2] = new Field("PRECISION", Oid.INT4);
f[3] = new Field("LITERAL_PREFIX", Oid.VARCHAR);
f[4] = new Field("LITERAL_SUFFIX", Oid.VARCHAR);
f[5] = new Field("CREATE_PARAMS", Oid.VARCHAR);
f[6] = new Field("NULLABLE", Oid.INT2);
f[7] = new Field("CASE_SENSITIVE", Oid.BOOL);
f[8] = new Field("SEARCHABLE", Oid.INT2);
f[9] = new Field("UNSIGNED_ATTRIBUTE", Oid.BOOL);
f[10] = new Field("FIXED_PREC_SCALE", Oid.BOOL);
f[11] = new Field("AUTO_INCREMENT", Oid.BOOL);
f[12] = new Field("LOCAL_TYPE_NAME", Oid.VARCHAR);
f[13] = new Field("MINIMUM_SCALE", Oid.INT2);
f[14] = new Field("MAXIMUM_SCALE", Oid.INT2);
f[15] = new Field("SQL_DATA_TYPE", Oid.INT4);
f[16] = new Field("SQL_DATETIME_SUB", Oid.INT4);
f[17] = new Field("NUM_PREC_RADIX", Oid.INT4);
String sql;
sql = "SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
+ " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
+ " WHERE n.nspname != 'pg_toast'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// cache some results, this will keep memory usage down, and speed
// things up a little.
byte bZero[] = connection.encodeString("0");
byte b10[] = connection.encodeString("10");
byte bf[] = connection.encodeString("f");
byte bt[] = connection.encodeString("t");
byte bliteral[] = connection.encodeString("'");
byte bNullable[] =
connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.typeNullable));
byte bSearchable[] =
connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.typeSearchable));
while (rs.next()) {
byte[][] tuple = new byte[18][];
String typname = rs.getString(1);
int typeOid = (int) rs.getLong(2);
tuple[0] = connection.encodeString(typname);
int sqlType = connection.getTypeInfo().getSQLType(typname);//这句话做了一次查询,也就是每次都要查询
tuple[1] =
connection.encodeString(Integer.toString(sqlType));
tuple[2] = connection
.encodeString(Integer.toString(connection.getTypeInfo().getMaximumPrecision(typeOid)));
// Using requiresQuoting(oid) would might trigger select statements that might fail with NPE
// if oid in question is being dropped.
// requiresQuotingSqlType is not bulletproof, however, it solves the most visible NPE.
if (connection.getTypeInfo().requiresQuotingSqlType(sqlType)) {
tuple[3] = bliteral;
tuple[4] = bliteral;
}
tuple[6] = bNullable; // all types can be null
tuple[7] = connection.getTypeInfo().isCaseSensitive(typeOid) ? bt : bf;
tuple[8] = bSearchable; // any thing can be used in the WHERE clause
tuple[9] = connection.getTypeInfo().isSigned(typeOid) ? bf : bt;
tuple[10] = bf; // false for now - must handle money
tuple[11] = bf; // false - it isn't autoincrement
tuple[13] = bZero; // min scale is zero
// only numeric can supports a scale.
tuple[14] = (typeOid == Oid.NUMERIC) ? connection.encodeString("1000") : bZero;
// 12 - LOCAL_TYPE_NAME is null
// 15 & 16 are unused so we return null
tuple[17] = b10; // everything is base 10
v.add(tuple);
// add pseudo-type serial, bigserial
if (typname.equals("int4")) {
byte[][] tuple1 = tuple.clone();
tuple1[0] = connection.encodeString("serial");
tuple1[11] = bt;
v.add(tuple1);
} else if (typname.equals("int8")) {
byte[][] tuple1 = tuple.clone();
tuple1[0] = connection.encodeString("bigserial");
tuple1[11] = bt;
v.add(tuple1);
}
}
rs.close();
stmt.close();
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
}
其他我们不用看,我们只需要找到public ResultSet getTypeInfo()方法,里面他在初始化的时候,执行了一条sql语句: sql = "SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
+ " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
+ " WHERE n.nspname != 'pg_toast'";
这个sql语句的作用就是查询出所有的类型和数据库表,将其放到缓存里面,这是为了提高查询性能做的,但是博主的数据库很庞大,所以他查询出来有接近7000条数据,看似也不大,但是注意看代码里面他循环了这7000条数据,在循环里面又做了一次查询,那继续跟踪,我们找到了getSQLType()方法所在类的,他的源码是这样的:
/*
* Copyright (c) 2005, PostgreSQL Global Development Group
* See the LICENSE file in the project root for more information.
*/
package org.postgresql.jdbc;
import org.postgresql.core.BaseConnection;
import org.postgresql.core.BaseStatement;
import org.postgresql.core.Oid;
import org.postgresql.core.QueryExecutor;
import org.postgresql.core.ServerVersion;
import org.postgresql.core.TypeInfo;
import org.postgresql.util.GT;
import org.postgresql.util.PGobject;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
public class TypeInfoCache implements TypeInfo {
// pgname (String) -> java.sql.Types (Integer)
private Map<String, Integer> _pgNameToSQLType;
// pgname (String) -> java class name (String)
// ie "text" -> "java.lang.String"
private Map<String, String> _pgNameToJavaClass;
// oid (Integer) -> pgname (String)
private Map<Integer, String> _oidToPgName;
// pgname (String) -> oid (Integer)
private Map<String, Integer> _pgNameToOid;
// pgname (String) -> extension pgobject (Class)
private Map<String, Class<? extends PGobject>> _pgNameToPgObject;
// type array oid -> base type's oid
private Map<Integer, Integer> _pgArrayToPgType;
// array type oid -> base type array element delimiter
private Map<Integer, Character> _arrayOidToDelimiter;
private BaseConnection _conn;
private final int _unknownLength;
private PreparedStatement _getOidStatementSimple;
private PreparedStatement _getOidStatementComplexNonArray;
private PreparedStatement _getOidStatementComplexArray;
private PreparedStatement _getNameStatement;
private PreparedStatement _getArrayElementOidStatement;
private PreparedStatement _getArrayDelimiterStatement;
private PreparedStatement _getTypeInfoStatement;
// basic pg types info:
// 0 - type name
// 1 - type oid
// 2 - sql type
// 3 - java class
// 4 - array type oid
private static final Object types[][] = {
{"int2", Oid.INT2, Types.SMALLINT, "java.lang.Integer", Oid.INT2_ARRAY},
{"int4", Oid.INT4, Types.INTEGER, "java.lang.Integer", Oid.INT4_ARRAY},
{"oid", Oid.OID, Types.BIGINT, "java.lang.Long", Oid.OID_ARRAY},
{"int8", Oid.INT8, Types.BIGINT, "java.lang.Long", Oid.INT8_ARRAY},
{"money", Oid.MONEY, Types.DOUBLE, "java.lang.Double", Oid.MONEY_ARRAY},
{"numeric", Oid.NUMERIC, Types.NUMERIC, "java.math.BigDecimal", Oid.NUMERIC_ARRAY},
{"float4", Oid.FLOAT4, Types.REAL, "java.lang.Float", Oid.FLOAT4_ARRAY},
{"float8", Oid.FLOAT8, Types.DOUBLE, "java.lang.Double", Oid.FLOAT8_ARRAY},
{"char", Oid.CHAR, Types.CHAR, "java.lang.String", Oid.CHAR_ARRAY},
{"bpchar", Oid.BPCHAR, Types.CHAR, "java.lang.String", Oid.BPCHAR_ARRAY},
{"varchar", Oid.VARCHAR, Types.VARCHAR, "java.lang.String", Oid.VARCHAR_ARRAY},
{"text", Oid.TEXT, Types.VARCHAR, "java.lang.String", Oid.TEXT_ARRAY},
{"name", Oid.NAME, Types.VARCHAR, "java.lang.String", Oid.NAME_ARRAY},
{"bytea", Oid.BYTEA, Types.BINARY, "[B", Oid.BYTEA_ARRAY},
{"bool", Oid.BOOL, Types.BIT, "java.lang.Boolean", Oid.BOOL_ARRAY},
{"bit", Oid.BIT, Types.BIT, "java.lang.Boolean", Oid.BIT_ARRAY},
{"date", Oid.DATE, Types.DATE, "java.sql.Date", Oid.DATE_ARRAY},
{"time", Oid.TIME, Types.TIME, "java.sql.Time", Oid.TIME_ARRAY},
{"timetz", Oid.TIMETZ, Types.TIME, "java.sql.Time", Oid.TIMETZ_ARRAY},
{"timestamp", Oid.TIMESTAMP, Types.TIMESTAMP, "java.sql.Timestamp", Oid.TIMESTAMP_ARRAY},
{"timestamptz", Oid.TIMESTAMPTZ, Types.TIMESTAMP, "java.sql.Timestamp",
Oid.TIMESTAMPTZ_ARRAY},
//JCP! if mvn.project.property.postgresql.jdbc.spec >= "JDBC4.2"
//JCP> {"refcursor", Oid.REF_CURSOR, Types.REF_CURSOR, "java.sql.ResultSet", Oid.REF_CURSOR_ARRAY},
//JCP! endif
{"json", Oid.JSON, Types.OTHER, "org.postgresql.util.PGobject", Oid.JSON_ARRAY},
{"point", Oid.POINT, Types.OTHER, "org.postgresql.geometric.PGpoint", Oid.POINT_ARRAY}
};
/**
* PG maps several alias to real type names. When we do queries against pg_catalog, we must use
* the real type, not an alias, so use this mapping.
*/
private final static HashMap<String, String> typeAliases;
static {
typeAliases = new HashMap<String, String>();
typeAliases.put("smallint", "int2");
typeAliases.put("integer", "int4");
typeAliases.put("int", "int4");
typeAliases.put("bigint", "int8");
typeAliases.put("float", "float8");
typeAliases.put("boolean", "bool");
typeAliases.put("decimal", "numeric");
}
public TypeInfoCache(BaseConnection conn, int unknownLength) {
_conn = conn;
_unknownLength = unknownLength;
_oidToPgName = new HashMap<Integer, String>();
_pgNameToOid = new HashMap<String, Integer>();
_pgNameToJavaClass = new HashMap<String, String>();
_pgNameToPgObject = new HashMap<String, Class<? extends PGobject>>();
_pgArrayToPgType = new HashMap<Integer, Integer>();
_arrayOidToDelimiter = new HashMap<Integer, Character>();
// needs to be synchronized because the iterator is returned
// from getPGTypeNamesWithSQLTypes()
_pgNameToSQLType = Collections.synchronizedMap(new HashMap<String, Integer>());
for (Object[] type : types) {
String pgTypeName = (String) type[0];
Integer oid = (Integer) type[1];
Integer sqlType = (Integer) type[2];
String javaClass = (String) type[3];
Integer arrayOid = (Integer) type[4];
addCoreType(pgTypeName, oid, sqlType, javaClass, arrayOid);
}
_pgNameToJavaClass.put("hstore", Map.class.getName());
}
public synchronized int getSQLType(String pgTypeName) throws SQLException {
if (pgTypeName.endsWith("[]")) {
return Types.ARRAY;
}
Integer i = _pgNameToSQLType.get(pgTypeName);
if (i != null) {
return i;
}
if (_getTypeInfoStatement == null) {
// There's no great way of telling what's an array type.
// People can name their own types starting with _.
// Other types use typelem that aren't actually arrays, like box.
//
String sql;
// in case of multiple records (in different schemas) choose the one from the current
// schema,
// otherwise take the last version of a type that is at least more deterministic then before
// (keeping old behaviour of finding types, that should not be found without correct search
// path)
sql = "SELECT typinput='array_in'::regproc, typtype "
+ " FROM pg_catalog.pg_type "
+ " LEFT "
+ " JOIN (select ns.oid as nspoid, ns.nspname, r.r "
+ " from pg_namespace as ns "
// -- go with older way of unnesting array to be compatible with 8.0
+ " join ( select s.r, (current_schemas(false))[s.r] as nspname "
+ " from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r "
+ " using ( nspname ) "
+ " ) as sp "
+ " ON sp.nspoid = typnamespace "
+ " WHERE typname = ? "
+ " ORDER BY sp.r, pg_type.oid DESC LIMIT 1;";
_getTypeInfoStatement = _conn.prepareStatement(sql);
}
_getTypeInfoStatement.setString(1, pgTypeName);
// Go through BaseStatement to avoid transaction start.
if (!((BaseStatement) _getTypeInfoStatement)
.executeWithFlags(QueryExecutor.QUERY_SUPPRESS_BEGIN)) {
throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
}
ResultSet rs = _getTypeInfoStatement.getResultSet();
Integer type = null;
if (rs.next()) {
boolean isArray = rs.getBoolean(1);
String typtype = rs.getString(2);
if (isArray) {
type = Types.ARRAY;
} else if ("c".equals(typtype)) {
type = Types.STRUCT;
} else if ("d".equals(typtype)) {
type = Types.DISTINCT;
} else if ("e".equals(typtype)) {
type = Types.VARCHAR;
}
}
if (type == null) {
type = Types.OTHER;
}
rs.close();
_pgNameToSQLType.put(pgTypeName, type);
return type;
}
}
到这里,我们就发现了问题,所以博主果断重写他的代码,并且进行优化,这个查询他是一条一条的查询,那么我们为什么不一次性查询出来呢?一次查询出来,再放到缓存里,这样大大节约了项目的启动时间。如何能重写呢?很简单,我们只需要在项目里创建和他的源码一样的包名就能够覆盖他本身的代码,请看优化后的代码:
/*
* Copyright (c) 2004, PostgreSQL Global Development Group
* See the LICENSE file in the project root for more information.
*/
package org.postgresql.jdbc;
import org.postgresql.core.BaseStatement;
import org.postgresql.core.Field;
import org.postgresql.core.Oid;
import org.postgresql.core.ServerVersion;
import org.postgresql.util.GT;
import org.postgresql.util.JdbcBlackHole;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.RowIdLifetime;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
public class PgDatabaseMetaData implements DatabaseMetaData {
public PgDatabaseMetaData(PgConnection conn) {
this.connection = conn;
}
private static final String keywords = "abort,acl,add,aggregate,append,archive,"
+ "arch_store,backward,binary,boolean,change,cluster,"
+ "copy,database,delimiter,delimiters,do,extend,"
+ "explain,forward,heavy,index,inherits,isnull,"
+ "light,listen,load,merge,nothing,notify,"
+ "notnull,oids,purge,rename,replace,retrieve,"
+ "returns,rule,recipe,setof,stdin,stdout,store,"
+ "vacuum,verbose,version";
protected final PgConnection connection; // The connection association
private int NAMEDATALEN = 0; // length for name datatype
private int INDEX_MAX_KEYS = 0; // maximum number of keys in an index.
protected int getMaxIndexKeys() throws SQLException {
if (INDEX_MAX_KEYS == 0) {
String sql;
sql = "SELECT setting FROM pg_catalog.pg_settings WHERE name='max_index_keys'";
Statement stmt = connection.createStatement();
ResultSet rs = null;
try {
rs = stmt.executeQuery(sql);
if (!rs.next()) {
stmt.close();
throw new PSQLException(
GT.tr(
"Unable to determine a value for MaxIndexKeys due to missing system catalog data."),
PSQLState.UNEXPECTED_ERROR);
}
INDEX_MAX_KEYS = rs.getInt(1);
} finally {
JdbcBlackHole.close(rs);
JdbcBlackHole.close(stmt);
}
}
return INDEX_MAX_KEYS;
}
protected int getMaxNameLength() throws SQLException {
if (NAMEDATALEN == 0) {
String sql;
sql = "SELECT t.typlen FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n "
+ "WHERE t.typnamespace=n.oid AND t.typname='name' AND n.nspname='pg_catalog'";
Statement stmt = connection.createStatement();
ResultSet rs = null;
try {
rs = stmt.executeQuery(sql);
if (!rs.next()) {
throw new PSQLException(GT.tr("Unable to find name datatype in the system catalogs."),
PSQLState.UNEXPECTED_ERROR);
}
NAMEDATALEN = rs.getInt("typlen");
} finally {
JdbcBlackHole.close(rs);
JdbcBlackHole.close(stmt);
}
}
return NAMEDATALEN - 1;
}
public boolean allProceduresAreCallable() throws SQLException {
return true; // For now...
}
public boolean allTablesAreSelectable() throws SQLException {
return true; // For now...
}
public String getURL() throws SQLException {
return connection.getURL();
}
public String getUserName() throws SQLException {
return connection.getUserName();
}
public boolean isReadOnly() throws SQLException {
return connection.isReadOnly();
}
public boolean nullsAreSortedHigh() throws SQLException {
return true;
}
public boolean nullsAreSortedLow() throws SQLException {
return false;
}
public boolean nullsAreSortedAtStart() throws SQLException {
return false;
}
public boolean nullsAreSortedAtEnd() throws SQLException {
return false;
}
/**
* Retrieves the name of this database product. We hope that it is PostgreSQL, so we return that
* explicitly.
*
* @return "PostgreSQL"
*/
@Override
public String getDatabaseProductName() throws SQLException {
return "PostgreSQL";
}
@Override
public String getDatabaseProductVersion() throws SQLException {
return connection.getDBVersionNumber();
}
@Override
public String getDriverName() {
return org.postgresql.util.DriverInfo.DRIVER_NAME;
}
@Override
public String getDriverVersion() {
return org.postgresql.util.DriverInfo.DRIVER_VERSION;
}
@Override
public int getDriverMajorVersion() {
return org.postgresql.util.DriverInfo.MAJOR_VERSION;
}
@Override
public int getDriverMinorVersion() {
return org.postgresql.util.DriverInfo.MINOR_VERSION;
}
/**
* Does the database store tables in a local file? No - it stores them in a file on the server.
*
* @return true if so
* @throws SQLException if a database access error occurs
*/
public boolean usesLocalFiles() throws SQLException {
return false;
}
/**
* Does the database use a file for each table? Well, not really, since it doesn't use local files.
*
* @return true if so
* @throws SQLException if a database access error occurs
*/
public boolean usesLocalFilePerTable() throws SQLException {
return false;
}
/**
* Does the database treat mixed case unquoted SQL identifiers as case sensitive and as a result
* store them in mixed case? A JDBC-Compliant driver will always return false.
*
* @return true if so
* @throws SQLException if a database access error occurs
*/
public boolean supportsMixedCaseIdentifiers() throws SQLException {
return false;
}
public boolean storesUpperCaseIdentifiers() throws SQLException {
return false;
}
public boolean storesLowerCaseIdentifiers() throws SQLException {
return true;
}
public boolean storesMixedCaseIdentifiers() throws SQLException {
return false;
}
/**
* Does the database treat mixed case quoted SQL identifiers as case sensitive and as a result
* store them in mixed case? A JDBC compliant driver will always return true.
*
* @return true if so
* @throws SQLException if a database access error occurs
*/
public boolean supportsMixedCaseQuotedIdentifiers() throws SQLException {
return true;
}
public boolean storesUpperCaseQuotedIdentifiers() throws SQLException {
return false;
}
public boolean storesLowerCaseQuotedIdentifiers() throws SQLException {
return false;
}
public boolean storesMixedCaseQuotedIdentifiers() throws SQLException {
return false;
}
/**
* What is the string used to quote SQL identifiers? This returns a space if identifier quoting
* isn't supported. A JDBC Compliant driver will always use a double quote character.
*
* @return the quoting string
* @throws SQLException if a database access error occurs
*/
public String getIdentifierQuoteString() throws SQLException {
return "\"";
}
/**
* {@inheritDoc}
*
* <p>
* Within PostgreSQL, the keywords are found in src/backend/parser/keywords.c
*
* <p>
* For SQL Keywords, I took the list provided at
* <a href="http://web.dementia.org/~shadow/sql/sql3bnf.sep93.txt"> http://web.dementia.org/~
* shadow/sql/sql3bnf.sep93.txt</a> which is for SQL3, not SQL-92, but it is close enough for this
* purpose.
*
* @return a comma separated list of keywords we use
* @throws SQLException if a database access error occurs
*/
public String getSQLKeywords() throws SQLException {
return keywords;
}
public String getNumericFunctions() throws SQLException {
return EscapedFunctions.ABS + ',' + EscapedFunctions.ACOS + ',' + EscapedFunctions.ASIN + ','
+ EscapedFunctions.ATAN + ',' + EscapedFunctions.ATAN2 + ',' + EscapedFunctions.CEILING
+ ',' + EscapedFunctions.COS + ',' + EscapedFunctions.COT + ',' + EscapedFunctions.DEGREES
+ ',' + EscapedFunctions.EXP + ',' + EscapedFunctions.FLOOR + ',' + EscapedFunctions.LOG
+ ',' + EscapedFunctions.LOG10 + ',' + EscapedFunctions.MOD + ',' + EscapedFunctions.PI
+ ',' + EscapedFunctions.POWER + ',' + EscapedFunctions.RADIANS + ','
+ EscapedFunctions.ROUND + ',' + EscapedFunctions.SIGN + ',' + EscapedFunctions.SIN + ','
+ EscapedFunctions.SQRT + ',' + EscapedFunctions.TAN + ',' + EscapedFunctions.TRUNCATE;
}
public String getStringFunctions() throws SQLException {
String funcs = EscapedFunctions.ASCII + ',' + EscapedFunctions.CHAR + ','
+ EscapedFunctions.CONCAT + ',' + EscapedFunctions.LCASE + ',' + EscapedFunctions.LEFT + ','
+ EscapedFunctions.LENGTH + ',' + EscapedFunctions.LTRIM + ',' + EscapedFunctions.REPEAT
+ ',' + EscapedFunctions.RTRIM + ',' + EscapedFunctions.SPACE + ','
+ EscapedFunctions.SUBSTRING + ',' + EscapedFunctions.UCASE;
// Currently these don't work correctly with parameterized
// arguments, so leave them out. They reorder the arguments
// when rewriting the query, but no translation layer is provided,
// so a setObject(N, obj) will not go to the correct parameter.
// ','+EscapedFunctions.INSERT+','+EscapedFunctions.LOCATE+
// ','+EscapedFunctions.RIGHT+
funcs += ',' + EscapedFunctions.REPLACE;
return funcs;
}
public String getSystemFunctions() throws SQLException {
return EscapedFunctions.DATABASE + ',' + EscapedFunctions.IFNULL + ',' + EscapedFunctions.USER;
}
public String getTimeDateFunctions() throws SQLException {
String timeDateFuncs = EscapedFunctions.CURDATE + ',' + EscapedFunctions.CURTIME + ','
+ EscapedFunctions.DAYNAME + ',' + EscapedFunctions.DAYOFMONTH + ','
+ EscapedFunctions.DAYOFWEEK + ',' + EscapedFunctions.DAYOFYEAR + ','
+ EscapedFunctions.HOUR + ',' + EscapedFunctions.MINUTE + ',' + EscapedFunctions.MONTH + ','
+ EscapedFunctions.MONTHNAME + ',' + EscapedFunctions.NOW + ',' + EscapedFunctions.QUARTER
+ ',' + EscapedFunctions.SECOND + ',' + EscapedFunctions.WEEK + ',' + EscapedFunctions.YEAR;
timeDateFuncs += ',' + EscapedFunctions.TIMESTAMPADD;
// +','+EscapedFunctions.TIMESTAMPDIFF;
return timeDateFuncs;
}
public String getSearchStringEscape() throws SQLException {
// This method originally returned "\\\\" assuming that it
// would be fed directly into pg's input parser so it would
// need two backslashes. This isn't how it's supposed to be
// used though. If passed as a PreparedStatement parameter
// or fed to a DatabaseMetaData method then double backslashes
// are incorrect. If you're feeding something directly into
// a query you are responsible for correctly escaping it.
// With 8.2+ this escaping is a little trickier because you
// must know the setting of standard_conforming_strings, but
// that's not our problem.
return "\\";
}
/**
* {@inheritDoc}
*
* <p>
* Postgresql allows any high-bit character to be used in an unquoted identifier, so we can't
* possibly list them all.
*
* From the file src/backend/parser/scan.l, an identifier is ident_start [A-Za-z\200-\377_]
* ident_cont [A-Za-z\200-\377_0-9\$] identifier {ident_start}{ident_cont}*
*
* @return a string containing the extra characters
* @throws SQLException if a database access error occurs
*/
public String getExtraNameCharacters() throws SQLException {
return "";
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 6.1+
*/
public boolean supportsAlterTableWithAddColumn() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.3+
*/
public boolean supportsAlterTableWithDropColumn() throws SQLException {
return true;
}
public boolean supportsColumnAliasing() throws SQLException {
return true;
}
public boolean nullPlusNonNullIsNull() throws SQLException {
return true;
}
public boolean supportsConvert() throws SQLException {
return false;
}
public boolean supportsConvert(int fromType, int toType) throws SQLException {
return false;
}
public boolean supportsTableCorrelationNames() throws SQLException {
return true;
}
public boolean supportsDifferentTableCorrelationNames() throws SQLException {
return false;
}
public boolean supportsExpressionsInOrderBy() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 6.4+
*/
public boolean supportsOrderByUnrelated() throws SQLException {
return true;
}
public boolean supportsGroupBy() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 6.4+
*/
public boolean supportsGroupByUnrelated() throws SQLException {
return true;
}
/*
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 6.4+
*/
public boolean supportsGroupByBeyondSelect() throws SQLException {
return true;
}
/*
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.1+
*/
public boolean supportsLikeEscapeClause() throws SQLException {
return true;
}
public boolean supportsMultipleResultSets() throws SQLException {
return true;
}
public boolean supportsMultipleTransactions() throws SQLException {
return true;
}
public boolean supportsNonNullableColumns() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* This grammar is defined at:
*
* <p>
* <a href="http://www.microsoft.com/msdn/sdk/platforms/doc/odbc/src/intropr.htm">http://www.
* microsoft.com/msdn/sdk/platforms/doc/odbc/src/intropr.htm</a>
*
* <p>
* In Appendix C. From this description, we seem to support the ODBC minimal (Level 0) grammar.
*
* @return true
*/
public boolean supportsMinimumSQLGrammar() throws SQLException {
return true;
}
/**
* Does this driver support the Core ODBC SQL grammar. We need SQL-92 conformance for this.
*
* @return false
* @throws SQLException if a database access error occurs
*/
public boolean supportsCoreSQLGrammar() throws SQLException {
return false;
}
/**
* Does this driver support the Extended (Level 2) ODBC SQL grammar. We don't conform to the Core
* (Level 1), so we can't conform to the Extended SQL Grammar.
*
* @return false
* @throws SQLException if a database access error occurs
*/
public boolean supportsExtendedSQLGrammar() throws SQLException {
return false;
}
/**
* Does this driver support the ANSI-92 entry level SQL grammar? All JDBC Compliant drivers must
* return true. We currently report false until 'schema' support is added. Then this should be
* changed to return true, since we will be mostly compliant (probably more compliant than many
* other databases) And since this is a requirement for all JDBC drivers we need to get to the
* point where we can return true.
*
* @return true if connected to PostgreSQL 7.3+
* @throws SQLException if a database access error occurs
*/
public boolean supportsANSI92EntryLevelSQL() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return false
*/
public boolean supportsANSI92IntermediateSQL() throws SQLException {
return false;
}
/**
* {@inheritDoc}
*
* @return false
*/
public boolean supportsANSI92FullSQL() throws SQLException {
return false;
}
/*
* Is the SQL Integrity Enhancement Facility supported? Our best guess is that this means support
* for constraints
*
* @return true
*
* @exception SQLException if a database access error occurs
*/
public boolean supportsIntegrityEnhancementFacility() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.1+
*/
public boolean supportsOuterJoins() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.1+
*/
public boolean supportsFullOuterJoins() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.1+
*/
public boolean supportsLimitedOuterJoins() throws SQLException {
return true;
}
/**
* {@inheritDoc}
* <p>
* PostgreSQL doesn't have schemas, but when it does, we'll use the term "schema".
*
* @return {@code "schema"}
*/
public String getSchemaTerm() throws SQLException {
return "schema";
}
/**
* {@inheritDoc}
*
* @return {@code "function"}
*/
public String getProcedureTerm() throws SQLException {
return "function";
}
/**
* {@inheritDoc}
*
* @return {@code "database"}
*/
public String getCatalogTerm() throws SQLException {
return "database";
}
public boolean isCatalogAtStart() throws SQLException {
return true;
}
public String getCatalogSeparator() throws SQLException {
return ".";
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.3+
*/
public boolean supportsSchemasInDataManipulation() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.3+
*/
public boolean supportsSchemasInProcedureCalls() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.3+
*/
public boolean supportsSchemasInTableDefinitions() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.3+
*/
public boolean supportsSchemasInIndexDefinitions() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.3+
*/
public boolean supportsSchemasInPrivilegeDefinitions() throws SQLException {
return true;
}
public boolean supportsCatalogsInDataManipulation() throws SQLException {
return false;
}
public boolean supportsCatalogsInProcedureCalls() throws SQLException {
return false;
}
public boolean supportsCatalogsInTableDefinitions() throws SQLException {
return false;
}
public boolean supportsCatalogsInIndexDefinitions() throws SQLException {
return false;
}
public boolean supportsCatalogsInPrivilegeDefinitions() throws SQLException {
return false;
}
/**
* We support cursors for gets only it seems. I dont see a method to get a positioned delete.
*
* @return false
* @throws SQLException if a database access error occurs
*/
public boolean supportsPositionedDelete() throws SQLException {
return false; // For now...
}
public boolean supportsPositionedUpdate() throws SQLException {
return false; // For now...
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 6.5+
*/
public boolean supportsSelectForUpdate() throws SQLException {
return true;
}
public boolean supportsStoredProcedures() throws SQLException {
return true;
}
public boolean supportsSubqueriesInComparisons() throws SQLException {
return true;
}
public boolean supportsSubqueriesInExists() throws SQLException {
return true;
}
public boolean supportsSubqueriesInIns() throws SQLException {
return true;
}
public boolean supportsSubqueriesInQuantifieds() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.1+
*/
public boolean supportsCorrelatedSubqueries() throws SQLException {
return true;
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 6.3+
*/
public boolean supportsUnion() throws SQLException {
return true; // since 6.3
}
/**
* {@inheritDoc}
*
* @return true if connected to PostgreSQL 7.1+
*/
public boolean supportsUnionAll() throws SQLException {
return true;
}
/**
* {@inheritDoc} In PostgreSQL, Cursors are only open within transactions.
*/
public boolean supportsOpenCursorsAcrossCommit() throws SQLException {
return false;
}
public boolean supportsOpenCursorsAcrossRollback() throws SQLException {
return false;
}
/**
* {@inheritDoc}
* <p>
* Can statements remain open across commits? They may, but this driver cannot guarantee that. In
* further reflection. we are talking a Statement object here, so the answer is yes, since the
* Statement is only a vehicle to ExecSQL()
*
* @return true
*/
public boolean supportsOpenStatementsAcrossCommit() throws SQLException {
return true;
}
/**
* {@inheritDoc}
* <p>
* Can statements remain open across rollbacks? They may, but this driver cannot guarantee that.
* In further contemplation, we are talking a Statement object here, so the answer is yes, since
* the Statement is only a vehicle to ExecSQL() in Connection
*
* @return true
*/
public boolean supportsOpenStatementsAcrossRollback() throws SQLException {
return true;
}
public int getMaxCharLiteralLength() throws SQLException {
return 0; // no limit
}
public int getMaxBinaryLiteralLength() throws SQLException {
return 0; // no limit
}
public int getMaxColumnNameLength() throws SQLException {
return getMaxNameLength();
}
public int getMaxColumnsInGroupBy() throws SQLException {
return 0; // no limit
}
public int getMaxColumnsInIndex() throws SQLException {
return getMaxIndexKeys();
}
public int getMaxColumnsInOrderBy() throws SQLException {
return 0; // no limit
}
public int getMaxColumnsInSelect() throws SQLException {
return 0; // no limit
}
/**
* {@inheritDoc} What is the maximum number of columns in a table? From the CREATE TABLE reference
* page...
*
* <p>
* "The new class is created as a heap with no initial data. A class can have no more than 1600
* attributes (realistically, this is limited by the fact that tuple sizes must be less than 8192
* bytes)..."
*
* @return the max columns
* @throws SQLException if a database access error occurs
*/
public int getMaxColumnsInTable() throws SQLException {
return 1600;
}
/**
* {@inheritDoc} How many active connection can we have at a time to this database? Well, since it
* depends on postmaster, which just does a listen() followed by an accept() and fork(), its
* basically very high. Unless the system runs out of processes, it can be 65535 (the number of
* aux. ports on a TCP/IP system). I will return 8192 since that is what even the largest system
* can realistically handle,
*
* @return the maximum number of connections
* @throws SQLException if a database access error occurs
*/
public int getMaxConnections() throws SQLException {
return 8192;
}
public int getMaxCursorNameLength() throws SQLException {
return getMaxNameLength();
}
public int getMaxIndexLength() throws SQLException {
return 0; // no limit (larger than an int anyway)
}
public int getMaxSchemaNameLength() throws SQLException {
return getMaxNameLength();
}
public int getMaxProcedureNameLength() throws SQLException {
return getMaxNameLength();
}
public int getMaxCatalogNameLength() throws SQLException {
return getMaxNameLength();
}
public int getMaxRowSize() throws SQLException {
return 1073741824; // 1 GB
}
public boolean doesMaxRowSizeIncludeBlobs() throws SQLException {
return false;
}
public int getMaxStatementLength() throws SQLException {
return 0; // actually whatever fits in size_t
}
public int getMaxStatements() throws SQLException {
return 0;
}
public int getMaxTableNameLength() throws SQLException {
return getMaxNameLength();
}
public int getMaxTablesInSelect() throws SQLException {
return 0; // no limit
}
public int getMaxUserNameLength() throws SQLException {
return getMaxNameLength();
}
public int getDefaultTransactionIsolation() throws SQLException {
return Connection.TRANSACTION_READ_COMMITTED;
}
public boolean supportsTransactions() throws SQLException {
return true;
}
/**
* {@inheritDoc}
* <p>
* We only support TRANSACTION_SERIALIZABLE and TRANSACTION_READ_COMMITTED before 8.0; from 8.0
* READ_UNCOMMITTED and REPEATABLE_READ are accepted aliases for READ_COMMITTED.
*/
public boolean supportsTransactionIsolationLevel(int level) throws SQLException {
switch (level) {
case Connection.TRANSACTION_READ_UNCOMMITTED:
case Connection.TRANSACTION_READ_COMMITTED:
case Connection.TRANSACTION_REPEATABLE_READ:
case Connection.TRANSACTION_SERIALIZABLE:
return true;
default:
return false;
}
}
public boolean supportsDataDefinitionAndDataManipulationTransactions() throws SQLException {
return true;
}
public boolean supportsDataManipulationTransactionsOnly() throws SQLException {
return false;
}
/**
* Does a data definition statement within a transaction force the transaction to commit? It seems
* to mean something like:
*
* <pre>
* CREATE TABLE T (A INT);
* INSERT INTO T (A) VALUES (2);
* BEGIN;
* UPDATE T SET A = A + 1;
* CREATE TABLE X (A INT);
* SELECT A FROM T INTO X;
* COMMIT;
* </pre>
*
* does the CREATE TABLE call cause a commit? The answer is no.
*
* @return true if so
* @throws SQLException if a database access error occurs
*/
public boolean dataDefinitionCausesTransactionCommit() throws SQLException {
return false;
}
public boolean dataDefinitionIgnoredInTransactions() throws SQLException {
return false;
}
/**
* Turn the provided value into a valid string literal for direct inclusion into a query. This
* includes the single quotes needed around it.
*
* @param s input value
*
* @return string literal for direct inclusion into a query
* @throws SQLException if something wrong happens
*/
protected String escapeQuotes(String s) throws SQLException {
StringBuilder sb = new StringBuilder();
if (!connection.getStandardConformingStrings()) {
sb.append("E");
}
sb.append("'");
sb.append(connection.escapeString(s));
sb.append("'");
return sb.toString();
}
public ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern)
throws SQLException {
String sql;
sql = "SELECT NULL AS PROCEDURE_CAT, n.nspname AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, "
+ "NULL, NULL, NULL, d.description AS REMARKS, "
+ DatabaseMetaData.procedureReturnsResult + " AS PROCEDURE_TYPE, "
+ " p.proname || '_' || p.oid AS SPECIFIC_NAME "
+ " FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p "
+ " LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid) "
+ " LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') "
+ " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') "
+ " WHERE p.pronamespace=n.oid ";
if (schemaPattern != null && !schemaPattern.isEmpty()) {
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
}
if (procedureNamePattern != null && !procedureNamePattern.isEmpty()) {
sql += " AND p.proname LIKE " + escapeQuotes(procedureNamePattern);
}
sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, p.oid::text ";
return createMetaDataStatement().executeQuery(sql);
}
public ResultSet getProcedureColumns(String catalog, String schemaPattern,
String procedureNamePattern, String columnNamePattern) throws SQLException {
int columns = 20;
Field f[] = new Field[columns];
List<byte[][]> v = new ArrayList<byte[][]>(); // The new ResultSet tuple stuff
f[0] = new Field("PROCEDURE_CAT", Oid.VARCHAR);
f[1] = new Field("PROCEDURE_SCHEM", Oid.VARCHAR);
f[2] = new Field("PROCEDURE_NAME", Oid.VARCHAR);
f[3] = new Field("COLUMN_NAME", Oid.VARCHAR);
f[4] = new Field("COLUMN_TYPE", Oid.INT2);
f[5] = new Field("DATA_TYPE", Oid.INT2);
f[6] = new Field("TYPE_NAME", Oid.VARCHAR);
f[7] = new Field("PRECISION", Oid.INT4);
f[8] = new Field("LENGTH", Oid.INT4);
f[9] = new Field("SCALE", Oid.INT2);
f[10] = new Field("RADIX", Oid.INT2);
f[11] = new Field("NULLABLE", Oid.INT2);
f[12] = new Field("REMARKS", Oid.VARCHAR);
f[13] = new Field("COLUMN_DEF", Oid.VARCHAR);
f[14] = new Field("SQL_DATA_TYPE", Oid.INT4);
f[15] = new Field("SQL_DATETIME_SUB", Oid.INT4);
f[16] = new Field("CHAR_OCTECT_LENGTH", Oid.INT4);
f[17] = new Field("ORDINAL_POSITION", Oid.INT4);
f[18] = new Field("IS_NULLABLE", Oid.VARCHAR);
f[19] = new Field("SPECIFIC_NAME", Oid.VARCHAR);
String sql;
sql = "SELECT n.nspname,p.proname,p.prorettype,p.proargtypes, t.typtype,t.typrelid, "
+ " p.proargnames, p.proargmodes, p.proallargtypes, p.oid "
+ " FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n, pg_catalog.pg_type t "
+ " WHERE p.pronamespace=n.oid AND p.prorettype=t.oid ";
if (schemaPattern != null && !schemaPattern.isEmpty()) {
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
}
if (procedureNamePattern != null && !procedureNamePattern.isEmpty()) {
sql += " AND p.proname LIKE " + escapeQuotes(procedureNamePattern);
}
sql += " ORDER BY n.nspname, p.proname, p.oid::text ";
byte isnullableUnknown[] = new byte[0];
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
byte schema[] = rs.getBytes("nspname");
byte procedureName[] = rs.getBytes("proname");
byte specificName[] =
connection.encodeString(rs.getString("proname") + "_" + rs.getString("oid"));
int returnType = (int) rs.getLong("prorettype");
String returnTypeType = rs.getString("typtype");
int returnTypeRelid = (int) rs.getLong("typrelid");
String strArgTypes = rs.getString("proargtypes");
StringTokenizer st = new StringTokenizer(strArgTypes);
List<Long> argTypes = new ArrayList<Long>();
while (st.hasMoreTokens()) {
argTypes.add(Long.valueOf(st.nextToken()));
}
String argNames[] = null;
Array argNamesArray = rs.getArray("proargnames");
if (argNamesArray != null) {
argNames = (String[]) argNamesArray.getArray();
}
String argModes[] = null;
Array argModesArray = rs.getArray("proargmodes");
if (argModesArray != null) {
argModes = (String[]) argModesArray.getArray();
}
int numArgs = argTypes.size();
Long allArgTypes[] = null;
Array allArgTypesArray = rs.getArray("proallargtypes");
if (allArgTypesArray != null) {
allArgTypes = (Long[]) allArgTypesArray.getArray();
numArgs = allArgTypes.length;
}
// decide if we are returning a single column result.
if (returnTypeType.equals("b") || returnTypeType.equals("d") || returnTypeType.equals("e")
|| (returnTypeType.equals("p") && argModesArray == null)) {
byte[][] tuple = new byte[columns][];
tuple[0] = null;
tuple[1] = schema;
tuple[2] = procedureName;
tuple[3] = connection.encodeString("returnValue");
tuple[4] = connection
.encodeString(Integer.toString(java.sql.DatabaseMetaData.procedureColumnReturn));
tuple[5] = connection
.encodeString(Integer.toString(connection.getTypeInfo().getSQLType(returnType)));
tuple[6] = connection.encodeString(connection.getTypeInfo().getPGType(returnType));
tuple[7] = null;
tuple[8] = null;
tuple[9] = null;
tuple[10] = null;
tuple[11] = connection
.encodeString(Integer.toString(java.sql.DatabaseMetaData.procedureNullableUnknown));
tuple[12] = null;
tuple[17] = connection.encodeString(Integer.toString(0));
tuple[18] = isnullableUnknown;
tuple[19] = specificName;
v.add(tuple);
}
// Add a row for each argument.
for (int i = 0; i < numArgs; i++) {
byte[][] tuple = new byte[columns][];
tuple[0] = null;
tuple[1] = schema;
tuple[2] = procedureName;
if (argNames != null) {
tuple[3] = connection.encodeString(argNames[i]);
} else {
tuple[3] = connection.encodeString("$" + (i + 1));
}
int columnMode = DatabaseMetaData.procedureColumnIn;
if (argModes != null && argModes[i].equals("o")) {
columnMode = DatabaseMetaData.procedureColumnOut;
} else if (argModes != null && argModes[i].equals("b")) {
columnMode = DatabaseMetaData.procedureColumnInOut;
} else if (argModes != null && argModes[i].equals("t")) {
columnMode = DatabaseMetaData.procedureColumnReturn;
}
tuple[4] = connection.encodeString(Integer.toString(columnMode));
int argOid;
if (allArgTypes != null) {
argOid = allArgTypes[i].intValue();
} else {
argOid = argTypes.get(i).intValue();
}
tuple[5] =
connection.encodeString(Integer.toString(connection.getTypeInfo().getSQLType(argOid)));
tuple[6] = connection.encodeString(connection.getTypeInfo().getPGType(argOid));
tuple[7] = null;
tuple[8] = null;
tuple[9] = null;
tuple[10] = null;
tuple[11] =
connection.encodeString(Integer.toString(DatabaseMetaData.procedureNullableUnknown));
tuple[12] = null;
tuple[17] = connection.encodeString(Integer.toString(i + 1));
tuple[18] = isnullableUnknown;
tuple[19] = specificName;
v.add(tuple);
}
// if we are returning a multi-column result.
if (returnTypeType.equals("c") || (returnTypeType.equals("p") && argModesArray != null)) {
String columnsql = "SELECT a.attname,a.atttypid FROM pg_catalog.pg_attribute a "
+ " WHERE a.attrelid = " + returnTypeRelid
+ " AND a.attnum > 0 ORDER BY a.attnum ";
Statement columnstmt = connection.createStatement();
ResultSet columnrs = columnstmt.executeQuery(columnsql);
while (columnrs.next()) {
int columnTypeOid = (int) columnrs.getLong("atttypid");
byte[][] tuple = new byte[columns][];
tuple[0] = null;
tuple[1] = schema;
tuple[2] = procedureName;
tuple[3] = columnrs.getBytes("attname");
tuple[4] = connection
.encodeString(Integer.toString(java.sql.DatabaseMetaData.procedureColumnResult));
tuple[5] = connection
.encodeString(Integer.toString(connection.getTypeInfo().getSQLType(columnTypeOid)));
tuple[6] = connection.encodeString(connection.getTypeInfo().getPGType(columnTypeOid));
tuple[7] = null;
tuple[8] = null;
tuple[9] = null;
tuple[10] = null;
tuple[11] = connection
.encodeString(Integer.toString(java.sql.DatabaseMetaData.procedureNullableUnknown));
tuple[12] = null;
tuple[17] = connection.encodeString(Integer.toString(0));
tuple[18] = isnullableUnknown;
tuple[19] = specificName;
v.add(tuple);
}
columnrs.close();
columnstmt.close();
}
}
rs.close();
stmt.close();
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
@Override
public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern,
String types[]) throws SQLException {
String select;
String orderby;
String useSchemas;
useSchemas = "SCHEMAS";
select = "SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, "
+ " CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' "
+ " WHEN true THEN CASE "
+ " WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind "
+ " WHEN 'r' THEN 'SYSTEM TABLE' "
+ " WHEN 'v' THEN 'SYSTEM VIEW' "
+ " WHEN 'i' THEN 'SYSTEM INDEX' "
+ " ELSE NULL "
+ " END "
+ " WHEN n.nspname = 'pg_toast' THEN CASE c.relkind "
+ " WHEN 'r' THEN 'SYSTEM TOAST TABLE' "
+ " WHEN 'i' THEN 'SYSTEM TOAST INDEX' "
+ " ELSE NULL "
+ " END "
+ " ELSE CASE c.relkind "
+ " WHEN 'r' THEN 'TEMPORARY TABLE' "
+ " WHEN 'i' THEN 'TEMPORARY INDEX' "
+ " WHEN 'S' THEN 'TEMPORARY SEQUENCE' "
+ " WHEN 'v' THEN 'TEMPORARY VIEW' "
+ " ELSE NULL "
+ " END "
+ " END "
+ " WHEN false THEN CASE c.relkind "
+ " WHEN 'r' THEN 'TABLE' "
+ " WHEN 'i' THEN 'INDEX' "
+ " WHEN 'S' THEN 'SEQUENCE' "
+ " WHEN 'v' THEN 'VIEW' "
+ " WHEN 'c' THEN 'TYPE' "
+ " WHEN 'f' THEN 'FOREIGN TABLE' "
+ " WHEN 'm' THEN 'MATERIALIZED VIEW' "
+ " ELSE NULL "
+ " END "
+ " ELSE NULL "
+ " END "
+ " AS TABLE_TYPE, d.description AS REMARKS "
+ " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c "
+ " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) "
+ " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') "
+ " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') "
+ " WHERE c.relnamespace = n.oid ";
if (schemaPattern != null && !schemaPattern.isEmpty()) {
select += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
}
orderby = " ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME ";
if (tableNamePattern != null && !tableNamePattern.isEmpty()) {
select += " AND c.relname LIKE " + escapeQuotes(tableNamePattern);
}
if (types != null) {
select += " AND (false ";
StringBuilder orclause = new StringBuilder();
for (String type : types) {
Map<String, String> clauses = tableTypeClauses.get(type);
if (clauses != null) {
String clause = clauses.get(useSchemas);
orclause.append(" OR ( ").append(clause).append(" ) ");
}
}
select += orclause.toString() + ") ";
}
String sql = select + orderby;
return createMetaDataStatement().executeQuery(sql);
}
private static final Map<String, Map<String, String>> tableTypeClauses;
static {
tableTypeClauses = new HashMap<String, Map<String, String>>();
Map<String, String> ht = new HashMap<String, String>();
tableTypeClauses.put("TABLE", ht);
ht.put("SCHEMAS",
"c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'");
ht.put("NOSCHEMAS", "c.relkind = 'r' AND c.relname !~ '^pg_'");
ht = new HashMap<String, String>();
tableTypeClauses.put("VIEW", ht);
ht.put("SCHEMAS",
"c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema'");
ht.put("NOSCHEMAS", "c.relkind = 'v' AND c.relname !~ '^pg_'");
ht = new HashMap<String, String>();
tableTypeClauses.put("INDEX", ht);
ht.put("SCHEMAS",
"c.relkind = 'i' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'");
ht.put("NOSCHEMAS", "c.relkind = 'i' AND c.relname !~ '^pg_'");
ht = new HashMap<String, String>();
tableTypeClauses.put("SEQUENCE", ht);
ht.put("SCHEMAS", "c.relkind = 'S'");
ht.put("NOSCHEMAS", "c.relkind = 'S'");
ht = new HashMap<String, String>();
tableTypeClauses.put("TYPE", ht);
ht.put("SCHEMAS",
"c.relkind = 'c' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'");
ht.put("NOSCHEMAS", "c.relkind = 'c' AND c.relname !~ '^pg_'");
ht = new HashMap<String, String>();
tableTypeClauses.put("SYSTEM TABLE", ht);
ht.put("SCHEMAS",
"c.relkind = 'r' AND (n.nspname = 'pg_catalog' OR n.nspname = 'information_schema')");
ht.put("NOSCHEMAS",
"c.relkind = 'r' AND c.relname ~ '^pg_' AND c.relname !~ '^pg_toast_' AND c.relname !~ '^pg_temp_'");
ht = new HashMap<String, String>();
tableTypeClauses.put("SYSTEM TOAST TABLE", ht);
ht.put("SCHEMAS", "c.relkind = 'r' AND n.nspname = 'pg_toast'");
ht.put("NOSCHEMAS", "c.relkind = 'r' AND c.relname ~ '^pg_toast_'");
ht = new HashMap<String, String>();
tableTypeClauses.put("SYSTEM TOAST INDEX", ht);
ht.put("SCHEMAS", "c.relkind = 'i' AND n.nspname = 'pg_toast'");
ht.put("NOSCHEMAS", "c.relkind = 'i' AND c.relname ~ '^pg_toast_'");
ht = new HashMap<String, String>();
tableTypeClauses.put("SYSTEM VIEW", ht);
ht.put("SCHEMAS",
"c.relkind = 'v' AND (n.nspname = 'pg_catalog' OR n.nspname = 'information_schema') ");
ht.put("NOSCHEMAS", "c.relkind = 'v' AND c.relname ~ '^pg_'");
ht = new HashMap<String, String>();
tableTypeClauses.put("SYSTEM INDEX", ht);
ht.put("SCHEMAS",
"c.relkind = 'i' AND (n.nspname = 'pg_catalog' OR n.nspname = 'information_schema') ");
ht.put("NOSCHEMAS",
"c.relkind = 'v' AND c.relname ~ '^pg_' AND c.relname !~ '^pg_toast_' AND c.relname !~ '^pg_temp_'");
ht = new HashMap<String, String>();
tableTypeClauses.put("TEMPORARY TABLE", ht);
ht.put("SCHEMAS", "c.relkind = 'r' AND n.nspname ~ '^pg_temp_' ");
ht.put("NOSCHEMAS", "c.relkind = 'r' AND c.relname ~ '^pg_temp_' ");
ht = new HashMap<String, String>();
tableTypeClauses.put("TEMPORARY INDEX", ht);
ht.put("SCHEMAS", "c.relkind = 'i' AND n.nspname ~ '^pg_temp_' ");
ht.put("NOSCHEMAS", "c.relkind = 'i' AND c.relname ~ '^pg_temp_' ");
ht = new HashMap<String, String>();
tableTypeClauses.put("TEMPORARY VIEW", ht);
ht.put("SCHEMAS", "c.relkind = 'v' AND n.nspname ~ '^pg_temp_' ");
ht.put("NOSCHEMAS", "c.relkind = 'v' AND c.relname ~ '^pg_temp_' ");
ht = new HashMap<String, String>();
tableTypeClauses.put("TEMPORARY SEQUENCE", ht);
ht.put("SCHEMAS", "c.relkind = 'S' AND n.nspname ~ '^pg_temp_' ");
ht.put("NOSCHEMAS", "c.relkind = 'S' AND c.relname ~ '^pg_temp_' ");
ht = new HashMap<String, String>();
tableTypeClauses.put("FOREIGN TABLE", ht);
ht.put("SCHEMAS", "c.relkind = 'f'");
ht.put("NOSCHEMAS", "c.relkind = 'f'");
ht = new HashMap<String, String>();
tableTypeClauses.put("MATERIALIZED VIEW", ht);
ht.put("SCHEMAS", "c.relkind = 'm'");
ht.put("NOSCHEMAS", "c.relkind = 'm'");
}
@Override
public ResultSet getSchemas() throws SQLException {
return getSchemas(null, null);
}
@Override
public ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException {
String sql;
sql = "SELECT nspname AS TABLE_SCHEM, NULL AS TABLE_CATALOG FROM pg_catalog.pg_namespace "
+ " WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_' "
+ " OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_' "
+ " OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_')) ";
if (schemaPattern != null && !schemaPattern.isEmpty()) {
sql += " AND nspname LIKE " + escapeQuotes(schemaPattern);
}
sql += " ORDER BY TABLE_SCHEM";
return createMetaDataStatement().executeQuery(sql);
}
/**
* PostgreSQL does not support multiple catalogs from a single connection, so to reduce confusion
* we only return the current catalog. {@inheritDoc}
*/
@Override
public ResultSet getCatalogs() throws SQLException {
Field f[] = new Field[1];
List<byte[][]> v = new ArrayList<byte[][]>();
f[0] = new Field("TABLE_CAT", Oid.VARCHAR);
byte[][] tuple = new byte[1][];
tuple[0] = connection.encodeString(connection.getCatalog());
v.add(tuple);
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
@Override
public ResultSet getTableTypes() throws SQLException {
String types[] = tableTypeClauses.keySet().toArray(new String[0]);
Arrays.sort(types);
Field f[] = new Field[1];
List<byte[][]> v = new ArrayList<byte[][]>();
f[0] = new Field("TABLE_TYPE", Oid.VARCHAR);
for (String type : types) {
byte[][] tuple = new byte[1][];
tuple[0] = connection.encodeString(type);
v.add(tuple);
}
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
public ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern,
String columnNamePattern) throws SQLException {
int numberOfFields = 23; // JDBC4
List<byte[][]> v = new ArrayList<byte[][]>(); // The new ResultSet tuple stuff
Field f[] = new Field[numberOfFields]; // The field descriptors for the new ResultSet
f[0] = new Field("TABLE_CAT", Oid.VARCHAR);
f[1] = new Field("TABLE_SCHEM", Oid.VARCHAR);
f[2] = new Field("TABLE_NAME", Oid.VARCHAR);
f[3] = new Field("COLUMN_NAME", Oid.VARCHAR);
f[4] = new Field("DATA_TYPE", Oid.INT2);
f[5] = new Field("TYPE_NAME", Oid.VARCHAR);
f[6] = new Field("COLUMN_SIZE", Oid.INT4);
f[7] = new Field("BUFFER_LENGTH", Oid.VARCHAR);
f[8] = new Field("DECIMAL_DIGITS", Oid.INT4);
f[9] = new Field("NUM_PREC_RADIX", Oid.INT4);
f[10] = new Field("NULLABLE", Oid.INT4);
f[11] = new Field("REMARKS", Oid.VARCHAR);
f[12] = new Field("COLUMN_DEF", Oid.VARCHAR);
f[13] = new Field("SQL_DATA_TYPE", Oid.INT4);
f[14] = new Field("SQL_DATETIME_SUB", Oid.INT4);
f[15] = new Field("CHAR_OCTET_LENGTH", Oid.VARCHAR);
f[16] = new Field("ORDINAL_POSITION", Oid.INT4);
f[17] = new Field("IS_NULLABLE", Oid.VARCHAR);
f[18] = new Field("SCOPE_CATLOG", Oid.VARCHAR);
f[19] = new Field("SCOPE_SCHEMA", Oid.VARCHAR);
f[20] = new Field("SCOPE_TABLE", Oid.VARCHAR);
f[21] = new Field("SOURCE_DATA_TYPE", Oid.INT2);
f[22] = new Field("IS_AUTOINCREMENT", Oid.VARCHAR);
String sql;
// a.attnum isn't decremented when preceding columns are dropped,
// so the only way to calculate the correct column number is with
// window functions, new in 8.4.
//
// We want to push as much predicate information below the window
// function as possible (schema/table names), but must leave
// column name outside so we correctly count the other columns.
//
if (connection.haveMinimumServerVersion(ServerVersion.v8_4)) {
sql = "SELECT * FROM (";
} else {
sql = "";
}
sql += "SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull "
+ "OR (t.typtype = 'd' AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,";
if (connection.haveMinimumServerVersion(ServerVersion.v8_4)) {
sql += "row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, ";
} else {
sql += "a.attnum,";
}
sql += "pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype "
+ " FROM pg_catalog.pg_namespace n "
+ " JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) "
+ " JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) "
+ " JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) "
+ " LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) "
+ " LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) "
+ " LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') "
+ " LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') "
+ " WHERE c.relkind in ('r','v','f','m') and a.attnum > 0 AND NOT a.attisdropped ";
if (schemaPattern != null && !schemaPattern.isEmpty()) {
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
}
if (tableNamePattern != null && !tableNamePattern.isEmpty()) {
sql += " AND c.relname LIKE " + escapeQuotes(tableNamePattern);
}
if (connection.haveMinimumServerVersion(ServerVersion.v8_4)) {
sql += ") c WHERE true ";
}
if (columnNamePattern != null && !columnNamePattern.isEmpty()) {
sql += " AND attname LIKE " + escapeQuotes(columnNamePattern);
}
sql += " ORDER BY nspname,c.relname,attnum ";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
byte[][] tuple = new byte[numberOfFields][];
int typeOid = (int) rs.getLong("atttypid");
int typeMod = rs.getInt("atttypmod");
tuple[0] = null; // Catalog name, not supported
tuple[1] = rs.getBytes("nspname"); // Schema
tuple[2] = rs.getBytes("relname"); // Table name
tuple[3] = rs.getBytes("attname"); // Column name
String typtype = rs.getString("typtype");
int sqlType;
if ("c".equals(typtype)) {
sqlType = Types.STRUCT;
} else if ("d".equals(typtype)) {
sqlType = Types.DISTINCT;
} else if ("e".equals(typtype)) {
sqlType = Types.VARCHAR;
} else {
sqlType = connection.getTypeInfo().getSQLType(typeOid);
}
tuple[4] = connection.encodeString(Integer.toString(sqlType));
String pgType = connection.getTypeInfo().getPGType(typeOid);
tuple[5] = connection.encodeString(pgType); // Type name
tuple[7] = null; // Buffer length
String defval = rs.getString("adsrc");
if (defval != null) {
if (pgType.equals("int4")) {
if (defval.contains("nextval(")) {
tuple[5] = connection.encodeString("serial"); // Type name == serial
}
} else if (pgType.equals("int8")) {
if (defval.contains("nextval(")) {
tuple[5] = connection.encodeString("bigserial"); // Type name == bigserial
}
}
}
int decimalDigits = connection.getTypeInfo().getScale(typeOid, typeMod);
int columnSize = connection.getTypeInfo().getPrecision(typeOid, typeMod);
if (columnSize == 0) {
columnSize = connection.getTypeInfo().getDisplaySize(typeOid, typeMod);
}
tuple[6] = connection.encodeString(Integer.toString(columnSize));
tuple[8] = connection.encodeString(Integer.toString(decimalDigits));
// Everything is base 10 unless we override later.
tuple[9] = connection.encodeString("10");
if (pgType.equals("bit") || pgType.equals("varbit")) {
tuple[9] = connection.encodeString("2");
}
tuple[10] = connection.encodeString(Integer.toString(rs.getBoolean("attnotnull")
? java.sql.DatabaseMetaData.columnNoNulls : java.sql.DatabaseMetaData.columnNullable)); // Nullable
tuple[11] = rs.getBytes("description"); // Description (if any)
tuple[12] = rs.getBytes("adsrc"); // Column default
tuple[13] = null; // sql data type (unused)
tuple[14] = null; // sql datetime sub (unused)
tuple[15] = tuple[6]; // char octet length
tuple[16] = connection.encodeString(String.valueOf(rs.getInt("attnum"))); // ordinal position
// Is nullable
tuple[17] = connection.encodeString(rs.getBoolean("attnotnull") ? "NO" : "YES");
int baseTypeOid = (int) rs.getLong("typbasetype");
tuple[18] = null; // SCOPE_CATLOG
tuple[19] = null; // SCOPE_SCHEMA
tuple[20] = null; // SCOPE_TABLE
tuple[21] = baseTypeOid == 0
? null
: connection.encodeString(Integer.toString(connection.getTypeInfo().getSQLType(baseTypeOid))); // SOURCE_DATA_TYPE
String autoinc = "NO";
if (defval != null && defval.contains("nextval(")) {
autoinc = "YES";
}
tuple[22] = connection.encodeString(autoinc);
v.add(tuple);
}
rs.close();
stmt.close();
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
@Override
public ResultSet getColumnPrivileges(String catalog, String schema, String table,
String columnNamePattern) throws SQLException {
Field f[] = new Field[8];
List<byte[][]> v = new ArrayList<byte[][]>();
f[0] = new Field("TABLE_CAT", Oid.VARCHAR);
f[1] = new Field("TABLE_SCHEM", Oid.VARCHAR);
f[2] = new Field("TABLE_NAME", Oid.VARCHAR);
f[3] = new Field("COLUMN_NAME", Oid.VARCHAR);
f[4] = new Field("GRANTOR", Oid.VARCHAR);
f[5] = new Field("GRANTEE", Oid.VARCHAR);
f[6] = new Field("PRIVILEGE", Oid.VARCHAR);
f[7] = new Field("IS_GRANTABLE", Oid.VARCHAR);
String sql;
sql = "SELECT n.nspname,c.relname,r.rolname,c.relacl, "
+ (connection.haveMinimumServerVersion(ServerVersion.v8_4) ? "a.attacl, " : "")
+ " a.attname "
+ " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c, "
+ " pg_catalog.pg_roles r, pg_catalog.pg_attribute a "
+ " WHERE c.relnamespace = n.oid "
+ " AND c.relowner = r.oid "
+ " AND c.oid = a.attrelid "
+ " AND c.relkind = 'r' "
+ " AND a.attnum > 0 AND NOT a.attisdropped ";
if (schema != null && !schema.isEmpty()) {
sql += " AND n.nspname = " + escapeQuotes(schema);
}
if (table != null && !table.isEmpty()) {
sql += " AND c.relname = " + escapeQuotes(table);
}
if (columnNamePattern != null && !columnNamePattern.isEmpty()) {
sql += " AND a.attname LIKE " + escapeQuotes(columnNamePattern);
}
sql += " ORDER BY attname ";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
byte schemaName[] = rs.getBytes("nspname");
byte tableName[] = rs.getBytes("relname");
byte column[] = rs.getBytes("attname");
String owner = rs.getString("rolname");
String relAcl = rs.getString("relacl");
// For instance: SELECT -> user1 -> list of [grantor, grantable]
Map<String, Map<String, List<String[]>>> permissions = parseACL(relAcl, owner);
if (connection.haveMinimumServerVersion(ServerVersion.v8_4)) {
String acl = rs.getString("attacl");
Map<String, Map<String, List<String[]>>> relPermissions = parseACL(acl, owner);
permissions.putAll(relPermissions);
}
String permNames[] = permissions.keySet().toArray(new String[0]);
Arrays.sort(permNames);
for (String permName : permNames) {
byte[] privilege = connection.encodeString(permName);
Map<String, List<String[]>> grantees = permissions.get(permName);
for (Map.Entry<String, List<String[]>> userToGrantable : grantees.entrySet()) {
List<String[]> grantor = userToGrantable.getValue();
String grantee = userToGrantable.getKey();
for (String[] grants : grantor) {
String grantable = owner.equals(grantee) ? "YES" : grants[1];
byte[][] tuple = new byte[8][];
tuple[0] = null;
tuple[1] = schemaName;
tuple[2] = tableName;
tuple[3] = column;
tuple[4] = connection.encodeString(grants[0]);
tuple[5] = connection.encodeString(grantee);
tuple[6] = privilege;
tuple[7] = connection.encodeString(grantable);
v.add(tuple);
}
}
}
}
rs.close();
stmt.close();
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
@Override
public ResultSet getTablePrivileges(String catalog, String schemaPattern,
String tableNamePattern) throws SQLException {
Field f[] = new Field[7];
List<byte[][]> v = new ArrayList<byte[][]>();
f[0] = new Field("TABLE_CAT", Oid.VARCHAR);
f[1] = new Field("TABLE_SCHEM", Oid.VARCHAR);
f[2] = new Field("TABLE_NAME", Oid.VARCHAR);
f[3] = new Field("GRANTOR", Oid.VARCHAR);
f[4] = new Field("GRANTEE", Oid.VARCHAR);
f[5] = new Field("PRIVILEGE", Oid.VARCHAR);
f[6] = new Field("IS_GRANTABLE", Oid.VARCHAR);
String sql;
sql = "SELECT n.nspname,c.relname,r.rolname,c.relacl "
+ " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c, pg_catalog.pg_roles r "
+ " WHERE c.relnamespace = n.oid "
+ " AND c.relowner = r.oid "
+ " AND c.relkind = 'r' ";
if (schemaPattern != null && !schemaPattern.isEmpty()) {
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern);
}
if (tableNamePattern != null && !tableNamePattern.isEmpty()) {
sql += " AND c.relname LIKE " + escapeQuotes(tableNamePattern);
}
sql += " ORDER BY nspname, relname ";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
byte schema[] = rs.getBytes("nspname");
byte table[] = rs.getBytes("relname");
String owner = rs.getString("rolname");
String acl = rs.getString("relacl");
Map<String, Map<String, List<String[]>>> permissions = parseACL(acl, owner);
String permNames[] = permissions.keySet().toArray(new String[0]);
Arrays.sort(permNames);
for (String permName : permNames) {
byte[] privilege = connection.encodeString(permName);
Map<String, List<String[]>> grantees = permissions.get(permName);
for (Map.Entry<String, List<String[]>> userToGrantable : grantees.entrySet()) {
List<String[]> grants = userToGrantable.getValue();
String granteeUser = userToGrantable.getKey();
for (String[] grantTuple : grants) {
// report the owner as grantor if it's missing
String grantor = grantTuple[0] == null ? owner : grantTuple[0];
// owner always has grant privileges
String grantable = owner.equals(granteeUser) ? "YES" : grantTuple[1];
byte[][] tuple = new byte[7][];
tuple[0] = null;
tuple[1] = schema;
tuple[2] = table;
tuple[3] = connection.encodeString(grantor);
tuple[4] = connection.encodeString(granteeUser);
tuple[5] = privilege;
tuple[6] = connection.encodeString(grantable);
v.add(tuple);
}
}
}
}
rs.close();
stmt.close();
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
/**
* Parse an String of ACLs into a List of ACLs.
*/
private static List<String> parseACLArray(String aclString) {
List<String> acls = new ArrayList<String>();
if (aclString == null || aclString.isEmpty()) {
return acls;
}
boolean inQuotes = false;
// start at 1 because of leading "{"
int beginIndex = 1;
char prevChar = ' ';
for (int i = beginIndex; i < aclString.length(); i++) {
char c = aclString.charAt(i);
if (c == '"' && prevChar != '\\') {
inQuotes = !inQuotes;
} else if (c == ',' && !inQuotes) {
acls.add(aclString.substring(beginIndex, i));
beginIndex = i + 1;
}
prevChar = c;
}
// add last element removing the trailing "}"
acls.add(aclString.substring(beginIndex, aclString.length() - 1));
// Strip out enclosing quotes, if any.
for (int i = 0; i < acls.size(); i++) {
String acl = acls.get(i);
if (acl.startsWith("\"") && acl.endsWith("\"")) {
acl = acl.substring(1, acl.length() - 1);
acls.set(i, acl);
}
}
return acls;
}
/**
* Add the user described by the given acl to the Lists of users with the privileges described by
* the acl.
*/
private static void addACLPrivileges(String acl, Map<String, Map<String, List<String[]>>> privileges) {
int equalIndex = acl.lastIndexOf("=");
int slashIndex = acl.lastIndexOf("/");
if (equalIndex == -1) {
return;
}
String user = acl.substring(0, equalIndex);
String grantor = null;
if (user.isEmpty()) {
user = "PUBLIC";
}
String privs;
if (slashIndex != -1) {
privs = acl.substring(equalIndex + 1, slashIndex);
grantor = acl.substring(slashIndex + 1, acl.length());
} else {
privs = acl.substring(equalIndex + 1, acl.length());
}
for (int i = 0; i < privs.length(); i++) {
char c = privs.charAt(i);
if (c != '*') {
String sqlpriv;
String grantable;
if (i < privs.length() - 1 && privs.charAt(i + 1) == '*') {
grantable = "YES";
} else {
grantable = "NO";
}
switch (c) {
case 'a':
sqlpriv = "INSERT";
break;
case 'r':
sqlpriv = "SELECT";
break;
case 'w':
sqlpriv = "UPDATE";
break;
case 'd':
sqlpriv = "DELETE";
break;
case 'D':
sqlpriv = "TRUNCATE";
break;
case 'R':
sqlpriv = "RULE";
break;
case 'x':
sqlpriv = "REFERENCES";
break;
case 't':
sqlpriv = "TRIGGER";
break;
// the following can't be granted to a table, but
// we'll keep them for completeness.
case 'X':
sqlpriv = "EXECUTE";
break;
case 'U':
sqlpriv = "USAGE";
break;
case 'C':
sqlpriv = "CREATE";
break;
case 'T':
sqlpriv = "CREATE TEMP";
break;
default:
sqlpriv = "UNKNOWN";
}
Map<String, List<String[]>> usersWithPermission = privileges.get(sqlpriv);
String[] grant = {grantor, grantable};
if (usersWithPermission == null) {
usersWithPermission = new HashMap<String, List<String[]>>();
List<String[]> permissionByGrantor = new ArrayList<String[]>();
permissionByGrantor.add(grant);
usersWithPermission.put(user, permissionByGrantor);
privileges.put(sqlpriv, usersWithPermission);
} else {
List<String[]> permissionByGrantor = usersWithPermission.get(user);
if (permissionByGrantor == null) {
permissionByGrantor = new ArrayList<String[]>();
permissionByGrantor.add(grant);
usersWithPermission.put(user, permissionByGrantor);
} else {
permissionByGrantor.add(grant);
}
}
}
}
}
/**
* Take the a String representing an array of ACLs and return a Map mapping the SQL permission
* name to a List of usernames who have that permission.
* For instance: {@code SELECT -> user1 -> list of [grantor, grantable]}
*
* @param aclArray ACL array
* @param owner owner
* @return a Map mapping the SQL permission name
*/
public Map<String, Map<String, List<String[]>>> parseACL(String aclArray, String owner) {
if (aclArray == null) {
// arwdxt -- 8.2 Removed the separate RULE permission
// arwdDxt -- 8.4 Added a separate TRUNCATE permission
String perms = connection.haveMinimumServerVersion(ServerVersion.v8_4) ? "arwdDxt" : "arwdxt";
aclArray = "{" + owner + "=" + perms + "/" + owner + "}";
}
List<String> acls = parseACLArray(aclArray);
Map<String, Map<String, List<String[]>>> privileges =
new HashMap<String, Map<String, List<String[]>>>();
for (String acl : acls) {
addACLPrivileges(acl, privileges);
}
return privileges;
}
public ResultSet getBestRowIdentifier(String catalog, String schema, String table,
int scope, boolean nullable) throws SQLException {
Field f[] = new Field[8];
List<byte[][]> v = new ArrayList<byte[][]>(); // The new ResultSet tuple stuff
f[0] = new Field("SCOPE", Oid.INT2);
f[1] = new Field("COLUMN_NAME", Oid.VARCHAR);
f[2] = new Field("DATA_TYPE", Oid.INT2);
f[3] = new Field("TYPE_NAME", Oid.VARCHAR);
f[4] = new Field("COLUMN_SIZE", Oid.INT4);
f[5] = new Field("BUFFER_LENGTH", Oid.INT4);
f[6] = new Field("DECIMAL_DIGITS", Oid.INT2);
f[7] = new Field("PSEUDO_COLUMN", Oid.INT2);
/*
* At the moment this simply returns a table's primary key, if there is one. I believe other
* unique indexes, ctid, and oid should also be considered. -KJ
*/
String sql;
sql = "SELECT a.attname, a.atttypid, atttypmod "
+ "FROM pg_catalog.pg_class ct "
+ " JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) "
+ " JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) "
+ " JOIN (SELECT i.indexrelid, i.indrelid, i.indisprimary, "
+ " information_schema._pg_expandarray(i.indkey) AS keys "
+ " FROM pg_catalog.pg_index i) i "
+ " ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid) "
+ "WHERE true ";
if (schema != null && !schema.isEmpty()) {
sql += " AND n.nspname = " + escapeQuotes(schema);
}
sql += " AND ct.relname = " + escapeQuotes(table)
+ " AND i.indisprimary "
+ " ORDER BY a.attnum ";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
byte tuple[][] = new byte[8][];
int typeOid = (int) rs.getLong("atttypid");
int typeMod = rs.getInt("atttypmod");
int decimalDigits = connection.getTypeInfo().getScale(typeOid, typeMod);
int columnSize = connection.getTypeInfo().getPrecision(typeOid, typeMod);
if (columnSize == 0) {
columnSize = connection.getTypeInfo().getDisplaySize(typeOid, typeMod);
}
tuple[0] = connection.encodeString(Integer.toString(scope));
tuple[1] = rs.getBytes("attname");
tuple[2] =
connection.encodeString(Integer.toString(connection.getTypeInfo().getSQLType(typeOid)));
tuple[3] = connection.encodeString(connection.getTypeInfo().getPGType(typeOid));
tuple[4] = connection.encodeString(Integer.toString(columnSize));
tuple[5] = null; // unused
tuple[6] = connection.encodeString(Integer.toString(decimalDigits));
tuple[7] =
connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.bestRowNotPseudo));
v.add(tuple);
}
rs.close();
stmt.close();
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
public ResultSet getVersionColumns(String catalog, String schema, String table)
throws SQLException {
Field f[] = new Field[8];
List<byte[][]> v = new ArrayList<byte[][]>(); // The new ResultSet tuple stuff
f[0] = new Field("SCOPE", Oid.INT2);
f[1] = new Field("COLUMN_NAME", Oid.VARCHAR);
f[2] = new Field("DATA_TYPE", Oid.INT2);
f[3] = new Field("TYPE_NAME", Oid.VARCHAR);
f[4] = new Field("COLUMN_SIZE", Oid.INT4);
f[5] = new Field("BUFFER_LENGTH", Oid.INT4);
f[6] = new Field("DECIMAL_DIGITS", Oid.INT2);
f[7] = new Field("PSEUDO_COLUMN", Oid.INT2);
byte tuple[][] = new byte[8][];
/*
* Postgresql does not have any column types that are automatically updated like some databases'
* timestamp type. We can't tell what rules or triggers might be doing, so we are left with the
* system columns that change on an update. An update may change all of the following system
* columns: ctid, xmax, xmin, cmax, and cmin. Depending on if we are in a transaction and
* whether we roll it back or not the only guaranteed change is to ctid. -KJ
*/
tuple[0] = null;
tuple[1] = connection.encodeString("ctid");
tuple[2] =
connection.encodeString(Integer.toString(connection.getTypeInfo().getSQLType("tid")));
tuple[3] = connection.encodeString("tid");
tuple[4] = null;
tuple[5] = null;
tuple[6] = null;
tuple[7] =
connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.versionColumnPseudo));
v.add(tuple);
/*
* Perhaps we should check that the given catalog.schema.table actually exists. -KJ
*/
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
public ResultSet getPrimaryKeys(String catalog, String schema, String table)
throws SQLException {
String sql;
sql = "SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, "
+ " ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME, "
+ " (i.keys).n AS KEY_SEQ, ci.relname AS PK_NAME "
+ "FROM pg_catalog.pg_class ct "
+ " JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) "
+ " JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) "
+ " JOIN (SELECT i.indexrelid, i.indrelid, i.indisprimary, "
+ " information_schema._pg_expandarray(i.indkey) AS keys "
+ " FROM pg_catalog.pg_index i) i "
+ " ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid) "
+ " JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) "
+ "WHERE true ";
if (schema != null && !schema.isEmpty()) {
sql += " AND n.nspname = " + escapeQuotes(schema);
}
if (table != null && !table.isEmpty()) {
sql += " AND ct.relname = " + escapeQuotes(table);
}
sql += " AND i.indisprimary "
+ " ORDER BY table_name, pk_name, key_seq";
return createMetaDataStatement().executeQuery(sql);
}
/**
* @param primaryCatalog primary catalog
* @param primarySchema primary schema
* @param primaryTable if provided will get the keys exported by this table
* @param foreignCatalog foreign catalog
* @param foreignSchema foreign schema
* @param foreignTable if provided will get the keys imported by this table
* @return ResultSet
* @throws SQLException if something wrong happens
*/
protected ResultSet getImportedExportedKeys(String primaryCatalog, String primarySchema,
String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable)
throws SQLException {
/*
* The addition of the pg_constraint in 7.3 table should have really helped us out here, but it
* comes up just a bit short. - The conkey, confkey columns aren't really useful without
* contrib/array unless we want to issues separate queries. - Unique indexes that can support
* foreign keys are not necessarily added to pg_constraint. Also multiple unique indexes
* covering the same keys can be created which make it difficult to determine the PK_NAME field.
*/
String sql =
"SELECT NULL::text AS PKTABLE_CAT, pkn.nspname AS PKTABLE_SCHEM, pkc.relname AS PKTABLE_NAME, pka.attname AS PKCOLUMN_NAME, "
+ "NULL::text AS FKTABLE_CAT, fkn.nspname AS FKTABLE_SCHEM, fkc.relname AS FKTABLE_NAME, fka.attname AS FKCOLUMN_NAME, "
+ "pos.n AS KEY_SEQ, "
+ "CASE con.confupdtype "
+ " WHEN 'c' THEN " + DatabaseMetaData.importedKeyCascade
+ " WHEN 'n' THEN " + DatabaseMetaData.importedKeySetNull
+ " WHEN 'd' THEN " + DatabaseMetaData.importedKeySetDefault
+ " WHEN 'r' THEN " + DatabaseMetaData.importedKeyRestrict
+ " WHEN 'a' THEN " + DatabaseMetaData.importedKeyNoAction
+ " ELSE NULL END AS UPDATE_RULE, "
+ "CASE con.confdeltype "
+ " WHEN 'c' THEN " + DatabaseMetaData.importedKeyCascade
+ " WHEN 'n' THEN " + DatabaseMetaData.importedKeySetNull
+ " WHEN 'd' THEN " + DatabaseMetaData.importedKeySetDefault
+ " WHEN 'r' THEN " + DatabaseMetaData.importedKeyRestrict
+ " WHEN 'a' THEN " + DatabaseMetaData.importedKeyNoAction
+ " ELSE NULL END AS DELETE_RULE, "
+ "con.conname AS FK_NAME, pkic.relname AS PK_NAME, "
+ "CASE "
+ " WHEN con.condeferrable AND con.condeferred THEN "
+ DatabaseMetaData.importedKeyInitiallyDeferred
+ " WHEN con.condeferrable THEN " + DatabaseMetaData.importedKeyInitiallyImmediate
+ " ELSE " + DatabaseMetaData.importedKeyNotDeferrable
+ " END AS DEFERRABILITY "
+ " FROM "
+ " pg_catalog.pg_namespace pkn, pg_catalog.pg_class pkc, pg_catalog.pg_attribute pka, "
+ " pg_catalog.pg_namespace fkn, pg_catalog.pg_class fkc, pg_catalog.pg_attribute fka, "
+ " pg_catalog.pg_constraint con, "
+ " pg_catalog.generate_series(1, " + getMaxIndexKeys() + ") pos(n), "
+ " pg_catalog.pg_class pkic";
// Starting in Postgres 9.0, pg_constraint was augmented with the conindid column, which
// contains the oid of the index supporting the constraint. This makes it unnecessary to do a
// further join on pg_depend.
if (!connection.haveMinimumServerVersion(ServerVersion.v9_0)) {
sql += ", pg_catalog.pg_depend dep ";
}
sql +=
" WHERE pkn.oid = pkc.relnamespace AND pkc.oid = pka.attrelid AND pka.attnum = con.confkey[pos.n] AND con.confrelid = pkc.oid "
+ " AND fkn.oid = fkc.relnamespace AND fkc.oid = fka.attrelid AND fka.attnum = con.conkey[pos.n] AND con.conrelid = fkc.oid "
+ " AND con.contype = 'f' AND pkic.relkind = 'i' ";
if (!connection.haveMinimumServerVersion(ServerVersion.v9_0)) {
sql += " AND con.oid = dep.objid AND pkic.oid = dep.refobjid AND dep.classid = 'pg_constraint'::regclass::oid AND dep.refclassid = 'pg_class'::regclass::oid ";
} else {
sql += " AND pkic.oid = con.conindid ";
}
if (primarySchema != null && !primarySchema.isEmpty()) {
sql += " AND pkn.nspname = " + escapeQuotes(primarySchema);
}
if (foreignSchema != null && !foreignSchema.isEmpty()) {
sql += " AND fkn.nspname = " + escapeQuotes(foreignSchema);
}
if (primaryTable != null && !primaryTable.isEmpty()) {
sql += " AND pkc.relname = " + escapeQuotes(primaryTable);
}
if (foreignTable != null && !foreignTable.isEmpty()) {
sql += " AND fkc.relname = " + escapeQuotes(foreignTable);
}
if (primaryTable != null) {
sql += " ORDER BY fkn.nspname,fkc.relname,con.conname,pos.n";
} else {
sql += " ORDER BY pkn.nspname,pkc.relname, con.conname,pos.n";
}
return createMetaDataStatement().executeQuery(sql);
}
public ResultSet getImportedKeys(String catalog, String schema, String table)
throws SQLException {
return getImportedExportedKeys(null, null, null, catalog, schema, table);
}
public ResultSet getExportedKeys(String catalog, String schema, String table)
throws SQLException {
return getImportedExportedKeys(catalog, schema, table, null, null, null);
}
public ResultSet getCrossReference(String primaryCatalog, String primarySchema,
String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable)
throws SQLException {
return getImportedExportedKeys(primaryCatalog, primarySchema, primaryTable, foreignCatalog,
foreignSchema, foreignTable);
}
public ResultSet getTypeInfo() throws SQLException {
Field f[] = new Field[18];
List<byte[][]> v = new ArrayList<byte[][]>(); // The new ResultSet tuple stuff
f[0] = new Field("TYPE_NAME", Oid.VARCHAR);
f[1] = new Field("DATA_TYPE", Oid.INT2);
f[2] = new Field("PRECISION", Oid.INT4);
f[3] = new Field("LITERAL_PREFIX", Oid.VARCHAR);
f[4] = new Field("LITERAL_SUFFIX", Oid.VARCHAR);
f[5] = new Field("CREATE_PARAMS", Oid.VARCHAR);
f[6] = new Field("NULLABLE", Oid.INT2);
f[7] = new Field("CASE_SENSITIVE", Oid.BOOL);
f[8] = new Field("SEARCHABLE", Oid.INT2);
f[9] = new Field("UNSIGNED_ATTRIBUTE", Oid.BOOL);
f[10] = new Field("FIXED_PREC_SCALE", Oid.BOOL);
f[11] = new Field("AUTO_INCREMENT", Oid.BOOL);
f[12] = new Field("LOCAL_TYPE_NAME", Oid.VARCHAR);
f[13] = new Field("MINIMUM_SCALE", Oid.INT2);
f[14] = new Field("MAXIMUM_SCALE", Oid.INT2);
f[15] = new Field("SQL_DATA_TYPE", Oid.INT4);
f[16] = new Field("SQL_DATETIME_SUB", Oid.INT4);
f[17] = new Field("NUM_PREC_RADIX", Oid.INT4);
String sql;
sql = "SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
+ " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
+ " WHERE n.nspname != 'pg_toast'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// cache some results, this will keep memory usage down, and speed
// things up a little.
byte bZero[] = connection.encodeString("0");
byte b10[] = connection.encodeString("10");
byte bf[] = connection.encodeString("f");
byte bt[] = connection.encodeString("t");
byte bliteral[] = connection.encodeString("'");
byte bNullable[] =
connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.typeNullable));
byte bSearchable[] =
connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.typeSearchable));
List<String> typnames = new ArrayList<>();
Map<String,Integer> map = new HashMap<>();
while (rs.next()) {
// byte[][] tuple = new byte[18][];
String typname = rs.getString(1);
int typeOid = (int) rs.getLong(2);
//
// tuple[0] = connection.encodeString(typname);
// if(typname.startsWith("proc")){
// System.out.println(typname);
// }
typnames.add(typname);
map.put(typname, typeOid);
tuple[1] =
connection.encodeString(Integer.toString(sqlType));
// tuple[2] = connection
// .encodeString(Integer.toString(connection.getTypeInfo().getMaximumPrecision(typeOid)));
//
// // Using requiresQuoting(oid) would might trigger select statements that might fail with NPE
// // if oid in question is being dropped.
// // requiresQuotingSqlType is not bulletproof, however, it solves the most visible NPE.
if (connection.getTypeInfo().requiresQuotingSqlType(sqlType)) {
tuple[3] = bliteral;
tuple[4] = bliteral;
}
//
// tuple[6] = bNullable; // all types can be null
// tuple[7] = connection.getTypeInfo().isCaseSensitive(typeOid) ? bt : bf;
// tuple[8] = bSearchable; // any thing can be used in the WHERE clause
// tuple[9] = connection.getTypeInfo().isSigned(typeOid) ? bf : bt;
// tuple[10] = bf; // false for now - must handle money
// tuple[11] = bf; // false - it isn't autoincrement
// tuple[13] = bZero; // min scale is zero
// // only numeric can supports a scale.
// tuple[14] = (typeOid == Oid.NUMERIC) ? connection.encodeString("1000") : bZero;
//
// // 12 - LOCAL_TYPE_NAME is null
// // 15 & 16 are unused so we return null
// tuple[17] = b10; // everything is base 10
// v.add(tuple);
//
// // add pseudo-type serial, bigserial
// if (typname.equals("int4")) {
// byte[][] tuple1 = tuple.clone();
//
// tuple1[0] = connection.encodeString("serial");
// tuple1[11] = bt;
// v.add(tuple1);
// } else if (typname.equals("int8")) {
// byte[][] tuple1 = tuple.clone();
//
// tuple1[0] = connection.encodeString("bigserial");
// tuple1[11] = bt;
// v.add(tuple1);
// }
}
Map<String,Integer> types = connection.getTypeInfo().getSQLType(typnames.toArray(new String[]{}));
for(Map.Entry<String, Integer> entry : types.entrySet()){
Integer type = entry.getValue();
if(null != type){
byte[][] tuple = new byte[18][];
String typname = entry.getKey();
int typeOid = map.get(typname);
tuple[0] = connection.encodeString(typname);
tuple[1] =
connection.encodeString(Integer.toString(entry.getValue()));
tuple[2] = connection
.encodeString(Integer.toString(connection.getTypeInfo().getMaximumPrecision(typeOid)));
// Using requiresQuoting(oid) would might trigger select statements that might fail with NPE
// if oid in question is being dropped.
// requiresQuotingSqlType is not bulletproof, however, it solves the most visible NPE.
if (connection.getTypeInfo().requiresQuotingSqlType(entry.getValue())) {
tuple[3] = bliteral;
tuple[4] = bliteral;
}
tuple[6] = bNullable; // all types can be null
tuple[7] = connection.getTypeInfo().isCaseSensitive(typeOid) ? bt : bf;
tuple[8] = bSearchable; // any thing can be used in the WHERE clause
tuple[9] = connection.getTypeInfo().isSigned(typeOid) ? bf : bt;
tuple[10] = bf; // false for now - must handle money
tuple[11] = bf; // false - it isn't autoincrement
tuple[13] = bZero; // min scale is zero
// only numeric can supports a scale.
tuple[14] = (typeOid == Oid.NUMERIC) ? connection.encodeString("1000") : bZero;
// 12 - LOCAL_TYPE_NAME is null
// 15 & 16 are unused so we return null
tuple[17] = b10; // everything is base 10
v.add(tuple);
// add pseudo-type serial, bigserial
if (typname.equals("int4")) {
byte[][] tuple1 = tuple.clone();
tuple1[0] = connection.encodeString("serial");
tuple1[11] = bt;
v.add(tuple1);
} else if (typname.equals("int8")) {
byte[][] tuple1 = tuple.clone();
tuple1[0] = connection.encodeString("bigserial");
tuple1[11] = bt;
v.add(tuple1);
}
}
}
rs.close();
stmt.close();
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
public ResultSet getIndexInfo(String catalog, String schema, String tableName,
boolean unique, boolean approximate) throws SQLException {
/*
* This is a complicated function because we have three possible situations: <= 7.2 no schemas,
* single column functional index 7.3 schemas, single column functional index >= 7.4 schemas,
* multi-column expressional index >= 8.3 supports ASC/DESC column info >= 9.0 no longer renames
* index columns on a table column rename, so we must look at the table attribute names
*
* with the single column functional index we need an extra join to the table's pg_attribute
* data to get the column the function operates on.
*/
String sql;
if (connection.haveMinimumServerVersion(ServerVersion.v8_3)) {
sql = "SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, "
+ " ct.relname AS TABLE_NAME, NOT i.indisunique AS NON_UNIQUE, "
+ " NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME, "
+ " CASE i.indisclustered "
+ " WHEN true THEN " + java.sql.DatabaseMetaData.tableIndexClustered
+ " ELSE CASE am.amname "
+ " WHEN 'hash' THEN " + java.sql.DatabaseMetaData.tableIndexHashed
+ " ELSE " + java.sql.DatabaseMetaData.tableIndexOther
+ " END "
+ " END AS TYPE, "
+ " (i.keys).n AS ORDINAL_POSITION, "
+ " trim(both '\"' from pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false)) AS COLUMN_NAME, "
+ (connection.haveMinimumServerVersion(ServerVersion.v9_6)
? " CASE am.amname "
+ " WHEN 'btree' THEN CASE i.indoption[(i.keys).n - 1] & 1 "
+ " WHEN 1 THEN 'D' "
+ " ELSE 'A' "
+ " END "
+ " ELSE NULL "
+ " END AS ASC_OR_DESC, "
: " CASE am.amcanorder "
+ " WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1 "
+ " WHEN 1 THEN 'D' "
+ " ELSE 'A' "
+ " END "
+ " ELSE NULL "
+ " END AS ASC_OR_DESC, ")
+ " ci.reltuples AS CARDINALITY, "
+ " ci.relpages AS PAGES, "
+ " pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION "
+ "FROM pg_catalog.pg_class ct "
+ " JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) "
+ " JOIN (SELECT i.indexrelid, i.indrelid, i.indoption, "
+ " i.indisunique, i.indisclustered, i.indpred, "
+ " i.indexprs, "
+ " information_schema._pg_expandarray(i.indkey) AS keys "
+ " FROM pg_catalog.pg_index i) i "
+ " ON (ct.oid = i.indrelid) "
+ " JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) "
+ " JOIN pg_catalog.pg_am am ON (ci.relam = am.oid) "
+ "WHERE true ";
if (schema != null && !schema.isEmpty()) {
sql += " AND n.nspname = " + escapeQuotes(schema);
}
} else {
String select;
String from;
String where;
select = "SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ";
from = " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, "
+ " pg_catalog.pg_attribute a, pg_catalog.pg_am am ";
where = " AND n.oid = ct.relnamespace ";
from += ", pg_catalog.pg_index i ";
if (schema != null && !schema.isEmpty()) {
where += " AND n.nspname = " + escapeQuotes(schema);
}
sql = select
+ " ct.relname AS TABLE_NAME, NOT i.indisunique AS NON_UNIQUE, NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME, "
+ " CASE i.indisclustered "
+ " WHEN true THEN " + java.sql.DatabaseMetaData.tableIndexClustered
+ " ELSE CASE am.amname "
+ " WHEN 'hash' THEN " + java.sql.DatabaseMetaData.tableIndexHashed
+ " ELSE " + java.sql.DatabaseMetaData.tableIndexOther
+ " END "
+ " END AS TYPE, "
+ " a.attnum AS ORDINAL_POSITION, "
+ " CASE WHEN i.indexprs IS NULL THEN a.attname "
+ " ELSE pg_catalog.pg_get_indexdef(ci.oid,a.attnum,false) END AS COLUMN_NAME, "
+ " NULL AS ASC_OR_DESC, "
+ " ci.reltuples AS CARDINALITY, "
+ " ci.relpages AS PAGES, "
+ " pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION "
+ from
+ " WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid AND ci.relam=am.oid "
+ where;
}
sql += " AND ct.relname = " + escapeQuotes(tableName);
if (unique) {
sql += " AND i.indisunique ";
}
sql += " ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION ";
return createMetaDataStatement().executeQuery(sql);
}
// ** JDBC 2 Extensions **
public boolean supportsResultSetType(int type) throws SQLException {
// The only type we don't support
return type != ResultSet.TYPE_SCROLL_SENSITIVE;
}
public boolean supportsResultSetConcurrency(int type, int concurrency) throws SQLException {
// These combinations are not supported!
if (type == ResultSet.TYPE_SCROLL_SENSITIVE) {
return false;
}
// We do support Updateable ResultSets
if (concurrency == ResultSet.CONCUR_UPDATABLE) {
return true;
}
// Everything else we do
return true;
}
/* lots of unsupported stuff... */
public boolean ownUpdatesAreVisible(int type) throws SQLException {
return true;
}
public boolean ownDeletesAreVisible(int type) throws SQLException {
return true;
}
public boolean ownInsertsAreVisible(int type) throws SQLException {
// indicates that
return true;
}
public boolean othersUpdatesAreVisible(int type) throws SQLException {
return false;
}
public boolean othersDeletesAreVisible(int i) throws SQLException {
return false;
}
public boolean othersInsertsAreVisible(int type) throws SQLException {
return false;
}
public boolean updatesAreDetected(int type) throws SQLException {
return false;
}
public boolean deletesAreDetected(int i) throws SQLException {
return false;
}
public boolean insertsAreDetected(int type) throws SQLException {
return false;
}
public boolean supportsBatchUpdates() throws SQLException {
return true;
}
public ResultSet getUDTs(String catalog, String schemaPattern, String typeNamePattern,
int[] types) throws SQLException {
String sql = "select "
+ "null as type_cat, n.nspname as type_schem, t.typname as type_name, null as class_name, "
+ "CASE WHEN t.typtype='c' then " + java.sql.Types.STRUCT + " else "
+ java.sql.Types.DISTINCT
+ " end as data_type, pg_catalog.obj_description(t.oid, 'pg_type') "
+ "as remarks, CASE WHEN t.typtype = 'd' then (select CASE";
StringBuilder sqlwhen = new StringBuilder();
for (Iterator<String> i = connection.getTypeInfo().getPGTypeNamesWithSQLTypes(); i.hasNext(); ) {
String pgType = i.next();
int sqlType = connection.getTypeInfo().getSQLType(pgType);
sqlwhen.append(" when typname = ").append(escapeQuotes(pgType)).append(" then ").append(sqlType);
}
sql += sqlwhen.toString();
sql += " else " + java.sql.Types.OTHER + " end from pg_type where oid=t.typbasetype) "
+ "else null end as base_type "
+ "from pg_catalog.pg_type t, pg_catalog.pg_namespace n where t.typnamespace = n.oid and n.nspname != 'pg_catalog' and n.nspname != 'pg_toast'";
StringBuilder toAdd = new StringBuilder();
if (types != null) {
toAdd.append(" and (false ");
for (int type : types) {
switch (type) {
case Types.STRUCT:
toAdd.append(" or t.typtype = 'c'");
break;
case Types.DISTINCT:
toAdd.append(" or t.typtype = 'd'");
break;
}
}
toAdd.append(" ) ");
} else {
toAdd.append(" and t.typtype IN ('c','d') ");
}
// spec says that if typeNamePattern is a fully qualified name
// then the schema and catalog are ignored
if (typeNamePattern != null) {
// search for qualifier
int firstQualifier = typeNamePattern.indexOf('.');
int secondQualifier = typeNamePattern.lastIndexOf('.');
if (firstQualifier != -1) {
// if one of them is -1 they both will be
if (firstQualifier != secondQualifier) {
// we have a catalog.schema.typename, ignore catalog
schemaPattern = typeNamePattern.substring(firstQualifier + 1, secondQualifier);
} else {
// we just have a schema.typename
schemaPattern = typeNamePattern.substring(0, firstQualifier);
}
// strip out just the typeName
typeNamePattern = typeNamePattern.substring(secondQualifier + 1);
}
toAdd.append(" and t.typname like ").append(escapeQuotes(typeNamePattern));
}
// schemaPattern may have been modified above
if (schemaPattern != null) {
toAdd.append(" and n.nspname like ").append(escapeQuotes(schemaPattern));
}
sql += toAdd.toString();
sql += " order by data_type, type_schem, type_name";
return createMetaDataStatement().executeQuery(sql);
}
@Override
public Connection getConnection() throws SQLException {
return connection;
}
protected Statement createMetaDataStatement() throws SQLException {
return connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}
public long getMaxLogicalLobSize() throws SQLException {
return 0;
}
public boolean supportsRefCursors() throws SQLException {
return true;
}
@Override
public RowIdLifetime getRowIdLifetime() throws SQLException {
throw org.postgresql.Driver.notImplemented(this.getClass(), "getRowIdLifetime()");
}
@Override
public boolean supportsStoredFunctionsUsingCallSyntax() throws SQLException {
return true;
}
@Override
public boolean autoCommitFailureClosesAllResultSets() throws SQLException {
return false;
}
@Override
public ResultSet getClientInfoProperties() throws SQLException {
Field f[] = new Field[4];
f[0] = new Field("NAME", Oid.VARCHAR);
f[1] = new Field("MAX_LEN", Oid.INT4);
f[2] = new Field("DEFAULT_VALUE", Oid.VARCHAR);
f[3] = new Field("DESCRIPTION", Oid.VARCHAR);
List<byte[][]> v = new ArrayList<byte[][]>();
if (connection.haveMinimumServerVersion(ServerVersion.v9_0)) {
byte[][] tuple = new byte[4][];
tuple[0] = connection.encodeString("ApplicationName");
tuple[1] = connection.encodeString(Integer.toString(getMaxNameLength()));
tuple[2] = connection.encodeString("");
tuple[3] = connection
.encodeString("The name of the application currently utilizing the connection.");
v.add(tuple);
}
return ((BaseStatement) createMetaDataStatement()).createDriverResultSet(f, v);
}
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return iface.isAssignableFrom(getClass());
}
public <T> T unwrap(Class<T> iface) throws SQLException {
if (iface.isAssignableFrom(getClass())) {
return iface.cast(this);
}
throw new SQLException("Cannot unwrap to " + iface.getName());
}
public ResultSet getFunctions(String catalog, String schemaPattern, String functionNamePattern)
throws SQLException {
return getProcedures(catalog, schemaPattern, functionNamePattern);
}
public ResultSet getFunctionColumns(String catalog, String schemaPattern,
String functionNamePattern, String columnNamePattern)
throws SQLException {
return getProcedureColumns(catalog, schemaPattern, functionNamePattern, columnNamePattern);
}
public ResultSet getPseudoColumns(String catalog, String schemaPattern, String tableNamePattern,
String columnNamePattern) throws SQLException {
throw org.postgresql.Driver.notImplemented(this.getClass(),
"getPseudoColumns(String, String, String, String)");
}
public boolean generatedKeyAlwaysReturned() throws SQLException {
return true;
}
public boolean supportsSavepoints() throws SQLException {
return true;
}
public boolean supportsNamedParameters() throws SQLException {
return false;
}
public boolean supportsMultipleOpenResults() throws SQLException {
return false;
}
public boolean supportsGetGeneratedKeys() throws SQLException {
// We don't support returning generated keys by column index,
// but that should be a rarer case than the ones we do support.
//
return true;
}
public ResultSet getSuperTypes(String catalog, String schemaPattern, String typeNamePattern)
throws SQLException {
throw org.postgresql.Driver.notImplemented(this.getClass(),
"getSuperTypes(String,String,String)");
}
public ResultSet getSuperTables(String catalog, String schemaPattern, String tableNamePattern)
throws SQLException {
throw org.postgresql.Driver.notImplemented(this.getClass(),
"getSuperTables(String,String,String,String)");
}
public ResultSet getAttributes(String catalog, String schemaPattern, String typeNamePattern,
String attributeNamePattern) throws SQLException {
throw org.postgresql.Driver.notImplemented(this.getClass(),
"getAttributes(String,String,String,String)");
}
public boolean supportsResultSetHoldability(int holdability) throws SQLException {
return true;
}
public int getResultSetHoldability() throws SQLException {
return ResultSet.HOLD_CURSORS_OVER_COMMIT;
}
@Override
public int getDatabaseMajorVersion() throws SQLException {
return connection.getServerMajorVersion();
}
@Override
public int getDatabaseMinorVersion() throws SQLException {
return connection.getServerMinorVersion();
}
@Override
public int getJDBCMajorVersion() {
return org.postgresql.util.DriverInfo.JDBC_MAJOR_VERSION;
}
@Override
public int getJDBCMinorVersion() {
return org.postgresql.util.DriverInfo.JDBC_MINOR_VERSION;
}
public int getSQLStateType() throws SQLException {
return sqlStateSQL;
}
public boolean locatorsUpdateCopy() throws SQLException {
/*
* Currently LOB's aren't updateable at all, so it doesn't matter what we return. We don't throw
* the notImplemented Exception because the 1.5 JDK's CachedRowSet calls this method regardless
* of whether large objects are used.
*/
return true;
}
public boolean supportsStatementPooling() throws SQLException {
return false;
}
}
/*
* Copyright (c) 2008, PostgreSQL Global Development Group
* See the LICENSE file in the project root for more information.
*/
package org.postgresql.core;
import org.postgresql.util.PGobject;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Map;
public interface TypeInfo {
void addCoreType(String pgTypeName, Integer oid, Integer sqlType, String javaClass,
Integer arrayOid);
void addDataType(String type, Class<? extends PGobject> klass) throws SQLException;
/**
* Look up the SQL typecode for a given type oid.
*
* @param oid the type's OID
* @return the SQL type code (a constant from {@link java.sql.Types}) for the type
* @throws SQLException if an error occurs when retrieving sql type
*/
int getSQLType(int oid) throws SQLException;
/**
* Look up the SQL typecode for a given postgresql type name.
*
* @param pgTypeName the server type name to look up
* @return the SQL type code (a constant from {@link java.sql.Types}) for the type
* @throws SQLException if an error occurs when retrieving sql type
*/
int getSQLType(String pgTypeName) throws SQLException;
Map<String,Integer> getSQLType(String[] pgTypeNames)throws SQLException;
/**
* Look up the oid for a given postgresql type name. This is the inverse of
* {@link #getPGType(int)}.
*
* @param pgTypeName the server type name to look up
* @return the type's OID, or 0 if unknown
* @throws SQLException if an error occurs when retrieving PG type
*/
int getPGType(String pgTypeName) throws SQLException;
/**
* Look up the postgresql type name for a given oid. This is the inverse of
* {@link #getPGType(String)}.
*
* @param oid the type's OID
* @return the server type name for that OID or null if unknown
* @throws SQLException if an error occurs when retrieving PG type
*/
String getPGType(int oid) throws SQLException;
/**
* Look up the oid of an array's base type given the array's type oid.
*
* @param oid the array type's OID
* @return the base type's OID, or 0 if unknown
* @throws SQLException if an error occurs when retrieving array element
*/
int getPGArrayElement(int oid) throws SQLException;
/**
* Determine the oid of the given base postgresql type's array type
*
* @param elementTypeName the base type's
* @return the array type's OID, or 0 if unknown
* @throws SQLException if an error occurs when retrieving array type
*/
int getPGArrayType(String elementTypeName) throws SQLException;
/**
* Determine the delimiter for the elements of the given array type oid.
*
* @param oid the array type's OID
* @return the base type's array type delimiter
* @throws SQLException if an error occurs when retrieving array delimiter
*/
char getArrayDelimiter(int oid) throws SQLException;
Iterator<String> getPGTypeNamesWithSQLTypes();
Class<? extends PGobject> getPGobject(String type);
String getJavaClass(int oid) throws SQLException;
String getTypeForAlias(String alias);
int getPrecision(int oid, int typmod);
int getScale(int oid, int typmod);
boolean isCaseSensitive(int oid);
boolean isSigned(int oid);
int getDisplaySize(int oid, int typmod);
int getMaximumPrecision(int oid);
boolean requiresQuoting(int oid) throws SQLException;
/**
* Returns true if particular sqlType requires quoting.
* This method is used internally by the driver, so it might disappear without notice.
*
* @param sqlType sql type as in java.sql.Types
* @return true if the type requires quoting
* @throws SQLException if something goes wrong
*/
boolean requiresQuotingSqlType(int sqlType) throws SQLException;
}
/*
* Copyright (c) 2005, PostgreSQL Global Development Group
* See the LICENSE file in the project root for more information.
*/
package org.postgresql.jdbc;
import org.postgresql.core.BaseConnection;
import org.postgresql.core.BaseStatement;
import org.postgresql.core.Oid;
import org.postgresql.core.QueryExecutor;
import org.postgresql.core.ServerVersion;
import org.postgresql.core.TypeInfo;
import org.postgresql.util.GT;
import org.postgresql.util.PGobject;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
public class TypeInfoCache implements TypeInfo {
// pgname (String) -> java.sql.Types (Integer)
private Map<String, Integer> _pgNameToSQLType;
// pgname (String) -> java class name (String)
// ie "text" -> "java.lang.String"
private Map<String, String> _pgNameToJavaClass;
// oid (Integer) -> pgname (String)
private Map<Integer, String> _oidToPgName;
// pgname (String) -> oid (Integer)
private Map<String, Integer> _pgNameToOid;
// pgname (String) -> extension pgobject (Class)
private Map<String, Class<? extends PGobject>> _pgNameToPgObject;
// type array oid -> base type's oid
private Map<Integer, Integer> _pgArrayToPgType;
// array type oid -> base type array element delimiter
private Map<Integer, Character> _arrayOidToDelimiter;
private BaseConnection _conn;
private final int _unknownLength;
private PreparedStatement _getOidStatementSimple;
private PreparedStatement _getOidStatementComplexNonArray;
private PreparedStatement _getOidStatementComplexArray;
private PreparedStatement _getNameStatement;
private PreparedStatement _getArrayElementOidStatement;
private PreparedStatement _getArrayDelimiterStatement;
private PreparedStatement _getTypeInfoStatement;
// basic pg types info:
// 0 - type name
// 1 - type oid
// 2 - sql type
// 3 - java class
// 4 - array type oid
private static final Object types[][] = {
{"int2", Oid.INT2, Types.SMALLINT, "java.lang.Integer", Oid.INT2_ARRAY},
{"int4", Oid.INT4, Types.INTEGER, "java.lang.Integer", Oid.INT4_ARRAY},
{"oid", Oid.OID, Types.BIGINT, "java.lang.Long", Oid.OID_ARRAY},
{"int8", Oid.INT8, Types.BIGINT, "java.lang.Long", Oid.INT8_ARRAY},
{"money", Oid.MONEY, Types.DOUBLE, "java.lang.Double", Oid.MONEY_ARRAY},
{"numeric", Oid.NUMERIC, Types.NUMERIC, "java.math.BigDecimal", Oid.NUMERIC_ARRAY},
{"float4", Oid.FLOAT4, Types.REAL, "java.lang.Float", Oid.FLOAT4_ARRAY},
{"float8", Oid.FLOAT8, Types.DOUBLE, "java.lang.Double", Oid.FLOAT8_ARRAY},
{"char", Oid.CHAR, Types.CHAR, "java.lang.String", Oid.CHAR_ARRAY},
{"bpchar", Oid.BPCHAR, Types.CHAR, "java.lang.String", Oid.BPCHAR_ARRAY},
{"varchar", Oid.VARCHAR, Types.VARCHAR, "java.lang.String", Oid.VARCHAR_ARRAY},
{"text", Oid.TEXT, Types.VARCHAR, "java.lang.String", Oid.TEXT_ARRAY},
{"name", Oid.NAME, Types.VARCHAR, "java.lang.String", Oid.NAME_ARRAY},
{"bytea", Oid.BYTEA, Types.BINARY, "[B", Oid.BYTEA_ARRAY},
{"bool", Oid.BOOL, Types.BIT, "java.lang.Boolean", Oid.BOOL_ARRAY},
{"bit", Oid.BIT, Types.BIT, "java.lang.Boolean", Oid.BIT_ARRAY},
{"date", Oid.DATE, Types.DATE, "java.sql.Date", Oid.DATE_ARRAY},
{"time", Oid.TIME, Types.TIME, "java.sql.Time", Oid.TIME_ARRAY},
{"timetz", Oid.TIMETZ, Types.TIME, "java.sql.Time", Oid.TIMETZ_ARRAY},
{"timestamp", Oid.TIMESTAMP, Types.TIMESTAMP, "java.sql.Timestamp", Oid.TIMESTAMP_ARRAY},
{"timestamptz", Oid.TIMESTAMPTZ, Types.TIMESTAMP, "java.sql.Timestamp",
Oid.TIMESTAMPTZ_ARRAY},
//JCP! if mvn.project.property.postgresql.jdbc.spec >= "JDBC4.2"
//JCP> {"refcursor", Oid.REF_CURSOR, Types.REF_CURSOR, "java.sql.ResultSet", Oid.REF_CURSOR_ARRAY},
//JCP! endif
{"json", Oid.JSON, Types.OTHER, "org.postgresql.util.PGobject", Oid.JSON_ARRAY},
{"point", Oid.POINT, Types.OTHER, "org.postgresql.geometric.PGpoint", Oid.POINT_ARRAY}
};
/**
* PG maps several alias to real type names. When we do queries against pg_catalog, we must use
* the real type, not an alias, so use this mapping.
*/
private final static HashMap<String, String> typeAliases;
static {
typeAliases = new HashMap<String, String>();
typeAliases.put("smallint", "int2");
typeAliases.put("integer", "int4");
typeAliases.put("int", "int4");
typeAliases.put("bigint", "int8");
typeAliases.put("float", "float8");
typeAliases.put("boolean", "bool");
typeAliases.put("decimal", "numeric");
}
public TypeInfoCache(BaseConnection conn, int unknownLength) {
_conn = conn;
_unknownLength = unknownLength;
_oidToPgName = new HashMap<Integer, String>();
_pgNameToOid = new HashMap<String, Integer>();
_pgNameToJavaClass = new HashMap<String, String>();
_pgNameToPgObject = new HashMap<String, Class<? extends PGobject>>();
_pgArrayToPgType = new HashMap<Integer, Integer>();
_arrayOidToDelimiter = new HashMap<Integer, Character>();
// needs to be synchronized because the iterator is returned
// from getPGTypeNamesWithSQLTypes()
_pgNameToSQLType = Collections.synchronizedMap(new HashMap<String, Integer>());
for (Object[] type : types) {
String pgTypeName = (String) type[0];
Integer oid = (Integer) type[1];
Integer sqlType = (Integer) type[2];
String javaClass = (String) type[3];
Integer arrayOid = (Integer) type[4];
addCoreType(pgTypeName, oid, sqlType, javaClass, arrayOid);
}
_pgNameToJavaClass.put("hstore", Map.class.getName());
}
public synchronized void addCoreType(String pgTypeName, Integer oid, Integer sqlType,
String javaClass, Integer arrayOid) {
_pgNameToJavaClass.put(pgTypeName, javaClass);
_pgNameToOid.put(pgTypeName, oid);
_oidToPgName.put(oid, pgTypeName);
_pgArrayToPgType.put(arrayOid, oid);
_pgNameToSQLType.put(pgTypeName, sqlType);
// Currently we hardcode all core types array delimiter
// to a comma. In a stock install the only exception is
// the box datatype and it's not a JDBC core type.
//
Character delim = ',';
_arrayOidToDelimiter.put(oid, delim);
String pgArrayTypeName = pgTypeName + "[]";
_pgNameToJavaClass.put(pgArrayTypeName, "java.sql.Array");
_pgNameToSQLType.put(pgArrayTypeName, Types.ARRAY);
_pgNameToOid.put(pgArrayTypeName, arrayOid);
pgArrayTypeName = "_" + pgTypeName;
if (!_pgNameToJavaClass.containsKey(pgArrayTypeName)) {
_pgNameToJavaClass.put(pgArrayTypeName, "java.sql.Array");
_pgNameToSQLType.put(pgArrayTypeName, Types.ARRAY);
_pgNameToOid.put(pgArrayTypeName, arrayOid);
_oidToPgName.put(arrayOid, pgArrayTypeName);
}
}
public synchronized void addDataType(String type, Class<? extends PGobject> klass)
throws SQLException {
_pgNameToPgObject.put(type, klass);
_pgNameToJavaClass.put(type, klass.getName());
}
public Iterator<String> getPGTypeNamesWithSQLTypes() {
return _pgNameToSQLType.keySet().iterator();
}
public int getSQLType(int oid) throws SQLException {
return getSQLType(getPGType(oid));
}
public synchronized int getSQLType(String pgTypeName) throws SQLException {
if (pgTypeName.endsWith("[]")) {
return Types.ARRAY;
}
Integer i = _pgNameToSQLType.get(pgTypeName);
if (i != null) {
return i;
}
if (_getTypeInfoStatement == null) {
// There's no great way of telling what's an array type.
// People can name their own types starting with _.
// Other types use typelem that aren't actually arrays, like box.
//
String sql;
// in case of multiple records (in different schemas) choose the one from the current
// schema,
// otherwise take the last version of a type that is at least more deterministic then before
// (keeping old behaviour of finding types, that should not be found without correct search
// path)
sql = "SELECT typinput='array_in'::regproc, typtype "
+ " FROM pg_catalog.pg_type "
+ " LEFT "
+ " JOIN (select ns.oid as nspoid, ns.nspname, r.r "
+ " from pg_namespace as ns "
// -- go with older way of unnesting array to be compatible with 8.0
+ " join ( select s.r, (current_schemas(false))[s.r] as nspname "
+ " from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r "
+ " using ( nspname ) "
+ " ) as sp "
+ " ON sp.nspoid = typnamespace "
+ " WHERE typname = ? "
+ " ORDER BY sp.r, pg_type.oid DESC LIMIT 1;";
_getTypeInfoStatement = _conn.prepareStatement(sql);
}
_getTypeInfoStatement.setString(1, pgTypeName);
// Go through BaseStatement to avoid transaction start.
if (!((BaseStatement) _getTypeInfoStatement)
.executeWithFlags(QueryExecutor.QUERY_SUPPRESS_BEGIN)) {
throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
}
ResultSet rs = _getTypeInfoStatement.getResultSet();
Integer type = null;
if (rs.next()) {
boolean isArray = rs.getBoolean(1);
String typtype = rs.getString(2);
if (isArray) {
type = Types.ARRAY;
} else if ("c".equals(typtype)) {
type = Types.STRUCT;
} else if ("d".equals(typtype)) {
type = Types.DISTINCT;
} else if ("e".equals(typtype)) {
type = Types.VARCHAR;
}
}
if (type == null) {
type = Types.OTHER;
}
rs.close();
_pgNameToSQLType.put(pgTypeName, type);
return type;
}
public synchronized Map<String,Integer> getSQLType(String[] pgTypeNames) throws SQLException {
int len = pgTypeNames.length;
Map<String,Integer> types = new HashMap<>();
if(null != pgTypeNames){
String pgTypeName = null;
// There's no great way of telling what's an array type.
// People can name their own types starting with _.
// Other types use typelem that aren't actually arrays, like box.
//
String sql;
// in case of multiple records (in different schemas) choose the one from the current
// schema,
// otherwise take the last version of a type that is at least more deterministic then before
// (keeping old behaviour of finding types, that should not be found without correct search
// path)
sql = "SELECT typname,typinput='array_in'::regproc, typtype "
+ " FROM pg_catalog.pg_type "
+ " LEFT "
+ " JOIN (select ns.oid as nspoid, ns.nspname, r.r "
+ " from pg_namespace as ns "
// -- go with older way of unnesting array to be compatible with 8.0
+ " join ( select s.r, (current_schemas(false))[s.r] as nspname "
+ " from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r "
+ " using ( nspname ) "
+ " ) as sp "
+ " ON sp.nspoid = typnamespace WHERE typname in (";
StringBuilder builder = new StringBuilder();
boolean first = true;
for (int i = 0; i < len; i++) {
pgTypeName = pgTypeNames[i];
if(first == false){
builder.append(',');
}else{
first = false;
}
builder.append('\'').append(pgTypeName).append('\'');
}
builder.append(')');
sql+=builder.toString();
_getTypeInfoStatement = _conn.prepareStatement(sql);
// _getTypeInfoStatement.setString(1, pgTypeName);
// Go through BaseStatement to avoid transaction start.
if (!((BaseStatement) _getTypeInfoStatement)
.executeWithFlags(QueryExecutor.QUERY_SUPPRESS_BEGIN)) {
throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
}
ResultSet rs = _getTypeInfoStatement.getResultSet();
Integer type = null;
while (rs.next()) {
String typname = rs.getString(1);
boolean isArray = rs.getBoolean(2);
String typtype = rs.getString(3);
if (isArray) {
type = Types.ARRAY;
} else if ("c".equals(typtype)) {
type = Types.STRUCT;
} else if ("d".equals(typtype)) {
type = Types.DISTINCT;
} else if ("e".equals(typtype)) {
type = Types.VARCHAR;
}
types.put(typname, type);
}
if (type == null) {
type = Types.OTHER;
}
rs.close();
_pgNameToSQLType.put(pgTypeName, type);
}
return types;
}
private PreparedStatement getOidStatement(String pgTypeName) throws SQLException {
boolean isArray = pgTypeName.endsWith("[]");
boolean hasQuote = pgTypeName.contains("\"");
int dotIndex = pgTypeName.indexOf('.');
if (dotIndex == -1 && !hasQuote && !isArray) {
if (_getOidStatementSimple == null) {
String sql;
// see comments in @getSQLType()
// -- go with older way of unnesting array to be compatible with 8.0
sql = "SELECT pg_type.oid, typname "
+ " FROM pg_catalog.pg_type "
+ " LEFT "
+ " JOIN (select ns.oid as nspoid, ns.nspname, r.r "
+ " from pg_namespace as ns "
+ " join ( select s.r, (current_schemas(false))[s.r] as nspname "
+ " from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r "
+ " using ( nspname ) "
+ " ) as sp "
+ " ON sp.nspoid = typnamespace "
+ " WHERE typname = ? "
+ " ORDER BY sp.r, pg_type.oid DESC LIMIT 1;";
_getOidStatementSimple = _conn.prepareStatement(sql);
}
// coerce to lower case to handle upper case type names
String lcName = pgTypeName.toLowerCase();
// default arrays are represented with _ as prefix ... this dont even work for public schema
// fully
_getOidStatementSimple.setString(1, lcName);
return _getOidStatementSimple;
}
PreparedStatement oidStatementComplex;
if (isArray) {
if (_getOidStatementComplexArray == null) {
String sql;
if (_conn.haveMinimumServerVersion(ServerVersion.v8_3)) {
sql = "SELECT t.typarray, arr.typname "
+ " FROM pg_catalog.pg_type t"
+ " JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid"
+ " JOIN pg_catalog.pg_type arr ON arr.oid = t.typarray"
+ " WHERE t.typname = ? AND (n.nspname = ? OR ? IS NULL AND n.nspname = ANY (current_schemas(true)))"
+ " ORDER BY t.oid DESC LIMIT 1";
} else {
sql = "SELECT t.oid, t.typname "
+ " FROM pg_catalog.pg_type t"
+ " JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid"
+ " WHERE t.typelem = (SELECT oid FROM pg_catalog.pg_type WHERE typname = ?)"
+ " AND substring(t.typname, 1, 1) = '_' AND t.typlen = -1"
+ " AND (n.nspname = ? OR ? IS NULL AND n.nspname = ANY (current_schemas(true)))"
+ " ORDER BY t.typelem DESC LIMIT 1";
}
_getOidStatementComplexArray = _conn.prepareStatement(sql);
}
oidStatementComplex = _getOidStatementComplexArray;
} else {
if (_getOidStatementComplexNonArray == null) {
String sql = "SELECT t.oid, t.typname "
+ " FROM pg_catalog.pg_type t"
+ " JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid"
+ " WHERE t.typname = ? AND (n.nspname = ? OR ? IS NULL AND n.nspname = ANY (current_schemas(true)))"
+ " ORDER BY t.oid DESC LIMIT 1";
_getOidStatementComplexNonArray = _conn.prepareStatement(sql);
}
oidStatementComplex = _getOidStatementComplexNonArray;
}
String fullName = isArray ? pgTypeName.substring(0, pgTypeName.length() - 2) : pgTypeName;
String schema;
String name;
// simple use case
if (dotIndex == -1) {
schema = null;
name = fullName;
} else {
if (fullName.startsWith("\"")) {
if (fullName.endsWith("\"")) {
String[] parts = fullName.split("\"\\.\"");
schema = parts.length == 2 ? parts[0] + "\"" : null;
name = parts.length == 2 ? "\"" + parts[1] : parts[0];
} else {
int lastDotIndex = fullName.lastIndexOf('.');
name = fullName.substring(lastDotIndex + 1);
schema = fullName.substring(0, lastDotIndex);
}
} else {
schema = fullName.substring(0, dotIndex);
name = fullName.substring(dotIndex + 1);
}
}
if (schema != null && schema.startsWith("\"") && schema.endsWith("\"")) {
schema = schema.substring(1, schema.length() - 1);
} else if (schema != null) {
schema = schema.toLowerCase();
}
if (name.startsWith("\"") && name.endsWith("\"")) {
name = name.substring(1, name.length() - 1);
} else {
name = name.toLowerCase();
}
oidStatementComplex.setString(1, name);
oidStatementComplex.setString(2, schema);
oidStatementComplex.setString(3, schema);
return oidStatementComplex;
}
public synchronized int getPGType(String pgTypeName) throws SQLException {
Integer oid = _pgNameToOid.get(pgTypeName);
if (oid != null) {
return oid;
}
PreparedStatement oidStatement = getOidStatement(pgTypeName);
// Go through BaseStatement to avoid transaction start.
if (!((BaseStatement) oidStatement).executeWithFlags(QueryExecutor.QUERY_SUPPRESS_BEGIN)) {
throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
}
oid = Oid.UNSPECIFIED;
ResultSet rs = oidStatement.getResultSet();
if (rs.next()) {
oid = (int) rs.getLong(1);
String internalName = rs.getString(2);
_oidToPgName.put(oid, internalName);
_pgNameToOid.put(internalName, oid);
}
_pgNameToOid.put(pgTypeName, oid);
rs.close();
return oid;
}
public synchronized String getPGType(int oid) throws SQLException {
if (oid == Oid.UNSPECIFIED) {
return null;
}
String pgTypeName = _oidToPgName.get(oid);
if (pgTypeName != null) {
return pgTypeName;
}
if (_getNameStatement == null) {
String sql;
sql = "SELECT n.nspname = ANY(current_schemas(true)), n.nspname, t.typname "
+ "FROM pg_catalog.pg_type t "
+ "JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = ?";
_getNameStatement = _conn.prepareStatement(sql);
}
_getNameStatement.setInt(1, oid);
// Go through BaseStatement to avoid transaction start.
if (!((BaseStatement) _getNameStatement).executeWithFlags(QueryExecutor.QUERY_SUPPRESS_BEGIN)) {
throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
}
ResultSet rs = _getNameStatement.getResultSet();
if (rs.next()) {
boolean onPath = rs.getBoolean(1);
String schema = rs.getString(2);
String name = rs.getString(3);
if (onPath) {
pgTypeName = name;
_pgNameToOid.put(schema + "." + name, oid);
} else {
// TODO: escaping !?
pgTypeName = "\"" + schema + "\".\"" + name + "\"";
// if all is lowercase add special type info
// TODO: should probably check for all special chars
if (schema.equals(schema.toLowerCase()) && schema.indexOf('.') == -1
&& name.equals(name.toLowerCase()) && name.indexOf('.') == -1) {
_pgNameToOid.put(schema + "." + name, oid);
}
}
_pgNameToOid.put(pgTypeName, oid);
_oidToPgName.put(oid, pgTypeName);
}
rs.close();
return pgTypeName;
}
public int getPGArrayType(String elementTypeName) throws SQLException {
elementTypeName = getTypeForAlias(elementTypeName);
return getPGType(elementTypeName + "[]");
}
/**
* Return the oid of the array's base element if it's an array, if not return the provided oid.
* This doesn't do any database lookups, so it's only useful for the originally provided type
* mappings. This is fine for it's intended uses where we only have intimate knowledge of types
* that are already known to the driver.
*
* @param oid input oid
* @return oid of the array's base element or the provided oid (if not array)
*/
protected synchronized int convertArrayToBaseOid(int oid) {
Integer i = _pgArrayToPgType.get(oid);
if (i == null) {
return oid;
}
return i;
}
public synchronized char getArrayDelimiter(int oid) throws SQLException {
if (oid == Oid.UNSPECIFIED) {
return ',';
}
Character delim = _arrayOidToDelimiter.get(oid);
if (delim != null) {
return delim;
}
if (_getArrayDelimiterStatement == null) {
String sql;
sql = "SELECT e.typdelim FROM pg_catalog.pg_type t, pg_catalog.pg_type e "
+ "WHERE t.oid = ? and t.typelem = e.oid";
_getArrayDelimiterStatement = _conn.prepareStatement(sql);
}
_getArrayDelimiterStatement.setInt(1, oid);
// Go through BaseStatement to avoid transaction start.
if (!((BaseStatement) _getArrayDelimiterStatement)
.executeWithFlags(QueryExecutor.QUERY_SUPPRESS_BEGIN)) {
throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
}
ResultSet rs = _getArrayDelimiterStatement.getResultSet();
if (!rs.next()) {
throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
}
String s = rs.getString(1);
delim = s.charAt(0);
_arrayOidToDelimiter.put(oid, delim);
rs.close();
return delim;
}
public synchronized int getPGArrayElement(int oid) throws SQLException {
if (oid == Oid.UNSPECIFIED) {
return Oid.UNSPECIFIED;
}
Integer pgType = _pgArrayToPgType.get(oid);
if (pgType != null) {
return pgType;
}
if (_getArrayElementOidStatement == null) {
String sql;
sql = "SELECT e.oid, n.nspname = ANY(current_schemas(true)), n.nspname, e.typname "
+ "FROM pg_catalog.pg_type t JOIN pg_catalog.pg_type e ON t.typelem = e.oid "
+ "JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE t.oid = ?";
_getArrayElementOidStatement = _conn.prepareStatement(sql);
}
_getArrayElementOidStatement.setInt(1, oid);
// Go through BaseStatement to avoid transaction start.
if (!((BaseStatement) _getArrayElementOidStatement)
.executeWithFlags(QueryExecutor.QUERY_SUPPRESS_BEGIN)) {
throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
}
ResultSet rs = _getArrayElementOidStatement.getResultSet();
if (!rs.next()) {
throw new PSQLException(GT.tr("No results were returned by the query."), PSQLState.NO_DATA);
}
pgType = (int) rs.getLong(1);
boolean onPath = rs.getBoolean(2);
String schema = rs.getString(3);
String name = rs.getString(4);
_pgArrayToPgType.put(oid, pgType);
_pgNameToOid.put(schema + "." + name, pgType);
String fullName = "\"" + schema + "\".\"" + name + "\"";
_pgNameToOid.put(fullName, pgType);
if (onPath && name.equals(name.toLowerCase())) {
_oidToPgName.put(pgType, name);
_pgNameToOid.put(name, pgType);
} else {
_oidToPgName.put(pgType, fullName);
}
rs.close();
return pgType;
}
public synchronized Class<? extends PGobject> getPGobject(String type) {
return _pgNameToPgObject.get(type);
}
public synchronized String getJavaClass(int oid) throws SQLException {
String pgTypeName = getPGType(oid);
String result = _pgNameToJavaClass.get(pgTypeName);
if (result != null) {
return result;
}
if (getSQLType(pgTypeName) == Types.ARRAY) {
result = "java.sql.Array";
_pgNameToJavaClass.put(pgTypeName, result);
}
return result;
}
public String getTypeForAlias(String alias) {
String type = typeAliases.get(alias);
if (type != null) {
return type;
}
if (alias.indexOf('"') == -1) {
type = typeAliases.get(alias.toLowerCase());
if (type != null) {
return type;
}
}
return alias;
}
public int getPrecision(int oid, int typmod) {
oid = convertArrayToBaseOid(oid);
switch (oid) {
case Oid.INT2:
return 5;
case Oid.OID:
case Oid.INT4:
return 10;
case Oid.INT8:
return 19;
case Oid.FLOAT4:
// For float4 and float8, we can normally only get 6 and 15
// significant digits out, but extra_float_digits may raise
// that number by up to two digits.
return 8;
case Oid.FLOAT8:
return 17;
case Oid.NUMERIC:
if (typmod == -1) {
return 0;
}
return ((typmod - 4) & 0xFFFF0000) >> 16;
case Oid.CHAR:
case Oid.BOOL:
return 1;
case Oid.BPCHAR:
case Oid.VARCHAR:
if (typmod == -1) {
return _unknownLength;
}
return typmod - 4;
// datetime types get the
// "length in characters of the String representation"
case Oid.DATE:
case Oid.TIME:
case Oid.TIMETZ:
case Oid.INTERVAL:
case Oid.TIMESTAMP:
case Oid.TIMESTAMPTZ:
return getDisplaySize(oid, typmod);
case Oid.BIT:
return typmod;
case Oid.VARBIT:
if (typmod == -1) {
return _unknownLength;
}
return typmod;
case Oid.TEXT:
case Oid.BYTEA:
default:
return _unknownLength;
}
}
public int getScale(int oid, int typmod) {
oid = convertArrayToBaseOid(oid);
switch (oid) {
case Oid.FLOAT4:
return 8;
case Oid.FLOAT8:
return 17;
case Oid.NUMERIC:
if (typmod == -1) {
return 0;
}
return (typmod - 4) & 0xFFFF;
case Oid.TIME:
case Oid.TIMETZ:
case Oid.TIMESTAMP:
case Oid.TIMESTAMPTZ:
if (typmod == -1) {
return 6;
}
return typmod;
case Oid.INTERVAL:
if (typmod == -1) {
return 6;
}
return typmod & 0xFFFF;
default:
return 0;
}
}
public boolean isCaseSensitive(int oid) {
oid = convertArrayToBaseOid(oid);
switch (oid) {
case Oid.OID:
case Oid.INT2:
case Oid.INT4:
case Oid.INT8:
case Oid.FLOAT4:
case Oid.FLOAT8:
case Oid.NUMERIC:
case Oid.BOOL:
case Oid.BIT:
case Oid.VARBIT:
case Oid.DATE:
case Oid.TIME:
case Oid.TIMETZ:
case Oid.TIMESTAMP:
case Oid.TIMESTAMPTZ:
case Oid.INTERVAL:
return false;
default:
return true;
}
}
public boolean isSigned(int oid) {
oid = convertArrayToBaseOid(oid);
switch (oid) {
case Oid.INT2:
case Oid.INT4:
case Oid.INT8:
case Oid.FLOAT4:
case Oid.FLOAT8:
case Oid.NUMERIC:
return true;
default:
return false;
}
}
public int getDisplaySize(int oid, int typmod) {
oid = convertArrayToBaseOid(oid);
switch (oid) {
case Oid.INT2:
return 6; // -32768 to +32767
case Oid.INT4:
return 11; // -2147483648 to +2147483647
case Oid.OID:
return 10; // 0 to 4294967295
case Oid.INT8:
return 20; // -9223372036854775808 to +9223372036854775807
case Oid.FLOAT4:
// varies based upon the extra_float_digits GUC.
// These values are for the longest possible length.
return 15; // sign + 9 digits + decimal point + e + sign + 2 digits
case Oid.FLOAT8:
return 25; // sign + 18 digits + decimal point + e + sign + 3 digits
case Oid.CHAR:
return 1;
case Oid.BOOL:
return 1;
case Oid.DATE:
return 13; // "4713-01-01 BC" to "01/01/4713 BC" - "31/12/32767"
case Oid.TIME:
case Oid.TIMETZ:
case Oid.TIMESTAMP:
case Oid.TIMESTAMPTZ:
// Calculate the number of decimal digits + the decimal point.
int secondSize;
switch (typmod) {
case -1:
secondSize = 6 + 1;
break;
case 0:
secondSize = 0;
break;
case 1:
// Bizarrely SELECT '0:0:0.1'::time(1); returns 2 digits.
secondSize = 2 + 1;
break;
default:
secondSize = typmod + 1;
break;
}
// We assume the worst case scenario for all of these.
// time = '00:00:00' = 8
// date = '5874897-12-31' = 13 (although at large values second precision is lost)
// date = '294276-11-20' = 12 --enable-integer-datetimes
// zone = '+11:30' = 6;
switch (oid) {
case Oid.TIME:
return 8 + secondSize;
case Oid.TIMETZ:
return 8 + secondSize + 6;
case Oid.TIMESTAMP:
return 13 + 1 + 8 + secondSize;
case Oid.TIMESTAMPTZ:
return 13 + 1 + 8 + secondSize + 6;
}
case Oid.INTERVAL:
// SELECT LENGTH('-123456789 years 11 months 33 days 23 hours 10.123456 seconds'::interval);
return 49;
case Oid.VARCHAR:
case Oid.BPCHAR:
if (typmod == -1) {
return _unknownLength;
}
return typmod - 4;
case Oid.NUMERIC:
if (typmod == -1) {
return 131089; // SELECT LENGTH(pow(10::numeric,131071)); 131071 = 2^17-1
}
int precision = (typmod - 4 >> 16) & 0xffff;
int scale = (typmod - 4) & 0xffff;
// sign + digits + decimal point (only if we have nonzero scale)
return 1 + precision + (scale != 0 ? 1 : 0);
case Oid.BIT:
return typmod;
case Oid.VARBIT:
if (typmod == -1) {
return _unknownLength;
}
return typmod;
case Oid.TEXT:
case Oid.BYTEA:
return _unknownLength;
default:
return _unknownLength;
}
}
public int getMaximumPrecision(int oid) {
oid = convertArrayToBaseOid(oid);
switch (oid) {
case Oid.NUMERIC:
return 1000;
case Oid.TIME:
case Oid.TIMETZ:
// Technically this depends on the --enable-integer-datetimes
// configure setting. It is 6 with integer and 10 with float.
return 6;
case Oid.TIMESTAMP:
case Oid.TIMESTAMPTZ:
case Oid.INTERVAL:
return 6;
case Oid.BPCHAR:
case Oid.VARCHAR:
return 10485760;
case Oid.BIT:
case Oid.VARBIT:
return 83886080;
default:
return 0;
}
}
public boolean requiresQuoting(int oid) throws SQLException {
int sqlType = getSQLType(oid);
return requiresQuotingSqlType(sqlType);
}
/**
* Returns true if particular sqlType requires quoting.
* This method is used internally by the driver, so it might disappear without notice.
*
* @param sqlType sql type as in java.sql.Types
* @return true if the type requires quoting
* @throws SQLException if something goes wrong
*/
public boolean requiresQuotingSqlType(int sqlType) throws SQLException {
switch (sqlType) {
case Types.BIGINT:
case Types.DOUBLE:
case Types.FLOAT:
case Types.INTEGER:
case Types.REAL:
case Types.SMALLINT:
case Types.TINYINT:
case Types.NUMERIC:
case Types.DECIMAL:
return false;
}
return true;
}
}
至此,我们发现启动变得非常的快,并且项目查询性能没有受到任何影响。
附源码下载地址:点击打开链接