metadata_test.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata_test

   Copyright 1999, 2004 The Apache Software Foundation or its licensors, as applicable.

   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 org.apache.derbyTesting.functionTests.tests.jdbcapi;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Timestamp;
import java.sql.Time;
import java.sql.Date;
import java.math.BigDecimal;

import java.util.Properties;

import org.apache.derby.tools.ij;

/**
 * Test of database meta-data.  This program simply calls each of the meta-data
 * methods, one by one, and prints the results.  The test passes if the printed
 * results match a previously stored "master".  Thus this test cannot actually
 * discern whether it passes or not.
 *
 */

public abstract class metadata_test {

	// Ids for the Derby internal procedures that are used to fetch
	// some of the metadata.

	protected static final int GET_PROCEDURES = 0;
 	protected static final int GET_PROCEDURE_COLUMNS = 1;
 	protected static final int GET_TABLES = 2;
 	protected static final int GET_COLUMNS = 3;
 	protected static final int GET_COLUMN_PRIVILEGES = 5;
 	protected static final int GET_TABLE_PRIVILEGES = 6;
 	protected static final int GET_BEST_ROW_IDENTIFIER = 7;
 	protected static final int GET_VERSION_COLUMNS = 8;
 	protected static final int GET_PRIMARY_KEYS = 9;
 	protected static final int GET_IMPORTED_KEYS = 10;
 	protected static final int GET_EXPORTED_KEYS = 11;
 	protected static final int GET_CROSS_REFERENCE = 12;
 	protected static final int GET_TYPE_INFO = 13;
 	protected static final int GET_INDEX_INFO = 14;

	protected static final int IGNORE_PROC_ID = -1;

	// We leave it up to the classes which extend this one to
	// initialize the following fields at contruct time.
	protected Connection con;
	protected static Statement s;

	protected void runTest() {

		DatabaseMetaData met;
		ResultSet rs;
		ResultSetMetaData rsmet;

		System.out.println("Test metadata starting");

		try
		{

			// test decimal type and other numeric types precision, scale,
			// and display width after operations, beetle 3875, 3906
			s.execute("create table t (i int, s smallint, r real, "+
				"d double precision, dt date, t time, ts timestamp, "+
				"c char(10), v varchar(40) not null, dc dec(10,2))");
			s.execute("insert into t values(1,2,3.3,4.4,date('1990-05-05'),"+
						 "time('12:06:06'),timestamp('1990-07-07 07:07:07.07'),"+
						 "'eight','nine', 11.1)");

			// test decimal type and other numeric types precision, scale,
			// and display width after operations, beetle 3875, 3906
			//rs = s.executeQuery("select dc from t where tn = 10 union select dc from t where i = 1");
			rs = s.executeQuery("select dc from t where dc = 11.1 union select dc from t where i = 1");
			rsmet = rs.getMetaData();
			System.out.println("Column display size of the union result is: " + rsmet.getColumnDisplaySize(1));
			rs.close();

			rs = s.executeQuery("select dc, dc, r+dc, d-dc, dc-d from t");
			rsmet = rs.getMetaData();
			System.out.println("dec(10,2) -- precision: " + rsmet.getPrecision(1) + " scale: " + rsmet.getScale(1) + " display size: " + rsmet.getColumnDisplaySize(1) + " type name: " + rsmet.getColumnTypeName(1));
			System.out.println("dec(10,2) -- precision: " + rsmet.getPrecision(2) + " scale: " + rsmet.getScale(2) + " display size: " + rsmet.getColumnDisplaySize(2) + " type name: " + rsmet.getColumnTypeName(2));
			System.out.println("real + dec(10,2) -- precision: " + rsmet.getPrecision(3) + " scale: " + rsmet.getScale(3) + " display size: " + rsmet.getColumnDisplaySize(3) + " type name: " + rsmet.getColumnTypeName(3));
			System.out.println("double precision - dec(10,2) -- precision: " + rsmet.getPrecision(4) + " scale: " + rsmet.getScale(4) + " display size: " + rsmet.getColumnDisplaySize(4) + " type name: " + rsmet.getColumnTypeName(4));
			// result is double, precision/scale don't make sense
			System.out.println("dec(10,2) - double precision -- precision: " + rsmet.getPrecision(5) + " scale: " + rsmet.getScale(5) + " display size: " + rsmet.getColumnDisplaySize(5) + " type name: " + rsmet.getColumnTypeName(5));
			while (rs.next())
				System.out.println("result row: " + rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + " " + rs.getString(5));
			rs.close();

			s.execute("insert into t values(1,2,3.3,4.4,date('1990-05-05'),"+
						 "time('12:06:06'),timestamp('1990-07-07 07:07:07.07'),"+
						 "'eight','nine', 11.11)");

			// test decimal/integer static column result scale consistent
			// with result set metadata after division, beetle 3901
			rs = s.executeQuery("select dc / 2 from t");
			rsmet = rs.getMetaData();
			System.out.println("Column result scale after division is: " + rsmet.getScale(1));
			while (rs.next())
				System.out.println("dc / 2 = " + rs.getString(1));
			rs.close();


			s.execute("create table louie (i int not null default 10, s smallint not null, " +
				      "c30 char(30) not null, " +
					  "vc10 varchar(10) not null default 'asdf', " +
					  "constraint PRIMKEY primary key(vc10, i), " +
					  "constraint UNIQUEKEY unique(c30, s), " + 
					  "ai bigint generated always as identity (start with -10, increment by 2001))");

			// Create another unique index on louie
			s.execute("create unique index u1 on louie(s, i)");
			// Create a non-unique index on louie
			s.execute("create index u2 on louie(s)");
			// Create a view on louie
			s.execute("create view screwie as select * from louie");

			// Create a foreign key
			s.execute("create table reftab (vc10 varchar(10), i int, " +
					  "s smallint, c30 char(30), " +
					  "s2 smallint, c302 char(30), " +
					  "dprim decimal(5,1) not null, dfor decimal(5,1) not null, "+
					  "constraint PKEY_REFTAB	primary key (dprim), " + 
					  "constraint FKEYSELF 		foreign key (dfor) references reftab, "+
					  "constraint FKEY1 		foreign key(vc10, i) references louie, " + 
				  	  "constraint FKEY2 		foreign key(c30, s2) references louie (c30, s), "+
				  	  "constraint FKEY3 		foreign key(c30, s) references louie (c30, s))");

			s.execute("create table reftab2 (t2_vc10 varchar(10), t2_i int, " +
					  "constraint T2_FKEY1 		foreign key(t2_vc10, t2_i) references louie)");

			// Create a table with all types
			s.execute("create table alltypes ( "+
							//"bitcol16_______ bit(16), "+
							//"bitvaryingcol32 bit varying(32), "+ 
							//"tinyintcol tinyint, "+
							"smallintcol smallint, "+
							"intcol int default 20, "+
							"bigintcol bigint, "+
							"realcol real, "+
							"doublepreccol double precision default 10, "+
							"floatcol float default 8.8, "+
							"decimalcol10p4s decimal(10,4), "+
							"numericcol20p2s numeric(20,2), "+
							"char8col___ char(8), "+
							"char8forbitcol___ char(8) for bit data, "+
							"varchar9col varchar(9), "+
							"varchar9bitcol varchar(9) for bit data, "+
							"longvarcharcol long varchar,"+
							"longvarbinarycol long varchar for bit data, "+
							//"nchar10col nchar(10)"
					  //+ ", nvarchar8col nvarchar(8)"
					  //+ ", longnvarchar long nvarchar"
					  //+ ", 
							"blobcol blob(3K), "+
							"clobcol clob(3K), "+
							"datecol date, "+
							"timecol time, "+
							"tscol timestamp"
					  + ")" );
			// test for beetle 4620
			s.execute("CREATE TABLE INFLIGHT(FLT_NUM CHAR(20) NOT NULL," + 
						"FLT_ORIGIN CHAR(6), " +
						"FLT_DEST CHAR(6),  " +
						"FLT_AIRCRAFT CHAR(20), " +
						"FLT_FLYING_TIME VARCHAR(22), "+
						"FLT_DEPT_TIME CHAR(8),  "+
						"FLT_ARR_TIME CHAR(8),  "+
						"FLT_NOTES VARCHAR(510), "+ 
						"FLT_DAYS_OF_WK CHAR(14), "+ 
						"FLT_CRAFT_PIC VARCHAR(32672), "+
						"PRIMARY KEY(FLT_NUM))");

			// Create procedures so we can test 
			// getProcedureColumns()
                        s.execute("create procedure GETPCTEST1 (" +
				// for creating, the procedure's params do not need to exactly match the method's
				"out outb VARCHAR(3), a VARCHAR(3), b NUMERIC, c SMALLINT, " +
				"e SMALLINT, f INTEGER, g BIGINT, h FLOAT, i DOUBLE PRECISION, " +
				"k DATE, l TIME, T TIMESTAMP )"+
				"language java external name " +
				"'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" +
							" parameter style java"); 
                        s.execute("create procedure GETPCTEST2 (pa INTEGER, pb BIGINT)"+
				"language java external name " +
				"'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" +
				" parameter style java"); 
                        s.execute("create procedure GETPCTEST3A (STRING1 VARCHAR(5), out STRING2 VARCHAR(5))"+
				"language java external name " +
				"'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" +
				" parameter style java"); 
                        s.execute("create procedure GETPCTEST3B (in STRING3 VARCHAR(5), inout STRING4 VARCHAR(5))"+
				"language java external name " +
				"'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc'" +
				" parameter style java"); 
                        s.execute("create procedure GETPCTEST4A()  "+
				"language java external name " +
				"'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4a'"+
				" parameter style java"); 
                        s.execute("create procedure GETPCTEST4B() "+
				"language java external name " +
				"'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b'" +
				" parameter style java"); 
                        s.execute("create procedure GETPCTEST4Bx(out retparam INTEGER) "+
				"language java external name " +
				"'org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata.getpc4b'" +
				" parameter style java"); 

			met = con.getMetaData();

			System.out.println("JDBC Driver '" + met.getDriverName() +
							   "', version " + met.getDriverMajorVersion() +
							   "." + met.getDriverMinorVersion() +
							   " (" + met.getDriverVersion() + ")");

			try {
			    System.out.println("The URL is: " + met.getURL());
			} catch (Throwable err) {
			    System.out.println("%%getURL() gave the exception: " + err);
			}

			System.out.println("allTablesAreSelectable(): " +
							   met.allTablesAreSelectable());
			
			System.out.println("maxColumnNameLength(): " + met.getMaxColumnNameLength());

			System.out.println();
			System.out.println("getSchemas():");
			dumpRS(met.getSchemas());

			System.out.println();
			System.out.println("getCatalogs():");
			dumpRS(met.getCatalogs());

			System.out.println("getSearchStringEscape(): " +
							   met.getSearchStringEscape());

			System.out.println("getSQLKeywords(): " +
							   met.getSQLKeywords());

			System.out.println("getDefaultTransactionIsolation(): " +
							   met.getDefaultTransactionIsolation());

			System.out.println("getProcedures():");
			dumpRS(GET_PROCEDURES, getMetaDataRS(met, GET_PROCEDURES,
				new String [] {null, "%", "GETPCTEST%"},
				null, null, null));


			/*
			 * any methods that were not tested above using code written
			 * specifically for it will now be tested in a generic way.
			 */


			System.out.println("allProceduresAreCallable(): " +
							   met.allProceduresAreCallable());
			System.out.println("getUserName(): " +
							   met.getUserName());
			System.out.println("isReadOnly(): " +
							   met.isReadOnly());
			System.out.println("nullsAreSortedHigh(): " +
							   met.nullsAreSortedHigh());
			System.out.println("nullsAreSortedLow(): " +
							   met.nullsAreSortedLow());
			System.out.println("nullsAreSortedAtStart(): " +
							   met.nullsAreSortedAtStart());
			System.out.println("nullsAreSortedAtEnd(): " +
							   met.nullsAreSortedAtEnd());


			System.out.println("getDatabaseProductName(): " + met.getDatabaseProductName());

			String v = met.getDatabaseProductVersion();
			int l = v.indexOf('(');
			if (l<0) l = v.length();
			v = v.substring(0,l);
			System.out.println("getDatabaseProductVersion(): " + v);
			System.out.println("getDriverVersion(): " +
							   met.getDriverVersion());
			System.out.println("usesLocalFiles(): " +
							   met.usesLocalFiles());
			System.out.println("usesLocalFilePerTable(): " +
							   met.usesLocalFilePerTable());
			System.out.println("supportsMixedCaseIdentifiers(): " +
							   met.supportsMixedCaseIdentifiers());
			System.out.println("storesUpperCaseIdentifiers(): " +
							   met.storesUpperCaseIdentifiers());
			System.out.println("storesLowerCaseIdentifiers(): " +
							   met.storesLowerCaseIdentifiers());
			System.out.println("storesMixedCaseIdentifiers(): " +
							   met.storesMixedCaseIdentifiers());
			System.out.println("supportsMixedCaseQuotedIdentifiers(): " +
							   met.supportsMixedCaseQuotedIdentifiers());
			System.out.println("storesUpperCaseQuotedIdentifiers(): " +
							   met.storesUpperCaseQuotedIdentifiers());
			System.out.println("storesLowerCaseQuotedIdentifiers(): " +
							   met.storesLowerCaseQuotedIdentifiers());
			System.out.println("storesMixedCaseQuotedIdentifiers(): " +
							   met.storesMixedCaseQuotedIdentifiers());
			System.out.println("getIdentifierQuoteString(): " +
							   met.getIdentifierQuoteString());
			System.out.println("getNumericFunctions(): " +
							   met.getNumericFunctions());
			System.out.println("getStringFunctions(): " +
							   met.getStringFunctions());
			System.out.println("getSystemFunctions(): " +