/*
 * Copyright (c) 2010, 2011 by Intevation GmbH
 *
 * This program is free software under the LGPL (>=v2.1)
 * Read the file LGPL.txt coming with the software for details
 * or visit http://www.gnu.org/licenses/ if it does not exist.
 */
package de.intevation.artifactdatabase;

import de.intevation.artifacts.Artifact;
import de.intevation.artifacts.ArtifactCollection;
import de.intevation.artifacts.ArtifactCollectionFactory;
import de.intevation.artifacts.ArtifactFactory;
import de.intevation.artifacts.ArtifactSerializer;
import de.intevation.artifacts.CollectionItem;
import de.intevation.artifacts.User;
import de.intevation.artifacts.UserFactory;

import de.intevation.artifacts.common.utils.XMLUtils;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;

import java.util.ArrayList;
import java.util.Date;

import org.apache.log4j.Logger;

import org.w3c.dom.Document;

/**
 * The backend implements the low level layer used to store artifacts
 * in a SQL database.
 *
 * @author <a href="mailto:sascha.teichmann@intevation.de">Sascha L. Teichmann</a>
 */
public class Backend
implements   DatabaseCleaner.ArtifactReviver
{
    private static Logger logger = Logger.getLogger(Backend.class);

    /**
     * The SQL statement to create new artifact id inside the database.
     */
    public static final String SQL_NEXT_ID =
        SQL.get("artifacts.id.nextval");

    /**
     * The SQL statement to insert an artifact into the database.
     */
    public static final String SQL_INSERT =
        SQL.get("artifacts.insert");

    /**
     * The SQL statement to update some columns of an existing
     * artifact in the database.
     */
    public static final String SQL_UPDATE =
        SQL.get("artifacts.update");

    /**
     * The SQL statement to touch the access time of an
     * artifact inside the database.
     */
    public static final String SQL_TOUCH =
        SQL.get("artifacts.touch");

    /**
     * The SQL statement to load an artifact by a given
     * identifier from the database.
     */
    public static final String SQL_LOAD_BY_GID =
        SQL.get("artifacts.select.gid");

    /**
     * The SQL statement to get the database id of an artifact
     * identified by the identifier.
     */
    public static final String SQL_GET_ID =
        SQL.get("artifacts.get.id");

    /**
     * The SQL statement to replace the content of an
     * existing artifact inside the database.
     */
    public static final String SQL_REPLACE =
        SQL.get("artifacts.replace");

    // USER SQL

    public static final String SQL_USERS_NEXT_ID =
        SQL.get("users.id.nextval");

    public static final String SQL_USERS_INSERT =
        SQL.get("users.insert");

    public static final String SQL_USERS_SELECT_ID_BY_GID =
        SQL.get("users.select.id.by.gid");

    public static final String SQL_USERS_SELECT_GID =
        SQL.get("users.select.gid");

    public static final String SQL_USERS_DELETE_ID =
        SQL.get("users.delete.id");

    public static final String SQL_USERS_DELETE_COLLECTIONS =
        SQL.get("users.delete.collections");

    public static final String SQL_USERS_SELECT_ALL =
        SQL.get("users.select.all");

    public static final String SQL_USERS_COLLECTIONS =
        SQL.get("users.collections");

    public static final String SQL_USERS_COLLECTION_IDS =
        SQL.get("users.collection.ids");

    public static final String SQL_USERS_DELETE_ALL_COLLECTIONS =
        SQL.get("users.delete.all.collections");

    public static final String SQL_ARTIFACTS_IN_ONLY_COLLECTION_ONLY =
        SQL.get("artifacts.in.one.collection.only");

    public static final String SQL_OUTDATE_ARTIFACTS_COLLECTION =
        SQL.get("outdate.artifacts.collection");

    public static final String SQL_OUTDATE_ARTIFACTS_USER =
        SQL.get("outdate.artifacts.user");

    public static final String SQL_DELETE_USER_COLLECTION_ITEMS =
        SQL.get("delete.user.collection.items");

    public static final String SQL_COLLECTIONS_NEXT_ID =
        SQL.get("collections.id.nextval");

    public static final String SQL_COLLECTIONS_INSERT =
        SQL.get("collections.insert");

    public static final String SQL_COLLECTIONS_SELECT_USER =
        SQL.get("collections.select.user");

    public static final String SQL_COLLECTIONS_SELECT_ALL =
        SQL.get("collections.select.all");

    public static final String SQL_COLLECTIONS_CREATION_TIME =
        SQL.get("collection.creation.time");

    public static final String SQL_COLLECTIONS_ID_BY_GID =
        SQL.get("collections.id.by.gid");

    public static final String SQL_DELETE_COLLECTION_ITEMS =
        SQL.get("delete.collection.items");

    public static final String SQL_DELETE_COLLECTION =
        SQL.get("delete.collection");

    public static final String SQL_COLLECTION_CHECK_ARTIFACT =
        SQL.get("collection.check.artifact");

    public static final String SQL_COLLECTION_ITEMS_ID_NEXTVAL =
        SQL.get("collection.items.id.nextval");

    public static final String SQL_COLLECTION_ITEMS_INSERT =
        SQL.get("collection.items.insert");

    public static final String SQL_COLLECTION_ITEM_GET_ATTRIBUTE =
        SQL.get("collection.item.get.attribute");

    public static final String SQL_COLLECTION_ITEM_SET_ATTRIBUTE =
        SQL.get("collection.item.set.attribute");

    public static final String SQL_COLLECTIONS_TOUCH_BY_GID =
        SQL.get("collections.touch.by.gid");

    public static final String SQL_COLLECTION_ITEM_ID_CID_AID =
        SQL.get("collection.item.id.cid.aid");

    public static final String SQL_COLLECTION_ITEM_OUTDATE_ARTIFACT =
        SQL.get("collection.item.outdate.artifact");

    public static final String SQL_COLLECTION_ITEM_DELETE =
        SQL.get("collection.item.delete");

    public static final String SQL_COLLECTIONS_TOUCH_BY_ID =
        SQL.get("collections.touch.by.id");

    public static final String SQL_COLLECTION_ITEMS_LIST_GID =
        SQL.get("collection.items.list.gid");

    /** The singleton.*/
    protected static Backend instance;

    /**
     * The database cleaner. Reference is stored here because
     * the cleaner is woken up if the backend finds an outdated
     * artifact. This artifact should be removed as soon as
     * possible.
     */
    protected DatabaseCleaner cleaner;

    /**
     * To revive an artifact from the bytes coming from the database
     * we need the artifact factory which references the artifact
     * serializer which is able to do the reviving job.
     */
    protected FactoryLookup   factoryLookup;

    /**
     * Little helper interface to decouple the ArtifactDatabase
     * from the Backend. A ArtifactDatabase should depend on a
     * Backend but a Backend not from an ArtifactDatabase.
     */
    public interface FactoryLookup {

        /**
         * Returns an ArtifactFactory which is bound to a given name.
         * @param factoryName The name of the artifact factory.
         * @return The ArtifactFactory bound to the factory name or
         * null if not matching factory is found.
         */
        ArtifactFactory getArtifactFactory(String factoryName);

    } // interface FactoryLookup

    /**
     * Inner class that brigdes between the persisten form of the
     * artifact and the living one inside the artifact database.
     * After the describe(), feed(), advance() and out() operations
     * of the artifact it must be possible to write to modified artifact
     * back into the database.
     */
    public final class PersistentArtifact
    extends            Id
    {
        private Artifact           artifact;
        private ArtifactSerializer serializer;
        private Long               ttl;

        /**
         * Cronstructor to create a persistent artifact.
         * @param artifact   The living artifact.
         * @param serializer The serializer to store the artifact
         * after the operations.
         * @param ttl The time to life of the artifact.
         * @param id The database id of the artifact.
         */
        public PersistentArtifact(
            Artifact           artifact,
            ArtifactSerializer serializer,
            Long               ttl,
            int                id
        ) {
            super(id);
            this.artifact   = artifact;
            this.serializer = serializer;
            this.ttl        = ttl;
        }

        /**
         * Returns the wrapped living artifact.
         * @return the living artifact.
         */
        public Artifact getArtifact() {
            return artifact;
        }

        /**
         * Returns the serialized which is able to write a
         * modified artifact back into the database.
         * @return The serializer.
         */
        public ArtifactSerializer getSerializer() {
            return serializer;
        }

        /**
         * The time to life of the artifact.
         * @return The time to live.
         */
        public Long getTTL() {
            return ttl;
        }

        /**
         * Stores the living artifact back into the database.
         */
        public void store() {
            if (logger.isDebugEnabled()) {
                logger.debug("storing artifact id = " + getId());
            }
            Backend.this.store(this);
        }

        /**
         * Only touches the access time of the artifact.
         */
        public void touch() {
            if (logger.isDebugEnabled()) {
                logger.debug("touching artifact id = " + getId());
            }
            Backend.this.touch(this);
        }
    } // class ArtifactWithId

    /**
     * Default constructor
     */
    public Backend() {
    }

    /**
     * Constructor to create a backend with a link to the database cleaner.
     * @param cleaner The clean which periodically removes outdated
     * artifacts from the database.
     */
    public Backend(DatabaseCleaner cleaner) {
        this.cleaner = cleaner;
    }


    /**
     * Returns the singleton of this Backend.
     *
     * @return the backend.
     */
    public static synchronized Backend getInstance() {
        if (instance == null) {
            instance = new Backend();
        }

        return instance;
    }

    /**
     * Sets the factory lookup mechanism to decouple ArtifactDatabase
     * and Backend.
     * @param factoryLookup
     */
    public void setFactoryLookup(FactoryLookup factoryLookup) {
        this.factoryLookup = factoryLookup;
    }

    /**
     * Sets the database cleaner explicitly.
     * @param cleaner The database cleaner
     */
    public void setCleaner(DatabaseCleaner cleaner) {
        this.cleaner = cleaner;
    }

    /**
     * Returns a new unique identifier to external identify
     * the artifact across the system. This implementation
     * uses random UUIDs v4 to achieve this target.
     * @return the new identifier
     */
    public String newIdentifier() {
        // TODO: check database for collisions.
        return StringUtils.newUUID();
    }

    public boolean isValidIdentifier(String identifier) {
        return StringUtils.checkUUID(identifier);
    }

    /**
     * Stores a new artifact into the database.
     * @param artifact The artifact to be stored
     * @param factory  The factory which build the artifact
     * @param ttl      The initial time to life of the artifact.
     * @return         A persistent wrapper around the living
     * artifact to be able to write modification later.
     * @throws Exception Thrown if something went wrong with the
     * storage process.
     */
    public PersistentArtifact storeInitially(
        Artifact        artifact,
        ArtifactFactory factory,
        Long            ttl
    )
    throws Exception
    {
        return new PersistentArtifact(
            artifact,
            factory.getSerializer(),
            ttl,
            insertDatabase(artifact, factory, ttl));
    }

    /**
     * Stores an artifact into database if it does not exist there.
     * If it exists there it is only updated.
     * @param artifact The artifact to store/update.
     * @param factory The factory which created the artifact.
     * @param ttl The initial time to live of the artifact.
     * @return A persistent version of the artifact to be able
     * to store a modification later.
     * @throws Exception Thrown if something went wrong during
     * storing/updating.
     */
    public PersistentArtifact storeOrReplace(
        Artifact        artifact,
        ArtifactFactory factory,
        Long            ttl
    )
    throws Exception
    {
        return new PersistentArtifact(
            artifact,
            factory.getSerializer(),
            ttl,
            storeOrReplaceDatabase(artifact, factory, ttl));
    }

    /**
     * Implementors of this interface are able to process the raw
     * artifact data from the database for loading.
     */
    public interface ArtifactLoader {

        /**
         * Creates a custom object from the raw artifact database data.
         * @param factory The factory that created this artifact.
         * @param ttl The current time to life of the artifact.
         * @param bytes The raw artifact bytes from the database.
         * @param id The database id of the artifact.
         * @return The custom object created by the implementation.
         */
        Object load(ArtifactFactory factory, Long ttl, byte [] bytes, int id);

    } // interface ArtifactLoader

    /**
     * Fetches an artifact from the database identified by the
     * given identifier.
     * @param identifer The identifier of the artifact.
     * @return A persistent wrapper around the found artifact
     * to be able to write back a modifaction later or null
     * if no artifact is found for this identifier.
     */
    public PersistentArtifact getArtifact(String identifer) {

        return (PersistentArtifact)loadArtifact(
            identifer,
            new ArtifactLoader() {

                public Object load(
                    ArtifactFactory factory,
                    Long            ttl,
                    byte []         bytes,
                    int             id
                ) {
                    ArtifactSerializer serializer = factory.getSerializer();

                    Artifact artifact = serializer.fromBytes(bytes);

                    return artifact == null
                        ? null
                        : new PersistentArtifact(artifact, serializer, ttl, id);
                }
            });
    }

    /**
     * More general loading mechanism for artifacts. The concrete
     * load processing is delegated to the given loader.
     * @param identifer The identifier of the artifact.
     * @param loader The loader which processes the raw database data.
     * @return The object created by the loader.
     */
    public Object loadArtifact(
        final String         identifer,
        final ArtifactLoader loader
    ) {
        if (!isValidIdentifier(identifer)) {
            return null;
        }

        final Object [] loaded = new Object[1];

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_LOAD_BY_GID);
                stmnt.setString(1, identifer);

                result = stmnt.executeQuery();

                if (!result.next()) {
                    return false;
                }

                int  id   = result.getInt(1);
                long ttlX = result.getLong(3);

                Long ttl = result.wasNull() ? null : Long.valueOf(ttlX);

                if (ttl != null) { // real time to life
                    long last_access = result.getTimestamp(2).getTime();
                    if (last_access + ttlX < System.currentTimeMillis()) {
                        artifactOutdated(id);
                        return false;
                    }
                }

                String factoryName = result.getString(4);

                if (factoryLookup == null) {
                    logger.error("factory lookup == null");
                    return false;
                }

                ArtifactFactory factory = factoryLookup
                    .getArtifactFactory(factoryName);

                if (factory == null) {
                    logger.error("factory '" + factoryName + "' not found");
                    return false;
                }

                byte [] bytes = result.getBytes(5);

                loaded[0] = loader.load(factory, ttl, bytes, id);
                return true;
            }
        };

        return exec.runRead() ? loaded[0] : null;
    }

    /**
     * Called if the load mechanism found an outdated artifact.
     * It  wakes up the database cleaner.
     * @param id The id of the outdated artifact.
     */
    protected void artifactOutdated(int id) {
        if (logger.isDebugEnabled()) {
            logger.info("artifactOutdated: id = " + id);
        }
        if (cleaner != null) {
            cleaner.wakeup();
        }
    }

    public Artifact reviveArtifact(String factoryName, byte [] bytes) {
        if (factoryLookup == null) {
            logger.error("reviveArtifact: factory lookup == null");
            return null;
        }
        ArtifactFactory factory = factoryLookup
            .getArtifactFactory(factoryName);

        if (factory == null) {
            logger.error(
                "reviveArtifact: no factory '" + factoryName + "' found");
            return null;
        }

        ArtifactSerializer serializer = factory.getSerializer();

        return serializer.fromBytes(bytes);
    }

    /**
     * Internal method to store/replace an artifact inside the database.
     * If an artifact with the given identifier does not exists it is
     * created else only the content data is updated.
     * @param artifact The artifact to be store/update inside the database.
     * @param factory The factory that created the artifact.
     * @param ttl The initial time to life of the artifact.
     * @return The database id of the stored/updated artifact.
     */
    protected int storeOrReplaceDatabase(
        final Artifact        artifact,
        final ArtifactFactory factory,
        final Long            ttl
    ) {
        final String uuid = artifact.identifier();

        if (!isValidIdentifier(uuid)) {
            throw new RuntimeException("No valid UUID");
        }

        final int [] id = new int[1];

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {

                prepareStatement(SQL_GET_ID);
                stmnt.setString(1, uuid);
                result = stmnt.executeQuery();

                Integer ID = result.next()
                    ? Integer.valueOf(result.getInt(1))
                    : null;

                reset();

                if (ID != null) { // already in database
                    prepareStatement(SQL_REPLACE);

                    if (ttl == null) {
                        stmnt.setNull(1, Types.BIGINT);
                    }
                    else {
                        stmnt.setLong(1, ttl.longValue());
                    }

                    stmnt.setString(2, factory.getName());
                    stmnt.setBytes(
                        3,
                        factory.getSerializer().toBytes(artifact));
                    id[0] = ID.intValue();
                    stmnt.setInt(4, id[0]);
                }
                else { // new artifact
                    prepareStatement(SQL_NEXT_ID);
                    result = stmnt.executeQuery();

                    if (!result.next()) {
                        logger.error("No id generated");
                        return false;
                    }

                    reset();

                    prepareStatement(SQL_INSERT);

                    id[0] = result.getInt(1);
                    stmnt.setInt(1, id[0]);
                    stmnt.setString(2, uuid);
                    if (ttl == null) {
                        stmnt.setNull(3, Types.BIGINT);
                    }
                    else {
                        stmnt.setLong(3, ttl.longValue());
                    }

                    stmnt.setString(4, factory.getName());

                    stmnt.setBytes(
                        5,
                        factory.getSerializer().toBytes(artifact));
                }
                stmnt.execute();
                conn.commit();
                return true;
            }
        };

        if (!exec.runWrite()) {
            throw new RuntimeException("failed insert artifact into database");
        }

        return id[0];
    }

    /**
     * Internal method to store an artifact inside the database.
     * @param artifact The artifact to be stored.
     * @param factory The factory which created the artifact.
     * @param ttl The initial time to live of the artifact.
     * @return The database id of the stored artifact.
     */
    protected int insertDatabase(
        final Artifact        artifact,
        final ArtifactFactory factory,
        final Long            ttl
    ) {
        final int [] id = new int[1];

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_NEXT_ID);
                result = stmnt.executeQuery();

                if (!result.next()) {
                    logger.error("No id generated");
                    return false;
                }

                id[0] = result.getInt(1);

                reset();
                prepareStatement(SQL_INSERT);

                String uuid = artifact.identifier();
                stmnt.setInt(1, id[0]);
                stmnt.setString(2, uuid);
                if (ttl == null) {
                    stmnt.setNull(3, Types.BIGINT);
                }
                else {
                    stmnt.setLong(3, ttl.longValue());
                }

                stmnt.setString(4, factory.getName());

                stmnt.setBytes(
                    5,
                    factory.getSerializer().toBytes(artifact));

                stmnt.execute();

                conn.commit();
                return true;
            }
        };

        if (!exec.runWrite()) {
            throw new RuntimeException("failed insert artifact into database");
        }

        return id[0];
    }

    /**
     * Touches the access timestamp of a given artifact to prevent
     * that it will be removed from the database by the database cleaner.
     * @param artifact The persistent wrapper around the living artifact.
     */
    public void touch(final PersistentArtifact artifact) {
        new SQLExecutor() {
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_TOUCH);
                stmnt.setInt(1, artifact.getId());
                stmnt.execute();
                conn.commit();
                return true;
            }
        }.runWrite();
    }

    /**
     * Writes modification of an artifact back to the database.
     * @param artifact The persistent wrapper around a living
     * artifact.
     */
    public void store(final PersistentArtifact artifact) {
        new SQLExecutor() {
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_UPDATE);
                stmnt.setInt(2, artifact.getId());

                byte [] bytes = artifact
                    .getSerializer()
                    .toBytes(artifact.getArtifact());

                stmnt.setBytes(1, bytes);
                stmnt.execute();
                conn.commit();
                return true;
            }
        }.runWrite();
    }

    public User createUser(
        final String      name, 
        final Document    role,
        final UserFactory factory,
        final Object      context
    ) {
        final User [] user = new User[1];

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {

                prepareStatement(SQL_USERS_NEXT_ID);
                result = stmnt.executeQuery();

                if (!result.next()) {
                    return false;
                }

                int id = result.getInt(1);

                reset();

                String identifier = newIdentifier();

                prepareStatement(SQL_USERS_INSERT);

                stmnt.setInt(1, id);
                stmnt.setString(2, identifier);
                stmnt.setString(3, name);

                byte [] roleData = role == null
                    ? null
                    : XMLUtils.toByteArray(role);

                if (roleData == null) {
                    stmnt.setNull(4, Types.BIGINT);
                }
                else {
                    stmnt.setBytes(4, roleData);
                }

                stmnt.execute();
                conn.commit();

                user[0] = factory.createUser(
                    identifier, name, role, context);
                return true;
            }
        };

        return exec.runWrite() ? user[0] : null;
    }

    public boolean deleteUser(final String identifier) {

        if (!isValidIdentifier(identifier)) {
            return false;
        }

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_USERS_SELECT_ID_BY_GID);

                stmnt.setString(1, identifier);
                result = stmnt.executeQuery();

                if (!result.next()) { // No such user
                    return false;
                }

                int id = result.getInt(1);

                reset();

                // outdate the artifacts exclusively used by the user

                prepareStatement(SQL_OUTDATE_ARTIFACTS_USER);
                stmnt.setInt(1, id);
                stmnt.setInt(2, id);
                stmnt.execute();

                reset();

                // delete the collection items of the user

                prepareStatement(SQL_DELETE_USER_COLLECTION_ITEMS);
                stmnt.setInt(1, id);
                stmnt.execute();
                
                reset();

                // delete the collections of the user

                prepareStatement(SQL_USERS_DELETE_COLLECTIONS);
                stmnt.setInt(1, id);
                stmnt.execute();

                reset();

                // delete the user

                prepareStatement(SQL_USERS_DELETE_ID);
                stmnt.setInt(1, id);
                stmnt.execute();

                conn.commit();
                return true;
            }
        };

        return exec.runWrite();
    }

    public User getUser(
        final String      identifier, 
        final UserFactory factory,
        final Object      context
    ) {
        if (!isValidIdentifier(identifier)) {
            logger.debug("Invalid UUID: '" + identifier + "'");
            return null;
        }

        final User [] user = new User[1];

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_USERS_SELECT_GID);
                stmnt.setString(1, identifier);
                result = stmnt.executeQuery();
                if (!result.next()) { // no such user
                    return false;
                }
                // omit id
                String  name     = result.getString(2);
                byte [] roleData = result.getBytes(3);

                Document role = XMLUtils.fromByteArray(roleData);

                user[0] = factory.createUser(
                    identifier, name, role, context);
                return true;
            }
        };

        return exec.runRead() ? user[0] : null;
    }

    public User [] getUsers(
        final UserFactory factory, 
        final Object      context
    ) {
        final ArrayList<User> users = new ArrayList<User>();

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_USERS_SELECT_ALL);
                result = stmnt.executeQuery();

                while (result.next()) {
                    // omit id
                    String  identifier = result.getString(2);
                    String  name       = result.getString(3);
                    byte [] roleData   = result.getBytes(4);

                    Document role = XMLUtils.fromByteArray(roleData);
                    User user = factory.createUser(
                        identifier, name, role, context);
                    users.add(user);
                }
                return true;
            }
        };

        return exec.runRead()
            ? users.toArray(new User[users.size()])
            : null;
    }

    public ArtifactCollection createCollection(
        final String                    ownerIdentifier, 
        final String                    name,
        final ArtifactCollectionFactory factory,
        final Document                  attribute,
        final Object                    context
    ) {
        if (name == null) {
            logger.debug("Name is null");
            return null;
        }

        if (!isValidIdentifier(ownerIdentifier)) {
            logger.debug("Invalid owner id: '" + ownerIdentifier + "'");
            return null;
        }

        final ArtifactCollection [] collection =
            new ArtifactCollection[1];

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                // fetch owner id
                prepareStatement(SQL_USERS_SELECT_ID_BY_GID);
                stmnt.setString(1, ownerIdentifier);
                result = stmnt.executeQuery();

                if (!result.next()) { // no such user
                    return false;
                }

                int ownerId = result.getInt(1);
                reset();

                // fetch new collection seq number.
                prepareStatement(SQL_COLLECTIONS_NEXT_ID);
                result = stmnt.executeQuery();

                if (!result.next()) { // no identifier generated
                    return false;
                }

                int id = result.getInt(1);
                reset();

                String identifier = newIdentifier();

                prepareStatement(SQL_COLLECTIONS_INSERT);

                stmnt.setInt(1, id);
                stmnt.setString(2, identifier);
                stmnt.setString(3, name);
                stmnt.setInt(4, ownerId);

                // XXX: A bit odd: we don't have a collection, yet.
                Long ttl = factory.timeToLiveUntouched(null, context);

                if (ttl == null) {
                    stmnt.setNull(5, Types.BIGINT);
                }
                else {
                    stmnt.setLong(5, ttl);
                }

                byte [] data = XMLUtils.toByteArray(attribute);

                if (data == null) {
                    stmnt.setNull(6, Types.BINARY);
                }
                else {
                    stmnt.setBytes(6, data);
                }

                stmnt.execute();
                conn.commit();

                reset();

                // fetch creation time from database
                // done this way to use the time system
                // of the database.

                prepareStatement(SQL_COLLECTIONS_CREATION_TIME);
                stmnt.setInt(1, id);

                result = stmnt.executeQuery();

                Date creationTime = null;

                if (result.next()) {
                    Timestamp timestamp = result.getTimestamp(1);
                    creationTime = new Date(timestamp.getTime());
                }

                collection[0] = factory.createCollection(
                    identifier, name, creationTime, attribute, context);

                return true;
            }
        };

        return exec.runWrite() ? collection[0]: null;
    }

    public ArtifactCollection [] listCollections(
        final String                    ownerIdentifier,
        final Document                  data,
        final ArtifactCollectionFactory collectionFactory,
        final UserFactory               userFactory,
        final Object                    context
    ) {
        if (ownerIdentifier != null 
        && !isValidIdentifier(ownerIdentifier)) {
            logger.debug("Invalid owner id: '" + ownerIdentifier + "'");
            return null;
        }

        final ArrayList<ArtifactCollection> collections =
            new ArrayList<ArtifactCollection>();

        SQLExecutor exec = new SQLExecutor() {

            public boolean doIt() throws SQLException {

                if (ownerIdentifier != null) {
                    prepareStatement(SQL_COLLECTIONS_SELECT_USER);
                    stmnt.setString(1, ownerIdentifier);
                }
                else {
                    prepareStatement(SQL_COLLECTIONS_SELECT_ALL);
                }

                result = stmnt.executeQuery();

                while (result.next()) {
                    String collectionIdentifier = result.getString(1);
                    String collectionName       = result.getString(2);
                    Date   creationTime         =
                        new Date(result.getTimestamp(3).getTime());
                    String userIdentifier       = result.getString(4);

                    ArtifactCollection collection =
                        collectionFactory.createCollection(
                            collectionIdentifier,
                            collectionName,
                            creationTime,
                            data,
                            context);

                    if (userIdentifier != null) {
                        collection.setUser(new LazyBackendUser(
                            userIdentifier, userFactory, Backend.this, context));
                    }

                    collections.add(collection);
                }
                return true;
            }
        };

        return exec.runRead()
            ? collections.toArray(new ArtifactCollection[collections.size()])
            : null;
    }


    public boolean deleteCollection(final String collectionId) {
        if (!isValidIdentifier(collectionId)) {
            logger.debug("Invalid collection id: '" + collectionId + "'");
            return false;
        }
        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                // fetch collection id
                prepareStatement(SQL_COLLECTIONS_ID_BY_GID);
                stmnt.setString(1, collectionId);
                result = stmnt.executeQuery();
                if (!result.next()) {
                    logger.debug("No such collection: " + collectionId);
                    return false;
                }
                int id = result.getInt(1);
                reset();

                // outdate artifacts that are only in this collection
                prepareStatement(SQL_OUTDATE_ARTIFACTS_COLLECTION);
                stmnt.setInt(1, id);
                stmnt.setInt(2, id);
                stmnt.execute();
                reset();

                // delete the collection items
                prepareStatement(SQL_DELETE_COLLECTION_ITEMS);
                stmnt.setInt(1, id);
                stmnt.execute();
                reset();

                // delete the collection
                prepareStatement(SQL_DELETE_COLLECTION);
                stmnt.setInt(1, id);
                stmnt.execute();
                conn.commit();
                return true;
            }
        };
        return exec.runWrite();
    }

    public Document getCollectionAttribute(
        final String collectionId,
        final String artifactId
    ) {
        if (!isValidIdentifier(collectionId)) {
            logger.debug("collection id is not valid: " + collectionId);
            return null;
        }
        if (!isValidIdentifier(artifactId)) {
            logger.debug("artifact id is not valid: " + artifactId);
            return null;
        }

        final Document [] document = new Document[1];

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_COLLECTION_ITEM_GET_ATTRIBUTE);
                stmnt.setString(1, collectionId);
                stmnt.setString(2, artifactId);
                result = stmnt.executeQuery();
                if (!result.next()) {
                    logger.debug("No such collection item");
                    return false;
                }
                document[0] = XMLUtils.fromByteArray(result.getBytes(1));
                return true;
            }
        };

        return exec.runRead() ? document[0] : null;
    }

    public boolean setCollectionAttribute(
        final String   collectionId, 
        final String   artifactId,
        Document       attribute
    ) {
        if (!isValidIdentifier(collectionId)) {
            logger.debug("collection id is not valid: " + collectionId);
            return false;
        }
        if (!isValidIdentifier(artifactId)) {
            logger.debug("artifact id is not valid: " + artifactId);
            return false;
        }

        final byte [] data = XMLUtils.toByteArray(attribute);

        return new SQLExecutor() {
            public boolean doIt() throws SQLException {

                // set the column in collection items
                prepareStatement(SQL_COLLECTION_ITEM_SET_ATTRIBUTE);
                if (data == null) {
                    stmnt.setNull(1, Types.BINARY);
                }
                else {
                    stmnt.setBytes(1, data);
                }
                stmnt.setString(2, collectionId);
                stmnt.setString(3, artifactId);
                stmnt.execute();
                reset();

                // touch the collection
                prepareStatement(SQL_COLLECTIONS_TOUCH_BY_GID);
                stmnt.setString(1, collectionId);
                stmnt.execute();

                conn.commit();
                return true;
            }
        }.runWrite();
    }

    public boolean addCollectionArtifact(
        final String   collectionId,
        final String   artifactId,
        final Document attribute
    ) {
        if (!isValidIdentifier(collectionId)) {
            logger.debug("Invalid collection id: '" + collectionId + "'");
            return false;
        }

        if (!isValidIdentifier(artifactId)) {
            logger.debug("Invalid artifact id: '" + artifactId + "'");
            return false;
        }

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                // fetch artifact id
                prepareStatement(SQL_GET_ID);
                stmnt.setString(1, artifactId);
                result = stmnt.executeQuery();
                if (!result.next()) {
                    logger.debug("No such artifact: " + artifactId);
                    return false;
                }
                int aid = result.getInt(1);
                reset();

                // fetch collection id
                prepareStatement(SQL_COLLECTIONS_ID_BY_GID);
                stmnt.setString(1, collectionId);
                result = stmnt.executeQuery();
                if (!result.next()) {
                    logger.debug("No such collection: " + collectionId);
                }
                int cid = result.getInt(1);
                reset();

                // check if artifact is already in collection
                prepareStatement(SQL_COLLECTION_CHECK_ARTIFACT);
                stmnt.setInt(1, aid);
                stmnt.setInt(2, cid);
                result = stmnt.executeQuery();
                if (result.next()) {
                    logger.debug("artifact already in collection");
                    return false;
                }
                reset();

                // fetch fresh id for new collection item
                prepareStatement(SQL_COLLECTION_ITEMS_ID_NEXTVAL);
                result = stmnt.executeQuery();
                if (!result.next()) {
                    logger.debug("no collection item id generated");
                    return false;
                }
                int ci_id = result.getInt(1);
                reset();

                // insert new collection item
                prepareStatement(SQL_COLLECTION_ITEMS_INSERT);
                stmnt.setInt(1, ci_id);
                stmnt.setInt(2, cid);
                stmnt.setInt(3, aid);

                byte [] data = attribute != null
                    ? XMLUtils.toByteArray(attribute)
                    : null;

                if (data == null) {
                    stmnt.setNull(4, Types.BINARY);
                }
                else {
                    stmnt.setBytes(4, data);
                }
                stmnt.execute();
                conn.commit();

                return true;
            }
        };
        return exec.runWrite();
    }

    public boolean removeCollectionArtifact(
        final String collectionId,
        final String artifactId
    ) {
        if (!isValidIdentifier(collectionId)) {
            logger.debug("Invalid collection id: '" + collectionId + "'");
            return false;
        }
        return new SQLExecutor() {
            public boolean doIt() throws SQLException {

                // fetch id, collection id and artitfact id
                prepareStatement(SQL_COLLECTION_ITEM_ID_CID_AID);
                stmnt.setString(1, collectionId);
                stmnt.setString(2, artifactId);
                result = stmnt.executeQuery();
                if (!result.next()) {
                    logger.debug("No such collection item");
                    return false;
                }
                int  id = result.getInt(1);
                int cid = result.getInt(2);
                int aid = result.getInt(3);
                reset();

                // outdate artifact iff it is only in this collection
                prepareStatement(SQL_COLLECTION_ITEM_OUTDATE_ARTIFACT);
                stmnt.setInt(1, aid);
                stmnt.setInt(2, cid);
                stmnt.setInt(3, aid);
                stmnt.execute();
                reset();

                // delete collection item
                prepareStatement(SQL_COLLECTION_ITEM_DELETE);
                stmnt.setInt(1, id);
                stmnt.execute();
                reset();

                // touch collection
                prepareStatement(SQL_COLLECTIONS_TOUCH_BY_ID);
                stmnt.setInt(1, cid);
                stmnt.execute();

                conn.commit();
                return true;
            }
        }.runWrite();
    }

    public CollectionItem [] listCollectionArtifacts(
        final String collectionId
    ) {
        if (!isValidIdentifier(collectionId)) {
            logger.debug("Invalid collection id: '" + collectionId + "'");
            return null;
        }

        final ArrayList<CollectionItem> collectionItems =
            new ArrayList<CollectionItem>();

        SQLExecutor exec = new SQLExecutor() {
            public boolean doIt() throws SQLException {
                prepareStatement(SQL_COLLECTION_ITEMS_LIST_GID);
                stmnt.setString(1, collectionId);
                result = stmnt.executeQuery();
                while (result.next()) {
                    CollectionItem item = new DefaultCollectionItem(
                        result.getString(1),
                        result.getBytes(2));
                    collectionItems.add(item);
                }
                return true;
            }
        };

        return exec.runRead()
            ? collectionItems.toArray(
                new CollectionItem[collectionItems.size()])
            : null;
    }
}
// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 :