Skip to content
Permalink

Comparing changes

Choose two branches to see what’s changed or to start a new pull request. If you need to, you can also or learn more about diff comparisons.

Open a pull request

Create a new pull request by comparing changes across two branches. If you need to, you can also . Learn more about diff comparisons here.
base repository: ZhengYang/odbc_fdw
Failed to load repositories. Confirm that selected base ref is valid, then try again.
Loading
base: master
Choose a base ref
...
head repository: bluthg/odbc_fdw
Failed to load repositories. Confirm that selected head ref is valid, then try again.
Loading
compare: master
Choose a head ref
Able to merge. These branches can be automatically merged.
  • 3 commits
  • 2 files changed
  • 1 contributor

Commits on Apr 19, 2014

  1. Added callback routines for PostgreSQL >= 9.2 

    based on code from tds_fdw (https://github.com/GeoffMontee/tds_fdw).
    @bluthg
    bluthg committed Apr 19, 2014
    Copy the full SHA
    0d44e9d View commit details

Commits on Jul 13, 2016

  1. Update README

    @bluthg
    bluthg authored Jul 13, 2016
    Copy the full SHA
    9757795 View commit details

Commits on Oct 29, 2020

  1. Update README

    @bluthg
    bluthg authored Oct 29, 2020

    Verified

    This commit was created on GitHub.com and signed with GitHub’s verified signature. The key has expired.
    GPG key ID: 4AEE18F83AFDEB23
    Expired
    Verified
    Learn about vigilant mode
    Copy the full SHA
    30237b3 View commit details
Showing with 173 additions and 106 deletions.
  1. +6 −103 README
  2. +167 −3 odbc_fdw.c
109 changes: 6 additions & 103 deletions README
View file
Original file line number Diff line number Diff line change
@@ -1,104 +1,7 @@
ODBC FDW (beta) for PostgreSQL 9.1+
===================================

This PostgreSQL extension implements a Foreign Data Wrapper (FDW) for
remote databases using Open Database Connectivity(ODBC): http://msdn.microsoft.com/en-us/library/ms714562(v=VS.85).aspx


Building
--------

To build the code, you need to have one of the ODBC driver managers installed
on your computer.

A list of driver managers is available here: http://en.wikipedia.org/wiki/Open_Database_Connectivity

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 and Windows 7. More testings on other platforms are coming soon.

TODO
-----------

I will test against other platforms with different driver managers and more foreign DBMS's.

Usage
-----

The following parameters can be set on ODBC foreign server:

dsn: The Database Source Name for the foreign database system you're connecting to.
Default: <none>

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

database: The name of the database to query.
Default: <none>

schema: The schema of the database to query.
Default: <none>

table: The name of the table to query.
Default: <none>

sql_query: Optional: User defined SQL statement for querying the foreign table.
Default: <none>

sql_count: Optional: User defined SQL statement for counting number of records in the foreign table.
Default: <none>

<column name>: The column mapping to remote table columns.
If there is no column mapping specified, default is the origninal column name.

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

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

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


Example
-------


CREATE SERVER odbc_server
FOREIGN DATA WRAPPER odbc_fdw
OPTIONS (dsn 'test');

CREATE FOREIGN TABLE odbc_table (
db_id integer,
db_name varchar(255),
db_desc text,
db_users float4,
db_createdtime timestamp
)
SERVER odbc_server
OPTIONS (
database 'myplace',
schema 'test',
table 'dblist',
sql_query 'select description,id,name,created_datetime,sd,users from `test`.`dblist`',
sql_count 'select count(id) from `test`.`dblist`',
db_id 'id',
db_name 'name',
db_desc 'description',
db_users 'users',
db_createdtime 'created_datetime'
);

CREATE USER MAPPING FOR postgres
SERVER odbc_server
OPTIONS (username 'root', password '');


--
Zheng Yang
zhengyang4k@gmail.com
************************************************
* Note: this fork was for some private purpose
* and had some significant issues.
* Pls. check out https://github.com/CartoDB/odbc_fdw
* instead!
************************************************
170 changes: 167 additions & 3 deletions odbc_fdw.c
View file
Original file line number Diff line number Diff line change
@@ -7,6 +7,8 @@
* This software is released under the PostgreSQL Licence.
*
* Author: Zheng Yang <zhengyang4k@gmail.com>
* Updated to 9.2+ by Gunnar "Nick" Bluth <nick@pro-open.de>
* based on tds_fdw code from Geoff Montee
*
* IDENTIFICATION
* odbc_fdw/odbc_fdw.c
@@ -36,6 +38,19 @@
#include "utils/relcache.h"
#include "storage/lock.h"
#include "miscadmin.h"
#include "mb/pg_wchar.h"
#include "optimizer/cost.h"
#include "storage/fd.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/rel.h"

#if (PG_VERSION_NUM >= 90200)
#include "optimizer/pathnode.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/planmain.h"
#endif


#include <stdio.h>
#include <sql.h>
@@ -109,12 +124,25 @@ PG_FUNCTION_INFO_V1(odbc_fdw_validator);
/*
* FDW callback routines
*/
static FdwPlan *odbcPlanForeignScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel);
static void odbcExplainForeignScan(ForeignScanState *node, ExplainState *es);
static void odbcBeginForeignScan(ForeignScanState *node, int eflags);
static TupleTableSlot *odbcIterateForeignScan(ForeignScanState *node);
static void odbcReScanForeignScan(ForeignScanState *node);
static void odbcEndForeignScan(ForeignScanState *node);
/* routines for 9.2.0+ */
#if (PG_VERSION_NUM >= 90200)
static void odbcGetForeignRelSize(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid);
static void odbcEstimateCosts(PlannerInfo *root, RelOptInfo *baserel, Cost *startup_cost, Cost *total_cost, Oid foreigntableid);
static void odbcGetForeignPaths(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid);
static bool odbcAnalyzeForeignTable(Relation relation, AcquireSampleRowsFunc *func, BlockNumber *totalpages);
static ForeignScan* odbcGetForeignPlan(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid, ForeignPath *best_path, List *tlist, List *scan_clauses);
/* routines for versions older than 9.2.0 */
#else
static FdwPlan *odbcPlanForeignScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel);
#endif




/*
* helper functions
@@ -125,8 +153,15 @@ Datum
odbc_fdw_handler(PG_FUNCTION_ARGS)
{
FdwRoutine *fdwroutine = makeNode(FdwRoutine);

/* FIXME */
#if (PG_VERSION_NUM >= 90200)
fdwroutine->GetForeignRelSize = odbcGetForeignRelSize;
fdwroutine->GetForeignPaths = odbcGetForeignPaths;
fdwroutine->AnalyzeForeignTable = odbcAnalyzeForeignTable;
fdwroutine->GetForeignPlan = odbcGetForeignPlan;
#else
fdwroutine->PlanForeignScan = odbcPlanForeignScan;
#endif
fdwroutine->ExplainForeignScan = odbcExplainForeignScan;
fdwroutine->BeginForeignScan = odbcBeginForeignScan;
fdwroutine->IterateForeignScan = odbcIterateForeignScan;
@@ -701,6 +736,134 @@ odbcIsValidOption(const char *option, Oid context)
}


/* routines for 9.2.0+ */
#if (PG_VERSION_NUM >= 90200)

static void odbcGetForeignRelSize(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid)
{
unsigned int table_size = 0;
char *svr_dsn = NULL;
char *svr_database = NULL;
char *svr_schema = NULL;
char *svr_table = NULL;
char *sql_query = NULL;
char *sql_count = NULL;
char *username = NULL;
char *password = NULL;
List *col_mapping_list;

#ifdef DEBUG
elog(NOTICE, "odbcGetForeignRelSize");
#endif

/* Fetch the foreign table options */
odbcGetOptions(foreigntableid, &svr_dsn, &svr_database, &svr_schema, &svr_table, &sql_query,
&sql_count, &username, &password, &col_mapping_list);

odbcGetTableSize(svr_dsn, svr_database, svr_schema, svr_table, username, password, sql_count, &table_size);

baserel->rows = table_size;
baserel->tuples = baserel->rows;
}

static void odbcEstimateCosts(PlannerInfo *root, RelOptInfo *baserel, Cost *startup_cost, Cost *total_cost, Oid foreigntableid)
{
unsigned int table_size = 0;
char *svr_dsn = NULL;
char *svr_database = NULL;
char *svr_schema = NULL;
char *svr_table = NULL;
char *sql_query = NULL;
char *sql_count = NULL;
char *username = NULL;
char *password = NULL;
List *col_mapping_list;

#ifdef DEBUG
elog(NOTICE, "odbcEstimateCosts");
#endif

/* Fetch the foreign table options */
odbcGetOptions(foreigntableid, &svr_dsn, &svr_database, &svr_schema, &svr_table, &sql_query,
&sql_count, &username, &password, &col_mapping_list);

odbcGetTableSize(svr_dsn, svr_database, svr_schema, svr_table, username, password, sql_count, &table_size);

*startup_cost = 25;

*total_cost = baserel->rows + *startup_cost;

#ifdef DEBUG
ereport(NOTICE,
(errmsg("----> finishing odbcEstimateCosts")
));
#endif
}

static void odbcGetForeignPaths(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid)
{
Cost startup_cost;
Cost total_cost;

#ifdef DEBUG
ereport(NOTICE,
(errmsg("----> starting odbcGetForeignPaths")
));
#endif

odbcEstimateCosts(root, baserel, &startup_cost, &total_cost, foreigntableid);

add_path(baserel,
(Path *) create_foreignscan_path(root, baserel, baserel->rows, startup_cost, total_cost,
NIL, NULL, NIL));

#ifdef DEBUG
ereport(NOTICE,
(errmsg("----> finishing odbcGetForeignPaths")
));
#endif
}

static bool odbcAnalyzeForeignTable(Relation relation, AcquireSampleRowsFunc *func, BlockNumber *totalpages)
{
#ifdef DEBUG
ereport(NOTICE,
(errmsg("----> starting odbcAnalyzeForeignTable")
));
#endif

#ifdef DEBUG
ereport(NOTICE,
(errmsg("----> finishing odbcAnalyzeForeignTable")
));
#endif

return false;
}

static ForeignScan* odbcGetForeignPlan(PlannerInfo *root, RelOptInfo *baserel,
Oid foreigntableid, ForeignPath *best_path, List *tlist, List *scan_clauses)
{
Index scan_relid = baserel->relid;
#ifdef DEBUG
ereport(NOTICE,
(errmsg("----> starting odbcGetForeignPlan")
));
#endif

scan_clauses = extract_actual_clauses(scan_clauses, false);

#ifdef DEBUG
ereport(NOTICE,
(errmsg("----> finishing odbcGetForeignPlan")
));
#endif

return make_foreignscan(tlist, scan_clauses, scan_relid, NIL, NIL);
}

/* routines for versions older than 9.2.0 */
#else

/*
* odbcPlanForeignScan
@@ -747,6 +910,7 @@ odbcPlanForeignScan(Oid foreigntableid, PlannerInfo *root, RelOptInfo *baserel)
return fdwplan;
}

#endif

/*
* odbcBeginForeignScan
@@ -1248,4 +1412,4 @@ odbcReScanForeignScan(ForeignScanState *node)
#ifdef DEBUG
elog(NOTICE, "odbcReScanForeignScan");
#endif
}
}