001: /*
002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
003: *
004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
005: *
006: * The contents of this file are subject to the terms of either the GNU
007: * General Public License Version 2 only ("GPL") or the Common
008: * Development and Distribution License("CDDL") (collectively, the
009: * "License"). You may not use this file except in compliance with the
010: * License. You can obtain a copy of the License at
011: * http://www.netbeans.org/cddl-gplv2.html
012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
013: * specific language governing permissions and limitations under the
014: * License. When distributing the software, include this License Header
015: * Notice in each file and include the License file at
016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
017: * particular file as subject to the "Classpath" exception as provided
018: * by Sun in the GPL Version 2 section of the License file that
019: * accompanied this code. If applicable, add the following below the
020: * License Header, with the fields enclosed by brackets [] replaced by
021: * your own identifying information:
022: * "Portions Copyrighted [year] [name of copyright owner]"
023: *
024: * Contributor(s):
025: *
026: * Portions Copyrighted 2007 Sun Microsystems, Inc.
027: */
028: package org.netbeans.modules.db.util;
029:
030: import java.sql.Connection;
031: import java.sql.DatabaseMetaData;
032: import java.sql.DriverManager;
033: import java.sql.PreparedStatement;
034: import java.sql.ResultSet;
035: import java.sql.ResultSetMetaData;
036: import java.sql.SQLException;
037: import java.sql.Statement;
038: import java.sql.Types;
039: import java.util.Iterator;
040: import java.util.Vector;
041: import java.util.logging.Level;
042: import java.util.logging.Logger;
043: import org.netbeans.lib.ddl.impl.AddColumn;
044: import org.netbeans.lib.ddl.impl.CreateIndex;
045: import org.netbeans.lib.ddl.impl.CreateTable;
046: import org.netbeans.lib.ddl.impl.CreateView;
047: import org.netbeans.lib.ddl.impl.DriverSpecification;
048: import org.netbeans.lib.ddl.impl.Specification;
049: import org.netbeans.lib.ddl.impl.SpecificationFactory;
050: import org.netbeans.lib.ddl.impl.TableColumn;
051: import org.netbeans.modules.db.test.TestBase;
052:
053: /**
054: * This class is a useful base test class that provides initial setup
055: * to get a connecxtion and also a number of utility routines
056: *
057: * @author <a href="mailto:david@vancouvering.com">David Van Couvering</a>
058: */
059: public abstract class DBTestBase extends TestBase {
060:
061: private static final Logger LOGGER = Logger
062: .getLogger(DBTestBase.class.getName());
063:
064: // Change this to get rid of output or to see output
065: protected static final Level DEBUGLEVEL = Level.FINE;
066:
067: protected static final String SCHEMA = "TESTDDL";
068:
069: private static String driverClass;
070: private static String dbUrl;
071: private static String username;
072: private static String password;
073: private static String dbname;
074: protected static String dblocation;
075:
076: private static String DRIVER_PROPERTY = "db.driverclass";
077: private static String URL_PROPERTY = "db.url";
078: private static String USERNAME_PROPERTY = "db.username";
079: private static String PASSWORD_PROPERTY = "db.password";
080: private static String DBDIR_PROPERTY = "db.dir";
081: private static String DBNAME_PROPERTY = "db.name";
082:
083: private static String quoteString = null;
084:
085: // This defines what happens to identifiers when stored in db
086: private static final int RULE_UNDEFINED = -1;
087: public static final int LC_RULE = 0; // everything goes to lower case
088: public static final int UC_RULE = 1; // everything goes to upper case
089: public static final int MC_RULE = 2; // mixed case remains mixed case
090: public static final int QUOTE_RETAINS_CASE = 3; // quoted idents retain case
091:
092: private static int unquotedCaseRule = RULE_UNDEFINED;
093: private static int quotedCaseRule = RULE_UNDEFINED;
094:
095: protected static SpecificationFactory specfactory;
096:
097: protected Connection conn;
098: protected Specification spec;
099: protected DriverSpecification drvSpec;
100:
101: static {
102: try {
103: specfactory = new SpecificationFactory();
104:
105: driverClass = System.getProperty(DRIVER_PROPERTY,
106: "org.apache.derby.jdbc.EmbeddedDriver");
107: dbname = System.getProperty(DBNAME_PROPERTY, "ddltestdb");
108:
109: dblocation = System.getProperty(DBDIR_PROPERTY, "");
110:
111: // Add a slash for the Derby URL syntax if we are
112: // requesting a specific path for database files
113: if (dblocation.length() > 0) {
114: dblocation = dblocation + "/";
115: }
116:
117: LOGGER.log(DEBUGLEVEL, "DB location is " + dblocation);
118:
119: dbUrl = System.getProperty(URL_PROPERTY, "jdbc:derby:"
120: + dblocation + dbname + ";create=true");
121:
122: LOGGER.log(DEBUGLEVEL, "DB URL is " + dbUrl);
123:
124: username = System.getProperty(USERNAME_PROPERTY, "testddl");
125: password = System.getProperty(PASSWORD_PROPERTY, "testddl");
126: } catch (Exception e) {
127: LOGGER.log(Level.SEVERE, null, e);
128: throw new RuntimeException(e);
129: }
130: }
131:
132: public DBTestBase(String name) {
133: super (name);
134: }
135:
136: public void setUp() throws Exception {
137: try {
138: getConnection();
139: createSchema();
140: setSchema();
141: initQuoteString();
142: spec = (Specification) specfactory
143: .createSpecification(conn);
144:
145: drvSpec = specfactory.createDriverSpecification(spec
146: .getMetaData().getDriverName().trim());
147: if (spec.getMetaData().getDriverName().trim().equals(
148: "jConnect (TM) for JDBC (TM)")) //NOI18N
149: //hack for Sybase ASE - copied from mainline code
150: drvSpec.setMetaData(conn.getMetaData());
151: else
152: drvSpec.setMetaData(spec.getMetaData());
153:
154: drvSpec.setCatalog(conn.getCatalog());
155: drvSpec.setSchema(SCHEMA);
156: } catch (SQLException e) {
157: SQLException original = e;
158: while (e != null) {
159: LOGGER.log(Level.SEVERE, null, e);
160: e = e.getNextException();
161: }
162:
163: throw original;
164: }
165: }
166:
167: protected Connection getConnection() throws Exception {
168: return getConnection(false);
169: }
170:
171: private void shutdownDerby() throws Exception {
172: Connection conn = getConnection(true);
173:
174: try {
175: conn.close();
176: } catch (SQLException sqle) {
177:
178: }
179: }
180:
181: private Connection getConnection(boolean shutdown) throws Exception {
182: String url;
183:
184: if (shutdown) {
185: url = dbUrl + ";shutdown=true";
186: } else {
187: url = dbUrl;
188: }
189:
190: Class.forName(driverClass);
191: conn = DriverManager.getConnection(dbUrl, username, password);
192: return conn;
193: }
194:
195: protected void createSchema() throws Exception {
196: dropSchema();
197: conn.createStatement().executeUpdate("CREATE SCHEMA " + SCHEMA);
198: }
199:
200: protected void dropSchema() throws Exception {
201: if (!schemaExists(SCHEMA)) {
202: return;
203: }
204:
205: assert (conn != null);
206:
207: // drop views first, as they depend on tables
208: DatabaseMetaData md = conn.getMetaData();
209:
210: ResultSet rs = md.getTables(null, SCHEMA, null,
211: new String[] { "VIEW" });
212: Vector views = new Vector();
213: while (rs.next()) {
214: String view = rs.getString(3);
215: LOGGER.log(DEBUGLEVEL, "view in schema: " + view);
216: views.add(view);
217: }
218: rs.close();
219:
220: setSchema();
221:
222: Iterator it = views.iterator();
223: while (it.hasNext()) {
224: String view = (String) it.next();
225: dropView(view);
226: }
227:
228: // drop all tables
229: md = conn.getMetaData();
230:
231: rs = md.getTables(null, SCHEMA, null, null);
232: Vector tables = new Vector();
233: while (rs.next()) {
234: String table = rs.getString(3);
235: LOGGER.log(DEBUGLEVEL, "table in schema: " + table);
236: tables.add(table);
237: }
238: rs.close();
239:
240: setSchema();
241:
242: it = tables.iterator();
243: while (it.hasNext()) {
244: String table = (String) it.next();
245: dropTable(table);
246: }
247:
248: // drop schema
249: try {
250: conn.createStatement().executeUpdate(
251: "DROP SCHEMA " + SCHEMA + " RESTRICT");
252: } catch (SQLException e) {
253: LOGGER.log(Level.FINE, null, e);
254: LOGGER.log(DEBUGLEVEL,
255: "Got an exception when attempting to "
256: + "drop the schema: " + e.getMessage());
257: }
258: }
259:
260: protected boolean schemaExists(String schemaName) throws Exception {
261: DatabaseMetaData md = conn.getMetaData();
262:
263: ResultSet rs = md.getSchemas();
264:
265: while (rs.next()) {
266: if (schemaName.equals(rs.getString(1))) {
267: return true;
268: }
269: }
270:
271: return false;
272: }
273:
274: protected void setSchema() throws Exception {
275: PreparedStatement stmt = conn.prepareStatement("SET SCHEMA "
276: + SCHEMA);
277: stmt.executeUpdate();
278: }
279:
280: protected void dropView(String viewname) {
281: try {
282: conn.createStatement().executeUpdate(
283: "DROP VIEW " + viewname);
284: } catch (Exception e) {
285: LOGGER.log(Level.FINE, null, e);
286: LOGGER.log(DEBUGLEVEL, "Got exception trying to drop view "
287: + viewname + ": " + e);
288: }
289: }
290:
291: protected void dropTable(String tablename) {
292: try {
293: Statement stmt = conn.createStatement();
294: stmt.executeUpdate("DROP TABLE " + tablename);
295: } catch (Exception e) {
296: LOGGER.log(Level.FINE, null, e);
297: LOGGER.log(DEBUGLEVEL,
298: "Got exception trying to drop table " + tablename
299: + ": " + e);
300: }
301: }
302:
303: private void initQuoteString() throws Exception {
304: if (quoteString != null) {
305: return;
306: }
307:
308: DatabaseMetaData md = conn.getMetaData();
309: quoteString = md.getIdentifierQuoteString();
310: }
311:
312: protected String quote(String value) throws Exception {
313: if (value == null || value.equals("")) {
314: return value;
315: }
316:
317: if (quoteString == null) {
318: initQuoteString();
319: }
320:
321: return quoteString + value + quoteString;
322: }
323:
324: protected boolean tableExists(String tablename) throws Exception {
325: tablename = fixIdentifier(tablename);
326: DatabaseMetaData md = conn.getMetaData();
327: ResultSet rs = md.getTables(null, SCHEMA, tablename, null);
328: return rs.next();
329: }
330:
331: protected boolean columnExists(String tablename, String colname)
332: throws Exception {
333: tablename = fixIdentifier(tablename);
334: colname = fixIdentifier(colname);
335: DatabaseMetaData md = conn.getMetaData();
336: ResultSet rs = md.getColumns(null, SCHEMA, tablename, colname);
337:
338: int numrows = printResults(rs, "columnExists(" + tablename
339: + ", " + colname + ")");
340:
341: rs.close();
342:
343: return numrows > 0;
344: }
345:
346: protected boolean indexExists(String tablename, String indexname)
347: throws Exception {
348: indexname = fixIdentifier(indexname);
349: DatabaseMetaData md = conn.getMetaData();
350: ResultSet rs = md.getIndexInfo(null, SCHEMA, tablename, false,
351: false);
352:
353: while (rs.next()) {
354: String idx = rs.getString(6);
355: if (idx.equals(indexname)) {
356: return true;
357: }
358: }
359:
360: return false;
361: }
362:
363: protected boolean viewExists(String viewName) throws Exception {
364: DatabaseMetaData md = conn.getMetaData();
365: ResultSet rs = md.getTables(null, SCHEMA,
366: fixIdentifier(viewName), new String[] { "VIEW" });
367:
368: return rs.next();
369: }
370:
371: protected boolean columnInPrimaryKey(String tablename,
372: String colname) throws Exception {
373: tablename = fixIdentifier(tablename);
374: colname = fixIdentifier(colname);
375: DatabaseMetaData md = conn.getMetaData();
376: ResultSet rs = md.getPrimaryKeys(null, SCHEMA, tablename);
377:
378: // printResults(rs, "columnInPrimaryKey(" + tablename + ", " +
379: // colname + ")");
380:
381: while (rs.next()) {
382: String pkCol = rs.getString(4);
383: if (pkCol.equals(colname)) {
384: return true;
385: }
386: }
387:
388: return false;
389: }
390:
391: protected void printAllTables() throws Exception {
392: DatabaseMetaData md = conn.getMetaData();
393: ResultSet rs = md.getTables(null, SCHEMA, "%", null);
394: printResults(rs, "printAllTables()");
395: }
396:
397: protected boolean columnInIndex(String tablename, String colname,
398: String indexname) throws Exception {
399: tablename = fixIdentifier(tablename);
400: colname = fixIdentifier(colname);
401: indexname = fixIdentifier(indexname);
402: DatabaseMetaData md = conn.getMetaData();
403: ResultSet rs = md.getIndexInfo(null, SCHEMA, tablename, false,
404: false);
405:
406: // printResults(rs, "columnInIndex(" + tablename + ", " + colname +
407: // ", " + indexname + ")");
408:
409: while (rs.next()) {
410: String ixName = rs.getString(6);
411: if (ixName != null && ixName.equals(indexname)) {
412: String ixColName = rs.getString(9);
413: if (ixColName.equals(colname)) {
414: return true;
415: }
416: }
417: }
418:
419: return false;
420: }
421:
422: protected boolean columnInAnyIndex(String tablename, String colname)
423: throws Exception {
424: tablename = fixIdentifier(tablename);
425: colname = fixIdentifier(colname);
426: DatabaseMetaData md = conn.getMetaData();
427: ResultSet rs = md.getIndexInfo(null, SCHEMA, tablename, false,
428: false);
429:
430: // printResults(rs, "columnInIndex(" + tablename + ", " + colname +
431: // ", " + indexname + ")");
432:
433: while (rs.next()) {
434: String ixName = rs.getString(6);
435: String ixColName = rs.getString(9);
436: if (ixColName.equals(colname)) {
437: return true;
438: }
439: }
440:
441: return false;
442: }
443:
444: protected boolean indexIsUnique(String tablename, String indexName)
445: throws Exception {
446: tablename = fixIdentifier(tablename);
447: indexName = fixIdentifier(indexName);
448: DatabaseMetaData md = conn.getMetaData();
449: ResultSet rs = md.getIndexInfo(null, SCHEMA, tablename, false,
450: false);
451:
452: // TODO - Parse results
453:
454: rs.close();
455: return true;
456: }
457:
458: /**
459: * Fix an identifier for a metadata call, as the metadata APIs
460: * require identifiers to be in proper case
461: */
462: public String fixIdentifier(String ident) throws Exception {
463: if (unquotedCaseRule == RULE_UNDEFINED) {
464: getCaseRules();
465: }
466:
467: if (isQuoted(ident)) {
468: switch (quotedCaseRule) {
469: case QUOTE_RETAINS_CASE:
470: break;
471: case UC_RULE:
472: ident = ident.toUpperCase();
473: break;
474: case LC_RULE:
475: ident = ident.toLowerCase();
476: break;
477: case MC_RULE:
478: break;
479: default:
480: LOGGER.log(Level.WARNING,
481: "Unexpected identifier rule: +"
482: + unquotedCaseRule
483: + ", assuming case is retained");
484: }
485:
486: return ident.substring(1, ident.length() - 1);
487: } else {
488: switch (unquotedCaseRule) {
489: case UC_RULE:
490: return ident.toUpperCase();
491: case LC_RULE:
492: return ident.toLowerCase();
493: case MC_RULE:
494: return ident;
495: default:
496: LOGGER.log(Level.WARNING,
497: "Unexpected identifier rule: +"
498: + unquotedCaseRule
499: + ", assuming upper case");
500: return ident.toUpperCase();
501: }
502: }
503: }
504:
505: protected boolean isQuoted(String ident) {
506: assert quoteString != null;
507:
508: return ident.startsWith(quoteString)
509: && ident.endsWith(quoteString);
510: }
511:
512: public int getUnquotedCaseRule() throws Exception {
513: getCaseRules();
514: return unquotedCaseRule;
515: }
516:
517: private void getCaseRules() throws Exception {
518: assert conn != null;
519:
520: DatabaseMetaData md;
521:
522: try {
523: md = conn.getMetaData();
524: if (md.storesUpperCaseIdentifiers()) {
525: unquotedCaseRule = UC_RULE;
526: } else if (md.storesLowerCaseIdentifiers()) {
527: unquotedCaseRule = LC_RULE;
528: } else if (md.storesMixedCaseIdentifiers()) {
529: unquotedCaseRule = MC_RULE;
530: } else {
531: unquotedCaseRule = UC_RULE;
532: }
533: } catch (SQLException sqle) {
534: LOGGER
535: .log(
536: Level.INFO,
537: "Exception trying to find out how "
538: + "db stores unquoted identifiers, assuming upper case: "
539: + sqle.getMessage());
540: LOGGER.log(Level.FINE, null, sqle);
541:
542: unquotedCaseRule = UC_RULE;
543: }
544:
545: try {
546: md = conn.getMetaData();
547:
548: if (md.storesLowerCaseQuotedIdentifiers()) {
549: quotedCaseRule = LC_RULE;
550: } else if (md.storesUpperCaseQuotedIdentifiers()) {
551: quotedCaseRule = UC_RULE;
552: } else if (md.storesMixedCaseQuotedIdentifiers()) {
553: quotedCaseRule = MC_RULE;
554: } else {
555: quotedCaseRule = QUOTE_RETAINS_CASE;
556: }
557: } catch (SQLException sqle) {
558: LOGGER
559: .log(
560: Level.INFO,
561: "Exception trying to find out how "
562: + "db stores quoted identifiers, assuming case is retained: "
563: + sqle.getMessage());
564: LOGGER.log(Level.FINE, null, sqle);
565:
566: quotedCaseRule = QUOTE_RETAINS_CASE;
567: }
568: }
569:
570: protected int printResults(ResultSet rs, String queryName)
571: throws Exception {
572: ResultSetMetaData rsmd = rs.getMetaData();
573: int numcols = rsmd.getColumnCount();
574: int numrows = 0;
575:
576: LOGGER.log(DEBUGLEVEL, "RESULTS FROM " + queryName);
577: assert (rs != null);
578:
579: StringBuffer buf = new StringBuffer();
580:
581: buf.append("|");
582: for (int i = 1; i <= numcols; i++) {
583: buf.append(rsmd.getColumnName(i) + "|");
584: }
585: LOGGER.log(DEBUGLEVEL, buf.toString());
586:
587: while (rs.next()) {
588: numrows++;
589: buf = new StringBuffer();
590: buf.append("|");
591: for (int i = 1; i <= numcols; i++) {
592: buf.append(rs.getString(i) + "|");
593: }
594: LOGGER.log(DEBUGLEVEL, buf.toString());
595: }
596:
597: return numrows;
598: }
599:
600: protected void createBasicTable(String tablename, String pkeyName)
601: throws Exception {
602: dropTable(tablename);
603: CreateTable cmd = spec.createCommandCreateTable(tablename);
604: cmd.setObjectOwner(SCHEMA);
605:
606: // primary key
607: TableColumn col = cmd.createPrimaryKeyColumn(pkeyName);
608: col.setColumnType(Types.INTEGER);
609: col.setNullAllowed(false);
610:
611: cmd.execute();
612: }
613:
614: protected void createView(String viewName, String query)
615: throws Exception {
616: CreateView cmd = spec.createCommandCreateView(viewName);
617: cmd.setQuery(query);
618: cmd.setObjectOwner(SCHEMA);
619: cmd.execute();
620:
621: assertFalse(cmd.wasException());
622: }
623:
624: protected void createSimpleIndex(String tablename,
625: String indexname, String colname) throws Exception {
626: // Need to get identifier into correct case because we are
627: // still quoting referred-to identifiers.
628: tablename = fixIdentifier(tablename);
629: CreateIndex xcmd = spec.createCommandCreateIndex(tablename);
630: xcmd.setIndexName(indexname);
631:
632: // *not* unique
633: xcmd.setIndexType(new String());
634:
635: xcmd.setObjectOwner(SCHEMA);
636: xcmd.specifyColumn(fixIdentifier(colname));
637:
638: xcmd.execute();
639: }
640:
641: /**
642: * Adds a basic column. Non-unique, allows nulls.
643: */
644: protected void addBasicColumn(String tablename, String colname,
645: int type, int size) throws Exception {
646: // Need to get identifier into correct case because we are
647: // still quoting referred-to identifiers.
648: tablename = fixIdentifier(tablename);
649: AddColumn cmd = spec.createCommandAddColumn(tablename);
650: cmd.setObjectOwner(SCHEMA);
651: TableColumn col = (TableColumn) cmd.createColumn(colname);
652: col.setColumnType(type);
653: col.setColumnSize(size);
654: col.setNullAllowed(true);
655:
656: cmd.execute();
657: if (cmd.wasException()) {
658: throw new Exception("Unable to add column");
659: }
660: }
661:
662: protected void tearDown() throws Exception {
663: if (conn != null) {
664: try {
665: conn.close();
666: shutdownDerby();
667: } catch (SQLException sqle) {
668: LOGGER.log(Level.INFO,
669: "Got exception closing connection: "
670: + sqle.getMessage());
671: }
672: }
673: }
674: }
|