001: package org.dbbrowser.db.engine.queryengine;
002:
003: import infrastructure.logging.Log;
004: import java.sql.ResultSet;
005: import java.sql.ResultSetMetaData;
006: import java.sql.SQLException;
007: import java.sql.Statement;
008: import java.util.ArrayList;
009: import java.util.Date;
010: import java.util.List;
011: import org.dbbrowser.db.engine.exception.DBEngineException;
012: import org.dbbrowser.db.engine.model.ColumnInfo;
013: import org.dbbrowser.db.engine.model.DBRow;
014: import org.dbbrowser.db.engine.model.DBTable;
015: import org.dbbrowser.db.engine.model.DBTableCell;
016: import org.dbbrowser.db.engine.model.View;
017: import org.dbbrowser.db.engine.SQLLog;
018:
019: /**
020: * The engine which runs the SQL queries on an Oracle DBMS
021: * @author amangat
022: *
023: */
024: public class Oracle9iDBQueryEngine extends AbstractDBQueryEngine {
025: /**
026: * Constructer
027: * @param statement
028: */
029: public Oracle9iDBQueryEngine(Statement statement) {
030: super (statement);
031: }
032:
033: public List listSchemas() throws DBEngineException {
034: Log.getInstance().infoMessage(
035: "Listing schemas in Oracle database",
036: this .getClass().getName());
037:
038: List listOfSchemas = new ArrayList();
039: try {
040: //To see tables in this schema
041: //String sql = "select TABLESPACE_NAME from user_tables";
042:
043: //SQL to get the tables in the user tablespaces
044: //String sql = "select distinct tablespace_name from all_tables";
045:
046: String sql = "select distinct owner from all_tables";
047:
048: //Log the SQL
049: SQLLog.getInstance().logSQLStatement(sql);
050:
051: ResultSet rs = this .getStatement().executeQuery(sql);
052: while (rs.next()) {
053: String schema = rs.getString("owner");
054:
055: if (schema != null) {
056: listOfSchemas.add(schema);
057: }
058: }
059: rs.close();
060: } catch (SQLException exc) {
061: throw new DBEngineException(exc.getMessage());
062: }
063:
064: Log.getInstance().infoMessage(
065: "Found " + listOfSchemas.size()
066: + " schemas in Oracle database",
067: this .getClass().getName());
068: return listOfSchemas;
069: }
070:
071: /**
072: * Returns a list of views accessible to the user. It is empty if there are no views for the user.
073: * @return - a list of Strings
074: * @throws DBEngineException
075: */
076: public List listViews() throws DBEngineException {
077: Log.getInstance().infoMessage(
078: "Listing views in Oracle database",
079: this .getClass().getName());
080:
081: List listOfViews = new ArrayList();
082: try {
083: String sql = "select view_name from user_views";
084:
085: //Log the SQL
086: SQLLog.getInstance().logSQLStatement(sql);
087:
088: ResultSet rs = this .getStatement().executeQuery(sql);
089: while (rs.next()) {
090: String viewName = rs.getString("view_name");
091:
092: if (viewName != null) {
093: View view = new View(null, viewName, null);
094: listOfViews.add(view);
095: }
096: }
097: rs.close();
098: } catch (SQLException exc) {
099: throw new DBEngineException(exc.getMessage());
100: }
101:
102: Log.getInstance().infoMessage(
103: "Found " + listOfViews.size()
104: + " views in Oracle database",
105: this .getClass().getName());
106: return listOfViews;
107: }
108:
109: /**
110: * Returns the SQL used to create a view
111: * @return - a String
112: * @throws DBEngineException
113: */
114: public String getSQLForView(View view) throws DBEngineException {
115: Log.getInstance().infoMessage(
116: "Getting SQL for " + view.getViewName()
117: + " in Oracle database",
118: this .getClass().getName());
119:
120: String sqlForView = null;
121: try {
122: String sql = "select text from user_views where view_name='"
123: + view.getViewName() + "'";
124:
125: //Log the SQL
126: SQLLog.getInstance().logSQLStatement(sql);
127:
128: ResultSet rs = this .getStatement().executeQuery(sql);
129: if (rs.next()) {
130: sqlForView = rs.getString("text");
131: }
132: rs.close();
133: } catch (SQLException exc) {
134: throw new DBEngineException(exc.getMessage());
135: }
136:
137: Log.getInstance().infoMessage(
138: "SQL for view " + view.getViewName() + " is "
139: + sqlForView + " in Oracle database",
140: this .getClass().getName());
141: return sqlForView;
142: }
143:
144: /**
145: * Returns a list of tables in the table space
146: * @param schemaName
147: * @return - a list of Strings
148: * @throws DBEngineException
149: */
150: public List listTablesInSchema(String schemaName)
151: throws DBEngineException {
152: Log.getInstance().infoMessage(
153: "Listing tables in " + schemaName + "...",
154: this .getClass().getName());
155:
156: List listOfTables = new ArrayList();
157: try {
158: String sql = "select distinct table_name from all_tables where owner = '"
159: + schemaName + "'";
160:
161: //Log the SQL
162: SQLLog.getInstance().logSQLStatement(sql);
163:
164: ResultSet rs = this .getStatement().executeQuery(sql);
165: while (rs.next()) {
166: String tablename = rs.getString("table_name");
167: listOfTables.add(tablename);
168: }
169: rs.close();
170: } catch (SQLException exc) {
171: throw new DBEngineException(exc.getMessage());
172: }
173:
174: Log.getInstance().infoMessage(
175: "Found " + listOfTables.size() + " tables in "
176: + schemaName, this .getClass().getName());
177:
178: return listOfTables;
179: }
180:
181: /**
182: * Get all the data in a table
183: * @param schemaName
184: * @param tableName
185: * @param offset
186: * @param numberOfRowsToReturn
187: * @return
188: * @throws DBEngineException
189: */
190: public DBTable getAllDataInATable(String schemaName,
191: String tableName, Integer offset,
192: Integer numberOfRowsToReturn) throws DBEngineException {
193: Log.getInstance().infoMessage(
194: "Getting all data in " + schemaName + "." + tableName
195: + "...", this .getClass().getName());
196:
197: //Get the number of rows in the table
198: Integer rowCount = this .getRowCount(schemaName, tableName);
199:
200: //Get the columns in the table
201: List listOfColumnsInATable = listColumnsInATable(schemaName,
202: tableName);
203:
204: //Get the primary key columns in the table
205: List listOfPrimaryKeyColumns = this .getPrimaryKeyColumnNames(
206: schemaName, tableName);
207:
208: String column = "";
209: //if there no primary keys, use the first column in the table
210: if (listOfPrimaryKeyColumns.isEmpty()) {
211: ColumnInfo ci = (ColumnInfo) listOfColumnsInATable.get(0);
212: column = ci.getColumnName();
213: } else {
214: //Use the first primary key
215: column = (String) listOfPrimaryKeyColumns.get(0);
216: }
217:
218: //select * from legacy_ctr_claim where rownum < 9 and claim_id in ( SELECT claim_id FROM legacy_ctr_claim group by claim_id ) minus
219: //select * from legacy_ctr_claim where rownum < 7 and claim_id in ( SELECT claim_id FROM legacy_ctr_claim group by claim_id )
220:
221: //Set the table data
222: String sql = "select * ";
223: /*for(int i=0; i<listOfColumnsInATable.size(); i++)
224: {
225: ColumnInfo columnInfo = (ColumnInfo)listOfColumnsInATable.get(i);
226: sql = sql + " " + columnInfo.getColumnName();
227: if( (i+1) < listOfColumnsInATable.size())
228: {
229: sql = sql + ", ";
230: }
231: }*/
232:
233: //sql = sql + " from " + tableName;
234: sql = sql + " from " + schemaName + "." + tableName;
235:
236: //Add the clause which allows us to restrict the number of rows
237: sql = sql + " where rownum <= "
238: + (numberOfRowsToReturn.intValue() + offset.intValue())
239: + " and " + column + " in " + " ( select " + column
240: + " from " + schemaName + "." + tableName
241: + " group by " + column + " ) " + " minus "
242: + " select * from " + schemaName + "." + tableName
243: + " where rownum < " + (offset.intValue() + 1)
244: + " and " + column + " in " + " ( select " + column
245: + " from " + schemaName + "." + tableName
246: + " group by " + column + " ) ";
247:
248: Log.getInstance().debugMessage("SQL is: " + sql,
249: this .getClass().getName());
250:
251: //Log the SQL
252: SQLLog.getInstance().logSQLStatement(sql);
253:
254: //Run the SQL
255: ResultSet rs = null;
256: DBTable dbTable = null;
257: List rows = new ArrayList();
258: try {
259: rs = this .getStatement().executeQuery(sql);
260:
261: //Get the data from the result set and build a DBTable
262: while (rs.next()) {
263: //Get the data for every column
264: List listOfRowData = new ArrayList();
265: for (int i = 0; i < listOfColumnsInATable.size(); i++) {
266: ColumnInfo columnInfo = (ColumnInfo) listOfColumnsInATable
267: .get(i);
268: String columnName = columnInfo.getColumnName();
269: Object o = rs.getObject(columnName);
270:
271: //if it is an oracle timestamp, get the timestamp
272: if ((o != null)
273: && ("oracle.sql.TIMESTAMP".equals(o
274: .getClass().getName()))) {
275: o = rs.getTimestamp(columnName);
276: }
277:
278: //if it is a date, get the date as a timestamp as date sometimes does not have hours and minutes data
279: if ((o != null) && (o instanceof Date)) {
280: o = rs.getTimestamp(columnName);
281: }
282:
283: DBTableCell dbTableCell = new DBTableCell(
284: columnInfo, o, Boolean.FALSE);
285:
286: listOfRowData.add(dbTableCell);
287: }
288: DBRow dbRow = new DBRow(listOfRowData);
289: rows.add(dbRow);
290: }
291:
292: if (rows.isEmpty()) {
293: dbTable = new DBTable(schemaName, tableName, rows,
294: offset, numberOfRowsToReturn,
295: listOfColumnsInATable);
296: } else {
297: dbTable = new DBTable(schemaName, tableName, rows,
298: offset, numberOfRowsToReturn, rowCount);
299: }
300:
301: rs.close();
302: } catch (SQLException exc) {
303: throw new DBEngineException(exc.getMessage());
304: }
305:
306: Log.getInstance().infoMessage(
307: "Found " + rowCount + " rows in " + schemaName + "."
308: + tableName, this .getClass().getName());
309:
310: return dbTable;
311: }
312:
313: /**
314: * Returns a list of columns in the table
315: * @param schemaName
316: * @param tableName
317: * @return - a list of ColumnInfo objects
318: * @throws DBEngineException
319: */
320: public List listColumnsInATable(String schemaName, String tableName)
321: throws DBEngineException {
322: Log.getInstance().infoMessage(
323: "Listing columns in " + schemaName + "." + tableName
324: + "...", this .getClass().getName());
325:
326: //Build the list of column infos
327: List listOfColumns = new ArrayList();
328:
329: //Get the list of primary key column names
330: List listOfPrimaryKeyColumnNames = getPrimaryKeyColumnNames(
331: schemaName, tableName);
332:
333: ResultSet rs = null;
334: try {
335: String sql = "select * from " + schemaName + "."
336: + tableName + " where rownum < 1";
337: //String sql = "select * from " + tableName;
338:
339: //Log the SQL
340: SQLLog.getInstance().logSQLStatement(sql);
341:
342: rs = this .getStatement().executeQuery(sql);
343: ResultSetMetaData rsmd = rs.getMetaData();
344:
345: int numberOfColumns = rsmd.getColumnCount();
346: for (int i = 1; i < numberOfColumns + 1; i++) {
347: String columnName = rsmd.getColumnName(i);
348: String equivalentJavaClass = rsmd.getColumnClassName(i);
349: String columnTypeName = rsmd.getColumnTypeName(i);
350: Integer columnDisplaysize = new Integer(rsmd
351: .getColumnDisplaySize(i));
352:
353: //Decide the nullable nature
354: int nullable = rsmd.isNullable(i);
355: String nullableNature = ColumnInfo.COLUMN_NULLABLE_NATURE_UNKNOWN;
356: if (nullable == ResultSetMetaData.columnNullable) {
357: nullableNature = ColumnInfo.COLUMN_NULLABLE;
358: }
359:
360: if (nullable == ResultSetMetaData.columnNoNulls) {
361: nullableNature = ColumnInfo.COLUMN_NOT_NULLABLE;
362: }
363:
364: Boolean isAutoIncrement = new Boolean(rsmd
365: .isAutoIncrement(i));
366:
367: //Check if this column is primary key column
368: boolean isPrimaryKeyColumn = listOfPrimaryKeyColumnNames
369: .contains(columnName);
370: Boolean isPrimaryKeyColumnBoolean = new Boolean(
371: isPrimaryKeyColumn);
372:
373: //Check if this column is editable/writable
374: //boolean isEditable = rsmd.isDefinitelyWritable( i );
375: //Boolean isEditableBoolean = new Boolean( isEditable );
376:
377: int columnType = rsmd.getColumnType(i);
378:
379: ColumnInfo columnInfo = new ColumnInfo(columnName,
380: columnTypeName, equivalentJavaClass,
381: columnDisplaysize, nullableNature,
382: isAutoIncrement, isPrimaryKeyColumnBoolean,
383: Boolean.TRUE, new Integer(columnType));
384: listOfColumns.add(columnInfo);
385: }
386: rs.close();
387: } catch (SQLException exc) {
388: throw new DBEngineException(exc.getMessage());
389: }
390:
391: Log.getInstance().infoMessage(
392: "Found " + listOfColumns.size() + " columns in "
393: + schemaName + "." + tableName,
394: this .getClass().getName());
395:
396: return listOfColumns;
397: }
398:
399: /**
400: * Lists the sequence in a database.
401: * @return - results as a dbtable
402: * @throws DBEngineException
403: */
404: public DBTable listSequences() throws DBEngineException {
405: Log.getInstance().infoMessage(
406: "Listing sequences in Oracle Database...",
407: this .getClass().getName());
408:
409: //Set the table data
410: String sql = "select sequence_name, min_value, max_value, increment_by, cycle_flag, last_number from user_sequences";
411:
412: //Log the SQL
413: SQLLog.getInstance().logSQLStatement(sql);
414:
415: //Run the SQL
416: DBTable dbTable = null;
417: List rows = new ArrayList();
418: ResultSet rs = null;
419: try {
420: rs = this .getStatement().executeQuery(sql);
421:
422: //Build the ColumnInfo objects
423: ColumnInfo columnInfoForSequenceName = new ColumnInfo(
424: "Sequence name", null, null, null, null, null,
425: Boolean.FALSE, Boolean.FALSE, null);
426: ColumnInfo columnInfoForMinValue = new ColumnInfo(
427: "Min value", null, null, null, null, null,
428: Boolean.FALSE, Boolean.FALSE, null);
429: ColumnInfo columnInfoForMaxValue = new ColumnInfo(
430: "Max value", null, null, null, null, null,
431: Boolean.FALSE, Boolean.FALSE, null);
432: ColumnInfo columnInfoForIncrementBy = new ColumnInfo(
433: "Increment by", null, null, null, null, null,
434: Boolean.FALSE, Boolean.FALSE, null);
435: ColumnInfo columnInfoForCycleFlag = new ColumnInfo("Cycle",
436: null, null, null, null, null, Boolean.FALSE,
437: Boolean.FALSE, null);
438: ColumnInfo columnInfoForLastNumber = new ColumnInfo(
439: "Last number", null, null, null, null, null,
440: Boolean.FALSE, Boolean.FALSE, null);
441:
442: //Get the data from the result set and build a DBTable
443: while (rs.next()) {
444: String sequenceName = rs.getString("sequence_name");
445: int minValue = rs.getInt("min_value");
446: double maxValue = rs.getDouble("max_value");
447: int incrementBy = rs.getInt("increment_by");
448: String cycle = rs.getString("cycle_flag");
449: double lastNumber = rs.getDouble("last_number");
450:
451: DBTableCell dbTableCellForSequenceName = new DBTableCell(
452: columnInfoForSequenceName, sequenceName,
453: Boolean.FALSE);
454: DBTableCell dbTableCellForMinValue = new DBTableCell(
455: columnInfoForMinValue, new Integer(minValue),
456: Boolean.FALSE);
457: DBTableCell dbTableCellForMaxValue = new DBTableCell(
458: columnInfoForMaxValue, new Double(maxValue),
459: Boolean.FALSE);
460: DBTableCell dbTableCellForIncrementBy = new DBTableCell(
461: columnInfoForIncrementBy, new Integer(
462: incrementBy), Boolean.FALSE);
463: DBTableCell dbTableCellForCycleFlag = new DBTableCell(
464: columnInfoForCycleFlag, new Boolean(cycle),
465: Boolean.FALSE);
466: DBTableCell dbTableCellForLastNumber = new DBTableCell(
467: columnInfoForLastNumber,
468: new Double(lastNumber), Boolean.FALSE);
469:
470: List listOfRowData = new ArrayList();
471: listOfRowData.add(dbTableCellForSequenceName);
472: listOfRowData.add(dbTableCellForMinValue);
473: listOfRowData.add(dbTableCellForMaxValue);
474: listOfRowData.add(dbTableCellForIncrementBy);
475: listOfRowData.add(dbTableCellForCycleFlag);
476: listOfRowData.add(dbTableCellForLastNumber);
477:
478: DBRow dbRow = new DBRow(listOfRowData);
479: rows.add(dbRow);
480: }
481:
482: dbTable = new DBTable(null, null, rows, null, null,
483: new Integer(0));
484:
485: rs.close();
486: } catch (SQLException exc) {
487: throw new DBEngineException(exc.getMessage());
488: }
489:
490: Log.getInstance().infoMessage(
491: "Found " + rows.size()
492: + " sequences in Oracle database",
493: this .getClass().getName());
494:
495: return dbTable;
496: }
497:
498: /**
499: * Lists the indexes
500: * @return
501: * @throws DBEngineException
502: */
503: public DBTable listIndexes() throws DBEngineException {
504: Log.getInstance().infoMessage(
505: "Listing indexes in Oracle Database...",
506: this .getClass().getName());
507:
508: //Set the table data
509: String sql = "select index_name, table_name, uniqueness, table_owner from user_indexes";
510:
511: //Log the SQL
512: SQLLog.getInstance().logSQLStatement(sql);
513:
514: //Run the SQL
515: DBTable dbTable = null;
516: List rows = new ArrayList();
517: ResultSet rs = null;
518: try {
519: rs = this .getStatement().executeQuery(sql);
520:
521: //Build the ColumnInfo objects
522: ColumnInfo columnInfoForIndexName = new ColumnInfo(
523: "Index name", null, null, null, null, null,
524: Boolean.FALSE, Boolean.FALSE, null);
525: ColumnInfo columnInfoForTableName = new ColumnInfo(
526: "Table name", null, null, null, null, null,
527: Boolean.FALSE, Boolean.FALSE, null);
528: ColumnInfo columnInfoForUniqueness = new ColumnInfo(
529: "Uniqueness", null, null, null, null, null,
530: Boolean.FALSE, Boolean.FALSE, null);
531: ColumnInfo columnInfoForTableOwner = new ColumnInfo(
532: "Table owner", null, null, null, null, null,
533: Boolean.FALSE, Boolean.FALSE, null);
534:
535: //Get the data from the result set and build a DBTable
536: while (rs.next()) {
537: String indexName = rs.getString("index_name");
538: String tableName = rs.getString("table_name");
539: String uniqueness = rs.getString("uniqueness");
540: String tableOwner = rs.getString("table_owner");
541:
542: DBTableCell dbTableCellForIndexName = new DBTableCell(
543: columnInfoForIndexName, indexName,
544: Boolean.FALSE);
545: DBTableCell dbTableCellForTableName = new DBTableCell(
546: columnInfoForTableName, tableName,
547: Boolean.FALSE);
548: DBTableCell dbTableCellForUniqueness = new DBTableCell(
549: columnInfoForUniqueness, uniqueness,
550: Boolean.FALSE);
551: DBTableCell dbTableCellForTableOwner = new DBTableCell(
552: columnInfoForTableOwner, tableOwner,
553: Boolean.FALSE);
554:
555: List listOfRowData = new ArrayList();
556: listOfRowData.add(dbTableCellForIndexName);
557: listOfRowData.add(dbTableCellForTableName);
558: listOfRowData.add(dbTableCellForUniqueness);
559: listOfRowData.add(dbTableCellForTableOwner);
560:
561: DBRow dbRow = new DBRow(listOfRowData);
562: rows.add(dbRow);
563: }
564:
565: dbTable = new DBTable(null, null, rows, null, null,
566: new Integer(0));
567:
568: rs.close();
569: } catch (SQLException exc) {
570: throw new DBEngineException(exc.getMessage());
571: }
572:
573: Log.getInstance().infoMessage(
574: "Found " + rows.size() + " indexes in Oracle database",
575: this .getClass().getName());
576:
577: return dbTable;
578: }
579:
580: /**
581: * Lists the constraints
582: * @return
583: * @throws DBEngineException
584: */
585: public DBTable listConstraints() throws DBEngineException {
586: Log.getInstance().infoMessage(
587: "Listing constraints in Oracle Database...",
588: this .getClass().getName());
589:
590: //Set the table data
591: String sql = "select constraint_name, table_name, search_condition, status, owner from user_constraints";
592:
593: //Log the SQL
594: SQLLog.getInstance().logSQLStatement(sql);
595:
596: //Run the SQL
597: DBTable dbTable = null;
598: List rows = new ArrayList();
599: ResultSet rs = null;
600: try {
601: rs = this .getStatement().executeQuery(sql);
602:
603: //Build the ColumnInfo objects
604: ColumnInfo columnInfoForConstraintName = new ColumnInfo(
605: "Constraint name", null, null, null, null, null,
606: Boolean.FALSE, Boolean.FALSE, null);
607: ColumnInfo columnInfoForTableName = new ColumnInfo(
608: "Table name", null, null, null, null, null,
609: Boolean.FALSE, Boolean.FALSE, null);
610: ColumnInfo columnInfoForSearchCondition = new ColumnInfo(
611: "Search condition", null, null, null, null, null,
612: Boolean.FALSE, Boolean.FALSE, null);
613: ColumnInfo columnInfoForStatus = new ColumnInfo("Status",
614: null, null, null, null, null, Boolean.FALSE,
615: Boolean.FALSE, null);
616: ColumnInfo columnInfoForOwner = new ColumnInfo("Owner",
617: null, null, null, null, null, Boolean.FALSE,
618: Boolean.FALSE, null);
619:
620: //Get the data from the result set and build a DBTable
621: while (rs.next()) {
622: String constraintName = rs.getString("constraint_name");
623: String tableName = rs.getString("table_name");
624: String searchCondition = rs
625: .getString("search_condition");
626: String status = rs.getString("status");
627: String owner = rs.getString("owner");
628:
629: DBTableCell dbTableCellForConstraintName = new DBTableCell(
630: columnInfoForConstraintName, constraintName,
631: Boolean.FALSE);
632: DBTableCell dbTableCellForTableName = new DBTableCell(
633: columnInfoForTableName, tableName,
634: Boolean.FALSE);
635: DBTableCell dbTableCellForSearchCondition = new DBTableCell(
636: columnInfoForSearchCondition, searchCondition,
637: Boolean.FALSE);
638: DBTableCell dbTableCellForStatus = new DBTableCell(
639: columnInfoForStatus, status, Boolean.FALSE);
640: DBTableCell dbTableCellForOwner = new DBTableCell(
641: columnInfoForOwner, owner, Boolean.FALSE);
642:
643: List listOfRowData = new ArrayList();
644: listOfRowData.add(dbTableCellForConstraintName);
645: listOfRowData.add(dbTableCellForTableName);
646: listOfRowData.add(dbTableCellForSearchCondition);
647: listOfRowData.add(dbTableCellForStatus);
648: listOfRowData.add(dbTableCellForOwner);
649:
650: DBRow dbRow = new DBRow(listOfRowData);
651: rows.add(dbRow);
652: }
653:
654: dbTable = new DBTable(null, null, rows, null, null,
655: new Integer(0));
656:
657: rs.close();
658: } catch (SQLException exc) {
659: throw new DBEngineException(exc.getMessage());
660: }
661:
662: Log.getInstance().infoMessage(
663: "Found " + rows.size()
664: + " constraints in Oracle database",
665: this.getClass().getName());
666:
667: return dbTable;
668:
669: }
670: }
|