/** find a layer seq number for appending at the end (ie, larger than any existing) */
public int newSeqNo(CommanderConnection conn, int serviceId)
throws Exception // FIXME: can we be more specific please?
{
log.info("Table_ServiceLayer::newSeqNo( dbc, serviceId=" + serviceId + " )");
Statement stmt = conn.createStatement();
ResultSet rs;
rs =
stmt.executeQuery(
"SELECT max(layerseq)+1"
+ " from "
+ Globals.TABLE_SERVICELAYER
+ " where "
+ Globals.TABLE_SERVICELAYER_SERVICEID
+ "="
+ serviceId);
stmt.setFetchSize(0); // Turn cursor off
int layerseq;
if (rs == null) layerseq = 1;
else {
rs.next();
layerseq = rs.getInt(1);
}
// if it exceeds the max allowed int value, raise an exception
if (layerseq > MAX_LAYERSEQ) {
Exception e =
new Exception("Error: layer sequencing overflow. Please contact your administrator.");
throw e;
}
log.info("Table_ServiceLayer::newSeqNo() -> " + layerseq);
return layerseq;
}
/**
* fetch all layers pertaining to a particular service, store result locally as a JDBC ResultList
* precondition: JDBC connection opened
*
* @param conn JDBC connection
* @throws SQLException
*/
public void fetchTuplesByServiceId(CommanderConnection conn, int sId) throws SQLException {
log.info("Table_ServiceLayer::fetchTuplesByServiceId()");
// execute query
String query =
"select "
+ Globals.TABLE_SERVICELAYER_SERVICEID
+ ", "
+ Globals.TABLE_SERVICELAYER_LAYERID
+ ", "
+ Globals.TABLE_SERVICELAYER_LAYERSEQ
+ " from "
+ Globals.TABLE_SERVICELAYER
+ " where "
+ Globals.TABLE_SERVICELAYER
+ "."
+ Globals.TABLE_SERVICELAYER_SERVICEID
+ " = "
+ sId
+ " order by "
+ Globals.TABLE_SERVICELAYER
+ "."
+ Globals.TABLE_SERVICELAYER_LAYERSEQ;
log.debug("want to execute: " + query);
Statement stmt = conn.createStatement();
stmt.setFetchSize(0); // Turn cursor off
resultSet = stmt.executeQuery(query);
log.info("Table_ServiceLayer::fetchTuplesByServiceId()");
}
/**
* fetch one tuple by its (service,layer) id, keep in local result set precondition: JDBC
* connection opened
*
* @param conn JDBC connection
* @param id service tuple id
* @throws SQLException, InvalidInputException
*/
public void fetchTupleById(CommanderConnection conn, int serviceId, int layerId)
throws SQLException {
log.info("Table_ServiceLayer::fetchTupleById()");
// execute query
String query =
"select "
+ Globals.TABLE_SERVICELAYER_SERVICEID
+ ", "
+ Globals.TABLE_SERVICELAYER_LAYERID
+ ", "
+ Globals.TABLE_SERVICELAYER_LAYERSEQ
+ " from "
+ Globals.TABLE_SERVICELAYER
+ " where "
+ Globals.TABLE_SERVICELAYER_SERVICEID
+ " = "
+ serviceId
+ " and "
+ Globals.TABLE_SERVICELAYER_LAYERID
+ " = "
+ layerId;
log.debug("want to execute: " + query);
Statement stmt = conn.createStatement();
resultSet = stmt.executeQuery(query);
log.info("Table_ServiceLayer::fetchTupleById()");
}
/**
* delete tuple given by its layer id precondition: JDBC connection opened
*
* @param conn JDBC connection
* @param layerId
* @throws SQLException
*/
public void deleteTuplesByLayerId(CommanderConnection conn, int layerId)
throws SQLException, ConnectionFailedException {
log.info("Table_ServiceLayer::deleteTuplesByLayerId()");
// --- plausi checks ----------------------------------
// - connection object
if (conn == null) {
log.info("Table_ServiceLayer::deleteTuplesByLayerId() -- Error: no database connection.");
throw new ConnectionFailedException("no database connection.");
}
// --- action -----------------------------------------
String query =
"delete from "
+ Globals.TABLE_SERVICELAYER
+ " where "
+ Globals.TABLE_SERVICELAYER_LAYERID
+ " = "
+ layerId;
log.debug("want to execute: " + query);
Statement stmt = conn.createStatement();
stmt.executeUpdate(query);
log.info("Table_ServiceLayer::deleteTuplesByLayerId() -- ok");
}
/**
* insert one tuple; instance's local attribute set is not modified. precondition: JDBC connection
* opened all strings non-null
*
* @param conn JDBC connection
* @param id service tuple id
* @throws SQLException
*/
public void insertTuple(CommanderConnection conn, int serviceId, int layerId, int layerSeq)
throws SQLException, ConnectionFailedException, InvalidInputException {
log.info("Table_ServiceLayer::insertTuple()");
// --- plausi checks ----------------------------------
// - connection object
if (conn == null) {
log.info("Table_ServiceLayer::insertTuple() -- Error: no database connection.");
throw new ConnectionFailedException("no database connection.");
}
// --- action -----------------------------------------
// make VERY sure that attribute names and values are listed in the same sequence!
String query =
"insert into "
+ Globals.TABLE_SERVICELAYER
+ " ( "
+ Globals.TABLE_SERVICELAYER_SERVICEID
+ ", "
+ Globals.TABLE_SERVICELAYER_LAYERID
+ ", "
+ Globals.TABLE_SERVICELAYER_LAYERSEQ
+ ") "
+ " values( "
+ serviceId
+ ", "
+ layerId
+ ", "
+ layerSeq
+ ")";
log.debug("want to execute: " + query);
Statement stmt = conn.createStatement();
stmt.executeUpdate(query);
log.info("Table_ServiceLayer::insertTuple()");
return;
}
public static void main(String[] args) {
// --- start action -----------------------------------
System.out.println("START class Table_ServiceLayer component test");
// --- cmd line parameter check ------------------------
if (args.length != 6) {
System.err.println("Usage: Table_ServiceLayer msgFile url user password jdbc debugLevel");
return;
}
String msgFile = args[0];
String url = args[1];
String usr = args[2];
String pwd = args[3];
String jdbc = args[4];
System.out.println(
"Parameters: msgFile="
+ msgFile
+ ", url="
+ url
+ ", user="******", password="******", jdbc="
+ jdbc);
try {
// --- test preparation -------------------------------
CommanderConnection c = new CommanderConnection(url, usr, pwd, jdbc);
// - set up service tuples
Table_Service myService1 = new Table_Service();
int srv1 =
myService1.insertTuple(
c,
UPDATESEQUENCE1,
AVAILABILITY1,
NAME1,
TITLE1,
ABSTRACT1,
KEYWORDS1,
FEES1,
ACCESSCONSTRAINTS1,
HOSTNAME1,
PORT1,
PATH1,
FORMATS1,
BASELAYERNAME1,
VENDORCAPABILITIES1,
CONTACTPERSON1,
CONTACTORGANIZATION1,
ADDRESSTYPE1,
ADDRESS1,
CITY1,
STATE1,
POSTCODE1,
COUNTRY1,
TEL1,
FAX1,
EMAIL1);
System.out.println("insert service #1 -> " + srv1);
Table_Service myService2 = new Table_Service();
int srv2 =
myService2.insertTuple(
c,
UPDATESEQUENCE2,
AVAILABILITY2,
NAME2,
TITLE2,
ABSTRACT2,
KEYWORDS2,
FEES2,
ACCESSCONSTRAINTS2,
HOSTNAME2,
PORT2,
PATH2,
FORMATS2,
BASELAYERNAME2,
VENDORCAPABILITIES2,
CONTACTPERSON2,
CONTACTORGANIZATION2,
ADDRESSTYPE2,
ADDRESS2,
CITY2,
STATE2,
POSTCODE2,
COUNTRY2,
TEL2,
FAX2,
EMAIL2);
System.out.println("insert service #2 -> " + srv2);
// - set up layer tuples
Table_Layer myLayer1 = new Table_Layer();
int lay1 =
myLayer1.insertTuple(
c,
LNAME1,
LTITLE1,
SRS1,
AUTHORITY1,
LATLONXMIN1,
LATLONXMAX1,
LATLONYMIN1,
LATLONYMAX1,
BBOXXMIN1,
BBOXXMAX1,
BBOXYMIN1,
BBOXYMAX1,
ATTRIBUTIONURL1,
ATTRIBUTIONTITLE1,
LOGOWIDTH1,
LOGOHEIGHT1,
LOGOFORMAT1,
LOGOURL1,
FEATUREURL1,
RESOLUTION1,
MAPTYPE1);
System.out.println("insert layer #1 -> " + lay1);
Table_Layer myLayer2 = new Table_Layer();
int lay2 =
myLayer2.insertTuple(
c,
LNAME2,
LTITLE2,
SRS2,
AUTHORITY2,
LATLONXMIN2,
LATLONXMAX2,
LATLONYMIN2,
LATLONYMAX2,
BBOXXMIN2,
BBOXXMAX2,
BBOXYMIN2,
BBOXYMAX2,
ATTRIBUTIONURL2,
ATTRIBUTIONTITLE2,
LOGOWIDTH2,
LOGOHEIGHT2,
LOGOFORMAT2,
LOGOURL2,
FEATUREURL2,
RESOLUTION2,
MAPTYPE2);
System.out.println("insert layer #2 -> " + lay2);
// --- test -------------------------------------------
// - create a reference instance
Table_ServiceLayer myServiceLayer1 = new Table_ServiceLayer();
myServiceLayer1.insertTuple(c, srv1, lay1, SEQ1);
System.out.println("insert Table_ServiceLayer tuple #1 with seq=" + SEQ1);
// - update reference instance
myServiceLayer1.updateTuple(c, srv1, lay1, SEQ2);
// - fetch values via 2nd instance, check all values
Table_ServiceLayer myServiceLayer2 = new Table_ServiceLayer();
myServiceLayer2.fetchTupleById(c, srv1, lay1);
if (myServiceLayer2.getServiceId() != srv1)
System.out.println(
"Error: attribute updated != attributed fetched: "
+ Globals.TABLE_SERVICELAYER_SERVICEID);
if (myServiceLayer2.getLayerId() != lay1)
System.out.println(
"Error: attribute updated != attributed fetched: "
+ Globals.TABLE_SERVICELAYER_LAYERID);
if (myServiceLayer2.getLayerSeq() != SEQ2)
System.out.println(
"Error: attribute updated != attributed fetched: "
+ Globals.TABLE_SERVICELAYER_LAYERID);
// - delete instance 1 and check it's gone
myServiceLayer1.deleteTuple(c, srv1, lay1);
try {
myServiceLayer2.fetchTupleById(c, srv1, lay1);
System.out.println("Error: deleted tuple still seems to exist.");
} catch (Exception e) {
System.out.println("fetching tuple deleted caused exception, that's ok.");
}
// - create second instance
myServiceLayer2.insertTuple(c, srv1, lay2, SEQ1);
System.out.println("insert Table_ServiceLayer tuple #1 with seq=" + SEQ1);
// --- test cleanup -----------------------------------
c.abort();
c.close();
} catch (Exception e) {
System.out.println("Error: caught exception: " + e.getMessage());
}
System.out.println("END class Table_ServiceLayer component test");
}
