/*******************************************************************************
*
* Copyright 2010 Alexandru Craciun, and individual contributors as indicated
* by the @authors tag.
*
* This is free software; you can redistribute it and/or modify it
* under the terms of the GNU Lesser General Public License as
* published by the Free Software Foundation; either version 3 of
* the License, or (at your option) any later version.
*
* This software is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this software; if not, write to the Free
* Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
* 02110-1301 USA, or see the FSF site: http://www.fsf.org.
******************************************************************************/
package org.netxilia.spi.impl.storage.db;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.log4j.Logger;
import org.netxilia.api.exception.StorageException;
import org.netxilia.api.model.SheetFullName;
import org.netxilia.api.reference.Range;
import org.netxilia.api.storage.IJsonSerializer;
import org.netxilia.spi.impl.storage.db.ddl.schema.DbTable;
import org.netxilia.spi.impl.storage.db.sql.RowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
public abstract class AbstractMapper {
private final static Logger log = Logger.getLogger(AbstractMapper.class);
public final static String COLUMN_CATEGORY = "column";
public final static String ROW_CATEGORY = "row";
public final static String SHEET_CATEGORY = "sheet";
public final static String CELLS_CATEGORY = "cells";
/**
* to store different all other properties not stored as values
*/
private DbTable propertiesTableModel;
@Autowired
private IJsonSerializer jsonSerializer;
private DbWorkbookStorageServiceImpl storageService;
/**
* this value should match the size of the "value" column in the properties table schema
*/
private int smallValueSize = 150;
public int getSmallValueSize() {
return smallValueSize;
}
public void setSmallValueSize(int smallValueSize) {
this.smallValueSize = smallValueSize;
}
public DbTable getPropertiesTableModel() {
return propertiesTableModel;
}
public void setPropertiesTableModel(DbTable propertiesTableModel) {
this.propertiesTableModel = propertiesTableModel;
}
public IJsonSerializer getJsonSerializer() {
return jsonSerializer;
}
public void setJsonSerializer(IJsonSerializer jsonSerializer) {
this.jsonSerializer = jsonSerializer;
}
public DbWorkbookStorageServiceImpl getStorageService() {
return storageService;
}
public void setStorageService(DbWorkbookStorageServiceImpl storageService) {
this.storageService = storageService;
}
protected String ensureTableExists(String tableName, DbTable tableModel, WorkbookDbSession data)
throws StorageException {
if (data.getSchema().getTable(tableName) == null) {
// the table does not even exist
DbTable sheetTable = new DbTable(tableModel);
sheetTable.setName(tableName);
try {
data.getDdl().writer().createTable(data.getSchema(), sheetTable);
} catch (SQLException e) {
throw new StorageException(e);
}
}
// check also columns are the same!
return tableName;
}
protected String getPropertiesTableName(WorkbookDbSession data) {
String tableName = data.getWorkbookId() + propertiesTableModel.getName();
return tableName;
}
protected <E extends Enum<E>> List<String> toStringList(Collection<E> enumProperties) {
List<String> strings = new ArrayList<String>(enumProperties.size());
for (Enum<?> e : enumProperties) {
strings.add(e.name());
}
return strings;
}
protected <T> void addObject(WorkbookDbSession data, SheetId sheetId, String category, T object, Object objectId,
List<String> properties) throws StorageException {
addObject(data, sheetId, category, object, objectId, properties, new DefaultPropertySerializer<T>());
}
protected <T> void addObject(WorkbookDbSession data, SheetId sheetId, String category, T object, Object objectId,
List<String> properties, IPropertySerializer<T> propertySerializer) throws StorageException {
if (properties == null) {
return;
}
for (String property : properties) {
String propertyValue = propertySerializer.serializeProperty(object, property);
if (propertyValue != null) {
addProperty(data, sheetId, category, objectId, property, propertyValue.toString());
}
}
}
protected <T> void setObject(WorkbookDbSession data, SheetId sheetId, String category, T object, Object objectId,
List<String> properties) throws StorageException {
setObject(data, sheetId, category, object, objectId, properties, new DefaultPropertySerializer<T>());
}
protected <T> void setObject(WorkbookDbSession data, SheetId sheetId, String category, T object, Object objectId,
List<String> properties, IPropertySerializer<T> propertySerializer) throws StorageException {
if (properties == null) {
return;
}
for (String property : properties) {
String propertyValue = propertySerializer.serializeProperty(object, property);
if (propertyValue == null) {
deleteProperty(data, sheetId, category, objectId, property);
} else {
if (!setProperty(data, sheetId, category, objectId, property, propertyValue.toString())) {
addProperty(data, sheetId, category, objectId, property, propertyValue.toString());
}
}
}
}
protected boolean addProperty(WorkbookDbSession data, SheetId sheetId, String category, Object objectId,
String property, Object value) throws StorageException {
String tableName = getPropertiesTableName(data);
ensureTableExists(tableName, propertiesTableModel, data);
// TODO may use JSON here for the value
try {
String rawValue = value.toString();
String smallValue = rawValue.length() <= smallValueSize ? rawValue : null;
String bigValue = rawValue.length() <= smallValueSize ? null : rawValue;
int rows = data.update("INSERT INTO " + tableName
+ " (sheet_id, category, object, property, value, big_value) VALUES (?,?,?,?,?,?)", //
sheetId.getId(), category, objectId.toString(), property, smallValue, bigValue);
return rows != 0;
} catch (Exception ex) {
throw new StorageException("Caused when inserting:" + sheetId.getId() + "," + category + ","
+ objectId.toString() + "," + property + "," + value.toString(), ex);
}
}
protected boolean setProperty(WorkbookDbSession data, SheetId sheetId, String category, Object objectId,
String property, Object value) throws StorageException {
String tableName = getPropertiesTableName(data);
ensureTableExists(tableName, propertiesTableModel, data);
String rawValue = value.toString();
String smallValue = rawValue.length() <= smallValueSize ? rawValue : null;
String bigValue = rawValue.length() <= smallValueSize ? null : rawValue;
// TODO may use JSON here for the value
int rows = data.update("UPDATE " + tableName
+ " SET value = ?, big_value = ? WHERE sheet_id = ? AND category = ? AND object = ? AND property = ?", //
smallValue, bigValue, sheetId.getId(), category, objectId.toString(), property);
return rows != 0;
}
protected boolean deleteProperty(WorkbookDbSession data, SheetId sheetId, String category, Object objectId,
String property) throws StorageException {
String tableName = getPropertiesTableName(data);
ensureTableExists(tableName, propertiesTableModel, data);
List<Object> whereParams = new ArrayList<Object>(3);
StringBuilder query = new StringBuilder("DELETE FROM " + tableName + " ");
addParam(query, whereParams, "sheet_id", sheetId.getId());
addParam(query, whereParams, "category", category);
if (objectId != null) {
addParam(query, whereParams, "object", objectId.toString());
}
addParam(query, whereParams, "property", property);
try {
int rows = data.update(query.toString(), whereParams.toArray());
return rows != 0;
} catch (Exception ex) {
throw new StorageException("Cannot execute query: " + query + " params:" + whereParams + ":" + ex, ex);
}
}
@SuppressWarnings("rawtypes")
private void addParam(StringBuilder query, List<Object> whereParams, String param, Object paramValue) {
if (paramValue != null) {
if (whereParams.size() == 0) {
query.append("WHERE");
} else {
query.append("AND");
}
if (paramValue instanceof Collection) {
query.append(" ").append(param).append(" IN (");
boolean first = true;
for (Object itemValue : (Collection) paramValue) {
if (!first) {
query.append(",");
}
query.append("?");
whereParams.add(itemValue);
first = false;
}
query.append(") ");
} else {
query.append(" ").append(param).append(" = ? ");
whereParams.add(paramValue);
}
}
}
/**
* return the list of the properties from the database corresponding the the given sheet. If the category, objectId
* or property are null they will not be used in the where clause.
*
* @
*/
protected String getProperty(WorkbookDbSession data, SheetId sheetId, String category, Object objectId,
String property) {
String tableName = getPropertiesTableName(data);
if (data.getSchema().getTable(tableName) == null) {
return null;
}
List<Object> whereParams = new ArrayList<Object>(3);
StringBuilder query = new StringBuilder("SELECT * FROM " + tableName + " ");
addParam(query, whereParams, "sheet_id", sheetId.getId());
addParam(query, whereParams, "category", category);
addParam(query, whereParams, "object", objectId);
addParam(query, whereParams, "property", property);
List<String> values = data.query(query.toString(), //
new PropertyLoader<Object>(null, null), whereParams.toArray());
return values.size() > 0 ? values.get(0) : null;
}
/**
* return the count of the properties from the database corresponding the the given sheet. If the category, objectId
* or property are null they will not be used in the where clause.
*
* @
*/
protected int count(WorkbookDbSession data, SheetId sheetId, String category, Object objectId, String property) {
String tableName = getPropertiesTableName(data);
if (data.getSchema().getTable(tableName) == null) {
return 0;
}
List<Object> whereParams = new ArrayList<Object>(3);
StringBuilder query = new StringBuilder("SELECT COUNT(*) FROM " + tableName + " ");
addParam(query, whereParams, "sheet_id", sheetId.getId());
addParam(query, whereParams, "category", category);
addParam(query, whereParams, "object", objectId);
addParam(query, whereParams, "property", property);
return data.queryForInt(query.toString(), whereParams.toArray());
}
private boolean queryForProperties(WorkbookDbSession data, SheetId sheetId, RowMapper<String> rowMapper,
String category, Object objectId, String property, Range records) {
String tableName = getPropertiesTableName(data);
if (data.getSchema().getTable(tableName) == null) {
return false;
}
List<Object> whereParams = new ArrayList<Object>(3);
StringBuilder query = new StringBuilder("SELECT * FROM " + tableName + " ");
if (sheetId != null) {
addParam(query, whereParams, "sheet_id", sheetId.getId());
}
addParam(query, whereParams, "category", category);
addParam(query, whereParams, "object", objectId);
addParam(query, whereParams, "property", property);
query.append("order by category, object, property");
if (records != null && !records.equals(Range.ALL)) {
ParameterizedQuery pq = new ParameterizedQuery(query.toString(), whereParams);
pq = DatabaseUtils.limitQuery(pq, records);
data.query(pq.getQuery(), //
rowMapper, pq.getParams().toArray());
} else {
data.query(query.toString(), //
rowMapper, whereParams.toArray());
}
return true;
}
/**
* return the list of the properties from the database corresponding the the given sheet. If the category, objectId
* or property are null they will not be used in the where clause.
*
* @
*/
protected <T> List<T> getProperties(WorkbookDbSession data, SheetId sheetId, IInstanceProvider<T> instanceProvider,
String category, Object objectId, String property) {
Map<String, T> instances = new LinkedHashMap<String, T>();
queryForProperties(data, sheetId, new PropertyLoader<T>(instances, instanceProvider), category, objectId,
property, null);
return new ArrayList<T>(instances.values());
}
/**
* return the list of the properties from the database corresponding the the given sheet. If the category, objectId
* or property are null they will not be used in the where clause.
*
* @
*/
protected <T> List<T> getProperties(WorkbookDbSession data, SheetId sheetId,
IInstanceProviderFromMap<T> instanceProvider, String category, Object objectId, String property) {
return getProperties(data, sheetId, instanceProvider, category, objectId, property, null);
}
/**
* return the list of the properties from the database corresponding the the given sheet. If the category, objectId
* or property are null they will not be used in the where clause.
*
* @
*/
protected <T> List<T> getProperties(WorkbookDbSession data, SheetId sheetId,
IInstanceProviderFromMap<T> instanceProvider, String category, Object objectId, String property,
Range records) {
PropertyInMapLoader loader = new PropertyInMapLoader();
queryForProperties(data, sheetId, loader, category, objectId, property, records);
// transfer the map of properties to an object
List<T> results = new ArrayList<T>();
for (Map.Entry<String, Map<String, String>> entry : loader.getInstances().entrySet()) {
T result = instanceProvider.newInstance(category, entry.getKey(), entry.getValue());
if (result != null) {
results.add(result);
}
}
return results;
}
protected SheetId generateNewSheetId(WorkbookDbSession data) {
String tableName = getPropertiesTableName(data);
ensureTableExists(tableName, propertiesTableModel, data);
String query = "SELECT MAX(sheet_id) FROM " + tableName;
int max = data.queryForInt(query);
// will return 0 if none sheet exists yet
return new SheetId(max + 1);
}
protected int getMaxObjectId(WorkbookDbSession data, SheetId sheetId, String category) {
String tableName = getPropertiesTableName(data);
ensureTableExists(tableName, propertiesTableModel, data);
String query = "SELECT MAX(object) FROM " + tableName + " WHERE sheet_id = ? AND category = ?";
return data.queryForInt(query.toString(), //
sheetId.getId(), category);
}
public SheetId findSheetIdByName(WorkbookDbSession data, SheetFullName fullName) {
String tableName = getPropertiesTableName(data);
if (data.getSchema().getTable(tableName) == null) {
return null;
}
String query = "SELECT sheet_id FROM " + tableName + " WHERE category = ? AND value = ?";
try {
int sheetId = data.queryForInt(query.toString(), //
SHEET_CATEGORY, fullName.getSheetName());
return new SheetId(sheetId);
} catch (EmptyResultDataAccessException ex) {
// not found
return null;
}
}
/** Internal helper that is used to restore a workbook from DB */
private class PropertyLoader<T> implements RowMapper<String> {
private final Map<String, T> instances;
private final IInstanceProvider<T> instanceProvider;
public PropertyLoader(Map<String, T> instances, IInstanceProvider<T> instanceProvider) {
this.instances = instances;
this.instanceProvider = instanceProvider;
}
@SuppressWarnings("unchecked")
@Override
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String objectId = rs.getString("object");
String property = rs.getString("property");
String smallValue = rs.getString("value");
String bigValue = rs.getString("big_value");
String value = smallValue != null ? smallValue : bigValue;
if (instanceProvider != null) {
T object = instances.get(objectId);
if (object == null) {
object = instanceProvider.newInstance(rs.getString("category"), objectId);
instances.put(objectId, object);
}
// give first a chance to instance provider to deal with the property
if (!instanceProvider.setProperty(object, property, value)) {
try {
// TODO should use here Type Converters or JSON !?
BeanUtils.setProperty(
object,
property,
jsonSerializer.deserialize(PropertyUtils.getPropertyType(object, property), "\""
+ value + "\""));
} catch (Exception e) {
log.error("Cannot set property " + property + " of object:" + object);
}
}
}
return value;
}
}
// /** Internal helper that is used to restore a workbook from DB */
private class PropertyInMapLoader implements RowMapper<String> {
private final Map<String, Map<String, String>> instances;
public PropertyInMapLoader() {
this.instances = new LinkedHashMap<String, Map<String, String>>();
}
public Map<String, Map<String, String>> getInstances() {
return instances;
}
@Override
public String mapRow(ResultSet rs, int rowNum) throws SQLException {
String objectId = rs.getString("object");
String property = rs.getString("property");
String smallValue = rs.getString("value");
String bigValue = rs.getString("big_value");
String value = smallValue != null ? smallValue : bigValue;
Map<String, String> object = instances.get(objectId);
if (object == null) {
object = new HashMap<String, String>();
instances.put(objectId, object);
}
object.put(property, value);
return value;
}
}
}