summaryrefslogtreecommitdiff
path: root/app/Models
diff options
context:
space:
mode:
Diffstat (limited to 'app/Models')
-rw-r--r--app/Models/CategoryDAO.php21
-rw-r--r--app/Models/ConfigurationSetter.php1
-rw-r--r--app/Models/Context.php20
-rw-r--r--app/Models/DatabaseDAOPGSQL.php43
-rw-r--r--app/Models/EntryDAO.php168
-rw-r--r--app/Models/EntryDAOPGSQL.php31
-rw-r--r--app/Models/EntryDAOSQLite.php20
-rw-r--r--app/Models/Factory.php42
-rw-r--r--app/Models/Feed.php41
-rw-r--r--app/Models/FeedDAO.php57
-rw-r--r--app/Models/FeedDAOSQLite.php4
-rw-r--r--app/Models/StatsDAO.php102
-rw-r--r--app/Models/StatsDAOPGSQL.php67
-rw-r--r--app/Models/StatsDAOSQLite.php59
-rw-r--r--app/Models/UserDAO.php54
15 files changed, 462 insertions, 268 deletions
diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php
index fc431553e..c2d57c241 100644
--- a/app/Models/CategoryDAO.php
+++ b/app/Models/CategoryDAO.php
@@ -1,6 +1,9 @@
<?php
class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
+
+ const defaultCategoryId = 1;
+
public function addCategory($valuesTmp) {
$sql = 'INSERT INTO `' . $this->prefix . 'category`(name) VALUES(?)';
$stm = $this->bd->prepare($sql);
@@ -10,7 +13,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
);
if ($stm && $stm->execute($values)) {
- return $this->bd->lastInsertId();
+ return $this->bd->lastInsertId('"' . $this->prefix . 'category_id_seq"');
} else {
$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
Minz_Log::error('SQL error addCategory: ' . $info[2]);
@@ -50,6 +53,9 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
}
public function deleteCategory($id) {
+ if ($id <= self::defaultCategoryId) {
+ return false;
+ }
$sql = 'DELETE FROM `' . $this->prefix . 'category` WHERE id=?';
$stm = $this->bd->prepare($sql);
@@ -100,7 +106,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
public function listCategories($prePopulateFeeds = true, $details = false) {
if ($prePopulateFeeds) {
$sql = 'SELECT c.id AS c_id, c.name AS c_name, '
- . ($details ? 'f.* ' : 'f.id, f.name, f.url, f.website, f.priority, f.error, f.cache_nbEntries, f.cache_nbUnreads ')
+ . ($details ? 'f.* ' : 'f.id, f.name, f.url, f.website, f.priority, f.error, f.`cache_nbEntries`, f.`cache_nbUnreads` ')
. 'FROM `' . $this->prefix . 'category` c '
. 'LEFT OUTER JOIN `' . $this->prefix . 'feed` f ON f.category=c.id '
. 'GROUP BY f.id, c_id '
@@ -117,7 +123,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
}
public function getDefault() {
- $sql = 'SELECT * FROM `' . $this->prefix . 'category` WHERE id=1';
+ $sql = 'SELECT * FROM `' . $this->prefix . 'category` WHERE id=' . self::defaultCategoryId;
$stm = $this->bd->prepare($sql);
$stm->execute();
@@ -131,11 +137,11 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
}
}
public function checkDefault() {
- $def_cat = $this->searchById(1);
+ $def_cat = $this->searchById(self::defaultCategoryId);
if ($def_cat == null) {
$cat = new FreshRSS_Category(_t('gen.short.default_category'));
- $cat->_id(1);
+ $cat->_id(self::defaultCategoryId);
$values = array(
'id' => $cat->id(),
@@ -207,12 +213,13 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
$previousLine = null;
$feedsDao = array();
+ $feedDao = FreshRSS_Factory::createFeedDAO();
foreach ($listDAO as $line) {
if ($previousLine['c_id'] != null && $line['c_id'] !== $previousLine['c_id']) {
// End of the current category, we add it to the $list
$cat = new FreshRSS_Category(
$previousLine['c_name'],
- FreshRSS_FeedDAO::daoToFeed($feedsDao, $previousLine['c_id'])
+ $feedDao->daoToFeed($feedsDao, $previousLine['c_id'])
);
$cat->_id($previousLine['c_id']);
$list[$previousLine['c_id']] = $cat;
@@ -228,7 +235,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
if ($previousLine != null) {
$cat = new FreshRSS_Category(
$previousLine['c_name'],
- FreshRSS_FeedDAO::daoToFeed($feedsDao, $previousLine['c_id'])
+ $feedDao->daoToFeed($feedsDao, $previousLine['c_id'])
);
$cat->_id($previousLine['c_id']);
$list[$previousLine['c_id']] = $cat;
diff --git a/app/Models/ConfigurationSetter.php b/app/Models/ConfigurationSetter.php
index 79bd0170b..046f54955 100644
--- a/app/Models/ConfigurationSetter.php
+++ b/app/Models/ConfigurationSetter.php
@@ -282,6 +282,7 @@ class FreshRSS_ConfigurationSetter {
switch ($value['type']) {
case 'mysql':
+ case 'pgsql':
if (empty($value['host']) ||
empty($value['user']) ||
empty($value['base']) ||
diff --git a/app/Models/Context.php b/app/Models/Context.php
index 2a58bd4ba..fd0e79fc1 100644
--- a/app/Models/Context.php
+++ b/app/Models/Context.php
@@ -35,6 +35,9 @@ class FreshRSS_Context {
public static $first_id = '';
public static $next_id = '';
public static $id_max = '';
+ public static $sinceHours = 0;
+
+ public static $isCli = false;
/**
* Initialize the context.
@@ -45,9 +48,6 @@ class FreshRSS_Context {
// Init configuration.
self::$system_conf = Minz_Configuration::get('system');
self::$user_conf = Minz_Configuration::get('user');
-
- $catDAO = new FreshRSS_CategoryDAO();
- self::$categories = $catDAO->listCategories();
}
/**
@@ -139,15 +139,22 @@ class FreshRSS_Context {
$id = substr($get, 2);
$nb_unread = 0;
+ if (empty(self::$categories)) {
+ $catDAO = new FreshRSS_CategoryDAO();
+ self::$categories = $catDAO->listCategories();
+ }
+
switch($type) {
case 'a':
self::$current_get['all'] = true;
self::$name = _t('index.feed.title');
+ self::$description = self::$system_conf->meta_description;
self::$get_unread = self::$total_unread;
break;
case 's':
self::$current_get['starred'] = true;
self::$name = _t('index.feed.title_fav');
+ self::$description = self::$system_conf->meta_description;
self::$get_unread = self::$total_starred['unread'];
// Update state if favorite is not yet enabled.
@@ -198,11 +205,16 @@ class FreshRSS_Context {
/**
* Set the value of $next_get attribute.
*/
- public static function _nextGet() {
+ private static function _nextGet() {
$get = self::currentGet();
// By default, $next_get == $get
self::$next_get = $get;
+ if (empty(self::$categories)) {
+ $catDAO = new FreshRSS_CategoryDAO();
+ self::$categories = $catDAO->listCategories();
+ }
+
if (self::$user_conf->onread_jump_next && strlen($get) > 2) {
$another_unread_id = '';
$found_current_get = false;
diff --git a/app/Models/DatabaseDAOPGSQL.php b/app/Models/DatabaseDAOPGSQL.php
new file mode 100644
index 000000000..a4edaa448
--- /dev/null
+++ b/app/Models/DatabaseDAOPGSQL.php
@@ -0,0 +1,43 @@
+<?php
+
+/**
+ * This class is used to test database is well-constructed.
+ */
+class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAO {
+ public function tablesAreCorrect() {
+ $db = FreshRSS_Context::$system_conf->db;
+ $dbowner = $db['user'];
+ $sql = 'SELECT * FROM pg_catalog.pg_tables where tableowner=?';
+ $stm = $this->bd->prepare($sql);
+ $values = array($dbowner);
+ $stm->execute($values);
+ $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 = 'select column_name as field, data_type as type, column_default as default, is_nullable as null from INFORMATION_SCHEMA.COLUMNS where table_name = ?';
+ $stm = $this->bd->prepare($sql);
+ $stm->execute(array($this->prefix . $table));
+ return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC));
+ }
+
+ public function daoToSchema($dao) {
+ return array(
+ 'name' => $dao['field'],
+ 'type' => strtolower($dao['type']),
+ 'notnull' => (bool)$dao['null'],
+ 'default' => $dao['default'],
+ );
+ }
+}
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 8f64098e5..4c6a9ea20 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -3,13 +3,21 @@
class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
public function isCompressed() {
- return parent::$sharedDbType !== 'sqlite';
+ return parent::$sharedDbType === 'mysql';
}
public function hasNativeHex() {
return parent::$sharedDbType !== 'sqlite';
}
+ public function sqlHexDecode($x) {
+ return 'unhex(' . $x . ')';
+ }
+
+ public function sqlHexEncode($x) {
+ return 'hex(' . $x . ')';
+ }
+
protected function addColumn($name) {
Minz_Log::warning('FreshRSS_EntryDAO::addColumn: ' . $name);
$hasTransaction = false;
@@ -20,7 +28,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
$this->bd->beginTransaction();
$hasTransaction = true;
}
- $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN lastSeen INT(11) DEFAULT 0');
+ $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()) {
@@ -105,32 +113,45 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
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(?)' : '?')
- . ', ?, ?, ?, '
- . ($this->hasNativeHex() ? 'X?' : '?')
- . ', ?, ?, ?, ?)';
+ . ', link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) '
+ . 'VALUES(:id, :guid, :title, :author, '
+ . ($this->isCompressed() ? 'COMPRESS(:content)' : ':content')
+ . ', :link, :date, :last_seen, '
+ . $this->sqlHexDecode(':hash')
+ . ', :is_read, :is_favorite, :id_feed, :tags)';
$this->addEntryPrepared = $this->bd->prepare($sql);
}
+ $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']);
+ $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760);
+ $valuesTmp['guid'] = safe_ascii($valuesTmp['guid']);
+ $this->addEntryPrepared->bindParam(':guid', $valuesTmp['guid']);
+ $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255);
+ $this->addEntryPrepared->bindParam(':title', $valuesTmp['title']);
+ $valuesTmp['author'] = substr($valuesTmp['author'], 0, 255);
+ $this->addEntryPrepared->bindParam(':author', $valuesTmp['author']);
+ $this->addEntryPrepared->bindParam(':content', $valuesTmp['content']);
+ $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023);
+ $valuesTmp['link'] = safe_ascii($valuesTmp['link']);
+ $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']);
+ $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT);
+ $valuesTmp['lastSeen'] = time();
+ $this->addEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT);
+ $valuesTmp['is_read'] = $valuesTmp['is_read'] ? 1 : 0;
+ $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'], PDO::PARAM_INT);
+ $valuesTmp['is_favorite'] = $valuesTmp['is_favorite'] ? 1 : 0;
+ $this->addEntryPrepared->bindParam(':is_favorite', $valuesTmp['is_favorite'], PDO::PARAM_INT);
+ $this->addEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT);
+ $valuesTmp['tags'] = substr($valuesTmp['tags'], 0, 1023);
+ $this->addEntryPrepared->bindParam(':tags', $valuesTmp['tags']);
+
+ if ($this->hasNativeHex()) {
+ $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hash']);
+ } else {
+ $valuesTmp['hashBin'] = pack('H*', $valuesTmp['hash']); //hex2bin() is PHP5.4+
+ $this->addEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']);
+ }
- $values = array(
- $valuesTmp['id'],
- substr($valuesTmp['guid'], 0, 760),
- substr($valuesTmp['title'], 0, 255),
- substr($valuesTmp['author'], 0, 255),
- $valuesTmp['content'],
- substr($valuesTmp['link'], 0, 1023),
- $valuesTmp['date'],
- time(),
- $this->hasNativeHex() ? $valuesTmp['hash'] : pack('H*', $valuesTmp['hash']), // X'09AF' hexadecimal literals do not work with SQLite/PDO //hex2bin() is PHP5.4+
- $valuesTmp['is_read'] ? 1 : 0,
- $valuesTmp['is_favorite'] ? 1 : 0,
- $valuesTmp['id_feed'],
- substr($valuesTmp['tags'], 0, 1023),
- );
-
- if ($this->addEntryPrepared && $this->addEntryPrepared->execute($values)) {
+ if ($this->addEntryPrepared && $this->addEntryPrepared->execute()) {
return $this->bd->lastInsertId();
} else {
$info = $this->addEntryPrepared == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $this->addEntryPrepared->errorInfo();
@@ -153,35 +174,44 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
if ($this->updateEntryPrepared === null) {
$sql = 'UPDATE `' . $this->prefix . 'entry` '
- . 'SET title=?, author=?, '
- . ($this->isCompressed() ? 'content_bin=COMPRESS(?)' : 'content=?')
- . ', link=?, date=?, lastSeen=?, hash='
- . ($this->hasNativeHex() ? 'X?' : '?')
- . ', ' . ($valuesTmp['is_read'] === null ? '' : 'is_read=?, ')
- . 'tags=? '
- . 'WHERE id_feed=? AND guid=?';
+ . 'SET title=:title, author=:author, '
+ . ($this->isCompressed() ? 'content_bin=COMPRESS(:content)' : 'content=:content')
+ . ', link=:link, date=:date, `lastSeen`=:last_seen, '
+ . 'hash=' . $this->sqlHexDecode(':hash')
+ . ', ' . ($valuesTmp['is_read'] === null ? '' : 'is_read=:is_read, ')
+ . 'tags=:tags '
+ . 'WHERE id_feed=:id_feed AND guid=:guid';
$this->updateEntryPrepared = $this->bd->prepare($sql);
}
- $values = array(
- substr($valuesTmp['title'], 0, 255),
- substr($valuesTmp['author'], 0, 255),
- $valuesTmp['content'],
- substr($valuesTmp['link'], 0, 1023),
- $valuesTmp['date'],
- time(),
- $this->hasNativeHex() ? $valuesTmp['hash'] : pack('H*', $valuesTmp['hash']),
- );
+ $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760);
+ $this->updateEntryPrepared->bindParam(':guid', $valuesTmp['guid']);
+ $valuesTmp['title'] = substr($valuesTmp['title'], 0, 255);
+ $this->updateEntryPrepared->bindParam(':title', $valuesTmp['title']);
+ $valuesTmp['author'] = substr($valuesTmp['author'], 0, 255);
+ $this->updateEntryPrepared->bindParam(':author', $valuesTmp['author']);
+ $this->updateEntryPrepared->bindParam(':content', $valuesTmp['content']);
+ $valuesTmp['link'] = substr($valuesTmp['link'], 0, 1023);
+ $valuesTmp['link'] = safe_ascii($valuesTmp['link']);
+ $this->updateEntryPrepared->bindParam(':link', $valuesTmp['link']);
+ $this->updateEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT);
+ $valuesTmp['lastSeen'] = time();
+ $this->updateEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT);
if ($valuesTmp['is_read'] !== null) {
- $values[] = $valuesTmp['is_read'] ? 1 : 0;
+ $this->updateEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'] ? 1 : 0, PDO::PARAM_INT);
+ }
+ $this->updateEntryPrepared->bindParam(':id_feed', $valuesTmp['id_feed'], PDO::PARAM_INT);
+ $valuesTmp['tags'] = substr($valuesTmp['tags'], 0, 1023);
+ $this->updateEntryPrepared->bindParam(':tags', $valuesTmp['tags']);
+
+ if ($this->hasNativeHex()) {
+ $this->updateEntryPrepared->bindParam(':hash', $valuesTmp['hash']);
+ } else {
+ $valuesTmp['hashBin'] = pack('H*', $valuesTmp['hash']); //hex2bin() is PHP5.4+
+ $this->updateEntryPrepared->bindParam(':hash', $valuesTmp['hashBin']);
}
- $values = array_merge($values, array(
- substr($valuesTmp['tags'], 0, 1023),
- $valuesTmp['id_feed'],
- substr($valuesTmp['guid'], 0, 760),
- ));
- if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute($values)) {
+ if ($this->updateEntryPrepared && $this->updateEntryPrepared->execute()) {
return $this->bd->lastInsertId();
} else {
$info = $this->updateEntryPrepared == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $this->updateEntryPrepared->errorInfo();
@@ -246,15 +276,19 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
. 'WHERE e.is_read=0 '
. 'GROUP BY e.id_feed'
. ') x ON x.id_feed=f.id '
- . 'SET f.cache_nbUnreads=COALESCE(x.nbUnreads, 0) '
- . 'WHERE 1';
+ . 'SET f.`cache_nbUnreads`=COALESCE(x.nbUnreads, 0)';
+ $hasWhere = false;
$values = array();
if ($feedId !== false) {
- $sql .= ' AND f.id=?';
+ $sql .= $hasWhere ? ' AND' : ' WHERE';
+ $hasWhere = true;
+ $sql .= ' f.id=?';
$values[] = $id;
}
if ($catId !== false) {
- $sql .= ' AND f.category=?';
+ $sql .= $hasWhere ? ' AND' : ' WHERE';
+ $hasWhere = true;
+ $sql .= ' f.category=?';
$values[] = $catId;
}
$stm = $this->bd->prepare($sql);
@@ -309,7 +343,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
} else {
$sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id '
. 'SET e.is_read=?,'
- . 'f.cache_nbUnreads=f.cache_nbUnreads' . ($is_read ? '-' : '+') . '1 '
+ . 'f.`cache_nbUnreads`=f.`cache_nbUnreads`' . ($is_read ? '-' : '+') . '1 '
. 'WHERE e.id=? AND e.is_read=?';
$values = array($is_read ? 1 : 0, $ids, $is_read ? 0 : 1);
$stm = $this->bd->prepare($sql);
@@ -430,17 +464,17 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
}
$this->bd->beginTransaction();
- $sql = 'UPDATE `' . $this->prefix . 'entry` e '
- . 'SET e.is_read=1 '
- . 'WHERE e.id_feed=? AND e.is_read=0 AND e.id <= ?';
+ $sql = 'UPDATE `' . $this->prefix . 'entry` '
+ . 'SET is_read=1 '
+ . 'WHERE id_feed=? AND is_read=0 AND id <= ?';
$values = array($id_feed, $idMax);
- list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filter, $state);
+ list($searchValues, $search) = $this->sqlListEntriesWhere('', $filter, $state);
$stm = $this->bd->prepare($sql . $search);
if (!($stm && $stm->execute(array_merge($values, $searchValues)))) {
$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
- Minz_Log::error('SQL error markReadFeed: ' . $info[2]);
+ Minz_Log::error('SQL error markReadFeed: ' . $info[2] . ' with SQL: ' . $sql . $search);
$this->bd->rollBack();
return false;
}
@@ -448,13 +482,13 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
if ($affected > 0) {
$sql = 'UPDATE `' . $this->prefix . 'feed` '
- . 'SET cache_nbUnreads=cache_nbUnreads-' . $affected
+ . 'SET `cache_nbUnreads`=`cache_nbUnreads`-' . $affected
. ' WHERE id=?';
$values = array($id_feed);
$stm = $this->bd->prepare($sql);
if (!($stm && $stm->execute($values))) {
$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
- Minz_Log::error('SQL error markReadFeed: ' . $info[2]);
+ Minz_Log::error('SQL error markReadFeed cache: ' . $info[2]);
$this->bd->rollBack();
return false;
}
@@ -658,7 +692,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
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). '?)';
+ $guids = array_unique($guids);
+ $sql = 'SELECT guid, ' . $this->sqlHexEncode('hash') . ' AS hex_hash FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)';
$stm = $this->bd->prepare($sql);
$values = array($id_feed);
$values = array_merge($values, $guids);
@@ -666,7 +701,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
$result = array();
$rows = $stm->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
- $result[$row['guid']] = $row['hexHash'];
+ $result[$row['guid']] = $row['hex_hash'];
}
return $result;
} else {
@@ -680,13 +715,16 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
}
}
- public function updateLastSeen($id_feed, $guids) {
+ public function updateLastSeen($id_feed, $guids, $mtime = 0) {
if (count($guids) < 1) {
return 0;
}
- $sql = 'UPDATE `' . $this->prefix . 'entry` SET lastSeen=? WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)';
+ $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);
+ if ($mtime <= 0) {
+ $mtime = time();
+ }
+ $values = array($mtime, $id_feed);
$values = array_merge($values, $guids);
if ($stm && $stm->execute($values)) {
return $stm->rowCount();
diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php
new file mode 100644
index 000000000..b96a62ebc
--- /dev/null
+++ b/app/Models/EntryDAOPGSQL.php
@@ -0,0 +1,31 @@
+<?php
+
+class FreshRSS_EntryDAOPGSQL extends FreshRSS_EntryDAOSQLite {
+
+ public function sqlHexDecode($x) {
+ return 'decode(' . $x . ", 'hex')";
+ }
+
+ public function sqlHexEncode($x) {
+ return 'encode(' . $x . ", 'hex')";
+ }
+
+ protected function autoUpdateDb($errorInfo) {
+ return false;
+ }
+
+ protected function addColumn($name) {
+ return false;
+ }
+
+ public function size($all = true) {
+ $db = FreshRSS_Context::$system_conf->db;
+ $sql = 'SELECT pg_size_pretty(pg_database_size(?))';
+ $values = array($db['base']);
+ $stm = $this->bd->prepare($sql);
+ $stm->execute($values);
+ $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0);
+ return $res[0];
+ }
+
+}
diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index dad34a93d..34e854608 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -2,6 +2,10 @@
class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
+ public function sqlHexDecode($x) {
+ return $x;
+ }
+
protected function autoUpdateDb($errorInfo) {
if (empty($errorInfo[0]) || $errorInfo[0] == '42S22') { //ER_BAD_FIELD_ERROR
//autoAddColumn
@@ -24,17 +28,21 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
protected function updateCacheUnreads($catId = false, $feedId = false) {
$sql = 'UPDATE `' . $this->prefix . 'feed` '
- . 'SET cache_nbUnreads=('
+ . 'SET `cache_nbUnreads`=('
. 'SELECT COUNT(*) AS nbUnreads FROM `' . $this->prefix . 'entry` e '
- . 'WHERE e.id_feed=`' . $this->prefix . 'feed`.id AND e.is_read=0) '
- . 'WHERE 1';
+ . 'WHERE e.id_feed=`' . $this->prefix . 'feed`.id AND e.is_read=0)';
+ $hasWhere = false;
$values = array();
if ($feedId !== false) {
- $sql .= ' AND id=?';
+ $sql .= $hasWhere ? ' AND' : ' WHERE';
+ $hasWhere = true;
+ $sql .= ' id=?';
$values[] = $feedId;
}
if ($catId !== false) {
- $sql .= ' AND category=?';
+ $sql .= $hasWhere ? ' AND' : ' WHERE';
+ $hasWhere = true;
+ $sql .= ' category=?';
$values[] = $catId;
}
$stm = $this->bd->prepare($sql);
@@ -82,7 +90,7 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
}
$affected = $stm->rowCount();
if ($affected > 0) {
- $sql = 'UPDATE `' . $this->prefix . 'feed` SET cache_nbUnreads=cache_nbUnreads' . ($is_read ? '-' : '+') . '1 '
+ $sql = 'UPDATE `' . $this->prefix . 'feed` SET `cache_nbUnreads`=`cache_nbUnreads`' . ($is_read ? '-' : '+') . '1 '
. 'WHERE id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)';
$values = array($ids);
$stm = $this->bd->prepare($sql);
diff --git a/app/Models/Factory.php b/app/Models/Factory.php
index db09d155d..764987c46 100644
--- a/app/Models/Factory.php
+++ b/app/Models/Factory.php
@@ -4,37 +4,47 @@ class FreshRSS_Factory {
public static function createFeedDao($username = null) {
$conf = Minz_Configuration::get('system');
- if ($conf->db['type'] === 'sqlite') {
- return new FreshRSS_FeedDAOSQLite($username);
- } else {
- return new FreshRSS_FeedDAO($username);
+ switch ($conf->db['type']) {
+ case 'sqlite':
+ return new FreshRSS_FeedDAOSQLite($username);
+ default:
+ return new FreshRSS_FeedDAO($username);
}
}
public static function createEntryDao($username = null) {
$conf = Minz_Configuration::get('system');
- if ($conf->db['type'] === 'sqlite') {
- return new FreshRSS_EntryDAOSQLite($username);
- } else {
- return new FreshRSS_EntryDAO($username);
+ switch ($conf->db['type']) {
+ case 'sqlite':
+ return new FreshRSS_EntryDAOSQLite($username);
+ case 'pgsql':
+ return new FreshRSS_EntryDAOPGSQL($username);
+ default:
+ return new FreshRSS_EntryDAO($username);
}
}
public static function createStatsDAO($username = null) {
$conf = Minz_Configuration::get('system');
- if ($conf->db['type'] === 'sqlite') {
- return new FreshRSS_StatsDAOSQLite($username);
- } else {
- return new FreshRSS_StatsDAO($username);
+ switch ($conf->db['type']) {
+ case 'sqlite':
+ return new FreshRSS_StatsDAOSQLite($username);
+ case 'pgsql':
+ return new FreshRSS_StatsDAOPGSQL($username);
+ default:
+ return new FreshRSS_StatsDAO($username);
}
}
public static function createDatabaseDAO($username = null) {
$conf = Minz_Configuration::get('system');
- if ($conf->db['type'] === 'sqlite') {
- return new FreshRSS_DatabaseDAOSQLite($username);
- } else {
- return new FreshRSS_DatabaseDAO($username);
+ switch ($conf->db['type']) {
+ case 'sqlite':
+ return new FreshRSS_DatabaseDAOSQLite($username);
+ case 'pgsql':
+ return new FreshRSS_DatabaseDAOPGSQL($username);
+ default:
+ return new FreshRSS_DatabaseDAO($username);
}
}
diff --git a/app/Models/Feed.php b/app/Models/Feed.php
index 6104b1e31..97cb1c47e 100644
--- a/app/Models/Feed.php
+++ b/app/Models/Feed.php
@@ -131,13 +131,26 @@ class FreshRSS_Feed extends Minz_Model {
return $this->nbNotRead;
}
public function faviconPrepare() {
- $file = DATA_PATH . '/favicons/' . $this->hash() . '.txt';
- if (!file_exists($file)) {
- $t = $this->website;
- if ($t == '') {
- $t = $this->url;
+ global $favicons_dir;
+ require_once(LIB_PATH . '/favicons.php');
+ $url = $this->website;
+ if ($url == '') {
+ $url = $this->url;
+ }
+ $txt = $favicons_dir . $this->hash() . '.txt';
+ if (!file_exists($txt)) {
+ file_put_contents($txt, $url);
+ }
+ if (FreshRSS_Context::$isCli) {
+ $ico = $favicons_dir . $this->hash() . '.ico';
+ $ico_mtime = @filemtime($ico);
+ $txt_mtime = @filemtime($txt);
+ if ($txt_mtime != false &&
+ ($ico_mtime == false || $ico_mtime < $txt_mtime || ($ico_mtime < time() - (14 * 86400)))) {
+ // no ico file or we should download a new one.
+ $url = file_get_contents($txt);
+ download_favicon($url, $ico) || touch($ico);
}
- file_put_contents($file, $t);
}
}
public static function faviconDelete($hash) {
@@ -216,7 +229,7 @@ class FreshRSS_Feed extends Minz_Model {
$this->nbEntries = intval($value);
}
- public function load($loadDetails = false) {
+ public function load($loadDetails = false, $noCache = false) {
if ($this->url !== null) {
if (CACHE_PATH === false) {
throw new Minz_FileNotExistException(
@@ -268,7 +281,7 @@ class FreshRSS_Feed extends Minz_Model {
$this->_url($clean_url);
}
- if (($mtime === true) || ($mtime > $this->lastUpdate)) {
+ if (($mtime === true) || ($mtime > $this->lastUpdate) || $noCache) {
//Minz_Log::debug('FreshRSS no cache ' . $mtime . ' > ' . $this->lastUpdate . ' for ' . $clean_url);
$this->loadEntries($feed); // et on charge les articles du flux
} else {
@@ -309,11 +322,11 @@ class FreshRSS_Feed extends Minz_Model {
$elinks[$elink] = '1';
$mime = strtolower($enclosure->get_type());
if (strpos($mime, 'image/') === 0) {
- $content .= '<br /><img lazyload="" postpone="" src="' . $elink . '" alt="" />';
+ $content .= '<p class="enclosure"><img src="' . $elink . '" alt="" /></p>';
} elseif (strpos($mime, 'audio/') === 0) {
- $content .= '<br /><audio lazyload="" postpone="" preload="none" src="' . $elink . '" controls="controls" />';
+ $content .= '<p class="enclosure"><audio preload="none" src="' . $elink . '" controls="controls"></audio> <a download="" href="' . $elink . '">💾</a></p>';
} elseif (strpos($mime, 'video/') === 0) {
- $content .= '<br /><video lazyload="" postpone="" preload="none" src="' . $elink . '" controls="controls" />';
+ $content .= '<p class="enclosure"><video preload="none" src="' . $elink . '" controls="controls"></video> <a download="" href="' . $elink . '">💾</a></p>';
} else {
unset($elinks[$elink]);
}
@@ -340,6 +353,10 @@ class FreshRSS_Feed extends Minz_Model {
$this->entries = $entries;
}
+ function cacheModifiedTime() {
+ return @filemtime(CACHE_PATH . '/' . md5($this->url) . '.spc');
+ }
+
function lock() {
$this->lockPath = TMP_PATH . '/' . $this->hash() . '.freshrss.lock';
if (file_exists($this->lockPath) && ((time() - @filemtime($this->lockPath)) > 3600)) {
@@ -460,7 +477,7 @@ class FreshRSS_Feed extends Minz_Model {
CURLOPT_URL => $this->hubUrl,
CURLOPT_FOLLOWLOCATION => true,
CURLOPT_RETURNTRANSFER => true,
- CURLOPT_USERAGENT => _t('gen.freshrss') . '/' . FRESHRSS_VERSION . ' (' . PHP_OS . '; ' . FRESHRSS_WEBSITE . ')',
+ CURLOPT_USERAGENT => 'FreshRSS/' . FRESHRSS_VERSION . ' (' . PHP_OS . '; ' . FRESHRSS_WEBSITE . ')',
CURLOPT_POSTFIELDS => 'hub.verify=sync'
. '&hub.mode=' . ($state ? 'subscribe' : 'unsubscribe')
. '&hub.topic=' . urlencode($this->selfUrl)
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index 475d39286..68398efd5 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -2,9 +2,12 @@
class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
public function addFeed($valuesTmp) {
- $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, lastUpdate, priority, httpAuth, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)';
+ $sql = 'INSERT INTO `' . $this->prefix . 'feed` (url, category, name, website, description, `lastUpdate`, priority, `httpAuth`, error, keep_history, ttl) VALUES(?, ?, ?, ?, ?, ?, 10, ?, 0, -2, -2)';
$stm = $this->bd->prepare($sql);
+ $valuesTmp['url'] = safe_ascii($valuesTmp['url']);
+ $valuesTmp['website'] = safe_ascii($valuesTmp['website']);
+
$values = array(
substr($valuesTmp['url'], 0, 511),
$valuesTmp['category'],
@@ -16,7 +19,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
);
if ($stm && $stm->execute($values)) {
- return $this->bd->lastInsertId();
+ return $this->bd->lastInsertId('"' . $this->prefix . 'feed_id_seq"');
} else {
$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
Minz_Log::error('SQL error addFeed: ' . $info[2]);
@@ -55,6 +58,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
}
public function updateFeed($id, $valuesTmp) {
+ if (isset($valuesTmp['url'])) {
+ $valuesTmp['url'] = safe_ascii($valuesTmp['url']);
+ }
+ if (isset($valuesTmp['website'])) {
+ $valuesTmp['website'] = safe_ascii($valuesTmp['website']);
+ }
+
$set = '';
foreach ($valuesTmp as $key => $v) {
$set .= $key . '=?, ';
@@ -82,22 +92,26 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
}
}
- public function updateLastUpdate($id, $inError = 0, $updateCache = true) {
+ public function updateLastUpdate($id, $inError = false, $updateCache = true, $mtime = 0) {
if ($updateCache) {
$sql = 'UPDATE `' . $this->prefix . 'feed` ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE
- . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),'
- . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0),'
- . 'lastUpdate=?, error=? '
+ . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),'
+ . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0),'
+ . '`lastUpdate`=?, error=? '
. 'WHERE id=?';
} else {
$sql = 'UPDATE `' . $this->prefix . 'feed` '
- . 'SET lastUpdate=?, error=? '
+ . 'SET `lastUpdate`=?, error=? '
. 'WHERE id=?';
}
+ if ($mtime <= 0) {
+ $mtime = time();
+ }
+
$values = array(
- time(),
- $inError,
+ $mtime,
+ $inError ? 1 : 0,
$id,
);
@@ -198,6 +212,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
}
}
+ public function listFeedsIds() {
+ $sql = 'SELECT id FROM `' . $this->prefix . 'feed`';
+ $stm = $this->bd->prepare($sql);
+ $stm->execute();
+ return $stm->fetchAll(PDO::FETCH_COLUMN, 0);
+ }
+
public function listFeeds() {
$sql = 'SELECT * FROM `' . $this->prefix . 'feed` ORDER BY name';
$stm = $this->bd->prepare($sql);
@@ -222,14 +243,14 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
return $feedCategoryNames;
}
+ /**
+ * Use $defaultCacheDuration == -1 to return all feeds, without filtering them by TTL.
+ */
public function listFeedsOrderUpdate($defaultCacheDuration = 3600) {
- if ($defaultCacheDuration < 0) {
- $defaultCacheDuration = 2147483647;
- }
- $sql = 'SELECT id, url, name, website, lastUpdate, pathEntries, httpAuth, keep_history, ttl '
+ $sql = 'SELECT id, url, name, website, `lastUpdate`, `pathEntries`, `httpAuth`, keep_history, ttl '
. 'FROM `' . $this->prefix . 'feed` '
- . 'WHERE ttl <> -1 AND lastUpdate < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) '
- . 'ORDER BY lastUpdate';
+ . ($defaultCacheDuration < 0 ? '' : 'WHERE ttl <> -1 AND `lastUpdate` < (' . (time() + 60) . '-(CASE WHEN ttl=-2 THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ')
+ . 'ORDER BY `lastUpdate`';
$stm = $this->bd->prepare($sql);
if (!($stm && $stm->execute())) {
$sql2 = 'ALTER TABLE `' . $this->prefix . 'feed` ADD COLUMN ttl INT NOT NULL DEFAULT -2'; //v0.7.3
@@ -282,7 +303,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
. 'FROM `' . $this->prefix . 'entry` e '
. 'GROUP BY e.id_feed'
. ') x ON x.id_feed=f.id '
- . 'SET f.cache_nbEntries=x.nbEntries, f.cache_nbUnreads=x.nbUnreads';
+ . 'SET f.`cache_nbEntries`=x.nbEntries, f.`cache_nbUnreads`=x.nbUnreads';
$stm = $this->bd->prepare($sql);
if ($stm && $stm->execute()) {
@@ -308,7 +329,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
$affected = $stm->rowCount();
$sql = 'UPDATE `' . $this->prefix . 'feed` '
- . 'SET cache_nbEntries=0, cache_nbUnreads=0 WHERE id=?';
+ . 'SET `cache_nbEntries`=0, `cache_nbUnreads`=0 WHERE id=?';
$values = array($id);
$stm = $this->bd->prepare($sql);
if (!($stm && $stm->execute($values))) {
@@ -326,7 +347,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
$sql = 'DELETE FROM `' . $this->prefix . 'entry` '
. 'WHERE id_feed=:id_feed AND id<=:id_max '
. 'AND is_favorite=0 ' //Do not remove favourites
- . 'AND lastSeen < (SELECT maxLastSeen FROM (SELECT (MAX(e3.lastSeen)-99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed=:id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance
+ . 'AND `lastSeen` < (SELECT maxLastSeen FROM (SELECT (MAX(e3.`lastSeen`)-99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed=:id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance
. '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);
diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php
index 7599fda53..440ae74da 100644
--- a/app/Models/FeedDAOSQLite.php
+++ b/app/Models/FeedDAOSQLite.php
@@ -4,8 +4,8 @@ class FreshRSS_FeedDAOSQLite extends FreshRSS_FeedDAO {
public function updateCachedValues() { //For one single feed, call updateLastUpdate($id)
$sql = 'UPDATE `' . $this->prefix . 'feed` '
- . 'SET cache_nbEntries=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),'
- . 'cache_nbUnreads=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0)';
+ . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),'
+ . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0)';
$stm = $this->bd->prepare($sql);
if ($stm && $stm->execute()) {
return $stm->rowCount();
diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php
index 4f83ff577..2ce4f2944 100644
--- a/app/Models/StatsDAO.php
+++ b/app/Models/StatsDAO.php
@@ -4,6 +4,10 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo {
const ENTRY_COUNT_PERIOD = 30;
+ protected function sqlFloor($s) {
+ return "FLOOR($s)";
+ }
+
/**
* Calculates entry repartition for all feeds and for main stream.
*
@@ -37,12 +41,12 @@ class FreshRSS_StatsDAO extends Minz_ModelPdo {
$filter .= "AND e.id_feed = {$feed}";
}
$sql = <<<SQL
-SELECT COUNT(1) AS `total`,
-COUNT(1) - SUM(e.is_read) AS `unread`,
-SUM(e.is_read) AS `read`,
-SUM(e.is_favorite) AS `favorite`
-FROM {$this->prefix}entry AS e
-, {$this->prefix}feed AS f
+SELECT COUNT(1) AS total,
+COUNT(1) - SUM(e.is_read) AS count_unreads,
+SUM(e.is_read) AS count_reads,
+SUM(e.is_favorite) AS count_favorites
+FROM `{$this->prefix}entry` AS e
+, `{$this->prefix}feed` AS f
WHERE e.id_feed = f.id
{$filter}
SQL;
@@ -61,14 +65,16 @@ SQL;
*/
public function calculateEntryCount() {
$count = $this->initEntryCountArray();
- $period = self::ENTRY_COUNT_PERIOD;
+ $midnight = mktime(0, 0, 0);
+ $oldest = $midnight - (self::ENTRY_COUNT_PERIOD * 86400);
// Get stats per day for the last 30 days
+ $sqlDay = $this->sqlFloor("(date - $midnight) / 86400");
$sql = <<<SQL
-SELECT DATEDIFF(FROM_UNIXTIME(e.date), NOW()) AS day,
-COUNT(1) AS count
-FROM {$this->prefix}entry AS e
-WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d')
+SELECT {$sqlDay} AS day,
+COUNT(*) as count
+FROM `{$this->prefix}entry`
+WHERE date >= {$oldest} AND date < {$midnight}
GROUP BY day
ORDER BY day ASC
SQL;
@@ -80,28 +86,7 @@ SQL;
$count[$value['day']] = (int) $value['count'];
}
- return $this->convertToSerie($count);
- }
-
- /**
- * Calculates entry average per day on a 30 days period.
- *
- * @return integer
- */
- public function calculateEntryAverage() {
- $period = self::ENTRY_COUNT_PERIOD;
-
- // Get stats per day for the last 30 days
- $sql = <<<SQL
-SELECT COUNT(1) / {$period} AS average
-FROM {$this->prefix}entry AS e
-WHERE FROM_UNIXTIME(e.date, '%Y%m%d') BETWEEN DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -{$period} DAY), '%Y%m%d') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), '%Y%m%d')
-SQL;
- $stm = $this->bd->prepare($sql);
- $stm->execute();
- $res = $stm->fetch(PDO::FETCH_NAMED);
-
- return round($res['average'], 2);
+ return $count;
}
/**
@@ -158,7 +143,7 @@ SQL;
$sql = <<<SQL
SELECT DATE_FORMAT(FROM_UNIXTIME(e.date), '{$period}') AS period
, COUNT(1) AS count
-FROM {$this->prefix}entry AS e
+FROM `{$this->prefix}entry` AS e
{$restrict}
GROUP BY period
ORDER BY period ASC
@@ -173,7 +158,7 @@ SQL;
$repartition[(int) $value['period']] = (int) $value['count'];
}
- return $this->convertToSerie($repartition);
+ return $repartition;
}
/**
@@ -222,7 +207,7 @@ SQL;
SELECT COUNT(1) AS count
, MIN(date) AS date_min
, MAX(date) AS date_max
-FROM {$this->prefix}entry AS e
+FROM `{$this->prefix}entry` AS e
{$restrict}
SQL;
$stm = $this->bd->prepare($sql);
@@ -266,8 +251,8 @@ SQL;
$sql = <<<SQL
SELECT c.name AS label
, COUNT(f.id) AS data
-FROM {$this->prefix}category AS c,
-{$this->prefix}feed AS f
+FROM `{$this->prefix}category` AS c,
+`{$this->prefix}feed` AS f
WHERE c.id = f.category
GROUP BY label
ORDER BY data DESC
@@ -276,7 +261,7 @@ SQL;
$stm->execute();
$res = $stm->fetchAll(PDO::FETCH_ASSOC);
- return $this->convertToPieSerie($res);
+ return $res;
}
/**
@@ -289,9 +274,9 @@ SQL;
$sql = <<<SQL
SELECT c.name AS label
, COUNT(e.id) AS data
-FROM {$this->prefix}category AS c,
-{$this->prefix}feed AS f,
-{$this->prefix}entry AS e
+FROM `{$this->prefix}category` AS c,
+`{$this->prefix}feed` AS f,
+`{$this->prefix}entry` AS e
WHERE c.id = f.category
AND f.id = e.id_feed
GROUP BY label
@@ -301,7 +286,7 @@ SQL;
$stm->execute();
$res = $stm->fetchAll(PDO::FETCH_ASSOC);
- return $this->convertToPieSerie($res);
+ return $res;
}
/**
@@ -315,9 +300,9 @@ SELECT f.id AS id
, MAX(f.name) AS name
, MAX(c.name) AS category
, COUNT(e.id) AS count
-FROM {$this->prefix}category AS c,
-{$this->prefix}feed AS f,
-{$this->prefix}entry AS e
+FROM `{$this->prefix}category` AS c,
+`{$this->prefix}feed` AS f,
+`{$this->prefix}entry` AS e
WHERE c.id = f.category
AND f.id = e.id_feed
GROUP BY f.id
@@ -340,8 +325,8 @@ SELECT MAX(f.id) as id
, MAX(f.name) AS name
, MAX(date) AS last_date
, COUNT(*) AS nb_articles
-FROM {$this->prefix}feed AS f,
-{$this->prefix}entry AS e
+FROM `{$this->prefix}feed` AS f,
+`{$this->prefix}entry` AS e
WHERE f.id = e.id_feed
GROUP BY f.id
ORDER BY name
@@ -351,27 +336,6 @@ SQL;
return $stm->fetchAll(PDO::FETCH_ASSOC);
}
- protected function convertToSerie($data) {
- $serie = array();
-
- foreach ($data as $key => $value) {
- $serie[] = array($key, $value);
- }
-
- return $serie;
- }
-
- protected function convertToPieSerie($data) {
- $serie = array();
-
- foreach ($data as $value) {
- $value['data'] = array(array(0, (int) $value['data']));
- $serie[] = $value;
- }
-
- return $serie;
- }
-
/**
* Gets days ready for graphs
*
diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php
new file mode 100644
index 000000000..1effbb64b
--- /dev/null
+++ b/app/Models/StatsDAOPGSQL.php
@@ -0,0 +1,67 @@
+<?php
+
+class FreshRSS_StatsDAOPGSQL extends FreshRSS_StatsDAO {
+
+ /**
+ * Calculates the number of article per hour of the day per feed
+ *
+ * @param integer $feed id
+ * @return string
+ */
+ public function calculateEntryRepartitionPerFeedPerHour($feed = null) {
+ return $this->calculateEntryRepartitionPerFeedPerPeriod('hour', $feed);
+ }
+
+ /**
+ * Calculates the number of article per day of week per feed
+ *
+ * @param integer $feed id
+ * @return string
+ */
+ public function calculateEntryRepartitionPerFeedPerDayOfWeek($feed = null) {
+ return $this->calculateEntryRepartitionPerFeedPerPeriod('day', $feed);
+ }
+
+ /**
+ * Calculates the number of article per month per feed
+ *
+ * @param integer $feed
+ * @return string
+ */
+ public function calculateEntryRepartitionPerFeedPerMonth($feed = null) {
+ return $this->calculateEntryRepartitionPerFeedPerPeriod('month', $feed);
+ }
+
+ /**
+ * Calculates the number of article per period per feed
+ *
+ * @param string $period format string to use for grouping
+ * @param integer $feed id
+ * @return string
+ */
+ protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) {
+ $restrict = '';
+ if ($feed) {
+ $restrict = "WHERE e.id_feed = {$feed}";
+ }
+ $sql = <<<SQL
+SELECT extract( {$period} from to_timestamp(e.date)) AS period
+, COUNT(1) AS count
+FROM "{$this->prefix}entry" AS e
+{$restrict}
+GROUP BY period
+ORDER BY period ASC
+SQL;
+
+ $stm = $this->bd->prepare($sql);
+ $stm->execute();
+ $res = $stm->fetchAll(PDO::FETCH_NAMED);
+
+ foreach ($res as $value) {
+ $repartition[(int) $value['period']] = (int) $value['count'];
+ }
+
+ return $repartition;
+ }
+
+}
diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php
index 9bfe8b20a..6cfc20463 100644
--- a/app/Models/StatsDAOSQLite.php
+++ b/app/Models/StatsDAOSQLite.php
@@ -2,59 +2,8 @@
class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO {
- /**
- * Calculates entry count per day on a 30 days period.
- * Returns the result as a JSON object.
- *
- * @return JSON object
- */
- public function calculateEntryCount() {
- $count = $this->initEntryCountArray();
- $period = parent::ENTRY_COUNT_PERIOD;
-
- // Get stats per day for the last 30 days
- $sql = <<<SQL
-SELECT round(julianday(e.date, 'unixepoch') - julianday('now')) AS day,
-COUNT(1) AS count
-FROM {$this->prefix}entry AS e
-WHERE strftime('%Y%m%d', e.date, 'unixepoch')
- BETWEEN strftime('%Y%m%d', 'now', '-{$period} days')
- AND strftime('%Y%m%d', 'now', '-1 day')
-GROUP BY day
-ORDER BY day ASC
-SQL;
- $stm = $this->bd->prepare($sql);
- $stm->execute();
- $res = $stm->fetchAll(PDO::FETCH_ASSOC);
-
- foreach ($res as $value) {
- $count[(int) $value['day']] = (int) $value['count'];
- }
-
- return $this->convertToSerie($count);
- }
-
- /**
- * Calculates entry average per day on a 30 days period.
- *
- * @return integer
- */
- public function calculateEntryAverage() {
- $period = self::ENTRY_COUNT_PERIOD;
-
- // Get stats per day for the last 30 days
- $sql = <<<SQL
-SELECT COUNT(1) / {$period} AS average
-FROM {$this->prefix}entry AS e
-WHERE strftime('%Y%m%d', e.date, 'unixepoch')
- BETWEEN strftime('%Y%m%d', 'now', '-{$period} days')
- AND strftime('%Y%m%d', 'now', '-1 day')
-SQL;
- $stm = $this->bd->prepare($sql);
- $stm->execute();
- $res = $stm->fetch(PDO::FETCH_NAMED);
-
- return round($res['average'], 2);
+ protected function sqlFloor($s) {
+ return "CAST(($s) AS INT)";
}
protected function calculateEntryRepartitionPerFeedPerPeriod($period, $feed = null) {
@@ -66,7 +15,7 @@ SQL;
$sql = <<<SQL
SELECT strftime('{$period}', e.date, 'unixepoch') AS period
, COUNT(1) AS count
-FROM {$this->prefix}entry AS e
+FROM `{$this->prefix}entry` AS e
{$restrict}
GROUP BY period
ORDER BY period ASC
@@ -81,7 +30,7 @@ SQL;
$repartition[(int) $value['period']] = (int) $value['count'];
}
- return $this->convertToSerie($repartition);
+ return $repartition;
}
}
diff --git a/app/Models/UserDAO.php b/app/Models/UserDAO.php
index b55766ab4..a95ee6bc4 100644
--- a/app/Models/UserDAO.php
+++ b/app/Models/UserDAO.php
@@ -1,34 +1,60 @@
<?php
class FreshRSS_UserDAO extends Minz_ModelPdo {
- public function createUser($username) {
+ public function createUser($username, $new_user_language, $insertDefaultFeeds = true) {
$db = FreshRSS_Context::$system_conf->db;
require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php');
$userPDO = new Minz_ModelPdo($username);
- $ok = false;
- if (defined('SQL_CREATE_TABLES')) { //E.g. MySQL
- $sql = sprintf(SQL_CREATE_TABLES, $db['prefix'] . $username . '_', _t('gen.short.default_category'));
- $stm = $userPDO->bd->prepare($sql);
- $ok = $stm && $stm->execute();
- } 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, '', _t('gen.short.default_category'));
+ $currentLanguage = Minz_Translate::language();
+
+ try {
+ Minz_Translate::reset($new_user_language);
+ $ok = false;
+ $bd_prefix_user = $db['prefix'] . $username . '_';
+ if (defined('SQL_CREATE_TABLES')) { //E.g. MySQL
+ $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
+ global $SQL_CREATE_TABLES;
+ if (is_array($SQL_CREATE_TABLES)) {
+ $ok = true;
+ foreach ($SQL_CREATE_TABLES as $instruction) {
+ $sql = sprintf($instruction, $bd_prefix_user, _t('gen.short.default_category'));
+ $stm = $userPDO->bd->prepare($sql);
+ $ok &= ($stm && $stm->execute());
+ }
+ }
+ }
+ 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());
+ $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());
}
+ Minz_Translate::reset($currentLanguage);
+
if ($ok) {
return true;
} else {
$info = empty($stm) ? array(2 => 'syntax error') : $stm->errorInfo();
- Minz_Log::error('SQL error : ' . $info[2]);
+ Minz_Log::error('SQL error: ' . $info[2]);
return false;
}
}
generated by cgit v1.2.3 (git 2.39.1) at 2026-02-10 12:14:01 +0000