/**
* Copyright 2007 ATG DUST Project Licensed under the Apache License, Version
* 2.0 (the "License"); you may not use this file except in compliance with the
* License. You may obtain a copy of the License at
* http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law
* or agreed to in writing, software distributed under the License is
* distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the specific language
* governing permissions and limitations under the License.
*/
package atg.adapter.gsa;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;
import javax.sql.DataSource;
import atg.core.util.StringUtils;
import atg.dtm.TransactionDemarcation;
import atg.dtm.TransactionDemarcationException;
import atg.nucleus.GenericService;
import atg.nucleus.ServiceException;
/**
* This class is designed to assist with database table manipulation such as
* adding tables. Parts copied from atg.service.idgen.?? by mgk
*
* @author mf
* @version 1.0
**/
public class SQLProcessorEngine extends GenericService {
// Vendor String for Apache Derby
public static final String APACHE_DERBY = "Apache Derby";
/* =========== CONSTRUCTORS ============= */
/**
* empty constructor
*/
public SQLProcessorEngine() {
};
/**
* Construct a generator
**/
public SQLProcessorEngine(GSARepository pRep) {
setRepository(pRep);
mDataSource = getRepository().getDataSource();
}
// ---------- Property: DataSource ----------
/**
* DataSource from which to get DB connections this property is NOT a public
* property because it is extracted from the repository property
*/
DataSource mDataSource;
private void setDataSource(DataSource pDataSource) {
mDataSource = pDataSource;
}
private DataSource getDataSource() {
return mDataSource;
}
/**
* GSARespository from which to get the DataSource and TransactionManager
*/
GSARepository mRepository;
public void setRepository(GSARepository pRep) {
mRepository = pRep;
}
public GSARepository getRepository() {
return mRepository;
}
/**
* String executed to determine whether a table exists. The table name is
* appended to the end of the string before execution occurs.
*/
String mDetermineTableExistsSQL = "SELECT count(*) from";
public void setDetermineTableExistsSQL(String pStr) {
mDetermineTableExistsSQL = pStr;
}
public String getDetermineTableExistsSQL() {
return mDetermineTableExistsSQL;
}
/**
* String executed to drop a table. The table name is appended to the end of
* the string before execution
*/
String mDropTableSQL = "DROP TABLE";
public void setDropTableSQL(String pStr) {
mDropTableSQL = pStr;
}
public String getDropTableSQL() {
return mDropTableSQL;
}
/**
* String delimiter used to separate the large String passed to createTables()
* into an array of individual Create Table statements default value is
* "CREATE TABLE" This delimiter _will_ be included in the final create
* statements
*/
String mCreateTableBeginDelimiter = "CREATE TABLE";
public void setCreateTableBeginDelimiter(String pStr) {
mCreateTableBeginDelimiter = pStr;
}
public String getCreateTableBeginDelimiter() {
return mCreateTableBeginDelimiter;
}
/**
* String delimiter used to separate the large String passed to createTables()
* into an array of individual Create Table statements default value is ";"
* This delimiter _will not_ be included in the final create statements
*/
String mCreateTableEndDelimiter = ";";
public void setCreateTableEndDelimiter(String pStr) {
mCreateTableEndDelimiter = pStr;
}
public String getCreateTableEndDelimiter() {
return mCreateTableEndDelimiter;
}
// -------------------------------------
/**
* method to execute when starting this component
*/
public void doStartService() throws ServiceException {
if (getRepository() == null)
throw new ServiceException("Repository property is null.");
setDataSource(getRepository().getDataSource());
}
/**
* Get a DB connection
*
* @return the connection
* @exception SQLProcessorException
* if there is DB trouble or DataSource trouble
**/
Connection getConnection() throws SQLProcessorException {
try {
DataSource ds = getDataSource();
if (ds == null)
throw new SQLProcessorException("no DataSource");
else
return ds.getConnection();
} catch (SQLException sqle) {
if (isLoggingDebug()) {
SQLException next = sqle;
while (next != null) {
logDebug(next);
next = next.getNextException();
}
}
throw new SQLProcessorException(sqle);
}
}
// -------------------------------------
/**
* Close a DB connection, logging any SQLExceptions. It is okay to pass a null
* connection here
*
* @param pConnection
* connection to close, may be null
**/
private final void close(Connection pConnection) {
if (pConnection != null) {
try {
pConnection.close();
} catch (SQLException sqle) {
if (isLoggingError())
logError(sqle);
}
}
}
// -------------------------------------
/**
* Close a result set, logging any SQLExceptions. It is okay to pass a null
* here
*
* @param pResultSet
* result set to close, may be null
**/
// private final void close(ResultSet pResultSet)
// {
// if (pResultSet != null)
// {
// try
// {
// pResultSet.close();
// }
// catch (SQLException sqle)
// {
// if (isLoggingError())
// logError(sqle);
// }
// }
// }
// -------------------------------------
/**
* Close a statement, logging any SQLExceptions. It is okay to pass a null
* here.
*
* @param pStatement
* statement to close, may be null
**/
private final void close(Statement pStatement) {
if (pStatement != null) {
try {
pStatement.close();
} catch (SQLException sqle) {
if (isLoggingError())
logError(sqle);
}
}
}
// -------------------------------------
/**
* Perform the specified SQL statement in a new transaction which is commited.
*
* @param pSQL
* SQL to execute
* @return the # of rows affected
* @exception SQLProcessorException
* if there is DB or xact trouble
**/
private int performSQL(String pSQL) throws SQLProcessorException {
TransactionDemarcation td = new TransactionDemarcation();
SQLProcessorException error = null;
int rows = 0;
try {
td.begin(mRepository.getTransactionManager(),
TransactionDemarcation.REQUIRES_NEW);
Connection c = null;
Statement s = null;
try {
// get DB connection
c = getConnection();
/*
* * most of this method is annoying try/catch/finally blocks* inflicted
* on us by JTA. the real work is here.
*/
s = c.createStatement();
// rows = s.executeUpdate(pSQL);
s.execute(pSQL);
} catch (SQLException sqle) {
error = new SQLProcessorException(sqle);
} finally {
close(s);
close(c);
}
} catch (TransactionDemarcationException e1) {
if (error == null)
error = new SQLProcessorException(e1);
else if (isLoggingError())
logError(e1);
} finally {
try {
td.end();
} catch (TransactionDemarcationException e2) {
if (error == null)
error = new SQLProcessorException(e2);
else if (isLoggingError())
logError(e2);
}
}
if (error != null)
throw error;
else
return rows;
}
/**
* This method is used to create tables in a database. It takes a String that
* contains all of the table creation statements and is of the format: CREATE
* TABLE foo ( <field specifications> ); ... CREATE TABLE bar ( <field
* specifications> ); Specifically, this is the format output by the GSA when
* a call is made to generateSQL(); Before making the tables, this large
* String will be split apart into an array of individual CREATE TABLE
* statements using the createTableBeginDelimiter and createTableEndDelimiter
* properties. By default, createTableBeginDelimiter = "CREATE TABLE" and
* createTableEndDelimiter = ";"
*
* @param String
* pStr - the String containing the CREATE TABLE statements
* @param boolean pDrop - indicates whether to drop tables and recreate them
* if the tables already exist in the database
* @return boolean true if any tables were created ( or dropped and created )
* @exception SQLProcessorException
* if an error occurs trying to create the tables
*/
public boolean createTables(List<String> pStatements, boolean pDrop)
throws SQLProcessorException {
boolean createdTables = false;
// get the create statements to execute and make sure they are
// in the proper order with regard to 'references' clauses
if (isLoggingDebug())
logDebug("Reordering CREATE TABLE statements so references don't fail...");
List<String> statements = reorderCreateStatements(pStatements);
// if dropping tables, do that before trying to create them
// throws exception if all tables can't be dropped
List<String> tableNames = getTableNames(statements);
if (pDrop) {
if (isLoggingInfo())
logInfo("Dropping tables...");
dropTables(tableNames);
}
// we can assume that if a table still exists it is because we
// didn't try to drop it. If we did try to drop it but couldn't,
// dropTables would throw an exception and this code would never
// be executed
if (isLoggingInfo())
logInfo("Creating tables...");
Iterator<String> iter = statements.iterator();
while (iter.hasNext()) {
String statement = iter.next();
String name = getTableName(statement);
boolean exists = tableExists(name);
if (name != null && !exists) {
if (isLoggingDebug())
logDebug("Creating table: " + name);
if (this.getRepository() instanceof InitializingGSA) {
if (!isLoggingDebug()
&& ((InitializingGSA) this.getRepository())
.isLoggingCreateTables())
logDebug(statement);
}
if (this.getRepository() instanceof InitializingVersionRepository) {
if (!isLoggingDebug()
&& ((InitializingVersionRepository) this.getRepository())
.isLoggingCreateTables())
logDebug(statement);
}
if (isDerby())
statement = stripNull(statement);
createTable(statement);
createdTables = true;
} else if (name != null && !pDrop) {
if (isLoggingInfo())
logInfo("Table already exists and dropTablesIfExist is false - not creating: "
+ name);
// dropExistingTables must be false or else table would have been
// dropped
} else {
// throw new SQLProcessorException("The table " + name +
// " was not created because name was null or table couldn't be dropped.");
logWarning("The table "
+ name
+ " was not created because name was null or table couldn't be dropped.");
}
}
return createdTables;
}
/**
* Removes any occurrence of the string "NULL" from a create statement if it
* is not preceded by the word "NOT".
*
* @param statement
* @return
*/
private String stripNull(String statement) {
// first make this all uppercase
StringBuffer subStatements = new StringBuffer();
String tempStatement = statement.toUpperCase();
StringTokenizer st = new StringTokenizer(tempStatement, ",");
while (st.hasMoreTokens()) {
String tok = st.nextToken();
int notNullIndex = tok.indexOf("NOT NULL");
if (notNullIndex > -1) {
// safe to return this unmodified
subStatements.append(tok + ",\n");
} else if (tok.indexOf("NULL") > -1) {
// need to strip this one.
// we assume that we can just remove the five characters above
String temp = StringUtils.replace(tok, "NULL", "");
// we also have to remove all the trailing spaces
subStatements.append(temp.trim() + ",\n");
} else {
// safe to return. no null at all.
if (st.hasMoreTokens())
subStatements.append(tok + ",\n");
else
// End of statement, so no comma
subStatements.append(tok);
}
}
return subStatements.toString();
}
private boolean mIsDerby = false;
private boolean mIsDerbySet = false;
/**
* Returns true if the current database is Apache Derby. The first invocation
* to this method will cache its answer.
*/
public boolean isDerby() throws SQLProcessorException {
if (!mIsDerbySet) {
mIsDerby = isDerbyUncached();
mIsDerbySet = true;
}
return mIsDerby;
}
/**
* Returns true if the current database is Apache Derby This method call is
* not cached and will make a database connection attempt on each invocation.
*
* @return
* @throws SQLProcessorException
*/
private boolean isDerbyUncached() throws SQLProcessorException {
boolean isDerby = false;
Connection c = null;
try {
c = getConnection();
DatabaseMetaData meta = c.getMetaData();
if (APACHE_DERBY.equals(meta.getDatabaseProductName())) {
isDerby = true;
}
return isDerby;
} catch (SQLException e) {
throw new SQLProcessorException(e);
} finally {
if (c != null) {
try {
c.close();
} catch (SQLException e) {
; // eat it
}
}
}
}
/**
* This is a method that is used to execute a 'CREATE TABLE' call. The String
* you pass in is expected to be of the format CREATE TABLE ( ..... )
*
* @return void
* @exception SQLProcessorException
* thrown if an error occurs creating the table
*/
private void createTable(String pStr) throws SQLProcessorException {
try {
performSQL(pStr);
} catch (SQLProcessorException spe) {
throw new SQLProcessorException(
"Caught exception executing create table statement \"" + pStr + "\"",
spe);
}
}
/**
* This method is used to iteratively drop tables. The iterative effort is
* necessary because tables may have references. ASSUMPTION: references only
* exist for tables that are defined within this repository. If references
* exist from tables outside this repository, this method will throw a
* SQLProcessorException
*
* @param Vector
* of CREATE TABLE statements indicating which tables to drop
* @exception SQLProcessorException
* thrown if all tables can not be dropped
*/
public void dropTablesFromCreateStatements(List<String> pCreateStatements)
throws SQLProcessorException {
List<String> names = getTableNames(pCreateStatements);
dropTables(names);
}
/**
* This method is used to iteratively drop tables. The iterative effort is
* necessary because tables may have references. ASSUMPTION: references only
* exist for tables that are defined within this repository. If references
* exist from tables outside this repository, this method will throw a
* SQLProcessorException
*
* @param Vector
* of names of tables to be dropped
* @exception SQLProcessorException
* thrown if all tables can not be dropped
*/
private void dropTables(List<String> pNames) throws SQLProcessorException {
// assuming only one table can be dropped each time, this should take
// at most n iterations where n is the nbr of tables being dropped
int maxIterations = pNames.size();
// every table is tried at least once
List<String> tablesToDrop = pNames;
List<String> remainingTables;
int attempt = 0;
do {
remainingTables = new ArrayList<String>();
Iterator<String> tables = tablesToDrop.iterator();
while (tables.hasNext()) {
String table = tables.next();
if (tableExists(table)) {
try {
logInfo("Attempting to drop table: " + table);
dropTable(table);
logInfo("Dropped table: " + table);
} catch (SQLProcessorException spe) {
// if this is the last iteration, throw an exception
if (attempt + 1 >= maxIterations)
throw spe;
// otherwise track this table for the next try
remainingTables.add(table);
}
}
}
tablesToDrop = remainingTables;
} while ((attempt++ < maxIterations) && (!remainingTables.isEmpty()));
}
/**
* This is a method that is used to execute a 'Drop Table' call. The method
* creates the drop table statement by appending the name passed as a method
* with the SQL that has been set as the dropTableSQL property. By default,
* this property is set to "Drop table"
*
* @param String
* - the name of the table to drop
* @exception SQLProcessorException
* thrown if an error occurs trying to drop the table
*/
private void dropTable(String pName) throws SQLProcessorException {
String sql = getDropTableSQL() + " " + pName;
try {
logDebug("Attempting to drop table: " + pName);
performSQL(sql);
} catch (SQLProcessorException spe) {
throw new SQLProcessorException(
"Caught exception executing drop table statement \"" + sql + "\"",
spe);
}
}
/**
* This method is used to extract table names from a Vector of CREATE
* statements returned by either a call to getCreateStatements() or
* getOrderedCreateStatements()
*
* @return Vector of table names
*/
private List<String> getTableNames(List<String> pStatements) {
if (isLoggingDebug())
logDebug("Getting table names...");
List<String> names = new ArrayList<String>();
// split the big string into a bunch of create table statements
List<String> createStatements = pStatements;
// now get the table name from each statement
Iterator<String> iter = createStatements.iterator();
while (iter.hasNext()) {
String thisName = getTableName(iter.next());
if (thisName != null && !names.contains(thisName)) {
names.add(thisName);
if (isLoggingDebug())
logDebug("Found table name: " + thisName);
}
}
return names;
}
/**
* This is a method used to extract the table name from a CREATE TABLE
* statement. It operates by finding the createTableBeginDelimiter and
* extracting the next word after the delimiter.
*
* @param String
* - the create table statement
* @return String - the name of the table; null if name can't be found
*/
private String getTableName(String pStr) {
String STATEMENT_BEGIN = getCreateTableBeginDelimiter();
if (isLoggingDebug() && (this.getRepository().getDebugLevel() > 6)) {
logDebug("Extracting table name from create table statement: " + pStr);
logDebug("Name is taken as word after createTableBeginDelimiter. Delimiter is set to: "
+ getCreateTableBeginDelimiter());
}
int index = pStr.indexOf(STATEMENT_BEGIN);
if (index == -1) {
if (isLoggingWarning())
logWarning("Could not extract name because start delimiter could not be found. Returning null.");
return null;
}
pStr = pStr.substring(index + STATEMENT_BEGIN.length());
// loop to get rid of any spaces immediately after the
// start delimiter
while (pStr.startsWith(" ") && (pStr.length() > 1)) {
pStr = pStr.substring(1);
}
int first_blank = pStr.indexOf(" ");
if (!(first_blank > 0)) {
if (isLoggingDebug())
logDebug("Could not extract name because no word was found after the start delimiter. Returning null.");
return null;
}
String name = pStr.substring(0, first_blank);
if (isLoggingDebug())
logDebug("Extracted table name: " + name);
return name;
}
/**
* This method is used to break the large string passed to createTables() into
* an array of CREATE TABLE statements. The string is split apart using the
* createTableBeginDelimiter and createTableEndDelimiter Strings. These can be
* set as the createTableBeginDelimiter and createTableEndDelimiter
* properties.
*
* @param String
* - String containing all the Create Table statements
* @return Vector of CREATE TABLE statements
*/
/*
* private Vector getCreateStatements( String pStr ) { String STATEMENT_BEGIN
* = getCreateTableBeginDelimiter(); String STATEMENT_END =
* getCreateTableEndDelimiter();
*
* Vector statements = new Vector();
*
* // we need to make sure we strip off the potential 'missing tableinfos...'
* // error, so we do that by initially recopying the string from the first //
* CREATE TABLE. also with this check we catch the condition where the //
* string being processed has no CREATE TABLE clauses int index =
* pStr.indexOf( STATEMENT_BEGIN );
*
* // now loop through and extract all of the CREATE statements String
* remaining = pStr.substring( index ); while ( index != -1 ) { int stop =
* remaining.indexOf( STATEMENT_END ); if ( stop == -1 ) { // error - this
* string is malformed wrt what we expected because no end delimiter was found
* if ( isLoggingError() )
* logError("malformed string passed to getCreateStatements - an end delimiter '"
* + STATEMENT_END +
* "' could not be found. Abandoning parsing of Create table statements.");
* break; }
*
* String thisCreate = remaining.substring(0,stop); if ( isLoggingDebug() )
* logDebug("Parsed create statement: " + thisCreate ); statements.add(
* thisCreate );
*
* // now see if there are any more statements remaining =
* remaining.substring( stop + 1 ); index = remaining.indexOf( STATEMENT_BEGIN
* ); // need to put this here so that we skip everything between the end of
* the previous // CREATE TABLE and the beginning of the next if ( index != -1
* ) remaining = remaining.substring( index ); }
*
* if ( isLoggingDebug() ) logDebug("Found " + statements.size() +
* " create statements.");
*
* return statements; }
*/
/**
* This method is used to order CREATE TABLE statments such that we do not try
* to create a table before any tables that it references. NOTE: if a
* reference exists for a table outside of this repository we will print a
* warning, but will _not_ throw an exception. If the referenced table doesn't
* exist, an exception will be thrown when the referencing table is created.
*
* @param String
* containing all of the CREATE TABLE statements as generated by a
* call to GSARepository.generateSQL()
* @return Vector of individual CREATE statements that are in the proper order
* to execute
* @exception SQLProcessorException
* if we detect a bad loop trying to resolve references
*/
private List<String> reorderCreateStatements(List<String> pStatements)
throws SQLProcessorException {
List<String> statements = pStatements;
List<String> names = getTableNames(statements);
List<String> orderedStatements = new ArrayList<String>();
// hashmap containing one entry for every table that references
// another, and holds Vector of those tables it is waiting to be made
HashMap<String, List<String>> refersTo = new HashMap<String, List<String>>();
// hashmap containing one entry for every table that is references by
// another, and holds Vector of all the tables that reference it
HashMap<String, List<String>> referencedBy = new HashMap<String, List<String>>();
// setup the tables so we know who makes which references
Iterator<String> iter = statements.iterator();
while (iter.hasNext()) {
String statement = iter.next();
String tableName = getTableName(statement);
List<String> references = getTableReferences(statement, tableName);
if (references.size() < 1) {
orderedStatements.add(statement);
} else {
// organize the references this table has
if (!checkReferencesInRepository(names, references)) {
if (isLoggingWarning())
logWarning("Table " + tableName
+ " references a table outside the repository.");
}
// create an entry in 'refersTo' for this table
refersTo.put(tableName, references);
// update referencedBy to include this table
Iterator<String> refs = references.iterator();
while (refs.hasNext()) {
String ref = refs.next();
List<String> v;
if (!referencedBy.containsKey(ref)) {
v = new ArrayList<String>();
v.add(tableName);
referencedBy.put(ref, v);
} else {
v = referencedBy.get(ref);
v.add(tableName);
}
}
}
}
// removed all of the previously the ordered statements
iter = orderedStatements.iterator();
while (iter.hasNext()) {
String statement = iter.next();
statements.remove(statement);
}
// now that we know all the references, order them appropriately
// assuming we add one table per loop, this should take at most
// n iterations where n is the starting number of statements to add
int maxTries = statements.size();
int attempt = 0;
while (statements.size() > 0) {
Iterator<String> iterator = statements.iterator();
List<String> newlyAdded = new ArrayList<String>();
while (iterator.hasNext()) {
String statement = iterator.next();
String tableName = getTableName(statement);
// is this table isn't waiting for another table, add it
if (!refersTo.containsKey(tableName)) {
// this would be an error condition !!
} else {
List<String> waitingOnTables = refersTo.get(tableName);
boolean okToAdd = true;
Iterator<String> i = waitingOnTables.iterator();
while (i.hasNext()) {
String waitingOn = i.next();
if (refersTo.containsKey(waitingOn)) {
okToAdd = false;
}
}
if (okToAdd) {
orderedStatements.add(statement);
newlyAdded.add(statement);
// let the other tables know this one is made
if (referencedBy.containsKey(tableName)) {
List<String> tablesWaiting = referencedBy.get(tableName);
Iterator<String> j = tablesWaiting.iterator();
while (j.hasNext()) {
String table = j.next();
List<String> v = refersTo.get(table);
v.remove(tableName);
}
}
}
}
}
// after each iteration, remove the newlyAdded statements from the list
Iterator<String> k = newlyAdded.iterator();
while (k.hasNext()) {
String s = k.next();
statements.remove(s);
}
// make sure we aren't looping infinitely
if (attempt++ > maxTries) {
if (isLoggingError()) {
logError("Still trying to resolve: ");
Iterator<String> left = statements.iterator();
while (left.hasNext()) {
String table = left.next();
logError(table);
}
}
throw new SQLProcessorException(
"Could not order tables appropriately...failing. Turn on loggingDebug for more info.");
}
}
return orderedStatements;
}
/**
* This method is used to extract the names of other tables that a table
* references. expected format is: CREATE TABLE foo ( x int not null
* references bar(id), y varchar null references doo(id), ... )
*
* @param String
* the CREATE TABLE statement
* @param String
* the name of the table
* @return Vector containing names of referenced tables
* @exception SQLProcessorException
* if the table has a reference to itself
*/
private List<String> getTableReferences(String pStr, String tableName)
throws SQLProcessorException {
String REFERENCES = " references ";
List<String> refs = new ArrayList<String>();
int start = pStr.toLowerCase().indexOf(REFERENCES);
while (start != -1) {
pStr = pStr.substring(start + REFERENCES.length());
String ref = pStr;
// stop at a '('
int stop = ref.indexOf("(");
ref = ref.substring(0, stop);
// remove spaces
ref = ref.trim();
// bail if the table references itself - i think that is just wrong
/*
* actually, jeff and mike said this should be allowed, so i won't throw
* an exception, i just won't add it to the list of references either...
*/
if (ref.equalsIgnoreCase(tableName)) {
// do nothing
// throw new SQLProcessorException("The create statement for table " +
// tableName +
// " contains a reference to itself.");
} else if ((ref.length() > 0) && !refs.contains(ref))
refs.add(ref);
start = pStr.toLowerCase().indexOf(REFERENCES);
}
if (isLoggingDebug()) {
Iterator<String> i = refs.iterator();
while (i.hasNext()) {
String s = i.next();
logDebug("Found reference: " + s);
}
}
return refs;
}
/**
* This method is used to determine whether all the items in the second Vector
* are contained in the first Vector.
*
* @param Vector
* of the names of all the tables in the repository
* @param Vector
* of the names of all the tables to check for
* @return boolean true if all items are in the Vector; false otherwise
*/
private boolean checkReferencesInRepository(List<String> pRepositoryTables,
List<String> pCheckTables) {
Iterator<String> iter = pCheckTables.iterator();
while (iter.hasNext()) {
String name = iter.next();
if (!pRepositoryTables.contains(name))
return false;
}
return true;
}
/**
* Method to determine whether a table already exists in the database. The
* method operates by appending the name passed as a parameter to the String
* that has been set in the determineTableExistsSQL property
*
* @param String
* - name of table to check for existence of
* @return boolean - true if table exists; false otherwise
*/
private boolean tableExists(String pTableName) {
// don't bother with query if name is invalid
if (pTableName == null || pTableName.length() == 0)
return false;
// create sql
String sql = getDetermineTableExistsSQL() + " " + pTableName;
// execute and check for an exception
try {
performSQL(sql);
} catch (SQLProcessorException spe) {
// we should only get an exception here if the table does NOT
// exist. in that case, don't throw the exception - just return false
if (isLoggingDebug()) {
logDebug("Table existence is determined by whether an exception is received when querying the table.");
logDebug("Caught exception checking whether table exists, so table doesn't exist.");
logDebug("Checked for existence with this statement \"" + sql + "\"");
logDebug("Set repository debugLevel > 6 to see full exception.");
if (this.getRepository().getDebugLevel() > 6)
logDebug(spe);
}
return false;
}
return true;
}
/**
* Returns true if there is at least one table in this schema
* This is handy for Derby since it will throw an error if one
* attempts to try a select statement to determine if a table
* exists and the schema has not yet been created.
* @return
*/
private boolean hasAnyTables() {
boolean foundTables = false;
Connection c = null;
try {
c = getConnection();
DatabaseMetaData metadata = null;
metadata = c.getMetaData();
String[] names = { "TABLE" };
ResultSet tableNames = metadata.getTables(null, null, null, names);
while (tableNames.next()) {
String tab = tableNames.getString("TABLE_NAME");
foundTables = true;
break;
}
tableNames.close();
} catch (SQLProcessorException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
c.close();
} catch (SQLException e) {
;
}
}
return foundTables;
}
/* These methods are really used - they were just part of mgk's original class */
// ---------- Property: (read-only) InsertSQL ----------
/** SQL to insert a new id space into the DB table */
transient String mInsertSQL;
/**
* Get property <code>InsertSQL</code>. The SQL is lazily generated.
*
* @beaninfo description: SQL to insert a new id space into the DB table
* @return InsertSQL
**/
// private String getInsertSQL()
// {
// // build SQL string if needed
// if (mInsertSQL == null)
// {
// StringBuffer buf = new StringBuffer(300);
// buf.append("INSERT INTO ");
// /*
// buf.append(getTableName());
// buf.append('(');
// buf.append(getNameColumn()).append(',');
// buf.append(getSeedColumn()).append(',');
// buf.append(getBatchSizeColumn()).append(',');
// buf.append(getPrefixColumn()).append(',');
// buf.append(getSuffixColumn());
// */
//
// buf.append(')').append('\n');
// buf.append("VALUES (?, ?, ?, ?, ?)\n");
//
// mInsertSQL = buf.toString();
// }
//
// return mInsertSQL;
// }
// ---------- Property: (read-only) UpdateSQL ----------
/** SQL to execute to update a specific id space in the DB */
transient String mUpdateSQL;
/**
* Get property <code>UpdateSQL</code>. The SQL is lazily generated.
*
* @beaninfo description: SQL to execute to update a specific id space int the
* D0B
* @return UpdateSQL
**/
// private String getUpdateSQL()
// {
// // generate SQL if needed
// if (mUpdateSQL == null)
// {
// StringBuffer buf = new StringBuffer(300);
// buf.append("UPDATE ");
// /*
// buf.append(getTableName());
// buf.append(" SET ");
// buf.append(getSeedColumn()).append('=');
// buf.append(getSeedColumn()).append('+');
// buf.append(getBatchSizeColumn());
// buf.append(" WHERE ");
// buf.append(getNameColumn()).append(" = ?");
//
// */
// mUpdateSQL = buf.toString();
// }
//
// return mUpdateSQL;
// }
// ---------- Property: (read-only) SelectSQL ----------
/** SQL to execute to load a specific id space from the DB */
transient String mSelectSQL;
/**
* Get property <code>SelectSQL</code>. The SQL is lazily generated.
*
* @beaninfo description: SQL to execute to load a specific id space from the
* DB
* @return SelectSQL
**/
// private String getSelectSQL()
// {
// // generate SQL if needed
// if (mSelectSQL == null)
// {
// StringBuffer buf = new StringBuffer(300);
// buf.append("SELECT ");
// /*
// buf.append(getSeedColumn()).append(',');
// buf.append(getBatchSizeColumn()).append(',');
// buf.append(getPrefixColumn()).append(',');
// buf.append(getSuffixColumn());
// buf.append(" FROM ");
// buf.append(getTableName());
// buf.append(" WHERE ");
// buf.append(getNameColumn()).append(" = ?");
// */
//
// mSelectSQL = buf.toString();
// }
//
// return mSelectSQL;
// }
}