mysql specific qquery

[koopa]

Just noticed that apparently QQ::Regexp has been removed in the beta-3.0 branch.
What was the reason for that step and what is the recommended workaround if I used a few (mysql) queries using QQ::Regexp in a 2.x project?

[spekary]

Can you provide a pointer to it? I don't see it in the 2.0 code when I search for it.

[matthiaz]

Strange, I can't seem to find it in the 2.2.3 version. But in one of our projects I do have this piece of code. Which we actually use in production. I have no idea how we got this though...
@koopa , is this similar to what you have?

class QQConditionRegexp extends QQConditionComparison {
    public function __construct(QQNode $objQueryNode, $strValue) {
        $this->objQueryNode = $objQueryNode;
        if (!$objQueryNode->_ParentNode)
            throw new QInvalidCastException('Unable to cast "' . $objQueryNode->_Name . '" table to Column-based QQNode', 3);

        if ($strValue instanceof QQNamedValue)
            $this->mixOperand = $strValue;
        else {
            try {
                $strValue = str_replace(
                    array('\\d',    '\\s',      '\\w',      '\\D',  '\\S',      '\\W',      '\'',           '"'), 
                    array('[0-9]',  '[[:space:]]',    '[A-Za-z0-9]',  '[^0-9]', '[^[:space:]]', '[^A-Za-z0-9]', '[.apostrophe.]',   '[.quotation-mark.]'),
                    $strValue
                );

                $strValue = preg_replace('/^\/|\/$/', '', $strValue); // no start/end delimiter in MySQL regex

                $this->mixOperand = QType::Cast($strValue, QType::String);
            } catch (QCallerException $objExc) {
                $objExc->IncrementOffset();
                $objExc->IncrementOffset();
                throw $objExc;
            }
        }
    }
    public function UpdateQueryBuilder(QQueryBuilder $objBuilder) {
        if ($this->mixOperand instanceof QQNamedValue)
            $objBuilder->AddWhereItem($this->objQueryNode->GetColumnAlias($objBuilder) . ' REGEXP ' . $this->mixOperand->Parameter());
        else
            $objBuilder->AddWhereItem($this->objQueryNode->GetColumnAlias($objBuilder) . ' REGEXP ' . $objBuilder->Database->SqlVariable($this->mixOperand));
    }
}

class QQConditionNotRegexp extends QQConditionComparison {
    public function __construct(QQNode $objQueryNode, $strValue) {
        $this->objQueryNode = $objQueryNode;
        if (!$objQueryNode->_ParentNode)
            throw new QInvalidCastException('Unable to cast "' . $objQueryNode->_Name . '" table to Column-based QQNode', 3);

        if ($strValue instanceof QQNamedValue)
            $this->mixOperand = $strValue;
        else {
            try {
                $strValue = str_replace(
                    array('\\d',    '\\s',      '\\w',      '\\D',  '\\S',      '\\W',      '\'',           '"'), 
                    array('[0-9]',  '[[:space:]]',  '[A-Za-z0-9]',  '[^0-9]',   '[^[:space:]]', '[^A-Za-z0-9]', '[.apostrophe.]',   '[.quotation-mark.]'),
                    $strValue
                );

                $strValue = preg_replace('/^\/|\/$/', '', $strValue); // no start/end delimiter in MySQL regex

                $this->mixOperand = QType::Cast($strValue, QType::String);
            } catch (QCallerException $objExc) {
                $objExc->IncrementOffset();
                $objExc->IncrementOffset();
                throw $objExc;
            }
        }
    }
    public function UpdateQueryBuilder(QQueryBuilder $objBuilder) {
        if ($this->mixOperand instanceof QQNamedValue)
            $objBuilder->AddWhereItem($this->objQueryNode->GetColumnAlias($objBuilder) . ' NOT REGEXP ' . $this->mixOperand->Parameter());
        else
            $objBuilder->AddWhereItem($this->objQueryNode->GetColumnAlias($objBuilder) . ' NOT REGEXP ' . $objBuilder->Database->SqlVariable($this->mixOperand));
    }
}

[spekary]

This code is Mysql specific, and so either would not be in the core because of that, or was removed because of that. Perhaps it was a plugin? In any case, to be in core, it would need to be generalized a bit. Mysql, Oracle and Postgres all use different syntax to do regular expression matching, so this needs to be pushed down into the database adapters somehow.

[matthiaz]

I looked a bit further and it looks like a colleage of mine added this part. So it might be inspired by a patch on the old qcubed trac website.
We work with mysql exclusively so that would makes sense.

[koopa]

Ah darn, that was indeed added by myself it seems. It's been a few years since I did that and my mistake was to not compare with the vanilla classes - apologies for the confusion! Lesson learned.
Not sure about the other SQL dialects, but the MySQL implementation we use is a pretty straight forward copy of the LIKE comparison condition, just with a "REGEXP" in place of the "LIKE"

[olegabr]

It can be done as a plugin to the QQuery package when we factor it out from qcubed to something like qcubed-qquery. Then there would be a place for qcubed-qquery-mysql package for mysql specific features.

[spekary]

Agreed. However, I think there is likely a database agnostic way to do REGEXP using database adapter calls. But, in general I agree that there is a place for a plugin, or even core extensions to the various databases. For example, QQ_MYSQL::InSet($objNode, $strTest), or QQ_PG::Jsonp type.