001: /*
002: * GeoTools - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) 2005-2006, GeoTools Project Managment Committee (PMC)
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation;
009: * version 2.1 of the License.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: */
016: package org.geotools.data.postgis.fidmapper;
017:
018: import java.io.IOException;
019: import java.sql.Connection;
020: import java.sql.ResultSet;
021: import java.sql.Statement;
022:
023: import org.geotools.data.jdbc.fidmapper.AutoIncrementFIDMapper;
024: import org.geotools.data.jdbc.fidmapper.FIDMapper;
025: import org.geotools.feature.Feature;
026: import org.geotools.feature.IllegalAttributeException;
027:
028: /**
029: * Generate FID based on an auto increment function, the most stable approach for
030: * use with editing.
031: *
032: * @author Jesse Eichar, Refractions Research, Inc.
033: * @author Cory Horner, Refractions Research, Inc.
034: */
035: public class PostGISAutoIncrementFIDMapper extends
036: AutoIncrementFIDMapper implements FIDMapper {
037:
038: private static final long serialVersionUID = -6082930630426171079L;
039:
040: /** Indicates that the pg_get_serial_sequence function exists, and works for this table */
041: boolean can_usepg_get_serial_sequence = true;
042:
043: /** Flag to indicate when we can't find the table's sequence */
044: boolean hasSerialSequence = true;
045:
046: /** The actual name of the sequence, if we have found it */
047: String sequenceName = null;
048:
049: public PostGISAutoIncrementFIDMapper(String tableSchemaName,
050: String tableName, String colName, int dataType) {
051: super (tableSchemaName, tableName, colName, dataType);
052: }
053:
054: public PostGISAutoIncrementFIDMapper(String tableName,
055: String colName, int dataType,
056: boolean returnFIDColumnsAsAttributes) {
057: super (tableName, colName, dataType);
058: this .returnFIDColumnsAsAttributes = returnFIDColumnsAsAttributes;
059: }
060:
061: public String createID(Connection conn, Feature feature,
062: Statement statement) throws IOException {
063: String id = retriveId(conn, feature, statement);
064: if (id != null && returnFIDColumnsAsAttributes) {
065: // we have to udpate the attribute in the feature too
066: try {
067: feature.setAttribute(colNames[0], id);
068: } catch (IllegalAttributeException e) {
069: throw new IOException("Could not set generated key "
070: + id + " into attribute " + colNames[0]);
071: }
072: }
073: return id;
074: }
075:
076: /**
077: * Attempts to determine the FID after it was inserted, using three techniques:
078: * 1. SELECT currval(pg_get_serial_sequence(...))
079: * 2. SELECT currval(sequence name) <-- using other methods to get name
080: * 3. SELECT fid ... ORDER BY fid DESC LIMIT 1
081: */
082: public String retriveId(Connection conn, Feature feature,
083: Statement statement) throws IOException {
084: ResultSet rs = null;
085: if (can_usepg_get_serial_sequence) {
086: try {
087: //use pg_get_serial_sequence('"table name"','"column name"')
088: String sql = "SELECT currval(pg_get_serial_sequence('\"";
089: String schema = getTableSchemaName();
090: if (schema != null && !schema.equals("")) {
091: sql = sql + schema + "\".\"";
092: }
093: sql = sql + getTableName() + "\"','" + getColumnName()
094: + "'))";
095: rs = statement.executeQuery(sql);
096: if (rs.next() && rs.getString("currval") != null)
097: return rs.getString("currval");
098: else {
099: can_usepg_get_serial_sequence = false;
100: }
101: } catch (Exception e) {
102: can_usepg_get_serial_sequence = false;
103: } finally {
104: try {
105: if (rs != null)
106: rs.close();
107: } catch (Exception e) {
108: //oh well
109: }
110: }
111: }
112: //TODO: add logging
113: if (hasSerialSequence) {
114: if (sequenceName == null) {
115: // try to find the sequence (this makes the assumption that
116: // the sequence name contains "tableName_columnName")
117: String sql = "SELECT relname FROM pg_catalog.pg_class WHERE relkind = 'S' AND relname LIKE '"
118: + getTableName()
119: + "_"
120: + getColumnName()
121: + "_seq'";
122: try {
123: rs = statement.executeQuery(sql);
124: if (rs.next() && rs.getString(1) != null) {
125: sequenceName = rs.getString(1);
126: } else {
127: hasSerialSequence = false;
128: }
129: } catch (Exception e) {
130: hasSerialSequence = false;
131: } finally {
132: try {
133: if (rs != null)
134: rs.close();
135: } catch (Exception e) {
136: //oh well
137: }
138: }
139: }
140:
141: if (sequenceName != null) {
142: //get the sequence value
143: String sql = "SELECT currval('\"" + sequenceName
144: + "\"')";
145: try {
146: rs = statement.executeQuery(sql);
147: if (rs.next() && rs.getString("currval") != null)
148: return rs.getString("currval");
149: else {
150: hasSerialSequence = false;
151: }
152: } catch (Exception e) {
153: hasSerialSequence = false;
154: } finally {
155: try {
156: if (rs != null)
157: rs.close();
158: } catch (Exception e) {
159: //oh well
160: }
161: }
162: }
163: }
164: return findInsertedFID(conn, feature, statement);
165: }
166:
167: /**
168: * Our last resort method for getting the FID.
169: */
170: private String findInsertedFID(Connection conn, Feature feature,
171: Statement statement) throws IOException {
172: String sql = "SELECT \"" + getColumnName() + "\" FROM \"";
173: String schema = getTableSchemaName();
174: if (schema != null && !schema.equals("")) {
175: sql = sql + schema + "\".\"";
176: }
177: sql = sql + getTableName() + "\" ORDER BY \"" + getColumnName()
178: + "\" DESC LIMIT 1;";
179: ResultSet rs = null;
180: try {
181: statement.execute(sql);
182: rs = statement.getResultSet();
183: rs.next();
184: return rs.getString(getColumnName());
185: } catch (Exception e) { //i surrender
186: return super .createID(conn, feature, statement);
187: } finally {
188: try {
189: if (rs != null)
190: rs.close();
191: } catch (Exception e) {
192: //oh well
193: }
194: }
195: }
196: }
|