001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.harness.procedure
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.store;
023:
024: import org.apache.derby.iapi.services.sanity.SanityManager;
025:
026: import org.apache.derby.tools.ij;
027:
028: import java.sql.Connection;
029: import java.sql.Statement;
030: import java.sql.PreparedStatement;
031: import java.sql.ResultSet;
032: import java.sql.SQLException;
033:
034: /**
035: Common utility functions that can be shared across store .java tests.
036: <p>
037: If more than one store tests wants a function, put it here rather than copy
038: it. Hopefully going forward, with enough utility functions adding new store
039: tests will be easier. New store tests should extend this test to pick
040: up access to utility routines - see OnlineCompressTest.java as an example.
041:
042: **/
043: public abstract class BaseTest {
044: private static boolean debug_system_procedures_created = false;
045: protected static boolean verbose = false;
046:
047: abstract public void testList(Connection conn) throws SQLException;
048:
049: void runTests(String[] argv) throws Throwable {
050: ij.getPropertyArg(argv);
051: Connection conn = ij.startJBMS();
052: System.out.println("conn from ij.startJBMS() = " + conn);
053: conn.setAutoCommit(false);
054:
055: try {
056: testList(conn);
057: } catch (SQLException sqle) {
058: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
059: System.out, sqle);
060: sqle.printStackTrace(System.out);
061: }
062: }
063:
064: public BaseTest() {
065: }
066:
067: protected void beginTest(Connection conn, String str)
068: throws SQLException {
069: log("Beginning test: " + str);
070: conn.commit();
071: }
072:
073: protected void testProgress(String str) throws SQLException {
074: log("Executing test: " + str);
075: }
076:
077: protected void endTest(Connection conn, String str)
078: throws SQLException {
079: conn.commit();
080: log("Ending test: " + str);
081: }
082:
083: protected void log(String str) {
084: System.out.println(str);
085: }
086:
087: protected void logError(String str) {
088: System.out.println("ERROR: " + str);
089: }
090:
091: /**
092: * Simple wrapper to execute a sql string.
093: **/
094: public void executeQuery(Connection conn, String stmt_str,
095: boolean commit_query) throws SQLException {
096: Statement stmt = conn.createStatement();
097: stmt.executeUpdate(stmt_str);
098: stmt.close();
099: if (commit_query)
100: conn.commit();
101: }
102:
103: /**
104: * Call consistency checker on the table.
105: * <p>
106: **/
107: protected boolean checkConsistency(Connection conn,
108: String schemaName, String tableName) throws SQLException {
109: Statement s = conn.createStatement();
110:
111: ResultSet rs = s
112: .executeQuery("values SYSCS_UTIL.SYSCS_CHECK_TABLE('"
113: + schemaName + "', '" + tableName + "')");
114:
115: if (!rs.next()) {
116: if (SanityManager.DEBUG) {
117: SanityManager
118: .THROWASSERT("no value from values clause.");
119: }
120: }
121:
122: boolean consistent = rs.getBoolean(1);
123:
124: rs.close();
125:
126: conn.commit();
127:
128: return (consistent);
129: }
130:
131: /**
132: * Call consistency checker on all the tables.
133: * <p>
134: **/
135: protected boolean checkAllConsistency(Connection conn)
136: throws SQLException {
137: Statement s = conn.createStatement();
138:
139: ResultSet rs = s
140: .executeQuery("select schemaname, tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE(schemaname, tablename) "
141: + "from sys.systables a, sys.sysschemas b where a.schemaid = b.schemaid");
142:
143: int table_count = 0;
144:
145: while (rs.next()) {
146: table_count++;
147: if (rs.getInt(3) != 1) {
148: System.out
149: .println("Bad return from consistency check of "
150: + rs.getString(1)
151: + "."
152: + rs.getString(2));
153: }
154: }
155:
156: if (table_count < 5) {
157: // there are at least 5 system catalogs.
158: System.out
159: .println("Something wrong with consistency check query, found only "
160: + table_count + " tables.");
161: }
162:
163: rs.close();
164: s.close();
165:
166: conn.commit();
167:
168: return (true);
169: }
170:
171: /**
172: * Create a system procedures to access SANE debug table routines.
173: * <p>
174: **/
175: protected void createDebugSystemProcedures(Connection conn)
176: throws SQLException {
177: Statement s = conn.createStatement();
178: s
179: .executeUpdate("CREATE FUNCTION D_CONGLOMID_PRINT(DBNAME VARCHAR(128), CONGLOMID INT) RETURNS VARCHAR(32000) RETURNS NULL ON NULL INPUT EXTERNAL NAME 'org.apache.derby.impl.store.raw.data.D_DiagnosticUtil.diag_conglomid' LANGUAGE JAVA PARAMETER STYLE JAVA");
180: s
181: .executeUpdate("CREATE FUNCTION DIAG_CONGLOMID(DBNAME VARCHAR(128), CONGLOMID INT) RETURNS VARCHAR(32000) RETURNS NULL ON NULL INPUT EXTERNAL NAME 'org.apache.derby.impl.store.raw.data.D_DiagnosticUtil.diag_conglomid' LANGUAGE JAVA PARAMETER STYLE JAVA");
182: s.close();
183: conn.commit();
184:
185: debug_system_procedures_created = true;
186: }
187:
188: /**
189: * Return string with table information.
190: * <p>
191: * Dumps summary store information about the table, also dumps extra
192: * information about individual pages into the error log file.
193: **/
194: String dump_table(Connection conn, String schemaName,
195: String tableName, boolean commit_transaction)
196: throws SQLException {
197: if (!debug_system_procedures_created)
198: createDebugSystemProcedures(conn);
199:
200: // run the following query:
201: //
202: // select
203: // sys.systables.tablename,
204: // sys.sysconglomerates.conglomeratenumber,
205: // DIAG_CONGLOMID('wombat', conglomeratenumber)
206: // from sys.systables, sys.sysconglomerates
207: // where
208: // sys.systables.tableid = sys.sysconglomerates.tableid and
209: // sys.systables.schemaid = sys.sysconglomerates.schemaid and
210: // sys.systables.tablename = tableName;
211: //
212: // TODO - really should join with schemaName too.
213:
214: PreparedStatement ps = conn
215: .prepareStatement("select sys.systables.tablename, sys.sysconglomerates.conglomeratenumber, DIAG_CONGLOMID('wombat', conglomeratenumber) from sys.systables, sys.sysconglomerates where sys.systables.tableid = sys.sysconglomerates.tableid and sys.systables.schemaid = sys.sysconglomerates.schemaid and sys.systables.tablename = ?");
216: ps.setString(1, tableName);
217: ResultSet rs = ps.executeQuery();
218:
219: if (!rs.next()) {
220: if (SanityManager.DEBUG) {
221: SanityManager
222: .THROWASSERT("no value from values clause.");
223: }
224: }
225:
226: String dump_table_info = rs.getString(3);
227:
228: rs.close();
229:
230: if (commit_transaction)
231: conn.commit();
232:
233: return (dump_table_info);
234:
235: }
236:
237: /**
238: * Get lock table.
239: * <p>
240: * Returns a single string with a dump of the entire lock table.
241: * <p>
242: *
243: * @return The lock table.
244: *
245: * @param conn The connection to use.
246: * @param include_system_locks If true include non-user locks like those
247: * requested by background internal threads.
248: *
249: **/
250: protected String get_lock_info(Connection conn,
251: boolean include_system_locks) throws SQLException {
252: // Run the following query to get the current locks in the system,
253: // toggling the "t.type='UserTransaction'" based on
254: // include_system_locks input:
255: //
256: // select
257: // cast(l.xid as char(8)) as xid,
258: // cast(username as char(8)) as username,
259: // cast(t.type as char(8)) as trantype,
260: // cast(l.type as char(8)) as type,
261: // cast(lockcount as char(3)) as cnt,
262: // cast(mode as char(4)) as mode,
263: // cast(tablename as char(12)) as tabname,
264: // cast(lockname as char(10)) as lockname,
265: // state,
266: // status
267: // from
268: // SYSCS_DIAG.LOCK_TABLE l
269: // right outer join SYSCS_DIAG.TRANSACTION_TABLE t
270: // on l.xid = t.xid where l.tableType <> 'S' and
271: // t.type='UserTransaction'
272: // order by
273: // tabname, type desc, mode, cnt, lockname;
274: String lock_query = "select cast(l.xid as char(8)) as xid, cast(username as char(8)) as username, cast(t.type as char(8)) as trantype, cast(l.type as char(8)) as type, cast(lockcount as char(3)) as cnt, cast(mode as char(4)) as mode, cast(tablename as char(12)) as tabname, cast(lockname as char(10)) as lockname, state, status from SYSCS_DIAG.LOCK_TABLE l right outer join SYSCS_DIAG.LOCK_TABLE t on l.xid = t.xid where l.tableType <> 'S' ";
275: if (!include_system_locks)
276: lock_query += "and t.type='UserTransaction' ";
277:
278: lock_query += "order by tabname, type desc, mode, cnt, lockname";
279:
280: PreparedStatement ps = conn.prepareStatement(lock_query);
281:
282: ResultSet rs = ps.executeQuery();
283:
284: String lock_output = "xid |username|trantype|type |cnt|mode|tabname |lockname |state|status\n"
285: + "---------------------------------------------------------------------------------\n";
286: while (rs.next()) {
287: String username = rs.getString(1);
288: String trantype = rs.getString(2);
289: String type = rs.getString(3);
290: String lockcount = rs.getString(4);
291: String mode = rs.getString(5);
292: String tabname = rs.getString(6);
293: String lockname = rs.getString(7);
294: String state = rs.getString(8);
295: String status = rs.getString(9);
296:
297: lock_output += username + "|" + trantype + "|" + type + "|"
298: + lockcount + "|" + mode + "|" + tabname + "|"
299: + lockname + "|" + state + "|" + status + "\n";
300: }
301:
302: rs.close();
303:
304: return (lock_output);
305: }
306:
307: /**
308: * create given table on the input connection.
309: * <p>
310: * Takes care of dropping the table if it exists already.
311: * <p>
312: *
313: * @exception StandardException Standard exception policy.
314: **/
315: public void createTable(Connection conn, String tbl_name,
316: String create_str) throws SQLException {
317: Statement stmt = conn.createStatement();
318:
319: // drop table, ignore table does not exist error.
320:
321: try {
322: stmt.executeUpdate("drop table " + tbl_name);
323: } catch (Exception e) {
324: // ignore drop table errors.
325: }
326:
327: stmt.executeUpdate(create_str);
328: }
329:
330: /**
331: * call the space table vti.
332: * <p>
333: * Utility test function to call the space table vti to get information
334: * about allocated and free pages. Information is passed back in an
335: * int array as follows:
336: * is_index = ret_info[0];
337: * num_alloc = ret_info[1];
338: * num_free = ret_info[2];
339: * page_size = ret_info[3];
340: * estimate_space_savings = ret_info[4];
341: * <p>
342: *
343: * @return the space information about the table.
344: *
345: * @exception StandardException Standard exception policy.
346: **/
347: protected static final int SPACE_INFO_IS_INDEX = 0;
348: protected static final int SPACE_INFO_NUM_ALLOC = 1;
349: protected static final int SPACE_INFO_NUM_FREE = 2;
350: protected static final int SPACE_INFO_NUM_UNFILLED = 3;
351: protected static final int SPACE_INFO_PAGE_SIZE = 4;
352: protected static final int SPACE_INFO_ESTIMSPACESAVING = 5;
353:
354: protected static final int SPACE_INFO_NUMCOLS = 6;
355:
356: protected int[] getSpaceInfo(Connection conn, String schemaName,
357: String tableName, boolean commit_xact) throws SQLException {
358: String stmt_str = "select conglomeratename, isindex, numallocatedpages, numfreepages, numunfilledpages, pagesize, estimspacesaving from new org.apache.derby.diag.SpaceTable('"
359: + tableName + "') t where isindex = 0";
360: PreparedStatement space_stmt = conn.prepareStatement(stmt_str);
361: ResultSet rs = space_stmt.executeQuery();
362:
363: if (!rs.next()) {
364: if (SanityManager.DEBUG) {
365: SanityManager
366: .THROWASSERT("No rows returned from space table query on table: "
367: + schemaName + "." + tableName);
368: }
369: }
370:
371: int[] ret_info = new int[SPACE_INFO_NUMCOLS];
372: String conglomerate_name = rs.getString(1);
373: for (int i = 0; i < SPACE_INFO_NUMCOLS; i++) {
374: ret_info[i] = rs.getInt(i + 2);
375: }
376:
377: if (rs.next()) {
378: if (SanityManager.DEBUG) {
379: SanityManager
380: .THROWASSERT("More than one row returned from space query on table: "
381: + schemaName + "." + tableName);
382: }
383: }
384:
385: if (verbose) {
386: System.out.println("Space information for " + schemaName
387: + "." + tableName + ":");
388: System.out.println("isindex = "
389: + ret_info[SPACE_INFO_IS_INDEX]);
390: System.out.println("num_alloc = "
391: + ret_info[SPACE_INFO_NUM_ALLOC]);
392: System.out.println("num_free = "
393: + ret_info[SPACE_INFO_NUM_FREE]);
394: System.out.println("num_unfilled = "
395: + ret_info[SPACE_INFO_NUM_UNFILLED]);
396: System.out.println("page_size = "
397: + ret_info[SPACE_INFO_PAGE_SIZE]);
398: System.out.println("estimspacesaving = "
399: + ret_info[SPACE_INFO_ESTIMSPACESAVING]);
400: }
401:
402: rs.close();
403:
404: if (commit_xact)
405: conn.commit();
406:
407: return (ret_info);
408: }
409:
410: /**
411: * Given output from getSpaceInfo(), return total pages in file.
412: * <p>
413: * simply the sum of allocated and free pages.
414: *
415: **/
416: protected int total_pages(int[] space_info) {
417: return (space_info[SPACE_INFO_NUM_FREE] + space_info[SPACE_INFO_NUM_ALLOC]);
418: }
419: }
|