Database's query functions should support querying for a row with NULL value
authorrniwa@webkit.org <rniwa@webkit.org@268f45cc-cd09-0410-ab3c-d52691b4dbfc>
Mon, 13 Mar 2017 21:44:32 +0000 (21:44 +0000)
committerrniwa@webkit.org <rniwa@webkit.org@268f45cc-cd09-0410-ab3c-d52691b4dbfc>
Mon, 13 Mar 2017 21:44:32 +0000 (21:44 +0000)
https://bugs.webkit.org/show_bug.cgi?id=169504

Reviewed by Antti Koivisto.

Add the support for calling select_* with one of column values set to NULL.
This feature is useful in webkit.org/b/146374 and webkit.org/b/168962.

* public/include/db.php:
(Database::prepare_params): Added $null_columns as an optional argument.
(Database::select_conditions_with_null_columns): Added. Builds up a query string by appending AND x is NULL
to match columns whose value must be NULL.
(Database::_select_update_or_insert_row):
(Database::select_rows):

git-svn-id: https://svn.webkit.org/repository/webkit/trunk@213870 268f45cc-cd09-0410-ab3c-d52691b4dbfc

Websites/perf.webkit.org/ChangeLog
Websites/perf.webkit.org/public/include/db.php

index 9ed84bf..553c307 100644 (file)
@@ -1,3 +1,20 @@
+2017-03-13  Ryosuke Niwa  <rniwa@webkit.org>
+
+        Database's query functions should support querying for a row with NULL value
+        https://bugs.webkit.org/show_bug.cgi?id=169504
+
+        Reviewed by Antti Koivisto.
+
+        Add the support for calling select_* with one of column values set to NULL.
+        This feature is useful in webkit.org/b/146374 and webkit.org/b/168962.
+
+        * public/include/db.php:
+        (Database::prepare_params): Added $null_columns as an optional argument.
+        (Database::select_conditions_with_null_columns): Added. Builds up a query string by appending AND x is NULL
+        to match columns whose value must be NULL.
+        (Database::_select_update_or_insert_row):
+        (Database::select_rows):
+
 2017-03-13  Dewei Zhu  <dewei_zhu@apple.com>
 
         Add the ability to report a commit with sub-commits.
index 2918db3..2ceb6de 100644 (file)
@@ -105,20 +105,38 @@ class Database
         return $prefix ? $prefix . '_' . $column : $column;
     }
 
-    private function prepare_params($params, &$placeholders, &$values) {
-        $column_names = array_keys($params);
+    private function prepare_params($params, &$placeholders, &$values, $null_columns = NULL) {
+        $column_names = array();
 
         $i = count($values) + 1;
-        foreach ($column_names as $name) {
+        foreach (array_keys($params) as $name) {
+            $current_value = $params[$name];
+            if ($current_value === NULL && $null_columns !== NULL) {
+                array_push($null_columns, $name);
+                continue;
+            }
             assert(ctype_alnum_underscore($name));
+            array_push($column_names, $name);
             array_push($placeholders, '$' . $i);
-            array_push($values, $params[$name]);
+            array_push($values, $current_value);
             $i++;
         }
 
         return $column_names;
     }
 
+    private function select_conditions_with_null_columns($prefix, $column_names, $placeholders, $null_columns) {
+        $column_names = $this->prefixed_column_names($column_names, $prefix);
+        $placeholders = join(', ', $placeholders);
+
+        if (!$column_names && !$placeholders)
+            $column_names = $placeholders = '1';
+        $query = "($column_names) = ($placeholders)";
+        foreach ($null_columns as $column_name)
+            $query .= ' AND ' . $this->prefixed_name($column_name, $prefix) . ' IS NULL';
+        return $query;
+    }
+
     function insert_row($table, $prefix, $params, $returning = 'id') {
         $placeholders = array();
         $values = array();
@@ -154,7 +172,8 @@ class Database
         $values = array();
 
         $select_placeholders = array();
-        $select_column_names = $this->prepare_params($select_params, $select_placeholders, $values);
+        $select_null_columns = array();
+        $select_column_names = $this->prepare_params($select_params, $select_placeholders, $values, $select_null_columns);
         $select_values = array_slice($values, 0);
 
         if ($insert_params === NULL)
@@ -165,9 +184,9 @@ class Database
         assert(!!$returning);
         assert(!$prefix || ctype_alnum_underscore($prefix));
         $returning_column_name = $returning == '*' ? '*' : $this->prefixed_name($returning, $prefix);
-        $select_column_names = $this->prefixed_column_names($select_column_names, $prefix);
-        $select_placeholders = join(', ', $select_placeholders);
-        $query = "SELECT $returning_column_name FROM $table WHERE ($select_column_names) = ($select_placeholders)";
+
+        $condition = $this->select_conditions_with_null_columns($prefix, $select_column_names, $select_placeholders, $select_null_columns);
+        $query = "SELECT $returning_column_name FROM $table WHERE $condition";
 
         $insert_column_names = $this->prefixed_column_names($insert_column_names, $prefix);
         $insert_placeholders = join(', ', $insert_placeholders);
@@ -176,7 +195,7 @@ class Database
         $rows = NULL;
         if ($should_update) {
             $rows = $this->query_and_fetch_all("UPDATE $table SET ($insert_column_names) = ($insert_placeholders)
-                WHERE ($select_column_names) = ($select_placeholders) RETURNING $returning_column_name", $values);
+                WHERE $condition RETURNING $returning_column_name", $values);
         }
         if (!$rows && $should_insert) {
             $rows = $this->query_and_fetch_all("INSERT INTO $table ($insert_column_names) SELECT $insert_placeholders
@@ -226,11 +245,12 @@ class Database
 
         $placeholders = array();
         $values = array();
-        $column_names = $this->prefixed_column_names($this->prepare_params($params, $placeholders, $values), $prefix);
-        $placeholders = join(', ', $placeholders);
-        if (!$column_names && !$placeholders)
-            $column_names = $placeholders = '1';
-        $query = "SELECT * FROM $table WHERE ($column_names) = ($placeholders)";
+        $null_columns = array();
+        $column_names = $this->prepare_params($params, $placeholders, $values, $null_columns);
+        $condition = $this->select_conditions_with_null_columns($prefix, $column_names, $placeholders, $null_columns);
+
+        $query = "SELECT * FROM $table WHERE $condition";
+
         if ($order_by) {
             if (!is_array($order_by))
                 $order_by = array($order_by);