Bugfixes: There were two problems with the internal sorting of items which have been fixed. This results in version 2.1.2 (database update recommended):v2.1.2

+ Bugfix [core]: If the insertion date of a newly created item has been placed to a datetime in the past after which other items have already appeared, the sorting of the forward and backward functionality has been interrupted because the WHERE clause in the database query had the condition that the next item must have a higher ID and the previous item must have a lower ID than the current one. If this newly created item was the last one and the insertion date had been placed to a datetime in the past, no next item could be fetched because THIS is already the item with the highest ID and the correct sorting by time_insert for this item has stopped working.

+ Bugfix [core]: If one or more items had exactly the same insertion time, the items with the exact same time has been appeard within the overview list in an order which did not correspond to the insertion sequence. There were several ways to fix this problem, but this would result in more complicated database queries and more code. To fix the problem in the simplest way, the column "time_insert" now has a UNIQUE index to prevent two or more items from having the exactly same insertion time.

Database update to version 2.1.2 (no existing data will be lost or changed):

ALTER TABLE `page` ADD UNIQUE KEY `time_insert` (`time_insert`);
ALTER TABLE `post` ADD UNIQUE KEY `time_insert` (`time_insert`);
ALTER TABLE `user` ADD UNIQUE KEY `time_insert` (`time_insert`);

2 files changed, 7 insertions, 7 deletions

diff --git a/core/namespace/Item.php b/core/namespace/Item.php
index 3192d05..d21a4d6 100644
--- a/core/namespace/Item.php
+++ b/core/namespace/Item.php
@@ -126,12 +126,12 @@ abstract class Item implements ItemInterface {
 	# Return previous item ID
 	#===============================================================================
 	public function getPrevID(): int {
-		$execute = 'SELECT id FROM %s WHERE DATE(time_insert) <= DATE(?) AND id < ? ORDER BY time_insert DESC, id DESC LIMIT 1';
+		$execute = 'SELECT id FROM %s WHERE time_insert < ? ORDER BY time_insert DESC LIMIT 1';
 
 		$attribute = "{$this->Reflection->getNamespaceName()}\\Attribute";
 		$Statement = $this->Database->prepare(sprintf($execute, $attribute::TABLE));
 
-		if($Statement->execute([$this->Attribute->get('time_insert'), $this->Attribute->get('id')])) {
+		if($Statement->execute([$this->Attribute->get('time_insert')])) {
 			return $Statement->fetchColumn();
 		}
 
@@ -142,12 +142,12 @@ abstract class Item implements ItemInterface {
 	# Return next item ID
 	#===============================================================================
 	public function getNextID(): int {
-		$execute = 'SELECT id FROM %s WHERE DATE(time_insert) >= DATE(?) AND id > ? ORDER BY time_insert ASC, id DESC LIMIT 1';
+		$execute = 'SELECT id FROM %s WHERE time_insert > ? ORDER BY time_insert ASC LIMIT 1';
 
 		$attribute = "{$this->Reflection->getNamespaceName()}\\Attribute";
 		$Statement = $this->Database->prepare(sprintf($execute, $attribute::TABLE));
 
-		if($Statement->execute([$this->Attribute->get('time_insert'), $this->Attribute->get('id')])) {
+		if($Statement->execute([$this->Attribute->get('time_insert')])) {
 			return $Statement->fetchColumn();
 		}
 
diff --git a/database.sql b/database.sql
index d85089a..9669280 100644
--- a/database.sql
+++ b/database.sql
@@ -55,10 +55,10 @@ INSERT INTO `user` (`id`, `time_insert`, `time_update`, `slug`, `username`, `pas
 -- =============================================================================
 -- Add keys for tables
 -- =============================================================================
-ALTER TABLE `page` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `page_user` (`user`);
-ALTER TABLE `post` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `post_user` (`user`);
+ALTER TABLE `page` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `time_insert` (`time_insert`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `page_user` (`user`);
+ALTER TABLE `post` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `time_insert` (`time_insert`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `post_user` (`user`);
 ALTER TABLE `post` ADD FULLTEXT KEY `body` (`body`);
-ALTER TABLE `user` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `username` (`username`), ADD UNIQUE KEY `slug` (`slug`);
+ALTER TABLE `user` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `time_insert` (`time_insert`), ADD UNIQUE KEY `username` (`username`), ADD UNIQUE KEY `slug` (`slug`);
 
 -- =============================================================================
 -- Add AUTO_INCREMENT for tables