001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.Connection;
034: import java.sql.DatabaseMetaData;
035: import java.sql.PreparedStatement;
036: import java.sql.ResultSet;
037: import java.sql.ResultSetMetaData;
038: import java.util.ArrayList;
039: import java.util.Iterator;
040: import java.util.List;
041:
042: import junit.framework.TestCase;
043: import junit.framework.TestResult;
044:
045: public class TestDatabaseMetaData extends TestBase {
046:
047: public TestDatabaseMetaData(String name) {
048: super (name);
049: }
050:
051: public void test() throws Exception {
052:
053: Connection conn = newConnection();
054: PreparedStatement pstmt;
055: int updateCount;
056:
057: try {
058: pstmt = conn
059: .prepareStatement("SET PROPERTY \"sql.enforce_strict_size\" true");
060:
061: pstmt.executeUpdate();
062: pstmt.close();
063:
064: pstmt = conn.prepareStatement("DROP TABLE t1 IF EXISTS");
065:
066: pstmt.executeUpdate();
067: pstmt.close();
068:
069: pstmt = conn
070: .prepareStatement("CREATE TABLE t1 (cha CHARACTER, dec DECIMAL, doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, "
071: + "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP );");
072: updateCount = pstmt.executeUpdate();
073:
074: assertTrue("expected update count of zero",
075: updateCount == 0);
076:
077: pstmt = conn
078: .prepareStatement("CREATE INDEX t1 ON t1 (cha );");
079: updateCount = pstmt.executeUpdate();
080: pstmt = conn.prepareStatement("DROP TABLE t2 IF EXISTS");
081: updateCount = pstmt.executeUpdate();
082: pstmt = conn
083: .prepareStatement("CREATE TABLE t2 (cha CHARACTER, dec DECIMAL, doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, "
084: + "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP );");
085: updateCount = pstmt.executeUpdate();
086: pstmt = conn
087: .prepareStatement("CREATE INDEX t2 ON t2 (cha );");
088: updateCount = pstmt.executeUpdate();
089:
090: DatabaseMetaData dbmd = conn.getMetaData();
091: ResultSet rsp = dbmd.getTablePrivileges(null, null, "T1");
092:
093: while (rsp.next()) {
094: System.out.println("Table: " + rsp.getString(3)
095: + " priv: " + rsp.getString(6));
096: }
097:
098: rsp = dbmd.getIndexInfo(null, null, "T1", false, false);
099:
100: while (rsp.next()) {
101: System.out.println("Table: " + rsp.getString(3)
102: + " IndexName: " + rsp.getString(6));
103: }
104:
105: rsp = dbmd.getIndexInfo(null, null, "T2", false, false);
106:
107: while (rsp.next()) {
108: System.out.println("Table: " + rsp.getString(3)
109: + " IndexName: " + rsp.getString(6));
110: }
111:
112: pstmt = conn.prepareStatement("DROP INDEX t2 ON t2;");
113: updateCount = pstmt.executeUpdate();
114: rsp = dbmd.getIndexInfo(null, null, "T2", false, false);
115:
116: assertTrue("expected getIndexInfo returns empty resultset",
117: rsp.next() == false);
118:
119: ResultSet rs = dbmd.getTables(null, null, "T1",
120: new String[] { "TABLE" });
121: ArrayList tablesarr = new ArrayList();
122: int i;
123:
124: for (i = 0; rs.next(); i++) {
125: String tempstr = rs.getString("TABLE_NAME").trim()
126: .toLowerCase();
127:
128: tablesarr.add(tempstr);
129: }
130:
131: rs.close();
132: assertTrue("expected table t1 count of 1", i == 1);
133:
134: Iterator it = tablesarr.iterator();
135:
136: for (; it.hasNext();) {
137:
138: // create new ArrayList and HashMap for the table
139: String tablename = ((String) it.next()).trim();
140: List collist = new ArrayList(30);
141:
142: rs = dbmd.getColumns(null, null, tablename
143: .toUpperCase(), null);
144:
145: for (i = 0; rs.next(); i++) {
146: collist.add(rs.getString("COLUMN_NAME").trim()
147: .toLowerCase());
148: }
149:
150: rs.close();
151: }
152:
153: pstmt = conn.prepareStatement("DROP TABLE t_1 IF EXISTS");
154:
155: pstmt.executeUpdate();
156: pstmt.close();
157:
158: pstmt = conn
159: .prepareStatement("CREATE TABLE t_1 (cha CHARACTER(10), dec DECIMAL(10,2), doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, "
160: + "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP, bool BOOLEAN );");
161: updateCount = pstmt.executeUpdate();
162:
163: assertTrue("expected update count of zero",
164: updateCount == 0);
165:
166: rs = dbmd.getTables(null, null, "T\\_1",
167: new String[] { "TABLE" });
168:
169: for (i = 0; rs.next(); i++) {
170: String tempstr = rs.getString("TABLE_NAME").trim()
171: .toLowerCase();
172:
173: tablesarr.add(tempstr);
174: }
175:
176: rs.close();
177: assertTrue("expected table t_1 count of 1", i == 1);
178:
179: // test various methods
180: dbmd.getPrimaryKeys(null, null, "T_1");
181: dbmd.getImportedKeys(null, null, "T_1");
182: dbmd
183: .getCrossReference(null, null, "T_1", null, null,
184: "T_1");
185:
186: // test ResultSetMetaData
187: pstmt = conn
188: .prepareStatement("INSERT INTO T_1 (cha, dec, doub) VALUES ('name', 10.23, 0)");
189:
190: pstmt.executeUpdate();
191: pstmt.close();
192:
193: pstmt = conn.prepareStatement("SELECT * FROM T_1");
194: rs = pstmt.executeQuery();
195:
196: ResultSetMetaData md = rs.getMetaData();
197: int x = md.getColumnDisplaySize(1);
198: int y = md.getColumnDisplaySize(2);
199: int b = md.getPrecision(2);
200: int c = md.getScale(1);
201: int d = md.getScale(2);
202: String e = md.getColumnClassName(10);
203: boolean testresult = (x == 10) && (y == 13) && (b == 10)
204: && (c == 0) && (d == 2)
205: && e.equals("java.sql.Timestamp");
206:
207: assertTrue("wrong result metadata", testresult);
208:
209: e = md.getColumnClassName(11);
210: testresult = e.equals("java.lang.Boolean");
211:
212: assertTrue("wrong result metadata", testresult);
213: pstmt.close();
214: conn.close();
215: } catch (Exception e) {
216: assertTrue("unable to prepare or execute DDL", false);
217: } finally {
218: conn.close();
219: }
220: }
221:
222: public static void main(String[] args) throws Exception {
223:
224: TestResult result;
225: TestCase test;
226: java.util.Enumeration failures;
227: int count;
228:
229: result = new TestResult();
230: test = new TestDatabaseMetaData("test");
231:
232: test.run(result);
233:
234: count = result.failureCount();
235:
236: System.out.println("TestDatabaseMetaData failure count: "
237: + count);
238:
239: failures = result.failures();
240:
241: while (failures.hasMoreElements()) {
242: System.out.println(failures.nextElement());
243: }
244: }
245: }
|