//2012-05-14 johnpfeiffer requires StorageGateway
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.ListIterator;
public class StorageGatewayDAO
{
private String databaseName;
private String tableName;
private int rowCount;
ArrayList <StorageGateway> storageGatewayList;
HashMap <String , StorageGateway> storageGatewayMap;
ArrayList <StorageGateway> joinedList;
StorageGatewayDAO( Connection c )
{
if( c != null)
{
this.databaseName = "storagegatewaymanager";
this.tableName = "PstCloudGateway";
try{
populateStorageGatewayList( c );
calculateRowCount( c );
System.out.println( "DEBUG: " + databaseName + "." + tableName + " contains " + rowCount + " rows");
populateStorageGatewayExtendedAttributes( c );
calculateRowCount( c );
System.out.println( "DEBUG: " + databaseName + "." + tableName + " contains " + rowCount + " rows");
joinStorageGateways();
}catch( SQLException sqle )
{ System.err.println( sqle.getMessage() );
}
catch( Exception e )
{ e.printStackTrace();
}
}else
{ throw new IllegalArgumentException( "database, tablename, or connection is null or empty" );
}
}
protected String getDatabaseName()
{ return databaseName;
}
protected String getTableName()
{ return tableName;
}
protected int getRowCount()
{ return rowCount; //storageGatewayList.size?
}
protected ArrayList <StorageGateway> getStorageGatewayList()
{ return joinedList;
}
//validation of populateMethod, not to generate the redundant storageGatewayList.size
private void calculateRowCount( Connection c ) throws SQLException
{
PreparedStatement pstmt = null;
ResultSet rs = null;
if( c != null )
{
try{
String query = "select count(*) from " + databaseName + "." + tableName + ";";
pstmt = c.prepareStatement( query );
rs = pstmt.executeQuery();
while( rs.next() )
{ this.rowCount = rs.getInt( 1 ) ; //select count(*) only returns one integer
}
}finally
{ if( pstmt != null ){ pstmt.close(); }
if( rs != null ){ rs.close(); }
}
}else
{ throw new IllegalArgumentException( "Connection cannot be null" );
}
}
private void populateStorageGatewayList( Connection c ) throws SQLException
{
this.storageGatewayList = new ArrayList <StorageGateway> ();
String query = "select * from " + databaseName + "." + tableName + " ;";
System.out.println( "DEBUG: " + query );
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
pstmt = c.prepareStatement( query );
rs = pstmt.executeQuery();
ResultSetMetaData rsmeta = rs.getMetaData();
int columnCount = rsmeta.getColumnCount();
while( rs.next() )
{
String oid = null;
String address = null;
short connected = -1;
String hash = null;
Timestamp lastModified = null;
short deleted = -1;
String deviceSerialNumber = null;
for ( int i = 1 ; i <= columnCount; i++ ) //column counting starts at 1
{
if( rs.getObject( i ) != null )
{
if( rsmeta.getColumnLabel( i ).equals( "oid" ) )
{ oid = rs.getString( i ).toLowerCase(); //defensive programming as oid is not supposed to be case sensitive
}
if( rsmeta.getColumnLabel( i ).equals( "address" ) )
{ address = rs.getString( i ).toLowerCase();
}
if( rsmeta.getColumnLabel( i ).equals( "connected" ) )
{ connected = rs.getShort( i );
}
if( rsmeta.getColumnLabel( i ).equals( "secureSessionHashId" ) )
{ hash = rs.getString( i ).toLowerCase();
}
if( rsmeta.getColumnLabel( i ).equals( "lastModifiedTimestamp" ) )
{ lastModified = rs.getTimestamp( i );
}
if( rsmeta.getColumnLabel( i ).equals( "deleted" ) )
{ deleted = rs.getShort( i );
}
if( rsmeta.getColumnLabel( i ).equals( "deviceSerialNumber" ) )
{ deviceSerialNumber = rs.getString( i ).toLowerCase();
}
}
}
storageGatewayList.add( new StorageGateway( oid , hash , address , connected , lastModified , deleted , deviceSerialNumber ) );
} //end while
}finally
{ if( pstmt != null ){ pstmt.close(); }
if( rs != null ){ rs.close(); }
}
}
//HashMap is not thread safe
private void populateStorageGatewayExtendedAttributes( Connection c ) throws SQLException
{
this.databaseName = "storagemanager";
this.tableName = "PstGateway";
this.storageGatewayMap = new HashMap <String,StorageGateway>(); //not thread safe
String query = "select * from " + databaseName + "." + tableName + " ;";
System.out.println( "DEBUG: " + query );
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
pstmt = c.prepareStatement( query );
rs = pstmt.executeQuery();
ResultSetMetaData rsmeta = rs.getMetaData();
int columnCount = rsmeta.getColumnCount();
while( rs.next() )
{
String oid = null;
String hash = null;
String deviceType = null;
String networkAccountOid = null;
int storageSizeInMB = -1;
short privateGateway = -1;
Timestamp creationDate = null;
short deleted = -1;
String deviceSerialNumber = null;
for ( int i = 1 ; i <= columnCount; i++ ) //column counting starts at 1
{
if( rs.getObject( i ) != null )
{
if( rsmeta.getColumnLabel( i ).equals( "oid" ) )
{ oid = rs.getString( i ).toLowerCase();
}
if( rsmeta.getColumnLabel( i ).equals( "secureSessionHashId" ) )
{ hash = rs.getString( i ).toLowerCase();
}
if( rsmeta.getColumnLabel( i ).equals( "deviceType" ) )
{ deviceType = rs.getString( i ).toUpperCase();
}
if( rsmeta.getColumnLabel( i ).equals( "networkAccountOid" ) )
{ networkAccountOid = rs.getString( i );
}
if( rsmeta.getColumnLabel( i ).equals( "storageSizeInMB" ) )
{ storageSizeInMB = rs.getInt( i );
}
if( rsmeta.getColumnLabel( i ).equals( "privateGateway" ) )
{ privateGateway = rs.getShort( i );
}
if( rsmeta.getColumnLabel( i ).equals( "lastModifiedTimestamp" ) )
{ creationDate = rs.getTimestamp( i );
}
if( rsmeta.getColumnLabel( i ).equals( "deleted" ) )
{ deleted = rs.getShort( i );
}
if( rsmeta.getColumnLabel( i ).equals( "deviceSerialNumber" ) )
{ deviceSerialNumber = rs.getString( i ).toLowerCase();
}
}
}
StorageGateway temp = new StorageGateway( oid , hash , deviceType , networkAccountOid , storageSizeInMB , privateGateway , creationDate , deleted , deviceSerialNumber );
storageGatewayMap.put( deviceSerialNumber , temp );
} //end while
}finally
{ if( pstmt != null ){ pstmt.close(); }
if( rs != null ){ rs.close(); }
}
}
private void joinStorageGateways()
{
System.out.println( "DEBUG: starting join" );
joinedList = new ArrayList <StorageGateway> ();
if( this.storageGatewayList != null && this.storageGatewayMap != null )
{
ListIterator <StorageGateway> it = this.storageGatewayList.listIterator();
while( it.hasNext () )
{
String oid = null;
String address = null;
short connected = -1;
String hash = null;
Timestamp lastModified = null;
String deviceType = null;
String networkAccountOid = null;
int storageSizeInMB = -1;
short privateGateway = -1;
Timestamp creationDate = null;
String sanityCheckHash = null;
short deleted = -1;
String deviceSerialNumber = null;
StorageGateway temp = it.next();
deviceSerialNumber = temp.getDeviceSerialNumber();
hash = temp.getHash();
if( deviceSerialNumber != null )
{
StorageGateway extended = this.storageGatewayMap.get( deviceSerialNumber );
if( extended != null )
{
sanityCheckHash = extended.getHash();
if( hash.equals( sanityCheckHash) ) // System.out.println( "DEBUG: " + hash + " = " + sanityCheckHash );
{
oid = temp.getOid();
address = temp.getAddress();
connected = temp.getConnected();
lastModified = temp.getLastModified();
deviceType = extended.getDeviceType();
networkAccountOid = extended.getNetworkAccountOid();
storageSizeInMB = extended.getStorageSizeInMB();
privateGateway = extended.getPrivateGateway();
creationDate = extended.getCreationDate();
deleted = extended.getDeleted();
StorageGateway joined = new StorageGateway( oid , hash , address , connected , lastModified , deviceType , networkAccountOid , storageSizeInMB , privateGateway , creationDate , deleted , deviceSerialNumber );
joinedList.add( joined );
}else
{ System.err.println("ERROR: hash mismatch for same oid, should throw exception" );
}
}else
{ System.err.println("ERROR: map does not contain corresponding extended attributes for this oid " + oid );
}
}else
{ System.err.println( "ERROR: oid should never be null!" );
}
} //end while
}else
{ System.err.println( "ERROR: The storagegateway List or Map is null" );
}
}
} // end class