Revision 13995
| branches/v10/extensions/extOracleSpatial/src/es/prodevelop/cit/gvsig/fmap/drivers/jdbc/oracle/OracleSpatialDriver.java | ||
|---|---|---|
| 119 | 119 |
|
| 120 | 120 |
import java.io.File; |
| 121 | 121 |
import java.io.IOException; |
| 122 |
import java.math.BigDecimal; |
|
| 122 | 123 |
|
| 123 | 124 |
import java.sql.Connection; |
| 124 | 125 |
import java.sql.DatabaseMetaData; |
| ... | ... | |
| 601 | 602 |
// ----------------------- |
| 602 | 603 |
_rs = st.executeQuery(not_restricted_sql + " where (rownum = 1)"); |
| 603 | 604 |
metaData = _rs.getMetaData(); |
| 605 |
userName = conn.getMetaData().getUserName(); |
|
| 606 |
|
|
| 604 | 607 |
|
| 605 | 608 |
// geoColInd = _rs.findColumn(geoColName); |
| 606 | 609 |
oneBasedGeoColInd = metaData.getColumnCount() + 1; |
| ... | ... | |
| 2759 | 2762 |
* @param withsrid False if the SRS is set to NULL. True otherwise. |
| 2760 | 2763 |
* @return the SQL sentence to perform the update |
| 2761 | 2764 |
*/ |
| 2762 |
public static String getMetadataUpdateSql(String tName, String ora_srid, |
|
| 2765 |
public static String getMetadataUpdateSql(String schema, String tName, String ora_srid,
|
|
| 2763 | 2766 |
Rectangle2D bbox, int dim, boolean withsrid) {
|
| 2764 | 2767 |
String[] dim_name = new String[dim]; |
| 2765 | 2768 |
double tolerance = 0.5; |
| ... | ... | |
| 2782 | 2785 |
} |
| 2783 | 2786 |
|
| 2784 | 2787 |
String resp = "INSERT INTO " + ORACLE_GEOMETADATA_VIEW + " " + |
| 2785 |
" ( TABLE_NAME, COLUMN_NAME, DIMINFO, SRID ) " + " VALUES (" + "'" +
|
|
| 2786 |
tName + "', " + "'" + DEFAULT_GEO_FIELD + "', " + |
|
| 2787 |
"SDO_DIM_ARRAY( " + "SDO_DIM_ELEMENT ('" + dim_name[0] + "', " +
|
|
| 2788 |
" ( OWNER, TABLE_NAME, COLUMN_NAME, DIMINFO, SRID ) " + " VALUES ("
|
|
| 2789 |
+ "'" + schema + "', " |
|
| 2790 |
+ "'" + tName + "', " |
|
| 2791 |
+ "'" + DEFAULT_GEO_FIELD + "', " + |
|
| 2792 |
"MDSYS.SDO_DIM_ARRAY( " + "MDSYS.SDO_DIM_ELEMENT ('" + dim_name[0] + "', " +
|
|
| 2788 | 2793 |
bbox.getMinX() + ", " + bbox.getMaxX() + ", " + tolerance + " ), " + |
| 2789 |
"SDO_DIM_ELEMENT ('" + dim_name[1] + "', " + bbox.getMinY() + ", " +
|
|
| 2794 |
"MDSYS.SDO_DIM_ELEMENT ('" + dim_name[1] + "', " + bbox.getMinY() + ", " +
|
|
| 2790 | 2795 |
bbox.getMaxY() + ", " + tolerance + " ))"; |
| 2791 | 2796 |
|
| 2792 | 2797 |
if (dim > 2) {
|
| 2793 | 2798 |
resp = resp.substring(0, resp.length() - 1) + ","; |
| 2794 |
resp = resp + "SDO_DIM_ELEMENT ('" + dim_name[2] +
|
|
| 2799 |
resp = resp + "MDSYS.SDO_DIM_ELEMENT ('" + dim_name[2] +
|
|
| 2795 | 2800 |
"', 0.0, 100.0, " + tolerance + " ))"; |
| 2796 | 2801 |
|
| 2797 | 2802 |
if (dim > 3) {
|
| 2798 | 2803 |
resp = resp.substring(0, resp.length() - 1) + ","; |
| 2799 |
resp = resp + "SDO_DIM_ELEMENT ('" + dim_name[3] +
|
|
| 2804 |
resp = resp + "MDSYS.SDO_DIM_ELEMENT ('" + dim_name[3] +
|
|
| 2800 | 2805 |
"', 0.0, 100.0, " + tolerance + " ))"; |
| 2801 | 2806 |
} |
| 2802 | 2807 |
} |
| ... | ... | |
| 3767 | 3772 |
throws SQLException {
|
| 3768 | 3773 |
DatabaseMetaData dbmd = conn.getMetaData(); |
| 3769 | 3774 |
String[] types = { "TABLE", "VIEW" };
|
| 3775 |
// String[] types = { "VIEW" };
|
|
| 3770 | 3776 |
|
| 3771 | 3777 |
ResultSet rs = null; |
| 3772 | 3778 |
rs = getTableNamesFromTable(dbmd.getTables(catalog, ORACLE_GEO_SCHEMA, |
| ... | ... | |
| 3851 | 3857 |
*/ |
| 3852 | 3858 |
public String[] getIdFieldsCandidates(Connection conn, String table_name) |
| 3853 | 3859 |
throws SQLException {
|
| 3854 |
String[] resp = { "ROWID" };
|
|
| 3860 |
|
|
| 3861 |
String rowid_avail_test = "SELECT ROWID FROM " + table_name + " WHERE ROWNUM = 1"; |
|
| 3862 |
Statement _st = conn.createStatement(); |
|
| 3863 |
ResultSet _rs = _st.executeQuery(rowid_avail_test); |
|
| 3864 |
_rs.close(); |
|
| 3865 |
_st.close(); |
|
| 3855 | 3866 |
|
| 3867 |
String[] resp = { "ROWID" };
|
|
| 3856 | 3868 |
return resp; |
| 3857 | 3869 |
} |
| 3858 | 3870 |
|
| ... | ... | |
| 3862 | 3874 |
*/ |
| 3863 | 3875 |
public String[] getGeometryFieldsCandidates(Connection conn, |
| 3864 | 3876 |
String table_name) throws SQLException {
|
| 3877 |
|
|
| 3865 | 3878 |
Statement _st = conn.createStatement(); |
| 3866 | 3879 |
String[] tokens = table_name.split("\\u002E", 2);
|
| 3867 | 3880 |
String qry; |
| ... | ... | |
| 3889 | 3902 |
_rs.close(); |
| 3890 | 3903 |
_st.close(); |
| 3891 | 3904 |
|
| 3892 |
return (String[]) aux.toArray(new String[0]); |
|
| 3905 |
String[] resp = (String[]) aux.toArray(new String[0]); |
|
| 3906 |
|
|
| 3907 |
return checkIndexes(conn, resp, table_name); |
|
| 3893 | 3908 |
} |
| 3894 | 3909 |
|
| 3895 |
/** |
|
| 3910 |
private String[] checkIndexes(Connection c, String[] all, String __t) throws SQLException {
|
|
| 3911 |
|
|
| 3912 |
ArrayList good_ones = new ArrayList(); |
|
| 3913 |
String t = __t; |
|
| 3914 |
if (t.lastIndexOf(".") != -1) t = t.substring(t.lastIndexOf(".") + 1, t.length());
|
|
| 3915 |
|
|
| 3916 |
for (int i=0; i<all.length; i++) {
|
|
| 3917 |
|
|
| 3918 |
String qry = "SELECT SRID, DIMINFO FROM " + ORACLE_GEOMETADATA_VIEW + |
|
| 3919 |
" WHERE TABLE_NAME = " + "'" + t.toUpperCase() + |
|
| 3920 |
"' AND COLUMN_NAME = '" + all[i].toUpperCase() + "'"; |
|
| 3921 |
|
|
| 3922 |
Statement _st = c.createStatement(); |
|
| 3923 |
ResultSet _rs = _st.executeQuery(qry); |
|
| 3924 |
if (_rs.next()) {
|
|
| 3925 |
String _srid = toString((BigDecimal) _rs.getObject(1)); |
|
| 3926 |
ARRAY diminfo = (ARRAY) _rs.getObject(2); |
|
| 3927 |
int len = diminfo.getOracleArray().length; |
|
| 3928 |
if (allowsGeoQueries(c, t, all[i], _srid, len)) {
|
|
| 3929 |
good_ones.add(all[i]); |
|
| 3930 |
} |
|
| 3931 |
} |
|
| 3932 |
_rs.close(); |
|
| 3933 |
_st.close(); |
|
| 3934 |
} |
|
| 3935 |
|
|
| 3936 |
if (good_ones.size() == 0) {
|
|
| 3937 |
throw new SQLException("no_indexes_on_declared_geo_fields");
|
|
| 3938 |
} |
|
| 3939 |
return (String[]) good_ones.toArray(new String[0]); |
|
| 3940 |
} |
|
| 3941 |
|
|
| 3942 |
private String toString(BigDecimal number) {
|
|
| 3943 |
|
|
| 3944 |
if (number == null) return "NULL"; |
|
| 3945 |
return "" + number.intValue(); |
|
| 3946 |
} |
|
| 3947 |
|
|
| 3948 |
private boolean allowsGeoQueries(Connection c, String _t, String gf, String _srid, int dims) {
|
|
| 3949 |
String p = getPointConstructor(dims, _srid); |
|
| 3950 |
String qry = "SELECT * FROM " + _t.toUpperCase() + " WHERE (ROWNUM = 1)"; |
|
| 3951 |
qry = "SELECT * FROM (" + qry + ") WHERE SDO_RELATE(" + "\"" + gf + "\", " + p + ", 'mask=TOUCH') = 'TRUE'";
|
|
| 3952 |
|
|
| 3953 |
try {
|
|
| 3954 |
Statement _st = c.createStatement(); |
|
| 3955 |
ResultSet _rs = _st.executeQuery(qry); |
|
| 3956 |
_rs.close(); |
|
| 3957 |
_st.close(); |
|
| 3958 |
} catch (Exception ex) {
|
|
| 3959 |
return false; |
|
| 3960 |
} |
|
| 3961 |
return true; |
|
| 3962 |
} |
|
| 3963 |
|
|
| 3964 |
private String getPointConstructor(int dims, String _srid) {
|
|
| 3965 |
|
|
| 3966 |
String coord = ""; |
|
| 3967 |
for (int i=0; i<dims; i++) coord = coord + "0, "; |
|
| 3968 |
coord = coord.substring(0, coord.length() - 2); |
|
| 3969 |
|
|
| 3970 |
return "MDSYS.SDO_GEOMETRY(" + (dims * 1000 + 1) + ", " + _srid + ", NULL, " +
|
|
| 3971 |
"MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1), MDSYS.SDO_ORDINATE_ARRAY(" + coord + "))";
|
|
| 3972 |
} |
|
| 3973 |
|
|
| 3974 |
private boolean stringInArrayListOfStrings(ArrayList l, String str) {
|
|
| 3975 |
|
|
| 3976 |
if (l == null) return false; |
|
| 3977 |
if (str == null) return false; |
|
| 3978 |
|
|
| 3979 |
String item = ""; |
|
| 3980 |
for (int i=0; i<l.size(); i++) {
|
|
| 3981 |
if (l.get(i) instanceof String) {
|
|
| 3982 |
item = (String) l.get(i); |
|
| 3983 |
if (item.compareToIgnoreCase(str) == 0) return true; |
|
| 3984 |
} |
|
| 3985 |
} |
|
| 3986 |
return false; |
|
| 3987 |
} |
|
| 3988 |
|
|
| 3989 |
/** |
|
| 3896 | 3990 |
* Utility method to check if a given table is empty. |
| 3897 | 3991 |
*/ |
| 3898 | 3992 |
public boolean isEmptyTable(Connection conn, String tableName) {
|
| ... | ... | |
| 4271 | 4365 |
return resp_aux; |
| 4272 | 4366 |
} |
| 4273 | 4367 |
|
| 4368 |
public void setUserName(String u) {
|
|
| 4369 |
userName = u; |
|
| 4370 |
} |
|
| 4274 | 4371 |
|
| 4372 |
public String getUserName() {
|
|
| 4373 |
return userName; |
|
| 4374 |
} |
|
| 4275 | 4375 |
|
| 4376 |
|
|
| 4377 |
|
|
| 4276 | 4378 |
} |
| branches/v10/extensions/extOracleSpatial/src/es/prodevelop/cit/gvsig/fmap/drivers/jdbc/oracle/OracleSpatialWriter.java | ||
|---|---|---|
| 126 | 126 |
storeWithSrid = false; |
| 127 | 127 |
} |
| 128 | 128 |
|
| 129 |
String _sql_rem_meta = OracleSpatialDriver.getRemoveMetadataSql((DBLayerDefinition) tableDef); |
|
| 130 |
String _sql_drop = OracleSpatialDriver.getDropTableSql((DBLayerDefinition) tableDef); |
|
| 131 |
String _sql_creation = OracleSpatialDriver.getTableCreationSql((DBLayerDefinition) tableDef); |
|
| 132 |
String _sql_index = OracleSpatialDriver.getIndexCreationSql((DBLayerDefinition) tableDef); |
|
| 129 |
String _sql_rem_meta = |
|
| 130 |
OracleSpatialDriver.getRemoveMetadataSql((DBLayerDefinition) tableDef); |
|
| 131 |
String _sql_drop = |
|
| 132 |
OracleSpatialDriver.getDropTableSql((DBLayerDefinition) tableDef); |
|
| 133 |
String _sql_creation = |
|
| 134 |
OracleSpatialDriver.getTableCreationSql((DBLayerDefinition) tableDef); |
|
| 135 |
String _sql_index = |
|
| 136 |
OracleSpatialDriver.getIndexCreationSql((DBLayerDefinition) tableDef); |
|
| 133 | 137 |
|
| 134 | 138 |
int dim_aux = dimensions; |
| 135 | 139 |
|
| 136 |
String _sql_meta = OracleSpatialDriver.getMetadataUpdateSql(((DBLayerDefinition) tableDef).getTableName(), |
|
| 140 |
String _sql_meta = OracleSpatialDriver.getMetadataUpdateSql( |
|
| 141 |
driver.getUserName(), |
|
| 142 |
((DBLayerDefinition) tableDef).getTableName(), |
|
| 137 | 143 |
oracleSRID, bbox, dim_aux, storeWithSrid); |
| 138 | 144 |
|
| 139 | 145 |
//dimensions); |
| ... | ... | |
| 165 | 171 |
catch (SQLException ex) {
|
| 166 | 172 |
logger.error("Error while executing SQL for metadata removal: " +
|
| 167 | 173 |
ex.getMessage(), ex); |
| 168 |
|
|
| 169 |
// throw new EditionException(ex.getMessage()); |
|
| 170 | 174 |
} |
| 171 | 175 |
|
| 172 | 176 |
try {
|
| ... | ... | |
| 177 | 181 |
catch (SQLException ex) {
|
| 178 | 182 |
logger.error("Error while executing SQL for table creation: " +
|
| 179 | 183 |
ex.getMessage(), ex); |
| 180 |
|
|
| 181 |
// throw new EditionException(ex.getMessage()); |
|
| 182 | 184 |
} |
| 183 | 185 |
|
| 184 | 186 |
try {
|
| ... | ... | |
| 190 | 192 |
logger.error( |
| 191 | 193 |
"Error while executing SQL for metadata insertion: " + |
| 192 | 194 |
ex.getMessage(), ex); |
| 193 |
|
|
| 194 |
// throw new EditionException(ex.getMessage()); |
|
| 195 | 195 |
} |
| 196 | 196 |
|
| 197 | 197 |
try {
|
| ... | ... | |
| 202 | 202 |
catch (SQLException ex) {
|
| 203 | 203 |
logger.error("Error while executing SQL for index creation: " +
|
| 204 | 204 |
ex.getMessage(), ex); |
| 205 |
|
|
| 206 |
// throw new EditionException(ex.getMessage()); |
|
| 207 | 205 |
} |
| 208 | 206 |
|
| 209 | 207 |
rowIndex = 0; |
| branches/v10/extensions/extOracleSpatial/src/es/prodevelop/cit/gvsig/jdbc_spatial/gui/jdbcwizard/TablesListItem.java | ||
|---|---|---|
| 42 | 42 |
*/ |
| 43 | 43 |
package es.prodevelop.cit.gvsig.jdbc_spatial.gui.jdbcwizard; |
| 44 | 44 |
|
| 45 |
import com.iver.andami.PluginServices; |
|
| 45 | 46 |
import com.iver.cit.gvsig.fmap.MapControl; |
| 46 | 47 |
import com.iver.cit.gvsig.fmap.drivers.DefaultDBDriver; |
| 47 | 48 |
|
| ... | ... | |
| 49 | 50 |
import java.sql.SQLException; |
| 50 | 51 |
|
| 51 | 52 |
import javax.swing.JCheckBox; |
| 53 |
import javax.swing.JOptionPane; |
|
| 52 | 54 |
|
| 53 | 55 |
|
| 54 | 56 |
/** |
| ... | ... | |
| 132 | 134 |
public UserTableSettingsPanel getUserTableSettingsPanel() |
| 133 | 135 |
throws SQLException {
|
| 134 | 136 |
if (tableSettingsPanel == null) {
|
| 135 |
String[] ids = driver.getIdFieldsCandidates(conn, tableName); |
|
| 136 |
String[] geos = driver.getGeometryFieldsCandidates(conn, tableName); |
|
| 137 |
|
|
| 138 |
String[] ids = new String[0]; |
|
| 139 |
try {
|
|
| 140 |
ids = driver.getIdFieldsCandidates(conn, tableName); |
|
| 141 |
} catch (SQLException se) {
|
|
| 142 |
String msg = PluginServices.getText(this, "id_not_available") + " " + tableName |
|
| 143 |
+ ":\n" + se.getMessage(); |
|
| 144 |
String title = PluginServices.getText(this, "id_error"); |
|
| 145 |
JOptionPane.showMessageDialog(parent, msg, title, JOptionPane.ERROR_MESSAGE); |
|
| 146 |
setSelected(false); |
|
| 147 |
} |
|
| 148 |
|
|
| 149 |
String[] geos = new String[0]; |
|
| 150 |
try {
|
|
| 151 |
geos = driver.getGeometryFieldsCandidates(conn, tableName); |
|
| 152 |
} catch (SQLException se) {
|
|
| 153 |
String msg = PluginServices.getText(this, "geo_field_not_available") |
|
| 154 |
+ ":\n" + PluginServices.getText(this, se.getMessage()) + ": " + tableName; |
|
| 155 |
String title = PluginServices.getText(this, "geo_field_error"); |
|
| 156 |
JOptionPane.showMessageDialog(parent, msg, title, JOptionPane.ERROR_MESSAGE); |
|
| 157 |
setSelected(false); |
|
| 158 |
} |
|
| 137 | 159 |
|
| 138 | 160 |
int ids_size = ids.length; |
| 139 | 161 |
FieldComboItem[] ids_ci = new FieldComboItem[ids_size]; |
| branches/v10/extensions/extOracleSpatial/src/es/prodevelop/cit/gvsig/jdbc_spatial/ExportToOracle.java | ||
|---|---|---|
| 219 | 219 |
addStartIfNotPresent(driver_ldef, |
| 220 | 220 |
OracleSpatialDriver.ORACLE_ID_FIELD); |
| 221 | 221 |
oDriver.setLyrDef(driver_ldef); |
| 222 |
oDriver.setUserName(cwp.getUser().toUpperCase()); |
|
| 222 | 223 |
|
| 223 | 224 |
writer.setDriver(oDriver); |
| 224 | 225 |
|
Also available in: Unified diff