MySQL Federated ODBC - Hello PostgreSQL!

Multiple Data sources in action! First, MySQL:

mysql> show plugins;
+----------------+--------+----------------+-----------------------------+---------+
| Name | Status | Type | Library | License |
+----------------+--------+----------------+-----------------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbcluster | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED_ODBC | ACTIVE | STORAGE ENGINE | libfederated_odbc_engine.so | GPL |
+----------------+--------+----------------+-----------------------------+---------+
12 rows in set (0.01 sec)

mysql> show create table t5;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t5 | CREATE TABLE `t5` (
`a` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`)
) ENGINE=FEDERATED_ODBC DEFAULT CHARSET=latin1 CONNECTION='obdc://Driver=myodbc3;Server=localhost;Database=federated_odbc;Port=5555;socket=/tmp/mysql-5555.sock;Option=3;UID=root:t2' |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t5;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)


let's create a nice little postgres table:

patg=> create table t1 (a int);
CREATE TABLE
patg=> insert into t1 values (1);
INSERT 0 1
patg=> insert into t1 values (22);
INSERT 0 1
patg=> insert into t1 values (333);
INSERT 0 1
patg=> select * from t1;
a
-----
1
22
333
(3 rows)

patg=>

Let us now create a Federated ODBC table to this nice little postgres table:

mysql> create table t1pg (a int(11)) ENGINE=FEDERATED_ODBC CONNECTION='odbc://Driver=postgresql;Server=localhost;Database=patg;Port=5432;Option=3;UID=patg;PASSWD=patg:t1';
Query OK, 0 rows affected (0.20 sec)

mysql> select * from t1pg;
+------+
| a |
+------+
| 1 |
| 22 |
| 333 |
+------+
3 rows in set (0.02 sec)

Very nice indeed!

One of the biggest headaches has been realising that backtick "`" characters to "quote" column or table names do NOT work on postgres. Neither does 'show table status' in ::info. There are other syntax errors to deal with as well, and I have to play with postgres's logging verbosity to see what it means when it says there has been a syntax error.Another question I have that I might've stumbled onto is that the connection handle I create is a global class handle and not specific to the share. So if I connect to
t5 and t5 happens a MySQL DSN (DSN/connection), then I access t1, which _should_ be a postgres DSN (DSN/connection), then I don't see that working all that well. So, what about each share having a connection? Also, how does one do functions like ::info? Not every database will give me information like 'show table status' gives me in MySQL.

A comment to a previous post indicated I would have to make sure all SQL is ANSI, and I think there's going to be a bit of thinking, work, to get this to be ANSI as possible but have it work nicely.