text: ﻿/*
  Deployment script for wealthmgr
  
  This code was generated by a tool.
  Changes to this file may cause incorrect behavior and will be lost if
  the code is regenerated.
  */
  
  GO
  SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
  
  SET NUMERIC_ROUNDABORT OFF;
  
  
  GO
  :setvar DatabaseName "wealthmgr"
  :setvar DefaultFilePrefix "wealthmgr"
  :setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL14.WEALTHMGR\MSSQL\DATA\"
  :setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL14.WEALTHMGR\MSSQL\DATA\"
  
  GO
  :on error exit
  GO
  /*
  Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
  To re-enable the script after enabling SQLCMD mode, execute the following:
  SET NOEXEC OFF; 
  */
  :setvar __IsSqlCmdEnabled "True"
  GO
  IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
      BEGIN
          PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
          SET NOEXEC ON;
      END
  
  
  GO
  USE [$(DatabaseName)];
  
  
  GO
  IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET ANSI_NULLS ON,
                  ANSI_PADDING ON,
                  ANSI_WARNINGS ON,
                  ARITHABORT ON,
                  CONCAT_NULL_YIELDS_NULL ON,
                  QUOTED_IDENTIFIER ON,
                  ANSI_NULL_DEFAULT ON,
                  CURSOR_DEFAULT LOCAL,
                  RECOVERY FULL 
              WITH ROLLBACK IMMEDIATE;
      END
  
  
  GO
  IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET PAGE_VERIFY NONE 
              WITH ROLLBACK IMMEDIATE;
      END
  
  
  GO
  ALTER DATABASE [$(DatabaseName)]
      SET TARGET_RECOVERY_TIME = 0 SECONDS 
      WITH ROLLBACK IMMEDIATE;
  
  
  GO
  IF EXISTS (SELECT 1
             FROM   [master].[dbo].[sysdatabases]
             WHERE  [name] = N'$(DatabaseName)')
      BEGIN
          ALTER DATABASE [$(DatabaseName)]
              SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO, OPERATION_MODE = READ_WRITE) 
              WITH ROLLBACK IMMEDIATE;
      END
  
  
  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)]					
  --------------------------------------------------------------------------------------
  */
  
  delete from tblStock
  delete from tblTransaction
  delete from tblUser
  GO
  
  GO
  PRINT N'Rename refactoring operation with key 7ce1f6b6-dd3f-485d-8c07-98d1287799be is skipped, element [dbo].[tblTransactions].[BuyOrSell] (SqlSimpleColumn) will not be renamed to Buy';
  
  
  GO
  PRINT N'Creating [dbo].[tblCrypto]...';
  
  
  GO
  CREATE TABLE [dbo].[tblCrypto] (
      [Id]           UNIQUEIDENTIFIER NOT NULL,
      [UserId]       UNIQUEIDENTIFIER NOT NULL,
      [Symbol]       NCHAR (100)      NOT NULL,
      [CurrentPrice] DECIMAL (18, 2)  NOT NULL,
      PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  PRINT N'Creating [dbo].[tblCryptoTran]...';
  
  
  GO
  CREATE TABLE [dbo].[tblCryptoTran] (
      [Id]        UNIQUEIDENTIFIER NOT NULL,
      [TransDate] DATE             NOT NULL,
      [Buy]       BIT              NOT NULL,
      [Quantity]  DECIMAL (18, 8)  NOT NULL,
      [Price]     DECIMAL (18, 2)  NOT NULL,
      [CryptoId]  UNIQUEIDENTIFIER NOT NULL,
      PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  PRINT N'Creating [dbo].[tblErrorLog]...';
  
  
  GO
  CREATE TABLE [dbo].[tblErrorLog] (
      [Id]       UNIQUEIDENTIFIER NOT NULL,
      [Message]  NCHAR (1000)     NOT NULL,
      [DateTime] DATETIME         NOT NULL,
      PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  PRINT N'Creating [dbo].[tblStock]...';
  
  
  GO
  CREATE TABLE [dbo].[tblStock] (
      [Id]                   UNIQUEIDENTIFIER NOT NULL,
      [UserId]               UNIQUEIDENTIFIER NOT NULL,
      [Ticker]               NCHAR (5)        NOT NULL,
      [CurrentPricePerShare] DECIMAL (10, 2)  NOT NULL,
      PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  PRINT N'Creating [dbo].[tblTransaction]...';
  
  
  GO
  CREATE TABLE [dbo].[tblTransaction] (
      [Id]                UNIQUEIDENTIFIER NOT NULL,
      [TransDate]         DATETIME         NULL,
      [Buy]               BIT              NULL,
      [Quantity]          INT              NULL,
      [PricePerSharePaid] DECIMAL (18, 2)  NULL,
      [StockId]           UNIQUEIDENTIFIER NULL,
      PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  PRINT N'Creating [dbo].[tblUser]...';
  
  
  GO
  CREATE TABLE [dbo].[tblUser] (
      [Id]        UNIQUEIDENTIFIER NOT NULL,
      [FirstName] NCHAR (50)       NULL,
      [LastName]  NCHAR (50)       NULL,
      [Email]     NCHAR (50)       NULL,
      [Password]  NCHAR (128)      NULL,
      PRIMARY KEY CLUSTERED ([Id] ASC)
  );
  
  
  GO
  PRINT N'Creating [dbo].[spGetTransactions]...';
  
  
  GO
  CREATE PROCEDURE [dbo].[spGetTransactions]
  AS
  	SELECT u.FirstName, s.Ticker, t.Buy, t.Quantity, t.PricePerSharePaid, t.TransDate from tblUser u
  	join tblStock s on u.Id = s.UserId
  	join tblTransaction t on t.StockId = s.Id
  	order by t.TransDate
  RETURN 0
  GO
  -- Refactoring step to update target server with deployed transaction logs
  
  IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
  BEGIN
      CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
      EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
  END
  GO
  IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = '7ce1f6b6-dd3f-485d-8c07-98d1287799be')
  INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('7ce1f6b6-dd3f-485d-8c07-98d1287799be')
  
  GO
  
  GO
  /*
  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)]					
  --------------------------------------------------------------------------------------
  */
  BEGIN
  	insert into [dbo].[tblUser] (Id, FirstName,LastName,Email,Password)
  	Values
  		(NEWID(), 'Alex', 'Star', 'admin', 'admin');
  
  END
  GO
  
  GO
  PRINT N'Update complete.';
  
  
  GO
  