· 8 years ago · Jul 21, 2018, 07:08 PM
1drop table if exists user_tool_values;
2drop table if exists user_tools;
3
4drop table if exists tool_attribute_map;
5drop table if exists tool_attributes;
6drop table if exists tool_attribute_groups;
7
8-- tool_attribute_groups
9create table if not exists tool_attribute_groups (
10 id tinyint unsigned not null auto_increment primary key ,
11 name varchar(50) default null
12) engine=innodb charset=utf8 collate=utf8_general_ci;
13insert into tool_attribute_groups (id, name) values
14(1, 'css'),
15(2, 'columns'),
16(3, 'rake_back'),
17(4, 'prop'),
18(5, 'welcome_email'),
19(6, 'convert_request_email'),
20(7, 'xml_link');
21
22-- tool_attributes
23create table if not exists tool_attributes (
24 id tinyint unsigned not null auto_increment primary key ,
25 name varchar(50) default null
26) engine=innodb charset=utf8 collate=utf8_general_ci;
27insert into tool_attributes (id, name) values
28-- css
29(1, 'css'),
30-- columns, multiple values
31(2, 'columns'),
32-- site_ids, multiple values
33(3, 'site_ids'),
34-- email
35(4, 'from_address'),
36(5, 'reply_to_address'),
37(6, 'body'),
38-- link
39(7, 'href');
40
41
42-- tool_attribute_map
43create table if not exists tool_attribute_map (
44 tool_id tinyint unsigned default null ,
45 tool_attribute_group_id tinyint unsigned default null ,
46 tool_attribute_id tinyint unsigned default null ,
47 foreign key (tool_id ) references tools (id ) ,
48 foreign key (tool_attribute_group_id ) references tool_attribute_groups (id ) ,
49 foreign key (tool_attribute_id ) references tool_attributes (id )
50) engine=innodb charset=utf8 collate=utf8_general_ci;
51
52insert into tool_attribute_map (tool_id, tool_attribute_group_id, tool_attribute_id) values
53-- site_index
54(1, 1, 1), -- css
55(1, 2, 2), -- columns
56(1, 3, 3), -- rakeback offers
57(1, 4, 3), -- propping offers
58-- wizard
59(2, 1, 1), -- css
60(2, 5, 4), -- welcome_email from
61(2, 5, 5), -- welcome_email reply_to
62(2, 5, 6), -- welcome_email body
63-- quick_signup
64(3, 1, 1), -- css
65(3, 5, 4), -- welcome_email from
66(3, 5, 5), -- welcome_email reply_to
67(3, 5, 6), -- welcome_email body
68-- convert_account
69(4, 1, 1), -- css
70(4, 5, 4), -- welcome_email from
71(4, 5, 5), -- welcome_email reply_to
72(4, 5, 6), -- welcome_email body
73(4, 6, 4), -- convert_request_email from
74(4, 6, 5), -- convert_request_email reply_to
75(4, 6, 6), -- convert_request_email body
76-- member_area
77(5, 1, 1), -- css
78-- importer
79(6, 7, 7); -- xml href
80
81
82-- user_tools
83create table if not exists user_tools (
84 id int unsigned not null auto_increment primary key ,
85 user_id int unsigned default null ,
86 user_tag varchar(255) default null ,
87 tool_id tinyint unsigned default null ,
88 preset varchar(255) default null ,
89 created_at datetime default null ,
90 updated_at timestamp ,
91 index (user_tag, preset) ,
92 foreign key (user_id ) references users(id) ,
93 foreign key (tool_id ) references tools(id) ,
94 foreign key (user_tag ) references user_tags(tag) on update cascade
95) engine=innodb charset=utf8 collate=utf8_general_ci;
96
97 -- user_tools trigger
98 create trigger user_tools_before_insert
99 before insert on user_tools for each row set new.created_at = now();
100
101 -- import current data
102 insert into user_tools (id, user_id, user_tag, tool_id, preset, created_at, updated_at)
103 (select id, user_id, user_tag, tool_id, name, created_at, updated_at from css order by id);
104
105
106
107-- user_tool_values
108create table if not exists user_tool_values (
109 id int unsigned not null auto_increment primary key ,
110 user_tool_id int unsigned default null ,
111 tool_attribute_id tinyint unsigned default null ,
112 value text default null ,
113 created_at datetime default null ,
114 updated_at timestamp ,
115 index (user_tool_id, tool_attribute_id ) ,
116 foreign key (user_tool_id ) references user_tools (id ) ,
117 foreign key (tool_attribute_id ) references tool_attributes (id )
118) engine=innodb charset=utf8 collate=utf8_general_ci;
119
120 -- user_tool_values trigger
121 create trigger user_tool_values_before_insert
122 before insert on user_tool_values for each row set new.created_at = now();
123
124 -- import current data
125 insert into user_tool_values (user_tool_id, tool_attribute_id, value, created_at, updated_at)
126 (select id, 1, code, created_at, updated_at from css order by id);