Skip to content

golgauth/couchdb_fdw

 
 

Repository files navigation

CouchDB FDW (beta) for PostgreSQL 9.1+  (Compatible PostgreSQL 9.2+)
======================================

This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for
the CouchDB document-oriented database: http://couchdb.apache.org/

IMPORTANT: There're bugs in the existing version. I'm working on it and will
be releasing another version very soon.


Building
--------

To build the code, you need the yajl(Yet Another JSON Library) and libcurl installed 
on your system. 

You can checkout the yajl from GitHub:
https://github.com/lloyd/yajl
OR
download from:
http://lloyd.github.com/yajl/

libcurl C interface can be found here:
http://curl.haxx.se/libcurl/c/

Once that's done, the extension can be built with:

PATH=/usr/local/pgsql91/bin/:$PATH make USE_PGXS=1 make
sudo PATH=/usr/local/pgsql91/bin/:$PATH make USE_PGXS=1 install

(assuming you have PostgreSQL 9.1 in /usr/local/pgsql91).

I've tested on Mac OS X 10.6 only, but other *nix's should also work.
I haven't tested on Windows, but the code should be good on MinGW.

Limitations
-----------

- If there is no quals to pushdown, we get a list of '_id's to begin with,
  and then fetch whatever records still exist as we build the tuples.

- We can only pushdown '_id' (with or without '_rev') to CouchDB, which must use 
  the TEXTEQ operator.

- The top-level-attributes of a document can be mapped to columns in a
  foreign table.
  
- In case you want the map a column to the entire JOSN doc, use 
	columnname '_doc'
  in foreign table options (which means your remote couchdb shouldn't have
  a top-level-attribute called '_doc', otherwise there will be conficts).

Usage
-----

The following parameters can be set on a CouchDB foreign server:

address:	The address or hostname of the CouchDB server.
	 	Default: 127.0.0.1

port:		The port number on which the CouchDB server is listening.
     		Default: 5984

The following parameter can be set on a CouchDB foreign table:

database:	The name of the CouchDB database to query.
	  	Default: test

<column name>:	The column mapping to remote JSON attributes.
		If there is no column mapping specified, default is the origninal column name.
		Please note that '_doc' is reserved for mapping the entire JSON document.

The following parameter can be set on a user mapping for a CouchDB
foreign server:

username:	The username to authenticate to the CouchDB server with.
		Default: <none>
		
password:	The password to authenticate to the CouchDB server with.
		Default: <none>

Example
-------

* Initializing the CouchDB server using curl:

	- Create database:
		curl -X PUT http://localhost:5984/testdb
	- List databases:
		curl -X GET http://localhost:5984/_all_dbs
	- Create document:
		curl -X PUT http://localhost:5984/testdb/mydoc_1 -d "{\"title\": \"My first document\", \"content\": \"Hello World!\"}"
	- Create user:
		curl -HContent-Type:application/json -vXPUT http://127.0.0.1:5984/_users/org.couchdb.user:testuser --data-binary "{\"_id\": \"org.couchdb.user:testuser\",\"name\": \"testuser\",\"roles\": [],\"type\": \"user\",\"password\": \"secret\"}"

* Testing the extension:
			
	CREATE EXTENSION couchdb_fdw;
	
	--Then create a foreign server to connect to your CouchDB server:

	CREATE SERVER couchdb_server 
		FOREIGN DATA WRAPPER couchdb_fdw 
		OPTIONS (address '127.0.0.1', port '5984');

	-- Create a foreign table:

	CREATE FOREIGN TABLE couchdb_table (key text, value text) 
		SERVER couchdb_server
		OPTIONS (database 'testdb', key '_id', value '_doc');

	--Create user mapping:

	CREATE USER MAPPING FOR PUBLIC
		SERVER couchdb_server
		OPTIONS (username 'testuser', password 'secret');	
		
	--And list documents...
	
	SELECT * FROM couchdb_table;


* Ouput gives:

"mydoc_1";"{"_id":"mydoc_1","_rev":"1-2fe116a139a166a7f85b85387327d4bc","title":"My first document","content":"Hello World!"}"

-- 
Zheng Yang
zhengyang4k@gmail.com
--
Ported to PostgreSQL 9.2+ Foreign Data Wrapper API
Gauthier Boaglio
gauthier.boaglio _4t_ gmail _D0t_ com
Windows installer: https://sourceforge.net/p/postgresql-mingw-w64/discussion/general/thread/0d15fd1a/#149e

About

PostgreSQL SQL/MED FDW for CouchDB (PostgreSQL 9.2+)

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages

  • C 98.5%
  • Makefile 1.5%