/*
 *  Copyright (c) 2005-2010, WSO2 Inc. (http://www.wso2.org) All Rights Reserved.
 *
 *  WSO2 Inc. licenses this file to you 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 org.wso2.carbon.dataservices.sql.driver;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.wso2.carbon.dataservices.sql.driver.parser.Constants;

import java.io.*;
import java.sql.*;
import java.util.Properties;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class TExcelConnection extends TConnection {
    private static final Log log = LogFactory.getLog(TExcelConnection.class);

    private Workbook workbook;

    /**
     * Lock used to lock the excel book while editing or modifying.
     */
    private static final Lock lock = new ReentrantLock();

    //variable used for debug purposes
    private static int lockCount = 0;

    private String filePath;

    public TExcelConnection(Properties props) throws SQLException {
        super(props);
        filePath = (String) props.get(Constants.DRIVER_PROPERTIES.FILE_PATH);
        this.workbook = this.createConnectionToExcelDocument(filePath);
    }

    /**
     * Creates a connection to the given Excel document and returns a workbook instance
     *
     * @param filePath Path to the Excel file
     * @return Instance of workbook class containing which represents a database in the
     *         world of SQL
     * @throws java.sql.SQLException SQLException
     */
    private Workbook createConnectionToExcelDocument(String filePath) throws SQLException {
        Workbook workbook;
        try {
            acquireLock();
            InputStream fin = TDriverUtil.getInputStreamFromPath(filePath);
            workbook = WorkbookFactory.create(fin);
        } catch (FileNotFoundException e) {
            throw new SQLException("Could not locate the EXCEL datasource in the provided " +
                                   "location", e);
        } catch (IOException | InvalidFormatException e) {
            throw new SQLException("Error occurred while initializing the EXCEL datasource", e);
        } catch (InterruptedException e) {
            throw new SQLException("Error Acquiring the lock for the workbook path - " + filePath, e);
        }
        return workbook;
    }

    /**
     * Helper method to acquire a lock for the transaction purpose.
     *
     * @throws InterruptedException
     * @throws SQLException
     */
    private synchronized void acquireLock() throws InterruptedException, SQLException {
        if (lock.tryLock(20, TimeUnit.SECONDS)) {
            if (log.isDebugEnabled()) {
                lockCount++;
                log.debug("Acquired the lock for the excel file to make it transactional, current lock count - " + lockCount);
            }
        } else {
            throw new SQLException("Error acquiring lock for the excel file even after 20 second wait, filePath - " + this.filePath);
        }
    }

    /**
     * Helper method to release the acquired lock at the end of the transaction.
     */
    private synchronized void releaseLock() {
        try {
            lock.unlock();
            if (log.isDebugEnabled()) {
                lockCount--;
                log.debug("Released the lock for excel file after the transaction, current lock count - " + lockCount);
            }
        } catch (IllegalMonitorStateException e) {
            if (log.isDebugEnabled()) {
                log.debug("Failed to release the lock as it is already released, lock count - " + lockCount, e);
            }
        } catch (Exception e) {
            if (log.isDebugEnabled()) {
                log.debug("Failed to release the lock as it is already released, lock count - " + lockCount, e);
            }
        }
    }

    public Workbook getWorkbook() {
        return workbook;
    }

    public Statement createStatement(String sql) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public Statement createStatement() throws SQLException {
        return new TPreparedStatement();
    }

    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        throw new SQLFeatureNotSupportedException("CallableStatements are not supported");
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType,
                                              int resultSetConcurrency) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType,
                                         int resultSetConcurrency) throws SQLException {
        throw new SQLFeatureNotSupportedException("CallableStatements are not supported");
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType,
                                              int resultSetConcurrency,
                                              int resultSetHoldability) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
                                         int resultSetHoldability) throws SQLException {
        throw new SQLFeatureNotSupportedException("CallableStatements are not supported");
    }

    @Override
    public PreparedStatement prepareStatement(String sql,
                                              int autoGeneratedKeys) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public PreparedStatement prepareStatement(String sql,
                                              int[] columnIndexes) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public PreparedStatement prepareStatement(String sql,
                                              String[] columnNames) throws SQLException {
        return null;
    }

    /**
     * Begin transaction method for Excel connections, This will reread the workbook and acquire the lock as well.
     *
     * @throws SQLException
     */
    public void beginExcelTransaction() throws SQLException {
        this.workbook = this.createConnectionToExcelDocument(filePath);
    }

    public void commit() throws SQLException {
        releaseLock();
    }

    public void rollback() throws SQLException {
        releaseLock();
    }

    public void close() throws SQLException {
        releaseLock();
    }
}

@@ -1406,16 +1406,166 @@
 kbook;%0A%0A
+    /**%0A     * The time in seconds which the lock will try waiting to acquire the lock for the file%0A     */%0A    private final int LOCK_TIMEOUT = 20;%0A%0A
     /**%0A
@@ -3466,18 +3466,28 @@
 tryLock(
-20
+LOCK_TIMEOUT
 , TimeUn

move the timeout value to a variable