diff options
Diffstat (limited to 'projecttemplates/RelyingPartyDatabase/Schema Objects')
41 files changed, 216 insertions, 0 deletions
diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Service Broker/Routes/AutoCreatedLocal.route.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Service Broker/Routes/AutoCreatedLocal.route.sql new file mode 100644 index 0000000..4d731a7 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Service Broker/Routes/AutoCreatedLocal.route.sql @@ -0,0 +1,4 @@ +CREATE ROUTE [AutoCreatedLocal] + AUTHORIZATION [dbo] + WITH ADDRESS = N'LOCAL'; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/Database.mdf.sqlfile.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/Database.mdf.sqlfile.sql new file mode 100644 index 0000000..0c2e5c8 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/Database.mdf.sqlfile.sql @@ -0,0 +1,3 @@ +ALTER DATABASE [$(DatabaseName)] + ADD FILE (NAME = [$(Path1)$(DatabaseName).mdf], FILENAME = '$(Path1)$(DatabaseName).mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY]; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/Database_log.sqlfile.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/Database_log.sqlfile.sql new file mode 100644 index 0000000..bcd70cd --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Database Level Objects/Storage/Files/Database_log.sqlfile.sql @@ -0,0 +1,3 @@ +ALTER DATABASE [$(DatabaseName)] + ADD LOG FILE (NAME = [$(DatabaseName)_log], FILENAME = '$(Path1)$(DatabaseName)_log.LDF', MAXSIZE = 2097152 MB, FILEGROWTH = 10 %); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Programmability/Stored Procedures/AddUser.proc.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Programmability/Stored Procedures/AddUser.proc.sql new file mode 100644 index 0000000..b22b231 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Programmability/Stored Procedures/AddUser.proc.sql @@ -0,0 +1,37 @@ +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 RoleId 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 diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Programmability/Stored Procedures/ClearExpiredAssociations.proc.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Programmability/Stored Procedures/ClearExpiredAssociations.proc.sql new file mode 100644 index 0000000..6a143d0 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Programmability/Stored Procedures/ClearExpiredAssociations.proc.sql @@ -0,0 +1,5 @@ +CREATE PROCEDURE dbo.ClearExpiredAssociations +AS + +DELETE FROM dbo.OpenIDAssociation +WHERE [Expiration] < getutcdate() diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Programmability/Stored Procedures/ClearExpiredNonces.proc.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Programmability/Stored Procedures/ClearExpiredNonces.proc.sql new file mode 100644 index 0000000..3299c6c --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Programmability/Stored Procedures/ClearExpiredNonces.proc.sql @@ -0,0 +1,5 @@ +CREATE PROCEDURE dbo.ClearExpiredNonces +AS + +DELETE FROM dbo.[Nonce] +WHERE [Expires] < getutcdate() diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/AuthenticationToken.table.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/AuthenticationToken.table.sql new file mode 100644 index 0000000..920e36e --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/AuthenticationToken.table.sql @@ -0,0 +1,10 @@ +CREATE TABLE [dbo].[AuthenticationToken] ( + [AuthenticationTokenId] 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) NULL, + [CreatedOn] DATETIME NOT NULL, + [LastUsed] DATETIME NOT NULL, + [UsageCount] INT NOT NULL +); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_AuthenticationToken_CreatedOn.defconst.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_AuthenticationToken_CreatedOn.defconst.sql new file mode 100644 index 0000000..df7c22e --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_AuthenticationToken_CreatedOn.defconst.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[AuthenticationToken] + ADD CONSTRAINT [DF_AuthenticationToken_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn]; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_AuthenticationToken_LastUsed.defconst.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_AuthenticationToken_LastUsed.defconst.sql new file mode 100644 index 0000000..95f5490 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_AuthenticationToken_LastUsed.defconst.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[AuthenticationToken] + ADD CONSTRAINT [DF_AuthenticationToken_LastUsed] DEFAULT (getutcdate()) FOR [LastUsed]; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_AuthenticationToken_UsageCount.defconst.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_AuthenticationToken_UsageCount.defconst.sql new file mode 100644 index 0000000..f7a65df --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_AuthenticationToken_UsageCount.defconst.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[AuthenticationToken] + ADD CONSTRAINT [DF_AuthenticationToken_UsageCount] DEFAULT ((0)) FOR [UsageCount]; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_IssuedToken_CreatedOn.defconst.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_IssuedToken_CreatedOn.defconst.sql new file mode 100644 index 0000000..c60323f --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_IssuedToken_CreatedOn.defconst.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[IssuedToken] + ADD CONSTRAINT [DF_IssuedToken_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn]; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_IssuedToken_IsAccessToken.defconst.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_IssuedToken_IsAccessToken.defconst.sql new file mode 100644 index 0000000..2e9e5fd --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_IssuedToken_IsAccessToken.defconst.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[IssuedToken] + ADD CONSTRAINT [DF_IssuedToken_IsAccessToken] DEFAULT ((0)) FOR [IsAccessToken]; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_Nonce_Issued.defconst.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_Nonce_Issued.defconst.sql new file mode 100644 index 0000000..84b5e52 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_Nonce_Issued.defconst.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[Nonce] + ADD CONSTRAINT [DF_Nonce_Issued] DEFAULT (getutcdate()) FOR [Issued]; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_User_CreatedOn.defconst.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_User_CreatedOn.defconst.sql new file mode 100644 index 0000000..101d2c2 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_User_CreatedOn.defconst.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[User] + ADD CONSTRAINT [DF_User_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn]; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_User_EmailAddressVerified.defconst.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_User_EmailAddressVerified.defconst.sql new file mode 100644 index 0000000..04779be --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Constraints/DF_User_EmailAddressVerified.defconst.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[User] + ADD CONSTRAINT [DF_User_EmailAddressVerified] DEFAULT ((0)) FOR [EmailAddressVerified]; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Consumer.table.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Consumer.table.sql new file mode 100644 index 0000000..8549a78 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Consumer.table.sql @@ -0,0 +1,11 @@ +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_CS_AS NULL, + [X509Certificate] IMAGE NULL, + [Callback] NVARCHAR (2048) NULL, + [VerificationCodeFormat] INT NOT NULL, + [VerificationCodeLength] INT NOT NULL, + [Name] NVARCHAR (50) NULL +); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_Consumer.index.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_Consumer.index.sql new file mode 100644 index 0000000..149ae35 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_Consumer.index.sql @@ -0,0 +1,3 @@ +CREATE UNIQUE NONCLUSTERED INDEX [IX_Consumer] + ON [dbo].[Consumer]([ConsumerKey] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_IssuedToken.index.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_IssuedToken.index.sql new file mode 100644 index 0000000..5bc3a53 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_IssuedToken.index.sql @@ -0,0 +1,3 @@ +CREATE UNIQUE NONCLUSTERED INDEX [IX_IssuedToken] + ON [dbo].[IssuedToken]([Token] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_Nonce_Code.index.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_Nonce_Code.index.sql new file mode 100644 index 0000000..5539512 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_Nonce_Code.index.sql @@ -0,0 +1,3 @@ +CREATE UNIQUE NONCLUSTERED INDEX [IX_Nonce_Code] + ON [dbo].[Nonce]([Context] ASC, [Code] ASC, [Issued] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_Nonce_Expires.index.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_Nonce_Expires.index.sql new file mode 100644 index 0000000..23b7cc1 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_Nonce_Expires.index.sql @@ -0,0 +1,3 @@ +CREATE NONCLUSTERED INDEX [IX_Nonce_Expires] + ON [dbo].[Nonce]([Expires] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_OpenIDAssociations.index.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_OpenIDAssociations.index.sql new file mode 100644 index 0000000..c137af6 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Indexes/IX_OpenIDAssociations.index.sql @@ -0,0 +1,3 @@ +CREATE UNIQUE NONCLUSTERED INDEX [IX_OpenIDAssociations] + ON [dbo].[OpenIDAssociation]([DistinguishingFactor] ASC, [AssociationHandle] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/IssuedToken.table.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/IssuedToken.table.sql new file mode 100644 index 0000000..8882e93 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/IssuedToken.table.sql @@ -0,0 +1,15 @@ +CREATE TABLE [dbo].[IssuedToken] ( + [IssuedTokenId] 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) NULL, + [VerificationCode] NVARCHAR (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL, + [ConsumerVersion] VARCHAR (10) NULL, + [ExpirationDate] DATETIME NULL, + [IsAccessToken] BIT NOT NULL, + [Scope] NVARCHAR (255) NULL +); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_AuthenticationToken_User.fkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_AuthenticationToken_User.fkey.sql new file mode 100644 index 0000000..4428616 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_AuthenticationToken_User.fkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[AuthenticationToken] + ADD CONSTRAINT [FK_AuthenticationToken_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId]) ON DELETE CASCADE ON UPDATE CASCADE; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_IssuedToken_Consumer.fkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_IssuedToken_Consumer.fkey.sql new file mode 100644 index 0000000..a5b3dac --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_IssuedToken_Consumer.fkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[IssuedToken] + ADD CONSTRAINT [FK_IssuedToken_Consumer] FOREIGN KEY ([ConsumerId]) REFERENCES [dbo].[Consumer] ([ConsumerId]) ON DELETE CASCADE ON UPDATE CASCADE; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_IssuedToken_User.fkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_IssuedToken_User.fkey.sql new file mode 100644 index 0000000..045a694 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_IssuedToken_User.fkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[IssuedToken] + ADD CONSTRAINT [FK_IssuedToken_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId]) ON DELETE CASCADE ON UPDATE CASCADE; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_UserRole_Role.fkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_UserRole_Role.fkey.sql new file mode 100644 index 0000000..859b6f6 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_UserRole_Role.fkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[UserRole] + ADD CONSTRAINT [FK_UserRole_Role] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[Role] ([RoleId]) ON DELETE CASCADE ON UPDATE CASCADE; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_UserRole_User.fkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_UserRole_User.fkey.sql new file mode 100644 index 0000000..bd0a303 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/FK_UserRole_User.fkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[UserRole] + ADD CONSTRAINT [FK_UserRole_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId]) ON DELETE CASCADE ON UPDATE CASCADE; + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_AuthenticationToken.pkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_AuthenticationToken.pkey.sql new file mode 100644 index 0000000..21ed5f9 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_AuthenticationToken.pkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[AuthenticationToken] + ADD CONSTRAINT [PK_AuthenticationToken] PRIMARY KEY CLUSTERED ([AuthenticationTokenId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_Consumer.pkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_Consumer.pkey.sql new file mode 100644 index 0000000..edde20f --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_Consumer.pkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[Consumer] + ADD CONSTRAINT [PK_Consumer] PRIMARY KEY CLUSTERED ([ConsumerId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_IssuedToken.pkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_IssuedToken.pkey.sql new file mode 100644 index 0000000..e2f95ef --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_IssuedToken.pkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[IssuedToken] + ADD CONSTRAINT [PK_IssuedToken] PRIMARY KEY CLUSTERED ([IssuedTokenId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_Nonce.pkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_Nonce.pkey.sql new file mode 100644 index 0000000..d6faf9e --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_Nonce.pkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[Nonce] + ADD CONSTRAINT [PK_Nonce] PRIMARY KEY CLUSTERED ([NonceId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_OpenIDAssociations.pkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_OpenIDAssociations.pkey.sql new file mode 100644 index 0000000..cdadaf7 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_OpenIDAssociations.pkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[OpenIDAssociation] + ADD CONSTRAINT [PK_OpenIDAssociations] PRIMARY KEY CLUSTERED ([AssociationId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_Role.pkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_Role.pkey.sql new file mode 100644 index 0000000..62b87cd --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_Role.pkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[Role] + ADD CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED ([RoleId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_User.pkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_User.pkey.sql new file mode 100644 index 0000000..d44081d --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_User.pkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[User] + ADD CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([UserId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_UserRole.pkey.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_UserRole.pkey.sql new file mode 100644 index 0000000..77579c0 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Keys/PK_UserRole.pkey.sql @@ -0,0 +1,3 @@ +ALTER TABLE [dbo].[UserRole] + ADD CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Log.table.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Log.table.sql new file mode 100644 index 0000000..84fd97a --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Log.table.sql @@ -0,0 +1,9 @@ +CREATE TABLE [dbo].[Log] ( + [Id] INT IDENTITY (1, 1) NOT NULL, + [Date] DATETIME NOT NULL, + [Thread] VARCHAR (255) NOT NULL, + [Level] VARCHAR (50) NOT NULL, + [Logger] VARCHAR (255) NOT NULL, + [Message] VARCHAR (4000) NOT NULL, + [Exception] VARCHAR (2000) NULL +) diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Nonce.table.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Nonce.table.sql new file mode 100644 index 0000000..bd52d69 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Nonce.table.sql @@ -0,0 +1,8 @@ +CREATE TABLE [dbo].[Nonce] ( + [NonceId] INT IDENTITY (1, 1) NOT NULL, + [Context] VARCHAR (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, + [Code] VARCHAR (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, + [Issued] DATETIME NOT NULL, + [Expires] DATETIME NOT NULL +); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/OpenIDAssociation.table.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/OpenIDAssociation.table.sql new file mode 100644 index 0000000..bbcf527 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/OpenIDAssociation.table.sql @@ -0,0 +1,9 @@ +CREATE TABLE [dbo].[OpenIDAssociation] ( + [AssociationId] INT IDENTITY (1, 1) NOT NULL, + [DistinguishingFactor] VARCHAR (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, + [AssociationHandle] VARCHAR (255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, + [Expiration] DATETIME NOT NULL, + [PrivateData] BINARY (64) NOT NULL, + [PrivateDataLength] INT NOT NULL +); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Role.table.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Role.table.sql new file mode 100644 index 0000000..eb7a33c --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/Role.table.sql @@ -0,0 +1,5 @@ +CREATE TABLE [dbo].[Role] ( + [RoleId] INT IDENTITY (1, 1) NOT NULL, + [Name] NVARCHAR (50) NOT NULL +); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/User.table.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/User.table.sql new file mode 100644 index 0000000..2df39d6 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/User.table.sql @@ -0,0 +1,9 @@ +CREATE TABLE [dbo].[User] ( + [UserId] INT IDENTITY (1, 1) NOT NULL, + [FirstName] NVARCHAR (50) NULL, + [LastName] NVARCHAR (50) NULL, + [EmailAddress] NVARCHAR (100) NULL, + [EmailAddressVerified] BIT NOT NULL, + [CreatedOn] DATETIME NOT NULL +); + diff --git a/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/UserRole.table.sql b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/UserRole.table.sql new file mode 100644 index 0000000..fc69e2e --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Schema Objects/Schemas/dbo/Tables/UserRole.table.sql @@ -0,0 +1,5 @@ +CREATE TABLE [dbo].[UserRole] ( + [UserId] INT NOT NULL, + [RoleId] INT NOT NULL +); + |