Revision 196
| trunk/org.gvsig.spatialite/org.gvsig.spatialite.provider/src/main/java/org/gvsig/spatialite/dal/SpatiaLiteHelper.java | ||
|---|---|---|
| 21 | 21 |
*/ |
| 22 | 22 |
package org.gvsig.spatialite.dal; |
| 23 | 23 |
|
| 24 |
import java.io.File; |
|
| 25 | 24 |
import org.gvsig.spatialite.dal.operations.SpatiaLiteOperationsFactory; |
| 26 | 25 |
import java.sql.Connection; |
| 27 |
import java.sql.SQLException; |
|
| 28 |
import java.sql.Statement; |
|
| 29 |
import java.util.Map; |
|
| 30 |
import org.apache.commons.dbcp.BasicDataSource; |
|
| 31 |
import org.apache.commons.io.FilenameUtils; |
|
| 32 |
import org.apache.commons.lang3.StringUtils; |
|
| 33 |
import org.gvsig.fmap.dal.SQLBuilder; |
|
| 26 |
import org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType; |
|
| 34 | 27 |
import org.gvsig.fmap.dal.exception.InitializeException; |
| 35 | 28 |
import org.gvsig.fmap.dal.resource.exception.AccessResourceException; |
| 36 | 29 |
import org.gvsig.fmap.dal.spi.DataServerExplorerProviderServices; |
| ... | ... | |
| 39 | 32 |
import org.gvsig.fmap.dal.store.jdbc.JDBCNewStoreParameters; |
| 40 | 33 |
import org.gvsig.fmap.dal.store.jdbc.JDBCServerExplorerParameters; |
| 41 | 34 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
| 42 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCDriverClassNotFoundException; |
|
| 43 | 35 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCServerExplorer; |
| 44 | 36 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCStoreProvider; |
| 45 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
|
| 46 | 37 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory; |
| 47 | 38 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCHelperBase; |
| 48 | 39 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
| ... | ... | |
| 56 | 47 |
import org.gvsig.fmap.geom.type.GeometryType; |
| 57 | 48 |
import org.slf4j.Logger; |
| 58 | 49 |
import org.slf4j.LoggerFactory; |
| 59 |
import org.sqlite.SQLiteConfig; |
|
| 60 |
import org.sqlite.SQLiteConfig.TransactionMode; |
|
| 61 | 50 |
|
| 62 | 51 |
@SuppressWarnings("UseSpecificCatch")
|
| 63 | 52 |
public class SpatiaLiteHelper extends JDBCHelperBase {
|
| ... | ... | |
| 65 | 54 |
static final Logger LOGGER = LoggerFactory.getLogger(SpatiaLiteHelper.class); |
| 66 | 55 |
|
| 67 | 56 |
public static final String NAME = "SpatiaLite"; |
| 68 |
public static final String SPATIALITE_JDBC_DRIVER = "org.sqlite.JDBC"; |
|
| 69 |
public static final String URL_FORMAT = "jdbc:sqlite:%s"; |
|
| 70 |
|
|
| 71 |
public static String getConnectionURL(SpatiaLiteConnectionParameters params) {
|
|
| 72 |
String fname = params.getFile().getAbsolutePath().replace("\\","/");
|
|
| 73 |
if( StringUtils.isEmpty(FilenameUtils.getExtension(fname)) ) {
|
|
| 74 |
fname = fname + ".sqlite"; |
|
| 75 |
params.setFile(new File(fname)); |
|
| 76 |
} |
|
| 77 |
String driverClassName = params.getJDBCDriverClassName(); |
|
| 78 |
if( StringUtils.isNotBlank(driverClassName) && |
|
| 79 |
!StringUtils.equals(driverClassName, SPATIALITE_JDBC_DRIVER) ) {
|
|
| 80 |
try {
|
|
| 81 |
Class.forName(driverClassName); |
|
| 82 |
} catch(Throwable th) {
|
|
| 83 |
LOGGER.warn("Can't load SpatiaLite JDBC Driver '"+driverClassName+"'.",th);
|
|
| 84 |
} |
|
| 85 |
} |
|
| 86 |
String connectionURLFormat = params.getURLFormat(); |
|
| 87 |
if( StringUtils.isBlank(connectionURLFormat) ) {
|
|
| 88 |
connectionURLFormat = URL_FORMAT; |
|
| 89 |
} |
|
| 90 |
String connectionURL = String.format(connectionURLFormat, fname); |
|
| 91 |
LOGGER.debug("connectionURL: {}", connectionURL);
|
|
| 92 |
return connectionURL; |
|
| 93 |
} |
|
| 94 |
|
|
| 95 |
private static class ConnectionProvider {
|
|
| 96 |
|
|
| 97 |
private static boolean needRegisterDriver = true; |
|
| 98 |
|
|
| 99 |
private BasicDataSource dataSource = null; |
|
| 100 |
|
|
| 101 |
private final SpatiaLiteConnectionParameters connectionParameters; |
|
| 102 |
|
|
| 103 |
public ConnectionProvider(SpatiaLiteConnectionParameters connectionParameters) {
|
|
| 104 |
this.connectionParameters = connectionParameters; |
|
| 105 |
} |
|
| 106 |
|
|
| 107 |
public Connection getConnection() throws SQLException {
|
|
| 108 |
File f = this.connectionParameters.getFile(); |
|
| 109 |
Connection conn = SpatiaLiteConnections.get(f); |
|
| 110 |
if( conn != null ) {
|
|
| 111 |
return conn; |
|
| 112 |
} |
|
| 113 |
boolean newDs = (this.dataSource == null); |
|
| 114 |
if (newDs) {
|
|
| 115 |
this.dataSource = this.createDataSource(); |
|
| 116 |
} |
|
| 117 |
conn = this.dataSource.getConnection(); |
|
| 118 |
loadExtension(conn); |
|
| 119 |
if (newDs) {
|
|
| 120 |
initSpatialMetadata(conn); |
|
| 121 |
} |
|
| 122 |
SpatiaLiteConnections.set(f, conn); |
|
| 123 |
return conn; |
|
| 124 |
} |
|
| 125 |
|
|
| 126 |
private BasicDataSource createDataSource() throws SQLException {
|
|
| 127 |
if (!this.isRegistered()) {
|
|
| 128 |
this.registerDriver(); |
|
| 129 |
} |
|
| 130 |
SpatiaLiteConnectionParameters params = connectionParameters; |
|
| 131 |
|
|
| 132 |
BasicDataSource ds = new BasicDataSource(); |
|
| 133 |
ds.setDriverClassName(params.getJDBCDriverClassName()); |
|
| 134 |
if( !StringUtils.isEmpty(params.getUser()) ) {
|
|
| 135 |
ds.setUsername(params.getUser()); |
|
| 136 |
} |
|
| 137 |
if( !StringUtils.isEmpty(params.getPassword()) ) {
|
|
| 138 |
ds.setPassword(params.getPassword()); |
|
| 139 |
} |
|
| 140 |
ds.setUrl(params.getUrl()); |
|
| 141 |
SQLiteConfig config = new SQLiteConfig(); |
|
| 142 |
config.setSharedCache(true); |
|
| 143 |
config.enableLoadExtension(true); |
|
| 144 |
config.setTransactionMode(TransactionMode.IMMEDIATE); |
|
| 145 |
for (Map.Entry e : config.toProperties().entrySet()) {
|
|
| 146 |
ds.addConnectionProperty((String)e.getKey(), (String)e.getValue()); |
|
| 147 |
} |
|
| 148 |
ds.setMaxWait(60L * 1000); |
|
| 149 |
return ds; |
|
| 150 |
} |
|
| 151 |
|
|
| 152 |
private boolean isRegistered() {
|
|
| 153 |
return needRegisterDriver; |
|
| 154 |
} |
|
| 155 |
|
|
| 156 |
public void registerDriver() throws SQLException {
|
|
| 157 |
String className = this.connectionParameters.getJDBCDriverClassName(); |
|
| 158 |
if (className == null) {
|
|
| 159 |
return; |
|
| 160 |
} |
|
| 161 |
try {
|
|
| 162 |
Class theClass = Class.forName(className); |
|
| 163 |
if (theClass == null) {
|
|
| 164 |
throw new JDBCDriverClassNotFoundException(NAME, className); |
|
| 165 |
} |
|
| 166 |
} catch (Exception e) {
|
|
| 167 |
throw new SQLException("Can't register JDBC driver '" + className + "'.", e);
|
|
| 168 |
} |
|
| 169 |
needRegisterDriver = false; |
|
| 170 |
} |
|
| 171 |
|
|
| 172 |
public void loadExtension(Connection conn) {
|
|
| 173 |
Statement st = null; |
|
| 174 |
try {
|
|
| 175 |
st = conn.createStatement(); |
|
| 176 |
JDBCUtils.execute(st, "SELECT load_extension('mod_spatialite')");
|
|
| 177 |
} catch(Exception ex) {
|
|
| 178 |
LOGGER.warn("Can't load mod_spatialite extension module for SQLite (" +
|
|
| 179 |
" driver class "+StringUtils.defaultString(connectionParameters.getJDBCDriverClassName()) + |
|
| 180 |
" url format "+StringUtils.defaultString(connectionParameters.getURLFormat()) + |
|
| 181 |
" file "+connectionParameters.getFile()==null?"null":connectionParameters.getFile().getAbsolutePath() + |
|
| 182 |
").", ex); |
|
| 183 |
} |
|
| 184 |
finally {
|
|
| 185 |
JDBCUtils.closeQuietly(st); |
|
| 186 |
} |
|
| 187 |
} |
|
| 188 |
|
|
| 189 |
public void initSpatialMetadata(Connection conn) {
|
|
| 190 |
Statement st = null; |
|
| 191 |
try {
|
|
| 192 |
st = conn.createStatement(); |
|
| 193 |
//harmless if already existing |
|
| 194 |
JDBCUtils.execute(st, "SELECT InitSpatialMetaData(1)"); |
|
| 195 |
} catch(Exception ex) {
|
|
| 196 |
LOGGER.warn("Can't initialize spatial metatada in SQLite database (" +
|
|
| 197 |
" driver class "+StringUtils.defaultString(connectionParameters.getJDBCDriverClassName()) + |
|
| 198 |
" url format "+StringUtils.defaultString(connectionParameters.getURLFormat()) + |
|
| 199 |
" file "+connectionParameters.getFile()==null?"null":connectionParameters.getFile().getAbsolutePath() + |
|
| 200 |
").", ex); |
|
| 201 |
} |
|
| 202 |
finally {
|
|
| 203 |
JDBCUtils.closeQuietly(st); |
|
| 204 |
} |
|
| 205 |
} |
|
| 206 |
|
|
| 207 |
} |
|
| 208 |
|
|
| 209 |
private ConnectionProvider connectionProvider = null; |
|
| 57 |
// public static final String SPATIALITE_JDBC_DRIVER = "org.sqlite.JDBC";
|
|
| 58 |
// public static final String URL_FORMAT = "jdbc:sqlite:%s";
|
|
| 59 |
// |
|
| 60 |
// public static String getConnectionURL(SpatiaLiteConnectionParameters params) {
|
|
| 61 |
// String fname = params.getFile().getAbsolutePath().replace("\\","/");
|
|
| 62 |
// if( StringUtils.isEmpty(FilenameUtils.getExtension(fname)) ) {
|
|
| 63 |
// fname = fname + ".sqlite";
|
|
| 64 |
// params.setFile(new File(fname));
|
|
| 65 |
// }
|
|
| 66 |
// String driverClassName = params.getJDBCDriverClassName();
|
|
| 67 |
// if( StringUtils.isNotBlank(driverClassName) &&
|
|
| 68 |
// !StringUtils.equals(driverClassName, SPATIALITE_JDBC_DRIVER) ) {
|
|
| 69 |
// try {
|
|
| 70 |
// Class.forName(driverClassName);
|
|
| 71 |
// } catch(Throwable th) {
|
|
| 72 |
// LOGGER.warn("Can't load SpatiaLite JDBC Driver '"+driverClassName+"'.",th);
|
|
| 73 |
// }
|
|
| 74 |
// }
|
|
| 75 |
// String connectionURLFormat = params.getURLFormat();
|
|
| 76 |
// if( StringUtils.isBlank(connectionURLFormat) ) {
|
|
| 77 |
// connectionURLFormat = URL_FORMAT;
|
|
| 78 |
// }
|
|
| 79 |
// String connectionURL = String.format(connectionURLFormat, fname);
|
|
| 80 |
// LOGGER.debug("connectionURL: {}", connectionURL);
|
|
| 81 |
// return connectionURL;
|
|
| 82 |
// }
|
|
| 83 |
// |
|
| 84 |
// private static class ConnectionProvider {
|
|
| 85 |
// |
|
| 86 |
// private static boolean needRegisterDriver = true;
|
|
| 87 |
// |
|
| 88 |
// private BasicDataSource dataSource = null;
|
|
| 89 |
// |
|
| 90 |
// private final SpatiaLiteConnectionParameters connectionParameters;
|
|
| 91 |
// |
|
| 92 |
// public ConnectionProvider(SpatiaLiteConnectionParameters connectionParameters) {
|
|
| 93 |
// this.connectionParameters = connectionParameters;
|
|
| 94 |
// }
|
|
| 95 |
// |
|
| 96 |
// public Connection getConnection() throws SQLException {
|
|
| 97 |
// File f = this.connectionParameters.getFile();
|
|
| 98 |
// Connection conn = SpatiaLiteConnections.get(f);
|
|
| 99 |
// if( conn != null ) {
|
|
| 100 |
// return conn;
|
|
| 101 |
// }
|
|
| 102 |
// boolean newDs = (this.dataSource == null);
|
|
| 103 |
// if (newDs) {
|
|
| 104 |
// this.dataSource = this.createDataSource();
|
|
| 105 |
// }
|
|
| 106 |
// conn = this.dataSource.getConnection();
|
|
| 107 |
// loadExtension(conn);
|
|
| 108 |
// if (newDs) {
|
|
| 109 |
// initSpatialMetadata(conn);
|
|
| 110 |
// }
|
|
| 111 |
// SpatiaLiteConnections.set(f, conn);
|
|
| 112 |
// return conn;
|
|
| 113 |
// }
|
|
| 114 |
// |
|
| 115 |
// private BasicDataSource createDataSource() throws SQLException {
|
|
| 116 |
// if (!this.isRegistered()) {
|
|
| 117 |
// this.registerDriver();
|
|
| 118 |
// }
|
|
| 119 |
// SpatiaLiteConnectionParameters params = connectionParameters;
|
|
| 120 |
// |
|
| 121 |
// BasicDataSource ds = new BasicDataSource();
|
|
| 122 |
// ds.setDriverClassName(params.getJDBCDriverClassName());
|
|
| 123 |
// if( !StringUtils.isEmpty(params.getUser()) ) {
|
|
| 124 |
// ds.setUsername(params.getUser());
|
|
| 125 |
// }
|
|
| 126 |
// if( !StringUtils.isEmpty(params.getPassword()) ) {
|
|
| 127 |
// ds.setPassword(params.getPassword());
|
|
| 128 |
// }
|
|
| 129 |
// ds.setUrl(params.getUrl());
|
|
| 130 |
// SQLiteConfig config = new SQLiteConfig();
|
|
| 131 |
// config.setSharedCache(true);
|
|
| 132 |
// config.enableLoadExtension(true);
|
|
| 133 |
// config.setTransactionMode(TransactionMode.IMMEDIATE);
|
|
| 134 |
// for (Map.Entry e : config.toProperties().entrySet()) {
|
|
| 135 |
// ds.addConnectionProperty((String)e.getKey(), (String)e.getValue());
|
|
| 136 |
// }
|
|
| 137 |
// ds.setMaxWait(60L * 1000);
|
|
| 138 |
// return ds;
|
|
| 139 |
// }
|
|
| 140 |
// |
|
| 141 |
// private boolean isRegistered() {
|
|
| 142 |
// return needRegisterDriver;
|
|
| 143 |
// }
|
|
| 144 |
// |
|
| 145 |
// public void registerDriver() throws SQLException {
|
|
| 146 |
// String className = this.connectionParameters.getJDBCDriverClassName();
|
|
| 147 |
// if (className == null) {
|
|
| 148 |
// return;
|
|
| 149 |
// }
|
|
| 150 |
// try {
|
|
| 151 |
// Class theClass = Class.forName(className);
|
|
| 152 |
// if (theClass == null) {
|
|
| 153 |
// throw new JDBCDriverClassNotFoundException(NAME, className);
|
|
| 154 |
// }
|
|
| 155 |
// } catch (Exception e) {
|
|
| 156 |
// throw new SQLException("Can't register JDBC driver '" + className + "'.", e);
|
|
| 157 |
// }
|
|
| 158 |
// needRegisterDriver = false;
|
|
| 159 |
// }
|
|
| 160 |
// |
|
| 161 |
// public void loadExtension(Connection conn) {
|
|
| 162 |
// Statement st = null;
|
|
| 163 |
// try {
|
|
| 164 |
// st = conn.createStatement();
|
|
| 165 |
// JDBCUtils.execute(st, "SELECT load_extension('mod_spatialite')");
|
|
| 166 |
// } catch(Exception ex) {
|
|
| 167 |
// LOGGER.warn("Can't load mod_spatialite extension module for SQLite (" +
|
|
| 168 |
// " driver class "+StringUtils.defaultString(connectionParameters.getJDBCDriverClassName()) +
|
|
| 169 |
// " url format "+StringUtils.defaultString(connectionParameters.getURLFormat()) +
|
|
| 170 |
// " file "+connectionParameters.getFile()==null?"null":connectionParameters.getFile().getAbsolutePath() +
|
|
| 171 |
// ").", ex);
|
|
| 172 |
// }
|
|
| 173 |
// finally {
|
|
| 174 |
// JDBCUtils.closeQuietly(st);
|
|
| 175 |
// }
|
|
| 176 |
// }
|
|
| 177 |
// |
|
| 178 |
// public void initSpatialMetadata(Connection conn) {
|
|
| 179 |
// Statement st = null;
|
|
| 180 |
// try {
|
|
| 181 |
// st = conn.createStatement();
|
|
| 182 |
// //harmless if already existing
|
|
| 183 |
// JDBCUtils.execute(st, "SELECT InitSpatialMetaData(1)");
|
|
| 184 |
// } catch(Exception ex) {
|
|
| 185 |
// LOGGER.warn("Can't initialize spatial metatada in SQLite database (" +
|
|
| 186 |
// " driver class "+StringUtils.defaultString(connectionParameters.getJDBCDriverClassName()) +
|
|
| 187 |
// " url format "+StringUtils.defaultString(connectionParameters.getURLFormat()) +
|
|
| 188 |
// " file "+connectionParameters.getFile()==null?"null":connectionParameters.getFile().getAbsolutePath() +
|
|
| 189 |
// ").", ex);
|
|
| 190 |
// }
|
|
| 191 |
// finally {
|
|
| 192 |
// JDBCUtils.closeQuietly(st);
|
|
| 193 |
// }
|
|
| 194 |
// }
|
|
| 195 |
// |
|
| 196 |
// }
|
|
| 197 |
// |
|
| 198 |
// private ConnectionProvider connectionProvider = null;
|
|
| 210 | 199 |
|
| 211 | 200 |
public SpatiaLiteHelper(JDBCConnectionParameters connectionParameters) {
|
| 212 | 201 |
super(connectionParameters); |
| ... | ... | |
| 215 | 204 |
|
| 216 | 205 |
@Override |
| 217 | 206 |
public synchronized Connection getConnection() throws AccessResourceException {
|
| 218 |
try {
|
|
| 219 |
if (this.connectionProvider == null) {
|
|
| 220 |
this.connectionProvider = new ConnectionProvider(this.getConnectionParameters()); |
|
| 221 |
} |
|
| 222 |
Connection connection = this.connectionProvider.getConnection(); |
|
| 223 |
LOGGER.debug("getConnection: connection = "+connection.hashCode());
|
|
| 224 |
return connection; |
|
| 225 |
} catch (SQLException ex) {
|
|
| 226 |
throw new AccessResourceException(SpatiaLiteLibrary.NAME, ex); |
|
| 227 |
} |
|
| 207 |
return SpatiaLiteConnections.getConnection(this.getConnectionParameters().getFile()); |
|
| 208 |
// try {
|
|
| 209 |
// if (this.connectionProvider == null) {
|
|
| 210 |
// this.connectionProvider = new ConnectionProvider(this.getConnectionParameters()); |
|
| 211 |
// } |
|
| 212 |
// Connection connection = this.connectionProvider.getConnection(); |
|
| 213 |
// LOGGER.debug("getConnection: connection = "+connection.hashCode());
|
|
| 214 |
// return connection; |
|
| 215 |
// } catch (SQLException ex) {
|
|
| 216 |
// throw new AccessResourceException(SpatiaLiteLibrary.NAME, ex); |
|
| 217 |
// } |
|
| 228 | 218 |
} |
| 229 | 219 |
|
| 220 |
@Override |
|
| 230 | 221 |
public void closeConnection(Connection connection) {
|
| 231 |
// With sqlite we only have one connection, which we never closed |
|
| 222 |
if( connection==null ) {
|
|
| 223 |
return; |
|
| 224 |
} |
|
| 225 |
SpatiaLiteConnections.close(connection); |
|
| 232 | 226 |
} |
| 227 |
|
|
| 228 |
@Override |
|
| 229 |
public void closeConnectionQuietly(Connection connection) {
|
|
| 230 |
if( connection==null ) {
|
|
| 231 |
return; |
|
| 232 |
} |
|
| 233 |
try {
|
|
| 234 |
SpatiaLiteConnections.close(connection); |
|
| 235 |
} catch(Exception ex) {
|
|
| 236 |
|
|
| 237 |
} |
|
| 238 |
} |
|
| 233 | 239 |
|
| 234 |
|
|
| 235 | 240 |
@Override |
| 236 | 241 |
public SpatiaLiteConnectionParameters getConnectionParameters() {
|
| 237 | 242 |
return (SpatiaLiteConnectionParameters) super.getConnectionParameters(); |
| ... | ... | |
| 239 | 244 |
|
| 240 | 245 |
@Override |
| 241 | 246 |
public String getConnectionURL() {
|
| 242 |
return getConnectionURL(this.getConnectionParameters()); |
|
| 247 |
return SpatiaLiteConnections.getConnectionURL(this.getConnectionParameters().getFile()); |
|
| 248 |
// return getConnectionURL(this.getConnectionParameters()); |
|
| 243 | 249 |
} |
| 244 | 250 |
|
| 245 | 251 |
@Override |
| ... | ... | |
| 252 | 258 |
return SpatiaLiteLibrary.NAME; |
| 253 | 259 |
} |
| 254 | 260 |
|
| 255 |
// @Override
|
|
| 256 |
// public JDBCSQLBuilderBase createSQLBuilder() {
|
|
| 257 |
// return new SpatiaLiteSQLBuilder(this);
|
|
| 258 |
// }
|
|
| 261 |
@Override |
|
| 262 |
public JDBCSQLBuilderBase createSQLBuilder() {
|
|
| 263 |
return new SpatiaLiteSQLBuilder(this); |
|
| 264 |
} |
|
| 259 | 265 |
|
| 260 | 266 |
@Override |
| 261 | 267 |
public OperationsFactory getOperations() {
|
| ... | ... | |
| 266 | 272 |
} |
| 267 | 273 |
|
| 268 | 274 |
@Override |
| 269 |
public SQLBuilder.GeometrySupportType getGeometrySupportType() {
|
|
| 270 |
return SQLBuilder.GeometrySupportType.WKB;
|
|
| 275 |
public GeometrySupportType getGeometrySupportType() {
|
|
| 276 |
return GeometrySupportType.WKB; |
|
| 271 | 277 |
} |
| 272 | 278 |
|
| 273 | 279 |
@Override |
| ... | ... | |
| 390 | 396 |
public JDBCStoreProvider createProvider(JDBCStoreParameters parameters, DataStoreProviderServices providerServices) throws InitializeException {
|
| 391 | 397 |
return super.createProvider(parameters, providerServices); |
| 392 | 398 |
} |
| 399 |
|
|
| 400 |
@Override |
|
| 401 |
public boolean isThreadSafe() {
|
|
| 402 |
return false; |
|
| 403 |
} |
|
| 393 | 404 |
|
| 394 |
|
|
| 395 | 405 |
} |
| trunk/org.gvsig.spatialite/org.gvsig.spatialite.provider/src/main/java/org/gvsig/spatialite/dal/SpatiaLiteConnections.java | ||
|---|---|---|
| 2 | 2 |
|
| 3 | 3 |
import java.io.File; |
| 4 | 4 |
import java.sql.Connection; |
| 5 |
import java.sql.DriverManager; |
|
| 6 |
import java.sql.SQLException; |
|
| 7 |
import java.sql.Statement; |
|
| 5 | 8 |
import java.util.HashMap; |
| 6 | 9 |
import java.util.Map; |
| 7 |
import java.util.Objects; |
|
| 10 |
import org.apache.commons.io.FilenameUtils; |
|
| 11 |
import org.apache.commons.lang3.StringUtils; |
|
| 12 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
|
| 13 |
import static org.gvsig.spatialite.dal.SpatiaLiteHelper.LOGGER; |
|
| 8 | 14 |
import org.slf4j.Logger; |
| 9 | 15 |
import org.slf4j.LoggerFactory; |
| 16 |
import org.sqlite.SQLiteConfig; |
|
| 10 | 17 |
|
| 11 | 18 |
/** |
| 12 | 19 |
* |
| 13 | 20 |
* @author jjdelcerro |
| 14 | 21 |
*/ |
| 22 |
@SuppressWarnings("UseSpecificCatch")
|
|
| 15 | 23 |
public class SpatiaLiteConnections {
|
| 16 | 24 |
|
| 17 | 25 |
private static final Logger LOGGER = LoggerFactory.getLogger(SpatiaLiteConnections.class); |
| 18 |
|
|
| 19 |
private static final Map<File,Connection> CONNECTIONS; |
|
| 20 |
|
|
| 21 |
static {
|
|
| 22 |
CONNECTIONS = new HashMap<>(); |
|
| 26 |
|
|
| 27 |
public static final String SPATIALITE_JDBC_DRIVER = "org.sqlite.JDBC"; |
|
| 28 |
public static final String URL_FORMAT = "jdbc:sqlite:%s"; |
|
| 29 |
|
|
| 30 |
private static class ConnectionEntry {
|
|
| 31 |
|
|
| 32 |
public ConnectionEntry(File f) {
|
|
| 33 |
this.f = f; |
|
| 34 |
this.refCount = 1; |
|
| 35 |
this.metadataInitialized = false; |
|
| 36 |
} |
|
| 37 |
|
|
| 38 |
private Connection conn; |
|
| 39 |
private File f; |
|
| 40 |
private int refCount; |
|
| 41 |
private boolean metadataInitialized; |
|
| 23 | 42 |
} |
| 24 |
|
|
| 25 |
public static Connection get(File f) {
|
|
| 26 |
Connection conn = CONNECTIONS.get(f); |
|
| 27 |
return conn; |
|
| 43 |
|
|
| 44 |
private static Map<File, ConnectionEntry> CONNECTIONS; |
|
| 45 |
|
|
| 46 |
public static synchronized Connection getConnection(File f) {
|
|
| 47 |
if (f == null) {
|
|
| 48 |
LOGGER.warn("Can't get connection for null file.");
|
|
| 49 |
return null; |
|
| 50 |
} |
|
| 51 |
if (CONNECTIONS == null) {
|
|
| 52 |
registerDriver(); |
|
| 53 |
} |
|
| 54 |
try {
|
|
| 55 |
ConnectionEntry entry = CONNECTIONS.get(f); |
|
| 56 |
if (entry == null) {
|
|
| 57 |
entry = new ConnectionEntry(f); |
|
| 58 |
} else {
|
|
| 59 |
if( entry.conn==null ) {
|
|
| 60 |
entry.refCount = 1; |
|
| 61 |
} else if( !entry.conn.isClosed()) {
|
|
| 62 |
entry.refCount += 1; |
|
| 63 |
LOGGER.debug("Reuse connection "+ entry.conn.hashCode() + " (refs "+entry.refCount+", "+entry.conn.toString()+")");
|
|
| 64 |
return entry.conn; |
|
| 65 |
} |
|
| 66 |
} |
|
| 67 |
|
|
| 68 |
SQLiteConfig config = new SQLiteConfig(); |
|
| 69 |
config.setSharedCache(true); |
|
| 70 |
config.enableLoadExtension(true); |
|
| 71 |
config.setTransactionMode(SQLiteConfig.TransactionMode.IMMEDIATE); |
|
| 72 |
|
|
| 73 |
entry.conn = DriverManager.getConnection( |
|
| 74 |
getConnectionURL(entry.f), |
|
| 75 |
config.toProperties() |
|
| 76 |
); |
|
| 77 |
loadExtension(entry); |
|
| 78 |
if( !entry.metadataInitialized ) {
|
|
| 79 |
initSpatialMetadata(entry); |
|
| 80 |
entry.metadataInitialized = true; |
|
| 81 |
} |
|
| 82 |
CONNECTIONS.put(f, entry); |
|
| 83 |
LOGGER.debug("Open connection "+ entry.conn.hashCode() + " (refs "+entry.refCount+", "+entry.conn.toString()+")");
|
|
| 84 |
return entry.conn; |
|
| 85 |
} catch (SQLException ex) {
|
|
| 86 |
LOGGER.warn("Can't get connection for file '" + f + "'.");
|
|
| 87 |
return null; |
|
| 88 |
} |
|
| 28 | 89 |
} |
| 29 |
|
|
| 30 |
public static void set(File f, Connection conn) {
|
|
| 31 |
LOGGER.info("#######>>>> Create new connection for file "+Objects.toString(f));
|
|
| 32 |
CONNECTIONS.put(f, conn); |
|
| 90 |
|
|
| 91 |
public static synchronized void close(Connection conn) {
|
|
| 92 |
for (ConnectionEntry entry : CONNECTIONS.values()) {
|
|
| 93 |
if (conn == entry.conn) {
|
|
| 94 |
entry.refCount -= 1; |
|
| 95 |
if (entry.refCount < 1) {
|
|
| 96 |
JDBCUtils.closeQuietly(entry.conn); |
|
| 97 |
entry.conn = null; |
|
| 98 |
entry.refCount = 0; |
|
| 99 |
return; |
|
| 100 |
} |
|
| 101 |
LOGGER.debug("Skip close connection "+ entry.conn.hashCode() + " (refs "+entry.refCount+", "+entry.conn.toString()+")");
|
|
| 102 |
break; |
|
| 103 |
} |
|
| 104 |
} |
|
| 33 | 105 |
} |
| 106 |
|
|
| 107 |
public static String getConnectionURL(File f) {
|
|
| 108 |
String fname = f.getAbsolutePath().replace("\\", "/");
|
|
| 109 |
if (StringUtils.isEmpty(FilenameUtils.getExtension(fname))) {
|
|
| 110 |
fname = fname + ".sqlite"; |
|
| 111 |
} |
|
| 112 |
String connectionURL = String.format(URL_FORMAT, fname); |
|
| 113 |
LOGGER.debug("connectionURL: {}", connectionURL);
|
|
| 114 |
return connectionURL; |
|
| 115 |
} |
|
| 116 |
|
|
| 117 |
public static void registerDriver() {
|
|
| 118 |
Class theClass = null; |
|
| 119 |
try {
|
|
| 120 |
theClass = Class.forName(SPATIALITE_JDBC_DRIVER); |
|
| 121 |
} catch (Exception e) {
|
|
| 122 |
LOGGER.warn("Can't register JDBC driver '" + SPATIALITE_JDBC_DRIVER + "'.", e);
|
|
| 123 |
throw new SpatiaLiteDriverNotFound(e); |
|
| 124 |
} |
|
| 125 |
if (theClass == null) {
|
|
| 126 |
LOGGER.warn("Can't register JDBC driver '" + SPATIALITE_JDBC_DRIVER + "'.");
|
|
| 127 |
throw new SpatiaLiteDriverNotFound(); |
|
| 128 |
} |
|
| 129 |
if (CONNECTIONS == null) {
|
|
| 130 |
CONNECTIONS = new HashMap<>(); |
|
| 131 |
} |
|
| 132 |
} |
|
| 133 |
|
|
| 134 |
private static void loadExtension(ConnectionEntry entry) {
|
|
| 135 |
Statement st = null; |
|
| 136 |
try {
|
|
| 137 |
st = entry.conn.createStatement(); |
|
| 138 |
JDBCUtils.execute(st, "SELECT load_extension('mod_spatialite')");
|
|
| 139 |
|
|
| 140 |
} catch (Exception ex) {
|
|
| 141 |
LOGGER.warn("Can't load mod_spatialite extension module for SQLite (" +
|
|
| 142 |
entry.f.getAbsolutePath() + ").", ex); |
|
| 143 |
|
|
| 144 |
} finally {
|
|
| 145 |
JDBCUtils.closeQuietly(st); |
|
| 146 |
} |
|
| 147 |
} |
|
| 148 |
|
|
| 149 |
private static void initSpatialMetadata(ConnectionEntry entry) {
|
|
| 150 |
Statement st = null; |
|
| 151 |
try {
|
|
| 152 |
st = entry.conn.createStatement(); |
|
| 153 |
JDBCUtils.execute(st, "SELECT InitSpatialMetaData(1)"); |
|
| 154 |
} catch (Exception ex) {
|
|
| 155 |
LOGGER.warn("Can't initialize spatial metatada in SQLite database (" +
|
|
| 156 |
entry.f.getAbsolutePath() + ").", ex); |
|
| 157 |
|
|
| 158 |
} finally {
|
|
| 159 |
JDBCUtils.closeQuietly(st); |
|
| 160 |
} |
|
| 161 |
} |
|
| 162 |
|
|
| 163 |
private static class SpatiaLiteDriverNotFound extends RuntimeException {
|
|
| 164 |
|
|
| 165 |
public SpatiaLiteDriverNotFound() {
|
|
| 166 |
super("Can't locate the SpatiaLite JDBC driver '" + SPATIALITE_JDBC_DRIVER + "'.");
|
|
| 167 |
} |
|
| 168 |
|
|
| 169 |
public SpatiaLiteDriverNotFound(Throwable cause) {
|
|
| 170 |
this(); |
|
| 171 |
this.initCause(cause); |
|
| 172 |
} |
|
| 173 |
} |
|
| 34 | 174 |
} |
| trunk/org.gvsig.spatialite/org.gvsig.spatialite.provider/src/main/java/org/gvsig/spatialite/dal/SpatiaLiteConnectionParametersHelper.java | ||
|---|---|---|
| 10 | 10 |
import org.gvsig.fmap.dal.exception.ValidateDataParametersException; |
| 11 | 11 |
import org.gvsig.fmap.dal.resource.db.DBParameters; |
| 12 | 12 |
import org.gvsig.fmap.dal.store.jdbc.JDBCConnectionParameters; |
| 13 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
|
| 14 | 13 |
|
| 15 | 14 |
|
| 16 | 15 |
public class SpatiaLiteConnectionParametersHelper {
|
| ... | ... | |
| 31 | 30 |
public String getUrl() {
|
| 32 | 31 |
String url = (String) this.getDynValue(JDBCConnectionParameters.URL_PARAMTER_NAME); |
| 33 | 32 |
if( StringUtils.isEmpty(url) ) {
|
| 34 |
url = SpatiaLiteHelper.getConnectionURL((SpatiaLiteConnectionParameters) this.parameters); |
|
| 33 |
// url = SpatiaLiteHelper.getConnectionURL((SpatiaLiteConnectionParameters) this.parameters); |
|
| 34 |
url = SpatiaLiteConnections.getConnectionURL(((SpatiaLiteConnectionParameters)this.parameters).getFile()); |
|
| 35 | 35 |
this.setDynValue(JDBCConnectionParameters.URL_PARAMTER_NAME,url); |
| 36 | 36 |
} |
| 37 | 37 |
return url; |
| ... | ... | |
| 40 | 40 |
public void validate() throws ValidateDataParametersException {
|
| 41 | 41 |
if (this.getDynValue(JDBCConnectionParameters.JDBC_DRIVER_CLASS_PARAMTER_NAME) == null) {
|
| 42 | 42 |
this.setDynValue(JDBCConnectionParameters.JDBC_DRIVER_CLASS_PARAMTER_NAME, |
| 43 |
SpatiaLiteHelper.SPATIALITE_JDBC_DRIVER
|
|
| 43 |
SpatiaLiteConnections.SPATIALITE_JDBC_DRIVER
|
|
| 44 | 44 |
); |
| 45 | 45 |
} |
| 46 | 46 |
if (getFile() == null) {
|
| trunk/org.gvsig.spatialite/org.gvsig.spatialite.provider/src/main/java/org/gvsig/spatialite/dal/operations/SpatiaLiteFetchFeatureTypeOperation.java | ||
|---|---|---|
| 11 | 11 |
import java.util.Map; |
| 12 | 12 |
import org.apache.commons.lang3.StringUtils; |
| 13 | 13 |
import org.cresques.cts.IProjection; |
| 14 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
|
| 14 | 15 |
import org.gvsig.fmap.dal.DataTypes; |
| 15 | 16 |
import org.gvsig.fmap.dal.exception.DataException; |
| 16 | 17 |
import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
| ... | ... | |
| 129 | 130 |
attr.setGeometryType(gt); |
| 130 | 131 |
|
| 131 | 132 |
JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder(); |
| 133 |
ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
| 134 |
|
|
| 132 | 135 |
sqlbuilder.select().column().name("geometry_type");
|
| 133 | 136 |
sqlbuilder.select().column().name("coord_dimension");
|
| 134 | 137 |
sqlbuilder.select().column().name("srid");
|
| 135 | 138 |
sqlbuilder.select().column().name("spatial_index_enabled");
|
| 136 | 139 |
sqlbuilder.select().where().set( |
| 137 |
sqlbuilder.eq(
|
|
| 138 |
sqlbuilder.column("f_table_name"),
|
|
| 139 |
sqlbuilder.constant(this.getTable().getTable().toLowerCase()) // geometry_columns always stores lowercase names
|
|
| 140 |
expbuilder.eq(
|
|
| 141 |
expbuilder.column("f_table_name"),
|
|
| 142 |
expbuilder.constant(this.getTable().getTable().toLowerCase()) // geometry_columns always stores lowercase names
|
|
| 140 | 143 |
) |
| 141 | 144 |
); |
| 142 | 145 |
sqlbuilder.select().where().and( |
| 143 |
sqlbuilder.eq(
|
|
| 144 |
sqlbuilder.column("f_geometry_column"),
|
|
| 145 |
sqlbuilder.constant(attr.getName().toLowerCase()) // geometry_columns always stores lowercase names
|
|
| 146 |
expbuilder.eq(
|
|
| 147 |
expbuilder.column("f_geometry_column"),
|
|
| 148 |
expbuilder.constant(attr.getName().toLowerCase()) // geometry_columns always stores lowercase names
|
|
| 146 | 149 |
) |
| 147 | 150 |
); |
| 148 | 151 |
sqlbuilder.select().from().table().name("geometry_columns");
|
| trunk/org.gvsig.spatialite/org.gvsig.spatialite.provider/src/main/java/org/gvsig/spatialite/dal/SpatiaLiteLibrary.java | ||
|---|---|---|
| 52 | 52 |
DataManagerProviderServices dataman = |
| 53 | 53 |
(DataManagerProviderServices) DALLocator.getDataManager(); |
| 54 | 54 |
|
| 55 |
try {
|
|
| 56 |
Class.forName(SpatiaLiteHelper.SPATIALITE_JDBC_DRIVER); |
|
| 57 |
} catch(Throwable th) {
|
|
| 58 |
SpatiaLiteHelper.LOGGER.warn("Can't load SpatiaLite JDBC Driver.",th);
|
|
| 59 |
} |
|
| 55 |
SpatiaLiteConnections.registerDriver(); |
|
| 56 |
// try {
|
|
| 57 |
// Class.forName(SpatiaLiteHelper.SPATIALITE_JDBC_DRIVER); |
|
| 58 |
// } catch(Throwable th) {
|
|
| 59 |
// SpatiaLiteHelper.LOGGER.warn("Can't load SpatiaLite JDBC Driver.",th);
|
|
| 60 |
// } |
|
| 60 | 61 |
|
| 61 | 62 |
DBHelper.registerParametersDefinition( |
| 62 | 63 |
NAME + "StoreParameters", |
| trunk/org.gvsig.spatialite/org.gvsig.spatialite.provider/src/main/java/org/gvsig/spatialite/dal/SpatiaLiteSQLBuilder.java | ||
|---|---|---|
| 13 | 13 |
import org.gvsig.fmap.geom.Geometry; |
| 14 | 14 |
import org.gvsig.tools.dispose.Disposable; |
| 15 | 15 |
import org.apache.commons.lang3.tuple.Pair; |
| 16 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters;
|
|
| 17 |
import org.gvsig.fmap.dal.SQLBuilder.Column;
|
|
| 16 |
import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter;
|
|
| 17 |
import org.gvsig.expressionevaluator.Formatter;
|
|
| 18 | 18 |
import org.gvsig.fmap.dal.SQLBuilder.ColumnDescriptor; |
| 19 |
import org.gvsig.spatialite.dal.expressionbuilderformatter.SpatiaLiteFormatter; |
|
| 19 | 20 |
|
| 21 |
@SuppressWarnings("UseSpecificCatch")
|
|
| 20 | 22 |
public class SpatiaLiteSQLBuilder extends JDBCSQLBuilderBase {
|
| 21 | 23 |
|
| 22 |
private SpatiaLiteSQLBuilder(SpatiaLiteHelper helper) {
|
|
| 24 |
protected Formatter formatter = null; |
|
| 25 |
|
|
| 26 |
public SpatiaLiteSQLBuilder(SpatiaLiteHelper helper) {
|
|
| 23 | 27 |
super(helper); |
| 24 |
|
|
| 28 |
|
|
| 25 | 29 |
// |
| 26 | 30 |
// SpatiaLite 4.3.0, SQL functions reference list |
| 27 | 31 |
// |
| ... | ... | |
| 29 | 33 |
// |
| 30 | 34 |
// https://sqlite.org/lang.html |
| 31 | 35 |
// |
| 32 |
|
|
| 33 |
config.set(SQLConfig.default_schema, ""); |
|
| 34 |
config.set(SQLConfig.support_schemas, false); |
|
| 35 |
config.set(SQLConfig.allowAutomaticValues, true); |
|
| 36 |
config.set(SQLConfig.geometry_type_support, this.helper.getGeometrySupportType()); |
|
| 37 |
config.set(SQLConfig.has_spatial_functions, this.helper.hasSpatialFunctions()); |
|
| 36 |
this.defaultSchema = ""; |
|
| 37 |
this.supportSchemas = false; |
|
| 38 |
this.allowAutomaticValues = true; |
|
| 39 |
this.geometrySupportType = this.helper.getGeometrySupportType(); |
|
| 40 |
this.hasSpatialFunctions = this.helper.hasSpatialFunctions(); |
|
| 38 | 41 |
|
| 39 |
config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table); |
|
| 40 |
config.remove_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table); |
|
| 41 |
|
|
| 42 |
config.set(SQLConfig.UPDATE_TABLE_STATISTICS_table,"SELECT UpdateLayerStatistics({0})");
|
|
| 42 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = null; |
|
| 43 |
this.STMT_DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = null; |
|
| 43 | 44 |
|
| 44 |
config.set(SQLConfig.ST_GeomFromEWKB, "GeomFromWKB({0}, {1})");
|
|
| 45 |
config.set(SQLConfig.ST_AsEWKB, "AsEWKB(({0}))");
|
|
| 46 |
config.set(SQLConfig.ST_ExtentAggregate, "Extent({0})");
|
|
| 47 |
config.set(SQLConfig.ST_UnionAggregate, "ST_Union({0})");
|
|
| 48 |
|
|
| 49 |
config.set(SQLConfig.lcase, "lower({0})");
|
|
| 50 |
config.set(SQLConfig.ucase, "upper({0})");
|
|
| 51 |
config.set(SQLConfig.operator_ILIKE, "lower({0}) LIKE lower({1})");
|
|
| 52 |
config.set(SQLConfig.isNull, "( ({0}) ISNULL )");
|
|
| 53 |
config.set(SQLConfig.notIsNull, "( ({0}) NOT NULL )");
|
|
| 45 |
this.STMT_UPDATE_TABLE_STATISTICS_table = "SELECT UpdateLayerStatistics({0})";
|
|
| 54 | 46 |
|
| 55 |
config.set(SQLConfig.type_boolean, "INTEGER");
|
|
| 56 |
config.set(SQLConfig.type_byte, "INTEGER");
|
|
| 57 |
config.set(SQLConfig.type_bytearray, "");
|
|
| 58 |
config.set(SQLConfig.type_geometry, "BLOB");
|
|
| 59 |
config.set(SQLConfig.type_char, "TEXT");
|
|
| 60 |
config.set(SQLConfig.type_date, "INTEGER");
|
|
| 61 |
config.set(SQLConfig.type_double, "REAL");
|
|
| 62 |
config.set(SQLConfig.type_numeric_p, "REAL");
|
|
| 63 |
config.set(SQLConfig.type_numeric_ps, "REAL");
|
|
| 64 |
config.set(SQLConfig.type_bigdecimal, "REAL");
|
|
| 65 |
config.set(SQLConfig.type_float, "REAL");
|
|
| 66 |
config.set(SQLConfig.type_int, "INTEGER");
|
|
| 67 |
config.set(SQLConfig.type_long, "INTEGER");
|
|
| 68 |
config.set(SQLConfig.type_string, "TEXT");
|
|
| 69 |
config.set(SQLConfig.type_string_p, "TEXT");
|
|
| 70 |
config.set(SQLConfig.type_time, "INTEGER");
|
|
| 71 |
config.set(SQLConfig.type_timestamp, "INTEGER");
|
|
| 72 |
config.set(SQLConfig.type_version, "TEXT");
|
|
| 73 |
config.set(SQLConfig.type_URI, "TEXT");
|
|
| 74 |
config.set(SQLConfig.type_URL, "TEXT");
|
|
| 75 |
config.set(SQLConfig.type_FILE, "TEXT");
|
|
| 76 |
config.set(SQLConfig.type_FOLDER, "TEXT");
|
|
| 47 |
this.type_boolean = "INTEGER";
|
|
| 48 |
this.type_byte = "INTEGER";
|
|
| 49 |
this.type_bytearray = "";
|
|
| 50 |
this.type_geometry = "BLOB";
|
|
| 51 |
this.type_char = "TEXT";
|
|
| 52 |
this.type_date = "INTEGER";
|
|
| 53 |
this.type_double = "REAL";
|
|
| 54 |
this.type_numeric_p = "REAL";
|
|
| 55 |
this.type_numeric_ps = "REAL";
|
|
| 56 |
this.type_bigdecimal = "REAL";
|
|
| 57 |
this.type_float = "REAL";
|
|
| 58 |
this.type_int = "INTEGER";
|
|
| 59 |
this.type_long = "INTEGER";
|
|
| 60 |
this.type_string = "TEXT";
|
|
| 61 |
this.type_string_p = "TEXT";
|
|
| 62 |
this.type_time = "INTEGER";
|
|
| 63 |
this.type_timestamp = "INTEGER";
|
|
| 64 |
this.type_version = "TEXT";
|
|
| 65 |
this.type_URI = "TEXT";
|
|
| 66 |
this.type_URL = "TEXT";
|
|
| 67 |
this.type_FILE = "TEXT";
|
|
| 68 |
this.type_FOLDER = "TEXT";
|
|
| 77 | 69 |
} |
| 78 | 70 |
|
| 71 |
@Override |
|
| 72 |
protected Formatter formatter() {
|
|
| 73 |
if (this.formatter == null) {
|
|
| 74 |
this.formatter = new SpatiaLiteFormatter(this); |
|
| 75 |
} |
|
| 76 |
return this.formatter; |
|
| 77 |
} |
|
| 78 |
|
|
| 79 | 79 |
public class SpatiaLiteTableNameBuilderBase extends TableNameBuilderBase {
|
| 80 | 80 |
|
| 81 | 81 |
@Override |
| ... | ... | |
| 87 | 87 |
public boolean has_database() {
|
| 88 | 88 |
return false; |
| 89 | 89 |
} |
| 90 |
|
|
| 90 |
|
|
| 91 | 91 |
} |
| 92 |
|
|
| 92 |
|
|
| 93 | 93 |
protected class SpatiaLiteCreateTableBuilder extends CreateTableBuilderBase {
|
| 94 | 94 |
|
| 95 | 95 |
@Override |
| 96 |
public List<String> toStrings() {
|
|
| 96 |
public List<String> toStrings(Formatter formatter) {
|
|
| 97 | 97 |
// |
| 98 | 98 |
// Respecto al base cambia la declaracion de campo automaticos: |
| 99 |
// - Los campos se crean autom?ticamente como SERIAL si son INTEGER PRIMARY KEY.
|
|
| 100 |
// - Existe la palabra clave AUTOINCREMENT que se puede usar
|
|
| 101 |
// en los campos INTEGER PRIMARY KEY, pero no se recomienda su uso (penaliza el rendimiento).
|
|
| 102 |
// - Spatialite no permite definir SERIALs para el resto de campos.
|
|
| 103 |
// - Hay que usar la funcion AddGeometryColumn para a?adir las columnas de tipo geometria
|
|
| 99 |
// - Los campos se crean autom?ticamente como SERIAL si son INTEGER PRIMARY KEY.
|
|
| 100 |
// - Existe la palabra clave AUTOINCREMENT que se puede usar
|
|
| 101 |
// en los campos INTEGER PRIMARY KEY, pero no se recomienda su uso (penaliza el rendimiento).
|
|
| 102 |
// - Spatialite no permite definir SERIALs para el resto de campos.
|
|
| 103 |
// - Hay que usar la funcion AddGeometryColumn para a?adir las columnas de tipo geometria
|
|
| 104 | 104 |
// - El orden en el que hay que declarar las constrains tambien cambia |
| 105 | 105 |
// respecto al que hay por defecto. |
| 106 | 106 |
// |
| ... | ... | |
| 108 | 108 |
StringBuilder builder = new StringBuilder(); |
| 109 | 109 |
|
| 110 | 110 |
builder.append("CREATE TABLE ");
|
| 111 |
builder.append(this.table.toString()); |
|
| 111 |
builder.append(this.table.toString(formatter));
|
|
| 112 | 112 |
builder.append(" (");
|
| 113 | 113 |
boolean first = true; |
| 114 | 114 |
for (ColumnDescriptor column : columns) {
|
| 115 |
if( column.isGeometry() ) {
|
|
| 115 |
if (column.isGeometry()) {
|
|
| 116 | 116 |
continue; |
| 117 | 117 |
} |
| 118 | 118 |
if (first) {
|
| ... | ... | |
| 120 | 120 |
} else {
|
| 121 | 121 |
builder.append(", ");
|
| 122 | 122 |
} |
| 123 |
builder.append(identifier(column.getName())); |
|
| 123 |
builder.append(as_identifier(column.getName()));
|
|
| 124 | 124 |
builder.append(" ");
|
| 125 | 125 |
builder.append(sqltype(column.getType(), column.getPrecision(), column.getSize(), column.getGeometryType(), column.getGeometrySubtype())); |
| 126 | 126 |
if (column.isPrimaryKey()) {
|
| ... | ... | |
| 131 | 131 |
builder.append(" DEFAULT NULL");
|
| 132 | 132 |
} |
| 133 | 133 |
} else {
|
| 134 |
if( column.getType() == DataTypes.DATE ) {
|
|
| 134 |
if (column.getType() == DataTypes.DATE) {
|
|
| 135 | 135 |
builder.append(" DEFAULT ( date('");
|
| 136 | 136 |
builder.append(column.getDefaultValue().toString()); |
| 137 | 137 |
builder.append("') )");
|
| ... | ... | |
| 150 | 150 |
|
| 151 | 151 |
String AddGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2,number,#######} , {3}, {4}, {5})";
|
| 152 | 152 |
for (ColumnDescriptor column : columns) {
|
| 153 |
if( column.isGeometry() ) {
|
|
| 153 |
if (column.isGeometry()) {
|
|
| 154 | 154 |
String sql = MessageFormat.format( |
| 155 |
AddGeometryColumn, |
|
| 156 |
this.table.toString(), |
|
| 157 |
identifier(column.getName()), |
|
| 158 |
column.getGeometrySRSId(), |
|
| 159 |
constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())),
|
|
| 160 |
constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())),
|
|
| 161 |
column.allowNulls()? 0:1
|
|
| 155 |
AddGeometryColumn,
|
|
| 156 |
this.table.toString(),
|
|
| 157 |
as_identifier(column.getName()),
|
|
| 158 |
column.getGeometrySRSId(),
|
|
| 159 |
as_string(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())),
|
|
| 160 |
as_string(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())),
|
|
| 161 |
column.allowNulls() ? 0 : 1
|
|
| 162 | 162 |
); |
| 163 | 163 |
sqls.add(sql); |
| 164 | 164 |
} |
| ... | ... | |
| 168 | 168 |
} |
| 169 | 169 |
|
| 170 | 170 |
public class SpatiaLiteSelectBuilderBase extends SelectBuilderBase {
|
| 171 |
|
|
| 171 |
|
|
| 172 | 172 |
@Override |
| 173 | 173 |
protected boolean isValid(StringBuilder message) {
|
| 174 |
if( message == null ) {
|
|
| 174 |
if (message == null) {
|
|
| 175 | 175 |
message = new StringBuilder(); |
| 176 | 176 |
} |
| 177 |
if( this.has_offset() && !this.has_order_by() ) {
|
|
| 177 |
if (this.has_offset() && !this.has_order_by()) {
|
|
| 178 | 178 |
// Algunos gestores de BBDD requieren que se especifique un |
| 179 | 179 |
// orden para poder usar OFFSET. Como eso parece buena idea para |
| 180 | 180 |
// asegurar que siempre tengamos los mismo resultados, lo exijimos |
| ... | ... | |
| 183 | 183 |
return false; |
| 184 | 184 |
} |
| 185 | 185 |
return true; |
| 186 |
}
|
|
| 187 |
|
|
| 186 |
} |
|
| 187 |
|
|
| 188 | 188 |
@Override |
| 189 |
public String toString() {
|
|
| 189 |
public String toString(Formatter formatter) {
|
|
| 190 | 190 |
// SpatiaLite requiere que si se especifica OFFSET deba especificarse |
| 191 | 191 |
// LIMIT obligatoriamente. Se le puede poner un LIMIT -1 cuando |
| 192 | 192 |
// queramos un OFFSET sin especificar un LIMIT. |
| 193 | 193 |
StringBuilder builder = new StringBuilder(); |
| 194 |
if( !isValid(builder) ) {
|
|
| 194 |
if (!isValid(builder)) {
|
|
| 195 | 195 |
throw new IllegalStateException(builder.toString()); |
| 196 | 196 |
} |
| 197 | 197 |
builder.append("SELECT ");
|
| 198 |
if( this.distinct ) {
|
|
| 198 |
if (this.distinct) {
|
|
| 199 | 199 |
builder.append("DISTINCT ");
|
| 200 | 200 |
} |
| 201 | 201 |
boolean first = true; |
| ... | ... | |
| 205 | 205 |
} else {
|
| 206 | 206 |
builder.append(", ");
|
| 207 | 207 |
} |
| 208 |
builder.append(column.toString()); |
|
| 208 |
builder.append(column.toString(formatter));
|
|
| 209 | 209 |
} |
| 210 | 210 |
|
| 211 |
if ( this.has_from() ) {
|
|
| 211 |
if (this.has_from()) {
|
|
| 212 | 212 |
builder.append(" FROM ");
|
| 213 |
builder.append(this.from.toString()); |
|
| 213 |
builder.append(this.from.toString(formatter));
|
|
| 214 | 214 |
} |
| 215 |
if ( this.has_where() ) {
|
|
| 215 |
if (this.has_where()) {
|
|
| 216 | 216 |
builder.append(" WHERE ");
|
| 217 |
builder.append(this.where.toString()); |
|
| 217 |
builder.append(this.where.toString(formatter));
|
|
| 218 | 218 |
} |
| 219 |
|
|
| 220 |
if( this.has_order_by() ) {
|
|
| 219 |
|
|
| 220 |
if (this.has_order_by()) {
|
|
| 221 | 221 |
builder.append(" ORDER BY ");
|
| 222 | 222 |
first = true; |
| 223 | 223 |
for (OrderByBuilder item : this.order_by) {
|
| ... | ... | |
| 226 | 226 |
} else {
|
| 227 | 227 |
builder.append(", ");
|
| 228 | 228 |
} |
| 229 |
builder.append(item.toString());
|
|
| 230 |
}
|
|
| 229 |
builder.append(item.toString(formatter));
|
|
| 230 |
} |
|
| 231 | 231 |
} |
| 232 |
|
|
| 233 |
if ( this.has_limit() && this.has_offset() ) {
|
|
| 232 |
|
|
| 233 |
if (this.has_limit() && this.has_offset()) {
|
|
| 234 | 234 |
builder.append(" LIMIT ");
|
| 235 | 235 |
builder.append(this.limit); |
| 236 | 236 |
builder.append(" OFFSET ");
|
| 237 | 237 |
builder.append(this.offset); |
| 238 |
|
|
| 239 |
} else if ( this.has_limit()) {
|
|
| 238 |
|
|
| 239 |
} else if (this.has_limit()) {
|
|
| 240 | 240 |
builder.append(" LIMIT ");
|
| 241 | 241 |
builder.append(this.limit); |
| 242 | 242 |
|
| 243 |
} else if ( this.has_offset() ) {
|
|
| 243 |
} else if (this.has_offset()) {
|
|
| 244 | 244 |
builder.append(" LIMIT -1 OFFSET ");
|
| 245 |
builder.append(this.offset);
|
|
| 245 |
builder.append(this.offset); |
|
| 246 | 246 |
} |
| 247 | 247 |
return builder.toString(); |
| 248 | 248 |
|
| ... | ... | |
| 250 | 250 |
} |
| 251 | 251 |
|
| 252 | 252 |
@Override |
| 253 |
public String bytearray(byte[] data) {
|
|
| 254 |
// SpatiaLite usa un formato diferencte para especificar un array de |
|
| 255 |
// bytes. En lugar de 0x... usa x'...' . |
|
| 256 |
StringBuilder builder = new StringBuilder(); |
|
| 257 |
builder.append("x'");
|
|
| 258 |
for (byte abyte : data) {
|
|
| 259 |
int v = abyte & 0xff; |
|
| 260 |
builder.append(String.format("%02x", v));
|
|
| 261 |
} |
|
| 262 |
builder.append("'");
|
|
| 263 |
return builder.toString(); |
|
| 264 |
} |
|
| 265 |
|
|
| 266 |
@Override |
|
| 267 | 253 |
public Object sqlgeometrydimension(int type, int subtype) {
|
| 268 | 254 |
//'XY' or 2: 2D points, identified by X and Y coordinates |
| 269 | 255 |
//'XYM': 2D points, identified by X and Y coordinates. Each point stores an M-value (measure) as well |
| 270 | 256 |
//'XYZ' or 3: 3D points, identified by X, Y and Z coordinates |
| 271 | 257 |
//'XYZM' or 4: 3D points, identified by X, Y and Z coordinates. Each point stores an M-value (measure) as well |
| 272 |
switch(subtype) {
|
|
| 258 |
switch (subtype) {
|
|
| 273 | 259 |
case Geometry.SUBTYPES.GEOM2D: |
| 274 | 260 |
default: |
| 275 | 261 |
return "XY"; |
| ... | ... | |
| 282 | 268 |
} |
| 283 | 269 |
} |
| 284 | 270 |
|
| 271 |
@Override |
|
| 285 | 272 |
public SpatiaLiteHelper getHelper() {
|
| 286 | 273 |
return (SpatiaLiteHelper) this.helper; |
| 287 | 274 |
} |
| 288 |
|
|
| 275 |
|
|
| 289 | 276 |
@Override |
| 290 | 277 |
public Disposable setParameters(PreparedStatement st, FeatureProvider feature) {
|
| 291 | 278 |
try {
|
| 292 | 279 |
FeatureType featureType = feature.getType(); |
| 293 | 280 |
List<Object> values = new ArrayList<>(); |
| 294 |
for (Parameter parameter : this.getParameters()) {
|
|
| 281 |
for (Parameter parameter : this.parameters()) {
|
|
| 295 | 282 |
if (parameter.is_constant()) {
|
| 296 |
values.add(parameter.getValue());
|
|
| 283 |
values.add(parameter.value());
|
|
| 297 | 284 |
} else {
|
| 298 |
String name = parameter.getName();
|
|
| 285 |
String name = parameter.name();
|
|
| 299 | 286 |
FeatureAttributeDescriptor descriptor = featureType.getAttributeDescriptor(name); |
| 300 |
switch( descriptor.getType() ) {
|
|
| 301 |
case DataTypes.DATE: |
|
| 302 |
Date value = (Date)(feature.get(name));
|
|
| 303 |
if( value == null ) {
|
|
| 304 |
values.add(null); |
|
| 305 |
} else {
|
|
| 306 |
values.add(value.getTime()); |
|
| 307 |
} |
|
| 308 |
break; |
|
| 309 |
case DataTypes.GEOMETRY: |
|
| 310 |
Geometry geom = this.getHelper().forceGeometryType( |
|
| 311 |
descriptor.getGeomType(), |
|
| 312 |
(Geometry)(feature.get(name))
|
|
| 313 |
); |
|
| 314 |
values.add(geom); |
|
| 315 |
break; |
|
| 316 |
default: |
|
| 317 |
values.add(feature.get(name)); |
|
| 318 |
break; |
|
| 287 |
switch (descriptor.getType()) {
|
|
| 288 |
case DataTypes.DATE:
|
|
| 289 |
Date value = (Date) (feature.get(name));
|
|
| 290 |
if (value == null) {
|
|
| 291 |
values.add(null);
|
|
| 292 |
} else {
|
|
| 293 |
values.add(value.getTime());
|
|
| 294 |
}
|
|
| 295 |
break;
|
|
| 296 |
case DataTypes.GEOMETRY:
|
|
| 297 |
Geometry geom = this.getHelper().forceGeometryType(
|
|
| 298 |
descriptor.getGeomType(),
|
|
| 299 |
(Geometry) (feature.get(name))
|
|
| 300 |
);
|
|
| 301 |
values.add(geom);
|
|
| 302 |
break;
|
|
| 303 |
default:
|
|
| 304 |
values.add(feature.get(name));
|
|
| 305 |
break;
|
|
| 319 | 306 |
} |
| 320 | 307 |
} |
| 321 | 308 |
} |
| ... | ... | |
| 332 | 319 |
} |
| 333 | 320 |
|
| 334 | 321 |
public class SpatiaLiteCreateIndexBuilder extends CreateIndexBuilderBase {
|
| 335 |
|
|
| 322 |
|
|
| 336 | 323 |
@Override |
| 337 |
public List<String> toStrings() {
|
|
| 324 |
public List<String> toStrings(Formatter formatter) {
|
|
| 338 | 325 |
List<String> sqls = new ArrayList<>(); |
| 339 |
if( this.isSpatial && this.columns.size()==1) {
|
|
| 326 |
if (this.isSpatial && this.columns.size() == 1) {
|
|
| 340 | 327 |
StringBuilder builder = new StringBuilder(); |
| 341 | 328 |
builder.append("SELECT CreateSpatialIndex('");
|
| 342 | 329 |
builder.append(this.table().getName()); |
| ... | ... | |
| 347 | 334 |
} else {
|
| 348 | 335 |
StringBuilder builder = new StringBuilder(); |
| 349 | 336 |
builder.append("CREATE ");
|
| 350 |
if( this.isUnique ) {
|
|
| 337 |
if (this.isUnique) {
|
|
| 351 | 338 |
builder.append("UNIQUE ");
|
| 352 | 339 |
} |
| 353 | 340 |
builder.append("INDEX ");
|
| 354 |
if( this.ifNotExist ) {
|
|
| 341 |
if (this.ifNotExist) {
|
|
| 355 | 342 |
builder.append("IF NOT EXISTS ");
|
| 356 | 343 |
} |
| 357 |
builder.append(identifier(this.indexName)); |
|
| 344 |
builder.append(as_identifier(this.indexName));
|
|
| 358 | 345 |
builder.append(" ON ");
|
| 359 |
builder.append(this.table.toString()); |
|
| 346 |
builder.append(this.table.toString(formatter));
|
|
| 360 | 347 |
builder.append(" ( ");
|
| 361 | 348 |
boolean is_first_column = true; |
| 362 |
for( String column : this.columns) {
|
|
| 363 |
if( is_first_column ) {
|
|
| 349 |
for (String column : this.columns) {
|
|
| 350 |
if (is_first_column) {
|
|
| 364 | 351 |
is_first_column = false; |
| 365 | 352 |
} else {
|
| 366 | 353 |
builder.append(", ");
|
| ... | ... | |
| 373 | 360 |
return sqls; |
| 374 | 361 |
} |
| 375 | 362 |
} |
| 376 |
|
|
| 363 |
|
|
| 377 | 364 |
@Override |
| 378 | 365 |
protected TableNameBuilder createTableNameBuilder() {
|
| 379 | 366 |
return new SpatiaLiteTableNameBuilderBase(); |
| 380 | 367 |
} |
| 381 |
|
|
| 368 |
|
|
| 382 | 369 |
@Override |
| 383 | 370 |
protected CreateTableBuilder createCreateTableBuilder() {
|
| 384 | 371 |
return new SpatiaLiteCreateTableBuilder(); |
| ... | ... | |
| 388 | 375 |
protected SelectBuilder createSelectBuilder() {
|
| 389 | 376 |
return new SpatiaLiteSelectBuilderBase(); |
| 390 | 377 |
} |
| 391 |
|
|
| 378 |
|
|
| 392 | 379 |
@Override |
| 393 | 380 |
protected CreateIndexBuilder createCreateIndexBuilder() {
|
| 394 | 381 |
return new SpatiaLiteCreateIndexBuilder(); |
| 395 | 382 |
} |
| 396 |
|
|
| 383 |
|
|
| 397 | 384 |
@Override |
| 398 | 385 |
protected AlterTableBuilder createAlterTableBuilder() {
|
| 399 |
return new SpatialiteAlterTableBuilder();
|
|
| 386 |
return new SpatialiteAlterTableBuilder();
|
|
| 400 | 387 |
} |
| 401 | 388 |
|
| 402 | 389 |
public class SpatialiteAlterTableBuilder extends AlterTableBuilderBase {
|
| 403 | 390 |
|
| 404 |
@Override
|
|
| 405 |
public List<String> toStrings() {
|
|
| 406 |
// Notes:
|
|
| 407 |
// - Alter column is not supported in SQLite
|
|
| 408 |
// - Fields are automatically created as serial if they are INTEGER PRIMARY KEY
|
|
| 409 |
// - It is not possible to define serial fields for other columns
|
|
| 410 |
List<String> sqls = new ArrayList<>();
|
|
| 411 |
if( this.isEmpty() ) {
|
|
| 412 |
return sqls;
|
|
| 413 |
}
|
|
| 414 |
for (String column : drops) {
|
|
| 415 |
StringBuilder builder = new StringBuilder();
|
|
| 416 |
builder.append("ALTER TABLE ");
|
|
| 417 |
builder.append(this.table.toString());
|
|
| 418 |
builder.append(" DROP COLUMN IF EXISTS ");
|
|
| 419 |
builder.append(identifier(column));
|
|
| 420 |
sqls.add(builder.toString());
|
|
| 421 |
}
|
|
| 391 |
@Override
|
|
| 392 |
public List<String> toStrings(Formatter formatter) {
|
|
| 393 |
// Notes:
|
|
| 394 |
// - Alter column is not supported in SQLite
|
|
| 395 |
// - Fields are automatically created as serial if they are INTEGER PRIMARY KEY
|
|
| 396 |
// - It is not possible to define serial fields for other columns
|
|
| 397 |
List<String> sqls = new ArrayList<>();
|
|
| 398 |
if (this.isEmpty()) {
|
|
| 399 |
return sqls;
|
|
| 400 |
}
|
|
| 401 |
for (String column : drops) {
|
|
| 402 |
StringBuilder builder = new StringBuilder();
|
|
| 403 |
builder.append("ALTER TABLE ");
|
|
| 404 |
builder.append(this.table.toString(formatter));
|
|
| 405 |
builder.append(" DROP COLUMN IF EXISTS ");
|
|
| 406 |
builder.append(as_identifier(column));
|
|
| 407 |
sqls.add(builder.toString());
|
|
| 408 |
}
|
|
| 422 | 409 |
|
| 423 |
for (ColumnDescriptor column : adds) {
|
|
| 424 |
if (column.isGeometry()) {
|
|
| 425 |
String addGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2,number,#######}, {3}, {4}, {5})";
|
|
| 426 |
String sql = MessageFormat.format( |
|
| 427 |
addGeometryColumn, |
|
| 428 |
this.table.toString(), |
|
| 429 |
identifier(column.getName()), |
|
| 430 |
column.getGeometrySRSId(), |
|
| 431 |
constant(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())), |
|
| 432 |
constant(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())), |
|
| 433 |
column.allowNulls()? 0:1 |
|
| 434 |
); |
|
| 435 |
sqls.add(sql); |
|
| 436 |
} |
|
| 437 |
else {
|
|
| 438 |
StringBuilder builder = new StringBuilder(); |
|
| 439 |
builder.append("ALTER TABLE ");
|
|
| 440 |
builder.append(this.table.toString()); |
|
| 441 |
builder.append(" ADD COLUMN ");
|
|
| 442 |
builder.append(identifier(column.getName())); |
|
| 443 |
builder.append(" ");
|
|
| 444 |
builder.append( |
|
| 445 |
sqltype( |
|
| 446 |
column.getType(), |
|
| 447 |
column.getPrecision(), |
|
| 448 |
column.getSize(), |
|
| 449 |
column.getGeometryType(), |
|
| 450 |
column.getGeometrySubtype() |
|
| 451 |
) |
|
| 452 |
); |
|
| 453 |
if (column.getDefaultValue() == null) {
|
|
| 454 |
if (column.allowNulls()) {
|
|
| 455 |
builder.append(" DEFAULT NULL");
|
|
| 456 |
} |
|
| 457 |
} else {
|
|
| 458 |
builder.append(" DEFAULT '");
|
|
| 459 |
builder.append(column.getDefaultValue().toString()); |
|
| 460 |
builder.append("'");
|
|
| 461 |
} |
|
| 462 |
if (column.allowNulls()) {
|
|
| 463 |
builder.append(" NULL");
|
|
| 464 |
} else {
|
|
| 465 |
builder.append(" NOT NULL");
|
|
| 466 |
} |
|
| 467 |
if (column.isPrimaryKey()) {
|
|
| 468 |
builder.append(" PRIMARY KEY");
|
|
| 469 |
} |
|
| 470 |
sqls.add(builder.toString()); |
|
| 471 |
} |
|
| 472 |
} |
|
| 473 |
for (Pair<String,String> pair : renames) {
|
|
| 474 |
StringBuilder builder = new StringBuilder(); |
|
| 475 |
builder.append("ALTER TABLE ");
|
|
| 476 |
builder.append(this.table.toString()); |
|
| 477 |
builder.append(" RENAME COLUMN ");
|
|
| 478 |
builder.append(identifier(pair.getLeft())); |
|
| 479 |
builder.append(" TO ");
|
|
| 480 |
builder.append(identifier(pair.getRight())); |
|
| 481 |
sqls.add(builder.toString()); |
|
| 482 |
} |
|
| 483 |
return sqls; |
|
| 484 |
} |
|
| 485 |
} |
|
| 486 |
|
|
| 487 |
private ColumnDescriptor getDescriptorForUseInIndex(Value arg) {
|
|
| 488 |
if( arg instanceof Column ) {
|
|
| 489 |
ColumnDescriptor descriptor = ((Column)arg).getDescriptor(); |
|
| 490 |
if( descriptor.getType()==DataTypes.GEOMETRY && descriptor.isIndexed() ) {
|
|
| 491 |
return descriptor; |
|
| 492 |
} |
|
| 493 |
} |
|
| 494 |
// Aqui probablemente estaria bien comprobar si el argumento es la funcion |
|
| 495 |
// ST_Envelope sobre un campo geometria y tambien usar el indice espacial |
|
| 496 |
// para cubrir el caso que se use algo como: |
|
| 497 |
// ST_Intersects(x, ST_Envelope("geom"))
|
|
| 498 |
if( arg instanceof Function ) {
|
|
| 499 |
if( ((Function) arg).getName().equalsIgnoreCase(config.getString(SQLConfig.ST_Envelope)) ) {
|
|
| 500 |
Value arg2 = ((Function) arg).parameters().get(0); |
|
| 501 |
if( arg2 instanceof Column ) {
|
|
| 502 |
ColumnDescriptor descriptor = ((Column)arg2).getDescriptor(); |
|
| 503 |
if( descriptor.getType()==DataTypes.GEOMETRY && descriptor.isIndexed() ) {
|
|
| 504 |
return descriptor; |
|
| 410 |
for (ColumnDescriptor column : adds) {
|
|
| 411 |
if (column.isGeometry()) {
|
|
| 412 |
String addGeometryColumn = "SELECT AddGeometryColumn({0} , {1} , {2,number,#######}, {3}, {4}, {5})";
|
|
| 413 |
String sql = MessageFormat.format( |
|
| 414 |
addGeometryColumn, |
|
| 415 |
this.table.toString(formatter), |
|
| 416 |
as_identifier(column.getName()), |
|
| 417 |
column.getGeometrySRSId(), |
|
| 418 |
as_string(sqlgeometrytype(column.getGeometryType(), column.getGeometrySubtype())), |
|
| 419 |
as_string(sqlgeometrydimension(column.getGeometryType(), column.getGeometrySubtype())), |
|
| 420 |
column.allowNulls() ? 0 : 1 |
|
| 421 |
); |
|
| 422 |
sqls.add(sql); |
|
| 423 |
} else {
|
|
| 424 |
StringBuilder builder = new StringBuilder(); |
|
| 425 |
builder.append("ALTER TABLE ");
|
|
| 426 |
builder.append(this.table.toString(formatter)); |
|
| 427 |
builder.append(" ADD COLUMN ");
|
|
| 428 |
builder.append(as_identifier(column.getName())); |
|
| 429 |
builder.append(" ");
|
|
| 430 |
builder.append( |
|
| 431 |
sqltype( |
|
| 432 |
column.getType(), |
|
| 433 |
column.getPrecision(), |
|
| 434 |
column.getSize(), |
|
| 435 |
column.getGeometryType(), |
|
| 436 |
column.getGeometrySubtype() |
|
| 437 |
) |
|
| 438 |
); |
|
| 439 |
if (column.getDefaultValue() == null) {
|
|
| 440 |
if (column.allowNulls()) {
|
|
| 441 |
builder.append(" DEFAULT NULL");
|
|
| 442 |
} |
|
| 443 |
} else {
|
|
| 444 |
builder.append(" DEFAULT '");
|
|
| 445 |
builder.append(column.getDefaultValue().toString()); |
|
| 446 |
builder.append("'");
|
|
| 505 | 447 |
} |
| 448 |
if (column.allowNulls()) {
|
|
| 449 |
builder.append(" NULL");
|
|
| 450 |
} else {
|
|
| 451 |
builder.append(" NOT NULL");
|
|
| 452 |
} |
|
| 453 |
if (column.isPrimaryKey()) {
|
|
| 454 |
builder.append(" PRIMARY KEY");
|
|
| 455 |
} |
|
| 456 |
sqls.add(builder.toString()); |
|
| 506 | 457 |
} |
| 507 | 458 |
} |
| 508 |
}
|
|
| 509 |
return null;
|
|
| 510 |
}
|
|
| 511 |
|
|
| 512 |
private ColumnDescriptor getDescriptorForUseInIndex(Value... args) {
|
|
| 513 |
for (Value arg : args) {
|
|
| 514 |
ColumnDescriptor descriptor = getDescriptorForUseInIndex(arg);
|
|
| 515 |
if( descriptor != null ) {
|
|
| 516 |
return descriptor;
|
|
| 459 |
for (Pair<String, String> pair : renames) {
|
|
| 460 |
StringBuilder builder = new StringBuilder();
|
|
| 461 |
builder.append("ALTER TABLE ");
|
|
| 462 |
builder.append(this.table.toString()); |
|
| 463 |
builder.append(" RENAME COLUMN ");
|
|
| 464 |
builder.append(as_identifier(pair.getLeft()));
|
|
| 465 |
builder.append(" TO ");
|
|
| 466 |
builder.append(as_identifier(pair.getRight()));
|
|
| 467 |
sqls.add(builder.toString());
|
|
| 517 | 468 |
} |
| 469 |
return sqls; |
|
| 518 | 470 |
} |
| 519 |
return null; |
|
| 520 | 471 |
} |
| 521 |
|
|
| 522 |
@Override |
|
| 523 |
public Function ST_Intersects(Value geom1, Value geom2) {
|
|
| 524 |
// return super.ST_Intersects(geom1, geom2); |
|
| 525 |
JDBCStoreParameters jdbcparams = null; |
|
| 526 |
ColumnDescriptor descriptor = getDescriptorForUseInIndex(geom1, geom2); |
|
| 527 |
|
|
| 528 |
if( descriptor != null ) {
|
|
| 529 |
jdbcparams = (JDBCStoreParameters) descriptor.getStoreParameters(); |
|
| 530 |
} |
|
| 531 |
Function f; |
|
| 532 |
if( jdbcparams!=null ) {
|
|
| 533 |
// Usamos indices espaciales |
|
| 534 |
f = builtin_function( |
|
| 535 |
"ST_Intersects", |
|
| 536 |
"(ST_Intersects({0},{1}) AND ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = ''"+jdbcparams.getTable()+"'' AND f_geometry_column = ''"+descriptor.getName()+"'' AND search_frame = \""+jdbcparams.getTable()+"\".\""+descriptor.getName()+"\"))",
|
|
| 537 |
geom1, |
|
| 538 |
geom2 |
|
| 539 |
); |
|
| 540 |
} else {
|
|
| 541 |
f = super.ST_Intersects(geom1, geom2); |
|
| 542 |
} |
|
| 543 |
return f; |
|
| 544 |
} |
|
| 545 | 472 |
} |
| trunk/org.gvsig.spatialite/org.gvsig.spatialite.provider/src/main/java/org/gvsig/spatialite/dal/expressionbuilderformatter/ST_Intersects.java | ||
|---|---|---|
| 1 |
package org.gvsig.spatialite.dal.expressionbuilderformatter; |
|
| 2 |
|
|
| 3 |
import java.text.MessageFormat; |
|
| 4 |
import java.util.List; |
|
| 5 |
import org.apache.commons.lang3.StringUtils; |
|
| 6 |
import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_ST_ENVELOPE; |
|
| 7 |
import static org.gvsig.expressionevaluator.ExpressionBuilder.FUNCTION_ST_INTERSECTS; |
|
| 8 |
import org.gvsig.expressionevaluator.ExpressionBuilder.Function; |
|
| 9 |
import org.gvsig.expressionevaluator.ExpressionBuilder.Value; |
|
| 10 |
import org.gvsig.expressionevaluator.ExpressionBuilder.Variable; |
|
| 11 |
import org.gvsig.expressionevaluator.Formatter; |
|
| 12 |
import org.gvsig.fmap.dal.SQLBuilder; |
|
| 13 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
|
| 14 |
import org.gvsig.fmap.dal.feature.FeatureType; |
|
| 15 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
|
| 16 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
|
| 17 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
|
| 18 |
|
|
| 19 |
/** |
|
| 20 |
* |
|
| 21 |
* @author jjdelcerro |
|
| 22 |
*/ |
|
| 23 |
class ST_Intersects implements Formatter<Value> {
|
|
| 24 |
|
|
| 25 |
private final Formatter<Value> formatter; |
|
| 26 |
private final SQLBuilder builder; |
|
| 27 |
private String column; |
|
| 28 |
private String table; |
|
| 29 |
|
|
| 30 |
public ST_Intersects(SQLBuilder builder, Formatter<Value> formatter) {
|
|
| 31 |
this.builder = builder; |
|
| 32 |
this.formatter = formatter; |
|
| 33 |
} |
|
| 34 |
|
|
| 35 |
@Override |
|
| 36 |
public boolean canApply(Value value) {
|
|
| 37 |
if (value instanceof Function) {
|
|
| 38 |
return StringUtils.equalsIgnoreCase(FUNCTION_ST_INTERSECTS, ((Function) value).name()); |
|
| 39 |
} |
|
| 40 |
return false; |
|
| 41 |
} |
|
| 42 |
|
|
| 43 |
@Override |
|
| 44 |
public String format(Value function) {
|
|
| 45 |
String r ; |
|
| 46 |
List<Value> parameters = ((Function) function).parameters(); |
|
| 47 |
Value geom1 = parameters.get(0); |
|
| 48 |
Value geom2 = parameters.get(1); |
|
| 49 |
if( this.useSpatialIndex(geom1, geom2) ) {
|
|
| 50 |
r = MessageFormat.format( |
|
| 51 |
"(ST_Intersects({0},{1}) AND ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = ''{2}'' AND f_geometry_column = ''{3}'' AND search_frame = \"{2}\".\"{3}\"))",
|
|
| 52 |
geom1.toString(this.formatter), |
|
| 53 |
geom2.toString(this.formatter), |
|
| 54 |
this.table, |
|
| 55 |
this.column |
|
| 56 |
); |
|
| 57 |
} else {
|
|
| 58 |
r = MessageFormat.format( |
|
| 59 |
"(ST_Intersects({0},{1}))",
|
|
| 60 |
geom1.toString(this.formatter), |
|
| 61 |
geom2.toString(this.formatter) |
|
| 62 |
); |
|
| 63 |
} |
|
| 64 |
return r; |
|
| 65 |
} |
|
| 66 |
|
|
| 67 |
private boolean useSpatialIndex(Value... values) {
|
|
| 68 |
for (Value value : values) {
|
|
| 69 |
if( value instanceof Variable ) {
|
|
| 70 |
if( useSpatialIndex((Variable)value) ) {
|
|
| 71 |
return true; |
|
| 72 |
} |
|
| 73 |
} |
|
| 74 |
// Tambien comprobamos si el argumento es la funcion |
|
| 75 |
// ST_Envelope sobre un campo geometria, si es asi tambien usaremos el |
|
| 76 |
// indice espacial, cubriendo el caso de que se use algo como: |
|
| 77 |
// ST_Intersects(x, ST_Envelope("geom"))
|
|
| 78 |
if( value instanceof Function ) {
|
|
| 79 |
if( ((Function) value).name().equalsIgnoreCase(FUNCTION_ST_ENVELOPE) ) {
|
|
| 80 |
Value value2 = ((Function) value).parameters().get(0); |
|
| 81 |
if( value2 instanceof Variable ) {
|
|
| 82 |
if( useSpatialIndex((Variable)value2) ) {
|
|
| 83 |
return true; |
|
| 84 |
} |
|
| 85 |
} |
|
Also available in: Unified diff