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.
<br /> CREATE TABLE federated_table (<br /> id INT(20) NOT NULL AUTO_INCREMENT,<br /> name VARCHAR(32) NOT NULL DEFAULT '',<br /> other INT(20) NOT NULL DEFAULT '0',<br /> PRIMARY KEY (id),<br /> INDEX name (name),<br /> INDEX other_key (other)<br /> )<br /> ENGINE=FEDERATED<br /> DEFAULT CHARSET=latin1<br /> CONNECTION='mysql://root@remote_host:9306/federated/test_table';<br />
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?
