From 22b41f3bfcbd5a54d59789c2cebfda6dc23b7dde Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 26 Mar 2017 00:01:11 +0100 Subject: Candidate implementation of defered insertion https://github.com/FreshRSS/FreshRSS/issues/530 --- app/Models/EntryDAO.php | 115 +++++++++++++++++++++++++++++++++++------------- 1 file changed, 84 insertions(+), 31 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index afcde3d7f..decae9307 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -88,6 +88,38 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return false; } + protected function createEntryTempTable() { + $ok = false; + $hadTransaction = $this->bd->inTransaction(); + if ($hadTransaction) { + $this->bd->commit(); + } + try { + $db = FreshRSS_Context::$system_conf->db; + require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); + Minz_Log::warning('SQL CREATE TABLE entrytmp...'); + if (defined('SQL_CREATE_TABLE_ENTRYTMP')) { + $sql = sprintf(SQL_CREATE_TABLE_ENTRYTMP, $this->prefix); + $stm = $this->bd->prepare($sql); + $ok = $stm && $stm->execute(); + } else { + global $SQL_CREATE_TABLE_ENTRYTMP; + $ok = !empty($SQL_CREATE_TABLE_ENTRYTMP); + foreach ($SQL_CREATE_TABLE_ENTRYTMP as $instruction) { + $sql = sprintf($instruction, $this->prefix); + $stm = $this->bd->prepare($sql); + $ok &= $stm && $stm->execute(); + } + } + } catch (Exception $e) { + Minz_Log::error('FreshRSS_EntryDAO::createEntryTempTable error: ' . $e->getMessage()); + } + if ($hadTransaction) { + $this->bd->beginTransaction(); + } + return $ok; + } + protected function autoUpdateDb($errorInfo) { if (isset($errorInfo[0])) { if ($errorInfo[0] === '42S22') { //ER_BAD_FIELD_ERROR @@ -97,6 +129,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $this->addColumn($column); } } + } elseif ($errorInfo[0] === '42S02' && stripos($errorInfo[2], 'entrytmp') !== false) { //ER_BAD_TABLE_ERROR + return $this->createEntryTempTable(); //v1.7 } } if (isset($errorInfo[1])) { @@ -110,8 +144,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { private $addEntryPrepared = null; public function addEntry($valuesTmp) { - if ($this->addEntryPrepared === null) { - $sql = 'INSERT INTO `' . $this->prefix . 'entry` (id, guid, title, author, ' + if ($this->addEntryPrepared == null) { + $sql = 'INSERT INTO `' . $this->prefix . 'entrytmp` (id, guid, title, author, ' . ($this->isCompressed() ? 'content_bin' : 'content') . ', link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) ' . 'VALUES(:id, :guid, :title, :author, ' @@ -121,41 +155,43 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . ', :is_read, :is_favorite, :id_feed, :tags)'; $this->addEntryPrepared = $this->bd->prepare($sql); } - $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']); - $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); - $valuesTmp['guid'] = safe_ascii($valuesTmp['guid']); - $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); - $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255); - $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); - $valuesTmp['author'] = substr($valuesTmp['author'], 0, 255); - $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); - $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); - $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); - $valuesTmp['link'] = safe_ascii($valuesTmp['link']); - $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); - $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); - $valuesTmp['lastSeen'] = time(); - $this->addEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT); - $valuesTmp['is_read'] = $valuesTmp['is_read'] ? 1 : 0; - $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'], PDO::PARAM_INT); - $valuesTmp['is_favorite'] = $valuesTmp['is_favorite'] ? 1 : 0; - $this->addEntryPrepared->bindParam(':is_favorite', $valuesTmp['is_favorite'], PDO::PARAM_INT); - $this->addEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT); - $valuesTmp['tags'] = substr($valuesTmp['tags'], 0, 1023); - $this->addEntryPrepared->bindParam(':tags', $valuesTmp['tags']); - - if ($this->hasNativeHex()) { - $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hash']); - } else { - $valuesTmp['hashBin'] = pack('H*', $valuesTmp['hash']); //hex2bin() is PHP5.4+ - $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']); + if ($this->addEntryPrepared) { + $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']); + $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); + $valuesTmp['guid'] = safe_ascii($valuesTmp['guid']); + $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']); + $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255); + $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']); + $valuesTmp['author'] = substr($valuesTmp['author'], 0, 255); + $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']); + $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']); + $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023); + $valuesTmp['link'] = safe_ascii($valuesTmp['link']); + $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); + $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); + $valuesTmp['lastSeen'] = time(); + $this->addEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT); + $valuesTmp['is_read'] = $valuesTmp['is_read'] ? 1 : 0; + $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'], PDO::PARAM_INT); + $valuesTmp['is_favorite'] = $valuesTmp['is_favorite'] ? 1 : 0; + $this->addEntryPrepared->bindParam(':is_favorite', $valuesTmp['is_favorite'], PDO::PARAM_INT); + $this->addEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT); + $valuesTmp['tags'] = substr($valuesTmp['tags'], 0, 1023); + $this->addEntryPrepared->bindParam(':tags', $valuesTmp['tags']); + + if ($this->hasNativeHex()) { + $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hash']); + } else { + $valuesTmp['hashBin'] = pack('H*', $valuesTmp['hash']); //hex2bin() is PHP5.4+ + $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']); + } } - if ($this->addEntryPrepared && $this->addEntryPrepared->execute()) { return $this->bd->lastInsertId(); } else { $info = $this->addEntryPrepared == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $this->addEntryPrepared->errorInfo(); if ($this->autoUpdateDb($info)) { + $this->addEntryPrepared = null; return $this->addEntry($valuesTmp); } elseif ((int)($info[0] / 1000) !== 23) { //Filter out "SQLSTATE Class code 23: Constraint Violation" because of expected duplicate entries Minz_Log::error('SQL error addEntry: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] @@ -165,6 +201,23 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } + public function commitNewEntries() { + $sql = 'SET @rank=SELECT MAX(id) - COUNT(*) FROM `' . $this->prefix . 'entrytmp`; ' . //MySQL-specific + 'INSERT IGNORE INTO `' . $this->prefix . 'entry` (id, guid, title, author, content_bin, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) ' . + 'SELECT @rank:=@rank+1 AS id, guid, title, author, content_bin, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags FROM `' . $this->prefix . 'entrytmp` ORDER BY date; ' . + 'DELETE FROM `' . $this->prefix . 'entrytmp` WHERE id <= @rank;'; + $stm = $this->bd->prepare($sql); + $hadTransaction = $this->bd->inTransaction(); + if (!$hadTransaction) { + $this->bd->beginTransaction(); + } + $result = $stm ? $stm->execute() : false; + if (!$hadTransaction) { + $this->bd->commit(); + } + return $result; + } + private $updateEntryPrepared = null; public function updateEntry($valuesTmp) { -- cgit v1.2.3 From a20fd9db9f0ed0e27c65671bb10402ced10587b1 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 26 Mar 2017 01:41:08 +0100 Subject: Defered insertion MySQL bug The update of cached values remains to be optimized --- app/Controllers/feedController.php | 1 + app/Models/EntryDAO.php | 6 +++--- app/SQL/install.sql.mysql.php | 2 +- 3 files changed, 5 insertions(+), 4 deletions(-) (limited to 'app/Models/EntryDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index a2d9d5c35..b565d0439 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -436,6 +436,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } if (!$noCommit) { $entryDAO->commitNewEntries(); + $feedDAO->updateCachedValues(); //TODO: Optimize } return array($updated_feeds, reset($feeds)); } diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index decae9307..9d11cec6a 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -121,6 +121,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } protected function autoUpdateDb($errorInfo) { + Minz_Log::warning('FreshRSS_EntryDAO::autoUpdateDb: ' . print_r($errorInfo, true)); if (isset($errorInfo[0])) { if ($errorInfo[0] === '42S22') { //ER_BAD_FIELD_ERROR //autoAddColumn @@ -202,16 +203,15 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function commitNewEntries() { - $sql = 'SET @rank=SELECT MAX(id) - COUNT(*) FROM `' . $this->prefix . 'entrytmp`; ' . //MySQL-specific + $sql = 'SET @rank=(SELECT MAX(id) - COUNT(*) FROM `' . $this->prefix . 'entrytmp`); ' . //MySQL-specific 'INSERT IGNORE INTO `' . $this->prefix . 'entry` (id, guid, title, author, content_bin, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) ' . 'SELECT @rank:=@rank+1 AS id, guid, title, author, content_bin, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags FROM `' . $this->prefix . 'entrytmp` ORDER BY date; ' . 'DELETE FROM `' . $this->prefix . 'entrytmp` WHERE id <= @rank;'; - $stm = $this->bd->prepare($sql); $hadTransaction = $this->bd->inTransaction(); if (!$hadTransaction) { $this->bd->beginTransaction(); } - $result = $stm ? $stm->execute() : false; + $result = $this->bd->exec($sql) !== false; if (!$hadTransaction) { $this->bd->commit(); } diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index ceca07f93..f42e08ad3 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -79,7 +79,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentrytmp` ( -- v1.7 PRIMARY KEY (`id`), FOREIGN KEY (`id_feed`) REFERENCES `%1$sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY (`id_feed`,`guid`), - INDEX (`date`), + INDEX (`date`) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = INNODB; '); -- cgit v1.2.3 From e956aee53d561fbdc11a78a50ad7cc041108e5b5 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 26 Mar 2017 14:07:06 +0200 Subject: More defered insertion. New SQL index New index entry_feed_read_index TODO: Auto add this index to existing version --- app/Controllers/feedController.php | 8 +++++- app/Models/EntryDAO.php | 1 - app/Models/Factory.php | 9 +----- app/Models/FeedDAO.php | 56 ++++++++++++++++++-------------------- app/Models/FeedDAOSQLite.php | 19 ------------- app/SQL/install.sql.mysql.php | 1 + app/SQL/install.sql.pgsql.php | 1 + app/SQL/install.sql.sqlite.php | 1 + 8 files changed, 37 insertions(+), 59 deletions(-) delete mode 100644 app/Models/FeedDAOSQLite.php (limited to 'app/Models/EntryDAO.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index b565d0439..bfc8b2045 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -393,7 +393,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } } - $feedDAO->updateLastUpdate($feed->id(), false, $entryDAO->inTransaction(), $mtime); + $feedDAO->updateLastUpdate($feed->id(), false, $mtime); if ($entryDAO->inTransaction()) { $entryDAO->commit(); } @@ -435,8 +435,14 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } } if (!$noCommit) { + if (!$entryDAO->inTransaction()) { + $entryDAO->beginTransaction(); + } $entryDAO->commitNewEntries(); $feedDAO->updateCachedValues(); //TODO: Optimize + if ($entryDAO->inTransaction()) { + $entryDAO->commit(); + } } return array($updated_feeds, reset($feeds)); } diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 9d11cec6a..39c00f01c 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -121,7 +121,6 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } protected function autoUpdateDb($errorInfo) { - Minz_Log::warning('FreshRSS_EntryDAO::autoUpdateDb: ' . print_r($errorInfo, true)); if (isset($errorInfo[0])) { if ($errorInfo[0] === '42S22') { //ER_BAD_FIELD_ERROR //autoAddColumn diff --git a/app/Models/Factory.php b/app/Models/Factory.php index 6502c38b7..dfccc883e 100644 --- a/app/Models/Factory.php +++ b/app/Models/Factory.php @@ -3,14 +3,7 @@ class FreshRSS_Factory { public static function createFeedDao($username = null) { - $conf = Minz_Configuration::get('system'); - switch ($conf->db['type']) { - case 'sqlite': - case 'pgsql': - return new FreshRSS_FeedDAOSQLite($username); - default: - return new FreshRSS_FeedDAO($username); - } + return new FreshRSS_FeedDAO($username); } public static function createEntryDao($username = null) { diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 0168aebd9..d278122e3 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -92,29 +92,15 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } - public function updateLastUpdate($id, $inError = false, $updateCache = true, $mtime = 0) { - if ($updateCache) { - $sql = 'UPDATE `' . $this->prefix . 'feed` ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE - . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' - . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0),' - . '`lastUpdate`=?, error=? ' - . 'WHERE id=?'; - } else { - $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET `lastUpdate`=?, error=? ' - . 'WHERE id=?'; - } - - if ($mtime <= 0) { - $mtime = time(); - } - + public function updateLastUpdate($id, $inError = false, $mtime = 0) { //See also updateCachedValue() + $sql = 'UPDATE `' . $this->prefix . 'feed` ' + . 'SET `lastUpdate`=?, error=? ' + . 'WHERE id=?'; $values = array( - $mtime, + $mtime <= 0 ? time() : $mtime, $inError ? 1 : 0, $id, ); - $stm = $this->bd->prepare($sql); if ($stm && $stm->execute($values)) { @@ -294,18 +280,28 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $res[0]['count']; } - public function updateCachedValues() { //For one single feed, call updateLastUpdate($id) - $sql = 'UPDATE `' . $this->prefix . 'feed` f ' - . 'INNER JOIN (' - . 'SELECT e.id_feed, ' - . 'COUNT(CASE WHEN e.is_read = 0 THEN 1 END) AS nbUnreads, ' - . 'COUNT(e.id) AS nbEntries ' - . 'FROM `' . $this->prefix . 'entry` e ' - . 'GROUP BY e.id_feed' - . ') x ON x.id_feed=f.id ' - . 'SET f.`cache_nbEntries`=x.nbEntries, f.`cache_nbUnreads`=x.nbUnreads'; + public function updateCachedValue($id) { //For multiple feeds, call updateCachedValues() + $sql = 'UPDATE `' . $this->prefix . 'feed` ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE + . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' + . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0) ' + . 'WHERE id=?'; + $values = array($id); $stm = $this->bd->prepare($sql); + if ($stm && $stm->execute($values)) { + return $stm->rowCount(); + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::error('SQL error updateCachedValue: ' . $info[2]); + return false; + } + } + + public function updateCachedValues() { //For one single feed, call updateCachedValue($id) + $sql = 'UPDATE `' . $this->prefix . 'feed` ' + . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' + . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0)'; + $stm = $this->bd->prepare($sql); if ($stm && $stm->execute()) { return $stm->rowCount(); } else { @@ -343,7 +339,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $affected; } - public function cleanOldEntries($id, $date_min, $keep = 15) { //Remember to call updateLastUpdate($id) or updateCachedValues() just after + public function cleanOldEntries($id, $date_min, $keep = 15) { //Remember to call updateCachedValue($id) or updateCachedValues() just after $sql = 'DELETE FROM `' . $this->prefix . 'entry` ' . 'WHERE id_feed=:id_feed AND id<=:id_max ' . 'AND is_favorite=0 ' //Do not remove favourites diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php deleted file mode 100644 index 440ae74da..000000000 --- a/app/Models/FeedDAOSQLite.php +++ /dev/null @@ -1,19 +0,0 @@ -prefix . 'feed` ' - . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' - . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0)'; - $stm = $this->bd->prepare($sql); - if ($stm && $stm->execute()) { - return $stm->rowCount(); - } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::error('SQL error updateCachedValues: ' . $info[2]); - return false; - } - } - -} diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index f42e08ad3..3ad68173f 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -55,6 +55,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( INDEX (`is_favorite`), -- v0.7 INDEX (`is_read`), -- v0.7 INDEX `entry_lastSeen_index` (`lastSeen`) -- v1.1.1 + INDEX `entry_feed_read_index` (`id_feed`,`is_read`) -- v1.7 //TODO: Auto add this index to existing version ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = INNODB; diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php index 1a666cb8a..ca4dfa925 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -50,6 +50,7 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX %1$sis_favorite_index ON "%1$sentry" ("is_favorite");', 'CREATE INDEX %1$sis_read_index ON "%1$sentry" ("is_read");', 'CREATE INDEX %1$sentry_lastSeen_index ON "%1$sentry" ("lastSeen");', +'CREATE INDEX %1$sentry_feed_read_index ON "%1$sentry" ("id_feed","is_read");', //v1.7 //TODO: Auto add this index to existing version 'INSERT INTO "%1$scategory" (name) SELECT \'%2$s\' WHERE NOT EXISTS (SELECT id FROM "%1$scategory" WHERE id = 1);', ); diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index ad7d525fd..dcb7a351a 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -53,6 +53,7 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX IF NOT EXISTS entry_is_favorite_index ON `entry`(`is_favorite`);', 'CREATE INDEX IF NOT EXISTS entry_is_read_index ON `entry`(`is_read`);', 'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `entry`(`lastSeen`);', //v1.1.1 +'CREATE INDEX IF NOT EXISTS entry_feed_read_index ON `entry`(`id_feed`,`is_read`);', //v1.7 //TODO: Auto add this index to existing version 'INSERT OR IGNORE INTO `category` (id, name) VALUES(1, "%2$s");', ); -- cgit v1.2.3