From 029688227b8c004ce660e06c61330c125f8b1c21 Mon Sep 17 00:00:00 2001 From: mistic100 Date: Wed, 22 Jun 2011 15:56:19 +0000 Subject: feature:2359 add single_update and single_insert functions git-svn-id: http://piwigo.org/svn/trunk@11485 68402e56-0260-453c-a942-63ccdbb3a9ee --- include/dblayer/functions_mysql.inc.php | 103 +++++++++++++++++++++- include/dblayer/functions_pdo-sqlite.inc.php | 127 +++++++++++++++++++++++---- include/dblayer/functions_pgsql.inc.php | 100 ++++++++++++++++++++- include/dblayer/functions_sqlite.inc.php | 127 +++++++++++++++++++++++---- 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 @@ -410,6 +470,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 * 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) @@ -379,6 +437,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 * 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) @@ -490,6 +547,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 * 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) @@ -391,6 +449,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 * -- cgit v1.2.3