dblook_test.java

derby database source code.good for you.

        return(ret);
	}

    private static final String[] ignorableSchemaNames = {
        "SYSIBM",
        "SYS",
        "SYSVISUAL",
        "SYSCAT",
        "SYSFUN",
        "SYSPROC",
        "SYSSTAT",
        "NULLID",
        "SYSCS_ADMIN",
        "SYSCS_DIAG",
        "SYSCS_UTIL",
        "SQLJ"};

	/* **********************************************
	 * dumpResultSet:
	 * Iterates through the received result set and
	 * dumps ALL columns in ALL rows of that result
	 * set to output.  Since no order is guaranteed
	 * in the received result set, we have to generate
	 * unique "ids" for each row in the result, and
	 * then use those ids to determine what order the
	 * rows will be output.  Failure to do so will
	 * lead to diffs in the test for rows that occur
	 * out of order.  The unique id's must NOT
	 * depend on system-generated id's, as the
	 * latter will vary for every run of the test,
	 * and thus will lead to different orderings
	 * every time (which we don't want).
	 *
	 * @param rs The result set that is being dumped.
	 * @param idToNameMap Mapping of various ids to
	 *  object names; used in forming unique ids.
	 * @param conn Connection from which the result set
	 *  originated.
	 ****/

	private void dumpResultSet (ResultSet rs,
		HashMap idToNameMap, Connection conn)
		throws Exception
	{

		// We need to form unique names for the rows of the
		// result set so that we can preserve the order of
		// the output and avoid diffs with a master.  This is
		// because a "select *" doesn't order rows--and even
		// though the schema for two databases might be the
		// same (i.e. the system tables contain all of the same
		// information) there's nothing to say the various rows in
		// the respective system tables will be the same (they
		// usually are NOT).  While system id's automatically
		// give us uniqueness, we can NOT order on them because
		// they vary from database to database; so, we need
		// to use something constant across the databases,
		// which is why we use object names.
		StringBuffer uniqueName = new StringBuffer();

		TreeMap orderedRows = new TreeMap();
		ArrayList rowValues = new ArrayList();
		ArrayList duplicateRowIds = new ArrayList();

		ResultSetMetaData rsmd = rs.getMetaData();
		int cols = rsmd.getColumnCount();
		while (rs.next()) {

			for (int i = 1; i <= cols; i++) {

				String colName = rsmd.getColumnName(i);
				String value = rs.getString(i);
				String mappedName = (String)idToNameMap.get(value);

				if ((colName.indexOf("SCHEMAID") != -1) &&
					(mappedName != null) &&
					((mappedName.indexOf("SYS") != -1) ||
                     (isIgnorableSchema(mappedName))))
                {
				// then this row of the result set is for a system
				// object, which will always be the same for the
				// source and new database, so don't bother dumping
				// them to the output file (makes the test less
				// like to require updates when changes to database
				// metadata for system objects are checked in).
					rowValues = null;
					break;
				}
				else if (colName.equals("JAVACLASSNAME") && (value != null) &&
					(value.indexOf("org.apache.derby") != -1) &&
					(value.indexOf(".util.") == -1)) {
				// this is a -- hack -- to see if the alias is a
				// a system alias, needed because aliases
				// (other than stored procedures) do not have
				// an associated schema).
					rowValues = null;
					break;
				}

				if (i == 1)
				// 1st column is just for figuring out whether
				// to dump this row; no need to actually include
				// it in the results.
					continue;

				String uniquePiece = dumpColumnData(colName,
					value, mappedName, rowValues);

				if (colName.equals("DEPENDENTID")) {
				// Special case: rows in the "DEPENDS" table
				// don't have unique ids or names; we have to
				// build one by extracting information indirectly.
					String hiddenInfo = getDependsData(rs, conn,
						idToNameMap);
					if (hiddenInfo.indexOf("SYS_OBJECT") != -1) {
					// this info is for a system object, so
					// ignore it.
						rowValues = null;
						break;
					}
					uniqueName.append(hiddenInfo);
					// Include the hidden data as part of the
					// output.
					rowValues.add(hiddenInfo);
				}

		 		if (uniquePiece != null)
					uniqueName.append(uniquePiece);

				if (colName.equals("STMTNAME") &&
				  (value.indexOf("TRIGGERACTN") != -1))
				// Special case: can't use statement name, because
				// the entire statement may be automatically generated
				// in each database (to back a trigger), so the name
				// in which case the generated name will be different
				// every time; but filtering out the name means
				// we have no other guaranteed unique 'id' for
				// ordering.  So, just take "text" field, and
				// design test db so that no two triggers have the
				// same text value.
				uniqueName.append(rs.getString(6));

			}

			if (rowValues != null) {

				if (duplicateRowIds.contains(uniqueName.toString()))
				// then we've already encountered this row id before;
				// to preserve ordering, use the entire row as an
				// id.
					handleDuplicateRow(rowValues, null, orderedRows);
				else {
					ArrayList oldRow = (ArrayList)(orderedRows.put(
						uniqueName.toString(), rowValues));
					if (oldRow != null) {
					// Duplicate row id.
						duplicateRowIds.add(uniqueName.toString());
						// Delete the row that has the duplicate row id.
							orderedRows.remove(uniqueName.toString());
						handleDuplicateRow(rowValues, oldRow, orderedRows);
					}
				}
			}

			uniqueName = new StringBuffer();
			rowValues = new ArrayList();

		}

		// Now, print out all of the data in this result set
		// using the order of the unique names that we created.
		Set objectNames = orderedRows.keySet();
		for (Iterator itr = objectNames.iterator();
			itr.hasNext(); ) {

			String row = (String)itr.next();
			ArrayList colData = (ArrayList)orderedRows.get(row);
			for (int i = 0; i < colData.size(); i++)
				writeOut((String)colData.get(i));
			writeOut("----");

		}

		orderedRows = null;
		rs.close();

	}

	/* **********************************************
	 * dumpColumnData:
	 * Stores the value for a specific column of
	 * some result set.  If the value needs to
	 * be filtered (to remove system-generated ids
	 * that would otherwise cause diffs with the
	 * master), that filtering is done here.
	 * @param colName Name of the column whose value we're
	 *  writing.
	 * @param value Value that we're writing.
	 * @param mappedName: Name corresponding to the value,
	 *  for cases where the value is actually an
	 *  object id (then we want to write the name
	 *  instead).
	 * rowValues a list of column values for the
	 *  current row of the result set.
	 * @return The (possibly filtered) value of the
	 *  received column has been added to the
	 *  "rowVals" array list, and the corresponding
	 *  piece of the row's unique name has been
	 *  returned, if one exists.
	 ****/

	private String dumpColumnData(String colName,
		String value, String mappedName, ArrayList rowVals)
	{

		if (mappedName == null) {
		// probably not an id.
			if (colName.equals("CONGLOMERATENUMBER") ||
				colName.equals("GENERATIONID"))
			// special case: these numbers aren't ids per
			// se, but they are still generated by the system,
			// and will cause diffs with the master; so, ignore
			// them.
				rowVals.add("<systemnumber>");
			else if (colName.equals("AUTOINCREMENTVALUE"))
			// special case: new database won't have any data,
			// old will, so unless we filter this out, we'll
			// get a diff.
				rowVals.add("<autoincval>");
			else if (colName.equals("VALID"))
			// special case: ignore whether or not stored
			// statements are valid (have been compiled)
			// since it depends on history of database,
			// which we can't duplicate.
				rowVals.add("<validityflag>");
			else if (value != null) {
				if (looksLikeSysGenName(value)) {
					if (columnHoldsObjectName(colName))
						rowVals.add("<systemname>");
					else {
					// looks like a sys gen name, but's actually a VALUE.
						rowVals.add(value);
						return value;
					}
				}
				else if (looksLikeSysGenId(value))
					rowVals.add("<systemid>");
				else {
					rowVals.add(value);
					if (columnHoldsObjectName(colName))
					// if it's a name, we need it as part of
					// our unique id.
						return value;
				}
			}
			else
			// null value.
				rowVals.add(value);
		}
		else {
		// it's an id, so write the corresponding name.
			if (!isSystemGenerated(mappedName)) {
			// Not an id-as-name, so use it as part of our unique id.
				rowVals.add(mappedName);
				return mappedName;
			}
			else
				rowVals.add("<systemname>");
		}

		// If we get here, we do NOT want the received value
		// to be treated as part of this row's unique name.
		return null;

	}

	/* **********************************************
	 * handleDuplicateRow:
	 * If we get here, then despite our efforts (while
	 * dumping the system catalogs for a database), we
	 * still have a duplicate row id.  So, as a last
	 * resort we just use the ENTIRE row as a 'row id'.
	 * In the rare-but-possible case that the entire
	 * row is a duplicate (as can happen with the
	 * SYSDEPENDS table), then we tag a simple number
	 * onto the latest row's id, so that the row will
	 * still show up multiple times--and since the rows
	 * are identical, it doesn't matter which comes
	 * 'first'.
	 * @param newRow The most recently-fetched row from
	 *  the database system catalogs.
	 * @param oldRow The row that was replaced when the
	 *  newRow was inserted (because they had the
	 *  same row id), or "null" if we were already
	 *  here once for this row id, and so just want
	 *  insert a new row.
	 * @param orderedRows The ordered set of rows, into
	 *  which oldRow and newRow need to be inserted.
	 * @return oldRow and newRow have been inserted
	 *  into orderedRows, and each has a (truly)
	 *  unique id with it.
	 ****/

	private void handleDuplicateRow(
		ArrayList newRow, ArrayList oldRow,
		TreeMap orderedRows)
	{

		// Add the received rows (old and new) with
		// unique row ids.

		StringBuffer newRowId = new StringBuffer();
		for (int i = 0; i < newRow.size(); i++)
			newRowId.append((String)newRow.get(i));

		Object obj = (ArrayList)(orderedRows.put(
						newRowId.toString(), newRow));
		if (obj != null)
		// entire row is a duplicate.
			orderedRows.put(newRowId.toString() + 
				duplicateCounter++, newRow);

		if (oldRow != null) {

			StringBuffer oldRowId = new StringBuffer();
			for (int i = 0; i < oldRow.size(); i++)
				oldRowId.append((String)oldRow.get(i));

			obj = (ArrayList)(orderedRows.put(
				oldRowId.toString(), oldRow));
			if (obj != null)
			// entire row is a duplicate.
				orderedRows.put(oldRowId.toString() +
					duplicateCounter++, oldRow);
		}

		return;

	}

	/* **********************************************
	 * createDBFromDDL:
	 * Read from the given script and use it to create
	 * a new database of the given name.
	 * @param newDBName Name of the database to be created.
	 * @param scriptName Name of the script containing the
	 *  DDL from which the new database will be created.
	 * @return New database has been created from
	 *   the script; any commands in the script that
	 *   failed to execute have been echoed to output.
	 ****/

	private void createDBFromDDL(String newDBName,
		String scriptName) throws Exception
	{

		System.out.println("\n\nCreating database '" + newDBName +
			"' from ddl script '" + scriptName + "'");

		Connection conn = DriverManager.getConnection(
			"jdbc:derby:" + newDBName + ";create=true");

		Statement stmt = conn.createStatement();
		BufferedReader ddlScript =
			new BufferedReader(new FileReader(scriptName));

		for (String sqlCmd = ddlScript.readLine(); sqlCmd != null;
			sqlCmd = ddlScript.readLine()) {

			if (sqlCmd.indexOf("--") == 0)
			// then this is a script comment; ignore it;
				continue;
			else if (sqlCmd.trim().length() == 0)
			// blank line; ignore it.
				continue;

			// Execute the command.
			if ((sqlCmd.charAt(sqlCmd.length()-1) == TEST_DELIMITER)
			  || (sqlCmd.charAt(sqlCmd.length()-1) == ';'))
			// strip off the delimiter.
				sqlCmd = sqlCmd.substring(0, sqlCmd.length()-1);

			try {
				stmt.execute(sqlCmd);
			} catch (Exception e) {
				System.out.println("FAILED: to execute cmd " +
					"from DDL script:\n" + sqlCmd + "\n");
				System.out.println(e.getMessage());
			}

		}

		// Cleanup.
		ddlScript.close();
		stmt.close();
		conn.close();

		return;

	}

	/* **********************************************
	 * writeOut:
	 * Write the received string to some output.
	 * @param str String to write.
	 ****/

	private static void writeOut(String str) {

		System.out.println(str);
		return;

	}