Il nostro punto della strada

User Tools

Site Tools

Trace:
push_notifications:postgresql

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
push_notifications:postgresql [2015/02/11 09:24] – [3. creare trigger pl/pgsql] lettipush_notifications:postgresql [2015/03/03 17:33] (current) – letti
Line 70: Line 70:
 <code> <code>
  
-CREATE OR REPLACE FUNCTION honeywell.push_notifications_login(url text, login text, password text)+CREATE OR REPLACE FUNCTION push_notifications_login(url text, login text, password text)
   RETURNS text AS   RETURNS text AS
 $BODY$ $BODY$
Line 89: Line 89:
   LANGUAGE plpythonu VOLATILE   LANGUAGE plpythonu VOLATILE
   COST 100;    COST 100; 
-ALTER FUNCTION honeywell.push_notifications_login(text, text, text)         +ALTER FUNCTION push_notifications_login(text, text, text)         
-  OWNER TO matteo;+  OWNER TO postgres;
  
 </code> </code>
Line 98: Line 98:
 <code> <code>
  
-CREATE OR REPLACE FUNCTION honeywell.push_notifications_notify(channel text, token text, payload text)+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   RETURNS text AS
 $BODY$ $BODY$
Line 108: Line 108:
 opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj)) opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cj))
  
-notify_values = {{'channel' : channel, 'to_tokens':token,'payload':payload}} +login_values = {'login': login,'password' : password} 
-login_data = urllib.urlencode(notify_values)+login_data = urllib.urlencode(login_values)
  
-notify_resp = opener.open(url,login_data) +login_resp = opener.open(login_url,login_data) 
-return notify_resp.getcode()+ 
 +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$ $BODY$
   LANGUAGE plpythonu VOLATILE   LANGUAGE plpythonu VOLATILE
   COST 100;   COST 100;
-ALTER FUNCTION honeywell.push_notifications_notify(text, text, text) +ALTER FUNCTION push_notifications_notify(text, text, text, text, text, text, text) 
-  OWNER TO matteo;+  OWNER TO postgres;
  
 </code> </code>
Line 148: Line 159:
         EXECUTE format('SELECT channel FROM honeywell_channel WHERE id=$1;') USING OLD.id INTO channel;         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;         EXECUTE format('SELECT token FROM honeywell_heatpump_token WHERE heat_pump_id=$1;') USING OLD.id INTO token;
-        payload := 'status turned to' || cast(((OLD.status + 1 ) % 2) as text);+        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';         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';         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 := ''user'', password := ''user'')'+       -- EXECUTE format('SELECT push_notifications_login(url := $1, login := ''admin'', password := ''admin'')'
         ) USING login_url;         ) USING login_url;
          
          
-        EXECUTE format('SELECT push_notifications_notify(url := $1,channel := $2,token := $3,payload := $4);' +        EXECUTE format('SELECT push_notifications_notify(login_url := $1, login := $2, password := $3, push_url := $4, channel := $5, token := $6, payload := $7);' 
-        ) USING notify_url,channel,token,payload;+        ) USING login_url, login, password, notify_url, channel, token, payload;
          
     END;     END;
Line 167: Line 178:
 </code> </code>
 ==== 4. agganciare trigger al server ==== ==== 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>
  
push_notifications/postgresql.1423646695.txt.gz · Last modified: 2015/02/11 09:24 by letti