aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authormistic100 <mistic@piwigo.org>2011-06-22 15:56:19 +0000
committermistic100 <mistic@piwigo.org>2011-06-22 15:56:19 +0000
commit029688227b8c004ce660e06c61330c125f8b1c21 (patch)
tree49af9a93b25c0eb8348d139d44672b628597bbc6
parent8d22af01b9d78f44cc9293466ac98b042239aa13 (diff)
feature:2359 add single_update and single_insert functions
git-svn-id: http://piwigo.org/svn/trunk@11485 68402e56-0260-453c-a942-63ccdbb3a9ee
-rw-r--r--include/dblayer/functions_mysql.inc.php103
-rw-r--r--include/dblayer/functions_pdo-sqlite.inc.php127
-rw-r--r--include/dblayer/functions_pgsql.inc.php100
-rw-r--r--include/dblayer/functions_sqlite.inc.php127
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