<?php
/*
ADOXML - Version 0.4
SQL2XML - XML2SQL
Copyright (C) 2004 F.J. De Klerk
This file is part of ADOXML.
ADOXML is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
ADOXML by Johan De Klerk
hide@address.com
Many thanks to David Williams for this great idea!
ADOXML has been tested on:
MySQL 4.0.*
SQLServer 2000
Let me know if you get it working on other DBMS
*/
require_once('adodb.inc.php');
require_once('ezxml.php');
class ADOXML {
var $include_pk = false;
/* Builds xml string
* $rs -> ADORecordset Object
* $tablename -> the table of the select query
*/
function & BuildXML(&$rs,$tablename) {
@$rs->Move(0);
$xml = '<?xml version="1.0"?>'."\n";
$xml .= '<table name="'.$tablename.'">'."\n";
$j = 0;
while (!$rs->EOF) {
$row = $rs->GetRowAssoc();
$xml .= "\t".'<row num="'.$j.'">'."\n";
$keys = array_keys($row);
if ($this->include_pk) {
$begin = 0;
}
else {
$begin = 1;
}
for ($i = $begin; $i < count($row); $i++) {
$xml .= "\t\t".'<'.strtolower($keys[$i]).'>';
$content = $row[$keys[$i]];
$xml .= str_replace('&','',$content);
$xml .= '</'.strtolower($keys[$i]).'>'."\n";
}
$xml .= "\t".'</row>'."\n";
$rs->moveNext();
$j++;
}
$xml .= '</table>';
@$rs->Move(0);
return $xml;
}
/* Builds XML String from select query
* $query -> the select query to transform
*/
function & BuildXMLWithQuery($query,&$conn) {
if (!is_object($conn)) {
trigger_error('ADOXML: Connection object not valid, for function BuildXMLWithQuery',E_USER_ERROR);
}
$rs = $conn->Execute($query);
if (!$rs) {
trigger_error('ADOXML: Error with select: '.$conn->ErrorMsg(),E_USER_ERROR);
}
return $this->BuildXML($rs,$this->_getTableName($query));
}
/* Read XML from file into string
* $xmlfile -> path to the file
*/
function & ReadXMLFile($xmlfile) {
if (file_exists($xmlfile)) {
$f = fopen($xmlfile,'r');
$xml = fread($f,filesize($xmlfile));
fclose($f);
return $xml;
}
else {
trigger_error('ADOXML: File: '.$xmlfile.' does not exist.',E_USER_ERROR);
}
}
/* Write XML string to file
* $xmlfile -> path to the file to be overwritten or created
*/
function WriteXMLFile(&$xml,$xmlfile) {
//@chmod('adoxml.php',777);
$f = @fopen($xmlfile,'w');
@fwrite($f,$xml);
@fclose($f);
if (file_exists($xmlfile)) {
return true;
}
else {
trigger_error('ADOXML: Error Writing File: '.$xmlfile,E_USER_ERROR);
}
}
/* Insert XML into table
* $xml -> the xml string (must adhere to adoxml xml format)
* $connto -> the ADO Connection object to use to execute the insert query
* $table -> the table you wish to insert to
*/
function InsertXML($xml,&$connto,$tablename) {
$this->_StripXML($xml);
if (!is_object($connto)) {
trigger_error('ADOXML: Connection object not valid, for function Insert',E_USER_ERROR);
}
$query = 'select * from '.$tablename;
@$connto->SelectLimit($query,0,0);
$rs =& $connto->Execute($query);
if (!$rs) {
trigger_error('ADOXML: Error with select: '.$connto->ErrorMsg(),E_USER_ERROR);
}
$root = eZXML::domTree($xml,array('TrimWhiteSpace'=>true));
$query = '';
foreach($root->children as $table) {
foreach($table->children as $row) {
if ($row->name == 'row') {
$query1 = 'insert into '.$tablename.'(';
$query2 = 'values(';
foreach($row->children as $field) {
if ($this->_fieldInRs($field->name,$rs)) {
$query1 .= $field->name.',';
$query2 .= "'".get_contents($field)."',";
}
}
$query1 = substr($query1,0,strlen($query1)-1).') ';
$query2 = substr($query2,0,strlen($query2)-1).') ';
$query = $query1.$query2;
if ($connto->Execute($query) == false) {
trigger_error('ADOXML: Error inserting: '.$connto->ErrorMsg(),E_USER_ERROR);
}
}
}
}
}
/* Update a table with XML as source of changes
* $xml -> the xml string (must adhere to adoxml xml format)
* $connto -> the ADO Connection object you want to use to perform the update query
* $tablename -> The table to update
*
* Note: This function assumes the first field in the XML source is the primary key field or the field you want to use to identify the row.
* You can generate XML String with primary key by setting class var include_pk to true
*/
function UpdateXML($xml,&$connto,$tablename) {
$this->_StripXML($xml);
if (!is_object($connto)) {
trigger_error('ADOXML: Connection object not valid, for function Update',E_USER_ERROR);
}
$query = 'select * from '.$tablename;
@$connto->SelectLimit($query,0,0);
$rs =& $connto->Execute($query);
if (!$rs) {
trigger_error('ADOXML: Error with select: '.$connto->ErrorMsg(),E_USER_ERROR);
}
$root = eZXML::domTree($xml,array('TrimWhiteSpace'=>true));
$query = '';
foreach($root->children as $table) {
foreach($table->children as $row) {
if ($row->name == 'row') {
$first = true;
$query = 'update '.$tablename.' set ';
foreach($row->children as $field) {
if ($first) {
$query1 = ' where '.$field->name."='".get_contents($field)."'";
$first = false;
}
if ($this->_fieldInRs($field->name,$rs)) {
$query .= $field->name."='".get_contents($field)."',";
}
}
$query = substr($query,0,strlen($query)-1);
$query .= $query1;
if ($connto->Execute($query) == false) {
trigger_error('ADOXML: Error updating: '.$connto->ErrorMsg(),E_USER_ERROR);
}
}
}
}
}
//PRIVATE FUNCTIONS
function _StripXML(&$xml) {
$chars = preg_split('//', $xml, -1, PREG_SPLIT_NO_EMPTY);
$intable = false;
$xml = '<?xml version="1.0"?>'."\n";
for ($i = 0; $i < count($chars); $i++) {
if (!$intable) {
if ($chars[$i] == '<') {
$tablename = $chars[$i+1].$chars[$i+2].$chars[$i+3].$chars[$i+4].$chars[$i+5];
if ($tablename == 'table') {
$intable = true;
$i--;
continue;
}
}
}
else {
$xml .= $chars[$i];
if ($chars[$i] == '>') {
$tablename = $chars[$i-6].$chars[$i-5].$chars[$i-4].$chars[$i-3].$chars[$i-2].$chars[$i-1];
if ($tablename == '/table') {
$intable = false;
break;
}
}
}
}
return $xml;
}
function & _GetTableName($query) {
$query = strtolower(trim($query));
$pos = strpos($query,'from');
$tablename = substr($query,$pos+5);
trim($tablename);
if (strstr($tablename,' ')) {
$pos = strpos($tablename,' ');
$tablename = substr($tablename,0,$pos);
}
return $tablename;
}
function _fieldInRs($field,$rs) {
for ($i = 0; $i < $rs->FieldCount(); $i++) {
$fl = $rs->FetchField($i);
if ($fl->name == $field) {
return true;
}
}
return false;
}
}
?>