org.netxilia.spi.impl.storage.db.CellsMapper.java Source code

Java tutorial

Introduction

Here is the source code for org.netxilia.spi.impl.storage.db.CellsMapper.java

Source

/*******************************************************************************
 * 
 * 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.util.ArrayList;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.log4j.Logger;
import org.netxilia.api.display.Styles;
import org.netxilia.api.exception.NotFoundException;
import org.netxilia.api.exception.StorageException;
import org.netxilia.api.formula.Formula;
import org.netxilia.api.model.CellCreator;
import org.netxilia.api.model.CellData;
import org.netxilia.api.model.CellData.Property;
import org.netxilia.api.model.CellDataWithProperties;
import org.netxilia.api.model.SheetDimensions;
import org.netxilia.api.model.SheetFullName;
import org.netxilia.api.model.SheetType;
import org.netxilia.api.reference.AreaReference;
import org.netxilia.api.reference.CellReference;
import org.netxilia.api.utils.CollectionUtils;
import org.netxilia.api.utils.IListElementCreator;
import org.netxilia.api.utils.Matrix;
import org.netxilia.api.utils.MatrixBuilder;
import org.netxilia.api.value.GenericValueType;
import org.netxilia.api.value.GenericValueUtils;
import org.netxilia.api.value.IGenericValue;
import org.netxilia.api.value.IGenericValueParseService;
import org.netxilia.api.value.StringValue;
import org.netxilia.spi.impl.storage.db.ddl.schema.DbTable;
import org.springframework.beans.factory.annotation.Autowired;

/**
 * This is the most complicated mapper because is stores value in the data table and all the order properties in a
 * compressed (SparseMatrix) format in the properties table.
 * 
 * @author <a href='mailto:ax.craciun@gmail.com'>Alexandru Craciun</a>
 * 
 */
public class CellsMapper extends AbstractMapper {
    private final static Logger log = Logger.getLogger(CellsMapper.class);

    private final static IGenericValue IGNORE_VALUE = new StringValue("__IGNORE");

    private final IListElementCreator<IGenericValue> GENERIC_VALUE_CREATOR = CollectionUtils
            .sameElementCreator(IGNORE_VALUE);

    @Autowired
    private SparseMatrixMapper matrixMapper;

    @Autowired
    private NormalRowsMapper rowsMapper;

    @Autowired
    private IGenericValueParseService parseService;

    public SparseMatrixMapper getMatrixMapper() {
        return matrixMapper;
    }

    public void setMatrixMapper(SparseMatrixMapper matrixMapper) {
        this.matrixMapper = matrixMapper;
    }

    public IGenericValueParseService getParseService() {
        return parseService;
    }

    public void setParseService(IGenericValueParseService parseService) {
        this.parseService = parseService;
    }

    public NormalRowsMapper getRowsMapper() {
        return rowsMapper;
    }

    public void setRowsMapper(NormalRowsMapper rowsMapper) {
        this.rowsMapper = rowsMapper;
    }

    private void saveRow(SheetDbSession session, DbTable valuesTable, int row, List<IGenericValue> cells,
            List<DbColumnStorageInfo> columnStorages) throws NotFoundException {
        DbRowStorageInfo rowStorage = session.getStorageService().getOrCreateRowStorage(session, row);
        StringBuilder query = new StringBuilder("UPDATE " + valuesTable.getName() + " SET ");
        List<Object> params = new ArrayList<Object>(cells.size());

        for (int col = 0; col < cells.size(); ++col) {
            IGenericValue value = cells.get(col);
            if (value == IGNORE_VALUE) {
                continue;
            }
            DbColumnStorageInfo columnStorage = columnStorages.get(col);
            String rawValue = value != null ? value.getStringValue() : null;
            int maxCellSize = valuesTable.getColumn(columnStorage.getDbColumnName()).getSize();
            if (rawValue != null && rawValue.length() > maxCellSize) {
                log.warn(new CellReference(row, col) + " value was truncated from " + rawValue.length() + " to "
                        + maxCellSize);
                rawValue = rawValue.substring(0, maxCellSize);
            }
            if (params.size() > 0) {
                query.append(",");
            }
            query.append(columnStorage.getDbColumnName()).append(" = ?");
            params.add(rawValue);
        }

        if (params.size() == 0) {
            return;
        }
        query.append(" WHERE id = ?");
        params.add(rowStorage.getId());
        session.getWorkbookData().update(query.toString(), params.toArray());
    }

    private int getMaxColumn(Collection<CellDataWithProperties> cells) {
        int maxColumn = 0;
        for (CellDataWithProperties saveCell : cells) {
            CellReference ref = saveCell.getCellData().getReference();
            maxColumn = Math.max(maxColumn, ref.getColumnIndex());
        }
        return maxColumn;
    }

    private void saveNormalData(DbSheetStorageInfo sheetStorage, SheetDbSession session, SheetFullName sheetName,
            Collection<CellDataWithProperties> cells, List<DbColumnStorageInfo> columnStorage)
            throws NotFoundException {
        String valueTableName = sheetStorage.getDbTableName();
        DbTable valuesTable = session.getWorkbookData().getSchema().getTable(valueTableName);
        if (valuesTable == null) {
            // the table should be created by the sheet
            throw new StorageException("The table " + valueTableName + " does not exist");
        }
        // TODO - quick save for one cell

        // cells grouped by row
        // for each row, the map contains the value in the corresponding column position
        // the special generic value is used to mark a cell to be ignored when saving
        Map<Integer, List<IGenericValue>> rows = new LinkedHashMap<Integer, List<IGenericValue>>();
        for (CellDataWithProperties saveCell : cells) {
            if (!saveCell.getProperties().contains(CellData.Property.value)) {
                continue;
            }
            CellReference ref = saveCell.getCellData().getReference();
            List<IGenericValue> row = rows.get(ref.getRowIndex());
            if (row == null) {
                row = new ArrayList<IGenericValue>(ref.getColumnIndex() + 1);
                rows.put(ref.getRowIndex(), row);
            }
            CollectionUtils.atLeastSize(row, ref.getColumnIndex() + 1, GENERIC_VALUE_CREATOR);
            row.set(ref.getColumnIndex(), saveCell.getCellData().getValue());
        }

        // save each row
        for (Map.Entry<Integer, List<IGenericValue>> entry : rows.entrySet()) {
            saveRow(session, valuesTable, entry.getKey(), entry.getValue(), columnStorage);
        }
    }

    public void saveCells(SheetDbSession session, SheetFullName sheetName, Collection<CellDataWithProperties> cells)
            throws NotFoundException {
        DbSheetStorageInfo sheetStorage = session.getStorageService().getSheetStorage(session);

        long t1 = System.currentTimeMillis();
        List<DbColumnStorageInfo> columnStorage = session.getStorageService().createColumnStorage(session,
                getMaxColumn(cells));

        if (sheetStorage.getType() == SheetType.normal) {
            saveNormalData(sheetStorage, session, sheetName, cells, columnStorage);
        }
        long t2 = System.currentTimeMillis();
        SparseMatrixCollection matrices = session.getStorageService().getCellsStorage(session);
        matrices.setListener(matrixMapper.getMatrixCollectionListener(session, sheetStorage));
        try {
            for (CellDataWithProperties saveCell : cells) {

                saveCellProperties(matrices, session, sheetName, sheetStorage.getType(), saveCell.getCellData(),
                        saveCell.getProperties());
            }
        } finally {
            matrices.getListener().save();
            matrices.setListener(null);
        }
        long t3 = System.currentTimeMillis();
        if (log.isDebugEnabled()) {
            log.debug("Mapper save:" + " data=" + (t2 - t1) + " props=" + (t3 - t2));
        }
    }

    /**
     * save a cell's property (including value for sheets of type other than "normal")
     */
    private CellData saveCellProperties(SparseMatrixCollection matrices, SheetDbSession session,
            SheetFullName sheetFullName, SheetType sheetType, CellData cell, Collection<Property> properties)
            throws NotFoundException {
        // make sure the corresponding row exists
        session.getStorageService().getOrCreateRowStorage(session, cell.getReference().getRowIndex());

        for (Property property : properties) {
            if (Property.value == property) {
                // TODO - store rich values

                if (sheetType != SheetType.normal) {
                    // create column
                    // create row
                    String rawValue = cell.getValue() != null ? cell.getValue().getStringValue() : null;
                    matrices.put(cell.getReference().getRowIndex(), cell.getReference().getColumnIndex(),
                            property.name(), rawValue);
                }
                // add also type
                matrices.put(cell.getReference().getRowIndex(), cell.getReference().getColumnIndex(),
                        CellData.Property.type.name(),
                        cell.getValue() != null ? cell.getValue().getValueType().name() : null);

            } else {
                // update the other properties
                Object propertyValue = null;
                try {
                    propertyValue = PropertyUtils.getSimpleProperty(cell, property.name());
                } catch (Exception e) {
                    log.error("Cannot get cell property " + property + ":" + e, e);
                }
                matrices.put(cell.getReference().getRowIndex(), cell.getReference().getColumnIndex(),
                        property.name(), propertyValue != null ? propertyValue.toString() : null);
                // save modification right away
            }
        }

        return cell;
    }

    public Matrix<CellData> loadCells(SheetDbSession data, SheetFullName sheetFullName, AreaReference area)
            throws StorageException, NotFoundException {
        DbSheetStorageInfo sheetStorage = data.getStorageService().getSheetStorage(data);
        SheetDimensions dimensions = data.getStorageService().getSheetDimensions(data);
        if (dimensions.getRowCount() == 0 || dimensions.getColumnCount() == 0) {
            // empty sheet
            return new Matrix<CellData>();
        }
        if (area.getFirstColumnIndex() >= dimensions.getColumnCount()
                || area.getFirstRowIndex() >= dimensions.getRowCount()) {
            // the desired area is completely outside the sheet
            return new Matrix<CellData>();
        }
        AreaReference boundArea = area.bind(dimensions.getRowCount(), dimensions.getColumnCount());
        if (boundArea.getSheetName() == null) {
            boundArea = boundArea.withSheetName(sheetFullName.getSheetName());
        }
        List<List<String>> values = null;
        if (sheetStorage.getType() == SheetType.normal) {
            values = rowsMapper.loadValues(data, sheetStorage, boundArea);
        }
        SparseMatrixCollection matrices = data.getStorageService().getCellsStorage(data);

        MatrixBuilder<CellData> cells = new MatrixBuilder<CellData>(new CellCreator(boundArea.getSheetName(),
                boundArea.getFirstRowIndex(), boundArea.getFirstColumnIndex()));
        for (CellReference cell : boundArea.iterable(dimensions.getRowCount(), dimensions.getColumnCount())) {
            int relativeRowIndex = cell.getRowIndex() - boundArea.getFirstRowIndex();
            int relativeColIndex = cell.getColumnIndex() - boundArea.getFirstColumnIndex();

            // load the other properties
            String type = matrices.get(cell.getRowIndex(), cell.getColumnIndex(), CellData.Property.type.name());
            String style = matrices.get(cell.getRowIndex(), cell.getColumnIndex(), CellData.Property.styles.name());
            String formula = matrices.get(cell.getRowIndex(), cell.getColumnIndex(),
                    CellData.Property.formula.name());

            String rawValue = null;
            if (sheetStorage.getType() == SheetType.normal) {
                if (relativeRowIndex < values.size()) {
                    List<String> row = values.get(relativeRowIndex);
                    if (row != null && relativeColIndex < row.size()) {
                        rawValue = row.get(relativeColIndex);
                    }
                }
            } else {
                rawValue = matrices.get(cell.getRowIndex(), cell.getColumnIndex(), CellData.Property.value.name());
            }
            IGenericValue value = null;
            try {
                // TODO - store rich values
                value = GenericValueUtils.rawStringToGeneric(rawValue,
                        type != null ? GenericValueType.valueOf(type) : GenericValueType.STRING);
            } catch (Exception e) {
                log.error("Could not convert [" + rawValue + "] to type " + type + ". Re-parse it.");
                value = parseService.parse(rawValue);
            }

            CellData cellData = new CellData(cell, value, Formula.valueOf(formula), Styles.valueOf(style));
            cells.set(relativeRowIndex, relativeColIndex, cellData);

        }

        return cells.build();
    }

}