Mercurial > libervia-pubsub

annotate idavoll/pgsql_backend.py @ 43:9685b7e291ef

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Moved common stuff out of pgsql_backend.py to backend.py. Implemented Storage class for memory backend. Implemented item storage for pgsql Storage.
author Ralph Meijer <ralphm@ik.nu>
date Mon, 01 Nov 2004 12:37:40 +0000
parents ea3d3544a52e
children 4447b3c5b857
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
28
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
1 from twisted.application import service
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
2 from twisted.internet import defer
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
3 from twisted.protocols.jabber import jid
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
4 from twisted.enterprise import adbapi
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
5 import backend
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
6
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
7 class Storage:
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
8 def __init__(self, user, database):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
9 self.dbpool = adbapi.ConnectionPool('pyPgSQL.PgSQL', user=user,
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
10 database=database)
28
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
11
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
12 def _get_node_configuration(self, cursor, node_id):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
13 configuration = {}
28
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
14 cursor.execute("""SELECT persistent, deliver_payload FROM nodes
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
15 WHERE node=%s""",
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
16 (node_id,))
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
17 try:
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
18 (configuration["persist_items"],
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
19 configuration["deliver_payloads"]) = cursor.fetchone()
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
20 return configuration
28
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
21 except TypeError:
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
22 raise backend.NodeNotFound
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
23
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
24 def get_node_configuration(self, node_id):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
25 return self.dbpool.runInteraction(self._get_node_configuration, node_id)
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
26
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
27 def _get_affiliation(self, cursor, node_id, entity):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
28 cursor.execute("""SELECT affiliation FROM affiliations
28
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
29 JOIN nodes ON (node_id=nodes.id)
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
30 JOIN entities ON (entity_id=entities.id)
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
31 WHERE node=%s AND jid=%s""",
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
32 (node_id.encode('utf8'), entity.encode('utf8')))
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
33
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
34 try:
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
35 return cursor.fetchone()[0]
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
36 except TypeError:
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
37 return None
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
38
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
39 def get_affiliation(self, node_id, entity):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
40 return self.dbpool.runInteraction(self._get_affiliation, node_id,
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
41 entity)
28
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
42
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
43 def get_subscribers(self, node_id):
43
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
44 d = self.dbpool.runQuery("""SELECT jid, resource FROM subscriptions
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
45 JOIN nodes ON (node_id=nodes.id)
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
46 JOIN entities ON (entity_id=entities.id)
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
47 WHERE node=%s AND
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
48 subscription='subscribed'""",
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
49 (node_id.encode('utf8'),))
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
50 d.addCallback(self._convert_to_jids)
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
51 return d
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
52
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
53 def _convert_to_jids(self, list):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
54 return [jid.JID("%s/%s" % (l[0], l[1])).full() for l in list]
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
55
43
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
56 def store_items(self, node_id, items, publisher):
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
57 return self.dbpool.runInteraction(self._store_items, node_id, items,
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
58 publisher)
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
59
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
60 def _store_items(self, cursor, node_id, items, publisher):
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
61 for item in items:
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
62 self._store_item(cursor, node_id, item, publisher)
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
63
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
64 def _store_item(self, cursor, node_id, item, publisher):
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
65 data = item.toXml()
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
66 cursor.execute("""UPDATE items SET publisher=%s, data=%s
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
67 FROM nodes
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
68 WHERE nodes.id = items.node_id AND
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
69 nodes.node = %s and items.item=%s""",
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
70 (publisher.encode('utf8'),
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
71 data.encode('utf8'),
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
72 node_id.encode('utf8'),
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
73 item["id"].encode('utf8')))
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
74 if cursor.rowcount == 1:
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
75 return
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
76
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
77 cursor.execute("""INSERT INTO items (node_id, item, publisher, data)
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
78 SELECT id, %s, %s, %s FROM nodes WHERE node=%s""",
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
79 (item["id"].encode('utf8'),
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
80 publisher.encode('utf8'),
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
81 data.encode('utf8'),
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
82 node_id.encode('utf8')))
9685b7e291ef Moved common stuff out of pgsql_backend.py to backend.py.
Ralph Meijer <ralphm@ik.nu>
parents: 41
diff changeset
83
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
84 class BackendService(backend.BackendService):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
85 """ PostgreSQL backend Service for a JEP-0060 pubsub service """
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
86
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
87 class PublishService(service.Service):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
88
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
89 __implements__ = backend.IPublishService,
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
90
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
91 def publish(self, node_id, items, requestor):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
92 return self.parent.publish(node_id, items, requestor)
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
93
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
94 class NotificationService(backend.NotificationService):
28
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
95
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
96 __implements__ = backend.INotificationService,
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
97
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
98 def get_notification_list(self, node_id, items):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
99 return self.parent.get_notification_list(node_id, items)
28
39d0c6fa027f Initial revision
Ralph Meijer <ralphm@ik.nu>
parents:
diff changeset
100
41
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
101 class PersistenceService(service.Service):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
102
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
103 __implements__ = backend.IPersistenceService,
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
104
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
105 def store_items(self, node_id, items, publisher):
ea3d3544a52e Rewrite using separated backend interfaces. The backend also uses a separate
Ralph Meijer <ralphm@ik.nu>
parents: 28
diff changeset
106 return self.parent.store_items(node_id, items, publisher)