From 4560e2d824a99514ea6a2ca2a89c8d0453923fbc Mon Sep 17 00:00:00 2001 From: plegall Date: Tue, 31 May 2016 16:00:03 +0200 Subject: fixes #478, add new column user_infos.last_visit This will speed up user edit popin opening, by avoiding to search in history for the last user visit. The column user_infos.last_visit_from_history true/false says if the last_visit has already been search in history (to avoid making it twice). I could have implemented the search of last_visit for all users in the migration task 149 but in case of many users and long history, it would have taken years to execute... --- include/functions.inc.php | 14 ++++++++++ include/functions_user.inc.php | 42 ++++++++++++++++++++++++++++++ include/ws_functions/pwg.users.php | 52 +++++++++++++++----------------------- install/db/149-database.php | 40 +++++++++++++++++++++++++++++ install/piwigo_structure-mysql.sql | 2 ++ 5 files changed, 119 insertions(+), 31 deletions(-) create mode 100644 install/db/149-database.php diff --git a/include/functions.inc.php b/include/functions.inc.php index 018747817..5cef77f62 100644 --- a/include/functions.inc.php +++ b/include/functions.inc.php @@ -411,6 +411,20 @@ function pwg_log($image_id = null, $image_type = null, $format_id = null) { global $conf, $user, $page; + $update_last_visit = true; + $update_last_visit = trigger_change('pwg_log_update_last_visit', $update_last_visit); + + if ($update_last_visit) + { + $query = ' +UPDATE '.USER_INFOS_TABLE.' + SET last_visit = NOW(), + lastmodified = lastmodified + WHERE user_id = '.$user['id'].' +'; + pwg_query($query); + } + $do_log = $conf['log']; if (is_admin()) { diff --git a/include/functions_user.inc.php b/include/functions_user.inc.php index de8f87c7f..6a910c511 100644 --- a/include/functions_user.inc.php +++ b/include/functions_user.inc.php @@ -1614,4 +1614,46 @@ UPDATE '.USER_AUTH_KEYS_TABLE.' ;'; pwg_query($query); } + +/** + * Gets the last visit (datetime) of a user, based on history table + * + * @since 2.9 + * @param int $user_id + * @param boolean $save_in_user_infos to store result in user_infos.last_visit + * @return string date & time of last visit + */ +function get_user_last_visit_from_history($user_id, $save_in_user_infos=false) +{ + $last_visit = null; + + $query = ' +SELECT + date, + time +FROM '.HISTORY_TABLE.' + WHERE user_id = '.$user_id.' + ORDER BY id DESC + LIMIT 1 +;'; + $result = pwg_query($query); + while ($row = pwg_db_fetch_assoc($result)) + { + $last_visit = $row['date'].' '.$row['time']; + } + + if ($save_in_user_infos) + { + $query = ' +UPDATE '.USER_INFOS_TABLE.' + SET last_visit = '.(is_null($last_visit) ? 'NULL' : "'".$last_visit."'").', + last_visit_from_history = \'true\', + lastmodified = lastmodified + WHERE user_id = '.$user_id.' +'; + pwg_query($query); + } + + return $last_visit; +} ?> diff --git a/include/ws_functions/pwg.users.php b/include/ws_functions/pwg.users.php index eaa96c9c1..1a55fcf90 100644 --- a/include/ws_functions/pwg.users.php +++ b/include/ws_functions/pwg.users.php @@ -123,7 +123,8 @@ function ws_users_getList($params, &$service) $ui_fields = array( 'status','level','language','theme','nb_image_page','recent_period','expand', - 'show_nb_comments','show_nb_hits','enabled_high','registration_date' + 'show_nb_comments','show_nb_hits','enabled_high','registration_date', + 'last_visit' ); foreach ($ui_fields as $field) { @@ -154,6 +155,12 @@ SELECT DISTINCT '; $query.= '"" AS groups'; } + if (isset($display['ui.last_visit'])) + { + if (!$first) $query.= ', '; + $query.= 'ui.last_visit_from_history AS last_visit_from_history'; + } + $query.= ' FROM '. USERS_TABLE .' AS u INNER JOIN '. USER_INFOS_TABLE .' AS ui @@ -210,42 +217,25 @@ SELECT user_id, group_id if (isset($params['display']['last_visit'])) { - $query = ' -SELECT - MAX(id) as history_id - FROM '.HISTORY_TABLE.' - WHERE user_id IN ('.implode(',', array_keys($users)).') - GROUP BY user_id -;'; - $history_ids = array_from_query($query, 'history_id'); - - if (count($history_ids) == 0) - { - $history_ids[] = -1; - } - - $query = ' -SELECT - user_id, - date, - time - FROM '.HISTORY_TABLE.' - WHERE id IN ('.implode(',', $history_ids).') -;'; - $result = pwg_query($query); - while ($row = pwg_db_fetch_assoc($result)) + foreach ($users as $cur_user) { - $last_visit = $row['date'].' '.$row['time']; - $users[ $row['user_id'] ]['last_visit'] = $last_visit; - + $last_visit = $cur_user['last_visit']; + $users[ $cur_user['id'] ]['last_visit'] = $last_visit; + + if (!get_boolean($cur_user['last_visit_from_history']) and empty($last_visit)) + { + $last_visit = get_user_last_visit_from_history($cur_user['id'], true); + $users[ $cur_user['id'] ]['last_visit'] = $last_visit; + } + if (isset($params['display']['last_visit_string'])) { - $users[ $row['user_id'] ]['last_visit_string'] = format_date($last_visit, array('day', 'month', 'year')); + $users[ $cur_user['id'] ]['last_visit_string'] = format_date($last_visit, array('day', 'month', 'year')); } if (isset($params['display']['last_visit_since'])) { - $users[ $row['user_id'] ]['last_visit_since'] = time_since($last_visit, 'day'); + $users[ $cur_user['id'] ]['last_visit_since'] = time_since($last_visit, 'day'); } } } @@ -632,4 +622,4 @@ SELECT )); } -?> \ No newline at end of file +?> diff --git a/install/db/149-database.php b/install/db/149-database.php new file mode 100644 index 000000000..a42af507a --- /dev/null +++ b/install/db/149-database.php @@ -0,0 +1,40 @@ + diff --git a/install/piwigo_structure-mysql.sql b/install/piwigo_structure-mysql.sql index 1c7853edc..e1c076320 100644 --- a/install/piwigo_structure-mysql.sql +++ b/install/piwigo_structure-mysql.sql @@ -460,6 +460,8 @@ CREATE TABLE `piwigo_user_infos` ( `level` tinyint unsigned NOT NULL default '0', `activation_key` varchar(255) default NULL, `activation_key_expire` datetime default NULL, + `last_visit` datetime default NULL, + `last_visit_from_history` enum('true','false') NOT NULL default 'false' `lastmodified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), KEY `lastmodified` (`lastmodified`) -- cgit v1.2.3 From 3f1dbf19c5ef84ae048501f6270e74d7d9638a71 Mon Sep 17 00:00:00 2001 From: plegall Date: Tue, 31 May 2016 16:11:19 +0200 Subject: typo fixed, missing comma --- install/piwigo_structure-mysql.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/install/piwigo_structure-mysql.sql b/install/piwigo_structure-mysql.sql index e1c076320..76e9fe90b 100644 --- a/install/piwigo_structure-mysql.sql +++ b/install/piwigo_structure-mysql.sql @@ -461,7 +461,7 @@ CREATE TABLE `piwigo_user_infos` ( `activation_key` varchar(255) default NULL, `activation_key_expire` datetime default NULL, `last_visit` datetime default NULL, - `last_visit_from_history` enum('true','false') NOT NULL default 'false' + `last_visit_from_history` enum('true','false') NOT NULL default 'false', `lastmodified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), KEY `lastmodified` (`lastmodified`) -- cgit v1.2.3