Subversion Repositories Applications.papyrus

<?php
        // db settings
        $dbserver = 'localhost';
        $dbuser = 'root';
        $dbpassword = 'root';
        
        error_reporting(E_ALL);
        
        /*
                Simple protocol:
                        - Inputs via POST variables. 
                        - Output is a string that can be evaluated into a JSON
                          First element of the array contains return status.
                                
                This simplified tutorial code should not be deployed without a security review.
        */
        
        @include "json.php";
        
        // set up response encoding 
        header("Content-Type: text/html; charset=utf-8");

        // util
        function getPostString($inName) {
                // make sure input strings are 'clean'
                return mysql_real_escape_string(@$_POST[$inName]);
        }
                
        // used for json encoding
        $json = new Services_JSON();
        
        function echoJson($inData) {
                global $json;
                // delay in ms
                $delay = getPostString('delay');
                if (!empty($delay))
                        usleep($delay * 1000);
                echo '/* ' . $json->encode($inData) . ' */';
        }
        
        function error($inMessage) {
                $inMessage = str_replace('"', '\\"', $inMessage);
                error_log($inMessage);
                //echo '/* ({error: true, message: "' . $inMessage . '"}) */';
                echoJson(array('error' => true, 'message' => $inMessage));
                exit;
        }


        function getArray($inResult, $inArray="true") {
                $o = Array();
                while ($row = ($inArray ? mysql_fetch_row($inResult) : mysql_fetch_object($inResult)))
                        $o[] = $row;
                return $o;      
        }
        
        // connect to DB
        mysql_connect($dbserver, $dbuser, $dbpassword);

        // select DB
        $database = getPostString("database");
        $database = ($database ? $database : $db);
        if (!mysql_select_db($database))
                error('failed to select db: ' . mysql_error());

        // select table
        $table = getPostString("table");
        $table = ($table ? $table : $dbtable);

        // cache
        $colCache = NULL;
        $pkCache = NULL;

        // set UTF8 output (MySql > 4.0)
        mysql_query("SET NAMES UTF8");
        
        // server, database, table meta data
        function getDatabases() {
                $result = mysql_query("SHOW DATABASES");
                $output = Array();
                while ($row = mysql_fetch_row($result)) {
                        $r = strtolower($row[0]);
                        if ($r != 'mysql' && $r != 'information_schema')
                                $output[] = $row[0];
                }       
                return $output; 
        }
        
        function getTables() {
                global $database;
                $result = mysql_query("SHOW TABLES FROM $database");
                $output = Array();
                while ($row = mysql_fetch_row($result))
                        $output[] = $row[0];
                return $output; 
        }
        
        function getColumns() {
                global $table, $colCache;
                if (!$colCache) {
                        $result = mysql_query("SHOW COLUMNS FROM `$table`");
                        return getArray($result, false);
                        $colCache = getArray($result, false);
                }
                return $colCache;       
        }
        
        // returns object: $this->name, $this->index
        function getPk() {
                global $pkCache;
                if (!$pkCache) {
                        $k = '';
                        $columns = getColumns();
                        for ($i=0; $i < count($columns); $i++) {
                                $c = $columns[$i];
                                if ($c->Key == 'PRI') {
                                        $k = $c->Field;
                                        break;
                                }       
                        }
                        $pkCache->index = $i;
                        $pkCache->name = $k;
                }       
                return $pkCache;
        }
        
        function getTableInfo() {
                global $table, $database;
                $c = getColumns();
                $r = rowcount();
                return array("count" => $r, "columns" => $c, "database" => $database, "table" => $table);
        }
        
        function getOldPostPkValue() {
                $pk = getPk();
                return getPostString('_o' . $pk->index);
        }
        
        function getNewPostPkValue() {
                $pk = getPk();
                return getPostString('_' . $pk->index);
        }
        
        function getPostColumns() {
                $columns = getColumns();
                for ($i=0, $a=array(), $p; (($p=getPostString("_".$i)) != ''); $i++) {
                        $r = new stdClass();
                        $r->name = $columns[$i]->Field;
                        $r->value = $p;
                        $a[] = $r;
                }       
                return $a;
        }
        
        function getOrderBy() {
                $ob = getPostString("orderby");
                if (is_numeric($ob)) {
                        $columns = getColumns();
                        $ob = $columns[intval($ob)-1]->Field;
                }
                return $ob;
        }
        
        function getWhere() {
                $w = getPostString("where");
                return ($w ? " WHERE $w" : "");
        }
        
        // basic operations
        function rowcount()     {
                global $table;
                $query = "SELECT COUNT(*) FROM `$table`" . getWhere();
                $result = mysql_query($query);
                if (!$result)
                        error("failed to perform query: $query. " . mysql_error());
                if ($row = mysql_fetch_row($result))
                        return $row[0];
                else
                        return 0;
        }
        
        function select($inQuery = '') {
                global $table;
                // built limit clause
                $lim = (int)getPostString("limit");
                $off = (int)getPostString("offset");
                $limit = ($lim || $off ? " LIMIT $off, $lim" : "");
                // build order by clause
                $desc = (boolean)getPostString("desc");
                $ob = getOrderBy();
                $orderby = ($ob ? " ORDER BY `" . $ob . "`" . ($desc ? " DESC" : "") : "");
                // build query
                $query = ($inQuery ? $inQuery : "SELECT * FROM `$table`" . getWhere() . $orderby . $limit);
                // execute query
                if (!$result = mysql_query($query))
                        error("failed to perform query: $query. " . mysql_error());
                // fetch each result row 
                return getArray($result);
        }

        function reflectRow() {
                global $table;
                $pk = getPk();
                $key = getNewPostPkValue();                     
                $where = "`$pk->name`=\"$key\"";
                return select("SELECT * FROM `$table` WHERE $where LIMIT 1");
        }
        
        function update() {
                // build set clause
                for ($i=0, $set = array(), $cols = getPostColumns(), $v; ($v=$cols[$i]); $i++)
                        $set[] = "`$v->name` = '$v->value'";
                $set = implode(', ', $set);
                // our table
                global $table;
                // build query
                $pk = getPk();
                $pkValue = getOldPostPkValue();
                $query = "UPDATE `$table` SET $set WHERE `$pk->name` = '$pkValue' LIMIT 1";
                // execute query
                if (!mysql_query($query))
                        error("failed to perform query: [$query]. " .
                                        "MySql says: [" . mysql_error() ."]");
                else {
                        return reflectRow();
                }       
        }
        
        function insert() {
                global $table;
                // build values clause
                for ($i=0, $values = array(), $cols = getPostColumns(), $v; ($v=$cols[$i]); $i++)
                        $values[] = $v->value;
                $values = '"' . implode('", "', $values) . '"';                 
                // build query
                $query = "INSERT INTO `$table` VALUES($values)";
                // execute query
                if (!mysql_query($query))
                        error("failed to perform query: [$query]. " .
                                        "MySql says: [" . mysql_error() ."]");
                else {
                        return reflectRow();
                }
        }
        
        function delete() {
                global $table;
                // build query
                $n = getPostString("count");
                $pk = getPk();
                for ($i = 0, $deleted=array(); $i < $n; $i++) {
                        $key = getPostString("_$i");
                        array_push($deleted, $key);
                        $query = "DELETE FROM `$table` WHERE `$pk->name`=\"$key\" LIMIT 1";
                        // execute query
                        if (!mysql_query($query) || mysql_affected_rows() != 1)
                                error("failed to perform query: [$query]. " .
                                        "Affected rows: " . mysql_affected_rows() .". " . 
                                        "MySql says: [" . mysql_error() ."]");
                }       
                return $deleted;                        
        }
        
        // find (full text search)
        function findData($inFindCol, $inFind, $inOrderBy, $inFullText) {
                global $table;
                $where = ($inFullText ? "WHERE MATCH(`$inFindCol`) AGAINST ('$inFind')" : "WHERE $inFindCol LIKE '$inFind'");
                $query = "SELECT * FROM $table $where $inOrderBy";
                $result = mysql_query($query);
                // return rows
                return getArray($result);
        }
        
        // binary search through sorted data, supports start point ($inFindFrom) and direction ($inFindForward)
        function findRow($inData, $inFindFrom=-1, $inFindForward) {
                $b = -1;
                $l = count($inData);
                if (!$inData)
                        return $b;
                if (!$inFindFrom==-1 || $l < 2)
                        $b = 0;
                else {
                        // binary search
                        $t = $l-1;
                        $b = 0;
                        while ($b <= $t) {
                                $p = floor(($b+$t)/2);
                                $d = $inData[$p][0];
                                if ($d < $inFindFrom)
                                        $b = $p + 1;
                                else if ($d > $inFindFrom)
                                        $t = $p - 1;
                                else {
                                        $b = $p;
                                        break;
                                }       
                        }       
                        if ($inFindFrom == $inData[$b][0]) {
                                // add or subtract 1
                                $b = ($inFindForward ? ($b+1 > $l-1 ? 0 : $b+1) : ($b-1 < 0 ? $l-1 : $b-1) );
                        }       
                        else if (!$inFindForward)
                                // subtract 1
                                $b = ($b-1 < 0 ? $l-1 : $b-1);
                }       
                return $inData[$b][0];
        }
        
        function buildFindWhere($inFindData, $inKey, $inCol) {
                $o = Array();
                foreach($inFindData as $row)
                        $o[] = $inCol . "='" . $row[$inKey] . "'";
                return (count($o) ? ' WHERE ' . implode(' OR ', $o) : '');
        }
                
        function find($inFindCol, $inFind='', $inOb='', $inFindFrom=0, $inFindForward=true, $inFullText=true) {
                global $table;
                // build order by clause
                $desc = (boolean)getPostString("desc");
                if (!$inOb)
                        $inOb = getOrderBy();
                if ($inOb)
                        $inOb = "`" . $inOb . "`"       ;
                $orderby = ($inOb ? " ORDER BY $inOb " . ($desc ? " DESC" : "") : "");
                // update inputs from post
                if (!$inFind)
                        $inFind = getPostString('findText');
                if (!$inFindCol)
                        $inFindCol = getPostString('findCol');  
                if (empty($inFindFrom))
                        $inFindFrom = getPostString('findFrom');
                $ff = getPostString('findForward');
                if ($ff)
                        $inFindForward = (strtolower($ff) == 'true' ? true : false);
                $ft = getPostString('findFullText');
                if ($ft)
                        $inFullText = (strtolower($ft) == 'true' ? true : false);       
                
                // get find data
                $f = findData($inFindCol, $inFind, $orderby,  $inFullText);
                $pk = getPk();

                // execute query
                $where = buildFindWhere($f, $pk->index, 'f');
                $query = "SELECT Row, f FROM (SELECT @row := @row + 1 AS Row, $pk->name as f FROM `$table` $orderby) AS tempTable $where";
                mysql_query('SET @row = -1;');
                if (!$result = mysql_query($query))
                        error("failed to perform query: $query. " . mysql_error());
                
                // return row number 
                return findRow(getArray($result), $inFindFrom, $inFindForward);
        }
        
        // our command list
        $cmds = array( 
                "count" => "rowcount", 
                "select" => "select",
                "update" => "update",
                "insert" => "insert",
                "delete" => "delete",
                "find" => "find",
                "databases" => "getDatabases",
                "tables" => "getTables",
                "columns" => "getColumns",
                "info" => "getTableInfo"
        );
                
        // process input params
        $cmd = @$_POST["command"];
        
        //$cmd="select";
        
        // dispatch command
        $func = @$cmds[$cmd];
        if (function_exists($func)) 
                echoJson(call_user_func($func));
        else
                error("bad command");
?>