Created
January 28, 2017 07:23
-
-
Save eleloya23/049452ec5e52474f3c310e38bd8ad93f to your computer and use it in GitHub Desktop.
A postgres pure function that allow you to generate periscope embedded urls.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- We must have a function that given the json data and the api key, it must generate the correct periscope_url | |
-- ######################################## | |
-- select periscope_url('jsondata', 'api'); | |
-- ######################################### | |
-- JSON DATA USED by DOCS: '{"dashboard":7863,"embed":"v2","filters":[{"name":"Filter1","value":"value1"},{"name":"Filter2","value":"1234"}]}' | |
-- API KEY USED BY DOCS: 'e179017a-62b0-4996-8a38-e91aa9f1' | |
-- Proof Of Concept: | |
-- psql=# select periscope_url('{"dashboard":7863,"embed":"v2","filters":[{"name":"Filter1","value":"value1"},{"name":"Filter2","value":"1234"}]}', 'e179017a-62b0-4996-8a38-e91aa9f1'); | |
-- https://www.periscopedata.com/api/embedded_dashboard?data=%7B%22dashboard%22%3A7863%2C%22embed%22%3A%22v2%22%2C%22filters%22%3A%5B%7B%22name%22%3A%22Filter1%22%2C%22value%22%3A%22value1%22%7D%2C%7B%22name%22%3A%22Filter2%22%2C%22value%22%3A%221234%22%7D%5D%7D&signature=adcb671e8e24572464c31e8f9ffc5f638ab302a0b673f72554d3cff96a692740 | |
CREATE EXTENSION pgcrypto; | |
CREATE OR REPLACE FUNCTION periscope_url(jsonblob text, apikey text) RETURNS text AS $$ | |
DECLARE | |
path text; | |
result text; | |
BEGIN | |
path := '/api/embedded_dashboard?data=' || encode_uri(jsonblob); | |
result := hmac(path, apikey, 'sha256'); | |
result := 'https://www.periscopedata.com' || path || '&signature=' || substring(result from 3); | |
RETURN result; | |
END; | |
$$ LANGUAGE plpgsql; | |
CREATE OR REPLACE FUNCTION encode_uri(input text) | |
RETURNS text | |
LANGUAGE plpgsql | |
IMMUTABLE STRICT | |
AS $function$ | |
DECLARE | |
parsed text; | |
safePattern text; | |
BEGIN | |
safePattern = 'a-zA-Z0-9_~/\-\.'; | |
IF input ~ ('[^' || safePattern || ']') THEN | |
SELECT STRING_AGG(fragment, '') | |
INTO parsed | |
FROM ( | |
SELECT prefix || encoded AS fragment | |
FROM ( | |
SELECT COALESCE(match[1], '') AS prefix, | |
upper(COALESCE('%' || encode(match[2]::bytea, 'hex'), '')) AS encoded | |
FROM ( | |
SELECT regexp_matches( | |
input, | |
'([' || safePattern || ']*)([^' || safePattern || '])?', | |
'g') AS match | |
) matches | |
) parsed | |
) fragments; | |
RETURN parsed; | |
ELSE | |
RETURN input; | |
END IF; | |
END; | |
$function$ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment