From 13ea9d50e35d9dd8cf7235a39d97a344e6091ea0 Mon Sep 17 00:00:00 2001 From: nikrou Date: Wed, 25 Nov 2009 19:02:57 +0000 Subject: 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 --- include/calendar_base.class.php | 2 +- include/calendar_monthly.class.php | 2 +- include/category_cats.inc.php | 8 ++- include/dblayer/functions_mysql.inc.php | 95 +++++++++++++++++++++++++++++++++ include/filter.inc.php | 3 +- include/functions.inc.php | 69 ++---------------------- include/functions_category.inc.php | 2 +- include/functions_notification.inc.php | 2 +- include/functions_plugins.inc.php | 2 +- include/functions_user.inc.php | 8 +-- include/section_init.inc.php | 3 +- include/ws_functions.inc.php | 10 ++-- 12 files changed, 117 insertions(+), 89 deletions(-) (limited to 'include') 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; $idate_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']) .'(,|$)\''; } -- cgit v1.2.3