pointers on implementing foreign keys for a storage engine

[Zardosht Kasheff]

Hello all,

Are there any pointers for how one would go about implementing foreign
keys in the storage engine? I cannot seem to figure it out from the
handler API.

Thanks
-Zardosht

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals

[tomas ulin]

There is no API for it. InnoDB parses the query itself for the foreign
key part.

BR,

Tomas

[Stewart Smith]

On Tue, 8 May 2012 11:23:34 -0400, Zardosht Kasheff <zard...@gmail.com> wrote:
> Are there any pointers for how one would go about implementing foreign
> keys in the storage engine? I cannot seem to figure it out from the
> handler API.

As Tomas said, there isn't really an API.

Ther was an aborted attempt to have server side Foreign keys (see
http://bugs.mysql.com/bug.php?id=43521 for public evidence of this
existing in some form) so that Falcon and other engines could have
foreign keys. I wouldn't hold my breath for this though, since Oracle
has both MySQL and InnoDB, the motivation to work on a rather complex
task to benefit other engines (of which there are not many, and users
are a very small number compared to InnoDB users) is rather small.

It is, and will be for the forseeable future, pretty much entirely the
responsibility of the storage engine.

There's three interactions with the MySQL core:
1) parsing the foreign key statements - look at InnoDB CREATE TABLE code
for how this is done. Yes, you're going to have to have your own parser.
At some point, MySQL began parsing these too... but that may have been
in earlier 6.0 trees around the time we forked Drizzle than in 5.1 or
5.5 or 5.6 which you'd be working against.
2) SHOW CREATE TABLE - again, look at InnoDB for how this is done, you
supply snippets of the SQL back to the server SHOW CREATE TABLE code.
3) propagating errors back to the user (again, look at innodb)

Other engines that have supported foreign keys through this interface
include PBXT... I'm not sure if anyone else got close or ont, I honestly
don't think so.... *maybe* some of the 6.0-falcon trees, but I doubt it.

--
Stewart Smith

[Paul McCullagh]

With some modifications you could use the PBXT code that parses the query, it's quite simple: https://launchpad.net/pbxt

See datadic_xt.cc:

XTParseTable::parseCreateTable()
XTParseTable::parseAlterTable()
XTParseTable::parseCreateIndex()
XTParseTable::parseDropIndex()

On May 9, 2012, at 12:30 PM, tomas ulin wrote:

>
> There is no API for it. InnoDB parses the query itself for the foreign key part.
>
> BR,
>
> Tomas
>
> On 2012-05-08 17:23, Zardosht Kasheff wrote:
>> Hello all,
>>

>> Are there any pointers for how one would go about implementing foreign
>> keys in the storage engine? I cannot seem to figure it out from the
>> handler API.
>>

>> Thanks
>> -Zardosht

[Dmitry Lenev]

Hello Zardosht!

* Zardosht Kasheff <zard...@gmail.com> [12/05/08 19:33]:

> Hello all,
>
> Are there any pointers for how one would go about implementing foreign
> keys in the storage engine? I cannot seem to figure it out from the
> handler API.

I am afraid there is no API which covers full lifecycle of a foreign key
at the moment.

What we have now is part of API which is responsible for providing
information about foreign keys in SE to SQL-layer, to be used by I_S
implementation, SHOW CREATE TABLE statement and some, but not all,
DDL statements like TRUNCATE TABLE, and some DML statements.

This part of API consists of the following handler methods/declarations:

/* Gets foreign key create string from InnoDB for SHOW CREATE TABLE. */
char* get_foreign_key_create_info();
void free_foreign_key_create_info(char* str);

struct st_foreign_key_info;
typedef struct st_foreign_key_info FOREIGN_KEY_INFO;

/*
Used by I_S implementation to get info about foreign keys for I_S
tables.
*/
int get_foreign_key_list(THD *thd, List<FOREIGN_KEY_INFO> *f_key_list);

/*
Used by TRUNCATE TABLE to figure out if table is referenced by some
other table.
*/
int get_parent_foreign_key_list(THD *thd, List<FOREIGN_KEY_INFO> *f_key_list);


/*
Used by TRUNCATE, REPLACE and RBR code to figure out if table is
referenced.
*/
uint referenced_by_foreign_key() { return 0;}

/* Used by partitioning code. */
bool is_fk_defined_on_table_or_index(uint index);

/*
Used by error-reporting code to get information about foreign key
which caused duplicate key problem during update of parent table.
*/
bool get_foreign_dup_key(char *child_table_name, uint child_table_name_len,
char *child_key_name, uint child_key_name_len);


As I have said above there is no real API for creating and dropping
foreign keys during DDL.

InnoDB does this by parsing text of original statement from within certain
storage engine calls (e.g. rename_table()), which are invoked during CREATE
and ALTER TABLE statements. I am not sure that it is a good way to choose
if you are implementing FK support in engine from the scratch. Maybe it is
better to rely on structures that SQL-layer parser produces for foreign keys.
These structures are constructed by parser anyway but just not passed
explicitly to SE.

Hope this helps!

Let me know if you have additional questions!

--
Dmitry Lenev, Software Developer
Oracle Development SPB/MySQL, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification