Eboubaker/icon.png

## gistfile1.txt
One of them is the last revision i dont have time to check now...
icon.png in images folder

## icon.png

      
    Raw
  

              icon.png
            
          
## index.php
<?php
# The include and require statements are identical, except upon failure:
//include_once 'variables.php';
//include_once 'parser.php';
//"select * from users where id < 10 or id > 40 and id < 100 and id < 120 or id > 150"


//// i didn't use POST because the browser will keeps showing me confirmation pop-up when i refresh the page
$_GET['query'] = "select name,project from employee where employeeId > 20 and employeeId < 180";
$_GET['tablesCount'] = "4";
$_GET['tablesLength'] = "50";
$result = null;
$debug = null;
$idname = "employeeId";

function match_one($pattern, $string)
{
    preg_match("/$pattern/i", $string, $matches);
    $matches = $matches[0] ?? '';
    return trim($matches);
}
function expression_split($expression): array
{
    $chunks = preg_split('/\s*[<>!=](?=[<>!=]*\s*\d)\s*/', $expression);
    if(!is_array($chunks))
        return [];
    return array_values(array_filter($chunks, function($subexp){
        return !empty($subexp) && !match_one('[!=<>]', $subexp);
    }));
}
function extract_range($expression)
{
    global $result, $tablesLength, $tablesCount, $idname;
    $parts = expression_split($expression);

    if(count($parts) === 2 && !match_one('\d', $parts[1]))
    {
        $result = "parse Error: Cannot compare non integer values in where clause, expression ('$expression')";
        return null;
    }
    if((int)$parts[1] < 0)
    {
        $result = "parse Error: comparing with negative values, expression ('$expression')";
        return null;
    }
    // $tableNumber = (int)(((int)$parts[1]+1)/$tablesLength+1);
    preg_match_all('/[<>!=](?=[<>!=]*\s*\d)/', $expression, $matches);
    $matches = is_array($matches[0]) ? $matches[0] : $matches;
    $operator = implode('', $matches);
    if(!$idname)
        $idname = $parts[0];
    else if(strtolower($parts[0]) !== strtolower($idname))
    {
        $result = "parse Error: expected '$idname $operator $parts[1]' got ('$expression')";
        return null;
    }
    switch ($operator)
    {
        case '>':
            return [(int)$parts[1], $tablesLength * $tablesCount+1];
        case '<':
            return [0, (int)$parts[1]];
        case '=' :
        case '>=':
        case '<=':
        case '!=':
            $result = "Parse error: operator '$operator' not supported in expression ('$parts[0] $operator $parts[1]')";
            return null;
        default:
            $result = "Syntax error: no operator found in expression ('$parts[0] $operator $parts[1]')";
            return null;
    }
}

if(isset($_GET['query']) && !empty($query = trim(preg_replace('/\n/', '',$_GET['query']))))
{
    (function ()
    {
        global $result, $query, $tablesCount, $tablesLength, $debug, $idname;
        $tablesCount = (int)trim($_GET['tablesCount']);
        $tablesLength = (int)trim($_GET['tablesLength']);
        if($tablesCount < 1)
        {
            $result = "Input Error: Tables count must be greater than 1";
            return;
        }
        if($tablesLength < 1)
        {
            $result = "Input Error: tables count must greater than 1";
            return;
        }
        $selecting = match_one('(?<=select).*(?=from)', $query);
        $tableName = match_one('(?<=from).*(?=where)', $query);
        $conditionClause = match_one('(?<=where).*', $query);

        if(strcmp($tableName, "employee"))
        {
            $result = "SQL Error: Unknown table $tableName";
            return;
        }
        foreach (preg_split('/\s*,\s*/', $selecting) as $column)
        {
            if(!in_array(strtolower($column), ["*", "employeeid", "birthday", "project", "name"]))
            {
                $result = "SQL Error: Unknown column $column";
                return;
            }
        }
        if(empty($tableName))
        {
            $result = "Syntax Error: no table selected";
            return;
        }
        if(empty($selecting))
        {
            $result = "Syntax Error: no columns selected";
            return;
        }

        if(empty($conditionClause) && !empty(match_one('where', $query)))
        {
            $result = "Syntax Error: empty condition clause";
            return;
        }
        if(empty($conditionClause) || ($v=filter_var(trim($conditionClause), FILTER_VALIDATE_INT)) > 0)
        {
            if($v === 0)
            {
                // empty set ∅
                $result = "SELECT $selecting FROM $tableName"."1"." WHERE 0";
                return;
            }
            // just a trick
            $conditionClause = "id > 0";
        }
        $debug=[];
        $maxRange = $tablesCount * $tablesLength;
        $ranges = [];
        $expressions = preg_split("/\s+or\s+/i", $conditionClause);
        foreach ($expressions as $expression)
        {
            if(count($ands = preg_split('/\s+and\s+/i', $expression)) > 1)
            {
                $and_ranges = [];
                // extract ranges of each AND
                foreach ($ands as $and)
                {
                    $r = extract_range($and);
                    if(!$r)
                        return;
                    $and_ranges[] = $r;
                }
                // combine ANDs into a single range
                $result = [0, $maxRange+1];
                foreach ($and_ranges as $range)
                {
                    if($range[0] > $result[0])
                        $result[0] = $range[0];
                    if($range[1] < $result[1])
                        $result[1] = $range[1];
                }
                $ranges[] = $result;
            }else
            {
                $r = extract_range($expression);
                if(!$r)
                    return;
                $ranges[] = $r;
            }
        }
        // sort ranges
        uasort($ranges, function ($r1, $r2) {
            return $r1[0] - $r2[0];
        });
        // remove collided ranges
        repeat:
        for ($i = 0, $iMax = count($ranges)-1; $i < $iMax; $i++)
        {
            if($ranges[$i][1] >= $ranges[$i+1][1])
            {
                unset($ranges[$i+1]);
                $ranges = array_values($ranges);
                goto repeat;
            }
        }
        // we have everything let's build the queries
        $queries = array_fill(0, $tablesCount, ["query" => "", "hasClause" => false, "hasWhere" => false]);
        foreach ($ranges as $range)
        {
            if($range[0] > $range[1])
            {
                $result = "Logical error: searching for $idname where $range[0]>$idname<$range[1] but $range[0]>$range[1] this would give no result ∅";
                return;
            }
            // range_table_index 0 & 1
            $r0ti = (int)(($range[0]-1)/$tablesLength);
            $r1ti = (int)(($range[1]-1)/$tablesLength);
            $r1ti = $r1ti >= count($queries) ? count($queries)-1 : $r1ti;

            if(($break = $range[1] - $range[0]) > $tablesLength)
            {
                $i = $r0ti;
                while($break > $tablesLength && ++$i < count($queries))
                {
                    $queries[$i]["query"] = "SELECT $selecting FROM $tableName".($i+1);
                    $queries[$i]["hasClause"] = true;
                    $break-=$tablesLength;
                }
            }

            if(($range[0] < ($r0ti + 1) * $tablesLength && $range[0] !== 0) || $range[0] === $range[1])
            {
                if(!$queries[$r0ti]["hasClause"])
                {
                    $queries[$r0ti]["query"] = "SELECT $selecting FROM $tableName".($r0ti+1);
                    $queries[$r0ti]["hasClause"] = true;
                }
                if(!$queries[$r0ti]["hasWhere"]) {
                    $queries[$r0ti]["query"] .= " WHERE ";
                    $queries[$r0ti]["hasWhere"] = true;
                }else
                    $queries[$r0ti]["query"] .= " OR ";
                $queries[$r0ti]["query"] .= "$idname > ".($range[0]-($r0ti*$tablesLength));
            }else if($range[0] === 0 && !$queries[$r0ti]["hasClause"]) {
                $queries[$r0ti]["query"] = "SELECT $selecting FROM $tableName".($r0ti+1);
                $queries[$r0ti]["hasClause"] = true;
            }
            if($range[1] <= $r1ti*$tablesLength+$tablesLength)
            {
                if(!$queries[$r1ti]["hasClause"])
                {
                    $queries[$r1ti]["query"] = "SELECT $selecting FROM $tableName".($r1ti+1);
                    $queries[$r1ti]["hasClause"] = true;
                }
                if(!$queries[$r1ti]["hasWhere"]) {
                    $queries[$r1ti]["query"] .= " WHERE ";
                    $queries[$r1ti]["hasWhere"] = true;
                }else
                    $queries[$r1ti]["query"] .= $r0ti === $r1ti ? " AND " : " OR ";
                $queries[$r1ti]["query"] .= "$idname < ".($range[1]-($r1ti*$tablesLength));
            }
        }
        // unionize the sub-queries
        $result = implode("\nUNION\n ", array_filter(array_map(static function ($v) {
            return $v["query"];
        }, $queries), static function ($v) {
            return !empty($v);
        }));
    })();
}else
{
    // default values
//    $query = ["select age from users where publicId < 10 or publicId > 40 and publicId < 120 or publicId > 150", // normal query
//              "select id from workers where worker_id > 20 and worker_id < 180", // normal query
//              "select class from students where id < 10 or id > 40 and id < 100 and id < 120 or id > 150", // collided ranges
//              "select gender from participants where pid < 50 and pid > 50", // empty set
//              "select gender from participants where pid < 50 and pid > 60" // error
//        ][random_int(0, 4)];
    $query = "";
    $tablesCount = 4;
    $tablesLength = 50;
}

//if($debug != null)
//{
//    echo $result.'\n';
//    var_dump($debug);
//    exit;
//}
?>
<!DOCTYPE HTML>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>Lab Distributed Databases </title>
        <link href='http://fonts.googleapis.com/css?family=Bitter' rel='stylesheet' type='text/css'>
        <link href="style.css" type="text/css" rel="stylesheet">
        <link rel="icon" href="images/icon.png" type="image/icon type">
    </head>
    <body onload="<?php echo empty($result)?"":"document.getElementById('result').scrollIntoView();"?>">
        <div class="form-style-10">
            <h1>Lab Distributed Databases<span>Parsing distributed “select” SQL query in distributed database horizontally fragmented</span></h1>
            <form method="get" action="/tp">
                <div class="section"><span>1</span>Input Query</div>
                <div class="inner-wrap">
                    <label for="query">Enter the input query</label>
                    <textarea id="query" required name="query"><?php echo $query; ?></textarea>
                </div>
                <div class="section"><span>2</span>Environment Parameters</div>
                <div class="inner-wrap" style="text-align: left;font-family: 'Bitter';font-size: .9rem">
                    <table>
                        <tbody>
                        <tr>
                            <th><label for="count">Number of Tables</label></th>
                            <td><input id="count" type="number" name="tablesCount" value="<?php echo $tablesCount; ?>"/></td>
                        </tr>
                        <tr>
                            <th><label for="length">Tables Length</label></th>
                            <td><input id="length" type="number" name="tablesLength" value="<?php echo $tablesLength; ?>"/></td>
                        </tr>
                        </tbody>
                    </table>
                </div>
                <div class="section"><span>3</span>Output Query</div>
                <div class="inner-wrap">
                    <label>Results</label>
                    <textarea id="result" name="field2" readonly rows="<?php echo !empty($result)?10:4; ?>" cols="50"><?php echo $result; ?></textarea>
                </div>
                <div class="button-section">
                    <input type="submit" value="execute" />
                </div>
            </form>
        </div>
    </body>
</html>

## index2.php
<?php
# The include and require statements are identical, except upon failure:
//include_once 'variables.php';
//include_once 'parser.php';
//"select * from users where id < 10 or id > 40 and id < 100 and id < 120 or id > 150"

//error_reporting(E_COMPILE_ERROR);

//// i didn't use POST because the browser will keeps showing me confirmation pop-up when i refresh the page
//$_GET['query'] = "select name,note from student where studentId > 20 and studentId < 60 or studentId > 70 and studentId < 110";
//$_GET['tablesCount'] = "4";
//$_GET['tablesLength'] = "50";
$result = null;
$debug = null;
$tableDefinition = "student";
$idname = "studentId";
$columnDefinition = ["studentId", "name", "birthday", "note"];
function match_one($pattern, $string)
{
    preg_match("/$pattern/i", $string, $matches);
    $matches = $matches[0] ?? '';
    return trim($matches);
}
function expression_split($expression): array
{
    $chunks = preg_split('/\s*[<>!=](?=[<>!=]*\s*\d)\s*/', $expression);
    if(!is_array($chunks))
        return [];
    return array_values(array_filter($chunks, function($subexp){
        return !empty($subexp) && !match_one('[!=<>]', $subexp);
    }));
}
function extract_range($expression)
{
    global $result, $tablesLength, $tablesCount, $idname, $columnDefinition;
    $parts = expression_split($expression);

    if(count($parts) === 2 && !match_one('\d', $parts[1]))
    {
        $result = "parse Error: Cannot compare non integer values in where clause, expression ('$expression')";
        return null;
    }
    if(empty($parts[1])) {
        $result = "parse Error: expression '$parts[0]' without comparision";
        return null;
    }
    if(!in_array($parts[0], $columnDefinition)) {
        $result = "SQL Error: unknown column $parts[0]";
        return null;
    }
    if((int)$parts[1] < 0)
    {
        $result = "parse Error: comparing with negative values, expression ('$expression')";
        return null;
    }
    // $tableNumber = (int)(((int)$parts[1]+1)/$tablesLength+1);
    preg_match_all('/[<>!=](?=[<>!=]*\s*\d)/', $expression, $matches);
    $matches = is_array($matches[0]) ? $matches[0] : $matches;
    $operator = implode('', $matches);
    if(!$idname)
        $idname = $parts[0];
    else if(strtolower($parts[0]) !== strtolower($idname))
    {
        $operator = empty($operator) ? "<" : $operator;
        $parts[1] = empty($parts[1]) ? "<expression>" : $parts[1];
        $result = "parse Error: expected '$idname $operator $parts[1]' got ('$expression')";
        return null;
    }
    switch ($operator)
    {
        case '>':
            return [(int)$parts[1], $tablesLength * $tablesCount+1];
        case '<':
            return [0, (int)$parts[1]];
        case '=' :
        case '>=':
        case '<=':
        case '!=':
            $result = "Parse error: operator '$operator' not supported in expression ('$parts[0] $operator $parts[1]')";
            return null;
        default:
            $result = "Syntax error: no operator found in expression ('$parts[0] $operator $parts[1]')";
            return null;
    }
}

if(isset($_GET['query']) && !empty($query = trim(preg_replace('/\n/', '',$_GET['query']))))
{
    (function ()
    {
        global $result, $query, $tablesCount, $tablesLength, $debug, $idname, $tableDefinition, $columnDefinition;
        $tablesCount = (int)trim($_GET['tablesCount']);
        $tablesLength = (int)trim($_GET['tablesLength']);
        if($tablesCount < 1)
        {
            $result = "Input Error: Tables count must be greater than 1";
            return;
        }
        if($tablesLength < 1)
        {
            $result = "Input Error: tables length must greater than 1";
            return;
        }
        $selecting = match_one('(?<=select).*(?=from)', $query);
        $tableName = match_one('(?<=from).*(?=where)', $query);
        $conditionClause = match_one('(?<=where).*', $query);

        if(strcmp(strtolower($tableName), strtolower($tableDefinition))) {
            $result = "SQL Error: Unknown table $tableName";
            return;
        }
        foreach (preg_split('/\s*,\s*/', $selecting) as $column)
        {
            $knownColumns = ["*", ...array_map(fn($v)=> strtolower($v), $columnDefinition)];
            if(!in_array(strtolower($column), $knownColumns))
            {
                $result = "SQL Error: Unknown column $column";
                return;
            }
        }
        if(empty($tableName))
        {
            $result = "Syntax Error: no table selected";
            return;
        }
        if(empty($selecting))
        {
            $result = "Syntax Error: no columns selected";
            return;
        }

        if(empty($conditionClause) && !empty(match_one('where', $query)))
        {
            $result = "Syntax Error: empty condition clause";
            return;
        }
        if(empty($conditionClause) || ($v=filter_var(trim($conditionClause), FILTER_VALIDATE_INT)) > 0)
        {
            if($v === 0)
            {
                // empty set ∅
                $result = "SELECT $selecting FROM $tableName"."1"." WHERE 0";
                return;
            }
            // just a trick
            $conditionClause = "$idname > 0";
        }
        $debug=[];
        $maxRange = $tablesCount * $tablesLength;
        $ranges = [];
        $expressions = preg_split("/\s+or\s+/i", $conditionClause);
        foreach ($expressions as $expression)
        {
            if(count($ands = preg_split('/\s+and\s+/i', $expression)) > 1)
            {
                $and_ranges = [];
                // extract ranges of each AND
                foreach ($ands as $and)
                {
                    $r = extract_range($and);
                    if(!$r)
                        return;
                    $and_ranges[] = $r;
                }
                // combine ANDs into a single range
                $result = [0, $maxRange+1];
                foreach ($and_ranges as $range)
                {
                    if($range[0] > $result[0])
                        $result[0] = $range[0];
                    if($range[1] < $result[1])
                        $result[1] = $range[1];
                }
                $ranges[] = $result;
            }else
            {
                $r = extract_range($expression);
                if(!$r)
                    return;
                $ranges[] = $r;
            }
        }
        // sort ranges
        uasort($ranges, function ($r1, $r2) {
            return $r1[0] - $r2[0];
        });
        // remove collided ranges
        repeat:
        for ($i = 0, $iMax = count($ranges)-1; $i < $iMax; $i++)
        {
            if($ranges[$i][1] >= $ranges[$i+1][1])
            {
                unset($ranges[$i+1]);
                $ranges = array_values($ranges);
                goto repeat;
            }
        }
        // we have everything let's build the queries
        $queries = array_fill(0, $tablesCount, ["query" => "", "hasClause" => false, "hasWhere" => false]);
        foreach ($ranges as $range)
        {
            if($range[0] > $range[1])
            {
                $result = "Logical error: searching for $idname where $range[0]>$idname<$range[1] but $range[0]>$range[1] this would give no result ∅";
                return;
            }
            // range_table_index 0 & 1
            $r0ti = (int)(($range[0]-1)/$tablesLength);
            $r1ti = (int)(($range[1]-1)/$tablesLength);
            $r1ti = $r1ti >= count($queries) ? count($queries)-1 : $r1ti;
            if(($break = $range[1] - $range[0]) > $tablesLength)
            {
                $i = $r0ti;
                while($break > $tablesLength && ++$i < count($queries))
                {
                    $queries[$i]["query"] = "SELECT $selecting FROM $tableName".($i+1);
                    $queries[$i]["hasClause"] = true;
                    $break-=$tablesLength;
                }
            }

            if(($range[0] < ($r0ti + 1) * $tablesLength && $range[0] !== 0) || $range[0] === $range[1])
            {
                if(!$queries[$r0ti]["hasClause"])
                {
                    $queries[$r0ti]["query"] = "SELECT $selecting FROM $tableName".($r0ti+1);
                    $queries[$r0ti]["hasClause"] = true;
                }
                if(!$queries[$r0ti]["hasWhere"]) {
                    $queries[$r0ti]["query"] .= " WHERE ";
                    $queries[$r0ti]["hasWhere"] = true;
                }else
                    $queries[$r0ti]["query"] .= " OR ";
                $queries[$r0ti]["query"] .= "$idname > ".($range[0]-($r0ti*$tablesLength));
            }else if($range[0] === 0 && !$queries[$r0ti]["hasClause"]) {
                $queries[$r0ti]["query"] = "SELECT $selecting FROM $tableName".($r0ti+1);
                $queries[$r0ti]["hasClause"] = true;
            }
            if($range[1] <= $r1ti*$tablesLength+$tablesLength)
            {
                if(!$queries[$r1ti]["hasClause"])
                {
                    $queries[$r1ti]["query"] = "SELECT $selecting FROM $tableName".($r1ti+1);
                    $queries[$r1ti]["hasClause"] = true;
                }
                if(!$queries[$r1ti]["hasWhere"]) {
                    $queries[$r1ti]["query"] .= " WHERE ";
                    $queries[$r1ti]["hasWhere"] = true;
                }else
                    $queries[$r1ti]["query"] .= $r0ti === $r1ti ? " AND " : " OR ";
                $queries[$r1ti]["query"] .= "$idname < ".($range[1]-($r1ti*$tablesLength));
            }
        }
        // unionize the sub-queries
        $result = implode("\nUNION\n ", array_filter(array_map(static function ($v) {
            return $v["query"];
        }, $queries), static function ($v) {
            return !empty($v);
        }));
    })();
}else
{
    // default values
//    $query = ["select age from users where publicId < 10 or publicId > 40 and publicId < 120 or publicId > 150", // normal query
//              "select id from workers where worker_id > 20 and worker_id < 180", // normal query
//              "select class from students where id < 10 or id > 40 and id < 100 and id < 120 or id > 150", // collided ranges
//              "select gender from participants where pid < 50 and pid > 50", // empty set
//              "select gender from participants where pid < 50 and pid > 60" // error
//        ][random_int(0, 4)];
    $query = "";
    $tablesCount = 4;
    $tablesLength = 50;
}
//if(!$env)
//{
//    $env = "T1:20 100\nT2:101 120\nT3:121 150\nT4:151 200";
//}
//if($debug != null)
//{
//    echo $result.'\n';
//    var_dump($debug);
//    exit;
//}
?>
<!DOCTYPE HTML>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>Lab Distributed Databases </title>
        <link href='http://fonts.googleapis.com/css?family=Bitter' rel='stylesheet' type='text/css'>
        <link href="style.css" type="text/css" rel="stylesheet">
        <link rel="icon" href="images/icon.png" type="image/icon type">
    </head>
    <body onload="<?php echo empty($result)?"":"document.getElementById('result').scrollIntoView();"?>">
        <div class="form-style-10">
            <h1>Lab Distributed Databases<span>Parsing distributed “select” SQL query in distributed database horizontally fragmented</span></h1>
            <form method="get" action="/tp">
                <div class="section"><span>1</span>Input Query</div>
                <div class="inner-wrap">
                    <label for="query">Enter the input query</label>
                    <textarea id="query" required name="query"><?php echo $query; ?></textarea>
                </div>
                <div class="section"><span>2</span>Environment Parameters</div>
                <div class="inner-wrap" style="text-align: left;font-family: 'Bitter';font-size: .9rem">
                    <table>
                        <tbody>
                        <tr>
                            <th><label for="count">Number of Tables</label></th>
                            <td><input id="count" type="number" name="tablesCount" value="<?php echo $tablesCount; ?>"/></td>
                        </tr>
                        <tr>
                            <th><label for="length">Tables Length</label></th>
                            <td><input id="length" type="number" name="tablesLength" value="<?php echo $tablesLength; ?>"/></td>
                        </tr>
                        </tbody>
                    </table>
                    <label>Schema Definition</label>
                    <code>
                        <?php echo $tableDefinition?>(<?php echo implode(',', $columnDefinition)?>)
                    </code>
                </div>
                <div class="section"><span>3</span>Output Query</div>
                <div class="inner-wrap">
                    <label>Results</label>
                    <textarea id="result" name="field2" readonly rows="<?php echo !empty($result)?10:4; ?>" cols="50"> <?php echo $result; ?></textarea>
                </div>
                <div class="button-section">
                    <input type="submit" value="execute" />
                </div>
            </form>
        </div>
    </body>
</html>

## style.css
body{
	background: #e0d9cb;
}
.form-style-10{
	max-width:450px;
	padding:30px;
	margin:40px auto;
	background: #FFF;
	border-radius: 10px;
	-webkit-border-radius:10px;
	-moz-border-radius: 10px;
	box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.13);
	-moz-box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.13);
	-webkit-box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.13);
}
.form-style-10 .inner-wrap{
	padding: 30px;
	background: #F8F8F8;
	border-radius: 6px;
	margin-bottom: 15px;
}
.form-style-10 h1{
	background: #2aad31;
	padding: 20px 30px 15px 30px;
	margin: -30px -30px 30px -30px;
	border-radius: 10px 10px 0 0;
	-webkit-border-radius: 10px 10px 0 0;
	-moz-border-radius: 10px 10px 0 0;
	color: #fff;
	text-shadow: 1px 1px 3px rgba(0, 0, 0, 0.12);
	font: normal 30px 'Bitter', serif;
	-moz-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	-webkit-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	border: 1px solid #2AAD31FF;
}
.form-style-10 h1 > span{
	display: block;
	margin-top: 2px;
	font: 13px Arial, Helvetica, sans-serif;
}
.form-style-10 label{
	display: block;
	font: 13px Arial, Helvetica, sans-serif;
	color: #888;
	margin-bottom: 15px;
}
.form-style-10 input[type="text"],
.form-style-10 input[type="date"],
.form-style-10 input[type="datetime"],
.form-style-10 input[type="email"],
.form-style-10 input[type="number"],
.form-style-10 input[type="search"],
.form-style-10 input[type="time"],
.form-style-10 input[type="url"],
.form-style-10 input[type="password"],
.form-style-10 textarea,
.form-style-10 select {
	display: block;
	box-sizing: border-box;
	-webkit-box-sizing: border-box;
	-moz-box-sizing: border-box;
	width: 100%;
	padding: 8px;
	border-radius: 6px;
	-webkit-border-radius:6px;
	-moz-border-radius:6px;
	border: 2px solid #fff;
	box-shadow: inset 0px 1px 1px rgba(0, 0, 0, 0.33);
	-moz-box-shadow: inset 0px 1px 1px rgba(0, 0, 0, 0.33);
	-webkit-box-shadow: inset 0px 1px 1px rgba(0, 0, 0, 0.33);
}

.form-style-10 .section{
	font: normal 20px 'Bitter', serif;
	color: #2AAD31FF;
	margin-bottom: 5px;
}
.form-style-10 .section span {
	background: #2AAD31FF;
	padding: 5px 10px 5px 10px;
	position: absolute;
	border-radius: 50%;
	-webkit-border-radius: 50%;
	-moz-border-radius: 50%;
	border: 4px solid #fff;
	font-size: 14px;
	margin-left: -45px;
	color: #fff;
	margin-top: -3px;
}
.form-style-10 input[type="button"],
.form-style-10 input[type="submit"]{
	background: #2AAD31FF;
	padding: 8px 20px 8px 20px;
	border-radius: 5px;
	-webkit-border-radius: 5px;
	-moz-border-radius: 5px;
	color: #fff;
	text-shadow: 1px 1px 3px rgba(0, 0, 0, 0.12);
	font: normal 30px 'Bitter', serif;
	-moz-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	-webkit-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	border: 1px solid #257C9E;
	font-size: 15px;
}
.form-style-10 input[type="button"]:hover,
.form-style-10 input[type="submit"]:hover{
	background: #2A6881;
	-moz-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.28);
	-webkit-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.28);
	box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.28);
}
.form-style-10 .privacy-policy{
	float: right;
	width: 250px;
	font: 12px Arial, Helvetica, sans-serif;
	color: #4D4D4D;
	margin-top: 10px;
	text-align: right;
}
	One of them is the last revision i dont have time to check now...
	icon.png in images folder
	<?php
	# The include and require statements are identical, except upon failure:
	//include_once 'variables.php';
	//include_once 'parser.php';
	//"select * from users where id < 10 or id > 40 and id < 100 and id < 120 or id > 150"


	//// i didn't use POST because the browser will keeps showing me confirmation pop-up when i refresh the page
	$_GET['query'] = "select name,project from employee where employeeId > 20 and employeeId < 180";
	$_GET['tablesCount'] = "4";
	$_GET['tablesLength'] = "50";
	$result = null;
	$debug = null;
	$idname = "employeeId";

	function match_one($pattern, $string)
	{
	preg_match("/$pattern/i", $string, $matches);
	$matches = $matches[0] ?? '';
	return trim($matches);
	}
	function expression_split($expression): array
	{
	$chunks = preg_split('/\s[<>!=](?=[<>!=]\s\d)\s/', $expression);
	if(!is_array($chunks))
	return [];
	return array_values(array_filter($chunks, function($subexp){
	return !empty($subexp) && !match_one('[!=<>]', $subexp);
	}));
	}
	function extract_range($expression)
	{
	global $result, $tablesLength, $tablesCount, $idname;
	$parts = expression_split($expression);

	if(count($parts) === 2 && !match_one('\d', $parts[1]))
	{
	$result = "parse Error: Cannot compare non integer values in where clause, expression ('$expression')";
	return null;
	}
	if((int)$parts[1] < 0)
	{
	$result = "parse Error: comparing with negative values, expression ('$expression')";
	return null;
	}
	// $tableNumber = (int)(((int)$parts[1]+1)/$tablesLength+1);
	preg_match_all('/[<>!=](?=[<>!=]\s\d)/', $expression, $matches);
	$matches = is_array($matches[0]) ? $matches[0] : $matches;
	$operator = implode('', $matches);
	if(!$idname)
	$idname = $parts[0];
	else if(strtolower($parts[0]) !== strtolower($idname))
	{
	$result = "parse Error: expected '$idname $operator $parts[1]' got ('$expression')";
	return null;
	}
	switch ($operator)
	{
	case '>':
	return [(int)$parts[1], $tablesLength * $tablesCount+1];
	case '<':
	return [0, (int)$parts[1]];
	case '=' :
	case '>=':
	case '<=':
	case '!=':
	$result = "Parse error: operator '$operator' not supported in expression ('$parts[0] $operator $parts[1]')";
	return null;
	default:
	$result = "Syntax error: no operator found in expression ('$parts[0] $operator $parts[1]')";
	return null;
	}
	}

	if(isset($_GET['query']) && !empty($query = trim(preg_replace('/\n/', '',$_GET['query']))))
	{
	(function ()
	{
	global $result, $query, $tablesCount, $tablesLength, $debug, $idname;
	$tablesCount = (int)trim($_GET['tablesCount']);
	$tablesLength = (int)trim($_GET['tablesLength']);
	if($tablesCount < 1)
	{
	$result = "Input Error: Tables count must be greater than 1";
	return;
	}
	if($tablesLength < 1)
	{
	$result = "Input Error: tables count must greater than 1";
	return;
	}
	$selecting = match_one('(?<=select).*(?=from)', $query);
	$tableName = match_one('(?<=from).*(?=where)', $query);
	$conditionClause = match_one('(?<=where).*', $query);

	if(strcmp($tableName, "employee"))
	{
	$result = "SQL Error: Unknown table $tableName";
	return;
	}
	foreach (preg_split('/\s,\s/', $selecting) as $column)
	{
	if(!in_array(strtolower($column), ["*", "employeeid", "birthday", "project", "name"]))
	{
	$result = "SQL Error: Unknown column $column";
	return;
	}
	}
	if(empty($tableName))
	{
	$result = "Syntax Error: no table selected";
	return;
	}
	if(empty($selecting))
	{
	$result = "Syntax Error: no columns selected";
	return;
	}

	if(empty($conditionClause) && !empty(match_one('where', $query)))
	{
	$result = "Syntax Error: empty condition clause";
	return;
	}
	if(empty($conditionClause) \|\| ($v=filter_var(trim($conditionClause), FILTER_VALIDATE_INT)) > 0)
	{
	if($v === 0)
	{
	// empty set ∅
	$result = "SELECT $selecting FROM $tableName"."1"." WHERE 0";
	return;
	}
	// just a trick
	$conditionClause = "id > 0";
	}
	$debug=[];
	$maxRange = $tablesCount * $tablesLength;
	$ranges = [];
	$expressions = preg_split("/\s+or\s+/i", $conditionClause);
	foreach ($expressions as $expression)
	{
	if(count($ands = preg_split('/\s+and\s+/i', $expression)) > 1)
	{
	$and_ranges = [];
	// extract ranges of each AND
	foreach ($ands as $and)
	{
	$r = extract_range($and);
	if(!$r)
	return;
	$and_ranges[] = $r;
	}
	// combine ANDs into a single range
	$result = [0, $maxRange+1];
	foreach ($and_ranges as $range)
	{
	if($range[0] > $result[0])
	$result[0] = $range[0];
	if($range[1] < $result[1])
	$result[1] = $range[1];
	}
	$ranges[] = $result;
	}else
	{
	$r = extract_range($expression);
	if(!$r)
	return;
	$ranges[] = $r;
	}
	}
	// sort ranges
	uasort($ranges, function ($r1, $r2) {
	return $r1[0] - $r2[0];
	});
	// remove collided ranges
	repeat:
	for ($i = 0, $iMax = count($ranges)-1; $i < $iMax; $i++)
	{
	if($ranges[$i][1] >= $ranges[$i+1][1])
	{
	unset($ranges[$i+1]);
	$ranges = array_values($ranges);
	goto repeat;
	}
	}
	// we have everything let's build the queries
	$queries = array_fill(0, $tablesCount, ["query" => "", "hasClause" => false, "hasWhere" => false]);
	foreach ($ranges as $range)
	{
	if($range[0] > $range[1])
	{
	$result = "Logical error: searching for $idname where $range[0]>$idname<$range[1] but $range[0]>$range[1] this would give no result ∅";
	return;
	}
	// range_table_index 0 & 1
	$r0ti = (int)(($range[0]-1)/$tablesLength);
	$r1ti = (int)(($range[1]-1)/$tablesLength);
	$r1ti = $r1ti >= count($queries) ? count($queries)-1 : $r1ti;

	if(($break = $range[1] - $range[0]) > $tablesLength)
	{
	$i = $r0ti;
	while($break > $tablesLength && ++$i < count($queries))
	{
	$queries[$i]["query"] = "SELECT $selecting FROM $tableName".($i+1);
	$queries[$i]["hasClause"] = true;
	$break-=$tablesLength;
	}
	}

	if(($range[0] < ($r0ti + 1) * $tablesLength && $range[0] !== 0) \|\| $range[0] === $range[1])
	{
	if(!$queries[$r0ti]["hasClause"])
	{
	$queries[$r0ti]["query"] = "SELECT $selecting FROM $tableName".($r0ti+1);
	$queries[$r0ti]["hasClause"] = true;
	}
	if(!$queries[$r0ti]["hasWhere"]) {
	$queries[$r0ti]["query"] .= " WHERE ";
	$queries[$r0ti]["hasWhere"] = true;
	}else
	$queries[$r0ti]["query"] .= " OR ";
	$queries[$r0ti]["query"] .= "$idname > ".($range[0]-($r0ti*$tablesLength));
	}else if($range[0] === 0 && !$queries[$r0ti]["hasClause"]) {
	$queries[$r0ti]["query"] = "SELECT $selecting FROM $tableName".($r0ti+1);
	$queries[$r0ti]["hasClause"] = true;
	}
	if($range[1] <= $r1ti*$tablesLength+$tablesLength)
	{
	if(!$queries[$r1ti]["hasClause"])
	{
	$queries[$r1ti]["query"] = "SELECT $selecting FROM $tableName".($r1ti+1);
	$queries[$r1ti]["hasClause"] = true;
	}
	if(!$queries[$r1ti]["hasWhere"]) {
	$queries[$r1ti]["query"] .= " WHERE ";
	$queries[$r1ti]["hasWhere"] = true;
	}else
	$queries[$r1ti]["query"] .= $r0ti === $r1ti ? " AND " : " OR ";
	$queries[$r1ti]["query"] .= "$idname < ".($range[1]-($r1ti*$tablesLength));
	}
	}
	// unionize the sub-queries
	$result = implode("\nUNION\n ", array_filter(array_map(static function ($v) {
	return $v["query"];
	}, $queries), static function ($v) {
	return !empty($v);
	}));
	})();
	}else
	{
	// default values
	// $query = ["select age from users where publicId < 10 or publicId > 40 and publicId < 120 or publicId > 150", // normal query
	// "select id from workers where worker_id > 20 and worker_id < 180", // normal query
	// "select class from students where id < 10 or id > 40 and id < 100 and id < 120 or id > 150", // collided ranges
	// "select gender from participants where pid < 50 and pid > 50", // empty set
	// "select gender from participants where pid < 50 and pid > 60" // error
	// ][random_int(0, 4)];
	$query = "";
	$tablesCount = 4;
	$tablesLength = 50;
	}

	//if($debug != null)
	//{
	// echo $result.'\n';
	// var_dump($debug);
	// exit;
	//}
	?>
	<!DOCTYPE HTML>
	<html>
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<title>Lab Distributed Databases </title>
	<link href='http://fonts.googleapis.com/css?family=Bitter' rel='stylesheet' type='text/css'>
	<link href="style.css" type="text/css" rel="stylesheet">
	<link rel="icon" href="images/icon.png" type="image/icon type">
	</head>
	<body onload="<?php echo empty($result)?"":"document.getElementById('result').scrollIntoView();"?>">
	<div class="form-style-10">
	<h1>Lab Distributed Databases<span>Parsing distributed “select” SQL query in distributed database horizontally fragmented</span></h1>
	<form method="get" action="/tp">
	<div class="section"><span>1</span>Input Query</div>
	<div class="inner-wrap">
	<label for="query">Enter the input query</label>
	<textarea id="query" required name="query"><?php echo $query; ?></textarea>
	</div>
	<div class="section"><span>2</span>Environment Parameters</div>
	<div class="inner-wrap" style="text-align: left;font-family: 'Bitter';font-size: .9rem">
	<table>
	<tbody>
	<tr>
	<th><label for="count">Number of Tables</label></th>
	<td><input id="count" type="number" name="tablesCount" value="<?php echo $tablesCount; ?>"/></td>
	</tr>
	<tr>
	<th><label for="length">Tables Length</label></th>
	<td><input id="length" type="number" name="tablesLength" value="<?php echo $tablesLength; ?>"/></td>
	</tr>
	</tbody>
	</table>
	</div>
	<div class="section"><span>3</span>Output Query</div>
	<div class="inner-wrap">
	<label>Results</label>
	<textarea id="result" name="field2" readonly rows="<?php echo !empty($result)?10:4; ?>" cols="50"><?php echo $result; ?></textarea>
	</div>
	<div class="button-section">
	<input type="submit" value="execute" />
	</div>
	</form>
	</div>
	</body>
	</html>
	body{
	background: #e0d9cb;
	}
	.form-style-10{
	max-width:450px;
	padding:30px;
	margin:40px auto;
	background: #FFF;
	border-radius: 10px;
	-webkit-border-radius:10px;
	-moz-border-radius: 10px;
	box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.13);
	-moz-box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.13);
	-webkit-box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.13);
	}
	.form-style-10 .inner-wrap{
	padding: 30px;
	background: #F8F8F8;
	border-radius: 6px;
	margin-bottom: 15px;
	}
	.form-style-10 h1{
	background: #2aad31;
	padding: 20px 30px 15px 30px;
	margin: -30px -30px 30px -30px;
	border-radius: 10px 10px 0 0;
	-webkit-border-radius: 10px 10px 0 0;
	-moz-border-radius: 10px 10px 0 0;
	color: #fff;
	text-shadow: 1px 1px 3px rgba(0, 0, 0, 0.12);
	font: normal 30px 'Bitter', serif;
	-moz-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	-webkit-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	border: 1px solid #2AAD31FF;
	}
	.form-style-10 h1 > span{
	display: block;
	margin-top: 2px;
	font: 13px Arial, Helvetica, sans-serif;
	}
	.form-style-10 label{
	display: block;
	font: 13px Arial, Helvetica, sans-serif;
	color: #888;
	margin-bottom: 15px;
	}
	.form-style-10 input[type="text"],
	.form-style-10 input[type="date"],
	.form-style-10 input[type="datetime"],
	.form-style-10 input[type="email"],
	.form-style-10 input[type="number"],
	.form-style-10 input[type="search"],
	.form-style-10 input[type="time"],
	.form-style-10 input[type="url"],
	.form-style-10 input[type="password"],
	.form-style-10 textarea,
	.form-style-10 select {
	display: block;
	box-sizing: border-box;
	-webkit-box-sizing: border-box;
	-moz-box-sizing: border-box;
	width: 100%;
	padding: 8px;
	border-radius: 6px;
	-webkit-border-radius:6px;
	-moz-border-radius:6px;
	border: 2px solid #fff;
	box-shadow: inset 0px 1px 1px rgba(0, 0, 0, 0.33);
	-moz-box-shadow: inset 0px 1px 1px rgba(0, 0, 0, 0.33);
	-webkit-box-shadow: inset 0px 1px 1px rgba(0, 0, 0, 0.33);
	}

	.form-style-10 .section{
	font: normal 20px 'Bitter', serif;
	color: #2AAD31FF;
	margin-bottom: 5px;
	}
	.form-style-10 .section span {
	background: #2AAD31FF;
	padding: 5px 10px 5px 10px;
	position: absolute;
	border-radius: 50%;
	-webkit-border-radius: 50%;
	-moz-border-radius: 50%;
	border: 4px solid #fff;
	font-size: 14px;
	margin-left: -45px;
	color: #fff;
	margin-top: -3px;
	}
	.form-style-10 input[type="button"],
	.form-style-10 input[type="submit"]{
	background: #2AAD31FF;
	padding: 8px 20px 8px 20px;
	border-radius: 5px;
	-webkit-border-radius: 5px;
	-moz-border-radius: 5px;
	color: #fff;
	text-shadow: 1px 1px 3px rgba(0, 0, 0, 0.12);
	font: normal 30px 'Bitter', serif;
	-moz-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	-webkit-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.17);
	border: 1px solid #257C9E;
	font-size: 15px;
	}
	.form-style-10 input[type="button"]:hover,
	.form-style-10 input[type="submit"]:hover{
	background: #2A6881;
	-moz-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.28);
	-webkit-box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.28);
	box-shadow: inset 0px 2px 2px 0px rgba(255, 255, 255, 0.28);
	}
	.form-style-10 .privacy-policy{
	float: right;
	width: 250px;
	font: 12px Arial, Helvetica, sans-serif;
	color: #4D4D4D;
	margin-top: 10px;
	text-align: right;
	}