/*
* This software is distributed under the terms of the FSF
* Gnu Lesser General Public License (see lgpl.txt).
*
* This program is distributed WITHOUT ANY WARRANTY. See the
* GNU General Public License for more details.
*/
package com.scooterframework.builtin.databrowser;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import com.scooterframework.common.logging.LogUtil;
import com.scooterframework.common.util.StringUtil;
import com.scooterframework.orm.sqldataexpress.object.ColumnInfo;
import com.scooterframework.orm.sqldataexpress.object.OmniDTO;
import com.scooterframework.orm.sqldataexpress.object.RowData;
import com.scooterframework.orm.sqldataexpress.object.RowInfo;
import com.scooterframework.orm.sqldataexpress.object.TableData;
import com.scooterframework.orm.sqldataexpress.object.TableInfo;
import com.scooterframework.orm.sqldataexpress.processor.DataProcessor;
import com.scooterframework.orm.sqldataexpress.processor.DataProcessorTypes;
import com.scooterframework.orm.sqldataexpress.service.SqlServiceConfig;
import com.scooterframework.orm.sqldataexpress.util.SqlExpressUtil;
/**
* Record model class handles record related access.
*
* @author (Fei) John Chen
*/
public class Record {
private static LogUtil log = LogUtil.getLogger(Record.class.getName());
public static String getFinderSql(String connName, String table) {
return SqlExpressUtil.getFinderSQL(connName, Table.getSafeTableName(connName, table));
}
public static List<RowData> getRows(String connName, String table) {
return getRows(connName, table, null);
}
public static List<RowData> getRows(String connName, String table, String whereClause) {
RowInfo ri = getRowInfo(connName, table);
// prepare inputs map
Map<String, Object> inputs = new HashMap<String, Object>();
String processorType = DataProcessorTypes.DIRECT_SQL_STATEMENT_PROCESSOR;
String processorName = getFinderSql(connName, table);
if (whereClause != null)
processorName = processorName + " " + whereClause;
// retrieve all records
inputs.put(DataProcessor.input_key_database_connection_name, connName);
TableData td = SqlServiceConfig.getSqlService().retrieveRows(inputs, processorType, processorName);
if (td != null) {
td.setHeader(ri);
}
return (td != null) ? td.getAllRows() : null;
}
public static RowData getRow(String connName, String table, String restfulId) {
RowInfo ri = getRowInfo(connName, table);
//prepare inputs map
Map<String, Object> inputs = new HashMap<String, Object>();
inputs.put(DataProcessor.input_key_database_connection_name, connName);
inputs.put("id", restfulId);
String processorType = DataProcessorTypes.DIRECT_SQL_STATEMENT_PROCESSOR;
String processorName = getFinderSql(connName, table);
String condition = prepareDynamicWhereClauseForRestfulId(inputs, ri, restfulId);
if (condition != null && !"".equals(condition)) processorName += " WHERE " + condition;
log.debug("select sql = " + processorName);
log.debug("select inputs = " + inputs);
TableData td = SqlServiceConfig.getSqlService().retrieveRow(inputs, processorType, processorName);
if (td != null) {
td.setHeader(ri);
}
return (td != null)?td.getFirstRow():null;
}
public static RowData createRecord(Map<String, Object> inputs, String connName, String table) {
RowInfo ri = getRowInfo(connName, table);
inputs = StringUtil.convertKeyToUpperCase(inputs);
StringBuilder insertSQL = new StringBuilder();
insertSQL.append("INSERT INTO ").append(SqlExpressUtil.getExpandedTableName(connName, table));
insertSQL.append(" ").append(prepareInsertSQL(ri, inputs));
log.debug("insert sql = " + insertSQL);
log.debug("insert inputs = " + inputs);
inputs.put(DataProcessor.input_key_database_connection_name, connName);
OmniDTO returnTO =
SqlServiceConfig.getSqlService().execute(inputs, DataProcessorTypes.DIRECT_SQL_STATEMENT_PROCESSOR, insertSQL.toString());
int insertCount = returnTO.getUpdatedRowCount();
RowData record = null;
if (insertCount == 1) {
record = new RowData(ri, null);
record.setData(inputs);
}
return record;
}
public static int updateRecord(Map<String, Object> inputs, String connName, String table, String restfulId) {
RowInfo ri = getRowInfo(connName, table);
inputs = StringUtil.convertKeyToUpperCase(inputs);
StringBuilder updateSQL = new StringBuilder();
updateSQL.append("UPDATE ").append(SqlExpressUtil.getExpandedTableName(connName, table));
updateSQL.append(" SET ").append(prepareSetSQL(ri, inputs));
String condition = prepareDynamicWhereClauseForRestfulId(inputs, ri, restfulId);
if (condition != null && !"".equals(condition)) updateSQL.append(" WHERE ").append(condition);
log.debug("updates sql = " + updateSQL);
log.debug("updates inputs = " + inputs);
inputs.put(DataProcessor.input_key_database_connection_name, connName);
OmniDTO returnTO =
SqlServiceConfig.getSqlService().execute(inputs, DataProcessorTypes.DIRECT_SQL_STATEMENT_PROCESSOR, updateSQL.toString());
int updateCount = returnTO.getUpdatedRowCount();
return updateCount;
}
public static int deleteRecord(Map<String, Object> inputs, String connName, String table, String restfulId) {
RowInfo ri = getRowInfo(connName, table);
StringBuilder deleteSQL = new StringBuilder();
deleteSQL.append("DELETE FROM ").append(SqlExpressUtil.getExpandedTableName(connName, table));
String condition = prepareDynamicWhereClauseForRestfulId(inputs, ri, restfulId);
if (condition != null && !"".equals(condition)) deleteSQL.append(" WHERE ").append(condition);
log.debug("delete sql = " + deleteSQL);
log.debug("delete inputs = " + inputs);
inputs.put(DataProcessor.input_key_database_connection_name, connName);
OmniDTO returnTO =
SqlServiceConfig.getSqlService().execute(inputs, DataProcessorTypes.DIRECT_SQL_STATEMENT_PROCESSOR, deleteSQL.toString());
int deleteCount = returnTO.getUpdatedRowCount();
return deleteCount;
}
public static RowInfo getRowInfo(String connName, String table) {
RowInfo ri = null;
TableInfo ti = SqlExpressUtil.lookupTableInfo(connName, table);
if (ti != null) {
ri = ti.getHeader();
}
if (ri == null) {
throw new IllegalArgumentException("Failed to retrieve column header " +
"information from table \"" + table + "\" with " +
"connection \"" + connName + "\"");
}
return ri;
}
public static String prepareInsertSQL(RowInfo ri, Map<String, Object> inputs) {
Set<String> inputKeys = inputs.keySet();
StringBuilder names = new StringBuilder();
StringBuilder values = new StringBuilder();
int maxSize = ri.getDimension();
ColumnInfo ci = null;
for(int i = 0; i < maxSize; i++) {
ci = ri.getColumnInfo(i);
String columnName = ci.getColumnName();
if (columnName == null) continue;
columnName = columnName.toUpperCase();
if (!inputKeys.contains(columnName)) {
if (!ri.isAuditedForCreate(columnName)) {
continue;
}
else {
inputs.put(columnName, getCurrentTimestamp());
}
}
names.append(columnName).append(", ");
values.append("?").append(columnName).append(", ");
}
StringBuilder result = new StringBuilder();
result.append("(").append(StringUtil.removeLastToken(names, ", "));
result.append(") VALUES (").append(StringUtil.removeLastToken(values, ", ")).append(")");
return result.toString();
}
public static String prepareSetSQL(RowInfo ri, Map<String, Object> inputs) {
Set<String> inputKeys = inputs.keySet();
StringBuilder sb = new StringBuilder();
int maxSize = ri.getDimension();
ColumnInfo ci = null;
for(int i = 0; i < maxSize; i++) {
ci = ri.getColumnInfo(i);
String columnName = ci.getColumnName();
if (columnName == null) continue;
columnName = columnName.toUpperCase();
if (ci.isReadOnly() || !ci.isWritable() || ci.isPrimaryKey()) continue;
if (!inputKeys.contains(columnName)) {
if (!ri.isAuditedForUpdate(columnName)) {
continue;
}
else {
inputs.put(columnName, getCurrentTimestamp());
}
}
sb.append(columnName).append(" = ?").append(columnName).append(", ");
}
return StringUtil.removeLastToken(sb, ", ").toString();
}
public static String prepareDynamicWhereClauseForRestfulId(Map<String, Object> inputs, RowInfo ri, String restfulId) {
String condition = "";
Map<String, String> map = SqlExpressUtil.getTableKeyMapForRestfulId(ri, restfulId);
if (map != null && map.size() > 0) {
condition = getDynamicWhereClauseForTableKeyMap(ri.getTable(), map);
inputs.putAll(map);
}
return condition;
}
private static String getDynamicWhereClauseForTableKeyMap(String table, Map<String, String> tableKeyMap) {
Map<String, String> map = new HashMap<String, String>();
StringBuilder sb = new StringBuilder();
if (tableKeyMap != null && tableKeyMap.size() > 0) {
for (Map.Entry<String, String> entry : tableKeyMap.entrySet()) {
String column = entry.getKey();
String token = table + "." + column;
sb.append(token).append("= ?").append(token).append(" AND ");
map.put(token, entry.getValue());
}
tableKeyMap.putAll(map);
}
return StringUtil.removeLastToken(sb.toString(), "AND ");
}
private static Timestamp getCurrentTimestamp() {
return new Timestamp(System.currentTimeMillis());
}
}