Preliminary Note: Here is explained how to compile and use two additional Foreign Data Wrappers: couchdb_fdw, oracle_fdw.
The resulting compiled files can be found in the file repository of this project, and more specifically this archive.
Note: For more detailed information/explanations about preliminary settings, please refer to this page.
WARNING: Regarding oracle_fdw, this wrapper is ONLY compatible with 64-bit version of Oracle Database if compiled in 64-bit, and with 32-bit version of Oracle Database otherwise.
Not paying attention to this would result in an error message of that kind (even if the environment - ORACLE_HOME, etc - was set correctly) :
ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle DETAIL: ********** Error ********** ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment handle SQL state :HV00N
(If you are experiencing problems during the process described in this document, feel free to send a ticket, and I'll try to help...)
export ARCH="x86_64-w64-mingw32"; export SRCDIR="/d/64/"; export MGWDIR="/c/mingw64/" export C_INCLUDE_PATH=$C_INCLUDE_PATH:/usr/local/include
export PATH=$MGWDIR"bin":$MGWDIR"lib":$MGWDIR$ARCH"/bin":$MGWDIR$ARCH"/lib":/usr/local/pgsql/bin:/usr/msys/local/pgsql/lib:/usr/local/bin:/usr/bin:/usr/local/ssl/bin:$MGWDIR$ARCH"/lib":$MGWDIR"opt/bin"
Get couchdb_fdw from sources from here.
Compiling yajl library (couchdb_fdw depends on it):
{ Get it from here }
install cmake:
http://www.cmake.org/cmake/resources/software.html,
http://www.cmake.org/files/v2.8/cmake-2.8.12.1-win32-x86.zip
export PATH=$PATH:/c/cmake-2.8.12.1-win32-x86/bin
unpack "lloyd-yajl-2.0.4-10-g4c539cb.tar.gz"
cd lloyd-yajl-4c539cb/
Patch file "CMakeLists.txt" (TODO: ".patch" !!!)
# IF (WIN32) # SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} /W4") # ADD_DEFINITIONS(-DWIN32) # SET(linkFlags "/PDB:NONE /INCREMENTAL:NO /OPT:NOREF /OPT:NOICF") # SET(CMAKE_EXE_LINKER_FLAGS "${linkFlags}" # CACHE STRING "YAJL linker flags" FORCE) # SET(CMAKE_EXE_LINKER_FLAGS_DEBUG "" # CACHE STRING "YAJL debug linker flags" FORCE) # SET(CMAKE_EXE_LINKER_FLAGS_RELEASE # CACHE STRING "YAJL release linker flags" FORCE) # SET(CMAKE_SHARED_LINKER_FLAGS "${linkFlags}" # CACHE STRING "YAJL shared linker flags" FORCE) # SET(CMAKE_MODULE_LINKER_FLAGS "${linkFlags}" # CACHE STRING "YAJL module linker flags" FORCE) # SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} /wd4996 /wd4255 /wd4130 /wd4100 /wd4711") # SET(CMAKE_C_FLAGS_DEBUG "/D DEBUG /Od /Z7") # SET(CMAKE_C_FLAGS_RELEASE "/D NDEBUG /O2") # ELSE (WIN32) SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -Wall") IF(CMAKE_COMPILER_IS_GNUCC) INCLUDE(CheckCCompilerFlag) CHECK_C_COMPILER_FLAG(-fvisibility=hidden HAVE_GCC_VISIBILITY) IF(HAVE_GCC_VISIBILITY) SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -fvisibility=hidden") ENDIF(HAVE_GCC_VISIBILITY) ENDIF(CMAKE_COMPILER_IS_GNUCC) SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -std=c99 -pedantic -Wpointer-arith -Wno-format-y2k -Wstrict-prototypes -Wmissing-declarations -Wnested-externs -Wextra -Wundef -Wwrite-strings -Wold-style-definition -Wredundant-decls -Wno-unused-parameter -Wno-sign-compare -Wmissing-prototypes") SET(CMAKE_C_FLAGS_DEBUG "-DDEBUG -g") SET(CMAKE_C_FLAGS_RELEASE "-DNDEBUG -O2 -Wuninitialized") # ENDIF (WIN32)
Tell cmake to generate makefiles for our compiler
CC=$ARCH"-gcc" cmake -G "MSYS Makefiles" ./
Build
make
Install all (by hand)
cp yajl-2.0.5/bin/* /usr/local/bin cp -R yajl-2.0.5/include/* /usr/local/include cp yajl-2.0.5/lib/*.dll /usr/local/bin cp yajl-2.0.5/lib/*.a /usr/local/lib cp yajl-2.0.5/share/pkgconfig/* /usr/local/lib/pkgconfig
Compile couchdb_fdw
cd $SRCDIR"/couchdb_fdw" export USE_PGXS=1 make CC=$ARCH"-gcc" LDFLAGS="-L/usr/local/bin/ -lcurl -lyajl" make install
If not done already, get Apache CouchDB Windows binary - Download.
Start your CouchDB server, and we're good to go:
Prepare CouchDB
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\"}"
Wrap & Test
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;
"mydoc_1";"{"_id":"mydoc_1","_rev":"1-2fe116a139a166a7f85b85387327d4bc","title":"My first document","content":"Hello World!"}"
IMPORTANT NOTE: The following requires a 64-bit version of the Oracle Database.
export ARCH="x86_64-w64-mingw32"; export SRCDIR="/d/64/"; export MGWDIR="/c/mingw64/" export C_INCLUDE_PATH=$C_INCLUDE_PATH:/usr/local/include
export PATH=$MGWDIR"bin":$MGWDIR"lib":$MGWDIR$ARCH"/bin":$MGWDIR$ARCH"/lib":/usr/local/pgsql/bin:/usr/msys/local/pgsql/lib:/usr/local/bin:/usr/bin:/usr/local/ssl/bin:$MGWDIR$ARCH"/lib":$MGWDIR"opt/bin"
cd $SRCDIR
Get oracle_fdw from sources from here.
If not done already, install Oracle Database (12.1.0.1.0) for "Microsoft Windows x64 (64-bit)" from here.
=> ports are: - TNS: 1521 - MTS: 2030 - HTTP: 8081
Set the ORACLE_HOME environment variable (used by "oracle_fdw" Makefile)
export ORACLE_HOME=/d/app2/golgauth2/product/12.1.0/dbhome_1
Add required oracle includes
export C_INCLUDE_PATH=$C_INCLUDE_PATH:$ORACLE_HOME"/../OCI/include"
Build
cd oracle_fdw/ make CC=$ARCH"-gcc" make install
=> Results in:
/bin/install -c -m 755 oracle_fdw.dll 'C:/mingw64/msys/local/pgsql/lib/oracle_fdw.dll' /bin/install -c -m 644 ./oracle_fdw.control 'C:/mingw64/msys/local/pgsql/share/extension/' /bin/install -c -m 644 ./oracle_fdw--1.0.sql 'C:/mingw64/msys/local/pgsql/share/extension/' /bin/install -c -m 644 ./README.oracle_fdw 'C:/mingw64/msys/local/pgsql/share/doc/extension/'
Install the "oci" dependency
cp $ORACLE_HOME"oci.dll" /usr/local/bin/
Set PATH correctly on your Oracle Database server side
SET ORACLE_HOME=D:\app\golgauth2\product\12.1.0\dbhome_1; SET TNS_ADMIN=D:\app\golgauth2\product\12.1.0\dbhome_1\NETWORK\ADMIN; SET PATH=%PATH%;D:\app\golgauth2\product\12.1.0\dbhome_1\BIN;
Setting up the Oracle Database
sqlplus SYSTEM/orapwd@//localhost:1521/ORADB
SQL> CREATE USER C##ORAUSER IDENTIFIED BY orapwd; GRANT connect, resource TO C##ORAUSER; GRANT UNLIMITED TABLESPACE TO C##ORAUSER; CONNECT C##ORAUSER/orapwd@//localhost:1521/ORADB; SHOW USER;
SQL> CREATE SCHEMA AUTHORIZATION C##ORAUSER CREATE table ORATAB ( ID NUMBER(5,0) NOT NULL, TEXT VARCHAR2(30), FLOATING NUMBER(7,2) NOT NULL );
SQL> DESCRIBE ORATAB Name Null? Type ------------------------------- -------- ------------ ID NOT NULL NUMBER(5) TEXT VARCHAR2(30) FLOATING NOT NULL NUMBER(7,2) SQL> INSERT INTO ORATAB (ID,TEXT,FLOATING) values (1, 'a', 1.1); INSERT INTO ORATAB (ID,TEXT,FLOATING) values (2, 'b', 2.2); INSERT INTO ORATAB (ID,TEXT,FLOATING) values (3, 'c', 3.3); SQL> SELECT * FROM ORATAB; SQL> QUIT;
Connecting to Oracle Database (PostgreSQL side)
--DROP EXTENSION oracle_fdw CASCADE; CREATE EXTENSION oracle_fdw; -- Then create a foreign server to connect to your OracleDB server: CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//localhost:1521/ORADB'); -- Get privilege & Create user mapping: CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'C##ORAUSER', password 'orapwd'); GRANT USAGE ON FOREIGN SERVER oradb TO postgres; -- Create a foreign table: CREATE FOREIGN TABLE oratab ( id integer NOT NULL, text character varying(30), floating double precision NOT NULL ) SERVER oradb OPTIONS (schema 'C##ORAUSER', table 'ORATAB'); -- Show table contents: SELECT * FROM oratab;
1;"a";1.1 2;"b";2.2 3;"c";3.3
{ READ this SO thread }
The six-step process to remove all things Oracle from a Windows machine:
Delete the Oracle services: In the registry, go to \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and delete everything that starts with "Oracle" (+ do the same for "ControlSet001", "ControlSet002", ...)
Remove other Oracle stuff from the registry: Go to \HKEY_LOCAL_MACHINE\SOFTWARE\ and delete the key ORACLE
Reboot
Delete all the Oracle software from the directories where you installed it
Delete the Oracle software inventory: Delete the directory C:\Program Files\Oracle or C:\Program Files (x86)\Oracle. You must do this no matter where you installed your Oracle software - the Oracle installer automatically writes information here.
Delete all shortcuts from your Start menu.
export ARCH="i686-w64-mingw32"; export SRCDIR="/d/32/"; export MGWDIR="/c/mingw32/" export C_INCLUDE_PATH=$C_INCLUDE_PATH:/usr/local/include
Get couchdb_fdw from sources from here.
Compiling yajl library (couchdb_fdw depends on it):
{ Get it from here }
install cmake:
http://www.cmake.org/cmake/resources/software.html,
http://www.cmake.org/files/v2.8/cmake-2.8.12.1-win32-x86.zip
export PATH=$PATH:/c/cmake-2.8.12.1-win32-x86/bin
unpack "lloyd-yajl-2.0.4-10-g4c539cb.tar.gz"
cd lloyd-yajl-4c539cb/
Patch file "CMakeLists.txt" (TODO: ".patch" !!!)
# IF (WIN32) # SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} /W4") # ADD_DEFINITIONS(-DWIN32) # SET(linkFlags "/PDB:NONE /INCREMENTAL:NO /OPT:NOREF /OPT:NOICF") # SET(CMAKE_EXE_LINKER_FLAGS "${linkFlags}" # CACHE STRING "YAJL linker flags" FORCE) # SET(CMAKE_EXE_LINKER_FLAGS_DEBUG "" # CACHE STRING "YAJL debug linker flags" FORCE) # SET(CMAKE_EXE_LINKER_FLAGS_RELEASE # CACHE STRING "YAJL release linker flags" FORCE) # SET(CMAKE_SHARED_LINKER_FLAGS "${linkFlags}" # CACHE STRING "YAJL shared linker flags" FORCE) # SET(CMAKE_MODULE_LINKER_FLAGS "${linkFlags}" # CACHE STRING "YAJL module linker flags" FORCE) # SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} /wd4996 /wd4255 /wd4130 /wd4100 /wd4711") # SET(CMAKE_C_FLAGS_DEBUG "/D DEBUG /Od /Z7") # SET(CMAKE_C_FLAGS_RELEASE "/D NDEBUG /O2") # ELSE (WIN32) SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -Wall") IF(CMAKE_COMPILER_IS_GNUCC) INCLUDE(CheckCCompilerFlag) CHECK_C_COMPILER_FLAG(-fvisibility=hidden HAVE_GCC_VISIBILITY) IF(HAVE_GCC_VISIBILITY) SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -fvisibility=hidden") ENDIF(HAVE_GCC_VISIBILITY) ENDIF(CMAKE_COMPILER_IS_GNUCC) SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} -std=c99 -pedantic -Wpointer-arith -Wno-format-y2k -Wstrict-prototypes -Wmissing-declarations -Wnested-externs -Wextra -Wundef -Wwrite-strings -Wold-style-definition -Wredundant-decls -Wno-unused-parameter -Wno-sign-compare -Wmissing-prototypes") SET(CMAKE_C_FLAGS_DEBUG "-DDEBUG -g") SET(CMAKE_C_FLAGS_RELEASE "-DNDEBUG -O2 -Wuninitialized") # ENDIF (WIN32)
Tell cmake to generate makefiles for our compiler
CC=$ARCH"-gcc" cmake -G "MSYS Makefiles" ./
Build
make
Install all (by hand)
cp yajl-2.0.5/bin/* /usr/local/bin cp -R yajl-2.0.5/include/* /usr/local/include cp yajl-2.0.5/lib/*.dll /usr/local/bin cp yajl-2.0.5/lib/*.a /usr/local/lib cp yajl-2.0.5/share/pkgconfig/* /usr/local/lib/pkgconfig
Compile couchdb_fdw
cd $SRCDIR"/couchdb_fdw" export USE_PGXS=1 make CC=$ARCH"-gcc" LDFLAGS="-L/usr/local/bin/ -lcurl -lyajl" make install
If not done already, get Apache CouchDB Windows binary - Download.
Start your CouchDB server, and we're good to go:
Prepare CouchDB
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\"}"
Wrap & Test
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;
"mydoc_1";"{"_id":"mydoc_1","_rev":"1-2fe116a139a166a7f85b85387327d4bc","title":"My first document","content":"Hello World!"}"
IMPORTANT NOTE: The following requires a 32-bit version of the Oracle Database.
export ARCH="i686-w64-mingw32"; export SRCDIR="/d/32/"; export MGWDIR="/c/mingw32/" export C_INCLUDE_PATH=$C_INCLUDE_PATH:/usr/local/include
export PATH=$MGWDIR"bin":$MGWDIR"lib":$MGWDIR$ARCH"/bin":$MGWDIR$ARCH"/lib":/usr/local/pgsql/bin:/usr/msys/local/pgsql/lib:/usr/local/bin:/usr/bin:/usr/local/ssl/bin:$MGWDIR$ARCH"/lib":$MGWDIR"opt/bin"
cd $SRCDIR
Get oracle_fdw from sources from here.
If not done already, install Oracle Database (11.2.0.1.0) for "Microsoft Windows (32-bit)" from here.
=> ports are: - TNS: 1521 - MTS: 2030 - HTTP: 8081
Set the ORACLE_HOME environment variable (used by "oracle_fdw" Makefile)
export ORACLE_HOME=/d/app2/golgauth/product/11.2.0/dbhome_1
Add required oracle includes
export C_INCLUDE_PATH=$C_INCLUDE_PATH:$ORACLE_HOME"/../OCI/include"
Build
cd oracle_fdw/ make CC=$ARCH"-gcc" make install
=> Results in:
/bin/install -c -m 755 oracle_fdw.dll 'C:/mingw32/msys/local/pgsql/lib/oracle_fdw.dll' /bin/install -c -m 644 ./oracle_fdw.control 'C:/mingw32/msys/local/pgsql/share/extension/' /bin/install -c -m 644 ./oracle_fdw--1.0.sql 'C:/mingw32/msys/local/pgsql/share/extension/' /bin/install -c -m 644 ./README.oracle_fdw 'C:/mingw32/msys/local/pgsql/share/doc/extension/'
Install the "oci" dependency
cp $ORACLE_HOME"oci.dll" /usr/local/bin/
Integrate "msvcr80.dll" to "mingw32" ("oci.dll" depends on it)
cp $ORACLE_HOME"/BIN/msvcr80.dll" $MGWDIR/$ARCH/lib/
Set PATH correctly on your Oracle Database server side
SET ORACLE_HOME=D:\app\golgauth2\product\11.2.0\dbhome_1; SET TNS_ADMIN=D:\app\golgauth2\product\11.2.0\dbhome_1\NETWORK\ADMIN; SET PATH=%PATH%;D:\app\golgauth2\product\11.2.0\dbhome_1\BIN;
Setting up the Oracle Database
sqlplus SYSTEM/orapwd@//localhost:1521/ORADB
SQL> CREATE USER C##ORAUSER IDENTIFIED BY orapwd; GRANT connect, resource TO C##ORAUSER; GRANT UNLIMITED TABLESPACE TO C##ORAUSER; CONNECT C##ORAUSER/orapwd@//localhost:1521/ORADB; SHOW USER;
SQL> CREATE SCHEMA AUTHORIZATION C##ORAUSER CREATE table ORATAB ( ID NUMBER(5,0) NOT NULL, TEXT VARCHAR2(30), FLOATING NUMBER(7,2) NOT NULL );
SQL> DESCRIBE ORATAB Name Null? Type ------------------------------- -------- ------------ ID NOT NULL NUMBER(5) TEXT VARCHAR2(30) FLOATING NOT NULL NUMBER(7,2) SQL> INSERT INTO ORATAB (ID,TEXT,FLOATING) values (1, 'a', 1.1); INSERT INTO ORATAB (ID,TEXT,FLOATING) values (2, 'b', 2.2); INSERT INTO ORATAB (ID,TEXT,FLOATING) values (3, 'c', 3.3); SQL> SELECT * FROM ORATAB; SQL> QUIT;
Connecting to Oracle Database (PostgreSQL side)
--DROP EXTENSION oracle_fdw CASCADE; CREATE EXTENSION oracle_fdw; -- Then create a foreign server to connect to your OracleDB server: CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//localhost:1521/ORADB'); -- Get privilege & Create user mapping: CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'C##ORAUSER', password 'orapwd'); GRANT USAGE ON FOREIGN SERVER oradb TO postgres; -- Create a foreign table: CREATE FOREIGN TABLE oratab ( id integer NOT NULL, text character varying(30), floating double precision NOT NULL ) SERVER oradb OPTIONS (schema 'C##ORAUSER', table 'ORATAB'); -- Show table contents: SELECT * FROM oratab;
1;"a";1.1 2;"b";2.2 3;"c";3.3
{ READ this SO thread }
The six-step process to remove all things Oracle from a Windows machine:
Delete the Oracle services: In the registry, go to \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and delete everything that starts with "Oracle" (+ do the same for "ControlSet001", "ControlSet002", ...)
Remove other Oracle stuff from the registry: Go to \HKEY_LOCAL_MACHINE\SOFTWARE\ and delete the key ORACLE
Reboot
Delete all the Oracle software from the directories where you installed it
Delete the Oracle software inventory: Delete the directory C:\Program Files\Oracle or C:\Program Files (x86)\Oracle. You must do this no matter where you installed your Oracle software - the Oracle installer automatically writes information here.
Delete all shortcuts from your Start menu.
Compilation is pretty straight forward (see other tutorials), just one thing to be highlighted: this wrapper depends on the odbc driver installed on your computer. In the following example, since we want to connect to a Microsoft Access database, the Makefile has to be slightly modified as follows:
SHLIB_LINK = -lodbc32 # instead of '-lodbc'
This will link to the odbc32.dll file corresponding to the targeted platform (from System32 for 64bit, from SysWOW64 for 32bit).
How to create the MS Access database is well explained here.
WORKING sql code:
psql -c"CREATE EXTENSION odbc_fdw;" -U postgres psql -c"CREATE SERVER odbc_server FOREIGN DATA WRAPPER odbc_fdw OPTIONS (dsn 'DSN_2013_OK');" -U postgres psql -c"CREATE USER MAPPING FOR postgres SERVER odbc_server OPTIONS (username 'glg', password 'glg');" -U postgres psql -c"CREATE FOREIGN TABLE odbc_table ( rubrique text, titre text, annee int) SERVER odbc_server OPTIONS ( database 'Test2013_ok', schema '', sql_query 'SELECT Rubrique, Rtitre, Annee FROM Formation', table 'Formation', rubrique 'Rubrique', titre 'Rtitre', annee 'Annee' );" -U postgres CREATE FOREIGN TABLE psql -c"SELECT * from odbc_table;" -U postgres rubrique | titre | annee ----------+-----------+------- rub_val | titre_val | 1902 (1 row)
Note: It came to work mainly at the time I added the missing option "schema ''" and probably
"sql_query '…'" as well...