From 711530a512b370d79b079205ce1f8376174f7f03 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 4 Apr 2015 22:39:31 +0200 Subject: SQL: detection of updates, and preparation for better burge https://github.com/FreshRSS/FreshRSS/issues/798 https://github.com/FreshRSS/FreshRSS/issues/493 SQLite not yet tested. Only MySQL tested so far. --- app/SQL/install.sql.sqlite.php | 7 +++++-- 1 file changed, 5 insertions(+), 2 deletions(-) (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 30bca2810..7517ead45 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -14,7 +14,7 @@ $SQL_CREATE_TABLES = array( `name` varchar(255) NOT NULL, `website` varchar(255), `description` text, - `lastUpdate` int(11) DEFAULT 0, + `lastUpdate` int(11) DEFAULT 0, -- Until year 2038 `priority` tinyint(2) NOT NULL DEFAULT 10, `pathEntries` varchar(511) DEFAULT NULL, `httpAuth` varchar(511) DEFAULT NULL, @@ -38,7 +38,9 @@ $SQL_CREATE_TABLES = array( `author` varchar(255), `content` text, `link` varchar(1023) NOT NULL, - `date` int(11), + `date` int(11), -- Until year 2038 + `lastSeen` INT(11) NOT NULL, -- v1.2, Until year 2038 + `hash` BINARY(16), -- v1.2 `is_read` boolean NOT NULL DEFAULT 0, `is_favorite` boolean NOT NULL DEFAULT 0, `id_feed` SMALLINT, @@ -50,6 +52,7 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX IF NOT EXISTS entry_is_favorite_index ON `%1$sentry`(`is_favorite`);', 'CREATE INDEX IF NOT EXISTS entry_is_read_index ON `%1$sentry`(`is_read`);', +'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `%1$sentry`(`lastSeen`);', //v1.2 'INSERT OR IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");', ); -- cgit v1.2.3 From 217c191a1ba3ac03b847d261a32e19975380fcad Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Mon, 11 May 2015 22:42:41 +0200 Subject: More SQLite compatibility Additional changes to add compatibility with SQLite for the new hash/lastSeen mode of updating articles. --- app/Models/EntryDAO.php | 71 ++++++++++++++++++++++++------------------ app/Models/EntryDAOSQLite.php | 15 +++++++++ app/Models/FeedDAO.php | 11 ++++--- app/SQL/install.sql.mysql.php | 2 +- app/SQL/install.sql.sqlite.php | 2 +- app/install.php | 2 ++ lib/Minz/ModelPdo.php | 5 +++ 7 files changed, 70 insertions(+), 38 deletions(-) (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 172eac897..eae9683ad 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -6,38 +6,48 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return parent::$sharedDbType !== 'sqlite'; } - protected function autoAddColumn($errorInfo) { - if (isset($errorInfo[0])) { - if ($errorInfo[0] == '42S22') { //ER_BAD_FIELD_ERROR - $hasTransaction = false; - try { - $stm = null; - if (stripos($errorInfo[2], 'lastSeen') !== false) { //v1.2 - if (!$this->bd->inTransaction()) { - $this->bd->beginTransaction(); - $hasTransaction = true; - } - $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN lastSeen INT(11) NOT NULL'); - if ($stm && $stm->execute()) { - $stm = $this->bd->prepare('CREATE INDEX entry_lastSeen_index ON `' . $this->prefix . 'entry`(`lastSeen`);'); //"IF NOT EXISTS" does not exist in MySQL 5.7 - if ($stm && $stm->execute()) { - if ($hasTransaction) { - $this->bd->commit(); - } - return true; - } - } + protected function addColumn($name) { + Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn: ' . $name); + $hasTransaction = false; + try { + $stm = null; + if ($name === 'lastSeen') { //v1.2 + if (!$this->bd->inTransaction()) { + $this->bd->beginTransaction(); + $hasTransaction = true; + } + $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN lastSeen INT(11) DEFAULT 0'); + if ($stm && $stm->execute()) { + $stm = $this->bd->prepare('CREATE INDEX entry_lastSeen_index ON `' . $this->prefix . 'entry`(`lastSeen`);'); //"IF NOT EXISTS" does not exist in MySQL 5.7 + if ($stm && $stm->execute()) { if ($hasTransaction) { - $this->bd->rollBack(); + $this->bd->commit(); } - } elseif (stripos($errorInfo[2], 'hash') !== false) { //v1.2 - $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN hash BINARY(16) NOT NULL'); - return $stm && $stm->execute(); + return true; } - } catch (Exception $e) { - Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn error: ' . $e->getMessage()); - if ($hasTransaction) { - $this->bd->rollBack(); + } + if ($hasTransaction) { + $this->bd->rollBack(); + } + } elseif ($name === 'hash') { //v1.2 + $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN hash BINARY(16)'); + return $stm && $stm->execute(); + } + } catch (Exception $e) { + Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn error: ' . $e->getMessage()); + if ($hasTransaction) { + $this->bd->rollBack(); + } + } + return false; + } + + protected function autoAddColumn($errorInfo) { + if (isset($errorInfo[0])) { + if ($errorInfo[0] == '42S22') { //ER_BAD_FIELD_ERROR + foreach (array('lastSeen', 'hash') as $column) { + if (stripos($errorInfo[2], $column) !== false) { + return $this->addColumn($column); } } } @@ -82,7 +92,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { 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] - . ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']); + . ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']. ' ' . $this->addEntryPrepared); } return false; } @@ -597,7 +607,6 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } return $result; } else { - $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); if ($this->autoAddColumn($info)) { return $this->listHashForFeedGuids($id_feed, $guids); diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index ffe0f037c..ff049d813 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -2,6 +2,21 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { + protected function autoAddColumn($errorInfo) { + if (empty($errorInfo[0]) || $errorInfo[0] == '42S22') { //ER_BAD_FIELD_ERROR + if ($tableInfo = $this->bd->query("SELECT sql FROM sqlite_master where name='entry'")) { + $showCreate = $tableInfo->fetchColumn(); + Minz_Log::debug('FreshRSS_EntryDAOSQLite::autoAddColumn: ' . $showCreate); + foreach (array('lastSeen', 'hash') as $column) { + if (stripos($showCreate, $column) === false) { + return $this->addColumn($column); + } + } + } + } + return false; + } + protected function sqlConcat($s1, $s2) { return $s1 . '||' . $s2; } diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 76025ff53..475d39286 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -330,11 +330,12 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . 'AND id NOT IN (SELECT id FROM (SELECT e2.id FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=:id_feed ORDER BY id DESC LIMIT :keep) keep)'; //Double select: MySQL doesn't support 'LIMIT & IN/ALL/ANY/SOME subquery' $stm = $this->bd->prepare($sql); - $id_max = intval($date_min) . '000000'; - - $stm->bindParam(':id_feed', $id, PDO::PARAM_INT); - $stm->bindParam(':id_max', $id_max, PDO::PARAM_STR); - $stm->bindParam(':keep', $keep, PDO::PARAM_INT); + if ($stm) { + $id_max = intval($date_min) . '000000'; + $stm->bindParam(':id_feed', $id, PDO::PARAM_INT); + $stm->bindParam(':id_max', $id_max, PDO::PARAM_STR); + $stm->bindParam(':keep', $keep, PDO::PARAM_INT); + } if ($stm && $stm->execute()) { return $stm->rowCount(); diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index afdd821b2..9c6af405d 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -41,7 +41,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( `content_bin` blob, -- v0.7 `link` varchar(1023) CHARACTER SET latin1 NOT NULL, `date` int(11), -- Until year 2038 - `lastSeen` INT(11) NOT NULL, -- v1.2, Until year 2038 + `lastSeen` INT(11) DEFAULT 0, -- v1.2, Until year 2038 `hash` BINARY(16), -- v1.2 `is_read` boolean NOT NULL DEFAULT 0, `is_favorite` boolean NOT NULL DEFAULT 0, diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 7517ead45..77e8e094c 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -39,7 +39,7 @@ $SQL_CREATE_TABLES = array( `content` text, `link` varchar(1023) NOT NULL, `date` int(11), -- Until year 2038 - `lastSeen` INT(11) NOT NULL, -- v1.2, Until year 2038 + `lastSeen` INT(11) DEFAULT 0, -- v1.2, Until year 2038 `hash` BINARY(16), -- v1.2 `is_read` boolean NOT NULL DEFAULT 0, `is_favorite` boolean NOT NULL DEFAULT 0, diff --git a/app/install.php b/app/install.php index 177173fdb..86afb9318 100644 --- a/app/install.php +++ b/app/install.php @@ -168,8 +168,10 @@ function saveStep3() { $_SESSION['bd_prefix_user'] = $_SESSION['bd_prefix'] .(empty($_SESSION['default_user']) ? '' :($_SESSION['default_user'] . '_')); } + //TODO: load `config.default.php` as default $config_array = array( 'environment' => 'production', + 'simplepie_syslog_enabled' => true, 'salt' => $_SESSION['salt'], 'title' => $_SESSION['title'], 'default_user' => $_SESSION['default_user'], diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php index ac7a1bed7..3e8ec1f43 100644 --- a/lib/Minz/ModelPdo.php +++ b/lib/Minz/ModelPdo.php @@ -134,4 +134,9 @@ class MinzPDO extends PDO { MinzPDO::check($statement); return parent::exec($statement); } + + public function query($statement) { + MinzPDO::check($statement); + return parent::query($statement); + } } -- cgit v1.2.3 From 96ba71e618468f7d28a04c4ebc7c46dd912ccd75 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 31 May 2015 20:22:27 +0200 Subject: MySQL create table bug https://github.com/FreshRSS/FreshRSS/issues/845 And updated version comments to 1.1.1 --- app/Models/EntryDAO.php | 2 +- app/SQL/install.sql.mysql.php | 8 ++++---- app/SQL/install.sql.sqlite.php | 6 +++--- 3 files changed, 8 insertions(+), 8 deletions(-) (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index f939a0fb3..bd575989d 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -92,7 +92,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { 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] - . ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']. ' ' . $this->addEntryPrepared); + . ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']); } return false; } diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index 9c6af405d..c5787d25b 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -41,8 +41,8 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( `content_bin` blob, -- v0.7 `link` varchar(1023) CHARACTER SET latin1 NOT NULL, `date` int(11), -- Until year 2038 - `lastSeen` INT(11) DEFAULT 0, -- v1.2, Until year 2038 - `hash` BINARY(16), -- v1.2 + `lastSeen` INT(11) DEFAULT 0, -- v1.1.1, Until year 2038 + `hash` BINARY(16), -- v1.1.1 `is_read` boolean NOT NULL DEFAULT 0, `is_favorite` boolean NOT NULL DEFAULT 0, `id_feed` SMALLINT, -- v0.7 @@ -51,8 +51,8 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( FOREIGN KEY (`id_feed`) REFERENCES `%1$sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY (`id_feed`,`guid`), -- v0.7 INDEX (`is_favorite`), -- v0.7 - INDEX (`is_read`) -- v0.7 - INDEX entry_lastSeen_index (`lastSeen`) -- v1.2 + INDEX (`is_read`), -- v0.7 + INDEX `entry_lastSeen_index` (`lastSeen`) -- v1.1.1 ) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = INNODB; diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 77e8e094c..71bad7311 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -39,8 +39,8 @@ $SQL_CREATE_TABLES = array( `content` text, `link` varchar(1023) NOT NULL, `date` int(11), -- Until year 2038 - `lastSeen` INT(11) DEFAULT 0, -- v1.2, Until year 2038 - `hash` BINARY(16), -- v1.2 + `lastSeen` INT(11) DEFAULT 0, -- v1.1.1, Until year 2038 + `hash` BINARY(16), -- v1.1.1 `is_read` boolean NOT NULL DEFAULT 0, `is_favorite` boolean NOT NULL DEFAULT 0, `id_feed` SMALLINT, @@ -52,7 +52,7 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX IF NOT EXISTS entry_is_favorite_index ON `%1$sentry`(`is_favorite`);', 'CREATE INDEX IF NOT EXISTS entry_is_read_index ON `%1$sentry`(`is_read`);', -'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `%1$sentry`(`lastSeen`);', //v1.2 +'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `%1$sentry`(`lastSeen`);', //v1.1.1 'INSERT OR IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");', ); -- cgit v1.2.3 From 9f0b8ce70d84182676eab89e1c681e5d8bbf7539 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Fri, 3 Jul 2015 23:29:46 +0200 Subject: Add default feeds https://github.com/FreshRSS/FreshRSS/issues/859 --- app/SQL/install.sql.mysql.php | 2 ++ app/SQL/install.sql.sqlite.php | 2 ++ 2 files changed, 4 insertions(+) (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index c5787d25b..0f4e04620 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -57,6 +57,8 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( ENGINE = INNODB; INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s"); +INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("http://freshrss.org/feeds/all.atom.xml", 1, "FreshRSS.org", "http://freshrss.org/", "FreshRSS, a free, self-hostable aggregator…", 86400); +INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS @ GitHub", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400); '); define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory'); diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 71bad7311..87d5cf286 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -55,6 +55,8 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `%1$sentry`(`lastSeen`);', //v1.1.1 'INSERT OR IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");', +'INSERT OR IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("http://freshrss.org/feeds/all.atom.xml", 1, "FreshRSS.org", "http://freshrss.org/", "FreshRSS, a free, self-hostable aggregator…", 86400);', +'INSERT OR IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS releases", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400);', ); define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory'); -- cgit v1.2.3 From 9b22272eee6e37963f6ed5f60b9f82382b23adc2 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 1 Oct 2016 20:54:08 +0200 Subject: PostgreSQL multiuser bug https://github.com/FreshRSS/FreshRSS/pull/1195 --- app/Models/UserDAO.php | 5 +++-- app/SQL/install.sql.pgsql.php | 12 ++++++------ app/SQL/install.sql.sqlite.php | 32 ++++++++++++++++---------------- 3 files changed, 25 insertions(+), 24 deletions(-) (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/Models/UserDAO.php b/app/Models/UserDAO.php index b55766ab4..e35be848c 100644 --- a/app/Models/UserDAO.php +++ b/app/Models/UserDAO.php @@ -8,8 +8,9 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { $userPDO = new Minz_ModelPdo($username); $ok = false; + $bd_prefix_user = $db['prefix'] . $username . '_'; if (defined('SQL_CREATE_TABLES')) { //E.g. MySQL - $sql = sprintf(SQL_CREATE_TABLES, $db['prefix'] . $username . '_', _t('gen.short.default_category')); + $sql = sprintf(SQL_CREATE_TABLES, $bd_prefix_user, _t('gen.short.default_category')); $stm = $userPDO->bd->prepare($sql); $ok = $stm && $stm->execute(); } else { //E.g. SQLite @@ -17,7 +18,7 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { if (is_array($SQL_CREATE_TABLES)) { $ok = true; foreach ($SQL_CREATE_TABLES as $instruction) { - $sql = sprintf($instruction, '', _t('gen.short.default_category')); + $sql = sprintf($instruction, $bd_prefix_user, _t('gen.short.default_category')); $stm = $userPDO->bd->prepare($sql); $ok &= ($stm && $stm->execute()); } diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php index e60c5a07b..841003def 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -26,9 +26,9 @@ $SQL_CREATE_TABLES = array( "cache_nbUnreads" INT DEFAULT 0, FOREIGN KEY ("category") REFERENCES "%1$scategory" ("id") ON DELETE SET NULL ON UPDATE CASCADE );', -'CREATE INDEX name_index ON "%1$sfeed" ("name");', -'CREATE INDEX priority_index ON "%1$sfeed" ("priority");', -'CREATE INDEX keep_history_index ON "%1$sfeed" ("keep_history");', +'CREATE INDEX %1$sname_index ON "%1$sfeed" ("name");', +'CREATE INDEX %1$spriority_index ON "%1$sfeed" ("priority");', +'CREATE INDEX %1$skeep_history_index ON "%1$sfeed" ("keep_history");', 'CREATE TABLE IF NOT EXISTS "%1$sentry" ( "id" BIGINT NOT NULL PRIMARY KEY, @@ -47,9 +47,9 @@ $SQL_CREATE_TABLES = array( FOREIGN KEY ("id_feed") REFERENCES "%1$sfeed" ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("id_feed","guid") );', -'CREATE INDEX is_favorite_index ON "%1$sentry" ("is_favorite");', -'CREATE INDEX is_read_index ON "%1$sentry" ("is_read");', -'CREATE INDEX entry_lastSeen_index ON "%1$sentry" ("lastSeen");', +'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");', 'INSERT INTO "%1$scategory" (id, name) VALUES(1, \'%2$s\');', 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) VALUES(\'http://freshrss.org/feeds/all.atom.xml\', 1, \'FreshRSS.org\', \'http://freshrss.org/\', \'FreshRSS, a free, self-hostable aggregator…\', 86400);', diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 87d5cf286..9d146d6f0 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -1,16 +1,16 @@ Date: Sat, 15 Oct 2016 20:01:48 +0200 Subject: SQL DROP TABLE https://github.com/FreshRSS/FreshRSS/issues/1320 --- app/SQL/install.sql.mysql.php | 2 +- app/SQL/install.sql.pgsql.php | 2 +- app/SQL/install.sql.sqlite.php | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index 92a00aecc..ca181303e 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -63,7 +63,7 @@ INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) V INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS @ GitHub", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400); '); -define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory'); +define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS `%1$sentry`, `%1$sfeed`, `%1$scategory`'); define('SQL_UPDATE_UTF8MB4', ' ALTER DATABASE `%2$s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php index 91c5b257d..0b86908cd 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -56,4 +56,4 @@ $SQL_CREATE_TABLES = array( 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'https://github.com/FreshRSS/FreshRSS/releases.atom\', 1, \'FreshRSS @ GitHub\', \'https://github.com/FreshRSS/FreshRSS/\', \'FreshRSS releases @ GitHub\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://github.com/FreshRSS/FreshRSS/releases.atom\');', ); -define('SQL_DROP_TABLES', 'DROP TABLES "%1$sentry", "%1$sfeed", "%1$scategory"'); +define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS "%1$sentry", "%1$sfeed", "%1$scategory"'); diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 9d146d6f0..1d3a5d92f 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -59,4 +59,4 @@ $SQL_CREATE_TABLES = array( 'INSERT OR IGNORE INTO `feed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS releases", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400);', ); -define('SQL_DROP_TABLES', 'DROP TABLES entry, feed, category'); +define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS entry, feed, category'); -- cgit v1.2.3 From 1182129ce5f07892afed190ffbb2ea4c7fc28967 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Mon, 24 Oct 2016 20:29:08 +0200 Subject: CLI option no-default-feeds https://github.com/FreshRSS/FreshRSS/issues/1095 --- app/Controllers/userController.php | 4 ++-- app/Models/UserDAO.php | 18 +++++++++++++++++- app/SQL/install.sql.mysql.php | 3 +++ app/SQL/install.sql.pgsql.php | 4 ++++ app/SQL/install.sql.sqlite.php | 4 ++++ app/install.php | 15 +++++++++++++++ cli/create-user.php | 7 +++++-- 7 files changed, 50 insertions(+), 5 deletions(-) (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/Controllers/userController.php b/app/Controllers/userController.php index 9dee16e8c..9d6ae18e6 100644 --- a/app/Controllers/userController.php +++ b/app/Controllers/userController.php @@ -99,7 +99,7 @@ class FreshRSS_user_Controller extends Minz_ActionController { $this->view->size_user = $entryDAO->size(); } - public static function createUser($new_user_name, $passwordPlain, $apiPasswordPlain, $userConfig = array()) { + public static function createUser($new_user_name, $passwordPlain, $apiPasswordPlain, $userConfig = array(), $insertDefaultFeeds = true) { if (!is_array($userConfig)) { $userConfig = array(); } @@ -138,7 +138,7 @@ class FreshRSS_user_Controller extends Minz_ActionController { } if ($ok) { $userDAO = new FreshRSS_UserDAO(); - $ok &= $userDAO->createUser($new_user_name, $userConfig['language']); + $ok &= $userDAO->createUser($new_user_name, $userConfig['language'], $insertDefaultFeeds); } return $ok; } diff --git a/app/Models/UserDAO.php b/app/Models/UserDAO.php index 597182693..a95ee6bc4 100644 --- a/app/Models/UserDAO.php +++ b/app/Models/UserDAO.php @@ -1,7 +1,7 @@ db; require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); @@ -28,6 +28,22 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { } } } + if ($insertDefaultFeeds) { + if (defined('SQL_INSERT_FEEDS')) { //E.g. MySQL + $sql = sprintf(SQL_INSERT_FEEDS, $bd_prefix_user); + $stm = $userPDO->bd->prepare($sql); + $ok &= $stm && $stm->execute(); + } else { //E.g. SQLite + global $SQL_INSERT_FEEDS; + if (is_array($SQL_INSERT_FEEDS)) { + foreach ($SQL_INSERT_FEEDS as $instruction) { + $sql = sprintf($instruction, $bd_prefix_user); + $stm = $userPDO->bd->prepare($sql); + $ok &= ($stm && $stm->execute()); + } + } + } + } } catch (Exception $e) { Minz_Log::error('Error while creating user: ' . $e->getMessage()); } diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index ca181303e..a454829d5 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -59,6 +59,9 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( ENGINE = INNODB; INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s"); +'); + +define('SQL_INSERT_FEEDS', ' INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("http://freshrss.org/feeds/all.atom.xml", 1, "FreshRSS.org", "http://freshrss.org/", "FreshRSS, a free, self-hostable aggregator…", 86400); INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS @ GitHub", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400); '); diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php index b343bda86..9f4240b98 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -52,6 +52,10 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX %1$sentry_lastSeen_index ON "%1$sentry" ("lastSeen");', 'INSERT INTO "%1$scategory" (name) SELECT \'%2$s\' WHERE NOT EXISTS (SELECT id FROM "%1$scategory" WHERE id = 1);', +); + +global $SQL_INSERT_FEEDS; +$SQL_INSERT_FEEDS = array( 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'http://freshrss.org/feeds/all.atom.xml\', 1, \'FreshRSS.org\', \'http://freshrss.org/\', \'FreshRSS, a free, self-hostable aggregator…\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'http://freshrss.org/feeds/all.atom.xml\');', 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'https://github.com/FreshRSS/FreshRSS/releases.atom\', 1, \'FreshRSS @ GitHub\', \'https://github.com/FreshRSS/FreshRSS/\', \'FreshRSS releases @ GitHub\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://github.com/FreshRSS/FreshRSS/releases.atom\');', ); diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 1d3a5d92f..68d93ba92 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -55,6 +55,10 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `entry`(`lastSeen`);', //v1.1.1 'INSERT OR IGNORE INTO `category` (id, name) VALUES(1, "%2$s");', +); + +global $SQL_INSERT_FEEDS; +$SQL_INSERT_FEEDS = array( 'INSERT OR IGNORE INTO `feed` (url, category, name, website, description, ttl) VALUES("http://freshrss.org/feeds/all.atom.xml", 1, "FreshRSS.org", "http://freshrss.org/", "FreshRSS, a free, self-hostable aggregator…", 86400);', 'INSERT OR IGNORE INTO `feed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS releases", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400);', ); diff --git a/app/install.php b/app/install.php index 6956761c7..0daa02b1b 100644 --- a/app/install.php +++ b/app/install.php @@ -357,6 +357,21 @@ function checkDbUser(&$dbOptions) { } } } + + if (defined('SQL_INSERT_FEEDS')) { + $sql = sprintf(SQL_INSERT_FEEDS, $dbOptions['bd_prefix_user']); + $stm = $c->prepare($sql); + $ok &= $stm->execute(); + } else { + global $SQL_INSERT_FEEDS; + if (is_array($SQL_INSERT_FEEDS)) { + foreach ($SQL_INSERT_FEEDS as $instruction) { + $sql = sprintf($instruction, $dbOptions['bd_prefix_user']); + $stm = $c->prepare($sql); + $ok &= $stm->execute(); + } + } + } } catch (PDOException $e) { $ok = false; $dbOptions['bd_error'] = $e->getMessage(); diff --git a/cli/create-user.php b/cli/create-user.php index 5e93d4605..c790acb59 100755 --- a/cli/create-user.php +++ b/cli/create-user.php @@ -9,11 +9,12 @@ $options = getopt('', array( 'language:', 'email:', 'token:', + 'no-default-feeds', )); if (empty($options['user'])) { fail('Usage: ' . basename(__FILE__) . " --user username ( --password 'password' --api-password 'api_password'" . - " --language en --email user@example.net --token 'longRandomString' )"); + " --language en --email user@example.net --token 'longRandomString --no-default-feeds' )"); } $username = $options['user']; if (!ctype_alnum($username)) { @@ -33,7 +34,9 @@ $ok = FreshRSS_user_Controller::createUser($username, array( 'language' => empty($options['language']) ? '' : $options['language'], 'token' => empty($options['token']) ? '' : $options['token'], - )); + ), + !isset($options['no-default-feeds']) + ); if (!$ok) { fail('FreshRSS could not create user!'); -- cgit v1.2.3