Mercurial > libervia-pubsub

comparison sat_pubsub/pgsql_storage.py @ 351:2098295747fd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
pgsql: cast items.data to text when needed + db schema version bump.
author Goffi <goffi@goffi.org>
date Fri, 08 Sep 2017 08:02:05 +0200
parents 20b82fb8de02
children efbdca10f0fb
comparison
equal deleted inserted replaced
350:4d4575911060 351:2098295747fd
73 psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY) 73 psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
74 74
75 # parseXml manage str, but we get unicode 75 # parseXml manage str, but we get unicode
76 parseXml = lambda unicode_data: generic.parseXml(unicode_data.encode('utf-8')) 76 parseXml = lambda unicode_data: generic.parseXml(unicode_data.encode('utf-8'))
77 PEP_COL_NAME = 'pep' 77 PEP_COL_NAME = 'pep'
78 CURRENT_VERSION = '2' 78 CURRENT_VERSION = '3'
79 79
80 80
81 def withPEP(query, values, pep, recipient): 81 def withPEP(query, values, pep, recipient):
82 """Helper method to facilitate PEP management 82 """Helper method to facilitate PEP management
83 83
376 raise ValueError("entities, nodes and accesses must not be empty") 376 raise ValueError("entities, nodes and accesses must not be empty")
377 if node_accesses != ('open',) or item_accesses != ('open',): 377 if node_accesses != ('open',) or item_accesses != ('open',):
378 raise NotImplementedError('only "open" access model is handled for now') 378 raise NotImplementedError('only "open" access model is handled for now')
379 if not pep: 379 if not pep:
380 raise NotImplementedError(u"getLastItems is only implemented for PEP at the moment") 380 raise NotImplementedError(u"getLastItems is only implemented for PEP at the moment")
381 d = self.dbpool.runQuery("""SELECT DISTINCT ON (node_id) pep, node, data, items.access_model 381 d = self.dbpool.runQuery("""SELECT DISTINCT ON (node_id) pep, node, data::text, items.access_model
382 FROM items 382 FROM items
383 NATURAL JOIN nodes 383 NATURAL JOIN nodes
384 WHERE nodes.pep IN %s 384 WHERE nodes.pep IN %s
385 AND node IN %s 385 AND node IN %s
386 AND nodes.access_model in %s 386 AND nodes.access_model in %s
889 889
890 # SELECT 890 # SELECT
891 if ids_only: 891 if ids_only:
892 query = ["SELECT item"] 892 query = ["SELECT item"]
893 else: 893 else:
894 query = ["SELECT data,items.access_model,item_id,date"] 894 query = ["SELECT data::text,items.access_model,item_id,date"]
895 895
896 query_order = self._appendSourcesAndFilters(query, args, authorized_groups, unrestricted, ext_data) 896 query_order = self._appendSourcesAndFilters(query, args, authorized_groups, unrestricted, ext_data)
897 897
898 if 'rsm' in ext_data: 898 if 'rsm' in ext_data:
899 rsm = ext_data['rsm'] 899 rsm = ext_data['rsm']
974 def _getItemsById(self, cursor, authorized_groups, unrestricted, itemIdentifiers): 974 def _getItemsById(self, cursor, authorized_groups, unrestricted, itemIdentifiers):
975 self._checkNodeExists(cursor) 975 self._checkNodeExists(cursor)
976 ret = [] 976 ret = []
977 if unrestricted: #we get everything without checking permissions 977 if unrestricted: #we get everything without checking permissions
978 for itemIdentifier in itemIdentifiers: 978 for itemIdentifier in itemIdentifiers:
979 cursor.execute("""SELECT data,items.access_model,item_id,date FROM nodes 979 cursor.execute("""SELECT data::text,items.access_model,item_id,date FROM nodes
980 INNER JOIN items USING (node_id) 980 INNER JOIN items USING (node_id)
981 WHERE node_id=%s AND item=%s""", 981 WHERE node_id=%s AND item=%s""",
982 (self.nodeDbId, 982 (self.nodeDbId,
983 itemIdentifier)) 983 itemIdentifier))
984 result = cursor.fetchone() 984 result = cursor.fetchone()
999 else: #we check permission before returning items 999 else: #we check permission before returning items
1000 for itemIdentifier in itemIdentifiers: 1000 for itemIdentifier in itemIdentifiers:
1001 args = [self.nodeDbId, itemIdentifier] 1001 args = [self.nodeDbId, itemIdentifier]
1002 if authorized_groups: 1002 if authorized_groups:
1003 args.append(authorized_groups) 1003 args.append(authorized_groups)
1004 cursor.execute("""SELECT data, date FROM nodes 1004 cursor.execute("""SELECT data::text, date FROM nodes
1005 INNER JOIN items USING (node_id) 1005 INNER JOIN items USING (node_id)
1006 LEFT JOIN item_groups_authorized USING (item_id) 1006 LEFT JOIN item_groups_authorized USING (item_id)
1007 WHERE node_id=%s AND item=%s AND 1007 WHERE node_id=%s AND item=%s AND
1008 (items.access_model='open' """ + 1008 (items.access_model='open' """ +
1009 ("or (items.access_model='roster' and groupname in %s)" if authorized_groups else '') + ")", 1009 ("or (items.access_model='roster' and groupname in %s)" if authorized_groups else '') + ")",