Il nostro punto della strada

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== GUIDA PER LE PUSH TRIGGHERATE DA postgreSQL DB =====

==== 0. push con Curl all'ACS app da server ====

=== Registration fatta da device android  ===

== Login ==

Req: POST /v1/users/login.json

Resp:

Request Time 	2014-11-06T14:28:40.514Z
Request Name 	POST /v1/users/login.json
Response Status 	200
Parameters 	{"version":"v1","action":"create","controller":"user_sessions","suppress_response_codes":"true","ti_analytics":"{\"mid\":\"68c2554c54f77013\",\"app_version\":\"1.0\",\"platform\":\"android\"}","key":"XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT","format":"json","password":"[FILTERED]","login":"APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ"}
User Agent 	Appcelerator Titanium/3.3.0 (XT1032; Android API Level: 19; it-IT;)
Source IP 	2.38.75.101
Response Size 	648 bytes

== Registration ==

Req: POST /v1/push_notification/subscribe.json 

Resp:

Request Time 	2014-11-06T14:28:42.063Z
Request Name 	POST /v1/push_notification/subscribe.json
Response Status 	200
Parameters 	{"version":"v1","channel":"honeywell.userapp","type":"android","device_token":"APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ","action":"subscribe","controller":"push_notification","suppress_response_codes":"true","ti_analytics":"{\"mid\":\"68c2554c54f77013\",\"app_version\":\"1.0\",\"platform\":\"android\"}","key":"XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT","format":"json","_session_id":"ABVNeeMJmfygK5oGDlvG1d171bI"}
User Agent 	Appcelerator Titanium/3.3.0 (XT1032; Android API Level: 19; it-IT;)
Source IP 	2.38.75.101
Response Size 	73 bytes

=== Push ===

Req: curl -b cookies.txt -c cookies.txt -X POST -F "channel=honeywell.userapp" -F "totokens=APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ" -F "payload=prova_acs" "https://api.cloud.appcelerator.com/v1/push_notificationnotify_tokens.json?key=XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT&pretty_json=true"

Resp:

Request Time 	2014-11-06T14:56:57.648Z
Request Name 	POST /v1/push_notification/notify_tokens.json
Response Status 	200
Parameters 	{"payload":"prova_acs","version":"v1","channel":"honeywell.userapp","action":"notify_tokens","controller":"push_notification","key":"XC6OWeoFQGEgP6Xtfb9V0mbF9oqgicjT","format":"json","to_tokens":"APA91bFrI1834PnWi2drJJbWNdwJqLKh9bf28ncmrhGQkPELFL7zNT6feYtMqLzRnD3pV7OykE3xuer4jBufSBJTPmM3JLhjqJdBg7wR3eRHkvN76-hp2D1JgWgQHgR5N1vJqEBVZYkAI4RYHPAV5NQz1APBSoGozJPsUXhifwdWxz0ftxco5KQ","pretty_json":"true"}
User Agent 	curl/7.26.0
Source IP 	2.38.75.101
Response Size 	64 bytes

Per una guida sulle push, vedere http://docs.befair.it/doku.php?id=push_notifications:appcelerator

=== PROVA CON urllib, urllib2, cookielib.CookieJar ===

**doc** http://stackoverflow.com/questions/3334809/python-urllib2-how-to-send-cookie-with-urlopen-request


==== 1. creare funzione per push con plpythonu (plpython2) ====

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 ====
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 ====

<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>