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 --- admin/cat_modify.php | 2 +- admin/element_set.php | 2 +- admin/include/functions_metadata.php | 2 +- admin/menubar.php | 10 ++-- admin/site_update.php | 12 ++--- comments.php | 8 +-- feed.php | 2 +- 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 ++-- random.php | 2 +- 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; $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']) .'(,|$)\''; } 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']).' ;'; -- cgit v1.2.3