001: package org.dbbrowser.db.engine.queryengine;
002:
003: import infrastructure.logging.Log;
004: import java.sql.DatabaseMetaData;
005: import java.sql.ResultSet;
006: import java.sql.ResultSetMetaData;
007: import java.sql.SQLException;
008: import java.sql.Statement;
009: import java.sql.Types;
010: import java.util.ArrayList;
011: import java.util.Iterator;
012: import java.util.List;
013: import org.dbbrowser.db.engine.exception.DBEngineException;
014: import org.dbbrowser.db.engine.model.ColumnInfo;
015: import org.dbbrowser.db.engine.model.DBRow;
016: import org.dbbrowser.db.engine.model.DBTable;
017: import org.dbbrowser.db.engine.model.DBTableCell;
018: import org.dbbrowser.db.engine.model.View;
019:
020: /**
021: * DBQuery Engine which gets data from a MySQL DBMS
022: */
023: public class MySQLDBQueryEngine extends AbstractDBQueryEngine implements
024: DBQueryEngine {
025: /**
026: * Constructer
027: * @param statement
028: */
029: public MySQLDBQueryEngine(Statement statement) {
030: super (statement);
031: }
032:
033: /**
034: * Returns a list of schemas in the Database.
035: * @return - a list of Strings. It is empty if there are no table spaces for the database.
036: * @throws DBEngineException
037: */
038: public List listSchemas() throws DBEngineException {
039: Log.getInstance().infoMessage(
040: "Listing database instances in MySQL...",
041: this .getClass().getName());
042:
043: List listOfDatabaseInstances = new ArrayList();
044: try {
045: String sql = "show databases";
046:
047: Log.getInstance().debugMessage("SQL is: " + sql,
048: this .getClass().getName());
049:
050: ResultSet rs = this .getStatement().executeQuery(sql);
051: while (rs.next()) {
052: String tablename = rs.getString("Database");
053: listOfDatabaseInstances.add(tablename);
054: }
055:
056: Log.getInstance().debugMessage(
057: "Finished listing schemas in MySQLDBQueryEngine",
058: this .getClass().getName());
059: rs.close();
060: } catch (SQLException exc) {
061: throw new DBEngineException(exc.getMessage());
062: }
063:
064: Log.getInstance().infoMessage(
065: "Found " + listOfDatabaseInstances.size()
066: + " database instances in MySQL",
067: this .getClass().getName());
068:
069: return listOfDatabaseInstances;
070: }
071:
072: /**
073: * MySQL 5 does not support views. Views are same as tables
074: * @return - a list of View objects
075: * @throws DBEngineException
076: */
077: public List listViews() throws DBEngineException {
078: Log.getInstance().infoMessage("Listing views in MySQL...",
079: this .getClass().getName());
080:
081: List views = new ArrayList();
082: try {
083: //Execute the statement to get the data in the table
084: String sql = "select table_schema, table_name from information_schema.views";
085:
086: Log.getInstance().debugMessage("SQL is: " + sql,
087: this .getClass().getName());
088:
089: ResultSet rs = this .getStatement().executeQuery(sql);
090:
091: //Get the data from the result set and build a DBTable
092: while (rs.next()) {
093: String schemaName = rs.getString("table_schema");
094: String viewName = rs.getString("table_name");
095: View view = new View(schemaName, viewName, null);
096: views.add(view);
097: }
098: Log.getInstance().debugMessage(
099: "Found" + views.size() + " views",
100: this .getClass().getName());
101: rs.close();
102: } catch (SQLException exc) {
103: throw new DBEngineException(exc.getMessage());
104: }
105:
106: return views;
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 MySQL...", this .getClass().getName());
118:
119: String viewDefinition = "";
120: try {
121: //Execute the statement to get the data in the table
122: String sql = "select view_definition from information_schema.views where table_name = '"
123: + view.getViewName()
124: + "' and table_schema = '"
125: + view.getSchemaName() + "'";
126:
127: Log.getInstance().debugMessage("SQL is: " + sql,
128: this .getClass().getName());
129:
130: ResultSet rs = this .getStatement().executeQuery(sql);
131:
132: //Get the data from the result set and build a DBTable
133: if (rs.next()) {
134: viewDefinition = rs.getString("view_definition");
135: }
136: Log.getInstance().debugMessage(
137: "View definition for " + view.getViewName()
138: + " is: \n" + viewDefinition,
139: this .getClass().getName());
140: rs.close();
141: } catch (SQLException exc) {
142: throw new DBEngineException(exc.getMessage());
143: }
144:
145: return viewDefinition;
146: }
147:
148: /**
149: * Get all the data in a table
150: * @param schemaName
151: * @param tableName
152: * @return
153: * @throws DBEngineException
154: */
155: public DBTable getAllDataInATable(String schemaName,
156: String tableName, Integer offset,
157: Integer numberOfRowsToReturn) throws DBEngineException {
158: Log.getInstance().infoMessage(
159: "Listing all data in " + schemaName + "." + tableName
160: + "...", this .getClass().getName());
161:
162: List listOfColumnsInATable = listColumnsInATable(schemaName,
163: tableName);
164:
165: //Get the number of rows in the table
166: Integer rowCount = this .getRowCount(schemaName, tableName);
167:
168: DBTable dbTable = null;
169: List rows = new ArrayList();
170: try {
171:
172: //Change the schema
173: String sql = "use " + schemaName;
174: this .getStatement().execute(sql);
175:
176: //Execute the statement to get the data in the table
177: sql = "select * from " + tableName;
178:
179: Log.getInstance().debugMessage("SQL is: " + sql,
180: this .getClass().getName());
181:
182: ResultSet rs = this .getStatement().executeQuery(sql);
183:
184: //Get the data from the result set and build a DBTable
185: while (rs.next()) {
186: //Get the data for every column
187: List listOfRowData = new ArrayList();
188: for (int i = 0; i < listOfColumnsInATable.size(); i++) {
189: ColumnInfo columnInfo = (ColumnInfo) listOfColumnsInATable
190: .get(i);
191: String columnName = columnInfo.getColumnName();
192: Object o = rs.getObject(columnName);
193: DBTableCell dbTableCell = null;
194:
195: dbTableCell = new DBTableCell(columnInfo, o,
196: Boolean.FALSE);
197:
198: listOfRowData.add(dbTableCell);
199: }
200: DBRow dbRow = new DBRow(listOfRowData);
201: rows.add(dbRow);
202: }
203:
204: if (rows.isEmpty()) {
205: dbTable = new DBTable(schemaName, tableName, rows,
206: offset, numberOfRowsToReturn,
207: listOfColumnsInATable);
208: } else {
209: dbTable = new DBTable(schemaName, tableName, rows,
210: offset, new Integer(offset.intValue()
211: + rows.size()), rowCount);
212: }
213:
214: Log.getInstance().debugMessage(
215: "Returning all data for " + schemaName + "."
216: + tableName + " in MySQLDBQueryEngine",
217: this .getClass().getName());
218:
219: rs.close();
220: } catch (SQLException exc) {
221: throw new DBEngineException(exc.getMessage());
222: }
223:
224: Log.getInstance().infoMessage(
225: "Found " + rows.size() + " rows in " + schemaName + "."
226: + tableName, this .getClass().getName());
227:
228: return dbTable;
229: }
230:
231: /**
232: * Returns a list of tables in the schema
233: * @param schemaName
234: * @return - a list of Strings
235: * @throws DBEngineException
236: */
237: public List listTablesInSchema(String databaseInstanceName)
238: throws DBEngineException {
239: Log.getInstance().infoMessage(
240: "Listing tables in MySQL database instance "
241: + databaseInstanceName + "...",
242: this .getClass().getName());
243:
244: List listOfTables = new ArrayList();
245: try {
246: //Change the tablespace
247: String sql = "use " + databaseInstanceName;
248: this .getStatement().execute(sql);
249:
250: //Select the list of tables
251: sql = "show tables";
252:
253: Log.getInstance().debugMessage("SQL is: " + sql,
254: this .getClass().getName());
255:
256: ResultSet rs = this .getStatement().executeQuery(sql);
257: while (rs.next()) {
258: String tablename = rs.getString("tables_in_"
259: + databaseInstanceName);
260: listOfTables.add(tablename);
261: }
262:
263: Log.getInstance().debugMessage(
264: "Finished listing all schemas for "
265: + databaseInstanceName
266: + " in MySQLDBQueryEngine",
267: this .getClass().getName());
268:
269: rs.close();
270: } catch (SQLException exc) {
271: throw new DBEngineException(exc.getMessage());
272: }
273:
274: Log.getInstance().infoMessage(
275: "Found " + listOfTables.size()
276: + " tables in MySQL database instance "
277: + databaseInstanceName,
278: this .getClass().getName());
279:
280: return listOfTables;
281: }
282:
283: /**
284: * Returns a list of columns in the table
285: * @param databaseInstanceName
286: * @param tableName
287: * @return - a list of ColumnInfo objects
288: * @throws DBEngineException
289: */
290: public List listColumnsInATable(String databaseInstanceName,
291: String tableName) throws DBEngineException {
292: Log.getInstance().infoMessage(
293: "Listing columns in table " + databaseInstanceName
294: + "." + tableName + "...",
295: this .getClass().getName());
296:
297: //Get the list of primary key column names
298: List primaryKeyColumnNames = this .getPrimaryKeyColumnNames(
299: databaseInstanceName, tableName);
300:
301: List listOfColumns = new ArrayList();
302: try {
303: String sql = "select * from " + databaseInstanceName + "."
304: + tableName;
305:
306: Log.getInstance().debugMessage("SQL is: " + sql,
307: this .getClass().getName());
308:
309: ResultSet rs = this .getStatement().executeQuery(sql);
310: ResultSetMetaData rsmd = rs.getMetaData();
311:
312: int numberOfColumns = rsmd.getColumnCount();
313: for (int i = 1; i < numberOfColumns + 1; i++) {
314: //Get the values from the ResultSetMetaData
315: String columnName = rsmd.getColumnName(i);
316: String equivalentJavaClass = rsmd.getColumnClassName(i);
317: String columnTypeName = rsmd.getColumnTypeName(i);
318: Integer columnDisplaysize = new Integer(rsmd
319: .getColumnDisplaySize(i));
320: int nullable = rsmd.isNullable(i);
321:
322: //Check if this column is a primary key column
323: boolean isPrimaryKeyColumn = primaryKeyColumnNames
324: .contains(columnName);
325: Boolean isPrimaryKeyColumnBoolean = new Boolean(
326: isPrimaryKeyColumn);
327:
328: //Build the column info
329: String nullableNature = ColumnInfo.COLUMN_NULLABLE_NATURE_UNKNOWN;
330: if (nullable == ResultSetMetaData.columnNullable) {
331: nullableNature = ColumnInfo.COLUMN_NULLABLE;
332: }
333:
334: if (nullable == ResultSetMetaData.columnNoNulls) {
335: nullableNature = ColumnInfo.COLUMN_NOT_NULLABLE;
336: }
337:
338: //Check if this column is editable/writable
339: //boolean isEditable = rsmd.isDefinitelyWritable( i );
340: //Boolean isEditableBoolean = new Boolean( isEditable );
341:
342: int columnType = rsmd.getColumnType(i);
343:
344: Boolean isAutoIncrement = new Boolean(rsmd
345: .isAutoIncrement(i));
346: ColumnInfo columnInfo = new ColumnInfo(columnName,
347: columnTypeName, equivalentJavaClass,
348: columnDisplaysize, nullableNature,
349: isAutoIncrement, isPrimaryKeyColumnBoolean,
350: Boolean.TRUE, new Integer(columnType));
351: listOfColumns.add(columnInfo);
352: }
353:
354: Log.getInstance().debugMessage(
355: "Finished listing all columns in for "
356: + databaseInstanceName + "." + tableName
357: + " in MySQLDBQueryEngine",
358: this .getClass().getName());
359:
360: rs.close();
361: } catch (SQLException exc) {
362: throw new DBEngineException(exc.getMessage());
363: }
364:
365: Log.getInstance().infoMessage(
366: "Found " + listOfColumns.size() + " columns in table "
367: + databaseInstanceName + "." + tableName,
368: this .getClass().getName());
369:
370: return listOfColumns;
371: }
372:
373: /**
374: * Returns the list of column names which are primary keys for the table
375: * @param schemaName
376: * @param tableName
377: * @return a list of strings - names of primary key columns
378: * @throws DBEngineException
379: */
380: public List getPrimaryKeyColumnNames(String schemaName,
381: String tableName) throws DBEngineException {
382: Log.getInstance().infoMessage("Listing primary key columns...",
383: this .getClass().getName());
384:
385: List listOfPrimaryKeyColumnNames = new ArrayList();
386: try {
387: DatabaseMetaData databaseMetaData = this .getStatement()
388: .getConnection().getMetaData();
389: ResultSet rs = databaseMetaData.getPrimaryKeys(schemaName,
390: null, tableName);
391:
392: while (rs.next()) {
393: String primaryKeyColumnNameForTable = rs
394: .getString("COLUMN_NAME");
395: String tableNameOfPrimaryKey = rs
396: .getString("TABLE_NAME");
397: String catalogNameForTable = rs.getString("TABLE_CAT");
398:
399: //if the primary key is for this table, add it
400: if (tableName.equals(tableNameOfPrimaryKey)
401: && schemaName.equals(catalogNameForTable)) {
402: listOfPrimaryKeyColumnNames
403: .add(primaryKeyColumnNameForTable);
404: }
405: }
406: rs.close();
407: } catch (SQLException exc) {
408: throw new DBEngineException(exc.getMessage());
409: }
410:
411: Log.getInstance().infoMessage(
412: "Found " + listOfPrimaryKeyColumnNames.size()
413: + " primary key columns",
414: this .getClass().getName());
415:
416: return listOfPrimaryKeyColumnNames;
417: }
418:
419: /**
420: * Lists the indexes
421: * @return
422: * @throws DBEngineException
423: */
424: public DBTable listIndexes() throws DBEngineException {
425: DBTable dbTableForIndexes = null;
426: List listOfColumnInfos = null;
427:
428: //ColumnInfo for first column
429: ColumnInfo firstColumnInfo = new ColumnInfo("Database name",
430: "VARCHAR2", "java.lang.String", new Integer(20),
431: ColumnInfo.COLUMN_NOT_NULLABLE, Boolean.FALSE,
432: Boolean.FALSE, Boolean.FALSE,
433: new Integer(Types.VARCHAR));
434:
435: try {
436: //Get the list of all schemas/databases in MySQL
437: List listOfSchemas = listSchemas();
438: List listOfDBRows = new ArrayList();
439:
440: //For each schemas, find all the tables
441: Iterator iteratorForSchemas = listOfSchemas.iterator();
442: while (iteratorForSchemas.hasNext()) {
443: String schemaName = (String) iteratorForSchemas.next();
444:
445: //Find all the tables in the schema
446: List listOfTablesInSchema = listTablesInSchema(schemaName);
447:
448: //Find all the indexes in the table
449: Iterator iteratorForListOfTablesInSchemas = listOfTablesInSchema
450: .iterator();
451: while (iteratorForListOfTablesInSchemas.hasNext()) {
452: String tableName = (String) iteratorForListOfTablesInSchemas
453: .next();
454:
455: //Get the indexes for the table
456: String sql = "show index in " + schemaName + "."
457: + tableName;
458: Log.getInstance().debugMessage("SQL is: " + sql,
459: this .getClass().getName());
460:
461: //Run the sql
462: ResultSet rs = this .getStatement()
463: .executeQuery(sql);
464:
465: //Build the column infos only if they have not been built yet
466: if (listOfColumnInfos == null) {
467: //Get the resultset metadata
468: ResultSetMetaData rsmd = rs.getMetaData();
469:
470: //Build the list of column infos
471: listOfColumnInfos = getListOfColumnInfosForIndexes(rsmd);
472:
473: //The first column info is the schema name
474: listOfColumnInfos.add(0, firstColumnInfo);
475: }
476:
477: //Get all the indexes for the table
478: while (rs.next()) {
479: List listOfDBTableCells = new ArrayList();
480: //Build a DBTableCell for each data
481: for (int i = 0; i < listOfColumnInfos.size(); i++) {
482: //if this is the first column, then insert data for database name first
483: if (i == 0) {
484: DBTableCell cell = new DBTableCell(
485: firstColumnInfo, schemaName,
486: Boolean.FALSE);
487: listOfDBTableCells.add(cell);
488: } else {
489: ColumnInfo ci = (ColumnInfo) listOfColumnInfos
490: .get(i);
491: Object value = rs.getObject(ci
492: .getColumnName());
493: DBTableCell cell = new DBTableCell(ci,
494: value, Boolean.FALSE);
495: listOfDBTableCells.add(cell);
496: }
497: }
498:
499: DBRow dbRow = new DBRow(listOfDBTableCells);
500: listOfDBRows.add(dbRow);
501: }
502: }
503: }
504: dbTableForIndexes = new DBTable(null, null, listOfDBRows,
505: new Integer(0), new Integer(0), new Integer(0));
506: } catch (SQLException exc) {
507: throw new DBEngineException(exc.getMessage());
508: }
509:
510: return dbTableForIndexes;
511: }
512:
513: private List getListOfColumnInfosForIndexes(ResultSetMetaData rsmd)
514: throws SQLException {
515: List listOfColumnInfos = new ArrayList();
516:
517: //Build the column info for each column
518: for (int i = 0; i < rsmd.getColumnCount(); i++) {
519: //Build the column info object
520: String columnName = rsmd.getColumnName(i + 1);
521: String equivalentJavaClass = rsmd.getColumnClassName(i + 1);
522: String columnTypeName = rsmd.getColumnTypeName(i + 1); //e.g. NUMBER, VARCHAR2
523: Integer columnDisplaysize = new Integer(rsmd
524: .getColumnDisplaySize(i + 1));
525: int columnType = rsmd.getColumnType(i + 1); //From java.sql.Types
526:
527: //Decide the nullable nature
528: int nullable = rsmd.isNullable(i + 1);
529: String nullableNature = ColumnInfo.COLUMN_NULLABLE_NATURE_UNKNOWN;
530: if (nullable == ResultSetMetaData.columnNullable) {
531: nullableNature = ColumnInfo.COLUMN_NULLABLE;
532: }
533:
534: if (nullable == ResultSetMetaData.columnNoNulls) {
535: nullableNature = ColumnInfo.COLUMN_NOT_NULLABLE;
536: }
537:
538: ColumnInfo columnInfo = new ColumnInfo(columnName,
539: columnTypeName, equivalentJavaClass,
540: columnDisplaysize, nullableNature, Boolean.FALSE,
541: Boolean.FALSE, Boolean.FALSE, new Integer(
542: columnType));
543: listOfColumnInfos.add(columnInfo);
544: }
545:
546: return listOfColumnInfos;
547: }
548:
549: /**
550: * Lists the constraints
551: * @return
552: * @throws DBEngineException
553: */
554: public DBTable listConstraints() throws DBEngineException {
555: throw new UnsupportedOperationException();
556: }
557: }
|