create_table.sql

通过串口连接短信猫进行短信的群发、单发、接收；管理群发订单、用户账户等

drop table T_Orders cascade constraints;

drop table T_PhoneNumber cascade constraints;

drop table T_Users cascade constraints;

drop table T_SendLog cascade constraints;

drop table T_ReceivedLog cascade constraints;
/*定单表*/
create table T_Orders 
(
     OrderID int not null,
     OrderStart date not null, 
     OrderEnd  date  not null, 
     OrderContent  varchar(140) not null 
); 
alter table T_Orders
add constraint PK_T_Orders_OrderID primary key(OrderID);
/*--定单号码列表*/
create table T_PhoneNumber 
(
     phoNumID int not null,
     OrderID int not null, 
     phoNumber varchar(24) not null
); 
alter table T_PhoneNumber
add constraint PK_T_PhoneNumber_phoNumID primary key(phoNumID);

alter table T_PhoneNumber
add constraint FK_T_Orders_OrderID foreign key (OrderID) references T_Orders(OrderID);

/*--用户表*/
create table T_Users
(
     UserID int not null,
     UserName varchar(20) not null,
     UserPassword  varchar(12),
     UserAuthority	int,
     UserCreateTime date not null
); 
alter table T_Users
add constraint PK_Users_UserID primary key(UserID);

alter table T_Users   
add constraint CK_UserAuthority check(UserAuthority=0 or UserAuthority=1);

/*--发送日志*/
create table T_SendLog  
(
    SendID int not null,
    OrderID int null, 
    SendTime date not null,
    SendResult int not null, 
    SendNumber  varchar(24)	not null 
); 
alter table T_SendLog
add constraint PK_T_SendLog_SendID primary key(SendID);

alter table T_SendLog
add constraint FK_T_SendLog_OrderID foreign key (OrderID) references T_Orders(OrderID);

alter table T_SendLog 
add constraint CK_T_SendLog_SendResult check(SendResult=0 or SendResult=1);

/*--接收日志*/
create table T_ReceivedLog 
(
       RecID int not null,
       RecPeerNumber varchar(24)	not null,
       ReplyTime	date	not null,
       ReplyContent	varchar(140)	null 
); 
alter table T_ReceivedLog
add constraint PK_T_ReceivedLog_RecID primary key(RecID);

COMMIT;