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 <a href="mailto:sascha.teichmann@intevation.de">Sascha L. Teichmann</a>
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<User> users = new ArrayList<User>();
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 :