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 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 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();
}