}

  /** This method is used to extract a possible CREATE INDEX statement from
   *  a CREATE TABLE statement that is generated by a Table.  If no CREATE
   *  INDEX statement is included, it returns null
   */
  private String extractIndexStatement(String pStatement) {
    String search = "CREATE INDEX ";
    String copy = pStatement.toUpperCase();
    int i = copy.indexOf(search);
    if (i != -1)
      return stripTrailingSemiColon(pStatement.substring(i));

    return null;
  }

  /** This method is used to remove any possible CREATE INDEX statements from
   *  the end of a CREATE TABLE statement generated by a Table.  It returns the
   *  CREATE TABLE statement with all CREATE INDEX statements removed.
   */
  private String removeIndexStatements(String pStatement) {
    String search = "CREATE INDEX ";
    String copy = pStatement.toUpperCase();
    int i = copy.indexOf(search);
    if (i != -1)
      pStatement = pStatement.substring(0, i);

    return stripTrailingSemiColon(pStatement);
  }

  /** This method is used to remove the trailing semicolon from a String.  It is assumed
   *  that these strings will only possibly have one semicolon, and that if there is one
   *  everything after the semicolon is junk.
   */
  private String stripTrailingSemiColon(String pStr) {
    if (pStr == null)
      return pStr;
    int idx = pStr.indexOf(";");
    if (idx != -1)
      pStr = pStr.substring(0, idx);

    return pStr;
  }

  // ---------- methods to help with user-specified SQL files -----------
  // allowable db types to specify
  public String SOLID = "solid";

  public String ORACLE = "oracle";

  public String MICROSOFT = "microsoft";

  public String INFORMIX = "informix";

  public String DB2 = "db2";

  public String SYBASE = "sybase";

  public String SYBASE2 = "Adaptive Server Enterprise"; // sybase 12.5

  public String DEFAULT = "default";

  private String[] dbTypes = { SOLID, ORACLE, MICROSOFT, INFORMIX, DB2, SYBASE,
      SYBASE2, DEFAULT };

  /** returns the dbtype for the database being used.  returned value will be one
   *  of the constants SOLID, ORACLE, MICROSOFT, INFORMIX, DB2, SYBASE, or DEFAULT
   *  if db type can not be determined.
   */
  private String getDatabaseType() {
    String type = getDatabaseName();
    for (int i = 0; i < dbTypes.length; i++) {
      if (type.toLowerCase().indexOf(dbTypes[i].toLowerCase()) > -1) {
        if (dbTypes[i].equals(SYBASE2))
          return SYBASE;
        return dbTypes[i];
      }
    }
    return DEFAULT;
  }

  /** returns array of user-specified SQL files that should be executed, or null
   *  if output from startSQLRepository should be used.
   *  @exception RepositoryException if an error occurs getting the array of files to execute
   */
  private String[] getSpecifiedCreateFiles() throws RepositoryException {
    // try to get mapped value for this specific db type, and if it's empty try the default
    String files = (String) getSqlCreateFiles().get(getDatabaseType());
    if (files == null)
      files = (String) getSqlCreateFiles().get(DEFAULT);
    // if it's still empty then just return b/c there's nothing to execute
    if (files == null)
      return null;

    // if file list is not null, convert it and return the array
    try {
      return TestUtils.convertFileArray(files, ",");

  public void setStripReferences(boolean pStrip) {
    mStripReferences = pStrip;
  }

  public boolean isStripReferences() {
    return mStripReferences;
  }

  // do we want to show the create table statements that are executed
  private boolean mShowCreate = false;

  public void setloggingCreateTables(boolean pLog) {
    mShowCreate = pLog;
  }

  public boolean isLoggingCreateTables() {
    return mShowCreate;
  }

  /* the SQLProcessorEngine to use for creating tables
   * this property is optional because we'll create a default
   * SQLProcessorEngine if the property isn't set
   */
  private SQLProcessorEngine mSQLProcessor = null;

  public void setSQLProcessor(SQLProcessorEngine pEngine) {
    mSQLProcessor = pEngine;
  }

  public SQLProcessorEngine getSQLProcessor() {
    // create a new processor if one isn't set
    if (mSQLProcessor == null) {
      mSQLProcessor = new SQLProcessorEngine(this);
      mSQLProcessor.setLoggingDebug(this.isLoggingDebug());
      mSQLProcessor.setLoggingError(this.isLoggingError());
      mSQLProcessor.setLoggingInfo(this.isLoggingInfo());
      mSQLProcessor.setLoggingWarning(this.isLoggingWarning());
      LogListener[] listeners = this.getLogListeners();
      for (int i = 0; i < listeners.length; i++) {
        mSQLProcessor.addLogListener(listeners[i]);
      }
    }

    return mSQLProcessor;
  }

  /** boolean indicating whether we should perform the import every time
   * Dynamo starts, or only perform the import if we created at least
   * one table.
   * NOTE: if dropTablesIfExist is true, the import will occur every time
   * because we will create tables every time.
   * default: false - only perform the import when tables are created
   */
  private boolean mImportEveryStartup = false;

  public void setImportEveryStartup(boolean pImport) {
    mImportEveryStartup = pImport;
  }

  public boolean isImportEveryStartup() {
    return mImportEveryStartup;
  }

  /** boolean indicating whether we should drop all tables associated with
   *  this repository when Dynamo is shut down.
   *  NOTE: this will only work properly is Dynamo is shutdown properly. It
   *  will not work if Dynamo is just killed
   *  default: false
   */
  private boolean mDropTablesAtShutdown = false;

  public void setDropTablesAtShutdown(boolean pDrop) {
    mDropTablesAtShutdown = pDrop;
  }

  public boolean isDropTablesAtShutdown() {
    return mDropTablesAtShutdown;
  }

  /** boolean indicating whether to wrap each imported file in it's own transaction.
   *  this is a new option in D5.5 that has changed the method signature of
   *  atg.adapter.gsa.xml.TemplateParser.importFiles()
   *  default: true
   */
  private boolean mImportWithTransaction = true;

  public void setImportWithTransaction(boolean pTran) {
    mImportWithTransaction = pTran;
  }

  public boolean isImportWithTransaction() {
    return mImportWithTransaction;
  }

  private Properties mSqlCreateFiles = new Properties();

  /** Optional mapping of user-specified sql files that should be executed instead of
   *  the SQL generated by startSQLRepository.  Key values must be one of (case sensitive):
   *  <b>default</b>, <b>oracle</b>, <b>solid</b>, <b>informix</b>, <b>microsoft</b>,
   *  <b>sybase</b>, or <b>db2</b>.  Mapped values should be a colon (:) separated
   *  ordered list of files to execute for that database type.
   *  <br>Specified files may use <pre>{....}</pre> notation to indicate a
   *  System variable that should be substituted at runtime, such as <pre>{atg.dynamo.root}</pre>.
   *  <p>The following behavior is observed:
   *  <pre>
   *          a) database meta data is used to determine specific database type
   *          b) when <b>default</b> not specified
   *            - if mapping exists for specific db type, those files are executed
   *            - otherwise, output from startSQLRepository is executed
   *          c) when <b>default</b> is specified
   *            - if mapping exists for specific db type, those files are executed
   *            - otherwise, files mapped under default are executed
   *          d) if a mapping exists for a db type in 'sqlCreateFiles' then a corresponding
   *             entry (to the specific db type, or to default) must exist.  Otherwise an exception
   *             is thrown at starup.
   *  </pre>
   *  <p>Also, when a file specified in the property 'sqlCreateFiles' is used (i.e. output
   *  from startSQLRepository is not being used) then the initializingGSA will always
   *  do the following at startup, unless property 'executeCreateAndDropScripts' is set to false:
   *  <pre>
   *          a) execute the appropriate dropSqlFile(s)
   *          b) execute the appropriate createSqlFile(s)
   *  </pre>
   *  If 'executeCreateAndDropScripts' is false then in the case where scripts normally would be run
   *  they will instead be skipped and no SQL (from scripts or startSQLRepository) will be executed.
   *  The reason for this restriction is that it's too difficult to know whether a database has
   *  been properly reset for the 'createSqlFile(s)' to run properly, so we err on the conservative
   *  side and always reset it.
   */
  public void setSqlCreateFiles(Properties pFiles) {
    mSqlCreateFiles = pFiles;
  }

  /** returns optional mapping of user-specified sql files that should be executed instead of
   *  the SQL generated by startSQLRepository.  see 'setSqlCreateFiles' for detailed
   *  explanation of this property.
   */
  public Properties getSqlCreateFiles() {
    return mSqlCreateFiles;
  }

  private Properties mSqlDropFiles = new Properties();

  /** returns optional mapping of user-specified sql files that should be executed during
   *  'tear-down' instead of basing it on the SQL generated by startSQLRepository.  see
   *  'setSqlCreateFiles' for detailed explanation of this property.
   */
  public void setSqlDropFiles(Properties pFiles) {
    mSqlDropFiles = pFiles;
  }

  /** returns optional mapping of user-specified sql files that should be executed during
   *  'tear-down' instead of basing it on the SQL generated by startSQLRepository.  see
   *  'setSqlCreateFiles' for detailed explanation of this property.
   */
  public Properties getSqlDropFiles() {
    return mSqlDropFiles;
  }

    while (end != -1) {
      String temp = pStr.substring(start, end);
      sb.append(temp);
      pStr = pStr.substring(end + ref.length());
      start = pStr.indexOf(endRef);
      end = pStr.indexOf(ref);
    }
    String temp2 = pStr.substring(start);
    sb.append(temp2);

    if (isLoggingDebug())
      logDebug("Final sql string -> references removed: \n" + sb.toString());

    return sb.toString();
  }

  private String stripForeignKey(String pStr) {
    if (isLoggingDebug()) {
      logDebug("Removing Foreign Key from SQL string...");
      if (this.getDebugLevel() > 6)
        logDebug("SQL string before Foreign Key are removed: \n" + pStr);
    }

    String key = "foreign key";
    int flag = 0;
    int end = 0;
    end = pStr.toLowerCase().lastIndexOf(key);

    while (end != -1) {
      flag = 1;
      pStr = pStr.substring(0, end);
      end = pStr.toLowerCase().lastIndexOf(key);
    }
    end = pStr.lastIndexOf(",");
    if (flag == 0)
      return pStr;
    else
      return pStr.substring(0, end) + " )";
  }

  /** This method is used to extract a possible CREATE INDEX statement from
   *  a CREATE TABLE statement that is generated by a Table.  If no CREATE
   *  INDEX statement is included, it returns null
   */
  private String extractIndexStatement(String pStatement) {

  private boolean mCreateTables = true;

  public void setCreateTables(boolean pCreate) {
    mCreateTables = pCreate;
  }

  public boolean isCreateTables() {
    return mCreateTables;
  }

  // do we want to drop tables that exist if we want to create
  // a table with the same name
  private boolean mDropTables = false;

  public void setDropTablesIfExist(boolean pDrop) {
    mDropTables = pDrop;
  }

  public boolean isDropTablesIfExist() {
    return mDropTables;
  }

  // the XML files containing export data from the TemplateParser
  // it will be imported into the database after tables are created
  // we load the files as Files instead of XMLFiles because the
  // TemplateParser requires a full file path to the import file
  // instead of using the CONFIGPATH
  private File[] mImportFiles = null;

  public void setImportFiles(File[] pFiles) {
    mImportFiles = pFiles;
  }

  public File[] getImportFiles() {
    return mImportFiles;
  }

  public String[] getImportFilesAsStrings() {
    File[] f = getImportFiles();
    if (f == null)
      return null;

    List<String> v = new ArrayList<String>();
    for (int i = 0; i < f.length; i++) {
      if (!v.contains(f[i].getAbsolutePath()))
        v.add(f[i].getAbsolutePath());
    }

    return (String[]) v.toArray(new String[v.size()]);
  }

  // do we want to strip the 'references(..)' statements from SQL
  // created by the GSA
  private boolean mStripReferences = false;

  }

  /** This method imports files using the TemplateParser
   *
   * @exception RepositoryException if an error occured while importing
   * one of the xml files.
   */
  private void importFiles() throws RepositoryException {
    if (isLoggingInfo())
      logInfo("Importing files...");

    String[] loadFiles = getImportFilesAsStrings();
    // just exit if no files were specified
    if (loadFiles == null) {
      if (isLoggingInfo())
        logInfo("No files specified for import.");
      return;
    }

    if (isLoggingDebug()) {
      logDebug("The following files will be imported:");
      for (int i = 0; i < loadFiles.length; i++) {
        logDebug("file: " + loadFiles[i]);
      }
    }

    // now load the import files if they were specified
    PrintWriter ps = new PrintWriter(System.out);
    if (loadFiles != null && loadFiles.length > 0) {
      try {