I’ve never used Federated. I’m waiting for the JDBC version capabilities so I can connect to a non MySQL Server (specifically Oracle). In reading the docs, I see that the syntax includes a CONNECTION String.


CREATE TABLE federated_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root@remote_host:9306/federated/test_table';


I’m surprised in the state of the syntax for two reasons.

• First, there are hardcoded variables in the string, this sort of breaks the rules of seperating syntax from authentication specifics
• Second, if you have multiple FEDERATED tables, you incur a level of duplication of this information, and then multiple necessary changes if the connection string is modified

I can’t ask I’d like to see the following, as I don’t use this syntax, but I’d like to suggest a level of abstraction of this information. Could the CONNECTION point to a referenced object (e.g. CONNECTION=’HOST1′). This would solve my second point. Now, the requirements of the HOST1 object needs to contain a connection string, and where would this be stored. I don’t have an answer for that one. Perhaps somebody else could make a suggestion?

Updated
Thanks Mike for your comment, click on comments below to read. Boy it’s been been a long time since I read any ISO Standards documents. Just to clarify Mike’s point for those investigative types. SQL/MED is Management of External Data, and the official ISO document is I believe:

ISO/IEC 9075-9:2003 Information technology – Database Languages – SQL – Part 9: Management of External Data (SQL/MED) ISO Reference

Abstract
ISO/IEC 9075 defines the SQL language. The scope of the SQL language is the definition of data structure and the operations on data stored in that structure. Parts 1, 2 and 11 encompass the minimum requirements of the language. Others parts define extensions.
ISO/IEC 9075-9:2003 defines extensions to SQL to support management of external data through the use of foreign-data wrappers and datalink types.

This document makes reference to DATALINK, a syntax I remember vaguely from Oracle Days. Will need to vett that. What’s also of note in this document is the reference to using the TABLE TYPE of ‘FOREIGN’.

I wonder why MySQL went with FEDERATED as the TABLE TYPE. I’m sure this is a good reason?