Differences

This shows you the differences between two versions of the page.

--- push_notifications:postgresql [2015/02/11 08:56] – [1. creare funzione per push con plpythonu (plpython2)] letti
+++ push_notifications:postgresql [2015/03/03 17:33] (current) – letti
@@ Line 66: / Line 66: @@
 Apply sql to db
-Example:
+ACS Login:
-> CREATE OR REPLACE FUNCTION honeywell.push_notifications_login(url text, login text, password text)
+<code>
->   RETURNS text AS
-> $BODY$
->
-> import urllib, urllib2
-> from cookielib import CookieJar
->
-> cj = CookieJar()
-> opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
->
-> login_values = {'login': login,'password' : password}
-> login_data = urllib.urlencode(login_values)
->
-> login_resp = opener.open(url,login_data)
-> return login_resp.getcode()
->
-> $BODY$
->   LANGUAGE plpythonu VOLATILE
->   COST 100;
-> ALTER FUNCTION honeywell.push_notifications_login(text, text, text)
->   OWNER TO matteo;
+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': login,'password' : password}
+login_data = urllib.urlencode(login_values)
+login_resp = opener.open(url,login_data)
+return login_resp.getcode()
+$BODY$
+  LANGUAGE plpythonu VOLATILE
+  COST 100;
+ALTER FUNCTION push_notifications_login(text, text, text)
+  OWNER TO postgres;
+</code>
+ACS push:
+<code>
+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': login,'password' : password}
+login_data = urllib.urlencode(login_values)
+login_resp = opener.open(login_url,login_data)
+if login_resp.getcode() == '200':
+    notify_values = {'channel' : channel, 'to_tokens':token,'payload':payload}
+    notify_data = urllib.urlencode(notify_values)
+    notify_resp = opener.open(push_url,notify_data)
+    if notify_restp.getcode() == '200':
+        return True
+else:
+    return False
+$BODY$
+  LANGUAGE plpythonu VOLATILE
+  COST 100;
+ALTER FUNCTION push_notifications_notify(text, text, text, text, text, text, text)
+  OWNER TO postgres;
+</code>
 ==== 2. push con plpythonu (plpython2) da shell postgresql ====
 SLELECT[function_name]
@@ Line 100: / Line 142: @@
 ==== 3. creare trigger pl/pgsql ====
+<code>
+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('SELECT app_key FROM honeywell_heatpump_token WHERE heat_pump_id=$1;') USING OLD.id INTO app_key;
+        EXECUTE format('SELECT channel FROM honeywell_channel WHERE id=$1;') USING OLD.id INTO channel;
+        EXECUTE format('SELECT token FROM honeywell_heatpump_token WHERE heat_pump_id=$1;') USING OLD.id INTO token;
+        payload := 'status turned to' || cast(NEW.status as text);
+        login_url := 'https://api.cloud.appcelerator.com/v1/users/login.json?key=' || app_key || '&pretty_json=true';
+        notify_url := 'https://api.cloud.appcelerator.com/v1/push_notification/notify_tokens.json?key=' || app_key  || '&pretty_json=true';
+       -- EXECUTE format('SELECT push_notifications_login(url := $1, login := ''admin'', password := ''admin'')'
+        ) USING login_url;
+        EXECUTE format('SELECT push_notifications_notify(login_url := $1, login := $2, password := $3, push_url := $4, channel := $5, token := $6, payload := $7);'
+        ) 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()
+</code>
 ==== 4. agganciare trigger al server ====
+==== 5. ESEMPIO ====
+Target database:
+<code>
+                       Lista delle relazioni
+ Schema |              Nome               |   Tipo   | Proprietario
+--------+---------------------------------+----------+--------------
+ public | honeywell_channel               | tabella  | matteo
+ public | honeywell_channel_id_seq        | sequenza | matteo
+ public | honeywell_heatpump              | tabella  | matteo
+ public | honeywell_heatpump_id_seq       | sequenza | matteo
+ public | honeywell_heatpump_token        | tabella  | matteo
+ public | honeywell_heatpump_token_id_seq | sequenza | matteo
+</code>
+<code>
+CREATE TABLE "honeywell_heatpump" ("id" SERIAL NOT NULL PRIMARY KEY ,"heat" varchar(32), "status" integer NOT NULL);
+CREATE TABLE "honeywell_channel" ("id" SERIAL NOT NULL PRIMARY KEY, "channel" varchar(64));
+CREATE TABLE "honeywell_heatpump_token" ("id" SERIAL NOT NULL PRIMARY KEY ,"heat_pump_id" integer NOT NULL REFERENCES "honeywell_heatpump" ("id"), "token" varchar(256),"app_key" varchar(64),"channel" integer NOT NULL REFERENCES "honeywell_channel" ("id"));
+</code>
+Define functions as in section 2 and 3.
+<code>
+INSERT INTO honeywell_heatpump (heat,status) VALUES ('heat1',0);
+INSERT INTO honeywell_channel (channel) VALUES ('honeywell.userapp');
+INSERT INTO honeywell_heatpump_token (heat_pump_id,token,app_key,channel) VALUES ((select id from honeywell_heatpump where id = 1),'APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ','XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT',(select id from honeywell_channel where id = 1));
+</code>
+<code>
+UPDATE honeywell_heatpump SET status = 1;
+</code>
+==== 5.1 ESEMPIO 2 ====
+Esempio di trigger su una tabella di uno schema.
+=== Creazione funzione da triggherare ===
+<code>
+CREATE OR REPLACE FUNCTION notify_status_update() RETURNS trigger AS $notify_status_update$
+    DECLARE
+        app_key text := 'lEJ3oT6DG92XYbx0pH3wzL2kOdphFr7y';
+        channel text := TG_TABLE_SCHEMA;
+        token text := 'everyone';
+        payload text;
+        login_url text := 'https://api.cloud.appcelerator.com/v1/users/login.json?key=' || app_key ;
+        notify_url text := 'https://api.cloud.appcelerator.com/v1/push_notification/notify_tokens.json?key=' || app_key ;
+        OLD RECORD;
+        TABLE_FULLNAME text := '"' || TG_TABLE_SCHEMA || '"."' || TG_TABLE_NAME || '"';
+        board_label text;
+    BEGIN
+        RAISE NOTICE 'channel: %', channel;
+        SELECT label FROM acs_service.chamber WHERE dbname = TG_TABLE_SCHEMA INTO board_label;
+        SELECT * FROM get_last_reliable_var_row(TABLE_FULLNAME,NEW.id) AS row_out (
+            id integer,
+            variablename character varying,
+            val character varying,
+            statuscode character varying,
+            "timestamp" integer,
+            timestampe integer,
+            started integer,
+            sampletime integer,
+            idactor integer
+        ) INTO OLD;
+	RAISE NOTICE 'OLD.val: % NEW.val: %', OLD.val, NEW.val;
+        -- Do nothing if only statuscode is changed...
+        IF OLD.val != NEW.val THEN
+            IF NEW.val::float <> 0 THEN
+                payload := board_label || ' is faulting: error ' || NEW.val;
+            ELSE
+                payload := board_label || ' no faulting anymore';
+            END IF;
+	    RAISE NOTICE 'login_url: %', login_url;
+            EXECUTE format('SELECT push_notifications_login(url := $1, login := ''admin'', password := ''admin'')'
+            ) USING login_url;
+            EXECUTE format('SELECT push_notifications_notify(url := $1,channel := $2,token := $3,payload := $4);'
+            ) USING notify_url, channel, token, payload;
+	    RAISE NOTICE 'notify_url: % channel: % token: % payload: %', notify_url, channel, token, payload;
+        END IF;
+        RETURN NEW;
+    END;
+$notify_status_update$ LANGUAGE plpgsql;
+</code>
+=== Creazione del trigger (DROP preventivo se necessario) ===
+La CREATE è fatta su un AFTER INSERT perchè ad un update su una righa corrisponde, in realtà, una CREATE nella tabella.
+<code>
+drop trigger notify_status_update ON [RELATION];
+CREATE TRIGGER notify_status_update AFTER INSERT ON  [RELATION]
+FOR EACH ROW WHEN ( NEW.statuscode NOT LIKE '%FF__') EXECUTE PROCEDURE notify_status_update();
+</code>
+Ad esempio: "KIT-ff-1e-c0-13-d4-0c"."v1"
+==== GET LAST ROW BEFORE INSERT ====
+Funzione per prendere l'ultima row valida nella tabella.
+Dato che siamo in una AFTER INSERT, l'ultima row valida è in realtà quella corrente.
+Per questo, dalla select viene esclusa l'ultima riga appena inserita (quella con id = NEW.id)
+<code>
+CREATE OR REPLACE FUNCTION get_last_reliable_var_row(tablename text, id integer)
+  RETURNS RECORD AS
+$BODY$
+DECLARE
+    last_row RECORD;
+BEGIN
+    EXECUTE 'SELECT * FROM ' || tablename || ' WHERE statuscode NOT LIKE '
+    || quote_literal('%%%%FF__') || ' AND id NOT IN (' || id || ') ORDER BY timestampe DESC LIMIT 1'
+        INTO last_row;
+    RETURN last_row;
+END;
+$BODY$                                                                                                                                         LANGUAGE plpgsql VOLATILE
+  COST 100;
+</code>