PHP Classes

File: dqml2tree.php

Recommend this page to a friend!
  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: 0.13
php7.2 compliance by replacing the part with function each
Date: 4 years ago
Size: 21,869 bytes
 

Contents

Class file image Download
<?php /** * Author : NEUMANN-RYSTOW François <_@skynext.org> * Version : 0.13 * Date : 27 Mar, 2018 * 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; $lr = 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) { $sub_tree = current($_tree); $branch = key($_tree); while ($branch != null) { if ($branch != '_SQL' && $branch != '_a' && $branch != '_z') { $this->_reduceTree($_tree[$branch]); if ((isset($_tree['_a']) ? $_tree['_a'] : '_') == (isset($sub_tree['_a']) ? $sub_tree['_a'] : '_') && (isset($_tree['_z']) ? $_tree['_z'] : '_') == (isset($sub_tree['_z']) ? $sub_tree['_z'] : '_') && count($_tree) < 5) $_tree = $sub_tree; } $sub_tree = next($_tree); $branch = key($_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', ''); unset($this->_tree['SQL']['_a']); unset($this->_tree['SQL']['_z']); return $this->_tree; } } ?>