// 2012-10-04 johnpfeiffer requires StorageGateway
// TODO: modify columnlabels to be getString, better yet use an ORM
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 static final String DATABASENAME = "storagegatewaymanager";
private static final String STORAGEGATEWAYTABLENAME = "PstCloudGateway";
private static final String STORAGEGATEWAYEXTENDEDTABLENAME = "PstGateway";
private static final String HEADEROID = "oid";
private static final String HEADERADDRESS = "address";
private static final String HEADERCONNECTED = "connected";
private static final String HEADERHASH = "secureSessionHashId";
private static final String HEADERLASTMODIFIED = "lastModifiedTimestamp";
private static final String HEADERDELETED = "deleted";
private static final String HEADERSERIALNUMBER = "deviceSerialNumber";
private String resultOid;
private String resultAddress;
private short resultConnected;
private String resultHash;
private Timestamp resultLastModified;
private short resultDeleted;
private String resultSerialNumber;
ArrayList <StorageGateway> storageGatewayCoreList;
ArrayList <StorageGateway> storageGatewayExtendedList;
HashMap <String , StorageGateway> storageGatewayMap;
ArrayList <StorageGateway> joinedList;
StorageGatewayDAO( Connection c )
{
if( c != null )
{
try
{
int rowCount = 0;
getStorageGatewayCore( c );
rowCount = calculateRowCount( c , STORAGEGATEWAYTABLENAME );
System.out.println( "DEBUG: " + DATABASENAME + "." + STORAGEGATEWAYTABLENAME + " contains " + rowCount + " rows" );
getStorageGatewayExtended( c );
System.out.println( "DEBUG: " + DATABASENAME + "." + STORAGEGATEWAYEXTENDEDTABLENAME + " 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" );
}
}
// validation of populateMethod, not to generate the redundant storageGatewayList.size
private int calculateRowCount( Connection c , String table ) throws SQLException
{
int rowCount = -1;
PreparedStatement pstmt = null;
ResultSet rs = null;
if( c != null )
{
try
{
String query = "select count(*) from " + DATABASENAME + "." + table + ";";
pstmt = c.prepareStatement( query );
rs = pstmt.executeQuery();
while( rs.next() )
{
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" );
}
return rowCount;
}
protected ArrayList <StorageGateway> getStorageGatewayList()
{
return joinedList;
}
private void getStorageGatewayCore( Connection c ) throws SQLException
{
String query = "select * from " + DATABASENAME + "." + STORAGEGATEWAYTABLENAME + " ;";
System.out.println( "DEBUG: " + query );
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = c.prepareStatement( query );
rs = pstmt.executeQuery();
this.storageGatewayCoreList = new ArrayList <StorageGateway>();
while( rs.next() )
{
resultOid = rs.getString( HEADEROID );
resultAddress = rs.getString( HEADERADDRESS );
resultConnected = rs.getShort( HEADERCONNECTED );
resultHash = rs.getString( HEADERHASH );
resultLastModified = rs.getTimestamp( HEADERLASTMODIFIED );
resultDeleted = rs.getShort( HEADERDELETED );
resultSerialNumber = rs.getString( HEADERSERIALNUMBER );
storageGatewayCoreList.add( new StorageGateway( resultOid , resultHash , resultAddress , resultConnected , resultLastModified , resultDeleted ,
resultSerialNumber ) );
}
}finally
{
if( pstmt != null )
{
pstmt.close();
}
if( rs != null )
{
rs.close();
}
}
}
// HashMap is not thread safe
private void getStorageGatewayExtended( Connection c ) throws SQLException
{
this.storageGatewayMap = new HashMap <String , StorageGateway>(); // not thread safe
String query = "select * from " + DATABASENAME + "." + STORAGEGATEWAYEXTENDEDTABLENAME + " ;";
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.storageGatewayCoreList != null && this.storageGatewayMap != null )
{
ListIterator <StorageGateway> it = this.storageGatewayCoreList.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