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 [2014/11/06 17:32] – lettipush_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, use createlang plpythonu dbname+To install PL/Python in a particular database: 
 +1. apt-get install postgresql-plpython if necessary 
 +2. form shell, createlang -U postgres -d push plpythonu
  
 +Since plpythonu is untrusted, onlu superusers can use it. 
 +
 +Create file with CREATE FUNCTION
 +
 +Apply sql to db
 +
 +ACS Login: 
 +
 +<code>
 +
 +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 ==== ==== 2. push con plpythonu (plpython2) da shell postgresql ====
 SLELECT[function_name] SLELECT[function_name]
  
 +ESEMPI:
 +
 +SELECT push_notifications_login(url := 'https://api.cloud.appcelerator.com/v1/users/login.json?key=XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT&pretty_json=true' , login := 'user', password := 'user');
 +
 +SELECT push_notifications_notify(url := 'https://api.cloud.appcelerator.com/v1/push_notification/notify_tokens.json?key=XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT&pretty_json=true', channel := 'honeywell.userapp', token := 'APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ', payload := 'prova_acs_questions');
 ==== 3. creare trigger pl/pgsql ==== ==== 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 ==== ==== 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.1415295130.txt.gz · Last modified: 2014/11/06 17:32 by letti