/*
 * $Header: /home/cvspublic/jakarta-slide/src/stores/org/apache/slide/store/impl/rdbms/PostgresRDBMSAdapter.java,v 1.20 2005/06/13 12:35:07 unico Exp $
 * $Revision: 1.20 $
 * $Date: 2005/06/13 12:35:07 $
 *
 * ====================================================================
 *
 * Copyright 1999-2005 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.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.20 $
 */

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;
        final StringBuffer sql = new StringBuffer("delete from PERMISSIONS where (OBJECT_ID, SUBJECT_ID, ACTION_ID) IN" +
                " (SELECT ou.URI_ID, su.URI_ID, au.URI_ID FROM URI ou, URI su, URI au WHERE ou.URI_STRING = ? and su.URI_STRING = ? and au.URI_STRING = ?)");
		PreparedStatement statement = null;
		try {
            final NodeRevisionNumber revisionNumber = permission.getRevisionNumber();
            // generate proper sql based on content of revision number
            if (revisionNumber != null) {
                sql.append(" and VERSION_NO = ?");
            } else {
                sql.append(" and VERSION_NO IS NULL");
            }
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, uri.toString());
            statement.setString(2, permission.getSubjectUri());
            statement.setString(3, permission.getActionUri());
            if (revisionNumber != null) {
                statement.setString(4, revisionNumber.toString());
            }
            statement.executeUpdate();
		} catch (SQLException e) {
            throw createException(e, uri.toString());
		} 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);
		}
	}

    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)";
    }

}
