From d6f414108667f32fe2b480adeb7ec9c218db2f4a Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 3 Jul 2014 21:26:30 +0200 Subject: Preparation for SQLite https://github.com/marienfressinaud/FreshRSS/issues/100 --- app/SQL/install.sql.sqlite.php | 57 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 57 insertions(+) create mode 100644 app/SQL/install.sql.sqlite.php (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php new file mode 100644 index 000000000..5bd294fde --- /dev/null +++ b/app/SQL/install.sql.sqlite.php @@ -0,0 +1,57 @@ + Date: Thu, 3 Jul 2014 22:11:25 +0200 Subject: Preparation #2 for SQLite https://github.com/marienfressinaud/FreshRSS/issues/100 --- app/Models/EntryDAOSQLite.php | 42 ++++++++++++++++++++++++++++++++++++++++++ app/Models/EntryDAO_SQLite.php | 42 ------------------------------------------ app/Models/Factory.php | 2 +- app/SQL/install.sql.sqlite.php | 2 +- app/install.php | 16 ++++++++++++---- 5 files changed, 56 insertions(+), 48 deletions(-) create mode 100644 app/Models/EntryDAOSQLite.php delete mode 100644 app/Models/EntryDAO_SQLite.php (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php new file mode 100644 index 000000000..45d3a3ea9 --- /dev/null +++ b/app/Models/EntryDAOSQLite.php @@ -0,0 +1,42 @@ +markRead($id, $is_read); + } + return $affected; + } + } else { + $this->bd->beginTransaction(); + $sql = 'UPDATE `' . $this->prefix . 'entry` e SET e.is_read = ? WHERE e.id=? AND e.is_read<>?'; + $values = array($is_read ? 1 : 0, $ids, $is_read ? 1 : 0); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute ($values))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack (); + return false; + } + $affected = $stm->rowCount(); + if ($affected > 0) { + $sql = 'UPDATE `' . $this->prefix . 'feed` f SET f.cache_nbUnreads=f.cache_nbUnreads' . ($is_read ? '-' : '+') . '1 ' + . 'WHERE f.id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)'; + $values = array($ids); + $stm = $this->bd->prepare($sql); + if (!($stm && $stm->execute ($values))) { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); + $this->bd->rollBack (); + return false; + } + } + $this->bd->commit(); + return $affected; + } + } +} diff --git a/app/Models/EntryDAO_SQLite.php b/app/Models/EntryDAO_SQLite.php deleted file mode 100644 index f148f3c63..000000000 --- a/app/Models/EntryDAO_SQLite.php +++ /dev/null @@ -1,42 +0,0 @@ -markRead($id, $is_read); - } - return $affected; - } - } else { - $this->bd->beginTransaction(); - $sql = 'UPDATE `' . $this->prefix . 'entry` e SET e.is_read = ? WHERE e.id=? AND e.is_read<>?'; - $values = array($is_read ? 1 : 0, $ids, $is_read ? 1 : 0); - $stm = $this->bd->prepare($sql); - if (!($stm && $stm->execute ($values))) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); - return false; - } - $affected = $stm->rowCount(); - if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` f SET f.cache_nbUnreads=f.cache_nbUnreads' . ($is_read ? '-' : '+') . '1 ' - . 'WHERE f.id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)'; - $values = array($ids); - $stm = $this->bd->prepare($sql); - if (!($stm && $stm->execute ($values))) { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record('SQL error markRead: ' . $info[2], Minz_Log::ERROR); - $this->bd->rollBack (); - return false; - } - } - $this->bd->commit(); - return $affected; - } - } -} diff --git a/app/Models/Factory.php b/app/Models/Factory.php index bea89c114..3ef68c41d 100644 --- a/app/Models/Factory.php +++ b/app/Models/Factory.php @@ -5,7 +5,7 @@ class FreshRSS_Factory { public static function createEntryDao() { $db = Minz_Configuration::dataBase(); if ($db['type'] === 'sqlite') { - return new FreshRSS_EntryDAO_SQLite(); + return new FreshRSS_EntryDAOSQLite(); } else { return new FreshRSS_EntryDAO(); } diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 5bd294fde..8cdec981f 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -49,7 +49,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`);', -'INSERT OR IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%1$s");', +'INSERT OR IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");', ); define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory'); diff --git a/app/install.php b/app/install.php index 9863dd556..357a8e340 100644 --- a/app/install.php +++ b/app/install.php @@ -366,7 +366,7 @@ function newPdo() { ); break; case 'sqlite': - $str = 'sqlite:' . DATA_PATH . '/' . $_SESSION['bd_base'] . '.sqlite'; + $str = 'sqlite:' . DATA_PATH . '/' . $_SESSION['default_user'] . '.sqlite'; $driver_options = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, ); @@ -407,7 +407,7 @@ function postUpdate() { } function deleteInstall () { - $res = unlink (DATA_PATH . 'do-install.txt'); + $res = unlink (DATA_PATH . '/do-install.txt'); if ($res) { header ('Location: index.php'); } @@ -665,7 +665,7 @@ function checkBD () { $str = 'mysql:host=' . $_SESSION['bd_host'] . ';dbname=' . $_SESSION['bd_base']; break; case 'sqlite': - $str = 'sqlite:' . DATA_PATH . '/' . $_SESSION['bd_base'] . '.sqlite'; + $str = 'sqlite:' . DATA_PATH . '/' . $_SESSION['default_user'] . '.sqlite'; $driver_options = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, ); @@ -921,7 +921,7 @@ function printStep3 () {
-
+
@@ -968,6 +969,13 @@ function printStep3 () {
+
+
-- cgit v1.2.3 From 1b43167fb8206dfd17923c389de1e3bd6b76045e Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 5 Jul 2014 01:20:45 +0200 Subject: SQL: Add f.ttl column to control feed cache duration Preparation of https://github.com/marienfressinaud/FreshRSS/issues/250 Will also be used to disable automatic update for selected feeds --- app/SQL/install.sql.mysql.php | 1 + app/SQL/install.sql.sqlite.php | 1 + 2 files changed, 2 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 d509cb4a8..16cb3a3b8 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -21,6 +21,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` ( `httpAuth` varchar(511) DEFAULT NULL, `error` boolean DEFAULT 0, `keep_history` MEDIUMINT NOT NULL DEFAULT -2, -- v0.7 + `ttl` INT NOT NULL DEFAULT -2, -- v0.7.3 `cache_nbEntries` int DEFAULT 0, -- v0.7 `cache_nbUnreads` int DEFAULT 0, -- v0.7 PRIMARY KEY (`id`), diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 8cdec981f..b90a5ef5e 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -19,6 +19,7 @@ $SQL_CREATE_TABLES = array( `httpAuth` varchar(511) DEFAULT NULL, `error` boolean DEFAULT 0, `keep_history` MEDIUMINT NOT NULL DEFAULT -2, + `ttl` INT NOT NULL DEFAULT -2, `cache_nbEntries` int DEFAULT 0, `cache_nbUnreads` int DEFAULT 0, FOREIGN KEY (`%1$scategory`) REFERENCES `%1$scategory`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, -- cgit v1.2.3 From d477373ef2879bdeeaa3c157287c0fab98afefdc Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 9 Aug 2014 19:58:39 +0200 Subject: SQLite: Bug creation new users Not tested much yet. Some MySQL parts changed a bit too to double-check. https://github.com/marienfressinaud/FreshRSS/issues/574 --- app/Models/UserDAO.php | 35 ++++++++++++++++++++++------------- app/SQL/install.sql.sqlite.php | 1 + lib/Minz/ModelPdo.php | 20 ++++++++++++-------- 3 files changed, 35 insertions(+), 21 deletions(-) (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/Models/UserDAO.php b/app/Models/UserDAO.php index 1763fac67..9f64fb4a7 100644 --- a/app/Models/UserDAO.php +++ b/app/Models/UserDAO.php @@ -4,18 +4,21 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { public function createUser($username) { $db = Minz_Configuration::dataBase(); require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); - - if (defined('SQL_CREATE_TABLES')) { + + $userPDO = new Minz_ModelPdo($username); + + $ok = false; + if (defined('SQL_CREATE_TABLES')) { //E.g. MySQL $sql = sprintf(SQL_CREATE_TABLES, $db['prefix'] . $username . '_', Minz_Translate::t('default_category')); - $stm = $this->bd->prepare($sql); + $stm = $userPDO->bd->prepare($sql); $ok = $stm && $stm->execute(); - } else { + } else { //E.g. SQLite global $SQL_CREATE_TABLES; if (is_array($SQL_CREATE_TABLES)) { $ok = true; foreach ($SQL_CREATE_TABLES as $instruction) { $sql = sprintf($instruction, '', Minz_Translate::t('default_category')); - $stm = $c->prepare($sql); + $stm = $userPDO->bd->prepare($sql); $ok &= ($stm && $stm->execute()); } } @@ -24,7 +27,7 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { if ($ok) { return true; } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + $info = empty($stm) ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); return false; } @@ -34,14 +37,20 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { $db = Minz_Configuration::dataBase(); require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); - $sql = sprintf(SQL_DROP_TABLES, $db['prefix'] . $username . '_'); - $stm = $this->bd->prepare($sql); - if ($stm && $stm->execute()) { - return true; + if ($db['type'] === 'sqlite') { + return unlink(DATA_PATH . '/' . $username . '.sqlite'); } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); - return false; + $userPDO = new Minz_ModelPdo($username); + + $sql = sprintf(SQL_DROP_TABLES, $db['prefix'] . $username . '_'); + $stm = $userPDO->bd->prepare($sql); + if ($stm && $stm->execute()) { + return true; + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::record ('SQL error : ' . $info[2], Minz_Log::ERROR); + return false; + } } } } diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index b90a5ef5e..7988ada04 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -1,4 +1,5 @@ bd = self::$sharedBd; $this->prefix = self::$sharedPrefix; return; @@ -42,6 +42,10 @@ class Minz_ModelPdo { $db = Minz_Configuration::dataBase(); + if ($currentUser === null) { + $currentUser = Minz_Session::param('currentUser', '_'); + } + try { $type = $db['type']; if ($type === 'mysql') { @@ -51,9 +55,9 @@ class Minz_ModelPdo { $driver_options = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', ); - $this->prefix = $db['prefix'] . Minz_Session::param('currentUser', '_') . '_'; + $this->prefix = $db['prefix'] . $currentUser . '_'; } elseif ($type === 'sqlite') { - $string = 'sqlite:' . DATA_PATH . '/' . Minz_Session::param('currentUser', '_') . '.sqlite'; + $string = 'sqlite:' . DATA_PATH . '/' . $currentUser . '.sqlite'; $driver_options = array( //PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, ); @@ -67,7 +71,7 @@ class Minz_ModelPdo { self::$sharedDbType = $type; self::$sharedPrefix = $this->prefix; - $this->bd = new FreshPDO( + $this->bd = new MinzPDO( $string, $db['user'], $db['password'], @@ -98,7 +102,7 @@ class Minz_ModelPdo { } } -class FreshPDO extends PDO { +class MinzPDO extends PDO { private static function check($statement) { if (preg_match('/^(?:UPDATE|INSERT|DELETE)/i', $statement)) { invalidateHttpCache(); @@ -106,12 +110,12 @@ class FreshPDO extends PDO { } public function prepare($statement, $driver_options = array()) { - FreshPDO::check($statement); + MinzPDO::check($statement); return parent::prepare($statement, $driver_options); } public function exec($statement) { - FreshPDO::check($statement); + MinzPDO::check($statement); return parent::exec($statement); } } -- cgit v1.2.3 From 7080a32650ab8b19e917d8add944a75cc98381bc Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Mon, 20 Oct 2014 11:54:31 +0200 Subject: Add checking installation feature --- app/Controllers/updateController.php | 14 +++++- app/Models/DatabaseDAO.php | 83 ++++++++++++++++++++++++++++++++++++ app/Models/DatabaseDAOSQLite.php | 48 +++++++++++++++++++++ app/Models/Factory.php | 9 ++++ app/SQL/install.sql.mysql.php | 2 - app/SQL/install.sql.sqlite.php | 2 - app/layout/aside_configure.phtml | 7 ++- app/layout/header.phtml | 1 + app/views/update/checkInstall.phtml | 30 +++++++++++++ lib/lib_rss.php | 62 +++++++++++++++++++++++++++ 10 files changed, 252 insertions(+), 6 deletions(-) create mode 100644 app/Models/DatabaseDAO.php create mode 100644 app/Models/DatabaseDAOSQLite.php create mode 100644 app/views/update/checkInstall.phtml (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/Controllers/updateController.php b/app/Controllers/updateController.php index 9d1e1ddf5..4ebb11f51 100644 --- a/app/Controllers/updateController.php +++ b/app/Controllers/updateController.php @@ -12,7 +12,6 @@ class FreshRSS_update_Controller extends Minz_ActionController { invalidateHttpCache(); - Minz_View::prependTitle(_t('update_system') . ' · '); $this->view->update_to_apply = false; $this->view->last_update_time = 'unknown'; $this->view->check_last_hour = false; @@ -24,6 +23,8 @@ class FreshRSS_update_Controller extends Minz_ActionController { } public function indexAction() { + Minz_View::prependTitle(_t('update_system') . ' · '); + if (file_exists(UPDATE_FILENAME) && !is_writable(FRESHRSS_PATH)) { $this->view->message = array( 'status' => 'bad', @@ -126,4 +127,15 @@ class FreshRSS_update_Controller extends Minz_ActionController { } } } + + /** + * This action displays information about installation. + */ + public function checkInstallAction() { + Minz_View::prependTitle(_t('gen.title.check_install') . ' · '); + + $this->view->status_php = check_install_php(); + $this->view->status_files = check_install_files(); + $this->view->status_database = check_install_database(); + } } diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php new file mode 100644 index 000000000..0d85718e3 --- /dev/null +++ b/app/Models/DatabaseDAO.php @@ -0,0 +1,83 @@ +bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + + $tables = array( + $this->prefix . 'category' => false, + $this->prefix . 'feed' => false, + $this->prefix . 'entry' => false, + ); + foreach ($res as $value) { + $tables[array_pop($value)] = true; + } + + return count(array_keys($tables, true, true)) == count($tables); + } + + public function getSchema($table) { + $sql = 'DESC ' . $this->prefix . $table; + $stm = $this->bd->prepare($sql); + $stm->execute(); + + return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC)); + } + + public function checkTable($table, $schema) { + $columns = $this->getSchema($table); + + $ok = (count($columns) == count($schema)); + foreach ($columns as $c) { + $ok &= in_array($c['name'], $schema); + } + + return $ok; + } + + public function categoryIsCorrect() { + return $this->checkTable('category', array( + 'id', 'name' + )); + } + + public function feedIsCorrect() { + return $this->checkTable('feed', array( + 'id', 'url', 'category', 'name', 'website', 'description', 'lastUpdate', + 'priority', 'pathEntries', 'httpAuth', 'error', 'keep_history', 'ttl', + 'cache_nbEntries', 'cache_nbUnreads' + )); + } + + public function entryIsCorrect() { + return $this->checkTable('entry', array( + 'id', 'guid', 'title', 'author', 'content_bin', 'link', 'date', 'is_read', + 'is_favorite', 'id_feed', 'tags' + )); + } + + public function daoToSchema($dao) { + return array( + 'name' => $dao['Field'], + 'type' => strtolower($dao['Type']), + 'notnull' => (bool)$dao['Null'], + 'default' => $dao['Default'], + ); + } + + public function listDaoToSchema($listDAO) { + $list = array(); + + foreach ($listDAO as $dao) { + $list[] = $this->daoToSchema($dao); + } + + return $list; + } +} diff --git a/app/Models/DatabaseDAOSQLite.php b/app/Models/DatabaseDAOSQLite.php new file mode 100644 index 000000000..7f53f967d --- /dev/null +++ b/app/Models/DatabaseDAOSQLite.php @@ -0,0 +1,48 @@ +bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + + $tables = array( + 'category' => false, + 'feed' => false, + 'entry' => false, + ); + foreach ($res as $value) { + $tables[$value['name']] = true; + } + + return count(array_keys($tables, true, true)) == count($tables); + } + + public function getSchema($table) { + $sql = 'PRAGMA table_info(' . $table . ')'; + $stm = $this->bd->prepare($sql); + $stm->execute(); + + return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC)); + } + + public function entryIsCorrect() { + return $this->checkTable('entry', array( + 'id', 'guid', 'title', 'author', 'content', 'link', 'date', 'is_read', + 'is_favorite', 'id_feed', 'tags' + )); + } + + public function daoToSchema($dao) { + return array( + 'name' => $dao['name'], + 'type' => strtolower($dao['type']), + 'notnull' => $dao['notnull'] === '1' ? true : false, + 'default' => $dao['dflt_value'], + ); + } +} diff --git a/app/Models/Factory.php b/app/Models/Factory.php index 93f4552f7..91cb84998 100644 --- a/app/Models/Factory.php +++ b/app/Models/Factory.php @@ -29,4 +29,13 @@ class FreshRSS_Factory { } } + public static function createDatabaseDAO($username = null) { + $db = Minz_Configuration::dataBase(); + if ($db['type'] === 'sqlite') { + return new FreshRSS_DatabaseDAOSQLite($username); + } else { + return new FreshRSS_DatabaseDAO($username); + } + } + } diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index 16cb3a3b8..cf0159199 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -57,5 +57,3 @@ INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s"); '); define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory'); - -define('SQL_SHOW_TABLES', 'SHOW tables;'); diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 7988ada04..30bca2810 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -55,5 +55,3 @@ $SQL_CREATE_TABLES = array( ); define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory'); - -define('SQL_SHOW_TABLES', 'SELECT name FROM sqlite_master WHERE type="table"'); diff --git a/app/layout/aside_configure.phtml b/app/layout/aside_configure.phtml index 20446c877..32dc19a4e 100644 --- a/app/layout/aside_configure.phtml +++ b/app/layout/aside_configure.phtml @@ -31,7 +31,12 @@
    • -
  • +
  • + +
    • +
    • diff --git a/app/layout/header.phtml b/app/layout/header.phtml index e848ac4eb..506cec175 100644 --- a/app/layout/header.phtml +++ b/app/layout/header.phtml @@ -68,6 +68,7 @@ if (Minz_Configuration::canLogIn()) {
    • +
  • diff --git a/app/views/update/checkInstall.phtml b/app/views/update/checkInstall.phtml new file mode 100644 index 000000000..32058714e --- /dev/null +++ b/app/views/update/checkInstall.phtml @@ -0,0 +1,30 @@ +partial('aside_configure'); ?> + +
    + + +

    + + status_php as $key => $status) { ?> +

    + +

    + + +

    + + status_files as $key => $status) { ?> +

    + +

    + + +

    + + status_database as $key => $status) { ?> +

    + +

    + + +
    diff --git a/lib/lib_rss.php b/lib/lib_rss.php index 2f9a2ea45..dbed207d0 100644 --- a/lib/lib_rss.php +++ b/lib/lib_rss.php @@ -245,3 +245,65 @@ function is_referer_from_same_domain() { } return (isset($host['port']) ? $host['port'] : 0) === (isset($referer['port']) ? $referer['port'] : 0); } + + +/** + * + */ +function check_install_php() { + return array( + 'php' => version_compare(PHP_VERSION, '5.2.1') >= 0, + 'minz' => file_exists(LIB_PATH . '/Minz'), + 'curl' => extension_loaded('curl'), + 'pdo_mysql' => extension_loaded('pdo_mysql'), + 'pdo_sqlite' => extension_loaded('pdo_sqlite'), + 'pdo' => extension_loaded('pdo_mysql') || extension_loaded('pdo_sqlite'), + 'pcre' => extension_loaded('pcre'), + 'ctype' => extension_loaded('ctype'), + 'dom' => class_exists('DOMDocument'), + 'json' => extension_loaded('json'), + 'zip' => extension_loaded('zip'), + ); +} + + +/** + * + */ +function check_install_files() { + return array( + 'data' => DATA_PATH && is_writable(DATA_PATH), + 'cache' => CACHE_PATH && is_writable(CACHE_PATH), + 'logs' => LOG_PATH && is_writable(LOG_PATH), + 'favicons' => is_writable(DATA_PATH . '/favicons'), + 'persona' => is_writable(DATA_PATH . '/persona'), + 'tokens' => is_writable(DATA_PATH . '/tokens'), + ); +} + + +/** + * + */ +function check_install_database() { + $status = array( + 'connection' => true, + 'tables' => false, + 'categories' => false, + 'feeds' => false, + 'entries' => false, + ); + + try { + $dbDAO = FreshRSS_Factory::createDatabaseDAO(); + + $status['tables'] = $dbDAO->tablesAreCorrect(); + $status['categories'] = $dbDAO->categoryIsCorrect(); + $status['feeds'] = $dbDAO->feedIsCorrect(); + $status['entries'] = $dbDAO->entryIsCorrect(); + } catch(Minz_PDOConnectionException $e) { + $status['connection'] = false; + } + + return $status; +} -- cgit v1.2.3 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/Controllers/feedController.php | 98 ++++++++------ app/Controllers/importExportController.php | 3 +- app/Models/Entry.php | 16 +++ app/Models/EntryDAO.php | 198 +++++++++++++++++++++-------- app/Models/Feed.php | 1 + app/SQL/install.sql.mysql.php | 7 +- app/SQL/install.sql.sqlite.php | 7 +- lib/lib_rss.php | 2 +- 8 files changed, 231 insertions(+), 101 deletions(-) (limited to 'app/SQL/install.sql.sqlite.php') diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 6f544d834..08a0257a2 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -145,7 +145,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { // Call the extension hook $name = $feed->name(); $feed = Minz_ExtensionManager::callHook('feed_before_insert', $feed); - if (is_null($feed)) { + if ($feed === null) { Minz_Request::bad(_t('feed_not_added', $name), $url_redirect); } @@ -181,7 +181,6 @@ class FreshRSS_feed_Controller extends Minz_ActionController { // Use a shared statement and a transaction to improve a LOT the // performances. - $prepared_statement = $entryDAO->addEntryPrepare(); $feedDAO->beginTransaction(); foreach ($entries as $entry) { // Entries are added without any verification. @@ -190,13 +189,13 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $entry->_isRead($is_read); $entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry); - if (is_null($entry)) { + if ($entry === null) { // An extension has returned a null value, there is nothing to insert. continue; } $values = $entry->toArray(); - $entryDAO->addEntry($values, $prepared_statement); + $entryDAO->addEntry($values); } $feedDAO->updateLastUpdate($feed->id()); $feedDAO->commit(); @@ -307,7 +306,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $feed->load(false); } catch (FreshRSS_Feed_Exception $e) { Minz_Log::notice($e->getMessage()); - $feedDAO->updateLastUpdate($feed->id(), 1); + $feedDAO->updateLastUpdate($feed->id(), true); $feed->unlock(); continue; } @@ -323,50 +322,69 @@ class FreshRSS_feed_Controller extends Minz_ActionController { // We want chronological order and SimplePie uses reverse order. $entries = array_reverse($feed->entries()); if (count($entries) > 0) { - // For this feed, check last n entry GUIDs already in database. - $existing_guids = array_fill_keys($entryDAO->listLastGuidsByFeed( - $feed->id(), count($entries) + 10 - ), 1); - $use_declared_date = empty($existing_guids); + $newGuids = array(); + foreach ($entries as $entry) { + $newGuids[] = $entry->guid(); + } + // For this feed, check existing GUIDs already in database. + $existingHashForGuids = $entryDAO->listHashForFeedGuids($feed->id(), $newGuids); + unset($newGuids); + $use_declared_date = empty($existingHashForGuids); + $oldGuids = array(); // Add entries in database if possible. - $prepared_statement = $entryDAO->addEntryPrepare(); - $feedDAO->beginTransaction(); foreach ($entries as $entry) { $entry_date = $entry->date(true); - if (isset($existing_guids[$entry->guid()]) || - ($feed_history == 0 && $entry_date < $date_min)) { - // This entry already exists in DB or should not be added - // considering configuration and date. - continue; - } - - $id = uTimeString(); - if ($use_declared_date || $entry_date < $date_min) { - // Use declared date at first import. - $id = min(time(), $entry_date) . uSecString(); + if (isset($existingHashForGuids[$entry->guid()])) { + $existingHash = $existingHashForGuids[$entry->guid()]; + if (strcasecmp($existingHash, $entry->hash()) === 0 || $existingHash === '00000000000000000000000000000000') { + //This entry already exists and is unchanged. TODO: Remove the test with the zero'ed hash in FreshRSS v1.3 + $oldGuids[] = $entry->guid(); + } else { //This entry already exists but has been updated + Minz_Log::debug('Entry with GUID `' . $entry->guid() . '` updated in feed ' . $feed->id() . + ', old hash ' . $existingHash . ', new hash ' . $entry->hash()); + $entry->_isRead($is_read); //Reset is_read + if (!$entryDAO->hasTransaction()) { + $entryDAO->beginTransaction(); + } + $entryDAO->updateEntry($entry->toArray()); + } + } elseif ($feed_history == 0 && $entry_date < $date_min) { + // This entry should not be added considering configuration and date. + $oldGuids[] = $entry->guid(); + } else { + $id = uTimeString(); + if ($use_declared_date || $entry_date < $date_min) { + // Use declared date at first import. + $id = min(time(), $entry_date) . uSecString(); + } + + $entry->_id($id); + $entry->_isRead($is_read); + + $entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry); + if ($entry === null) { + // An extension has returned a null value, there is nothing to insert. + continue; + } + + if (!$entryDAO->hasTransaction()) { + $entryDAO->beginTransaction(); + } + $entryDAO->addEntry($entry->toArray()); } - - $entry->_id($id); - $entry->_isRead($is_read); - - $entry = Minz_ExtensionManager::callHook('entry_before_insert', $entry); - if (is_null($entry)) { - // An extension has returned a null value, there is nothing to insert. - continue; - } - - $values = $entry->toArray(); - $entryDAO->addEntry($values, $prepared_statement); } + $entryDAO->updateLastSeen($feed->id(), $oldGuids); } + //TODO: updateLastSeen old GUIDS once in a while, in the case of caching (i.e. the whole feed content has not changed) if ($feed_history >= 0 && rand(0, 30) === 1) { // TODO: move this function in web cron when available (see entry::purge) // Remove old entries once in 30. - if (!$feedDAO->hasTransaction()) { - $feedDAO->beginTransaction(); + if (!$entryDAO->hasTransaction()) { + $entryDAO->beginTransaction(); } + //TODO: more robust system based on entry.lastSeen to avoid cleaning entries that are still published in the RSS feed. $nb = $feedDAO->cleanOldEntries($feed->id(), $date_min, @@ -377,9 +395,9 @@ class FreshRSS_feed_Controller extends Minz_ActionController { } } - $feedDAO->updateLastUpdate($feed->id(), 0, $feedDAO->hasTransaction()); - if ($feedDAO->hasTransaction()) { - $feedDAO->commit(); + $feedDAO->updateLastUpdate($feed->id(), 0, $entryDAO->hasTransaction()); + if ($entryDAO->hasTransaction()) { + $entryDAO->commit(); } if ($feed->url() !== $url) { diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index 589777b2a..26b163e43 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -361,7 +361,6 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { } // Then, articles are imported. - $prepared_statement = $this->entryDAO->addEntryPrepare(); $this->entryDAO->beginTransaction(); foreach ($article_object['items'] as $item) { if (!isset($article_to_feed[$item['id']])) { @@ -396,7 +395,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { } $values = $entry->toArray(); - $id = $this->entryDAO->addEntry($values, $prepared_statement); + $id = $this->entryDAO->addEntry($values); if (!$error && ($id === false)) { $error = true; diff --git a/app/Models/Entry.php b/app/Models/Entry.php index 346c98a92..6931c9f25 100644 --- a/app/Models/Entry.php +++ b/app/Models/Entry.php @@ -14,6 +14,7 @@ class FreshRSS_Entry extends Minz_Model { private $content; private $link; private $date; + private $hash = null; private $is_read; private $is_favorite; private $feed; @@ -88,6 +89,14 @@ class FreshRSS_Entry extends Minz_Model { } } + public function hash() { + if ($this->hash === null) { + //Do not include $this->date because it may be automatically generated when lacking + $this->hash = md5($this->link . $this->title . $this->author . $this->content . $this->tags(true)); + } + return $this->hash; + } + public function _id($value) { $this->id = $value; } @@ -95,18 +104,23 @@ class FreshRSS_Entry extends Minz_Model { $this->guid = $value; } public function _title($value) { + $this->hash = null; $this->title = $value; } public function _author($value) { + $this->hash = null; $this->author = $value; } public function _content($value) { + $this->hash = null; $this->content = $value; } public function _link($value) { + $this->hash = null; $this->link = $value; } public function _date($value) { + $this->hash = null; $value = intval($value); $this->date = $value > 1 ? $value : time(); } @@ -120,6 +134,7 @@ class FreshRSS_Entry extends Minz_Model { $this->feed = $value; } public function _tags($value) { + $this->hash = null; if (!is_array($value)) { $value = array($value); } @@ -182,6 +197,7 @@ class FreshRSS_Entry extends Minz_Model { 'content' => $this->content(), 'link' => $this->link(), 'date' => $this->date(true), + 'hash' => $this->hash(), 'is_read' => $this->isRead(), 'is_favorite' => $this->isFavorite(), 'id_feed' => $this->feed(), diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 9736d5cd3..5b4b85547 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -6,20 +6,57 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return parent::$sharedDbType !== 'sqlite'; } - public function addEntryPrepare() { - $sql = 'INSERT INTO `' . $this->prefix . 'entry`(id, guid, title, author, ' - . ($this->isCompressed() ? 'content_bin' : 'content') - . ', link, date, is_read, is_favorite, id_feed, tags) ' - . 'VALUES(?, ?, ?, ?, ' - . ($this->isCompressed() ? 'COMPRESS(?)' : '?') - . ', ?, ?, ?, ?, ?, ?)'; - return $this->bd->prepare($sql); + 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; + } + } + if ($hasTransaction) { + $this->bd->rollBack(); + } + } 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(); + } + } catch (Exception $e) { + Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn error: ' . $e->getMessage()); + if ($hasTransaction) { + $this->bd->rollBack(); + } + } + } + } + return false; } - public function addEntry($valuesTmp, $preparedStatement = null) { - $stm = $preparedStatement === null ? - FreshRSS_EntryDAO::addEntryPrepare() : - $preparedStatement; + private $addEntryPrepared = null; + + public function addEntry($valuesTmp) { + if ($this->addEntryPrepared === null) { + $sql = 'INSERT INTO `' . $this->prefix . 'entry` (id, guid, title, author, ' + . ($this->isCompressed() ? 'content_bin' : 'content') + . ', link, date, lastSeen, hash, is_read, is_favorite, id_feed, tags) ' + . 'VALUES(?, ?, ?, ?, ' + . ($this->isCompressed() ? 'COMPRESS(?)' : '?') + . ', ?, ?, ?, X?, ?, ?, ?, ?)'; + $this->addEntryPrepared = $this->bd->prepare($sql); + } $values = array( $valuesTmp['id'], @@ -29,55 +66,65 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $valuesTmp['content'], substr($valuesTmp['link'], 0, 1023), $valuesTmp['date'], + time(), + $valuesTmp['hash'], $valuesTmp['is_read'] ? 1 : 0, $valuesTmp['is_favorite'] ? 1 : 0, $valuesTmp['id_feed'], substr($valuesTmp['tags'], 0, 1023), ); - if ($stm && $stm->execute($values)) { + if ($this->addEntryPrepared && $this->addEntryPrepared->execute($values)) { return $this->bd->lastInsertId(); } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - if ((int)($info[0] / 1000) !== 23) { //Filter out "SQLSTATE Class code 23: Constraint Violation" because of expected duplicate entries + $info = $this->addEntryPrepared == null ? array(2 => 'syntax error') : $this->addEntryPrepared->errorInfo(); + if ($this->autoAddColumn($info)) { + 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']); - } /*else { - Minz_Log::debug('SQL error ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] - . ' while adding entry in feed ' . $valuesTmp['id_feed'] . ' with title: ' . $valuesTmp['title']); - }*/ + } return false; } } - public function addEntryObject($entry, $conf, $feedHistory) { - $existingGuids = array_fill_keys( - $this->listLastGuidsByFeed($entry->feed(), 20), 1 - ); - - $nb_month_old = max($conf->old_entries, 1); - $date_min = time() - (3600 * 24 * 30 * $nb_month_old); + private $updateEntryPrepared = null; - $eDate = $entry->date(true); - - if ($feedHistory == -2) { - $feedHistory = $conf->keep_history_default; + public function updateEntry($valuesTmp) { + if ($this->updateEntryPrepared === null) { + $sql = 'UPDATE `' . $this->prefix . 'entry` ' + . 'SET title=?, author=?, ' + . ($this->isCompressed() ? 'content_bin=COMPRESS(?)' : 'content=?') + . ', link=?, date=?, lastSeen=?, hash=X?, is_read=?, tags=? ' + . 'WHERE id_feed=? AND guid=?'; + $this->updateEntryPrepared = $this->bd->prepare($sql); } - if (!isset($existingGuids[$entry->guid()]) && - ($feedHistory != 0 || $eDate >= $date_min || $entry->isFavorite())) { - $values = $entry->toArray(); - - $useDeclaredDate = empty($existingGuids); - $values['id'] = ($useDeclaredDate || $eDate < $date_min) ? - min(time(), $eDate) . uSecString() : - uTimeString(); + $values = array( + substr($valuesTmp['title'], 0, 255), + substr($valuesTmp['author'], 0, 255), + $valuesTmp['content'], + substr($valuesTmp['link'], 0, 1023), + $valuesTmp['date'], + time(), + $valuesTmp['hash'], + $valuesTmp['is_read'] ? 1 : 0, + substr($valuesTmp['tags'], 0, 1023), + $valuesTmp['id_feed'], + substr($valuesTmp['guid'], 0, 760), + ); - return $this->addEntry($values); + if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute($values)) { + return $this->bd->lastInsertId(); + } else { + $info = $this->updateEntryPrepared == null ? array(2 => 'syntax error') : $this->updateEntryPrepared->errorInfo(); + if ($this->autoAddColumn($info)) { + return $this->updateEntry($valuesTmp); + } + Minz_Log::error('SQL error updateEntry: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] + . ' while updating entry with GUID ' . $valuesTmp['guid'] . ' in feed ' . $valuesTmp['id_feed']); + return false; } - - // We don't return Entry object to avoid a research in DB - return -1; } /** @@ -94,6 +141,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if (!is_array($ids)) { $ids = array($ids); } + if (count($ids) < 1) { + return 0; + } $sql = 'UPDATE `' . $this->prefix . 'entry` ' . 'SET is_favorite=? ' . 'WHERE id IN (' . str_repeat('?,', count($ids) - 1). '?)'; @@ -296,11 +346,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { * * If $idMax equals 0, a deprecated debug message is logged * - * @param integer $id feed ID + * @param integer $id_feed feed ID * @param integer $idMax fail safe article ID * @return integer affected rows */ - public function markReadFeed($id, $idMax = 0) { + public function markReadFeed($id_feed, $idMax = 0) { if ($idMax == 0) { $idMax = time() . '000000'; Minz_Log::debug('Calling markReadFeed(0) is deprecated!'); @@ -310,7 +360,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'UPDATE `' . $this->prefix . 'entry` ' . 'SET is_read=1 ' . 'WHERE id_feed=? AND is_read=0 AND id <= ?'; - $values = array($id, $idMax); + $values = array($id_feed, $idMax); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -324,7 +374,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'UPDATE `' . $this->prefix . 'feed` ' . 'SET cache_nbUnreads=cache_nbUnreads-' . $affected . ' WHERE id=?'; - $values = array($id); + $values = array($id_feed); $stm = $this->bd->prepare($sql); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); @@ -338,7 +388,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $affected; } - public function searchByGuid($feed_id, $id) { + public function searchByGuid($id_feed, $guid) { // un guid est unique pour un flux donné $sql = 'SELECT id, guid, title, author, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') @@ -347,8 +397,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $stm = $this->bd->prepare($sql); $values = array( - $feed_id, - $id + $id_feed, + $guid, ); $stm->execute($values); @@ -519,12 +569,52 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $stm->fetchAll(PDO::FETCH_COLUMN, 0); } - public function listLastGuidsByFeed($id, $n) { - $sql = 'SELECT guid FROM `' . $this->prefix . 'entry` WHERE id_feed=? ORDER BY id DESC LIMIT ' . intval($n); + public function listHashForFeedGuids($id_feed, $guids) { + if (count($guids) < 1) { + return array(); + } + $sql = 'SELECT guid, hex(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); - $stm->execute($values); - return $stm->fetchAll(PDO::FETCH_COLUMN, 0); + $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(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 updateLastSeen($id_feed, $guids) { + if (count($guids) < 1) { + return 0; + } + $sql = 'UPDATE `' . $this->prefix . 'entry` SET lastSeen=? WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; + $stm = $this->bd->prepare($sql); + $values = array(time(), $id_feed); + $values = array_merge($values, $guids); + if ($stm && $stm->execute($values)) { + return $stm->rowCount(); + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + if ($this->autoAddColumn($info)) { + return $this->updateLastSeen($id_feed, $guids); + } + Minz_Log::error('SQL error updateLastSeen: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2] + . ' while updating feed ' . $id_feed); + return false; + } } public function countUnreadRead() { diff --git a/app/Models/Feed.php b/app/Models/Feed.php index 5ce03be5d..27c83ffd5 100644 --- a/app/Models/Feed.php +++ b/app/Models/Feed.php @@ -255,6 +255,7 @@ class FreshRSS_Feed extends Minz_Model { $feed->__destruct(); //http://simplepie.org/wiki/faq/i_m_getting_memory_leaks unset($feed); + //TODO: Return a different information in case of cache/no-cache, and give access to the GUIDs in case of cache } } } diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index cf0159199..afdd821b2 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -15,7 +15,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` ( `name` varchar(255) NOT NULL, `website` varchar(255) CHARACTER SET latin1, `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, @@ -40,7 +40,9 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( `author` varchar(255), `content_bin` blob, -- v0.7 `link` varchar(1023) CHARACTER SET latin1 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, -- v0.7 @@ -50,6 +52,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( UNIQUE KEY (`id_feed`,`guid`), -- v0.7 INDEX (`is_favorite`), -- v0.7 INDEX (`is_read`) -- v0.7 + INDEX entry_lastSeen_index (`lastSeen`) -- v1.2 ) 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 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");', ); diff --git a/lib/lib_rss.php b/lib/lib_rss.php index e5fe73041..c6bdfde0e 100644 --- a/lib/lib_rss.php +++ b/lib/lib_rss.php @@ -38,7 +38,7 @@ function classAutoloader($class) { include(APP_PATH . '/Models/' . $components[1] . '.php'); return; case 3: //Controllers, Exceptions - @include(APP_PATH . '/' . $components[2] . 's/' . $components[1] . $components[2] . '.php'); + include(APP_PATH . '/' . $components[2] . 's/' . $components[1] . $components[2] . '.php'); return; } } elseif (strpos($class, 'Minz') === 0) { -- 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