diff options
Diffstat (limited to 'projecttemplates/WebFormsRelyingParty/Admin/CreateDatabase.sql')
| -rw-r--r-- | projecttemplates/WebFormsRelyingParty/Admin/CreateDatabase.sql | 197 |
1 files changed, 0 insertions, 197 deletions
diff --git a/projecttemplates/WebFormsRelyingParty/Admin/CreateDatabase.sql b/projecttemplates/WebFormsRelyingParty/Admin/CreateDatabase.sql deleted file mode 100644 index 52ca669..0000000 --- a/projecttemplates/WebFormsRelyingParty/Admin/CreateDatabase.sql +++ /dev/null @@ -1,197 +0,0 @@ -SET ANSI_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO -CREATE TABLE [dbo].[Consumer]( - [ConsumerId] [int] IDENTITY(1,1) NOT NULL, - [ConsumerKey] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, - [ConsumerSecret] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [X509Certificate] [image] NULL, - [Callback] [nvarchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [VerificationCodeFormat] [int] NOT NULL, - [VerificationCodeLength] [int] NOT NULL, - [Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - CONSTRAINT [PK_Consumer] PRIMARY KEY CLUSTERED -( - [ConsumerId] ASC -)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] -) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] -GO -CREATE UNIQUE NONCLUSTERED INDEX [IX_Consumer] ON [dbo].[Consumer] -( - [ConsumerKey] ASC -)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] -GO -SET ANSI_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO -CREATE TABLE [dbo].[User]( - [Id] [int] IDENTITY(1,1) NOT NULL, - [FirstName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [EmailAddress] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [EmailAddressVerified] [bit] NOT NULL, - CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED -( - [Id] 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_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO -CREATE TABLE [dbo].[Role]( - [Id] [int] IDENTITY(1,1) NOT NULL, - [Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, - CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED -( - [Id] 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_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO -SET ANSI_PADDING ON -GO -CREATE TABLE [dbo].[IssuedToken]( - [TokenId] [int] IDENTITY(1,1) NOT NULL, - [ConsumerId] [int] NOT NULL, - [UserId] [int] NULL, - [Token] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, - [TokenSecret] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, - [CreatedOn] [datetime] NOT NULL, - [Callback] [nvarchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [VerificationCode] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [ConsumerVersion] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [ExpirationDate] [datetime] NULL, - [IsAccessToken] [bit] NOT NULL, - [Scope] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - CONSTRAINT [PK_IssuedToken] PRIMARY KEY CLUSTERED -( - [TokenId] 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 -CREATE UNIQUE NONCLUSTERED INDEX [IX_IssuedToken] ON [dbo].[IssuedToken] -( - [Token] ASC -)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] -GO -SET ANSI_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO -CREATE TABLE [dbo].[UserRole]( - [UserId] [int] NOT NULL, - [RoleId] [int] NOT NULL, - CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED -( - [UserId] ASC, - [RoleId] 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_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO -CREATE TABLE [dbo].[AuthenticationToken]( - [Id] [int] IDENTITY(1,1) NOT NULL, - [UserId] [int] NOT NULL, - [OpenIdClaimedIdentifier] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, - [OpenIdFriendlyIdentifier] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - CONSTRAINT [PK_AuthenticationToken] PRIMARY KEY CLUSTERED -( - [Id] 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_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO -CREATE PROCEDURE [dbo].[AddUser] - ( - @firstName nvarchar(50), - @lastName nvarchar(50), - @openid nvarchar(255), - @role nvarchar(255) - ) -AS - DECLARE - @roleid int, - @userid int - - BEGIN TRANSACTION - - INSERT INTO [dbo].[User] (FirstName, LastName) VALUES (@firstName, @lastName) - SET @userid = (SELECT @@IDENTITY) - - IF (SELECT COUNT(*) FROM dbo.Role WHERE [Name] = @role) = 0 - BEGIN - INSERT INTO dbo.Role (Name) VALUES (@role) - SET @roleid = (SELECT @@IDENTITY) - END - ELSE - BEGIN - SET @roleid = (SELECT Id FROM dbo.Role WHERE [Name] = @role) - END - - INSERT INTO dbo.UserRole (UserId, RoleId) VALUES (@userId, @roleid) - - INSERT INTO dbo.AuthenticationToken - (UserId, OpenIdClaimedIdentifier, OpenIdFriendlyIdentifier) - VALUES - (@userid, @openid, @openid) - - COMMIT TRANSACTION - - RETURN @userid -GO -ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_EmailAddressVerified] DEFAULT ((0)) FOR [EmailAddressVerified] -GO -ALTER TABLE [dbo].[IssuedToken] ADD CONSTRAINT [DF_IssuedToken_CreatedOn] DEFAULT (getdate()) FOR [CreatedOn] -GO -ALTER TABLE [dbo].[IssuedToken] ADD CONSTRAINT [DF_IssuedToken_IsAccessToken] DEFAULT ((0)) FOR [IsAccessToken] -GO -ALTER TABLE [dbo].[IssuedToken] WITH CHECK ADD CONSTRAINT [FK_IssuedToken_Consumer] FOREIGN KEY([ConsumerId]) -REFERENCES [dbo].[Consumer] ([ConsumerId]) -ON UPDATE CASCADE -ON DELETE CASCADE -GO -ALTER TABLE [dbo].[IssuedToken] CHECK CONSTRAINT [FK_IssuedToken_Consumer] -GO -ALTER TABLE [dbo].[IssuedToken] WITH CHECK ADD CONSTRAINT [FK_IssuedToken_User] FOREIGN KEY([UserId]) -REFERENCES [dbo].[User] ([Id]) -ON UPDATE CASCADE -ON DELETE CASCADE -GO -ALTER TABLE [dbo].[IssuedToken] CHECK CONSTRAINT [FK_IssuedToken_User] -GO -ALTER TABLE [dbo].[UserRole] WITH CHECK ADD CONSTRAINT [FK_UserRole_Role] FOREIGN KEY([RoleId]) -REFERENCES [dbo].[Role] ([Id]) -ON UPDATE CASCADE -ON DELETE CASCADE -GO -ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK_UserRole_Role] -GO -ALTER TABLE [dbo].[UserRole] WITH CHECK ADD CONSTRAINT [FK_UserRole_User] FOREIGN KEY([UserId]) -REFERENCES [dbo].[User] ([Id]) -ON UPDATE CASCADE -ON DELETE CASCADE -GO -ALTER TABLE [dbo].[UserRole] CHECK CONSTRAINT [FK_UserRole_User] -GO -ALTER TABLE [dbo].[AuthenticationToken] WITH CHECK ADD CONSTRAINT [FK_AuthenticationToken_User] FOREIGN KEY([UserId]) -REFERENCES [dbo].[User] ([Id]) -ON UPDATE CASCADE -ON DELETE CASCADE -GO -ALTER TABLE [dbo].[AuthenticationToken] CHECK CONSTRAINT [FK_AuthenticationToken_User] -GO |