feature:2359 add single_update and single_insert functions

git-svn-id: http://piwigo.org/svn/trunk@11485 68402e56-0260-453c-a942-63ccdbb3a9ee

4 files changed, 423 insertions, 34 deletions

diff --git a/include/dblayer/functions_mysql.inc.php b/include/dblayer/functions_mysql.inc.php
index 7a163af7c..de63c0cac 100644
--- a/include/dblayer/functions_mysql.inc.php
+++ b/include/dblayer/functions_mysql.inc.php
@@ -219,6 +219,7 @@ function mass_updates($tablename, $dbfields, $datas, $flags=0)
 {
   if (count($datas) == 0)
     return;
+  
   // depending on the MySQL version, we use the multi table update or N update queries
   if (count($datas) < 10)
   {
@@ -232,13 +233,13 @@ UPDATE '.$tablename.'
       {
         $separator = $is_first ? '' : ",\n    ";
 
-        if (isset($data[$key]) and $data[$key] !== '')
+        if (isset($data[$key]) and $data[$key] != '')
         {
           $query.= $separator.$key.' = \''.$data[$key].'\'';
         }
         else
         {
-          if ($flags & MASS_UPDATES_SKIP_EMPTY )
+          if ( $flags & MASS_UPDATES_SKIP_EMPTY )
             continue; // next field
           $query.= "$separator$key = NULL";
         }
@@ -345,6 +346,65 @@ DROP TABLE '.$temporary_tablename;
   }
 }
 
+/**
+ * updates one line in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array data
+ * @param int flags - if MASS_UPDATES_SKIP_EMPTY - empty values do not overwrite existing ones
+ * @return void
+ */
+function single_update($tablename, $dbfields, $data, $flags=0)
+{
+  if (count($data) == 0)
+    return;
+
+  $query = '
+UPDATE '.$tablename.'
+  SET ';
+  $is_first = true;
+  foreach ($dbfields['update'] as $key)
+  {
+    $separator = $is_first ? '' : ",\n    ";
+
+    if (isset($data[$key]) and $data[$key] != '')
+    {
+      $query.= $separator.$key.' = \''.$data[$key].'\'';
+    }
+    else
+    {
+      if ( $flags & MASS_UPDATES_SKIP_EMPTY )
+        continue; // next field
+      $query.= "$separator$key = NULL";
+    }
+    $is_first = false;
+  }
+  if (!$is_first)
+  {// only if one field at least updated
+    $query.= '
+  WHERE ';
+    $is_first = true;
+    foreach ($dbfields['primary'] as $key)
+    {
+      if (!$is_first)
+      {
+        $query.= ' AND ';
+      }
+      if ( isset($data[$key]) )
+      {
+        $query.= $key.' = \''.$data[$key].'\'';
+      }
+      else
+      {
+        $query.= $key.' IS NULL';
+      }
+      $is_first = false;
+    }
+    pwg_query($query);
+  }
+}
+
 
 /**
  * inserts multiple lines in a table
@@ -411,6 +471,45 @@ INSERT INTO '.$table_name.'
 }
 
 /**
+ * inserts on line in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array insert
+ * @return void
+ */
+function single_insert($table_name, $dbfields, $insert)
+{
+  if (count($insert) != 0)
+  {
+    $query = '
+INSERT INTO '.$table_name.'
+  ('.implode(',', $dbfields).')
+  VALUES';
+
+    $query .= '(';
+    foreach ($dbfields as $field_id => $dbfield)
+    {
+      if ($field_id > 0)
+      {
+        $query .= ',';
+      }
+      if (!isset($insert[$dbfield]) or $insert[$dbfield] === '')
+      {
+        $query .= 'NULL';
+      }
+      else
+      {
+        $query .= "'".$insert[$dbfield]."'";
+      }
+    }
+    $query .= ')';
+    
+    pwg_query($query);
+  }
+}
+
+/**
  * Do maintenance on all PWG tables
  *
  * @return none
diff --git a/include/dblayer/functions_pdo-sqlite.inc.php b/include/dblayer/functions_pdo-sqlite.inc.php
index 3346da7ad..22751d04a 100644
--- a/include/dblayer/functions_pdo-sqlite.inc.php
+++ b/include/dblayer/functions_pdo-sqlite.inc.php
@@ -278,13 +278,13 @@ UPDATE '.$tablename.'
       
       if (isset($data[$key]) and $data[$key] != '')
       {
-	$query.= $separator.$key.' = \''.$data[$key].'\'';
+        $query.= $separator.$key.' = \''.$data[$key].'\'';
       }
       else
       {
-	if ($flags & MASS_UPDATES_SKIP_EMPTY )
-	  continue; // next field
-	$query.= "$separator$key = NULL";
+        if ( $flags & MASS_UPDATES_SKIP_EMPTY )
+          continue; // next field
+        $query.= "$separator$key = NULL";
       }
       $is_first = false;
     }
@@ -295,25 +295,84 @@ UPDATE '.$tablename.'
       $is_first = true;
       foreach ($dbfields['primary'] as $key)
       {
-	if (!$is_first)
+        if (!$is_first)
         {
-	  $query.= ' AND ';
-	}
-	if ( isset($data[$key]) )
+          $query.= ' AND ';
+        }
+        if ( isset($data[$key]) )
         {
-	  $query.= $key.' = \''.$data[$key].'\'';
-	}
-	else
+          $query.= $key.' = \''.$data[$key].'\'';
+        }
+        else
         {
-	  $query.= $key.' IS NULL';
-	}
-	$is_first = false;
+          $query.= $key.' IS NULL';
+        }
+        $is_first = false;
       }
       pwg_query($query);
     }
   }
 }
 
+/**
+ * updates on line in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array data
+ * @param int flags - if MASS_UPDATES_SKIP_EMPTY - empty values do not overwrite existing ones
+ * @return void
+ */
+function single_update($tablename, $dbfields, $data, $flags=0)
+{
+  if (count($data) == 0)
+    return;
+
+  $query = '
+UPDATE '.$tablename.'
+  SET ';
+  $is_first = true;
+  foreach ($dbfields['update'] as $key)
+  {
+    $separator = $is_first ? '' : ",\n    ";
+    
+    if (isset($data[$key]) and $data[$key] != '')
+    {
+      $query.= $separator.$key.' = \''.$data[$key].'\'';
+    }
+    else
+    {
+      if ( $flags & MASS_UPDATES_SKIP_EMPTY )
+        continue; // next field
+      $query.= "$separator$key = NULL";
+    }
+    $is_first = false;
+  }
+  if (!$is_first)
+  {// only if one field at least updated
+    $query.= '
+  WHERE ';
+    $is_first = true;
+    foreach ($dbfields['primary'] as $key)
+    {
+      if (!$is_first)
+      {
+        $query.= ' AND ';
+      }
+      if ( isset($data[$key]) )
+      {
+        $query.= $key.' = \''.$data[$key].'\'';
+      }
+      else
+      {
+        $query.= $key.' IS NULL';
+      }
+      $is_first = false;
+    }
+    pwg_query($query);
+  }
+}
+
 
 /**
  * inserts multiple lines in a table
@@ -323,7 +382,6 @@ UPDATE '.$tablename.'
  * @param array inserts
  * @return void
  */
-
 function mass_inserts($table_name, $dbfields, $datas)
 {
   if (count($datas) != 0)
@@ -380,6 +438,45 @@ INSERT INTO '.$table_name.'
 }
 
 /**
+ * inserts one line in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array insert
+ * @return void
+ */
+function single_insert($table_name, $dbfields, $insert)
+{
+  if (count($insert) != 0)
+  {
+    $query = '
+INSERT INTO '.$table_name.'
+  ('.implode(',', $dbfields).')
+  VALUES';
+
+    $query .= '(';
+    foreach ($dbfields as $field_id => $dbfield)
+    {
+      if ($field_id > 0)
+      {
+        $query .= ',';
+      }
+      if (!isset($insert[$dbfield]) or $insert[$dbfield] === '')
+      {
+        $query .= 'NULL';
+      }
+      else
+      {
+        $query .= "'".$insert[$dbfield]."'";
+      }
+    }
+    $query .= ')';
+    
+    pwg_query($query);
+  }
+}
+
+/**
  * Do maintenance on all PWG tables
  *
  * @return none
diff --git a/include/dblayer/functions_pgsql.inc.php b/include/dblayer/functions_pgsql.inc.php
index 67c42eeb1..c8a8dedbd 100644
--- a/include/dblayer/functions_pgsql.inc.php
+++ b/include/dblayer/functions_pgsql.inc.php
@@ -355,7 +355,7 @@ UPDATE '.$tablename.'
         }
         else
         {
-          if ($flags & MASS_UPDATES_SKIP_EMPTY )
+          if ( $flags & MASS_UPDATES_SKIP_EMPTY )
             continue; // next field
           $query.= "$separator$key = NULL";
         }
@@ -425,6 +425,64 @@ DROP TABLE '.$temporary_tablename;
   }
 }
 
+/**
+ * updates on line in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array data
+ * @param int flags - if MASS_UPDATES_SKIP_EMPTY - empty values do not overwrite existing ones
+ * @return void
+ */
+function single_update($tablename, $dbfields, $data, $flags=0)
+{
+  if (count($data) == 0)
+    return;
+
+  $query = '
+UPDATE '.$tablename.'
+  SET ';
+  $is_first = true;
+  foreach ($dbfields['update'] as $key)
+  {
+    $separator = $is_first ? '' : ",\n    ";
+
+    if (isset($data[$key]) and $data[$key] != '')
+    {
+      $query.= $separator.$key.' = \''.$data[$key].'\'';
+    }
+    else
+    {
+      if ( $flags & MASS_UPDATES_SKIP_EMPTY )
+        continue; // next field
+      $query.= "$separator$key = NULL";
+    }
+    $is_first = false;
+  }
+  if (!$is_first)
+  {// only if one field at least updated
+    $query.= '
+  WHERE ';
+    $is_first = true;
+    foreach ($dbfields['primary'] as $key)
+    {
+      if (!$is_first)
+      {
+        $query.= ' AND ';
+      }
+      if ( isset($data[$key]) )
+      {
+        $query.= $key.' = \''.$data[$key].'\'';
+      }
+      else
+      {
+        $query.= $key.' IS NULL';
+      }
+      $is_first = false;
+    }
+    pwg_query($query);
+  } 
+}
 
 /**
  * inserts multiple lines in a table
@@ -434,7 +492,6 @@ DROP TABLE '.$temporary_tablename;
  * @param array inserts
  * @return void
  */
-
 function mass_inserts($table_name, $dbfields, $datas)
 {
   if (count($datas) != 0)
@@ -491,6 +548,45 @@ INSERT INTO '.$table_name.'
 }
 
 /**
+ * inserts on line in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array insert
+ * @return void
+ */
+function single_insert($table_name, $dbfields, $insert)
+{
+  if (count($insert) != 0)
+  {
+    $query = '
+INSERT INTO '.$table_name.'
+  ('.implode(',', $dbfields).')
+  VALUES';
+
+    $query .= '(';
+    foreach ($dbfields as $field_id => $dbfield)
+    {
+      if ($field_id > 0)
+      {
+        $query .= ',';
+      }
+      if (!isset($insert[$dbfield]) or $insert[$dbfield] === '')
+      {
+        $query .= 'NULL';
+      }
+      else
+      {
+        $query .= "'".$insert[$dbfield]."'";
+      }
+    }
+    $query .= ')';
+    
+    pwg_query($query);
+  }
+}
+
+/**
  * Do maintenance on all PWG tables
  *
  * @return none
diff --git a/include/dblayer/functions_sqlite.inc.php b/include/dblayer/functions_sqlite.inc.php
index ac86db0f9..b3b9a2c1d 100644
--- a/include/dblayer/functions_sqlite.inc.php
+++ b/include/dblayer/functions_sqlite.inc.php
@@ -290,13 +290,13 @@ UPDATE '.$tablename.'
       
       if (isset($data[$key]) and $data[$key] != '')
       {
-	$query.= $separator.$key.' = \''.$data[$key].'\'';
+        $query.= $separator.$key.' = \''.$data[$key].'\'';
       }
       else
       {
-	if ($flags & MASS_UPDATES_SKIP_EMPTY )
-	  continue; // next field
-	$query.= "$separator$key = NULL";
+        if ( $flags & MASS_UPDATES_SKIP_EMPTY )
+          continue; // next field
+        $query.= "$separator$key = NULL";
       }
       $is_first = false;
     }
@@ -307,25 +307,84 @@ UPDATE '.$tablename.'
       $is_first = true;
       foreach ($dbfields['primary'] as $key)
       {
-	if (!$is_first)
+        if (!$is_first)
         {
-	  $query.= ' AND ';
-	}
-	if ( isset($data[$key]) )
+          $query.= ' AND ';
+        }
+        if ( isset($data[$key]) )
         {
-	  $query.= $key.' = \''.$data[$key].'\'';
-	}
-	else
+          $query.= $key.' = \''.$data[$key].'\'';
+        }
+        else
         {
-	  $query.= $key.' IS NULL';
-	}
-	$is_first = false;
+          $query.= $key.' IS NULL';
+        }
+        $is_first = false;
       }
       pwg_query($query);
     }
   }
 }
 
+/**
+ * updates on line in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array data
+ * @param int flags - if MASS_UPDATES_SKIP_EMPTY - empty values do not overwrite existing ones
+ * @return void
+ */
+function single_update($tablename, $dbfields, $data, $flags=0)
+{
+  if (count($data) == 0)
+    return;
+
+  $query = '
+UPDATE '.$tablename.'
+  SET ';
+  $is_first = true;
+  foreach ($dbfields['update'] as $key)
+  {
+    $separator = $is_first ? '' : ",\n    ";
+    
+    if (isset($data[$key]) and $data[$key] != '')
+    {
+      $query.= $separator.$key.' = \''.$data[$key].'\'';
+    }
+    else
+    {
+      if ( $flags & MASS_UPDATES_SKIP_EMPTY )
+        continue; // next field
+      $query.= "$separator$key = NULL";
+    }
+    $is_first = false;
+  }
+  if (!$is_first)
+  {// only if one field at least updated
+    $query.= '
+  WHERE ';
+    $is_first = true;
+    foreach ($dbfields['primary'] as $key)
+    {
+      if (!$is_first)
+      {
+        $query.= ' AND ';
+      }
+      if ( isset($data[$key]) )
+      {
+        $query.= $key.' = \''.$data[$key].'\'';
+      }
+      else
+      {
+        $query.= $key.' IS NULL';
+      }
+      $is_first = false;
+    }
+    pwg_query($query);
+  }
+}
+
 
 /**
  * inserts multiple lines in a table
@@ -335,7 +394,6 @@ UPDATE '.$tablename.'
  * @param array inserts
  * @return void
  */
-
 function mass_inserts($table_name, $dbfields, $datas)
 {
   if (count($datas) != 0)
@@ -392,6 +450,45 @@ INSERT INTO '.$table_name.'
 }
 
 /**
+ * inserts one line in a table
+ *
+ * @param string table_name
+ * @param array dbfields
+ * @param array insert
+ * @return void
+ */
+function single_insert($table_name, $dbfields, $insert)
+{
+  if (count($insert) != 0)
+  {
+    $query = '
+INSERT INTO '.$table_name.'
+  ('.implode(',', $dbfields).')
+  VALUES';
+
+    $query .= '(';
+    foreach ($dbfields as $field_id => $dbfield)
+    {
+      if ($field_id > 0)
+      {
+        $query .= ',';
+      }
+      if (!isset($insert[$dbfield]) or $insert[$dbfield] === '')
+      {
+        $query .= 'NULL';
+      }
+      else
+      {
+        $query .= "'".$insert[$dbfield]."'";
+      }
+    }
+    $query .= ')';
+    
+    pwg_query($query);
+  }
+}
+
+/**
  * Do maintenance on all PWG tables
  *
  * @return none