From 76a027c9bbfc646c9690f00d63be49cc4287b9c3 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 10 Nov 2013 22:45:58 +0100 Subject: Amélioration des performances de small_hash MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- public/install.php | 7 ++----- 1 file changed, 2 insertions(+), 5 deletions(-) (limited to 'public/install.php') diff --git a/public/install.php b/public/install.php index 5b0618c37..0c39a031e 100644 --- a/public/install.php +++ b/public/install.php @@ -66,13 +66,10 @@ function writeArray ($f, $array) { } } +// tiré de Shaarli de Seb Sauvage //Format RFC 4648 base64url function small_hash ($txt) { $t = rtrim (base64_encode (hash ('crc32', $txt, true)), '='); - $t = str_replace ('+', '-', $t); // Get rid of characters which need encoding in URLs. - $t = str_replace ('/', '_', $t); - $t = str_replace ('=', '@', $t); - - return $t; + return strtr ($t, '+/', '-_'); } // gestion internationalisation -- cgit v1.2.3 From b8821e6b3f94b77033441c193f9e6ea873a2e280 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Mon, 11 Nov 2013 20:55:25 +0100 Subject: MySQL : Index et changements mineurs de types MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Légers changements qui ne devraient pas perturber le fonctionnement. Début de contribution à https://github.com/marienfressinaud/FreshRSS/issues/254 et https://github.com/marienfressinaud/FreshRSS/issues/258 --- public/install.php | 22 ++++++++++++++-------- 1 file changed, 14 insertions(+), 8 deletions(-) (limited to 'public/install.php') diff --git a/public/install.php b/public/install.php index 0c39a031e..ec7d50fbd 100644 --- a/public/install.php +++ b/public/install.php @@ -14,7 +14,8 @@ define ('SQL_REQ_CAT', 'CREATE TABLE IF NOT EXISTS `%scategory` ( `id` varchar(6) NOT NULL, `name` varchar(255) NOT NULL, `color` varchar(7) NOT NULL, - PRIMARY KEY (`id`) + PRIMARY KEY (`id`), + INDEX (`name`) );'); define ('SQL_REQ_FEED', 'CREATE TABLE IF NOT EXISTS `%sfeed` ( @@ -25,13 +26,16 @@ define ('SQL_REQ_FEED', 'CREATE TABLE IF NOT EXISTS `%sfeed` ( `website` text NOT NULL, `description` text NOT NULL, `lastUpdate` int(11) NOT NULL, - `priority` int(2) NOT NULL DEFAULT \'10\', + `priority` tinyint NOT NULL DEFAULT \'10\', `pathEntries` varchar(500) DEFAULT NULL, `httpAuth` varchar(500) DEFAULT NULL, - `error` int(1) NOT NULL DEFAULT \'0\', - `keep_history` int(1) NOT NULL DEFAULT \'0\', + `error` boolean NOT NULL DEFAULT \'0\', + `keep_history` boolean NOT NULL DEFAULT \'0\', PRIMARY KEY (`id`), - FOREIGN KEY (`category`) REFERENCES %scategory(id) ON DELETE SET NULL ON UPDATE CASCADE + FOREIGN KEY (`category`) REFERENCES %scategory(id) ON DELETE SET NULL ON UPDATE CASCADE, + INDEX (`name`), + INDEX (`priority`), + INDEX (`keep_history`) );'); define ('SQL_REQ_ENTRY', 'CREATE TABLE IF NOT EXISTS `%sentry` ( @@ -42,12 +46,14 @@ define ('SQL_REQ_ENTRY', 'CREATE TABLE IF NOT EXISTS `%sentry` ( `content` text NOT NULL, `link` text NOT NULL, `date` int(11) NOT NULL, - `is_read` int(11) NOT NULL DEFAULT \'0\', - `is_favorite` int(11) NOT NULL DEFAULT \'0\', + `is_read` boolean NOT NULL DEFAULT \'0\', + `is_favorite` boolean NOT NULL DEFAULT \'0\', `id_feed` varchar(6) NOT NULL, `tags` text NOT NULL, PRIMARY KEY (`id`), - FOREIGN KEY (`id_feed`) REFERENCES %sfeed(id) ON DELETE CASCADE ON UPDATE CASCADE + FOREIGN KEY (`id_feed`) REFERENCES %sfeed(id) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX (`is_favorite`), + INDEX (`is_read`) );'); -- cgit v1.2.3 From 5af7c472ed406693d19daf6899ad5ea615840740 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Mon, 11 Nov 2013 22:19:37 +0100 Subject: MySQL : changements mineurs de types MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit D'autres changements de types, toujours sans modification de comportement, mais plus efficace. En particulier char(6) plutôt que varchar(6) pour les identifiants en attendant un entier, et varchar plutôt que text dans des champs généralement courts et souvent retournés par les requêtes les plus importantes --- app/models/Entry.php | 22 +++++++++++----------- app/models/Feed.php | 8 ++++---- public/install.php | 26 +++++++++++++------------- 3 files changed, 28 insertions(+), 28 deletions(-) (limited to 'public/install.php') diff --git a/app/models/Entry.php b/app/models/Entry.php index 94cb6f774..d03c3a3c1 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -179,16 +179,16 @@ class Entry extends Model { public function toArray () { return array ( 'id' => $this->id (), - 'guid' => substr($this->guid (), 0, 65535), - 'title' => substr($this->title (), 0, 255), - 'author' => substr($this->author (), 0, 255), - 'content' => substr($this->content (), 0, 65535), - 'link' => substr($this->link (), 0, 65535), + 'guid' => $this->guid (), + 'title' => $this->title (), + 'author' => $this->author (), + 'content' => $this->content (), + 'link' => $this->link (), 'date' => $this->date (true), 'is_read' => $this->isRead (), 'is_favorite' => $this->isFavorite (), 'id_feed' => $this->feed (), - 'tags' => substr($this->tags (true), 0, 65535), + 'tags' => $this->tags (true), ); } } @@ -200,16 +200,16 @@ class EntryDAO extends Model_pdo { $values = array ( $valuesTmp['id'], - $valuesTmp['guid'], - $valuesTmp['title'], - $valuesTmp['author'], + substr($valuesTmp['guid'], 0, 511), + substr($valuesTmp['title'], 0, 255), + substr($valuesTmp['author'], 0, 255), base64_encode (gzdeflate (serialize ($valuesTmp['content']))), - $valuesTmp['link'], + substr($valuesTmp['link'], 0, 1023), $valuesTmp['date'], $valuesTmp['is_read'], $valuesTmp['is_favorite'], $valuesTmp['id_feed'], - $valuesTmp['tags'], + substr($valuesTmp['tags'], 0, 1023), ); if ($stm && $stm->execute ($values)) { diff --git a/app/models/Feed.php b/app/models/Feed.php index 41750d43e..c6ebc9caf 100644 --- a/app/models/Feed.php +++ b/app/models/Feed.php @@ -309,11 +309,11 @@ class FeedDAO extends Model_pdo { $values = array ( $valuesTmp['id'], - $valuesTmp['url'], + substr($valuesTmp['url'], 0, 511), $valuesTmp['category'], - $valuesTmp['name'], - $valuesTmp['website'], - $valuesTmp['description'], + substr($valuesTmp['name'], 0, 255), + substr($valuesTmp['website'], 0, 255), + substr($valuesTmp['description'], 0, 1023), $valuesTmp['lastUpdate'], base64_encode ($valuesTmp['httpAuth']), ); diff --git a/public/install.php b/public/install.php index ec7d50fbd..dd9846fe5 100644 --- a/public/install.php +++ b/public/install.php @@ -11,7 +11,7 @@ if (isset ($_GET['step'])) { define ('SQL_REQ_CREATE_DB', 'CREATE DATABASE %s DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;'); define ('SQL_REQ_CAT', 'CREATE TABLE IF NOT EXISTS `%scategory` ( - `id` varchar(6) NOT NULL, + `id` char(6) NOT NULL, `name` varchar(255) NOT NULL, `color` varchar(7) NOT NULL, PRIMARY KEY (`id`), @@ -19,16 +19,16 @@ define ('SQL_REQ_CAT', 'CREATE TABLE IF NOT EXISTS `%scategory` ( );'); define ('SQL_REQ_FEED', 'CREATE TABLE IF NOT EXISTS `%sfeed` ( - `id` varchar(6) NOT NULL, - `url` text NOT NULL, - `category` varchar(6) DEFAULT \'000000\', + `id` char(6) NOT NULL, + `url` varchar(511) NOT NULL, + `category` char(6) DEFAULT \'000000\', `name` varchar(255) NOT NULL, - `website` text NOT NULL, - `description` text NOT NULL, + `website` varchar(255) NOT NULL, + `description` varchar(1023) NOT NULL, `lastUpdate` int(11) NOT NULL, `priority` tinyint NOT NULL DEFAULT \'10\', - `pathEntries` varchar(500) DEFAULT NULL, - `httpAuth` varchar(500) DEFAULT NULL, + `pathEntries` varchar(511) DEFAULT NULL, + `httpAuth` varchar(511) DEFAULT NULL, `error` boolean NOT NULL DEFAULT \'0\', `keep_history` boolean NOT NULL DEFAULT \'0\', PRIMARY KEY (`id`), @@ -39,17 +39,17 @@ define ('SQL_REQ_FEED', 'CREATE TABLE IF NOT EXISTS `%sfeed` ( );'); define ('SQL_REQ_ENTRY', 'CREATE TABLE IF NOT EXISTS `%sentry` ( - `id` varchar(6) NOT NULL, - `guid` text NOT NULL, + `id` char(6) NOT NULL, + `guid` varchar(511) NOT NULL, `title` varchar(255) NOT NULL, `author` varchar(255) NOT NULL, `content` text NOT NULL, - `link` text NOT NULL, + `link` varchar(1023) NOT NULL, `date` int(11) NOT NULL, `is_read` boolean NOT NULL DEFAULT \'0\', `is_favorite` boolean NOT NULL DEFAULT \'0\', - `id_feed` varchar(6) NOT NULL, - `tags` text NOT NULL, + `id_feed` char(6) NOT NULL, + `tags` varchar(1023) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`id_feed`) REFERENCES %sfeed(id) ON DELETE CASCADE ON UPDATE CASCADE, INDEX (`is_favorite`), -- cgit v1.2.3 From 94c5f41f6d663114bfac9390fc9eb72605a9e8ce Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Mon, 11 Nov 2013 22:27:11 +0100 Subject: MySQL : protection longueurs catégories MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- app/models/Category.php | 4 ++-- public/install.php | 4 ++-- 2 files changed, 4 insertions(+), 4 deletions(-) (limited to 'public/install.php') diff --git a/app/models/Category.php b/app/models/Category.php index 7659e68f6..419284130 100755 --- a/app/models/Category.php +++ b/app/models/Category.php @@ -95,8 +95,8 @@ class CategoryDAO extends Model_pdo { $values = array ( $valuesTmp['id'], - $valuesTmp['name'], - $valuesTmp['color'], + substr($valuesTmp['name'], 0, 255), + substr($valuesTmp['color'], 0, 7), ); if ($stm && $stm->execute ($values)) { diff --git a/public/install.php b/public/install.php index dd9846fe5..7dde53e20 100644 --- a/public/install.php +++ b/public/install.php @@ -13,7 +13,7 @@ define ('SQL_REQ_CREATE_DB', 'CREATE DATABASE %s DEFAULT CHARACTER SET utf8 COLL define ('SQL_REQ_CAT', 'CREATE TABLE IF NOT EXISTS `%scategory` ( `id` char(6) NOT NULL, `name` varchar(255) NOT NULL, - `color` varchar(7) NOT NULL, + `color` char(7) NOT NULL, PRIMARY KEY (`id`), INDEX (`name`) );'); @@ -26,7 +26,7 @@ define ('SQL_REQ_FEED', 'CREATE TABLE IF NOT EXISTS `%sfeed` ( `website` varchar(255) NOT NULL, `description` varchar(1023) NOT NULL, `lastUpdate` int(11) NOT NULL, - `priority` tinyint NOT NULL DEFAULT \'10\', + `priority` tinyint(2) NOT NULL DEFAULT \'10\', `pathEntries` varchar(511) DEFAULT NULL, `httpAuth` varchar(511) DEFAULT NULL, `error` boolean NOT NULL DEFAULT \'0\', -- cgit v1.2.3 From 3f93866f38aff6b9f10d57472bf0ccf16d268499 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 12 Nov 2013 18:49:18 +0100 Subject: SQL : Optimisation vitesse MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Optimisation de la vitesse de quelques requêtes, surtout après les essais d'INDEX. Suite de https://github.com/marienfressinaud/FreshRSS/issues/254 Note pour plus tard : il faudra supprimer les INDEX inutilisés. --- app/layout/aside_flux.phtml | 2 +- app/models/Entry.php | 47 +++++++++++++++++++-------------------------- public/install.php | 2 +- 3 files changed, 22 insertions(+), 29 deletions(-) (limited to 'public/install.php') diff --git a/app/layout/aside_flux.phtml b/app/layout/aside_flux.phtml index 511bb4564..87e2c000d 100644 --- a/app/layout/aside_flux.phtml +++ b/app/layout/aside_flux.phtml @@ -24,7 +24,7 @@ diff --git a/app/models/Entry.php b/app/models/Entry.php index d03c3a3c1..5c6b379d8 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -449,42 +449,35 @@ class EntryDAO extends Model_pdo { } public function countUnreadRead () { - $sql = 'SELECT is_read, COUNT(*) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE priority > 0 GROUP BY is_read'; + $sql = 'SELECT COUNT(e.id) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE priority > 0' + . ' UNION SELECT COUNT(e.id) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE priority > 0 AND is_read = 0'; $stm = $this->bd->prepare ($sql); $stm->execute (); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - - $readUnread = array('unread' => 0, 'read' => 0); - foreach ($res as $line) { - switch (intval($line['is_read'])) { - case 0: $readUnread['unread'] = intval($line['count']); break; - case 1: $readUnread['read'] = intval($line['count']); break; - } - } - return $readUnread; + $res = $stm->fetchAll (PDO::FETCH_COLUMN, 0); + return array('total' => $res[0], 'unread' => $res[1], 'read' => $res[0] - $res[1]); } - public function count () { //Deprecated: use countUnreadRead() instead - $unreadRead = $this->countUnreadRead (); //This makes better use of caching - return $unreadRead['unread'] + $unreadRead['read']; + public function count () { + $sql = 'SELECT COUNT(e.id) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE priority > 0'; + $stm = $this->bd->prepare ($sql); + $stm->execute (); + $res = $stm->fetchAll (PDO::FETCH_COLUMN, 0); + return $res[0]; } - public function countNotRead () { //Deprecated: use countUnreadRead() instead - $unreadRead = $this->countUnreadRead (); //This makes better use of caching - return $unreadRead['unread']; + public function countNotRead () { + $sql = 'SELECT COUNT(e.id) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE priority > 0 AND is_read = 0'; + $stm = $this->bd->prepare ($sql); + $stm->execute (); + $res = $stm->fetchAll (PDO::FETCH_COLUMN, 0); + return $res[0]; } public function countUnreadReadFavorites () { - $sql = 'SELECT is_read, COUNT(*) AS count FROM ' . $this->prefix . 'entry WHERE is_favorite=1 GROUP BY is_read'; + $sql = 'SELECT COUNT(id) FROM ' . $this->prefix . 'entry WHERE is_favorite=1' + . ' UNION SELECT COUNT(id) FROM ' . $this->prefix . 'entry WHERE is_favorite=1 AND is_read = 0'; $stm = $this->bd->prepare ($sql); $stm->execute (); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - $readUnread = array('unread' => 0, 'read' => 0); - foreach ($res as $line) { - switch (intval($line['is_read'])) { - case 0: $readUnread['unread'] = intval($line['count']); break; - case 1: $readUnread['read'] = intval($line['count']); break; - } - } - return $readUnread; + $res = $stm->fetchAll (PDO::FETCH_COLUMN, 0); + return array('all' => $res[0], 'unread' => $res[1], 'read' => $res[0] - $res[1]); } public function optimizeTable() { diff --git a/public/install.php b/public/install.php index 7dde53e20..0dfe77c57 100644 --- a/public/install.php +++ b/public/install.php @@ -24,7 +24,7 @@ define ('SQL_REQ_FEED', 'CREATE TABLE IF NOT EXISTS `%sfeed` ( `category` char(6) DEFAULT \'000000\', `name` varchar(255) NOT NULL, `website` varchar(255) NOT NULL, - `description` varchar(1023) NOT NULL, + `description` text NOT NULL, `lastUpdate` int(11) NOT NULL, `priority` tinyint(2) NOT NULL DEFAULT \'10\', `pathEntries` varchar(511) DEFAULT NULL, -- cgit v1.2.3 From 6f081f9dafce403738b2cae1c02eba653b9f54f9 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 13 Nov 2013 21:39:53 +0100 Subject: Un peu plus de HTML5 pour les types des champs de formulaires --- app/layout/header.phtml | 2 +- app/views/configure/display.phtml | 2 +- public/install.php | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) (limited to 'public/install.php') diff --git a/app/layout/header.phtml b/app/layout/header.phtml index 4f19d1c31..d936a71c6 100644 --- a/app/layout/header.phtml +++ b/app/layout/header.phtml @@ -25,7 +25,7 @@
- + diff --git a/app/views/configure/display.phtml b/app/views/configure/display.phtml index 22fc72f1b..c67cec331 100644 --- a/app/views/configure/display.phtml +++ b/app/views/configure/display.phtml @@ -196,7 +196,7 @@
- +
diff --git a/public/install.php b/public/install.php index 0dfe77c57..02bef0129 100644 --- a/public/install.php +++ b/public/install.php @@ -497,7 +497,7 @@ function printStep2 () { -- cgit v1.2.3 From bff31a6d5ec76ebb1ef724a281cc7318991fc11e Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 13 Nov 2013 22:11:57 +0100 Subject: Correction type url inapproprié MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- public/install.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'public/install.php') diff --git a/public/install.php b/public/install.php index 02bef0129..0dfe77c57 100644 --- a/public/install.php +++ b/public/install.php @@ -497,7 +497,7 @@ function printStep2 () { -- cgit v1.2.3 From 8e8f7254cae079cc791de99eafa477ff53eb8351 Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Fri, 15 Nov 2013 22:32:57 +0100 Subject: Suppression du champs "sel" lors de l'installation MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Génération d'un sel basé sur le temps à la place. Pas parfait mais sûrement suffisant pour FreshRSS --- public/install.php | 14 ++++---------- 1 file changed, 4 insertions(+), 10 deletions(-) (limited to 'public/install.php') diff --git a/public/install.php b/public/install.php index 0dfe77c57..07e18dd37 100644 --- a/public/install.php +++ b/public/install.php @@ -140,13 +140,13 @@ function saveLanguage () { } function saveStep2 () { if (!empty ($_POST)) { - if (empty ($_POST['sel']) || - empty ($_POST['title']) || + if (empty ($_POST['title']) || empty ($_POST['old_entries'])) { return false; } - $_SESSION['sel'] = addslashes ($_POST['sel']); + $first_sel = small_hash (time ()); + $_SESSION['sel'] = small_hash (time () . $first_sel) . $first_sel; $_SESSION['base_url'] = addslashes ($_POST['base_url']); $_SESSION['title'] = addslashes ($_POST['title']); $_SESSION['old_entries'] = $_POST['old_entries']; @@ -484,17 +484,11 @@ function printStep2 () { -
- -
- -
-
-