001: package org.dbbrowser.db.engine.updateengine;
002:
003: import infrastructure.logging.Log;
004: import java.math.BigDecimal;
005: import java.sql.SQLException;
006: import java.sql.Statement;
007: import java.sql.Types;
008: import java.text.DateFormat;
009: import java.text.ParseException;
010: import java.text.SimpleDateFormat;
011: import java.util.Date;
012: import java.util.Iterator;
013: import java.util.List;
014: import org.dbbrowser.db.engine.exception.DBEngineException;
015: import org.dbbrowser.db.engine.model.ColumnInfo;
016: import org.dbbrowser.db.engine.model.DBRow;
017: import org.dbbrowser.db.engine.model.DBTableCell;
018: import org.dbbrowser.db.engine.model.View;
019: import org.dbbrowser.db.engine.SQLLog;
020:
021: /**
022: * Updates the database
023: */
024: public class GenericDBUpdateEngine implements DBUpdateEngine {
025: private Statement statement = null;
026:
027: /**
028: * Constructer
029: * @param statement
030: */
031: public GenericDBUpdateEngine(Statement statement) {
032: this .statement = statement;
033: }
034:
035: /**
036: * Returns the DB statement used to update the database
037: * @return
038: */
039: public Statement getStatement() {
040: return this .statement;
041: }
042:
043: /**
044: * Update the view definition for the view
045: * @param view
046: * @throws DBEngineException
047: */
048: public void updateViewDefinition(View view)
049: throws DBEngineException {
050: Log.getInstance().infoMessage(
051: "Updating view definition for view: "
052: + view.getViewName() + "...",
053: this .getClass().getName());
054:
055: String sql = "";
056:
057: if (view.getSchemaName() == null) {
058: sql = "create or replace view " + view.getViewName()
059: + " as " + view.getViewDefinition();
060: } else {
061: sql = "create or replace view " + view.getSchemaName()
062: + "." + view.getViewName() + " as "
063: + view.getViewDefinition();
064: }
065:
066: Log.getInstance().debugMessage(
067: "SQL for updating view is: " + sql,
068: this .getClass().getName());
069:
070: //Log the SQL
071: SQLLog.getInstance().logSQLStatement(sql);
072:
073: try {
074: this .getStatement().execute(sql);
075: } catch (SQLException exc) {
076: throw new DBEngineException(exc.getMessage());
077: }
078:
079: Log.getInstance().infoMessage(
080: "Finished update to view definition",
081: this .getClass().getName());
082: }
083:
084: /**
085: * Add new column to the table
086: * @param schemaName
087: * @param tableName
088: * @param columnInfo
089: * @throws DBEngineException
090: */
091: public void addNewColumn(String schemaName, String tableName,
092: ColumnInfo columnInfo) throws DBEngineException {
093: Log.getInstance().infoMessage(
094: "Adding new column into " + tableName + "...",
095: this .getClass().getName());
096:
097: //Decide the nullable nature
098: String nullableNature = "";
099: if (ColumnInfo.COLUMN_NOT_NULLABLE.equals(columnInfo
100: .getNullableNature())) {
101: nullableNature = " not null ";
102: }
103:
104: String sql = "";
105: //If it is a varchar, then build the SQL
106: if (ColumnInfo.COLUMN_TYPE_VARCHAR.equals(columnInfo
107: .getColumnTypeName())) {
108: sql = "alter table " + tableName + " add "
109: + columnInfo.getColumnName() + " "
110: + columnInfo.getColumnTypeName() + "("
111: + columnInfo.getColumnDisplaySize().intValue()
112: + ")" + nullableNature;
113: } else if (ColumnInfo.COLUMN_TYPE_NUMBER.equals(columnInfo
114: .getColumnTypeName())) {
115: sql = "alter table " + tableName + " add "
116: + columnInfo.getColumnName() + " "
117: + columnInfo.getColumnTypeName() + "("
118: + columnInfo.getColumnDisplaySize().intValue()
119: + ")" + nullableNature;
120: } else if (ColumnInfo.COLUMN_TYPE_DATE.equals(columnInfo
121: .getColumnTypeName())
122: || ColumnInfo.COLUMN_TYPE_DATE_TIME.equals(columnInfo
123: .getColumnTypeName())) {
124: sql = "alter table " + tableName + " add "
125: + columnInfo.getColumnName() + " "
126: + columnInfo.getColumnTypeName() + nullableNature;
127: } else {
128: throw new UnsupportedOperationException(
129: "*** Invalid data type: "
130: + columnInfo.getColumnTypeName() + " ***");
131: }
132:
133: Log.getInstance().debugMessage(
134: "SQL for adding new column is: " + sql,
135: this .getClass().getName());
136:
137: //Log the SQL
138: SQLLog.getInstance().logSQLStatement(sql);
139:
140: try {
141: this .getStatement().execute(sql);
142: } catch (SQLException exc) {
143: throw new DBEngineException(exc.getMessage());
144: }
145:
146: Log.getInstance().infoMessage(
147: "Add new complete complete in " + tableName,
148: this .getClass().getName());
149: }
150:
151: /**
152: * Drop the column from the table
153: * @param schemaName
154: * @param tableName
155: * @param columnInfo
156: * @throws DBEngineException
157: */
158: public void dropColumn(String schemaName, String tableName,
159: ColumnInfo columnInfo) throws DBEngineException {
160: Log.getInstance().infoMessage(
161: "Dropping column " + columnInfo.getColumnName()
162: + " from " + tableName + "...",
163: this .getClass().getName());
164:
165: String sql = "alter table " + tableName + " drop column "
166: + columnInfo.getColumnName();
167:
168: Log.getInstance().debugMessage(
169: "SQL for dropping column is: " + sql,
170: this .getClass().getName());
171:
172: //Log the SQL
173: SQLLog.getInstance().logSQLStatement(sql);
174:
175: try {
176: this .getStatement().execute(sql);
177: } catch (SQLException exc) {
178: throw new DBEngineException(exc.getMessage());
179: }
180:
181: Log.getInstance().infoMessage(
182: "Column " + columnInfo.getColumnName()
183: + " dropped from " + tableName,
184: this .getClass().getName());
185: }
186:
187: /**
188: * Add a new row to the database
189: * @param schemaName
190: * @param tableName
191: * @param dbRow
192: * @throws DBEngineException
193: */
194: public void addNewRow(String schemaName, String tableName,
195: DBRow dbRow) throws DBEngineException {
196: Log.getInstance().infoMessage(
197: "Adding new record into " + tableName + "...",
198: this .getClass().getName());
199:
200: //Get the list of cells which need to be added
201: List listOfDBTableCellsWhichNeedToBeAdded = dbRow
202: .getListOFDBTableCells();
203:
204: //Add the row only if there are some values
205: if (listOfDBTableCellsWhichNeedToBeAdded != null
206: && (!listOfDBTableCellsWhichNeedToBeAdded.isEmpty())) {
207: //Build the sql using the values which have changed
208: StringBuffer buffer = new StringBuffer();
209: buffer.append("insert into " + tableName + " ( ");
210: Iterator i = listOfDBTableCellsWhichNeedToBeAdded
211: .iterator();
212: while (i.hasNext()) {
213: DBTableCell dbTableCell = (DBTableCell) i.next();
214:
215: //Add the column name to the list
216: String columnName = dbTableCell.getColumnInfo()
217: .getColumnName();
218:
219: //Add to buffer
220: buffer.append(columnName);
221:
222: //if there are more, add the comma
223: if (i.hasNext()) {
224: buffer.append(", ");
225: }
226: }
227:
228: buffer.append(")values(");
229:
230: i = listOfDBTableCellsWhichNeedToBeAdded.iterator();
231: while (i.hasNext()) {
232: DBTableCell dbTableCell = (DBTableCell) i.next();
233:
234: //Format the value for update
235: String formattedValue = this .formatValue(dbTableCell
236: .getValue(), dbTableCell.getColumnInfo()
237: .getColumnType().intValue());
238:
239: //Build the sql
240: buffer.append(formattedValue);
241:
242: if (i.hasNext()) {
243: buffer.append(", ");
244: }
245: }
246:
247: buffer.append(")");
248:
249: String sql = buffer.toString();
250: Log.getInstance().debugMessage(
251: "SQL for adding new row is: " + sql,
252: this .getClass().getName());
253:
254: //Log the SQL
255: SQLLog.getInstance().logSQLStatement(sql);
256:
257: try {
258: this .getStatement().execute(sql);
259: } catch (SQLException exc) {
260: throw new DBEngineException(exc.getMessage());
261: }
262: }
263:
264: Log.getInstance().infoMessage(
265: "Add new row complete in " + tableName,
266: this .getClass().getName());
267:
268: }
269:
270: /**
271: * Delete the row
272: * @param schemaName
273: * @param tableName
274: * @param dbRow
275: * @throws DBEngineException
276: */
277: public void deleteRow(String schemaName, String tableName,
278: DBRow dbRow) throws DBEngineException {
279: Log.getInstance().infoMessage(
280: "Deleting record from " + tableName + "...",
281: this .getClass().getName());
282:
283: //Get the list of primary key columns
284: List listOfPrimaryKeycolumns = dbRow
285: .getListOfPrimaryKeyDBTableCells();
286:
287: //If there are no primary key columns, throw an exception
288: if (listOfPrimaryKeycolumns.isEmpty()) {
289: throw new DBEngineException(
290: "Cannot delete rows from table which has no primary key columns");
291: }
292:
293: //Build SQL statement
294: String sql = "delete from " + tableName + " where ";
295: Iterator i = listOfPrimaryKeycolumns.iterator();
296: while (i.hasNext()) {
297: DBTableCell dbTableCell = (DBTableCell) i.next();
298:
299: //If it is a string, put the ' commas around the value
300: if (dbTableCell.getColumnInfo().getColumnType().intValue() == Types.VARCHAR) {
301: sql = sql + dbTableCell.getColumnInfo().getColumnName()
302: + " = '" + dbTableCell.getFormattedValue()
303: + "'";
304: } else {
305: sql = sql + dbTableCell.getColumnInfo().getColumnName()
306: + " = " + dbTableCell.getFormattedValue();
307: }
308:
309: if (i.hasNext()) {
310: sql = sql + " and ";
311: }
312: }
313:
314: Log.getInstance().debugMessage("SQL for delete is: " + sql,
315: this .getClass().getName());
316:
317: //Log the SQL
318: SQLLog.getInstance().logSQLStatement(sql);
319:
320: try {
321: this .getStatement().executeUpdate(sql);
322: } catch (SQLException exc) {
323: throw new DBEngineException(exc.getMessage());
324: }
325:
326: Log.getInstance().infoMessage(
327: "Deleted one record from " + tableName,
328: this .getClass().getName());
329: }
330:
331: /**
332: * Update the database using the data from the DBRow
333: * @param schemaName
334: * @param tableName
335: * @param dbRow
336: */
337: public void update(String schemaName, String tableName, DBRow dbRow)
338: throws DBEngineException {
339: Log.getInstance().infoMessage(
340: "Updating record from " + tableName + "...",
341: this .getClass().getName());
342:
343: //Get the list of primary key columns
344: List listOfPrimaryKeyDBTableCells = dbRow
345: .getListOfPrimaryKeyDBTableCells();
346:
347: //If there are no primary keys, raise and exception
348: if (listOfPrimaryKeyDBTableCells == null
349: || listOfPrimaryKeyDBTableCells.isEmpty()) {
350: throw new DBEngineException(
351: "Cannot update a table which does not have a unique primary key column");
352: }
353:
354: //Get the list of cells which have changed
355: List listOfDBTableCellsWhichHaveChanged = dbRow
356: .getListOfChangedNonPrimaryKeyCells();
357:
358: //If there are cells which have changed, then update them
359: if (listOfDBTableCellsWhichHaveChanged != null
360: && (!listOfDBTableCellsWhichHaveChanged.isEmpty())) {
361: //Build the sql using the values which have changed
362: StringBuffer buffer = new StringBuffer();
363: buffer.append("Update " + tableName + " set ");
364: Iterator i = listOfDBTableCellsWhichHaveChanged.iterator();
365: while (i.hasNext()) {
366: DBTableCell dbTableCell = (DBTableCell) i.next();
367:
368: //Format the value for update
369: String formattedValue = this .formatValue(dbTableCell
370: .getValue(), dbTableCell.getColumnInfo()
371: .getColumnType().intValue());
372:
373: //Build the sql
374: buffer.append(dbTableCell.getColumnInfo()
375: .getColumnName()
376: + "=" + formattedValue);
377:
378: if (i.hasNext()) {
379: buffer.append(", ");
380: }
381: }
382:
383: //Build the sql using the primary key columns
384: buffer.append(" where ");
385: i = listOfPrimaryKeyDBTableCells.iterator();
386: while (i.hasNext()) {
387: DBTableCell dbTableCell = (DBTableCell) i.next();
388:
389: //Format the value for update
390: String formattedValue = this .formatValue(dbTableCell
391: .getValue(), dbTableCell.getColumnInfo()
392: .getColumnType().intValue());
393:
394: //Build the sql
395: buffer.append(dbTableCell.getColumnInfo()
396: .getColumnName()
397: + "=" + formattedValue);
398:
399: if (i.hasNext()) {
400: buffer.append(" and ");
401: }
402: }
403:
404: String sql = buffer.toString();
405: Log.getInstance().debugMessage("SQL for update is: " + sql,
406: this .getClass().getName());
407:
408: //Log the SQL
409: SQLLog.getInstance().logSQLStatement(sql);
410:
411: try {
412: this .getStatement().executeUpdate(sql);
413: } catch (SQLException exc) {
414: throw new DBEngineException(exc.getMessage());
415: }
416: }
417:
418: Log.getInstance().infoMessage(
419: "Update complete in " + tableName,
420: this .getClass().getName());
421: }
422:
423: protected String formatValue(Object value, int dataType)
424: throws DBEngineException {
425: String formattedValue = "";
426:
427: if (dataType == Types.VARCHAR) {
428: formattedValue = "'" + value.toString() + "'";
429: } else if (dataType == Types.INTEGER) {
430: formattedValue = value.toString();
431: try {
432: int valueToUpdate = Integer.parseInt(formattedValue);
433: formattedValue = valueToUpdate + "";
434: } catch (NumberFormatException exc) {
435: throw new DBEngineException(exc.getMessage());
436: }
437: } else if (dataType == Types.NUMERIC) {
438: formattedValue = value.toString();
439: try {
440: BigDecimal bd = new BigDecimal(formattedValue);
441: formattedValue = bd.toString() + "";
442: } catch (NumberFormatException exc) {
443: throw new DBEngineException(exc.getMessage());
444: }
445: } else if (dataType == Types.DATE
446: || dataType == Types.TIMESTAMP) {
447: formattedValue = value.toString();
448: try {
449: DateFormat dateFormatForDisplay = DBTableCell
450: .getDateFormat();
451: Date date = dateFormatForDisplay
452: .parse(value.toString());
453: DateFormat dateFormatForUpdate = new SimpleDateFormat(
454: DBTableCell.DATE_FORMAT_STRING);
455: formattedValue = "'" + dateFormatForUpdate.format(date)
456: + "'";
457: } catch (ParseException exc) {
458: throw new DBEngineException(exc.getMessage());
459: }
460: } else if (dataType == Types.BIGINT) {
461: try {
462: long val = Long.parseLong(value.toString());
463: formattedValue = val + "";
464: } catch (NumberFormatException exc) {
465:
466: throw new DBEngineException(exc.getMessage());
467: }
468:
469: } else if (dataType == Types.BIT) {
470: int val = 0;
471: try {
472: val = Integer.parseInt(value.toString());
473: formattedValue = val + "";
474: } catch (NumberFormatException exc) {
475: if (value.toString().equals(true + "")) {
476: val = 1;
477: } else if (value.toString().equals(false + "")) {
478: val = 0;
479: } else {
480: throw new DBEngineException(exc.getMessage());
481: }
482: }
483:
484: } else if (dataType == Types.CHAR) {
485: formattedValue = "'" + value.toString() + "'";
486:
487: } else if (dataType == Types.DECIMAL) {
488: try {
489: BigDecimal val = new BigDecimal(value.toString());
490: formattedValue = val + "";
491: } catch (NumberFormatException exc) {
492:
493: throw new DBEngineException(exc.getMessage());
494: }
495:
496: } else if (dataType == Types.FLOAT) {
497: try {
498: double val = Double.parseDouble(value.toString());
499: formattedValue = val + "";
500: } catch (NumberFormatException exc) {
501:
502: throw new DBEngineException(exc.getMessage());
503: }
504:
505: } else if (dataType == Types.CLOB) {
506:
507: formattedValue = "'" + value.toString() + "'";
508:
509: } else if (dataType == Types.REAL) {
510: try {
511: float val = Float.parseFloat(value.toString());
512: formattedValue = val + "";
513: } catch (NumberFormatException exc) {
514:
515: throw new DBEngineException(exc.getMessage());
516: }
517:
518: } else if (dataType == Types.SMALLINT) {
519: try {
520: int val = Integer.parseInt(value.toString());
521: formattedValue = val + "";
522: } catch (NumberFormatException exc) {
523:
524: throw new DBEngineException(exc.getMessage());
525: }
526:
527: } else if (dataType == Types.TINYINT) {
528: try {
529: int val = Integer.parseInt(value.toString());
530: formattedValue = val + "";
531: } catch (NumberFormatException exc) {
532:
533: throw new DBEngineException(exc.getMessage());
534: }
535: } else {
536: throw new DBEngineException("Cannot format "
537: + value.toString() + " into " + dataType);
538: }
539: return formattedValue;
540: }
541: }
|