setup.sql

SGIPClient_DELPHI.rar

/****** Object:  Database sgip    Script Date: 2004-11-20 15:44:39 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'sgip')
	DROP DATABASE [sgip]
GO

CREATE DATABASE [sgip]  ON (NAME = N'sgip_Data', FILENAME = N'C:\sgip_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'sgip_Log', FILENAME = N'C:\sgip_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO

exec sp_dboption N'sgip', N'autoclose', N'false'
GO

exec sp_dboption N'sgip', N'bulkcopy', N'false'
GO

exec sp_dboption N'sgip', N'trunc. log', N'false'
GO

exec sp_dboption N'sgip', N'torn page detection', N'true'
GO

exec sp_dboption N'sgip', N'read only', N'false'
GO

exec sp_dboption N'sgip', N'dbo use', N'false'
GO

exec sp_dboption N'sgip', N'single', N'false'
GO

exec sp_dboption N'sgip', N'autoshrink', N'false'
GO

exec sp_dboption N'sgip', N'ANSI null default', N'false'
GO

exec sp_dboption N'sgip', N'recursive triggers', N'false'
GO

exec sp_dboption N'sgip', N'ANSI nulls', N'false'
GO

exec sp_dboption N'sgip', N'concat null yields null', N'false'
GO

exec sp_dboption N'sgip', N'cursor close on commit', N'false'
GO

exec sp_dboption N'sgip', N'default to local cursor', N'false'
GO

exec sp_dboption N'sgip', N'quoted identifier', N'false'
GO

exec sp_dboption N'sgip', N'ANSI warnings', N'false'
GO

exec sp_dboption N'sgip', N'auto create statistics', N'true'
GO

exec sp_dboption N'sgip', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'sgip', N'db chaining', N'false'
GO

use [sgip]
GO

/****** Object:  Table [dbo].[RECEIVED]    Script Date: 2004-11-20 15:44:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RECEIVED]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RECEIVED]
GO

/****** Object:  Table [dbo].[RECEIVEWAIT]    Script Date: 2004-11-20 15:44:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RECEIVEWAIT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RECEIVEWAIT]
GO

/****** Object:  Table [dbo].[REPORTED]    Script Date: 2004-11-20 15:44:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[REPORTED]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REPORTED]
GO

/****** Object:  Table [dbo].[REPORTWAIT]    Script Date: 2004-11-20 15:44:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[REPORTWAIT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REPORTWAIT]
GO

/****** Object:  Table [dbo].[SENDED]    Script Date: 2004-11-20 15:44:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SENDED]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SENDED]
GO

/****** Object:  Table [dbo].[SENDWAIT]    Script Date: 2004-11-20 15:44:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SENDWAIT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SENDWAIT]
GO

/****** Object:  Login sgipuser    Script Date: 2004-11-20 15:44:40 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'sgipuser')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'sgip', @loginlang = N'简体中文'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'sgipuser', null, @logindb, @loginlang
END
GO

/****** Object:  User dbo    Script Date: 2004-11-20 15:44:40 ******/
/****** Object:  User sgipuser    Script Date: 2004-11-20 15:44:40 ******/
if not exists (select * from dbo.sysusers where name = N'sgipuser' and uid < 16382)
	EXEC sp_grantdbaccess N'sgipuser', N'sgipuser'
GO

/****** Object:  User sgipuser    Script Date: 2004-11-20 15:44:40 ******/
exec sp_addrolemember N'db_owner', N'sgipuser'
GO

/****** Object:  Table [dbo].[RECEIVED]    Script Date: 2004-11-20 15:44:41 ******/
CREATE TABLE [dbo].[RECEIVED] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[SpCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[UserCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[MsgId] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[ReceiveTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SendTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Message] [varchar] (140) COLLATE Chinese_PRC_CI_AS NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SmsFlag] [smallint] NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[RECEIVEWAIT]    Script Date: 2004-11-20 15:44:41 ******/
CREATE TABLE [dbo].[RECEIVEWAIT] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[MsgId] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[UserCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[TP_pid] [int] NULL ,
	[TP_udhi] [int] NULL ,
	[MsgFmt] [int] NULL ,
	[MsgContent] [varchar] (140) COLLATE Chinese_PRC_CI_AS NULL ,
	[SmsFlag] [smallint] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[REPORTED]    Script Date: 2004-11-20 15:44:42 ******/
CREATE TABLE [dbo].[REPORTED] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[UserCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[MsgID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Sub] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Dlvrd] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Submittime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Donetime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Stat] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[Err] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[REPORTWAIT]    Script Date: 2004-11-20 15:44:42 ******/
CREATE TABLE [dbo].[REPORTWAIT] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[DestTermID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[MsgID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[ReportType] [tinyint] NULL ,
	[Stat] [tinyint] NULL ,
	[Err] [tinyint] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[SENDED]    Script Date: 2004-11-20 15:44:42 ******/
CREATE TABLE [dbo].[SENDED] (
	[id] [int] NOT NULL ,
	[UserCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[MsgId] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
	[SendTime] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Schedule] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Expire] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[Message] [varchar] (140) COLLATE Chinese_PRC_CI_AS NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[ServiceId] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[NeedReport] [tinyint] NULL ,
	[Priority] [smallint] NULL ,
	[FeeType] [tinyint] NULL ,
	[FeeValue] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
	[GivenValue] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
	[SourceName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[SourceId] [int] NULL ,
	[SmsFlag] [smallint] NULL ,
	[LastTime] [datetime] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[SENDWAIT]    Script Date: 2004-11-20 15:44:42 ******/
CREATE TABLE [dbo].[SENDWAIT] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[UserCode] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[SendTime] [datetime] NULL ,
	[Schedule] [datetime] NULL ,
	[Expire] [datetime] NULL ,
	[Message] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL ,
	[Mobile] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[ServiceId] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[NeedReport] [tinyint] NULL ,
	[Priority] [smallint] NULL ,
	[FeeType] [tinyint] NULL ,
	[FeeValue] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
	[GivenValue] [varchar] (6) COLLATE Chinese_PRC_CI_AS NULL ,
	[SourceName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[SourceId] [int] NULL ,
	[SmsFlag] [smallint] NULL ,
	[Retry] [smallint] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[RECEIVED] WITH NOCHECK ADD 
	CONSTRAINT [PK_received] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[RECEIVEWAIT] WITH NOCHECK ADD 
	CONSTRAINT [PK_receivewait] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[REPORTED] WITH NOCHECK ADD 
	CONSTRAINT [PK_reported] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[REPORTWAIT] WITH NOCHECK ADD 
	CONSTRAINT [PK_reportwait] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[SENDWAIT] WITH NOCHECK ADD 
	CONSTRAINT [PK_sendwait] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[RECEIVED] ADD 
	CONSTRAINT [DF_received_SmsFlag] DEFAULT (0) FOR [SmsFlag]
GO

ALTER TABLE [dbo].[RECEIVEWAIT] ADD 
	CONSTRAINT [DF_receivewait_SmsFlag] DEFAULT (0) FOR [SmsFlag]
GO

ALTER TABLE [dbo].[SENDED] ADD 
	CONSTRAINT [DF_sended_SmsFlag] DEFAULT (0) FOR [SmsFlag],
	CONSTRAINT [DF_SENDED_LastTime] DEFAULT (getdate()) FOR [LastTime]
GO

ALTER TABLE [dbo].[SENDWAIT] ADD 
	CONSTRAINT [DF_SENDWAIT_UserCode] DEFAULT (880011) FOR [UserCode],
	CONSTRAINT [DF_SENDWAIT_SendTime] DEFAULT (getdate()) FOR [SendTime],
	CONSTRAINT [DF_SENDWAIT_Message] DEFAULT ('hello') FOR [Message],
	CONSTRAINT [DF_SENDWAIT_Mobile] DEFAULT (13851771993) FOR [Mobile],
	CONSTRAINT [DF_sendwait_ServiceId] DEFAULT ('Test') FOR [ServiceId],
	CONSTRAINT [DF_SENDWAIT_NeedReport] DEFAULT (1) FOR [NeedReport],
	CONSTRAINT [DF_sendwait_Priority] DEFAULT (0) FOR [Priority],
	CONSTRAINT [DF_sendwait_FeeType] DEFAULT ('01') FOR [FeeType],
	CONSTRAINT [DF_sendwait_FeeCode] DEFAULT ('00000') FOR [FeeValue],
	CONSTRAINT [DF_SENDWAIT_GivenValue] DEFAULT ('00000') FOR [GivenValue],
	CONSTRAINT [DF_SENDWAIT_SourceName] DEFAULT ('SPID') FOR [SourceName],
	CONSTRAINT [DF_SENDWAIT_SourceId] DEFAULT (0) FOR [SourceId],
	CONSTRAINT [DF_sendwait_SmsFlag] DEFAULT (0) FOR [SmsFlag],
	CONSTRAINT [DF_sendwait_Retry] DEFAULT (0) FOR [Retry]
GO