数据库.sql

来自「中国电信短信SMGP3.0+ISMP网关」· SQL 代码 · 共 135 行

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cdma_Deliver]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cdma_Deliver]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cdma_IsmpOrderRelationUpdateNotify]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cdma_IsmpOrderRelationUpdateNotify]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cdma_Report]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cdma_Report]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cdma_Submit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cdma_Submit]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cdma_SubmitLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cdma_SubmitLog]
GO

CREATE TABLE [dbo].[cdma_Deliver] (
	[xid] [int] IDENTITY (1, 1) NOT NULL ,
	[Msgid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[ServiceId] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[AccessNo] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
	[Userphone] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
	[MsgContent] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[LinkId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SubmitMsgType] [tinyint] NULL ,
	[SPDealResult] [tinyint] NULL ,
	[gDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[cdma_IsmpOrderRelationUpdateNotify] (
	[xid] [int] IDENTITY (1, 1) NOT NULL ,
	[OPType] [int] NULL ,
	[packageID] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[productID] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[streamingNo] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[userID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[userIDType] [int] NULL ,
	[gDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[cdma_Report] (
	[xid] [int] IDENTITY (1, 1) NOT NULL ,
	[MsgId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[StatusFlag] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SubmitTime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[DoneTime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[gDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[cdma_Submit] (
	[xid] [int] IDENTITY (1, 1) NOT NULL ,
	[ServiceId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[FeeType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[FeeCode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[AccessNo] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
	[DestPhone] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
	[FeeUserType] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[FeePhone] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
	[MessageCoding] [int] NULL ,
	[MsgContent] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[LinkId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SubmitMsgType] [tinyint] NULL ,
	[SPDealResult] [tinyint] NULL ,
	[Registered_delivery] [bit] NULL ,
	[Priority] [int] NULL ,
	[SubmitTime] [datetime] NULL ,
	[gDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[cdma_SubmitLog] (
	[xid] [int] IDENTITY (1, 1) NOT NULL ,
	[MsgId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[Status] [int] NULL ,
	[ServiceId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[FeeType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[FeeCode] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[AccessNo] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
	[DestPhone] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
	[FeeUserType] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
	[FeePhone] [varchar] (25) COLLATE Chinese_PRC_CI_AS NULL ,
	[MessageCoding] [int] NULL ,
	[MsgContent] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
	[LinkId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[SubmitMsgType] [tinyint] NULL ,
	[SPDealResult] [tinyint] NULL ,
	[Registered_delivery] [bit] NULL ,
	[Priority] [int] NULL ,
	[SubmitTime] [datetime] NULL ,
	[gDate] [datetime] NULL 
) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [cdma_Submit1] ON [dbo].[cdma_Submit]([SubmitTime], [Priority] DESC ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[cdma_Deliver] ADD 
	CONSTRAINT [DF_cdma_Deliver_gDate] DEFAULT (getdate()) FOR [gDate]
GO

ALTER TABLE [dbo].[cdma_IsmpOrderRelationUpdateNotify] ADD 
	CONSTRAINT [DF_cdma_IsmpOrderRelationUpdateNotify_gDate] DEFAULT (getdate()) FOR [gDate]
GO

ALTER TABLE [dbo].[cdma_Report] ADD 
	CONSTRAINT [DF_cdma_Report_gDate] DEFAULT (getdate()) FOR [gDate]
GO

ALTER TABLE [dbo].[cdma_Submit] ADD 
	CONSTRAINT [DF_cdma_Submit_FeeUserType] DEFAULT (2) FOR [FeeUserType],
	CONSTRAINT [DF_cdma_Submit_MessageCoding] DEFAULT (15) FOR [MessageCoding],
	CONSTRAINT [DF_cdma_Submit_SubmitMsgType] DEFAULT (0) FOR [SubmitMsgType],
	CONSTRAINT [DF_cdma_Submit_SPDealResult] DEFAULT (0) FOR [SPDealResult],
	CONSTRAINT [DF_cdma_Submit_registered_delivery] DEFAULT (1) FOR [Registered_delivery],
	CONSTRAINT [DF_cdma_Submit_Priority] DEFAULT (3) FOR [Priority],
	CONSTRAINT [DF_cdma_Submit_SubmitTime] DEFAULT (getdate()) FOR [SubmitTime],
	CONSTRAINT [DF_cdma_Submit_gDate] DEFAULT (getdate()) FOR [gDate]
GO

 CREATE  INDEX [cdma_Submit2] ON [dbo].[cdma_Submit]([xid]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[cdma_SubmitLog] ADD 
	CONSTRAINT [DF_cdma_SubmitLog_SubmitTime] DEFAULT (getdate()) FOR [SubmitTime],
	CONSTRAINT [DF_cdma_SubmitLog_gDate] DEFAULT (getdate()) FOR [gDate]
GO