public String generateTableRowDeleteStatement(ITableRowData tableRowData) { StringBuffer deleteBuffer = new StringBuffer(); deleteBuffer.append( "DELETE FROM " //$NON-NLS-1$ + tableRowData.getTable().getQualifiedName() + " "); //$NON-NLS-1$ // Find unique (Primary Key) column to find the row List<ITableColumnData> uniqueColumns = new ArrayList<ITableColumnData>(); for (int i = 0; i < tableRowData.getColumnCount(); i++) { IColumn column = tableRowData.getTableColumnData(i).getColumn(); if ((column != null) && column.isPrimaryKey()) { uniqueColumns.add(tableRowData.getTableColumnData(i)); } } if (uniqueColumns.isEmpty()) { // Treat all columns as Unique columns, result is unsure uniqueColumns = tableRowData.getTableColumnData(); } int firstColumn = 0; for (ITableColumnData tableColumnData : uniqueColumns) { if (tableColumnData.getColumn().getType().getSearchable() == ISqlType.SEARCHABLE) { String whereClause; if (firstColumn == 0) { whereClause = "WHERE "; // $NON-NLS-1$ firstColumn++; } else { whereClause = " AND "; // $NON-NLS-1$ } deleteBuffer.append(whereClause); deleteBuffer.append( quoteIdentifier(tableRowData.getTable().getDatabaseInfo(), tableColumnData.getName())); if (tableColumnData.getValueAsString() == null) { deleteBuffer.append(" is null "); // $NON-NLS-1$ } else { deleteBuffer.append(" = "); // $NON-NLS-1$ deleteBuffer.append(getQuotedValue(tableColumnData)); } } } deleteBuffer.append(";\n"); // $NON-NLS-1$ // if (generateCommit(tableRowData.getTable())) { // deleteBuffer.append("COMMIT;"); // } return deleteBuffer.toString(); }
public String generateTableRowInsertStatement(ITableRowData tableRowData) { StringBuffer insertBuffer = new StringBuffer(); insertBuffer.append( "INSERT INTO " //$NON-NLS-1$ + tableRowData.getTable().getQualifiedName() + " "); //$NON-NLS-1$ StringBuffer columnsBuffer = new StringBuffer(); columnsBuffer.append("( "); // $NON-NLS-1$ StringBuffer valuesBuffer = new StringBuffer(); valuesBuffer.append("( "); // $NON-NLS-1$ for (int i = 0; i < tableRowData.getColumnCount(); i++) { ITableColumnData columnData = tableRowData.getTableColumnData(i); if (!columnData.isAutoIncrement() && !columnData.isReadOnly() && (columnData.getValue() != null) && !columnData.getValueAsString().trim().equals("")) { columnsBuffer.append( quoteIdentifier(tableRowData.getTable().getDatabaseInfo(), columnData.getName())); if (i < tableRowData.getColumnCount() - 1) { columnsBuffer.append(","); // $NON-NLS-1$ } valuesBuffer.append(getQuotedValue(columnData)); if (i < tableRowData.getColumnCount() - 1) { valuesBuffer.append(","); // $NON-NLS-1$ } } } columnsBuffer.append(") "); // $NON-NLS-1$ valuesBuffer.append(") "); // $NON-NLS-1$ insertBuffer.append(columnsBuffer.toString()); insertBuffer.append("VALUES "); // $NON-NLS-1$ insertBuffer.append(valuesBuffer.toString()); insertBuffer.append(";\n"); // $NON-NLS-1$ // if (generateCommit(tableRowData.getTable())) { // insertBuffer.append("COMMIT;"); // } // ErrorManager.showInformationMessage(insertBuffer.toString()); return insertBuffer.toString(); }
public String generateTableRowUpdateStatement( ITableRowData currentRowData, ITableRowData updateRowData) { StringBuffer updateBuffer = new StringBuffer(); updateBuffer.append( "UPDATE " //$NON-NLS-1$ + currentRowData.getTable().getQualifiedName() + " "); //$NON-NLS-1$ // Find unique (Primary Key) column to find the row List<ITableColumnData> uniqueColumns = new ArrayList<ITableColumnData>(); for (int i = 0; i < currentRowData.getColumnCount(); i++) { ITableColumnData tableColumnData = currentRowData.getTableColumnData(i); IColumn column = tableColumnData.getColumn(); if ((column != null) && column.isPrimaryKey()) { uniqueColumns.add(tableColumnData); } } int updatedColumnCount = 0; for (int i = 0; i < updateRowData.getColumnCount(); i++) { ITableColumnData columnData = updateRowData.getTableColumnData(i); if (!columnData.isAutoIncrement() && !columnData.isReadOnly() && (columnData.getValue() != null) && !columnData.getValueAsString().trim().equals("")) { if (updatedColumnCount == 0) { updateBuffer.append("SET "); // $NON-NLS-1$ } else { updateBuffer.append(", "); // $NON-NLS-1$ } updateBuffer.append( quoteIdentifier(currentRowData.getTable().getDatabaseInfo(), columnData.getName())); updateBuffer.append(" = "); // $NON-NLS-1$ updateBuffer.append(getQuotedValue(columnData)); updatedColumnCount++; } } if (uniqueColumns.isEmpty()) { // Treat all columns as Unique columns, result is unsure uniqueColumns = currentRowData.getTableColumnData(); } int firstColumn = 0; for (ITableColumnData tableColumnData : uniqueColumns) { if (tableColumnData.getColumn().getType().getSearchable() == ISqlType.SEARCHABLE) { String whereClause; if (firstColumn == 0) { whereClause = " WHERE "; // $NON-NLS-1$ } else { whereClause = " AND "; // $NON-NLS-1$ } updateBuffer.append(whereClause); updateBuffer.append( quoteIdentifier( currentRowData.getTable().getDatabaseInfo(), tableColumnData.getName())); if (tableColumnData.getValueAsString() == null) { updateBuffer.append(" is null "); // $NON-NLS-1$ } else { updateBuffer.append(" = "); // $NON-NLS-1$ updateBuffer.append(getQuotedValue(tableColumnData)); } } } updateBuffer.append(";\n"); // $NON-NLS-1$ // if (generateCommit(currentRowData.getTable())) { // updateBuffer.append("COMMIT;"); // } // ErrorManager.showInformationMessage(updateBuffer.toString()); return updateBuffer.toString(); }