· 7 years ago · Feb 14, 2019, 04:36 AM
1# VIRTUE CARD DATA DEFINITION LANGUAGE
2
3USE vc;
4
5
6########################
7# Drop tables
8########################
9
10DROP TABLE IF EXISTS vc;
11
12
13DROP TABLE IF EXISTS discussion;
14DROP TABLE IF EXISTS disc_group_member;
15DROP TABLE IF EXISTS disc_group;
16DROP TABLE IF EXISTS journal;
17DROP TABLE IF EXISTS user_card;
18DROP TABLE IF EXISTS card_content_back;
19DROP TABLE IF EXISTS card_reflection;
20DROP TABLE IF EXISTS custom_card;
21DROP TABLE IF EXISTS card;
22DROP TABLE IF EXISTS friend;
23DROP TABLE IF EXISTS comment;
24DROP TABLE IF EXISTS kudos;
25DROP TABLE IF EXISTS highlight;
26DROP TABLE IF EXISTS user_setting;
27DROP TABLE IF EXISTS user;
28DROP TABLE IF EXISTS lu_card_type;
29DROP TABLE IF EXISTS lu_gender;
30DROP TABLE IF EXISTS lu_group_type;
31DROP TABLE IF EXISTS lu_status_card;
32DROP TABLE IF EXISTS lu_status_user;
33
34########################
35#
36# LOOKUP TABLES
37#
38########################
39
40# lu_card_type
41CREATE TABLE lu_card_type (
42 code CHAR(3) NOT NULL,
43 description VARCHAR(255) NOT NULL,
44 PRIMARY KEY (code)
45) ENGINE=InnoDB DEFAULT CHARSET=latin1;
46
47INSERT INTO lu_card_type(code, description) VALUES ('EDU', 'Education');
48INSERT INTO lu_card_type(code, description) VALUES ('FAM', 'Family');
49INSERT INTO lu_card_type(code, description) VALUES ('REF', 'Reflection');
50INSERT INTO lu_card_type(code, description) VALUES ('SUN', 'Sunset');
51
52
53# lu_gender
54CREATE TABLE lu_gender (
55 code CHAR(3) NOT NULL,
56 description VARCHAR(255) NOT NULL,
57 sort_order TINYINT(255) UNSIGNED,
58 PRIMARY KEY (code)
59) ENGINE=InnoDB DEFAULT CHARSET=latin1;
60
61INSERT INTO lu_gender(code, description, sort_order) VALUES ('MAL', 'Male', 1);
62INSERT INTO lu_gender(code, description, sort_order) VALUES ('FEM', 'Female', 2);
63INSERT INTO lu_gender(code, description, sort_order) VALUES ('UNK', 'Unknown', 3);
64
65
66# lu_group_type
67CREATE TABLE lu_group_type (
68 code CHAR(3) NOT NULL,
69 description VARCHAR(255) NOT NULL,
70 PRIMARY KEY (code)
71) ENGINE=InnoDB DEFAULT CHARSET=latin1;
72
73INSERT INTO lu_group_type(code, description) VALUES ('PRI', 'Private');
74INSERT INTO lu_group_type(code, description) VALUES ('GRO', 'Group');
75INSERT INTO lu_group_type(code, description) VALUES ('PUB', 'Public');
76
77# lu_status_card
78CREATE TABLE lu_status_card (
79 code CHAR(3) NOT NULL,
80 description VARCHAR(255) NOT NULL,
81 PRIMARY KEY (code)
82) ENGINE=InnoDB DEFAULT CHARSET=latin1;
83
84INSERT INTO lu_status_card(code, description) VALUES ('ACT', 'Active');
85INSERT INTO lu_status_card(code, description) VALUES ('INA', 'Inactive');
86
87
88# lu_status_user
89CREATE TABLE lu_status_user (
90 code CHAR(3) NOT NULL,
91 description VARCHAR(255) NOT NULL,
92 sort_order TINYINT(255) UNSIGNED,
93 PRIMARY KEY (code)
94) ENGINE=InnoDB DEFAULT CHARSET=latin1;
95
96INSERT INTO lu_status_user(code, description, sort_order) VALUES ('ACT', 'Active',1);
97INSERT INTO lu_status_user(code, description, sort_order) VALUES ('INA', 'Inactive',2);
98INSERT INTO lu_status_user(code, description, sort_order) VALUES ('DEL', 'Deleted',3);
99INSERT INTO lu_status_user(code, description, sort_order) VALUES ('SUS', 'Suspended',4);
100INSERT INTO lu_status_user(code, description, sort_order) VALUES ('INV', 'Invited',5);
101INSERT INTO lu_status_user(code, description, sort_order) VALUES ('NEW', 'New',6);
102
103
104
105########################
106#
107# MAIN TABLES
108#
109########################
110
111# user
112CREATE TABLE user (
113 user_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
114 email VARCHAR(255) NULL DEFAULT NULL, # email address (erick.delacruz@mph-intl.com)
115 user_name VARCHAR(100) NULL DEFAULT NULL, # username (erick)
116 status CHAR(3) NULL DEFAULT NULL, # ACT (Active), INA (Inactive), SUS (Suspended), DEL (Deleted), INV (Invited)
117 password VARCHAR(150) NULL DEFAULT NULL, # password (combination of characters, numbers and special characters)
118 password_salt VARCHAR(10) DEFAULT '', # password salt
119 first_name VARCHAR(255) NOT NULL, # first name
120 last_name VARCHAR(255) NOT NULL, # last name
121 gender CHAR(3) NOT NULL, # MAL (Male), FEM (FEMALE)
122 birthday DATE NULL DEFAULT NULL, # birthday
123 phone_no VARCHAR(20) NOT NULL, # (555) 555-1234
124 media_profile VARCHAR(255) NULL DEFAULT NULL, # media profile path
125 media_cover VARCHAR(255) NULL DEFAULT NULL, # media cover path
126 last_login INT(10) UNSIGNED NOT NULL DEFAULT '0', # unix timestamp last login
127 last_activity INT(10) UNSIGNED NOT NULL DEFAULT '0', # unix timestamp last activity
128 who_added INT(10) UNSIGNED DEFAULT NULL, # id of who added
129 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
130 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
131 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
132 PRIMARY KEY (user_id),
133 CONSTRAINT user_fk_01 FOREIGN KEY (status) REFERENCES lu_status_user(code),
134 CONSTRAINT user_fk_02 FOREIGN KEY (gender) REFERENCES lu_gender(code),
135 UNIQUE KEY user_uq_01 (email),
136 UNIQUE KEY user_uq_02 (user_name)
137) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
138
139-- INSERT INTO user(email, user_name, status, password) VALUES ('jon@mph-intl.com', 'jonindick', 'ACT', 'test123');,
140INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
141 VALUES('jon@mph-intl.com', 'jonindick', 'ACT', 'test123', 'Jonathan', 'Indick', 'MAL', '1979-04-10', '+939743456549', '0', '0', UNIX_TIMESTAMP());
142
143INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
144 VALUES('denver.pelesco@mph-intl.com', 'dens', 'ACT', 'test123', 'Denver', 'Pelesco', 'MAL', '1985-05-05', '+939743456549', '0', '0', UNIX_TIMESTAMP());
145
146INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
147 VALUES('kenneth.vargas@mph-intl.com', 'kenneth026', 'ACT', 'test123', 'Kenneth', 'Vargas', 'MAL', '1999-11-26', '+939452454579', '0', '0', UNIX_TIMESTAMP());
148
149INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
150 VALUES('mario.speedwagon@yahoo.com', 'MarioSpeedwagon', 'ACT', 'test123', 'Mario', 'Speedwagon', 'MAL', '1969-08-10', '+939185647232', '0', '0', UNIX_TIMESTAMP());
151
152INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
153 VALUES('PeteyCruiser@gmail.com', 'PeteyCruiser', 'ACT', 'test123', 'Petty', 'Cruiser', 'FEM', '1985-05-30', '+93985674125', '0', '0', UNIX_TIMESTAMP());
154
155INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
156 VALUES('PaulMolive@gmail.com', 'PaulMolive', 'ACT', 'test123', 'Paul', 'Molive', 'MAL' ,'1970-11-10', '+939265647242', '0', '0', UNIX_TIMESTAMP());
157
158INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
159 VALUES('AnnaSthesia@gmail.com', 'AnnaSthesia', 'ACT', 'test123', 'Anna', 'Sthesia', 'FEM', '1986-12-30', '+93985874125', '0', '0', UNIX_TIMESTAMP());
160
161INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
162 VALUES('GailForcewind@gmail.com', 'GailForcewind', 'ACT', 'test123', 'Gail', 'Forcewind', 'MAL', '1987-11-08', '+939265547247', '0', '0', UNIX_TIMESTAMP());
163
164INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
165 VALUES('AnnaMull@gmail.com', 'AnnaMull', 'ACT', 'test123', 'Anna', 'Mull', 'FEM', '1985-01-30', '+93987874126', '0', '0', UNIX_TIMESTAMP());
166
167INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
168 VALUES('PaigeTurner@gmail.com', 'PaigeTurner', 'ACT', 'test123', 'Paige', 'Turner', 'MAL', '1969-06-23', '+939268757248', '0', '0', UNIX_TIMESTAMP());
169-- 10
170INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
171 VALUES('BobFrapples@yahoo.com', 'BobFrapples', 'ACT', 'test123', 'Bob', 'Frapples', 'MAL', '1974-04-04', '+939878741261', '0', '0', UNIX_TIMESTAMP());
172
173INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
174 VALUES('EmeryDejulio@yahoo.com', 'EmeryDejulio', 'ACT', 'test123', 'Emery ', 'Dejulio', 'MAL', '1975-03-11', '+939878774601', '0', '0', UNIX_TIMESTAMP());
175
176INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
177 VALUES('KarlyGowins@yahoo.com', 'KarlyGowins', 'ACT', 'test123', 'Karly', 'Gowins', 'FEM', '1976-05-02', '+939148741261', '0', '0', UNIX_TIMESTAMP());
178
179INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
180 VALUES('EugeneNatoli@yahoo.com', 'EugeneNatoli', 'ACT', 'test123', 'Eugene ', 'Natoli', 'MAL', '1977-06-20', '+939878774502', '0', '0', UNIX_TIMESTAMP());
181
182INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
183 VALUES('NannieRothwell@yahoo.com', 'NannieRothwell', 'ACT', 'test123', 'Nannie', 'Rothwell', 'FEM', '1978-06-20', '+939128777530', '0', '0', UNIX_TIMESTAMP());
184
185INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
186 VALUES('SharynNordahl@yahoo.com', 'SharynNordahl', 'ACT', 'test123', 'Sharyn', 'Nordahl', 'FEM', '1979-07-20', '+939128884405', '0', '0', UNIX_TIMESTAMP());
187
188INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
189 VALUES('MichealThornsberry@yahoo.com', 'MarybethCarvajal', 'ACT', 'test123', 'Micheal', 'Thornsberry', 'MAL', '1972-06-20', '+939128775234', '0', '0', UNIX_TIMESTAMP());
190
191INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
192 VALUES('ClorindaCobler@yahoo.com', 'ClorindaCobler', 'ACT', 'test123', 'Clorinda', 'Cobler', 'FEM', '1986-08-11', '+939127882864', '0', '0', UNIX_TIMESTAMP());
193
194INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
195 VALUES('TheodoraRobles@yahoo.com', 'TheodoraRobles', 'ACT', 'test123', 'Theodora', 'Robles', 'MAL', '1995-10-20', '+939128779854', '0', '0', UNIX_TIMESTAMP());
196
197INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
198 VALUES('RoderickMclean@yahoo.com', 'RoderickMclean', 'ACT', 'test123', 'Roderick', 'Mclean', 'MAL', '1994-09-14', '+939127581456', '0', '0', UNIX_TIMESTAMP());
199-- 20
200INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
201 VALUES('MonikaAugustus@yahoo.com', 'MonikaAugustus', 'ACT', 'test123', 'Monika', 'Augustus', 'FEM', '1993-10-21', '+939128771122', '0', '0', UNIX_TIMESTAMP());
202
203INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
204 VALUES('LasandraRainwater@yahoo.com', 'LasandraRainwater', 'ACT', 'test123', 'Lasandra', 'Rainwater', 'FEM', '1992-09-25', '+939127583578', '0', '0', UNIX_TIMESTAMP());
205
206INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
207 VALUES('AnnamariaAlmaguers@yahoo.com', 'AnnamariaAlmaguers', 'ACT', 'test123', 'Annamaria', 'Almaguers', 'FEM', '1985-10-14', '+939128778574', '0', '0', UNIX_TIMESTAMP());
208
209INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
210 VALUES('MarinaLangston@yahoo.com', 'MarinaLangston', 'ACT', 'test123', 'Marina', 'Langston', 'FEM', '1970-01-14', '+639124512361', '0', '0', UNIX_TIMESTAMP());
211
212INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
213 VALUES('JuanitaMowers@yahoo.com', 'JuanitaMowers', 'ACT', 'test123', 'Juanita', 'Mowers', 'FEM', '1971-11-21', '+939128797818', '0', '0', UNIX_TIMESTAMP());
214
215INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
216 VALUES('RosendoTolan@yahoo.com', 'RosendoTolan', 'ACT', 'test123', 'Rosendo', 'Tolan', 'MAL', '1972-12-25', '+939127582365', '0', '0', UNIX_TIMESTAMP());
217
218INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
219 VALUES('KirbyTremper@yahoo.com', 'KirbyTremper', 'ACT', 'test123', 'Kirby', 'Tremper', 'MAL', '1973-10-09', '+939125772414', '0', '0', UNIX_TIMESTAMP());
220
221INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
222 VALUES('ChinWalton@yahoo.com', 'ChinWalton', 'ACT', 'test123', 'Chin', 'Walton', 'FEM', '1974-01-23', '+939124581514', '0', '0', UNIX_TIMESTAMP());
223
224INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
225 VALUES('AllynAcevedo@yahoo.com', 'AllynAcevedo', 'ACT', 'test123', 'Allyn', 'Acevedo', 'FEM', '1975-10-09', '+939125778844', '0', '0', UNIX_TIMESTAMP());
226
227INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
228 VALUES('NoelleBrabham@yahoo.com', 'NoelleBrabham', 'ACT', 'test123', 'Noelle', 'Brabham', 'FEM', '1976-01-23', '+939124585858', '0', '0', UNIX_TIMESTAMP());
229-- 30
230INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
231 VALUES('LawanaYoakum@gmail.com', 'LawanaYoakum', 'ACT', 'test123', 'Lawana', 'Yoakum', 'FEM', '1977-10-09', '+939125771987', '0', '0', UNIX_TIMESTAMP());
232
233INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
234 VALUES('DerrickSpagnoli@gmail.com', 'DerrickSpagnoli', 'ACT', 'test123', 'Derrick', 'Spagnoli', 'MAL', '1978-01-23', '+939124581122', '0', '0', UNIX_TIMESTAMP());
235
236INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
237 VALUES('GayleneSlifer@gmail.com', 'GayleneSlifer', 'ACT', 'test123', 'Gaylene', 'Slifer', 'FEM', '1979-10-09', '+939125774526', '0', '0', UNIX_TIMESTAMP());
238
239INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
240 VALUES('DustinMerwin@gmail.com', 'DustinMerwin', 'ACT', 'test123', 'Dustin', 'Merwin', 'MAL', '1980-01-23', '+939124580826', '0', '0', UNIX_TIMESTAMP());
241
242INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
243 VALUES('JuleneMcmullen@gmail.com', 'JuleneMcmullen', 'ACT', 'test123', 'Julene', 'Mcmullen', 'FEM', '1981-10-09', '+939125771547', '0', '0', UNIX_TIMESTAMP());
244
245INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
246 VALUES('HildredTrimble@gmail.com', 'HildredTrimble', 'ACT', 'test123', 'Hildred', 'Trimble', 'FEM', '1982-01-23', '+939124587856', '0', '0', UNIX_TIMESTAMP());
247
248INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
249 VALUES('DawnRhein@gmail.com', 'DawnRhein', 'ACT', 'test123', 'Dawn', 'Rhein', 'FEM', '1983-10-09', '+939125777854', '0', '0', UNIX_TIMESTAMP());
250
251INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
252 VALUES('ElliottChaloux@gmail.com', 'ElliottChaloux', 'ACT', 'test123', 'Elliott', 'Chaloux', 'MAL', '1985-01-23', '+939124586566', '0', '0', UNIX_TIMESTAMP());
253
254INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
255 VALUES('FosterRoca@gmail.com', 'FosterRoca', 'ACT', 'test123', 'Foster', 'Roca', 'MAL', '1985-01-23', '+93912459654', '0', '0', UNIX_TIMESTAMP());
256
257INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
258 VALUES('LarryLoo@gmail.com', 'LarryLoo', 'ACT', 'test123', 'Larry', 'Loo', 'MAL', '1986-01-23', '+939124587859', '0', '0', UNIX_TIMESTAMP());
259-- 40
260INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
261 VALUES('JenellCurren@gmail.com', 'JenellCurren', 'ACT', 'test123', 'Jenell', 'Curren', 'FEM', '1987-01-23', '+939124583568', '0', '0', UNIX_TIMESTAMP());
262
263INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
264 VALUES('VenettaVise@gmail.com', 'VenettaVise', 'ACT', 'test123', 'Venetta', 'Vise', 'FEM', '1988-01-23', '+939124586597', '0', '0', UNIX_TIMESTAMP());
265
266INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
267 VALUES('PattieHoulihan@gmail.com', 'PattieHoulihan', 'ACT', 'test123', 'Pattie', 'Houlihan', 'FEM', '1989-01-23', '+939124588523', '0', '0', UNIX_TIMESTAMP());
268
269INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
270 VALUES('ShandiGuffey@gmail.com', 'ShandiGuffey', 'ACT', 'test123', 'Shandi', 'Guffey', 'FEM', '1990-01-23', '+939124581457', '0', '0', UNIX_TIMESTAMP());
271
272INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
273 VALUES('JestineVolkert@gmail.com', 'JestineVolkert', 'ACT', 'test123', 'Jestine', 'Volkert', 'FEM', '1991-01-23', '+939124581486', '0', '0', UNIX_TIMESTAMP());
274
275INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
276 VALUES('NicolMesa@gmail.com', 'NicolMesa', 'ACT', 'test123', 'Nicol', 'Mesa', 'FEM', '1992-01-23', '+939124581425', '0', '0', UNIX_TIMESTAMP());
277
278INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
279 VALUES('LoryThetford@gmail.com', 'LoryThetford', 'ACT', 'test123', 'Lory', 'Thetford', 'FEM', '1993-01-23', '+939124581169', '0', '0', UNIX_TIMESTAMP());
280
281INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
282 VALUES('ChinGerling@gmail.com', 'ChinGerling', 'ACT', 'test123', 'Chin', 'Gerling', 'FEM', '1994-01-23', '+939124582369', '0', '0', UNIX_TIMESTAMP());
283
284INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
285 VALUES('UlaYeary@gmail.com', 'UlaYeary', 'ACT', 'test123', 'Ula', 'Yeary', 'FEM', '1995-01-23', '+939124581478', '0', '0', UNIX_TIMESTAMP());
286
287INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
288 VALUES('DorisTooker@gmail.com', 'DorisTooker', 'ACT', 'test123', 'Doris', 'Tooker', 'FEM', '1980-01-23', '+939124585894', '0', '0', UNIX_TIMESTAMP());
289-- 50
290INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
291 VALUES('IssacCoover@yahoo.com', 'IssacCoover', 'ACT', 'test123', 'Issac', 'Coover', 'MAL', '1981-01-23', '+939124581236', '0', '0', UNIX_TIMESTAMP());
292
293INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
294 VALUES('DaleDunnigan@yahoo.com', 'DaleDunnigan', 'ACT', 'test123', 'Dale', 'Dunnigan', 'MAL', '1982-01-23', '+939124584789', '0', '0', UNIX_TIMESTAMP());
295
296INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
297 VALUES('GenevaStuck@yahoo.com', 'GenevaStuck', 'ACT', 'test123', 'Geneva', 'Stuck', 'FEM', '1983-01-23', '+939124585236', '0', '0', UNIX_TIMESTAMP());
298
299INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
300 VALUES('DyGrimmett@yahoo.com', 'DyGrimmett', 'ACT', 'test123', 'Dy', 'Grimmett', 'FEM', '1984-01-23', '+939124585478', '0', '0', UNIX_TIMESTAMP());
301
302INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
303 VALUES('ShelbaRigdon@yahoo.com', 'ShelbaRigdon', 'ACT', 'test123', 'Shelba', 'Rigdon', 'FEM', '1985-01-23', '+939124581258', '0', '0', UNIX_TIMESTAMP());
304
305INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
306 VALUES('JohnathanMicek@gmail.com', 'JohnathanMicek', 'ACT', 'test123', 'Johnathan', 'Micek', 'MAL', '1985-01-23', '+939124581597', '0', '0', UNIX_TIMESTAMP());
307
308INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
309 VALUES('MohammadEthridge@gmail.com', 'MohammadEthridge', 'ACT', 'test123', 'Mohammad', 'Ethridge', 'MAL', '1985-01-23', '+939124587531', '0', '0', UNIX_TIMESTAMP());
310
311INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
312 VALUES('HarrisPiercy@gmail.com', 'HarrisPiercy', 'ACT', 'test123', 'Harris', 'Piercy', 'MAL', '1970-01-23', '+939124584682', '0', '0', UNIX_TIMESTAMP());
313
314INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
315 VALUES('MontyMiltenberger@gmail.com', 'MontyMiltenberger', 'ACT', 'test123', 'Monty', 'Miltenberger', 'MAL', '1985-01-23', '+939124588956', '0', '0', UNIX_TIMESTAMP());
316
317INSERT INTO user(email, user_name, status, password, first_name, last_name, gender, birthday, phone_no, last_login, last_activity, when_added)
318 VALUES('LoydGuiney@gmail.com', 'LoydGuiney', 'ACT', 'test123', 'Loyd', 'Guiney', 'MAL', '1972-01-23', '+939124584585', '0', '0', UNIX_TIMESTAMP());
319-- 60
320
321# card
322CREATE TABLE card (
323 card_id INT(10) UNSIGNED NOT NULL, # Primary key
324 card_type CHAR(3) NOT NULL, # card type
325 card_name VARCHAR(100) NULL DEFAULT NULL, # card name
326 status CHAR(3) NULL DEFAULT NULL, # ACT (Active), INA (Inactive), SUS (Suspended), DEL (Deleted), INV (Invited)
327 title VARCHAR(100) NOT NULL, # title
328 img_front VARCHAR(255) NULL DEFAULT NULL, # path to the image
329 img_front_bg VARCHAR(255) NULL DEFAULT NULL, # path to the image bg
330 img_feat VARCHAR(255) NULL DEFAULT NULL, # path to the image
331 img_feat_loc CHAR(3) NOT NULL, # TOP (Top), BOT (Bottom)
332 img_back VARCHAR(255) NULL DEFAULT NULL, # path to the image
333 img_back_bg VARCHAR(255) NULL DEFAULT NULL, # path to the image
334 description VARCHAR(1000) NOT NULL, # description
335 content_front VARCHAR(5000) NOT NULL, # content - front
336 content_back_title VARCHAR(100) NOT NULL, # content - back title
337 quote VARCHAR(1000) NOT NULL, # quote
338 quote_author VARCHAR(100) NULL, # quote author
339 PRIMARY KEY (card_id),
340 CONSTRAINT card_fk_01 FOREIGN KEY (status) REFERENCES lu_status_card(code),
341 CONSTRAINT card_fk_02 FOREIGN KEY (card_type) REFERENCES lu_card_type(code),
342 UNIQUE KEY card_uq_01 (card_type, card_name)
343) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
344
345-- FAMILY CARD
346
347-- EDUCATOR CARD
348
349-- REFLECTION
350-- 3001
351INSERT INTO card(card_id, card_type, card_name, status, title, img_front, img_front_bg, img_feat, img_feat_loc, img_back, img_back_bg, description, content_front, content_back_title, quote, quote_author)
352 VALUES (3001, 'REF', 'Acceptance', 'ACT', 'Acceptance', '','','','','','','Acceptance is embracing life on its own terms. We are open to what is, rather than wishing for something different. We face the truth in all circumstances with honesty and courage.',
353 'Acceptance is embracing life on its own terms. We are open to what is, rather than wishing for something different. We face the truth in all circumstances with honesty and courage. Acceptance helps us to bend without breaking in the winds of tests, to gather the lessons and step forward with new wisdom and awareness. We affirm others and ourselves for the qualities we do have and avoid judgment and criticism for what we don’t have. Accepting myself allows me to give what I have to the world.',
354
355 'The Practice of Acceptance',
356
357 'The greatest gift that you can give to others is the gift of unconditional love and acceptance.',
358 'Brian Tracey');
359-- 3002
360INSERT INTO card(card_id, card_type, card_name, status, title, img_front, img_front_bg, img_feat, img_feat_loc, img_back, img_back_bg, description, content_front, content_back_title, quote, quote_author)
361 VALUES (3002, 'REF', 'Accountability', 'ACT', 'Accountability', '','','','','','','Accountability is the willingness to stand in responsibility for every choice. It is acting with integrity, doing what we say we will do. When we make a mistake we do not seek to hide it or avoid it.',
362 'Accountability is the willingness to stand in responsibility for every choice. It is acting with integrity, doing what we say we will do. When we make a mistake we do not seek to hide it or avoid it. We have the courage to face it willingly. We are open to the lessons it can bring, and ready to make amends. With accountability, people can rely on us, because we are answerable for our actions. We appreciate both the good we have done and the good to be gleaned from the choices that do not turn out right. We are guided by our highest standards.',
363
364 'The Practice of Accountability',
365
366 'Always take 100% responsibility for any activity you’re involved in.',
367 'Gay Hendricks & Kate Ludeman, The Corporate Mystic');
368-- 3003
369INSERT INTO card(card_id, card_type, card_name, status, title, img_front, img_front_bg, img_feat, img_feat_loc, img_back, img_back_bg, description, content_front, content_back_title, quote, quote_author)
370 VALUES (3003, 'REF', 'Appreciation', 'ACT', 'Appreciation', '','','','','','','Appreciation is seeing the good in life. It is recognizing the gifts that come our way and showing gratitude for them. When we express appreciation to others, we mirror the virtues we see in them.',
371 'Appreciation is seeing the good in life. It is recognizing the gifts that come our way and showing gratitude for them. When we express appreciation to others, we mirror the virtues we see in them. We perceive their positive intentions and honor them with our gratitude. Love thrives on appreciation. Intimacy grows deeper when we know we are seen and valued. As we do the spiritual work life calls us to do, it is important to appreciate our own efforts and to acknowledge our progress. We don’t dwell on the unpleasant things of life. We value the learning even in the painful times. Drinking deeply of the beauty and pleasures life offers, we don’t waste a single day. ',
372
373 'The Practice of Appreciation',
374
375 'For what has been, thanks! For what shall be—yes!',
376 'Dag Hammerskjold');
377-- 3004
378INSERT INTO card(card_id, card_type, card_name, status, title, img_front, img_front_bg, img_feat, img_feat_loc, img_back, img_back_bg, description, content_front, content_back_title, quote, quote_author)
379 VALUES (3004, 'REF', 'Assertiveness', 'ACT', 'Assertiveness', '','','','','','','Assertiveness is speaking one’s truth with peaceful confidence. It is discerning the song we were given to sing and using our talents as a gift to the world. We have the courage to speak up for what we believe is right.',
380 'Assertiveness is speaking one’s truth with peaceful confidence. It is discerning the song we were given to sing and using our talents as a gift to the world. We have the courage to speak up for what we believe is right. We tell the truth about what is just. Assertiveness comes from knowing our own worth, and honoring the dignity of who we are. We stand on our own holy ground, and set boundaries without guilt. We never beg or make demands. We ask for what we need by making simple, positive requests. We treat ourselves with respect and expect respect at all times.',
381
382 'The Practice of Assertiveness',
383
384 'Dost thou reckon thyself a puny form, when within thee the universe is folded?',
385 'Imam Ali');
386-- 3005
387INSERT INTO card(card_id, card_type, card_name, status, title, img_front, img_front_bg, img_feat, img_feat_loc, img_back, img_back_bg, description, content_front, content_back_title, quote, quote_author)
388 VALUES (3005, 'REF', 'Awe', 'ACT', 'Awe', '','','','','','','Awe is deep respect and reverence for the Source of life. It is humility and wonder before the power of God. Contemplating life’s beauty and mystery leads us to communion.',
389 'Awe is deep respect and reverence for the Source of life. It is humility and wonder before the power of God. Contemplating life’s beauty and mystery leads us to communion. Reflecting on our place in the universe, we seem small and insignificant, yet it is awe-inspiring to realize that no one else on earth is just like us. Each of us has a unique potential for reflecting the Divine virtues in our souls. We awaken them by choosing to live them. When we practice awe, we are alert to the signs and wonders placed in our path. We open ourselves to Grace.',
390
391 'The Practice of Awe',
392
393 'The highest point a man can attain is not Knowledge, or Virtue, or Goodness, or Victory, but something even greater, more heroic and more despairing: Sacred Awe!',
394 'Nikos Kazantzakis');
395-- 3006
396
397
398-- SUNSET CARD
399
400
401-- FAMILY CARD
402-- INSERT INTO user(card_id, card_type, card_name, status) VALUES (1001, 'FAM', 'Acceptance', 'ACT');
403-- INSERT INTO user(card_id, card_type, card_name, status) VALUES (1002, 'FAM', 'Accountability', 'ACT');
404
405-- EDUCATOR CARD
406-- INSERT INTO user(card_id, card_type, card_name, status) VALUES (2001, 'EDU', 'Acceptance', 'ACT');
407-- INSERT INTO user(card_id, card_type, card_name, status) VALUES (2002, 'EDU', 'Accountability', 'ACT');
408
409-- REFLECTION CARD
410-- INSERT INTO user(card_id, card_type, card_name, status) VALUES (3001, 'REF', 'Acceptance', 'ACT');
411-- INSERT INTO user(card_id, card_type, card_name, status) VALUES (3002, 'REF', 'Accountability', 'ACT');
412
413-- SUNSET CARD
414-- INSERT INTO user(card_id, card_type, card_name, status) VALUES (4001, 'SUN', 'Acceptance', 'ACT');
415-- INSERT INTO user(card_id, card_type, card_name, status) VALUES (4002, 'SUN', 'Accountability', 'ACT');
416
417
418# card_content_back
419CREATE TABLE card_content_back (
420 card_content_back_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
421 card_id INT(10) UNSIGNED NOT NULL, # card id
422 content VARCHAR(255) NOT NULL, # content
423 PRIMARY KEY (card_content_back_id),
424 CONSTRAINT card_content_back_fk_01 FOREIGN KEY (card_id) REFERENCES card(card_id),
425 UNIQUE KEY card_content_back_uq_01 (card_id, content)
426) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
427
428-- Card content back 1001
429-- card_id, content_front
430
431-- FAMILY CARD
432-- INSERT INTO card_content_back(card_id, content) VALUES (1001, 'I receive my tests with humor and grace.');
433-- INSERT INTO card_content_back(card_id, content) VALUES (1001, 'I seek the truth in all I experience.');
434-- INSERT INTO card_content_back(card_id, content) VALUES (1001, 'and so on...');
435
436-- EDUCATOR CARD
437
438-- REFLECTION CARD
439-- 3001 Acceptance
440INSERT INTO card_content_back(card_id, content) VALUES (3001, 'I receive my tests with humor and grace.');
441INSERT INTO card_content_back(card_id, content) VALUES (3001, 'I seek the truth in all I experience.');
442INSERT INTO card_content_back(card_id, content) VALUES (3001, 'I accept the things I cannot change.');
443INSERT INTO card_content_back(card_id, content) VALUES (3001, 'I trust that there is some good in whatever happens.');
444INSERT INTO card_content_back(card_id, content) VALUES (3001, 'I accept my intimates as they are.');
445INSERT INTO card_content_back(card_id, content) VALUES (3001, 'I trust that I am a person of value.');
446-- 3002 Accountability
447INSERT INTO card_content_back(card_id, content) VALUES (3002, 'I take responsibility for my choices.');
448INSERT INTO card_content_back(card_id, content) VALUES (3002, 'I have the humility to face my mistakes.');
449INSERT INTO card_content_back(card_id, content) VALUES (3002, 'I am a lifelong learner.');
450INSERT INTO card_content_back(card_id, content) VALUES (3002, 'I make amends.');
451INSERT INTO card_content_back(card_id, content) VALUES (3002, 'I weigh both positive and negative outcomes.');
452INSERT INTO card_content_back(card_id, content) VALUES (3002, 'I live up to my sense of justice.');
453-- 3003 Appreciation
454INSERT INTO card_content_back(card_id, content) VALUES (3003, 'I look for the good in life and in people.');
455INSERT INTO card_content_back(card_id, content) VALUES (3003, 'I freely express gratitude and admiration.');
456INSERT INTO card_content_back(card_id, content) VALUES (3003, 'I honor my own progress.');
457INSERT INTO card_content_back(card_id, content) VALUES (3003, 'I value the gifts in my tests.');
458INSERT INTO card_content_back(card_id, content) VALUES (3003, 'I don’t allow myself to stay down and disappointed.');
459INSERT INTO card_content_back(card_id, content) VALUES (3003, 'I enjoy all that life has to offer.');
460INSERT INTO card_content_back(card_id, content) VALUES (3003, 'I feel grateful for the bounty of my life.');
461-- 3004 Assertiveness
462INSERT INTO card_content_back(card_id, content) VALUES (3004, 'I speak my truth with confidence.');
463INSERT INTO card_content_back(card_id, content) VALUES (3004, 'I value the gifts I have to give.');
464INSERT INTO card_content_back(card_id, content) VALUES (3004, 'I stand up for what I think is right.');
465INSERT INTO card_content_back(card_id, content) VALUES (3004, 'I honor my own worth.');
466INSERT INTO card_content_back(card_id, content) VALUES (3004, 'I set clear boundaries.');
467INSERT INTO card_content_back(card_id, content) VALUES (3004, 'I know I am worthy of respect.');
468-- 3005 Awe
469INSERT INTO card_content_back(card_id, content) VALUES (3005, 'I am humble before the power of the Creator.');
470INSERT INTO card_content_back(card_id, content) VALUES (3005, 'I take time to contemplate and commune.');
471INSERT INTO card_content_back(card_id, content) VALUES (3005, 'I am aware of the sacredness of life.');
472INSERT INTO card_content_back(card_id, content) VALUES (3005, 'I cultivate the Divine virtues in my nature.');
473INSERT INTO card_content_back(card_id, content) VALUES (3005, 'I discern the signs and synchronicities of life.');
474INSERT INTO card_content_back(card_id, content) VALUES (3005, 'I deepen my connection to Grace.');
475-- 3006
476
477-- SUNSET CARD
478
479# card_reflection
480CREATE TABLE card_reflection (
481 card_reflection_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
482 card_id INT(10) UNSIGNED NOT NULL, # card id
483 reflection VARCHAR(255) NOT NULL, # reflection
484 PRIMARY KEY (card_reflection_id),
485 CONSTRAINT card_reflection_fk_01 FOREIGN KEY (card_id) REFERENCES card(card_id),
486 UNIQUE KEY card_reflection_uq_01 (card_id, reflection)
487) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
488
489-- INSERT INTO card_reflection(card_id, reflection) VALUES (1001, 'I am thankful for the gift of Acceptance.');
490-- INSERT INTO card_reflection(card_id, reflection) VALUES (1001, 'It brings me serenity.');
491
492-- FAMILY CARD
493
494-- EDUCATOR CARD
495
496-- REFLECTION CARD
497-- 3001 Acceptance
498INSERT INTO card_reflection(card_id, reflection) VALUES (3001, 'I am thankful for the gift of Acceptance.');
499INSERT INTO card_reflection(card_id, reflection) VALUES (3001, 'It brings me serenity.');
500-- 3002 Accountability
501INSERT INTO card_reflection(card_id, reflection) VALUES (3002, 'I am thankful for the gift of Accountability.');
502INSERT INTO card_reflection(card_id, reflection) VALUES (3002, 'It is my moral high ground.');
503-- 3003 Appreciation
504INSERT INTO card_reflection(card_id, reflection) VALUES (3003, 'I am thankful for the gift of Appreciation.');
505INSERT INTO card_reflection(card_id, reflection) VALUES (3003, 'It fills my cup.');
506-- 3004 Assertiveness
507INSERT INTO card_reflection(card_id, reflection) VALUES (3004, 'I am thankful for the gift of Assertiveness.');
508INSERT INTO card_reflection(card_id, reflection) VALUES (3004, 'It is the guardian of my dignity.');
509-- 3005 Awe
510INSERT INTO card_reflection(card_id, reflection) VALUES (3005, 'I am thankful for the gift of Awe.');
511INSERT INTO card_reflection(card_id, reflection) VALUES (3005, 'It fills me with wonder.');
512
513
514
515-- SUNSET CARd
516
517
518
519# user_card
520CREATE TABLE user_card (
521 user_card_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
522 user_id INT(10) UNSIGNED NOT NULL, # user id
523 card_id INT(10) UNSIGNED NOT NULL, # card id
524 is_fave BOOLEAN NOT NULL DEFAULT 0, # is fave
525 is_read BOOLEAN NOT NULL DEFAULT 0, # is read
526 when_read INT(10) UNSIGNED NULL, # unix timestamp when read
527 who_added INT(10) UNSIGNED NOT NULL, # id of who added
528 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
529 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
530 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
531 PRIMARY KEY (user_card_id),
532 CONSTRAINT user_card_fk_01 FOREIGN KEY (user_id) REFERENCES user(user_id),
533 CONSTRAINT user_card_fk_02 FOREIGN KEY (card_id) REFERENCES card(card_id),
534 CONSTRAINT user_card_fk_88 FOREIGN KEY (who_added) REFERENCES user(user_id),
535 CONSTRAINT user_card_fk_89 FOREIGN KEY (who_updated) REFERENCES user(user_id),
536 UNIQUE KEY user_card_uq_01 (user_id, card_id)
537) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
538
539# custom_card
540CREATE TABLE custom_card (
541 custom_card_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
542 user_id INT(10) UNSIGNED NOT NULL, # user id
543 image VARCHAR(255) NOT NULL, # background image
544 content VARCHAR(5000) NOT NULL, # content
545 font_size INT(3) UNSIGNED NOT NULL, # font size
546 font_color CHAR(6) NOT NULL, # font color
547 when_read INT(10) UNSIGNED NULL, # unix timestamp when read
548 who_added INT(10) UNSIGNED NOT NULL, # id of who added
549 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
550 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
551 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
552 PRIMARY KEY (custom_card_id),
553 CONSTRAINT custom_card_fk_01 FOREIGN KEY (user_id) REFERENCES user(user_id),
554 CONSTRAINT custom_card_fk_88 FOREIGN KEY (who_added) REFERENCES user(user_id),
555 CONSTRAINT custom_card_fk_89 FOREIGN KEY (who_updated) REFERENCES user(user_id)
556) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
557
558
559# journal
560CREATE TABLE journal (
561 journal_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
562 user_id INT(10) UNSIGNED NOT NULL, # user id
563 card_id INT(10) UNSIGNED NOT NULL, # card id
564 journal_entry VARCHAR(5000) NOT NULL, # entry
565 image_feature VARCHAR(255) NOT NULL, # featured image
566 background_color CHAR(6) NULL, # html color
567 who_added INT(10) UNSIGNED NOT NULL, # id of who added
568 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
569 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
570 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
571 PRIMARY KEY (journal_id),
572 CONSTRAINT journal_fk_01 FOREIGN KEY (user_id) REFERENCES user(user_id),
573 CONSTRAINT journal_fk_02 FOREIGN KEY (card_id) REFERENCES card(card_id),
574 CONSTRAINT journal_fk_88 FOREIGN KEY (who_added) REFERENCES user(user_id),
575 CONSTRAINT journal_fk_89 FOREIGN KEY (who_updated) REFERENCES user(user_id),
576 UNIQUE KEY journal_uq_01 (user_id, card_id)
577) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
578
579# discgroup
580CREATE TABLE disc_group (
581 disc_group_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
582 card_id INT(10) UNSIGNED NOT NULL, # card id
583 group_name VARCHAR(255) NOT NULL , # group name
584 group_type CHAR(3) NULL, # group type 'PRI', 'GRO', 'PUB'
585 who_added INT(10) UNSIGNED NOT NULL, # id of who added
586 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
587 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
588 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
589 PRIMARY KEY (disc_group_id),
590 CONSTRAINT disc_group_fk_01 FOREIGN KEY (card_id) REFERENCES card(card_id),
591 CONSTRAINT disc_group_fk_02 FOREIGN KEY (group_type) REFERENCES lu_group_type(code),
592 CONSTRAINT disc_group_fk_88 FOREIGN KEY (who_added) REFERENCES user(user_id),
593 CONSTRAINT disc_group_fk_89 FOREIGN KEY (who_updated) REFERENCES user(user_id),
594 KEY disc_group_uq_01 (card_id, group_name)
595) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
596
597# disc_group_member
598CREATE TABLE disc_group_member (
599 disc_group_member_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
600 disc_group_id INT(10) UNSIGNED NOT NULL, # disc group id
601 user_id INT(10) UNSIGNED NOT NULL, # user id
602 who_added INT(10) UNSIGNED NOT NULL, # id of who added
603 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
604 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
605 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
606 PRIMARY KEY (disc_group_member_id),
607 CONSTRAINT disc_group_member_fk_01 FOREIGN KEY (disc_group_id) REFERENCES disc_group(disc_group_id),
608 CONSTRAINT disc_group_member_fk_02 FOREIGN KEY (user_id) REFERENCES user(user_id),
609 CONSTRAINT disc_group_member_fk_88 FOREIGN KEY (who_added) REFERENCES user(user_id),
610 CONSTRAINT disc_group_member_fk_89 FOREIGN KEY (who_updated) REFERENCES user(user_id),
611 UNIQUE KEY disc_group_member_uq_01 (disc_group_id, user_id)
612) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
613
614# discussion
615CREATE TABLE discussion (
616 discussion_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
617 disc_group_id INT(10) UNSIGNED NOT NULL, # disc group id
618 sender_user_id INT(10) UNSIGNED NOT NULL, # sender user id
619 message VARCHAR(5000) NOT NULL, # message
620 who_added INT(10) UNSIGNED NOT NULL, # id of who added
621 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
622 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
623 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
624 PRIMARY KEY (discussion_id),
625 CONSTRAINT discussion_fk_01 FOREIGN KEY (disc_group_id) REFERENCES disc_group(disc_group_id),
626 CONSTRAINT discussion_fk_02 FOREIGN KEY (sender_user_id) REFERENCES user(user_id),
627 CONSTRAINT discussion_fk_88 FOREIGN KEY (who_added) REFERENCES user(user_id),
628 CONSTRAINT discussion_fk_89 FOREIGN KEY (who_updated) REFERENCES user(user_id),
629 UNIQUE KEY discussion_uq_01 (disc_group_id, when_added)
630) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
631
632# friend
633CREATE TABLE friend (
634 friend_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
635 user_id INT(10) UNSIGNED NOT NULL, # user id
636 friend_user_id INT(10) UNSIGNED NOT NULL, # friend user id
637 status CHAR(3) NOT NULL, # status
638 who_added INT(10) UNSIGNED NOT NULL, # id of who added
639 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
640 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
641 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
642 PRIMARY KEY (friend_id),
643 CONSTRAINT friend_fk_01 FOREIGN KEY (user_id) REFERENCES user(user_id),
644 CONSTRAINT friend_fk_02 FOREIGN KEY (friend_user_id) REFERENCES user(user_id),
645 CONSTRAINT friend_fk_88 FOREIGN KEY (who_added) REFERENCES user(user_id),
646 CONSTRAINT friend_fk_89 FOREIGN KEY (who_updated) REFERENCES user(user_id),
647 UNIQUE KEY friend_uq_01 (user_id, friend_user_id)
648) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
649
650# user_setting
651CREATE TABLE user_setting (
652 user_id INT(10) UNSIGNED NOT NULL, # user id
653 auto_reset CHAR(3) NOT NULL, # ANN (Annually), SEM (Semi-Annually), QTR (Quarterly)
654 auto_reminder VARCHAR(3) NOT NULL, # ON (On), OFF (Off)
655 who_added INT(10) UNSIGNED NOT NULL, # id of who added
656 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
657 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
658 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
659 PRIMARY KEY (user_id),
660 CONSTRAINT user_setting_fk_01 FOREIGN KEY (user_id) REFERENCES user(user_id),
661 CONSTRAINT user_setting_fk_88 FOREIGN KEY (who_added) REFERENCES user(user_id),
662 CONSTRAINT user_setting_fk_89 FOREIGN KEY (who_updated) REFERENCES user(user_id)
663) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
664
665CREATE TABLE highlight (
666 highlight_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
667 user_id INT(10) UNSIGNED NOT NULL, # user id
668 card_id INT(10) UNSIGNED NOT NULL, # card id
669 content VARCHAR(500) NOT NULL, # highlighted texts
670 who_added INT(10) UNSIGNED NOT NULL, # id of who added
671 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
672 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
673 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
674 PRIMARY KEY (highlight_id),
675 CONSTRAINT highlight_fk_01 FOREIGN KEY (user_id) REFERENCES user(user_id)
676) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
677
678
679CREATE TABLE comment (
680 comment_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
681 highlight_id INT(10) UNSIGNED NOT NULL,
682 user_id INT(10) UNSIGNED NOT NULL,
683 comment_content VARCHAR(1000) NOT NULL,
684 who_added INT(10) UNSIGNED NOT NULL, # id of who added
685 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
686 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
687 when_updated INT(10) UNSIGNED DEFAULT NULL,
688 PRIMARY KEY (comment_id),
689 CONSTRAINT comment_fk_01 FOREIGN KEY (highlight_id) REFERENCES highlight(highlight_id),
690 CONSTRAINT comment_fk_02 FOREIGN KEY (user_id) REFERENCES user(user_id)
691) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
692
693CREATE TABLE kudos (
694 kudos_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
695 highlight_id INT(10) UNSIGNED NOT NULL,
696 user_id INT(10) UNSIGNED NOT NULL,
697 who_added INT(10) UNSIGNED NOT NULL, # id of who added
698 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
699 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
700 when_updated INT(10) UNSIGNED DEFAULT NULL,
701 PRIMARY KEY (kudos_id),
702 CONSTRAINT kudos_fk_01 FOREIGN KEY (highlight_id) REFERENCES highlight(highlight_id),
703 CONSTRAINT kudos_fk_02 FOREIGN KEY (user_id) REFERENCES user(user_id)
704) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
705
706/*
707# reminder
708CREATE TABLE reminder (
709 reminder_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, # Primary key
710 user_id INT(10) UNSIGNED NOT NULL, # user id
711 reminder_type CHAR(3) NOT NULL, # ONE (Once), REC (Recurring)
712 status CHAR(3) NOT NULL, # status
713 details VARCHAR(255) NOT NULL, # details
714 reminder_day CHAR(3) NULL, # details
715 reminder_datetime INT(10) UNSIGNED NOT NULL, # unix timestamp reminder
716 who_added INT(10) UNSIGNED NOT NULL, # id of who added
717 when_added INT(10) UNSIGNED NOT NULL, # unix timestamp when added
718 who_updated INT(10) UNSIGNED DEFAULT NULL, # id of who updated
719 when_updated INT(10) UNSIGNED DEFAULT NULL, # unix timestamp who added
720 PRIMARY KEY (friend_id),
721 CONSTRAINT friend_fk_01 FOREIGN KEY (user_id) REFERENCES user(user_id),
722 CONSTRAINT friend_fk_02 FOREIGN KEY (friend_user_id) REFERENCES user(user_id),
723 CONSTRAINT friend_fk_88 FOREIGN KEY (who_added) REFERENCES user(user_id),
724 CONSTRAINT friend_fk_89 FOREIGN KEY (who_updated) REFERENCES user(user_id),
725 UNIQUE KEY friend_uq_01 (user_id, friend_user_id)
726) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
727*/
728
729/*
730TIMELINE
731
732
733NOTIFICATION
734
735
736COMMENT
737*/