dblook.java

derby database source code.good for you.

		// attributes.  This list can begin with either a colon
		// or a semi-colon.
			stop = dbUrl.indexOf(":", start);
			if (stop != -1) {
				if ((dbUrl.charAt(stop+1) == '/') ||
						(dbUrl.charAt(stop+1) == '\\'))
				// then this colon is part of the path (ex. "C:"),
				// so ignore it.
					stop = dbUrl.indexOf(":", stop+2);
			}
			int stop2 = dbUrl.length();
			if (stop == -1)
			// no colons; see if we can find a semi-colon.
				stop = dbUrl.indexOf(";", start);
			else
				stop2 = dbUrl.indexOf(";", start);
			stop = (stop <= stop2 ? stop : stop2);
		}

		if (stop == -1)
		// we have a url that ends with database name (no
		// other attributes appended).
			stop = dbUrl.length();

		return dbUrl.substring(start, stop);

	}

	/* ************************************************
	 * extractTableNamesFromList:
	 * Given an array of command line arguments containing
	 * a list of table names beginning at start'th position,
	 * read the list of table names and store them as
	 * our target table list.  Names without quotes are
	 * turned into ALL CAPS and then double quotes are
	 * added; names whcih already have double quotes are
	 * stored exactly as they are. NOTE: DB2 enforces
	 * maximum of 30 tables, and ignores the rest; so
	 * do we.
	 * @param args Array of command line arguments.
	 * @start Position of the start of the list of tables
	 *  with the args array.
	 * @return The position of the last table name in
	 *  the list of table names.
	 ****/

	private int extractTableNamesFromList(String [] args,
		int start)
	{

		int argIndex = start;
		int count = 0;
		tableList = new ArrayList();
		while (argIndex < args.length) {

			if (((args[argIndex].length() > 0) && (args[argIndex].charAt(0) == '-')) ||
				(++count > DB2_MAX_NUMBER_OF_TABLES))
			// we're done with the table list.
				break;

			if ((args[argIndex].length() > 0) && (args[argIndex].charAt(0) == '"'))
			// it's quoted.
				tableList.add(addQuotes(expandDoubleQuotes(
					stripQuotes(args[argIndex++]))));
			else
			// not quoted, so make it all caps, then add
			// quotes.
				tableList.add(addQuotes(
					expandDoubleQuotes(args[argIndex++].toUpperCase(
					java.util.Locale.ENGLISH))));

		}

		if (tableList.size() == 0)
			tableList = null;

		return argIndex - 1;

	}

	/* ************************************************
	 * showVariables:
	 * Echo primary variables to output, so user can see
	 * what s/he specified.
	 ****/

	private void showVariables() {

		if (ddlFileName != null) {
			Logs.reportString("============================\n");
			Logs.reportMessage("DBLOOK_FileCreation");
			if (verbose)
				writeVerboseOutput("DBLOOK_OutputLocation",
					ddlFileName);
		}

		Logs.reportMessage("DBLOOK_Timestamp",
			new Timestamp(System.currentTimeMillis()).toString());
		Logs.reportMessage("DBLOOK_DBName", sourceDBName);
		Logs.reportMessage("DBLOOK_DBUrl", sourceDBUrl);
		if (tableList != null)
			Logs.reportMessage("DBLOOK_TargetTables");
		if (schemaParam != null)
			Logs.reportMessage("DBLOOK_TargetSchema", stripQuotes(schemaParam));
		Logs.reportString("appendLogs: " + appendLogs + "\n");
		return;

	}

	/* ************************************************
	 * go:
	 * Connect to the source database, prepare statements,
	 * and load a list of table id-to-name mappings.  Then,
	 * generate the DDL for the various objects in the
	 * database by making calls to static methods of helper
	 * classes (one helper class for each type of database
	 * object).  If a particular object type should not be
	 * generated (because of the user-specified command-
	 * line), then we enforce that here.
	 * @precondition all user-specified parameters have
	 *  been loaded.
	 * @return DDL for the source database has been
	 *  generated and printed to output, subject to
	 *  user-specified restrictions.
	 * ****/

	private void go()
		throws Exception
	{

		try
		{
			// Connect to the database, prepare statements,
			// and load id-to-name mappings.
			this.conn = DriverManager.getConnection(sourceDBUrl);
			prepForDump();

			// Generate DDL.

			// Start with schemas, since we might need them to
			// exist for jars to load properly.
			DB_Schema.doSchemas(this.conn,
				(tableList != null) && (targetSchema == null));

			if (tableList == null) {
			// Don't do these if user just wants table-related objects.
				DB_Jar.doJars(sourceDBName, this.conn);
				DB_Alias.doProceduresAndFunctions(this.conn);
			}

			DB_Table.doTables(this.conn, tableIdToNameMap);
			DB_Index.doIndexes(this.conn);
			DB_Alias.doSynonyms(this.conn);
			DB_Key.doKeys(this.conn);
			DB_Check.doChecks(this.conn);

			if (!skipViews)
				DB_View.doViews(this.conn);

			DB_Trigger.doTriggers(this.conn);

			// That's it; we're done.
			if (getColNameFromNumberQuery != null)
				getColNameFromNumberQuery.close();
			Logs.cleanup();

		}
		catch (SQLException sqlE)
		{
			Logs.debug(sqlE);
			Logs.debug(Logs.unRollExceptions(sqlE), (String)null);
			Logs.cleanup();
			return;
		}
		catch (Exception e)
		{
			Logs.debug(e);
			Logs.cleanup();
			return;
		}
		finally {
		// Close our connection.
			if (conn != null) {
				conn.commit();
				conn.close();
			}
		}

	}

	/* ************************************************
	 * prepForDump:
	 * Prepare any useful statements (i.e. statements that
	 * are required by more than one helper class) and load
	 * the id-to-name mappings for the source database.
	 ****/

	private void prepForDump() throws Exception {

		// We're only SELECTing throughout all of this, so no need
		// to commit (plus, disabling commit makes it easier to
		// have multiple ResultSets open on the same connection).
		this.conn.setAutoCommit(false);

		// Prepare statements.
		getColNameFromNumberQuery = conn.prepareStatement(
			"SELECT COLUMNNAME FROM SYS.SYSCOLUMNS WHERE " +
			"REFERENCEID = ? AND COLUMNNUMBER = ?");

		// Load list of user tables and table ids, for general use.
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT T.TABLEID, T.TABLENAME, " +
				"S.SCHEMANAME FROM SYS.SYSTABLES T, SYS.SYSSCHEMAS S " + 
				"WHERE T.TABLETYPE = 'T' AND T.SCHEMAID = S.SCHEMAID");

		while (rs.next()) {
			String tableName = addQuotes(expandDoubleQuotes(rs.getString(2)));
			String schemaName = addQuotes(expandDoubleQuotes(rs.getString(3)));
			tableIdToNameMap.put(rs.getString(1), 
				schemaName + "." + tableName);
		}

		// Load schema id's and names.
		rs = stmt.executeQuery("SELECT SCHEMAID, SCHEMANAME FROM " +
			"SYS.SYSSCHEMAS");
		while (rs.next()) {
			schemaMap.put(rs.getString(1),
				addQuotes(expandDoubleQuotes(rs.getString(2))));
		}

		stmt.close();

		// Load default property values.
		return;

	}

	/* ************************************************
	 * getColumnListFromDescription:
	 * Takes string description of column numbers in the
	 * form of "(2, 1, 3...)" and the id of the table
	 * having those columns, and then returns a string
	 * with the column numbers replaced by their actual
	 * names ('2' is replaced with the 2nd column in the
	 * table, '1' with the first column, etc.).
	 * @param tableId the id of the table to which the column
	 *   numbers should be applied.
	 * @param description a string holding a list of column
	 *  numbers, enclosed in parentheses and separated
	 *  by commas.
	 * @return a new string with the column numbers in
	 *  'description' replaced by their column names;
	 *  also, the parentheses have been stripped off.
	 ****/

	public static String getColumnListFromDescription(String tableId,
		String description) throws SQLException
	{

		StringBuffer sb = new StringBuffer();
		StringTokenizer tokenizer = new StringTokenizer(
			description.substring(description.indexOf("(") + 1,
				description.lastIndexOf(")")), " ,", true);

		boolean firstCol = true;
		while (tokenizer.hasMoreTokens()) {

			String tok = tokenizer.nextToken().trim();
			if (tok.equals(""))
				continue;
			else if (tok.equals(",")) {
				firstCol = false;
				continue;
			}
			try {
				String colName = getColNameFromNumber(tableId,
					(Integer.valueOf(tok)).intValue());
				if (!firstCol)
					sb.append(", ");
				sb.append(colName);
			} catch (NumberFormatException e) {
			// not a number; could be "ASC" or "DESC" tag,
			// which is okay; otherwise, something's wrong.
				tok = tok.toUpperCase();
				if (tok.equals("DESC") || tok.equals("ASC"))
				// then this is okay; just add the token to result.
					sb.append(" " + tok);
				else
				// shouldn't happen.
					Logs.debug("INTERNAL ERROR: read a non-number (" +
						tok + ") when a column number was expected:\n" +
						description, (String)null);
			}

		}

		return sb.toString();

	}

	/* ************************************************
	 * getColNameFromNumber:
	 * Takes a tableid and a column number colNum, and
	 * returns the name of the colNum'th column in the
	 * table with tableid.
	 * @param tableid id of the table.
	 * @param colNum number of the column for which we want
	 *  the name.
	 * @return The name of the colNum'th column in the
	 *  table with tableid.
	 ****/

	public static String getColNameFromNumber(String tableId,
		int colNum) throws SQLException
	{

		getColNameFromNumberQuery.setString(1, tableId);
		getColNameFromNumberQuery.setInt(2, colNum);
		ResultSet rs = getColNameFromNumberQuery.executeQuery();

		if (!rs.next()) {
		// shouldn't happen.
			Logs.debug("INTERNAL ERROR: Failed column number " +
				"lookup for table " + lookupTableId(tableId) +
				", column " + colNum, (String)null);
			rs.close();
			return "";
		}
		else {
			String colName = addQuotes(expandDoubleQuotes(rs.getString(1)));
			rs.close();
			return colName;
		}

	}

	/* ************************************************
	 * addQuotes:
	 * Add quotes to the received object name, and return
	 * the result.
	 * @param name the name to which to add quotes.
	 * @return the name with double quotes around it.
	 ****/

	public static String addQuotes(String name) {

		if (name == null)
			return null;

		return "\"" + name + "\"";

	}

	/* ************************************************
	 * stripQuotes:
	 * Takes a name and, if the name is enclosed in
	 * quotes, strips the quotes off.  This method
	 * assumes that the received String either has no quotes,
	 * or has a quote (double or single) as the very first
	 * AND very last character.
	 * @param quotedName a name with quotes as the first
	 *  and last character, or else with no quotes at all.
	 * @return quotedName, without the quotes.
	 ****/

	public static String stripQuotes(String quotedName) {

		if (quotedName == null)
			return null;

		if (!(quotedName.startsWith("'") || quotedName.startsWith("\"")))
		// name doesn't _start_ with a quote, so we do nothing.
			return quotedName;

		if (!(quotedName.endsWith("'") || quotedName.endsWith("\"")))
		// name doesn't _end_ with a quote, so we do nothing.
			return quotedName;