Since MariaDB 5.2, a storage engine can allow the user to specify additional attributes per index, field, or table. The engine needs to declare what attributes it introduces.
== API
There are three new members in the <<code>>hanlerton<</code>> structure, they can be set in the engine's initialization function as follows:
<<code lang=c>>
example_hton->table_options= example_table_option_array;
example_hton->field_options= example_field_option_array;
example_hton->index_options= example_index_option_array;
<</code>>
The arrays are declared statically, as in the following example:
<<code lang=c>>
struct ha_table_option_struct
{
char *strparam;
ulonglong ullparam;
uint enumparam;
bool boolparam;
};
ha_create_table_option example_table_option_list[]=
{
HA_TOPTION_NUMBER("NUMBER", ullparam, UINT_MAX32, 0, UINT_MAX32, 10),
HA_TOPTION_STRING("STR", strparam),
HA_TOPTION_ENUM("one_or_two", enumparam, "one,two", 0),
HA_TOPTION_BOOL("YESNO", boolparam, 1),
HA_TOPTION_END
};
<</code>>
The engine declares a structure
<<code lang=c inline=true>>ha_table_option_struct<</code>>
that will hold values of these new attributes.
And it describes these attributes to MySQL by creating an array of
<<code lang=c inline=true>>HA_TOPTION_*<</code>> macros. Note a detail: these macros expect a structure called
<<code lang=c inline=true>>ha_table_option_struct<</code>>, if the structure is called differently, a
<<code lang=c inline=true>>#define<</code>> will be needed.
There are four supported types of attributes:
<<style class="darkheader-nospace-borders" style="font-size:0.9em;">>
|= type|= corresponding<<entity>>nbsp<</entity>>C<<entity>>nbsp<</entity>>type|= macro name|= additional parameters of a macro|
|an integer number|##unsigned long long##|##HA_TOPTION_NUMBER##|a default value, minimal allowed value, maximal allowed value, a factor, that any allowed should be a multiple of.|
| a string|##char *##|##HA_TOPTION_STRING##|none. The default value is a null pointer.|
|one value from a list of allowed values|##unsigned int##| ##HA_TOPTION_ENUM##|a string with a comma-separated list of allowed values, and a default value as a number, starting from 0.|
|a boolean|##bool##|##HA_TOPTION_BOOL##|a default value|
<</style>>
//Do **not** use // <<code>>enum<</code>> // for your // <<code>>HA_TOPTION_ENUM<</code>> // C structure members, the size of the // <<code>>enum<</code>> // depends on the compiler, and even on the compilation options, and the plugin API uses only types with known storage sizes.//
In all macros the first two parameters are name of the attribute as should be used in SQL in the <<code>>CREATE TABLE<</code>> statement, and the name of the corresponding member of the <<code>>ha_table_option_struct<</code>> structure.
The array ends with a <<code>>HA_TOPTION_END<</code>> macro.
Field and index (key) attributes are declared similarly using <<code>>HA_FOPTION_*<</code>> and <<code>>HA_IOPTION_*<</code>> macros.
When in a <<code>>CREATE TABLE<</code>> statement, the <<code>>::create()<</code>> handler method is called, the table attributes are available in the <<code>>table_arg->s->option_struct<</code>>, field attributes - in the <<code>>option_struct<</code>> member of the individual fields (objects of the <<code>>Field<</code>> class), index attributes - in the <<code>>option_struct<</code>> member of the individual keys (objects of the <<code>>KEY<</code>> class).
Additionally, they are available in most other handler methods: the attributes are stored in the <<code>>.frm<</code>> file and on every open MySQL makes them available to the engine by filling the corresponding <<code>>option_struct<</code>> members of the table, fields, and keys.
The <<code>>ALTER TABLE<</code>> needs a special support from the engine. MySQL compares old and new table definitions to decide whether it needs to rebuild the table or not. As the semantics of the engine declared attributes is unknown, MySQL cannot make this decision by analyzing attribute values - this is delegated to the engine. The <<code>>HA_CREATE_INFO<</code>> structure has three new members:
<<code lang=c>>
ha_table_option_struct *option_struct; ///< structure with parsed table options
ha_field_option_struct **fields_option_struct; ///< array of field option structures
ha_index_option_struct **indexes_option_struct; ///< array of index option structures
<</code>>
The engine (in the <<code>>::check_if_incompatible_data()<</code>> method) is responsible for comparing new values of the attributes from the <<code>>HA_CREATE_INFO<</code>> structure with the old values from the table and returning <<code>>COMPATIBLE_DATA_NO<</code>> if they were changed in such a way that requires the table to be rebuild.
The example of declaring the attributes and comparing the values for the <<code>>ALTER TABLE<</code>> can be found in the EXAMPLE engine.
== SQL
The engine declared attributes can be specified per field, index, or table in the <<code>>CREATE TABLE<</code>> or <<code>>ALTER TABLE<</code>>. The syntax is the conventional:
<<sql>>
CREATE TABLE ... (
field ... [attribute=value [attribute=value ...]],
...
index ... [attribute=value [attribute=value ...]],
...
) ... [attribute=value [attribute=value ...]]
<</sql>>
All values must be specified as literals, not expressions. The value of a boolean option may be specified as one of YES, NO, ON, OFF, 1, or 0. A string value may be specified either quoted or not, as an identifier (if it is a valid identifier, of course). Compare with the old behavior:
<<sql>>
CREATE TABLE ... (
) ENGINE=FEDERATED CONNECTION='mysql://root@127.0.0.1';
<</sql>>
where the value of the ENGINE attribute is specified not quoted, while the value of the CONNECTION is quoted.
When an attribute is set, it will be stored with the table definition and shown in the <<sql>>SHOW CREATE TABLE;<</sql>>. To remove an attribute from a table definition use <<sql>>ALTER TABLE<</sql>> to set its value to a <<sql>>DEFAULT<</sql>>.
The values of unknown attributes or attributes with the illegal values cause an error by default. But by enabling <<code lang=sql inline=true>>IGNORE_BAD_TABLE_OPTIONS<</code>> sql mode this error can be downgraded to a warning. In this case invalid attributes are stored in the <<code>>.frm<</code>> file verbatim, as later, if the table is altered to use a different storage engine, they may become valid for the new engine. This mode is implicitly enabled in the replication slave thread.
== See Also
* [[writing-plugins-for-mariadb|Writing Plugins for MariaDB]]
* [[storage-engines|Storage Engines]]
* [[storage-engine-development|Storage Engine Development]]
