Twitter Example

The Twitter API, version 1.1, requires all requests to be authenticated. So we make two requests to Twitter, one to get a 'Bearer' key and the other to get the twitter-stream data.

To use the API, you must create a Twitter developer account, and register your app there. Twitter will then provide you a Consumer Key and Consumer Secret. You prepare these items and submit them to the /oauth2/token endpoint to get the Bearer key. This Bearer key is valid forever, or until explicitly invalidated. This first step, thus, might be a one-off operation.

var q = " \
  SELECT 'url'::TEXT as \"category\", Null::TEXT as \"idx\", 'POST'::TEXT AS \"name\", \
  	   'https://api.twitter.com/oauth2/token'::TEXT as \"value\" \n \
  -- RECORD: 'url', Null, 'POST', 'https://api.....' \n \
  \
  UNION SELECT * FROM (\n \
  SELECT 'header'::TEXT as \"category\", Null::TEXT as \"idx\", 'Authorization'::TEXT AS \"name\", \
  'Basic ' || replace(\
   encode(tk.client_key::BYTEA || ':'::BYTEA || tk.client_secret::BYTEA, 'base64'::TEXT), chr(10), '') AS \"value\" \n \
  FROM auth.fedoauth_providers tk WHERE provider = 'Twitter' LIMIT 1 ) AS _r \n \
  -- RECORD: 'header', Null, 'Authorization', 'Basic .....' \n \
  \
  UNION \n \
  SELECT 'header'::TEXT as \"category\", Null::TEXT as \"idx\", 'Content-Type'::TEXT AS \"name\", \
         'application/x-www-form-urlencoded;charset=UTF-8'::TEXT as \"value\" \n \
  -- RECORD: 'header', Null, 'Content-Type', 'application/x-www.......' \n \
  \
  UNION \n \
  SELECT 'body'::TEXT as \"category\", Null::TEXT as \"idx\", ''::TEXT AS \"name\", \
  	   'grant_type=client_credentials'::TEXT AS \"value\" \n \
  -- RECORD: 'body', Null, '', 'grant_type=client_credentials'";

Rdbhost.connect('www.rdbhost.com', 14);

var pr = Rdbhost.preauth()
		.query(q)
		.proxy('proxy')
		.get_data();

pr.then(function(d) {

	assert.ok(d, 'ok');
	var jsn = d.result_sets[0].records.rows[0].result,
	data = JSON.parse(jsn.result);
	assert.ok(data.token_type === 'bearer');
	done();
})
.catch(function(e) {
	assert.ok(! e, e.message);
	done();
})

			

This code assumes a table 'auth.fedoauth_providers', with fields 'client_key' and 'client_secret' containing the Consumer Key and Consumer Secret from Twitter's developer site.

The value jsn.result, in the example is a JSON string like:

{"token_type":"bearer",
 "access_token":"AAAAAAAAAAAAAAAAA~~~~~~~~ZYmcqnAkyhA13wvMa826OcFVScFCCjAbxCNT4ph"}
            

MailGun Example

MailGun.com is an emailing web-service. Submit your email content and envelope information via an http request, and they will send the email for you. You do need an account with them and the API key for the account. Low volume accounts are free. The example assumes the key is in table 'apikeys'.

var q = " \
  SELECT 'url', Null, 'POST', \n \
         'https://api.mailgun.net/v2/sandboxd0ba1f4751cf4b7894f3ff1204175f38.mailgun.org/messages'\n \
    UNION\n \
  SELECT 'auth', Null, 'api', apikey FROM auth.apikeys WHERE service = 'mailgun'\n \
    UNION \n \
  SELECT 'field', Null, 'from', 'rdbhost@sandboxd0ba1f4751cf4b7894f3ff1204175f38.mailgun.org' \n \
    UNION \n \
  SELECT 'field', Null, 'to', 'dvk@travelbyroad.net' \n \
    UNION \n \
  SELECT 'field', Null, 'subject', 'Demo Email' \n \
    UNION \n \
  SELECT 'field', Null, 'text', 'This demo email was sent via rdbhost proxy' \n \
    UNION \n \
  SELECT 'field', Null, 'html', 'This <b>demo email</b> was sent via rdbhost proxy' \n \
";

    Rdbhost.connect('dev.rdbhost.com', 14);

    var pr = Rdbhost.preauth()
        .query(q)
        .proxy('proxy')
        .get_data();

    pr.then(function(d) {

        assert.ok(d, 'ok');
        var jsn = d.result_sets[0].records.rows[0].result,
            data = JSON.parse(jsn.result);
        assert.ok(data.message.indexOf('Queued. Thank') >-1, 'bad response' );
        done();
    })
    .catch(function(e) {
        assert.ok(! e, e.message);
        done();
     })

});
			

The long API url and the noisy test email address are both copy-paste from the Mailgun configuration pages. The response from Mailgun, through the proxy service, will resemble the following.

{
    "times": [
        "0.026692",
        "0.002611"
    ],
    "request-id": "preauth1",
    "websocket-id": "x7f8be8751898",
    "status": [
        "complete",
        "OK"
    ],
    "noise": "qKOoYqu42mNz",
    "row_count": [
        1,
        "1 Rows Affected"
    ],
    "result_sets": [
        {
            "records": {
                "rows": [
                    {
                        "result": {
                            "result": "{\n  \"id\": \"<20170725031835.13905.591C85B5022A6C60@sandboxd0ba1f4751cf4b7894f3ff1204175f38.mailgun.org>\",\n  \"message\": \"Queued. Thank you.\"\n}",
                            "idx": "None"
                        },
                        "idx": "None"
                    }
                ],
                "header": {
                    "result": 705,
                    "idx": 705
                }
            },
            "row_count": [
                1,
                "1 Rows Affected"
            ],
            "status": [
                "complete",
                "OK"
            ]
        }
    ]
}
            

Your client code would need to extract the original JSON string and re-decode it.

  var d = json.loads(rawData);
  var origData = d.result_sets[0].records.rows[0][1].result;  // original json string from mailgun
  var mailgunResponse = json.loads(origData);
  var messageId = mailgunResponse.id;