001: package jimm.datavision.source.sql;
002:
003: import jimm.datavision.*;
004: import jimm.datavision.source.*;
005: import jimm.util.XMLWriter;
006: import jimm.util.I18N;
007: import java.sql.*;
008: import java.util.*;
009:
010: /**
011: * A database knows about the tables in a database.
012: *
013: * @author Jim Menard, <a href="mailto:jimm@io.com">jimm@io.com</a>
014: */
015: public class Database extends DataSource {
016:
017: protected static final String[] DB_OBJECT_TYPES = { "TABLE", "VIEW" };
018:
019: protected String driverClassName;
020: protected String connInfo;
021: protected String name;
022: protected String username;
023: protected TreeMap tables;
024: protected HashMap tableCacheMap;
025: protected String schemaName;
026: protected Connection conn;
027: private String password;
028: protected boolean hasPassword;
029: protected boolean connectionOwnedByMe;
030: protected boolean storesLowerCaseIdentifiers;
031: protected boolean storesUpperCaseIdentifiers;
032:
033: public Database(Connection conn, Report report) throws SQLException {
034: super (report, new SQLQuery(report));
035:
036: this .driverClassName = "";
037: this .connInfo = "";
038: this .name = "";
039: this .username = "";
040: this .password = ""; // Not null so we won't bother asking
041: hasPassword = true;
042: tables = null;
043:
044: this .conn = conn;
045: connectionOwnedByMe = false;
046: loadAllTables();
047: }
048:
049: /**
050: * Constructor.
051: *
052: * @param driverClassName database driver class name
053: * @param connInfo database connection info string
054: * @param report the report using this database
055: * @param name the database name
056: * @param user the user name to use when logging in to the database
057: */
058: public Database(String driverClassName, String connInfo,
059: Report report, String name, String user)
060: throws SQLException, ClassNotFoundException,
061: InstantiationException, IllegalAccessException,
062: UserCancellationException {
063: this (driverClassName, connInfo, report, name, user, null, false);
064: }
065:
066: /**
067: * Constructor.
068: *
069: * @param driverClassName database driver class name
070: * @param connInfo database connection info string
071: * @param report the report using this database
072: * @param name the database name
073: * @param user the user name to use when logging in to the database
074: * @param password the database password
075: */
076: public Database(String driverClassName, String connInfo,
077: Report report, String name, String user, String password)
078: throws SQLException, ClassNotFoundException,
079: InstantiationException, IllegalAccessException,
080: UserCancellationException {
081: this (driverClassName, connInfo, report, name, user, password,
082: true);
083: }
084:
085: /**
086: * Constructor.
087: *
088: * @param driverClassName database driver class name
089: * @param connInfo database connection info string
090: * @param report the report using this database
091: * @param name the database name
092: * @param user the user name to use when logging in to the database
093: * @param password the database password
094: * @param givenPassword if <code>true</code>, the password was passed in
095: * to some other constructor
096: */
097: protected Database(String driverClassName, String connInfo,
098: Report report, String name, String user, String password,
099: boolean givenPassword) throws SQLException,
100: ClassNotFoundException, InstantiationException,
101: IllegalAccessException, UserCancellationException {
102: super (report, new SQLQuery(report));
103:
104: this .driverClassName = driverClassName;
105: this .connInfo = connInfo;
106: this .name = name;
107: this .username = (user == null) ? "" : user;
108: this .password = password;
109: tables = null;
110: hasPassword = givenPassword;
111:
112: initializeConnection();
113: loadAllTables();
114: }
115:
116: public boolean canJoinTables() {
117: return true;
118: }
119:
120: public boolean isSQLGenerated() {
121: return true;
122: }
123:
124: public boolean isConnectionEditable() {
125: return true;
126: }
127:
128: public boolean areRecordsSelectable() {
129: return true;
130: }
131:
132: public boolean areRecordsSortable() {
133: return true;
134: }
135:
136: public boolean canGroupRecords() {
137: return true;
138: }
139:
140: /**
141: * Given an id (a column name), returns the column that has that id. If no
142: * column with the specified id exists, returns <code>null</code>. Uses
143: * <code>Table.findColumn</code>.
144: *
145: * @param id a column id
146: * @return a column, or <code>null</code> if no column with the specified
147: * id exists
148: * @see Table#findColumn
149: */
150: public Column findColumn(Object id) {
151: if (tables == null)
152: return null;
153:
154: String str = id.toString();
155: int pos = str.lastIndexOf('.');
156: String tableName = str.substring(0, pos);
157: Table t = findTable(tableName);
158: return t == null ? null : t.findColumn(id);
159: }
160:
161: /**
162: * Given a table name, find the table. The table name may have a schema name
163: * or not. We look for the table first using <var>tableName</var> as-is,
164: * then we use this database's schema name, then we try no schema name
165: * at all.
166: *
167: * @param tableName a table name, perhaps including a schema name.
168: */
169: protected Table findTable(String tableName) {
170: // First try a simple exact match using tableCacheMap. This will often
171: // fail the first time, but after we have found a table using the quite
172: // convoluted search below we store the table in tableCacheMap.
173: Table t = (Table) tableCacheMap.get(tableName);
174: if (t != null)
175: return t;
176:
177: String origTableName = tableName; // Preserve passed-in value for map key
178:
179: String schemaName = null;
180: int pos = tableName.indexOf('.');
181: if (pos >= 0) {
182: schemaName = tableName.substring(0, pos);
183: tableName = tableName.substring(pos + 1);
184: }
185:
186: if (!getReport().caseSensitiveDatabaseNames()) {
187: if (schemaName != null)
188: schemaName = schemaName.toLowerCase();
189: tableName = tableName.toLowerCase();
190: }
191:
192: // First try with table's schema name, if any.
193: if (schemaName != null) {
194: if ((t = findTableWithId(schemaName + '.' + tableName)) != null) {
195: tableCacheMap.put(origTableName, t);
196: return t;
197: }
198: }
199:
200: // Now try with database's schema name if it's different from the
201: // table's schema name.
202: if (name != null) {
203: String dbSchemaName = name;
204: if (!getReport().caseSensitiveDatabaseNames())
205: dbSchemaName = dbSchemaName.toLowerCase();
206:
207: if (!dbSchemaName.equals(schemaName)) {
208: if ((t = findTableWithId(dbSchemaName + '.' + tableName)) != null) {
209: tableCacheMap.put(origTableName, t);
210: return t;
211: }
212: }
213: }
214:
215: // Finally, try with just the table name.
216: if ((t = findTableWithId(tableName)) != null) {
217: tableCacheMap.put(origTableName, t);
218: return t;
219: }
220:
221: return null;
222: }
223:
224: /**
225: * Finds table with given <var>id</var> string. Returns <code>null</code> if
226: * it is not found. If the report says that database names are not
227: * case-sensitive, then we do a case-insensitive comparison.
228: *
229: * @param id a table id; if names are not case-sensitive then <var>id</var>
230: * will be lower-case when it is passed in
231: */
232: protected Table findTableWithId(String id) {
233: boolean caseSensitive = getReport()
234: .caseSensitiveDatabaseNames();
235: for (Iterator iter = tables.keySet().iterator(); iter.hasNext();) {
236: String key = (String) iter.next();
237: if (key.equals(id)
238: || (!caseSensitive && key.equalsIgnoreCase(id)))
239: return (Table) tables.get(key);
240: }
241:
242: return null;
243: }
244:
245: public Iterator tables() {
246: return tables.values().iterator();
247: }
248:
249: public Iterator tablesUsedInReport() {
250: return ((SQLQuery) query).getTablesUsed().iterator();
251: }
252:
253: public Iterator columns() {
254: return new ColumnIterator(tables.values().iterator());
255: }
256:
257: public DataCursor execute() throws SQLException {
258: return new ResultSetRow(conn, (SQLQuery) query);
259: }
260:
261: public boolean storesLowerCaseIdentifiers() {
262: return storesLowerCaseIdentifiers;
263: }
264:
265: public boolean storesUpperCaseIdentifiers() {
266: return storesUpperCaseIdentifiers;
267: }
268:
269: /**
270: * Initializes the connection to the database.
271: */
272: public void initializeConnection() throws ClassNotFoundException,
273: InstantiationException, IllegalAccessException,
274: UserCancellationException {
275: // Keep trying until we succeed. We will only exit if we succeed,
276: // if the user cancels, or if something bad happens while obtaining
277: // the database connection.
278: boolean ok = false;
279: while (!ok) {
280: if (!hasPassword) {
281: // Calling askForPassword sets our user name and password
282: report.askForPassword(this );
283: hasPassword = true;
284: }
285: if (password == null)
286: throw new UserCancellationException(I18N
287: .get("Database.cancelled"));
288:
289: try {
290: if (connInfo == null || connInfo.length() == 0)
291: throw new IllegalArgumentException(I18N
292: .get("Database.missing_conn_info"));
293: if (username == null) // Allow empty username
294: throw new IllegalArgumentException(I18N
295: .get("Database.missing_user_name"));
296: if (password == null) // Allow empty password
297: throw new IllegalArgumentException(I18N
298: .get("Database.null_password"));
299:
300: // Load the database JDBC driver
301: Driver d = (Driver) Class.forName(driverClassName)
302: .newInstance();
303: DriverManager.registerDriver(d);
304:
305: // Connect to the database
306: if (username.length() == 0)
307: conn = DriverManager.getConnection(connInfo);
308: else
309: conn = DriverManager.getConnection(connInfo,
310: username, password);
311: connectionOwnedByMe = true;
312:
313: ok = true;
314: } catch (SQLException sqle) { // Force another password request
315: ErrorHandler.error(sqle);
316: hasPassword = false;
317: report.setDatabasePassword(null);
318: }
319: }
320: }
321:
322: /**
323: * Returns a connection to the database.
324: *
325: * @return a connection to the database
326: */
327: public Connection getConnection() {
328: return conn;
329: }
330:
331: /**
332: * Reset key instance variables, closes current connection, and "reloads"
333: * all table information (compares new info with existing info and complains
334: * if any existing info is not in the new info).
335: * <p>
336: * <em>Note:</em> if the connection we currently have was created by this
337: * object, we close it. If the connection was handed to us, we do
338: * <em>not</em> close the connection.
339: *
340: * @param driverClassName database driver class name
341: * @param connInfo database connection info string
342: * @param dbName database name
343: * @param username the user name to use when logging in to the database
344: */
345: public void reset(String driverClassName, String connInfo,
346: String dbName, String username, String password)
347: throws SQLException, ClassNotFoundException,
348: InstantiationException, IllegalAccessException,
349: UserCancellationException {
350: setDriverClassName(driverClassName);
351: setConnectionInfo(connInfo);
352: setName(dbName);
353: setUserName(username);
354: this .password = password;
355: hasPassword = true;
356:
357: if (conn != null) {
358: if (connectionOwnedByMe)
359: conn.close();
360: conn = null;
361: }
362: initializeConnection();
363: loadAllTables();
364:
365: report.reloadColumns();
366: }
367:
368: /**
369: * Loads information about all tables in the database. If no tables are
370: * found when using the database schema name, try again with a
371: * <code>null</code> schema name.
372: */
373: protected void loadAllTables() throws SQLException {
374: tables = new TreeMap();
375: tableCacheMap = new HashMap();
376: schemaName = null;
377:
378: DatabaseMetaData dbmd = getConnection().getMetaData();
379: storesLowerCaseIdentifiers = dbmd.storesLowerCaseIdentifiers();
380: storesUpperCaseIdentifiers = dbmd.storesUpperCaseIdentifiers();
381:
382: try {
383: // Specify both schema name and DB_OBJECT_TYPES.
384: loadTablesUsingSchemaNameAndTypes(dbmd, name,
385: DB_OBJECT_TYPES);
386: } catch (SQLException e) {
387: } catch (NullPointerException npe) {
388: }
389:
390: try {
391: if (tables.isEmpty() && name != null) // Only schema name
392: loadTablesUsingSchemaNameAndTypes(dbmd, name, null);
393: } catch (SQLException e) {
394: } catch (NullPointerException npe) {
395: }
396:
397: try {
398: if (tables.isEmpty()) // No schema name, use types
399: loadTablesUsingSchemaNameAndTypes(dbmd, null,
400: DB_OBJECT_TYPES);
401: } catch (SQLException e) {
402: } catch (NullPointerException npe) {
403: }
404:
405: // If no tables found, try again with null database name. This time,
406: // throw an exception if there is a problem.
407: if (tables.isEmpty()) // No schema name, no types
408: loadTablesUsingSchemaNameAndTypes(dbmd, null, null);
409: }
410:
411: /**
412: * Loads our list of tables using a database meta data object and a
413: * schema name. The schema name may be <code>null</code>.
414: *
415: * @param dbmd the database meta data object
416: * @param objectTypes a list of database object types
417: * @param schema the schema name; may be <code>null</code>
418: */
419: protected void loadTablesUsingSchemaNameAndTypes(
420: DatabaseMetaData dbmd, String schema, String[] objectTypes)
421: throws SQLException {
422: ResultSet rset = dbmd.getTables(null, schema, "%", objectTypes);
423: if (rset == null)
424: return;
425:
426: boolean schemaNameFailed = false; // Avoid banging our head against a wall
427: while (rset.next()) {
428: String name = rset.getString("TABLE_NAME").trim();
429:
430: if (!schemaNameFailed) {
431: try {
432: schemaName = rset.getString("TABLE_SCHEM");
433: } catch (SQLException sqle) {
434: schemaNameFailed = true;
435: }
436: if (schemaName != null && schemaName.length() > 0)
437: name = schemaName.trim() + '.' + name;
438: }
439:
440: SQLTable t = new SQLTable(this , name, dbmd);
441: tables.put(t.getId().toString(), t);
442: }
443: rset.close();
444: }
445:
446: /**
447: * Returns the driver class name.
448: *
449: * @return the driver class name
450: */
451: public String getDriverClassName() {
452: return driverClassName;
453: }
454:
455: /**
456: * Sets the driver class name.
457: *
458: * @param newDriverClassName the driver class name
459: */
460: protected void setDriverClassName(String newDriverClassName) {
461: driverClassName = newDriverClassName;
462: }
463:
464: /**
465: * Returns the connection info string.
466: *
467: * @return the connection info string
468: */
469: public String getConnectionInfo() {
470: return connInfo;
471: }
472:
473: /**
474: * Sets the connection info string.
475: *
476: * @param newConnectionInfo the connection info string
477: */
478: protected void setConnectionInfo(String newConnectionInfo) {
479: connInfo = newConnectionInfo;
480: }
481:
482: /**
483: * Returns the database name.
484: *
485: * @return the database name
486: */
487: public String getName() {
488: return name;
489: }
490:
491: /**
492: * Sets the name.
493: *
494: * @param newName the new name
495: */
496: protected void setName(String newName) {
497: name = newName;
498: }
499:
500: /**
501: * Returns the user name.
502: *
503: * @return the user name
504: */
505: public String getUserName() {
506: return username;
507: }
508:
509: /**
510: * Sets the user name.
511: *
512: * @param newUserName the new user name
513: */
514: public void setUserName(String newUserName) {
515: username = newUserName;
516: }
517:
518: /**
519: * Returns the password.
520: *
521: * @return the password
522: */
523: public String getPassword() {
524: return hasPassword ? password : null;
525: }
526:
527: /**
528: * Sets the password.
529: *
530: * @param newPassword the new password
531: */
532: public void setPassword(String newPassword) {
533: password = newPassword;
534: hasPassword = true;
535: }
536:
537: /**
538: * Writes this database and all its tables as an XML tag.
539: *
540: * @param out a writer that knows how to write XML
541: */
542: protected void doWriteXML(XMLWriter out) {
543: out.startElement("database");
544: out.attr("driverClassName", driverClassName);
545: out.attr("connInfo", connInfo);
546: out.attr("name", name);
547: out.attr("username", username);
548: out.endElement();
549: }
550:
551: }
|