Revision 46
| trunk/org.gvsig.postgresql/org.gvsig.postgresql.provider/src/main/java/org/gvsig/fmap/dal/store/postgresql/PostgreSQLHelper.java | ||
|---|---|---|
| 30 | 30 |
*/ |
| 31 | 31 |
package org.gvsig.fmap.dal.store.postgresql; |
| 32 | 32 |
|
| 33 |
import java.io.File; |
|
| 34 |
import java.io.InputStream; |
|
| 35 |
import java.net.URL; |
|
| 33 | 36 |
import java.sql.Connection; |
| 34 | 37 |
import java.sql.PreparedStatement; |
| 35 | 38 |
import java.sql.ResultSet; |
| ... | ... | |
| 41 | 44 |
import java.util.Iterator; |
| 42 | 45 |
import java.util.List; |
| 43 | 46 |
import java.util.Map; |
| 47 |
import java.util.Properties; |
|
| 44 | 48 |
import java.util.TreeMap; |
| 45 | 49 |
import java.util.TreeSet; |
| 46 | 50 |
|
| ... | ... | |
| 97 | 101 |
|
| 98 | 102 |
private Map pgSR2SRSID = new TreeMap(); |
| 99 | 103 |
private Map srsID2pgSR = new TreeMap(); |
| 104 |
|
|
| 105 |
private static Properties beforePostgis13 = null; |
|
| 106 |
private int[] postGISVersion = { 0,0,0 };
|
|
| 107 |
private boolean versionSet = false; |
|
| 100 | 108 |
|
| 101 |
|
|
| 102 | 109 |
PostgreSQLHelper(JDBCHelperUser consumer, |
| 103 | 110 |
PostgreSQLConnectionParameters params) |
| 104 | 111 |
throws InitializeException {
|
| ... | ... | |
| 157 | 164 |
throws DataException {
|
| 158 | 165 |
|
| 159 | 166 |
StringBuilder strb = new StringBuilder(); |
| 160 |
strb.append("Select ST_asbinary(ST_extent(");
|
|
| 167 |
strb.append("Select " + getFunctionName("ST_AsBinary") + "(" +
|
|
| 168 |
getFunctionName("ST_Extent") + "(");
|
|
| 161 | 169 |
strb.append(escapeFieldName(geometryAttrName)); |
| 162 | 170 |
strb.append(")) from ");
|
| 163 | 171 |
|
| ... | ... | |
| 171 | 179 |
} |
| 172 | 180 |
|
| 173 | 181 |
if (limit != null){
|
| 174 |
strb.append(" where ST_intersects(ST_GeomFromText('");
|
|
| 182 |
strb.append(" where " + getFunctionName("ST_Intersects") + "("
|
|
| 183 |
+ getFunctionName("ST_GeomFromText") + "('");
|
|
| 175 | 184 |
String workAreaWkt = null; |
| 176 | 185 |
try {
|
| 177 | 186 |
workAreaWkt = limit.getGeometry().convertToWKT(); |
| ... | ... | |
| 189 | 198 |
} else {
|
| 190 | 199 |
strb.append(Integer.toString(sridInt)); |
| 191 | 200 |
} |
| 192 |
strb.append("), ST_envelope(");
|
|
| 201 |
strb.append("), " + getFunctionName("ST_Envelope") + "(");
|
|
| 193 | 202 |
strb.append(escapeFieldName(geometryAttrName)); |
| 194 | 203 |
strb.append(")) ");
|
| 195 | 204 |
} |
| 196 | 205 |
|
| 197 | 206 |
final String sql = strb.toString(); |
| 198 | 207 |
|
| 199 |
GeometryManager geoMan = GeometryLocator.getGeometryManager(); |
|
| 200 |
|
|
| 201 | 208 |
this.open(); |
| 202 | 209 |
|
| 203 | 210 |
return (Envelope) getResource().execute(new ResourceAction() {
|
| 204 | 211 |
public Object run() throws Exception {
|
| 205 | 212 |
ResultSet rs = null; |
| 206 | 213 |
Statement st = null; |
| 207 |
String schema = null; |
|
| 208 | 214 |
Connection conn = null; |
| 209 | 215 |
Envelope fullEnvelope = null; |
| 210 | 216 |
|
| ... | ... | |
| 771 | 777 |
|
| 772 | 778 |
public String getSqlFieldName(FeatureAttributeDescriptor attribute) {
|
| 773 | 779 |
if (attribute.getType() == DataTypes.GEOMETRY) {
|
| 774 |
return "ST_AsBinary(" + super.getSqlFieldName(attribute) + ")";
|
|
| 780 |
return getFunctionName("ST_AsBinary") + "("
|
|
| 781 |
+ super.getSqlFieldName(attribute) + ")"; |
|
| 775 | 782 |
} |
| 776 | 783 |
return super.getSqlFieldName(attribute); |
| 777 | 784 |
} |
| ... | ... | |
| 874 | 881 |
|
| 875 | 882 |
return super.dalValueToJDBC(attributeDescriptor, object); |
| 876 | 883 |
} |
| 884 |
|
|
| 885 |
// ======================================= |
|
| 886 |
|
|
| 887 |
|
|
| 888 |
public String getFunctionName(String newFuncName) {
|
|
| 889 |
|
|
| 890 |
if (!versionSet) {
|
|
| 891 |
postGISVersion = getPostgisVersion(); |
|
| 892 |
versionSet = true; |
|
| 893 |
} |
|
| 894 |
return getFunctionNameForVersion(newFuncName, postGISVersion); |
|
| 877 | 895 |
} |
| 896 |
|
|
| 897 |
private String getFunctionNameForVersion(String newFuncName, int[] pv) {
|
|
| 898 |
|
|
| 899 |
if (newFuncName == null || pv == null) {
|
|
| 900 |
return newFuncName; |
|
| 901 |
} |
|
| 902 |
|
|
| 903 |
if (pv.length < 2) {
|
|
| 904 |
// cannot compare |
|
| 905 |
return newFuncName; |
|
| 906 |
} |
|
| 907 |
|
|
| 908 |
if (pv[0] > 1) {
|
|
| 909 |
return newFuncName; |
|
| 910 |
} |
|
| 911 |
|
|
| 912 |
if (pv[0] == 1 && pv[1] >= 3) {
|
|
| 913 |
return newFuncName; |
|
| 914 |
} |
|
| 915 |
|
|
| 916 |
Properties pp = this.getBeforePostgis13Properties(); |
|
| 917 |
String k = newFuncName.toLowerCase(); |
|
| 918 |
String v = pp.getProperty(k); |
|
| 919 |
if (v == null) {
|
|
| 920 |
return newFuncName; |
|
| 921 |
} else {
|
|
| 922 |
return v; |
|
| 923 |
} |
|
| 924 |
} |
|
| 925 |
|
|
| 926 |
private int[] getPostgisVersion() {
|
|
| 927 |
|
|
| 928 |
String sql = "SELECT PostGIS_Lib_Version()"; |
|
| 929 |
ResultSet rs = null; |
|
| 930 |
Statement st = null; |
|
| 931 |
String v = null; |
|
| 932 |
|
|
| 933 |
try {
|
|
| 934 |
st = this.getConnection().createStatement(); |
|
| 935 |
rs = st.executeQuery(sql); |
|
| 936 |
rs.next(); |
|
| 937 |
v = rs.getString(1); |
|
| 938 |
if (v == null) {
|
|
| 939 |
throw new Exception("Returned version is NULL");
|
|
| 940 |
} |
|
| 941 |
} catch (Exception exc) {
|
|
| 942 |
logger.error("Unable to get Postgis version: " + exc.getMessage(), exc);
|
|
| 943 |
return null; |
|
| 944 |
} finally {
|
|
| 945 |
try { rs.close(); } catch (Exception e) {};
|
|
| 946 |
try { st.close(); } catch (Exception e) {};
|
|
| 947 |
} |
|
| 948 |
|
|
| 949 |
String[] vv = v.split("\\.");
|
|
| 950 |
int[] resp = new int[3]; |
|
| 951 |
try {
|
|
| 952 |
for (int i=0; i<3; i++) {
|
|
| 953 |
resp[i] = Integer.parseInt(vv[i]); |
|
| 954 |
} |
|
| 955 |
} catch (Exception exc) {
|
|
| 956 |
logger.error("Unable to parse version: " + v, exc);
|
|
| 957 |
return null; |
|
| 958 |
} |
|
| 959 |
return resp; |
|
| 960 |
} |
|
| 961 |
|
|
| 962 |
|
|
| 963 |
protected Properties getBeforePostgis13Properties() {
|
|
| 964 |
if (beforePostgis13 == null) {
|
|
| 965 |
|
|
| 966 |
beforePostgis13 = new Properties(); |
|
| 967 |
// Left side MUST be in lower case |
|
| 968 |
// Right side will be used if Postgis version < 1.3 |
|
| 969 |
beforePostgis13.setProperty("st_intersects", "Intersects");
|
|
| 970 |
beforePostgis13.setProperty("st_extent", "Extent");
|
|
| 971 |
beforePostgis13.setProperty("st_envelope", "Envelope");
|
|
| 972 |
beforePostgis13.setProperty("st_asbinary", "AsBinary");
|
|
| 973 |
beforePostgis13.setProperty("st_geomfromtext", "GeomFromText");
|
|
| 974 |
beforePostgis13.setProperty("st_geomfromwkb", "GeomFromWKB");
|
|
| 975 |
} |
|
| 976 |
return beforePostgis13; |
|
| 977 |
} |
|
| 978 |
|
|
| 979 |
|
|
| 980 |
|
|
| 981 |
} |
|
| trunk/org.gvsig.postgresql/org.gvsig.postgresql.provider/src/main/java/org/gvsig/fmap/dal/store/postgresql/PostgreSQLSetProvider.java | ||
|---|---|---|
| 139 | 139 |
} |
| 140 | 140 |
return resp; |
| 141 | 141 |
} |
| 142 |
|
|
| 143 |
private String getFunctionName(String newFunctionName) {
|
|
| 144 |
|
|
| 145 |
PostgreSQLStoreProvider pg_sto_prov = (PostgreSQLStoreProvider) this.getStore(); |
|
| 146 |
PostgreSQLHelper hpr = pg_sto_prov.getPgHelper(); |
|
| 147 |
if (hpr == null) {
|
|
| 148 |
logger.info("Unable to get PG helper.", new Exception("Helper is null"));
|
|
| 149 |
return newFunctionName; |
|
| 150 |
} else {
|
|
| 151 |
return hpr.getFunctionName(newFunctionName); |
|
| 152 |
} |
|
| 153 |
} |
|
| 142 | 154 |
|
| 143 | 155 |
|
| 144 | 156 |
private String appendWorkingAreaCondition(String sql) throws Exception {
|
| 145 | 157 |
|
| 158 |
|
|
| 146 | 159 |
DBStoreParameters dbParams = |
| 147 | 160 |
(DBStoreParameters) getStore().getParameters(); |
| 148 | 161 |
|
| ... | ... | |
| 158 | 171 |
StringBuffer strbuf = new StringBuffer(); |
| 159 | 172 |
|
| 160 | 173 |
if (sql == null) {
|
| 161 |
strbuf.append("ST_intersects(ST_GeomFromText('");
|
|
| 174 |
strbuf.append( |
|
| 175 |
getFunctionName("ST_Intersects") + "("
|
|
| 176 |
+ getFunctionName("ST_GeomFromText") + "('");
|
|
| 162 | 177 |
} else {
|
| 163 | 178 |
strbuf.append("(");
|
| 164 | 179 |
strbuf.append(sql); |
| 165 |
strbuf.append(") AND ST_intersects(ST_GeomFromText('");
|
|
| 180 |
strbuf.append(") AND "
|
|
| 181 |
+ getFunctionName("ST_Intersects") + "("
|
|
| 182 |
+ getFunctionName("ST_GeomFromText") + "('");
|
|
| 166 | 183 |
} |
| 167 | 184 |
|
| 168 | 185 |
String workAreaWkt = null; |
| ... | ... | |
| 181 | 198 |
} else {
|
| 182 | 199 |
strbuf.append(Integer.toString(sridInt)); |
| 183 | 200 |
} |
| 184 |
strbuf.append("), ST_envelope(");
|
|
| 201 |
strbuf.append("), " + getFunctionName("ST_Envelope") + "(");
|
|
| 185 | 202 |
strbuf.append(helper.escapeFieldName(geoname)); |
| 186 | 203 |
strbuf.append("))");
|
| 187 | 204 |
|
| trunk/org.gvsig.postgresql/org.gvsig.postgresql.provider/src/main/java/org/gvsig/fmap/dal/store/postgresql/PostgreSQLStoreProvider.java | ||
|---|---|---|
| 27 | 27 |
|
| 28 | 28 |
package org.gvsig.fmap.dal.store.postgresql; |
| 29 | 29 |
|
| 30 |
import java.sql.DatabaseMetaData; |
|
| 31 |
import java.sql.ResultSet; |
|
| 32 |
import java.sql.SQLException; |
|
| 33 |
import java.sql.Statement; |
|
| 34 |
import java.util.Iterator; |
|
| 30 | 35 |
import java.util.List; |
| 36 |
import java.util.Properties; |
|
| 31 | 37 |
import java.util.regex.Matcher; |
| 32 | 38 |
import java.util.regex.Pattern; |
| 33 | 39 |
|
| ... | ... | |
| 44 | 50 |
import org.gvsig.fmap.dal.feature.FeatureType; |
| 45 | 51 |
import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
| 46 | 52 |
import org.gvsig.fmap.dal.feature.spi.FeatureSetProvider; |
| 53 |
import org.gvsig.fmap.dal.resource.exception.AccessResourceException; |
|
| 47 | 54 |
import org.gvsig.fmap.dal.spi.DataStoreProviderServices; |
| 48 | 55 |
import org.gvsig.fmap.dal.store.db.DBHelper; |
| 49 | 56 |
import org.gvsig.fmap.dal.store.jdbc.JDBCHelper; |
| 50 | 57 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreProviderWriter; |
| 58 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException; |
|
| 59 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
|
| 51 | 60 |
import org.gvsig.fmap.geom.Geometry; |
| 52 | 61 |
import org.slf4j.Logger; |
| 53 | 62 |
import org.slf4j.LoggerFactory; |
| ... | ... | |
| 61 | 70 |
public static final String DESCRIPTION = "PostgreSQL source"; |
| 62 | 71 |
|
| 63 | 72 |
public static final String METADATA_DEFINITION_NAME = NAME; |
| 64 |
|
|
| 65 |
|
|
| 73 |
|
|
| 66 | 74 |
public PostgreSQLStoreProvider(PostgreSQLStoreParameters params, |
| 67 | 75 |
DataStoreProviderServices storeServices) |
| 68 | 76 |
throws InitializeException {
|
| ... | ... | |
| 74 | 82 |
} |
| 75 | 83 |
|
| 76 | 84 |
protected JDBCHelper createHelper() throws InitializeException {
|
| 77 |
return new PostgreSQLHelper(this, getPGParameters()); |
|
| 85 |
JDBCHelper resp = new PostgreSQLHelper(this, getPGParameters()); |
|
| 86 |
|
|
| 87 |
return resp; |
|
| 78 | 88 |
} |
| 79 | 89 |
|
| 80 |
protected String fixFilter(String filter) {
|
|
| 81 |
if (filter == null) {
|
|
| 90 |
|
|
| 91 |
|
|
| 92 |
protected String fixFilter(String _filter) {
|
|
| 93 |
if (_filter == null) {
|
|
| 82 | 94 |
return null; |
| 83 | 95 |
} |
| 96 |
|
|
| 97 |
String filter = fixFunctionNames(_filter); |
|
| 84 | 98 |
|
| 85 | 99 |
// Transform SRS to code |
| 86 | 100 |
// GeomFromText\s*\(\s*'[^']*'\s*,\s*('[^']*')\s*\)
|
| 101 |
|
|
| 102 |
String geom_from_text = this.getFunctionName("ST_GeomFromText");
|
|
| 87 | 103 |
Pattern pattern = Pattern |
| 88 |
.compile("ST_GeomFromText\\s*\\(\\s*'[^']*'\\s*,\\s*'([^']*)'\\s*\\)");
|
|
| 104 |
.compile(geom_from_text + "\\s*\\(\\s*'[^']*'\\s*,\\s*'([^']*)'\\s*\\)");
|
|
| 89 | 105 |
Matcher matcher = pattern.matcher(filter); |
| 90 | 106 |
StringBuilder strb = new StringBuilder(); |
| 91 | 107 |
int pos = 0; |
| ... | ... | |
| 109 | 125 |
return strb.toString(); |
| 110 | 126 |
} |
| 111 | 127 |
|
| 112 |
public String getName() {
|
|
| 128 |
|
|
| 129 |
public String getName() {
|
|
| 113 | 130 |
return NAME; |
| 114 | 131 |
} |
| 115 | 132 |
|
| ... | ... | |
| 155 | 172 |
return true; |
| 156 | 173 |
} |
| 157 | 174 |
|
| 158 |
// ************************************************************************************// |
|
| 159 | 175 |
|
| 160 |
|
|
| 161 |
// ************************************************************************************// |
|
| 162 |
|
|
| 163 |
|
|
| 164 |
|
|
| 165 | 176 |
protected PostgreSQLHelper getPgHelper() {
|
| 166 | 177 |
return (PostgreSQLHelper) getHelper(); |
| 167 | 178 |
} |
| 168 | 179 |
|
| 169 |
// ************************************************************************************// |
|
| 170 | 180 |
|
| 171 |
// ************************************************************************************// |
|
| 172 | 181 |
|
| 173 |
|
|
| 174 |
|
|
| 175 | 182 |
public boolean canWriteGeometry(int geometryType, int geometrySubtype) |
| 176 | 183 |
throws DataException {
|
| 177 | 184 |
FeatureType type = getFeatureStore().getDefaultFeatureType(); |
| ... | ... | |
| 226 | 233 |
|
| 227 | 234 |
if (attr.getType() == DataTypes.GEOMETRY) {
|
| 228 | 235 |
fields.add(helper.escapeFieldName(attr.getName())); |
| 229 |
values.add("ST_GeomFromWKB(?,?)");
|
|
| 236 |
values.add(getFunctionName("ST_GeomFromWKB") + "(?,?)");
|
|
| 230 | 237 |
} else {
|
| 231 | 238 |
super.prepareAttributeForInsert(attr, fields, values); |
| 232 | 239 |
} |
| ... | ... | |
| 237 | 244 |
List<String> values) {
|
| 238 | 245 |
if (attr.getType() == DataTypes.GEOMETRY) {
|
| 239 | 246 |
values.add(helper.escapeFieldName(attr.getName()) |
| 240 |
+ " = ST_GeomFromWKB(?,?)");
|
|
| 247 |
+ " = " + getFunctionName("ST_GeomFromWKB") + "(?,?)");
|
|
| 241 | 248 |
} else {
|
| 242 | 249 |
super.prepareAttributeForUpdate(attr, values); |
| 243 | 250 |
} |
| ... | ... | |
| 313 | 320 |
|
| 314 | 321 |
return actions; |
| 315 | 322 |
} |
| 323 |
|
|
| 316 | 324 |
|
| 325 |
private String getFunctionName(String newFunctionName) {
|
|
| 326 |
|
|
| 327 |
PostgreSQLHelper hpr = getPgHelper(); |
|
| 328 |
if (hpr == null) {
|
|
| 329 |
logger.info("Unable to get PG helper.", new Exception("Helper is null"));
|
|
| 330 |
return newFunctionName; |
|
| 331 |
} else {
|
|
| 332 |
return hpr.getFunctionName(newFunctionName); |
|
| 333 |
} |
|
| 334 |
} |
|
| 335 |
|
|
| 336 |
private String fixFunctionNames(String _filter) {
|
|
| 337 |
|
|
| 338 |
Properties props = this.getPgHelper().getBeforePostgis13Properties(); |
|
| 339 |
Iterator iter = props.keySet().iterator(); |
|
| 340 |
String kstr = null; |
|
| 341 |
String vstr = null; |
|
| 342 |
|
|
| 343 |
String resp = _filter; |
|
| 344 |
|
|
| 345 |
while (iter.hasNext()) {
|
|
| 346 |
kstr = (String) iter.next(); |
|
| 347 |
vstr = getPgHelper().getFunctionName(kstr); |
|
| 348 |
resp = replace(resp, kstr, vstr); |
|
| 349 |
} |
|
| 350 |
return resp; |
|
| 351 |
} |
|
| 352 |
|
|
| 353 |
private String replace(String str, String oldstr, String newstr) {
|
|
| 354 |
|
|
| 355 |
if (oldstr == null || newstr == null || |
|
| 356 |
oldstr.length() == 0 || oldstr.equals(newstr)) {
|
|
| 357 |
return str; |
|
| 358 |
} |
|
| 359 |
|
|
| 360 |
String lowerstr = str.toLowerCase(); |
|
| 361 |
String lowerold = oldstr.toLowerCase(); |
|
| 362 |
|
|
| 363 |
if (lowerstr.indexOf(lowerold) == -1) {
|
|
| 364 |
// nothing to do |
|
| 365 |
return str; |
|
| 366 |
} |
|
| 367 |
|
|
| 368 |
Pattern p = Pattern.compile(lowerold, Pattern.LITERAL); |
|
| 369 |
String[] parts = p.split(lowerstr); |
|
| 370 |
|
|
| 371 |
StringBuffer resp = new StringBuffer(); |
|
| 372 |
int auxind = 0; |
|
| 373 |
resp.append(str.subSequence(0, parts[0].length())); |
|
| 374 |
for (int i=1; i<parts.length; i++) {
|
|
| 375 |
resp.append(newstr); |
|
| 376 |
auxind = getIndex(parts, i-1, oldstr.length()); |
|
| 377 |
resp.append(str.subSequence(auxind, auxind + parts[i].length())); |
|
| 378 |
} |
|
| 379 |
return resp.toString(); |
|
| 380 |
} |
|
| 381 |
|
|
| 382 |
/** |
|
| 383 |
* This method gets the index where the n-th part (0-based) |
|
| 384 |
* starts in the original string |
|
| 385 |
* |
|
| 386 |
* @param parts |
|
| 387 |
* @param n |
|
| 388 |
* @param length |
|
| 389 |
* @return |
|
| 390 |
*/ |
|
| 391 |
private int getIndex(String[] parts, int till_n, int length) {
|
|
| 392 |
|
|
| 393 |
int resp = 0; |
|
| 394 |
for (int i=0; i<(till_n+1); i++) {
|
|
| 395 |
resp = resp + parts[i].length(); |
|
| 396 |
resp = resp + length; |
|
| 397 |
} |
|
| 398 |
return resp; |
|
| 399 |
} |
|
| 400 |
|
|
| 401 |
|
|
| 317 | 402 |
} |
| trunk/org.gvsig.postgresql/pom.xml | ||
|---|---|---|
| 11 | 11 |
<parent> |
| 12 | 12 |
<groupId>org.gvsig</groupId> |
| 13 | 13 |
<artifactId>org.gvsig.desktop</artifactId> |
| 14 |
<version>2.0.21</version>
|
|
| 14 |
<version>2.0.23-SNAPSHOT</version>
|
|
| 15 | 15 |
</parent> |
| 16 | 16 |
|
| 17 | 17 |
<url>https://devel.gvsig.org/redmine/projects/gvsig-postgresql</url> |
Also available in: Unified diff