/*
 * The contents of this file are subject to the Mozilla Public License Version 1.1
 * (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.mozilla.org/MPL/>.
 * 
 * Software distributed under the License is distributed on an "AS IS" basis, WITHOUT
 * WARRANTY OF ANY KIND, either express or implied. See the License for the specific
 * language governing rights and limitations under the License.
 * 
 * The Original Code is the Venice Web Communities System.
 * 
 * The Initial Developer of the Original Code is Eric J. Bowersox <erbo@silcom.com>,
 * for Silverwrist Design Studios.  Portions created by Eric J. Bowersox are
 * Copyright (C) 2002 Eric J. Bowersox/Silverwrist Design Studios.  All Rights Reserved.
 * 
 * Contributor(s): 
 */
package com.silverwrist.dynamo.security;

import java.security.acl.LastOwnerException;
import java.security.acl.NotOwnerException;
import java.sql.*;
import java.util.*;
import com.silverwrist.util.*;
import com.silverwrist.dynamo.except.*;
import com.silverwrist.dynamo.iface.*;
import com.silverwrist.dynamo.util.*;

class AclOperations_mysql extends AclOperations
{
  /*--------------------------------------------------------------------------------
   * Static data members
   *--------------------------------------------------------------------------------
   */

  private static final int OWNERFLAG_USER = 0;
  private static final int OWNERFLAG_GROUP = 1;

  private static final int PRINCIPALFLAG_USER = 0;
  private static final int PRINCIPALFLAG_GROUP = 1;

  private static final int ACEFLAG_POSITIVE = 0;
  private static final int ACEFLAG_NEGATIVE = 16;

  private static final String OWNER_TEST_STMT =
      "SELECT ownerid FROM aclowner WHERE aclid = ? AND ownerid = ? AND flags = ?;";

  private static final boolean[] TESTUSER_TABLE =
  {
    false, false, false, false,
    true,  false, false, false,
    true,  true,  false, false,
    true,  true,  true,  false
  };

  /*--------------------------------------------------------------------------------
   * Attributes
   *--------------------------------------------------------------------------------
   */

  private DBUtilities m_utils;                // reference to utilities object

  /*--------------------------------------------------------------------------------
   * Constructor
   *--------------------------------------------------------------------------------
   */

  AclOperations_mysql(DBConnectionPool pool, DBUtilities utils)
  {
    super(pool);
    m_utils = utils;

  } // end constructor

  /*--------------------------------------------------------------------------------
   * Abstract implementations from class AclOperations
   *--------------------------------------------------------------------------------
   */

  boolean testOwner(int aclid, PrincipalID owner) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // create the appropriate SQL statement
      stmt = conn.prepareStatement(OWNER_TEST_STMT);
      stmt.setInt(1,aclid);
      stmt.setInt(2,owner.getID());
      stmt.setInt(3,(owner.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      rs = stmt.executeQuery();
      return rs.next();

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end testOwner

  boolean addOwner(int aclid, PrincipalID caller, PrincipalID owner)
      throws DatabaseException, NotOwnerException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the owners table temporarily
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES aclowner WRITE;");

      // test to see if we own the thing first
      stmt = conn.prepareStatement(OWNER_TEST_STMT);
      stmt.setInt(1,aclid);
      stmt.setInt(2,caller.getID());
      stmt.setInt(3,(caller.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      rs = stmt.executeQuery();
      if (!(rs.next()))
	throw new NotOwnerException();

      // now see if the existing owner is present?  (use same statement)
      stmt.setInt(1,aclid);
      stmt.setInt(2,owner.getID());
      stmt.setInt(3,(owner.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      SQLUtils.shutdown(rs);
      rs = stmt.executeQuery();
      if (rs.next())
	return false;  // owner already present

      // insert the new owner record
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("INSERT INTO aclowner (aclid, ownerid, flags) VALUES (?, ?, ?);");
      stmt.setInt(1,aclid);
      stmt.setInt(2,owner.getID());
      stmt.setInt(3,(owner.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      stmt.executeUpdate();
      return true;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      MySQLUtils.unlockTables(conn);
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(stmt2);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end addOwner

  boolean deleteOwner(int aclid, PrincipalID caller, PrincipalID owner)
      throws DatabaseException, NotOwnerException, LastOwnerException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the owners table temporarily
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES aclowner WRITE;");

      // test to see if we own the thing first
      stmt = conn.prepareStatement(OWNER_TEST_STMT);
      stmt.setInt(1,aclid);
      stmt.setInt(2,caller.getID());
      stmt.setInt(3,(caller.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      rs = stmt.executeQuery();
      if (!(rs.next()))
	throw new NotOwnerException();

      // now see if the existing owner is present?  (use same statement)
      stmt.setInt(1,aclid);
      stmt.setInt(2,owner.getID());
      stmt.setInt(3,(owner.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      SQLUtils.shutdown(rs);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return false;  // owner not present

      // is the owner we're about to remove the last one?
      SQLUtils.shutdown(rs); 
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("SELECT COUNT(*) FROM aclowner WHERE aclid = ?;");
      stmt.setInt(1,aclid);
      rs = stmt.executeQuery();
      if (SQLUtils.getReturnCountInt(rs,1)<=1)
	throw new LastOwnerException();

      // OK, go ahead and delete it.
      SQLUtils.shutdown(rs); 
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("DELETE FROM aclowner WHERE aclid = ? AND ownerid = ? AND flags = ?;");
      stmt.setInt(1,aclid);
      stmt.setInt(2,owner.getID());
      stmt.setInt(3,(owner.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      stmt.executeUpdate();
      return true;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      MySQLUtils.unlockTables(conn);
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(stmt2);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end deleteOwner

  void setAclName(int aclid, PrincipalID caller, String name) throws DatabaseException, NotOwnerException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the tables we need
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES acl WRITE, aclowner READ;");

      // test to see if we own the thing first
      stmt = conn.prepareStatement(OWNER_TEST_STMT);
      stmt.setInt(1,aclid);
      stmt.setInt(2,caller.getID());
      stmt.setInt(3,(caller.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      rs = stmt.executeQuery();
      if (!(rs.next()))
	throw new NotOwnerException();

      // poke in the new name
      SQLUtils.shutdown(rs); 
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("UPDATE acl SET aclname = ? WHERE aclid = ?;");
      stmt.setString(1,name);
      stmt.setInt(2,aclid);
      stmt.executeUpdate();


    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      MySQLUtils.unlockTables(conn);
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(stmt2);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end setAclName

  int getAceCount(int aclid) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // run a quick query
      stmt = conn.prepareStatement("SELECT COUNT(*) FROM acldata WHERE aclid = ?;");
      stmt.setInt(1,aclid);
      rs = stmt.executeQuery();
      return SQLUtils.getReturnCountInt(rs,1);

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAceCount

  int getAceID(int aclid, PrincipalID caller, int index) throws DatabaseException, NotOwnerException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the tables we need
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES acldata READ, aclowner READ;");

      // test to see if we own the thing first
      stmt = conn.prepareStatement(OWNER_TEST_STMT);
      stmt.setInt(1,aclid);
      stmt.setInt(2,caller.getID());
      stmt.setInt(3,(caller.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      rs = stmt.executeQuery();
      if (!(rs.next()))
	throw new NotOwnerException();

      // now query the ACL data to find the ACE ID
      SQLUtils.shutdown(rs); 
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("SELECT aceid FROM acldata WHERE aclid = ? AND seq = ?;");
      stmt.setInt(1,aclid);
      stmt.setInt(2,index);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	throw new IndexOutOfBoundsException("index is beyond last ACE for this ACL");
      return rs.getInt(1);

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      MySQLUtils.unlockTables(conn);
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(stmt2);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAceId

  boolean insertAce(int aclid, PrincipalID caller, int aceid, int at_index)
      throws DatabaseException, NotOwnerException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the tables we need
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES acldata WRITE, aclowner READ, ace READ;");

      // test to see if we own the thing first
      stmt = conn.prepareStatement(OWNER_TEST_STMT);
      stmt.setInt(1,aclid);
      stmt.setInt(2,caller.getID());
      stmt.setInt(3,(caller.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      rs = stmt.executeQuery();
      if (!(rs.next()))
	throw new NotOwnerException();

      // An ACL can contain at most one positive and one negative ACE for each unique Principal.
      // This translates out to a unique combination of principal ID and flags for each ACE.  So
      // get the principal ID and flags from our specified ACE to test against the current ACL contents.
      SQLUtils.shutdown(rs); 
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("SELECT pri, flags FROM ace WHERE aceid = ?;");
      stmt.setInt(1,aceid);
      rs = stmt.executeQuery();
      if (!(rs.next()))
      { // throw DatabaseException if the ACE isn't there
	DatabaseException de = new DatabaseException(SRMOperations_mysql.class,"SecurityMessages",
						     "ace.notFound");
	de.setParameter(0,String.valueOf(aceid));
	throw de;

      } // end if

      int ace_pri = rs.getInt(1);
      int ace_flags = rs.getInt(2);

      // Now conduct a query to look for another ACE with that same combination already in our ACL.
      SQLUtils.shutdown(rs); 
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("SELECT ace.aceid FROM ace, acldata WHERE ace.pri = ? AND ace.flags = ? "
				   + "AND ace.aceid = acldata.aceid AND acldata.aclid = ?;");
      stmt.setInt(1,ace_pri);
      stmt.setInt(2,ace_flags);
      stmt.setInt(3,aclid);
      rs = stmt.executeQuery();
      if (rs.next())
	return false;

      // Figure out the new sequence number for this ACE.
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("SELECT MAX(seq) FROM acldata WHERE aclid = ?;");
      stmt.setInt(1,aclid);
      rs = stmt.executeQuery();
      int max_seq = SQLUtils.getReturnCountInt(rs,1);
      boolean renumber = true;
      if ((at_index<0) || (at_index>max_seq))
      { // reset index and note that we need not do renumbering
	at_index = max_seq + 1;
	renumber = false;

      } // end if

      if (renumber)
      { // Renumber the other ACEs to provide room.
	SQLUtils.shutdown(rs);
	rs = null;
	SQLUtils.shutdown(stmt);
	stmt = conn.prepareStatement("UPDATE acldata SET seq = seq + 1 WHERE seq >= ?;");
	stmt.setInt(1,at_index);
	stmt.executeUpdate();

      } // end if

      // Now add the new record to the ACL data table.
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("INSERT INTO acldata (aclid, seq, aceid) VALUES (?, ?, ?);");
      stmt.setInt(1,aclid);
      stmt.setInt(2,at_index);
      stmt.setInt(3,aceid);
      stmt.executeUpdate();
      return true;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      MySQLUtils.unlockTables(conn);
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(stmt2);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end insertAce

  boolean removeAce(int aclid, PrincipalID caller, int aceid) throws DatabaseException, NotOwnerException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the tables we need
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES acldata WRITE, aclowner READ;");

      // test to see if we own the thing first
      stmt = conn.prepareStatement(OWNER_TEST_STMT);
      stmt.setInt(1,aclid);
      stmt.setInt(2,caller.getID());
      stmt.setInt(3,(caller.isGroup() ? OWNERFLAG_GROUP : OWNERFLAG_USER));
      rs = stmt.executeQuery();
      if (!(rs.next()))
	throw new NotOwnerException();

      // Look for the ACE ID in the list, and get the old sequence number.
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("SELECT seq FROM acldata WHERE aclid = ? AND aceid = ?;");
      stmt.setInt(1,aclid);
      stmt.setInt(2,aceid);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return false;  // not in the list
      int old_seq = rs.getInt(1);

      // Remove this entry from the table.
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("DELETE FROM acldata WHERE aclid = ? AND seq = ?;");
      stmt.setInt(1,aclid);
      stmt.setInt(2,old_seq);
      stmt.executeUpdate();

      // Now renumber all the entries after the one we just updated.
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("UPDATE acldata SET seq = seq - 1 WHERE aclid = ? AND seq > ?;");
      stmt.setInt(1,aclid);
      stmt.setInt(2,old_seq);
      stmt.executeUpdate();
      return true;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      MySQLUtils.unlockTables(conn);
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(stmt2);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end removeAce

  Set getGroupPermissionSetForUser(int aclid, int uid, boolean negative) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // and here's the query...
      stmt = conn.prepareStatement("SELECT acedata.perm_nsid, acedata.perm_name "
				   + "FROM acedata, ace, acldata, groupmembers "
				   + "WHERE acedata.aceid = ace.aceid AND ace.aceid = acldata.aceid "
				   + "AND acldata.aclid = ? AND ace.flags = ? "
				   + "AND ace.pri = groupmembers.gid AND groupmembers.uid = ? "
				   + "ORDER BY acldata.seq;");
      stmt.setInt(1,aclid);
      stmt.setInt(2,PRINCIPALFLAG_GROUP | (negative ? ACEFLAG_NEGATIVE : ACEFLAG_POSITIVE));
      stmt.setInt(3,uid);
      rs = stmt.executeQuery();

      // create the return value
      HashSet rc = new HashSet();
      while (rs.next())
	rc.add(new PropertyKey(rs.getInt(1),rs.getString(2)));
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally
    
  } // end getGroupPermissionSetForUser

  Set getGroupPermissionSetForGroup(int aclid, int gid, boolean negative) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // and here's the query...
      stmt = conn.prepareStatement("SELECT acedata.perm_nsid, acedata.perm_name FROM acedata, ace, acldata "
				   + "WHERE acedata.aceid = ace.aceid AND ace.aceid = acldata.aceid "
				   + "AND acldata.aclid = ? AND ace.flags = ? AND ace.pri = ? "
				   + "ORDER BY acldata.seq;");
      stmt.setInt(1,aclid);
      stmt.setInt(2,PRINCIPALFLAG_GROUP | (negative ? ACEFLAG_NEGATIVE : ACEFLAG_POSITIVE));
      stmt.setInt(3,gid);
      rs = stmt.executeQuery();

      // create the return value
      HashSet rc = new HashSet();
      while (rs.next())
	rc.add(new PropertyKey(rs.getInt(1),rs.getString(2)));
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally
    
  } // end getGroupPermissionSetForGroup

  Set getUserPermissionSet(int aclid, int uid, boolean negative) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // and here's the query...
      stmt = conn.prepareStatement("SELECT acedata.perm_nsid, acedata.perm_name FROM acedata, ace, acldata "
				   + "WHERE acedata.aceid = ace.aceid AND ace.aceid = acldata.aceid "
				   + "AND acldata.aclid = ? AND ace.flags = ? AND ace.pri = ? "
				   + "ORDER BY acldata.seq;");
      stmt.setInt(1,aclid);
      stmt.setInt(2,PRINCIPALFLAG_USER | (negative ? ACEFLAG_NEGATIVE : ACEFLAG_POSITIVE));
      stmt.setInt(3,uid);
      rs = stmt.executeQuery();

      // create the return value
      HashSet rc = new HashSet();
      while (rs.next())
	rc.add(new PropertyKey(rs.getInt(1),rs.getString(2)));
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally
    
  } // end getUserPermissionSet

  boolean testUserPermission(int aclid, int uid, PropertyKey perm) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      int table_index = 0;

      // test for positive and negative user values first
      stmt = conn.prepareStatement("SELECT ace.flags FROM acedata, ace, acldata, groupmembers "
				   + "WHERE acedata.perm_nsid = ? AND acedata.perm_name = ? "
				   + "AND acedata.aceid = ace.aceid AND ace.aceid = acldata.aceid "
				   + "AND acldata.aclid = ? AND (ace.flags = 0 OR ace.flags = 16) "
				   + "AND ace.pri = ?;");
      stmt.setInt(1,perm.getNamespaceID());
      stmt.setString(2,perm.getName());
      stmt.setInt(3,aclid);
      stmt.setInt(4,uid);
      rs = stmt.executeQuery();
      while (rs.next())
      { // examine flag values
	int flag = rs.getInt(1);
	if (flag==0)
	  table_index += 8;  // positive user
	else if (flag==16)
	  table_index += 2;  // negative user

      } // end while

      // then test for positive and negative group values
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);
      stmt = conn.prepareStatement("SELECT DISTINCT ace.flags FROM acedata, ace, acldata, groupmembers "
				   + "WHERE acedata.perm_nsid = ? AND acedata.perm_name = ? "
				   + "AND acedata.aceid = ace.aceid AND ace.aceid = acldata.aceid "
				   + "AND acldata.aclid = ? AND (ace.flags = 1 OR ace.flags = 17) "
				   + "AND ace.pri = groupmembers.gid AND groupmembers.uid = ?;");
      stmt.setInt(1,perm.getNamespaceID());
      stmt.setString(2,perm.getName());
      stmt.setInt(3,aclid);
      stmt.setInt(4,uid);
      rs = stmt.executeQuery();
      while (rs.next())
      { // examine flag values
	int flag = rs.getInt(1);
	if (flag==1)
	  table_index += 4;  // positive group
	else if (flag==17)
	  table_index += 1;  // negative group

      } // end while

      // Apply the boolean truth table to determine our result.
      return TESTUSER_TABLE[table_index];

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally
    
  } // end testUserPermission

  boolean testGroupPermission(int aclid, int gid, PropertyKey perm) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      boolean positive = false;
      boolean negative = false;

      // test for positive and negative values simultaneously
      stmt = conn.prepareStatement("SELECT acedata.aceflags FROM acedata, ace, acldata, groupmembers "
				   + "WHERE acedata.perm_nsid = ? AND acedata.perm_name = ? "
				   + "AND acedata.aceid = ace.aceid AND ace.aceid = acldata.aceid "
				   + "AND acldata.aclid = ? AND (ace.flags = 1 OR ace.flags = 17) "
				   + "AND ace.pri = ?;");
      stmt.setInt(1,perm.getNamespaceID());
      stmt.setString(2,perm.getName());
      stmt.setInt(3,aclid);
      stmt.setInt(4,gid);
      rs = stmt.executeQuery();
      while (rs.next())
      { // examine flag values
	int flag = rs.getInt(1);
	if (flag==1)
	  positive = true;   // positive group
	else if (flag==17)
	  negative = true;   // negative group

      } // end while

      return (positive && !negative);

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally
    
  } // end testGroupPermission

  int[] getAceIDs(int aclid) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // set up the query
      stmt = conn.prepareStatement("SELECT aceid FROM acldata WHERE aclid = ? ORDER BY seq;");
      stmt.setInt(1,aclid);
      rs = stmt.executeQuery();

      // read off into an ArrayList
      ArrayList tmp = new ArrayList();
      while (rs.next())
	tmp.add(new Integer(rs.getInt(1)));

      // now convert to a real array
      int[] rc = new int[tmp.size()];
      for (int i=0; i<tmp.size(); i++)
	rc[i] = ((Integer)(tmp.get(i))).intValue();
      tmp.clear();
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally
    
  } // end getAceIDs

} // end class AclOperations_mysql
