WL#3807: Automatic enum data type

Dictionary based space management for state values. Original idea by Dmitry
Yankovsky.

Create new data type "auto enum" which automatically organizes a 1:n
relationship transparent for the user.

This approach could also be used for normalizing table structures inside of the
server without changing the DDL interface with an ALTER TABLE MODIFY COLUMN a
auto enum statement.

A field declared as "auto enum" stores only an id for it's value and safes
therefore duplicate's space.

This idea can be described with two tables and a view. It would be nice to have
such a data type inside of the MySQL server.


SET @@sql_mode= 'TRADITIONAL';

DROP TABLE IF EXISTS order_base;
DROP TABLE IF EXISTS state_base;
DROP VIEW IF EXISTS "order";

CREATE TABLE order_base (
  id int unsigned auto_increment NOT NULL PRIMARY KEY,
  name varchar(150),
  state varchar(150),  -- Dummy field as placeholder for INSERTs.
  state_id int unsigned
) Engine MyISAM;

CREATE TABLE state_base (
  id int unsigned auto_increment NOT NULL PRIMARY KEY,
  state varchar (150)
) Engine MyISAM;

CREATE VIEW "order" AS
SELECT
  a.id,
  a.name,
  b.state -- This would be the auto enum field.
FROM order_base as a, state_base as b
WHERE a.state_id = b.id;

DROP TRIGGER IF EXISTS dictionary;
DELIMITER //
CREATE TRIGGER dictionary BEFORE INSERT ON "order_base"
  FOR EACH ROW BEGIN
    SELECT count(*) INTO @a FROM state_base WHERE state_base.state = NEW.state;
    IF @a = 0 THEN
      INSERT INTO state_base VALUES (DEFAULT, NEW.state);
    END IF;
    SELECT id INTO @rec_id FROM state_base WHERE state = NEW.state;
    SET NEW.state_id = @rec_id;
    SET NEW.state = NULL;  -- Saving space!
  END;//
DELIMITER ;

-- MySQL has no TRIGGER ON SELECT!
--
-- DROP TRIGGER IF EXISTS dictionary_select;
-- DELIMITER //
-- CREATE TRIGGER dictionary_select AFTER SELECT ON "order"
--   FOR EACH ROW BEGIN
--     SELECT state INTO @state FROM state_base WHERE id = OLD.state_id;
--     SET NEW.state = @state;
--   END;//
-- DELIMITER ;

INSERT INTO order_base (name, state) VALUES ('first item', 'first state');
INSERT INTO order_base (name, state) VALUES ('first item', 'first state');
INSERT INTO order_base (name, state) VALUES ('first item', 'first state2');

SELECT name, state FROM "order";
SELECT * FROM state_base;