DBUpdateLargeObjects.java

/*
 * Copyright 2013 Gregory Graham.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package nz.co.gregs.dbvolution.actions;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.CharArrayReader;
import java.io.IOError;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import nz.co.gregs.dbvolution.databases.DBDatabase;
import nz.co.gregs.dbvolution.DBRow;
import nz.co.gregs.dbvolution.databases.DBStatement;
import nz.co.gregs.dbvolution.databases.QueryIntention;
import nz.co.gregs.dbvolution.databases.definitions.DBDefinition;
import nz.co.gregs.dbvolution.internal.query.LargeObjectHandlerType;
import nz.co.gregs.dbvolution.datatypes.DBLargeObject;
import nz.co.gregs.dbvolution.datatypes.QueryableDatatype;
import nz.co.gregs.dbvolution.exceptions.DBRuntimeException;
import nz.co.gregs.dbvolution.internal.properties.PropertyWrapper;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Provides support for the abstract concept of updating rows with BLOB columns.
 *
 * <p>
 * The best way to use this is by using {@link DBUpdate#getUpdates(nz.co.gregs.dbvolution.DBRow...)
 * } to automatically use this action.
 *
 * <p style="color: #F90;">Support DBvolution at
 * <a href="http://patreon.com/dbvolution" target=new>Patreon</a></p>
 *
 * @author Gregory Graham
 */
public class DBUpdateLargeObjects extends DBUpdate {

    private static final long serialVersionUID = 1l;

    private static final Log LOG = LogFactory.getLog(DBUpdateLargeObjects.class);

    /**
     * Creates a DBUpdateLargeObjects action for the supplied row.
     *
     * @param row the row to be updated
     */
    protected DBUpdateLargeObjects(DBRow row) {
        super(row, QueryIntention.UPDATE_ROW_WITH_LARGE_OBJECT);
    }
    /**
     * Creates a DBUpdateLargeObjects action for the supplied row.
     *
     * @param row the row to be updated
     * @param intent the intended action this is being used for
     */
    protected DBUpdateLargeObjects(DBRow row, QueryIntention intent) {
        super(row, intent);
    }

    @Override
    public DBActionList execute(DBDatabase db) throws SQLException {
        DBRow table = getRow();
        DBActionList actions;
        DBDefinition defn = db.getDefinition();
        try (DBStatement statement = db.getDBStatement()) {
            actions = new DBActionList();
            for (var prop : getInterestingLargeObjects(table)) {
                final String col = prop.columnName();
                final DBLargeObject<?> largeObject = (DBLargeObject<?>) prop.getQueryableDatatype();

                if (largeObject.isNull()) {
                    setToNullUsingStringValue(defn, table, col, largeObject, db, statement);
                } else {
                    LargeObjectHandlerType handler = defn.preferredLargeObjectWriter(largeObject);
                    switch (handler) {
                        case BLOB:
                            setUsingBLOB(defn, table, col, largeObject, db, statement);
                            break;
                        case BASE64:
                            setUsingBase64String(defn, table, col, largeObject, db, statement);
                            break;
                        case BINARYSTREAM:
                            setUsingBinaryStream(defn, table, col, largeObject, db, statement);
                            break;
                        case CHARSTREAM:
                            setUsingCharacterStream(defn, table, col, largeObject, db, statement);
                            break;
                        case CLOB:
                            setUsingCLOB(defn, table, col, largeObject, db, statement);
                            break;
                        case STRING:
                            setUsingStringValue(defn, table, col, largeObject, db, statement);
                            break;
                        case JAVAOBJECT:
                            setUsingJavaObject(defn, table, col, largeObject, db, statement);
                            break;
                        case BYTE:
                            setUsingByteArray(defn, table, col, largeObject, db, statement);
                            break;
                    }
                }
                DBUpdateLargeObjects update = new DBUpdateLargeObjects(table);
                actions.add(update);
                largeObject.setUnchanged();
            }
        } catch (SQLException | IOException ex) {
            Logger.getLogger(DBUpdateLargeObjects.class.getName()).log(Level.SEVERE, null, ex);
            throw new DBRuntimeException("Can't Set LargeObject: IOError", ex);
        }
        return actions;
    }

    private void setUsingStringValue(DBDefinition defn, DBRow row, final String col, final DBLargeObject<?> largeObject, DBDatabase db, DBStatement statement) throws SQLException {

        String sqlString = defn.beginUpdateLine()
                + defn.formatTableName(row)
                + defn.beginSetClause()
                + defn.formatColumnName(col)
                + defn.getEqualsComparator()
                + " ? "
                + defn.beginWhereClause()
                + getPrimaryKeySQL(db, row)
                + defn.endSQLStatement();
        LOG.debug(sqlString);
        try (PreparedStatement prep = statement.getConnection().prepareStatement(sqlString)) {
            prep.setString(1, largeObject.stringValue());
            prep.execute();
        }

        statement.execute("UPDATING LARGE OBJECTS IN ROW", QueryIntention.UPDATE_ROW, sqlString);
    }

    private void setToNullUsingStringValue(DBDefinition defn, DBRow row, final String col, final DBLargeObject<?> largeObject, DBDatabase db, DBStatement statement) throws SQLException {
        String sqlString = defn.beginUpdateLine()
                + defn.formatTableName(row)
                + defn.beginSetClause()
                + defn.formatColumnName(col)
                + defn.getEqualsComparator()
                + defn.getNull()
                + defn.beginWhereClause()
                + getPrimaryKeySQL(db, row)
                + defn.endSQLStatement();
        LOG.debug(sqlString);
        statement.execute("Updating large object to null", QueryIntention.UPDATE_ROW, sqlString);
    }

    private void setUsingBinaryStream(DBDefinition defn, DBRow row, final String col, final DBLargeObject<?> largeObject, DBDatabase db, DBStatement statement) throws SQLException {
        String sqlString = defn.beginUpdateLine()
                + defn.formatTableName(row)
                + defn.beginSetClause()
                + defn.formatColumnName(col)
                + defn.getEqualsComparator()
                + defn.getPreparedVariableSymbol()
                + defn.beginWhereClause()
                + getPrimaryKeySQL(db, row)
                + defn.endSQLStatement();
        db.printSQLIfRequested(sqlString);
        LOG.debug(sqlString);
        try (PreparedStatement prep = statement.getConnection().prepareStatement(sqlString)) {
            try {
                prep.setBinaryStream(1, largeObject.getInputStream());
            } catch (SQLException exp) {
                try {
                    prep.setBinaryStream(1, largeObject.getInputStream(), largeObject.getSize());
                } catch (Exception exp2) {
                    throw new DBRuntimeException("Failed to set binary stream", exp);
                }
            }
            prep.execute();
        }
    }

    private void setUsingBLOB(DBDefinition defn, DBRow row, String col, DBLargeObject<?> largeObject, DBDatabase db, DBStatement statement) throws SQLException, IOException {
        String sqlString = defn.beginUpdateLine()
                + defn.formatTableName(row)
                + defn.beginSetClause()
                + defn.formatColumnName(col)
                + defn.getEqualsComparator()
                + defn.getPreparedVariableSymbol()
                + defn.beginWhereClause()
                + getPrimaryKeySQL(db, row)
                + defn.endSQLStatement();
        LOG.debug(sqlString);
        try (PreparedStatement prep = statement.getConnection().prepareStatement(sqlString)) {
            prep.setBlob(1, largeObject.getInputStream(), largeObject.getSize());
            prep.execute();
        }
    }

    private void setUsingCLOB(DBDefinition defn, DBRow row, String col, DBLargeObject<?> largeObject, DBDatabase db, DBStatement statement) throws SQLException, IOException {
        String sqlString = defn.beginUpdateLine()
                + defn.formatTableName(row)
                + defn.beginSetClause()
                + defn.formatColumnName(col)
                + defn.getEqualsComparator()
                + defn.getPreparedVariableSymbol()
                + defn.beginWhereClause()
                + getPrimaryKeySQL(db, row)
                + defn.endSQLStatement();
        LOG.debug(sqlString);
        try (PreparedStatement prep = statement.getConnection().prepareStatement(sqlString)) {
            prep.setClob(1, new InputStreamReader(largeObject.getInputStream(), Charset.forName("UTF-8")), largeObject.getSize());
            prep.execute();
        }
    }

    private void setUsingBase64String(DBDefinition defn, DBRow row, final String col, final DBLargeObject<?> largeObject, DBDatabase db, DBStatement statement) throws SQLException, IOException {
        String sqlString = defn.beginUpdateLine()
                + defn.formatTableName(row)
                + defn.beginSetClause()
                + defn.formatColumnName(col)
                + defn.getEqualsComparator()
                + defn.getPreparedVariableSymbol()
                + defn.beginWhereClause()
                + getPrimaryKeySQL(db, row)
                + defn.endSQLStatement();
        LOG.debug(sqlString);
        try (PreparedStatement prep = statement.getConnection().prepareStatement(sqlString)) {
            InputStream inputStream = largeObject.getInputStream();

            try (InputStream input = new BufferedInputStream(inputStream)) {
                List<byte[]> byteArrays = new ArrayList<>();

                int totalBytesRead = 0;
                byte[] resultSetBytes;
                resultSetBytes = new byte[100000];
                int bytesRead = input.read(resultSetBytes);
                while (bytesRead > 0) {
                    totalBytesRead += bytesRead;
                    byteArrays.add(resultSetBytes);
                    resultSetBytes = new byte[100000];
                    bytesRead = input.read(resultSetBytes);
                }
                byte[] bytes = new byte[totalBytesRead];
                int bytesAdded = 0;
                for (byte[] someBytes : byteArrays) {
                    System.arraycopy(someBytes, 0, bytes, bytesAdded, Math.min(someBytes.length, bytes.length - bytesAdded));
                    bytesAdded += someBytes.length;
                }
                String b64encoded = Base64.encodeBase64String(bytes);
                prep.setString(1, b64encoded);
                prep.execute();
            }
        }
    }

    private void setUsingCharacterStream(DBDefinition defn, DBRow row, final String col, final DBLargeObject<?> largeObject, DBDatabase db, DBStatement statement) throws SQLException, IOError, IOException {
        String sqlString = defn.beginUpdateLine()
                + defn.formatTableName(row)
                + defn.beginSetClause()
                + defn.formatColumnName(col)
                + defn.getEqualsComparator()
                + defn.getPreparedVariableSymbol()
                + defn.beginWhereClause()
                + getPrimaryKeySQL(db, row)
                + defn.endSQLStatement();
        db.printSQLIfRequested(sqlString);
        LOG.debug(sqlString);
        try (PreparedStatement prep = statement.getConnection().prepareStatement(sqlString)) {
            InputStream inputStream = largeObject.getInputStream();

            try (InputStreamReader input = new InputStreamReader(inputStream, "UTF-8")) {
                List<char[]> byteArrays = new ArrayList<>();

                int totalBytesRead = 0;
                char[] resultSetBytes;
                resultSetBytes = new char[100000];
                int bytesRead = input.read(resultSetBytes);
                while (bytesRead > 0) {
                    totalBytesRead += bytesRead;
                    byteArrays.add(resultSetBytes);
                    resultSetBytes = new char[100000];
                    bytesRead = input.read(resultSetBytes);
                }
                char[] bytes = new char[totalBytesRead];
                int bytesAdded = 0;
                for (char[] someBytes : byteArrays) {
                    System.arraycopy(someBytes, 0, bytes, bytesAdded, Math.min(someBytes.length, bytes.length - bytesAdded));
                    bytesAdded += someBytes.length;
                }
                prep.setCharacterStream(1, new BufferedReader(new CharArrayReader(bytes)), bytes.length);
                prep.execute();
            }
        }
    }

    @Override
    public List<String> getSQLStatements(DBDatabase db) {
        List<String> strs = new ArrayList<>();
        strs.add(db.getDefinition().startMultilineComment() + " SAVE BINARY DATA" + db.getDefinition().endMultilineComment());
        return strs;
    }

    /**
     * Finds all the DBLargeObject fields that this action will need to update.
     *
     * @param row the row to be updated
     * <p style="color: #F90;">Support DBvolution at
     * <a href="http://patreon.com/dbvolution" target=new>Patreon</a></p>
     * @return a list of the interesting DBLargeObjects.
     */
    protected List<PropertyWrapper<?, ?, ?>> getInterestingLargeObjects(DBRow row) {
        return getChangedLargeObjects(row);
    }

    @Override
    protected DBActionList getRevertDBActionList() {
        return new DBActionList();
    }

    private List<PropertyWrapper<?, ?, ?>> getChangedLargeObjects(DBRow row) {
        List<PropertyWrapper<?, ?, ?>> changed = new ArrayList<>();
        if (row.hasLargeObjects()) {
            for (QueryableDatatype<?> qdt : row.getLargeObjects()) {
                if (qdt instanceof DBLargeObject) {
                    DBLargeObject<?> large = (DBLargeObject<?>) qdt;
                    if (large.hasChanged()) {
                        changed.add(row.getPropertyWrapperOf(qdt));
                    }
                }
            }
        }
        return changed;
    }

    private void setUsingJavaObject(DBDefinition defn, DBRow row, String col, DBLargeObject<?> largeObject, DBDatabase db, DBStatement statement) throws SQLException {
        String sqlString = defn.beginUpdateLine()
                + defn.formatTableName(row)
                + defn.beginSetClause()
                + defn.formatColumnName(col)
                + defn.getEqualsComparator()
                + defn.getPreparedVariableSymbol()
                + defn.beginWhereClause()
                + getPrimaryKeySQL(db, row)
                + defn.endSQLStatement();
        LOG.debug(sqlString);
        try (PreparedStatement prep = statement.getConnection().prepareStatement(sqlString)) {
            prep.setObject(1, largeObject.getValue());
            prep.execute();
        }
    }

    private void setUsingByteArray(DBDefinition defn, DBRow row, String col, DBLargeObject<?> largeObject, DBDatabase db, DBStatement statement) throws SQLException, IOException {
        String sqlString = defn.beginUpdateLine()
                + defn.formatTableName(row)
                + defn.beginSetClause()
                + defn.formatColumnName(col)
                + defn.getEqualsComparator()
                + defn.getPreparedVariableSymbol()
                + defn.beginWhereClause()
                + getPrimaryKeySQL(db, row)
                + defn.endSQLStatement();
        LOG.debug(sqlString);
        try (PreparedStatement prep = statement.getConnection().prepareStatement(sqlString)) {
            InputStream inputStream = largeObject.getInputStream();

            try (InputStream input = new BufferedInputStream(inputStream)) {
                List<byte[]> byteArrays = new ArrayList<>();

                int totalBytesRead = 0;
                byte[] resultSetBytes;
                resultSetBytes = new byte[100000];
                int bytesRead = input.read(resultSetBytes);
                while (bytesRead > 0) {
                    totalBytesRead += bytesRead;
                    byteArrays.add(resultSetBytes);
                    resultSetBytes = new byte[100000];
                    bytesRead = input.read(resultSetBytes);
                }
                byte[] bytes = new byte[totalBytesRead];
                int bytesAdded = 0;
                for (byte[] someBytes : byteArrays) {
                    System.arraycopy(someBytes, 0, bytes, bytesAdded, Math.min(someBytes.length, bytes.length - bytesAdded));
                    bytesAdded += someBytes.length;
                }
                prep.setBytes(1, bytes);
                prep.execute();
            }
        }
    }
}