Mercurial > libervia-pubsub

comparison sat_pubsub/pgsql_storage.py @ 456:b52ebc45b8e3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
psql: fix RSM requests handling: the sub-query to get row number to filter on for `before` and `after` was missing some filters and thus incorrect. This has been fixed by re-using `_appendSourcesAndFilters`. rel: 399
author Goffi <goffi@goffi.org>
date Thu, 30 Sep 2021 16:55:05 +0200
parents 7f1394bb96db
children 7c9792f934a2
comparison
equal deleted inserted replaced
455:0b5233981671 456:b52ebc45b8e3
1180 if rsm.before != '': 1180 if rsm.before != '':
1181 query.insert(0,"SELECT * from (") 1181 query.insert(0,"SELECT * from (")
1182 query[1] += f",row_number() OVER ({query_order}) as row_number" 1182 query[1] += f",row_number() OVER ({query_order}) as row_number"
1183 query.append(f"{query_order}) as x") 1183 query.append(f"{query_order}) as x")
1184 query.append( 1184 query.append(
1185 f"WHERE row_number<(SELECT row_number FROM (SELECT row_number() OVER ({query_order}) as row_number,item FROM items WHERE node_id=%s) as row_num_sub WHERE item=%s)" 1185 "WHERE row_number<(SELECT row_number FROM (SELECT row_number() "
1186 f"OVER ({query_order}) as row_number,item"
1186 ) 1187 )
1187 args.extend((self.nodeDbId, rsm.before)) 1188 self._appendSourcesAndFilters(
1189 query, args, authorized_groups, unrestricted, ext_data
1190 )
1191 query.append(") as row_num_sub WHERE item=%s)")
1192 args.append(rsm.before)
1188 # we need to reverse order in a first query to get the right 1193 # we need to reverse order in a first query to get the right
1189 # items 1194 # items
1190 query.insert(0,"SELECT * from (") 1195 query.insert(0,"SELECT * from (")
1191 query.append(self.getOrderBy(ext_data, direction='DESC')) 1196 query.append(self.getOrderBy(ext_data, direction='DESC'))
1192 query.append("LIMIT %s) as x") 1197 query.append("LIMIT %s) as x")
1202 elif rsm.after: 1207 elif rsm.after:
1203 query.insert(0,"SELECT * from (") 1208 query.insert(0,"SELECT * from (")
1204 query[1] += f",row_number() OVER ({query_order}) as row_number" 1209 query[1] += f",row_number() OVER ({query_order}) as row_number"
1205 query.append(f"{query_order}) as x") 1210 query.append(f"{query_order}) as x")
1206 query.append( 1211 query.append(
1207 f"WHERE row_number>(SELECT row_number FROM (SELECT row_number() OVER ({query_order}) as row_number,item FROM items WHERE node_id=%s) as row_num_sub WHERE item=%s)" 1212 "WHERE row_number>(SELECT row_number FROM (SELECT row_number() "
1213 f"OVER ({query_order}) as row_number,item"
1208 ) 1214 )
1209 args.extend((self.nodeDbId, rsm.after)) 1215 self._appendSourcesAndFilters(
1216 query, args, authorized_groups, unrestricted, ext_data
1217 )
1218 query.append(") as row_num_sub WHERE item=%s)")
1219 args.append(rsm.after)
1210 else: 1220 else:
1211 rsm = False 1221 rsm = False
1212 1222
1213 query.append(query_order) 1223 query.append(query_order)
1214 1224