Skip to content

C
Compass
Switch branch/tag
Find file
BlameHistoryPermalink
compass_db_schema.sql 14.6 KB
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 
##----system_constant_dat create

DROP TABLE IF EXISTS system_constant_dat;
CREATE TABLE IF NOT EXISTS system_constant_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	name varchar(64) NOT NULL,
	title varchar(128) NOT NULL,
	constant_value text NOT NULL,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX system_constant_dat_name_idx ON system_constant_dat(name);


##----government_mst create

DROP TABLE IF EXISTS government_mst;
CREATE TABLE IF NOT EXISTS government_mst(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	province varchar(64) NOT NULL,
	city varchar(64),
	district varchar(64),
	title text NOT NULL,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX government_mst_province_idx ON government_mst(province);
Create INDEX government_mst_city_idx ON government_mst(city);
Create INDEX government_mst_district_idx ON government_mst(district);


##----government_qr_dat create

DROP TABLE IF EXISTS government_qr_dat;
CREATE TABLE IF NOT EXISTS government_qr_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	government_id int8 NOT NULL,
	province varchar(64) NOT NULL,
	city varchar(64),
	district varchar(64),
	max_count int8 NOT NULL DEFAULT '0',
	limit_date timestamp NOT NULL,
	title text NOT NULL,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX government_qr_dat_province_idx ON government_qr_dat(province);
Create INDEX government_qr_dat_city_idx ON government_qr_dat(city);
Create INDEX government_qr_dat_district_idx ON government_qr_dat(district);


##----account_mst create

DROP TABLE IF EXISTS account_mst;
CREATE TABLE IF NOT EXISTS account_mst(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	login text NOT NULL,
	password text NOT NULL,
	name varchar(64) NOT NULL,
	contact text,
	role varchar(128) NOT NULL,
	user_id int8 NOT NULL DEFAULT '0',
	school_id int8 NOT NULL DEFAULT '0',
	government_id int8 NOT NULL DEFAULT '0',
	modules text NOT NULL,
	comment text,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX account_mst_login_idx ON account_mst(login(255));
Create INDEX account_mst_password_idx ON account_mst(password(255));
Create INDEX account_mst_name_idx ON account_mst(name);


##----school_mst create

DROP TABLE IF EXISTS school_mst;
CREATE TABLE IF NOT EXISTS school_mst(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	school_no text NOT NULL,
	original_source int8 NOT NULL DEFAULT '0',
	title text NOT NULL,
	school_type varchar(64) NOT NULL,
	front_image varchar(64) NOT NULL,
	comment text NOT NULL,
	address text NOT NULL,
	province varchar(64),
	city varchar(64),
	district varchar(64),
	street text,
	longitude text NOT NULL,
	latitude text NOT NULL DEFAULT 'UNKNOW',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX school_mst_id_idx ON school_mst(id);
Create INDEX school_mst_title_idx ON school_mst(title(255));
Create INDEX school_mst_school_type_idx ON school_mst(school_type);


##----grade_mst create

DROP TABLE IF EXISTS grade_mst;
CREATE TABLE IF NOT EXISTS grade_mst(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	school_id int8 NOT NULL,
	school_no text NOT NULL,
	original_source int8 NOT NULL DEFAULT '0',
	title text NOT NULL,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX grade_mst_school_id_idx ON grade_mst(school_id);


##----class_mst create

DROP TABLE IF EXISTS class_mst;
CREATE TABLE IF NOT EXISTS class_mst(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	school_id int8 NOT NULL,
	school_no text NOT NULL,
	class_no text NOT NULL,
	original_source int8 NOT NULL DEFAULT '0',
	grade_id int8 NOT NULL,
	title text NOT NULL,
	member_count int8 NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX class_mst_school_id_idx ON class_mst(school_id);
Create INDEX class_mst_grade_id_idx ON class_mst(grade_id);
Create INDEX class_mst_class_no_idx ON class_mst(class_no(255));


##----circle_dat create

DROP TABLE IF EXISTS circle_dat;
CREATE TABLE IF NOT EXISTS circle_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	title text NOT NULL,
	member_count int8 NOT NULL DEFAULT '0',
	need_check tinyint(1) NOT NULL DEFAULT '0',
	comment text NOT NULL,
	longitude text NOT NULL,
	latitude text NOT NULL DEFAULT 'UNKNOW',
	address text NOT NULL,
	owner_id int8 NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;



##----circle_member_dat create

DROP TABLE IF EXISTS circle_member_dat;
CREATE TABLE IF NOT EXISTS circle_member_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	circle_id int8 NOT NULL DEFAULT '0',
	user_id int8 NOT NULL DEFAULT '0',
	member_count int8 NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;



##----user_mst create

DROP TABLE IF EXISTS user_mst;
CREATE TABLE IF NOT EXISTS user_mst(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	school_no text NOT NULL DEFAULT '0',
	class_no text NOT NULL DEFAULT '0',
	original_source int8 NOT NULL DEFAULT '0',
	openid varchar(64) NOT NULL DEFAULT 'UNKNOW',
	unionid varchar(64) NOT NULL DEFAULT 'UNKNOW',
	name varchar(64),
	mobile varchar(32),
	account_id int8 NOT NULL DEFAULT '0',
	child_age int8 NOT NULL DEFAULT '1',
	role varchar(128) NOT NULL DEFAULT '0',
	longitude text NOT NULL,
	latitude text NOT NULL DEFAULT 'UNKNOW',
	organization_submit_date timestamp,
	organization_no varchar(255),
	organization_title text,
	legal_person varchar(255),
	organization_contact text,
	legal_person_imgage1 varchar(255),
	legal_person_imgage2 varchar(255),
	licensen_imgage varchar(255),
	other_imgage varchar(255),
	organization_status varchar(64) NOT NULL DEFAULT 'NEW',
	ability_point int8 NOT NULL DEFAULT '0',
	service_point int8 NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;



##----social_event_dat create

DROP TABLE IF EXISTS social_event_dat;
CREATE TABLE IF NOT EXISTS social_event_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	school_no text NOT NULL DEFAULT '0',
	class_no text NOT NULL DEFAULT '0',
	original_source int8 NOT NULL DEFAULT '0',
	title text NOT NULL,
	author text NOT NULL,
	publish_time timestamp NOT NULL,
	content text NOT NULL,
	scope int8 NOT NULL,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX social_event_dat_school_no_idx ON social_event_dat(school_no(255));
Create INDEX social_event_dat_class_no_idx ON social_event_dat(class_no(255));
Create INDEX social_event_dat_original_source_idx ON social_event_dat(original_source);


##----social_event_member_dat create

DROP TABLE IF EXISTS social_event_member_dat;
CREATE TABLE IF NOT EXISTS social_event_member_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	social_event_id int8 NOT NULL,
	user_id text NOT NULL,
	comment text NOT NULL,
	images text NOT NULL,
	status varchar(64) NOT NULL DEFAULT '0',
	point int8 NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX social_event_member_dat_user_id_idx ON social_event_member_dat(user_id(255));


##----family_event_dat create

DROP TABLE IF EXISTS family_event_dat;
CREATE TABLE IF NOT EXISTS family_event_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	school_no text NOT NULL DEFAULT '0',
	class_no text NOT NULL DEFAULT '0',
	original_source int8 NOT NULL DEFAULT '0',
	title text NOT NULL,
	author text NOT NULL,
	publish_time timestamp NOT NULL,
	content text NOT NULL,
	scope int8 NOT NULL,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX family_event_dat_school_no_idx ON family_event_dat(school_no(255));
Create INDEX family_event_dat_class_no_idx ON family_event_dat(class_no(255));
Create INDEX family_event_dat_original_source_idx ON family_event_dat(original_source);


##----family_event_member_dat create

DROP TABLE IF EXISTS family_event_member_dat;
CREATE TABLE IF NOT EXISTS family_event_member_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	family_event_id int8 NOT NULL,
	user_id text NOT NULL,
	comment text NOT NULL,
	images text NOT NULL,
	status varchar(64) NOT NULL DEFAULT '0',
	point int8 NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX family_event_member_dat_user_id_idx ON family_event_member_dat(user_id(255));


##----volunteer_event_dat create

DROP TABLE IF EXISTS volunteer_event_dat;
CREATE TABLE IF NOT EXISTS volunteer_event_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	title text NOT NULL,
	start_time timestamp NOT NULL,
	finish_time timestamp NOT NULL,
	position text NOT NULL,
	position_longitude text NOT NULL,
	position_latitude text NOT NULL,
	scope int8 NOT NULL,
	max_member int8 NOT NULL DEFAULT '0',
	time_length varchar(64) NOT NULL,
	leader_name varchar(64) NOT NULL,
	leader_contact varchar(128) NOT NULL,
	venue text NOT NULL,
	status varchar(64) NOT NULL,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;



##----volunteer_event_member_dat create

DROP TABLE IF EXISTS volunteer_event_member_dat;
CREATE TABLE IF NOT EXISTS volunteer_event_member_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	volunteer_event_id int8 NOT NULL,
	user_id text NOT NULL,
	comment text NOT NULL,
	images text NOT NULL,
	position text NOT NULL,
	position_longitude text NOT NULL,
	position_latitude text NOT NULL,
	status varchar(64) NOT NULL DEFAULT 'NEW',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX volunteer_event_member_dat_user_id_idx ON volunteer_event_member_dat(user_id(255));


##----user_certificate_dat create

DROP TABLE IF EXISTS user_certificate_dat;
CREATE TABLE IF NOT EXISTS user_certificate_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	user_id text NOT NULL,
	certificate_id text NOT NULL,
	name varchar(64) NOT NULL,
	mobile varchar(32) NOT NULL,
	address varchar(128) NOT NULL,
	status varchar(64) NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX user_certificate_dat_user_id_idx ON user_certificate_dat(user_id(255));
Create INDEX user_certificate_dat_certificate_id_idx ON user_certificate_dat(certificate_id(255));


##----certificate_mst create

DROP TABLE IF EXISTS certificate_mst;
CREATE TABLE IF NOT EXISTS certificate_mst(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	title varchar(128) NOT NULL,
	front_image varchar(64) NOT NULL,
	point text,
	status varchar(64) NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;



##----user_point_log create

DROP TABLE IF EXISTS user_point_log;
CREATE TABLE IF NOT EXISTS user_point_log(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	user_id int8 NOT NULL PRIMARY KEY auto_increment,
	action_type int4 NOT NULL,
	point int4 NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX user_point_log_user_id_idx ON user_point_log(user_id);


##----course_media_dat create

DROP TABLE IF EXISTS course_media_dat;
CREATE TABLE IF NOT EXISTS course_media_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	course_id int8 NOT NULL DEFAULT '0',
	title varchar(128) NOT NULL,
	tags text NOT NULL,
	front_image varchar(64) NOT NULL,
	media text NOT NULL,
	teacher varchar(128),
	is_free tinyint(1) NOT NULL DEFAULT '0',
	price float(5,2) NOT NULL DEFAULT '0',
	view_count int8 NOT NULL DEFAULT '0',
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX course_media_dat_course_id_idx ON course_media_dat(course_id);


##----media_tag_dat create

DROP TABLE IF EXISTS media_tag_dat;
CREATE TABLE IF NOT EXISTS media_tag_dat(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	tag varchar(128) NOT NULL,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX media_tag_dat_tag_idx ON media_tag_dat(tag);


##----course_mst create

DROP TABLE IF EXISTS course_mst;
CREATE TABLE IF NOT EXISTS course_mst(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	title varchar(128) NOT NULL,
	front_image varchar(64) NOT NULL,
	teacher_profile text,
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;



##----user_point_log create

DROP TABLE IF EXISTS user_point_log;
CREATE TABLE IF NOT EXISTS user_point_log(
	id bigint unsigned NOT NULL PRIMARY KEY auto_increment,
	registration_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	user_id int8 NOT NULL,
	media_id int8 NOT NULL,
	is_free tinyint(1) NOT NULL DEFAULT '0',
	money float(5,2) NOT NULL DEFAULT '0',
	order_no varchar(255),
	mchid_order_no varchar(255),
	status varchar(64),
	delete_flg tinyint(1) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;

Create INDEX user_point_log_user_id_idx ON user_point_log(user_id);