aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authornikrou <nikrou@piwigo.org>2009-11-25 19:02:57 +0000
committernikrou <nikrou@piwigo.org>2009-11-25 19:02:57 +0000
commit13ea9d50e35d9dd8cf7235a39d97a344e6091ea0 (patch)
treea3c6e08df8ea10d0d5f56ffc2f1f14d6cbcfcd67
parentd30639ec98f8e2929137ab6e00cb2fe3ba295957 (diff)
Feature 1255: modification in sql queries
- manage random function - manage regex syntax - manage quote (single instead of double) - manage interval git-svn-id: http://piwigo.org/svn/trunk@4367 68402e56-0260-453c-a942-63ccdbb3a9ee
-rw-r--r--admin/cat_modify.php2
-rw-r--r--admin/element_set.php2
-rw-r--r--admin/include/functions_metadata.php2
-rw-r--r--admin/menubar.php10
-rw-r--r--admin/site_update.php12
-rw-r--r--comments.php8
-rw-r--r--feed.php2
-rw-r--r--include/calendar_base.class.php2
-rw-r--r--include/calendar_monthly.class.php2
-rw-r--r--include/category_cats.inc.php8
-rw-r--r--include/dblayer/functions_mysql.inc.php95
-rw-r--r--include/filter.inc.php3
-rw-r--r--include/functions.inc.php69
-rw-r--r--include/functions_category.inc.php2
-rw-r--r--include/functions_notification.inc.php2
-rw-r--r--include/functions_plugins.inc.php2
-rw-r--r--include/functions_user.inc.php8
-rw-r--r--include/section_init.inc.php3
-rw-r--r--include/ws_functions.inc.php10
-rw-r--r--random.php2
20 files changed, 134 insertions, 112 deletions
diff --git a/admin/cat_modify.php b/admin/cat_modify.php
index 06a001253..5dc39954c 100644
--- a/admin/cat_modify.php
+++ b/admin/cat_modify.php
@@ -67,7 +67,7 @@ if (isset($_POST['submit']))
array(
'id' => $_GET['cat_id'],
'name' => @$_POST['name'],
- 'commentable' => $_POST['commentable'],
+ 'commentable' => isset($_POST['commentable'])?$_POST['commentable']:'false',
'uploadable' =>
isset($_POST['uploadable']) ? $_POST['uploadable'] : 'false',
'comment' =>
diff --git a/admin/element_set.php b/admin/element_set.php
index 1c84854d5..ce9ce63da 100644
--- a/admin/element_set.php
+++ b/admin/element_set.php
@@ -196,7 +196,7 @@ elseif ('recent'== $_GET['cat'])
{
$query = 'SELECT id
FROM '.IMAGES_TABLE.'
- WHERE date_available BETWEEN DATE_SUB("'.$row['date'].'", INTERVAL 1 DAY) AND "'.$row['date'].'"';
+ WHERE date_available BETWEEN '.pwg_db_get_recent_period_expression(1, $row['date']).' AND \''.$row['date'].'\'';
$page['cat_elements_id'] = array_from_query($query, 'id');
}
}
diff --git a/admin/include/functions_metadata.php b/admin/include/functions_metadata.php
index f1e9f2549..a4d5588d4 100644
--- a/admin/include/functions_metadata.php
+++ b/admin/include/functions_metadata.php
@@ -246,7 +246,7 @@ SELECT id
if ($recursive)
{
$query.= '
- AND uppercats REGEXP \'(^|,)'.$category_id.'(,|$)\'
+ AND uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$category_id.'(,|$)\'
';
}
else
diff --git a/admin/menubar.php b/admin/menubar.php
index f6ebeab98..a0bb8ee03 100644
--- a/admin/menubar.php
+++ b/admin/menubar.php
@@ -69,8 +69,8 @@ if ( isset($_POST['reset']) and !is_adviser())
$mb_conf = array();
$query = '
UPDATE '.CONFIG_TABLE.'
- SET value=""
- WHERE param="blk_'.addslashes($menu->get_id()).'"
+ SET value=\'\'
+ WHERE param=\'blk_'.addslashes($menu->get_id()).'\'
LIMIT 1';
pwg_query($query);
}
@@ -143,9 +143,9 @@ if ( isset($_POST['submit']) and !is_adviser() )
*/
$query = '
UPDATE '.CONFIG_TABLE.'
- SET value="'.addslashes(serialize($mb_conf_db)).'"
- WHERE param="blk_'.addslashes($menu->get_id()).'"
- LIMIT 1';
+ SET value=\''.addslashes(serialize($mb_conf_db)).'\'
+ WHERE param=\'blk_'.addslashes($menu->get_id()).'\'
+ ';
pwg_query($query);
}
diff --git a/admin/site_update.php b/admin/site_update.php
index 84c6a8f87..b4980595d 100644
--- a/admin/site_update.php
+++ b/admin/site_update.php
@@ -148,7 +148,7 @@ SELECT id, uppercats, global_rank, status, visible
if (isset($_POST['subcats-included']) and $_POST['subcats-included'] == 1)
{
$query.= '
- AND uppercats REGEXP \'(^|,)'.$_POST['cat'].'(,|$)\'
+ AND uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$_POST['cat'].'(,|$)\'
';
}
else
@@ -207,10 +207,7 @@ SELECT id_uppercat, MAX(rank)+1 AS next_rank
}
// next category id available
- $query = '
-SELECT IF(MAX(id)+1 IS NULL, 1, MAX(id)+1) AS next_id
- FROM '.CATEGORIES_TABLE;
- list($next_id) = pwg_db_fetch_row(pwg_query($query));
+ $next_id = pwg_db_nextval('id', CATEGORIES_TABLE);
// retrieve sub-directories fulldirs from the site reader
$fs_fulldirs = $site_reader->get_full_directories($basedir);
@@ -399,10 +396,7 @@ SELECT file,storage_category_id
}
// next element id available
- $query = '
-SELECT IF(MAX(id)+1 IS NULL, 1, MAX(id)+1) AS next_element_id
- FROM '.IMAGES_TABLE;
- list($next_element_id) = pwg_db_fetch_row(pwg_query($query));
+ $next_element_id = pwg_db_nextval('id', IMAGES_TABLE);
$start = get_moment();
diff --git a/comments.php b/comments.php
index 958ff63f3..3d8c9cccf 100644
--- a/comments.php
+++ b/comments.php
@@ -51,11 +51,11 @@ $items_number = array(5,10,20,50,'all');
//
$since_options = array(
1 => array('label' => l10n('today'),
- 'clause' => 'date > SUBDATE(CURDATE(), INTERVAL 1 DAY)'),
+ 'clause' => 'date > '.pwg_db_get_recent_period_expression(1)),
2 => array('label' => sprintf(l10n('last %d days'), 7),
- 'clause' => 'date > SUBDATE(CURDATE(), INTERVAL 7 DAY)'),
+ 'clause' => 'date > '.pwg_db_get_recent_period_expression(7)),
3 => array('label' => sprintf(l10n('last %d days'), 30),
- 'clause' => 'date > SUBDATE(CURDATE(), INTERVAL 30 DAY)'),
+ 'clause' => 'date > '.pwg_db_get_recent_period_expression(30)),
4 => array('label' => l10n('the beginning'),
'clause' => '1=1') // stupid but generic
);
@@ -138,7 +138,7 @@ $page['where_clauses'][] = $since_options[$page['since']]['clause'];
// which status to filter on ?
if ( !is_admin() )
{
- $page['where_clauses'][] = 'validated="true"';
+ $page['where_clauses'][] = 'validated=\'true\'';
}
$page['where_clauses'][] = get_sql_condition_FandF
diff --git a/feed.php b/feed.php
index 5f74d5091..229f1156c 100644
--- a/feed.php
+++ b/feed.php
@@ -156,7 +156,7 @@ if ( !empty($feed_id) and empty($news) )
{
$query = '
UPDATE '.USER_FEED_TABLE.'
- SET last_check = DATE_ADD(\''.$dbnow.'\', INTERVAL -15 DAY )
+ SET last_check = '.pwg_db_get_recent_period_expression(-15, $dbnow).'
WHERE id = \''.$feed_id.'\'
;';
pwg_query($query);
diff --git a/include/calendar_base.class.php b/include/calendar_base.class.php
index a0b90ba05..079da2621 100644
--- a/include/calendar_base.class.php
+++ b/include/calendar_base.class.php
@@ -268,7 +268,7 @@ $this->get_date_where($level).'
$prev = $next =null;
if ( empty($page['chronology_date']) )
return;
- $query = 'SELECT CONCAT_WS("-"';
+ $query = 'SELECT CONCAT_WS(\'-\'';
for ($i=0; $i<count($page['chronology_date']); $i++)
{
if ( 'any' === $page['chronology_date'][$i] )
diff --git a/include/calendar_monthly.class.php b/include/calendar_monthly.class.php
index 0bd68ee1e..f24ef9f26 100644
--- a/include/calendar_monthly.class.php
+++ b/include/calendar_monthly.class.php
@@ -347,7 +347,7 @@ SELECT id, file,tn_ext,path, width, height, DAYOFWEEK('.$this->date_field.')-1 a
$query.= $this->inner_sql;
$query.= $this->get_date_where();
$query.= '
- ORDER BY RAND()
+ ORDER BY '.DB_RANDOM_FUNCTION.'()
LIMIT 1';
unset ( $page['chronology_date'][CDAY] );
diff --git a/include/category_cats.inc.php b/include/category_cats.inc.php
index dfd009519..e9e7b0e7c 100644
--- a/include/category_cats.inc.php
+++ b/include/category_cats.inc.php
@@ -35,9 +35,7 @@ SELECT
c.*, nb_images, date_last, max_date_last, count_images, count_categories
FROM '.CATEGORIES_TABLE.' c INNER JOIN '.USER_CACHE_CATEGORIES_TABLE.'
ON id = cat_id and user_id = '.$user['id'].'
- WHERE date_last >= SUBDATE(
- CURRENT_DATE,INTERVAL '.$user['recent_period'].' DAY
- )
+ WHERE date_last >= '.pwg_db_get_recent_period_expression($user['recent_period']).'
'.get_sql_condition_FandF
(
array
@@ -104,7 +102,7 @@ SELECT image_id
),
"\n AND"
).'
- ORDER BY RAND()
+ ORDER BY '.DB_RANDOM_FUNCTION.'()
LIMIT 1
;';
$subresult = pwg_query($query);
@@ -132,7 +130,7 @@ SELECT image_id
),
"\n AND"
).'
- ORDER BY RAND()
+ ORDER BY '.DB_RANDOM_FUNCTION.'()
LIMIT 1
;';
$subresult = pwg_query($query);
diff --git a/include/dblayer/functions_mysql.inc.php b/include/dblayer/functions_mysql.inc.php
index 4128e960e..f21ace0f3 100644
--- a/include/dblayer/functions_mysql.inc.php
+++ b/include/dblayer/functions_mysql.inc.php
@@ -21,6 +21,11 @@
// | USA. |
// +-----------------------------------------------------------------------+
+define('DB_ENGINE', 'MySQL');
+
+define('DB_REGEX_OPERATOR', 'REGEXP');
+define('DB_RANDOM_FUNCTION', 'RAND');
+
/**
*
* simple functions
@@ -107,6 +112,16 @@ function pwg_query($query)
return $result;
}
+function pwg_db_nextval($column, $table)
+{
+ $query = '
+SELECT IF(MAX('.$column.')+1 IS NULL, 1, MAX('.$column.')+1)
+ FROM '.$table;
+ list($next) = pwg_db_fetch_row(pwg_query($query));
+
+ return $next;
+}
+
function pwg_db_changes($result)
{
return mysql_affected_rows($result);
@@ -443,6 +458,86 @@ function do_maintenance_all_tables()
}
}
+function pwg_db_get_recent_period_expression($period, $date='CURRENT_DATE')
+{
+ if ($date!='CURRENT_DATE')
+ {
+ $date = '\''.$date.'\'';
+ }
+
+ return 'SUBDATE('.$date.',INTERVAL '.$period.' DAY)';
+}
+
+function pwg_db_get_recent_period($period, $date='CURRENT_DATE')
+{
+ $query = '
+SELECT '.pwg_db_get_recent_period_expression($period);
+ list($d) = pwg_db_fetch_row(pwg_query($query));
+
+ return $d;
+}
+
+/**
+ * returns an array containing the possible values of an enum field
+ *
+ * @param string tablename
+ * @param string fieldname
+ */
+function get_enums($table, $field)
+{
+ // retrieving the properties of the table. Each line represents a field :
+ // columns are 'Field', 'Type'
+ $result = pwg_query('desc '.$table);
+ while ($row = pwg_db_fetch_assoc($result))
+ {
+ // we are only interested in the the field given in parameter for the
+ // function
+ if ($row['Field'] == $field)
+ {
+ // retrieving possible values of the enum field
+ // enum('blue','green','black')
+ $options = explode(',', substr($row['Type'], 5, -1));
+ foreach ($options as $i => $option)
+ {
+ $options[$i] = str_replace("'", '',$option);
+ }
+ }
+ }
+ pwg_db_free_result($result);
+ return $options;
+}
+
+// get_boolean transforms a string to a boolean value. If the string is
+// "false" (case insensitive), then the boolean value false is returned. In
+// any other case, true is returned.
+function get_boolean( $string )
+{
+ $boolean = true;
+ if ( 'false' == strtolower($string) )
+ {
+ $boolean = false;
+ }
+ return $boolean;
+}
+
+/**
+ * returns boolean string 'true' or 'false' if the given var is boolean
+ *
+ * @param mixed $var
+ * @return mixed
+ */
+function boolean_to_string($var)
+{
+ if (is_bool($var))
+ {
+ return $var ? 'true' : 'false';
+ }
+ else
+ {
+ return $var;
+ }
+}
+
// my_error returns (or send to standard output) the message concerning the
// error occured for the last mysql query.
function my_error($header, $die)
diff --git a/include/filter.inc.php b/include/filter.inc.php
index c8c3e56e8..623fa25e7 100644
--- a/include/filter.inc.php
+++ b/include/filter.inc.php
@@ -96,8 +96,7 @@ WHERE ';
category_id IN ('.$filter['visible_categories'].') and';
}
$query.= '
- date_available >= SUBDATE(
- CURRENT_DATE,INTERVAL '.$filter['recent_period'].' DAY)';
+ date_available >= '.pwg_db_get_recent_period_expression($filter['recent_period']);
$filter['visible_images'] = implode(',', array_from_query($query, 'image_id'));
diff --git a/include/functions.inc.php b/include/functions.inc.php
index a31c85f13..cbf47b50d 100644
--- a/include/functions.inc.php
+++ b/include/functions.inc.php
@@ -33,67 +33,6 @@ include_once( PHPWG_ROOT_PATH .'include/functions_plugins.inc.php' );
//----------------------------------------------------------- generic functions
-/**
- * returns an array containing the possible values of an enum field
- *
- * @param string tablename
- * @param string fieldname
- */
-function get_enums($table, $field)
-{
- // retrieving the properties of the table. Each line represents a field :
- // columns are 'Field', 'Type'
- $result = pwg_query('desc '.$table);
- while ($row = pwg_db_fetch_assoc($result))
- {
- // we are only interested in the the field given in parameter for the
- // function
- if ($row['Field'] == $field)
- {
- // retrieving possible values of the enum field
- // enum('blue','green','black')
- $options = explode(',', substr($row['Type'], 5, -1));
- foreach ($options as $i => $option)
- {
- $options[$i] = str_replace("'", '',$option);
- }
- }
- }
- pwg_db_free_result($result);
- return $options;
-}
-
-// get_boolean transforms a string to a boolean value. If the string is
-// "false" (case insensitive), then the boolean value false is returned. In
-// any other case, true is returned.
-function get_boolean( $string )
-{
- $boolean = true;
- if ( 'false' == strtolower($string) )
- {
- $boolean = false;
- }
- return $boolean;
-}
-
-/**
- * returns boolean string 'true' or 'false' if the given var is boolean
- *
- * @param mixed $var
- * @return mixed
- */
-function boolean_to_string($var)
-{
- if (is_bool($var))
- {
- return $var ? 'true' : 'false';
- }
- else
- {
- return $var;
- }
-}
-
// The function get_moment returns a float value coresponding to the number
// of seconds since the unix epoch (1st January 1970) and the microseconds
// are precised : e.g. 1052343429.89276600
@@ -540,8 +479,8 @@ INSERT INTO '.HISTORY_TABLE.'
)
VALUES
(
- CURDATE(),
- CURTIME(),
+ CURRENT_DATE,
+ CURRENT_TIME,
'.$user['id'].',
\''.$_SERVER['REMOTE_ADDR'].'\',
'.(isset($page['section']) ? "'".$page['section']."'" : 'NULL').',
@@ -1497,9 +1436,7 @@ function get_icon($date, $is_child_date = false)
if (!isset($cache['get_icon']['sql_recent_date']))
{
// Use MySql date in order to standardize all recent "actions/queries"
- list($cache['get_icon']['sql_recent_date']) =
- pwg_db_fetch_row(pwg_query('select SUBDATE(
- CURRENT_DATE,INTERVAL '.$user['recent_period'].' DAY)'));
+ $cache['get_icon']['sql_recent_date'] = pwg_db_get_recent_period($user['recent_period']);
}
$cache['get_icon'][$date] = $date > $cache['get_icon']['sql_recent_date'];
diff --git a/include/functions_category.inc.php b/include/functions_category.inc.php
index fb981fcbd..d35114ca3 100644
--- a/include/functions_category.inc.php
+++ b/include/functions_category.inc.php
@@ -377,7 +377,7 @@ SELECT DISTINCT(id)
$query.= '
OR ';
}
- $query.= 'uppercats REGEXP \'(^|,)'.$category_id.'(,|$)\'';
+ $query.= 'uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$category_id.'(,|$)\'';
}
$query.= '
;';
diff --git a/include/functions_notification.inc.php b/include/functions_notification.inc.php
index 58091786a..082356846 100644
--- a/include/functions_notification.inc.php
+++ b/include/functions_notification.inc.php
@@ -463,7 +463,7 @@ SELECT DISTINCT id, path, name, tn_ext, file
'.$where_sql.'
AND date_available="'.$dates[$i]['date_available'].'"
AND tn_ext IS NOT NULL
- ORDER BY RAND(NOW())
+ ORDER BY '.DB_RANDOM_FUNCTION.'())
LIMIT '.$max_elements.'
;';
$dates[$i]['elements'] = array();
diff --git a/include/functions_plugins.inc.php b/include/functions_plugins.inc.php
index a6bbbb31f..83126b84c 100644
--- a/include/functions_plugins.inc.php
+++ b/include/functions_plugins.inc.php
@@ -224,7 +224,7 @@ SELECT * FROM '.PLUGINS_TABLE;
$clauses = array();
if (!empty($state))
{
- $clauses[] = 'state="'.$state.'"';
+ $clauses[] = 'state=\''.$state.'\'';
}
if (!empty($id))
{
diff --git a/include/functions_user.inc.php b/include/functions_user.inc.php
index 51f8420c9..2a2ed79e9 100644
--- a/include/functions_user.inc.php
+++ b/include/functions_user.inc.php
@@ -385,8 +385,8 @@ INSERT INTO '.USER_CACHE_TABLE.'
VALUES
('.$userdata['id'].',\''.boolean_to_string($userdata['need_update']).'\','
.$userdata['cache_update_time'].',\''
- .$userdata['forbidden_categories'].'\','.$userdata['nb_total_images'].',"'
- .$userdata['image_access_type'].'","'.$userdata['image_access_list'].'")';
+ .$userdata['forbidden_categories'].'\','.$userdata['nb_total_images'].',\''
+ .$userdata['image_access_type'].'\',\''.$userdata['image_access_list'].'\')';
pwg_query($query);
}
}
@@ -632,7 +632,7 @@ FROM '.CATEGORIES_TABLE.' as c
if ( isset($filter_days) )
{
- $query .= ' AND i.date_available > SUBDATE(CURRENT_DATE,INTERVAL '.$filter_days.' DAY)';
+ $query .= ' AND i.date_available > '.pwg_db_get_recent_period_expression($filter_days);
}
if ( !empty($userdata['forbidden_categories']) )
@@ -1039,7 +1039,7 @@ function try_log_user($username, $password, $remember_me)
SELECT '.$conf['user_fields']['id'].' AS id,
'.$conf['user_fields']['password'].' AS password
FROM '.USERS_TABLE.'
- WHERE '.$conf['user_fields']['username'].' = \''.mysql_real_escape_string($username).'\'
+ WHERE '.$conf['user_fields']['username'].' = \''.pwg_db_real_escape_string($username).'\'
;';
$row = pwg_db_fetch_assoc(pwg_query($query));
if ($row['password'] == $conf['pass_convert']($password))
diff --git a/include/section_init.inc.php b/include/section_init.inc.php
index ce3e3a553..5cc0fbb35 100644
--- a/include/section_init.inc.php
+++ b/include/section_init.inc.php
@@ -449,8 +449,7 @@ SELECT DISTINCT(id)
FROM '.IMAGES_TABLE.'
INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id
WHERE
- date_available >= SUBDATE(
- CURRENT_DATE,INTERVAL '.$user['recent_period'].' DAY)
+ date_available >= '.pwg_db_get_recent_period_expression($user['recent_period']).'
'.$forbidden.'
'.$conf['order_by'].'
;';
diff --git a/include/ws_functions.inc.php b/include/ws_functions.inc.php
index 8f7d7882f..733b220ac 100644
--- a/include/ws_functions.inc.php
+++ b/include/ws_functions.inc.php
@@ -121,15 +121,15 @@ function ws_std_image_sql_order( $params, $tbl_name='' )
case 'date_posted':
$matches[1][$i] = 'date_available'; break;
case 'rand': case 'random':
- $matches[1][$i] = 'RAND()'; break;
+ $matches[1][$i] = DB_RANDOM_FUNCTION.'()'; break;
}
$sortable_fields = array('id', 'file', 'name', 'hit', 'average_rate',
- 'date_creation', 'date_available', 'RAND()' );
+ 'date_creation', 'date_available', DB_RANDOM_FUNCTION.'()' );
if ( in_array($matches[1][$i], $sortable_fields) )
{
if (!empty($ret))
$ret .= ', ';
- if ($matches[1][$i] != 'RAND()' )
+ if ($matches[1][$i] != DB_RANDOM_FUNCTION.'()' )
{
$ret .= $tbl_name;
}
@@ -230,7 +230,7 @@ function ws_categories_getImages($params, &$service)
continue;
if ($params['recursive'])
{
- $where_clauses[] = 'uppercats REGEXP \'(^|,)'.$cat_id.'(,|$)\'';
+ $where_clauses[] = 'uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.$cat_id.'(,|$)\'';
}
else
{
@@ -377,7 +377,7 @@ function ws_categories_getList($params, &$service)
}
else if ($params['cat_id']>0)
{
- $where[] = 'uppercats REGEXP \'(^|,)'.
+ $where[] = 'uppercats '.DB_REGEX_OPERATOR.' \'(^|,)'.
(int)($params['cat_id'])
.'(,|$)\'';
}
diff --git a/random.php b/random.php
index 81920aba9..f1adfada5 100644
--- a/random.php
+++ b/random.php
@@ -51,7 +51,7 @@ SELECT DISTINCT(id)
),
'WHERE'
).'
- ORDER BY RAND(NOW())
+ ORDER BY '.DB_RANDOM_FUNCTION.'()
LIMIT '.min(50, $conf['top_number'],$user['nb_image_page']).'
;';