SelectQueryHandler::getLeftJoins() - Code Metrics - sweetrdf/in-memory-store-sqlite - Measure and Improve Code Quality continuously with Scrutinizer

SelectQueryHandler::getLeftJoins() A
last analyzed 2024-11-26 16:47 UTC

↳ Parent: SelectQueryHandler
Complexity

Conditions	4
Paths	6
Size

Total Lines	15
Code Lines	9
Duplication

Lines	0
Ratio	0 %
Code Coverage

Tests	9
CRAP Score	4.016
Importance

Changes	1
Bugs	0	Features	0
Metric	Value
cc	4
eloc	9
c	1
b	0
f	0
nc	6
nop	0
dl	0
loc	15
ccs	9
cts	10
cp	0.9
crap	4.016
rs	9.9666
<?php

/**
 * This file is part of the sweetrdf/InMemoryStoreSqlite package and licensed under
 * the terms of the GPL-2 license.
 *
 * (c) Konrad Abicht <[email protected]>
 * (c) Benjamin Nowack
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace sweetrdf\InMemoryStoreSqlite\Store\QueryHandler;

use Exception;
use sweetrdf\InMemoryStoreSqlite\PDOSQLiteAdapter;

class SelectQueryHandler extends QueryHandler
{
    private int $cache_results;


    /**
     * @var array<mixed>
     */
    private array $dependency_log;

    private string $engine_type;


    /**
     * @var array<miyed>
     */
    private array $index;

    /**
     * @var array<miyed>
     */
    private array $indexes;

    /**
     * @var array<miyed>
     */
    private array $initial_index;

    private int $is_union_query;

    /**
     * @var array<miyed>
     */
    protected array $infos;

    private $opt_sql;

    private int $opt_sql_pd_count;

    private int $pattern_order_offset;

    public function runQuery($infos)
    {
        $this->infos = $infos;
        $this->infos['null_vars'] = [];
        $this->indexes = [];
        $this->pattern_order_offset = 0;
        $q_sql = $this->getSQL();

        /* create intermediate results (ID-based) */
        $tmp_tbl = $this->createTempTable($q_sql);

        /* join values */
        $r = $this->getFinalQueryResult($q_sql, $tmp_tbl);

        /* remove intermediate results */
        $this->store->getDBObject()->exec('DROP TABLE IF EXISTS '.$tmp_tbl);

        return $r;
    }

    private function getSQL()
    {
        $r = '';
        $nl = "\n";
        $this->buildInitialIndexes();
        foreach ($this->indexes as $i => $index) {
            $this->index = array_merge($this->getEmptyIndex(), $index);
            $this->analyzeIndex($this->getPattern('0'));
            $sub_r = $this->getQuerySQL();
            $r .= $r ? $nl.'UNION'.$this->getDistinctSQL().$nl : '';

            $setBracket = $this->is_union_query && !$this->store->getDBObject() instanceof PDOSQLiteAdapter;

            $r .= $setBracket ? '('.$sub_r.')' : $sub_r;

            $this->indexes[$i] = $this->index;
        }
        $r .= $this->is_union_query ? $this->getLIMITSQL() : '';
        $orderInfos = $this->infos['query']['order_infos'] ?? 0;
        if ($orderInfos) {
            $r = preg_replace('/SELECT(\s+DISTINCT)?\s*/', 'SELECT\\1 NULL AS `TMPPOS`, ', $r);
        }
        $pd_count = $this->problematicDependencies();
        if ($pd_count) {
            /* re-arranging the patterns sometimes reduces the LEFT JOIN dependencies */
            $set_sql = 0;
            if (!$this->pattern_order_offset) {
                $set_sql = 1;
            }
            if (!$set_sql && ($pd_count < $this->opt_sql_pd_count)) {
                $set_sql = 1;
            }
            if (!$set_sql && ($pd_count == $this->opt_sql_pd_count) && (\strlen($r) < \strlen($this->opt_sql))) {
                $set_sql = 1;
            }
            if ($set_sql) {
                $this->opt_sql = $r;
                $this->opt_sql_pd_count = $pd_count;
            }
            ++$this->pattern_order_offset;
            if ($this->pattern_order_offset > 5) {
                return $this->opt_sql;
            }

            return $this->getSQL();
        }

        return $r;
    }

    private function buildInitialIndexes()
    {
        $this->dependency_log = [];
        $this->index = $this->getEmptyIndex();

        // if no pattern is in the query, the index "pattern" is undefined, which leads to an error.
        // TODO throw an exception/raise an error and avoid "Undefined index: pattern" notification
        $this->buildIndex($this->infos['query']['pattern'], 0);
        $tmp = $this->index;
        $this->analyzeIndex($this->getPattern('0'));
        $this->initial_index = $this->index;
        $this->index = $tmp;
        $this->is_union_query = $this->index['union_branches'] ? 1 : 0;
        $this->indexes = $this->is_union_query ? $this->getUnionIndexes($this->index) : [$this->index];
    }

    private function createTempTable($q_sql)
    {
        $tbl = 'Q'.md5($q_sql.time().uniqid(rand()));
        if (\strlen($tbl) > 64) {
            $tbl = 'Q'.md5($tbl);
        }

        $tmp_sql = 'CREATE TABLE '.$tbl.' ( '.$this->getTempTableDefForSQLite($q_sql).')';
        $tmpSql2 = str_replace('CREATE TEMPORARY', 'CREATE', $tmp_sql);

        if (
            !$this->store->getDBObject()->simpleQuery($tmp_sql)
            && !$this->store->getDBObject()->simpleQuery($tmpSql2)
            && !empty($this->store->getDBObject()->getErrorMessage())
        ) {
            return $this->logger->error(
class A
{
    function getObject()
    {
        return null;
    }

}

$a = new A();
if ($a->getObject()) {
                $this->store->getDBObject()->getErrorMessage()
            );
        }
        if (false === $this->store->getDBObject()->exec('INSERT INTO '.$tbl.' '."\n".$q_sql)) {
            $this->logger->error($this->store->getDBObject()->getErrorMessage());
        }

        return $tbl;
    }

    private function getEmptyIndex()
    {
        return [
            'from' => [],
            'join' => [],
            'left_join' => [],
            'vars' => [], 'graph_vars' => [], 'graph_uris' => [],
            'bnodes' => [],
            'triple_patterns' => [],
            'sub_joins' => [],
            'constraints' => [],
            'union_branches' => [],
            'patterns' => [],
            'havings' => [],
        ];
    }

    private function getTempTableDefForSQLite($q_sql)
    {
        $col_part = preg_replace('/^SELECT\s*(DISTINCT)?(.*)FROM.*$/s', '\\2', $q_sql);
        $parts = explode(',', $col_part);
        $has_order_infos = $this->infos['query']['order_infos'] ?? 0;
        $r = '';
        $added = [];
        foreach ($parts as $part) {
            if (preg_match('/\.?(.+)\s+AS\s+`(.+)`/U', trim($part), $m) && !isset($added[$m[2]])) {
                $alias = $m[2];
                if ('TMPPOS' == $alias) {
                    continue;
                }
                $r .= $r ? ',' : '';
                $r .= "\n `".$alias.'` INTEGER UNSIGNED';
                $added[$alias] = 1;
            }
        }
        if ($has_order_infos) {
            $r = "\n".'`TMPPOS` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, '.$r;
        }

        return $r ? $r."\n" : '';
    }

    /**
     * It is protected because AskQueryHandler needs and overrides this function.
     */
    protected function getFinalQueryResult($q_sql, $tmp_tbl)
    {
        /* var names */
        $vars = [];
        $aggregate_vars = [];
        foreach ($this->infos['query']['result_vars'] as $entry) {
            if ($entry['aggregate']) {
                $vars[] = $entry['alias'];
                $aggregate_vars[] = $entry['alias'];
            } else {
                $vars[] = $entry['var'];
            }
        }
        /* result */
        $r = ['variables' => $vars];
        $v_sql = $this->getValueSQL($tmp_tbl, $q_sql);

        $entries = [];

        try {
            $entries = $this->store->getDBObject()->fetchList($v_sql);
        } catch (\Exception $e) {
            $this->logger->error($e->getMessage());
        }

        $rows = [];
        $types = [0 => 'uri', 1 => 'bnode', 2 => 'literal'];
        if (0 < \count($entries)) {
            foreach ($entries as $pre_row) {
                $row = [];
                foreach ($vars as $var) {
                    if (isset($pre_row[$var])) {
                        $row[$var] = $pre_row[$var];
                        $row[$var.' type'] = isset($pre_row[$var.' type'])
                            ? $types[$pre_row[$var.' type']]
                            : (
                                \in_array($var, $aggregate_vars)
                                    ? 'literal'
                                    : 'uri'
                            );
                        if (
                            isset($pre_row[$var.' lang_dt'])
                            && ($lang_dt = $pre_row[$var.' lang_dt'])
                        ) {
                            if (preg_match('/^([a-z]+(\-[a-z0-9]+)*)$/i', $lang_dt)) {
                                $row[$var.' lang'] = $lang_dt;
                            } else {
                                $row[$var.' datatype'] = $lang_dt;
                            }
                        }
                    }
                }
                if ($row || !$vars) {
                    $rows[] = $row;
                }
            }
        }
        $r['rows'] = $rows;

        return $r;
    }

    private function buildIndex($pattern, $id)
    {
        $pattern['id'] = $id;
        $type = $pattern['type'] ?? '';
        $constraint = $pattern['constraint'] ?? 0;
        if ('filter' == $type && $constraint) {
            $sub_pattern = $pattern['constraint'];
            $sub_pattern['parent_id'] = $id;
            $sub_id = $id.'_0';
            $this->buildIndex($sub_pattern, $sub_id);
            $pattern['constraint'] = $sub_id;
        } else {
            $sub_patterns = $pattern['patterns'] ?? [];
            $keys = array_keys($sub_patterns);
            $spc = \count($sub_patterns);
            if ($spc > 4 && $this->pattern_order_offset) {
                $keys = [];
                for ($i = 0; $i < $spc; ++$i) {
                    $keys[$i] = $i + $this->pattern_order_offset;
                    while ($keys[$i] >= $spc) {
                        $keys[$i] -= $spc;
                    }
                }
            }
            foreach ($keys as $i => $key) {
                $sub_pattern = $sub_patterns[$key];
                $sub_pattern['parent_id'] = $id;
                $sub_id = $id.'_'.$key;
                $this->buildIndex($sub_pattern, $sub_id);
                $pattern['patterns'][$i] = $sub_id;
                if ('union' == $type) {
                    $this->index['union_branches'][] = $sub_id;
                }
            }
        }
        $this->index['patterns'][$id] = $pattern;
    }

    private function analyzeIndex($pattern)
    {
        $type = $pattern['type'] ?? '';
        if (!$type) {
            return false;
        }
        $type = $pattern['type'];
        $id = $pattern['id'];
        /* triple */
        if ('triple' == $type) {
            foreach (['s', 'p', 'o'] as $term) {
                if ('var' == $pattern[$term.'_type']) {
                    $val = $pattern[$term];
                    $this->index['vars'][$val] = array_merge(
                        $this->index['vars'][$val] ?? [],
                        [['table' => $pattern['id'], 'col' => $term]]
                    );
                }
                if ('bnode' == $pattern[$term.'_type']) {
                    $val = $pattern[$term];
                    $this->index['bnodes'][$val] = array_merge(
                        $this->index['bnodes'][$val] ?? [],
                        [['table' => $pattern['id'], 'col' => $term]]
                    );
                }
            }
            $this->index['triple_patterns'][] = $pattern['id'];
            /* joins */
            if ($this->isOptionalPattern($id)) {
                $this->index['left_join'][] = $id;
            } elseif (!$this->index['from']) {
                $this->index['from'][] = $id;
            } elseif (!$this->getJoinInfos($id)) {
                $this->index['from'][] = $id;
            } else {
                $this->index['join'][] = $id;
            }
            /* graph infos, graph vars */
            $this->index['patterns'][$id]['graph_infos'] = $this->getGraphInfos($id);
            foreach ($this->index['patterns'][$id]['graph_infos'] as $info) {
                if ('graph' == $info['type']) {
                    if ($info['var']) {
                        $val = $info['var']['value'];
                        $this->index['graph_vars'][$val] = array_merge(
                            $this->index['graph_vars'][$val] ?? [],
                            [['table' => $id]]
                        );
                    } elseif ($info['uri']) {
                        $val = $info['uri'];
                        $this->index['graph_uris'][$val] = array_merge(
                            $this->index['graph_uris'][$val] ?? [],
                            [['table' => $id]]
                        );
                    }
                }
            }
        }
        $sub_ids = $pattern['patterns'] ?? [];
        foreach ($sub_ids as $sub_id) {
            $this->analyzeIndex($this->getPattern($sub_id));
        }
    }

    private function getGraphInfos($id)
    {
        $r = [];
        if ($id) {
            $pattern = $this->index['patterns'][$id];
            $type = $pattern['type'];
            /* graph */
            if ('graph' == $type) {
                $r[] = ['type' => 'graph', 'var' => $pattern['var'], 'uri' => $pattern['uri']];
            }
            $p_pattern = $this->index['patterns'][$pattern['parent_id']];
            if (isset($p_pattern['graph_infos'])) {
                return array_merge($p_pattern['graph_infos'], $r);
            }

            return array_merge($this->getGraphInfos($pattern['parent_id']), $r);
        } else {
            /* FROM / FROM NAMED */
            if (isset($this->infos['query']['dataset'])) {
                foreach ($this->infos['query']['dataset'] as $set) {
                    $r[] = array_merge(['type' => 'dataset'], $set);
                }
            }
        }

        return $r;
    }

    private function getPattern($id): array
    {
        if (\is_array($id)) {
            return $id;
        }

        return $this->index['patterns'][$id] ?? [];
    }

    private function getInitialPattern($id): array
    {
        return $this->initial_index['patterns'][$id] ?? [];
    }

    private function getUnionIndexes($pre_index)
    {
        $r = [];
        $branches = [];
        $min_depth = 1000;
        /* only process branches with minimum depth */
        foreach ($pre_index['union_branches'] as $id) {
            $branches[$id] = \count(preg_split('/\_/', $id));
            $min_depth = min($min_depth, $branches[$id]);
        }
        foreach ($branches as $branch_id => $depth) {
            if ($depth == $min_depth) {
                $union_id = preg_replace('/\_[0-9]+$/', '', $branch_id);
                $index = [
                    'keeping' => $branch_id,
                    'union_branches' => [],
                    'patterns' => $pre_index['patterns'],
                ];
                $old_branches = $index['patterns'][$union_id]['patterns'];
                $skip_id = ($old_branches[0] == $branch_id) ? $old_branches[1] : $old_branches[0];
                $index['patterns'][$union_id]['type'] = 'group';
                $index['patterns'][$union_id]['patterns'] = [$branch_id];
                foreach ($index['patterns'] as $pattern_id => $pattern) {
                    if (preg_match('/^'.$skip_id.'/', $pattern_id)) {
                        unset($index['patterns'][$pattern_id]);
                    } elseif ('union' == $pattern['type']) {
                        foreach ($pattern['patterns'] as $sub_union_branch_id) {
                            $index['union_branches'][] = $sub_union_branch_id;
                        }
                    }
                }
                if ($index['union_branches']) {
                    $r = array_merge($r, $this->getUnionIndexes($index));
                } else {
                    $r[] = $index;
                }
            }
        }

        return $r;
    }

    private function isOptionalPattern($id)
    {
        $pattern = $this->getPattern($id);
        $type = $pattern['type'] ?? '';
        if ('optional' == $type) {
            return 1;
        }

        $parentId = $pattern['parent_id'] ?? '0';
        if ('0' == $parentId) {
            return 0;
        }

        return $this->isOptionalPattern($pattern['parent_id']);
    }

    private function getOptionalPattern($id)
    {
        $pn = $this->getPattern($id);
        do {
            $pn = $this->getPattern($pn['parent_id']);
        } while ($pn['parent_id'] && ('optional' != $pn['type']));

        return $pn['id'];
    }

    private function sameOptional($id, $id2)
    {
        return $this->getOptionalPattern($id) == $this->getOptionalPattern($id2);
    }

    private function isUnionPattern($id)
    {
        $pattern = $this->getPattern($id);

        $type = $pattern['type'] ?? '';
        if ('union' == $type) {
            return 1;
        }

        $parentId = $pattern['parent_id'] ?? '0';
        if ('0' == $parentId) {
            return 0;
        }

        return $this->isUnionPattern($parentId);
    }

    private function getValueTable($col)
    {
        return preg_match('/^(s|o)$/', $col) ? $col.'2val' : 'id2val';
    }

    private function getGraphTable()
    {
        return 'g2t';
    }

    private function getQuerySQL()
    {
        $nl = "\n";
        $where_sql = $this->getWHERESQL();  /* pre-fills $index['sub_joins'] $index['constraints'] */

        $order_sql = $this->getORDERSQL();  /* pre-fills $index['sub_joins'] $index['constraints'] */


        return ''.(
            $this->is_union_query
                ? 'SELECT'
                : 'SELECT'.$this->getDistinctSQL()
        ).$nl.
                    $this->getResultVarsSQL().$nl. /* fills $index['sub_joins'] */
                    $this->getFROMSQL().
                    $this->getAllJoinsSQL().
                    $this->getWHERESQL().
                    $this->getGROUPSQL().
                    $this->getORDERSQL().
                    (
                        $this->is_union_query
                        ? ''
                        : $this->getLIMITSQL()
                    ).$nl.'';
    }

    private function getDistinctSQL()
    {
        $distinct = $this->infos['query']['distinct'] ?? 0;
        $reduced = $this->infos['query']['reduced'] ?? 0;

        $check = $distinct || $reduced;
        if ($this->is_union_query) {
            return $check ? '' : ' ALL';
        }

        return $check ? ' DISTINCT' : '';
    }

    private function getResultVarsSQL()
    {
        $r = '';
        $vars = $this->infos['query']['result_vars'];
        $nl = "\n";
        $added = [];
        foreach ($vars as $var) {
            $var_name = $var['var'];
            $tbl_alias = '';
            if ($tbl_infos = $this->getVarTableInfos($var_name, 0)) {
                $tbl = $tbl_infos['table'];
                $col = $tbl_infos['col'];
                $tbl_alias = $tbl_infos['table_alias'];
            } elseif (1 == $var_name) {/* ASK query */
                $r .= '1 AS `success`';
            } else {
                $msg = 'Result variable "'.$var_name.'" not used in query.';
                $this->logger->warning($msg);
            }

            if ($tbl_alias) {
                /* aggregate */
                if ($var['aggregate']) {
                    $conv_code = '';
                    if ('count' != strtolower($var['aggregate'])) {
                        $tbl_alias = 'V_'.$tbl.'_'.$col.'.val';

                        $conv_code = '0 + ';
                    }
                    if (!isset($added[$var['alias']])) {
                        $r .= $r ? ','.$nl.'  ' : '  ';

                        $distinct = $this->infos['query']['distinct'] ?? 0;
                        $distinct_code = ('count' == strtolower($var['aggregate']))
                            && $distinct ? 'DISTINCT ' : '';

                        $r .= $var['aggregate']
                            .'('.$conv_code.$distinct_code.$tbl_alias.') AS `'.$var['alias'].'`';
                        $added[$var['alias']] = 1;
                    }
                } else {
                    /* normal var */
                    if (!isset($added[$var_name])) {
                        $r .= $r ? ','.$nl.'  ' : '  ';
                        $r .= $tbl_alias.' AS `'.$var_name.'`';
                        $is_s = ('s' == $col);
                        $is_o = ('o' == $col);
                        if ('NULL' == $tbl_alias) {
                            /* type / add in UNION queries? */
                            if ($is_s || $is_o) {
                                $r .= ', '.$nl.'    NULL AS `'.$var_name.' type`';
                            }
                            /* lang_dt / always add it in UNION queries, the var may be used as s/p/o */
                            if ($is_o || $this->is_union_query) {
                                $r .= ', '.$nl.'    NULL AS `'.$var_name.' lang_dt`';
                            }
                        } else {
                            /* type */
                            if ($is_s || $is_o) {
                                $r .= ', '.$nl.'    '.$tbl_alias.'_type AS `'.$var_name.' type`';
                            }
                            /* lang_dt / always add it in UNION queries, the var may be used as s/p/o */
                            if ($is_o) {
                                $r .= ', '.$nl.'    '.$tbl_alias.'_lang_dt AS `'.$var_name.' lang_dt`';
                            } elseif ($this->is_union_query) {
                                $r .= ', '.$nl.'    NULL AS `'.$var_name.' lang_dt`';
                            }
                        }
                        $added[$var_name] = 1;
                    }
                }
                if (!\in_array($tbl_alias, $this->index['sub_joins'])) {
                    $this->index['sub_joins'][] = $tbl_alias;
                }
            }
        }

        return $r ? $r : '1 AS `success`';
    }

    private function getVarTableInfos($var, $ignore_initial_index = 1)
    {
        if ('*' == $var) {
            return ['table' => '', 'col' => '', 'table_alias' => '*'];
        }
        if ($infos = $this->index['vars'][$var] ?? 0) {
            $infos[0]['table_alias'] = 'T_'.$infos[0]['table'].'.'.$infos[0]['col'];

            return $infos[0];
        }
        if ($infos = $this->index['graph_vars'][$var] ?? 0) {
            $infos[0]['col'] = 'g';
            $infos[0]['table_alias'] = 'G_'.$infos[0]['table'].'.'.$infos[0]['col'];

            return $infos[0];
        }
        if ($this->is_union_query && !$ignore_initial_index) {
            if (
                ($infos = $this->initial_index['vars'][$var] ?? 0)
                || ($infos = $this->initial_index['graph_vars'][$var] ?? 0)
            ) {
                if (!\in_array($var, $this->infos['null_vars'])) {
                    $this->infos['null_vars'][] = $var;
                }
                $infos[0]['table_alias'] = 'NULL';
                $infos[0]['col'] = !isset($infos[0]['col']) ? '' : $infos[0]['col'];

                return $infos[0];
            }
        }

        return 0;
    }

    private function getFROMSQL()
    {
        $from_ids = $this->index['from'];
        $r = '';
        foreach ($from_ids as $from_id) {
            $r .= $r ? ', ' : '';
            $r .= 'triple T_'.$from_id;
        }

        return $r ? 'FROM '.$r : '';
    }

    private function getOrderedJoinIDs()
    {
        return array_merge($this->index['from'], $this->index['join'], $this->index['left_join']);
    }

    private function getJoinInfos($id)
    {
        $r = [];
        $tbl_ids = $this->getOrderedJoinIDs();
        $pattern = $this->getPattern($id);
        foreach ($tbl_ids as $tbl_id) {
            $tbl_pattern = $this->getPattern($tbl_id);
            if ($tbl_id != $id) {
                foreach (['s', 'p', 'o'] as $tbl_term) {
                    foreach (['var', 'bnode', 'uri'] as $term_type) {
                        if ($tbl_pattern[$tbl_term.'_type'] == $term_type) {
                            foreach (['s', 'p', 'o'] as $term) {
                                if (($pattern[$term.'_type'] == $term_type) && ($tbl_pattern[$tbl_term] == $pattern[$term])) {
                                    $r[] = ['term' => $term, 'join_tbl' => $tbl_id, 'join_term' => $tbl_term];
                                }
                            }
                        }
                    }
                }
            }
        }

        return $r;
    }

    private function getAllJoinsSQL()
    {
        $js = $this->getJoins();
        $ljs = $this->getLeftJoins();
        $entries = array_merge($js, $ljs);
        $id2code = [];
        foreach ($entries as $entry) {
            if (preg_match('/([^\s]+) ON (.*)/s', $entry, $m)) {
                $id2code[$m[1]] = $entry;
            }
        }
        $deps = [];
        foreach ($id2code as $id => $code) {
            $deps[$id]['rank'] = 0;
            foreach ($id2code as $other_id => $other_code) {
                $deps[$id]['rank'] += ($id != $other_id) && preg_match('/'.$other_id.'/', $code) ? 1 : 0;
                $deps[$id][$other_id] = ($id != $other_id) && preg_match('/'.$other_id.'/', $code) ? 1 : 0;
            }
        }
        $r = '';
        do {
            /* get next 0-rank */
            $next_id = 0;
            foreach ($deps as $id => $infos) {
                if (0 == $infos['rank']) {
                    $next_id = $id;
                    break;
                }
            }
            if ($next_id) {
                $r .= "\n".$id2code[$next_id];
                unset($deps[$next_id]);
                foreach ($deps as $id => $infos) {
                    $deps[$id]['rank'] = 0;
                    unset($deps[$id][$next_id]);
                    foreach ($infos as $k => $v) {
                        if (!\in_array($k, ['rank', $next_id])) {
                            $deps[$id]['rank'] += $v;
                            $deps[$id][$k] = $v;
                        }
                    }
                }
            }
        } while ($next_id);

        return $r;
    }

    private function getJoins()
    {
        $r = [];
        $nl = "\n";
        foreach ($this->index['join'] as $id) {
            $sub_r = $this->getJoinConditionSQL($id);
            $r[] = 'JOIN triple T_'.$id.' ON ('.$sub_r.$nl.')';
        }
        foreach (array_merge($this->index['from'], $this->index['join']) as $id) {
            if ($sub_r = $this->getRequiredSubJoinSQL($id)) {
                $r[] = $sub_r;
            }
        }

        return $r;
    }

    private function getLeftJoins()
    {
        $r = [];
        $nl = "\n";
        foreach ($this->index['left_join'] as $id) {
            $sub_r = $this->getJoinConditionSQL($id);
            $r[] = 'LEFT JOIN triple T_'.$id.' ON ('.$sub_r.$nl.')';
        }
        foreach ($this->index['left_join'] as $id) {
            if ($sub_r = $this->getRequiredSubJoinSQL($id, 'LEFT')) {
                $r[] = $sub_r;
            }
        }

        return $r;
    }

    private function getJoinConditionSQL($id)
    {
        $r = '';
        $nl = "\n";
        $infos = $this->getJoinInfos($id);
        $pattern = $this->getPattern($id);

        $tbl = 'T_'.$id;
        /* core dependency */
        $d_tbls = $this->getDependentJoins($id);
        foreach ($d_tbls as $d_tbl) {
            if (preg_match('/^T_([0-9\_]+)\.[spo]+/', $d_tbl, $m) && ($m[1] != $id)) {
                if ($this->isJoinedBefore($m[1], $id) && !\in_array($m[1], array_merge($this->index['from'], $this->index['join']))) {
0   == false // true
0   == null  // true
123 == false // false
123 == null  // false

// It is often better to use strict comparison
0 === false // false
0 === null  // false
                    $r .= $r ? $nl.'  AND ' : $nl.'  ';
                    $r .= '('.$d_tbl.' IS NOT NULL)';
                }
                $this->logDependency($id, $d_tbl);
            }
        }
        /* triple-based join info */
        foreach ($infos as $info) {
            if ($this->isJoinedBefore($info['join_tbl'], $id) && $this->joinDependsOn($id, $info['join_tbl'])) {
0   == false // true
0   == null  // true
123 == false // false
123 == null  // false

// It is often better to use strict comparison
0 === false // false
0 === null  // false
                $r .= $r ? $nl.'  AND ' : $nl.'  ';
                $r .= '('.$tbl.'.'.$info['term'].' = T_'.$info['join_tbl'].'.'.$info['join_term'].')';
            }
        }
        /* filters etc */
        if ($sub_r = $this->getPatternSQL($pattern, 'join__T_'.$id)) {
            $r .= $r ? $nl.'  AND '.$sub_r : $nl.'  '.'('.$sub_r.')';
        }

        return $r;
    }

    /**
     * A log of identified table join dependencies in getJoinConditionSQL.
     */
    private function logDependency($id, $tbl)
    {
        if (!isset($this->dependency_log[$id])) {
            $this->dependency_log[$id] = [];
        }
        if (!\in_array($tbl, $this->dependency_log[$id])) {
            $this->dependency_log[$id][] = $tbl;
        }
    }

    /**
     * checks whether entries in the dependecy log could perhaps be optimized
     * (triggers re-ordering of patterns.
     */
    private function problematicDependencies()
    {
        foreach ($this->dependency_log as $id => $tbls) {
            if (\count($tbls) > 1) {
                return \count($tbls);
            }
        }

        return 0;
    }

    private function isJoinedBefore($tbl_1, $tbl_2)
    {
        $tbl_ids = $this->getOrderedJoinIDs();
        foreach ($tbl_ids as $id) {
            if ($id == $tbl_1) {
                return 1;
            }
            if ($id == $tbl_2) {
                return 0;
            }
        }
    }

    private function joinDependsOn($id, $id2)
    {
        if (\in_array($id2, array_merge($this->index['from'], $this->index['join']))) {
            return 1;
        }
        $d_tbls = $this->getDependentJoins($id2);
        //echo $id . ' :: ' . $id2 . '=>' . print_r($d_tbls, 1);
        foreach ($d_tbls as $d_tbl) {
            if (preg_match('/^T_'.$id.'\./', $d_tbl)) {
                return 1;
            }
        }

        return 0;
    }

    private function getDependentJoins($id)
    {
        $r = [];
        /* sub joins */
        foreach ($this->index['sub_joins'] as $alias) {
            if (preg_match('/^(T|V|G)_'.$id.'/', $alias)) {
                $r[] = $alias;
            }
        }
        /* siblings in shared optional */
        $o_id = $this->getOptionalPattern($id);
        foreach ($this->index['sub_joins'] as $alias) {
            if (preg_match('/^(T|V|G)_'.$o_id.'/', $alias) && !\in_array($alias, $r)) {
                $r[] = $alias;
            }
        }
        foreach ($this->index['left_join'] as $alias) {
            if (preg_match('/^'.$o_id.'/', $alias) && !\in_array($alias, $r)) {
                $r[] = 'T_'.$alias.'.s';
            }
        }

        return $r;
    }

    private function getRequiredSubJoinSQL($id, $prefix = '')
    {
        /* id is a triple pattern id. Optional FILTERS and GRAPHs are getting added to the join directly */
        $nl = "\n";
        $r = '';
        foreach ($this->index['sub_joins'] as $alias) {
            if (preg_match('/^V_'.$id.'_([a-z\_]+)\.val$/', $alias, $m)) {
                $col = $m[1];
                $sub_r = '';
                if ($this->isOptionalPattern($id)) {
                    $pattern = $this->getPattern($id);
                    do {
                        $pattern = $this->getPattern($pattern['parent_id']);
                    } while ($pattern['parent_id'] && ('optional' != $pattern['type']));
                    $sub_r = $this->getPatternSQL($pattern, 'sub_join__V_'.$id);
                }
                $sub_r = $sub_r ? $nl.'  AND ('.$sub_r.')' : '';
                /* lang dt only on literals */
                if ('o_lang_dt' == $col) {
                    $sub_sub_r = 'T_'.$id.'.o_type = 2';
                    $sub_r .= $nl.'  AND ('.$sub_sub_r.')';
                }
                $cur_prefix = $prefix ? $prefix.' ' : '';
                if ('g' == $col) {
                    $r .= trim($cur_prefix.'JOIN '.$this->getValueTable($col).' V_'.$id.'_'.$col.' ON ('.$nl.'  (G_'.$id.'.'.$col.' = V_'.$id.'_'.$col.'.id) '.$sub_r.$nl.')');
                } else {
                    $r .= trim($cur_prefix.'JOIN '.$this->getValueTable($col).' V_'.$id.'_'.$col.' ON ('.$nl.'  (T_'.$id.'.'.$col.' = V_'.$id.'_'.$col.'.id) '.$sub_r.$nl.')');
                }
            } elseif (preg_match('/^G_'.$id.'\.g$/', $alias, $m)) {
                $pattern = $this->getPattern($id);
                $sub_r = $this->getPatternSQL($pattern, 'graph_sub_join__G_'.$id);
                $sub_r = $sub_r ? $nl.'  AND '.$sub_r : '';
                /* dataset restrictions */
                $gi = $this->getGraphInfos($id);
                $sub_sub_r = '';
                $added_gts = [];
                foreach ($gi as $set) {
                    if (isset($set['graph']) && !\in_array($set['graph'], $added_gts)) {
                        $sub_sub_r .= '' !== $sub_sub_r ? ',' : '';
                        $sub_sub_r .= $this->getTermID($set['graph'], 'g');
                        $added_gts[] = $set['graph'];
                    }
                }
                $sub_r .= ('' !== $sub_sub_r) ? $nl.' AND (G_'.$id.'.g IN ('.$sub_sub_r.'))' : '';
                /* other graph join conditions */
                foreach ($this->index['graph_vars'] as $var => $occurs) {
                    $occur_tbls = [];
                    foreach ($occurs as $occur) {
                        $occur_tbls[] = $occur['table'];
                        if ($occur['table'] == $id) {
                            break;
                        }
                    }
                    foreach ($occur_tbls as $tbl) {
                        if (($tbl != $id) && \in_array($id, $occur_tbls) && $this->isJoinedBefore($tbl, $id)) {
0   == false // true
0   == null  // true
123 == false // false
123 == null  // false

// It is often better to use strict comparison
0 === false // false
0 === null  // false
                            $sub_r .= $nl.'  AND (G_'.$id.'.g = G_'.$tbl.'.g)';
                        }
                    }
                }
                $cur_prefix = $prefix ? $prefix.' ' : '';
                $r .= trim($cur_prefix.'JOIN '.$this->getGraphTable().' G_'.$id.' ON ('.$nl.'  (T_'.$id.'.t = G_'.$id.'.t)'.$sub_r.$nl.')');
            }
        }

        return $r;
    }

    private function getWHERESQL()
    {
        $r = '';
        $nl = "\n";
        /* standard constraints */
        $sub_r = $this->getPatternSQL($this->getPattern('0'), 'where');
        /* additional constraints */
        foreach ($this->index['from'] as $id) {
            if ($sub_sub_r = $this->getConstraintSQL($id)) {
                $sub_r .= $sub_r ? $nl.' AND '.$sub_sub_r : $sub_sub_r;
            }
        }
        $r .= $sub_r ?: '';
        /* left join dependencies */
        foreach ($this->index['left_join'] as $id) {
            $d_joins = $this->getDependentJoins($id);
            $added = [];
            $d_aliases = [];
            $id_alias = 'T_'.$id.'.s';
            foreach ($d_joins as $alias) {
                if (preg_match('/^(T|V|G)_([0-9\_]+)(_[spo])?\.([a-z\_]+)/', $alias, $m)) {
                    $tbl_type = $m[1];
                    $tbl_pattern_id = $m[2];
                    $suffix = $m[3];
                    /* get rid of dependency permutations and nested optionals */
                    if (($tbl_pattern_id >= $id) && $this->sameOptional($tbl_pattern_id, $id)) {
                        if (!\in_array($tbl_type.'_'.$tbl_pattern_id.$suffix, $added)) {
                            $sub_r .= $sub_r ? ' AND ' : '';
                            $sub_r .= $alias.' IS NULL';
                            $d_aliases[] = $alias;
                            $added[] = $tbl_type.'_'.$tbl_pattern_id.$suffix;
                            $id_alias = ($tbl_pattern_id == $id) ? $alias : $id_alias;
                        }
                    }
                }
            }
            /* TODO fix this! */
            if (\count($d_aliases) > 2) {
                $sub_r1 = '  /* '.$id_alias.' dependencies */';
                $sub_r2 = '(('.$id_alias.' IS NULL) OR (CONCAT('.implode(', ', $d_aliases).') IS NOT NULL))';
                $r .= $r ? $nl.$sub_r1.$nl.'  AND '.$sub_r2 : $sub_r1.$nl.$sub_r2;
            }
        }

        return $r ? $nl.'WHERE '.$r : '';
    }

    private function addConstraintSQLEntry($id, $sql)
    {
        if (!isset($this->index['constraints'][$id])) {
            $this->index['constraints'][$id] = [];
        }
        if (!\in_array($sql, $this->index['constraints'][$id])) {
            $this->index['constraints'][$id][] = $sql;
        }
    }

    private function getConstraintSQL($id)
    {
        $r = '';
        $nl = "\n";
        $constraints = $this->index['constraints'][$id] ?? [];
        foreach ($constraints as $constraint) {
            $r .= $r ? $nl.'  AND '.$constraint : $constraint;
        }

        return $r;
    }

    private function getPatternSQL($pattern, $context)
    {
        $type = $pattern['type'] ?? '';
        if (!$type) {
            return '';
        }

        $m = 'get'.ucfirst($type).'PatternSQL';

        return method_exists($this, $m)
            ? $this->$m($pattern, $context)
            : $this->getDefaultPatternSQL($pattern, $context);
    }

    private function getDefaultPatternSQL($pattern, $context)
    {
        $r = '';
        $nl = "\n";
        $sub_ids = $pattern['patterns'] ?? [];
        foreach ($sub_ids as $sub_id) {
            $sub_r = $this->getPatternSQL($this->getPattern($sub_id), $context);
            $r .= ($r && $sub_r) ? $nl.'  AND ('.$sub_r.')' : ($sub_r ?: '');
        }

        return $r ? $r : '';
    }

    private function getTriplePatternSQL($pattern, $context)
    {
        $r = '';
        $nl = "\n";
        $id = $pattern['id'];
        /* s p o */
        $vars = [];
        foreach (['s', 'p', 'o'] as $term) {
            $sub_r = '';
            $type = $pattern[$term.'_type'];
            if ('uri' == $type) {
                $term_id = $this->getTermID($pattern[$term], $term);
                $sub_r = '(T_'.$id.'.'.$term.' = '.$term_id.') /* '
                    .preg_replace('/[\#\*\>]/', '::', $pattern[$term]).' */';
            } elseif ('literal' == $type) {
                $term_id = $this->getTermID($pattern[$term], $term);
                $sub_r = '(T_'.$id.'.'.$term.' = '.$term_id.') /* '
                    .preg_replace('/[\#\n\*\>]/', ' ', $pattern[$term]).' */';
                if (
                    ($lang_dt = $pattern[$term.'_lang'] ?? '')
                    || ($lang_dt = $pattern[$term.'_datatype'] ?? '')
                ) {
                    $lang_dt_id = $this->getTermID($lang_dt);
                    $sub_r .= $nl
                        .'  AND (T_'.$id.'.'.$term.'_lang_dt = '.$lang_dt_id.') /* '
                        .preg_replace('/[\#\*\>]/', '::', $lang_dt).' */';
                }
            } elseif ('var' == $type) {
                $val = $pattern[$term];
                if (isset($vars[$val])) {
                    /* repeated var in pattern */
                    $sub_r = '(T_'.$id.'.'.$term.'='.'T_'.$id.'.'.$vars[$val].')';
                }
                $vars[$val] = $term;
                if ($infos = $this->index['graph_vars'][$val] ?? 0) {
                    /* graph var in triple pattern */
                    $sub_r .= $sub_r ? $nl.'  AND ' : '';
                    $tbl = $infos[0]['table'];
                    $sub_r .= 'G_'.$tbl.'.g = T_'.$id.'.'.$term;
                }
            }
            if ($sub_r) {
                if (
                    preg_match('/^(join)/', $context)
                    || (preg_match('/^where/', $context) && \in_array($id, $this->index['from']))
                ) {
                    $r .= $r ? $nl.'  AND '.$sub_r : $sub_r;
                }
            }
        }
        /* g */
        if ($infos = $pattern['graph_infos']) {
            $tbl_alias = 'G_'.$id.'.g';
            if (!\in_array($tbl_alias, $this->index['sub_joins'])) {
                $this->index['sub_joins'][] = $tbl_alias;
            }
            $sub_r = ['graph_var' => '', 'graph_uri' => '', 'from' => '', 'from_named' => ''];
            foreach ($infos as $info) {
                $type = $info['type'];
                if ('graph' == $type) {
                    if ($info['uri']) {
                        $term_id = $this->getTermID($info['uri'], 'g');
                        $sub_r['graph_uri'] .= $sub_r['graph_uri'] ? $nl.' AND ' : '';
                        $sub_r['graph_uri'] .= '('.$tbl_alias.' = '.$term_id.') /* '
                            .preg_replace('/[\#\*\>]/', '::', $info['uri']).' */';
                    }
                }
            }
            if ($sub_r['from'] && $sub_r['from_named']) {
                $sub_r['from_named'] = '';
            }
            if (!$sub_r['from'] && !$sub_r['from_named']) {
                $sub_r['graph_var'] = '';
            }
            if (preg_match('/^(graph_sub_join)/', $context)) {
                foreach ($sub_r as $g_type => $g_sql) {
                    if ($g_sql) {
                        $r .= $r ? $nl.'  AND '.$g_sql : $g_sql;
                    }
                }
            }
        }
        /* optional sibling filters? */
        if (preg_match('/^(join|sub_join)/', $context) && $this->isOptionalPattern($id)) {
            $o_pattern = $pattern;
            do {
                $o_pattern = $this->getPattern($o_pattern['parent_id']);
            } while ($o_pattern['parent_id'] && ('optional' != $o_pattern['type']));
            if ($sub_r = $this->getPatternSQL($o_pattern, 'optional_filter'.preg_replace('/^(.*)(__.*)$/', '\\2', $context))) {
                $r .= $r ? $nl.'  AND '.$sub_r : $sub_r;
            }
            /* created constraints */
            if ($sub_r = $this->getConstraintSQL($id)) {
                $r .= $r ? $nl.'  AND '.$sub_r : $sub_r;
            }
        }
        /* result */
        if (preg_match('/^(where)/', $context) && $this->isOptionalPattern($id)) {
            return '';
        }

        return $r;
    }

    private function getFilterPatternSQL($pattern, $context)
    {
        $r = '';
        $id = $pattern['id'];
        $constraint_id = $pattern['constraint'] ?? '';
        $constraint = $this->getPattern($constraint_id);
        $constraint_type = $constraint['type'];
        if ('built_in_call' == $constraint_type) {
            $r = $this->getBuiltInCallSQL($constraint, $context);
        } elseif ('expression' == $constraint_type) {
            $r = $this->getExpressionSQL($constraint, $context, '', 'filter');
        } else {
            $m = 'get'.ucfirst($constraint_type).'ExpressionSQL';
            if (method_exists($this, $m)) {
                $r = $this->$m($constraint, $context, '', 'filter');
            }
        }
        if ($this->isOptionalPattern($id) && !preg_match('/^(join|optional_filter)/', $context)) {
            return '';
        }
        /* unconnected vars in FILTERs eval to false */
        $sub_r = $this->hasUnconnectedFilterVars($id);
        if ($sub_r) {
            if ('alias' == $sub_r) {
                if (!\in_array($r, $this->index['havings'])) {
                    $this->index['havings'][] = $r;
                }

                return '';
            } elseif (preg_match('/^T([^\s]+\.)g (.*)$/s', $r, $m)) {/* graph filter */
                return 'G'.$m[1].'t '.$m[2];
            } elseif (preg_match('/^\(*V[^\s]+_g\.val .*$/s', $r, $m)) {
                /* graph value filter, @@improveMe */
            } else {
                return 'FALSE';
            }
        }
        /* some really ugly tweaks */
        /* empty language filter: FILTER ( lang(?v) = '' ) */
        $r = preg_replace(
            '/\(\/\* language call \*\/ ([^\s]+) = ""\)/s',
            '((\\1 = "") OR (\\1 LIKE "%:%"))',
            $r
        );

        return $r;
    }

    /**
     * Checks if vars in the given (filter) pattern are used within the filter's scope.
     */
    private function hasUnconnectedFilterVars($filter_pattern_id)
    {
        $scope_id = $this->getFilterScope($filter_pattern_id);
        $vars = $this->getFilterVars($filter_pattern_id);
        $r = 0;
        foreach ($vars as $var_name) {
            if ($this->isUsedTripleVar($var_name, $scope_id)) {
0   == false // true
0   == null  // true
123 == false // false
123 == null  // false

// It is often better to use strict comparison
0 === false // false
0 === null  // false
                continue;
            }
            if ($this->isAliasVar($var_name)) {
                $r = 'alias';
                break;
            }
            $r = 1;
            break;
        }

        return $r;
    }

    /**
     * Returns the given filter pattern's scope (the id of the parent group pattern).
     */
    private function getFilterScope($filter_pattern_id)
    {
        $patterns = $this->initial_index['patterns'];
        $r = '';
        foreach ($patterns as $id => $p) {
            /* the id has to be sub-part of the given filter id */
            if (!preg_match('/^'.$id.'.+/', $filter_pattern_id)) {
                continue;
            }
            /* we are looking for a group or union */
            if (!preg_match('/^(group|union)$/', $p['type'])) {
                continue;
            }
            /* we are looking for the longest/deepest match */
            if (\strlen($id) > \strlen($r)) {
                $r = $id;
            }
        }

        return $r;
    }

    /**
     * Builds a list of vars used in the given (filter) pattern.
     */
    private function getFilterVars($filter_pattern_id)
    {
        $r = [];
        $patterns = $this->initial_index['patterns'];
        /* find vars in the given filter (i.e. the given id is part of their pattern id) */
        foreach ($patterns as $id => $p) {
            if (!preg_match('/^'.$filter_pattern_id.'.+/', $id)) {
                continue;
            }
            $var_name = '';
            if ('var' == $p['type']) {
                $var_name = $p['value'];
            } elseif (('built_in_call' == $p['type']) && ('bound' == $p['call'])) {
                $var_name = $p['args'][0]['value'];
            }
            if ($var_name && !\in_array($var_name, $r)) {
                $r[] = $var_name;
            }
        }

        return $r;
    }

    /**
     * Checks if $var_name appears as result projection alias.
     */
    private function isAliasVar($var_name)
    {
        foreach ($this->infos['query']['result_vars'] as $r_var) {
            if ($r_var['alias'] == $var_name) {
                return 1;
            }
        }

        return 0;
    }

    /**
     * Checks if $var_name is used in a triple pattern in the given scope.
     */
    private function isUsedTripleVar($var_name, $scope_id = '0')
    {
        $patterns = $this->initial_index['patterns'];
        foreach ($patterns as $id => $p) {
            if ('triple' != $p['type']) {
                continue;
            }
            if (!preg_match('/^'.$scope_id.'.+/', $id)) {
                continue;
            }
            foreach (['s', 'p', 'o'] as $term) {
                if ('var' != $p[$term.'_type']) {
                    continue;
                }
                if ($p[$term] == $var_name) {
                    return 1;
                }
            }
        }
    }

    private function getExpressionSQL($pattern, $context, $val_type = '', $parent_type = '')
    {
        $r = '';
        $type = $pattern['type'] ?? '';
        $sub_type = $pattern['sub_type'] ?? $type;
        if (preg_match('/^(and|or)$/', $sub_type)) {
            foreach ($pattern['patterns'] as $sub_id) {
                $sub_pattern = $this->getPattern($sub_id);
                $sub_pattern_type = $sub_pattern['type'];
                if ('built_in_call' == $sub_pattern_type) {
                    $sub_r = $this->getBuiltInCallSQL($sub_pattern, $context, '', $parent_type);
                    $sub_r = $this->getBuiltInCallSQL($sub_pattern, $context, '', $parent_type);
                } else {
                    $sub_r = $this->getExpressionSQL($sub_pattern, $context, '', $parent_type);
                }
                if ($sub_r) {
                    $r .= $r ? ' '.strtoupper($sub_type).' ('.$sub_r.')' : '('.$sub_r.')';
                }
            }
        } elseif ('built_in_call' == $sub_type) {
            $r = $this->getBuiltInCallSQL($pattern, $context, $val_type, $parent_type);
        } elseif (preg_match('/literal/', $sub_type)) {
            $r = $this->getLiteralExpressionSQL($pattern, $context, $val_type, $parent_type);
        } elseif ($sub_type) {
            $m = 'get'.ucfirst($sub_type).'ExpressionSQL';
            if (method_exists($this, $m)) {
                $r = $this->$m($pattern, $context, '', $parent_type);
            }
        }
        /* skip expressions that reference non-yet-joined tables */
        if (preg_match('/__(T|V|G)_(.+)$/', $context, $m)) {
            $context_pattern_id = $m[2];
            $context_table_type = $m[1];
            if (preg_match_all('/((T|V|G)(\_[0-9])+)/', $r, $m)) {
                $aliases = $m[1];
                $keep = 1;
                foreach ($aliases as $alias) {
                    if (preg_match('/(T|V|G)_(.*)$/', $alias, $m)) {
                        $tbl_type = $m[1];
                        $tbl = $m[2];
                        if (!$this->isJoinedBefore($tbl, $context_pattern_id)) {
0   == false // true
0   == null  // true
123 == false // false
123 == null  // false

// It is often better to use strict comparison
0 === false // false
0 === null  // false
                            $keep = 0;
                        } elseif (($context_pattern_id == $tbl) && preg_match('/(TV)/', $context_table_type.$tbl_type)) {
                            $keep = 0;
                        }
                    }
                }
                $r = $keep ? $r : '';
            }
        }

        return $r ? '('.$r.')' : $r;
    }

    private function detectExpressionValueType($pattern_ids)
    {
        foreach ($pattern_ids as $id) {
            $pattern = $this->getPattern($id);
            $type = $pattern['type'] ?? '';
            if (('literal' == $type) && isset($pattern['datatype'])) {
                $numericDatatypes = [$this->xsd.'integer', $this->xsd.'float', $this->xsd.'double'];
                if (\in_array($pattern['datatype'], $numericDatatypes)) {
                    return 'numeric';
                }
            }
        }

        return '';
    }

    private function getRelationalExpressionSQL($pattern, $context, $val_type = '', $parent_type = '')
    {
        $r = '';
        $val_type = $this->detectExpressionValueType($pattern['patterns']);
        $op = $pattern['operator'];
        foreach ($pattern['patterns'] as $sub_id) {
            $sub_pattern = $this->getPattern($sub_id);
            $sub_pattern['parent_op'] = $op;
            $sub_type = $sub_pattern['type'];
            $m = ('built_in_call' == $sub_type) ? 'getBuiltInCallSQL' : 'get'.ucfirst($sub_type).'ExpressionSQL';
            $m = str_replace('ExpressionExpression', 'Expression', $m);
            $sub_r = method_exists($this, $m) ? $this->$m($sub_pattern, $context, $val_type, 'relational') : '';
            $r .= $r ? ' '.$op.' '.$sub_r : $sub_r;
        }

        /*
         * SQLite related adaption for relational expressions like ?w < 100
         *
         * We have to cast the variable behind ?w to a number otherwise we don't get
         * meaningful results.
         */
        if ($this->store->getDBObject() instanceof PDOSQLiteAdapter) {

            // Regex to catch things like: ?w < 100, ?w > 20
            $regex = '/([T\_0-9]+\.o_comp)\s*[<>]{1}\s*[0-9]+/si';
            if (0 < preg_match_all($regex, $r, $matches)) {
                foreach ($matches[1] as $variable) {
                    $r = str_replace($variable, 'CAST ('.$variable.' as float)', $r);
                }
            }
        }

        return $r ? '('.$r.')' : $r;
    }

    private function getAdditiveExpressionSQL($pattern, $context, $val_type = '')
    {
        $r = '';
        $val_type = $this->detectExpressionValueType($pattern['patterns']);
        foreach ($pattern['patterns'] as $sub_id) {
            $sub_pattern = $this->getPattern($sub_id);
            $sub_type = $sub_pattern['type'] ?? '';
            $m = ('built_in_call' == $sub_type)
                ? 'getBuiltInCallSQL'
                : 'get'.ucfirst($sub_type).'ExpressionSQL';
            $m = str_replace('ExpressionExpression', 'Expression', $m);
            $sub_r = method_exists($this, $m)
                ? $this->$m($sub_pattern, $context, $val_type, 'additive')
                : '';
            $r .= $r ? ' '.$sub_r : $sub_r;
        }

        return $r;
    }

    private function getMultiplicativeExpressionSQL($pattern, $context, $val_type = '', $parent_type = '')
    {
        $r = '';
        $val_type = $this->detectExpressionValueType($pattern['patterns']);
        foreach ($pattern['patterns'] as $sub_id) {
            $sub_pattern = $this->getPattern($sub_id);
            $sub_type = $sub_pattern['type'];
            $m = ('built_in_call' == $sub_type)
                ? 'getBuiltInCallSQL'
                : 'get'.ucfirst($sub_type).'ExpressionSQL';
            $m = str_replace('ExpressionExpression', 'Expression', $m);
            $sub_r = method_exists($this, $m)
                ? $this->$m($sub_pattern, $context, $val_type, 'multiplicative')
                : '';
            $r .= $r ? ' '.$sub_r : $sub_r;
        }

        return $r;
    }

    private function getVarExpressionSQL($pattern, $context, $val_type = '', $parent_type = '')
    {
        $var = $pattern['value'];
        $info = $this->getVarTableInfos($var);

        $tbl = false;
        if (isset($info['table'])) {
            $tbl = $info['table'];
        }

        if (!$tbl) {
            /* might be an aggregate var */
            $vars = $this->infos['query']['result_vars'];
            foreach ($vars as $test_var) {
                if ($test_var['alias'] == $pattern['value']) {
                    return '`'.$pattern['value'].'`';
                }
            }

            return '';
        }
        $col = $info['col'];
        $parentOp = $pattern['parent_op'] ?? '';
        if ('order' == $context && 'o' == $col) {
            $tbl_alias = 'T_'.$tbl.'.o_comp';
        } elseif ('sameterm' == $context) {
            $tbl_alias = 'T_'.$tbl.'.'.$col;
        } elseif ('relational' == $parent_type && 'o' == $col && preg_match('/[\<\>]/', $parentOp)) {
            $tbl_alias = 'T_'.$tbl.'.o_comp';
        } else {
            $tbl_alias = 'V_'.$tbl.'_'.$col.'.val';
            if (!\in_array($tbl_alias, $this->index['sub_joins'])) {
                $this->index['sub_joins'][] = $tbl_alias;
            }
        }

        $op = $pattern['operator'] ?? '';
        if (preg_match('/^(filter|and)/', $parent_type)) {
            if ('!' == $op) {
                $r = '((('.$tbl_alias.' = 0) AND (CONCAT("1", '.$tbl_alias.') != 1))'; /* 0 and no string */
                $r .= ' OR ('.$tbl_alias.' IN ("", "false")))'; /* or "", or "false" */
            } else {
                $r = '(('.$tbl_alias.' != 0)'; /* not null */
                $r .= ' OR ((CONCAT("1", '.$tbl_alias.') = 1) AND ('.$tbl_alias.' NOT IN ("", "false"))))'; /* string, and not "" or "false" */
            }
        } else {
            $r = trim($op.' '.$tbl_alias);
            if ('numeric' == $val_type) {
                if (preg_match('/__(T|V|G)_(.+)$/', $context, $m)) {
                    $context_pattern_id = $m[2];
                    $context_table_type = $m[1];
                } else {
                    $context_pattern_id = $pattern['id'];
                    $context_table_type = 'T';
                }
                if ($this->isJoinedBefore($tbl, $context_pattern_id)) {
0   == false // true
0   == null  // true
123 == false // false
123 == null  // false

// It is often better to use strict comparison
0 === false // false
0 === null  // false
                    $add = ($tbl != $context_pattern_id) ? 1 : 0;
                    $add = (!$add && ('V' == $context_table_type)) ? 1 : 0;
                    if ($add) {
                        $this->addConstraintSQLEntry($context_pattern_id, '('.$r.' = "0" OR '.$r.'*1.0 != 0)');
                    }
                }
            }
        }

        return $r;
    }

    private function getUriExpressionSQL($pattern)
    {
        $val = $pattern['uri'];
        $r = $pattern['operator'];
        $r .= is_numeric($val) ? ' '.$val : ' "'.$this->store->getDBObject()->escape($val).'"';

        return $r;
    }

    private function getLiteralExpressionSQL($pattern, $context, $val_type = '', $parent_type = '')
    {
        $val = $pattern['value'];
        $r = $pattern['operator'];
        $datatype = $pattern['datatype'] ?? '';

        if (is_numeric($val) && ($pattern['datatype'] ?? 0)) {
            $r .= ' '.$val;
        } elseif (
            preg_match('/^(true|false)$/i', $val)
            && 'http://www.w3.org/2001/XMLSchema#boolean' == $datatype
        ) {
            $r .= ' '.strtoupper($val);
        } elseif ('regex' == $parent_type) {
            $sub_r = $this->store->getDBObject()->escape($val);
            $r .= ' "'.preg_replace('/\x5c\x5c/', '\\', $sub_r).'"';
        } else {
            $r .= ' "'.$this->store->getDBObject()->escape($val).'"';
        }

        $lang_dt = $pattern['lang'] ?? $pattern['datatype'] ?? '';
        if ($lang_dt) {
            /* try table/alias via var in siblings */
            if ($var = $this->findSiblingVarExpression($pattern['id'])) {
                if (isset($this->index['vars'][$var])) {
                    $infos = $this->index['vars'][$var];
                    foreach ($infos as $info) {
                        if ('o' == $info['col']) {
                            $tbl = $info['table'];
                            $term_id = $this->getTermID($lang_dt);
                            if ('!=' != $pattern['operator']) {
                                if (preg_match('/__(T|V|G)_(.+)$/', $context, $m)) {
                                    $context_pattern_id = $m[2];
                                    $context_table_type = $m[1];

                                } elseif ('where' == $context) {
                                    $context_pattern_id = $tbl;
                                } else {
                                    $context_pattern_id = $pattern['id'];
                                }
                                // TODO better dependency check
                                if ($tbl == $context_pattern_id) {
                                    if ($term_id || ('http://www.w3.org/2001/XMLSchema#integer' != $lang_dt)) {
                                        /* skip, if simple int, but no id */
                                        $this->addConstraintSQLEntry($context_pattern_id, 'T_'.$tbl.'.o_lang_dt = '.$term_id.' /* '.preg_replace('/[\#\*\>]/', '::', $lang_dt).' */');
                                    }
                                }
                            }
                            break;
                        }
                    }
                }
            }
        }

        return trim($r);
    }

    private function findSiblingVarExpression($id)
    {
        $pattern = $this->getPattern($id);
        do {
            $pattern = $this->getPattern($pattern['parent_id']);
        } while ($pattern['parent_id'] && ('expression' != $pattern['type']));

        $sub_patterns = $pattern['patterns'] ?? [];
        foreach ($sub_patterns as $sub_id) {
            $sub_pattern = $this->getPattern($sub_id);
            if ('var' == $sub_pattern['type']) {
                return $sub_pattern['value'];
            }
        }

        return '';
    }

    private function getFunctionExpressionSQL($pattern, $context, $val_type = '', $parent_type = '')
    {
        $fnc_uri = $pattern['uri'];
        $op = $pattern['operator'] ?? '';
        if ($op) {
            $op .= ' ';
        }

        /* simple type conversions */
        if (0 === strpos($fnc_uri, 'http://www.w3.org/2001/XMLSchema#')) {
            return $op.$this->getExpressionSQL($pattern['args'][0], $context, $val_type, $parent_type);
        }

        return '';
    }

    private function getBuiltInCallSQL($pattern, $context)
    {
        $call = $pattern['call'];
        $m = 'get'.ucfirst($call).'CallSQL';
        if (method_exists($this, $m)) {
            return $this->$m($pattern, $context);
        } else {
            throw new Exception('Unknown built-in call "'.$call.'"');
        }

        return '';
function fx() {
    try {
        doSomething();
        return true;
    }
    catch (\Exception $e) {
        return false;
    }

    return false;
}
    }

    private function getBoundCallSQL($pattern, $context)
    {
        $r = '';

        $var = $pattern['args'][0]['value'];
        $info = $this->getVarTableInfos($var);
        if (!$tbl = $info['table']) {
            return '';
        }
        $col = $info['col'];
        $tbl_alias = 'T_'.$tbl.'.'.$col;
        if ('!' == $pattern['operator']) {
            return $tbl_alias.' IS NULL';
        }

        return $tbl_alias.' IS NOT NULL';
    }

    private function getHasTypeCallSQL($pattern, $context, $type)
    {
        $r = '';

        $var = $pattern['args'][0]['value'];
        $info = $this->getVarTableInfos($var);
        if (!$tbl = $info['table']) {
            return '';
        }
        $col = $info['col'];
        $tbl_alias = 'T_'.$tbl.'.'.$col.'_type';

        $operator = $pattern['operator'] ?? '';

        return $tbl_alias.' '.$operator.'= '.$type;
    }

    private function getIsliteralCallSQL($pattern, $context)
    {
        return $this->getHasTypeCallSQL($pattern, $context, 2);
    }

    private function getIsblankCallSQL($pattern, $context)
    {
        return $this->getHasTypeCallSQL($pattern, $context, 1);
    }

    private function getIsiriCallSQL($pattern, $context)
    {
        return $this->getHasTypeCallSQL($pattern, $context, 0);
    }

    private function getIsuriCallSQL($pattern, $context)
    {
        return $this->getHasTypeCallSQL($pattern, $context, 0);
    }

    private function getStrCallSQL($pattern, $context)
    {
        $sub_pattern = $pattern['args'][0];
        $sub_type = $sub_pattern['type'];
        $m = 'get'.ucfirst($sub_type).'ExpressionSQL';
        if (method_exists($this, $m)) {
            return $this->$m($sub_pattern, $context);
        }
    }

    private function getFunctionCallSQL($pattern, $context)
    {
        $f_uri = $pattern['uri'];
        if (preg_match('/(integer|double|float|string)$/', $f_uri)) {/* skip conversions */
            $sub_pattern = $pattern['args'][0];
            $sub_type = $sub_pattern['type'];
            $m = 'get'.ucfirst($sub_type).'ExpressionSQL';
            if (method_exists($this, $m)) {
                return $this->$m($sub_pattern, $context);
            }
        }
    }

    private function getLangDatatypeCallSQL($pattern, $context)
    {
        $r = '';

        /* proceed with first argument only (assumed as base type for type promotion) */
        if (isset($pattern['patterns'])) {
            $sub_pattern = ['args' => [$pattern['patterns'][0]]];

            return $this->getLangDatatypeCallSQL($sub_pattern, $context);
        }
        if (!isset($pattern['args'])) {
            return 'FALSE';
        }
        $sub_type = $pattern['args'][0]['type'];
        if ('var' != $sub_type) {
            return $this->getLangDatatypeCallSQL($pattern['args'][0], $context);
        }
        $var = $pattern['args'][0]['value'];
        $info = $this->getVarTableInfos($var);
        if (!$tbl = $info['table']) {
            return '';
        }
        $col = 'o_lang_dt';
        $tbl_alias = 'V_'.$tbl.'_'.$col.'.val';
        if (!\in_array($tbl_alias, $this->index['sub_joins'])) {
            $this->index['sub_joins'][] = $tbl_alias;
        }
        $op = $pattern['operator'] ?? '';
        $r = trim($op.' '.$tbl_alias);

        return $r;
    }

    private function getDatatypeCallSQL($pattern, $context)
    {
        return '/* datatype call */ '.$this->getLangDatatypeCallSQL($pattern, $context);
    }

    private function getLangCallSQL($pattern, $context)
    {
        return '/* language call */ '.$this->getLangDatatypeCallSQL($pattern, $context);
    }

    private function getLangmatchesCallSQL($pattern, $context)
    {
        if (2 == \count($pattern['args'])) {
            $arg_1 = $pattern['args'][0];
            $arg_2 = $pattern['args'][1];
            $sub_r_1 = $this->getBuiltInCallSQL($arg_1, $context); /* adds value join */
            $sub_r_2 = $this->getExpressionSQL($arg_2, $context);
            $op = $pattern['operator'] ?? '';
            if (preg_match('/^([\"\'])([^\'\"]+)/', $sub_r_2, $m)) {
                if ('*' == $m[2]) {
                    $r = '!' == $op
                        ? 'NOT ('.$sub_r_1.' REGEXP "^[a-zA-Z\-]+$"'.')'
                        : $sub_r_1.' REGEXP "^[a-zA-Z\-]+$"';
                } else {
                    $r = ('!' == $op) ? $sub_r_1.' NOT LIKE '.$m[1].$m[2].'%'.$m[1] : $sub_r_1.' LIKE '.$m[1].$m[2].'%'.$m[1];
                }
            } else {
                $r = ('!' == $op) ? $sub_r_1.' NOT LIKE CONCAT('.$sub_r_2.', "%")' : $sub_r_1.' LIKE CONCAT('.$sub_r_2.', "%")';
            }

            return $r;
        }

        return '';
    }

    /**
     * @todo not in use, so remove?
     */
    private function getSametermCallSQL($pattern, $context)
    {
        if (2 == \count($pattern['args'])) {
            $arg_1 = $pattern['args'][0];
            $arg_2 = $pattern['args'][1];
            $sub_r_1 = $this->getExpressionSQL($arg_1, 'sameterm');
            $sub_r_2 = $this->getExpressionSQL($arg_2, 'sameterm');
            $op = $pattern['operator'] ?? '';
            $r = $sub_r_1.' '.$op.'= '.$sub_r_2;

            return $r;
        }

        return '';
    }

    private function getRegexCallSQL($pattern, $context)
    {
        $ac = \count($pattern['args']);
        if ($ac >= 2) {
            foreach ($pattern['args'] as $i => $arg) {
                $var = 'sub_r_'.($i + 1);
                $$var = $this->getExpressionSQL($arg, $context, '', 'regex');
            }
            $sub_r_3 = (isset($sub_r_3) && preg_match('/[\"\'](.+)[\"\']/', $sub_r_3, $m)) ? strtolower($m[1]) : '';

            $operator = $pattern['operator'] ?? '';
            $op = '!' == $operator ? ' NOT' : '';
            if (!$sub_r_1 || !$sub_r_2) {

                return '';
            }
            $is_simple_search = preg_match('/^[\(\"]+(\^)?([a-z0-9\_\-\s]+)(\$)?[\)\"]+$/is', $sub_r_2, $m);

            $is_simple_search = preg_match('/^[\(\"]+(\^)?([^\\\*\[\]\}\{\(\)\"\'\?\+\.]+)(\$)?[\)\"]+$/is', $sub_r_2, $m);
            $is_o_search = preg_match('/o\.val\)*$/', $sub_r_1);
            /* fulltext search (may have "|") */
            if ($is_simple_search && $is_o_search && !$op && (\strlen($m[2]) > 8)) {
                /* MATCH variations */
                if (($val_parts = preg_split('/\|/', $m[2]))) {
                    return 'MATCH('.trim($sub_r_1, '()').') AGAINST("'.implode(' ', $val_parts).'")';
                } else {
                    return 'MATCH('.trim($sub_r_1, '()').') AGAINST("'.$m[2].'")';
                }
            }
            if (preg_match('/\|/', $sub_r_2)) {
                $is_simple_search = 0;
            }
            /* LIKE */
            if ($is_simple_search && ('i' == $sub_r_3)) {
                $sub_r_2 = $m[1] ? $m[2] : '%'.$m[2];
                $sub_r_2 .= isset($m[3]) && $m[3] ? '' : '%';

                return $sub_r_1.$op.' LIKE "'.$sub_r_2.'"';
            }
            /* REGEXP */
            $opt = '';
            if (!$this->store->getDBObject() instanceof PDOSQLiteAdapter) {

                $opt = ('i' == $sub_r_3) ? '' : 'BINARY ';
            }

            return $sub_r_1.$op.' REGEXP '.$opt.$sub_r_2;
        }

        return '';
    }

    private function getGROUPSQL()
    {
        $r = '';
        $nl = "\n";
        $infos = $this->infos['query']['group_infos'] ?? [];
        foreach ($infos as $info) {
            $var = $info['value'];
            if ($tbl_infos = $this->getVarTableInfos($var, 0)) {
                $tbl_alias = $tbl_infos['table_alias'];
                $r .= $r ? ', ' : 'GROUP BY ';
                $r .= $tbl_alias;
            }
        }
        $hr = '';
        foreach ($this->index['havings'] as $having) {
            $hr .= $hr ? ' AND' : ' HAVING';
            $hr .= '('.$having.')';
        }
        $r .= $hr;

        return $r ? $nl.$r : $r;
    }

    private function getORDERSQL()
    {
        $r = '';
        $nl = "\n";
        $infos = $this->infos['query']['order_infos'] ?? [];
        foreach ($infos as $info) {
            $type = $info['type'];
            $ms = [
                'expression' => 'getExpressionSQL',
                'built_in_call' => 'getBuiltInCallSQL',
                'function_call' => 'getFunctionCallSQL',
            ];
            $m = isset($ms[$type]) ? $ms[$type] : 'get'.ucfirst($type).'ExpressionSQL';
            if (method_exists($this, $m)) {
                $sub_r = '('.$this->$m($info, 'order').')';
                $direction = $info['direction'] ?? '';
                $sub_r .= 'desc' == $direction ? ' DESC' : '';
                $r .= $r ? ','.$nl.$sub_r : $sub_r;
            }
        }

        return $r ? $nl.'ORDER BY '.$r : '';
    }

    private function getLIMITSQL()
    {
        $r = '';
        $nl = "\n";
        if (isset($this->infos['query']['limit'])) {
            $limit = $this->infos['query']['limit'];
            $offset = (int) ($this->infos['query']['offset'] ?? 0);
            $r = 'LIMIT '.$offset.','.$limit;
        } elseif (isset($this->infos['query']['offset'])) {
            $offset = (int) $this->infos['query']['offset'];
            $r = 'LIMIT '.$offset.',999999999999';
        }

        return $r ? $nl.$r : '';
    }

    private function getValueSQL($q_tbl, $q_sql)
    {
        $r = '';
        /* result vars */
        $vars = $this->infos['query']['result_vars'];
        $nl = "\n";
        $v_tbls = ['JOIN' => [], 'LEFT JOIN' => []];
        $vc = 1;
        foreach ($vars as $var) {
            $var_name = $var['var'];
            $r .= $r ? ','.$nl.'  ' : '  ';
            $col = '';
            $tbl = '';
            if ('*' != $var_name) {
                if (\in_array($var_name, $this->infos['null_vars'])) {
                    if (isset($this->initial_index['vars'][$var_name])) {
                        $col = $this->initial_index['vars'][$var_name][0]['col'];
                        $tbl = $this->initial_index['vars'][$var_name][0]['table'];
                    }
                    if (isset($this->initial_index['graph_vars'][$var_name])) {
                        $col = 'g';
                        $tbl = $this->initial_index['graph_vars'][$var_name][0]['table'];
                    }
                } elseif (isset($this->index['vars'][$var_name])) {
                    $col = $this->index['vars'][$var_name][0]['col'];
                    $tbl = $this->index['vars'][$var_name][0]['table'];
                }
            }
            if ($var['aggregate']) {
                $r .= 'TMP.`'.$var['alias'].'`';
            } else {
                /* val may be NULL */
                $join_type = \in_array($tbl, array_merge($this->index['from'], $this->index['join'])) ? 'JOIN' : 'LEFT JOIN';
                $v_tbls[$join_type][] = ['t_col' => $col, 'q_col' => $var_name, 'vc' => $vc];
                $r .= 'V'.$vc.'.val AS `'.$var_name.'`';
                if (\in_array($col, ['s', 'o'])) {
                    if (strpos($q_sql, '`'.$var_name.' type`')) {
                        $r .= ', '.$nl.'    TMP.`'.$var_name.' type` AS `'.$var_name.' type`';
                    } else {
                        $r .= ', '.$nl.'    NULL AS `'.$var_name.' type`';
                    }
                }
                ++$vc;
                if ('o' == $col) {
                    $v_tbls[$join_type][] = ['t_col' => 'id', 'q_col' => $var_name.' lang_dt', 'vc' => $vc];
                    if (strpos($q_sql, '`'.$var_name.' lang_dt`')) {
                        $r .= ', '.$nl.'    V'.$vc.'.val AS `'.$var_name.' lang_dt`';
                        ++$vc;
                    } else {
                        $r .= ', '.$nl.'    NULL AS `'.$var_name.' lang_dt`';
                    }
                }
            }
        }
        if (!$r) {
            $r = '*';
        }
        /* from */
        $r .= $nl.'FROM ('.$q_tbl.' TMP)';
        foreach (['JOIN', 'LEFT JOIN'] as $join_type) {
            foreach ($v_tbls[$join_type] as $v_tbl) {
                $tbl = $this->getValueTable($v_tbl['t_col']);
                $var_name = preg_replace('/^([^\s]+)(.*)$/', '\\1', $v_tbl['q_col']);
                $cur_join_type = \in_array($var_name, $this->infos['null_vars']) ? 'LEFT JOIN' : $join_type;
                if (!strpos($q_sql, '`'.$v_tbl['q_col'].'`')) {
                    continue;
                }
                $r .= $nl
                    .' '.$cur_join_type
                    .' '.$tbl.' V'.$v_tbl['vc']
                    .' ON ((V'.$v_tbl['vc'].'.id = TMP.`'.$v_tbl['q_col'].'`))';
            }
        }
        /* create pos columns, id needed */
        $orderInfos = $this->infos['query']['order_infos'] ?? [];
        if ($orderInfos) {
            $r .= $nl.' ORDER BY TMPPOS';
        }

        return 'SELECT'.$nl.$r;
    }
}

sweetrdf / in-memory-store-sqlite

SelectQueryHandler::getLeftJoins() A last analyzed 2024-11-26 16:47 UTC

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like

SelectQueryHandler::getLeftJoins() A
last analyzed 2024-11-26 16:47 UTC
