package com.ximple.eofms.jobs;
|
|
import java.io.File;
|
import java.io.IOException;
|
import java.net.MalformedURLException;
|
import java.sql.DriverManager;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.Iterator;
|
import java.util.Properties;
|
|
import org.apache.commons.logging.Log;
|
import org.apache.commons.logging.LogFactory;
|
import org.apache.commons.transaction.memory.PessimisticMapWrapper;
|
import org.apache.commons.transaction.util.CommonsLoggingLogger;
|
import org.apache.commons.transaction.util.LoggerFacade;
|
import org.geotools.data.FeatureWriter;
|
import org.geotools.data.Transaction;
|
import org.geotools.data.shapefile.ShapefileDataStore;
|
import org.geotools.feature.Feature;
|
import org.geotools.feature.FeatureType;
|
import org.geotools.feature.IllegalAttributeException;
|
import org.geotools.feature.SimpleFeature;
|
|
import com.vividsolutions.jts.util.Assert;
|
|
import oracle.jdbc.OracleConnection;
|
|
import com.ximple.eofms.filter.CreateLineStringStrategy;
|
import com.ximple.eofms.filter.ElementDispatcher;
|
import com.ximple.eofms.filter.TypeCompIdDispatchableFilter;
|
import com.ximple.io.dgn7.Element;
|
|
public class OracleConvertJobContext
|
{
|
static Log logger = LogFactory.getLog(OracleConvertJobContext.class);
|
static final LoggerFacade sLogger = new CommonsLoggingLogger(logger);
|
|
private static final String ORACLE_URL = "jdbc:oracle:thin:@";
|
private static final String PROPUsrKey = "user";
|
private static final String PROPPassKey = "password";
|
|
/**
|
* Table Prefiex
|
*/
|
protected static final String TABLE_PREFIX = "GEO$";
|
protected static final String UDT_SCHEMA = "SPATIALDB";
|
|
/**
|
* User Types
|
*/
|
protected static final String UDT_RAWS = "CREATE OR REPLACE TYPE \"" + UDT_SCHEMA + "\".\"RAWS\" AS VARRAY (1048576) OF NUMBER(38)";
|
protected static final String UDT_OFMID = "CREATE OR REPLACE TYPE \"" + UDT_SCHEMA + "\".\"OFMID\" AS OBJECT ("
|
+ "\"CLSID\" NUMBER(5), \"OID\" NUMBER(10), \"STATUS\" NUMBER(5), \"COMPID\" NUMBER(3), "
|
+ "\"RULEID\" NUMBER(3), \"OCCID\" NUMBER(3))";
|
|
protected static final String UDT_RAWSNAME = "RAWS";
|
protected static final String UDT_OFMIDNAME = "OFMID";
|
|
/**
|
* Utility SQL
|
*/
|
protected static final String TAB_DROP = "DROP TABLE %s.%s CASCADE CONSTRAINTS";
|
|
protected static final String TAB_DELETE = "DELETE FROM %s.%s";
|
|
/**
|
* Table Schema
|
*/
|
protected static final String TAB_RANGENODEINDEX_1 = "CREATE TABLE \"%s\".\"%s\"\n"
|
+ " ( \"RNID\" INTEGER NOT NULL ENABLE,\n"
|
+ " \"RPID\" INTEGER NOT NULL ENABLE,\n"
|
+ " \"RNG_LOWX\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"RNG_LOWY\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"RNG_HIGHX\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"RNG_HIGHY\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"EXTENTS\" MDSYS.SDO_GEOMETRY,\n"
|
+ " \"RNDESCR\" VARCHAR2(255), \n"
|
+ " PRIMARY KEY ( \"RNID\" ) ENABLE )";
|
|
protected static final String TAB_RANGENODEINDEX = "CREATE TABLE \"%s\".\"%s\"\n"
|
+ " ( \"RNID\" INTEGER NOT NULL ENABLE,\n"
|
+ " \"RPID\" INTEGER NOT NULL ENABLE,\n"
|
+ " \"RNG_LOWX\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"RNG_LOWY\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"RNG_HIGHX\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"RNG_HIGHY\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"RNDESCR\" VARCHAR2(255), \n"
|
+ " PRIMARY KEY ( \"RNID\" ) ENABLE )";
|
|
protected static final String TAB_RANGENODESTORAGE = "CREATE TABLE \"%s\".\"%s\"\n"
|
+ " ( \"RNID\" INTEGER NOT NULL ENABLE,\n"
|
+ " \"LAYERID\" NUMBER(5,0) NOT NULL ENABLE,\n"
|
+ " \"LASTUPDATE\" DATE NOT NULL ENABLE,\n"
|
+ " \"SPACETABLE\" VARCHAR2(255)\n" + " )";
|
|
protected static final String TAB_ELEMENTINDEX_1 = "CREATE TABLE %s.%s (\n"
|
+ " \"ELMNO\" INTEGER NOT NULL ENABLE,\n"
|
+ " \"TYPE\" NUMBER(5) NOT NULL ENABLE,\n"
|
+ " \"XLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"YLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"ZLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"XHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"YHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"ZHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n"
|
+ " \"TAG_LUFID\" NUMBER(10) NOT NULL ENABLE,\n"
|
+ " \"TAG_SFSC\" NUMBER(5) NOT NULL ENABLE,\n"
|
+ " \"TAG_SSTAT\" NUMBER(5) NOT NULL ENABLE,\n"
|
+ " \"TAG_BCOMPID\" NUMBER(3) NOT NULL ENABLE,\n"
|
+ " \"TAG_BRULENO\" NUMBER(3) NOT NULL ENABLE,\n"
|
+ " \"TAG_SOCCID\" NUMBER(5) NOT NULL ENABLE,\n"
|
+ " \"SPACENAME\" VARCHAR2(255) NOT NULL ENABLE,\n"
|
+ " PRIMARY KEY (\"ELMNO\") ENABLE\n"
|
+ " )";
|
|
protected static final String TAB_ELEMENTINDEX = "CREATE TABLE %s.%s (\n"
|
+ " \"ELMNO\" INTEGER NOT NULL ENABLE,\n"
|
+ " \"TYPE\" NUMBER(5) NOT NULL ENABLE,\n"
|
+ " \"XLOW\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"YLOW\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"ZLOW\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"XHIGH\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"YHIGH\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"ZHIGH\" FLOAT NOT NULL ENABLE,\n"
|
+ " \"TAG_LUFID\" NUMBER(10) NOT NULL ENABLE,\n"
|
+ " \"TAG_SFSC\" NUMBER(5) NOT NULL ENABLE,\n"
|
+ " \"TAG_SSTAT\" NUMBER(5) NOT NULL ENABLE,\n"
|
+ " \"TAG_BCOMPID\" NUMBER(3) NOT NULL ENABLE,\n"
|
+ " \"TAG_BRULENO\" NUMBER(3) NOT NULL ENABLE,\n"
|
+ " \"TAG_SOCCID\" NUMBER(5) NOT NULL ENABLE,\n"
|
+ " \"SPACENAME\" VARCHAR2(255) NOT NULL ENABLE,\n"
|
+ " PRIMARY KEY (\"ELMNO\") ENABLE\n"
|
+ " )";
|
|
protected static final String TAB_IGDSSEED = "CREATE TABLE %s.%s (\n"
|
+ " \"ELMNO\" INTEGER NOT NULL ENABLE,\n"
|
+ " \"SEEDELM\" \"" + UDT_SCHEMA
|
+ "\".\"RAWS\" NOT NULL ENABLE\n" + " )";
|
|
protected static final String TAB_STORAGE_1 = "CREATE TABLE %s.%s (\n" +
|
" \"ELMNO\" INTEGER NOT NULL ENABLE,\n" +
|
" \"XLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
|
" \"YLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
|
" \"XHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
|
" \"YHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
|
" \"EXTENTS\" MDSYS.SDO_GEOMETRY, \n" +
|
" \"PROPS\" INTEGER NOT NULL ENABLE,\n" +
|
" \"TAG_LUFID\" NUMBER(10) NOT NULL ENABLE,\n" +
|
" \"TAG_SFSC\" NUMBER(5) NOT NULL ENABLE,\n" +
|
" \"TAG_SSTAT\" NUMBER(5) NOT NULL ENABLE,\n" +
|
" \"TAG_BCOMPID\" NUMBER(3) NOT NULL ENABLE,\n" +
|
" \"TAG_BRULENO\" NUMBER(3) NOT NULL ENABLE,\n" +
|
" \"TAG_SOCCID\" NUMBER(5) NOT NULL ENABLE,\n" +
|
" \"LAYERID\" NUMBER(5) NOT NULL ENABLE,\n" +
|
" \"ELEMENT\" \"" + UDT_SCHEMA + "\".\"RAWS\" NOT NULL ENABLE, \n" +
|
" \"GEOM\" MDSYS.SDO_GEOMETRY \n" +
|
" )";
|
|
protected static final String TAB_STORAGE = "CREATE TABLE %s.%s (\n" +
|
" \"ELMNO\" INTEGER NOT NULL ENABLE,\n" +
|
" \"XLOW\" FLOAT NOT NULL ENABLE,\n" +
|
" \"YLOW\" FLOAT NOT NULL ENABLE,\n" +
|
" \"XHIGH\" FLOAT NOT NULL ENABLE,\n" +
|
" \"YHIGH\" FLOAT NOT NULL ENABLE,\n" +
|
" \"PROPS\" INTEGER NOT NULL ENABLE,\n" +
|
" \"TAG_LUFID\" NUMBER(10) NOT NULL ENABLE,\n" +
|
" \"TAG_SFSC\" NUMBER(5) NOT NULL ENABLE,\n" +
|
" \"TAG_SSTAT\" NUMBER(5) NOT NULL ENABLE,\n" +
|
" \"TAG_BCOMPID\" NUMBER(3) NOT NULL ENABLE,\n" +
|
" \"TAG_BRULENO\" NUMBER(3) NOT NULL ENABLE,\n" +
|
" \"TAG_SOCCID\" NUMBER(5) NOT NULL ENABLE,\n" +
|
" \"LAYERID\" NUMBER(5) NOT NULL ENABLE,\n" +
|
" \"ELEMENT\" \"" + UDT_SCHEMA + "\".\"RAWS\" NOT NULL ENABLE \n" +
|
" )";
|
|
protected static final String TAB_STORAGE2 = "CREATE TABLE %s.%s (\n" +
|
" \"ELMNO\" INTEGER NOT NULL ENABLE,\n" +
|
" \"XLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
|
" \"YLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
|
" \"XHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
|
" \"YHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
|
" \"EXTENTS\" MDSYS.SDO_GEOMETRY, \n" +
|
" \"PROPS\" INTEGER NOT NULL ENABLE,\n" +
|
" \"ID\" " + UDT_SCHEMA + ".OFMID NOT NULL ENABLE,\n" +
|
" \"LAYERID\" NUMBER(5) NOT NULL ENABLE,\n" +
|
" \"ELEMENT\" \"" + UDT_SCHEMA + "\".\"RAWS\" NOT NULL ENABLE \n" +
|
" )";
|
/**
|
* Trigger
|
*/
|
protected static final String TRG_SPACENODE = "CREATE OR REPLACE TRIGGER \"%s\".\"%s\"\n"
|
+ " AFTER DELETE OR INSERT OR UPDATE ON \"%s\".\"%s\"\n" + " BEGIN\n"
|
+ " UPDATE SPACENODES SET LASTUPDATE = SYSDATE\n" + " WHERE SNID = \"%d\";\n"
|
+ " END;";
|
|
protected static final String TRG_ELMINDEX =
|
"CREATE OR REPLACE TRIGGER \"%s\".\"%s\"\n"
|
+ " AFTER INSERT OR UPDATE OR DELETE ON \"%s\".\"%s\"\n"
|
+ " REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW\n"
|
+ " BEGIN\n"
|
+ " IF INSERTING THEN\n"
|
+ " INSERT INTO \"%s\".\"%s\" (ELMNO, TYPE, XLOW, YLOW, XHIGH, YHIGH,\n"
|
+ " UFID, FSC, COMPID, OCCID, SPACENAME)\n"
|
+ " VALUES (SD$ELEMENTNUMBER_SEQ.NEXTVAL, :new.ELMTYPE, :new.XLOW, :new.YLOW, :new.XHIGH, :new.YHIGH,\n"
|
+ " :new.UFID, :new.FSC, :new.COMPID, :new.OCCID, '%s');\n"
|
+ " ELSIF DELETING THEN\n"
|
+ " DELETE FROM \"%s\".\"%s\"\n"
|
+ " WHERE \"%s\".UFID = :old.UFID AND\n"
|
+ " \"%s\".FSC = :old.FSC AND\n"
|
+ " \"%s\".COMPID = :old.COMPID AND\n"
|
+ " \"%s\".OCCID = :old.OCCID;\n"
|
+ " ELSE\n" + " UPDATE \"%s\"\n"
|
+ " SET XLOW = :new.XLOW, YLOW = :new.YLOW, XHIGH = :new.XHIGH, YHIGH = :new.YHIGH\n"
|
+ " WHERE FSC = :new.FSC AND UFID = :new.UFID AND COMPID = :new.COMPID AND OCCID = :new.OCCID;\n"
|
+ " END IF;\n" + " END;";
|
|
/**
|
*
|
*/
|
protected static final String TAB_ELEMENTSET_PREFIX = "ELMSET_";
|
|
protected static final String STMT_CLEARCYCLEBIN = "PURGE RECYCLEBIN";
|
|
protected static final String SMTM_GRANTOBJECTTYPE = "GRANT EXECUTE ANY TYPE TO \"" + UDT_SCHEMA + "\"";
|
|
protected static final long TIMEOUT = Long.MAX_VALUE;
|
|
static
|
{
|
try
|
{
|
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
|
} catch (SQLException e)
|
{
|
Assert.shouldNeverReachHere(e.getMessage());
|
}
|
}
|
|
public static String getCurrentURL(String oracleHost, String oraclePort, String oracleInstance)
|
{
|
StringBuilder builder = new StringBuilder();
|
|
builder.append(ORACLE_URL);
|
builder.append(oracleHost);
|
builder.append(":");
|
builder.append(oraclePort);
|
builder.append(":");
|
builder.append(oracleInstance);
|
|
return builder.toString();
|
}
|
|
private String _oracleHost;
|
private String _oracleInstance;
|
private String _oraclePort;
|
private String _dataPath;
|
|
private OracleConnection oracleConnection = null;
|
private Properties properties;
|
|
private ElementDispatcher elementDispatcher;
|
|
private HashMap featuresContext = new HashMap();
|
private PessimisticMapWrapper txFeaturesContext;
|
|
public OracleConvertJobContext()
|
{
|
properties = new Properties();
|
elementDispatcher = createElementDispatcher();
|
txFeaturesContext = new PessimisticMapWrapper(featuresContext, sLogger);
|
|
}
|
|
private ElementDispatcher createElementDispatcher()
|
{
|
ElementDispatcher dispatcher = new ElementDispatcher();
|
dispatcher.addRule(new TypeCompIdDispatchableFilter("Conductor", new CreateLineStringStrategy(), 106, 0));
|
return dispatcher;
|
}
|
|
public void setLogin(String userName, String password)
|
{
|
properties.put(PROPUsrKey, userName);
|
properties.put(PROPPassKey, password);
|
}
|
|
public void setShapeData(String dataPath)
|
{
|
_dataPath = dataPath;
|
}
|
|
public OracleConnection getOracleConnection()
|
{
|
try
|
{
|
if (oracleConnection == null)
|
{
|
oracleConnection = (OracleConnection) DriverManager.getConnection(
|
getCurrentURL(_oracleHost, _oraclePort, _oracleInstance),
|
properties);
|
}
|
|
return oracleConnection;
|
} catch (SQLException e)
|
{
|
logger.warn(e.getMessage(), e);
|
}
|
|
oracleConnection = null;
|
|
return null;
|
}
|
|
public void closeConnection()
|
{
|
try
|
{
|
if (oracleConnection != null)
|
{
|
oracleConnection.close();
|
oracleConnection = null;
|
}
|
} catch (SQLException e)
|
{
|
logger.warn(e.getMessage(), e);
|
}
|
}
|
|
public void setConnectionInfo(String oracleHost, String oraclePort, String oracleInstance)
|
{
|
_oracleHost = oracleHost;
|
_oracleInstance = oracleInstance;
|
_oraclePort = oraclePort;
|
}
|
|
public void putFeatureCollection(Element element)
|
{
|
Feature feature = elementDispatcher.execute(element);
|
if (feature == null)
|
return;
|
|
if (!txFeaturesContext.containsKey(feature.getFeatureType()))
|
{
|
txFeaturesContext.put(feature.getFeatureType(), new ArrayList());
|
}
|
ArrayList arrayList = (ArrayList) txFeaturesContext.get(feature.getFeatureType());
|
arrayList.add(feature);
|
}
|
|
public void startTransaction()
|
{
|
txFeaturesContext.startTransaction();
|
}
|
|
public void commitTransaction()
|
{
|
txFeaturesContext.commitTransaction();
|
if (!featuresContext.isEmpty())
|
{
|
updateDataStore();
|
}
|
}
|
|
public void rollbackTransaction()
|
{
|
txFeaturesContext.rollbackTransaction();
|
if (!featuresContext.isEmpty())
|
{
|
updateDataStore();
|
}
|
}
|
|
private void updateDataStore()
|
{
|
// todo:
|
Iterator it = featuresContext.keySet().iterator();
|
|
try
|
{
|
while (it.hasNext())
|
{
|
FeatureType featureType = (FeatureType) it.next();
|
File sfile = new File(_dataPath + "\\" + featureType.getTypeName());
|
ShapefileDataStore shapefileDataStore = new ShapefileDataStore(sfile.toURL());
|
shapefileDataStore.createSchema(featureType);
|
FeatureWriter writer = shapefileDataStore.getFeatureWriter(featureType.getTypeName(), Transaction.AUTO_COMMIT);
|
ArrayList features = (ArrayList) featuresContext.get(featureType);
|
Iterator itFeature = features.iterator();
|
while (itFeature.hasNext())
|
{
|
Feature feature = (Feature) itFeature.next();
|
((SimpleFeature) writer.next()).setAttributes(feature.getAttributes(null));
|
}
|
writer.close();
|
}
|
featuresContext.clear();
|
} catch (MalformedURLException e)
|
{
|
logger.error(e.getMessage(), e);
|
} catch (IllegalAttributeException e)
|
{
|
logger.error(e.getMessage(), e);
|
} catch (IOException e)
|
{
|
logger.error(e.getMessage(), e);
|
}
|
|
}
|
|
}
|
