Mercurial > libervia-pubsub

comparison sat_pubsub/pgsql_storage.py @ 319:a51947371625

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
pgsql storage: use item_id instead of date to order and select items
author Goffi <goffi@goffi.org>
date Sun, 03 Jan 2016 18:33:22 +0100
parents d13526c0eb32
children 54d90c73b8b5
comparison
equal deleted inserted replaced
318:d13526c0eb32 319:a51947371625
736 args.append(filter_.value) 736 args.append(filter_.value)
737 else: 737 else:
738 query.append("AND publisher LIKE %s") 738 query.append("AND publisher LIKE %s")
739 args.append(u"{}%".format(filter_.value)) 739 args.append(u"{}%".format(filter_.value))
740 740
741 return "ORDER BY date DESC" 741 return "ORDER BY item_id DESC"
742 742
743 def _getItems(self, cursor, authorized_groups, unrestricted, maxItems, ext_data): 743 def _getItems(self, cursor, authorized_groups, unrestricted, maxItems, ext_data):
744 self._checkNodeExists(cursor) 744 self._checkNodeExists(cursor)
745 745
746 if maxItems == 0: 746 if maxItems == 0:
747 return [] 747 return []
748 748
749 args = [] 749 args = []
750 750
751 # SELECT 751 # SELECT
752 query = ["SELECT data,items.access_model,item_id,date"] 752 query = ["SELECT data,items.access_model,item_id"]
753 753
754 query_order = self._appendSourcesAndFilters(query, args, authorized_groups, unrestricted, ext_data) 754 query_order = self._appendSourcesAndFilters(query, args, authorized_groups, unrestricted, ext_data)
755 755
756 if 'rsm' in ext_data: 756 if 'rsm' in ext_data:
757 rsm = ext_data['rsm'] 757 rsm = ext_data['rsm']
758 maxItems = rsm.max 758 maxItems = rsm.max
759 if rsm.index is not None: 759 if rsm.index is not None:
760 # We need to know the date of corresponding to the index (offset) of the current query 760 # We need to know the item_id of corresponding to the index (offset) of the current query
761 # so we execute the query to look for the date 761 # so we execute the query to look for the item_id
762 tmp_query = query[:] 762 tmp_query = query[:]
763 tmp_args = args[:] 763 tmp_args = args[:]
764 tmp_query[0] = "SELECT date" 764 tmp_query[0] = "SELECT item_id"
765 tmp_query.append("{} LIMIT 1 OFFSET %s".format(query_order)) 765 tmp_query.append("{} LIMIT 1 OFFSET %s".format(query_order))
766 tmp_args.append(rsm.index) 766 tmp_args.append(rsm.index)
767 cursor.execute(' '.join(query), args) 767 cursor.execute(' '.join(query), args)
768 # FIXME: bad index is not managed yet 768 # FIXME: bad index is not managed yet
769 date = cursor.fetchall()[0][0] 769 item_id = cursor.fetchall()[0][0]
770 770
771 # now that we have the date, we can use it 771 # now that we have the id, we can use it
772 query.append("AND date<=%s") 772 query.append("AND item_id<=%s")
773 args.append(date) 773 args.append(item_id)
774 elif rsm.before is not None: 774 elif rsm.before is not None:
775 if rsm.before != '': 775 if rsm.before != '':
776 query.append("AND date>(SELECT date FROM items WHERE item=%s LIMIT 1)") 776 query.append("AND item_id>(SELECT item_id FROM items WHERE item=%s LIMIT 1)")
777 args.append(rsm.before) 777 args.append(rsm.before)
778 if maxItems is not None: 778 if maxItems is not None:
779 # if we have maxItems (i.e. a limit), we need to reverse order 779 # if we have maxItems (i.e. a limit), we need to reverse order
780 # in a first query to get the right items 780 # in a first query to get the right items
781 query.insert(0,"SELECT * from (") 781 query.insert(0,"SELECT * from (")
782 query.append("ORDER BY date ASC LIMIT %s) as x") 782 query.append("ORDER BY item_id ASC LIMIT %s) as x")
783 args.append(maxItems) 783 args.append(maxItems)
784 elif rsm.after: 784 elif rsm.after:
785 query.append("AND date<(SELECT date FROM items WHERE item=%s LIMIT 1)") 785 query.append("AND item_id<(SELECT item_id FROM items WHERE item=%s LIMIT 1)")
786 args.append(rsm.after) 786 args.append(rsm.after)
787 787
788 query.append(query_order) 788 query.append(query_order)
789 789
790 if maxItems is not None: 790 if maxItems is not None: