# HG changeset patch
# User Goffi <goffi@goffi.org>
# Date 1451842402 -3600
# Node ID a51947371625a71e0c5973b7aa272928b8b95fd3
# Parent  d13526c0eb32a478363a3861006d51feee03e080
pgsql storage: use item_id instead of date to order and select items

diff -r d13526c0eb32 -r a51947371625 sat_pubsub/pgsql_storage.py
--- a/sat_pubsub/pgsql_storage.py	Sun Jan 03 18:33:22 2016 +0100
+++ b/sat_pubsub/pgsql_storage.py	Sun Jan 03 18:33:22 2016 +0100
@@ -738,7 +738,7 @@
                         query.append("AND publisher LIKE %s")
                         args.append(u"{}%".format(filter_.value))
 
-        return "ORDER BY date DESC"
+        return "ORDER BY item_id DESC"
 
     def _getItems(self, cursor, authorized_groups, unrestricted, maxItems, ext_data):
         self._checkNodeExists(cursor)
@@ -749,7 +749,7 @@
         args = []
 
         # SELECT
-        query = ["SELECT data,items.access_model,item_id,date"]
+        query = ["SELECT data,items.access_model,item_id"]
 
         query_order = self._appendSourcesAndFilters(query, args, authorized_groups, unrestricted, ext_data)
 
@@ -757,32 +757,32 @@
             rsm = ext_data['rsm']
             maxItems = rsm.max
             if rsm.index is not None:
-                # We need to know the date of corresponding to the index (offset) of the current query
-                # so we execute the query to look for the date
+                # We need to know the item_id of corresponding to the index (offset) of the current query
+                # so we execute the query to look for the item_id
                 tmp_query = query[:]
                 tmp_args = args[:]
-                tmp_query[0] = "SELECT date"
+                tmp_query[0] = "SELECT item_id"
                 tmp_query.append("{} LIMIT 1 OFFSET %s".format(query_order))
                 tmp_args.append(rsm.index)
                 cursor.execute(' '.join(query), args)
                 # FIXME: bad index is not managed yet
-                date = cursor.fetchall()[0][0]
+                item_id = cursor.fetchall()[0][0]
 
-                # now that we have the date, we can use it
-                query.append("AND date<=%s")
-                args.append(date)
+                # now that we have the id, we can use it
+                query.append("AND item_id<=%s")
+                args.append(item_id)
             elif rsm.before is not None:
                 if rsm.before != '':
-                    query.append("AND date>(SELECT date FROM items WHERE item=%s LIMIT 1)")
+                    query.append("AND item_id>(SELECT item_id FROM items WHERE item=%s LIMIT 1)")
                     args.append(rsm.before)
                 if maxItems is not None:
                     # if we have maxItems (i.e. a limit), we need to reverse order
                     # in a first query to get the right items
                     query.insert(0,"SELECT * from (")
-                    query.append("ORDER BY date ASC LIMIT %s) as x")
+                    query.append("ORDER BY item_id ASC LIMIT %s) as x")
                     args.append(maxItems)
             elif rsm.after:
-                query.append("AND date<(SELECT date FROM items WHERE item=%s LIMIT 1)")
+                query.append("AND item_id<(SELECT item_id FROM items WHERE item=%s LIMIT 1)")
                 args.append(rsm.after)
 
         query.append(query_order)