ingo@100: /* ingo@100: * Copyright (c) 2010 by Intevation GmbH ingo@100: * ingo@100: * This program is free software under the LGPL (>=v2.1) ingo@100: * Read the file LGPL.txt coming with the software for details ingo@100: * or visit http://www.gnu.org/licenses/ if it does not exist. ingo@100: */ ingo@100: sascha@13: package de.intevation.artifactdatabase; sascha@13: sascha@13: import de.intevation.artifacts.Artifact; sascha@41: import de.intevation.artifacts.ArtifactFactory; sascha@41: import de.intevation.artifacts.ArtifactSerializer; sascha@133: import de.intevation.artifacts.User; sascha@13: sascha@138: import de.intevation.artifacts.common.utils.XMLUtils; sascha@138: sascha@93: import java.sql.Connection; sascha@93: import java.sql.PreparedStatement; sascha@93: import java.sql.ResultSet; sascha@93: import java.sql.SQLException; sascha@93: import java.sql.Types; sascha@93: sascha@148: import java.util.ArrayList; sascha@148: sascha@93: import javax.sql.DataSource; sascha@93: sascha@17: import org.apache.log4j.Logger; sascha@17: sascha@133: import org.w3c.dom.Document; sascha@133: sascha@133: sascha@13: /** sascha@90: * The backend implements the low level layer used to store artifacts sascha@90: * in a SQL database. sascha@90: * ingo@79: * @author Sascha L. Teichmann sascha@13: */ sascha@13: public class Backend sascha@41: implements DatabaseCleaner.ArtifactReviver sascha@13: { sascha@17: private static Logger logger = Logger.getLogger(Backend.class); sascha@17: sascha@90: /** sascha@90: * The SQL statement to create new artifact id inside the database. sascha@90: */ sascha@14: public static final String SQL_NEXT_ID = sascha@14: SQL.get("artifacts.id.nextval"); sascha@14: sascha@90: /** sascha@90: * The SQL statement to insert an artifact into the database. sascha@90: */ sascha@14: public static final String SQL_INSERT = sascha@14: SQL.get("artifacts.insert"); sascha@14: sascha@90: /** sascha@90: * The SQL statement to update some columns of an existing sascha@90: * artifact in the database. sascha@90: */ sascha@14: public static final String SQL_UPDATE = sascha@14: SQL.get("artifacts.update"); sascha@14: sascha@90: /** sascha@90: * The SQL statement to touch the access time of an sascha@90: * artifact inside the database. sascha@90: */ sascha@14: public static final String SQL_TOUCH = sascha@14: SQL.get("artifacts.touch"); sascha@14: sascha@90: /** sascha@90: * The SQL statement to load an artifact by a given sascha@90: * identifier from the database. sascha@90: */ sascha@15: public static final String SQL_LOAD_BY_GID = sascha@15: SQL.get("artifacts.select.gid"); sascha@15: sascha@90: /** sascha@90: * The SQL statement to get the database id of an artifact sascha@90: * identified by the identifier. sascha@90: */ ingo@80: public static final String SQL_GET_ID = ingo@80: SQL.get("artifacts.get.id"); ingo@80: sascha@90: /** sascha@90: * The SQL statement to replace the content of an sascha@90: * existing artifact inside the database. sascha@90: */ ingo@80: public static final String SQL_REPLACE = ingo@80: SQL.get("artifacts.replace"); ingo@80: sascha@133: // USER SQL sascha@133: sascha@133: public static final String SQL_USERS_NEXT_ID = sascha@133: SQL.get("users.id.nextval"); sascha@133: sascha@133: public static final String SQL_USERS_INSERT = sascha@133: SQL.get("users.insert"); sascha@133: sascha@144: public static final String SQL_USERS_SELECT_ID_BY_GID = sascha@144: SQL.get("users.select.id.by.gid"); sascha@144: sascha@133: public static final String SQL_USERS_SELECT_GID = sascha@133: SQL.get("users.select.gid"); sascha@133: sascha@133: public static final String SQL_USERS_DELETE_ID = sascha@133: SQL.get("users.delete.id"); sascha@133: sascha@133: public static final String SQL_USERS_DELETE_COLLECTIONS = sascha@133: SQL.get("users.delete.collections"); sascha@133: sascha@148: public static final String SQL_USERS_SELECT_ALL = sascha@133: SQL.get("users.select.all"); sascha@133: sascha@144: public static final String SQL_USERS_COLLECTIONS = sascha@144: SQL.get("users.collections"); sascha@144: sascha@144: public static final String SQL_USERS_COLLECTION_IDS = sascha@144: SQL.get("users.collection.ids"); sascha@144: sascha@144: public static final String SQL_USERS_DELETE_ALL_COLLECTIONS = sascha@144: SQL.get("users.delete.all.collections"); sascha@144: sascha@144: public static final String SQL_ARTIFACTS_IN_ONLY_COLLECTION_ONLY = sascha@144: SQL.get("artifacts.in.one.collection.only"); sascha@144: sascha@144: public static final String SQL_OUTDATE_ARTIFACTS_COLLECTION = sascha@144: SQL.get("outdate.artifacts.collection"); sascha@144: sascha@144: public static final String SQL_OUTDATE_ARTIFACTS_USER = sascha@144: SQL.get("outdate.artifacts.user"); sascha@144: sascha@144: public static final String SQL_DELETE_USER_COLLECTION_ITEMS = sascha@144: SQL.get("delete.user.collection.items"); sascha@144: ingo@128: /** The singleton.*/ ingo@128: protected static Backend instance; ingo@128: sascha@90: /** sascha@90: * The database cleaner. Reference is stored here because sascha@90: * the cleaner is woken up if the backend finds an outdated sascha@90: * artifact. This artifact should be removed as soon as sascha@90: * possible. sascha@90: */ sascha@30: protected DatabaseCleaner cleaner; sascha@30: sascha@90: /** sascha@90: * To revive an artifact from the bytes coming from the database sascha@90: * we need the artifact factory which references the artifact sascha@90: * serializer which is able to do the reviving job. sascha@90: */ sascha@41: protected FactoryLookup factoryLookup; sascha@41: sascha@90: /** sascha@90: * Little helper interface to decouple the ArtifactDatabase sascha@90: * from the Backend. A ArtifactDatabase should depend on a sascha@90: * Backend but a Backend not from an ArtifactDatabase. sascha@90: */ sascha@41: public interface FactoryLookup { sascha@41: sascha@90: /** sascha@90: * Returns an ArtifactFactory which is bound to a given name. sascha@90: * @param factoryName The name of the artifact factory. sascha@90: * @return The ArtifactFactory bound to the factory name or sascha@90: * null if not matching factory is found. sascha@90: */ sascha@41: ArtifactFactory getArtifactFactory(String factoryName); sascha@41: sascha@41: } // interface FactoryLookup sascha@41: sascha@90: /** sascha@90: * Inner class that brigdes between the persisten form of the sascha@90: * artifact and the living one inside the artifact database. sascha@90: * After the describe(), feed(), advance() and out() operations sascha@90: * of the artifact it must be possible to write to modified artifact sascha@90: * back into the database. sascha@90: */ sascha@32: public final class PersistentArtifact sascha@32: extends Id sascha@14: { sascha@41: private Artifact artifact; sascha@41: private ArtifactSerializer serializer; ingo@84: private Long ttl; sascha@14: sascha@90: /** sascha@90: * Cronstructor to create a persistent artifact. sascha@90: * @param artifact The living artifact. sascha@90: * @param serializer The serializer to store the artifact sascha@90: * after the operations. sascha@90: * @param ttl The time to life of the artifact. sascha@90: * @param id The database id of the artifact. sascha@90: */ sascha@41: public PersistentArtifact( sascha@47: Artifact artifact, sascha@41: ArtifactSerializer serializer, ingo@84: Long ttl, sascha@41: int id sascha@41: ) { sascha@32: super(id); sascha@41: this.artifact = artifact; sascha@41: this.serializer = serializer; ingo@84: this.ttl = ttl; sascha@14: } sascha@14: sascha@90: /** sascha@90: * Returns the wrapped living artifact. sascha@90: * @return the living artifact. sascha@90: */ sascha@32: public Artifact getArtifact() { sascha@32: return artifact; sascha@14: } sascha@14: sascha@90: /** sascha@90: * Returns the serialized which is able to write a sascha@90: * modified artifact back into the database. sascha@90: * @return The serializer. sascha@90: */ sascha@41: public ArtifactSerializer getSerializer() { sascha@41: return serializer; sascha@41: } sascha@41: sascha@90: /** sascha@90: * The time to life of the artifact. sascha@90: * @return The time to live. sascha@90: */ ingo@84: public Long getTTL() { ingo@84: return ttl; ingo@84: } ingo@84: sascha@90: /** sascha@90: * Stores the living artifact back into the database. sascha@90: */ sascha@32: public void store() { sascha@38: if (logger.isDebugEnabled()) { sascha@38: logger.debug("storing artifact id = " + getId()); sascha@38: } sascha@32: Backend.this.store(this); sascha@14: } sascha@14: sascha@90: /** sascha@90: * Only touches the access time of the artifact. sascha@90: */ sascha@32: public void touch() { sascha@38: if (logger.isDebugEnabled()) { sascha@38: logger.debug("touching artifact id = " + getId()); sascha@38: } sascha@32: Backend.this.touch(this); sascha@14: } sascha@32: } // class ArtifactWithId sascha@14: sascha@90: /** sascha@90: * Default constructor sascha@90: */ sascha@13: public Backend() { sascha@13: } sascha@13: sascha@90: /** sascha@90: * Constructor to create a backend with a link to the database cleaner. sascha@90: * @param cleaner The clean which periodically removes outdated sascha@90: * artifacts from the database. sascha@90: */ sascha@30: public Backend(DatabaseCleaner cleaner) { sascha@30: this.cleaner = cleaner; sascha@30: } sascha@30: ingo@128: ingo@128: /** ingo@128: * Returns the singleton of this Backend. ingo@128: * ingo@128: * @return the backend. ingo@128: */ ingo@128: public static synchronized Backend getInstance() { ingo@128: if (instance == null) { ingo@128: instance = new Backend(); ingo@128: } ingo@128: ingo@128: return instance; ingo@128: } ingo@128: sascha@90: /** sascha@90: * Sets the factory lookup mechanism to decouple ArtifactDatabase sascha@90: * and Backend. sascha@90: * @param factoryLookup sascha@90: */ sascha@41: public void setFactoryLookup(FactoryLookup factoryLookup) { sascha@41: this.factoryLookup = factoryLookup; sascha@41: } sascha@41: sascha@90: /** sascha@90: * Sets the database cleaner explicitly. sascha@90: * @param cleaner The database cleaner sascha@90: */ sascha@32: public void setCleaner(DatabaseCleaner cleaner) { sascha@32: this.cleaner = cleaner; sascha@32: } sascha@32: sascha@90: /** sascha@90: * Returns a new unique identifier to external identify sascha@90: * the artifact across the system. This implementation sascha@90: * uses random UUIDs v4 to achieve this target. sascha@90: * @return the new identifier sascha@90: */ sascha@32: public String newIdentifier() { sascha@32: // TODO: check database for collisions. ingo@79: return StringUtils.newUUID(); sascha@32: } sascha@32: sascha@90: /** sascha@90: * Stores a new artifact into the database. sascha@90: * @param artifact The artifact to be stored sascha@90: * @param factory The factory which build the artifact sascha@90: * @param ttl The initial time to life of the artifact. sascha@90: * @return A persistent wrapper around the living sascha@90: * artifact to be able to write modification later. sascha@90: * @throws Exception Thrown if something went wrong with the sascha@90: * storage process. sascha@90: */ sascha@32: public PersistentArtifact storeInitially( sascha@41: Artifact artifact, sascha@41: ArtifactFactory factory, sascha@41: Long ttl sascha@47: ) sascha@32: throws Exception sascha@32: { sascha@32: return new PersistentArtifact( sascha@32: artifact, sascha@41: factory.getSerializer(), ingo@84: ttl, sascha@41: insertDatabase(artifact, factory, ttl)); sascha@32: } sascha@47: sascha@90: /** sascha@90: * Stores an artifact into database if it does not exist there. sascha@90: * If it exists there it is only updated. sascha@90: * @param artifact The artifact to store/update. sascha@90: * @param factory The factory which created the artifact. sascha@90: * @param ttl The initial time to live of the artifact. sascha@90: * @return A persistent version of the artifact to be able sascha@90: * to store a modification later. sascha@90: * @throws Exception Thrown if something went wrong during sascha@90: * storing/updating. sascha@90: */ ingo@80: public PersistentArtifact storeOrReplace( ingo@80: Artifact artifact, ingo@80: ArtifactFactory factory, ingo@80: Long ttl ingo@80: ) ingo@80: throws Exception ingo@80: { ingo@80: return new PersistentArtifact( ingo@80: artifact, ingo@80: factory.getSerializer(), ingo@84: ttl, ingo@80: storeOrReplaceDatabase(artifact, factory, ttl)); ingo@80: } ingo@80: sascha@90: /** sascha@90: * Implementors of this interface are able to process the raw sascha@90: * artifact data from the database for loading. sascha@90: */ ingo@79: public interface ArtifactLoader { ingo@79: sascha@90: /** sascha@90: * Creates a custom object from the raw artifact database data. sascha@90: * @param factory The factory that created this artifact. sascha@90: * @param ttl The current time to life of the artifact. sascha@90: * @param bytes The raw artifact bytes from the database. sascha@90: * @param id The database id of the artifact. sascha@90: * @return The custom object created by the implementation. sascha@90: */ ingo@84: Object load(ArtifactFactory factory, Long ttl, byte [] bytes, int id); ingo@79: ingo@79: } // interface ArtifactLoader ingo@79: sascha@90: /** sascha@90: * Fetches an artifact from the database identified by the sascha@90: * given identifier. sascha@90: * @param identifer The identifier of the artifact. sascha@90: * @return A persistent wrapper around the found artifact sascha@90: * to be able to write back a modifaction later or null sascha@90: * if no artifact is found for this identifier. sascha@90: */ sascha@32: public PersistentArtifact getArtifact(String identifer) { sascha@15: ingo@79: return (PersistentArtifact)loadArtifact( sascha@86: identifer, ingo@79: new ArtifactLoader() { ingo@79: ingo@79: public Object load( ingo@79: ArtifactFactory factory, ingo@84: Long ttl, ingo@79: byte [] bytes, ingo@79: int id ingo@79: ) { ingo@79: ArtifactSerializer serializer = factory.getSerializer(); ingo@79: ingo@79: Artifact artifact = serializer.fromBytes(bytes); ingo@79: ingo@79: return artifact == null ingo@79: ? null ingo@84: : new PersistentArtifact(artifact, serializer, ttl, id); ingo@79: } ingo@79: }); ingo@79: } ingo@79: sascha@90: /** sascha@90: * More general loading mechanism for artifacts. The concrete sascha@90: * load processing is delegated to the given loader. sascha@90: * @param identifer The identifier of the artifact. sascha@90: * @param loader The loader which processes the raw database data. sascha@90: * @return The object created by the loader. sascha@90: */ ingo@79: public Object loadArtifact(String identifer, ArtifactLoader loader) { ingo@79: ingo@79: if (!StringUtils.checkUUID(identifer)) { sascha@15: return null; sascha@15: } sascha@15: sascha@15: Connection connection = null; sascha@15: PreparedStatement stmnt_load = null; sascha@15: ResultSet load_result = null; sascha@15: sascha@15: DataSource dataSource = DBConnection.getDataSource(); sascha@15: try { sascha@15: connection = dataSource.getConnection(); sascha@15: stmnt_load = connection.prepareStatement(SQL_LOAD_BY_GID); sascha@32: stmnt_load.setString(1, identifer); sascha@15: sascha@15: load_result = stmnt_load.executeQuery(); sascha@15: sascha@15: if (!load_result.next()) { sascha@15: return null; sascha@15: } sascha@15: ingo@84: int id = load_result.getInt(1); ingo@84: long ttlX = load_result.getLong(3); sascha@15: ingo@84: Long ttl = load_result.wasNull() ? null : Long.valueOf(ttlX); ingo@84: ingo@84: if (ttl != null) { // real time to life sascha@15: long last_access = load_result.getTimestamp(2).getTime(); ingo@84: if (last_access + ttlX < System.currentTimeMillis()) { sascha@15: artifactOutdated(id); sascha@15: return null; sascha@15: } sascha@15: } sascha@15: sascha@41: String factoryName = load_result.getString(4); sascha@15: sascha@41: if (factoryLookup == null) { sascha@41: logger.error("factory lookup == null"); sascha@41: return null; sascha@41: } sascha@41: sascha@41: ArtifactFactory factory = factoryLookup sascha@41: .getArtifactFactory(factoryName); sascha@41: sascha@41: if (factory == null) { sascha@41: logger.error("factory '" + factoryName + "' not found"); sascha@41: return null; sascha@41: } sascha@41: sascha@41: byte [] bytes = load_result.getBytes(5); sascha@41: ingo@84: return loader.load(factory, ttl, bytes, id); sascha@15: } sascha@15: catch (SQLException sqle) { sascha@17: logger.error(sqle.getLocalizedMessage(), sqle); sascha@15: } sascha@15: finally { sascha@15: if (load_result != null) { sascha@15: try { load_result.close(); } sascha@15: catch (SQLException sqle) {} sascha@15: } sascha@15: if (stmnt_load != null) { sascha@15: try { load_result.close(); } sascha@15: catch (SQLException sqle) {} sascha@15: } sascha@15: if (connection != null) { sascha@47: try { connection.close(); } sascha@15: catch (SQLException sqle) {} sascha@15: } sascha@15: } sascha@15: return null; sascha@15: } sascha@15: sascha@90: /** sascha@90: * Called if the load mechanism found an outdated artifact. sascha@90: * It wakes up the database cleaner. sascha@90: * @param id The id of the outdated artifact. sascha@90: */ sascha@15: protected void artifactOutdated(int id) { ingo@79: if (logger.isDebugEnabled()) { ingo@79: logger.info("artifactOutdated: id = " + id); ingo@79: } sascha@30: if (cleaner != null) { sascha@30: cleaner.wakeup(); sascha@30: } sascha@15: } sascha@15: sascha@41: public Artifact reviveArtifact(String factoryName, byte [] bytes) { sascha@41: if (factoryLookup == null) { sascha@41: logger.error("reviveArtifact: factory lookup == null"); sascha@41: return null; sascha@41: } sascha@41: ArtifactFactory factory = factoryLookup sascha@41: .getArtifactFactory(factoryName); sascha@32: sascha@41: if (factory == null) { sascha@90: logger.error( sascha@90: "reviveArtifact: no factory '" + factoryName + "' found"); sascha@41: return null; sascha@41: } sascha@41: sascha@41: ArtifactSerializer serializer = factory.getSerializer(); sascha@41: sascha@41: return serializer.fromBytes(bytes); sascha@41: } sascha@41: sascha@90: /** sascha@90: * Internal method to store/replace an artifact inside the database. sascha@90: * If an artifact with the given identifier does not exists it is sascha@90: * created else only the content data is updated. sascha@90: * @param artifact The artifact to be store/update inside the database. sascha@90: * @param factory The factory that created the artifact. sascha@90: * @param ttl The initial time to life of the artifact. sascha@90: * @return The database id of the stored/updated artifact. sascha@90: */ ingo@80: protected int storeOrReplaceDatabase( ingo@80: Artifact artifact, ingo@80: ArtifactFactory factory, ingo@80: Long ttl ingo@80: ) { ingo@80: String uuid = artifact.identifier(); ingo@80: ingo@80: if (!StringUtils.checkUUID(uuid)) { ingo@80: throw new RuntimeException("No valid UUID"); ingo@80: } ingo@80: ingo@80: Connection connection = null; ingo@80: PreparedStatement stmnt = null; ingo@80: ResultSet result = null; ingo@80: ingo@80: DataSource dataSource = DBConnection.getDataSource(); ingo@80: try { ingo@80: connection = dataSource.getConnection(); ingo@80: try { ingo@80: connection.setAutoCommit(false); ingo@80: ingo@80: stmnt = connection.prepareStatement(SQL_GET_ID); ingo@80: ingo@80: stmnt.setString(1, uuid); ingo@80: result = stmnt.executeQuery(); ingo@80: ingo@80: Integer ID = result.next() ingo@80: ? Integer.valueOf(result.getInt(1)) ingo@80: : null; ingo@80: ingo@80: result.close(); result = null; ingo@80: stmnt.close(); stmnt = null; ingo@80: ingo@80: if (ID != null) { // already in database ingo@80: int id = ID.intValue(); ingo@80: ingo@80: stmnt = connection.prepareStatement(SQL_REPLACE); ingo@80: ingo@80: if (ttl == null) { ingo@80: stmnt.setNull(1, Types.BIGINT); ingo@80: } ingo@80: else { ingo@80: stmnt.setLong(1, ttl.longValue()); ingo@80: } ingo@80: ingo@80: stmnt.setString(2, factory.getName()); ingo@80: stmnt.setBytes( ingo@80: 3, ingo@80: factory.getSerializer().toBytes(artifact)); ingo@80: stmnt.setInt(4, id); ingo@80: ingo@80: stmnt.execute(); ingo@80: connection.commit(); ingo@80: return id; ingo@80: } ingo@80: ingo@80: stmnt = connection.prepareStatement(SQL_NEXT_ID); ingo@80: result = stmnt.executeQuery(); ingo@80: ingo@80: if (!result.next()) { ingo@80: throw new RuntimeException("No id generated"); ingo@80: } ingo@80: ingo@80: int id = result.getInt(1); ingo@80: ingo@80: result.close(); result = null; ingo@80: stmnt.close(); stmnt = null; ingo@80: ingo@80: stmnt = connection.prepareStatement(SQL_INSERT); ingo@80: ingo@80: stmnt.setInt(1, id); ingo@80: stmnt.setString(2, uuid); ingo@80: if (ttl == null) { ingo@80: stmnt.setNull(3, Types.BIGINT); ingo@80: } ingo@80: else { ingo@80: stmnt.setLong(3, ttl.longValue()); ingo@80: } ingo@80: ingo@80: stmnt.setString(4, factory.getName()); ingo@80: ingo@80: stmnt.setBytes( ingo@80: 5, ingo@80: factory.getSerializer().toBytes(artifact)); ingo@80: ingo@80: stmnt.execute(); ingo@80: connection.commit(); ingo@80: return id; ingo@80: } ingo@80: catch (SQLException sqle) { ingo@80: connection.rollback(); ingo@80: throw sqle; ingo@80: } ingo@80: } ingo@80: catch (SQLException sqle) { ingo@80: logger.error(sqle.getLocalizedMessage(), sqle); ingo@80: } ingo@80: finally { ingo@80: if (result != null) { ingo@80: try { result.close(); } ingo@80: catch (SQLException sqle) {} ingo@80: } ingo@80: if (stmnt != null) { ingo@80: try { stmnt.close(); } ingo@80: catch (SQLException sqle) {} ingo@80: } ingo@80: if (connection != null) { ingo@80: try { connection.close(); } ingo@80: catch (SQLException sqle) {} ingo@80: } ingo@80: } ingo@80: throw new RuntimeException("failed insert artifact into database"); ingo@80: } ingo@80: sascha@90: /** sascha@90: * Internal method to store an artifact inside the database. sascha@90: * @param artifact The artifact to be stored. sascha@90: * @param factory The factory which created the artifact. sascha@90: * @param ttl The initial time to live of the artifact. sascha@90: * @return The database id of the stored artifact. sascha@90: */ sascha@41: protected int insertDatabase( sascha@47: Artifact artifact, sascha@41: ArtifactFactory factory, sascha@41: Long ttl sascha@41: ) { sascha@32: String uuid = artifact.identifier(); sascha@32: sascha@14: Connection connection = null; sascha@14: PreparedStatement stmnt_next_id = null; sascha@14: PreparedStatement stmnt_insert = null; sascha@14: ResultSet res_id = null; sascha@14: sascha@14: DataSource dataSource = DBConnection.getDataSource(); sascha@14: try { sascha@14: connection = dataSource.getConnection(); sascha@14: try { sascha@14: connection.setAutoCommit(false); sascha@14: sascha@14: stmnt_next_id = connection.prepareStatement(SQL_NEXT_ID); sascha@14: stmnt_insert = connection.prepareStatement(SQL_INSERT); sascha@14: sascha@14: res_id = stmnt_next_id.executeQuery(); sascha@14: sascha@14: if (!res_id.next()) { sascha@14: throw new RuntimeException("No id generated"); sascha@14: } sascha@14: sascha@14: int id = res_id.getInt(1); sascha@14: sascha@14: stmnt_insert.setInt(1, id); sascha@32: stmnt_insert.setString(2, uuid); sascha@14: if (ttl == null) { sascha@14: stmnt_insert.setNull(3, Types.BIGINT); sascha@14: } sascha@14: else { sascha@14: stmnt_insert.setLong(3, ttl.longValue()); sascha@14: } sascha@14: sascha@41: stmnt_insert.setString(4, factory.getName()); sascha@41: sascha@41: stmnt_insert.setBytes( sascha@41: 5, sascha@41: factory.getSerializer().toBytes(artifact)); sascha@32: sascha@14: stmnt_insert.execute(); sascha@14: sascha@14: connection.commit(); sascha@14: sascha@14: return id; sascha@14: } sascha@14: catch (SQLException sqle) { sascha@14: connection.rollback(); sascha@14: throw sqle; sascha@14: } sascha@14: } sascha@14: catch (SQLException sqle) { sascha@17: logger.error(sqle.getLocalizedMessage(), sqle); sascha@14: } sascha@14: finally { sascha@14: if (res_id != null) { sascha@14: try { res_id.close(); } sascha@14: catch (SQLException sqle) {} sascha@14: } sascha@14: if (stmnt_insert != null) { sascha@14: try { stmnt_insert.close(); } sascha@14: catch (SQLException sqle) {} sascha@14: } sascha@14: if (stmnt_next_id != null) { sascha@14: try { stmnt_next_id.close(); } sascha@14: catch (SQLException sqle) {} sascha@14: } sascha@14: if (connection != null) { sascha@14: try { connection.close(); } sascha@14: catch (SQLException sqle) {} sascha@14: } sascha@14: } sascha@14: throw new RuntimeException("failed insert artifact into database"); sascha@14: } sascha@14: sascha@90: /** sascha@90: * Touches the access timestamp of a given artifact to prevent sascha@90: * that it will be removed from the database by the database cleaner. sascha@90: * @param artifact The persistent wrapper around the living artifact. sascha@90: */ sascha@32: public void touch(PersistentArtifact artifact) { sascha@26: sascha@14: try { sascha@26: Connection connection = null; sascha@26: PreparedStatement stmnt_touch = null; sascha@26: DataSource dataSource = DBConnection.getDataSource(); sascha@14: try { sascha@26: connection = dataSource.getConnection(); sascha@26: try { sascha@26: connection.setAutoCommit(false); sascha@41: stmnt_touch = connection.prepareStatement(SQL_TOUCH); sascha@32: stmnt_touch.setInt(1, artifact.getId()); sascha@26: stmnt_touch.execute(); sascha@26: connection.commit(); sascha@26: } sascha@26: catch (SQLException sqle) { sascha@26: connection.rollback(); sascha@26: } sascha@14: } sascha@14: catch (SQLException sqle) { sascha@26: logger.error(sqle.getLocalizedMessage(), sqle); sascha@26: } sascha@26: finally { sascha@26: if (stmnt_touch != null) { sascha@26: try { stmnt_touch.close(); } sascha@26: catch (SQLException sqle) {} sascha@26: } sascha@26: if (connection != null) { sascha@26: try { connection.close(); } sascha@26: catch (SQLException sqle) {} sascha@26: } sascha@14: } sascha@14: } sascha@26: catch (Exception e) { sascha@26: logger.error(e.getLocalizedMessage(), e); sascha@14: } sascha@14: } sascha@14: sascha@90: /** sascha@90: * Writes modification of an artifact back to the database. sascha@90: * @param artifact The persistent wrapper around a living sascha@90: * artifact. sascha@90: */ sascha@32: public void store(PersistentArtifact artifact) { sascha@26: sascha@14: try { sascha@26: Connection connection = null; sascha@26: PreparedStatement stmnt_update = null; sascha@26: DataSource dataSource = DBConnection.getDataSource(); sascha@14: try { sascha@26: connection = dataSource.getConnection(); sascha@26: try { sascha@26: connection.setAutoCommit(false); sascha@26: stmnt_update = connection.prepareStatement(SQL_UPDATE); sascha@32: stmnt_update.setInt(2, artifact.getId()); sascha@26: sascha@41: byte [] bytes = artifact sascha@41: .getSerializer() sascha@41: .toBytes(artifact.getArtifact()); sascha@26: sascha@26: stmnt_update.setBytes(1, bytes); sascha@26: stmnt_update.execute(); sascha@26: connection.commit(); sascha@26: } sascha@26: catch (SQLException sqle) { sascha@26: connection.rollback(); sascha@26: } sascha@14: } sascha@14: catch (SQLException sqle) { sascha@26: logger.error(sqle.getLocalizedMessage(), sqle); sascha@26: } sascha@26: finally { sascha@26: if (stmnt_update != null) { sascha@26: try { stmnt_update.close(); } sascha@26: catch (SQLException sqle) {} sascha@26: } sascha@26: if (connection != null) { sascha@26: try { connection.close(); } sascha@26: catch (SQLException sqle) {} sascha@26: } sascha@14: } sascha@14: } sascha@26: catch (Exception e) { sascha@26: logger.error(e.getLocalizedMessage(), e); sascha@14: } sascha@14: } sascha@133: sascha@133: public User createUser( sascha@133: String name, sascha@133: Document role sascha@133: ) { sascha@138: Connection connection = null; sascha@138: PreparedStatement stmnt_next_id = null; sascha@138: PreparedStatement stmnt_insert = null; sascha@145: ResultSet result = null; sascha@138: sascha@138: String identifier = newIdentifier(); sascha@138: sascha@138: DataSource dataSource = DBConnection.getDataSource(); sascha@138: try { sascha@138: connection = dataSource.getConnection(); sascha@138: try { sascha@138: connection.setAutoCommit(false); sascha@138: sascha@138: stmnt_next_id = connection.prepareStatement(SQL_USERS_NEXT_ID); sascha@138: stmnt_insert = connection.prepareStatement(SQL_USERS_INSERT); sascha@138: sascha@145: result = stmnt_next_id.executeQuery(); sascha@138: sascha@146: if (!result.next()) { sascha@138: throw new RuntimeException("No id generated"); sascha@138: } sascha@138: sascha@145: int id = result.getInt(1); sascha@138: sascha@138: stmnt_insert.setInt(1, id); sascha@138: stmnt_insert.setString(2, identifier); sascha@138: stmnt_insert.setString(3, name); sascha@138: sascha@138: byte [] roleData = role == null sascha@138: ? null sascha@138: : XMLUtils.toByteArray(role); sascha@138: sascha@138: if (roleData == null) { sascha@138: stmnt_insert.setNull(4, Types.BIGINT); sascha@138: } sascha@138: else { sascha@138: stmnt_insert.setBytes(4, roleData); sascha@138: } sascha@138: sascha@138: stmnt_insert.execute(); sascha@138: sascha@138: connection.commit(); sascha@138: sascha@138: return new DefaultUser(identifier, name, role); sascha@138: } sascha@138: catch (SQLException sqle) { sascha@138: connection.rollback(); sascha@138: throw sqle; sascha@138: } sascha@138: } sascha@138: catch (SQLException sqle) { sascha@138: logger.error(sqle.getLocalizedMessage(), sqle); sascha@138: } sascha@138: finally { sascha@145: if (result != null) { sascha@145: try { result.close(); } sascha@138: catch (SQLException sqle) {} sascha@138: } sascha@138: if (stmnt_insert != null) { sascha@138: try { stmnt_insert.close(); } sascha@138: catch (SQLException sqle) {} sascha@138: } sascha@138: if (stmnt_next_id != null) { sascha@138: try { stmnt_next_id.close(); } sascha@138: catch (SQLException sqle) {} sascha@138: } sascha@138: if (connection != null) { sascha@138: try { connection.close(); } sascha@138: catch (SQLException sqle) {} sascha@138: } sascha@138: } sascha@133: return null; sascha@133: } sascha@133: sascha@133: public void deleteUser(User user) { sascha@144: sascha@147: Connection conn = null; sascha@147: ResultSet result = null; sascha@147: PreparedStatement stmnt = null; sascha@144: sascha@144: String identifier = user.identifier(); sascha@144: sascha@147: if (!StringUtils.checkUUID(identifier)) { sascha@147: return; sascha@147: } sascha@147: sascha@144: DataSource dataSource = DBConnection.getDataSource(); sascha@144: try { sascha@144: conn = dataSource.getConnection(); sascha@144: try { sascha@144: conn.setAutoCommit(false); sascha@144: sascha@144: // fetch user id sascha@144: stmnt = conn.prepareStatement(SQL_USERS_SELECT_ID_BY_GID); sascha@144: sascha@144: stmnt.setString(1, identifier); sascha@144: result = stmnt.executeQuery(); sascha@144: sascha@144: if (!result.next()) { // No such user sascha@144: return; sascha@144: } sascha@144: sascha@144: int id = result.getInt(1); sascha@144: sascha@144: result.close(); result = null; sascha@144: stmnt.close(); stmnt = null; sascha@144: sascha@144: // outdate the artifacts exclusively used by the user sascha@144: sascha@144: stmnt = conn.prepareStatement(SQL_OUTDATE_ARTIFACTS_USER); sascha@144: stmnt.setInt(1, id); sascha@144: stmnt.setInt(2, id); sascha@144: stmnt.execute(); sascha@144: sascha@144: stmnt.close(); stmnt = null; sascha@144: sascha@144: // delete the collection items of the user sascha@144: sascha@144: stmnt = conn.prepareStatement(SQL_DELETE_USER_COLLECTION_ITEMS); sascha@144: stmnt.setInt(1, id); sascha@144: stmnt.execute(); sascha@144: stmnt.close(); stmnt = null; sascha@144: sascha@144: // delete the collections of the user sascha@144: sascha@144: stmnt = conn.prepareStatement(SQL_USERS_COLLECTIONS); sascha@144: stmnt.setInt(1, id); sascha@144: stmnt.execute(); sascha@144: stmnt.close(); stmnt = null; sascha@144: sascha@144: // delete the user sascha@144: sascha@144: stmnt = conn.prepareStatement(SQL_USERS_DELETE_ID); sascha@144: stmnt.setInt(1, id); sascha@144: stmnt.execute(); sascha@144: sascha@144: conn.commit(); sascha@144: } sascha@144: catch (SQLException sqle) { sascha@144: conn.rollback(); sascha@144: throw sqle; sascha@144: } sascha@144: } sascha@144: catch (SQLException sqle) { sascha@144: logger.error(sqle.getLocalizedMessage(), sqle); sascha@144: } sascha@144: finally { sascha@144: if (result != null) { sascha@144: try { result.close(); } sascha@144: catch (SQLException sqle) {} sascha@144: } sascha@144: if (stmnt != null) { sascha@144: try { stmnt.close(); } sascha@144: catch (SQLException sqle) {} sascha@144: } sascha@144: if (conn != null) { sascha@144: try { conn.close(); } sascha@144: catch (SQLException sqle) {} sascha@144: } sascha@144: } sascha@133: } sascha@133: sascha@133: public User getUser(String identifier) { sascha@147: sascha@147: Connection conn = null; sascha@147: ResultSet result = null; sascha@147: PreparedStatement stmnt = null; sascha@147: sascha@147: if (!StringUtils.checkUUID(identifier)) { sascha@147: logger.debug("Invalid UUID: '" + identifier + "'"); sascha@147: return null; sascha@147: } sascha@147: sascha@147: DataSource dataSource = DBConnection.getDataSource(); sascha@147: try { sascha@147: conn = dataSource.getConnection(); sascha@147: stmnt = conn.prepareStatement(SQL_USERS_SELECT_ID_BY_GID); sascha@147: stmnt.setString(1, identifier); sascha@147: result = stmnt.executeQuery(); sascha@147: if (!result.next()) { // no such user sascha@147: return null; sascha@147: } sascha@147: // omit id sascha@147: String name = result.getString(2); sascha@147: byte [] roleData = result.getBytes(3); sascha@147: sascha@147: Document role = XMLUtils.fromByteArray(roleData); sascha@147: sascha@147: return new DefaultUser(identifier, name, role); sascha@147: } sascha@147: catch (SQLException sqle) { sascha@147: logger.error(sqle.getLocalizedMessage(), sqle); sascha@147: } sascha@147: finally { sascha@147: if (result != null) { sascha@147: try { result.close(); } sascha@147: catch (SQLException sqle) {} sascha@147: } sascha@147: if (stmnt != null) { sascha@147: try { stmnt.close(); } sascha@147: catch (SQLException sqle) {} sascha@147: } sascha@147: if (conn != null) { sascha@147: try { conn.close(); } sascha@147: catch (SQLException sqle) {} sascha@147: } sascha@147: } sascha@147: sascha@133: return null; sascha@133: } sascha@133: sascha@133: public User [] getUsers() { sascha@148: Connection conn = null; sascha@148: ResultSet result = null; sascha@148: PreparedStatement stmnt = null; sascha@148: sascha@148: DataSource dataSource = DBConnection.getDataSource(); sascha@148: try { sascha@148: conn = dataSource.getConnection(); sascha@148: stmnt = conn.prepareStatement(SQL_USERS_SELECT_ALL); sascha@148: result = stmnt.executeQuery(); sascha@148: sascha@148: ArrayList users = new ArrayList(); sascha@148: sascha@148: while (result.next()) { sascha@148: // omit id sascha@148: String identifier = result.getString(2); sascha@148: String name = result.getString(3); sascha@148: byte [] roleData = result.getBytes(4); sascha@148: sascha@148: Document role = XMLUtils.fromByteArray(roleData); sascha@148: User user = new DefaultUser(identifier, name, role); sascha@148: users.add(user); sascha@148: } sascha@148: sascha@148: return users.toArray(new User[users.size()]); sascha@148: } sascha@148: catch (SQLException sqle) { sascha@148: logger.error(sqle.getLocalizedMessage(), sqle); sascha@148: } sascha@148: finally { sascha@148: if (result != null) { sascha@148: try { result.close(); } sascha@148: catch (SQLException sqle) {} sascha@148: } sascha@148: if (stmnt != null) { sascha@148: try { stmnt.close(); } sascha@148: catch (SQLException sqle) {} sascha@148: } sascha@148: if (conn != null) { sascha@148: try { conn.close(); } sascha@148: catch (SQLException sqle) {} sascha@148: } sascha@148: } sascha@148: sascha@133: return null; sascha@133: } sascha@13: } ingo@79: // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :