Adding a New Data Type to MariaDB with Type_handler – Part 5

We are concluding our series related to new data types using the Type_handler framework, with some limitations that are not yet covered by the framework:

• No custom indexing methods. A plugin type cannot introduce a new indexing method.
• No custom hashing. Plugin types can provide their own function for hash-based operations. Things like MEMORY table indexes, GROUP BY, and partitioning fall back to the underlying type’s hash.
• No new field attributes. Plugin types cannot define custom attributes beyond the existing ones: length, precision, scale, and GIS SRID.

It would have been handy for our MONEY datatype to have the possibility to define, for example, the currency to show. Or the format to have something like this:

• $1,000,000
• 40.000,50 EUR
• ¥2 000 000

Unfortunately, we can’t create new field attributes for our data type. As you probably already know, a column has an extra attribute called COMMENT. We can use it to serve our needs.

Extending Field_money

We will need to extend our Field_money class in sql_type_money.h with a format style and the display configuration structure:

  enum money_format_style
  {
    MONEY_FMT_CURRENCY_FIRST= 0,
    MONEY_FMT_CURRENCY_LAST
  };

  struct money_display_config
  {
    char currency[32];
    char decimal_sep;
    char thousands_sep;
    bool allow_grouping;
    money_format_style style;
  };

And we also add two private methods to parse the COMMENT attribute and format the output:

private:
  bool parse_comment_config(money_display_config *cfg) const;
  void format_money_string(String *to, double nr,
                           const money_display_config &cfg) const;

These two functions will be specified in plugin.cc

bool Field_money::parse_comment_config(money_display_config *cfg) const
{
  money_set_default_display_config(cfg);

  if (!comment.str || !comment.length)
    return true;

  const char *p= comment.str;
  const char *end= comment.str + comment.length;

  while (p < end)
  {
    const char *entry_end= (const char*) memchr(p, ';', (size_t) (end - p));
    if (!entry_end)
      entry_end= end;

    const char *eq= (const char*) memchr(p, '=', (size_t) (entry_end - p));
    if (eq)
    {
      const char *k= money_trim_left(p, eq);
      const char *k_end= money_trim_right_begin(k, eq);
      const char *v= money_trim_left(eq + 1, entry_end);
      const char *v_end= money_trim_right_begin(v, entry_end);
      size_t k_len= (size_t) (k_end - k);
      size_t v_len= (size_t) (v_end - v);
      money_apply_comment_option(k, k_len, v, v_len, cfg);
    }

    p= entry_end < end ? entry_end + 1 : end;
  }

  if (cfg->decimal_sep == cfg->thousands_sep)
  {
    if (cfg->decimal_sep == ',')
      cfg->thousands_sep= '.';
    else
      cfg->thousands_sep= ',';
  }

  return true;
}

void Field_money::format_money_string(String *to, double nr,
                                      const money_display_config &cfg) const
{
  decimal_digits_t out_dec= money_effective_decimals(dec);
  char raw[DOUBLE_TO_STRING_CONVERSION_BUFFER_SIZE];
  size_t raw_len= my_fcvt(nr, out_dec, raw, NULL);
  raw[raw_len]= '\0';
  const char *p= raw;
  bool negative= false;

  if (*p == '-')
  {
    negative= true;
    p++;
  }

  const char *dot= strchr(p, '.');
  size_t int_len= dot ? (size_t) (dot - p) : strlen(p);
  const char *frac= dot ? dot + 1 : "";
  size_t frac_len= dot ? strlen(frac) : 0;

  to->length(0);
  to->set_charset(system_charset_info);

  if (negative && to->append('-'))
    return;

  if (cfg.style == MONEY_FMT_CURRENCY_FIRST && money_append_currency(to, cfg, true))
    return;

  for (size_t i= 0; i < int_len; i++)
  {
    if (cfg.allow_grouping && cfg.thousands_sep && i > 0 && ((int_len - i) % 3) == 0)
    {
      if (to->append(cfg.thousands_sep))
        return;
    }
    if (to->append(p[i]))
      return;
  }

  if (out_dec > 0)
  {
    if (to->append(cfg.decimal_sep))
      return;
    for (uint i= 0; i < out_dec; i++)
    {
      if (to->append((i < frac_len) ? frac[i] : '0'))
        return;
    }
  }

  if (cfg.style == MONEY_FMT_CURRENCY_LAST && money_append_currency(to, cfg, false))
    return;
}

All the other functions used by these two functions are defined in a new include file money.h.

Finally, we modified Field_money::send(Protocol *protocol) to parse the config before displaying it:

bool Field_money::send(Protocol *protocol)
{
  DBUG_ASSERT(marked_for_read());

  String money_buf;

  money_display_config cfg;
  parse_comment_config(&cfg);
  format_money_string(&money_buf, Field_money::val_real(), cfg);

  return protocol->store(&money_buf);
}

The code for the modified data type is, as usual, in the GitHub repository on the part5 branch.

Example

As an example is worth a thousand words, let’s see the plugin in action:

MariaDB [test]> install soname 'type_money';
Query OK, 0 rows affected (0.012 sec

MariaDB [test]> use test;
Database changed

MariaDB [test]> create table t1 (id int auto_increment primary key, 
    amount money(10,2) comment
      'currency=EUR;format=currenty_last;decimal=,;thousands=space',
    credit money(10,2) default null comment
     'currency=$;format=currency_first;decimal=.;thousands=,'
    ) engine=innodb;
Query OK, 0 rows affected (0.006 sec)

MariaDB [test]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` money(10,2) DEFAULT NULL COMMENT 'currency=EUR;format=currenty_last;decimal=,;thousands=space',
  `credit` money(10,2) DEFAULT NULL COMMENT 'currency=$;format=currency_first;decimal=.;thousands=,',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1 row in set (0.011 sec)


MariaDB [test]> insert into t1 (amount,credit) values (41578.4,2342);
Query OK, 1 row affected (0.011 sec)

MariaDB [test]> insert into t1 (amount,credit) values (24.4,12.50);
Query OK, 1 row affected (0.013 sec)

MariaDB [test]> insert into t1 (amount,credit) values (15678.309,-1234.299);
Query OK, 1 row affected (0.006 sec)


MariaDB [test]> select * from t1;
+----+---------------+------------+
| id | amount        | credit     |
+----+---------------+------------+
|  1 | 41 578,40 EUR | $2,342.00  |
|  2 | 15 678,31 EUR | -$1,234.30 |
|  3 | 24,40 EUR     | $12.50     |
+----+---------------+------------+
3 rows in set (0.010 sec)

Conlusion

As you’ve seen throughout this series, it’s quite simple to add a new data type to MariaDB using the Type_handler framework.

If we compare it to MySQL, it’s much easier to add a new data type, and the new ones provided in MariaDB, such as INET4, INET6, UUID are all already using the framework.

So let us know which data type you are adding, and happy coding!

• Adding a New Data Type to MariaDB with Type_handler – Part 0 – how to build MariaDB Server
• Adding a New Data Type to MariaDB with Type_handler – Part 1 – understanding the framework
• Adding a New Data Type to MariaDB with Type_handler – Part 2 – minimal working data type
• Adding a New Data Type to MariaDB with Type_handler – Part 3 – data type output transformation
• Adding a New Data Type to MariaDB with Type_handler – Part 4 – overriding existing types
• Adding a New Data Type to MariaDB with Type_handler – Part 5 – limitations & workarounds