Changes between Initial Version and Version 1 of Phase0

Timestamp:

09/04/25 00:27:45 (8 weeks ago)

Author:

213280

Comment:

Creation of phase0

Phase0

@@ +1 @@
+== Креирање на табели ==
+
+{{{
+#!sql
+CREATE TABLE [dbo].[Business]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [Name] NVARCHAR (100) NOT NULL,
+    [Address] NVARCHAR (200) NOT NULL,
+    [Rating] REAL NOT NULL,
+    [PhoneNumber] NVARCHAR(15) NOT NULL,
+    [BackGroundImage] VARBINARY(MAX) NULL,
+    [Logo] VARBINARY(MAX) NULL,
+    [IsOpen] BIT NOT NULL,
+    [IsAvailable] BIT NOT NULL,
+    [Type] INT NOT NULL,
+    [OwnerId] INT NOT NULL,
+    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [DeletedAt] DATETIME2 NULL,
+    [IsDeleted] BIT NOT NULL DEFAULT 0,
+    CONSTRAINT [PK_Business] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_Business_User_OwnerId] FOREIGN KEY (OwnerId) REFERENCES [User]([Id]) ON DELETE NO ACTION,
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[FoodCategory]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [Name] NVARCHAR (100) NOT NULL,
+    [IsAvailable] BIT NOT NULL,
+    [BusinessId] INT NOT NULL,
+    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [DeletedAt] DATETIME2 NULL,
+    [IsDeleted] BIT NOT NULL DEFAULT 0,
+    CONSTRAINT [PK_FoodCategory] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_FoodCategory_Business_BusinessId] FOREIGN KEY (BusinessId) REFERENCES [Business]([Id]) ON DELETE CASCADE,
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[FoodItem]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [Name] NVARCHAR (100) NOT NULL,
+    [Price] INT NOT NULL,
+    [Description] NVARCHAR (300) NULL,
+    [IsAvailable] BIT NOT NULL,
+    [FoodItemImage] VARBINARY(MAX),
+    [FoodCategoryId] INT NOT NULL,
+    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [DeletedAt] DATETIME2 NULL,
+    [IsDeleted] BIT NOT NULL DEFAULT 0,
+    CONSTRAINT [PK_FoodItem] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_FoodItem_FoodCategory_FoodCategoryId] FOREIGN KEY (FoodCategoryId) REFERENCES [FoodCategory]([Id]) ON DELETE CASCADE,
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[ItemInOrder]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [Quantity] INT NOT NULL,
+    [FoodItemId] INT NOT NULL,
+    [OrderId] INT NOT NULL,
+    CONSTRAINT [PK_ItemInOrder] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_ItemInOrder_FoodItem_FoodItemId] FOREIGN KEY (FoodItemId) REFERENCES [FoodItem]([Id]) ON DELETE NO ACTION,
+    CONSTRAINT [FK_ItemInOrder_Order_OrderId] FOREIGN KEY (OrderId) REFERENCES [Order]([Id]) ON DELETE CASCADE,
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[ItemInShoppingCart]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [Quantity] INT NOT NULL,
+    [FoodItemId] INT NOT NULL,
+    [ShoppingCartId] INT NOT NULL,
+    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [DeletedAt] DATETIME2 NULL,
+    [IsDeleted] BIT NOT NULL DEFAULT 0,
+    CONSTRAINT [PK_ItemInShoppingCart] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_ItemInShoppingCart_FoodItem_FoodItemId] FOREIGN KEY (FoodItemId) REFERENCES [FoodItem]([Id]) ON DELETE NO ACTION,
+    CONSTRAINT [FK_ItemInShoppingCart_ShoppingCard_ShoppingCartId] FOREIGN KEY (ShoppingCartId) REFERENCES [ShoppingCart]([Id]) ON DELETE CASCADE,
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[Order]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [OrderGlobalStatus] INT NOT NULL,
+    [TotalPrice] DECIMAL NOT NULL,
+    [DeliveryFee] DECIMAL NOT NULL,
+    [CustomerId] INT NOT NULL,
+    [DeliveryDriverId] INT NULL,
+    [BusinessId] INT NOT NULL,
+    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [DeletedAt] DATETIME2 NULL,
+    [IsDeleted] BIT NOT NULL DEFAULT 0,
+    CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_Order_User_CustomerId] FOREIGN KEY (CustomerId) REFERENCES [User]([Id]) ON DELETE NO ACTION,
+    CONSTRAINT [FK_Order_User_DeliveryDriverId] FOREIGN KEY (DeliveryDriverId) REFERENCES [User]([Id]) ON DELETE NO ACTION,
+    CONSTRAINT [FK_Order_Business_BusinessId] FOREIGN KEY (BusinessId) REFERENCES [Business]([Id]) ON DELETE NO ACTION,
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[OrderHistory]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [TimeStamp] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [OrderGlobalStatus] INT NOT NULL,
+    [OrderId] INT NOT NULL,
+    [UpdaterUserId] INT NOT NULL,
+    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [DeletedAt] DATETIME2 NULL,
+    [IsDeleted] BIT NOT NULL DEFAULT 0,
+    CONSTRAINT [PK_OrderHistory] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_OrderHistory_Order_OrderId] FOREIGN KEY (OrderId) REFERENCES [Order]([Id]) ON DELETE CASCADE,
+    CONSTRAINT [FK_OrderHistory_User_UpdaterUserId] FOREIGN KEY (UpdaterUserId) REFERENCES [User]([Id]) ON DELETE NO ACTION,
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[Role]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [Name] NVARCHAR (50) NOT NULL,
+    CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED ([Id] ASC),
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[ShoppingCart]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [OwnerId] INT NOT NULL,
+    CONSTRAINT [PK_ShoppingCart] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_ShoppingCart_User_OwnerId] FOREIGN KEY (OwnerId) REFERENCES [User]([Id]) ON DELETE CASCADE,
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[Token]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [AccessToken] NVARCHAR (MAX) NOT NULL,
+    [RefreshToken] NVARCHAR (MAX) NOT NULL,
+    [AccessTokenExpirationDate] DATETIME2 NOT NULL, 
+    [RefreshTokenExpirationDate] DATETIME2 NOT NULL,
+    [UserId] INT NOT NULL,
+    CONSTRAINT [PK_Token] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_Token_User_UserId] FOREIGN KEY (UserId) REFERENCES [User]([Id]) ON DELETE CASCADE,
+);
+}}}
+
+{{{
+#!sql
+CREATE TABLE [dbo].[User]
+(
+    [Id] INT IDENTITY (1, 1) NOT NULL,
+    [Email] NVARCHAR (50) NOT NULL UNIQUE,
+    [PasswordHash] NVARCHAR (200) NOT NULL,
+    [FirstName] NVARCHAR (100) NOT NULL,
+    [LastName] NVARCHAR (100) NOT NULL,
+    [RoleId] INT NOT NULL,
+    [CreatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [UpdatedAt] DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
+    [DeletedAt] DATETIME2 NULL,
+    [IsDeleted] BIT NOT NULL DEFAULT 0,
+    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Id] ASC),
+    CONSTRAINT [FK_User_Role_RoleId] FOREIGN KEY (RoleId) REFERENCES [Role]([Id]) ON DELETE CASCADE,
+);
+}}}
+
+----
+
+== Креирање на тест податоци ==
+
+{{{
+#!sql
+DECLARE @RoleId INT = 1;
+DECLARE @NumberOfUsers INT = 1000; 
+DECLARE @InsertedUsers INT = 0;
+DECLARE @BatchSize INT = 1000;
+
+WHILE @InsertedUsers < @NumberOfUsers
+BEGIN
+    INSERT INTO [dbo].[User] 
+        (Email, PasswordHash, FirstName, LastName, RoleId, CreatedAt, UpdatedAt, DeletedAt, IsDeleted)
+    SELECT TOP (@BatchSize)
+        CONCAT('user', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers, '@example.com'),
+        'AQAAAAIAAYagAAAAEDiHqhqrUlhjMSqG+Wm9CP10wIioySTDOI1PEgvzV5uIoRhJAXdrXLLqQ6mSsw3lug==',
+        CONCAT('FirstName', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers),
+        CONCAT('LastName', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers),
+        @RoleId,
+        DATEADD(SECOND, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers, GETUTCDATE()),
+        DATEADD(SECOND, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + @InsertedUsers, GETUTCDATE()),
+        NULL,
+        0
+    FROM sys.objects a CROSS JOIN sys.objects b;
+
+    SET @InsertedUsers = @InsertedUsers + @BatchSize;
+END;
+}}}
+
+{{{
+#!sql
+DECLARE @NumberOfUsers INT = 10000;
+DECLARE @NumberOfOrders INT = 20000000;
+DECLARE @InsertedOrders INT = 0;
+DECLARE @BatchSize INT = 20000;
+DECLARE @StartDate DATETIME2 = '2021-01-01';
+DECLARE @EndDate DATETIME2 = '2024-12-31';
+
+WHILE @InsertedOrders < @NumberOfOrders
+BEGIN
+    INSERT INTO [dbo].[Order]
+        (OrderGlobalStatus, TotalPrice, DeliveryFee, CustomerId, DeliveryDriverId, BusinessId, CreatedAt, UpdatedAt, DeletedAt, IsDeleted)
+    SELECT TOP (@BatchSize)
+        CASE WHEN (ABS(CHECKSUM(NEWID())) % 10) = 0 THEN 1 ELSE (ABS(CHECKSUM(NEWID())) % 5) + 2 END,
+        CAST((ABS(CHECKSUM(NEWID())) % 9000 + 1000) / 100.0 AS DECIMAL(10,2)),
+        CAST((ABS(CHECKSUM(NEWID())) % 800 + 200) / 100.0 AS DECIMAL(10,2)),
+        (ABS(CHECKSUM(NEWID())) % @NumberOfUsers) + 1,
+        CASE WHEN (ABS(CHECKSUM(NEWID())) % 10) = 0 THEN NULL ELSE (ABS(CHECKSUM(NEWID())) % @NumberOfUsers) + 1 END,
+        (ABS(CHECKSUM(NEWID())) % 3) + 1,
+        DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % DATEDIFF(SECOND, @StartDate, @EndDate), @StartDate),
+        DATEADD(MINUTE, ABS(CHECKSUM(NEWID())) % 121, DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % DATEDIFF(SECOND, @StartDate, @EndDate), @StartDate)),
+        NULL,
+        0
+    FROM sys.objects a CROSS JOIN sys.objects b;
+
+    SET @InsertedOrders = @InsertedOrders + @BatchSize;
+        PRINT CONCAT('Orders inserted: ', @InsertedOrders);
+END;
+}}}
+
+{{{
+#!sql
+-- ========================================
+-- Insert ItemInOrder (batch)
+-- ========================================
+DECLARE @InsertedItems INT = 0;
+DECLARE @NumberOfItemsInOrder INT = 40000000
+DECLARE @NumberOfOrders INT = 32980003
+DECLARE @BatchSize INT = 20000
+
+WHILE @InsertedItems < @NumberOfItemsInOrder
+BEGIN
+    INSERT INTO [dbo].[ItemInOrder]
+        (Quantity, FoodItemId, OrderId)
+    SELECT TOP (@BatchSize)
+
+                -- Quantity between 1 and 5
+                (ABS(CHECKSUM(NEWID())) % 5) + 1 AS Quantity,
+
+                -- FoodItemId only between 1 and 17
+        (ABS(CHECKSUM(NEWID())) % 17) + 1 as FoodItemId,
+
+                -- Attach to random existing order
+        (ABS(CHECKSUM(NEWID())) % @NumberOfOrders) + 1 AS OrderId
+    FROM sys.objects a CROSS JOIN sys.objects b;
+
+    SET @InsertedItems = @InsertedItems + @BatchSize;
+    PRINT CONCAT('ItemInOrder inserted: ', @InsertedItems);
+END
+}}}
+
+{{{
+#!sql
+
+-- ========================================
+-- Parameters
+-- ========================================
+
+DECLARE @NumberOfOrderHistory INT = 15000000; -- change as needed
+DECLARE @NumberOfUsers INT = 1000; 
+DECLARE @NumberOfOrders INT = 10000000
+DECLARE @InsertedHistory INT = 0;
+DECLARE @BatchSize INT = 20000
+
+
+-- ========================================
+-- Insert OrderHistory (batch)
+-- ========================================
+
+
+WHILE @InsertedHistory < @NumberOfOrderHistory
+BEGIN
+    INSERT INTO [dbo].[OrderHistory]
+        (TimeStamp, OrderGlobalStatus, OrderId, UpdaterUserId, CreatedAt, UpdatedAt, DeletedAt, IsDeleted)
+    SELECT TOP (@BatchSize)
+        DATEADD(MINUTE, -ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), GETUTCDATE()),
+
+                -- Random status
+                (ABS(CHECKSUM(NEWID())) % 6) + 1 AS OrderGlobalStatus,
+                
+                -- Random order id (must exist from Orders table)
+                (ABS(CHECKSUM(NEWID())) % @NumberOfOrders) + 1 AS OrderId,
+                
+                -- Random updater user
+                (ABS(CHECKSUM(NEWID())) % @NumberOfUsers) + 1 AS UpdaterUserId,  
+
+        GETUTCDATE(),
+        GETUTCDATE(),
+        NULL,
+        0
+    FROM sys.objects a CROSS JOIN sys.objects b;
+
+    SET @InsertedHistory = @InsertedHistory + @@ROWCOUNT;
+    PRINT CONCAT('OrderHistory inserted: ', @InsertedHistory);
+END
+
+
+}}}
+
+
+
+