You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
206 lines
18 KiB
206 lines
18 KiB
USE [master]
|
|
GO
|
|
/****** Object: Database [odb_generic] Script Date: 2016/11/14 下午 11:43:05 ******/
|
|
CREATE DATABASE [odb_generic]
|
|
CONTAINMENT = NONE
|
|
ON PRIMARY
|
|
( NAME = N'odb_generic', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.HERCULESMS2014\MSSQL\DATA\odb_generic.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
|
|
LOG ON
|
|
( NAME = N'odb_generic_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.HERCULESMS2014\MSSQL\DATA\odb_generic_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET COMPATIBILITY_LEVEL = 120
|
|
GO
|
|
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
|
|
begin
|
|
EXEC [odb_generic].[dbo].[sp_fulltext_database] @action = 'enable'
|
|
end
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET ANSI_NULL_DEFAULT OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET ANSI_NULLS OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET ANSI_PADDING OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET ANSI_WARNINGS OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET ARITHABORT OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET AUTO_CLOSE OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET AUTO_SHRINK OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET AUTO_UPDATE_STATISTICS ON
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET CURSOR_CLOSE_ON_COMMIT OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET CURSOR_DEFAULT GLOBAL
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET CONCAT_NULL_YIELDS_NULL OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET NUMERIC_ROUNDABORT OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET QUOTED_IDENTIFIER OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET RECURSIVE_TRIGGERS OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET DISABLE_BROKER
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET DATE_CORRELATION_OPTIMIZATION OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET TRUSTWORTHY OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET ALLOW_SNAPSHOT_ISOLATION OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET PARAMETERIZATION SIMPLE
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET READ_COMMITTED_SNAPSHOT OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET HONOR_BROKER_PRIORITY OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET RECOVERY SIMPLE
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET MULTI_USER
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET PAGE_VERIFY CHECKSUM
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET DB_CHAINING OFF
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET TARGET_RECOVERY_TIME = 0 SECONDS
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET DELAYED_DURABILITY = DISABLED
|
|
GO
|
|
USE [odb_generic]
|
|
GO
|
|
/****** Object: User [ta] Script Date: 2016/11/14 下午 11:43:05 ******/
|
|
CREATE USER [ta] FOR LOGIN [ta] WITH DEFAULT_SCHEMA=[dbo]
|
|
GO
|
|
ALTER ROLE [db_owner] ADD MEMBER [ta]
|
|
GO
|
|
ALTER ROLE [db_datareader] ADD MEMBER [ta]
|
|
GO
|
|
ALTER ROLE [db_datawriter] ADD MEMBER [ta]
|
|
GO
|
|
/****** Object: Table [dbo].[otb_role] Script Date: 2016/11/14 下午 11:43:05 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
SET ANSI_PADDING ON
|
|
GO
|
|
CREATE TABLE [dbo].[otb_role](
|
|
[guid] [char](36) NOT NULL CONSTRAINT [DF_tb_role_guid] DEFAULT (newid()),
|
|
[role_id] [varchar](16) NULL,
|
|
[active_flag] [char](1) NOT NULL CONSTRAINT [DF_tb_role_active_flag] DEFAULT ('Y'),
|
|
[create_user_guid] [char](36) NULL,
|
|
[create_date] [datetime] NOT NULL CONSTRAINT [DF_tb_role_create_date] DEFAULT (getdate()),
|
|
[modify_user_guid] [char](36) NULL,
|
|
[modify_date] [datetime] NOT NULL CONSTRAINT [DF_tb_role_modify_date] DEFAULT (getdate()),
|
|
CONSTRAINT [PK_tb_role] PRIMARY KEY CLUSTERED
|
|
(
|
|
[guid] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
GO
|
|
SET ANSI_PADDING OFF
|
|
GO
|
|
/****** Object: Table [dbo].[otb_session] Script Date: 2016/11/14 下午 11:43:05 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
SET ANSI_PADDING ON
|
|
GO
|
|
CREATE TABLE [dbo].[otb_session](
|
|
[guid] [char](36) NOT NULL CONSTRAINT [DF_otb_session_guid] DEFAULT (newid()),
|
|
[active_flag] [char](1) NOT NULL CONSTRAINT [DF_otb_session_active_flag] DEFAULT ('Y'),
|
|
[create_user_guid] [char](36) NULL,
|
|
[create_date] [datetime] NOT NULL CONSTRAINT [DF_otb_session_create_date] DEFAULT (getdate()),
|
|
[modify_user_guid] [char](36) NULL,
|
|
[modify_date] [datetime] NOT NULL CONSTRAINT [DF_otb_session_modify_date] DEFAULT (getdate()),
|
|
CONSTRAINT [PK_otb_session] PRIMARY KEY CLUSTERED
|
|
(
|
|
[guid] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
GO
|
|
SET ANSI_PADDING OFF
|
|
GO
|
|
/****** Object: Table [dbo].[otb_user] Script Date: 2016/11/14 下午 11:43:05 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
SET ANSI_PADDING ON
|
|
GO
|
|
CREATE TABLE [dbo].[otb_user](
|
|
[guid] [char](36) NOT NULL CONSTRAINT [DF_tb_user_guid] DEFAULT (newid()),
|
|
[user_id] [nvarchar](16) NOT NULL,
|
|
[role_guid] [char](36) NULL,
|
|
[user_name] [nvarchar](32) NULL,
|
|
[email] [varchar](64) NULL,
|
|
[tel] [varchar](20) NULL,
|
|
[mobile] [varchar](15) NULL,
|
|
[active_flag] [char](1) NOT NULL CONSTRAINT [DF_tb_user_active_flag] DEFAULT ('Y'),
|
|
[create_user_guid] [char](36) NULL,
|
|
[create_date] [datetime] NOT NULL CONSTRAINT [DF_tb_user_create_date] DEFAULT (getdate()),
|
|
[modify_user_guid] [char](36) NULL,
|
|
[modify_date] [datetime] NOT NULL CONSTRAINT [DF_tb_user_modify_date] DEFAULT (getdate()),
|
|
CONSTRAINT [PK_tb_user] PRIMARY KEY CLUSTERED
|
|
(
|
|
[guid] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY]
|
|
|
|
GO
|
|
SET ANSI_PADDING OFF
|
|
GO
|
|
INSERT [dbo].[otb_role] ([guid], [role_id], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'F7B925A1-54B9-4E28-B585-AA4347B49747', N'Admin', N'Y', NULL, CAST(N'2016-08-22 23:42:17.853' AS DateTime), NULL, CAST(N'2016-08-22 23:42:17.853' AS DateTime))
|
|
INSERT [dbo].[otb_role] ([guid], [role_id], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'FBF6C416-E973-4174-A3AD-299C13AE311C', N'User', N'Y', NULL, CAST(N'2016-08-22 23:42:44.833' AS DateTime), NULL, CAST(N'2016-08-22 23:42:44.833' AS DateTime))
|
|
INSERT [dbo].[otb_session] ([guid], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'e718ad5a-ba58-405a-9cc0-815f5807ab54', N'Y', N'D19126A3-CABC-4CD6-BA73-3766D8C51222', CAST(N'2016-11-14 23:35:28.737' AS DateTime), N'D19126A3-CABC-4CD6-BA73-3766D8C51222', CAST(N'2016-11-14 23:35:28.737' AS DateTime))
|
|
INSERT [dbo].[otb_user] ([guid], [user_id], [role_guid], [user_name], [email], [tel], [mobile], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'287DD4C7-D445-493A-8E33-46217B639F41', N'Member1', N'FBF6C416-E973-4174-A3AD-299C13AE311C', N'Merber 1', NULL, NULL, NULL, N'Y', N'D19126A3-CABC-4CD6-BA73-3766D8C51222', CAST(N'2016-08-22 23:47:44.807' AS DateTime), N'D19126A3-CABC-4CD6-BA73-3766D8C51222', CAST(N'2016-08-22 23:47:44.807' AS DateTime))
|
|
INSERT [dbo].[otb_user] ([guid], [user_id], [role_guid], [user_name], [email], [tel], [mobile], [active_flag], [create_user_guid], [create_date], [modify_user_guid], [modify_date]) VALUES (N'D19126A3-CABC-4CD6-BA73-3766D8C51222', N'Hercules', N'F7B925A1-54B9-4E28-B585-AA4347B49747', N'Hercules Chen', NULL, NULL, NULL, N'Y', NULL, CAST(N'2016-08-22 23:46:30.030' AS DateTime), NULL, CAST(N'2016-08-22 23:46:30.030' AS DateTime))
|
|
ALTER TABLE [dbo].[otb_role] WITH CHECK ADD CONSTRAINT [FK_tb_role_tb_user_create_user] FOREIGN KEY([create_user_guid])
|
|
REFERENCES [dbo].[otb_user] ([guid])
|
|
GO
|
|
ALTER TABLE [dbo].[otb_role] CHECK CONSTRAINT [FK_tb_role_tb_user_create_user]
|
|
GO
|
|
ALTER TABLE [dbo].[otb_role] WITH CHECK ADD CONSTRAINT [FK_tb_role_tb_user_modify_user] FOREIGN KEY([modify_user_guid])
|
|
REFERENCES [dbo].[otb_user] ([guid])
|
|
GO
|
|
ALTER TABLE [dbo].[otb_role] CHECK CONSTRAINT [FK_tb_role_tb_user_modify_user]
|
|
GO
|
|
ALTER TABLE [dbo].[otb_session] WITH CHECK ADD CONSTRAINT [FK_otb_session_tb_user_create_user] FOREIGN KEY([create_user_guid])
|
|
REFERENCES [dbo].[otb_user] ([guid])
|
|
GO
|
|
ALTER TABLE [dbo].[otb_session] CHECK CONSTRAINT [FK_otb_session_tb_user_create_user]
|
|
GO
|
|
ALTER TABLE [dbo].[otb_session] WITH CHECK ADD CONSTRAINT [FK_otb_session_tb_user_modify_user] FOREIGN KEY([modify_user_guid])
|
|
REFERENCES [dbo].[otb_user] ([guid])
|
|
GO
|
|
ALTER TABLE [dbo].[otb_session] CHECK CONSTRAINT [FK_otb_session_tb_user_modify_user]
|
|
GO
|
|
ALTER TABLE [dbo].[otb_user] WITH CHECK ADD CONSTRAINT [FK_tb_user_tb_role] FOREIGN KEY([role_guid])
|
|
REFERENCES [dbo].[otb_role] ([guid])
|
|
GO
|
|
ALTER TABLE [dbo].[otb_user] CHECK CONSTRAINT [FK_tb_user_tb_role]
|
|
GO
|
|
ALTER TABLE [dbo].[otb_user] WITH CHECK ADD CONSTRAINT [FK_tb_user_tb_user_create_user] FOREIGN KEY([create_user_guid])
|
|
REFERENCES [dbo].[otb_user] ([guid])
|
|
GO
|
|
ALTER TABLE [dbo].[otb_user] CHECK CONSTRAINT [FK_tb_user_tb_user_create_user]
|
|
GO
|
|
ALTER TABLE [dbo].[otb_user] WITH CHECK ADD CONSTRAINT [FK_tb_user_tb_user_modify_user] FOREIGN KEY([modify_user_guid])
|
|
REFERENCES [dbo].[otb_user] ([guid])
|
|
GO
|
|
ALTER TABLE [dbo].[otb_user] CHECK CONSTRAINT [FK_tb_user_tb_user_modify_user]
|
|
GO
|
|
USE [master]
|
|
GO
|
|
ALTER DATABASE [odb_generic] SET READ_WRITE
|
|
GO
|