diff options
| author | Andrew Arnott <andrewarnott@gmail.com> | 2009-12-12 09:26:21 -0800 |
|---|---|---|
| committer | Andrew Arnott <andrewarnott@gmail.com> | 2009-12-12 09:26:21 -0800 |
| commit | 764a5c47d2d5042ee0e2cec54f9cee7011b949a1 (patch) | |
| tree | 6fe8912555b67597a93ecf36f31707a592755bdd | |
| parent | 077d53305f2e3ba2247e0ad7a396727e21a9cfff (diff) | |
| parent | c578ed605653e848253ad6940c9d522846812a53 (diff) | |
| download | DotNetOpenAuth-764a5c47d2d5042ee0e2cec54f9cee7011b949a1.zip DotNetOpenAuth-764a5c47d2d5042ee0e2cec54f9cee7011b949a1.tar.gz DotNetOpenAuth-764a5c47d2d5042ee0e2cec54f9cee7011b949a1.tar.bz2 | |
Project template's database schema now derives from a database project.
Merge branch 'projtemplateDBproject' into v3.3
53 files changed, 1343 insertions, 244 deletions
@@ -18,3 +18,4 @@ PrecompiledWeb *.swx .dotest *.Publish.xml +*.dbproj.schemaview @@ -140,6 +140,9 @@ </Target> <Target Name="BuildProjectTemplates"> + <!-- Deploy the latest SQL script first, so that the class library can embed the latest version. --> + <MSBuild Projects="$(ProjectRoot)projecttemplates\RelyingPartyDatabase\RelyingPartyDatabase.dbproj" Targets="Deploy" /> + <MSBuild Projects="@(ProjectTemplates)" /> </Target> diff --git a/projecttemplates/RelyingPartyDatabase/.gitignore b/projecttemplates/RelyingPartyDatabase/.gitignore new file mode 100644 index 0000000..55b665f --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/.gitignore @@ -0,0 +1,3 @@ +sql/debug +sql/release +*.dbmdl diff --git a/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlcmdvars b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlcmdvars new file mode 100644 index 0000000..705af45 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlcmdvars @@ -0,0 +1,10 @@ +<?xml version="1.0" encoding="utf-8"?> +<SqlCommandVariables xmlns="urn:Microsoft.VisualStudio.Data.Schema.Project.SqlCmdVars"> + <Version>1</Version> + <Properties> + <Property> + <PropertyName>Path1</PropertyName> + <PropertyValue>WEBROOT\App_Data\</PropertyValue> + </Property> + </Properties> +</SqlCommandVariables>
\ No newline at end of file diff --git a/projecttemplates/RelyingPartyDatabase/Properties/Database.sqldeployment b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqldeployment new file mode 100644 index 0000000..c3f58f1 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqldeployment @@ -0,0 +1,17 @@ +<?xml version="1.0" encoding="utf-8"?> +<DeploymentConfigurationSettings xmlns="urn:Microsoft.VisualStudio.Data.Schema.Project.DeploymentConfigurationSettings"> + <Version>1</Version> + <Properties> + <AbortOnFirstError>False</AbortOnFirstError> + <AlwaysCreateNewDatabase>False</AlwaysCreateNewDatabase> + <BlockIncrementalDeploymentIfDataLoss>True</BlockIncrementalDeploymentIfDataLoss> + <CommentOutSetVarDeclarations>True</CommentOutSetVarDeclarations> + <DeployDatabaseProperties>True</DeployDatabaseProperties> + <DeploymentCollationPreference>UseSourceModelCollation</DeploymentCollationPreference> + <DoNotUseAlterAssemblyStatementsToUpdateCLRTypes>False</DoNotUseAlterAssemblyStatementsToUpdateCLRTypes> + <GenerateDropsIfNotInProject>False</GenerateDropsIfNotInProject> + <IgnoreFileAndLogFilePath>True</IgnoreFileAndLogFilePath> + <PerformDatabaseBackup>False</PerformDatabaseBackup> + <SingleUserMode>False</SingleUserMode> + </Properties> +</DeploymentConfigurationSettings>
\ No newline at end of file diff --git a/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlpermissions b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlpermissions new file mode 100644 index 0000000..2b973b7 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlpermissions @@ -0,0 +1,44 @@ +<?xml version="1.0" encoding="utf-8"?> +<Permissions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:Microsoft.VisualStudio.Data.Schema.Permissions" Version="1.0"> + <!-- The examples below are provided to illustrate how permissions + are defined in the project system for Databases, Objects, + and Columns. + + GRANT Database Permissions + + <PermissionStatement Action ="GRANT"> + <Permission>CREATE TABLE</Permission> + <Grantee>User1</Grantee> + </PermissionStatement> + + GRANT Object Permission + + <PermissionStatement Action ="GRANT"> + <Permission>SELECT</Permission> + <Grantee>User1</Grantee> + <Object Name ="Table1" Schema ="User1" Type ="OBJECT"/> + </PermissionStatement> + + DENY Object Permission + + <PermissionStatement Action ="DENY"> + <Permission>DELETE</Permission> + <Grantee>User1</Grantee> + <Object Name ="Table1" Schema ="User1" Type ="OBJECT"/> + </PermissionStatement> + + GRANT Object Column Permission + + <PermissionStatement Action ="GRANT"> + <Permission>SELECT</Permission> + <Grantee>User1</Grantee> + <Object Name ="Table1" Schema ="User1" Type ="OBJECT"> + <Columns Treatment ="INCLUDE"> + <Column Name=”Col1”/> + <Column Name=”Col2”/> + <Column Name=”…”/> + </Columns> + </Object> + </PermissionStatement> + --> +</Permissions>
\ No newline at end of file diff --git a/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlsettings b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlsettings new file mode 100644 index 0000000..b7d41ee --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Properties/Database.sqlsettings @@ -0,0 +1,46 @@ +<?xml version="1.0" encoding="utf-8"?> +<CatalogProperties xmlns="urn:Microsoft.VisualStudio.Data.Schema.Project.CatalogProperties"> + <Version>1</Version> + <Properties> + <AllowSnapshotIsolation>False</AllowSnapshotIsolation> + <AnsiNullDefault>False</AnsiNullDefault> + <AnsiNulls>False</AnsiNulls> + <AnsiPadding>False</AnsiPadding> + <AnsiWarnings>False</AnsiWarnings> + <ArithAbort>False</ArithAbort> + <AutoClose>True</AutoClose> + <AutoCreateStatistics>True</AutoCreateStatistics> + <AutoShrink>False</AutoShrink> + <AutoUpdateStatistics>True</AutoUpdateStatistics> + <AutoUpdateStatisticsAsynchronously>False</AutoUpdateStatisticsAsynchronously> + <ChangeTrackingRetentionPeriod>2</ChangeTrackingRetentionPeriod> + <ChangeTrackingRetentionUnit>Days</ChangeTrackingRetentionUnit> + <CloseCursorOnCommitEnabled>False</CloseCursorOnCommitEnabled> + <CompatibilityMode>90</CompatibilityMode> + <ConcatNullYieldsNull>False</ConcatNullYieldsNull> + <DatabaseAccess>MULTI_USER</DatabaseAccess> + <DatabaseChaining>False</DatabaseChaining> + <DatabaseState>ONLINE</DatabaseState> + <DateCorrelationOptimizationOn>False</DateCorrelationOptimizationOn> + <DefaultCollation>SQL_Latin1_General_CP1_CI_AS</DefaultCollation> + <DefaultCursor>GLOBAL</DefaultCursor> + <DefaultFilegroup>PRIMARY</DefaultFilegroup> + <EnableFullTextSearch>True</EnableFullTextSearch> + <IsBrokerPriorityHonored>False</IsBrokerPriorityHonored> + <IsChangeTrackingAutoCleanupOn>True</IsChangeTrackingAutoCleanupOn> + <IsChangeTrackingOn>False</IsChangeTrackingOn> + <IsEncryptionOn>False</IsEncryptionOn> + <NumericRoundAbort>False</NumericRoundAbort> + <PageVerify>CHECKSUM</PageVerify> + <Parameterization>SIMPLE</Parameterization> + <QuotedIdentifier>False</QuotedIdentifier> + <ReadCommittedSnapshot>False</ReadCommittedSnapshot> + <Recovery>SIMPLE</Recovery> + <RecursiveTriggersEnabled>False</RecursiveTriggersEnabled> + <ServiceBrokerOption>DisableBroker</ServiceBrokerOption> + <TornPageDetection>False</TornPageDetection> + <Trustworthy>False</Trustworthy> + <UpdateOptions>READ_WRITE</UpdateOptions> + <VardecimalStorageFormatOn>False</VardecimalStorageFormatOn> + </Properties> +</CatalogProperties>
\ No newline at end of file diff --git a/projecttemplates/RelyingPartyDatabase/RelyingPartyDatabase.dbproj b/projecttemplates/RelyingPartyDatabase/RelyingPartyDatabase.dbproj new file mode 100644 index 0000000..4ffaf9c --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/RelyingPartyDatabase.dbproj @@ -0,0 +1,286 @@ +<?xml version="1.0" encoding="utf-8"?> +<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="3.5"> + <PropertyGroup> + <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration> + <Name>"DatabaseProject"</Name> + <SchemaVersion>2.0</SchemaVersion> + <ProjectVersion>3.5</ProjectVersion> + <ProjectGuid>{2b4261ac-25ac-4b8d-b459-1c42b6b1401d}</ProjectGuid> + <DSP>Microsoft.Data.Schema.Sql.SqlDsp.Sql100DatabaseSchemaProvider</DSP> + <AppDesignerFolder>Properties</AppDesignerFolder> + <ShowWizard>True</ShowWizard> + <OutputType>Database</OutputType> + <RootPath> + </RootPath> + <IncludeSchemaNameInFileName>False</IncludeSchemaNameInFileName> + <ModelCollation>1033,CI</ModelCollation> + <DefaultFileStructure>BySchema</DefaultFileStructure> + <DeployToDatabaseAddToServerExplorer>False</DeployToDatabaseAddToServerExplorer> + <DeployToScript>True</DeployToScript> + <CatalogPropertiesFile>Properties\Database.sqlsettings</CatalogPropertiesFile> + <ServerPropertiesFile> + </ServerPropertiesFile> + <RootNamespace>RelyingPartyDatabase</RootNamespace> + <DefaultSchema>dbo</DefaultSchema> + <PreviousProjectVersion>3.5</PreviousProjectVersion> + <AllowServerObjects>False</AllowServerObjects> + <AllowDatabaseObjects>True</AllowDatabaseObjects> + </PropertyGroup> + <PropertyGroup Condition=" '$(Configuration)' == 'Release' "> + <OutputPath>.\sql\release\</OutputPath> + <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName> + <TargetConnectionString> + </TargetConnectionString> + <TargetDatabase>RelyingPartyDatabase</TargetDatabase> + <TreatWarningsAsErrors>False</TreatWarningsAsErrors> + <SuppressWarnings> + </SuppressWarnings> + <DeploymentConfigFile>Properties\Database.sqldeployment</DeploymentConfigFile> + <SqlCommandVariablesFile>Properties\Database.sqlcmdvars</SqlCommandVariablesFile> + <DeployToDatabase>False</DeployToDatabase> + </PropertyGroup> + <PropertyGroup Condition=" '$(Configuration)' == 'Debug' "> + <OutputPath>.\sql\debug\</OutputPath> + <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName> + <TargetConnectionString> + </TargetConnectionString> + <TargetDatabase>RelyingPartyDatabase</TargetDatabase> + <TreatWarningsAsErrors>False</TreatWarningsAsErrors> + <SuppressWarnings> + </SuppressWarnings> + <DeploymentConfigFile>Properties\Database.sqldeployment</DeploymentConfigFile> + <SqlCommandVariablesFile>Properties\Database.sqlcmdvars</SqlCommandVariablesFile> + <DeployToDatabase>False</DeployToDatabase> + <DeployScriptFileName>..\..\..\RelyingPartyLogic\CreateDatabase.sql</DeployScriptFileName> + </PropertyGroup> + <!--Import the settings--> + <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets" /> + <ItemGroup> + <Folder Include="Properties\" /> + <Folder Include="Schema Objects\" /> + <Folder Include="Schema Objects\Database Level Objects\" /> + <Folder Include="Schema Objects\Database Level Objects\Assemblies\" /> + <Folder Include="Schema Objects\Database Level Objects\Database Triggers\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Asymmetric Keys\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Certificates\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Database Audit Specification\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Database Encryption Keys\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Master Keys\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Roles\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Roles\Application Roles\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Roles\Database Roles\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Schemas\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Signatures\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Symmetric Keys\" /> + <Folder Include="Schema Objects\Database Level Objects\Security\Users\" /> + <Folder Include="Schema Objects\Database Level Objects\Service Broker\" /> + <Folder Include="Schema Objects\Database Level Objects\Service Broker\Broker Priorities\" /> + <Folder Include="Schema Objects\Database Level Objects\Service Broker\Contracts\" /> + <Folder Include="Schema Objects\Database Level Objects\Service Broker\Event Notifications\" /> + <Folder Include="Schema Objects\Database Level Objects\Service Broker\Message Types\" /> + <Folder Include="Schema Objects\Database Level Objects\Service Broker\Queues\" /> + <Folder Include="Schema Objects\Database Level Objects\Service Broker\Remote Service Binding\" /> + <Folder Include="Schema Objects\Database Level Objects\Service Broker\Routes\" /> + <Folder Include="Schema Objects\Database Level Objects\Service Broker\Services\" /> + <Folder Include="Schema Objects\Database Level Objects\Storage\" /> + <Folder Include="Schema Objects\Database Level Objects\Storage\Filegroups\" /> + <Folder Include="Schema Objects\Database Level Objects\Storage\Files\" /> + <Folder Include="Schema Objects\Database Level Objects\Storage\Full Text Catalogs\" /> + <Folder Include="Schema Objects\Database Level Objects\Storage\Full Text Stoplists\" /> + <Folder Include="Schema Objects\Database Level Objects\Storage\Partition Functions\" /> + <Folder Include="Schema Objects\Database Level Objects\Storage\Partition Schemes\" /> + <Folder Include="Schema Objects\Schemas\" /> + <Folder Include="Schema Objects\Schemas\dbo\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\Defaults\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\Functions\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\Rules\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\Types\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\Types\User Defined Data Types\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\Types\User Defined Table Types\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\Types\User Defined Types CLR\" /> + <Folder Include="Schema Objects\Schemas\dbo\Programmability\Types\XML Schema Collections\" /> + <Folder Include="Schema Objects\Schemas\dbo\Synonyms\" /> + <Folder Include="Schema Objects\Schemas\dbo\Tables\" /> + <Folder Include="Schema Objects\Schemas\dbo\Tables\Constraints\" /> + <Folder Include="Schema Objects\Schemas\dbo\Tables\Indexes\" /> + <Folder Include="Schema Objects\Schemas\dbo\Tables\Keys\" /> + <Folder Include="Schema Objects\Schemas\dbo\Tables\Statistics\" /> + <Folder Include="Schema Objects\Schemas\dbo\Tables\Triggers\" /> + <Folder Include="Schema Objects\Schemas\dbo\Views\" /> + <Folder Include="Schema Objects\Schemas\dbo\Views\Indexes\" /> + <Folder Include="Schema Objects\Schemas\dbo\Views\Statistics\" /> + <Folder Include="Schema Objects\Schemas\dbo\Views\Triggers\" /> + <Folder Include="Scripts" /> + <Folder Include="Scripts\Pre-Deployment" /> + <Folder Include="Scripts\Post-Deployment" /> + <Folder Include="Data Generation Plans" /> + <Folder Include="Schema Comparisons" /> + </ItemGroup> + <ItemGroup> + <PropertiesFile Include="Properties\Database.sqlsettings"> + </PropertiesFile> + <PropertiesFile Include="Properties\Database.sqldeployment"> + </PropertiesFile> + <PropertiesFile Include="Properties\Database.sqlcmdvars"> + </PropertiesFile> + <Build Include="Properties\Database.sqlpermissions"> + <ModelBuilderType>Permissions</ModelBuilderType> + </Build> + <Build Include="Schema Objects\Database Level Objects\Service Broker\Routes\AutoCreatedLocal.route.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Database Level Objects\Storage\Files\Database.mdf.sqlfile.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Database Level Objects\Storage\Files\Database_log.sqlfile.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\AddUser.proc.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\ClearExpiredAssociations.proc.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Programmability\Stored Procedures\ClearExpiredNonces.proc.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\AuthenticationToken.table.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Constraints\DF_AuthenticationToken_CreatedOn.defconst.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Constraints\DF_AuthenticationToken_LastUsed.defconst.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Constraints\DF_AuthenticationToken_UsageCount.defconst.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Constraints\DF_IssuedToken_CreatedOn.defconst.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Constraints\DF_IssuedToken_IsAccessToken.defconst.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Constraints\DF_Nonce_Issued.defconst.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Constraints\DF_User_CreatedOn.defconst.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Constraints\DF_User_EmailAddressVerified.defconst.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Consumer.table.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Indexes\IX_Consumer.index.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Indexes\IX_IssuedToken.index.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Indexes\IX_Nonce_Code.index.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Indexes\IX_Nonce_Expires.index.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Indexes\IX_OpenIDAssociations.index.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\IssuedToken.table.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\FK_AuthenticationToken_User.fkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\FK_IssuedToken_Consumer.fkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\FK_IssuedToken_User.fkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\FK_UserRole_Role.fkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\FK_UserRole_User.fkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\PK_AuthenticationToken.pkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\PK_Consumer.pkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\PK_IssuedToken.pkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\PK_Nonce.pkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\PK_OpenIDAssociations.pkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\PK_Role.pkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\PK_User.pkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Keys\PK_UserRole.pkey.sql"> + <SubType>Code</SubType> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Nonce.table.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\OpenIDAssociation.table.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\Role.table.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\User.table.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <Build Include="Schema Objects\Schemas\dbo\Tables\UserRole.table.sql"> + <SubType>Code</SubType> + <AnsiNulls>On</AnsiNulls> + <QuotedIdentifier>On</QuotedIdentifier> + </Build> + <PreDeploy Include="Scripts\Pre-Deployment\Script.PreDeployment.sql"> + <SubType>Code</SubType> + </PreDeploy> + <PostDeploy Include="Scripts\Post-Deployment\Script.PostDeployment.sql"> + <SubType>Code</SubType> + </PostDeploy> + </ItemGroup> + <ItemGroup> + <ArtifactReference Include="$(VSTSDBDirectory)\Extensions\SqlServer\2008\DBSchemas\Microsoft.SqlTypes.dbschema"> + <HintPath>..\..\..\..\..\..\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas\Microsoft.SqlTypes.dbschema</HintPath> + </ArtifactReference> + </ItemGroup> +</Project>
\ No newline at end of file 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/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 +); + diff --git a/projecttemplates/RelyingPartyDatabase/Scripts/Post-Deployment/Script.PostDeployment.sql b/projecttemplates/RelyingPartyDatabase/Scripts/Post-Deployment/Script.PostDeployment.sql new file mode 100644 index 0000000..37db4f5 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Scripts/Post-Deployment/Script.PostDeployment.sql @@ -0,0 +1,11 @@ +/* +Post-Deployment Script Template +-------------------------------------------------------------------------------------- + This file contains SQL statements that will be appended to the build script. + Use SQLCMD syntax to include a file in the post-deployment script. + Example: :r .\myfile.sql + Use SQLCMD syntax to reference a variable in the post-deployment script. + Example: :setvar TableName MyTable + SELECT * FROM [$(TableName)] +-------------------------------------------------------------------------------------- +*/
\ No newline at end of file diff --git a/projecttemplates/RelyingPartyDatabase/Scripts/Pre-Deployment/Script.PreDeployment.sql b/projecttemplates/RelyingPartyDatabase/Scripts/Pre-Deployment/Script.PreDeployment.sql new file mode 100644 index 0000000..0c9f8d6 --- /dev/null +++ b/projecttemplates/RelyingPartyDatabase/Scripts/Pre-Deployment/Script.PreDeployment.sql @@ -0,0 +1,11 @@ +/* + Pre-Deployment Script Template +-------------------------------------------------------------------------------------- + This file contains SQL statements that will be executed before the build script. + Use SQLCMD syntax to include a file in the pre-deployment script. + Example: :r .\myfile.sql + Use SQLCMD syntax to reference a variable in the pre-deployment script. + Example: :setvar TableName MyTable + SELECT * FROM [$(TableName)] +-------------------------------------------------------------------------------------- +*/
\ No newline at end of file diff --git a/projecttemplates/RelyingPartyLogic/CreateDatabase.sql b/projecttemplates/RelyingPartyLogic/CreateDatabase.sql index 0470448..722740f 100644 --- a/projecttemplates/RelyingPartyLogic/CreateDatabase.sql +++ b/projecttemplates/RelyingPartyLogic/CreateDatabase.sql @@ -1,213 +1,597 @@ -SET ANSI_NULLS ON +/* +Deployment script for RelyingPartyDatabase +*/ + GO -SET QUOTED_IDENTIFIER ON +SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER OFF; + + GO -CREATE PROCEDURE [dbo].[ClearExpiredNonces] -AS +/* +:setvar Path1 "WEBROOT\App_Data\" +:setvar DatabaseName "RelyingPartyDatabase" +:setvar DefaultDataPath "" +*/ -DELETE FROM dbo.[Nonce] -WHERE [Expires] < getutcdate() GO -SET ANSI_NULLS ON +USE [master] + GO -SET QUOTED_IDENTIFIER ON +IF (DB_ID(N'$(DatabaseName)') IS NOT NULL + AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE') +BEGIN + RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT + RETURN +END + GO -CREATE PROCEDURE [dbo].[ClearExpiredAssociations] -AS +IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) +BEGIN + ALTER DATABASE [$(DatabaseName)] + SET SINGLE_USER WITH ROLLBACK IMMEDIATE; + DROP DATABASE [$(DatabaseName)]; +END + +GO +PRINT N'Creating $(DatabaseName)...' +GO +CREATE DATABASE [$(DatabaseName)] + ON + PRIMARY(NAME = [$(Path1)$(DatabaseName).mdf], FILENAME = '$(Path1)$(DatabaseName).mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB) + LOG ON (NAME = [$(DatabaseName)_log], FILENAME = '$(Path1)$(DatabaseName)_log.LDF', MAXSIZE = 2097152 MB, FILEGROWTH = 10 %) COLLATE SQL_Latin1_General_CP1_CI_AS +GO +EXECUTE sp_dbcmptlevel [$(DatabaseName)], 90; + + +GO +IF EXISTS (SELECT 1 + FROM [master].[dbo].[sysdatabases] + WHERE [name] = N'$(DatabaseName)') + BEGIN + ALTER DATABASE [$(DatabaseName)] + SET ANSI_NULLS OFF, + ANSI_PADDING OFF, + ANSI_WARNINGS OFF, + ARITHABORT OFF, + CONCAT_NULL_YIELDS_NULL OFF, + NUMERIC_ROUNDABORT OFF, + QUOTED_IDENTIFIER OFF, + ANSI_NULL_DEFAULT OFF, + CURSOR_DEFAULT GLOBAL, + RECOVERY SIMPLE, + CURSOR_CLOSE_ON_COMMIT OFF, + AUTO_CREATE_STATISTICS ON, + AUTO_SHRINK OFF, + AUTO_UPDATE_STATISTICS ON, + RECURSIVE_TRIGGERS OFF + WITH ROLLBACK IMMEDIATE; + ALTER DATABASE [$(DatabaseName)] + SET AUTO_CLOSE ON + WITH ROLLBACK IMMEDIATE; + END + + +GO +IF EXISTS (SELECT 1 + FROM [master].[dbo].[sysdatabases] + WHERE [name] = N'$(DatabaseName)') + BEGIN + ALTER DATABASE [$(DatabaseName)] + SET ALLOW_SNAPSHOT_ISOLATION OFF; + END + + +GO +IF EXISTS (SELECT 1 + FROM [master].[dbo].[sysdatabases] + WHERE [name] = N'$(DatabaseName)') + BEGIN + ALTER DATABASE [$(DatabaseName)] + SET READ_COMMITTED_SNAPSHOT OFF; + END + + +GO +IF EXISTS (SELECT 1 + FROM [master].[dbo].[sysdatabases] + WHERE [name] = N'$(DatabaseName)') + BEGIN + ALTER DATABASE [$(DatabaseName)] + SET AUTO_UPDATE_STATISTICS_ASYNC OFF, + PAGE_VERIFY CHECKSUM, + DATE_CORRELATION_OPTIMIZATION OFF, + DISABLE_BROKER, + PARAMETERIZATION SIMPLE + WITH ROLLBACK IMMEDIATE; + END + + +GO +IF IS_SRVROLEMEMBER(N'sysadmin') = 1 + BEGIN + IF EXISTS (SELECT 1 + FROM [master].[dbo].[sysdatabases] + WHERE [name] = N'$(DatabaseName)') + BEGIN + EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)] + SET TRUSTWORTHY OFF, + DB_CHAINING OFF + WITH ROLLBACK IMMEDIATE'; + END + END +ELSE + BEGIN + PRINT N'The database settings for DB_CHAINING or TRUSTWORTHY cannot be modified. You must be a SysAdmin to apply these settings.'; + END + + +GO +IF EXISTS (SELECT 1 + FROM [master].[dbo].[sysdatabases] + WHERE [name] = N'$(DatabaseName)') + BEGIN + ALTER DATABASE [$(DatabaseName)] + SET HONOR_BROKER_PRIORITY OFF + WITH ROLLBACK IMMEDIATE; + END + + +GO +USE [$(DatabaseName)] + +GO +IF fulltextserviceproperty(N'IsFulltextInstalled') = 1 + EXECUTE sp_fulltext_database 'enable'; + + +GO + +GO +/* + Pre-Deployment Script Template +-------------------------------------------------------------------------------------- + This file contains SQL statements that will be executed before the build script. + Use SQLCMD syntax to include a file in the pre-deployment script. + Example: :r .\myfile.sql + Use SQLCMD syntax to reference a variable in the pre-deployment script. + Example: :setvar TableName MyTable + SELECT * FROM [$(TableName)] +-------------------------------------------------------------------------------------- +*/ + +GO + +GO +PRINT N'Creating dbo.AuthenticationToken...'; + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + +GO +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 +); + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + +GO +PRINT N'Creating dbo.Consumer...'; + + +GO +SET ANSI_NULLS, 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_CS_AS NULL, + [X509Certificate] IMAGE NULL, + [Callback] NVARCHAR (2048) NULL, + [VerificationCodeFormat] INT NOT NULL, + [VerificationCodeLength] INT NOT NULL, + [Name] NVARCHAR (50) NULL +); + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + +GO +PRINT N'Creating dbo.IssuedToken...'; + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + +GO +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 +); + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + +GO +PRINT N'Creating dbo.Nonce...'; + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + +GO +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 +); + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + +GO +PRINT N'Creating dbo.OpenIDAssociation...'; + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + +GO +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 +); + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + +GO +PRINT N'Creating dbo.Role...'; + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + +GO +CREATE TABLE [dbo].[Role] ( + [RoleId] INT IDENTITY (1, 1) NOT NULL, + [Name] NVARCHAR (50) NOT NULL +); + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + +GO +PRINT N'Creating dbo.User...'; + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + +GO +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 +); + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + +GO +PRINT N'Creating dbo.UserRole...'; + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + +GO +CREATE TABLE [dbo].[UserRole] ( + [UserId] INT NOT NULL, + [RoleId] INT NOT NULL +); + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + +GO +PRINT N'Creating dbo.Consumer.IX_Consumer...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.IssuedToken.IX_IssuedToken...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.Nonce.IX_Nonce_Code...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.Nonce.IX_Nonce_Expires...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.OpenIDAssociation.IX_OpenIDAssociations...'; + + +GO +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); + -DELETE FROM dbo.OpenIDAssociation -WHERE [Expiration] < getutcdate() GO -SET ANSI_NULLS ON -GO -SET QUOTED_IDENTIFIER ON -GO -SET ANSI_PADDING ON -GO -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, - CONSTRAINT [PK_Nonce] PRIMARY KEY CLUSTERED -( - [NonceId] 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_Nonce_Code] ON [dbo].[Nonce] -( - [Context] ASC, - [Code] ASC, - [Issued] 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 -CREATE NONCLUSTERED INDEX [IX_Nonce_Expires] ON [dbo].[Nonce] -( - [Expires] 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 -SET ANSI_PADDING ON -GO -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, - CONSTRAINT [PK_OpenIDAssociations] PRIMARY KEY CLUSTERED -( - [AssociationId] 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_OpenIDAssociations] ON [dbo].[OpenIDAssociation] -( - [DistinguishingFactor] ASC, - [AssociationHandle] 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].[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) 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]( - [UserId] [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, - [CreatedOn] [datetime] NOT NULL, - CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED -( - [UserId] 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]( - [RoleId] [int] IDENTITY(1,1) NOT NULL, - [Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, - CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED -( - [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 -SET ANSI_PADDING ON -GO -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) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [VerificationCode] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CS_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 -( - [IssuedTokenId] 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]( - [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) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, - [CreatedOn] [datetime] NOT NULL, - [LastUsed] [datetime] NOT NULL, - [UsageCount] [int] NOT NULL, - CONSTRAINT [PK_AuthenticationToken] PRIMARY KEY CLUSTERED -( - [AuthenticationTokenId] 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 +PRINT N'Creating dbo.DF_AuthenticationToken_CreatedOn...'; + + +GO +ALTER TABLE [dbo].[AuthenticationToken] + ADD CONSTRAINT [DF_AuthenticationToken_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn]; + + +GO +PRINT N'Creating dbo.DF_AuthenticationToken_LastUsed...'; + + +GO +ALTER TABLE [dbo].[AuthenticationToken] + ADD CONSTRAINT [DF_AuthenticationToken_LastUsed] DEFAULT (getutcdate()) FOR [LastUsed]; + + +GO +PRINT N'Creating dbo.DF_AuthenticationToken_UsageCount...'; + + +GO +ALTER TABLE [dbo].[AuthenticationToken] + ADD CONSTRAINT [DF_AuthenticationToken_UsageCount] DEFAULT ((0)) FOR [UsageCount]; + + +GO +PRINT N'Creating dbo.DF_IssuedToken_CreatedOn...'; + + +GO +ALTER TABLE [dbo].[IssuedToken] + ADD CONSTRAINT [DF_IssuedToken_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn]; + + +GO +PRINT N'Creating dbo.DF_IssuedToken_IsAccessToken...'; + + +GO +ALTER TABLE [dbo].[IssuedToken] + ADD CONSTRAINT [DF_IssuedToken_IsAccessToken] DEFAULT ((0)) FOR [IsAccessToken]; + + +GO +PRINT N'Creating dbo.DF_Nonce_Issued...'; + + +GO +ALTER TABLE [dbo].[Nonce] + ADD CONSTRAINT [DF_Nonce_Issued] DEFAULT (getutcdate()) FOR [Issued]; + + +GO +PRINT N'Creating dbo.DF_User_CreatedOn...'; + + +GO +ALTER TABLE [dbo].[User] + ADD CONSTRAINT [DF_User_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn]; + + +GO +PRINT N'Creating dbo.DF_User_EmailAddressVerified...'; + + +GO +ALTER TABLE [dbo].[User] + ADD CONSTRAINT [DF_User_EmailAddressVerified] DEFAULT ((0)) FOR [EmailAddressVerified]; + + +GO +PRINT N'Creating dbo.PK_AuthenticationToken...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.PK_Consumer...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.PK_IssuedToken...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.PK_Nonce...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.PK_OpenIDAssociations...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.PK_Role...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.PK_User...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.PK_UserRole...'; + + +GO +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); + + +GO +PRINT N'Creating dbo.FK_AuthenticationToken_User...'; + + +GO +ALTER TABLE [dbo].[AuthenticationToken] + ADD CONSTRAINT [FK_AuthenticationToken_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId]) ON DELETE CASCADE ON UPDATE CASCADE; + + +GO +PRINT N'Creating dbo.FK_IssuedToken_Consumer...'; + + +GO +ALTER TABLE [dbo].[IssuedToken] + ADD CONSTRAINT [FK_IssuedToken_Consumer] FOREIGN KEY ([ConsumerId]) REFERENCES [dbo].[Consumer] ([ConsumerId]) ON DELETE CASCADE ON UPDATE CASCADE; + + +GO +PRINT N'Creating dbo.FK_IssuedToken_User...'; + + +GO +ALTER TABLE [dbo].[IssuedToken] + ADD CONSTRAINT [FK_IssuedToken_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId]) ON DELETE CASCADE ON UPDATE CASCADE; + + +GO +PRINT N'Creating dbo.FK_UserRole_Role...'; + + +GO +ALTER TABLE [dbo].[UserRole] + ADD CONSTRAINT [FK_UserRole_Role] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[Role] ([RoleId]) ON DELETE CASCADE ON UPDATE CASCADE; + + +GO +PRINT N'Creating dbo.FK_UserRole_User...'; + + +GO +ALTER TABLE [dbo].[UserRole] + ADD CONSTRAINT [FK_UserRole_User] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([UserId]) ON DELETE CASCADE ON UPDATE CASCADE; + + +GO +PRINT N'Creating dbo.AddUser...'; + + +GO +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + GO CREATE PROCEDURE [dbo].[AddUser] - ( - @firstName nvarchar(50), - @lastName nvarchar(50), - @openid nvarchar(255), - @role nvarchar(255) - ) +@firstName NVARCHAR (50), @lastName NVARCHAR (50), @openid NVARCHAR (255), @role NVARCHAR (255) AS - DECLARE +DECLARE @roleid int, @userid int @@ -236,55 +620,85 @@ AS COMMIT TRANSACTION RETURN @userid + + GO -ALTER TABLE [dbo].[Nonce] ADD CONSTRAINT [DF_Nonce_Issued] DEFAULT (getutcdate()) FOR [Issued] -GO -ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_EmailAddressVerified] DEFAULT ((0)) FOR [EmailAddressVerified] -GO -ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn] +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + GO -ALTER TABLE [dbo].[IssuedToken] ADD CONSTRAINT [DF_IssuedToken_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn] +PRINT N'Creating dbo.ClearExpiredAssociations...'; + + GO -ALTER TABLE [dbo].[IssuedToken] ADD CONSTRAINT [DF_IssuedToken_IsAccessToken] DEFAULT ((0)) FOR [IsAccessToken] +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + GO -ALTER TABLE [dbo].[AuthenticationToken] ADD CONSTRAINT [DF_AuthenticationToken_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn] +CREATE PROCEDURE [dbo].[ClearExpiredAssociations] + +AS +DELETE FROM dbo.OpenIDAssociation +WHERE [Expiration] < getutcdate() + + GO -ALTER TABLE [dbo].[AuthenticationToken] ADD CONSTRAINT [DF_AuthenticationToken_LastUsed] DEFAULT (getutcdate()) FOR [LastUsed] +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + GO -ALTER TABLE [dbo].[AuthenticationToken] ADD CONSTRAINT [DF_AuthenticationToken_UsageCount] DEFAULT ((0)) FOR [UsageCount] +PRINT N'Creating dbo.ClearExpiredNonces...'; + + 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 +SET ANSI_NULLS, QUOTED_IDENTIFIER ON; + + GO -ALTER TABLE [dbo].[IssuedToken] CHECK CONSTRAINT [FK_IssuedToken_Consumer] +CREATE PROCEDURE [dbo].[ClearExpiredNonces] + +AS +DELETE FROM dbo.[Nonce] +WHERE [Expires] < getutcdate() + + GO -ALTER TABLE [dbo].[IssuedToken] WITH CHECK ADD CONSTRAINT [FK_IssuedToken_User] FOREIGN KEY([UserId]) -REFERENCES [dbo].[User] ([UserId]) -ON UPDATE CASCADE -ON DELETE CASCADE +SET ANSI_NULLS, QUOTED_IDENTIFIER OFF; + + GO -ALTER TABLE [dbo].[IssuedToken] CHECK CONSTRAINT [FK_IssuedToken_User] +PRINT N'Creating AutoCreatedLocal...'; + + GO -ALTER TABLE [dbo].[UserRole] WITH CHECK ADD CONSTRAINT [FK_UserRole_Role] FOREIGN KEY([RoleId]) -REFERENCES [dbo].[Role] ([RoleId]) -ON UPDATE CASCADE -ON DELETE CASCADE +CREATE ROUTE [AutoCreatedLocal] + AUTHORIZATION [dbo] + WITH ADDRESS = N'LOCAL'; + + 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] ([UserId]) -ON UPDATE CASCADE -ON DELETE CASCADE +/* +Post-Deployment Script Template +-------------------------------------------------------------------------------------- + This file contains SQL statements that will be appended to the build script. + Use SQLCMD syntax to include a file in the post-deployment script. + Example: :r .\myfile.sql + Use SQLCMD syntax to reference a variable in the post-deployment script. + Example: :setvar TableName MyTable + SELECT * FROM [$(TableName)] +-------------------------------------------------------------------------------------- +*/ + 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] ([UserId]) -ON UPDATE CASCADE -ON DELETE CASCADE + GO -ALTER TABLE [dbo].[AuthenticationToken] CHECK CONSTRAINT [FK_AuthenticationToken_User] +ALTER DATABASE [$(DatabaseName)] + SET MULTI_USER + WITH ROLLBACK IMMEDIATE; + + GO diff --git a/projecttemplates/RelyingPartyLogic/Utilities.cs b/projecttemplates/RelyingPartyLogic/Utilities.cs index fb05306..023121e 100644 --- a/projecttemplates/RelyingPartyLogic/Utilities.cs +++ b/projecttemplates/RelyingPartyLogic/Utilities.cs @@ -11,6 +11,7 @@ namespace RelyingPartyLogic { using System.Data.Common; using System.Data.EntityClient; using System.Data.Objects; + using System.Data.SqlClient; using System.Globalization; using System.IO; using System.Linq; @@ -40,27 +41,56 @@ namespace RelyingPartyLogic { public static void CreateDatabase(Identifier claimedId, string friendlyId, string databaseName) { const string SqlFormat = @" -CREATE DATABASE [{0}] ON (NAME='{0}', FILENAME='{0}') +{0} GO -USE ""{0}"" -GO -{1} -EXEC [dbo].[AddUser] 'admin', 'admin', '{2}', '{3}' +EXEC [dbo].[AddUser] 'admin', 'admin', '{1}', '{2}' GO "; - string schemaSql; + var removeSnippets = new string[] { @" +IF EXISTS (SELECT 1 + FROM [master].[dbo].[sysdatabases] + WHERE [name] = N'$(DatabaseName)') + BEGIN + ALTER DATABASE [$(DatabaseName)] + SET HONOR_BROKER_PRIORITY OFF + WITH ROLLBACK IMMEDIATE; + END + + +GO", @" +PRINT N'Creating AutoCreatedLocal...'; + + +GO +CREATE ROUTE [AutoCreatedLocal] + AUTHORIZATION [dbo] + WITH ADDRESS = N'LOCAL'; + + +GO +"}; + StringBuilder schemaSqlBuilder = new StringBuilder(); using (var sr = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(DefaultNamespace + ".CreateDatabase.sql"))) { - schemaSql = sr.ReadToEnd(); + schemaSqlBuilder.Append(sr.ReadToEnd()); } + foreach (string remove in removeSnippets) { + schemaSqlBuilder.Replace(remove, string.Empty); + } + schemaSqlBuilder.Replace("$(Path1)", HttpContext.Current.Server.MapPath("~/App_Data/")); + schemaSqlBuilder.Replace("$(DatabaseName)", databaseName); + string databasePath = HttpContext.Current.Server.MapPath("~/App_Data/" + databaseName + ".mdf"); - string sql = string.Format(CultureInfo.InvariantCulture, SqlFormat, databasePath, schemaSql, claimedId, "Admin"); + string sql = string.Format(CultureInfo.InvariantCulture, SqlFormat, schemaSqlBuilder, claimedId, "Admin"); var serverConnection = new ServerConnection(".\\sqlexpress"); try { serverConnection.ExecuteNonQuery(sql); - var server = new Server(serverConnection); - server.DetachDatabase(databasePath, true); } finally { + try { + var server = new Server(serverConnection); + server.DetachDatabase(databaseName, true); + } catch (SqlException) { + } serverConnection.Disconnect(); } } diff --git a/src/DotNetOpenAuth.sln b/src/DotNetOpenAuth.sln index 3233865..569a7bc 100644 --- a/src/DotNetOpenAuth.sln +++ b/src/DotNetOpenAuth.sln @@ -7,6 +7,7 @@ Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "DotNetOpenAuth.Test", "DotN EndProject Project("{2150E333-8FDC-42A3-9474-1A3956D46DE8}") = "Solution Items", "Solution Items", "{20B5E173-C3C4-49F8-BD25-E69044075B4D}" ProjectSection(SolutionItems) = preProject + ..\build.proj = ..\build.proj DotNetOpenAuth.vsmdi = DotNetOpenAuth.vsmdi ..\LICENSE.txt = ..\LICENSE.txt LocalTestRun.testrunconfig = LocalTestRun.testrunconfig @@ -169,6 +170,11 @@ EndProject Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "WebFormsRelyingParty", "..\projecttemplates\WebFormsRelyingParty\WebFormsRelyingParty.csproj", "{A78F8FC6-7B03-4230-BE41-761E400D6810}" EndProject Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "RelyingPartyLogic", "..\projecttemplates\RelyingPartyLogic\RelyingPartyLogic.csproj", "{17932639-1F50-48AF-B0A5-E2BF832F82CC}" + ProjectSection(ProjectDependencies) = postProject + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D} = {2B4261AC-25AC-4B8D-B459-1C42B6B1401D} + EndProjectSection +EndProject +Project("{C8D11400-126E-41CD-887F-60BD40844F9E}") = "RelyingPartyDatabase", "..\projecttemplates\RelyingPartyDatabase\RelyingPartyDatabase.dbproj", "{2B4261AC-25AC-4B8D-B459-1C42B6B1401D}" EndProject Global GlobalSection(TestCaseManagementSettings) = postSolution @@ -276,6 +282,15 @@ Global {17932639-1F50-48AF-B0A5-E2BF832F82CC}.Debug|Any CPU.Build.0 = Debug|Any CPU {17932639-1F50-48AF-B0A5-E2BF832F82CC}.Release|Any CPU.ActiveCfg = Release|Any CPU {17932639-1F50-48AF-B0A5-E2BF832F82CC}.Release|Any CPU.Build.0 = Release|Any CPU + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D}.CodeAnalysis|Any CPU.ActiveCfg = Debug|Any CPU + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D}.CodeAnalysis|Any CPU.Build.0 = Debug|Any CPU + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D}.CodeAnalysis|Any CPU.Deploy.0 = Debug|Any CPU + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D}.Debug|Any CPU.ActiveCfg = Debug|Any CPU + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D}.Debug|Any CPU.Build.0 = Debug|Any CPU + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D}.Debug|Any CPU.Deploy.0 = Debug|Any CPU + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D}.Release|Any CPU.ActiveCfg = Release|Any CPU + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D}.Release|Any CPU.Build.0 = Release|Any CPU + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D}.Release|Any CPU.Deploy.0 = Release|Any CPU EndGlobalSection GlobalSection(SolutionProperties) = preSolution HideSolutionNode = FALSE @@ -298,5 +313,6 @@ Global {5C65603B-235F-47E6-B536-06385C60DE7F} = {E9ED920D-1F83-48C0-9A4B-09CCE505FE6D} {A78F8FC6-7B03-4230-BE41-761E400D6810} = {B9EB8729-4B54-4453-B089-FE6761BA3057} {17932639-1F50-48AF-B0A5-E2BF832F82CC} = {B9EB8729-4B54-4453-B089-FE6761BA3057} + {2B4261AC-25AC-4B8D-B459-1C42B6B1401D} = {B9EB8729-4B54-4453-B089-FE6761BA3057} EndGlobalSection EndGlobal |