Public Member Functions | Data Fields

SQL2XML Class Reference

Class for exporting XML documents stored in a relational database to a domxml representation. More...

Public Member Functions

 SQL2XML ($database_connection, $a_obj_id)
 constructor init db-handler
 _SQL2XML ()
 retrieveHeader ()
 getXMLDocument ()
 Gets a XML document from the database and returns the domxml representation of it.
 buildXML ()
 getStructure ($a_xml_id)
 getTree ($a_lo_id)
 gets specified element and all its subelements, builds with this information a domDocument and return the domxml representation of this xml document
 getNode ($a_node_id)
 gets specified element with all attributes and text elements return the domDocument
 getElementName ($a_node_id)
 gets specified element
 getElementText ($a_node_id)
 getAttributes ($a_node_id)
 get all attributes of specified element returns false if element has no attributes
 getAttributeValue ($a_lo_id, $a_element, $a_attribute)
 get a single attribute value of a given element and LearningObject

Data Fields

 $domxml
 $obj_id
 $mapping
 $doc
 $hash
 $db
 $nestedset

Detailed Description

Class for exporting XML documents stored in a relational database to a domxml representation.

Author:
Sascha Hofmann <shofmann@databay.de>
Helmut Schottmüller <hschottm@tzi.de>
Version:
Id:
class.SQL2XML.php 4949 2004-09-14 19:13:58Z hschottm

Definition at line 31 of file class.SQL2XML.php.

Member Function Documentation

SQL2XML::_SQL2XML (  ) 

Definition at line 89 of file class.SQL2XML.php.

                            {
                if ($this->doc) {
                        $this->doc->free();
                }
        }

SQL2XML::buildXML (  ) 

Definition at line 129 of file class.SQL2XML.php.

Referenced by getXMLDocument().

        {
                foreach ($this->nestedset as $key => $node_data)
                {
                        $insert = false;
                        switch ($node_data["node_type_id"])
                        {
                                case XML_ELEMENT_NODE:
                                        $node = $this->doc->create_element($node_data["element"]);
                                        // set attributes
                                        if (is_array($node_data["attr_list"]))
                                        {
                                                foreach ($node_data["attr_list"] as $attr => $value)
                                                {
                                                        $node->set_attribute($attr, $value);
                                                }
                                        }
                                        $insert = true;
                                        break;
                                case XML_TEXT_NODE:
                                        $node = $this->doc->create_text_node($node_data["textnode"]);
                                        $insert = true;
                                        break;
                                case XML_COMMENT_NODE:
                                        $node = $this->doc->create_comment($node_data["comment"]);
                                        $insert = true;
                                        break;
                        }

                        if ($insert)
                        {
                                //get parent node
                                $parent = $this->hash[$node_data["parent_node_id"]];
                                //build node
                                if (!$parent) {
                                        $node = $this->doc->append_child($node);
                                } else {
                                        $node = $parent->append_child($node);
                                }
                                $this->hash[$this->nestedset[$key]["node_id"]] = $node;
                        }
                }
        }

Here is the caller graph for this function:

SQL2XML::getAttributes ( $  a_node_id  ) 

get all attributes of specified element returns false if element has no attributes

Parameters:
integer node_id of domNode
Returns:
array all attributes (attr[name] = value) public

Definition at line 375 of file class.SQL2XML.php.

References $data, $q, $res, and $row.

        {
                $q = "SELECT a_name.attribute,a_value.value ".
                         "FROM xml_attribute_idx AS a_idx ".
                         "LEFT JOIN xml_attribute_name AS a_name ON a_idx.attribute_id=a_name.attribute_id ".
                         "LEFT JOIN xml_attribute_value AS a_value ON a_idx.value_id=a_value.value_id ".
                         "WHERE a_idx.node_id = '".$a_node_id."'";
                         
                 //echo $q;exit;

                $res = $this->db->query($q);
                
                if ($res->numRows())
                {
                        while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
                        {
                                $data[$row["attribute"]] = $row["value"];
                        }

                        return $data;
                }

                return false;
        }

SQL2XML::getAttributeValue ( $  a_lo_id,
$  a_element,
$  a_attribute 
)

get a single attribute value of a given element and LearningObject

Parameters:
integer lo_id
string element name
Returns:
string attribute value public

Definition at line 409 of file class.SQL2XML.php.

References $q, $res, and $row.

        {

                $q = "SELECT a_value.value ".
                         "FROM xml_tree AS lo ".
                         "LEFT JOIN lo_element_idx AS e_idx ON lo.node_id = e_idx.node_id ".
                         "LEFT JOIN lo_element_name AS el ON e_idx.element_id = el.element_id ".
                         "LEFT JOIN lo_attribute_idx AS a_idx ON lo.node_id = a_idx.node_id ".
                         "LEFT JOIN lo_attribute_name AS a_name ON a_idx.attribute_id=a_name.attribute_id ".
                         "LEFT JOIN lo_attribute_value AS a_value ON a_idx.value_id=a_value.value_id ".
                         "WHERE xml_id = '".$a_lo_id."' ".
                         "AND lo.struct > 0 ". // <-- need a bitwise AND against 1
                         "AND el.element = '".$a_element."' ".
                         "AND a_name.attribute = '".$a_attribute."'";
                         
                //echo $q;exit;

                $res = $this->db->query($q);
                $row = $res->fetchRow();
        
                return $row[0];
        }

SQL2XML::getElementName ( $  a_node_id  ) 

gets specified element

Parameters:
integer node_id of domNode
Returns:
object domNode public

Definition at line 348 of file class.SQL2XML.php.

References $q, and $res.

        {
                $q = "SELECT leaf_text FROM xml_element_name_leaf ".
                         "WHERE node_id='".$a_node_id."' LIMIT 1";
                //$res = $this->db->query($q);
                $res = $this->db->getRow($q, DB_FETCHMODE_ASSOC);

                return $res["leaf_text"];
        }

SQL2XML::getElementText ( $  a_node_id  ) 

Definition at line 358 of file class.SQL2XML.php.

References $q, and $res.

        {
                $q = "SELECT leaf_text FROM xml_text_leaf ".
                         "WHERE node_id='".$a_node_id."' LIMIT 1";
                //$res = $this->db->query($q);
                $res = $this->db->getRow($q, DB_FETCHMODE_ASSOC);

                return $res["leaf_text"];
        }

SQL2XML::getNode ( $  a_node_id  ) 

gets specified element with all attributes and text elements return the domDocument

Parameters:
integer node_id where to start fetching the xml data
Returns:
object domDocument public

Definition at line 336 of file class.SQL2XML.php.

        {
                return $node;
        }

SQL2XML::getStructure ( $  a_xml_id  ) 

Definition at line 173 of file class.SQL2XML.php.

References $data, $q, $res, and $row.

Referenced by getXMLDocument().

        {
                $q = "SELECT lo.node_id, lo.node_type_id, lo.xml_id, lo.parent_node_id, lo.struct, tx.textnode, comm.comment, el.element, a_name.attribute, a_value.value ".
                         "FROM xml_tree AS lo ".
                         "LEFT OUTER JOIN xml_element_idx AS e_idx ON lo.node_id = e_idx.node_id ".
                         "LEFT OUTER JOIN xml_element_name AS el ON e_idx.element_id = el.element_id ".
                         "LEFT OUTER JOIN xml_text AS tx ON lo.node_id = tx.node_id ".
                         "LEFT OUTER JOIN xml_comment AS comm ON lo.node_id = comm.node_id " .
                         "LEFT OUTER JOIN xml_attribute_idx AS a_idx ON lo.node_id = a_idx.node_id ".
                         "LEFT JOIN xml_attribute_name AS a_name ON a_idx.attribute_id=a_name.attribute_id ".
                         "LEFT JOIN xml_attribute_value AS a_value ON a_idx.value_id=a_value.value_id ".
                         "WHERE xml_id='".$a_xml_id."' ".
                         "ORDER BY lft ASC";
                // 2. variant: I think this is the fastest but you need mysql 4.x in order to use UNION statement
/*
                $q = "SELECT rgt FROM lo_tree ".
                         "WHERE lo_id = '".$a_lo_id."' ".
                         "AND lft = 1";
                
                $res = $this->db->query($q);
                
                while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
                {
                        $rgt_max = $row["rgt"];
                }

                $q = "(SELECT n.lft AS seq_no_1, 4 AS seq_no_2, 0 AS seq_no_3, 0 AS seq_no_4, x.node_id AS seq_no_5, x.textnode AS parsed_text ".
                         "FROM lo_tree n, lo_text x ".
                         "WHERE n.lo_id = '".$a_lo_id."' ".
                         "AND n.lft >= 1 ".
                         "AND n.lft < '".$rgt_max."' ".
                         "AND n.node_id = x.node_id) ".
                         "UNION ".
                         "(SELECT n.lft, 1, 0, 0, 0, t.lft_delimiter ".
                         "FROM lo_tree n, lo_node_type t ".
                         "WHERE n.lo_id = '".$a_lo_id."' ".
                         "AND n.node_type_id = t.node_type_id ".
                         "AND n.lft >= 1 ".
                         "AND n.lft < '".$rgt_max."' ".
                         "AND t.lft_delimiter > '') ".
                         "UNION ".
                         "(SELECT n.lft, 2, 0, 0, 0, e.element ".
                         "FROM lo_element_name e, lo_element_idx e_idx, lo_tree n ".
                         "WHERE n.lo_id = '".$a_lo_id."' ".
                         "AND n.node_id = e_idx.node_id AND ".
                         "e.element_id = e_idx.element_id) ".
                         "UNION ".
                         "(SELECT n.lft, 3, a.attribute_id, 1, 0, CONCAT(' ', a.attribute, '=\"' ) ".
                         "FROM lo_attribute_name a, lo_attribute_idx a_idx, lo_tree n ".
                         "WHERE n.lo_id = '".$a_lo_id."' ".
                         "AND n.lft >= 1 ".
                         "AND n.lft < '".$rgt_max."' ".
                         "AND n.node_id = a_idx.node_id ".
                         "AND a.attribute_id = a_idx.attribute_id) ".
                         "UNION ".
                         "(SELECT n.lft, 3, a_idx.attribute_id, 2, n.node_id, CONCAT( a.value, '\"' ) ".
                         "FROM lo_attribute_value a, lo_attribute_idx a_idx, lo_tree n ".
                         "WHERE n.lo_id = '".$a_lo_id."' ".
                         "AND n.lft >= 1 ".
                         "AND n.lft < '".$rgt_max."' ".
                         "AND n.node_id = a_idx.node_id ".
                         "AND a.value_id = a_idx.value_id) ".
                         "UNION ".
                         "(SELECT n.lft, 9, 0, 0, 0, t.rgt_delimiter ".
                         "FROM lo_tree n, lo_node_type t ".
                         "WHERE n.lo_id = '".$a_lo_id."' ".
                         "AND n.node_type_id = t.node_type_id ".
                         "AND t.rgt_delimiter > '' ".
                         "AND n.lft >= 1 ".
                         "AND n.lft < '".$rgt_max."') ".
                         "UNION ".
                         "(SELECT n.rgt, 10, 0, 0, 0, CONCAT( t.lft_delimiter, '/' ) ".
                         "FROM lo_tree n, lo_node_type t ".
                         "WHERE n.lo_id = '".$a_lo_id."' ".
                         "AND n.node_type_id = t.node_type_id ".
                         "AND n.node_type_id = 1 ".
                         "AND t.lft_delimiter > '' ".
                         "AND n.lft >= 1 ".
                         "AND n.lft < '".$rgt_max."') ".
                         "UNION ".
                         "(SELECT n.rgt, 10, 0, 3, 0, e.element ".
                         "FROM lo_element_name e, lo_element_idx e_idx, lo_tree n ".
                         "WHERE n.lo_id = '".$a_lo_id."' ".
                         "AND n.node_id = e_idx.node_id ".
                         "AND e.element_id = e_idx.element_id) ".
                         "UNION ".
                         "(SELECT n.rgt, 10, 0, 4, 0, t.rgt_delimiter FROM lo_tree n, lo_node_type t ".
                         "WHERE n.lo_id = '".$a_lo_id."' ".
                         "AND n.node_type_id = t.node_type_id ".
                         "AND t.rgt_delimiter > '' ".
                         "AND n.lft >= 1 ".
                         "AND n.lft < '".$rgt_max."') ".
                         "ORDER BY seq_no_1, seq_no_2, seq_no_3, seq_no_4, seq_no_5";
                
                
                //echo $q;exit;

                $res = $this->db->query($q);
                
                if ($res->numRows() == 0)
                {
                        print("no LearningObject ID given");
                }

                while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
                {
                        $data[] = $row["parsed_text"];
                }
                
                echo TUtil::StopWatch($T1)." get_structure<br/>";
                
                return implode($data);
*/

                $res = $this->db->query($q);
                while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
                {
                        $data[$row["node_id"]] = $row;

                        if ($row["struct"] & 1)
                        {
                                $tmp[$row["node_id"]][] = array ($row["attribute"] => $row["value"]);
                        }
                }

                foreach ($tmp as $node_id => $node)
                {
                        $attr_list = array();
                        
                        foreach ($node as $attr)
                        {
                                //var_dump($attr);
                                $attr_list[key($attr)] = $attr[key($attr)];
                        }

                        $data[$node_id]["attr_list"] = $attr_list;
                }
                return $data;
        }

Here is the caller graph for this function:

SQL2XML::getTree ( $  a_lo_id  ) 

gets specified element and all its subelements, builds with this information a domDocument and return the domxml representation of this xml document

Parameters:
integer object_id where to start fetching the xml data
Returns:
object domDocument public

Definition at line 323 of file class.SQL2XML.php.

References $tree.

        {
                return $tree;
        }

SQL2XML::getXMLDocument (  ) 

Gets a XML document from the database and returns the domxml representation of it.

Returns:
object domDocument private

Definition at line 114 of file class.SQL2XML.php.

References buildXML(), domxml_open_mem(), getStructure(), and retrieveHeader().

Referenced by SQL2XML().

        {
                $this->nestedset = $this->getStructure($this->obj_id);
                // create the xml string (workaround for domxml_new_doc) ***
                $xmlHeader = $this->retrieveHeader() . "<root />";
                // create a domxml document object
                $this->doc = domxml_open_mem($xmlHeader);
                // delete dummy node 
                $root = $this->doc->document_element();
                $root->unlink_node();
                // build XML document
                $this->buildXML();
                return $this->doc->dump_mem(true);
        }

Here is the call graph for this function:

Here is the caller graph for this function:

SQL2XML::retrieveHeader (  ) 

Definition at line 95 of file class.SQL2XML.php.

References $q, $result, and $row.

Referenced by getXMLDocument().

                                  {
                $q = sprintf("SELECT * FROM xml_object WHERE ID=%s",
                        $this->db->quote($this->obj_id)
                );
                $result = $this->db->query($q);
                if ($row = $result->fetchRow(DB_FETCHMODE_OBJECT)) {
                        return "<?xml version=\"$row->version\" encoding=\"$row->encoding\"?>";
                } else {
                        return "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
                }
        }

Here is the caller graph for this function:

SQL2XML::SQL2XML ( $  database_connection,
$  a_obj_id 
)

constructor init db-handler

public

Definition at line 81 of file class.SQL2XML.php.

References getXMLDocument().

        {
                $this->db = $database_connection;
                $this->obj_id = $a_obj_id;
                $this->getXMLDocument();
                register_shutdown_function(array(&$this, '_SQL2XML'));
        }

Here is the call graph for this function:

Field Documentation

SQL2XML::$db

Definition at line 72 of file class.SQL2XML.php.

SQL2XML::$doc

Definition at line 57 of file class.SQL2XML.php.

SQL2XML::$domxml

Definition at line 39 of file class.SQL2XML.php.

SQL2XML::$hash

Definition at line 64 of file class.SQL2XML.php.

SQL2XML::$mapping

Definition at line 55 of file class.SQL2XML.php.

SQL2XML::$nestedset

Definition at line 74 of file class.SQL2XML.php.

SQL2XML::$obj_id

Definition at line 47 of file class.SQL2XML.php.

The documentation for this class was generated from the following file:
Generated on Fri Dec 13 2013 09:07:00 for ILIAS Release_3_4_x_branch .rev 46804 by  doxygen 1.7.1