import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
+import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
+import java.util.LinkedList;
+import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.zip.GZIPInputStream;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
-import fr.gouv.finances.dgfip.utils.Pair;
+import fr.gouv.finances.dgfip.utils.QueryProvider;
import fr.gouv.finances.dgfip.xemelios.auth.UnauthorizedException;
import fr.gouv.finances.dgfip.xemelios.auth.XemeliosUser;
-import fr.gouv.finances.dgfip.xemelios.common.PJRef;
import fr.gouv.finances.dgfip.xemelios.common.config.DocumentModel;
import fr.gouv.finances.dgfip.xemelios.common.config.ElementModel;
import fr.gouv.finances.dgfip.xemelios.common.config.EtatModel;
import fr.gouv.finances.dgfip.xemelios.common.config.Loader;
-import fr.gouv.finances.dgfip.xemelios.common.config.PJRefInfo;
import fr.gouv.finances.dgfip.xemelios.common.config.SpecialKeyModel;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.PersistenceConfigParser;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TDocument;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TPersistenceConfig;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TSqlIndex;
import fr.gouv.finances.dgfip.xemelios.data.impl.sqlconfig.TTable;
+import fr.gouv.finances.dgfip.xemelios.importers.ImportJob;
import fr.gouv.finances.dgfip.xemelios.utils.GZipUtils;
/**
private final Object locker = new Object();
private boolean useCachedPersistence = true;
static protected Map<String, Class> persistenceModels = new HashMap<String, Class>();
+ protected QueryProvider queryProvider;
static {
AbstractDataImpl.persistenceModels.put("xemelios-sql", TPersistenceConfig.class);
protected File documentConfigDirectory = null;
protected ArchiveLocator archiveLocator;
+ public QueryProvider getQueryProvider() {
+ return this.queryProvider;
+ }
+
public boolean importElement(final DocumentModel dm, final EtatModel currentEtat, final String sourceFileName, final String elementName, final Document element, final int overwriteMode) throws DataAccessException {
return false;
}
protected abstract void initConnections() throws Exception;
+ protected abstract URL getSpecificQueryFile();
+
@Override
public void initialize() throws Exception {
this.initConnections();
+ this.queryProvider = new QueryProvider();
+ final URL defaultQueryFile = AbstractDataImpl.class.getResource("default-queries.properties");
+ this.queryProvider.load(defaultQueryFile);
+
+ final URL specificQueryFile = this.getSpecificQueryFile();
+ if (specificQueryFile != null) {
+ this.queryProvider.load(specificQueryFile);
+ }
+
if (this.archiveLocator != null) {
this.archiveLocator.initialize();
}
+
}
public ArchiveLocator getArchiveLocator() {
}
@Override
- public ArrayList<String> getDocumentListFromXPath(final ElementModel em, final String collectivite, final String budget, final String sp1, final String sp2, final String sp3, final String xpath, final XemeliosUser user)
- throws DataConfigurationException, DataAccessException, UnauthorizedException, InvalidParameterException {
+ public ArrayList<String> getDocumentListFromXPath(final ElementModel em, final String collectivite, final String budget, final String sp1, final String sp2, final String sp3, final String xpath, final XemeliosUser user) throws DataConfigurationException, DataAccessException,
+ UnauthorizedException, InvalidParameterException {
if (!user.hasRole(XemeliosUser.ROLE_SEARCH)) {
throw new UnauthorizedException("Vous n'êtes pas autorisé à faire des recherches");
}
if (!user.hasCollectivite(collectivite, em.getParent().getParent())) {
throw new UnauthorizedException("Vous n'êtes pas autorisé à rechercher des données pour cette collectivité");
}
-
+
final ArrayList<String> ret = new ArrayList<String>();
final StringBuffer selectClause = new StringBuffer();
final StringBuffer fromClause = new StringBuffer();
final String tName = sec.getIndexTable(si.getTableId()).getBaseName();
whereClause.append("AND ").append(tName).append(".").append(si.getColumn()).append("=? ");
if (!addedTableNames.contains(si.getTableId())) {
- fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName())
- .append("." + pc.getCollectiviteCodeColname() + "=").append(tName).append("." + pc.getCollectiviteCodeColname() + " AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getBudgetCodeColname() + "=")
- .append(tName).append("." + pc.getBudgetCodeColname() + "");
+ fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getCollectiviteCodeColname() + "=").append(tName)
+ .append("." + pc.getCollectiviteCodeColname() + " AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getBudgetCodeColname() + "=").append(tName).append("." + pc.getBudgetCodeColname() + "");
addedTableNames.add(si.getTableId());
}
} else {
final String tName = sec.getIndexTable(si.getTableId()).getBaseName();
whereClause.append("AND ").append(tName).append(".").append(si.getColumn()).append("=? ");
if (!addedTableNames.contains(si.getTableId())) {
- fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName())
- .append("." + pc.getCollectiviteCodeColname() + "=").append(tName).append("." + pc.getCollectiviteCodeColname() + " AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getBudgetCodeColname() + "=")
- .append(tName).append("." + pc.getBudgetCodeColname() + "");
+ fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getCollectiviteCodeColname() + "=").append(tName)
+ .append("." + pc.getCollectiviteCodeColname() + " AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getBudgetCodeColname() + "=").append(tName).append("." + pc.getBudgetCodeColname() + "");
addedTableNames.add(si.getTableId());
}
} else {
final String tName = sec.getIndexTable(si.getTableId()).getBaseName();
whereClause.append("AND ").append(tName).append(".").append(si.getColumn()).append("=? ");
if (!addedTableNames.contains(si.getTableId())) {
- fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName())
- .append("." + pc.getCollectiviteCodeColname() + "=").append(tName).append("." + pc.getCollectiviteCodeColname() + " AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getBudgetCodeColname() + "=")
- .append(tName).append("." + pc.getBudgetCodeColname() + "");
+ fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getCollectiviteCodeColname() + "=").append(tName)
+ .append("." + pc.getCollectiviteCodeColname() + " AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getBudgetCodeColname() + "=").append(tName).append("." + pc.getBudgetCodeColname() + "");
addedTableNames.add(si.getTableId());
}
} else {
whereClause.append("AND ").append(tName).append(".").append(si.getColumn()).append(op).append(value).append(" ");
if (!addedTableNames.contains(si.getTableId())) {
- fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName())
- .append("." + pc.getCollectiviteCodeColname() + "=").append(tName).append("." + pc.getCollectiviteCodeColname() + " AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getBudgetCodeColname() + "=")
- .append(tName).append("." + pc.getBudgetCodeColname() + "");
+ fromClause.append(" LEFT OUTER JOIN ").append(tName).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(tName).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getCollectiviteCodeColname() + "=").append(tName)
+ .append("." + pc.getCollectiviteCodeColname() + " AND ").append(sec.getMaintable().getBaseName()).append("." + pc.getBudgetCodeColname() + "=").append(tName).append("." + pc.getBudgetCodeColname() + "");
addedTableNames.add(si.getTableId());
}
} else {
// Adaptation de la requete en fonction des spécificités du moteur de SGBD utilisé
- sql = adaptSqlForDatabaseEngine(sql);
+ sql = this.adaptSqlForDatabaseEngine(sql);
logger.debug(sql);
// well, now sql is complete
PreparedStatement ps = null;
ResultSet rs = null;
try {
- con = getConnection();
+ con = this.getConnection();
ps = con.prepareStatement(sql);
ps.setString(1, collectivite);
logger.debug("1(coll)->" + collectivite);
}
}
if (con != null) {
- releaseConnection(con);
+ this.releaseConnection(con);
}
}
return ret;
ps.setString(1, archiveName);
ps.executeUpdate();
ps.close();
- adaptCommitForDatabaseEngine(con);
+ this.adaptCommitForDatabaseEngine(con);
} catch (final Exception e) {
logger.error(e, e);
protected abstract void adaptCommitForDatabaseEngine(final Connection connection) throws SQLException;
+ public long createImportJob(final Connection connection, final String archiveName, final String exercice, final String userLogin, final String importScheme) throws SQLException {
+ final String query = this.queryProvider.getQuery("createImportJob");
+ final PreparedStatement ps = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
+ try {
+ ps.setString(1, archiveName);
+ ps.setString(2, exercice);
+ ps.setString(3, userLogin);
+ ps.setString(4, importScheme);
+ ps.executeUpdate();
+
+ final ResultSet rs = ps.getGeneratedKeys();
+ rs.next();
+ return rs.getLong(1);
+ } finally {
+ ps.close();
+ }
+ }
+
+ public String getParameterValue(final Connection connection, final String paramName) throws SQLException {
+ final String query = this.queryProvider.getQuery("getParameterValue");
+ final PreparedStatement ps = connection.prepareStatement(query);
+ try {
+ ps.setString(1, paramName);
+ final ResultSet rs = ps.executeQuery();
+ return rs.next() ? rs.getString(1) : null;
+ } finally {
+ ps.close();
+ }
+ }
+
+ public boolean parameterExists(final Connection connection, final String paramName) throws SQLException {
+ final PreparedStatement ps = connection.prepareStatement(this.queryProvider.getQuery("setParameterValue.exists"));
+ try {
+ final ResultSet rs = ps.executeQuery();
+ return rs.next();
+ } finally {
+ ps.close();
+ }
+ }
+
+ public void setParameterValue(final Connection connection, final String paramName, final String paramValue) throws SQLException {
+ final String query = this.queryProvider.getQuery(this.parameterExists(connection, paramName) ? "setParameterValue.update" : "setParameterValue.insert");
+ final PreparedStatement ps = connection.prepareStatement(query);
+ try {
+ ps.setString(1, paramName);
+ ps.setString(2, paramValue);
+ ps.execute();
+ } finally {
+ ps.close();
+ }
+ }
+
+ public boolean isAlreadyImportingArchive(final Connection connection, final String archiveName, final String exercice) throws SQLException {
+ final PreparedStatement ps = connection.prepareStatement(this.queryProvider.getQuery("isAlreadyImportingArchive"));
+ try {
+ ps.setString(1, archiveName);
+ ps.setString(2, exercice);
+ return ps.executeQuery().next();
+ } finally {
+ ps.close();
+ }
+ }
+
+ public void declareMapping(final Connection connection, final String archiveName, final String collectivite, final String exercice) throws SQLException {
+ final PreparedStatement psRowExists = connection.prepareStatement(this.queryProvider.getQuery("declareMapping.exists"));
+ try {
+ psRowExists.setString(1, collectivite);
+ psRowExists.setString(2, exercice);
+ psRowExists.setString(3, archiveName);
+
+ final PreparedStatement psUpdate = connection.prepareStatement(this.queryProvider.getQuery(psRowExists.executeQuery().next() ? "declareMapping.update" : "declareMapping.insert"));
+ try {
+ psUpdate.setString(1, archiveName);
+ psUpdate.setString(2, collectivite);
+ psUpdate.setString(3, exercice);
+ psUpdate.execute();
+ } finally {
+ psUpdate.close();
+ }
+ } finally {
+ psRowExists.close();
+ }
+ }
+
+ public void updateArchiveStatus(final Connection connection, final long jobId, final ImportJob.Status status) throws SQLException {
+ final PreparedStatement ps = connection.prepareStatement(this.queryProvider.getQuery("updateArchiveStatus"));
+ try {
+ ps.setInt(1, status.getValue());
+ ps.setLong(2, jobId);
+ ps.executeUpdate();
+ } finally {
+ ps.close();
+ }
+ }
+
+ public List<ImportJob> getArchiveToImport(final Connection connection) throws SQLException {
+ final List<ImportJob> result = new LinkedList<ImportJob>();
+ final PreparedStatement ps = connection.prepareStatement(this.queryProvider.getQuery("getArchiveToImport"));
+ try {
+ ps.setInt(1, ImportJob.Status.ASKED.getValue());
+ final ResultSet rs = ps.executeQuery();
+
+ while (rs.next()) {
+ result.add(new ImportJob(rs));
+ }
+ } finally {
+ ps.close();
+ }
+
+ return result;
+ }
+
+ @Override
+ public void patchDocIdInitDocName(final Connection connection) throws SQLException {
+ final String alterQuery = this.queryProvider.getQuery("patchDocIdInitDocName.alterTable");
+
+ final PreparedStatement ps = connection.prepareStatement(this.queryProvider.getQuery("patchDocIdInitDocName.getTableColumn"));
+ try {
+ final ResultSet rs = ps.executeQuery();
+ final Statement alterSt = connection.createStatement();
+
+ while (rs.next()) {
+ final String sql = alterQuery.replace(":table", rs.getString(1)).replace(":column", rs.getString(2));
+ alterSt.execute(sql);
+ }
+
+ this.setParameterValue("patchDocIdInitDocName", "true");
+ } finally {
+ ps.close();
+ }
+ }
+
+ @Override
+ public boolean isPatchDocIdInitDocNameNeeded(final Connection connection) throws SQLException {
+ final PreparedStatement ps = connection.prepareStatement(this.queryProvider.getQuery("isPatchDocIdInitDocNameNeeded"));
+ try {
+ final ResultSet rs = ps.executeQuery();
+ return rs.next() && (rs.getInt(1) > 0);
+ } finally {
+ ps.close();
+ }
+ }
}