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