Login   Register  
PHP Classes
elePHPant
Icontem

File: dqml2tree.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of François NEUMANN-RYSTOW  >  SQL Parse Convert to Tree Array  >  dqml2tree.php  >  Download  
File: dqml2tree.php
Role: Class source
Content type: text/plain
Description: Class File to include in your php script.
Class: SQL Parse Convert to Tree Array
Build a tree to represent an SQL query
Author: By
Last change: debug, recursive parenthesis (something OR (otherthing AND blabla))
Date: 5 years ago
Size: 21,490 bytes
 

Contents

Class file image Download
<?php
/**
* Author  : NEUMANN-RYSTOW François <kalachnkv@free.fr>
* Version : 0.12
* Date    : 7 Jan, 2009
* Purpose : Convert SQL Query (only DQL(SELECT) and DML(INSERT, UPDATE, DELETE)) to TREE
*/

class dqml2tree
{
    var $sql = '';
    var $_sql = '';
    var $_as = Array();
    var $_base_rules = Array();
    var $_inter_rules = Array();
    var $_noval_rules = Array();
    var $_sheet_rules = Array();
    var $_table_rules = Array();
    var $_list_base_rules = Array();
    var $_zonesQ = Array();
    var $_zonesP = Array();
    var $_tree = Array();
    var $_base_tree = Array();
    var $_inter_tree = Array();
    var $_zonesP_tree = Array();
    var $_lexs = Array();

    function dqml2tree($query)
    {
        $this->_base_rules['SQL'] = Array(
        'SELECT',
        'INSERT',
        'UPDATE',
        'DELETE'
        );

        $this->_base_rules['SELECT'] = Array(
        'FROM',
        'WHERE',
        'GROUP',
        'HAVING',
        'UNION',
        'INTERSECT',
        'MINUS',
        'EXCEPT',
        'ORDER',
        'LIMIT',
        'OFFSET'
        );

        $this->_base_rules['UPDATE'] = Array(
        'SET',
        'FROM',
        'WHERE'
        );

        $this->_base_rules['INSERT'] = Array(
        'INTO',
        'VALUES'
        );

        $this->_base_rules['DELETE'] = Array(
        'FROM',
        'WHERE'
        );

        $this->_base_rules['UNION'] = Array('SELECT');
        $this->_base_rules['INTERSECT'] = Array('SELECT');
        $this->_base_rules['MINUS'] = Array('SELECT');
        $this->_base_rules['EXCEPT'] = Array('SELECT');

        foreach ($this->_base_rules as $key => $vals) {
            $this->_list_base_rules[$key] = $key;
            foreach ($vals as $val)  $this->_list_base_rules[$val] = $val;
        }

        $this->_inter_rules['SELECT'] = Array('*SELECT' => ',');
        $this->_inter_rules['UPDATE'] = Array('*UPDATE' => ',');
        $this->_inter_rules['INTO'] = Array('*INSERT' => ' (');
        $this->_inter_rules['*INSERT'] = Array('*INTO' => ',');
        $this->_inter_rules['FROM'] = Array('*FROM' => ',');
        $this->_inter_rules['WHERE'] = Array('*OR' => ' OR ');
        $this->_inter_rules['HAVING'] = Array('*OR' => ' OR ');
        $this->_inter_rules['GROUP'] = Array('*GROUP' => ',');
        $this->_inter_rules['ORDER'] = Array('*ORDER' => ',');
        $this->_inter_rules['LIMIT'] = Array('*LIMIT' => ',');
        $this->_inter_rules['VALUES'] = Array('*VALUES' => ',');
        $this->_inter_rules['SET'] = Array('*SET' => ',');
        $this->_inter_rules['*FROM'] = Array('*JOIN' => ' JOIN ');
        $this->_inter_rules['*JOIN'] = Array('*ON' => ' ON ');
        $this->_inter_rules['*ON'] = Array('*OR' => ' OR ');
        $this->_inter_rules['*OR'] = Array('*AND' => ' AND ');
        $this->_inter_rules['*SET'] = Array('#SET' => '=');
        $this->_inter_rules['*AND'] = Array(
        '!IN' => ' IN ',
        '!IS' => ' IS ',
        '!DIFF' => '<>',
        '!EQLESS' => '<=',
        '!EQPLUS' => '>=',
        '!EQ' => '=',
        '!LESS' => '<',
        '!PLUS' => '>',
        '!LIKE' => ' LIKE ',
        '!ILIKE' => ' ILIKE '
        );

        $this->_as = Array('*AS' => ' AS ', '*AS' => ' ');
        $this->_inter_rules['*SELECT'] = $this->_as;
        $this->_inter_rules['0|*JOIN'] = $this->_as;
        $this->_inter_rules['0|*ON'] = $this->_as;

        $this->_noval_rules = Array('', ',', 'AS', 'BY', 'OR', 'AND', 'ON', 'JOIN');
        $this->_sheet_rules = Array(
        'ORDER' => Array('ASC', 'DESC'),
        '*ORDER' => Array('ASC', 'DESC'),
        '*JOIN' => Array('INNER', 'OUTER', 'LEFT', 'RIGHT'),
        '*ON' => Array('INNER', 'OUTER', 'LEFT', 'RIGHT')
        );
        foreach ($this->_sheet_rules['*ON'] as $j) $this->_noval_rules[] = 'JOIN ' . $j;
        $this->_table_rules = Array('FROM', 'JOIN', 'ON', 'INSERT', 'UPDATE', '*FROM', '*JOIN', '*ON', '*INSERT', '*UPDATE');

        $this->sql = $query;
        $this->_tree['SQL']['_a'] = 0;
        $this->_tree['SQL']['_z'] = strlen($query);
    }

    function _reformatQuery()
    {
        $_sql = $this->sql;
        $_sql = strtoupper($_sql);
        $_sql = preg_replace('/;[\s\n]*$/', '', $_sql);
        $_sql = str_replace("\'", "''", $_sql);
        $_sql = str_replace("\r", ' ', $_sql);
        $_sql = str_replace("\n", ' ', $_sql);
        $_sql = str_replace(' INNER JOIN ', ' JOIN INNER ', $_sql);
        $_sql = str_replace(' OUTER JOIN ', ' JOIN OUTER ', $_sql);
        $_sql = str_replace(' LEFT JOIN ', ' JOIN LEFT ', $_sql);
        $_sql = str_replace(' RIGHT JOIN ', ' JOIN RIGHT ', $_sql);
        $this->_sql = $_sql;
    }

    function _zonesQuote()
    {
        $char = "'";
        $sub_sql = $this->_sql;
        $pos = strpos($sub_sql, $char);
        $index = 0;
        $etat = false;
        while ($pos !== false) {
            $index += $pos;
            $sub_sql = substr($sub_sql, $pos + 1);
            $sub_sql = ' ' . $sub_sql;
            $etat = ($etat == false ? true : false);
            $this->_zonesQ[$index] = $etat;
            $pos = strpos($sub_sql, $char);
        }

        $etat = ($etat == false ? true : false);
        $this->_zonesQ[strlen($this->_sql)] = $etat;
        $last_false = false;
        foreach ($this->_zonesQ as $index => $etat) {
            if ($etat == false) $last_false = $index;
            else if ($last_false != false) {
                if ($last_false == $index - 1) {
                    unset($this->_zonesQ[$index - 1]);
                    unset($this->_zonesQ[$index]);
                }
            }
        }

        $outzones = Array();
        $inzone = Array();
        $last_false = -1;
        $last_true = -1;
        foreach ($this->_zonesQ as $index => $etat) {
            if ($etat == true) {
                $outzones[] = Array('_a' => $last_false + 1, '_z' => $index);
                $last_true = $index;
            }
            if ($etat == false) {
                $inzone[] = Array('_a' => $last_true + 1, '_z' => $index);
                $last_false = $index;
            }
        }

        $this->_zonesQ = Array('out' => $outzones, 'in' => $inzone);
    }

    function _zonesParenthesis()
    {
        $chaine = $this->sql;
        $open_char = '(';
        $close_char = ')';
        foreach (Array($open_char => '_a', $close_char => '_z') as $char => $etat) {
            $sub_sql = $chaine;
            $pos = strpos($sub_sql, $char);
            $index = 0;
            while ($pos !== false) {
                $index += $pos;
                $sub_sql = substr($sub_sql, $pos + 1);
                $sub_sql = ' ' . $sub_sql;
                if (!$this->_inZone($index, $this->_zonesQ['in'])) $this->_zonesP[$index] = $etat;
                $pos = strpos($sub_sql, $char);
            }
        }

        ksort($this->_zonesP);
        $pile = Array();
        foreach ($this->_zonesP as $index => $etat) {
            if ($etat == '_a') {
                array_push($pile, $index);
                $monkey = &$this->_zonesP_tree;
                foreach ($pile as $key => $value) {
                    $monkey = &$monkey[$value];
                }
                $monkey[$etat] = $index;
            }
            if ($etat == '_z') {
                $monkey = &$this->_zonesP_tree;
                foreach ($pile as $key => $value) {
                    $monkey = &$monkey[$value];
                }
                $monkey[$etat] = $index;
                $open = array_pop($pile);
            }
        }

        $this->_zonesP_tree['_a'] = 0;
        $this->_zonesP_tree['_z'] = strlen($this->_sql);
        $this->_outZone($this->_zonesP_tree);
    }

    function _outZone(&$inzones)
    {
        $outzones[] = Array('_a' => $inzones['_a'], '_z' => $inzones['_z']);
        foreach ($inzones as $i => $izone) {
            if ($i != '_a' && $i != '_z') {

                foreach ($outzones as $o => $ozone) {

                    if ($izone['_a'] < $ozone['_z'] && $izone['_z'] > $ozone['_a']) {

                        if ($izone['_a'] > $ozone['_a'] && $izone['_z'] < $ozone['_z']) {
                            $outzones[] = Array('_a' => $ozone['_a'],
                                                '_z' => $izone['_a'] - 1);
                            $outzones[] = Array('_a' => $izone['_z'] + 1,
                                                '_z' => $ozone['_z']);
                            unset($outzones[$o]);
                        }
                        elseif ($izone['_z'] > $ozone['_z']) {
                            $outzones[] = Array('_a' => $ozone['_a'],
                                                '_z' => $izone['_z'] - 1);
                            unset($outzones[$o]);
                        }
                        elseif ($izone['_a'] < $ozone['_a']) {
                            $outzones[] = Array('_a' => $izone['_z'] + 1,
                                                '_z' => $ozone['_z']);
                            unset($outzones[$o]);
                        }

                    }

                }

                $this->_outZone($inzones[$i]);
            }
        }
        $inzones['out'] = $outzones;
    }

    function _inZone($index, $zones)
    {
        $in = false;
        foreach ($zones as $zone) {
            if ($index >= $zone['_a'] && $index <= $zone['_z']) $in = true;
        }
        return $in;
    }

    function _makeBaseTree(&$_tree, $inzones)
    {
        $sqlU = str_replace('_', 'U', substr($this->_sql, $inzones['_a'], $inzones['_z'] - $inzones['_a']));
        $this->_lexs[$inzones['_a']] = str_word_count($sqlU, 2);
        $deep = 0;
        $branch[$deep] = 'SQL';

        foreach ($this->_lexs[$inzones['_a']] as $index => $candidate_rule) {
            $index += $inzones['_a'];

            if (in_array($candidate_rule, $this->_list_base_rules) && $this->_inZone($index, $inzones['out']) && $this->_inZone($index, $this->_zonesQ['out'])) {

                while (!isset($this->_base_rules[$branch[$deep]])) {
                    unset($branch[$deep]);
                    $deep--;
                }

                while (!in_array($candidate_rule, $this->_base_rules[$branch[$deep]])) {
                    unset($branch[$deep]);
                    $deep--;
                    if ($deep < 0) exit;
                }

                if(in_array($candidate_rule, $this->_base_rules[$branch[$deep]])) {
                    $deep++;
                    $branch[$deep] = $candidate_rule;
                    $monkey = &$_tree;
                    foreach ($branch as $key => $value) {
                        $monkey = &$monkey[$value];
                    }
                    $monkey['_a'] = $index + strlen($candidate_rule);
                    if (isset($oldmonkey)) {
                        $oldmonkey['_z'] = $index - 1;
                        $oldmonkey['_SQL'] = substr($this->sql, $oldmonkey['_a'], $oldmonkey['_z'] - $oldmonkey['_a']);
                    }
                    $oldmonkey = &$monkey;
                }

            }

        }

        $oldmonkey['_z'] = $inzones['_z'];
        $oldmonkey['_SQL'] = substr($this->sql, $oldmonkey['_a'], $inzones['_z'] - $oldmonkey['_a']);
    }

    function _makeInterBranches($inter_rule_key, $inter_rule_val, $begin, $end, $outzones)
    {
        $poses = Array();
        $length_rule = strlen($inter_rule_val);
        $min_lr = 0;
        if (substr($this->_sql, $begin, 1) == '(') $min_lr = 1;

        if ($inter_rule_val != '') {
            $pos = strpos($this->_sql, $inter_rule_val, $begin);
            while ($pos !== false) {
                if ($this->_inZone($pos, $outzones) && $this->_inZone($pos, $this->_zonesQ['out']) && $pos < $end) {
                    $poses[] = $pos;
                }
                $sbegin = $pos + 1;
                $pos = strpos($this->_sql, $inter_rule_val, $sbegin);
            }
        }

        $i = 0;
        $last_pos = $begin;
        foreach ($poses as $pos) {
            if (!in_array(trim(substr($this->_sql, $last_pos + $lr, $pos - $last_pos - $lr)), $this->_noval_rules)) {
                $lr = ($i > 0 ? $length_rule : $min_lr);
                $inter_branches[$i . '|' . $inter_rule_key] = Array(
                    '_a' => $last_pos,
                    '_z' => $pos,
                    '_SQL' => str_repeat(' ', $lr) . substr($this->sql, $last_pos + $lr, $pos - $last_pos - $lr)
                );
                $last_pos = $pos;
                $i++;
            }
        }

        if (!in_array(trim(substr($this->_sql, $last_pos + $lr, $end - $last_pos - $lr)), $this->_noval_rules)) {
            $lr = ($i > 0 ? $length_rule : $min_lr);
            $inter_branches[$i . '|' . $inter_rule_key] = Array(
                '_a' => $last_pos,
                '_z' => $end,
                '_SQL' => str_repeat(' ', $lr) . substr($this->sql, $last_pos + $lr, $end - $last_pos - $lr)
            );
        }

        return $inter_branches;
    }

    function _makeInterTree(&$_tree, $outzones)
    {
        foreach ($_tree as $branch_rule => $sub_tree) {
            if ($branch_rule != '_a' && $branch_rule != '_z' && $branch_rule != '_SQL') {

                $found_rule = false;
                $pur_rule = $branch_rule;
                if (array_key_exists($pur_rule, $this->_inter_rules)) $found_rule = true;
                else {
                    $rules = explode('|', $branch_rule, 2);
                    if (is_numeric(substr($rules[0], 0, 1))) $pur_rule = $rules[1];
                    if (array_key_exists($pur_rule, $this->_inter_rules)) $found_rule = true;
                }

                if ($found_rule) {
                    foreach ($this->_inter_rules[$pur_rule] as $inter_rule_key => $inter_rule_val) {

                        if (is_numeric($sub_tree['_a']) && is_numeric($sub_tree['_z'])) {

                            $new_branches = $this->_makeInterBranches($inter_rule_key, $inter_rule_val, $sub_tree['_a'], $sub_tree['_z'], $outzones);

                            if (count($new_branches) > 1 || substr($inter_rule_key, 0, 1) != '!') {
                                if (is_array($new_branches))
                                $_tree[$branch_rule] += $new_branches;
                                if (substr($inter_rule_key, 0, 1) == '!') break;
                            }
                        }

                    }
                }

                $this->_makeInterTree($_tree[$branch_rule], $outzones);
            }
        }
    }

    function _makeChildsTree(&$_tree, $parents, &$inzones)
    {
        foreach ($_tree as $branch => $sub_tree) {
            if ($branch == '_SQL' && count($_tree) < 4) {

                $child = substr($this->_sql, $_tree['_a'], $_tree['_z'] - $_tree['_a']);
                if (strlen($child) > 0) {
                    $pos = strpos($child, '(', 1);
                    if ($pos !== false) {

                        $pos += $_tree['_a'];
                        if (isset($inzones[$pos])) {
                            $new_tree = Array();
                            $this->_makeBaseTree($new_tree, $inzones[$pos]);

                            if (empty($new_tree)) {
                                unset($new_tree);
                                // remontée récurcive jusqu'à la dernière base rule
                                preg_match('/([A-Z]+)\(/', substr($this->_sql, $_tree['_a'], $pos - $_tree['_a'] + 1), $agr);
                                if (isset($agr[1])) {
                                    $p = $agr[1];
                                    $this->_inter_rules[$p] = Array('*' . $p => ',');
                                    $this->_inter_rules['*' . $p] = $this->_as;
                                }
                                else {
                                    $n = count($parents);
                                    while ($n > 0) {
                                        if (in_array($parents[$n], $this->_list_base_rules)) {
                                            $p = $parents[$n];
                                            $n = 0;
                                        }
                                        $n--;
                                    }
                                }
                                $new_tree[$p]['_a'] = $inzones[$pos]['_a'];
                                $new_tree[$p]['_z'] = $inzones[$pos]['_z'];
                                $new_tree[$p]['_SQL'] = ' ' . substr($this->sql, $inzones[$pos]['_a'] + 1, $inzones[$pos]['_z'] - ($inzones[$pos]['_a'] + 1 ));
                                $parents = Array('SQL');
                            }
                            else $parents[] = $branch;

                            $this->_makeInterTree($new_tree, $inzones[$pos]['out']);
                            $this->_makeChildsTree($new_tree, $parents, $inzones[$pos]);
                            $_tree += $new_tree;
                        }

                    }
                }

            }
            elseif ($branch != '_SQL' && $branch != '_a' && $branch != '_z') {
                $parents[] = $branch;
                $this->_makeChildsTree($_tree[$branch], $parents, $inzones);
            }
        }
    }

    function _reduceTree(&$_tree)
    {
        while (list($branch, $sub_tree) = each($_tree)) {
            if ($branch != '_SQL' && $branch != '_a' && $branch != '_z') {
                $this->_reduceTree($_tree[$branch]);
                if ($_tree['_a'] == $sub_tree['_a'] && $_tree['_z'] == $sub_tree['_z'] && count($_tree) < 5) $_tree = $sub_tree;
            }
        }
    }

    function _makeAdvanceTree(&$_tree, $parent, $grand_parent)
    {
        foreach ((Array)$_tree as $branch => $sub_tree) {
            if ($branch == '_SQL') {

                if (count($_tree) < 4) {

                    $sub_tree = str_replace("\r", ' ', $sub_tree);
                    $sub_tree = str_replace("\n", ' ', $sub_tree);

                    if (substr($parent, -2) == 'AS') $_parent = $grand_parent;
                    else $_parent = $parent;

                    $rules = explode('|', $_parent, 2);
                    if (is_numeric(substr($rules[0], 0, 1))) {
                        $num_rule = $rules[0];
                        $pur_rule = $rules[1];
                    }
                    else {
                        $num_rule = 0;
                        $pur_rule = $rules[0];
                    }
                    
                    if ($num_rule == 0 || substr($pur_rule, 0, 1) != '!') {
                        $subs = explode(' ', $sub_tree);
                        $sub_tree = '';
                        if (!isset($this->_sheet_rules[$pur_rule])) $this->_sheet_rules[$pur_rule] = Array();
                        foreach ($subs as $sub) {
                            $subU = strtoupper($sub);
                            if (!in_array($subU, $this->_noval_rules)) {
                                if (in_array($subU, $this->_sheet_rules[$pur_rule])) $_tree[$sub] = $sub;
                                else $sub_tree .= $sub . ' ';
                            }
                        }
                    }
                    $lsub_tree = trim($sub_tree);

                    if(in_array($pur_rule, $this->_table_rules)) {
                        $_tree['TABLE'] = $lsub_tree;
                    }
                    elseif (is_numeric($lsub_tree) || substr($lsub_tree, 0, 1) == "'") {
                        $_tree['VAL'] = $lsub_tree;
                    }
                    else {
                        $lsubs = explode('.', $lsub_tree);
                        if (count($lsubs) > 1) {
                            $_tree['TABLE'] = $lsubs[0];
                            $_tree['FIELD'] = $lsubs[1];
                        }
                        else {
                            $_tree['FIELD'] = str_replace(',', '', $lsub_tree);
                        }
                    }

                }

                unset($_tree['_SQL']);
                unset($_tree['_a']);
                unset($_tree['_z']);
            }
            elseif ($branch != '_SQL' && $branch != '_a' && $branch != '_z') {
                $this->_makeAdvanceTree($_tree[$branch], $branch, $parent);
            }
        }
    }

    function make()
    {
        $this->_reformatQuery();
        $this->_zonesQuote();
        $this->_zonesParenthesis();
        $this->_makeBaseTree($this->_tree, $this->_zonesP_tree);
        $this->_makeInterTree($this->_tree['SQL'], $this->_zonesP_tree['out']);
        $this->_makeChildsTree($this->_tree['SQL'], Array('SQL'), $this->_zonesP_tree);
        $this->_reduceTree($this->_tree);
        $this->_makeAdvanceTree($this->_tree, 'SQL', '');
        return $this->_tree;
    }
}
?>