<?PHP

#
#   FILE:  MetadataField.php
#
#   Part of the Collection Workflow Integration System
#   Copyright 2002-2010 Internet Scout
#   http://scout.wisc.edu
#

class MetadataField {

    # ---- PUBLIC INTERFACE --------------------------------------------------

    # Update methods for timestamp fields
    const UPDATEMETHOD_NOAUTOUPDATE   = "NoAutoUpdate";
    const UPDATEMETHOD_ONRECORDCREATE = "OnRecordCreate";
    const UPDATEMETHOD_BUTTON         = "Button";
    const UPDATEMETHOD_ONRECORDEDIT   = "OnRecordEdit";
    const UPDATEMETHOD_ONRECORDCHANGE = "OnRecordChange";

    # get current error status of object
    function Status() {  return $this->ErrorStatus;  }

    # get/set type of field as enumerated value
    function Type($NewValue = DB_NOVALUE)
    {
        # if new value supplied
        if (($NewValue != DB_NOVALUE)
             && ($NewValue != MetadataField::$FieldTypePHPEnums[$this->DBFields["FieldType"]]))
        {
            # update database fields and store new type
            $this->ModifyField(NULL, $NewValue);
        }

        # return type to caller
        return MetadataField::$FieldTypePHPEnums[$this->DBFields["FieldType"]];
    }

    # get type of field as type name (string)
    function TypeAsName()
    {
        return $this->DBFields["FieldType"];
    }

    # get/set name of field
    function Name($NewName = DB_NOVALUE)
    {
        # if new name specified
        if (($NewName != DB_NOVALUE)
            && (trim($NewName) != $this->DBFields["FieldName"]))
        {
            # if field name is invalid
            $NewName = trim($NewName);
            if (strlen($this->NormalizeFieldNameForDB($NewName)) < 1)
            {
                # set error status to indicate illegal name
                $this->ErrorStatus = MetadataSchema::MDFSTAT_ILLEGALNAME;
            }
            else
            {
                # check for duplicate name
                $DuplicateCount = $this->DB->Query("SELECT COUNT(*) AS RecordCount FROM MetadataFields "
                                             ."WHERE FieldName = '".addslashes($NewName)."'",
                                             "RecordCount");

                # if field name is duplicate
                if ($DuplicateCount > 0)
                {
                    # set error status to indicate duplicate name
                    $this->ErrorStatus = MetadataSchema::MDFSTAT_DUPLICATENAME;
                }
                else
                {
                    # modify database declaration to reflect new field name
                    $this->ErrorStatus = MetadataSchema::MDFSTAT_OK;
                    $this->ModifyField($NewName);
                }
            }
        }

        # return value to caller
        return $this->DBFields["FieldName"];
    }

    # get associative array (enumeration => string) containing field types we can convert to
    function GetAllowedConversionTypes()
    {
        # determine type list based on our type
        switch ($this->Type())
        {
        case MetadataSchema::MDFTYPE_TEXT:
        case MetadataSchema::MDFTYPE_PARAGRAPH:
        case MetadataSchema::MDFTYPE_NUMBER:
        case MetadataSchema::MDFTYPE_FLAG:
        case MetadataSchema::MDFTYPE_URL:
            $AllowedTypes = array(
                MetadataSchema::MDFTYPE_TEXT       => "Text",
                MetadataSchema::MDFTYPE_PARAGRAPH  => "Paragraph",
                MetadataSchema::MDFTYPE_NUMBER     => "Number",
                MetadataSchema::MDFTYPE_FLAG       => "Flag",
                MetadataSchema::MDFTYPE_URL        => "Url"
                );
            break;

        case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
        case MetadataSchema::MDFTYPE_OPTION:
            $AllowedTypes = array(
                MetadataSchema::MDFTYPE_CONTROLLEDNAME => "ControlledName",
                MetadataSchema::MDFTYPE_OPTION         => "Option",
                );
            break;

        case MetadataSchema::MDFTYPE_DATE:
            $AllowedTypes = array(
                MetadataSchema::MDFTYPE_TEXT  => "Text",
                MetadataSchema::MDFTYPE_DATE  => "Date",
                );
            break;

        case MetadataSchema::MDFTYPE_IMAGE:
            $AllowedTypes = array(
                MetadataSchema::MDFTYPE_TEXT  => "Text",
                MetadataSchema::MDFTYPE_IMAGE => "Still Image",
                );
            break;

        case MetadataSchema::MDFTYPE_TIMESTAMP:
        case MetadataSchema::MDFTYPE_TREE:
        case MetadataSchema::MDFTYPE_USER:
        case MetadataSchema::MDFTYPE_FILE:
        default:
            $AllowedTypes = array();
            break;
        }

        # return type list to caller
        return $AllowedTypes;
    }

    # get/set whether item is temporary instance
    function IsTempItem($NewSetting = NULL)
    {
        $ItemTableName = "MetadataFields";
        $ItemIdFieldName = "FieldId";
        $ItemFactoryObjectName = "MetadataSchema";
        $ItemAssociationTables = array(
                "FieldQualifierInts",
                );
        $ItemAssociationFieldName = "MetadataFieldId";

        # if new temp item setting supplied
        if ($NewSetting !== NULL)
        {
            # if caller requested to switch
            if ((($this->Id() < 0) && ($NewSetting == FALSE))
                    || (($this->Id() >= 0) && ($NewSetting == TRUE)))
            {
                # if field name is invalid
                if (strlen($this->NormalizeFieldNameForDB($this->Name())) < 1)
                {
                    # set error status to indicate illegal name
                    $this->ErrorStatus = MetadataSchema::MDFSTAT_ILLEGALNAME;
                }
                else
                {
                    # lock DB tables to prevent next ID from being grabbed
                    $DB = $this->DB;
                    $DB->Query("LOCK TABLES ".$ItemTableName." WRITE,".
                            "APSessions WRITE, APSessionData WRITE");

                    # get next temp item ID
                    $OldItemId = $this->Id();
                    $Factory = new $ItemFactoryObjectName();
                    if ($NewSetting == TRUE)
                    {
                        $this->Id = $Factory->GetNextTempItemId();
                    }
                    else
                    {
                        $this->Id = $Factory->GetNextItemId();
                    }

                    # change item ID
                    $DB->Query("UPDATE ".$ItemTableName." SET ".$ItemIdFieldName." = ".
                        $this->Id.  " WHERE ".$ItemIdFieldName." = ".$OldItemId);

                    # release DB tables
                    $DB->Query("UNLOCK TABLES");

                    # change associations
                    foreach ($ItemAssociationTables as $TableName)
                    {
                        $DB->Query("UPDATE ".$TableName." SET ".$ItemAssociationFieldName." = ".
                                $this->Id.  " WHERE ".$ItemAssociationFieldName." = ".$OldItemId);
                    }

                    # if changing item from temp to non-temp
                    if ($NewSetting == FALSE)
                    {
                        # add any needed database fields and/or entries
                        $this->AddDatabaseFields();
                    }
                }
            }
        }

        # report to caller whether we are a temp item
        return ($this->Id() < 0) ? TRUE : FALSE;
    }

    # get field attributes
    function Id() {  return $this->DBFields["FieldId"];  }
    function DBFieldName() {  return $this->DBFields["DBFieldName"];  }

    # get/set field attributes
    function Description($NewValue = DB_NOVALUE) {  return $this->UpdateValue("Description", $NewValue);  }
    function RequiredBySPT($NewValue = DB_NOVALUE) {  return $this->UpdateValue("RequiredBySPT", $NewValue);  }
    function Enabled($NewValue = DB_NOVALUE) {  return $this->UpdateValue("Enabled", $NewValue);  }
    function Optional($NewValue = DB_NOVALUE) {  return $this->UpdateValue("Optional", $NewValue);  }
    function Viewable($NewValue = DB_NOVALUE) {  return $this->UpdateValue("Viewable", $NewValue);  }
    function AllowMultiple($NewValue = DB_NOVALUE) {  return $this->UpdateValue("AllowMultiple", $NewValue);  }
    function IncludeInKeywordSearch($NewValue = DB_NOVALUE) {  return $this->UpdateValue("IncludeInKeywordSearch", $NewValue);  }
    function IncludeInAdvancedSearch($NewValue = DB_NOVALUE) {  return $this->UpdateValue("IncludeInAdvancedSearch", $NewValue);  }
    function IncludeInRecommenderSystem($NewValue = DB_NOVALUE) {  return $this->UpdateValue("IncludeInRecommenderSystem", $NewValue);  }
    function TextFieldSize($NewValue = DB_NOVALUE) {  return $this->UpdateValue("TextFieldSize", $NewValue);  }
    function MaxLength($NewValue = DB_NOVALUE) {  return $this->UpdateValue("MaxLength", $NewValue);  }
    function ParagraphRows($NewValue = DB_NOVALUE) {  return $this->UpdateValue("ParagraphRows", $NewValue);  }
    function ParagraphCols($NewValue = DB_NOVALUE) {  return $this->UpdateValue("ParagraphCols", $NewValue);  }
    function MinValue($NewValue = DB_NOVALUE) {  return $this->UpdateValue("MinValue", $NewValue);  }
    function MaxValue($NewValue = DB_NOVALUE) {  return $this->UpdateValue("MaxValue", $NewValue);  }
    function FlagOnLabel($NewValue = DB_NOVALUE) {  return $this->UpdateValue("FlagOnLabel", $NewValue);  }
    function FlagOffLabel($NewValue = DB_NOVALUE) {  return $this->UpdateValue("FlagOffLabel", $NewValue);  }
    function DateFormat($NewValue = DB_NOVALUE) {  return $this->UpdateValue("DateFormat", $NewValue);  }
    function SearchWeight($NewValue = DB_NOVALUE) {  return $this->UpdateValue("SearchWeight", $NewValue);  }
    function RecommenderWeight($NewValue = DB_NOVALUE) {  return $this->UpdateValue("RecommenderWeight", $NewValue);  }
    function MaxHeight($NewValue = DB_NOVALUE) {  return $this->UpdateValue("MaxHeight", $NewValue);  }
    function MaxWidth($NewValue = DB_NOVALUE) {  return $this->UpdateValue("MaxWidth", $NewValue);  }
    function MaxPreviewHeight($NewValue = DB_NOVALUE) {  return $this->UpdateValue("MaxPreviewHeight", $NewValue);  }
    function MaxPreviewWidth($NewValue = DB_NOVALUE) {  return $this->UpdateValue("MaxPreviewWidth", $NewValue);  }
    function MaxThumbnailHeight($NewValue = DB_NOVALUE) {  return $this->UpdateValue("MaxThumbnailHeight", $NewValue);  }
    function MaxThumbnailWidth($NewValue = DB_NOVALUE) {  return $this->UpdateValue("MaxThumbnailWidth", $NewValue);  }
    function DefaultAltText($NewValue = DB_NOVALUE) {  return $this->UpdateValue("DefaultAltText", $NewValue);  }
    function UsesQualifiers($NewValue = DB_NOVALUE) {  return $this->UpdateValue("UsesQualifiers", $NewValue);  }
    function DefaultQualifier($NewValue = DB_NOVALUE) {  return $this->UpdateValue("DefaultQualifier", $NewValue);  }
    function UseForOaiSets($NewValue = DB_NOVALUE) {  return $this->UpdateValue("UseForOaiSets", $NewValue);  }
    function ViewingPrivilege($NewValue = DB_NOVALUE) {  return $this->UpdateValue("ViewingPrivilege", $NewValue);  }
    function AuthoringPrivilege($NewValue = DB_NOVALUE) {  return $this->UpdateValue("AuthoringPrivilege", $NewValue);  }
    function EditingPrivilege($NewValue = DB_NOVALUE) {  return $this->UpdateValue("EditingPrivilege", $NewValue);  }

    function PointPrecision($NewValue = DB_NOVALUE)
    {
        if ($NewValue !== DB_NOVALUE && $this->Id() >= 0)
        {
            $OldValue = $this->UpdateValue("PointPrecision", DB_NOVALUE);

            if ($NewValue != $OldValue)
            {
                $Decimals  = $this->UpdateValue("PointDecimalDigits", DB_NOVALUE);

                $this->DB->Query("ALTER TABLE Resources MODIFY COLUMN "
                           ."`".$this->DBFields["DBFieldName"]."X` "
                           ."DECIMAL(".$NewValue.",".$Decimals.")");
                $this->DB->Query("ALTER TABLE Resources MODIFY COLUMN "
                           ."`".$this->DBFields["DBFieldName"]."Y` "
                           ."DECIMAL(".$NewValue.",".$Decimals.")");
            }
        }

        return $this->UpdateValue("PointPrecision", $NewValue);
    }

    function PointDecimalDigits($NewValue = DB_NOVALUE)
    {
        if ($NewValue !== DB_NOVALUE && $this->Id() >= 0)
        {
            $OldValue = $this->UpdateValue("PointDecimalDigits", DB_NOVALUE);

            if ($NewValue != $OldValue)
            {
                $Precision = $this->UpdateValue("PointPrecision", DB_NOVALUE);

                $this->DB->Query("ALTER TABLE Resources MODIFY COLUMN "
                           ."`".$this->DBFields["DBFieldName"]."X` "
                           ."DECIMAL(".$Precision.",".$NewValue.")");
                $this->DB->Query("ALTER TABLE Resources MODIFY COLUMN "
                           ."`".$this->DBFields["DBFieldName"]."Y` "
                           ."DECIMAL(".$Precision.",".$NewValue.")");
            }
        }

        return $this->UpdateValue("PointDecimalDigits", $NewValue);
    }

    function DefaultValue($NewValue = DB_NOVALUE)
    {
        if ($this->Type() == MetadataSchema::MDFTYPE_POINT)
        {
            if ($NewValue !== DB_NOVALUE &&
                isset($NewValue["X"]) && isset($NewValue["Y"]))
            {
                $NewValue = $NewValue["X"].",".$NewValue["Y"];
            }

            $tmp = explode(",", $this->UpdateValue("DefaultValue", $NewValue));

            if (count($tmp)==2)
            {
                $rc = array("X" => $tmp[0], "Y" => $tmp[1]);
            }
            else
            {
                $rc = array("X" => NULL, "Y" => NULL);
            }
        }
        else
        {
            $rc = $this->UpdateValue("DefaultValue", $NewValue);
        }
        return $rc;
    }

    function UpdateMethod($NewValue = DB_NOVALUE)
    {
        return $this->UpdateValue("UpdateMethod", $NewValue);
    }

    # get possible values (only meaningful for Trees, Controlled Names, Options, Flags)
    # (index for returned array is IDs for values)
    function GetPossibleValues($MaxNumberOfValues = NULL, $Offset=0)
    {
        # retrieve values based on field type
        switch ($this->Type())
        {
            case MetadataSchema::MDFTYPE_TREE:
                $QueryString = "SELECT ClassificationId, ClassificationName"
                        ." FROM Classifications WHERE FieldId = ".$this->Id()
                        ." ORDER BY ClassificationName";
                if ($MaxNumberOfValues)
                {
                    $QueryString .= " LIMIT ".intval($MaxNumberOfValues)." OFFSET "
                        .intval($Offset);
                }
                $this->DB->Query($QueryString);
                $PossibleValues = $this->DB->FetchColumn(
                        "ClassificationName", "ClassificationId");
                break;

            case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
            case MetadataSchema::MDFTYPE_OPTION:
                $QueryString = "SELECT ControlledNameId, ControlledName"
                        ." FROM ControlledNames WHERE FieldId = ".$this->Id()
                        ." ORDER BY ControlledName";
                if ($MaxNumberOfValues)
                {
                    $QueryString .= " LIMIT ".intval($MaxNumberOfValues)." OFFSET "
                        .intval($Offset);
                }
                $this->DB->Query($QueryString);
                $PossibleValues = $this->DB->FetchColumn(
                        "ControlledName", "ControlledNameId");
                break;

            case MetadataSchema::MDFTYPE_FLAG:
                $PossibleValues[0] = $this->FlagOffLabel();
                $PossibleValues[1] = $this->FlagOnLabel();
                break;

            default:
                # for everything else return an empty array
                $PossibleValues = array();
                break;
        }

        # return array of possible values to caller
        return $PossibleValues;
    }

    # get count of possible values (only meaningful for Trees, Controlled Names, Options)
    function GetCountOfPossibleValues()
    {
        # retrieve values based on field type
        switch ($this->Type())
        {
            case MetadataSchema::MDFTYPE_TREE:
                $Count = $this->DB->Query("SELECT count(*) AS ValueCount"
                        ." FROM Classifications WHERE FieldId = ".$this->Id(),
                        "ValueCount");
                break;

            case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
            case MetadataSchema::MDFTYPE_OPTION:
                $Count = $this->DB->Query("SELECT count(*) AS ValueCount"
                        ." FROM ControlledNames WHERE FieldId = ".$this->Id(),
                        "ValueCount");
                break;

            case MetadataSchema::MDFTYPE_FLAG:
                $Count = 2;
                break;

            default:
                # for everything else return an empty array
                $Count = 0;
                break;
        }

        # return count of possible values to caller
        return $Count;
    }

    # get ID for specified value (only meaningful for Trees / Controlled Names / Options)
    # (returns NULL if value not found)
    function GetIdForValue($Value)
    {
        # retrieve ID based on field type
        switch ($this->Type())
        {
            case MetadataSchema::MDFTYPE_TREE:
                $Id = $this->DB->Query("SELECT ClassificationId FROM Classifications"
                        ." WHERE ClassificationName = '".addslashes($Value)."'"
                        ." AND FieldId = ".$this->Id(),
                        "ClassificationId");
                break;

            case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
            case MetadataSchema::MDFTYPE_OPTION:
                $Id = $this->DB->Query("SELECT ControlledNameId FROM ControlledNames"
                        ." WHERE ControlledName = '".addslashes($Value)."'"
                        ." AND FieldId = ".$this->Id(),
                        "ControlledNameId");
                break;

            default:
                # for everything else return NULL
                $Id = NULL;
                break;
        }

        # return ID for value to caller
        return $Id;
    }

    # get value for specified ID (only meaningful for Trees / Controlled Names / Options)
    # (returns NULL if ID not found)
    function GetValueForId($Id)
    {
        # retrieve ID based on field type
        switch ($this->Type())
        {
            case MetadataSchema::MDFTYPE_TREE:
                $Value = $this->DB->Query("SELECT ClassificationName FROM Classifications"
                        ." WHERE ClassificationId = '".intval($Id)."'"
                        ." AND FieldId = ".$this->Id(),
                        "ClassificationName");
                break;

            case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
            case MetadataSchema::MDFTYPE_OPTION:
                $Value = $this->DB->Query("SELECT ControlledName FROM ControlledNames"
                        ." WHERE ControlledNameId = '".intval($Id)."'"
                        ." AND FieldId = ".$this->Id(),
                        "ControlledName");
                break;

            default:
                # for everything else return NULL
                $Value = NULL;
                break;
        }

        # return ID for value to caller
        return $Value;
    }



    # get/set whether field uses item-level qualifiers
    function HasItemLevelQualifiers($NewValue = DB_NOVALUE)
    {
        # if value provided different from present value
        if (($NewValue != DB_NOVALUE)
            && ($NewValue != $this->DBFields["HasItemLevelQualifiers"]))
        {
            # check if qualifier column currently exists
            $QualColName = $this->DBFieldName()."Qualifier";
            $QualColExists = $this->DB->FieldExists("Resources", $QualColName);

            # if new value indicates qualifiers should now be used
            if ($NewValue == TRUE)
            {
                # if qualifier column does not exist in DB for this field
                if ($QualColExists == FALSE)
                {
                    # add qualifier column in DB for this field
                    $this->DB->Query("ALTER TABLE Resources ADD COLUMN `"
                                     .$QualColName."` INT");
                }
            }
            else
            {
                # if qualifier column exists in DB for this field
                if ($QualColExists == TRUE)
                {
                    # remove qualifier column from DB for this field
                    $this->DB->Query("ALTER TABLE Resources DROP COLUMN `"
                                     .$QualColName."`");
                }
            }
        }

        return $this->UpdateValue("HasItemLevelQualifiers", $NewValue);
    }

    # get list of qualifiers associated with field
    function AssociatedQualifierList()
    {
        # start with empty list
        $List = array();

        # for each associated qualifier
        $this->DB->Query("SELECT QualifierId FROM FieldQualifierInts"
                     ." WHERE MetadataFieldId = ".$this->DBFields["FieldId"]);
        while ($Record = $this->DB->FetchRow())
        {
            # load qualifier object
            $Qual = new Qualifier($Record["QualifierId"]);

            # add qualifier ID and name to list
            $List[$Qual->Id()] = $Qual->Name();
        }

        # return list to caller
        return $List;
    }

    # get list of qualifiers not associated with field
    function UnassociatedQualifierList()
    {
        # grab list of associated qualifiers
        $AssociatedQualifiers = $this->AssociatedQualifierList();

        # get list of all qualifiers
        $QFactory = new QualifierFactory();
        $AllQualifiers = $QFactory->QualifierList();

        # return list of unassociated qualifiers
        return array_diff($AllQualifiers, $AssociatedQualifiers);
    }

    # add qualifier association
    function AssociateWithQualifier($QualifierIdOrObject)
    {
        # if qualifier object passed in
        if (is_object($QualifierIdOrObject))
        {
            # grab qualifier ID from object
            $QualifierIdOrObject = $QualifierIdOrObject->Id();
        }

        # if not already associated
        $RecordCount = $this->DB->Query(
            "SELECT COUNT(*) AS RecordCount FROM FieldQualifierInts"
            ." WHERE QualifierId = ".$QualifierIdOrObject
            ." AND MetadataFieldId = ".$this->Id(), "RecordCount");
        if ($RecordCount < 1)
        {
            # associate field with qualifier
            $this->DB->Query("INSERT INTO FieldQualifierInts SET"
                             ." QualifierId = ".$QualifierIdOrObject.","
                             ." MetadataFieldId = ".$this->Id());
        }
    }

    # delete qualifier association
    function UnassociateWithQualifier($QualifierIdOrObject)
    {
        # if qualifier object passed in
        if (is_object($QualifierIdOrObject))
        {
            # grab qualifier ID from object
            $QualifierIdOrObject = $QualifierIdOrObject->Id();
        }

        # delete intersection record from database
        $this->DB->Query("DELETE FROM FieldQualifierInts WHERE QualifierId = "
                         .$QualifierIdOrObject." AND MetadataFieldId = ".
                         $this->Id());
    }

    # retrieve item factory object for this field
    function GetFactory()
    {
        switch ($this->Type())
        {
            case MetadataSchema::MDFTYPE_TREE:
                $Factory = new ClassificationFactory($this->Id());
                break;

            case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
            case MetadataSchema::MDFTYPE_OPTION:
                $Factory = new ControlledNameFactory($this->Id());
                break;

            default:
                $Factory = NULL;
                break;
        }

        return $Factory;
    }


    # ---- PRIVATE INTERFACE -------------------------------------------------

    private $DB;
    private $DBFields;
    private $ErrorStatus;

    # field type DB/PHP enum translations
    public static $FieldTypeDBEnums = array(
            MetadataSchema::MDFTYPE_TEXT             => "Text",
            MetadataSchema::MDFTYPE_PARAGRAPH        => "Paragraph",
            MetadataSchema::MDFTYPE_NUMBER           => "Number",
            MetadataSchema::MDFTYPE_DATE             => "Date",
            MetadataSchema::MDFTYPE_TIMESTAMP        => "TimeStamp",
            MetadataSchema::MDFTYPE_FLAG             => "Flag",
            MetadataSchema::MDFTYPE_TREE             => "Tree",
            MetadataSchema::MDFTYPE_CONTROLLEDNAME   => "ControlledName",
            MetadataSchema::MDFTYPE_OPTION           => "Option",
            MetadataSchema::MDFTYPE_USER             => "User",
            MetadataSchema::MDFTYPE_IMAGE            => "Still Image",
            MetadataSchema::MDFTYPE_FILE             => "File",
            MetadataSchema::MDFTYPE_URL              => "Url",
            MetadataSchema::MDFTYPE_POINT            => "Point"
            );
    public static $FieldTypeDBAllowedEnums = array(
            MetadataSchema::MDFTYPE_TEXT             => "Text",
            MetadataSchema::MDFTYPE_PARAGRAPH        => "Paragraph",
            MetadataSchema::MDFTYPE_NUMBER           => "Number",
            MetadataSchema::MDFTYPE_DATE             => "Date",
            MetadataSchema::MDFTYPE_TIMESTAMP        => "TimeStamp",
            MetadataSchema::MDFTYPE_FLAG             => "Flag",
            MetadataSchema::MDFTYPE_TREE             => "Tree",
            MetadataSchema::MDFTYPE_CONTROLLEDNAME   => "ControlledName",
            MetadataSchema::MDFTYPE_OPTION           => "Option",
            MetadataSchema::MDFTYPE_IMAGE            => "Still Image",
            MetadataSchema::MDFTYPE_FILE             => "File",
            MetadataSchema::MDFTYPE_URL              => "Url",
            MetadataSchema::MDFTYPE_POINT            => "Point"
            );
    public static $FieldTypePHPEnums = array(
            "Text"                   => MetadataSchema::MDFTYPE_TEXT,
            "Paragraph"              => MetadataSchema::MDFTYPE_PARAGRAPH,
            "Number"                 => MetadataSchema::MDFTYPE_NUMBER,
            "Date"                   => MetadataSchema::MDFTYPE_DATE,
            "TimeStamp"              => MetadataSchema::MDFTYPE_TIMESTAMP,
            "Flag"                   => MetadataSchema::MDFTYPE_FLAG,
            "Tree"                   => MetadataSchema::MDFTYPE_TREE,
            "ControlledName"         => MetadataSchema::MDFTYPE_CONTROLLEDNAME,
            "Option"                 => MetadataSchema::MDFTYPE_OPTION,
            "User"                   => MetadataSchema::MDFTYPE_USER,
            "Still Image"            => MetadataSchema::MDFTYPE_IMAGE,
            "File"                   => MetadataSchema::MDFTYPE_FILE,
            "Url"                    => MetadataSchema::MDFTYPE_URL,
            "Point"                  => MetadataSchema::MDFTYPE_POINT
            );

    public static $UpdateTypes = array(
        MetadataField::UPDATEMETHOD_NOAUTOUPDATE   => "Do not update automatically",
        MetadataField::UPDATEMETHOD_ONRECORDCREATE => "Update on record creation",
        MetadataField::UPDATEMETHOD_BUTTON         => "Provide an update button",
        MetadataField::UPDATEMETHOD_ONRECORDEDIT   => "Update when record is edited",
        MetadataField::UPDATEMETHOD_ONRECORDCHANGE => "Update when record is changed"
        );


    # object constructor (only for use by MetadataSchema object)
    function MetadataField($FieldId, $FieldName = NULL, $FieldType = NULL,
                           $Optional = TRUE, $DefaultValue = NULL)
    {
        # assume everything will be okay
        $this->ErrorStatus = MetadataSchema::MDFSTAT_OK;

        # grab our own database handle
        $this->DB = new Database();
        $DB = $this->DB;

        # if field ID supplied
        if ($FieldId != NULL)
        {
            # look up field in database
            $DB->Query("SELECT * FROM MetadataFields WHERE FieldId = ".intval($FieldId));
            $Record = $DB->FetchRow();
        }

        # if no field ID supplied or if record not found in database
        if (($FieldId == NULL) || ($Record == NULL))
        {
            # error out if valid field type not supplied
            if (empty(MetadataField::$FieldTypeDBEnums[$FieldType]))
            {
                $this->ErrorStatus = MetadataSchema::MDFSTAT_FIELDDOESNOTEXIST;
                return;
            }

            # if field name supplied
            $FieldName = trim($FieldName);
            if (strlen($FieldName) > 0)
            {
                # error out if field name is duplicate
                $DuplicateCount = $DB->Query(
                        "SELECT COUNT(*) AS RecordCount FROM MetadataFields "
                            ."WHERE FieldName = '".addslashes($FieldName)."'",
                        "RecordCount");
                if ($DuplicateCount > 0)
                {
                    $this->ErrorStatus = MetadataSchema::MDFSTAT_DUPLICATENAME;
                    return;
                }
            }

            # grab current user ID
            global $G_User;
            $UserId = $G_User->Get("UserId");

            # lock DB tables and get next temporary field ID
            $Schema = new MetadataSchema();
            $DB->Query("LOCK TABLES MetadataFields WRITE");
            $FieldId = $Schema->GetNextTempItemId();

            # add field to MDF table in database
            $DB->Query("INSERT INTO MetadataFields "
                  ."(FieldId, FieldName, FieldType, Optional, DefaultValue, LastModifiedById) VALUES "
                  ."(".intval($FieldId).", "
                  ."'".addslashes($FieldName)."', "
                  ."'".MetadataField::$FieldTypeDBEnums[$FieldType]."', "
                  .($Optional ? 1 : 0).", "
                  ."'".addslashes($DefaultValue)."',"
                  ."'".$UserId."')");

            # release DB tables
            $DB->Query("UNLOCK TABLES");

            # re-read record from database
            $DB->Query("SELECT * FROM MetadataFields WHERE FieldId = "
                    .intval($FieldId));
            $this->DBFields = $DB->FetchRow();
            $this->DBFields["DBFieldName"] =
                    $this->NormalizeFieldNameForDB($this->DBFields["FieldName"]);

            # set field order values for new field
            $FieldCount = $DB->Query("SELECT COUNT(*) AS FieldCount FROM MetadataFields", "FieldCount");
            $this->OrderPosition(MetadataSchema::MDFORDER_DISPLAY,
                                 ($FieldCount + 1));
            $this->OrderPosition(MetadataSchema::MDFORDER_EDITING,
                                 ($FieldCount + 1));
        }
        else
        {
            # save values locally
            $this->DBFields = $Record;
            $this->DBFields["DBFieldName"] =
                    $this->NormalizeFieldNameForDB($Record["FieldName"]);
        }
    }

    # remove field from database (only for use by MetadataSchema object)
    function Drop()
    {
        # clear other database entries as appropriate for field type
        $DB = $this->DB;
        $DBFieldName = $this->DBFields["DBFieldName"];
        switch (MetadataField::$FieldTypePHPEnums[$this->DBFields["FieldType"]])
        {
            case MetadataSchema::MDFTYPE_TEXT:
            case MetadataSchema::MDFTYPE_PARAGRAPH:
            case MetadataSchema::MDFTYPE_NUMBER:
            case MetadataSchema::MDFTYPE_USER:
            case MetadataSchema::MDFTYPE_IMAGE:
            case MetadataSchema::MDFTYPE_TIMESTAMP:
            case MetadataSchema::MDFTYPE_URL:
                # remove field from resources table
                if ($DB->FieldExists("Resources", $DBFieldName))
                {
                    $DB->Query("ALTER TABLE Resources DROP COLUMN `".$DBFieldName."`");
                }
                break;

            case MetadataSchema::MDFTYPE_POINT:
                if ($DB->FieldExists("Resources", $DBFieldName."X"))
                {
                    $DB->Query("ALTER TABLE Resources DROP COLUMN `".$DBFieldName."X`");
                    $DB->Query("ALTER TABLE Resources DROP COLUMN `".$DBFieldName."Y`");
                }
                break;

            case MetadataSchema::MDFTYPE_FLAG:
                # remove field from resources table
                if ($DB->FieldExists("Resources", $DBFieldName))
                {
                    $DB->Query("ALTER TABLE Resources DROP COLUMN `".$DBFieldName."`");
                }
                break;

            case MetadataSchema::MDFTYPE_DATE:
                # remove fields from resources table
                if ($DB->FieldExists("Resources", $DBFieldName."Begin"))
                {
                    $DB->Query("ALTER TABLE Resources DROP COLUMN `".$DBFieldName."Begin`");
                    $DB->Query("ALTER TABLE Resources DROP COLUMN `".$DBFieldName."End`");
                    $DB->Query("ALTER TABLE Resources DROP COLUMN `".$DBFieldName."Precision`");
                }
                break;

            case MetadataSchema::MDFTYPE_TREE:
                $DB->Query("SELECT ClassificationId FROM Classifications "
                           ."WHERE FieldId = ".$this->Id());
                $TempDB = new SPTDatabase();
                while ($ClassificationId = $DB->FetchField("ClassificationId"))
                {
                    # remove any resource / name intersections
                    $TempDB->Query("DELETE FROM ResourceClassInts WHERE "
                                   ."ClassificationId = ".$ClassificationId);

                    # remove controlled name
                    $TempDB->Query("DELETE FROM Classifications WHERE "
                                   ."ClassificationId = ".$ClassificationId);
                }
                break;

            case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
            case MetadataSchema::MDFTYPE_OPTION:
                $DB->Query("SELECT ControlledNameId FROM ControlledNames "
                           ."WHERE FieldId = ".$this->Id());
                $TempDB = new SPTDatabase();
                while ($ControlledNameId = $DB->FetchField("ControlledNameId"))
                {
                    # remove any resource / name intersections
                    $TempDB->Query("DELETE FROM ResourceNameInts WHERE "
                                   ."ControlledNameId = ".$ControlledNameId);

                    # remove any variant names
                    $TempDB->Query("DELETE FROM VariantNames WHERE "
                                   ."ControlledNameId = ".$ControlledNameId);

                    # remove controlled name
                    $TempDB->Query("DELETE FROM ControlledNames WHERE "
                                   ."ControlledNameId = ".$ControlledNameId);
                }
                break;

            case MetadataSchema::MDFTYPE_FILE:
                # for each file associated with this field
                $DB->Query("SELECT FileId FROM Files WHERE FieldId = '".$this->Id()."'");
                while ($FileId = $DB->FetchRow())
                {
                    # delete file
                    $File = new File(intval($FileId));
                    $File->Delete();
                }
                break;
        }

        # remove field from database
        $DB->Query("DELETE FROM MetadataFields "
                   ."WHERE FieldId = '".$this->DBFields["FieldId"]."'");

        # remove any qualifier associations
        $DB->Query("DELETE FROM FieldQualifierInts WHERE MetadataFieldId = '"
                   .$this->DBFields["FieldId"]."'");
    }

    # modify any database fields
    function ModifyField($NewName = NULL, $NewType = NULL)
    {
        # grab old DB field name
        $OldDBFieldName = $this->DBFields["DBFieldName"];
        $OldFieldType = NULL;

        # if new field name supplied
        if ($NewName != NULL)
        {
            # cache the old name for options and controllednames below
            $OldName = $this->DBFields["FieldName"];

            # store new name
            $this->UpdateValue("FieldName", $NewName);

            # determine new DB field name
            $NewDBFieldName = $this->NormalizeFieldNameForDB($NewName);

            # store new database field name
            $this->DBFields["DBFieldName"] = $NewDBFieldName;
        }
        else
        {
            # set new field name equal to old field name
            $NewDBFieldName = $OldDBFieldName;
        }

        # if new type supplied
        if ($NewType != NULL)
        {
            # grab old field type
            $OldFieldType = MetadataField::$FieldTypePHPEnums[$this->DBFields["FieldType"]];

            # store new field type
            $this->UpdateValue("FieldType", MetadataField::$FieldTypeDBEnums[$NewType]);
        }

        # if this is not a temporary field
        if ($this->Id() >= 0)
        {
            # modify field in DB as appropriate for field type
            $DB = $this->DB;
            $FieldType = MetadataField::$FieldTypePHPEnums[$this->DBFields["FieldType"]];
            switch ($FieldType)
            {
                case MetadataSchema::MDFTYPE_TEXT:
                case MetadataSchema::MDFTYPE_PARAGRAPH:
                case MetadataSchema::MDFTYPE_URL:
                    # alter field declaration in Resources table
                    $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                               .$OldDBFieldName."` `"
                               .$NewDBFieldName."` TEXT "
                               .($this->DBFields["Optional"] ? "" : "NOT NULL"));
                    break;

                case MetadataSchema::MDFTYPE_NUMBER:
                case MetadataSchema::MDFTYPE_USER:
                    # alter field declaration in Resources table
                    $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                               .$OldDBFieldName."` `"
                               .$NewDBFieldName."` INT "
                               .($this->DBFields["Optional"] ? "" : "NOT NULL"));
                    break;

                case MetadataSchema::MDFTYPE_POINT:
                    $Precision = $this->UpdateValue("PointPrecision",
                                                    DB_NOVALUE);
                    $Digits    = $this->UpdateValue("PointDecimalDigits",
                                                    DB_NOVALUE);
                    $DB->Query("ALTER TABLE Resources CHANGE COLUMN "
                               ."`".$OldDBFieldName."X` "
                               ."`".$NewDBFieldName."X`".
                               " DECIMAL(".$Precision.",".$Digits.")");
                    $DB->Query("ALTER TABLE Resources CHANGE COLUMN "
                               ."`".$OldDBFieldName."Y` "
                               ."`".$NewDBFieldName."Y`".
                               " DECIMAL(".$Precision.",".$Digits.")");
                    break;

                case MetadataSchema::MDFTYPE_FILE:
                    # if DB field name has changed
                    if ($NewDBFieldName != $OldDBFieldName)
                    {
                        # alter field declaration in Resources table
                        $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                                   .$OldDBFieldName."` `"
                                   .$NewDBFieldName."` TEXT");
                    }
                    break;

                case MetadataSchema::MDFTYPE_IMAGE:
                    # if DB field name has changed
                    if ($NewDBFieldName != $OldDBFieldName)
                    {
                        # alter field declaration in Resources table
                        $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                                   .$OldDBFieldName."` `"
                                   .$NewDBFieldName."` INT");
                    }
                    break;

                case MetadataSchema::MDFTYPE_FLAG:
                    # alter field declaration in Resources table
                    $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                               .$OldDBFieldName."` `"
                               .$NewDBFieldName."` INT"
                               ." DEFAULT ".intval($this->DefaultValue()));

                    # set any unset values to default
                    $DB->Query("UPDATE Resources SET `".$NewDBFieldName
                            ."` = ".intval($this->DefaultValue())
                            ." WHERE `".$NewDBFieldName."` IS NULL");
                    break;

                case MetadataSchema::MDFTYPE_DATE:
                    # if new type supplied and new type is different from old
                    if (($NewType != NULL) && ($NewType != $OldFieldType))
                    {
                        # if old type was time stamp
                        if ($OldFieldType == MetadataSchema::MDFTYPE_TIMESTAMP)
                        {
                            # change time stamp field in resources table to begin date
                            $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                                       .$OldDBFieldName."` `"
                                       .$NewDBFieldName."Begin` DATE "
                                       .($this->DBFields["Optional"] ? "" : "NOT NULL"));

                            # add end date and precision fields
                            $DB->Query("ALTER TABLE Resources ADD COLUMN `".$NewDBFieldName."End"
                                       ."` DATE");
                            $DB->Query("ALTER TABLE Resources ADD COLUMN `".$NewDBFieldName."Precision`"
                                       ." INT ".($Optional ? "" : "NOT NULL"));

                            # set precision to reflect time stamp content
                            $DB->Query("UPDATE Resources SET `".$NewDBFieldName."Precision` = "
                                       .(DATEPRE_BEGINYEAR|DATEPRE_BEGINMONTH|DATEPRE_BEGINDAY));
                        }
                        else
                        {
                            exit("<br>ERROR:  Attempt to convert metadata field to date from type other than timestamp<br>\n");
                        }
                    }
                    else
                    {
                        # change name of fields
                        $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                                   .$OldDBFieldName."Begin` `"
                                   .$NewDBFieldName."Begin` DATE "
                                   .($this->DBFields["Optional"] ? "" : "NOT NULL"));
                        $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                                   .$OldDBFieldName."End` `"
                                   .$NewDBFieldName."End` DATE "
                                   .($this->DBFields["Optional"] ? "" : "NOT NULL"));
                        $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                                   .$OldDBFieldName."Precision` `"
                                   .$NewDBFieldName."Precision` INT "
                                   .($this->DBFields["Optional"] ? "" : "NOT NULL"));
                    }
                    break;

                case MetadataSchema::MDFTYPE_TIMESTAMP:
                    # if new type supplied and new type is different from old
                    if (($NewType != NULL) && ($NewType != $OldFieldType))
                    {
                        # if old type was date
                        if ($OldFieldType == MetadataSchema::MDFTYPE_DATE)
                        {
                            # change begin date field in resource table to time stamp
                            $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                                       .$OldDBFieldName."Begin` `"
                                       .$NewDBFieldName."` DATETIME "
                                       .($this->DBFields["Optional"] ? "" : "NOT NULL"));

                            # drop end date and precision fields
                            $DB->Query("ALTER TABLE Resources DROP COLUMN `"
                                       .$OldDBFieldName."End`");
                            $DB->Query("ALTER TABLE Resources DROP COLUMN `"
                                       .$OldDBFieldName."Precision`");
                        }
                        else
                        {
                            exit("<br>ERROR:  Attempt to convert metadata field to time stamp from type other than date<br>\n");
                        }
                    }
                    else
                    {
                        # change name of field
                        $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                                   .$OldDBFieldName."` `"
                                   .$NewDBFieldName."` DATETIME "
                                   .($this->DBFields["Optional"] ? "" : "NOT NULL"));
                    }
                    break;

                case MetadataSchema::MDFTYPE_TREE:
                case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
                case MetadataSchema::MDFTYPE_OPTION:
                    break;
            }

            # if qualifier DB field exists
            if ($DB->FieldExists("Resources", $OldDBFieldName."Qualifier"))
            {
                # rename qualifier DB field
                $DB->Query("ALTER TABLE Resources CHANGE COLUMN `"
                           .$OldDBFieldName."Qualifier` `"
                           .$NewDBFieldName."Qualifier` INT ");
            }
        }
    }

    # convenience function to supply parameters to Database->UpdateValue()
    function UpdateValue($FieldName, $NewValue)
    {
        return $this->DB->UpdateValue("MetadataFields", $FieldName, $NewValue,
                               "FieldId = ".intval($this->DBFields["FieldId"]),
                               $this->DBFields);
    }

    # normalize field name for use as database field name
    function NormalizeFieldNameForDB($Name)
    {
        return preg_replace("/[^a-z0-9]/i", "", $Name);
    }

    # add any needed database fields and/or entries
    function AddDatabaseFields()
    {
        # grab values for common use
        $DB = $this->DB;
        $FieldName = $this->Name();
        $DBFieldName = $this->DBFieldName();
        $Optional = $this->Optional();
        $DefaultValue = $this->DefaultValue();

        # set up field(s) based on field type
        switch ($this->Type())
        {
            case MetadataSchema::MDFTYPE_TEXT:
            case MetadataSchema::MDFTYPE_PARAGRAPH:
            case MetadataSchema::MDFTYPE_URL:
                # add field to resources table (if not already present)
                if (!$DB->FieldExists("Resources", $DBFieldName))
                {
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `".$DBFieldName
                               ."` TEXT ".($Optional ? "" : "NOT NULL"));
                }

                # if default value supplied
                if ($DefaultValue != NULL)
                {
                    # set all existing records to default value
                    $DB->Query("UPDATE Resources SET `"
                               .$DBFieldName."` = '".addslashes($DefaultValue)."'");
                }
                break;

            case MetadataSchema::MDFTYPE_NUMBER:
                # add field to resources table (if not already present)
                if (!$DB->FieldExists("Resources", $DBFieldName))
                {
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `".$DBFieldName
                               ."` INT ".($Optional ? "" : "NOT NULL"));
                }

                # if default value supplied
                if ($DefaultValue != NULL)
                {
                    # set all existing records to default value
                    $DB->Query("UPDATE Resources SET `"
                               .$DBFieldName."` = '".addslashes($DefaultValue)."'");
                }
                break;

            case MetadataSchema::MDFTYPE_POINT:
                if (!$DB->FieldExists("Resources", $DBFieldName."X"))
                {
                    $Precision = $this->UpdateValue("PointPrecision",
                                                    DB_NOVALUE);
                    $Digits    = $this->UpdateValue("PointDecimalDigits",
                                                    DB_NOVALUE);

                    $DB->Query("ALTER TABLE Resources ADD COLUMN `"
                               .$DBFieldName."X`".
                               " DECIMAL(".$Precision.",".$Digits.")");
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `"
                               .$DBFieldName."Y`".
                               " DECIMAL(".$Precision.",".$Digits.")");
                }

                break;
            case MetadataSchema::MDFTYPE_FLAG:
                # if field is not already present in database
                if (!$DB->FieldExists("Resources", $DBFieldName))
                {
                    # add field to resources table
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `".$DBFieldName
                               ."` INT DEFAULT ".intval($DefaultValue));

                    # set all existing records to default value
                    $DB->Query("UPDATE Resources SET `"
                               .$DBFieldName."` = ".intval($DefaultValue));
                }
                break;

            case MetadataSchema::MDFTYPE_USER:
                # add field to resources table (if not already present)
                if (!$DB->FieldExists("Resources", $DBFieldName))
                {
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `".$DBFieldName
                               ."` INT ".($Optional ? "" : "NOT NULL"));
                }
                break;

            case MetadataSchema::MDFTYPE_FILE:
                # add fields to resources table (if not already present)
                if (!$DB->FieldExists("Resources", $DBFieldName))
                {
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `"
                               .$DBFieldName."` TEXT");
                }
                break;

            case MetadataSchema::MDFTYPE_IMAGE:
                # add fields to resources table (if not already present)
                if (!$DB->FieldExists("Resources", $DBFieldName))
                {
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `"
                               .$DBFieldName."` INT");
                }
                break;

            case MetadataSchema::MDFTYPE_DATE:
                # add fields to resources table (if not already present)
                if (!$DB->FieldExists("Resources", $DBFieldName."Begin"))
                {
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `".$DBFieldName."Begin`"
                               ." DATE ".($Optional ? "" : "NOT NULL"));
                }
                if (!$DB->FieldExists("Resources", $DBFieldName."End"))
                {
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `".$DBFieldName."End`"
                               ." DATE");
                }
                if (!$DB->FieldExists("Resources", $DBFieldName."Precision"))
                {
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `".$DBFieldName."Precision`"
                               ." INT ".($Optional ? "" : "NOT NULL"));
                }
                break;

            case MetadataSchema::MDFTYPE_TIMESTAMP:
                # add fields to resources table (if not already present)
                if (!$DB->FieldExists("Resources", $DBFieldName))
                {
                    $DB->Query("ALTER TABLE Resources ADD COLUMN `".$DBFieldName
                               ."` DATETIME ".($Optional ? "" : "NOT NULL"));
                }
                break;

            case MetadataSchema::MDFTYPE_TREE:
            case MetadataSchema::MDFTYPE_CONTROLLEDNAME:
            case MetadataSchema::MDFTYPE_OPTION:
                break;

            default:
                exit("<br>ERROR:  Attempt to add database fields for illegal metadata field type<br>\n");
                break;
        }
    }

    # get/set field order positions
    function OrderPosition($OrderType, $NewValue = DB_NOVALUE)
    {
        switch ($OrderType)
        {
            case MetadataSchema::MDFORDER_DISPLAY:
                return $this->UpdateValue("DisplayOrderPosition", $NewValue);
                break;

            case MetadataSchema::MDFORDER_EDITING:
                return $this->UpdateValue("EditingOrderPosition", $NewValue);
                break;

            default:
                exit("invalid order type passed to MetadataField::OrderPosition");
                break;
        }
    }
}


?>
