1create database innodb_memcache;
2
3use innodb_memcache;
4
5
6-- ------------------------------------------------------------------------
7-- Following are set of "configuration tables" that used to configure
8-- the InnoDB Memcached.
9-- ------------------------------------------------------------------------
10
11-- ------------------------------------------------------------------------
12-- Table `cache_policies`
13--
14-- Each record in this table represents a named caching policy, specifying:
15--  * How the memcache GET command is executed, including whether to get
16--    records from local cache only, from InnoDB only, from local cache if
17--    present (treating InnoDB as a backing store), or not at all.
18--  * Similarly, how memcache SET commands are executed.
19--  * How memcache DELETE commands are executed.
20--  * Whether flushing the cache should cause a mass delete from NDB.
21--
22-- ------------------------------------------------------------------------
23CREATE  TABLE IF NOT EXISTS `cache_policies` (
24	`policy_name` VARCHAR(40) PRIMARY KEY,
25	`get_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
26	 NOT NULL ,
27	`set_policy` ENUM('innodb_only', 'cache_only','caching','disabled')
28	 NOT NULL ,
29	`delete_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
30	 NOT NULL,
31	`flush_policy` ENUM('innodb_only', 'cache_only', 'caching','disabled')
32	 NOT NULL
33) ENGINE = innodb;
34
35
36-- ------------------------------------------------------------------------
37-- Table `containers`
38--
39-- A container record describes an InnoDB table used for data storage by
40-- InnoDB Memcache.
41-- There must be a unique index on the `key column`, and unique index name
42-- is specified in the `unique_idx_name_on_key` column of the table
43-- `value_columns` are comma-separated lists of the columns that make up
44-- the memcache key and value. Each column width is defined such that they
45-- are in consistent with NDB memcached.
46-- ------------------------------------------------------------------------
47
48CREATE  TABLE IF NOT EXISTS `containers` (
49	`name` varchar(50) not null primary key,
50	`db_schema` VARCHAR(250) NOT NULL,
51	`db_table` VARCHAR(250) NOT NULL,
52	`key_columns` VARCHAR(250) NOT NULL,
53	`value_columns` VARCHAR(250),
54	`flags` VARCHAR(250) NOT NULL DEFAULT "0",
55	`cas_column` VARCHAR(250),
56	`expire_time_column` VARCHAR(250),
57	`unique_idx_name_on_key` VARCHAR(250) NOT NULL
58) ENGINE = InnoDB;
59
60CREATE  TABLE IF NOT EXISTS `config_options` (
61	`name` varchar(50) not null primary key,
62	`value` varchar(50)) ENGINE = InnoDB;
63
64-- ------------------------------------------------------------------------
65-- This is an example
66-- We create a InnoDB table `demo_test` is the `test` database
67-- and insert an entry into contrainers' table to tell InnoDB Memcache
68-- that we has such InnoDB table as back store:
69-- c1 -> key
70-- c2 -> value
71-- c3 -> flags
72-- c4 -> cas
73-- c5 -> exp time
74-- PRIMARY -> use primary key to search
75-- ------------------------------------------------------------------------
76
77INSERT INTO containers VALUES ("aaa", "test", "demo_test",
78			       "c1", "c2",  "c3", "c4", "c5", "PRIMARY");
79
80INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
81				  "innodb_only", "innodb_only", "innodb_only");
82
83INSERT INTO config_options VALUES("separator", "|");
84INSERT INTO config_options VALUES("table_map_delimiter", ".");
85
86USE test
87
88-- ------------------------------------------------------------------------
89-- Key (c1) must be VARCHAR or CHAR type, memcached supports key up to 255
90-- Bytes
91-- Value (c2) must be VARCHAR or CHAR type
92-- Flag (c3) is a 32 bits integer
93-- CAS (c4) is a 64 bits integer, per memcached define
94-- Exp (c5) is again a 32 bits integer
95-- ------------------------------------------------------------------------
96CREATE TABLE demo_test (c1 VARCHAR(32),
97			c2 VARCHAR(1024),
98			c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
99ENGINE = INNODB;
100
101INSERT INTO demo_test VALUES ("AA", "HELLO, HELLO", 8, 0, 0);
102