Rewrite statements with WHERE clause

[mapcentia]

Is it possible to rewrite statements with a WHERE clause. So given this:

"SELECT * FROM inventory ORDER BY date LIMIT 10"

Should become:

"SELECT * FROM inventory WHERE userid='Jack' ORDER BY date LIMIT 10 "

And do this for arbitrary statements: SELECT, DELETE, UPDATE, UNION, sub-queries?

The use-case is an application which let users run arbitrary statements over HTTP and we want to add a rule based system, so users only can select and change data according to rules. A rule could be:

User: Jack; Relation: inventory; Operation: SELECT; rule: userid='jack'

[sad-spirit]

Assuming I correctly understand your question, you need to create a Select object from the initial query:

$select = $factory->createFromString("SELECT * FROM inventory ORDER BY date LIMIT 10");

and then add the criterion to its $where property (which is an instance of WhereOrHavingClause)

$select->where->and("userid='Jack'");

This will work for any type of statement having a WHERE clause.

Only I'd recommend against embedding constants in criteria, use parameters instead

$select->where->and("userid = :userid");

[mapcentia]

Yes, that works. But now I have a sub-query like this:

SELECT *, (select foo from bar limit 1) as bib FROM bup;

And I get this:

select *, (
        select foo
        from bar
        limit 1
    ) as bib
from bup
where userid = 'Jack'

I would like to add one WHERE clause to the bup SELECT and another to the sub-query on bib. Can it be done?

[sad-spirit]

If $select is the outer select statement, then

$select->list[1]->expression->query->where

is the WHERE clause of the subselect in its target list.

A bit of explanation: $select->list[1] is the second offset in target list of the outer select statement, it contains an instance of nodes\TargetElement. The latter has $expression (containing a subselect) and $alias (containing 'bib') properties. As subselect is used in scalar context, it is represented by an instance of nodes\expressions\SubselectExpression with actual Select instance as its $query property.

[mapcentia]

Thanks for the input. I figured out how to create a walker class, which will (I think) set WHERE clause on all statements. Thanks for the great work!

class TableWalker extends BlankWalker
{
    public function walkSelectStatement(Select $statement)
    {
        foreach ($statement->from->getIterator() as $from) {
            // Check rule for $from->name->relation->value and set WHERE
            $statement->where->and("userid='SELECT'");
        }
        parent::walkSelectStatement($statement);
    }

    public function walkUpdateStatement(Update $statement)
    {
        // Check rule for $statement->relation->relation->relation->value and set WHERE
        $statement->where->and("userid='UPDATE'");
        parent::walkUpdateStatement($statement);
    }
    public function walkDeleteStatement(Delete $statement): void
    {
        foreach ($statement->using->getIterator() as $using) {
            // Check rule for $using->name->relation->value and set WHERE
            $statement->where->and("userid='USING'");
        }
        // Check rule for $statement->relation->relation->relation->value and set WHERE
        $statement->where->and("userid='DELETE'");
        parent::walkDeleteStatement($statement);
    }
    public function walkInsertStatement(Insert $statement)
    {
       // Check rule for $statement->relation->relation->relation->value and set WHERE
        if (!empty($statement->onConflict)) $statement->onConflict->where->and("userid='INSERT'");
        parent::walkInsertStatement($statement);
    }
}