-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: D:/Liquibase/databaseChangeLog/dbchangelog.xml
-- Ran at: 19/05/16 10:56 PM
-- Against: liquibase@jdbc:sqlserver://xxx.xxx.xxx.xxx:1433;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=liquibase_qa;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite;
-- Liquibase version: 3.4.2
-- *********************************************************************

USE [liquibase_qa];
GO

-- Create Database Lock Table
CREATE TABLE [dbo].[DATABASECHANGELOGLOCK] ([ID] [int] NOT NULL, [LOCKED] [bit] NOT NULL, [LOCKGRANTED] [datetime2](3) NULL, [LOCKEDBY] [nvarchar](255) NULL, CONSTRAINT [PK_DATABASECHANGELOGLOCK] PRIMARY KEY ([ID]))
GO

-- Initialize Database Lock Table
DELETE FROM [dbo].[DATABASECHANGELOGLOCK]
GO

INSERT INTO [dbo].[DATABASECHANGELOGLOCK] ([ID], [LOCKED]) VALUES (1, 0)
GO

-- Lock Database
UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 1, [LOCKEDBY] = 'LIQUIBASE-PC (XXX.XXX.XX.X)', [LOCKGRANTED] = '2016-05-19T22:56:03.433' WHERE [ID] = 1 AND [LOCKED] = 0
GO

-- Create Database Change Log Table
CREATE TABLE [dbo].[DATABASECHANGELOG] ([ID] [nvarchar](255) NOT NULL, [AUTHOR] [nvarchar](255) NOT NULL, [FILENAME] [nvarchar](255) NOT NULL, [DATEEXECUTED] [datetime2](3) NOT NULL, [ORDEREXECUTED] [int] NOT NULL, [EXECTYPE] [nvarchar](10) NOT NULL, [MD5SUM] [nvarchar](35) NULL, [DESCRIPTION] [nvarchar](255) NULL, [COMMENTS] [nvarchar](255) NULL, [TAG] [nvarchar](255) NULL, [LIQUIBASE] [nvarchar](20) NULL, [CONTEXTS] [nvarchar](255) NULL, [LABELS] [nvarchar](255) NULL)
GO

-- Changeset D:/Liquibase/databaseChangeLog/dbchangelog.xml::1::sadequl_hussain
CREATE TABLE [dbo].[customer] ([id] [int] NOT NULL, [customer_name] [varchar](50) NOT NULL, [active] [bit] CONSTRAINT DF_customer_active DEFAULT 1 NULL, CONSTRAINT [PK_CUSTOMER] PRIMARY KEY ([id]))
GO

INSERT INTO [dbo].[DATABASECHANGELOG] ([ID], [AUTHOR], [FILENAME], [DATEEXECUTED], [ORDEREXECUTED], [MD5SUM], [DESCRIPTION], [COMMENTS], [EXECTYPE], [CONTEXTS], [LABELS], [LIQUIBASE]) VALUES ('1', 'sadequl_hussain', 'D:/Liquibase/databaseChangeLog/dbchangelog.xml', GETDATE(), 1, '7:d44c40fae0486e89163890a5c288e7f3', 'createTable', '', 'EXECUTED', NULL, NULL, '3.4.2')
GO

-- Changeset D:/Liquibase/databaseChangeLog/dbchangelog.xml::2::sadequl_hussain
ALTER TABLE [dbo].[customer] ADD [join_date] [datetime]
GO

INSERT INTO [dbo].[DATABASECHANGELOG] ([ID], [AUTHOR], [FILENAME], [DATEEXECUTED], [ORDEREXECUTED], [MD5SUM], [DESCRIPTION], [COMMENTS], [EXECTYPE], [CONTEXTS], [LABELS], [LIQUIBASE]) VALUES ('2', 'sadequl_hussain', 'D:/Liquibase/databaseChangeLog/dbchangelog.xml', GETDATE(), 2, '7:28893b40561eddb52a42553c86dc7431', 'addColumn', '', 'EXECUTED', NULL, NULL, '3.4.2')
GO

-- Changeset D:/Liquibase/databaseChangeLog/dbchangelog.xml::3::sadequl_hussain
CREATE TABLE [dbo].[products] ([product_id] [int] NOT NULL, [product_name] [varchar](50) NOT NULL, [active] [bit] CONSTRAINT DF_products_active DEFAULT 1 NULL, CONSTRAINT [PK_PRODUCTS] PRIMARY KEY ([product_id]))
GO

INSERT INTO [dbo].[DATABASECHANGELOG] ([ID], [AUTHOR], [FILENAME], [DATEEXECUTED], [ORDEREXECUTED], [MD5SUM], [DESCRIPTION], [COMMENTS], [EXECTYPE], [CONTEXTS], [LABELS], [LIQUIBASE]) VALUES ('3', 'sadequl_hussain', 'D:/Liquibase/databaseChangeLog/dbchangelog.xml', GETDATE(), 3, '7:9ec89a43d23491656594a9731abbc6df', 'createTable', '', 'EXECUTED', NULL, NULL, '3.4.2')
GO

-- Changeset D:/Liquibase/databaseChangeLog/dbchangelog.xml::4::sadequl_hussain
-- The orders table should be created after the customer and products table.
CREATE TABLE [dbo].[orders] ([order_id] [int] NOT NULL, [customer_id] [int] NOT NULL, [product_id] [int] NOT NULL, [order_date] [datetime] NOT NULL, CONSTRAINT [PK_ORDERS] PRIMARY KEY ([order_id]))
GO

INSERT INTO [dbo].[DATABASECHANGELOG] ([ID], [AUTHOR], [FILENAME], [DATEEXECUTED], [ORDEREXECUTED], [MD5SUM], [DESCRIPTION], [COMMENTS], [EXECTYPE], [CONTEXTS], [LABELS], [LIQUIBASE]) VALUES ('4', 'sadequl_hussain', 'D:/Liquibase/databaseChangeLog/dbchangelog.xml', GETDATE(), 4, '7:bcfd3d34664975089dcfe25edbd71b25', 'createTable', 'The orders table should be created after the customer and products table.', 'EXECUTED', NULL, NULL, '3.4.2')
GO

-- Changeset D:/Liquibase/databaseChangeLog/dbchangelog.xml::5::sadequl_hussain
-- The orders table should be created after the customer and products table.
ALTER TABLE [dbo].[orders] ADD CONSTRAINT [FK_CUSTOMER_ID] FOREIGN KEY ([customer_id]) REFERENCES [dbo].[customer] ([id])
GO

ALTER TABLE [dbo].[orders] ADD CONSTRAINT [FK_PRODUCT_ID] FOREIGN KEY ([product_id]) REFERENCES [dbo].[products] ([product_id])
GO

INSERT INTO [dbo].[DATABASECHANGELOG] ([ID], [AUTHOR], [FILENAME], [DATEEXECUTED], [ORDEREXECUTED], [MD5SUM], [DESCRIPTION], [COMMENTS], [EXECTYPE], [CONTEXTS], [LABELS], [LIQUIBASE]) VALUES ('5', 'sadequl_hussain', 'D:/Liquibase/databaseChangeLog/dbchangelog.xml', GETDATE(), 5, '7:bc0737f82bab6a950891ff94b70cf01f', 'addForeignKeyConstraint (x2)', 'The orders table should be created after the customer and products table.', 'EXECUTED', NULL, NULL, '3.4.2')
GO

-- Release Database Lock
UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 0, [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL WHERE [ID] = 1
GO