db_mysql.sql

事件提醒系统

drop database if exists cal;
create database cal;

grant all on cal.*  to calendar IDENTIFIED by 'calendar';

use cal;

set names GBK;

drop table if exists cal_events;
drop table if exists cal_users;
drop table if exists cal_types;

#用户表
create table sys_user(
	userid int auto_increment,
	username varchar(20) not null,
	password varchar(20) not null,
	popedom smallint not null,
	constraint pri_userid primary key(userid)
);

insert into sys_user values('10001','tomo','123',1);
insert into sys_user values('10002','jena','123',1);

#事件类别表
create table cal_types(
	typeid int auto_increment,
	userid int not null,
	isglobal int default '1',    #1为全局类别
	eventype varchar(16) not null,
	constraint pri_typeid primary key(typeid)
);

insert into cal_types values('100001','10000',1,'公司会议');
insert into cal_types values('100002','10001',0,'客户会见');
insert into cal_types values('100003','10002',0,'活动');


#事件表
create table cal_events(
	eventid int auto_increment,
	eventname varchar(30) not null,
	eventdescribe varchar(200),
	userid int not null,
	typeid int not null,
	createtime datetime not null,
	occurtime datetime not null,
	early_remindtime int,
	space_remindtime int,
	constraint pri_eventid primary key(eventid),
	constraint for_userid foreign key(userid) references sys_user(userid),
	constraint for_typeid foreign key(typeid) references cal_types(typeid)	
);

insert into cal_events values('10001','公司开会','一般重要','10001','100001','2009-2-24 10:15:00','2009-2-24 12:30:00',60,10);
insert into cal_events values('10002','公司访问','一般重要','10001','100002','2009-2-24 10:12:00','2009-2-24 12:30:00',60,20);
insert into cal_events values('10003','公司聚餐','一般重要','10001','100003','2009-2-24 19:10:00','2009-2-24 12:30:00',30,10);
insert into cal_events values('10004','客户访问','一般重要','10002','100001','2009-2-24 10:10:00','2009-2-24 12:30:00',120,20);
insert into cal_events values('10005','上级检查','一般重要','10001','100001',now(),'2009-03-24 12:30:00',120,30);
insert into cal_events values('10006','户外活动','一般重要','10001','100001',now(),'2009-03-05 12:30:00',120,30);
insert into cal_events values('10007','朋友聚餐','一般重要','10001','100001',now(),'2009-03-24 12:30:00',120,30);
insert into cal_events values('10008','朋友生日','一般重要','10001','100001',now(),'2009-03-20 12:30:00',120,30);
insert into cal_events values('10009','家庭事','一般重要','10001','100001',now(),'2009-03-12 01:30:00',120,30);
insert into cal_events values('10010','比赛','一般重要','10001','100001',now(),'2009-03-12 01:03:00',120,30);
insert into cal_events values('10011','测试比赛','一般重要','10001','100001',now(),'2009-03-12 10:06:00',120,30);
insert into cal_events values('10012','回家','一般重要','10001','100001',now(),'2009-03-12 09:45:00',120,30);