push_notifications:postgresql
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
push_notifications:postgresql [2014/11/06 17:32] – letti | push_notifications:postgresql [2015/03/03 17:33] (current) – letti | ||
---|---|---|---|
Line 56: | Line 56: | ||
==== 1. creare funzione per push con plpythonu (plpython2) ==== | ==== 1. creare funzione per push con plpythonu (plpython2) ==== | ||
- | To install PL/Python in a particular database, | + | To install PL/Python in a particular database: |
+ | 1. apt-get install postgresql-plpython if necessary | ||
+ | 2. form shell, createlang | ||
+ | Since plpythonu is untrusted, onlu superusers can use it. | ||
+ | |||
+ | Create file with CREATE FUNCTION | ||
+ | |||
+ | Apply sql to db | ||
+ | |||
+ | ACS Login: | ||
+ | |||
+ | < | ||
+ | |||
+ | CREATE OR REPLACE FUNCTION push_notifications_login(url text, login text, password text) | ||
+ | RETURNS text AS | ||
+ | $BODY$ | ||
+ | |||
+ | import urllib, urllib2 | ||
+ | from cookielib import CookieJar | ||
+ | |||
+ | cj = CookieJar() | ||
+ | opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj)) | ||
+ | |||
+ | login_values = {' | ||
+ | login_data = urllib.urlencode(login_values) | ||
+ | |||
+ | login_resp = opener.open(url, | ||
+ | return login_resp.getcode() | ||
+ | | ||
+ | $BODY$ | ||
+ | LANGUAGE plpythonu VOLATILE | ||
+ | COST 100; | ||
+ | ALTER FUNCTION push_notifications_login(text, | ||
+ | OWNER TO postgres; | ||
+ | |||
+ | </ | ||
+ | |||
+ | ACS push: | ||
+ | |||
+ | < | ||
+ | |||
+ | CREATE OR REPLACE FUNCTION push_notifications_notify(login_url text, login text, password text, push_url text, channel text, token text, payload text) | ||
+ | RETURNS text AS | ||
+ | $BODY$ | ||
+ | |||
+ | import urllib, urllib2 | ||
+ | from cookielib import CookieJar | ||
+ | |||
+ | cj = CookieJar() | ||
+ | opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj)) | ||
+ | |||
+ | login_values = {' | ||
+ | login_data = urllib.urlencode(login_values) | ||
+ | |||
+ | login_resp = opener.open(login_url, | ||
+ | |||
+ | if login_resp.getcode() == ' | ||
+ | |||
+ | notify_values = {' | ||
+ | notify_data = urllib.urlencode(notify_values) | ||
+ | |||
+ | notify_resp = opener.open(push_url, | ||
+ | | ||
+ | if notify_restp.getcode() == ' | ||
+ | return True | ||
+ | else: | ||
+ | return False | ||
+ | |||
+ | $BODY$ | ||
+ | LANGUAGE plpythonu VOLATILE | ||
+ | COST 100; | ||
+ | ALTER FUNCTION push_notifications_notify(text, | ||
+ | OWNER TO postgres; | ||
+ | |||
+ | </ | ||
==== 2. push con plpythonu (plpython2) da shell postgresql ==== | ==== 2. push con plpythonu (plpython2) da shell postgresql ==== | ||
SLELECT[function_name] | SLELECT[function_name] | ||
+ | ESEMPI: | ||
+ | |||
+ | SELECT push_notifications_login(url := ' | ||
+ | |||
+ | SELECT push_notifications_notify(url := ' | ||
==== 3. creare trigger pl/pgsql ==== | ==== 3. creare trigger pl/pgsql ==== | ||
+ | < | ||
+ | |||
+ | CREATE OR REPLACE FUNCTION notify_status_update() RETURNS trigger AS $notify_status_update$ | ||
+ | DECLARE | ||
+ | |||
+ | app_key text; | ||
+ | channel text; | ||
+ | token text; | ||
+ | payload text; | ||
+ | login_url text; | ||
+ | notify_url text; | ||
+ | | ||
+ | BEGIN | ||
+ | |||
+ | EXECUTE format(' | ||
+ | EXECUTE format(' | ||
+ | EXECUTE format(' | ||
+ | payload := ' | ||
+ | login_url := ' | ||
+ | notify_url := ' | ||
+ | |||
+ | -- EXECUTE format(' | ||
+ | ) USING login_url; | ||
+ | | ||
+ | | ||
+ | EXECUTE format(' | ||
+ | ) USING login_url, login, password, notify_url, channel, token, payload; | ||
+ | | ||
+ | END; | ||
+ | $notify_status_update$ LANGUAGE plpgsql; | ||
+ | |||
+ | CREATE TRIGGER notify_status_update BEFORE UPDATE ON honeywell_heatpump | ||
+ | FOR EACH ROW EXECUTE PROCEDURE notify_status_update() | ||
+ | | ||
+ | </ | ||
==== 4. agganciare trigger al server ==== | ==== 4. agganciare trigger al server ==== | ||
+ | |||
+ | ==== 5. ESEMPIO ==== | ||
+ | |||
+ | Target database: | ||
+ | |||
+ | < | ||
+ | Lista delle relazioni | ||
+ | | ||
+ | --------+---------------------------------+----------+-------------- | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | </ | ||
+ | |||
+ | < | ||
+ | |||
+ | CREATE TABLE " | ||
+ | CREATE TABLE " | ||
+ | CREATE TABLE " | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | Define functions as in section 2 and 3. | ||
+ | |||
+ | < | ||
+ | |||
+ | INSERT INTO honeywell_heatpump (heat, | ||
+ | INSERT INTO honeywell_channel (channel) VALUES (' | ||
+ | INSERT INTO honeywell_heatpump_token (heat_pump_id, | ||
+ | |||
+ | </ | ||
+ | |||
+ | < | ||
+ | |||
+ | UPDATE honeywell_heatpump SET status = 1; | ||
+ | |||
+ | </ | ||
+ | |||
+ | ==== 5.1 ESEMPIO 2 ==== | ||
+ | |||
+ | Esempio di trigger su una tabella di uno schema. | ||
+ | |||
+ | === Creazione funzione da triggherare === | ||
+ | |||
+ | < | ||
+ | |||
+ | CREATE OR REPLACE FUNCTION notify_status_update() RETURNS trigger AS $notify_status_update$ | ||
+ | DECLARE | ||
+ | |||
+ | app_key text := ' | ||
+ | channel text := TG_TABLE_SCHEMA; | ||
+ | token text := ' | ||
+ | payload text; | ||
+ | login_url text := ' | ||
+ | notify_url text := ' | ||
+ | |||
+ | OLD RECORD; | ||
+ | TABLE_FULLNAME text := '"' | ||
+ | board_label text; | ||
+ | |||
+ | BEGIN | ||
+ | | ||
+ | |||
+ | RAISE NOTICE ' | ||
+ | SELECT label FROM acs_service.chamber WHERE dbname = TG_TABLE_SCHEMA INTO board_label; | ||
+ | SELECT * FROM get_last_reliable_var_row(TABLE_FULLNAME, | ||
+ | | ||
+ | id integer, | ||
+ | variablename character varying, | ||
+ | val character varying, | ||
+ | statuscode character varying, | ||
+ | " | ||
+ | timestampe integer, | ||
+ | started integer, | ||
+ | sampletime integer, | ||
+ | idactor integer | ||
+ | ) INTO OLD; | ||
+ | |||
+ | RAISE NOTICE ' | ||
+ | -- Do nothing if only statuscode is changed... | ||
+ | IF OLD.val != NEW.val THEN | ||
+ | |||
+ | IF NEW.val:: | ||
+ | payload := board_label || ' is faulting: error ' || NEW.val; | ||
+ | ELSE | ||
+ | payload := board_label || ' no faulting anymore'; | ||
+ | END IF; | ||
+ | |||
+ | RAISE NOTICE ' | ||
+ | EXECUTE format(' | ||
+ | ) USING login_url; | ||
+ | |||
+ | |||
+ | EXECUTE format(' | ||
+ | ) USING notify_url, channel, token, payload; | ||
+ | RAISE NOTICE ' | ||
+ | |||
+ | END IF; | ||
+ | |||
+ | RETURN NEW; | ||
+ | END; | ||
+ | $notify_status_update$ LANGUAGE plpgsql; | ||
+ | |||
+ | </ | ||
+ | |||
+ | === Creazione del trigger (DROP preventivo se necessario) === | ||
+ | |||
+ | La CREATE è fatta su un AFTER INSERT perchè ad un update su una righa corrisponde, | ||
+ | |||
+ | < | ||
+ | |||
+ | drop trigger notify_status_update ON [RELATION]; | ||
+ | CREATE TRIGGER notify_status_update AFTER INSERT ON [RELATION] | ||
+ | FOR EACH ROW WHEN ( NEW.statuscode NOT LIKE ' | ||
+ | |||
+ | </ | ||
+ | |||
+ | Ad esempio: " | ||
+ | |||
+ | |||
+ | ==== GET LAST ROW BEFORE INSERT ==== | ||
+ | |||
+ | Funzione per prendere l' | ||
+ | |||
+ | Dato che siamo in una AFTER INSERT, l' | ||
+ | |||
+ | Per questo, dalla select viene esclusa l' | ||
+ | |||
+ | < | ||
+ | |||
+ | CREATE OR REPLACE FUNCTION get_last_reliable_var_row(tablename text, id integer) | ||
+ | RETURNS RECORD AS | ||
+ | $BODY$ | ||
+ | DECLARE | ||
+ | last_row RECORD; | ||
+ | BEGIN | ||
+ | EXECUTE ' | ||
+ | || quote_literal(' | ||
+ | INTO last_row; | ||
+ | RETURN last_row; | ||
+ | END; | ||
+ | $BODY$ | ||
+ | COST 100; | ||
+ | |||
+ | </ | ||
push_notifications/postgresql.1415295130.txt.gz · Last modified: 2014/11/06 17:32 by letti