/**
* OrbisGIS is a java GIS application dedicated to research in GIScience.
* OrbisGIS is developed by the GIS group of the DECIDE team of the
* Lab-STICC CNRS laboratory, see <http://www.lab-sticc.fr/>.
*
* The GIS group of the DECIDE team is located at :
*
* Laboratoire Lab-STICC – CNRS UMR 6285
* Equipe DECIDE
* UNIVERSITÉ DE BRETAGNE-SUD
* Institut Universitaire de Technologie de Vannes
* 8, Rue Montaigne - BP 561 56017 Vannes Cedex
*
* OrbisGIS is distributed under GPL 3 license.
*
* Copyright (C) 2007-2014 CNRS (IRSTV FR CNRS 2488)
* Copyright (C) 2015-2017 CNRS (Lab-STICC UMR CNRS 6285)
*
* This file is part of OrbisGIS.
*
* OrbisGIS is free software: you can redistribute it and/or modify it under the
* terms of the GNU General Public License as published by the Free Software
* Foundation, either version 3 of the License, or (at your option) any later
* version.
*
* OrbisGIS 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 General Public License for more details.
*
* You should have received a copy of the GNU General Public License along with
* OrbisGIS. If not, see <http://www.gnu.org/licenses/>.
*
* For more information, please consult: <http://www.orbisgis.org/>
* or contact directly:
* info_at_ orbisgis.org
*/
package org.orbisgis.corejdbc;
import com.vividsolutions.jts.geom.Envelope;
import com.vividsolutions.jts.geom.Geometry;
import org.h2gis.utilities.JDBCUtilities;
import org.h2gis.utilities.SFSUtilities;
import org.h2gis.utilities.SpatialResultSet;
import org.h2gis.utilities.TableLocation;
import org.orbisgis.commons.progress.ProgressMonitor;
import org.orbisgis.corejdbc.common.LongUnion;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xnap.commons.i18n.I18n;
import org.xnap.commons.i18n.I18nFactory;
import java.beans.EventHandler;
import java.beans.PropertyChangeListener;
import java.io.Closeable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import java.util.*;
import java.util.concurrent.locks.Lock;
import org.apache.commons.math3.stat.descriptive.SummaryStatistics;
/**
* JDBC operations that does not affect database.
* @author Nicolas Fortin
*/
public class ReadTable {
/** SQL function to evaluate */
public enum STATS { COUNT, SUM, AVG, STDDEV_SAMP, MIN, MAX}
protected final static I18n I18N = I18nFactory.getI18n(ReadTable.class, Locale.getDefault(), I18nFactory.FALLBACK);
private static Logger LOGGER = LoggerFactory.getLogger(ReadTable.class);
private static final int INSERT_BATCH_SIZE = 30;
public static Collection<Integer> getSortedColumnRowIndex(Connection connection,ReadRowSet originalOrder, String table, String originalColumnName, boolean ascending, ProgressMonitor progressMonitor) throws SQLException {
String quoteIdentifier = TableLocation.quoteIdentifier(originalColumnName);
TableLocation tableLocation = TableLocation.parse(table);
Collection<Integer> columnValues;
try(Statement st = connection.createStatement()) {
int rowCount = 0;
try(ResultSet rs = st.executeQuery("SELECT COUNT(*) cpt from "+tableLocation.toString())) {
if(rs.next()) {
rowCount = rs.getInt(1);
}
}
columnValues = new ArrayList<>(rowCount);
PropertyChangeListener listener = EventHandler.create(PropertyChangeListener.class, st, "cancel");
progressMonitor.addPropertyChangeListener(ProgressMonitor.PROP_CANCEL,listener);
try {
int pkIndex = JDBCUtilities.getIntegerPrimaryKey(connection, tableLocation.toString());
if (pkIndex > 0) {
ProgressMonitor jobProgress = progressMonitor.startTask(2);
// Do not cache values
// Use SQL sort
DatabaseMetaData meta = connection.getMetaData();
String pkFieldName = TableLocation.quoteIdentifier(JDBCUtilities.getFieldName(meta, table, pkIndex));
String desc = "";
if(!ascending) {
desc = " DESC";
}
// Create a map of Row Id to Pk Value
ProgressMonitor cacheProgress = jobProgress.startTask(I18N.tr("Cache primary key values"), rowCount);
Map<Long, Integer> pkValueToRowId = new HashMap<>(rowCount);
int rowId=0;
Lock lock = originalOrder.getReadLock();
lock.tryLock();
try{
originalOrder.beforeFirst();
while (originalOrder.next()) {
rowId++;
pkValueToRowId.put(originalOrder.getPk(), rowId);
cacheProgress.endTask();
}
} finally {
lock.unlock();
}
// Read ordered pk values
ProgressMonitor sortProgress = jobProgress.startTask(I18N.tr("Read sorted keys"), rowCount);
try(ResultSet rs = st.executeQuery("select "+pkFieldName+" from "+table+" ORDER BY "+quoteIdentifier+desc)) {
while(rs.next()) {
columnValues.add(pkValueToRowId.get(rs.getLong(1)));
sortProgress.endTask();
}
}
} else {
ProgressMonitor jobProgress = progressMonitor.startTask(2);
//Cache values
ProgressMonitor cacheProgress = jobProgress.startTask(I18N.tr("Cache table values"), rowCount);
Comparable[] cache = new Comparable[rowCount];
Lock lock = originalOrder.getReadLock();
lock.tryLock();
try{
originalOrder.beforeFirst();
int i = 0;
final int fieldIndex = originalOrder.findColumn(originalColumnName);
long time1=0,time2=0,time3=0,time4=0,time5=0;
time5-=System.currentTimeMillis();
while(originalOrder.next()) {
time5+=System.currentTimeMillis();
time1-=System.currentTimeMillis();
Object obj = originalOrder.getObject(fieldIndex);
time1+=System.currentTimeMillis();
time2-=System.currentTimeMillis();
if(obj != null && !(obj instanceof Comparable)) {
throw new SQLException(I18N.tr("Could only sort comparable database object type"));
}
time2+=System.currentTimeMillis();
time3-=System.currentTimeMillis();
cache[i++] = (Comparable)obj;
time3+=System.currentTimeMillis();
time4-=System.currentTimeMillis();
cacheProgress.endTask();
time4+=System.currentTimeMillis();
time5-=System.currentTimeMillis();
}
time5+=System.currentTimeMillis();
System.out.println("time 1:"+time1+";"+"time 2:"+time2+";"+"time 3:"+time3+";"+"time 4:"+time4+";"+"time 5:"+time5+";");
} finally {
lock.unlock();
}
ProgressMonitor sortProgress = jobProgress.startTask(I18N.tr("Sort table values"), rowCount);
Comparator<Integer> comparator = new SortValueCachedComparator(cache);
if (!ascending) {
comparator = Collections.reverseOrder(comparator);
}
columnValues = new TreeSet<>(comparator);
for (int i = 1; i <= rowCount; i++) {
columnValues.add(i);
sortProgress.endTask();
}
}
} finally {
progressMonitor.removePropertyChangeListener(listener);
}
return columnValues;
}
}
public static SortedSet<Long> getRowPkFromRowNumber(ReadRowSet rowSet, SortedSet<Integer> rowNumber) throws SQLException {
SortedSet<Long> modelRows = new LongUnion();
for (int rowNum : rowNumber) {
rowSet.absolute(rowNum);
modelRows.add(rowSet.getPk());
}
return modelRows;
}
public static long getRowCount(Connection connection, String tableReference) throws SQLException {
TableLocation tableLocation = TableLocation.parse(tableReference);
if(JDBCUtilities.isH2DataBase(connection.getMetaData())) {
try(PreparedStatement st = SFSUtilities.prepareInformationSchemaStatement(connection,tableLocation.getCatalog(),
tableLocation.getSchema(), tableLocation.getTable(), "INFORMATION_SCHEMA.TABLES", "",
"TABLE_CATALOG","TABLE_SCHEMA","TABLE_NAME");
ResultSet rs = st.executeQuery()) {
if(rs.next()) {
long estimatedRowCount = rs.getLong("ROW_COUNT_ESTIMATE");
// 100 because H2 views est
if(estimatedRowCount > 0 && !"VIEW".equalsIgnoreCase(rs.getString("TABLE_TYPE"))) {
return estimatedRowCount;
}
}
} catch (Exception ex) {
// This method failed, will use standard one
LOGGER.debug(ex.getLocalizedMessage(), ex);
}
}
// Use precise row count
try(Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("SELECT COUNT(*) cpt FROM "+tableReference)) {
rs.next();
return rs.getLong(1);
}
}
/**
* Return a concatened and human readable format of provided result set
* @param rs result set to read
* @param maxFieldLength Maximum field length to print
* @param maxPrintedRows Maximum printed rows
* @param addColumns Add column header
* @param alignColumns Align columns by using padding
* @return human readable format of provided result set
* @throws SQLException
*/
public static String resultSetToString(ResultSet rs,int maxFieldLength, int maxPrintedRows, boolean addColumns,
boolean alignColumns) throws SQLException {
return resultSetToString(rs, maxFieldLength, maxPrintedRows, addColumns, alignColumns, new AcceptAllFilter());
}
/**
* Return a concatened and human readable format of provided result set
* @param rs result set to read
* @param maxFieldLength Maximum field length to print
* @param maxPrintedRows Maximum printed rows
* @param addColumns Add column header
* @param alignColumns Align columns by using padding
* @param resultSetFilter Accept or refuse rows by implementing this interface
* @return human readable format of provided result set
* @throws SQLException
*/
public static String resultSetToString(ResultSet rs,int maxFieldLength, int maxPrintedRows, boolean addColumns, boolean alignColumns, ResultSetFilter resultSetFilter) throws SQLException {
// Print headers
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
StringBuilder lines = new StringBuilder();
StringBuilder formatStringBuilder = new StringBuilder();
String[] header = new String[columnCount];
for(int idColumn = 1; idColumn <= columnCount; idColumn++) {
header[idColumn-1] = metaData.getColumnLabel(idColumn)+"("+metaData.getColumnTypeName(idColumn)+")";
if(alignColumns) {
formatStringBuilder.append("%-");
formatStringBuilder.append(maxFieldLength);
formatStringBuilder.append("s ");
} else {
formatStringBuilder.append("%s ");
}
}
if(addColumns) {
lines.append(String.format(formatStringBuilder.toString(), header));
lines.append("\n");
}
int shownLines = 0;
NumberFormat decimalFormat = NumberFormat.getInstance(Locale.getDefault());
decimalFormat.setGroupingUsed(false);
decimalFormat.setMaximumFractionDigits(16);
while(rs.next() && shownLines < maxPrintedRows) {
if(resultSetFilter.printRow(rs)) {
String[] row = new String[columnCount];
for (int idColumn = 1; idColumn <= columnCount; idColumn++) {
Object valObj = rs.getObject(idColumn);
String value;
if (valObj instanceof Number) {
value = decimalFormat.format(valObj);
} else {
value = rs.getString(idColumn);
}
if (value != null) {
if (columnCount > 1 && value.length() > maxFieldLength) {
value = value.substring(0, maxFieldLength - 2) + "..";
}
} else {
value = "NULL";
}
row[idColumn - 1] = value;
}
shownLines++;
lines.append(String.format(formatStringBuilder.toString(), row));
lines.append("\n");
}
}
if(lines.length() != 0) {
return lines.toString();
} else {
return I18N.tr("No attributes to show");
}
}
private static class AcceptAllFilter implements ResultSetFilter {
@Override
public boolean printRow(ResultSet rs) {
return true;
}
}
public static interface ResultSetFilter {
/**
* @param rs Result set in valid row
* @return True if the row should be printed
*/
boolean printRow(ResultSet rs) throws SQLException;
}
/**
* Compute numeric stats of the specified table column.
* @param connection Available connection
* @param tableName Table name
* @param columnName Column name
* @param pm Progress monitor
* @return An array of attributes {@link STATS}
* @throws SQLException
*/
public static String[] computeStatsSQL(Connection connection, String tableName, String columnName, ProgressMonitor pm) throws SQLException {
String[] stats = new String[STATS.values().length];
StringBuilder sb = new StringBuilder();
for(STATS func : STATS.values()) {
if(sb.length()!=0) {
sb.append(", ");
}
sb.append(func.name());
sb.append("(");
sb.append(columnName);
sb.append("::double precision) ");
sb.append(func.name());
}
try(Statement st = connection.createStatement()) {
// Cancel select
PropertyChangeListener listener = EventHandler.create(PropertyChangeListener.class, st, "cancel");
pm.addPropertyChangeListener(ProgressMonitor.PROP_CANCEL,
listener);
try(ResultSet rs = st.executeQuery(String.format("SELECT %s FROM %s",sb.toString(), tableName ))) {
if(rs.next()) {
for(STATS func : STATS.values()) {
stats[func.ordinal()] = rs.getString(func.name());
}
}
} finally {
pm.removePropertyChangeListener(listener);
}
}
return stats;
}
/**
* Compute numeric stats of the specified table column using a limited input rows. Stats are not done in the sql side.
* @param connection Available connection
* @param tableName Table name
* @param columnName Column name
* @param rowNum Row id
* @param pm Progress monitor
* @return An array of attributes {@link STATS}
* @throws SQLException
*/
public static String[] computeStatsLocal(Connection connection, String tableName, String columnName, SortedSet<Integer> rowNum, ProgressMonitor pm) throws SQLException {
String[] res = new String[STATS.values().length];
SummaryStatistics stats = new SummaryStatistics();
try(Statement st = connection.createStatement()) {
// Cancel select
PropertyChangeListener listener = EventHandler.create(PropertyChangeListener.class, st, "cancel");
pm.addPropertyChangeListener(ProgressMonitor.PROP_CANCEL,
listener);
try (ResultSet rs = st.executeQuery(String.format("SELECT %s FROM %s",columnName, tableName ))) {
ProgressMonitor fetchProgress = pm.startTask(rowNum.size());
while(rs.next() && !pm.isCancelled()) {
if(rowNum.contains(rs.getRow())) {
stats.addValue(rs.getDouble(columnName));
fetchProgress.endTask();
}
}
} finally {
pm.removePropertyChangeListener(listener);
}
}
res[STATS.SUM.ordinal()] = Double.toString(stats.getSum());
res[STATS.AVG.ordinal()] = Double.toString(stats.getMean());
res[STATS.COUNT.ordinal()] = Long.toString(stats.getN());
res[STATS.MIN.ordinal()] = Double.toString(stats.getMin());
res[STATS.MAX.ordinal()] = Double.toString(stats.getMax());
res[STATS.STDDEV_SAMP.ordinal()] = Double.toString(stats.getStandardDeviation());
return res;
}
/**
* Retrieve the envelope of selection of lines
* @param manager Data Manager
* @param tableName Table identifier [[catalog.]schema.]table
* @param rowsId Line number [1-n]
* @param pm Progress monitor
* @return Envelope of rows
* @throws SQLException
*/
public static Envelope getTableSelectionEnvelope(DataManager manager, String tableName, SortedSet<Long> rowsId, ProgressMonitor pm) throws SQLException {
try( Connection connection = manager.getDataSource().getConnection()) {
Envelope selectionEnvelope = null;
List<String> geomFields = SFSUtilities.getGeometryFields(connection, TableLocation.parse(tableName));
if(geomFields.isEmpty()) {
throw new SQLException(I18N.tr("Table table {0} does not contain any geometry fields", tableName));
}
String fieldQuery = "ST_EXTENT(" + TableLocation.quoteIdentifier(geomFields.get(0)) + ")";
try(FilteredResultSet fRs = new FilteredResultSet(connection, tableName, rowsId, pm, false, fieldQuery)) {
SpatialResultSet rs = fRs.getResultSet();
if (rs.next() && rs.getGeometry() != null) {
selectionEnvelope = rs.getGeometry().getEnvelopeInternal();
}
return selectionEnvelope;
} catch (IOException ex) {
throw new SQLException(ex.getLocalizedMessage(), ex);
}
}
}
/**
*
* @param dataManager RowSet factory
* @param table Table identifier
* @param geometryColumn Name of the geometry column
* @param selection Selection polygon
* @param contains If true selection is used with contains, else this is intersects.
* @return List of row id.
* @throws SQLException
*/
public static Set<Long> getTablePkByEnvelope(DataManager dataManager, String table,String geometryColumn,
Geometry selection, boolean contains) throws SQLException {
Set<Long> newSelection = new HashSet<>(50);
TableLocation tableLocation = TableLocation.parse(table);
// There is a where condition then system row index can't be used
try(Connection connection = dataManager.getDataSource().getConnection()) {
String pkName = MetaData.getPkName(connection, tableLocation.toString(), true);
boolean isH2 = JDBCUtilities.isH2DataBase(connection.getMetaData());
if(!pkName.isEmpty()) {
String from = tableLocation.toString();
String sqlFunction = contains ? "ST_CONTAINS(?, %s)" : "ST_INTERSECTS(?, %s)";
try(PreparedStatement st = connection.prepareStatement(String.format("SELECT %s FROM %s WHERE %s && ? AND " + sqlFunction,
TableLocation.quoteIdentifier(pkName), from,
TableLocation.quoteIdentifier(geometryColumn), TableLocation.quoteIdentifier(geometryColumn)))) {
st.setObject(1, selection);
st.setObject(2, selection);
try(SpatialResultSet rs = st.executeQuery().unwrap(SpatialResultSet.class)) {
while (rs.next()) {
newSelection.add(rs.getLong(1));
}
}
}
} else {
throw new SQLException(I18N.tr("Table "+table+" do not contain any information in order to identify row"));
}
}
return newSelection;
}
public static class FilteredResultSet implements Closeable {
private SpatialResultSet resultSet;
private String selectionTable;
private Statement st;
private Connection connection;
/**
* @param connection Active connection
* @param tableName Table to query
* @param rowsId Primary keys to filter
* @param pm Progress information and cancel
* @param geometryOnly Retrieve only the first geometry field
* @param customFields Query the table with custom field selection
* @throws SQLException If an error occurred
*/
public FilteredResultSet(Connection connection, String tableName, SortedSet<Long> rowsId, ProgressMonitor pm,boolean geometryOnly ,String customFields) throws SQLException {
this.connection = connection;
st = connection.createStatement();
PropertyChangeListener cancelListener = EventHandler.create(PropertyChangeListener.class, st, "cancel");
pm.addPropertyChangeListener(ProgressMonitor.PROP_CANCEL,cancelListener);
try {
String fields = "t1.*";
if(geometryOnly) {
List<String> geomFields = SFSUtilities.getGeometryFields(connection, TableLocation.parse(tableName));
if (geomFields.isEmpty()) {
throw new SQLException(I18N.tr("Table table {0} does not contain any geometry fields", tableName));
}
fields = TableLocation.quoteIdentifier(geomFields.get(0));
} else if(customFields != null && !customFields.isEmpty()) {
fields = customFields;
}
// Create a temporary table that contain selected pk
selectionTable = CreateTable.createIndexTempTable(connection, pm,rowsId,"pk", INSERT_BATCH_SIZE);
String pkName = MetaData.getPkName(connection, tableName, true);
StringBuilder pkEquality = new StringBuilder("t1." + pkName + " = ");
if (!pkName.equals(MetaData.POSTGRE_ROW_IDENTIFIER)) {
pkEquality.append("t2.pk");
} else {
pkEquality.append(MetaData.castLongToTid("t2.pk"));
}
// Join with temp table and compute the envelope on the server side
resultSet = st.executeQuery("SELECT " + fields + " FROM " + tableName + " t1, " + selectionTable + " t2 where " + pkEquality).unwrap(SpatialResultSet.class);
} finally {
pm.removePropertyChangeListener(cancelListener);
}
}
public SpatialResultSet getResultSet() {
return resultSet;
}
@Override
public void close() throws IOException {
try {
resultSet.close();
st.execute("DROP TABLE IF EXISTS "+selectionTable);
st.close();
} catch (SQLException ex) {
throw new IOException(ex.getLocalizedMessage(), ex);
}
}
}
}