From d6f414108667f32fe2b480adeb7ec9c218db2f4a Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Thu, 3 Jul 2014 21:26:30 +0200
Subject: Preparation for SQLite

https://github.com/marienfressinaud/FreshRSS/issues/100
---
 app/SQL/install.sql.mysql.php  | 60 ++++++++++++++++++++++++++++++++++++++++++
 app/SQL/install.sql.sqlite.php | 57 +++++++++++++++++++++++++++++++++++++++
 2 files changed, 117 insertions(+)
 create mode 100644 app/SQL/install.sql.mysql.php
 create mode 100644 app/SQL/install.sql.sqlite.php

(limited to 'app/SQL')

diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
new file mode 100644
index 000000000..d509cb4a8
--- /dev/null
+++ b/app/SQL/install.sql.mysql.php
@@ -0,0 +1,60 @@
+<?php
+define('SQL_CREATE_TABLES', '
+CREATE TABLE IF NOT EXISTS `%1$scategory` (
+	`id` SMALLINT NOT NULL AUTO_INCREMENT,	-- v0.7
+	`name` varchar(255) NOT NULL,
+	PRIMARY KEY (`id`),
+	UNIQUE KEY (`name`)	-- v0.7
+) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+ENGINE = INNODB;
+
+CREATE TABLE IF NOT EXISTS `%1$sfeed` (
+	`id` SMALLINT NOT NULL AUTO_INCREMENT,	-- v0.7
+	`url` varchar(511) CHARACTER SET latin1 NOT NULL,
+	`category` SMALLINT DEFAULT 0,	-- v0.7
+	`name` varchar(255) NOT NULL,
+	`website` varchar(255) CHARACTER SET latin1,
+	`description` text,
+	`lastUpdate` int(11) DEFAULT 0,
+	`priority` tinyint(2) NOT NULL DEFAULT 10,
+	`pathEntries` varchar(511) DEFAULT NULL,
+	`httpAuth` varchar(511) DEFAULT NULL,
+	`error` boolean DEFAULT 0,
+	`keep_history` MEDIUMINT NOT NULL DEFAULT -2,	-- v0.7
+	`cache_nbEntries` int DEFAULT 0,	-- v0.7
+	`cache_nbUnreads` int DEFAULT 0,	-- v0.7
+	PRIMARY KEY (`id`),
+	FOREIGN KEY (`category`) REFERENCES `%1$scategory`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
+	UNIQUE KEY (`url`),	-- v0.7
+	INDEX (`name`),	-- v0.7
+	INDEX (`priority`),	-- v0.7
+	INDEX (`keep_history`)	-- v0.7
+) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+ENGINE = INNODB;
+
+CREATE TABLE IF NOT EXISTS `%1$sentry` (
+	`id` bigint NOT NULL,	-- v0.7
+	`guid` varchar(760) CHARACTER SET latin1 NOT NULL,	-- Maximum for UNIQUE is 767B
+	`title` varchar(255) NOT NULL,
+	`author` varchar(255),
+	`content_bin` blob,	-- v0.7
+	`link` varchar(1023) CHARACTER SET latin1 NOT NULL,
+	`date` int(11),
+	`is_read` boolean NOT NULL DEFAULT 0,
+	`is_favorite` boolean NOT NULL DEFAULT 0,
+	`id_feed` SMALLINT,	-- v0.7
+	`tags` varchar(1023),
+	PRIMARY KEY (`id`),
+	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
+) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+ENGINE = INNODB;
+
+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
new file mode 100644
index 000000000..5bd294fde
--- /dev/null
+++ b/app/SQL/install.sql.sqlite.php
@@ -0,0 +1,57 @@
+<?php
+$SQL_CREATE_TABLES = array(
+'CREATE TABLE IF NOT EXISTS `%1$scategory` (
+	`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`name` varchar(255) NOT NULL,
+	UNIQUE (`name`)
+);',
+
+'CREATE TABLE IF NOT EXISTS `%1$sfeed` (
+	`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+	`url` varchar(511) NOT NULL,
+	`%1$scategory` SMALLINT DEFAULT 0,
+	`name` varchar(255) NOT NULL,
+	`website` varchar(255),
+	`description` text,
+	`lastUpdate` int(11) DEFAULT 0,
+	`priority` tinyint(2) NOT NULL DEFAULT 10,
+	`pathEntries` varchar(511) DEFAULT NULL,
+	`httpAuth` varchar(511) DEFAULT NULL,
+	`error` boolean DEFAULT 0,
+	`keep_history` MEDIUMINT 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,
+	UNIQUE (`url`)
+);',
+
+'CREATE INDEX IF NOT EXISTS feed_name_index ON `%1$sfeed`(`name`);',
+'CREATE INDEX IF NOT EXISTS feed_priority_index ON `%1$sfeed`(`priority`);',
+'CREATE INDEX IF NOT EXISTS feed_keep_history_index ON `%1$sfeed`(`keep_history`);',
+
+'CREATE TABLE IF NOT EXISTS `%1$sentry` (
+	`id` bigint NOT NULL,
+	`guid` varchar(760) NOT NULL,
+	`title` varchar(255) NOT NULL,
+	`author` varchar(255),
+	`content` text,
+	`link` varchar(1023) NOT NULL,
+	`date` int(11),
+	`is_read` boolean NOT NULL DEFAULT 0,
+	`is_favorite` boolean NOT NULL DEFAULT 0,
+	`id_feed` SMALLINT,
+	`tags` varchar(1023),
+	PRIMARY KEY (`id`),
+	FOREIGN KEY (`id_feed`) REFERENCES `%1$sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+	UNIQUE (`id_feed`,`guid`)
+);',
+
+'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");',
+);
+
+define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory');
+
+define('SQL_SHOW_TABLES', 'SELECT name FROM sqlite_master WHERE type="table"');
-- 
cgit v1.2.3


From 2501bb337e75c41f97570f25775e20131faf2f2a Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
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')

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 @@
+<?php
+
+class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
+
+	public function markRead($ids, $is_read = true) {
+		if (is_array($ids)) {	//Many IDs at once
+			if (true) {	//Not supported yet in SQLite, so always call IDs one by one
+				$affected = 0;
+				foreach ($ids as $id) {
+					$affected += $this->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 @@
-<?php
-
-class FreshRSS_EntryDAO_SQLite extends FreshRSS_EntryDAO {
-
-	public function markRead($ids, $is_read = true) {
-		if (is_array($ids)) {	//Many IDs at once
-			if (true) {	//Not supported yet in SQLite, so always call IDs one by one
-				$affected = 0;
-				foreach ($ids as $id) {
-					$affected += $this->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 () {
 		<div class="form-group">
 			<label class="group-name" for="type"><?php echo _t ('bdd_type'); ?></label>
 			<div class="group-controls">
-				<select name="type" id="type">
+				<select name="type" id="type" onchange="mySqlShowHide()">
 				<option value="mysql"
 					<?php echo (isset($_SESSION['bd_type']) && $_SESSION['bd_type'] === 'mysql') ? 'selected="selected"' : ''; ?>>
 					MySQL
@@ -934,6 +934,7 @@ function printStep3 () {
 			</div>
 		</div>
 
+		<div id="mysql">
 		<div class="form-group">
 			<label class="group-name" for="host"><?php echo _t ('host'); ?></label>
 			<div class="group-controls">
@@ -968,6 +969,13 @@ function printStep3 () {
 				<input type="text" id="prefix" name="prefix" maxlength="16" pattern="[0-9A-Za-z_]{1,16}" value="<?php echo isset ($_SESSION['bd_prefix']) ? $_SESSION['bd_prefix'] : 'freshrss_'; ?>" />
 			</div>
 		</div>
+		</div>
+		<script>
+			function mySqlShowHide() {
+				document.getElementById('mysql').style.display = document.getElementById('type').value === 'mysql' ? 'block' : 'none';
+			}
+			mySqlShowHide();
+		</script>
 
 		<div class="form-group form-actions">
 			<div class="group-controls">
-- 
cgit v1.2.3


From 1b43167fb8206dfd17923c389de1e3bd6b76045e Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
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')

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