Query generator

• 10-10-2009 @ 18:45
• 0 reacties

Iedere KMT omgeving draait op een database. Daarin worden alle relaties, pagina's, weblogs, reacties e.d. opgeslagen. Om deze via een generieke manier op te kunnen halen maakt Kingsquare vanaf het begin al gebruik van een eenduidige manier van het opstellen van een query.

De functie buildQuery() is een functie die eigenlijk een array aan opties omzet naar een valide en schone (hier en daar geoptimaliseerde) query die vervolgens weer gebruikt kan worden in andere processen of zo rechtstreeks naar de database toe kan. Het is een stuk ORM wat ervoor zorgt dat er op zeer laag niveau al bepaalde beveiligingsrisico's kunnen worden ondervangen.

Het werkt als volgt:

/**
 * Build an SQL statement for BASE-extended objects
 *
 * @param string $objectType
 * @param rights-constant $rights
 * @param array $sqlArray
 * @param integer $start
 * @param integer $max
 * @param boolean $implementationZeroToo
 * @param boolean $ignoreDeleted
 * @param boolean $distinct
 * @param boolean $rightsOperatorOr
 * @return string SQL statement
 */
function buildQuery($objectType, $rights = RIGHTS_READ, $sqlArray = array(), $start=-1, $max=-1, $implementationZeroToo = false, $ignoreDeleted = false, $distinct = true, $rightsOperatorOr=false) {
    global $tableDefinitions, $noTableDefinitions, $db;

    $allDefinitions = array_merge($tableDefinitions, $noTableDefinitions);
    if (!isset($sqlArray['FROM']) || !in_array($allDefinitions[$objectType], $sqlArray['FROM']) && $objectType !='Base') {
        $from = $allDefinitions[$objectType];
        if (is_array($from)) {
            $sqlArray['WHERE'][] = $from[1];
            $from = reset($from);
        }
        $sqlArray['FROM'][] = $from;
        $sqlArray['WHERE'][] = $from.'.`id` = '.$tableDefinitions['Base'].'.`id`';
    }

    foreach($sqlArray['FROM'] as $key => $fromStr) {
        if (strpos($fromStr, ',') !== false) {
            unset($sqlArray['FROM'][$key]);
            $sqlArray['FROM'] = array_merge($sqlArray['FROM'], explode(',', $fromStr));
        }
    }

    if (!in_array($tableDefinitions['Base'], $sqlArray['FROM'])) $sqlArray['FROM'][] = $tableDefinitions['Base'];

    // [  hiervoor gebeuren er nog allerlei security zaken, extra rechten controles e.d.] //
    if (!empty($sqlArray['INDEX'])) {
        foreach($sqlArray['INDEX'] as $indexType => $indexTables) {
            foreach($indexTables as $indexTable => $indexColumns) {
                $indexTableIndexId = array_search($indexTable, $sqlArray['FROM']);
                $sqlArray['FROM'][$indexTableIndexId] .= ' '.$indexType.' INDEX ('.implode(', ', $indexColumns).')';
            }
        }
    }
    if (empty($sqlArray['SELECT'])) $sqlArray['SELECT'] = array($tableDefinitions['Base'].'.`id`');
    $sql = 'SELECT '.($distinct?'DISTINCT ':'').implode(', ', array_unique($sqlArray['SELECT'])).' ';
    if (isset($sqlArray['FROM']) && is_array($sqlArray['FROM'])) $sql .= 'FROM ('.implode(', ', array_unique($sqlArray['FROM'])).') ';
    if (isset($sqlArray['INNER JOIN']) && is_array($sqlArray['INNER JOIN'])) $sql.= 'INNER JOIN '.implode(' INNER JOIN ', array_unique($sqlArray['INNER JOIN'])).' ';
    if (isset($sqlArray['STRAIGHT JOIN']) && is_array($sqlArray['STRAIGHT JOIN'])) $sql.= 'STRAIGHT JOIN '.implode(' STRAIGHT JOIN ', array_unique($sqlArray['STRAIGHT JOIN'])).' ';
    if (isset($sqlArray['LEFT JOIN']) && is_array($sqlArray['LEFT JOIN'])) $sql.= 'LEFT JOIN '.implode(' LEFT JOIN ', array_unique($sqlArray['LEFT JOIN'])).' ';
    if (isset($sqlArray['WHERE']) && is_array($sqlArray['WHERE'])) $sql .= 'WHERE ('.implode(') AND (', array_unique($sqlArray['WHERE'])).') ';
    if (isset($sqlArray['GROUP BY']) && is_array($sqlArray['GROUP BY'])) $sql .= 'GROUP BY '.implode(', ', array_unique($sqlArray['GROUP BY'])).' ';
    if (isset($sqlArray['HAVING']) && is_array($sqlArray['HAVING'])) $sql .= 'HAVING ('.implode(') AND (', array_unique($sqlArray['HAVING'])).') ';
    if (isset($sqlArray['ORDER BY']) && is_array($sqlArray['ORDER BY'])) $sql .= 'ORDER BY '.implode(', ', array_unique($sqlArray['ORDER BY'])).' ';

    $limit = (($start!=-1)? $start : '').(($max!=-1)? ', '.$max : '');
    if (!empty($limit)) $sql .= 'LIMIT '.$limit;
    if ($debug) $debugLog->add('buildQuery('.$objectType.'): '.$sql);
    return $sql;
}

Wat er gebeurt is dat er een query wordt samengesteld a.d.v. een object type, de geldende rechten en een sqlArray. Dit zou er als volgt uit kunnen zien:

Situatie: geef de query die alle personen ophaalt die ik mag lezen:

$sqlArray = array();
$sql = buildQuery('Person', RIGHTS_READ, $sqlArray);

Situatie: geef de query die alle personen teruggeeft waarvan de achternaam met een A begint en de voornaam met een B én die al eens iets besteld hebben:

$sqlArray = array();
$sqlArray['WHERE'][] = 'person.firstName LIKE B%';
$sqlArray['WHERE'][] = 'person.lastName LIKE A%';
$sqlArray['INNER JOIN'][] = 'customer ON (customer.personId = person.id)';
$sql = buildQuery('Person', RIGHTS_READ, $sqlArray);

Uiteraard zijn er nog veel meer opties, maar de opzet van buildQuery is zodanig dat er dus vrij gemakklijk aan de hand van de sqlArray er altijd een nette (ook qua volgorde) sql query uitkomt die percies doet wat hij moet doen. Door het toevoegen van (ongeacht de volgorde) extra clauses of joins, order by's etc kan er toch in de code een duidelijke vraag worden geformuleerd zonder écht in de query logica of opmaak te moeten duiken. Tegelijk is door het opnemen van in dit geval het `object type` ook extra optimalisatie mogelijk. Zo is het eenvoudig om bijvoorbeeld bepaalde data automatisch aan elkaar te joinen of juist weg te laten indien een bepaald object opgehaald wordt.

Terug naar het weblog overzicht