fix sql

5 files changed, 28 insertions, 19 deletions

diff --git a/CHANGELOG b/CHANGELOG
index 289c8643e..c6dbb068b 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -3,7 +3,7 @@ owncloud-news (1.601)
 * Replace Google Reader import with export and import of unread and starred articles
 * Autopurge limit is now added to the number of articles each feed gets when it updates
 * Fix CORS headers for OPTIONS request deeper than one level
-* Use before and after update cleanup hooks to make sure that read items are not turned unread again after an update
+* Use before and after update cleanup hooks to make sure that read items are not turned unread again after an update. This breaks custom updaters. The updater script has been adjusted accordingly
 
 owncloud-news (1.404)
 * Fix bug on postgres databases that would not delete old articles
diff --git a/db/itemmapper.php b/db/itemmapper.php
index 891cb624d..1ec283826 100644
--- a/db/itemmapper.php
+++ b/db/itemmapper.php
@@ -259,12 +259,12 @@ class ItemMapper extends Mapper implements IMapper {
 	public function deleteReadOlderThanThreshold($threshold){
 		$status = StatusFlag::STARRED | StatusFlag::UNREAD;
 		$sql = 'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' .
-		'`items`.`feed_id` AS `feed_id`, ' . 
+		'`items`.`feed_id` AS `feed_id` ' . 
 			'FROM `*PREFIX*news_items` `items` ' .
 			'JOIN `*PREFIX*news_feeds` `feeds` ' .
 				'ON `feeds`.`id` = `items`.`feed_id` ' .
 			'WHERE NOT ((`items`.`status` & ?) > 0) ' .
-			'GROUP BY `items`.`feed_id` ' .
+			'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' .
 			'HAVING COUNT(*) > ?';
 		$params = array($status, $threshold);
 		$result = $this->execute($sql, $params);
diff --git a/db/postgres/itemmapper.php b/db/postgres/itemmapper.php
index ddf83ae10..0ccc9eeb4 100644
--- a/db/postgres/itemmapper.php
+++ b/db/postgres/itemmapper.php
@@ -45,10 +45,13 @@ class ItemMapper extends \OCA\News\Db\ItemMapper {
 	 */
 	public function deleteReadOlderThanThreshold($threshold){
 		$status = StatusFlag::STARRED | StatusFlag::UNREAD;
-		$sql = 'SELECT COUNT(*) `size`, `feed_id` ' .
-			'FROM `*PREFIX*news_items` ' .
-			'WHERE NOT ((`status` & ?) > 0) ' .
-			'GROUP BY `feed_id` ' .
+		$sql = 'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' .
+		'`items`.`feed_id` AS `feed_id` ' . 
+			'FROM `*PREFIX*news_items` `items` ' .
+			'JOIN `*PREFIX*news_feeds` `feeds` ' .
+				'ON `feeds`.`id` = `items`.`feed_id` ' .
+			'WHERE NOT ((`items`.`status` & ?) > 0) ' .
+			'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' .
 			'HAVING COUNT(*) > ?';
 		$params = array($status, $threshold);
 		$result = $this->execute($sql, $params);
diff --git a/tests/unit/db/ItemMapperTest.php b/tests/unit/db/ItemMapperTest.php
index bba3e143e..fb7489f3d 100644
--- a/tests/unit/db/ItemMapperTest.php
+++ b/tests/unit/db/ItemMapperTest.php
@@ -326,12 +326,12 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility {
 	public function testDeleteReadOlderThanThresholdDoesNotDeleteBelowThreshold(){
 		$status = StatusFlag::STARRED | StatusFlag::UNREAD;
 		$sql =  'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' .
-		'`items`.`feed_id` AS `feed_id`, ' . 
+		'`items`.`feed_id` AS `feed_id` ' . 
 			'FROM `*PREFIX*news_items` `items` ' .
 			'JOIN `*PREFIX*news_feeds` `feeds` ' .
 				'ON `feeds`.`id` = `items`.`feed_id` ' .
 			'WHERE NOT ((`items`.`status` & ?) > 0) ' .
-			'GROUP BY `items`.`feed_id` ' .
+			'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' .
 			'HAVING COUNT(*) > ?';
 
 		$threshold = 10;
@@ -350,12 +350,12 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility {
 		$status = StatusFlag::STARRED | StatusFlag::UNREAD;
 
 		$sql1 = 'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' .
-		'`items`.`feed_id` AS `feed_id`, ' . 
+		'`items`.`feed_id` AS `feed_id` ' . 
 			'FROM `*PREFIX*news_items` `items` ' .
 			'JOIN `*PREFIX*news_feeds` `feeds` ' .
 				'ON `feeds`.`id` = `items`.`feed_id` ' .
 			'WHERE NOT ((`items`.`status` & ?) > 0) ' .
-			'GROUP BY `items`.`feed_id` ' .
+			'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' .
 			'HAVING COUNT(*) > ?';
 		$params1 = array($status, $threshold);
 
diff --git a/tests/unit/db/postgres/ItemMapperTest.php b/tests/unit/db/postgres/ItemMapperTest.php
index 3e9fb8867..0dad0471a 100644
--- a/tests/unit/db/postgres/ItemMapperTest.php
+++ b/tests/unit/db/postgres/ItemMapperTest.php
@@ -109,10 +109,13 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility {
 
 	public function testDeleteReadOlderThanThresholdDoesNotDeleteBelowThreshold(){
 		$status = StatusFlag::STARRED | StatusFlag::UNREAD;
-		$sql = 'SELECT COUNT(*) `size`, `feed_id` ' .
-			'FROM `*PREFIX*news_items` ' .
-			'WHERE NOT ((`status` & ?) > 0) ' .
-			'GROUP BY `feed_id` ' .
+		$sql = 'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' .
+		'`items`.`feed_id` AS `feed_id` ' . 
+			'FROM `*PREFIX*news_items` `items` ' .
+			'JOIN `*PREFIX*news_feeds` `feeds` ' .
+				'ON `feeds`.`id` = `items`.`feed_id` ' .
+			'WHERE NOT ((`items`.`status` & ?) > 0) ' .
+			'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' .
 			'HAVING COUNT(*) > ?';
 
 		$threshold = 10;
@@ -130,10 +133,13 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility {
 		$threshold = 10;
 		$status = StatusFlag::STARRED | StatusFlag::UNREAD;
 
-		$sql1 = 'SELECT COUNT(*) `size`, `feed_id` ' .
-			'FROM `*PREFIX*news_items` ' .
-			'WHERE NOT ((`status` & ?) > 0) ' .
-			'GROUP BY `feed_id` ' .
+		$sql1 = 'SELECT COUNT(*) - `feeds`.`articles_per_update` AS `size`, ' .
+		'`items`.`feed_id` AS `feed_id` ' . 
+			'FROM `*PREFIX*news_items` `items` ' .
+			'JOIN `*PREFIX*news_feeds` `feeds` ' .
+				'ON `feeds`.`id` = `items`.`feed_id` ' .
+			'WHERE NOT ((`items`.`status` & ?) > 0) ' .
+			'GROUP BY `items`.`feed_id`, `feeds`.`articles_per_update` ' .
 			'HAVING COUNT(*) > ?';
 		$params1 = array($status, $threshold);