Database.com-FDW-for-PostgreSQL
Database.com-FDW-for-PostgreSQL copied to clipboard
A Foreign Data Wrapper allowing you to see data in database.com/Force.com from PostgreSQL
Database.com FDW for PostgreSQL
This Python module implements the multicorn.ForeignDataWrapper
interface to allow you to create foreign tables in PostgreSQL 9.1+ that map to sobjects in database.com/Force.com. Column names and qualifiers (e.g. Name LIKE 'P%'
) are passed to database.com to minimize the amount of data on the wire.
- Version 0.0.7 fix a bug that prevented querying using null values
- Version 0.0.6 allow to set api_version in the options
- Version 0.0.5 fix utf8 encoding error, when querying with non ascii characters
- Version 0.0.4 updates yajl-py refs to prevent YajlContentHandler is not defined issue with latest yajl and yajl-py
- Version 0.0.3 removes the requirement for column names to be a case-sensitive match for the database.com field names.
- Version 0.0.2 switched to using the yajl-py streaming JSON parser to avoid reading the entire response from database.com into memory at once.
Pre-requisites
Installation
-
Create a Remote Access Application, since you will need a client ID and client secret so that that the FDW can login via OAuth and use the REST API.
-
Build the FDW module:
$ cd Database.com-FDW-for-PostgreSQL $ python setup.py sdist $ sudo python setup.py install
or, with easy_install:
$ cd Database.com-FDW-for-PostgreSQL $ sudo easy_install .
-
In the PostgreSQL client, create an extension and foreign server:
CREATE EXTENSION multicorn; CREATE SERVER multicorn_force FOREIGN DATA WRAPPER multicorn OPTIONS ( wrapper 'forcefdw.DatabaseDotComForeignDataWrapper', api_version 'v35.0', login_server 'https://login.salesforce.com' );
Default version is v23.0 and default login server is https://login.salesforce.com
-
Create a foreign table. You can use any subset of fields from the sobject, and column/field name matching is not case-sensitive:
CREATE FOREIGN TABLE contacts ( firstname character varying, lastname character varying, email character varying ) SERVER multicorn_force OPTIONS ( obj_type 'Contact', client_id 'CONSUMER_KEY_FROM_REMOTE_ACCESS_APP, client_secret 'CONSUMER_SECRET_FROM_REMOTE_ACCESS_APP', username '[email protected]', password '********' );
-
Query the foreign table as if it were any other table. You will see some diagnostics as the FDW interacts with database.com/Force.com. Here are some examples:
SELECT *
SELECT * FROM contacts; NOTICE: Logged in to https://login.salesforce.com as [email protected] NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact firstname | lastname | email -----------+-------------------------------------+--------------------------- Rose | Gonzalez | [email protected] Sean | Forbes | [email protected] Jack | Rogers | [email protected] Pat | Stumuller | [email protected] Andy | Young | [email protected] Tim | Barr | [email protected] ...etc...
SELECT
a column with a conditionpostgres=# SELECT email FROM contacts WHERE lastname LIKE 'G%'; NOTICE: SOQL query is SELECT lastname,email FROM Contact WHERE lastname LIKE 'G%' email ------------------- [email protected] [email protected] [email protected] (3 rows)
Aggregator
postgres=# SELECT COUNT(*) FROM contacts WHERE lastname LIKE 'G%'; NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact WHERE lastname LIKE 'G%' count ------- 3 (1 row)s
JOIN
postgres=# CREATE TABLE example ( postgres(# email varchar PRIMARY KEY, postgres(# favorite_color varchar NOT NULL postgres(# ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "example_pkey" for table "example" CREATE TABLE postgres=# INSERT INTO example VALUES('[email protected]', 'Red'); INSERT 0 1 postgres=# INSERT INTO example VALUES('[email protected]', 'Green'); INSERT 0 1 postgres=# INSERT INTO example VALUES('[email protected]', 'Blue'); INSERT 0 1 postgres=# SELECT favorite_color FROM example JOIN contacts ON example.email=contacts.email; NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact favorite_color ---------------- Red Green Blue (3 rows) postgres=# SELECT favorite_color FROM example JOIN contacts ON example.email=contacts.email WHERE contacts.firstname = 'Rose'; NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact WHERE firstname = 'Rose' favorite_color ---------------- Red (1 row)
Token refresh
postgres=# SELECT DISTINCT email FROM contacts LIMIT 1; NOTICE: SOQL query is SELECT email FROM Contact NOTICE: Invalid token 00D50000000IZ3Z!AQ0AQBwEiMxpN5VhLER2PKlifISWxln8ztl2V0cw3BPUAf3IxiD6ZG8Ei5PBcJoCKHDZRmp8lGnFDPQl7kaYgKL73vHHkqbG - trying refresh NOTICE: Logged in to https://login.salesforce.com as [email protected] NOTICE: SOQL query is SELECT email FROM Contact email ------------------------ [email protected] (1 row)
EXPLAIN
postgres=# EXPLAIN ANALYZE SELECT * FROM contacts ORDER BY lastname ASC LIMIT 3; NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Limit (cost=129263.11..129263.12 rows=3 width=96) (actual time=431.883..431.887 rows=3 loops=1) -> Sort (cost=129263.11..154263.11 rows=9999999 width=96) (actual time=431.880..431.880 rows=3 loops=1) Sort Key: lastname Sort Method: top-N heapsort Memory: 17kB -> Foreign Scan on contacts (cost=10.00..15.00 rows=9999999 width=96) (actual time=429.914..431.726 rows=69 loops=1) Foreign multicorn: multicorn Foreign multicorn cost: 10 Total runtime: 431.941 ms (8 rows)
License
3-clause BSD. See license file.