Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 147 lines (120 sloc) 6.603 kb
03ba206 @metadaddy-sfdc Added README
authored
1 Database.com FDW for PostgreSQL
2 ===============================
3
7c0469c @metadaddy-sfdc Tidy up README
authored
4 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.
9699f46 @metadaddy-sfdc Added more preamble to README
authored
5
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
6 Version 0.0.3 removes the requirement for column names to be a case-sensitive match for the database.com field names.
03ba206 @metadaddy-sfdc Added README
authored
7
8 Pre-requisites
9 --------------
10
c45b426 @metadaddy-sfdc Trying to get formatting correct
authored
11 * [PostgreSQL 9.1+](http://www.postgresql.org/)
12 * [Python](http://python.org/)
13 * [Multicorn](http://multicorn.org)
2deea82 @metadaddy-sfdc Version 0.0.2 - use yajl-py streaming JSON parser
authored
14 * [yajl-py](http://pykler.github.com/yajl-py/)
15 * [YAJL](http://lloyd.github.com/yajl/)
03ba206 @metadaddy-sfdc Added README
authored
16
17 Installation
18 ------------
19
20 1. [Create a Remote Access Application](http://wiki.developerforce.com/page/Getting_Started_with_the_Force.com_REST_API#Setup), since you will need a client ID and client secret so that that the FDW can login via OAuth and use the REST API.
21 2. [Install Multicorn](http://multicorn.org/#installation)
2deea82 @metadaddy-sfdc Version 0.0.2 - use yajl-py streaming JSON parser
authored
22 3. [Build and install YAJL](http://lloyd.github.com/yajl/)
23 4. [Build and install yajl-py](http://pykler.github.com/yajl-py/)
24 5. Build the FDW module:
205aa76 @metadaddy-sfdc Trying to get formatting correct
authored
25
874b5b6 @metadaddy-sfdc Trying to get formatting correct
authored
26 $ cd Database.com-FDW-for-PostgreSQL
27 $ python setup.py sdist
28 $ sudo python setup.py install
205aa76 @metadaddy-sfdc Trying to get formatting correct
authored
29
2deea82 @metadaddy-sfdc Version 0.0.2 - use yajl-py streaming JSON parser
authored
30 6. In the PostgreSQL client, create an extension and foreign server:
11fafc8 @metadaddy-sfdc Trying to get formatting correct
authored
31
32
33 CREATE EXTENSION multicorn;
34 CREATE SERVER multicorn_force FOREIGN DATA WRAPPER multicorn
35 OPTIONS (
36 wrapper 'forcefdw.DatabaseDotComForeignDataWrapper'
37 );
38
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
39 7. Create a foreign table. You can use any subset of fields from the sobject, and column/field name matching is not case-sensitive:
11fafc8 @metadaddy-sfdc Trying to get formatting correct
authored
40
41 CREATE FOREIGN TABLE contacts (
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
42 firstname character varying,
43 lastname character varying,
44 email character varying
11fafc8 @metadaddy-sfdc Trying to get formatting correct
authored
45 ) SERVER multicorn_force OPTIONS (
46 obj_type 'Contact',
47 client_id 'CONSUMER_KEY_FROM_REMOTE_ACCESS_APP,
48 client_secret 'CONSUMER_SECRET_FROM_REMOTE_ACCESS_APP',
49 username 'user@domain.com',
50 password '********'
51 );
52
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
53 8. 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:
03ba206 @metadaddy-sfdc Added README
authored
54
1b17a60 @metadaddy-sfdc Added a look under the covers
authored
55 `SELECT *`
56
11fafc8 @metadaddy-sfdc Trying to get formatting correct
authored
57 SELECT * FROM contacts;
58 NOTICE: Logged in to https://login.salesforce.com as pat@superpat.com
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
59 NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact
60 firstname | lastname | email
11fafc8 @metadaddy-sfdc Trying to get formatting correct
authored
61 -----------+-------------------------------------+---------------------------
62 Rose | Gonzalez | rose@edge.com
63 Sean | Forbes | sean@edge.com
64 Jack | Rogers | jrogers@burlington.com
65 Pat | Stumuller | pat@pyramid.net
66 Andy | Young | a_young@dickenson.com
67 Tim | Barr | barr_tim@grandhotels.com
68 ...etc...
1b17a60 @metadaddy-sfdc Added a look under the covers
authored
69
70 `SELECT` a column with a condition
71
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
72 postgres=# SELECT email FROM contacts WHERE lastname LIKE 'G%';
73 NOTICE: SOQL query is SELECT lastname,email FROM Contact WHERE lastname LIKE 'G%'
74 email
11fafc8 @metadaddy-sfdc Trying to get formatting correct
authored
75 -------------------
76 rose@edge.com
77 jane_gray@uoa.edu
78 agreen@uog.com
79 (3 rows)
1b17a60 @metadaddy-sfdc Added a look under the covers
authored
80
81 Aggregator
82
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
83 postgres=# SELECT COUNT(*) FROM contacts WHERE lastname LIKE 'G%';
84 NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact WHERE lastname LIKE 'G%'
11fafc8 @metadaddy-sfdc Trying to get formatting correct
authored
85 count
86 -------
87 3
88 (1 row)s
1b17a60 @metadaddy-sfdc Added a look under the covers
authored
89
90 `JOIN`
91
2e87d23 @metadaddy-sfdc Added join example to README
authored
92 postgres=# CREATE TABLE example (
93 postgres(# email varchar PRIMARY KEY,
94 postgres(# favorite_color varchar NOT NULL
95 postgres(# );
96 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "example_pkey" for table "example"
97 CREATE TABLE
98 postgres=# INSERT INTO example VALUES('rose@edge.com', 'Red');
99 INSERT 0 1
100 postgres=# INSERT INTO example VALUES('jane_gray@uoa.edu', 'Green');
101 INSERT 0 1
102 postgres=# INSERT INTO example VALUES('agreen@uog.com', 'Blue');
103 INSERT 0 1
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
104 postgres=# SELECT favorite_color FROM example JOIN contacts ON example.email=contacts.email;
105 NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact
2e87d23 @metadaddy-sfdc Added join example to README
authored
106 favorite_color
107 ----------------
108 Red
109 Green
110 Blue
111 (3 rows)
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
112 postgres=# SELECT favorite_color FROM example JOIN contacts ON example.email=contacts.email WHERE contacts.firstname = 'Rose';
113 NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact WHERE firstname = 'Rose'
2e87d23 @metadaddy-sfdc Added join example to README
authored
114 favorite_color
115 ----------------
116 Red
117 (1 row)
118
11b24e0 @metadaddy-sfdc Added token refresh to README
authored
119 Token refresh
120
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
121 postgres=# SELECT DISTINCT email FROM contacts LIMIT 1;
122 NOTICE: SOQL query is SELECT email FROM Contact
11b24e0 @metadaddy-sfdc Added token refresh to README
authored
123 NOTICE: Invalid token 00D50000000IZ3Z!AQ0AQBwEiMxpN5VhLER2PKlifISWxln8ztl2V0cw3BPUAf3IxiD6ZG8Ei5PBcJoCKHDZRmp8lGnFDPQl7kaYgKL73vHHkqbG - trying refresh
124 NOTICE: Logged in to https://login.salesforce.com as pat@superpat.com
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
125 NOTICE: SOQL query is SELECT email FROM Contact
126 email
11b24e0 @metadaddy-sfdc Added token refresh to README
authored
127 ------------------------
128 jrogers@burlington.com
129 (1 row)
130
1b17a60 @metadaddy-sfdc Added a look under the covers
authored
131 `EXPLAIN`
132
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
133 postgres=# EXPLAIN ANALYZE SELECT * FROM contacts ORDER BY lastname ASC LIMIT 3;
134 NOTICE: SOQL query is SELECT lastname,email,firstname FROM Contact
1b17a60 @metadaddy-sfdc Added a look under the covers
authored
135 QUERY PLAN
136 --------------------------------------------------------------------------------------------------------------------------------
137 Limit (cost=129263.11..129263.12 rows=3 width=96) (actual time=431.883..431.887 rows=3 loops=1)
138 -> Sort (cost=129263.11..154263.11 rows=9999999 width=96) (actual time=431.880..431.880 rows=3 loops=1)
ae64ffa @metadaddy-sfdc Version 0.0.3 - remove requirement for case-sensitive match and quoting ...
authored
139 Sort Key: lastname
1b17a60 @metadaddy-sfdc Added a look under the covers
authored
140 Sort Method: top-N heapsort Memory: 17kB
141 -> Foreign Scan on contacts (cost=10.00..15.00 rows=9999999 width=96) (actual time=429.914..431.726 rows=69 loops=1)
142 Foreign multicorn: multicorn
143 Foreign multicorn cost: 10
144 Total runtime: 431.941 ms
145 (8 rows)
146
Something went wrong with that request. Please try again.