/**
* This program 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, either version 3 of the License, or
* (at your option) any later version.
*
* This program 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 Lesser General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*
* @author Arne Kepp / The Open Planning Project 2009
*
*/
package org.geowebcache.storage.metastore.jdbc;
import static org.geowebcache.storage.metastore.jdbc.JDBCUtils.close;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Arrays;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.geowebcache.storage.BlobStore;
import org.geowebcache.storage.DefaultStorageFinder;
import org.geowebcache.storage.DiscontinuousTileRange;
import org.geowebcache.storage.StorageException;
import org.geowebcache.storage.StorageObject;
import org.geowebcache.storage.TileObject;
import org.geowebcache.storage.TileRange;
import org.h2.jdbcx.JdbcConnectionPool;
/**
* Wrapper class for the JDBC object, used by JDBCMetaBackend
*
* Performs mundane tasks such as
* <ul>
* <li>initialize the database</li>
* <li>create tables</li>
* <li>create iterators</li>
* </ul>
*
*/
class JDBCMBWrapper {
private static Log log = LogFactory
.getLog(org.geowebcache.storage.metastore.jdbc.JDBCMBWrapper.class);
/** Database version, for automatic updates */
static int DB_VERSION = 120;
/** Connection information */
final String jdbcString;
final String username;
final String password;
final String driverClass;
/**
* H2 would close all the file handles to the database once you closed the last connection.
* Reopening from scratch can take almost a second, so keeping one connection around in the
* background ensures that this doesn't happen.
* <p>
* It _really_ makes a difference if connection pooling is disabled!
* </p>
*/
private Connection persistentConnection;
boolean closing = false;
/** Timeout for locked objects, 60 seconds by default **/
protected long lockTimeout = 60000;
private boolean useConnectionPooling;
private int maxConnections;
private JdbcConnectionPool connPool;
protected JDBCMBWrapper(String driverClass, String jdbcString, String username,
String password, boolean useConnectionPooling, int maxConnections)
throws StorageException, SQLException {
this.jdbcString = jdbcString;
this.username = username;
this.password = password;
this.driverClass = driverClass;
this.useConnectionPooling = useConnectionPooling;
this.maxConnections = maxConnections;
try {
Class.forName(driverClass);
} catch (ClassNotFoundException cnfe) {
throw new StorageException("Class not found: " + cnfe.getMessage());
}
if (!useConnectionPooling) {
persistentConnection = getConnection();
}
checkTables();
}
public JDBCMBWrapper(DefaultStorageFinder defStoreFind, boolean useConnectionPooling,
int maxConnections) throws StorageException, SQLException {
String envStrUsername;
String envStrPassword;
String envStrJdbcUrl;
String envStrDriver;
envStrUsername = defStoreFind.findEnvVar(DefaultStorageFinder.GWC_METASTORE_USERNAME);
envStrPassword = defStoreFind.findEnvVar(DefaultStorageFinder.GWC_METASTORE_PASSWORD);
envStrJdbcUrl = defStoreFind.findEnvVar(DefaultStorageFinder.GWC_METASTORE_JDBC_URL);
envStrDriver = defStoreFind.findEnvVar(DefaultStorageFinder.GWC_METASTORE_DRIVER_CLASS);
this.useConnectionPooling = useConnectionPooling;
this.maxConnections = maxConnections;
if (envStrUsername != null) {
username = envStrUsername;
} else {
this.username = "sa";
}
if (envStrPassword != null) {
this.password = envStrPassword;
} else {
this.password = "";
}
if (envStrDriver != null) {
this.driverClass = envStrDriver;
} else {
this.driverClass = "org.h2.Driver";
}
if (envStrJdbcUrl != null) {
this.jdbcString = envStrJdbcUrl;
} else {
String path = defStoreFind.getDefaultPath() + File.separator + "meta_jdbc_h2";
File dir = new File(path);
if (!dir.exists() && !dir.mkdirs()) {
throw new StorageException("Unable to create " + dir.getAbsolutePath()
+ " for H2 database.");
}
this.jdbcString = "jdbc:h2:file:" + path + File.separator + "gwc_metastore"
+ ";TRACE_LEVEL_FILE=0;AUTO_SERVER=TRUE";
}
try {
Class.forName(driverClass);
} catch (ClassNotFoundException cnfe) {
throw new StorageException("Class not found: " + cnfe.getMessage());
}
if (!useConnectionPooling) {
persistentConnection = getConnection();
}
checkTables();
}
protected Connection getConnection() throws SQLException {
if (closing) {
throw new IllegalStateException(getClass().getSimpleName() + " is being shut down");
}
Connection conn;
if (useConnectionPooling) {
if (connPool == null) {
connPool = JdbcConnectionPool.create(jdbcString, username, password == null ? ""
: password);
connPool.setMaxConnections(maxConnections);
}
conn = connPool.getConnection();
} else {
conn = DriverManager.getConnection(jdbcString, username, password);
}
conn.setAutoCommit(true);
return conn;
}
private void checkTables() throws StorageException, SQLException {
final Connection conn = getConnection();
try {
/** Easy ones */
condCreate(conn, "LAYERS",
"ID BIGINT AUTO_INCREMENT PRIMARY KEY, VALUE VARCHAR(254) UNIQUE", "VALUE",
null);
condCreate(conn, "PARAMETERS",
"ID BIGINT AUTO_INCREMENT PRIMARY KEY, VALUE VARCHAR(254) UNIQUE", "VALUE",
null);
condCreate(conn, "FORMATS",
"ID BIGINT AUTO_INCREMENT PRIMARY KEY, VALUE VARCHAR(126) UNIQUE", "VALUE",
null);
condCreate(conn, "GRIDSETS",
"ID BIGINT AUTO_INCREMENT PRIMARY KEY, VALUE VARCHAR(126) UNIQUE", "VALUE",
null);
int fromVersion = getDbVersion(conn);
log.info("MetaStore database is version " + fromVersion);
if (fromVersion != DB_VERSION) {
if (fromVersion < DB_VERSION) {
runDbUpgrade(conn, fromVersion);
} else {
log.error("Metastore database is newer than the runnin version of GWC. Proceeding with undefined results.");
}
}
/** Slightly more complex */
checkWFSTable(conn);
checkTilesTable(conn);
} finally {
close(conn);
}
}
/**
* Checks / creates the "variables" table and verifies that the db_version variable is.
*
* @param conn
* @throws SQLException
* @throws StorageException
* if the database is newer than the software
*/
protected int getDbVersion(Connection conn) throws SQLException, StorageException {
condCreate(conn, "VARIABLES", "KEY VARCHAR(32), VALUE VARCHAR(128)", "KEY", null);
Statement st = null;
ResultSet rs = null;
try {
st = conn.createStatement();
rs = st.executeQuery("SELECT VALUE FROM VARIABLES WHERE KEY LIKE 'db_version'");
if (rs.first()) {
// Check what version of the database this is
String db_versionStr = rs.getString("value");
int cur_db_version = Integer.parseInt(db_versionStr);
return cur_db_version;
} else {
// This is a new database, insert current value
st.execute("INSERT INTO VARIABLES (KEY,VALUE) " + " VALUES ('db_version',"
+ JDBCMBWrapper.DB_VERSION + ")");
return JDBCMBWrapper.DB_VERSION;
}
} finally {
close(rs);
close(st);
}
}
private void checkWFSTable(Connection conn) throws SQLException {
condCreate(conn, "WFS", "WFS_ID BIGINT AUTO_INCREMENT PRIMARY KEY, PARAMETERS_ID BIGINT, "
+ "QUERY_BLOB_MD5 VARCHAR(32), QUERY_BLOB_SIZE INT, " + "BLOB_SIZE INT, "
+ "CREATED BIGINT, ACCESS_LAST BIGINT, ACCESS_COUNT BIGINT, " + "LOCK TIMESTAMP",
"PARAMETERS_ID", "QUERY_BLOB_MD5, QUERY_BLOB_SIZE");
}
private void checkTilesTable(Connection conn) throws SQLException {
condCreate(conn, "TILES", "TILE_ID BIGINT AUTO_INCREMENT PRIMARY KEY, LAYER_ID BIGINT, "
+ "X BIGINT, Y BIGINT, Z BIGINT, GRIDSET_ID INT, FORMAT_ID BIGINT, "
+ "PARAMETERS_ID BIGINT, BLOB_SIZE INT, "
+ "CREATED BIGINT, ACCESS_LAST BIGINT, ACCESS_COUNT BIGINT, " + "LOCK TIMESTAMP",
"LAYER_ID, X, Y, Z, GRIDSET_ID, FORMAT_ID, PARAMETERS_ID", null);
}
private void condCreate(Connection conn, String tableName, String columns, String indexColumns,
String index2Columns) throws SQLException {
Statement st = null;
try {
st = conn.createStatement();
st.execute("CREATE TABLE IF NOT EXISTS " + tableName + " (" + columns + ")");
st.execute("CREATE INDEX IF NOT EXISTS " + "IDX_" + tableName + " ON " + tableName
+ " (" + indexColumns + ")");
if (index2Columns != null) {
st.execute("CREATE INDEX IF NOT EXISTS " + "IDX2_" + tableName + " ON " + tableName
+ " (" + index2Columns + ")");
}
} finally {
close(st);
}
}
private void runDbUpgrade(Connection conn, int fromVersion) {
log.info("Upgrading H2 database from " + fromVersion + " to " + JDBCMBWrapper.DB_VERSION);
boolean earlier = false;
if (fromVersion == 110) {
log.info("Running database upgrade from 110 to 111");
earlier = true;
try {
// We start with this one to block other instances
String query = "UPDATE VARIABLES SET VALUE = ? WHERE KEY = ?";
PreparedStatement prep = conn.prepareStatement(query);
try {
prep.setString(1, "111");
prep.setString(2, "db_version");
prep.execute();
} finally {
close(prep);
}
query = "ALTER TABLE TILES ADD LOCK TIMESTAMP";
Statement st = conn.createStatement();
try {
st.execute(query);
} finally {
close(st);
}
query = "ALTER TABLE WFS ADD LOCK TIMESTAMP";
st = conn.createStatement();
try {
st.execute(query);
} finally {
close(st);
}
log.info("Database upgrade from 110 to 111 completed");
} catch (SQLException se) {
log.error("110 to 111 upgrade failed: " + se.getMessage());
}
}
if (fromVersion == 111 || earlier) {
log.info("Running database upgrade from 111 to 120");
try {
// We start with this one to block other instances
String query = "UPDATE VARIABLES SET VALUE = ? WHERE KEY = ?";
PreparedStatement prep = conn.prepareStatement(query);
try {
prep.setString(1, "120");
prep.setString(2, "db_version");
prep.execute();
} finally {
close(prep);
}
query = "ALTER TABLE TILES ALTER COLUMN SRS_ID RENAME TO GRIDSET_ID";
Statement st = conn.createStatement();
try {
st.execute(query);
} finally {
close(st);
}
// Now add the existing grids to the IdCache, so that the old stuff
// continues working: EPSG:4326 -> 4326 , which is what the SRS_ID used to be
query = "SELECT GRIDSET_ID FROM TILES GROUP BY GRIDSET_ID";
st = conn.createStatement();
try {
ResultSet rs = st.executeQuery(query);
try {
while (rs.next()) {
int val = rs.getInt(1);
query = "INSERT INTO GRIDSETS (ID, VALUE) VALUES (?,?)";
prep = conn.prepareStatement(query);
try {
prep.setLong(1, val);
prep.setString(2, "EPSG:" + val);
prep.executeUpdate();
} finally {
close(prep);
}
}
} finally {
close(rs);
}
} finally {
close(st);
}
log.info("Database upgrade from 111 to 120 completed");
} catch (SQLException se) {
log.error("111 to 120 upgrade failed: " + se.getMessage());
}
}
}
protected void deleteTile(TileObject stObj) throws SQLException {
final Connection conn = getConnection();
try {
deleteTile(conn, stObj);
} finally {
close(conn);
}
}
protected void deleteTile(Connection conn, TileObject stObj) throws SQLException {
String query;
if (stObj.getParametersId() == -1L) {
query = "DELETE FROM TILES WHERE "
+ " LAYER_ID = ? AND X = ? AND Y = ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID IS NULL";
} else {
query = "DELETE FROM TILES WHERE "
+ " LAYER_ID = ? AND X = ? AND Y = ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID = ?";
}
long[] xyz = stObj.getXYZ();
PreparedStatement prep = conn.prepareStatement(query);
try {
prep.setLong(1, stObj.getLayerId());
prep.setLong(2, xyz[0]);
prep.setLong(3, xyz[1]);
prep.setLong(4, xyz[2]);
prep.setLong(5, stObj.getGridSetIdId());
prep.setLong(6, stObj.getFormatId());
if (stObj.getParametersId() != -1L) {
prep.setLong(7, stObj.getParametersId());
}
prep.execute();
} finally {
close(prep);
}
}
protected boolean getTile(TileObject stObj) throws SQLException {
String query;
if (stObj.getParametersId() == -1L) {
query = "SELECT TILE_ID,BLOB_SIZE,CREATED,LOCK,NOW() FROM TILES WHERE "
+ " LAYER_ID = ? AND X = ? AND Y = ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID IS NULL LIMIT 1 ";
} else {
query = "SELECT TILE_ID,BLOB_SIZE,CREATED,LOCK,NOW() FROM TILES WHERE "
+ " LAYER_ID = ? AND X = ? AND Y = ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID = ? LIMIT 1 ";
}
long[] xyz = stObj.getXYZ();
final Connection conn = getConnection();
PreparedStatement prep = null;
try {
prep = conn.prepareStatement(query);
prep.setLong(1, stObj.getLayerId());
prep.setLong(2, xyz[0]);
prep.setLong(3, xyz[1]);
prep.setLong(4, xyz[2]);
prep.setLong(5, stObj.getGridSetIdId());
prep.setLong(6, stObj.getFormatId());
if (stObj.getParametersId() != -1L) {
prep.setLong(7, stObj.getParametersId());
}
ResultSet rs = prep.executeQuery();
try {
if (rs.first()) {
Timestamp lock = rs.getTimestamp(4);
// This tile is locked
if (lock != null) {
Timestamp now = rs.getTimestamp(5);
long diff = now.getTime() - lock.getTime();
// System.out.println(now.getTime() + " " + System.currentTimeMillis());
if (diff > lockTimeout) {
log.warn("Database lock exceeded (" + diff + "ms , " + lock.toString()
+ ") for " + stObj.toString() + ", clearing tile.");
deleteTile(conn, stObj);
stObj.setStatus(StorageObject.Status.EXPIRED_LOCK);
} else {
stObj.setStatus(StorageObject.Status.LOCK);
}
// This puts the request back in the queue
return false;
}
stObj.setId(rs.getLong(1));
stObj.setBlobSize(rs.getInt(2));
stObj.setCreated(rs.getLong(3));
stObj.setStatus(StorageObject.Status.HIT);
return true;
} else {
stObj.setStatus(StorageObject.Status.MISS);
return false;
}
} finally {
close(rs);
}
} finally {
close(prep);
close(conn);
}
}
public void putTile(TileObject stObj) throws SQLException, StorageException {
String query = "MERGE INTO "
+ "TILES(LAYER_ID,X,Y,Z,GRIDSET_ID,FORMAT_ID,PARAMETERS_ID,BLOB_SIZE,LOCK,CREATED) "
+ "KEY(LAYER_ID,X,Y,Z,GRIDSET_ID,FORMAT_ID,PARAMETERS_ID) "
+ "VALUES(?,?,?,?,?,?,?,?,NOW(),?)";
long[] xyz = stObj.getXYZ();
final Connection conn = getConnection();
try {
Long insertId;
PreparedStatement prep = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
try {
prep.setLong(1, stObj.getLayerId());
prep.setLong(2, xyz[0]);
prep.setLong(3, xyz[1]);
prep.setLong(4, xyz[2]);
prep.setLong(5, stObj.getGridSetIdId());
prep.setLong(6, stObj.getFormatId());
if (stObj.getParametersId() == -1L) {
prep.setNull(7, java.sql.Types.BIGINT);
} else {
prep.setLong(7, stObj.getParametersId());
}
prep.setInt(8, stObj.getBlobSize());
prep.setLong(9, System.currentTimeMillis());
insertId = wrappedInsert(prep);
} finally {
close(prep);
}
if (insertId == null) {
log.error("Did not receive a id for " + query);
} else {
stObj.setId(insertId.longValue());
}
} finally {
conn.close();
}
}
public boolean unlockTile(TileObject stObj) throws SQLException, StorageException {
String query = null;
if (stObj.getParametersId() == -1L) {
query = "UPDATE TILES SET LOCK = NULL WHERE "
+ " LAYER_ID = ? AND X = ? AND Y = ? AND Z = ? "
+ " AND GRIDSET_ID = ? AND FORMAT_ID = ? AND " + " PARAMETERS_ID IS NULL";
} else {
query = "UPDATE TILES SET LOCK = NULL WHERE "
+ " LAYER_ID = ? AND X = ? AND Y = ? AND Z = ? "
+ " AND GRIDSET_ID = ? AND FORMAT_ID = ? AND " + " PARAMETERS_ID = ?";
}
long[] xyz = stObj.getXYZ();
final Connection conn = getConnection();
PreparedStatement prep = null;
try {
prep = conn.prepareStatement(query);
prep.setLong(1, stObj.getLayerId());
prep.setLong(2, xyz[0]);
prep.setLong(3, xyz[1]);
prep.setLong(4, xyz[2]);
prep.setLong(5, stObj.getGridSetIdId());
prep.setLong(6, stObj.getFormatId());
if (stObj.getParametersId() != -1L) {
prep.setLong(7, stObj.getParametersId());
}
int affected = prep.executeUpdate();
// System.out.println("Affected: " + affected);
if (affected == 1) {
return true;
} else {
log.error("Expected to clear lock on one row, but got " + affected);
return false;
}
} finally {
close(prep);
close(conn);
}
}
protected Long wrappedInsert(PreparedStatement st) throws SQLException {
ResultSet rs = null;
try {
st.executeUpdate();
rs = st.getGeneratedKeys();
if (rs.next()) {
return Long.valueOf(rs.getLong(1));
}
return null;
} finally {
close(rs);
}
}
public void destroy() {
Connection conn = null;
try {
conn = getConnection();
this.closing = true;
try {
conn.createStatement().execute("SHUTDOWN");
} catch (SQLException se) {
log.warn("SHUTDOWN call to JDBC resulted in: " + se.getMessage());
}
} catch (SQLException e) {
log.error("Couldn't obtain JDBC Connection to perform database shut down", e);
} finally {
if (conn != null) {
// should be already closed after SHUTDOWN
boolean closed = false;
try {
closed = conn.isClosed();
} catch (SQLException e) {
log.error(e);
}
if (!closed) {
close(conn);
}
}
}
try {
Thread.sleep(250);
} catch (InterruptedException e) {
e.printStackTrace();
}
System.gc();
try {
Thread.sleep(500);
} catch (InterruptedException e) {
e.printStackTrace();
}
System.gc();
}
private PreparedStatement getTileSet(final Connection conn, long layerId, long formatId,
long parametersId, long zoomLevel, long[] bounds, long srsNumber) throws SQLException {
String query;
if (parametersId == -1L) {
query = "SELECT TILE_ID, X, Y, Z FROM TILES WHERE "
+ " LAYER_ID = ? AND X >= ? AND X <= ? AND Y >= ? AND Y <= ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID IS NULL";
} else {
query = "SELECT TILE_ID, X, Y, Z FROM TILES WHERE "
+ " LAYER_ID = ? AND X >= ? AND X <= ? AND Y >= ? AND Y <= ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID = ?";
}
PreparedStatement prep = conn.prepareStatement(query);
prep.setLong(1, layerId);
prep.setLong(2, bounds[0]);
prep.setLong(3, bounds[2]);
prep.setLong(4, bounds[1]);
prep.setLong(5, bounds[3]);
prep.setLong(6, zoomLevel);
prep.setLong(7, srsNumber);
prep.setLong(8, formatId);
if (parametersId != -1L) {
prep.setLong(9, parametersId);
}
return prep;
}
private void deleteRange(final Connection conn, long layerId, long formatId, long parametersId,
int zoomLevel, long[] bounds, long gridSetIdId) throws SQLException {
String query;
if (parametersId == -1L) {
query = "DELETE FROM TILES WHERE "
+ " LAYER_ID = ? AND X >= ? AND X <= ? AND Y >= ? AND Y <= ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID IS NULL";
} else {
query = "DELETE FROM TILES WHERE "
+ " LAYER_ID = ? AND X >= ? AND X <= ? AND Y >= ? AND Y <= ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID = ?";
}
PreparedStatement prep = conn.prepareStatement(query);
try {
prep.setLong(1, layerId);
prep.setLong(2, bounds[0]);
prep.setLong(3, bounds[2]);
prep.setLong(4, bounds[1]);
prep.setLong(5, bounds[3]);
prep.setLong(6, zoomLevel);
prep.setLong(7, gridSetIdId);
prep.setLong(8, formatId);
if (parametersId != -1L) {
prep.setLong(9, parametersId);
}
prep.execute();
} finally {
close(prep);
}
}
public void deleteLayer(long layerId) throws SQLException {
String query = "DELETE FROM TILES WHERE LAYER_ID = ?";
final Connection conn = getConnection();
try {
PreparedStatement prep = conn.prepareStatement(query);
try {
prep.setLong(1, layerId);
prep.execute();
} finally {
close(prep);
}
} finally {
close(conn);
}
}
public void renameLayer(final long layerId, final String newLayerName) throws SQLException {
String statement = "UPDATE LAYERS SET VALUE = ? WHERE ID = ?";
final Connection conn = getConnection();
try {
PreparedStatement prep = conn.prepareStatement(statement);
try {
prep.setString(1, newLayerName);
prep.setLong(2, layerId);
prep.execute();
int updateCount = prep.getUpdateCount();
if (1 == updateCount) {
log.info("Layer " + layerId + " successfully renamed to '" + newLayerName + "'");
} else {
log.warn("Attempt to rename layer with id " + layerId + " as '" + newLayerName
+ "' did not produce any update on the database!");
}
} finally {
close(prep);
}
} finally {
close(conn);
}
}
public boolean deleteRange(BlobStore blobStore, TileRange trObj, int zoomLevel, long layerId,
long formatId, long parametersId, long gridSetIdId) {
DiscontinuousTileRange dtrObj = null;
long[] deletedTiles = null;
if (trObj instanceof DiscontinuousTileRange) {
dtrObj = (DiscontinuousTileRange) trObj;
deletedTiles = new long[100];
}
long[] bounds = trObj.rangeBounds[zoomLevel];
final Connection conn;
try {
conn = getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
PreparedStatement tileSetQuery = null;
ResultSet rs = null;
try {
tileSetQuery = getTileSet(conn, layerId, formatId, parametersId, zoomLevel, bounds,
gridSetIdId);
rs = tileSetQuery.executeQuery();
int deletedIdx = 0;
while (rs.next()) {
// TILE_ID, X, Y, Z
long[] xyz = new long[3];
xyz[0] = rs.getLong(2);
xyz[1] = rs.getLong(3);
xyz[2] = rs.getLong(4);
if (dtrObj != null && !dtrObj.contains(xyz)) {
continue;
}
// System.out.println("x: " + xyz[0] + " y: " + xyz[1] + " z: " + xyz[2]);
TileObject to = TileObject.createQueryTileObject(trObj.layerName, xyz,
trObj.gridSetId, trObj.mimeType.getFormat(), trObj.parameters);
to.setParamtersId(parametersId);
try {
blobStore.delete(to);
} catch (StorageException e) {
log.debug("Error while deleting range: " + e.getMessage());
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
if (deletedTiles != null) {
deletedTiles[deletedIdx] = rs.getLong(1);
deletedIdx++;
if (deletedIdx == deletedTiles.length) {
deleteTileSet(conn, deletedTiles, deletedIdx);
deletedIdx = 0;
}
}
}
// Now remove the tiles from the database
if (deletedTiles != null) {
deleteTileSet(conn, deletedTiles, deletedIdx);
} else {
deleteRange(conn, layerId, formatId, parametersId, zoomLevel, bounds, gridSetIdId);
}
} catch (SQLException e) {
log.error("deleteRange failed: " + e.getMessage());
e.printStackTrace();
return false;
} finally {
close(rs);
close(tileSetQuery);
close(conn);
}
return true;
}
/**
* Deletes a tile
*
* @param tileIds
* @param stopIdx
* @throws SQLException
*/
private void deleteTileSet(final Connection conn, long[] tileIds, int stopIdx)
throws SQLException {
if (stopIdx == 0) {
return;
}
StringBuffer sb = new StringBuffer();
sb.append("DELETE FROM TILES WHERE TILE_ID IN (");
sb.append(tileIds[0]);
for (int i = 1; i < stopIdx; i++) {
sb.append(",");
sb.append(tileIds[i]);
}
sb.append(")");
PreparedStatement prepDel = conn.prepareStatement(sb.toString());
try {
prepDel.execute();
} finally {
close(prepDel);
}
log.debug("Deleted " + Arrays.toString(tileIds));
}
public void expireRange(TileRange trObj, int zoomLevel, long layerId, long formatId,
long parametersId, long gridSetIdId) throws SQLException {
long[] bounds = trObj.rangeBounds[zoomLevel];
String query;
if (parametersId == -1L) {
query = "UPDATE TILES SET CREATED = -1 WHERE "
+ " LAYER_ID = ? AND X >= ? AND X <= ? AND Y >= ? AND Y <= ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID IS NULL";
} else {
query = "UPDATE TILES SET CREATED = -1 WHERE "
+ " LAYER_ID = ? AND X >= ? AND X <= ? AND Y >= ? AND Y <= ? AND Z = ? AND GRIDSET_ID = ? "
+ " AND FORMAT_ID = ? AND PARAMETERS_ID = ?";
}
final Connection conn = getConnection();
try {
PreparedStatement prep = conn.prepareStatement(query);
try {
prep.setLong(1, layerId);
prep.setLong(2, bounds[0]);
prep.setLong(3, bounds[2]);
prep.setLong(4, bounds[1]);
prep.setLong(5, bounds[3]);
prep.setLong(6, zoomLevel);
prep.setLong(7, gridSetIdId);
prep.setLong(8, formatId);
if (parametersId != -1L) {
prep.setLong(9, parametersId);
}
prep.execute();
} finally {
close(prep);
}
} finally {
close(conn);
}
}
}