include_once("group.php5");
include_once("schema_loader.php5");
include_once("db_connection.php5");
include_once("query_object.php5");
class mysql_to_xml {
/**********************************************************************************************************/
function get_table_names($project,$group) {
$group_names = $group->get_group_names();
$tables = array();
foreach($group_names as $name) {
$tables[] = $project."_".$name;
}
return $tables;
}
/**********************************************************************************************************/
function unhtmlentities ($string) {
$trans_tbl = get_html_translation_table (HTML_ENTITIES);
$trans_tbl = array_flip ($trans_tbl);
$length = strlen($string);
for($i=0;$i<$length;$i++) {
$char = $string[$i];
}
$ret = strtr ($string, $trans_tbl);
return preg_replace('/(\d+);/me',
"chr('\\1')",$ret);
}
/**********************************************************************************************************/
function query_database($query_object) {
$main_ids = array();
$group = $query_object->get_group();
$head_tag = $group->name;
$project = $query_object->get_project();
$query_type = $query_object->get_query_type();
$search_stripped_fields = $query_object->get_search_stripped_fields();
$char_classes = $query_object->get_char_classes();
$sort_order = $query_object->get_sort_order();
$num_conditions = $query_object->get_num_conditions();
$subqueries = "";
$tables_used = array();
for($i=0;$i<$num_conditions;$i++) {
$subqueries .= "(";
$db_cols = $query_object->get_db_cols($i);
$reg_exp = $query_object->get_reg_exp($i);
$string = $query_object->get_string($i);
$special_chars = array("(",")","'","\"");
$escaped = array("[(]","[)]","[']","[\"]");
$string = str_replace($special_chars,$escaped,$string);
$connective = $query_object->get_connective($i);
if($query_type == "advanced_query") {
$subqueries .= $connective;
}
if(count($char_classes) != 0) {
foreach($char_classes as $key=>$value) {
if(($key != "") && ($value != "")) {
$string = ereg_replace($key,$value,$string);
}
}
}
if(eregi("not",$reg_exp)) {
$inner_connective = "AND";
}
else {
$inner_connective = "OR";
}
$regexp_phrase = ereg_replace("~", $string, $reg_exp);
$db_cols = explode(" ", $db_cols);
if($search_stripped_fields == "true") {
foreach($db_cols as $col) {
if($group->contains_field($col."_s")) {
$db_cols[] = $col."_s";
}
}
}
foreach($db_cols as $col) {
$group_names = $group->find_group($col);
foreach($group_names as $group_name=>$count) {
$table = $project."_".$group_name;
if(!in_array($table,$tables_used)) {
$tables_used[] = $table;
}
$subquery = "(";
for($j=0;$j<$count;$j++) {
$phrase = ereg_replace("field",$table.".".$col."_$j",$regexp_phrase);
$subquery .= "$phrase $inner_connective ";
}
$subquery = substr($subquery,0,-(strlen($inner_connective)+2)).")";
$subqueries .= " $subquery $inner_connective ";
}
}
$length = strlen($inner_connective)+1;
$subqueries = substr($subqueries,0,(-1*$length));
$subqueries .= ")";
if($query_type != "advanced_query") {
$subqueries .= $connective;
}
}
$last_connective = $query_object->get_last_connective();
if($query_type == "advanced_query") {
$subqueries .= $last_connective;
}
else {
if($last_connective != "") {
$less = strlen($last_connective) * -1;
$subqueries = substr($subqueries,0,$less);
}
}
if($query_object->get_num_conditions() != 0) {
$tables = $this->get_table_names($project, $group);
$t = array_shift($tables);
foreach($tables as $table) {
if(in_array($table,$tables_used)) {
$t .= " LEFT JOIN $table USING (".$group->name."_id)";
}
}
//$id = $project."_".$group->name.".".$head_tag."_id";
$id = $tables_used[0].".".$head_tag."_id";
$query = "SELECT DISTINCT $id FROM $t WHERE $subqueries";
if($sort_order != ""){
$query .= "ORDER BY $sort_order";
}
$query_result = mysql_query($query);
if(mysql_error()) {
echo "mysql_to_xml.query_database()".mysql_error()."
";
echo "query: $query
";
}
while($row = mysql_fetch_assoc($query_result)) {
$main_ids[] = $row[$head_tag."_id"];
}
$main_ids = array_unique($main_ids);
$query_object->set_page("1");
}
else {
$query_object->set_page("0");
}
$query_object->set_main_ids($main_ids);
$subqueries = ereg_replace("<","<",$subqueries);
$subqueries = ereg_replace(">",">",$subqueries);
$query_object->set_query($query);
return $query_object;
}
/**********************************************************************************************************/
/*
The linguist indicates the corresponding XSLT file using the variable, $stylesheet.
If no $stylesheet is provided, the data is returned in xml form.
*/
function convert_resultset_to_xml($query_object) {
$group = $query_object->get_group();
$head_tag = $group->name;
$main_ids = $query_object->get_main_ids();
$page = $query_object->get_page();
$stylesheet = $query_object->get_stylesheet();
$project = $query_object->get_project();
$max_results = $query_object->get_max_results();
$xml = "\n";
/*
if($stylesheet != "") {
$xml .= "\n";
}
*/
$strings = "";
$num_conditions = $query_object->get_num_conditions();
for($i=0;$i<$num_conditions;$i++) {
$string = $query_object->get_string($i);
$string = ereg_replace("<","<",$string);
$string = ereg_replace(">",">",$string);
$strings .= "$string\n";
}
$xml .= "\n";
$xml .= $strings;
$max_results = $query_object->get_max_results();
if($max_results == "") {
$max_results = 50;
}
if(count($main_ids) > 0) {
$reg_exp = "";
$last = $page*$max_results;
if($last > count($main_ids)) {
$last = count($main_ids);
}
for($j=($page*$max_results-$max_results);$j<$last;$j++) {
$ids[] = $main_ids[$j];
}
foreach($ids as $main_id) {
$main_id = trim($main_id);
if($main_id != "") {
$reg_exp .= $head_tag."_id = $main_id OR ";
}
}
$reg_exp = substr($reg_exp,0,-4);
$tables = $this->get_table_names($project,$group);
foreach($tables as $table) {
$group_name = substr($table,strpos($table,"_")+1);
$query = "SELECT * FROM $table WHERE $reg_exp";
$query_result = mysql_query($query);
if($group_name == $head_tag) {
$id_field = $head_tag."_id";
}
else {
$id_field = "parent_id";
}
while($row = mysql_fetch_assoc($query_result)) {
$data[$row[$id_field]."_$group_name"][] = $row;
}
}
foreach($ids as $index=>$main_id) {
if($main_id != "") {
$group->destroy_subgroups();
$group->main_id = $main_id;
$group->id = $main_id;
$group->parent = false;
$row = $data[$main_id."_".$group->name][0];
$group->load_query_result($row,$data);
$template = explode(" ",trim($group->template));
$cur_xml = $group->to_xml_by_template($template,false);
$xml .= $cur_xml;
}
}
}
$xml .= "";
$query_object->set_xml($xml);
return $query_object;
}
/**********************************************************************************************************/
function load_resultset_into_group($query_object,$main_id) {
$group = $query_object->get_group();
$head_tag = $group->name;
$reg_exp = "";
$main_id = trim($main_id);
if($main_id != "") {
$reg_exp = $head_tag."_id = $main_id";
}
$project = $query_object->get_project();
$tables = $this->get_table_names($project,$group);
foreach($tables as $table) {
$query = "SELECT * FROM $table WHERE $reg_exp";
$query_result = mysql_query($query);
$group_name = substr($table,strpos($table,"_")+1);
if($group_name == $head_tag) {
$id_field = $head_tag."_id";
}
else {
$id_field = "parent_id";
}
while($row = mysql_fetch_assoc($query_result)) {
$data[$row[$id_field]."_".$group_name][] = $row;
}
}
$group->destroy_subgroups();
$group->main_id = $main_id;
$group->id = $main_id;
$group->parent = false;
$row = $data[$main_id."_".$group->name][0];
$group->load_query_result($row,$data);
//$template = explode(" ",trim($group->template));
return $group;
}
/**
* Write an entire project to xml.
*
* The schema file for the given project must be located at ../$project/schema.xml.
*/
function db_to_xml($project,$output_file) {
$max_entries_per_write = 100;
$out = fopen("../$project/$output_file","w");
$sl = new schema_loader();
$group = $sl->create_group_structure("../$project/schema.xml","");
$main_table = $project."_".$group->name;
$main_id_col = $group->name."_id";
$head_tag = $group->name;
$query = "SELECT $main_id_col FROM $main_table";
$query_result_main_ids = mysql_query($query);
$num_rows = mysql_num_rows($query_result_main_ids);
echo "num_rows: $num_rows
";
$xml = "\n";
$xml .= "\n";
$tables = $this->get_table_names($project,$group);
$reg_exp = "";
$ids = array();
$data = array();
for($i=1; $i<=$num_rows;$i++) {
$row = mysql_fetch_assoc($query_result_main_ids);
$main_id = $row[$main_id_col];
$reg_exp .= " OR $main_id_col=".$main_id;
$ids[] = $main_id;
if((($i % $max_entries_per_write) == 0) || ($i == $num_rows)) {
echo "writing to file: ".($i - $max_entries_per_write)." to $i
";
$reg_exp = substr($reg_exp,4);
foreach($tables as $table) {
$query = "SELECT * FROM $table WHERE $reg_exp";
$query_result = mysql_query($query);
$group_name = substr($table,strpos($table,"_")+1);
if($group_name == $head_tag) {
$id_field = $head_tag."_id";
}
else {
$id_field = "parent_id";
}
while($row = mysql_fetch_assoc($query_result)) {
$data[$row[$id_field]."_".$group_name][] = $row;
}
}
foreach($ids as $index=>$main_id) {
if($main_id != "") {
$group->destroy_subgroups();
$group->main_id = $main_id;
$group->id = $main_id;
$group->parent = false;
$row = $data[$main_id."_".$group->name][0];
$group->load_query_result($row,$data);
$template = explode(" ",trim($group->template));
$cur_xml = $group->to_xml_by_template($template,false);
$xml .= $cur_xml;
}
}
fwrite($out,$xml);
$xml = "";
$reg_exp = "";
$ids = array();
$data = array();
}
}
fwrite($out,"");
fclose($out);
}
}
?>