<?php
//============================================================================
// REST AJAX Web Service
//      Receives user:   The database user id
//               pswd:   The database password  
//               db:     The database name   
//               sql:    The sql code that you want to execute  
//               dbtype: (optional) The DB type MYSQL, ORACLE, SQLITE    if none = MYSQL
//               format: (optional) The output format HTML, XML or JSON, if none = HTML
//               host:   (optional) The host name,                       if none = localhost  
//               port:   (optional) The port number,                     if none = MySQL:3306 Oracle:1521  
//      Returns: data from database in either HTML, XML or JSON format
//               if HTML then HTML web page with HTML table
//               if XML  then XML standard
//               if JSON then array of objects as:
//                       [ 
//                           {"name":"value", "name2":"value2", ...}, 
//                           {"name":"value", "name2":"value2", ...} 
//                       ]  
//============================================================================

    error_reporting(0);
 
    $colNames = array();         #array to hold returned column names
    $data     = array();         #2 dim array to hold returned data

    read_param();                #read data from the query String
    if (! $error)
        execute_sql();           #execute the SQL                        
    send_result();               #send the results using either XML or JSON

//============================================================================
function read_param()
{
//  error_reporting(0);                         #turn off PHP error reporting

    global $userid,$password, $database, $sql, $dbtype, $format, $host, $port, $sqlOrig;

    $userid    = $_GET['user'];                 #get HTML form param fields
    $password  = $_GET['pswd'];
    $database  = $_GET['db'  ];               
    $sql       = $_GET['sql' ];
    $dbtype    = $_GET['dbtype'];
    $format    = $_GET['format'];
    $host      = $_GET['host'];
    $port      = $_GET['port'];
    
    $sqlOrig   = $sql;							#save the original SQL

    if(! $dbtype) $dbtype = 'mysql'; 
    if(! $format) $format = 'html'; 
    if(! $host)   $host   = 'localhost'; 
    
    $sql = str_replace('\\', '', $sql);         #php adds \ before any quotes
                                                #need to preserve those for mysql
    $sql = str_replace('~', '%', $sql);         #I used ~ instead of %
                                                #% is used for url encoding
}

//============================================================================
function execute_sql()
{
    global $userid, $password, $database, $sql, $dbtype, $host, $port, $error;
    global $colNames, $data, $msg;

    if (! $userid or ! $password or ! $database or ! $sql)
    {
       $error='Enter URL?user=...&pswd=...&db=...&sql=...&host=...&port=...&dbtype=mysql|sqlite&format=HTML|XML|JSON';
       include "zCode/include.php";               // hyperlink to see the code 
       return;
    }

    if ( preg_match('/demo/i',$userid) 
    && ! preg_match('/^\s*(select|desc|show|with)/i', $sql) )
    {
        $colNames[0] = 'message';
        $data[0][0]  = 'For demo* database, you can only perform queries';
        return;        
    }

    if (strtolower($dbtype) == 'mysql')  execute_mysql();  
//  if (strtolower($dbtype) == 'oracle') execute_oracle();       #No Oracle database on storm 
    if (strtolower($dbtype) == 'sqlite') execute_sqlite();  
}

//============================================================================
function execute_mysql()
{
    global $userid, $password, $database, $sql, $host, $port;
    global $connect, $colNames, $data, $msg, $error;

    $host2 = 'p:' . $host;                     #make it persistent connection

    if ($port) 
        $connect = mysqli_connect($host2,$userid,$password,$database,$port);  #connect to mysql server
    else
        $connect = mysqli_connect($host2,$userid,$password,$database);        #connect to mysql server

    $connect = mysqli_connect($host2,$userid,$password,$database);
    if (! $connect)
    {
         $error = 'Could not connect: ERROR '. mysqli_connect_errno() .': '. mysqli_connect_error();
         return;
    }

    $result = mysqli_query($connect, $sql);                            #execute mysql query 

    if (! $result)
    {
        $error = 'Could not execute sql: ERROR '. mysqli_errno($connect) .': '. mysqli_error($connect);
        return;
    }

    if(is_object($result))                              #if this is an object (result of a query) 
    {
        $numCols = mysqli_num_fields($result);                  #number of columns returned 
        $numRows = mysqli_num_rows($result);                    #number of rows returned

        $fields = mysqli_fetch_fields($result);                 #get all fields as an array of objects
        for ($i=0; $i < $numCols; $i++)                         #loop thru the array
            $colNames[$i] = $fields[$i]->name;                  #store the field name in col array

        for ($i=0; $i < $numRows; $i++)                         #loop thru rows
            $data[$i] = mysqli_fetch_row($result);              #store row in 2 dim data array

        mysqli_free_result($result);                            #free result buffer 
    }
    else                                                        #if not a cursor (e.g. result of an update) 
    {
        $numRows = mysqli_affected_rows($connect);              #number of rows affected

        $msg = "Update Successful";    
        if ($numRows > 0)
            $msg .= " -- $numRows row(s) affected";
            
        $colNames[0] = 'message';
        $data[0][0]  = $msg;     
    }
}

//============================================================================
function execute_oracle()
{
    global $userid, $password, $database, $sql, $host, $port;
    global $connect, $colNames, $data, $msg, $error;

    $port2        = ($port) ? ':'.$port : "";                  #if port append ':' to it
    $host_port    = $host . $port2;                            #concatenate host & port    
    $host_port_db = $host_port .'/'. $database;                #concatenate host_port/database 

    $connect = oci_connect($userid,$password,$host_port_db);              #connect to oracle server
    if (! $connect) 
    {
        $oracle_error = oci_error();                                #an array or errors
        $error = 'Could not connect: ' . $oracle_error['message'];
        return;
    }

    if ( preg_match('/^\s*desc(ribe)?\s+/i', $sql) )        #sql contains DESC or DESCRIBE
        $sql = oracle_desc($sql);                           #change it to proper SELECT

    $stmt = oci_parse($connect, $sql);                      #parse oracle statement 
    if (! $stmt)
    {
        $oracle_error = oci_error($connect);
        $error = 'Could parse statement: ' . $oracle_error['message'];
        return;
    }

    $result = oci_execute($stmt);                           #execute oracle query                
    if (! $result)
    {
        $oracle_error = oci_error($stmt);
        $error = 'Could not execute sql: ' . $oracle_error['message'];
        return;
    }

    if (oci_num_fields($stmt) > 0)                          #if this is result of a query
    {
        $numCols = oci_num_fields($stmt);                   #number of columns returned

        for ($i=1; $i <= $numCols; $i++)                    #loop thru columns
            $colNames[$i-1] = oci_field_name($stmt, $i);    #get name and store in array

        $i=0;
        while ($row = oci_fetch_array($stmt, OCI_NUM+OCI_RETURN_NULLS+OCI_RETURN_LOBS))  #numeric,with nulls,LOB->strings 
            $data[$i++] = $row;                             #store row in 2 dim data array

        oci_free_statement($stmt);                          #free result buffer

        $msg = "$i rows returned";
    }
    else                                                    #if not a cursor (e.g. result of an update)
    {
        $numRows = oci_num_rows($stmt);                     #number of rows affected

        $msg = "Update Successful";    
        if ($numRows > 0)
            $msg .= " -- $numRows row(s) affected";

        $colNames[0] = 'message';
        $data[0][0]  = $msg;     
    }
}
//============================================================================
function execute_sqlite()
{
    global $userid, $password, $database, $sql, $host, $port;
    global $connect, $colNames, $data, $msg, $error;

    $location = '/home/staff/sultan/sqlite/' .$database. '.db';     #location of SQLite database file       
    $connect  = new SQLite3($location);                             #connect to SQLite database file
 
    if (! $connect) 
    {
        $error = 'Could not connect: ERROR ' . $connect->lastErrorMsg();
        return;
    }

    if ( preg_match('/^\s*desc(ribe)?\s+/i', $sql) )        #sql contains DESC or DESCRIBE
        $sql = sqlite_desc($sql);                           #change it to proper SELECT

    $result = $connect->query($sql);
        
    if (preg_match('/^\s*(select|desc|show|explain|call|set|with)/i', $sql))   #if SQL is a query
    {
        if (!$result)
        {
            $error = "Query Failed - " . $connect->lastErrorMsg();
            return;
        }                
                           
        $firstrow=true;
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) 
        {
            if ($firstrow)
                foreach ($row as $name=>$value)             #loop thru all column names/values
                    $colNames[] = $name;                    #add column name to colNames array
            $firstrow=false;
            $data[] = $row;                                 #add row to 2dim data array
        }

            $numRows = count($data);                        #number of rows returned
            $msg = "$numRows rows returned";
    }
    else                                                    #if SQL is not a query (e.g. it is an update) 
    {
        if ($result==false)                                             #an error
            $msg = "Update Failed - " . $connect->lastErrorMsg();
        else
            $msg = "Update Successful";
        
        $colNames[0] = 'message';
        $data[0][0]  = $msg;                  
    }  
}

//===============================================================================
// For Oracle, change DESC table, to the equivalent SELECT from catalog 
//===============================================================================
function oracle_desc($sql)
{
    $sql  = strtoupper($sql);				            #convert to uppercase
    $sql  = str_replace('DESCRIBE','DESC',$sql);	    #change DESCRIBE to DESC
    $tbl  = substr($sql, strpos($sql,'DESC')+4);        #get the table name 
    $tbl  = trim($tbl); 
    $sql  = "select column_name, data_type, data_length, nullable, data_default 
             from   user_tab_columns
             where  table_name = '$tbl'"; 
    return $sql;
}

//=================================================================================
// For SQLite, change DESC table, to the equivalent SELECT from pragma_table_info 
//=================================================================================
function sqlite_desc($sql)
{
    $sql  = strtoupper($sql);                           #convert to uppercase
    $sql  = str_replace('DESCRIBE','DESC',$sql);        #change DESCRIBE to DESC
    $tbl  = substr($sql, strpos($sql,'DESC')+4);        #get the table name
    $tbl  = trim($tbl); 
    $sql  = "select * from  pragma_table_info('$tbl')";
    return $sql;
}

//============================================================================
function send_result()
{
    global $sqlOrig, $database, $colNames, $data, $msg, $error, $format;

    //------if no output format type requested = HTML ---------------------------------------

    if (strtolower($format) == 'html')                                      
    {
        header("Content-type: text/html");
        
        if ($error)
            print "<h3> $error </h3> \n";
        else
        {
            print "<h3><nobr> $sqlOrig </h3> \n";
            print "<table style='font:10pt arial; padding-left:5px; border:solid 2px black; border-collapse:collapse'> \n";
            print "<tr style='background:tan;'>";
            foreach($colNames as $colName)                       #loop thru the headers
            {
                $colName  = str_replace(' ','_', $colName);      #if column alias has ' ' 
                print "<th style='font:bold; border:solid 1px black;'><nobr> $colName  </th>"; 
            }
            print "</tr> \n";

            foreach($data as $row)                          #loop thru the data rows
            {
                print "<tr style='background:lightyellow;' valign=top>";
                foreach($row as $value)                     #loop thru columns
                {
                    print "<td style='border:solid 1px black; padding-left:5px;'><nobr>$value  </td>";
                }
                print "</tr> \n";
            }
            print "</table> \n";
        }
	
//      include "include.php";
     }

     //------for XML----------------------------------------------------------

    if (strtolower($format) == 'xml')                   #if requesting XML 
    {
        header("Content-type: text/xml");
        
        print "<?xml version='1.0'?> \n";

        print "<sqlData> \n";

        foreach($data as $row)                          #loop thru the rows
        {
            print "    <row> \n";

            for ($i=0; $i<count($row); $i++)            #loop thru column names
            {
                $colName  = $colNames[$i];
                $colValue = $row[$i];
                if (!$colValue) $colValue = $row[$colName];             #for sqlite (can only access by colname) 
                $colName  = str_replace(' ','_', $colName);             #if column alias has ' ' 
                print "        <$colName>$colValue</$colName> \n";
            }
            print "    </row> \n";
        }

        if ($error)
            print "<row><error> $error </error></row> \n";

        print "</sqlData> \n";
     }

    //-----For JSON-----------------------------------------------------------

    if (strtolower($format) == 'json')                  #if requesting JSON 
    {
        header("Content-type: text/plain");
        
        $jsonString =  "[ \n";

        foreach($data as $row)                          #loop thru the rows
        {
            $jsonString .= "    {";

            for ($i=0; $i<count($row); $i++)            #loop thru column names
            {
                $colName  = $colNames[$i];
                $colValue = $row[$i]; 
                if (!$colValue) $colValue = $row[$colName];             #for sqlite (can only access by colname) 
                $colName  = str_replace(' ','_', $colName);             #if column alias has ' ' 
                $jsonString .=  "\"$colName\":\"$colValue\", ";
            }

           $jsonString = preg_replace("/, $/"," ", $jsonString);        #strip off the last comma for the object

            $jsonString .= "}, \n";
        }
        $jsonString = preg_replace("/, \n$/"," \n", $jsonString);       #strip off the last comma for the array 

        $jsonString .=  "] \n";

        if ($error)
            print "[ {\"error\":\"$error\"} ] \n";
        else 
            print $jsonString;
     }
}

//============================================================================
?>