D-Net

package eu.dnetlib.data.mdstore.modular.postgres;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import javax.sql.DataSource;

import org.antlr.stringtemplate.StringTemplate;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang.StringUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Required;

import org.springframework.core.io.Resource;

import eu.dnetlib.data.mdstore.MDStoreServiceException;

import eu.dnetlib.data.mdstore.modular.RecordParser;

import eu.dnetlib.data.mdstore.modular.connector.MDStore;

import eu.dnetlib.data.mdstore.modular.connector.MDStoreDao;

/**

 * The Class MDStoreDaoPostgresImpl.

 */

public class MDStoreDaoPostgresImpl implements MDStoreDao {

	/** The datasource. */

	@javax.annotation.Resource(name = "mdstoreDataSource")

	private DataSource datasource;

	/** The metadata table. */

	private static String METADATA_TABLE = "metadata";

	/** The MDID column name. */

	private static String MDIDColumnName = "mdid";

	/** The template metadata. */

	private Resource templateMetadata;

	/** The template collection. */

	private Resource templateCollection;

	/** The record parser. */

	@Autowired

	private RecordParser recordParser;

	@Autowired

	private PostgresTransactionManagerImpl transactionManager;

	/** The Constant log. */

	private static final Log log = LogFactory.getLog(MDStoreDaoPostgresImpl.class);

	/**

	 * Verify if Exists a particular table.

	 *

	 * @param tableName

	 *            the table name

	 * @param conn

	 *            the conn

	 * @return true, if successful

	 * @throws SQLException

	 *             the SQL exception

	 */

	private boolean existsTable(final String tableName, final Connection conn) throws SQLException {

		DatabaseMetaData md = conn.getMetaData();

		ResultSet rs = md.getTables(null, null, tableName, null);

		while (rs.next()) {

			String tName = rs.getString(3);

			if (tableName.equals(tName)) return true;

		}

		return false;

	}

	/*

	 * (non-Javadoc)

	 * 

	 * @see eu.dnetlib.data.mdstore.modular.connector.MDStoreDao#getMDStore(java.lang.String)

	 */

	@Override

	public MDStore getMDStore(final String mdId) throws MDStoreServiceException {

		Connection conn = null;

		try {

			conn = datasource.getConnection();

			verifyConsistency(conn);

			String mdProfileID = transactionManager.getMDStoreForTransaction(mdId);

			String sql = String.format("select * from %s where %s = ? or current_table= ?", METADATA_TABLE, MDIDColumnName);

			if (mdProfileID != null) {

				sql = String.format("select * from %s where %s = ?", METADATA_TABLE, MDIDColumnName);

			}

			PreparedStatement ps = conn.prepareStatement(sql);

			if (mdProfileID != null) {

				ps.setString(1, mdProfileID);

			} else {

				ps.setString(1, mdId);

				ps.setString(2, mdId);

			}

			ResultSet rs = ps.executeQuery();

			if (!rs.next()) throw new MDStoreServiceException("Collection not found for MDStoreID :" + (mdProfileID != null ? mdProfileID : mdId));

			String tableName = mdProfileID != null ? mdId : rs.getString("current_table");

			String format = rs.getString("format");

			String layout = rs.getString("layout");

			String interpretation = rs.getString("interpretation");

			int size = rs.getInt("size");

			if (!existsTable(tableName, conn))

				throw new MDStoreServiceException("Error the current MDstore of: " + mdId + " with id " + tableName + " not exist!");

			return new PostgresMDStore(tableName, format, layout, interpretation, this.datasource, true, recordParser, size, mdProfileID != null ? mdProfileID

					: mdId);

		} catch (Exception e) {

			log.error("Error on get mdstore", e);

			throw new MDStoreServiceException(e);

		} finally {

			if (conn != null) {

				try {

					conn.close();

				} catch (SQLException e) {

					log.error("Error on close connection", e);

					throw new MDStoreServiceException(e);

				}

			}

		}

	}

	/**

	 * Initialize tables.

	 *

	 * @param connection

	 *            the connection

	 * @throws IOException

	 *             Signals that an I/O exception has occurred.

	 * @throws SQLException

	 *             the SQL exception

	 */

	private void initializeTables(final Connection connection) throws IOException, SQLException {

		InputStream is = getTemplateMetadata().getInputStream();

		String createTableQuery = IOUtils.toString(is);

		PreparedStatement ps = connection.prepareStatement(createTableQuery);

		ps.executeUpdate();

	}

	/**

	 * Verify consistency.

	 *

	 * @param conn

	 *            the conn

	 * @throws SQLException

	 *             the SQL exception

	 * @throws IOException

	 *             Signals that an I/O exception has occurred.

	 */

	private void verifyConsistency(final Connection conn) throws SQLException, IOException {

		if (existsTable(METADATA_TABLE, conn)) return;

		initializeTables(conn);

	}

	/*

	 * (non-Javadoc)

	 * 

	 * @see eu.dnetlib.data.mdstore.modular.connector.MDStoreDao#listMDStores()

	 */

	@Override

	public List<String> listMDStores() throws MDStoreServiceException {

		Connection conn = null;

		try {

			conn = datasource.getConnection();

			verifyConsistency(conn);

			String query = "select  " + MDIDColumnName + "  from metadata";

			PreparedStatement st = conn.prepareStatement(query);

			ResultSet rs = st.executeQuery();

			List<String> ids = new ArrayList<String>();

			while (rs.next()) {

				ids.add(rs.getString(MDIDColumnName));

			}

			return ids;

		} catch (Exception e) {

			log.error("Error on listing mdstore", e);

			throw new MDStoreServiceException(e);

		} finally {

			if (conn != null) {

				try {

					conn.close();

				} catch (SQLException e) {

					log.error("Error on close connection", e);

					throw new MDStoreServiceException(e);

				}

			}

		}

	}

	/*

	 * (non-Javadoc)

	 * 

	 * @see eu.dnetlib.data.mdstore.modular.connector.MDStoreDao#listMDStores(java.lang.String, java.lang.String, java.lang.String)

	 */

	@Override

	public List<String> listMDStores(final String format, final String layout, final String interpretation) throws MDStoreServiceException {

		Connection conn = null;

		try {

			conn = datasource.getConnection();

			verifyConsistency(conn);

			String query = "select  " + MDIDColumnName + "  from metadata where lower(format) = ? and lower(layout) = ? and lower(interpretation) = ?";

			PreparedStatement st = conn.prepareStatement(query);

			st.setString(1, format.toLowerCase());

			st.setString(2, layout.toLowerCase());

			st.setString(3, interpretation.toLowerCase());

			ResultSet rs = st.executeQuery();

			List<String> ids = new ArrayList<String>();

			while (rs.next()) {

				ids.add(rs.getString(MDIDColumnName));

			}

			return ids;

		} catch (Exception e) {

			log.error("Error on listing mdstore", e);

			throw new MDStoreServiceException(e);

		} finally {

			if (conn != null) {

				try {

					conn.close();

				} catch (SQLException e) {

					log.error("Error on close connection", e);

					throw new MDStoreServiceException(e);

				}

			}

		}

	}

	/*

	 * (non-Javadoc)

	 * 

	 * @see eu.dnetlib.data.mdstore.modular.connector.MDStoreDao#createMDStore(java.lang.String, java.lang.String, java.lang.String,

	 * java.lang.String)

	 */

	@Override

	public void createMDStore(final String mdId, final String format, final String interpretation, final String layout) throws MDStoreServiceException {

		Connection conn = null;

		try {

			conn = datasource.getConnection();

			verifyConsistency(conn);

			String query = "insert into  " + METADATA_TABLE + "  values (?,?,?,?,?,?,?)";

			PreparedStatement st = conn.prepareStatement(query);

			st.setString(1, mdId);

			st.setString(2, format);

			st.setString(3, layout);

			st.setString(4, interpretation);

			String idName = mdId;

			if (mdId.contains("_")) {

				idName = StringUtils.substringBefore(mdId, "_");

			}

			st.setString(5, "mdstore_" + idName.replace("-", "_"));

			st.setInt(6, 0);

			st.setInt(7, 0);

			log.debug(st.toString());

			st.executeUpdate();

			final StringTemplate templateCollection = new StringTemplate(IOUtils.toString(getTemplateCollection().getInputStream()));

			templateCollection.setAttribute("name", "mdstore_" + idName.replace("-", "_"));

			st = conn.prepareStatement(templateCollection.toString());

			st.executeUpdate();

		} catch (Exception e) {

			log.error("Error on inserting mdstore", e);

			throw new MDStoreServiceException(e);

		} finally {

			if (conn != null) {

				try {

					conn.close();

				} catch (SQLException e) {

					log.error("Error on close connection", e);

					throw new MDStoreServiceException(e);

				}

			}

		}

	}

	/*

	 * (non-Javadoc)

	 * 

	 * @see eu.dnetlib.data.mdstore.modular.connector.MDStoreDao#deleteMDStore(java.lang.String)

	 */

	@Override

	public void deleteMDStore(final String mdId) throws MDStoreServiceException {

		Connection conn = null;

		try {

			conn = datasource.getConnection();

			verifyConsistency(conn);

			// RETREIVE THE CURRENT USED TABLE ON METADATA

			String sql = String.format("select * from %s where %s = ?", METADATA_TABLE, MDIDColumnName);

			PreparedStatement ps = conn.prepareStatement(sql);

			ps.setString(1, mdId);

			ResultSet rs = ps.executeQuery();

			if (rs.next() == false) throw new MDStoreServiceException("Error on deleting mdstore: " + mdId + " not found in metadata");

			String tableName = rs.getString("current_table");

			// DELETE THE RELATIVE FIELDS TABLE

			sql = String.format("drop table %s_fields", tableName);

			ps = conn.prepareStatement(sql);

			ps.executeUpdate();

			// DELETE THE RELATIVE TABLE

			sql = String.format("drop table %s", tableName);

			ps = conn.prepareStatement(sql);

			ps.executeUpdate();

			// DELETE THE DISCARDED TABLE

			sql = String.format("drop table %s_discarded", tableName);

			ps = conn.prepareStatement(sql);

			ps.executeUpdate();

			sql = String.format("delete from %s where lower(%s) = ? ", METADATA_TABLE, MDIDColumnName);

			ps = conn.prepareStatement(sql);

			ps.setString(1, mdId.toLowerCase());

			ps.executeUpdate();

		} catch (Exception e) {

			log.error("Error on deleting mdstore", e);

			throw new MDStoreServiceException(e);

		} finally {

			if (conn != null) {

				try {

					conn.close();

				} catch (SQLException e) {

					log.error("Error on close connection", e);

					throw new MDStoreServiceException(e);

				}

			}

		}

	}

	/**

	 * Gets the template metadata.

	 *

	 * @return the template metadata

	 */

	public Resource getTemplateMetadata() {

		return templateMetadata;

	}

	/**

	 * Sets the template metadata.

	 *

	 * @param templateMetadata

	 *            the new template metadata

	 */

	@Required

	public void setTemplateMetadata(final Resource templateMetadata) {

		this.templateMetadata = templateMetadata;

	}

	/**

	 * Gets the template collection.

	 *

	 * @return the template collection

	 */

	public Resource getTemplateCollection() {

		return templateCollection;

	}

	/**

	 * Sets the template collection.

	 *

	 * @param templateCollection

	 *            the new template collection

	 */

	@Required

	public void setTemplateCollection(final Resource templateCollection) {

		this.templateCollection = templateCollection;

	}

}