/*
 * $Header: /esup-portail/injac/applications/SlideServer/Packages/esup-serveur-WebDav-3/update/slide/add-ons/stores/uPortalStore/src/stores/org/apache/slide/store/impl/rdbms/PostgresRDBMSAdapter.java,v 1.1 2005/02/08 09:23:45 tbellemb Exp $
 * $Revision: 1.1 $
 * $Date: 2005/02/08 09:23:45 $
 *
 * ====================================================================
 *
 * Copyright 1999-2003 The Apache Software Foundation 
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 */

package org.apache.slide.store.impl.rdbms;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.slide.common.Service;
import org.apache.slide.common.ServiceAccessException;
import org.apache.slide.common.Uri;
import org.apache.slide.content.NodeRevisionDescriptor;
import org.apache.slide.content.NodeRevisionNumber;
import org.apache.slide.lock.LockTokenNotFoundException;
import org.apache.slide.lock.NodeLock;
import org.apache.slide.macro.ConflictException;
import org.apache.slide.security.NodePermission;
import org.apache.slide.structure.ObjectNode;
import org.apache.slide.structure.ObjectNotFoundException;
import org.apache.slide.util.logger.Logger;

/**
 * Adapter for Postgres 7.3/7.4.
 * 
 * The delete statements differ from StandardRDBMSAdapter
 * as Postgres does not understand "DELETE table FROM ...".
 *  
 * The Postgres driver for version 7.4.1 will alway load
 * the content into memory. This will be a problem for 
 * large documents.
 * 
 * @version $Revision: 1.1 $
 */

public class PostgresRDBMSAdapter extends StandardRDBMSAdapter implements SequenceAdapter {

	protected static String LOG_CHANNEL =
		PostgresRDBMSAdapter.class.getName();

    protected static String normalizeSequenceName(String sequenceName) {
        return sequenceName.replace('-', '_').toUpperCase() + "_SEQ";
    }

	public PostgresRDBMSAdapter(Service service, Logger logger) {
		super(service, logger);
	}

	public void removeObject(Connection connection, Uri uri, ObjectNode object)
		throws ServiceAccessException, ObjectNotFoundException {
		PreparedStatement statement = null;
		try {

			clearBinding(connection, uri);

			// delete links
			try {
				statement =
					connection.prepareStatement(
						"delete from LINKS where LINKS.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
				statement.setString(1, uri.toString());
				statement.executeUpdate();
			} finally {
				close(statement);
			}
			// delete version history
			// FIXME: Is this true??? Should the version history be removed if the object is removed???
			try {
				statement =
					connection.prepareStatement(
						"delete from VERSION_HISTORY  where VERSION_HISTORY.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
				statement.setString(1, uri.toString());
				statement.executeUpdate();
			} finally {
				close(statement);
			}
			// delete version
			try {
				statement =
					connection.prepareStatement(
						"delete from VERSION where VERSION.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
				statement.setString(1, uri.toString());
				statement.executeUpdate();
			} finally {
				close(statement);
			}
			// delete the object itself
			try {
				statement =
					connection.prepareStatement(
						"delete from OBJECT  where OBJECT.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
				statement.setString(1, uri.toString());
				statement.executeUpdate();
			} finally {
				close(statement);
			}
			// finally delete the uri
			try {
				statement =
					connection.prepareStatement(
						"delete from URI where URI_STRING = ?");
				statement.setString(1, uri.toString());
				statement.executeUpdate();
			} finally {
				close(statement);
			}
		} catch (SQLException e) {
			getLogger().log(e, LOG_CHANNEL, Logger.ERROR);
			throw new ServiceAccessException(service, e);
		}
	}

	public void removeRevisionContent(
		Connection connection,
		Uri uri,
		NodeRevisionDescriptor revisionDescriptor)
		throws ServiceAccessException {
		try {
			PreparedStatement statement = null;
			try {
				statement =
					connection.prepareStatement(
						"delete from VERSION_CONTENT where VERSION_CONTENT.VERSION_ID = VERSION_HISTORY.VERSION_ID and VERSION_HISTORY.REVISION_NO = ? and VERSION_HISTORY.URI_ID=URI.URI_ID AND URI.URI_STRING=?");
				statement.setString(
					1,
					revisionDescriptor.getRevisionNumber().toString());
				statement.setString(2, uri.toString());
				statement.executeUpdate();
			} finally {
				close(statement);
			}
		} catch (Exception e) {
			getLogger().log(e, LOG_CHANNEL, Logger.ERROR);
			throw new ServiceAccessException(service, e);
		}
	}

	public void removeRevisionDescriptor(
		Connection connection,
		Uri uri,
		NodeRevisionNumber revisionNumber)
		throws ServiceAccessException {
		PreparedStatement statement = null;
		try {
			try {
				statement =
					connection.prepareStatement(
						"delete from VERSION_LABELS where VERSION_LABELS.VERSION_ID = VERSION_HISTORY.VERSION_ID and VERSION_HISTORY.REVISION_NO = ? and VERSION_HISTORY.URI_ID = URI.URI_ID AND URI.URI_STRING = ?");
				statement.setString(1, revisionNumber.toString());
				statement.setString(2, uri.toString());
				statement.executeUpdate();
			} finally {
				close(statement);
			}
			try {
				statement =
					connection.prepareStatement(
						"delete from PROPERTIES where PROPERTIES.VERSION_ID = VERSION_HISTORY.VERSION_ID and VERSION_HISTORY.REVISION_NO = ? and VERSION_HISTORY.URI_ID = URI.URI_ID AND URI.URI_STRING = ?");
				statement.setString(1, revisionNumber.toString());
				statement.setString(2, uri.toString());
				statement.executeUpdate();
			} finally {
				close(statement);
			}
		} catch (SQLException e) {
			getLogger().log(e, LOG_CHANNEL, Logger.ERROR);
			throw new ServiceAccessException(service, e);
		}
	}

    public void removeRevisionDescriptors(Connection connection, Uri uri) throws ServiceAccessException {
        PreparedStatement statement = null;
        try {
            
            statement =
                connection.prepareStatement(
                "delete from VERSION_PREDS where VERSION_PREDS.VERSION_ID = VERSION_HISTORY.VERSION_ID and VERSION_HISTORY.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
            statement.setString(1, uri.toString());
            statement.executeUpdate();
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        } finally {
            close(statement);
        }
    }

	public void removeLock(Connection connection, Uri uri, NodeLock lock)
		throws ServiceAccessException, LockTokenNotFoundException {
		PreparedStatement statement = null;
		try {
			try {
				statement =
					connection.prepareStatement(
						"delete from LOCKS where LOCK_ID = URI.URI_ID and URI.URI_STRING=?");
				statement.setString(1, lock.getLockId());
				statement.executeUpdate();
			} finally {
				close(statement);
			}
			try {
				statement =
					connection.prepareStatement(
						"delete from URI where URI_STRING=?");
				statement.setString(1, lock.getLockId());
            statement.executeUpdate();
			} finally {
				close(statement);
			}
		} catch (SQLException e) {
			getLogger().log(e, LOG_CHANNEL, Logger.ERROR);
			throw new ServiceAccessException(service, e);
		}
	}

	public void revokePermission(
		Connection connection,
		Uri uri,
		NodePermission permission)
		throws ServiceAccessException {
        if (permission == null) return;
		PreparedStatement statement = null;
		try {
			NodeRevisionNumber revisionNumber = permission.getRevisionNumber();
			statement =
				connection.prepareStatement(
					"delete from PERMISSIONS, URI ou, URI su, URI au where OBJECT_ID = ou.URI_ID and ou.URI_STRING = ? and SUBJECT_ID = su.URI_ID and su.URI_STRING = ? and ACTION_ID = au.URI_ID and au.URI_STRING = ? and VERSION_NO = ?");
			statement.setString(1, permission.getObjectUri());
			statement.setString(2, permission.getSubjectUri());
			statement.setString(3, permission.getActionUri());
            statement.setString(3, revisionNumber.toString());
			statement.executeUpdate();
		} catch (SQLException e) {
			getLogger().log(e, LOG_CHANNEL, Logger.ERROR);
			throw new ServiceAccessException(service, e);
		} finally {
			close(statement);
		}
	}

	public void revokePermissions(Connection connection, Uri uri)
		throws ServiceAccessException {
		PreparedStatement statement = null;
		try {
			statement =
				connection.prepareStatement(
					"delete from PERMISSIONS where PERMISSIONS.OBJECT_ID = URI.URI_ID and URI.URI_STRING = ?");
			statement.setString(1, uri.toString());
			statement.executeUpdate();
		} catch (SQLException e) {
			getLogger().log(e, LOG_CHANNEL, Logger.ERROR);
			throw new ServiceAccessException(service, e);
		} finally {
			close(statement);
		}
	}

	protected void clearBinding(Connection connection, Uri uri)
		throws ServiceAccessException, ObjectNotFoundException, SQLException {
		PreparedStatement statement = null;

		// clear this uri from having bindings and being bound 

		// getLogger().log("Clear bindings for " + uri.toString(),LOG_CHANNEL,Logger.INFO);  

		try {
			statement =
				connection.prepareStatement(
					"delete from BINDING where (BINDING.URI_ID = URI.URI_ID and URI.URI_STRING = ?) ");
			//or (BINDING.CHILD_UURI_ID = URI.URI_ID and URI.URI_STRING = ?");
			statement.setString(1, uri.toString());
			//statement.setString(2, uri.toString());
			statement.executeUpdate();
		} finally {
			close(statement);
		}

		try {
			statement =
				connection.prepareStatement(
					"delete from PARENT_BINDING where PARENT_BINDING.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
			//  or PARENT_BINDING.PARENT_UURI_ID = URI.URI_ID and URI.URI_STRING = ?");
			statement.setString(1, uri.toString());
			//statement.setString(2, uri.toString());
			statement.executeUpdate();
		} finally {
			close(statement);
		}
	}

	protected ServiceAccessException createException(
		SQLException e,
		String uri) {
		/*  For Postgresql error states see  http://developer.postgresql.org/docs/postgres/errcodes-appendix.html
		 */

		String sqlstate = e.getSQLState();
		
        if (sqlstate != null) {
            if (sqlstate.startsWith("23")) {
                getLogger().log(e.getErrorCode() + ": Deadlock resolved on " + uri,
                    LOG_CHANNEL, Logger.WARNING);
                return new ServiceAccessException(service, new ConflictException(uri));
            } else if (sqlstate.startsWith("40")) {
                getLogger().log(
                    e.getErrorCode() + ": Deadlock resolved on " + uri,
                    LOG_CHANNEL,
                    Logger.WARNING);
                return new ServiceAccessException(service, new ConflictException(uri));
            } else {
                getLogger().log(
                    "SQL error "
                        + e.getErrorCode()
                        + " on "
                        + uri
                        + ": "
                        + e.getMessage(),
                    LOG_CHANNEL,
                    Logger.ERROR);

                return new ServiceAccessException(service, e);
            }
        }
        else {
            return super.createException(e, uri);
        }
	}

    public boolean isSequenceSupported(Connection conn) {
        return true;
    }

    public boolean createSequence(Connection conn, String sequenceName) throws ServiceAccessException {

        String query = "CREATE SEQUENCE " + normalizeSequenceName(sequenceName);

        PreparedStatement statement = null;

        try {
            statement = conn.prepareStatement(query);
            statement.executeUpdate();
            return true;
        } catch (SQLException e) {
            throw new ServiceAccessException(service, e);
        } finally {
            close(statement);
        }

    }

    public long nextSequenceValue(Connection conn, String sequenceName) throws ServiceAccessException {
        String selectQuery = "SELECT nextval('" + normalizeSequenceName(sequenceName)+"');";

        PreparedStatement selectStatement = null;
        ResultSet res = null;

        try {
            selectStatement = conn.prepareStatement(selectQuery);
            res = selectStatement.executeQuery();
            if (!res.next()) {
                throw new ServiceAccessException(service, "Could not increment sequence " + sequenceName);
            }
            long value = res.getLong(1);
            return value;
        } catch (SQLException e) {
            throw new ServiceAccessException(service, e);
        } finally {
            close(selectStatement, res);
        }
    }

    public boolean sequenceExists(Connection conn, String sequenceName) throws ServiceAccessException {

        PreparedStatement selectStatement = null;
        ResultSet res = null;

        try {
            selectStatement = conn.prepareStatement("SELECT * FROM " + normalizeSequenceName(sequenceName));
            res = selectStatement.executeQuery();
            return true;
        } catch (SQLException e) {
            return false;
        } finally {
            close(selectStatement, res);
        }
    }
    
    protected String convertRevisionNumberToComparable(String revisioNumber) {
        return "cast(split_part("
            + revisioNumber
            + ", '.', 1) as numeric), cast(split_part("
            + revisioNumber
            + ", '.', 2) as numeric)";
    }

}
