From 38cf7a109ee80cc03edfd420b641676ecd1dfae6 Mon Sep 17 00:00:00 2001 From: Alexis Degrugillier Date: Sat, 8 Nov 2014 09:26:01 -0500 Subject: Add more info in article repartition page I added the same information than on the main stat page (total, read, unread and favorite) on the repartition page. Some refactoring was needed. --- app/Models/StatsDAO.php | 48 ++++++++++++++++++++++++++---------------------- 1 file changed, 26 insertions(+), 22 deletions(-) (limited to 'app/Models/StatsDAO.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 283d5dcb1..0ca251228 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -6,18 +6,36 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo { /** * Calculates entry repartition for all feeds and for main stream. + * + * @return array + */ + public function calculateEntryRepartition() { + return array( + 'main_stream' => $this->calculateEntryRepartitionPerFeed(null, true), + 'all_feeds' => $this->calculateEntryRepartitionPerFeed(null, false), + ); + } + + /** + * Calculates entry repartition for the selection. * The repartition includes: * - total entries * - read entries * - unread entries * - favorite entries * - * @return type + * @param null|integer $feed feed id + * @param boolean $only_main + * @return array */ - public function calculateEntryRepartition() { - $repartition = array(); - - // Generates the repartition for the main stream of entry + public function calculateEntryRepartitionPerFeed($feed = null, $only_main = false) { + $filter = ''; + if ($only_main) { + $filter .= 'AND f.priority = 10'; + } + if (!is_null($feed)) { + $filter .= "AND e.id_feed = {$feed}"; + } $sql = <<prefix}entry AS e , {$this->prefix}feed AS f WHERE e.id_feed = f.id -AND f.priority = 10 -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetchAll(PDO::FETCH_ASSOC); - $repartition['main_stream'] = $res[0]; - - // Generates the repartition for all entries - $sql = <<prefix}entry AS e +{$filter} SQL; $stm = $this->bd->prepare($sql); $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_ASSOC); - $repartition['all_feeds'] = $res[0]; - return $repartition; + return $res[0]; } /** @@ -179,7 +183,7 @@ SQL; * @return integer */ public function calculateEntryAveragePerFeedPerHour($feed = null) { - return $this->calculateEntryAveragePerFeedPerPeriod(1/24, $feed); + return $this->calculateEntryAveragePerFeedPerPeriod(1 / 24, $feed); } /** -- cgit v1.2.3 From 960abfcc6558d8421423a42b7781184dad0d9bc7 Mon Sep 17 00:00:00 2001 From: Alexis Degrugillier Date: Sat, 8 Nov 2014 09:29:51 -0500 Subject: Refactor some if statements --- app/Models/StatsDAO.php | 6 ++---- 1 file changed, 2 insertions(+), 4 deletions(-) (limited to 'app/Models/StatsDAO.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 0ca251228..255a2b1ff 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -151,10 +151,9 @@ SQL; * @return string */ protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) { + $restrict = ''; if ($feed) { $restrict = "WHERE e.id_feed = {$feed}"; - } else { - $restrict = ''; } $sql = << Date: Thu, 11 Dec 2014 16:10:39 +0100 Subject: Update i18n for statistics --- app/Controllers/statsController.php | 2 +- app/Models/StatsDAO.php | 2 +- app/i18n/en/admin.php | 27 +++++++++++ app/i18n/en/gen.php | 88 +++++++++++++++--------------------- app/i18n/fr/admin.php | 27 +++++++++++ app/i18n/fr/gen.php | 89 +++++++++++++++---------------------- app/layout/aside_stats.phtml | 6 +-- app/views/stats/idle.phtml | 16 +++---- app/views/stats/index.phtml | 34 +++++++------- app/views/stats/repartition.phtml | 22 ++++----- 10 files changed, 167 insertions(+), 146 deletions(-) (limited to 'app/Models/StatsDAO.php') diff --git a/app/Controllers/statsController.php b/app/Controllers/statsController.php index 578df9434..4a597ae7d 100644 --- a/app/Controllers/statsController.php +++ b/app/Controllers/statsController.php @@ -15,7 +15,7 @@ class FreshRSS_stats_Controller extends Minz_ActionController { Minz_Error::error(403); } - Minz_View::prependTitle(_t('stats') . ' · '); + Minz_View::prependTitle(_t('admin.stats.title') . ' · '); } /** diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 255a2b1ff..80caccc49 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -418,7 +418,7 @@ SQL; */ private function convertToTranslatedJson($data = array()) { $translated = array_map(function($a) { - return _t($a); + return _t('gen.date.' . $a); }, $data); return json_encode($translated); diff --git a/app/i18n/en/admin.php b/app/i18n/en/admin.php index cf8d8bd00..3ac36a914 100644 --- a/app/i18n/en/admin.php +++ b/app/i18n/en/admin.php @@ -87,9 +87,36 @@ return array( ), ), 'stats' => array( + '_' => 'Statistics', + 'all_feeds' => 'All feeds', + 'category' => 'Category', + 'entry_count' => 'Entry count', + 'entry_per_category' => 'Entries per category', + 'entry_per_day' => 'Entries per day (last 30 days)', + 'entry_per_day_of_week' => 'Per day of week (average: %.2f messages)', + 'entry_per_hour' => 'Per hour (average: %.2f messages)', + 'entry_per_month' => 'Per month (average: %.2f messages)', + 'entry_repartition' => 'Entries repartition', + 'feed' => 'Feed', + 'feed_per_category' => 'Feeds per category', 'idle' => 'Idle feeds', 'main' => 'Main statistics', + 'main_stream' => 'Main stream', + 'menu' => array( + 'idle' => 'Idle feeds', + 'main' => 'Main statistics', + 'repartition' => 'Articles repartition', + ), + 'no_idle' => 'There is no idle feed!', + 'number_articles' => '%d articles', + 'percent_of_total' => '%% of total', 'repartition' => 'Articles repartition', + 'status_favorites' => 'Favourites', + 'status_read' => 'Read', + 'status_total' => 'Total', + 'status_unread' => 'Unread', + 'title' => 'Statistics', + 'top_feed' => 'Top ten feeds', ), 'users' => array( 'articles_and_size' => '%s articles (%s)', diff --git a/app/i18n/en/gen.php b/app/i18n/en/gen.php index 18e63a335..17fef12c7 100644 --- a/app/i18n/en/gen.php +++ b/app/i18n/en/gen.php @@ -5,6 +5,7 @@ return array( 'back_to_rss_feeds' => '← Go back to your RSS feeds', 'disable' => 'Disable', 'enable' => 'Enable', + 'filter' => 'Filtrer', 'manage' => 'Manage', 'remove' => 'Remove', ), @@ -25,10 +26,45 @@ return array( 'Nov' => '\\N\\o\\v\\e\\m\\b\\e\\r', 'Oct' => '\\O\\c\\t\\o\\b\\e\\r', 'Sep' => '\\S\\e\\p\\t\\e\\m\\b\\e\\r', + 'apr' => 'apr', + 'april' => 'Apr', + 'aug' => 'aug', + 'august' => 'Aug', 'before_yesterday' => 'Before yesterday', + 'dec' => 'dec', + 'december' => 'Dec', + 'feb' => 'feb', + 'february' => 'Feb', 'format_date' => '%s j\\<\\s\\u\\p\\>S\\<\\/\\s\\u\\p\\> Y', 'format_date_hour' => '%s j\\<\\s\\u\\p\\>S\\<\\/\\s\\u\\p\\> Y \\a\\t H\\:i', + 'fri' => 'Fri', + 'jan' => 'jan', + 'january' => 'Jan', + 'jul' => 'jul', + 'july' => 'Jul', + 'jun' => 'jun', + 'june' => 'Jun', + 'last_3_month' => 'Last three months', + 'last_6_month' => 'Last six months', + 'last_month' => 'Last month', + 'last_week' => 'Last week', + 'last_year' => 'Last year', + 'mar' => 'mar', + 'march' => 'Mar', + 'may' => 'May', + 'mon' => 'Mon', + 'nov' => 'nov', + 'november' => 'Nov', + 'oct' => 'oct', + 'october' => 'Oct', + 'sat' => 'Sat', + 'sep' => 'sep', + 'september' => 'Sep', + 'sun' => 'Sun', + 'thu' => 'Thu', 'today' => 'Today', + 'tue' => 'Tue', + 'wed' => 'Wed', 'yesterday' => 'Yesterday', ), 'js' => array( @@ -83,13 +119,10 @@ return array( 'administration' => 'Manage', 'advanced' => 'Advanced', 'after_onread' => 'After “mark all as read”,', - 'all_feeds' => 'All feeds', 'allow_anonymous' => 'Allow anonymous reading of the articles of the default user (%s)', 'allow_anonymous_refresh' => 'Allow anonymous refresh of the articles', 'already_subscribed' => 'You have already subscribed to %s', 'api_enabled' => 'Allow API access (required for mobile apps)', - 'apr' => 'apr', - 'april' => 'Apr', 'archiving_configuration_help' => 'More options are available in the individual stream settings', 'article' => 'Article', 'article_icons' => 'Article icons', @@ -102,8 +135,6 @@ return array( 'articles_to_display' => 'Articles to display', 'ask_empty' => 'Clear?', 'attention' => 'Attention!', - 'aug' => 'aug', - 'august' => 'Aug', 'auth_form' => 'Web form (traditional, requires JavaScript)', 'auth_form_not_set' => 'A problem occured during authentication system configuration. Please retry later.', 'auth_form_set' => 'Form is now your default authentication system.', @@ -169,8 +200,6 @@ return array( 'current_user' => 'Current user', 'damn' => 'Damn!', 'data_is_ok' => 'Permissions on data directory are good', - 'dec' => 'dec', - 'december' => 'Dec', 'default_category' => 'Uncategorized', 'default_user' => 'Username of the default user (maximum 16 alphanumeric characters)', 'default_view' => 'Default view', @@ -190,8 +219,6 @@ return array( 'export_opml' => 'Export list of feeds (OPML)', 'export_starred' => 'Export your favourites', 'favicons_is_ok' => 'Permissions on favicons directory are good', - 'feb' => 'feb', - 'february' => 'Feb', 'feed' => 'Feed', 'feed_actualized' => '%s has been updated', 'feed_added' => 'RSS feed %s has been added', @@ -219,7 +246,6 @@ return array( 'fix_errors_before' => 'Fix errors before skip to the next step.', 'focus_search' => 'Access search box', 'freshrss_installation' => 'Installation · FreshRSS', - 'fri' => 'Fri', 'general_conf_is_ok' => 'General configuration has been saved.', 'general_configuration' => 'General configuration', 'help' => 'Display documentation', @@ -242,40 +268,25 @@ return array( 'invalid_login' => 'Login is invalid', 'invalid_url' => 'URL %s is invalid', 'is_admin' => 'is administrator', - 'jan' => 'jan', - 'january' => 'Jan', 'javascript_for_shortcuts' => 'JavaScript must be enabled in order to use shortcuts', 'javascript_is_better' => 'FreshRSS is more pleasant with JavaScript enabled', 'javascript_should_be_activated' => 'JavaScript must be enabled', - 'jul' => 'jul', - 'july' => 'Jul', 'jump_next' => 'jump to next unread sibling (feed or category)', - 'jun' => 'jun', - 'june' => 'Jun', 'keep_history' => 'Minimum number of articles to keep', 'keep_logged_in' => 'Keep me logged in (1 month)', 'language' => 'Language', 'language_defined' => 'Language has been defined.', - 'last_3_month' => 'Last three months', - 'last_6_month' => 'Last six months', 'last_article' => 'Skip to the last article', - 'last_month' => 'Last month', - 'last_week' => 'Last week', - 'last_year' => 'Last year', 'log_is_ok' => 'Permissions on logs directory are good', 'login_configuration' => 'Login', 'login_persona_problem' => 'Connection problem with Persona?', 'login_required' => 'Login required:', 'login_with_persona' => 'Login with Persona', - 'mar' => 'mar', - 'march' => 'Mar', 'mark_cat_read' => 'Mark category as read', 'mark_favorite' => 'Mark as favourite', 'mark_feed_read' => 'Mark feed as read', - 'may' => 'May', 'minz_is_nok' => 'You lack the Minz framework. You should execute build.sh script or download it on Github and install in %s directory the content of its /lib directory.', 'minz_is_ok' => 'You have the Minz framework', - 'mon' => 'Mon', 'month' => 'months', 'more_information' => 'More information', 'n_entries_deleted' => '%d articles have been deleted', @@ -296,13 +307,8 @@ return array( 'not_read' => '%d unread', 'not_reads' => '%d unread', 'not_yet_implemented' => 'Not yet implemented', - 'nov' => 'nov', - 'november' => 'Nov', - 'number_articles' => '%d articles', 'number_divided_when_reader' => 'Divided by 2 in the reading view.', 'number_feeds' => '%d feeds', - 'oct' => 'oct', - 'october' => 'Oct', 'ok' => 'Ok!', 'oops' => 'Oops!', 'optimization_complete' => 'Optimization complete', @@ -360,13 +366,10 @@ return array( 'refresh' => 'Refresh', 'retrieve_truncated_feeds' => 'Retrieves truncated RSS feeds (attention, requires more time!)', 'rss_feed_management' => 'RSS feeds management', - 'sat' => 'Sat', 'save' => 'Save', 'scroll' => 'while scrolling', 'seconds_(0_means_no_timeout)' => 'seconds (0 means no timeout)', 'see_on_website' => 'See on original website', - 'sep' => 'sep', - 'september' => 'Sep', 'share_name' => 'Share name to display', 'share_url' => 'Share URL to use', 'sharing_management' => 'Sharing options management', @@ -381,32 +384,14 @@ return array( 'show_in_all_flux' => 'Show in main stream', 'sort_order' => 'Sort order', 'starred_list' => 'List of favourite articles', - 'stats_entry_count' => 'Entry count', - 'stats_entry_per_category' => 'Entries per category', - 'stats_entry_per_day' => 'Entries per day (last 30 days)', - 'stats_entry_per_day_of_week' => 'Per day of week (average: %.2f messages)', - 'stats_entry_per_hour' => 'Per hour (average: %.2f messages)', - 'stats_entry_per_month' => 'Per month (average: %.2f messages)', - 'stats_entry_repartition' => 'Entries repartition', - 'stats_feed_per_category' => 'Feeds per category', - 'stats_no_idle' => 'There is no idle feed!', - 'stats_percent_of_total' => '%% of total', - 'stats_top_feed' => 'Top ten feeds', - 'status_favorites' => 'Favourites', - 'status_read' => 'Read', - 'status_total' => 'Total', - 'status_unread' => 'Unread', 'steps' => 'Steps', 'sticky_post' => 'Stick the article to the top when opened', 'submit' => 'Submit', - 'sun' => 'Sun', 'theme' => 'Theme', 'this_is_the_end' => 'This is the end', - 'thu' => 'Thu', 'top_line' => 'Top line', 'truncate' => 'Delete all articles', 'ttl' => 'Do not automatically refresh more often than', - 'tue' => 'Tue', 'unsafe_autologin' => 'Allow unsafe automatic login using the format: ', 'update_apply' => 'Apply', 'update_can_apply' => 'An update is available.', @@ -431,7 +416,6 @@ return array( 'users_list' => 'List of users', 'version_update' => 'Update', 'website_url' => 'Website URL', - 'wed' => 'Wed', 'width_large' => 'Large', 'width_medium' => 'Medium', 'width_no_limit' => 'No limit', diff --git a/app/i18n/fr/admin.php b/app/i18n/fr/admin.php index 3d7201439..1e9eb03af 100644 --- a/app/i18n/fr/admin.php +++ b/app/i18n/fr/admin.php @@ -87,9 +87,36 @@ return array( ), ), 'stats' => array( + '_' => 'Statistiques', + 'all_feeds' => 'Tous les flux', + 'category' => 'Catégorie', + 'entry_count' => 'Nombre d’articles', + 'entry_per_category' => 'Articles par catégorie', + 'entry_per_day' => 'Nombre d’articles par jour (30 derniers jours)', + 'entry_per_day_of_week' => 'Par jour de la semaine (moyenne : %.2f messages)', + 'entry_per_hour' => 'Par heure (moyenne : %.2f messages)', + 'entry_per_month' => 'Par mois (moyenne : %.2f messages)', + 'entry_repartition' => 'Répartition des articles', + 'feed' => 'Flux', + 'feed_per_category' => 'Flux par catégorie', 'idle' => 'Flux inactifs', 'main' => 'Statistiques principales', + 'main_stream' => 'Flux principal', + 'menu' => array( + 'idle' => 'Flux inactifs', + 'main' => 'Statistiques principales', + 'repartition' => 'Répartition des articles', + ), + 'no_idle' => 'Il n’y a aucun flux inactif !', + 'number_articles' => '%d articles', + 'percent_of_total' => '%% du total', 'repartition' => 'Répartition des articles', + 'status_favorites' => 'favoris', + 'status_read' => 'lus', + 'status_total' => 'total', + 'status_unread' => 'non lus', + 'title' => 'Statistiques', + 'top_feed' => 'Les dix plus gros flux', ), 'users' => array( 'articles_and_size' => '%s articles (%s)', diff --git a/app/i18n/fr/gen.php b/app/i18n/fr/gen.php index e7c1c0d09..af595c20b 100644 --- a/app/i18n/fr/gen.php +++ b/app/i18n/fr/gen.php @@ -5,6 +5,7 @@ return array( 'back_to_rss_feeds' => '← Retour à vos flux RSS', 'disable' => 'Désactiver', 'enable' => 'Activer', + 'filter' => 'Filtrer', 'manage' => 'Gérer', 'remove' => 'Supprimer', ), @@ -25,10 +26,45 @@ return array( 'Nov' => '\\n\\o\\v\\e\\m\\b\\r\\e', 'Oct' => '\\o\\c\\t\\o\\b\\r\\e', 'Sep' => '\\s\\e\\p\\t\\e\\m\\b\\r\\e', + 'apr' => 'avr.', + 'april' => 'avril', + 'aug' => 'août', + 'august' => 'août', 'before_yesterday' => 'À partir d’avant-hier', + 'dec' => 'déc.', + 'december' => 'décembre', + 'feb' => 'fév.', + 'february' => 'février', + 'fri' => 'ven.', + 'jan' => 'jan.', + 'january' => 'janvier', + 'jul' => 'jui.', + 'july' => 'juillet', + 'jun' => 'juin', + 'june' => 'juin', 'format_date' => 'j %s Y', 'format_date_hour' => 'j %s Y \\à H\\:i', + 'last_3_month' => 'Depuis les trois derniers mois', + 'last_6_month' => 'Depuis les six derniers mois', + 'last_month' => 'Depuis le mois dernier', + 'last_week' => 'Depuis la semaine dernière', + 'last_year' => 'Depuis l’année dernière', + 'mar' => 'mar.', + 'march' => 'mars', + 'may' => 'mai.', + 'mon' => 'lun.', + 'nov' => 'nov.', + 'november' => 'novembre', + 'oct' => 'oct.', + 'october' => 'octobre', + 'sat' => 'sam.', + 'sep' => 'sep.', + 'september' => 'septembre', + 'sun' => 'dim.', + 'thu' => 'jeu.', 'today' => 'Aujourd’hui', + 'tue' => 'mar.', + 'wed' => 'mer.', 'yesterday' => 'Hier', ), 'js' => array( @@ -71,7 +107,6 @@ return array( '_' => 'Titre', 'authentication' => 'Authentification', 'check_install' => 'Vérification de l’installation', - 'global_view' => 'Vue globale', 'user_management' => 'Gestion des utilisateurs', 'user_profile' => 'Profil', ), @@ -84,13 +119,10 @@ return array( 'administration' => 'Gérer', 'advanced' => 'Avancé', 'after_onread' => 'Après “marquer tout comme lu”,', - 'all_feeds' => 'Tous les flux', 'allow_anonymous' => 'Autoriser la lecture anonyme des articles de l’utilisateur par défaut (%s)', 'allow_anonymous_refresh' => 'Autoriser le rafraîchissement anonyme des flux', 'already_subscribed' => 'Vous êtes déjà abonné à %s', 'api_enabled' => 'Autoriser l’accès par API (nécessaire pour les applis mobiles)', - 'apr' => 'avr.', - 'april' => 'avril', 'archiving_configuration_help' => 'D’autres options sont disponibles dans la configuration individuelle des flux.', 'article' => 'Article', 'article_icons' => 'Icônes d’article', @@ -103,8 +135,6 @@ return array( 'articles_to_display' => 'Articles à afficher', 'ask_empty' => 'Vider ?', 'attention' => 'Attention !', - 'aug' => 'août', - 'august' => 'août', 'auth_form' => 'Formulaire (traditionnel, requiert JavaScript)', 'auth_form_not_set' => 'Un problème est survenu lors de la configuration de votre système d’authentification. Veuillez réessayer plus tard.', 'auth_form_set' => 'Le formulaire est désormais votre système d’authentification.', @@ -170,8 +200,6 @@ return array( 'current_user' => 'Utilisateur actuel', 'damn' => 'Arf !', 'data_is_ok' => 'Les droits sur le répertoire de data sont bons', - 'dec' => 'déc.', - 'december' => 'décembre', 'default_category' => 'Sans catégorie', 'default_user' => 'Nom de l’utilisateur par défaut (16 caractères alphanumériques maximum)', 'default_view' => 'Vue par défaut', @@ -191,8 +219,6 @@ return array( 'export_opml' => 'Exporter la liste des flux (OPML)', 'export_starred' => 'Exporter les favoris', 'favicons_is_ok' => 'Les droits sur le répertoire des favicons sont bons', - 'feb' => 'fév.', - 'february' => 'février', 'feed' => 'Flux', 'feed_actualized' => '%s a été mis à jour.', 'feed_added' => 'Le flux %s a bien été ajouté.', @@ -220,7 +246,6 @@ return array( 'fix_errors_before' => 'Veuillez corriger les erreurs avant de passer à l’étape suivante.', 'focus_search' => 'Accéder à la recherche', 'freshrss_installation' => 'Installation · FreshRSS', - 'fri' => 'ven.', 'general_conf_is_ok' => 'La configuration générale a été enregistrée.', 'general_configuration' => 'Configuration générale', 'help' => 'Afficher la documentation', @@ -243,40 +268,25 @@ return array( 'invalid_login' => 'L’identifiant est invalide !', 'invalid_url' => 'L’url %s est invalide.', 'is_admin' => 'est administrateur', - 'jan' => 'jan.', - 'january' => 'janvier', 'javascript_for_shortcuts' => 'Le JavaScript doit être activé pour pouvoir profiter des raccourcis.', 'javascript_is_better' => 'FreshRSS est plus agréable à utiliser avec JavaScript activé', 'javascript_should_be_activated' => 'Le JavaScript doit être activé.', - 'jul' => 'jui.', - 'july' => 'juillet', 'jump_next' => 'sauter au prochain voisin non lu (flux ou catégorie)', - 'jun' => 'juin', - 'june' => 'juin', 'keep_history' => 'Nombre minimum d’articles à conserver', 'keep_logged_in' => 'Rester connecté (1 mois)', 'language' => 'Langue', 'language_defined' => 'La langue a bien été définie.', - 'last_3_month' => 'Depuis les trois derniers mois', - 'last_6_month' => 'Depuis les six derniers mois', 'last_article' => 'Passer au dernier article', - 'last_month' => 'Depuis le mois dernier', - 'last_week' => 'Depuis la semaine dernière', - 'last_year' => 'Depuis l’année dernière', 'log_is_ok' => 'Les droits sur le répertoire des logs sont bons', 'login_configuration' => 'Identification', 'login_persona_problem' => 'Problème de connexion à Persona ?', 'login_required' => 'Accès protégé par mot de passe :', 'login_with_persona' => 'Connexion avec Persona', - 'mar' => 'mar.', - 'march' => 'mars', 'mark_cat_read' => 'Marquer la catégorie comme lue', 'mark_favorite' => 'Mettre en favori', 'mark_feed_read' => 'Marquer le flux comme lu', - 'may' => 'mai.', 'minz_is_nok' => 'Vous ne disposez pas de la librairie Minz. Vous devriez exécuter le script build.sh ou bien la télécharger sur Github et installer dans le répertoire %s le contenu de son répertoire /lib.', 'minz_is_ok' => 'Vous disposez du framework Minz', - 'mon' => 'lun.', 'month' => 'mois', 'more_information' => 'Plus d’informations', 'n_entries_deleted' => '%d articles ont été supprimés.', @@ -297,13 +307,8 @@ return array( 'not_read' => '%d non lu', 'not_reads' => '%d non lus', 'not_yet_implemented' => 'Pas encore implémenté', - 'nov' => 'nov.', - 'november' => 'novembre', - 'number_articles' => '%d articles', 'number_divided_when_reader' => 'Divisé par 2 dans la vue de lecture.', 'number_feeds' => '%d flux', - 'oct' => 'oct.', - 'october' => 'octobre', 'ok' => 'Ok !', 'oops' => 'Oups !', 'optimization_complete' => 'Optimisation terminée.', @@ -361,13 +366,10 @@ return array( 'refresh' => 'Actualisation', 'retrieve_truncated_feeds' => 'Permet de récupérer les flux tronqués (attention, demande plus de temps !)', 'rss_feed_management' => 'Gestion des flux RSS', - 'sat' => 'sam.', 'save' => 'Enregistrer', 'scroll' => 'au défilement de la page', 'seconds_(0_means_no_timeout)' => 'secondes (0 signifie aucun timeout ) ', 'see_on_website' => 'Voir sur le site d’origine', - 'sep' => 'sep.', - 'september' => 'septembre', 'share_name' => 'Nom du partage à afficher', 'share_url' => 'URL du partage à utiliser', 'sharing_management' => 'Gestion des options de partage', @@ -382,32 +384,14 @@ return array( 'show_in_all_flux' => 'Afficher dans le flux principal', 'sort_order' => 'Ordre de tri', 'starred_list' => 'Liste des articles favoris', - 'stats_entry_count' => 'Nombre d’articles', - 'stats_entry_per_category' => 'Articles par catégorie', - 'stats_entry_per_day' => 'Nombre d’articles par jour (30 derniers jours)', - 'stats_entry_per_day_of_week' => 'Par jour de la semaine (moyenne : %.2f messages)', - 'stats_entry_per_hour' => 'Par heure (moyenne : %.2f messages)', - 'stats_entry_per_month' => 'Par mois (moyenne : %.2f messages)', - 'stats_entry_repartition' => 'Répartition des articles', - 'stats_feed_per_category' => 'Flux par catégorie', - 'stats_no_idle' => 'Il n’y a aucun flux inactif !', - 'stats_percent_of_total' => '%% du total', - 'stats_top_feed' => 'Les dix plus gros flux', - 'status_favorites' => 'favoris', - 'status_read' => 'lus', - 'status_total' => 'total', - 'status_unread' => 'non lus', 'steps' => 'Étapes', 'sticky_post' => 'Aligner l’article en haut quand il est ouvert', 'submit' => 'Valider', - 'sun' => 'dim.', 'theme' => 'Thème', 'this_is_the_end' => 'This is the end', - 'thu' => 'jeu.', 'top_line' => 'Ligne du haut', 'truncate' => 'Supprimer tous les articles', 'ttl' => 'Ne pas automatiquement rafraîchir plus souvent que', - 'tue' => 'mar.', 'unsafe_autologin' => 'Autoriser les connexions automatiques non-sûres au format : ', 'update_apply' => 'Appliquer la mise à jour', 'update_can_apply' => 'Une mise à jour est disponible.', @@ -432,7 +416,6 @@ return array( 'users_list' => 'Liste des utilisateurs', 'version_update' => 'Mise à jour', 'website_url' => 'URL du site', - 'wed' => 'mer.', 'width_large' => 'Large', 'width_medium' => 'Moyenne', 'width_no_limit' => 'Pas de limite', diff --git a/app/layout/aside_stats.phtml b/app/layout/aside_stats.phtml index 559087df1..4bdaf7165 100644 --- a/app/layout/aside_stats.phtml +++ b/app/layout/aside_stats.phtml @@ -1,12 +1,12 @@
  • - +
  • - +
  • - +
diff --git a/app/views/stats/idle.phtml b/app/views/stats/idle.phtml index 75cba1081..bfee4366c 100644 --- a/app/views/stats/idle.phtml +++ b/app/views/stats/idle.phtml @@ -1,9 +1,9 @@ partial('aside_stats'); ?>
- + -

+

-

+

@@ -24,13 +24,13 @@
  • - - - + + +
  • - () + ()
@@ -42,7 +42,7 @@ if ($nothing) { ?>

- +

diff --git a/app/views/stats/index.phtml b/app/views/stats/index.phtml index 1300cb2c7..c13c5d26c 100644 --- a/app/views/stats/index.phtml +++ b/app/views/stats/index.phtml @@ -1,38 +1,38 @@ partial('aside_stats'); ?>
- + -

+

-

+

- - + + - + - + - + - + @@ -41,14 +41,14 @@
-

+

 
repartition['main_stream']['total']); ?> repartition['all_feeds']['total']); ?>
repartition['main_stream']['read']); ?> repartition['all_feeds']['read']); ?>
repartition['main_stream']['unread']); ?> repartition['all_feeds']['unread']); ?>
repartition['main_stream']['favorite']); ?> repartition['all_feeds']['favorite']); ?>
- - - - + + + + @@ -65,18 +65,18 @@
-

+

-

+

-

+

diff --git a/app/views/stats/repartition.phtml b/app/views/stats/repartition.phtml index 4ea71cfb5..b20d9bbd0 100644 --- a/app/views/stats/repartition.phtml +++ b/app/views/stats/repartition.phtml @@ -1,12 +1,12 @@ partial('aside_stats'); ?>
- + -

+

- - - - + + + + @@ -47,17 +47,17 @@
-

averageHour); ?>

+

averageHour); ?>

-

averageDayOfWeek); ?>

+

averageDayOfWeek); ?>

-

averageMonth); ?>

+

averageMonth); ?>

-- cgit v1.2.3 From 264d05297c72e87b114a8e930db7eae7affe5690 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 21 Feb 2016 17:26:37 +0100 Subject: CSP for statistics https://github.com/FreshRSS/FreshRSS/issues/1075 --- app/Models/StatsDAO.php | 20 ++++---- app/Models/StatsDAOSQLite.php | 4 +- app/views/stats/index.phtml | 64 ++++------------------- app/views/stats/repartition.phtml | 103 ++++++++------------------------------ p/scripts/repartition.js | 72 ++++++++++++++++++++++++++ p/scripts/stats.js | 56 +++++++++++++++++++++ 6 files changed, 169 insertions(+), 150 deletions(-) create mode 100644 p/scripts/repartition.js create mode 100644 p/scripts/stats.js (limited to 'app/Models/StatsDAO.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 80caccc49..5ca333396 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -55,9 +55,9 @@ SQL; /** * Calculates entry count per day on a 30 days period. - * Returns the result as a JSON string. + * Returns the result as a JSON object. * - * @return string + * @return JSON object */ public function calculateEntryCount() { $count = $this->initEntryCountArray(); @@ -257,9 +257,9 @@ SQL; /** * Calculates feed count per category. - * Returns the result as a JSON string. + * Returns the result as a JSON object. * - * @return string + * @return JSON object */ public function calculateFeedByCategory() { $sql = <<initEntryCountArray(); diff --git a/app/views/stats/index.phtml b/app/views/stats/index.phtml index 18bcd4d99..c11b88999 100644 --- a/app/views/stats/index.phtml +++ b/app/views/stats/index.phtml @@ -82,58 +82,12 @@ - + + diff --git a/app/views/stats/repartition.phtml b/app/views/stats/repartition.phtml index b20d9bbd0..980b26a3d 100644 --- a/app/views/stats/repartition.phtml +++ b/app/views/stats/repartition.phtml @@ -30,20 +30,20 @@
-
repartition['total']; ?>
+
- - - - + + + + - - - - + + + + -
repartition['total']; ?>repartition['read']; ?>repartition['unread']; ?>repartition['favorite']; ?>repartition['total']; ?>repartition['read']; ?>repartition['unread']; ?>repartition['favorite']; ?>
+
@@ -62,76 +62,13 @@
- + + diff --git a/p/scripts/repartition.js b/p/scripts/repartition.js new file mode 100644 index 000000000..a391de2f2 --- /dev/null +++ b/p/scripts/repartition.js @@ -0,0 +1,72 @@ +"use strict"; +function initStats() { + if (!window.Flotr) { + if (window.console) { + console.log('FreshRSS waiting for Flotr…'); + } + window.setTimeout(initStats, 50); + return; + } + var jsonRepartition = document.getElementById('jsonRepartition'), + stats = JSON.parse(jsonRepartition.innerHTML); + jsonRepartition.outerHTML = ''; + // Entry per hour + Flotr.draw(document.getElementById('statsEntryPerHour'), + [{ + data: stats.repartitionHour, + bars: {horizontal: false, show: true} + }], + { + grid: {verticalLines: false}, + xaxis: {noTicks: 23, + tickFormatter: function(x) { + var x = parseInt(x); + return x + 1; + }, + min: -0.9, + max: 23.9, + tickDecimals: 0}, + yaxis: {min: 0}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return numberFormat(obj.y);}} + }); + // Entry per day of week + Flotr.draw(document.getElementById('statsEntryPerDayOfWeek'), + [{ + data: stats.repartitionDayOfWeek, + bars: {horizontal: false, show: true} + }], + { + grid: {verticalLines: false}, + xaxis: {noTicks: 6, + tickFormatter: function(x) { + var x = parseInt(x); + return stats.days[x]; + }, + min: -0.9, + max: 6.9, + tickDecimals: 0}, + yaxis: {min: 0}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return numberFormat(obj.y);}} + }); + // Entry per month + Flotr.draw(document.getElementById('statsEntryPerMonth'), + [{ + data: stats.repartitionMonth, + bars: {horizontal: false, show: true} + }], + { + grid: {verticalLines: false}, + xaxis: {noTicks: 12, + tickFormatter: function(x) { + var x = parseInt(x); + return stats.months[(x - 1)]; + }, + min: 0.1, + max: 12.9, + tickDecimals: 0}, + yaxis: {min: 0}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return numberFormat(obj.y);}} + }); + +} +initStats(); diff --git a/p/scripts/stats.js b/p/scripts/stats.js new file mode 100644 index 000000000..2e8ab6e27 --- /dev/null +++ b/p/scripts/stats.js @@ -0,0 +1,56 @@ +"use strict"; +function initStats() { + if (!window.Flotr) { + if (window.console) { + console.log('FreshRSS waiting for Flotr…'); + } + window.setTimeout(initStats, 50); + return; + } + var jsonStats = document.getElementById('jsonStats'), + stats = JSON.parse(jsonStats.innerHTML); + jsonStats.outerHTML = ''; + // Entry per day + var avg = []; + for (var i = -31; i <= 0; i++) { + avg.push([i, stats.average]); + } + Flotr.draw(document.getElementById('statsEntryPerDay'), + [{ + data: stats.dataCount, + bars: {horizontal: false, show: true} + },{ + data: avg, + lines: {show: true}, + label: stats.average, + }], + { + grid: {verticalLines: false}, + xaxis: {noTicks: 6, showLabels: false, tickDecimals: 0, min: -30.75, max: -0.25}, + yaxis: {min: 0}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return numberFormat(obj.y);}} + }); + // Feed per category + Flotr.draw(document.getElementById('statsFeedPerCategory'), + stats.feedByCategory, + { + grid: {verticalLines: false, horizontalLines: false}, + pie: {explode: 10, show: true, labelFormatter: function(){return '';}}, + xaxis: {showLabels: false}, + yaxis: {showLabels: false}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return obj.series.label + ' - '+ numberFormat(obj.y) + ' ('+ (obj.fraction * 100).toFixed(1) + '%)';}}, + legend: {container: document.getElementById('statsFeedPerCategoryLegend'), noColumns: 3} + }); + // Entry per category + Flotr.draw(document.getElementById('statsEntryPerCategory'), + stats.entryByCategory, + { + grid: {verticalLines: false, horizontalLines: false}, + pie: {explode: 10, show: true, labelFormatter: function(){return '';}}, + xaxis: {showLabels: false}, + yaxis: {showLabels: false}, + mouse: {relative: true, track: true, trackDecimals: 0, trackFormatter: function(obj) {return obj.series.label + ' - '+ numberFormat(obj.y) + ' ('+ (obj.fraction * 100).toFixed(1) + '%)';}}, + legend: {container: document.getElementById('statsEntryPerCategoryLegend'), noColumns: 3} + }); +} +initStats(); -- cgit v1.2.3 From 7c1b5e322cca0134f57b3a436129985ba9170b9f Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 2 Aug 2016 22:49:35 +0200 Subject: PostgreSQL draft https://github.com/FreshRSS/FreshRSS/issues/416 Based on @Damstre work https://github.com/FreshRSS/FreshRSS/pull/1071 Not tested --- app/Models/CategoryDAO.php | 7 +- app/Models/ConfigurationSetter.php | 1 + app/Models/EntryDAO.php | 2 +- app/Models/EntryDAOPGSQL.php | 92 ++++++++++++++++++ app/Models/Factory.php | 42 ++++---- app/Models/FeedDAO.php | 2 +- app/Models/StatsDAO.php | 8 +- app/Models/StatsDAOPGSQL.php | 192 +++++++++++++++++++++++++++++++++++++ app/SQL/install.sql.mysql.php | 2 + app/SQL/install.sql.pgsql.php | 91 ++++++++++++++++++ app/install.php | 31 +++++- lib/Minz/ModelPdo.php | 77 +++++++++------ p/scripts/install.js | 6 +- 13 files changed, 495 insertions(+), 58 deletions(-) create mode 100644 app/Models/EntryDAOPGSQL.php create mode 100644 app/Models/StatsDAOPGSQL.php create mode 100644 app/SQL/install.sql.pgsql.php (limited to 'app/Models/StatsDAO.php') diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index fc431553e..a44edb0f6 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -10,7 +10,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable ); if ($stm && $stm->execute($values)) { - return $this->bd->lastInsertId(); + return $this->bd->lastInsertId('"' . parent::prefix . 'category_id_seq"'); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error addCategory: ' . $info[2]); @@ -207,12 +207,13 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable $previousLine = null; $feedsDao = array(); + $feedDao = FreshRSS_Factory::createFeedDAO(); foreach ($listDAO as $line) { if ($previousLine['c_id'] != null && $line['c_id'] !== $previousLine['c_id']) { // End of the current category, we add it to the $list $cat = new FreshRSS_Category( $previousLine['c_name'], - FreshRSS_FeedDAO::daoToFeed($feedsDao, $previousLine['c_id']) + $feedDao->daoToFeed($feedsDao, $previousLine['c_id']) ); $cat->_id($previousLine['c_id']); $list[$previousLine['c_id']] = $cat; @@ -228,7 +229,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable if ($previousLine != null) { $cat = new FreshRSS_Category( $previousLine['c_name'], - FreshRSS_FeedDAO::daoToFeed($feedsDao, $previousLine['c_id']) + $feedDao->daoToFeed($feedsDao, $previousLine['c_id']) ); $cat->_id($previousLine['c_id']); $list[$previousLine['c_id']] = $cat; diff --git a/app/Models/ConfigurationSetter.php b/app/Models/ConfigurationSetter.php index e472b1e7f..988e83356 100644 --- a/app/Models/ConfigurationSetter.php +++ b/app/Models/ConfigurationSetter.php @@ -287,6 +287,7 @@ class FreshRSS_ConfigurationSetter { switch ($value['type']) { case 'mysql': + case 'pgsql': if (empty($value['host']) || empty($value['user']) || empty($value['base']) || diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index c9e6f9742..ba52d3f15 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -3,7 +3,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function isCompressed() { - return parent::$sharedDbType !== 'sqlite'; + return parent::$sharedDbType === 'mysql'; } public function hasNativeHex() { diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php new file mode 100644 index 000000000..95c12ff5d --- /dev/null +++ b/app/Models/EntryDAOPGSQL.php @@ -0,0 +1,92 @@ +bd->beginTransaction(); + + $sql = 'UPDATE "' . $this->prefix . 'entry" ' + . 'SET is_read=:is_read ' + . 'WHERE id_feed=:id_feed AND NOT is_read AND id <= :idmax'; + $values = array($id_feed, $idMax); + $stm = $this->bd->prepare($sql); + $stm->bindValue(':is_read', true, PDO::PARAM_BOOL); + $stm->bindValue(':id_feed', $id_feed); + $stm->bindValue(':idmax', $idMax); + + if (!($stm && $stm->execute())) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::error('SQL error markReadFeed: ' . $info[2]); + $this->bd->rollBack(); + return false; + } + $affected = $stm->rowCount(); + + $this->bd->commit(); + return $affected; + } + + public function listHashForFeedGuids($id_feed, $guids) { + if (count($guids) < 1) { + return array(); + } + $sql = 'SELECT guid, hash AS hexHash FROM "' . $this->prefix . 'entry" WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; + $stm = $this->bd->prepare($sql); + $values = array($id_feed); + $values = array_merge($values, $guids); + if ($stm && $stm->execute($values)) { + $result = array(); + $rows = $stm->fetchAll(PDO::FETCH_ASSOC); + foreach ($rows as $row) { + $result[$row['guid']] = $row['hexHash']; + } + return $result; + } else { + $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); + if ($this->autoAddColumn($info)) { + return $this->listHashForFeedGuids($id_feed, $guids); + } + Minz_Log::error('SQL error listHashForFeedGuids: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] + . ' while querying feed ' . $id_feed); + return false; + } + } + + public function optimizeTable() { + return null; + } + + public function size($all = true) { + $db = FreshRSS_Context::$system_conf->db; + $sql = 'SELECT pg_size_pretty(pg_database_size(?))'; + $values = array($db['base']); + $stm = $this->bd->prepare($sql); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); + return $res[0]; + } + +} diff --git a/app/Models/Factory.php b/app/Models/Factory.php index db09d155d..764987c46 100644 --- a/app/Models/Factory.php +++ b/app/Models/Factory.php @@ -4,37 +4,47 @@ class FreshRSS_Factory { public static function createFeedDao($username = null) { $conf = Minz_Configuration::get('system'); - if ($conf->db['type'] === 'sqlite') { - return new FreshRSS_FeedDAOSQLite($username); - } else { - return new FreshRSS_FeedDAO($username); + switch ($conf->db['type']) { + case 'sqlite': + return new FreshRSS_FeedDAOSQLite($username); + default: + return new FreshRSS_FeedDAO($username); } } public static function createEntryDao($username = null) { $conf = Minz_Configuration::get('system'); - if ($conf->db['type'] === 'sqlite') { - return new FreshRSS_EntryDAOSQLite($username); - } else { - return new FreshRSS_EntryDAO($username); + switch ($conf->db['type']) { + case 'sqlite': + return new FreshRSS_EntryDAOSQLite($username); + case 'pgsql': + return new FreshRSS_EntryDAOPGSQL($username); + default: + return new FreshRSS_EntryDAO($username); } } public static function createStatsDAO($username = null) { $conf = Minz_Configuration::get('system'); - if ($conf->db['type'] === 'sqlite') { - return new FreshRSS_StatsDAOSQLite($username); - } else { - return new FreshRSS_StatsDAO($username); + switch ($conf->db['type']) { + case 'sqlite': + return new FreshRSS_StatsDAOSQLite($username); + case 'pgsql': + return new FreshRSS_StatsDAOPGSQL($username); + default: + return new FreshRSS_StatsDAO($username); } } public static function createDatabaseDAO($username = null) { $conf = Minz_Configuration::get('system'); - if ($conf->db['type'] === 'sqlite') { - return new FreshRSS_DatabaseDAOSQLite($username); - } else { - return new FreshRSS_DatabaseDAO($username); + switch ($conf->db['type']) { + case 'sqlite': + return new FreshRSS_DatabaseDAOSQLite($username); + case 'pgsql': + return new FreshRSS_DatabaseDAOPGSQL($username); + default: + return new FreshRSS_DatabaseDAO($username); } } diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 475d39286..f29dac9c0 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -16,7 +16,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { ); if ($stm && $stm->execute($values)) { - return $this->bd->lastInsertId(); + return $this->bd->lastInsertId('"' . parent::prefix . 'feed_id_seq"'); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error addFeed: ' . $info[2]); diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 5ca333396..e18ba4748 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -37,10 +37,10 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo { $filter .= "AND e.id_feed = {$feed}"; } $sql = <<prefix}entry AS e , {$this->prefix}feed AS f WHERE e.id_feed = f.id diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php new file mode 100644 index 000000000..0bde72e58 --- /dev/null +++ b/app/Models/StatsDAOPGSQL.php @@ -0,0 +1,192 @@ +prefix}entry" AS e +, "{$this->prefix}feed" AS f +WHERE e.id_feed = f.id +{$filter} +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + + return $res[0]; + } + + /** + * Calculates entry count per day on a 30 days period. + * Returns the result as a JSON string. + * + * @return string + */ + public function calculateEntryCount() { + $count = $this->initEntryCountArray(); + $period = self::ENTRY_COUNT_PERIOD; + + // Get stats per day for the last 30 days + $sql = <<prefix}entry" AS e +WHERE to_timestamp(e.date) BETWEEN NOW() - INTERVAL '{$period} DAYS' AND NOW() - INTERVAL '1 DAY' +GROUP BY day +ORDER BY day ASC +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + + foreach ($res as $value) { + $count[$value['day']] = (int) $value['count']; + } + + return $this->convertToSerie($count); + } + + /** + * Calculates entry average per day on a 30 days period. + * + * @return integer + */ + public function calculateEntryAverage() { + $period = self::ENTRY_COUNT_PERIOD; + + // Get stats per day for the last 30 days + $sql = <<prefix}entry" AS e +WHERE to_timestamp(e.date) BETWEEN NOW() - INTERVAL '{$period} DAYS' AND NOW() - INTERVAL '1 DAY' +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetch(PDO::FETCH_NAMED); + + return round($res['average'], 2); + } + + /** + * Calculates the number of article per hour of the day per feed + * + * @param integer $feed id + * @return string + */ + public function calculateEntryRepartitionPerFeedPerHour($feed = null) { + return $this->calculateEntryRepartitionPerFeedPerPeriod('hour', $feed); + } + + /** + * Calculates the number of article per day of week per feed + * + * @param integer $feed id + * @return string + */ + public function calculateEntryRepartitionPerFeedPerDayOfWeek($feed = null) { + return $this->calculateEntryRepartitionPerFeedPerPeriod('day', $feed); + } + + /** + * Calculates the number of article per month per feed + * + * @param integer $feed + * @return string + */ + public function calculateEntryRepartitionPerFeedPerMonth($feed = null) { + return $this->calculateEntryRepartitionPerFeedPerPeriod('month', $feed); + } + + /** + * Calculates the number of article per period per feed + * + * @param string $period format string to use for grouping + * @param integer $feed id + * @return string + */ + protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) { + $restrict = ''; + if ($feed) { + $restrict = "WHERE e.id_feed = {$feed}"; + } + $sql = <<prefix}entry" AS e +{$restrict} +GROUP BY period +ORDER BY period ASC +SQL; + + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_NAMED); + + foreach ($res as $value) { + $repartition[(int) $value['period']] = (int) $value['count']; + } + + return $this->convertToSerie($repartition); + } + + /** + * Calculates the average number of article per feed + * + * @param float $period number used to divide the number of day in the period + * @param integer $feed id + * @return integer + */ + protected function calculateEntryAveragePerFeedPerPeriod($period, $feed = null) { + $restrict = ''; + if ($feed) { + $restrict = "WHERE e.id_feed = {$feed}"; + } + $sql = <<prefix}entry" AS e +{$restrict} +SQL; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetch(PDO::FETCH_NAMED); + $date_min = new \DateTime(); + $date_min->setTimestamp($res['date_min']); + $date_max = new \DateTime(); + $date_max->setTimestamp($res['date_max']); + $interval = $date_max->diff($date_min, true); + $interval_in_days = $interval->format('%a'); + if ($interval_in_days <= 0) { + // Surely only one article. + // We will return count / (period/period) == count. + $interval_in_days = $period; + } + + return $res['count'] / ($interval_in_days / $period); + } + +} diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index c78839ef7..92a00aecc 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -1,4 +1,6 @@ $curl ? 'ok' : 'ko', 'pdo-mysql' => $pdo_mysql ? 'ok' : 'ko', 'pdo-sqlite' => $pdo_sqlite ? 'ok' : 'ko', + 'pdo-pgsql' => $pdo_pgsql ? 'ok' : 'ko', 'pdo' => $pdo ? 'ok' : 'ko', 'pcre' => $pcre ? 'ok' : 'ko', 'ctype' => $ctype ? 'ok' : 'ko', @@ -435,6 +438,22 @@ function checkBD() { PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, ); break; + case 'pgsql': + $driver_options = array( + PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, + ); + + try { // on ouvre une connexion juste pour créer la base si elle n'existe pas + $str = 'pgsql:host=' . $_SESSION['bd_host'] . ';'; + $c = new PDO($str, $_SESSION['bd_user'], $_SESSION['bd_password'], $driver_options); + $sql = sprintf(SQL_CREATE_DB, $_SESSION['bd_base']); + $res = $c->query($sql); + } catch (PDOException $e) { + } + + // on écrase la précédente connexion en sélectionnant la nouvelle BDD + $str = 'pgsql:host=' . $_SESSION['bd_host'] . ';dbname=' . $_SESSION['bd_base']; + break; default: return false; } @@ -708,6 +727,12 @@ function printStep3() { SQLite + + +
diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php index 845aecaae..b98a26d06 100644 --- a/lib/Minz/ModelPdo.php +++ b/lib/Minz/ModelPdo.php @@ -55,36 +55,36 @@ class Minz_ModelPdo { $driver_options = isset($conf->db['pdo_options']) && is_array($conf->db['pdo_options']) ? $conf->db['pdo_options'] : array(); try { - $type = $db['type']; - if ($type === 'mysql') { - $string = 'mysql:host=' . $db['host'] - . ';dbname=' . $db['base'] - . ';charset=utf8mb4'; - $driver_options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES utf8mb4'; - $this->prefix = $db['prefix'] . $currentUser . '_'; - } elseif ($type === 'sqlite') { - $string = 'sqlite:' . join_path(DATA_PATH, 'users', $currentUser, 'db.sqlite'); - //$driver_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION; - $this->prefix = ''; - } else { - throw new Minz_PDOConnectionException( - 'Invalid database type!', - $db['user'], Minz_Exception::ERROR - ); - } - self::$sharedDbType = $type; - self::$sharedPrefix = $this->prefix; - - $this->bd = new MinzPDO( - $string, - $db['user'], - $db['password'], - $driver_options - ); - if ($type === 'sqlite') { - $this->bd->exec('PRAGMA foreign_keys = ON;'); + switch ($db['type']) { + case 'mysql': + $string = 'mysql:host=' . $db['host'] . ';dbname=' . $db['base'] . ';charset=utf8mb4'; + $driver_options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES utf8mb4'; + $this->prefix = $db['prefix'] . $currentUser . '_'; + $this->bd = new MinzPDO($string, $db['user'], $db['password'], $driver_options); + //TODO Consider: $this->bd->exec("SET SESSION sql_mode = 'ANSI_QUOTES';"); + break; + case 'sqlite': + $string = 'sqlite:' . join_path(DATA_PATH, 'users', $currentUser, 'db.sqlite'); + $this->prefix = ''; + $this->bd = new MinzPDO($string, $db['user'], $db['password'], $driver_options); + $this->bd->exec('PRAGMA foreign_keys = ON;'); + break; + case 'pgsql': + $string = 'pgsql:host=' . $db['host'] . ';dbname=' . $db['base'] . ';charset=utf8'; + $this->prefix = $db['prefix'] . $currentUser . '_'; + $this->bd = new MinzPDOPGSQL($string, $db['user'], $db['password'], $driver_options); + $this->bd->exec("SET NAMES 'UTF8';"); + break; + default: + throw new Minz_PDOConnectionException( + 'Invalid database type!', + $db['user'], Minz_Exception::ERROR + ); + break; } self::$sharedBd = $this->bd; + self::$sharedDbType = $db['type']; + self::$sharedPrefix = $this->prefix; } catch (Exception $e) { throw new Minz_PDOConnectionException( $string, @@ -119,18 +119,39 @@ class MinzPDO extends PDO { } } + protected function compatibility($statement) { + return $statement; + } + public function prepare($statement, $driver_options = array()) { MinzPDO::check($statement); + $statement = MinzPDO::compatibility($statement); return parent::prepare($statement, $driver_options); } public function exec($statement) { MinzPDO::check($statement); + $statement = MinzPDO::compatibility($statement); return parent::exec($statement); } public function query($statement) { MinzPDO::check($statement); + $statement = MinzPDO::compatibility($statement); return parent::query($statement); } + + public function lastInsertId($name = null) { + return parent::lastInsertId(); //We discard the name, only used by PostgreSQL + } +} + +class MinzPDOPGSQL extends MinzPDO { + protected function compatibility($statement) { + return str_replace(array('`', " X'"), array('"', " E'\\x"), $statement); + } + + public function lastInsertId($name = null) { + return parent::lastInsertId($name); + } } diff --git a/p/scripts/install.js b/p/scripts/install.js index 57fc2450a..3e1db57b5 100644 --- a/p/scripts/install.js +++ b/p/scripts/install.js @@ -42,13 +42,15 @@ if (auth_type) { function mySqlShowHide() { var mysql = document.getElementById('mysql'); if (mysql) { - mysql.style.display = document.getElementById('type').value === 'mysql' ? 'block' : 'none'; - if (document.getElementById('type').value !== 'mysql') { + if (document.getElementById('type').value === 'sqlite') { document.getElementById('host').value = ''; document.getElementById('user').value = ''; document.getElementById('pass').value = ''; document.getElementById('base').value = ''; document.getElementById('prefix').value = ''; + mysql.style.display = 'none'; + } else { + mysql.style.display = 'block'; } } } -- cgit v1.2.3 From bee833bf524e58ea9cf5309fb89f6f8b30005720 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 7 Aug 2016 00:54:49 +0200 Subject: Problematic MySQL reserved keyword `read` and `reads` are reserved keywords --- app/Models/StatsDAO.php | 6 +++--- app/views/stats/index.phtml | 12 ++++++------ app/views/stats/repartition.phtml | 8 ++++---- 3 files changed, 13 insertions(+), 13 deletions(-) (limited to 'app/Models/StatsDAO.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index e18ba4748..3f5bf8515 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -38,9 +38,9 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo { } $sql = <<prefix}entry AS e , {$this->prefix}feed AS f WHERE e.id_feed = f.id diff --git a/app/views/stats/index.phtml b/app/views/stats/index.phtml index 0a2fbdb10..a36f812a8 100644 --- a/app/views/stats/index.phtml +++ b/app/views/stats/index.phtml @@ -23,18 +23,18 @@ - repartition['main_stream']['read']); ?> - repartition['all_feeds']['read']); ?> + repartition['main_stream']['count_reads']); ?> + repartition['all_feeds']['count_reads']); ?> - repartition['main_stream']['unread']); ?> - repartition['all_feeds']['unread']); ?> + repartition['main_stream']['count_unreads']); ?> + repartition['all_feeds']['count_unreads']); ?> - repartition['main_stream']['favorite']); ?> - repartition['all_feeds']['favorite']); ?> + repartition['main_stream']['count_favorites']); ?> + repartition['all_feeds']['count_favorites']); ?> diff --git a/app/views/stats/repartition.phtml b/app/views/stats/repartition.phtml index ffb2c361e..5ebcdce5a 100644 --- a/app/views/stats/repartition.phtml +++ b/app/views/stats/repartition.phtml @@ -12,7 +12,7 @@ if (!empty($feeds)) { echo ''; foreach ($feeds as $feed) { - if ($this->feed && $feed->id() == $this->feed->id()){ + if ($this->feed && $feed->id() == $this->feed->id()) { echo ''; } else { echo ''; @@ -39,9 +39,9 @@ repartition['total']; ?> - repartition['read']; ?> - repartition['unread']; ?> - repartition['favorite']; ?> + repartition['count_reads']; ?> + repartition['count_unreads']; ?> + repartition['count_favorites']; ?> -- cgit v1.2.3 From 961407b4e0546dd38a696a650898fcefbe2d6c8c Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 28 Aug 2016 13:36:11 +0200 Subject: Variable initialization PHP warning when not feed is iddle https://github.com/FreshRSS/FreshRSS/issues/1227#issuecomment-242702464 --- app/Models/StatsDAO.php | 1 + 1 file changed, 1 insertion(+) (limited to 'app/Models/StatsDAO.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 5ca333396..4f83ff577 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -168,6 +168,7 @@ SQL; $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_NAMED); + $repartition = array(); foreach ($res as $value) { $repartition[(int) $value['period']] = (int) $value['count']; } -- cgit v1.2.3 From ccb56bcbf3ef69228ae4147a76cf3059f519bbf3 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 11 Sep 2016 11:24:32 +0200 Subject: Simplify SQL in statistics Reduce the use of product-specific date functions. Improve performances. Remove redundant functions. --- app/Controllers/statsController.php | 36 +++++++++++++++++----- app/Models/StatsDAO.php | 61 ++++++------------------------------- app/Models/StatsDAOSQLite.php | 55 --------------------------------- 3 files changed, 39 insertions(+), 113 deletions(-) (limited to 'app/Models/StatsDAO.php') diff --git a/app/Controllers/statsController.php b/app/Controllers/statsController.php index 4a597ae7d..5d1dee72c 100644 --- a/app/Controllers/statsController.php +++ b/app/Controllers/statsController.php @@ -18,6 +18,27 @@ class FreshRSS_stats_Controller extends Minz_ActionController { Minz_View::prependTitle(_t('admin.stats.title') . ' · '); } + private function convertToSerie($data) { + $serie = array(); + + foreach ($data as $key => $value) { + $serie[] = array($key, $value); + } + + return $serie; + } + + private function convertToPieSerie($data) { + $serie = array(); + + foreach ($data as $value) { + $value['data'] = array(array(0, (int) $value['data'])); + $serie[] = $value; + } + + return $serie; + } + /** * This action handles the statistic main page. * @@ -33,10 +54,11 @@ class FreshRSS_stats_Controller extends Minz_ActionController { $statsDAO = FreshRSS_Factory::createStatsDAO(); Minz_View::appendScript(Minz_Url::display('/scripts/flotr2.min.js?' . @filemtime(PUBLIC_PATH . '/scripts/flotr2.min.js'))); $this->view->repartition = $statsDAO->calculateEntryRepartition(); - $this->view->count = $statsDAO->calculateEntryCount(); - $this->view->average = $statsDAO->calculateEntryAverage(); - $this->view->feedByCategory = $statsDAO->calculateFeedByCategory(); - $this->view->entryByCategory = $statsDAO->calculateEntryByCategory(); + $entryCount = $statsDAO->calculateEntryCount(); + $this->view->count = $this->convertToSerie($entryCount); + $this->view->average = round(array_sum(array_values($entryCount)) / count($entryCount), 2); + $this->view->feedByCategory = $this->convertToPieSerie($statsDAO->calculateFeedByCategory()); + $this->view->entryByCategory = $this->convertToPieSerie($statsDAO->calculateEntryByCategory()); $this->view->topFeed = $statsDAO->calculateTopFeed(); } @@ -118,11 +140,11 @@ class FreshRSS_stats_Controller extends Minz_ActionController { $this->view->days = $statsDAO->getDays(); $this->view->months = $statsDAO->getMonths(); $this->view->repartition = $statsDAO->calculateEntryRepartitionPerFeed($id); - $this->view->repartitionHour = $statsDAO->calculateEntryRepartitionPerFeedPerHour($id); + $this->view->repartitionHour = $this->convertToSerie($statsDAO->calculateEntryRepartitionPerFeedPerHour($id)); $this->view->averageHour = $statsDAO->calculateEntryAveragePerFeedPerHour($id); - $this->view->repartitionDayOfWeek = $statsDAO->calculateEntryRepartitionPerFeedPerDayOfWeek($id); + $this->view->repartitionDayOfWeek = $this->convertToSerie($statsDAO->calculateEntryRepartitionPerFeedPerDayOfWeek($id)); $this->view->averageDayOfWeek = $statsDAO->calculateEntryAveragePerFeedPerDayOfWeek($id); - $this->view->repartitionMonth = $statsDAO->calculateEntryRepartitionPerFeedPerMonth($id); + $this->view->repartitionMonth = $this->convertToSerie($statsDAO->calculateEntryRepartitionPerFeedPerMonth($id)); $this->view->averageMonth = $statsDAO->calculateEntryAveragePerFeedPerMonth($id); } } diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 4f83ff577..28882baab 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -61,14 +61,15 @@ SQL; */ public function calculateEntryCount() { $count = $this->initEntryCountArray(); - $period = self::ENTRY_COUNT_PERIOD; + $midnight = mktime(0, 0, 0); + $oldest = $midnight - (self::ENTRY_COUNT_PERIOD * 86400); // Get stats per day for the last 30 days $sql = <<prefix}entry AS e -WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d') +SELECT FLOOR((date - {$midnight}) / 86400) AS day, +COUNT(*) as count +FROM {$this->prefix}entry +WHERE date >= {$oldest} AND date < {$midnight} GROUP BY day ORDER BY day ASC SQL; @@ -80,28 +81,7 @@ SQL; $count[$value['day']] = (int) $value['count']; } - return $this->convertToSerie($count); - } - - /** - * Calculates entry average per day on a 30 days period. - * - * @return integer - */ - public function calculateEntryAverage() { - $period = self::ENTRY_COUNT_PERIOD; - - // Get stats per day for the last 30 days - $sql = <<prefix}entry AS e -WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d') -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetch(PDO::FETCH_NAMED); - - return round($res['average'], 2); + return $count; } /** @@ -173,7 +153,7 @@ SQL; $repartition[(int) $value['period']] = (int) $value['count']; } - return $this->convertToSerie($repartition); + return $repartition; } /** @@ -276,7 +256,7 @@ SQL; $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_ASSOC); - return $this->convertToPieSerie($res); + return $res; } /** @@ -301,7 +281,7 @@ SQL; $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_ASSOC); - return $this->convertToPieSerie($res); + return $res; } /** @@ -351,27 +331,6 @@ SQL; return $stm->fetchAll(PDO::FETCH_ASSOC); } - protected function convertToSerie($data) { - $serie = array(); - - foreach ($data as $key => $value) { - $serie[] = array($key, $value); - } - - return $serie; - } - - protected function convertToPieSerie($data) { - $serie = array(); - - foreach ($data as $value) { - $value['data'] = array(array(0, (int) $value['data'])); - $serie[] = $value; - } - - return $serie; - } - /** * Gets days ready for graphs * diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index 9bfe8b20a..e09d18c77 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -2,61 +2,6 @@ class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO { - /** - * Calculates entry count per day on a 30 days period. - * Returns the result as a JSON object. - * - * @return JSON object - */ - public function calculateEntryCount() { - $count = $this->initEntryCountArray(); - $period = parent::ENTRY_COUNT_PERIOD; - - // Get stats per day for the last 30 days - $sql = <<prefix}entry AS e -WHERE strftime('%Y%m%d', e.date, 'unixepoch') - BETWEEN strftime('%Y%m%d', 'now', '-{$period} days') - AND strftime('%Y%m%d', 'now', '-1 day') -GROUP BY day -ORDER BY day ASC -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetchAll(PDO::FETCH_ASSOC); - - foreach ($res as $value) { - $count[(int) $value['day']] = (int) $value['count']; - } - - return $this->convertToSerie($count); - } - - /** - * Calculates entry average per day on a 30 days period. - * - * @return integer - */ - public function calculateEntryAverage() { - $period = self::ENTRY_COUNT_PERIOD; - - // Get stats per day for the last 30 days - $sql = <<prefix}entry AS e -WHERE strftime('%Y%m%d', e.date, 'unixepoch') - BETWEEN strftime('%Y%m%d', 'now', '-{$period} days') - AND strftime('%Y%m%d', 'now', '-1 day') -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetch(PDO::FETCH_NAMED); - - return round($res['average'], 2); - } - protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) { if ($feed) { $restrict = "WHERE e.id_feed = {$feed}"; -- cgit v1.2.3 From dbc68590da1d95c249f780e2d3ff4707f6f504e9 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 27 Sep 2016 23:35:39 +0200 Subject: A bit more PostgreSQL Simplified statistics https://github.com/FreshRSS/FreshRSS/pull/1250 --- CHANGELOG.md | 2 + README.fr.md | 2 +- README.md | 2 +- app/Models/StatsDAO.php | 30 +++++----- app/Models/StatsDAOPGSQL.php | 125 ------------------------------------------ app/Models/StatsDAOSQLite.php | 2 +- app/install.php | 2 +- 7 files changed, 21 insertions(+), 144 deletions(-) (limited to 'app/Models/StatsDAO.php') diff --git a/CHANGELOG.md b/CHANGELOG.md index f13922837..e88daba93 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -4,6 +4,8 @@ * API * Support for editing feeds and categories from client applications [#1254](https://github.com/FreshRSS/FreshRSS/issues/1254) +* Compatibility: + * Experimental support for PostgreSQL [#1195](https://github.com/FreshRSS/FreshRSS/pull/1195) * Features * Better control of number of entries per page or RSS feed [#1249](https://github.com/FreshRSS/FreshRSS/issues/1249) * Since X hours: `https://freshrss.example/i/?a=rss&hours=3` diff --git a/README.fr.md b/README.fr.md index 8dc0f6fec..65cef544f 100644 --- a/README.fr.md +++ b/README.fr.md @@ -35,7 +35,7 @@ Nous sommes une communauté amicale. * PHP 5.3.3+ (PHP 5.4+ recommandé, et PHP 5.5+ pour les performances, et PHP 7+ pour d’encore meilleures performances) * Requis : [DOM](http://php.net/dom), [XML](http://php.net/xml), [PDO_MySQL](http://php.net/pdo-mysql) ou [PDO_SQLite](http://php.net/pdo-sqlite) ou [PDO_PGSQL](http://php.net/pdo-pgsql), [cURL](http://php.net/curl) * Recommandés : [JSON](http://php.net/json), [GMP](http://php.net/gmp) (pour accès API sur plateformes < 64 bits), [IDN](http://php.net/intl.idn) (pour les noms de domaines internationalisés), [mbstring](http://php.net/mbstring) et/ou [iconv](http://php.net/iconv) (pour conversion d’encodages), [Zip](http://php.net/zip) (pour import/export), [zlib](http://php.net/zlib) (pour les flux compressés) -* MySQL 5.5.3+ (recommandé), ou SQLite 3.7.4+, ou PostgreSQL +* MySQL 5.5.3+ (recommandé), ou SQLite 3.7.4+, ou PostgreSQL (experimental) * Un navigateur Web récent tel Firefox, Internet Explorer 11 / Edge, Chrome, Opera, Safari. * Fonctionne aussi sur mobile diff --git a/README.md b/README.md index fc2c6fdb9..505a1d970 100644 --- a/README.md +++ b/README.md @@ -35,7 +35,7 @@ We are a friendly community. * PHP 5.3.3+ (PHP 5.4+ recommended, and PHP 5.5+ for performance, and PHP 7 for even higher performance) * Required extensions: [DOM](http://php.net/dom), [XML](http://php.net/xml), [PDO_MySQL](http://php.net/pdo-mysql) or [PDO_SQLite](http://php.net/pdo-sqlite) or [PDO_PGSQL](http://php.net/pdo-pgsql), [cURL](http://php.net/curl) * Recommended extensions: [JSON](http://php.net/json), [GMP](http://php.net/gmp) (for API access on platforms < 64 bits), [IDN](http://php.net/intl.idn) (for Internationalized Domain Names), [mbstring](http://php.net/mbstring) and/or [iconv](http://php.net/iconv) (for charset conversion), [Zip](http://php.net/zip) (for import/export), [zlib](http://php.net/zlib) (for compressed feeds) -* MySQL 5.5.3+ (recommended), or SQLite 3.7.4+, or PostgreSQL +* MySQL 5.5.3+ (recommended), or SQLite 3.7.4+, or PostgreSQL (experimental) * A recent browser like Firefox, Internet Explorer 11 / Edge, Chrome, Opera, Safari. * Works on mobile diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index e6998a6d7..fa682f488 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -41,8 +41,8 @@ SELECT COUNT(1) AS total, COUNT(1) - SUM(e.is_read) AS count_unreads, SUM(e.is_read) AS count_reads, SUM(e.is_favorite) AS count_favorites -FROM {$this->prefix}entry AS e -, {$this->prefix}feed AS f +FROM `{$this->prefix}entry` AS e +, `{$this->prefix}feed` AS f WHERE e.id_feed = f.id {$filter} SQL; @@ -68,7 +68,7 @@ SQL; $sql = <<prefix}entry +FROM `{$this->prefix}entry` WHERE date >= {$oldest} AND date < {$midnight} GROUP BY day ORDER BY day ASC @@ -138,7 +138,7 @@ SQL; $sql = <<prefix}entry AS e +FROM `{$this->prefix}entry` AS e {$restrict} GROUP BY period ORDER BY period ASC @@ -202,7 +202,7 @@ SQL; SELECT COUNT(1) AS count , MIN(date) AS date_min , MAX(date) AS date_max -FROM {$this->prefix}entry AS e +FROM `{$this->prefix}entry` AS e {$restrict} SQL; $stm = $this->bd->prepare($sql); @@ -246,8 +246,8 @@ SQL; $sql = <<prefix}category AS c, -{$this->prefix}feed AS f +FROM `{$this->prefix}category` AS c, +`{$this->prefix}feed` AS f WHERE c.id = f.category GROUP BY label ORDER BY data DESC @@ -269,9 +269,9 @@ SQL; $sql = <<prefix}category AS c, -{$this->prefix}feed AS f, -{$this->prefix}entry AS e +FROM `{$this->prefix}category` AS c, +`{$this->prefix}feed` AS f, +`{$this->prefix}entry` AS e WHERE c.id = f.category AND f.id = e.id_feed GROUP BY label @@ -295,9 +295,9 @@ SELECT f.id AS id , MAX(f.name) AS name , MAX(c.name) AS category , COUNT(e.id) AS count -FROM {$this->prefix}category AS c, -{$this->prefix}feed AS f, -{$this->prefix}entry AS e +FROM `{$this->prefix}category` AS c, +`{$this->prefix}feed` AS f, +`{$this->prefix}entry` AS e WHERE c.id = f.category AND f.id = e.id_feed GROUP BY f.id @@ -320,8 +320,8 @@ SELECT MAX(f.id) as id , MAX(f.name) AS name , MAX(date) AS last_date , COUNT(*) AS nb_articles -FROM {$this->prefix}feed AS f, -{$this->prefix}entry AS e +FROM `{$this->prefix}feed` AS f, +`{$this->prefix}entry` AS e WHERE f.id = e.id_feed GROUP BY f.id ORDER BY name diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php index 0bde72e58..649dfa8b5 100644 --- a/app/Models/StatsDAOPGSQL.php +++ b/app/Models/StatsDAOPGSQL.php @@ -2,94 +2,6 @@ class FreshRSS_StatsDAOPGSQL extends FreshRSS_StatsDAO { - /** - * Calculates entry repartition for the selection. - * The repartition includes: - * - total entries - * - read entries - * - unread entries - * - favorite entries - * - * @param null|integer $feed feed id - * @param boolean $only_main - * @return array - */ - public function calculateEntryRepartitionPerFeed($feed = null, $only_main = false) { - $filter = ''; - if ($only_main) { - $filter .= 'AND f.priority = 10'; - } - if (!is_null($feed)) { - $filter .= "AND e.id_feed = {$feed}"; - } - $sql = <<prefix}entry" AS e -, "{$this->prefix}feed" AS f -WHERE e.id_feed = f.id -{$filter} -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetchAll(PDO::FETCH_ASSOC); - - return $res[0]; - } - - /** - * Calculates entry count per day on a 30 days period. - * Returns the result as a JSON string. - * - * @return string - */ - public function calculateEntryCount() { - $count = $this->initEntryCountArray(); - $period = self::ENTRY_COUNT_PERIOD; - - // Get stats per day for the last 30 days - $sql = <<prefix}entry" AS e -WHERE to_timestamp(e.date) BETWEEN NOW() - INTERVAL '{$period} DAYS' AND NOW() - INTERVAL '1 DAY' -GROUP BY day -ORDER BY day ASC -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetchAll(PDO::FETCH_ASSOC); - - foreach ($res as $value) { - $count[$value['day']] = (int) $value['count']; - } - - return $this->convertToSerie($count); - } - - /** - * Calculates entry average per day on a 30 days period. - * - * @return integer - */ - public function calculateEntryAverage() { - $period = self::ENTRY_COUNT_PERIOD; - - // Get stats per day for the last 30 days - $sql = <<prefix}entry" AS e -WHERE to_timestamp(e.date) BETWEEN NOW() - INTERVAL '{$period} DAYS' AND NOW() - INTERVAL '1 DAY' -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetch(PDO::FETCH_NAMED); - - return round($res['average'], 2); - } - /** * Calculates the number of article per hour of the day per feed * @@ -152,41 +64,4 @@ SQL; return $this->convertToSerie($repartition); } - /** - * Calculates the average number of article per feed - * - * @param float $period number used to divide the number of day in the period - * @param integer $feed id - * @return integer - */ - protected function calculateEntryAveragePerFeedPerPeriod($period, $feed = null) { - $restrict = ''; - if ($feed) { - $restrict = "WHERE e.id_feed = {$feed}"; - } - $sql = <<prefix}entry" AS e -{$restrict} -SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); - $res = $stm->fetch(PDO::FETCH_NAMED); - $date_min = new \DateTime(); - $date_min->setTimestamp($res['date_min']); - $date_max = new \DateTime(); - $date_max->setTimestamp($res['date_max']); - $interval = $date_max->diff($date_min, true); - $interval_in_days = $interval->format('%a'); - if ($interval_in_days <= 0) { - // Surely only one article. - // We will return count / (period/period) == count. - $interval_in_days = $period; - } - - return $res['count'] / ($interval_in_days / $period); - } - } diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index e09d18c77..ec0cfa81a 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -11,7 +11,7 @@ class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO { $sql = <<prefix}entry AS e +FROM `{$this->prefix}entry` AS e {$restrict} GROUP BY period ORDER BY period ASC diff --git a/app/install.php b/app/install.php index 7f4e65034..1972379e5 100644 --- a/app/install.php +++ b/app/install.php @@ -714,7 +714,7 @@ function printStep3() { -- cgit v1.2.3 From c35111fe53ba2081abc735cb61e83987ade5ce86 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 29 Sep 2016 23:36:25 +0200 Subject: Fix SQLite --- app/Models/StatsDAO.php | 7 ++++++- app/Models/StatsDAOSQLite.php | 6 +++++- 2 files changed, 11 insertions(+), 2 deletions(-) (limited to 'app/Models/StatsDAO.php') diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index fa682f488..2ce4f2944 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -4,6 +4,10 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo { const ENTRY_COUNT_PERIOD = 30; + protected function sqlFloor($s) { + return "FLOOR($s)"; + } + /** * Calculates entry repartition for all feeds and for main stream. * @@ -65,8 +69,9 @@ SQL; $oldest = $midnight - (self::ENTRY_COUNT_PERIOD * 86400); // Get stats per day for the last 30 days + $sqlDay = $this->sqlFloor("(date - $midnight) / 86400"); $sql = <<prefix}entry` WHERE date >= {$oldest} AND date < {$midnight} diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index ec0cfa81a..6cfc20463 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -2,6 +2,10 @@ class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO { + protected function sqlFloor($s) { + return "CAST(($s) AS INT)"; + } + protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) { if ($feed) { $restrict = "WHERE e.id_feed = {$feed}"; @@ -26,7 +30,7 @@ SQL; $repartition[(int) $value['period']] = (int) $value['count']; } - return $this->convertToSerie($repartition); + return $repartition; } } -- cgit v1.2.3