<?php
define('idDiGIR_recStr','$Id: DiGIR_recStr.php,v 1.8 2004/02/03 14:04:38 rdg Exp $');
if (defined('idDIGIR_VERSION_REQUEST'))
    return;

/*
* $Id: DiGIR_recStr.php,v 1.8 2004/02/03 14:04:38 rdg Exp $
*/
require_once('DiGIR_globals.php');
require_once('DiGIR_utils.php');
require_once('DiGIR_config.php');
require_once('adodb/adodb.inc.php');

/**************
== Class DiGIRRecord ==
Implements a mechanism for constructing DiGIR records
given a conceptual schema, database configuration,
a record structure definition, and a database result set.

Example of a record structure definition fragment in a search request
{{{
	<structure>
		<xsd:element name="record">
			<xsd:complexType>
				<xsd:sequence>
					<xsd:element ref="darwin:DateLastModified"/>
					<xsd:element ref="darwin:InstitutionCode"/>
					<xsd:element ref="darwin:CollectionCode"/>
					<xsd:element ref="darwin:CatalogNumber"/>
					<xsd:element ref="darwin:ScientificName"/>
					<xsd:element ref="darwin:Genus"/>
					<xsd:element ref="darwin:Species"/>
				</xsd:sequence>
			</xsd:complexType>
		</xsd:element>
	</structure>
}}}

A record template is constructed as an array of elements.  Each element of the
array contains:
{{{
Data Node:

 [0] type of this structure (0 = data node, 1 = edge)
 [1] output element name
 [2] data buffer (filled for construction of each record)
 [3] optional formatting string
 [4] name of source table/view
 [5] name of source field

 note: need to address value multiplicity here.  If an element may appear more
 than once in a record then?

Edge Node:

 [0] type of this structure (0 = data node, 1 = edge)
 [1] output element name
 [2] array of data elements
}}}

**Note:** if the formatting string entry is NULL, then the default output generated
by the database connection is used.


The XMLSchema fragment above would be encoded in the internal data structure as:
{{{
$rs = array();
array_push($rs,array(0,'darwin:DateLastModified',NULL,NULL,'root','DLM'));
array_push($rs,array(0,'darwin:InstitutionCode',NULL,NULL,'root','ICode'));
array_push($rs,array(0,'darwin:CollectionCode',NULL,NULL,'root','CCode'));
array_push($rs,array(0,'darwin:ScientificName',NULL,NULL,'root','SciName'));
array_push($rs,array(0,'darwin:Genus',NULL,NULL,'root','Genus'));
array_push($rs,array(0,'darwin:Species',NULL,NULL,'root','Species'));
}}}

The record structure template data buffer entries are filled by extracting
content from the data source one record at a time.

=== Filling the Data Buffer ===

  * [http://www2002.org/CDROM/refereed/226/ about nested sql queries]
  * [http://phplens.com/lens/php-book/optimizing-debugging-php.php about optimizing php]


First, generate result set that contains records in the root table that match the
filter constraints (call this the "index set").  This is minimally a set of
record indexes (row keys) that can be used to locate all the field element
values for each record that is to be returned.

{{{
  Root
  +-----------------+
  |Key    f1    f2  |
  +=================+
  |12     ab    eef |
  +-----------------+
  |14     ac    eeg |
  +-----------------+
  |15     aa    eio |
  +-----------------+
  |18     af    erg |
  +-----------------+
}}}


For each row of the index set
    extract relevant fields from the root table
    for each group of fields in sub tables
      create query to extract row(s) that match the current record index
      retrieve records based on constructed query

{{{
        +------+
        | Root |
        +,"-`.-+
       ,"     `-.
  +--,"-+      +-`.--+
  |Sub_1|      |Sub_2|
  +-----+      +-----+

for each record in root
  get root fields
  select fields from sub_1 where sub_1.key = root key
  select fields from sub_2 where sub_2.key = root key
}}}
***************/
class DiGIRRecord
{
    //root is the record structure
    var $root;
    
    //list of tables
    //key is name of the table
    //value is NULL or after retrieveNextRecord, is an array of values
    //for the current record (keyed by field name)
    //** there may be more than a single row for a table in tableList
    var $tableList;
    
    //key is name of table, value is an array of field names.
    var $tableFields;
    
    //namespaces used in the record schema.  These are appended to the 
    //content element in the generated records.
    var $nameSpaces;
    
    //The name of the root table
    var $rootName;
    
    //the handle for the XMLSchema namespace
    var $schemaNS;
    
    //handle for the XMLSchema-instance namespace
    
    /***
    * Constructor
    * 
    * Just initializes stuff.
    */
    function DiGIRRecord()
    {
        $this->root = NULL;
        $this->tableList = array();
        $this->tableFields = array();
        $this->nameSpaces = array();
        $this->schemaNS = '';
        $this->schemaInst = 'xsi';
    }

    /**
    * Constructs the record template given an XPath object that has
    * loaded the record structure.
    *
    * @param $xp = XPath object with the record schema loaded
    *
    * The root element is an element with name='record'.
    * This should be a complex type that contains a <sequence> or <all>
    * element (technically the name of this element is ignored by this code).
    */
    function buildTemplate(&$xp)
    {
        //need to search the XPath object for all xmlns attributes
        $this->nameSpaces = getNamespaces($xp,$this->nameSpaces);

        //resolve the tag used for XMLSchema
        $this->schemaNS = "xs";
        foreach ($this->nameSpaces as $k=>$v)
        {
            if ($v == XMLSCHEMANS)
                $this->schemaNS = $k;
        }
        $schemaNStmp = $this->schemaNS;

        if ($this->schemaNS == $this->nameSpaces[0])
        {
            //XMLSchema NS is alredy the default namespace for the record
            //structure, so don't re-use it.
            $this->schemaNS = '';
        }
        else
        {
            $this->schemaNS .= ':';
        }
        
        //namespace label in use
        //Locate the node that identifies the first element.
        $startPath = "//".$this->schemaNS."element[@name='record']/".
                    $this->schemaNS."complexType/*/child::*";
        DebugMsg("record schema ns = ".$this->schemaNS);
        DebugMsg("Start Path = ".$startPath);
        DebugMsg("Namespaces = ".$this->listNamespaces());
        
        //Start constructing the template from the match, recursing into the
        //record structure if necessary
        $rs = $this->_buildTemplate($xp,$startPath,$this->schemaNS);

        if (count($rs) == 0) {
            appendError(DC_INVALIDQUERY,
                "Could not find a valid Record Structure in the request.",
                DIAG_ERROR);
        }
        
        //remove references to name spaces that are not used in the record
        //structure.  XMLSchema NS is used when element content is NILL
        $tmp = array();
        foreach($rs as $node)
        {
            if ($node[0] == 0)
            {
                foreach ($this->nameSpaces as $k => $v)
                {
                    if ($node[6] == $v)
                        $tmp[$k] = $v;
                }
            }
        }
        $this->nameSpaces = $tmp;
        //add the xmlschema namespace to the list of namespaces used by the
        //record builder
        $this->addNamespace($schemaNStmp,XMLSCHEMANS);

		//resolve the tag used for XMLSchema instance
		$this->schemaInst = NULL;
		foreach ($this->nameSpaces as $k=>$v)
		{
		    if ($v == XMLSCHEMAINST)
		      $this->schemaInst = $k;
		}
		if ($this->schemaInst === NULL)
		{
		    $this->schemaInst = "xsi";
            $this->addNamespace($this->schemaInst,XMLSCHEMAINST);
		}
        $this->schemaInst .= ':';

        //The resulting record structure
        $this->root = $rs;

        DebugMsg("Namespaces = ".$this->listNamespaces());

    }
    
    /**
    * Internal routine that uses the XPath library to discern the
    * intended record structure.
    * 
    * @param $xp = XPath object with record schema loaded
    * @param $path = search path to locate the elements for a particular edge
    * @param $schemaNS = the namespace handle for the xml schema elements
    */
    function _buildTemplate(&$xp, $path, $schemaNS)
    {
        $nodes = $xp->evaluate($path);
        $ares = array();
        foreach ($nodes as $apath)
        {
            $nd = $xp->getNode($apath);
            if ($nd['name'] == $schemaNS."element")
            {
                $attr = $xp->getAttributes($apath,"ref");
                if ($attr != NULL)
                {
                    // 0       1            2        3               4             5              6                 7
                    //type, element name, value, format string, source table, source field, element name space, output tag
                    //split the name and get it's namespace
                    //** item 2 is now an array
                    $tmp = split(':',$attr);
                    $nm = $attr;
                    $ns = "";
                    if (count($tmp) > 1)
                    {
                        $nm = $tmp[1];
                        $ns = $this->nameSpaces[$tmp[0]];
                    }
                    else
                    {
                        //the attribute is in the default namespace
                        $nm = $attr;
                        $ns = $this->nameSpaces[0];
                    }
                    array_push($ares,array(0,$nm,NULL,NULL,NULL,NULL,$ns,$attr));
                }
                else
                {
                    $attr = $xp->getAttributes($apath,"name");
                    $npath = substr($path,0,strlen($path)-1);
                    $npath = $npath.$schemaNS."element[@name='".$attr."']/".$schemaNS."complexType/*/child::*";
                    $tmpa = $this->_buildTemplate($xp,$npath,$schemaNS);
                    array_push($ares,array(1,$attr,$tmpa));
                }
            }
        }
        return $ares;
    }
    /**
    * Creates internal data source table structure definition.
    *
    * Loads the table names and fields for the data sources of the record
    * structure by examining the Configuration information.  Also generates
    * the list of data source tables (this->tableList)
    * @param $Cfg
    */
    function fillStructureTablesFromConfig(&$Cfg)
    {
        $this->_fillStructureTablesFromConfig($this->root,$Cfg);
    }
    
    /**
    * Internal function called by fillStructureTablesFromConfig()
    * @param $recStr = reference to a level in the internal record structure
    * @param $Cfg = reference to the configuration data set.
    */
    function _fillStructureTablesFromConfig(&$recStr, &$Cfg)
    {
        $n = count($recStr);
        for ($i=0; $i < $n; $i++)
        {
            switch ($recStr[$i][0])
            {
            case 0:
                $id = $Cfg->getConceptByName($recStr[$i][1],$recStr[$i][6]);
                $concept = $Cfg->getConcept($id);
                // Consider only mapped and returnable concepts (1=table, 2=field, 7=returnable)
                if (($concept != NULL) && ($concept[1]) && ($concept[2]) && ($concept[7]))
                {
                    $recStr[$i][4] = strtolower($concept[1]); //table
                    $recStr[$i][5] = strtolower($concept[2]); //field
                    if (!array_key_exists($concept[1],$this->tableList))
                        $this->tableList[$concept[1]] = NULL;
                    if (!array_key_exists($concept[1],$this->tableFields))
                        $this->tableFields[$concept[1]] = array();
                    if(!in_array($concept[2],$this->tableFields[$concept[1]]))
                        array_push($this->tableFields[$concept[1]],$concept[2]);
                }
                break;
            case 1:
                $this->_fillStructureTablesFromConfig($recStr[$i][2],$Cfg);
                break;
            }
        }
    }

    /**
    * Fills the data buffer with the next row of data and returns an XML 
    * string representation of a record from the current data buffer contents.
    * The key of the array is the name of the table/view from which the data
    * was drawn.  This is used to locate the appropriate table from which to
    * extract a value.
    */
    function generateRecord(&$Cfg, &$DBcn, &$RSroot, $clientkey)
    {
        $this->_clearDataBuffer($this->root);
        $this->_retrieveNextRecord($Cfg, $DBcn, $RSroot);
 
        if ($clientkey == '')
            $rec = '<record>';
        else
            $rec = "<record clientKey=\"".$clientkey."\">";

        $rec .= $this->_generateRecord($this->root);

        $rec .= '</record>';

        return $rec;
    }
    
    /**
    * Internal routine that recursively traverses the
    * record structure, clearing all it's values.
    */
    function _clearDataBuffer(&$recStr)
    {
        $n = count($recStr);
        for ($i=0; $i < $n; $i++)
        {
            switch ($recStr[$i][0])
            {
            case 0:
                $recStr[$i][2] = NULL;
                break;
            case 1:
                $this->_clearDataBuffer($recStr[$i][2]);
            }
        }
    }

    /**
    * Internal function that retrieves the next record from the data store.
    * Returns 0 if ok, otherwise negative.
    * @param Cfg = Configuration object
    * @param DBcn = open ADOdb connection to the source database
    * @param RSroot = adodb resultset created with 
    *                 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
    *                The resultset cursor is moved forward one record. 
    *
    * TODO: relax requirement for all fields in root table and just use
    * an index set instead.  Need to test efficiency issues here- recursive
    * queries for each record (cpu !) or retrieve all elements from all tables
    * by joining on root table (memory !).
    *
    * These issues *could* be made mute by implementing output caching before
    * even talking to the database.  See the PEAR cache library for details
    * about some nice caching options.
    *
    * TODO: Need to test how adodb handles having potentially very large number
    * of small result sets.  It would probably be better to create a couple of large ones
    * but then the issue becomes efficiently locating the relevant rows in the 
    * subordinate result sets.  adodb doesn't seem to be too efficient for this.
    */
    function _retrieveNextRecord(&$Cfg, &$DBcn, &$RSroot)
    {
        if ($RSroot->EOF)
        {
            return -1;
        }
        $this->rootName = strtolower($Cfg->RootTableName());
        $keys = array_keys($this->tableList);
        $n = count($keys);
        
        $tmpdata = $RSroot->GetArray(1);

        $encoding = $Cfg->connectionEncoding;

        $this->_prepareRecordSet($tmpdata, $encoding);
        
        //** the root will always be unique for a record - so only get one row
        $this->tableList[$this->rootName] = array(0=>$tmpdata[0]);

        //the key for this record
        //TODO: the database schema may have tables further away in the hierarchy
        //that are not keyed against the root table.  
        //This information is contained in the config structure - need
        //to make better use of it here.
        $rowKeyName = $Cfg->tableKeyFieldName(NULL);
        $rowKeyVal = '';
        if (isset($this->tableList[$this->rootName][0][$rowKeyName]))
            $rowKeyVal = $this->tableList[$this->rootName][0][$rowKeyName];

        for ($i=0; $i < $n; $i++)
        {
            if (strcasecmp($this->rootName,$keys[$i]) == 0)
            {
                //root table is already created, just pull the current
                //row from the result set
                //$tmpdata = $RSroot->GetArray(1);
                //$this->tableList[$rootName] = $tmpdata[0];
            }
            else
            {               
                $rowKeyName = $Cfg->tableKeyFieldName($keys[$i]);
                $sql = "SELECT * FROM ".$keys[$i]." WHERE (".$rowKeyName."=".$rowKeyVal.")";
                $RS = & $DBcn->Execute($sql);
                $tmpdata = $RS->GetArray();

                $this->_prepareRecordSet($tmpdata, $encoding);
                
                //TODO: need to trap errors here.
                $this->tableList[$keys[$i]] = $tmpdata;
                $RS->Close();
            }
        }
        if(_DEBUG)
        {
            echo "<d><![CDATA[";
            print_r($this->tableList);
            echo "]]></d>\n";
        }
        return 0;
    }

    /**
    * Internal function called by retrieveNextRecord(), it converts retrieved values
    * to UTF-8 if db charset encoding is different, and also convert all field names
    * to lower case to avoid problems when referencing the elements later.
    * @param $RS = reference to the record set
    * @param $encoding = charset encoding used by resource database.
    */
    function _prepareRecordSet(&$rowdata, $encoding)
    {
        foreach ($rowdata as $rowid => $fields)
        {
            // Convert all keys (field names) to lower case
            $rowdata[$rowid] = array_change_key_case($rowdata[$rowid], CASE_LOWER);

            // If db charset encoding is different from UTF-8 and conversion function exists,
            // convert values to UTF-8
            if (strcasecmp($encoding, 'UTF-8') and function_exists('mb_convert_encoding'))
            {
                foreach ($fields as $field => $value)
                {
                    if ($value <> NULL)
                    {
                        $rowdata[$rowid][$field] = mb_convert_encoding($value, 'UTF-8', $encoding);
                    }
                }
            }
        }
    }
    
    /**
    * Internal routine that recursively traverses the record structure filling the
    * buffer values from the record set captured by retrieveNextRecord() and also
    * outputting them as an xml structure.
    */
    function _generateRecord(&$recStr)
    {
        $res = '';        

        //for each element in record structure
        $n = count($recStr);
        for ($i=0; $i<$n; $i++)
        {
            switch ($recStr[$i][0])
            {
            case 0:
                //data node
                $tmp = $recStr[$i][4]; // reference to the record set (table or view name)
                if ($tmp === NULL)
                {
                    //the element content is NULL
                    $recStr[$i][2] = NULL;

                    $res .= '<'.$recStr[$i][7]." ".$this->schemaInst."nil='true'/>\n";
                }
                else
                {
                    foreach ($this->tableList[$tmp] as $rowv)
                    {
                        if (_DEBUG)
                        {
                            echo "<d><![CDATA[fillBuffer\n";
                            print_r($rowv);
                            echo "]]></d>\n";
                        }
                        if (!isset($recStr[$i][2]))
                        {
                            $recStr[$i][2] = array();
                        }
                        $val = $rowv[$recStr[$i][5]];
                        array_push($recStr[$i][2],$val); // store in data buffer
                        
                        $restmp = '';
                        //is the content NULL?
                        if ($val === NULL)
                        {
                            $res .= '<'.$recStr[$i][7]." ".$this->schemaInst."nil='true'/>\n";
                        }
                        else
                        {
                            $restmp = '<'.$recStr[$i][7].'>';
                            //do we have a format spec for this value ([3])?
                            if ($recStr[$i][3] == NULL)
                            {
                                $restmp .= escapeXMLSpecialChars($val);
                            }
                            else
                            {
                                $restmp .= escapeXMLSpecialChars(sprintf($recStr[$i][3],$val));
                            }
                            $restmp .= '</'.$recStr[$i][7].">\n";
                        }
                        $res .= $restmp;
                    }
                }
                break;
            case 1:
                //recurse into structure
                $restmp = '';
                if ($recStr[$i][1] != NULL)
                {
                    $restmp .= '<'.$recStr[$i][1].'>';
                }
                $restmp .= $this->_generateRecord($recStr[$i][2]);
                if ($recStr[$i][1] != NULL)
                {
                    $restmp .= '</'.$recStr[$i][1].">\n";
                }
                $res .= $restmp;
                break;
            default:
                break;
            }
        }
        return $res;
    }
    
    /**
    * Clear the list of namespaces
    */
    function clearNamespaces()
    {
        $this->nameSpaces = array();
    }
    
    /**
    * Adds a namespace to the internal list of namespaces
    *
    * @param string name = handle of namespace (null = default name space)
    * @param string space = namespace
    */
    function addNamespace($name,$space)
    {
        $this->nameSpaces[$name] = $space;
    }

    /**
    * lists the namespaces as a string suitable for inclusion in an XML element
    * @return string concatenated namespaces
    */
    function listNamespaces()
    {
        $res = "";
        foreach ($this->nameSpaces as $nm => $va)
        {
            if ($nm === 0)
            {
                $res .= " xmlns='".$va."'";
            }
            else
            {
                $res .= " xmlns:".$nm."='".$va."'";
            }
        }
        return $res;
    }
        
    /**
    * Returns true if the specified namespace exists in the list of namespaces
    */
    function hasNamespace($ns)
    {
        foreach ($this->nameSpaces as $k =>$v)
        {
            if ($v == $ns)
                return true;
        }
        return $false;
    }
        
    // for debugging
    function buildDummyTemplate()
    {
        //add the name space
        $this->nameSpaces["darwin"] = "http://tbd.org/darwin";
        
        //0 = type: 0 = node, 1= edge
        //1 = concept name
        //2 = value
        //3 = format string
        $rs = array();
        array_push($rs,array(0,'darwin:InstitutionCode',NULL,NULL,NULL,NULL));
        array_push($rs,array(0,'darwin:CollectionCode',NULL,NULL,NULL,NULL));
        array_push($rs,array(0,'darwin:ScientificName',NULL,NULL,NULL,NULL));

        $rs1 = array();
        array_push($rs1,array(0,'darwin:Genus',NULL,NULL,NULL,NULL));
        array_push($rs1,array(0,'darwin:Species',NULL,NULL,NULL,NULL));
        array_push($rs,array(1,'name',$rs1));
        
        $rs1 = array();
        array_push($rs1,array(0,'darwin:Locality',NULL,NULL,NULL,NULL));
        array_push($rs1,array(0,'darwin:Latitude',NULL,NULL,NULL,NULL));
        array_push($rs1,array(0,'darwin:Longitude',NULL,NULL,NULL,NULL));
        array_push($rs,array(1,'location',$rs1));

        $this->root = $rs;
    }
}

class DiGIRScanRecord extends DiGIRRecord
{
    function DiGIRScanRecord()
    {
        $this->DiGIRRecord();
    }

    function _retrieveNextRecord(&$Cfg, &$DBcn, &$RSroot)
    {
        if ($RSroot->EOF)
        {
            return -1;
        }
        $this->rootName = $Cfg->RootTableName();
        $keys = array_keys($this->tableList);
        $n = count($keys);

        $tmpdata = $RSroot->GetArray(1);

        $encoding = $Cfg->connectionEncoding;

        $this->_prepareRecordSet($tmpdata, $encoding);

        $this->tableList[$this->rootName] = $tmpdata[0];

        return 0;
    }

    /**
    * Internal routine called by generateRecord.
    * Recursively traverses the record structure filling the buffer
    * values from the record set buffer captured by _retrieveNextRecord()
    * For scan records this really expects a single element node
    */
    function _generateRecord(&$recStr)
    {
        $res = '';

        //for each element in record structure
        $n = count($recStr);
        for ($i=0; $i<$n; $i++)
        {
            switch ($recStr[$i][0])
            {
            case 0:
                //data node
                $tmp = $this->rootName;
                if (($tmp != NULL) && (array_key_exists($tmp,$this->tableList)))
                {
                    $ev = $this->tableList[$tmp];
                    if (isset($ev))
                    {
                        //retrieve value from record set
                        $evf = $ev[0];
                        $evc = $ev[1];
                        if ((isset($evf)) && isset($evc))
                        {
                            $recStr[$i][2] = $evf;
                            $recStr[$i][8] = $evc;
                            
                            if (is_null($evf))
                            {
                                $restmp = "<".$recStr[$i][7]." count='".$evc."' ";
                                $restmp .= $this->schemaInst."nil='true'/>\n";
                            }
                            else
                            {
                                $restmp = "<".$recStr[$i][7]." count='".$evc."'>";
                                if ($recStr[$i][3] == NULL)
                                {
                                    $restmp .= escapeXMLSpecialChars($evf);
                                }
                                else
                                {
                                    $restmp .= escapeXMLSpecialChars(sprintf($recStr[$i][3],$evf));
                                }
                                $restmp .= '</'.$recStr[$i][7].">\n";
                            }
                            $res .= $restmp;
                        }
                    }
                }
                else
                    $recStr[$i][2] = NULL;
                break;
            default:
                 break;
            }
        }
        return $res;
    }

    //return array [0] = field name, [1] = table name
    function getRequestNode()
    {
        //this will always be the first (and only) node of the root structure
        $ares = array();
        $ares['table'] = $this->root[0][4];
        $ares['field'] = $this->root[0][5];
        return $ares;
    }

}
/*
//some debug code

$tmp = new ConfigBuilder("c:/digir/config/dwc2config.xml");
$Config = $tmp->Config;

$tmp = new DiGIRRecord();

//load from xml schema
$tmp->buildDummyTemplate();

echo '<pre>';
print_r($tmp->root);
echo '</pre>';

$tmp->fillStructureTablesFromConfig($Config);

echo '<pre>';
print_r($tmp->tableList);
echo '</pre>';

echo '<pre>';
print_r($tmp->tableFields);
echo '</pre>';

//create recordset

$cn = &ADONewConnection('ado_access');
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$cn->PConnect("Provider=Microsoft.JET.OLEDB.4.0;Data Source=\"c:\database\digir\example.mdb\"");

$sql = "select * from zqrytable where scientificname like '%otus%'";
$RS = &$cn->Execute($sql);

while (!$RS->EOF)
{
    $tmp->retrieveNextRecord($Config, $cn, $RS);
    $tmp->fillDataBuffer();
    $res = $tmp->generateRecord();
    echo $res;
    echo "\n";
}

$tmp->clearDataBuffer();
$res = $tmp->generateRecord();
echo $res;
echo "\n";

flush();
*/
?>
