/*
* GeoTools - The Open Source Java GIS Toolkit
* http://geotools.org
*
* (C) 2002-2008, Open Source Geospatial Foundation (OSGeo)
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation;
* version 2.1 of the License.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*/
package org.geotools.jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.geotools.util.logging.Logging;
/**
* Looks up the primary key using the {@link DatabaseMetaData} for the specified table, looking both
* for primary keys and unique indexes. The sequence lookup is performed in conjuction with the sql
* dialect
*
* @author Andrea Aime - OpenGeo
*
*/
public class HeuristicPrimaryKeyFinder extends PrimaryKeyFinder {
protected static final Logger LOGGER = Logging.getLogger(HeuristicPrimaryKeyFinder.class);
@Override
public PrimaryKey getPrimaryKey(JDBCDataStore store, String databaseSchema, String tableName,
Connection cx) throws SQLException {
DatabaseMetaData metaData = cx.getMetaData();
LOGGER.log(Level.FINE, "Getting information about primary keys of {0}", tableName);
ResultSet primaryKey = metaData.getPrimaryKeys(null, databaseSchema, tableName);
try {
/*
* <LI><B>TABLE_CAT</B> String => table catalog (may be <code>null</code>)
* <LI><B>TABLE_SCHEM</B> String => table schema (may be <code>null</code>)
* <LI><B>TABLE_NAME</B> String => table name <LI><B>COLUMN_NAME</B> String => column
* name <LI><B>KEY_SEQ</B> short => sequence number within primary key
* <LI><B>PK_NAME</B> String => primary key name (may be <code>null</code>)
*/
PrimaryKey pkey = createPrimaryKey(store, primaryKey, metaData, databaseSchema,
tableName, cx);
if (pkey == null) {
// No known database supports unique indexes on views and this check
// causes problems with Oracle, so we skip it
if (!store.isView(metaData, databaseSchema, tableName)) {
// no primary key, check for a unique index
LOGGER.log(Level.FINE, "Getting information about unique indexes of {0}",
tableName);
ResultSet uniqueIndex = metaData.getIndexInfo(null, databaseSchema, tableName,
true, true);
try {
pkey = createPrimaryKey(store, uniqueIndex, metaData, databaseSchema,
tableName, cx);
} finally {
store.closeSafe(uniqueIndex);
}
}
}
return pkey;
} finally {
store.closeSafe(primaryKey);
}
}
/*
* Creates a key from a primary key or unique index.
*/
PrimaryKey createPrimaryKey(JDBCDataStore store, ResultSet index, DatabaseMetaData metaData,
String databaseSchema, String tableName, Connection cx) throws SQLException {
ArrayList<PrimaryKeyColumn> cols = new ArrayList();
while (index.next()) {
String columnName = index.getString("COLUMN_NAME");
// work around. For some reason the first record returned is always 'empty'
// this was tested on Oracle and Postgres databases
if (columnName == null) {
continue;
}
// look up the type ( should only be one row )
ResultSet columns = metaData.getColumns(null, databaseSchema, tableName, columnName);
columns.next();
int binding = columns.getInt("DATA_TYPE");
Class columnType = store.getMapping(binding);
if (columnType == null) {
LOGGER.warning("No class for sql type " + binding);
columnType = Object.class;
}
// determine which type of primary key we have
PrimaryKeyColumn col = null;
// 1. Auto Incrementing?
Statement st = cx.createStatement();
try {
// not actually going to get data
st.setFetchSize(1);
StringBuffer sql = new StringBuffer();
sql.append("SELECT ");
store.getSQLDialect().encodeColumnName(columnName, sql);
sql.append(" FROM ");
store.encodeTableName(tableName, sql);
sql.append(" WHERE 0=1");
LOGGER.log(Level.FINE, "Grabbing table pk metadata: {0}", sql);
ResultSet rs = st.executeQuery(sql.toString());
try {
if (rs.getMetaData().isAutoIncrement(1)) {
col = new AutoGeneratedPrimaryKeyColumn(columnName, columnType);
}
} finally {
store.closeSafe(rs);
}
} finally {
store.closeSafe(st);
}
// 2. Has a sequence?
if (col == null) {
try {
String sequenceName = store.getSQLDialect().getSequenceForColumn(
databaseSchema, tableName, columnName, cx);
if (sequenceName != null) {
col = new SequencedPrimaryKeyColumn(columnName, columnType, sequenceName);
}
} catch (Exception e) {
// log the exception , and continue on
LOGGER.log(Level.WARNING, "Error occured determining sequence for "
+ columnName + ", " + tableName, e);
}
}
if (col == null) {
col = new NonIncrementingPrimaryKeyColumn(columnName, columnType);
}
cols.add(col);
}
if (!cols.isEmpty()) {
return new PrimaryKey(tableName, cols);
}
return null;
}
}