/**
* License Agreement for OpenSearchServer
*
* Copyright (C) 2010-2015 Emmanuel Keller / Jaeksoft
*
* http://www.open-search-server.com
*
* This file is part of OpenSearchServer.
*
* OpenSearchServer is free software: you can redistribute it and/or
* modify it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* OpenSearchServer 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 General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with OpenSearchServer.
* If not, see <http://www.gnu.org/licenses/>.
**/
package com.jaeksoft.searchlib.crawler.database;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.xml.xpath.XPathExpressionException;
import org.apache.commons.lang3.StringUtils;
import org.w3c.dom.Node;
import org.xml.sax.SAXException;
import com.jaeksoft.pojodbc.Query;
import com.jaeksoft.pojodbc.Transaction;
import com.jaeksoft.pojodbc.connection.JDBCConnection;
import com.jaeksoft.searchlib.util.DomUtils;
import com.jaeksoft.searchlib.util.IOUtils;
import com.jaeksoft.searchlib.util.Variables;
import com.jaeksoft.searchlib.util.XPathParser;
import com.jaeksoft.searchlib.util.XmlWriter;
public class DatabaseCrawlSql extends DatabaseCrawlAbstract {
public static enum SqlUpdateMode {
NO_CALL, ONE_CALL_PER_PRIMARY_KEY, PRIMARY_KEY_LIST, PRIMARY_KEY_CHAR_LIST;
public static SqlUpdateMode find(String label) {
for (SqlUpdateMode v : values())
if (v.name().equals(label))
return v;
return NO_CALL;
}
public static String[] getNameList() {
String[] list = new String[values().length];
int i = 0;
for (SqlUpdateMode item : values())
list[i++] = item.name();
return list;
}
}
protected final static String DBCRAWL_ATTR_FETCH_SIZE = "fetchSize";
private String driverClass;
private IsolationLevelEnum isolationLevel;
private String sqlSelect;
private String sqlUpdate;
private SqlUpdateMode sqlUpdateMode;
private String primaryKey;
private String uniqueKeyDeleteField;
private int fetchSize;
public DatabaseCrawlSql(DatabaseCrawlMaster crawlMaster, DatabasePropertyManager propertyManager, String name) {
super(crawlMaster, propertyManager, name);
driverClass = null;
isolationLevel = IsolationLevelEnum.TRANSACTION_NONE;
sqlSelect = null;
sqlUpdate = null;
sqlUpdateMode = SqlUpdateMode.NO_CALL;
primaryKey = null;
uniqueKeyDeleteField = null;
fetchSize = 50;
}
public void applyVariables(Variables variables) {
if (variables == null)
return;
sqlSelect = variables.replace(sqlSelect);
sqlUpdate = variables.replace(sqlUpdate);
}
public DatabaseCrawlSql(DatabaseCrawlMaster crawlMaster, DatabasePropertyManager propertyManager) {
this(crawlMaster, propertyManager, null);
}
protected DatabaseCrawlSql(DatabaseCrawlSql crawl) {
super((DatabaseCrawlMaster) crawl.threadMaster, crawl.propertyManager);
crawl.copyTo(this);
}
@Override
public DatabaseCrawlAbstract duplicate() {
return new DatabaseCrawlSql(this);
}
@Override
public void copyTo(DatabaseCrawlAbstract crawlAbstract) {
super.copyTo(crawlAbstract);
DatabaseCrawlSql crawl = (DatabaseCrawlSql) crawlAbstract;
crawl.driverClass = this.driverClass;
crawl.isolationLevel = this.isolationLevel;
crawl.sqlSelect = this.sqlSelect;
crawl.sqlUpdate = this.sqlUpdate;
crawl.sqlUpdateMode = this.sqlUpdateMode;
crawl.primaryKey = this.primaryKey;
crawl.uniqueKeyDeleteField = this.uniqueKeyDeleteField;
crawl.fetchSize = this.fetchSize;
}
@Override
public DatabaseCrawlEnum getType() {
return DatabaseCrawlEnum.DB_SQL;
}
/**
* @return the driverClass
*/
public String getDriverClass() {
return driverClass;
}
/**
* @param driverClass
* the driverClass to set
*/
public void setDriverClass(String driverClass) {
this.driverClass = driverClass;
}
/**
* @param sql
* the sqlSelect to set
*/
public void setSqlSelect(String sql) {
this.sqlSelect = sql;
}
/**
* @return the sqlUpdate
*/
public String getSqlSelect() {
return sqlSelect;
}
/**
* @param sql
* the sqlSelect to set
*/
public void setSqlUpdate(String sql) {
this.sqlUpdate = sql;
}
/**
* @return the sqlUpdate
*/
public String getSqlUpdate() {
return sqlUpdate;
}
/**
* @return the sqlUpdateMode
*/
public SqlUpdateMode getSqlUpdateMode() {
return sqlUpdateMode;
}
/**
* @param sqlUpdateMode
* the sqlUpdateMode to set
*/
public void setSqlUpdateMode(SqlUpdateMode sqlUpdateMode) {
this.sqlUpdateMode = sqlUpdateMode;
}
/**
* @param primaryKey
* the primaryKey to set
*/
public void setPrimaryKey(String primaryKey) {
this.primaryKey = primaryKey;
}
/**
* @return the primaryKey
*/
public String getPrimaryKey() {
return primaryKey;
}
protected final static String DBCRAWL_ATTR_DRIVER_CLASS = "driverClass";
protected final static String DBCRAWL_ATTR_ISOLATION_LEVEL = "isolationLevel";
protected final static String DBCRAWL_ATTR_PRIMARY_KEY = "primaryKey";
protected final static String DBCRAWL_ATTR_UNIQUE_KEY_DELETE_FIELD = "uniqueKeyDeleteField";
protected final static String DBCRAWL_NODE_NAME_SQL_SELECT = "sql";
protected final static String DBCRAWL_NODE_NAME_SQL_UPDATE = "sqlUpdate";
protected final static String DBCRAWL_ATTR__NAME_SQL_UPDATE_MODE = "mode";
public DatabaseCrawlSql(DatabaseCrawlMaster crawlMaster, DatabasePropertyManager propertyManager, XPathParser xpp,
Node item) throws XPathExpressionException {
super(crawlMaster, propertyManager, xpp, item);
setDriverClass(XPathParser.getAttributeString(item, DBCRAWL_ATTR_DRIVER_CLASS));
setIsolationLevel(IsolationLevelEnum.find(XPathParser.getAttributeString(item, DBCRAWL_ATTR_ISOLATION_LEVEL)));
setPrimaryKey(XPathParser.getAttributeString(item, DBCRAWL_ATTR_PRIMARY_KEY));
setUniqueKeyDeleteField(XPathParser.getAttributeString(item, DBCRAWL_ATTR_UNIQUE_KEY_DELETE_FIELD));
setBufferSize(XPathParser.getAttributeValue(item, DBCRAWL_ATTR_BUFFER_SIZE));
setFetchSize(DomUtils.getAttributeInteger(item, DBCRAWL_ATTR_FETCH_SIZE, 50));
Node sqlNode = xpp.getNode(item, DBCRAWL_NODE_NAME_SQL_SELECT);
if (sqlNode != null)
setSqlSelect(xpp.getNodeString(sqlNode, true));
sqlNode = xpp.getNode(item, DBCRAWL_NODE_NAME_SQL_UPDATE);
if (sqlNode != null) {
setSqlUpdate(xpp.getNodeString(sqlNode, true));
setSqlUpdateMode(
SqlUpdateMode.find(DomUtils.getAttributeText(sqlNode, DBCRAWL_ATTR__NAME_SQL_UPDATE_MODE)));
}
}
@Override
public void writeXml(XmlWriter xmlWriter) throws SAXException {
xmlWriter.startElement(DBCRAWL_NODE_NAME, DBCRAWL_ATTR_NAME, getName(), DBCRAWL_ATTR_TYPE, getType().name(),
DBCRAWL_ATTR_DRIVER_CLASS, getDriverClass(), DBCRAWL_ATTR_ISOLATION_LEVEL,
isolationLevel != null ? isolationLevel.name() : null, DBCRAWL_ATTR_USER, getUser(),
DBCRAWL_ATTR_PASSWORD, getPassword(), DBCRAWL_ATTR_URL, getUrl(), DBCRAWL_ATTR_LANG,
getLang().getCode(), DBCRAWL_ATTR_PRIMARY_KEY, primaryKey, DBCRAWL_ATTR_UNIQUE_KEY_DELETE_FIELD,
uniqueKeyDeleteField, DBCRAWL_ATTR_BUFFER_SIZE, Integer.toString(getBufferSize()),
DBCRAWL_ATTR_FETCH_SIZE, Integer.toString(getFetchSize()), DBCRAWL_ATTR_MSSLEEP,
Integer.toString(getMsSleep()));
xmlWriter.startElement(DBCRAWL_NODE_NAME_MAP);
getFieldMap().store(xmlWriter);
xmlWriter.endElement();
// SQL Select Node
xmlWriter.startElement(DBCRAWL_NODE_NAME_SQL_SELECT);
xmlWriter.textNode(getSqlSelect());
xmlWriter.endElement();
// SQL Update Node
if (!StringUtils.isEmpty(sqlUpdate)) {
xmlWriter.startElement(DBCRAWL_NODE_NAME_SQL_UPDATE, DBCRAWL_ATTR__NAME_SQL_UPDATE_MODE,
sqlUpdateMode == null ? null : sqlUpdateMode.name());
xmlWriter.textNode(getSqlUpdate());
xmlWriter.endElement();
}
xmlWriter.endElement();
}
/**
* @return the isolationLevel
*/
public IsolationLevelEnum getIsolationLevel() {
return isolationLevel;
}
/**
* @param isolationLevel
* the isolationLevel to set
*/
public void setIsolationLevel(IsolationLevelEnum isolationLevel) {
this.isolationLevel = isolationLevel;
}
/**
* @return the uniqueKeyDeleteField
*/
public String getUniqueKeyDeleteField() {
return uniqueKeyDeleteField;
}
/**
* @param uniqueKeyDeleteField
* the uniqueKeyDeleteField to set
*/
public void setUniqueKeyDeleteField(String uniqueKeyDeleteField) {
this.uniqueKeyDeleteField = uniqueKeyDeleteField;
}
/**
* @return the fetchSize
*/
public int getFetchSize() {
return fetchSize;
}
/**
* @param fetchSize
* the fetchSize to set
*/
public void setFetchSize(int fetchSize) {
this.fetchSize = fetchSize;
}
public JDBCConnection getNewJdbcConnection()
throws InstantiationException, IllegalAccessException, ClassNotFoundException {
JDBCConnection jdbcCnx = new JDBCConnection();
jdbcCnx.setDriver(driverClass);
jdbcCnx.setUrl(getUrl());
String user = getFinalUser();
if (!StringUtils.isEmpty(user))
jdbcCnx.setUsername(user);
String password = getFinalPassword();
if (!StringUtils.isEmpty(password))
jdbcCnx.setPassword(password);
return jdbcCnx;
}
public Transaction getNewTransaction(JDBCConnection jdbcCnx) throws SQLException {
return jdbcCnx.getNewTransaction(false, isolationLevel.value);
}
@Override
public String test() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
JDBCConnection jdbcCnx = getNewJdbcConnection();
Transaction transaction = null;
StringWriter sw = null;
PrintWriter pw = null;
try {
sw = new StringWriter();
pw = new PrintWriter(sw);
transaction = getNewTransaction(jdbcCnx);
Query query = transaction.prepare(sqlSelect, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
query.getStatement().setFetchSize(getFetchSize());
ResultSet resultSet = query.getResultSet();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
pw.print("Found ");
pw.print(columnCount);
pw.println(" column(s)");
for (int i = 1; i <= columnCount; i++) {
pw.print(i);
pw.print(": ");
pw.println(metaData.getColumnLabel(i));
}
return sw.toString();
} finally {
IOUtils.close(pw, sw);
if (transaction != null)
transaction.close();
}
}
}