Scribd
Upload
60 views5 pages

Database Script

The document contains the SQL code to create database tables for an online delivery application. It creates tables for Users, Couriers, Warehouses, Orders, and Locations, and defines relationships between the tables. It also includes code to add, remove, and modify columns in the tables over multiple migrations.

Uploaded by

sahoti
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
60 views5 pages

Database Script

The document contains the SQL code to create database tables for an online delivery application. It creates tables for Users, Couriers, Warehouses, Orders, and Locations, and defines relationships between the tables. It also includes code to add, remove, and modify columns in the tables over multiple migrations.

Uploaded by

sahoti
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 5

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL

BEGIN
CREATE TABLE [__EFMigrationsHistory] (
[MigrationId] nvarchar(150) NOT NULL,
[ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
END;
GO

BEGIN TRANSACTION;
GO

CREATE TABLE [Users] (


[Id] uniqueidentifier NOT NULL,
[Username] nvarchar(max) NOT NULL,
[PhoneNumber] nvarchar(max) NULL,
[CheckCurrency] nvarchar(max) NOT NULL,
[PasswordHash] nvarchar(max) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY ([Id])
);
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])


VALUES (N'20211117082841_Initial', N'5.0.12');
GO

COMMIT;
GO

BEGIN TRANSACTION;
GO

DECLARE @var0 sysname;


SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].
[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Users]') AND [c].[name] =
N'CheckCurrency');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Users] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Users] DROP COLUMN [CheckCurrency];
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])


VALUES (N'20211117085407_UpdateUser', N'5.0.12');
GO

COMMIT;
GO

BEGIN TRANSACTION;
GO

CREATE TABLE [Couriers] (


[Id] uniqueidentifier NOT NULL,
[Username] nvarchar(max) NOT NULL,
[FullName] nvarchar(max) NOT NULL,
[PhoneNumber] nvarchar(max) NULL,
[PasswordHash] nvarchar(max) NOT NULL,
[DeliveredOrdersCount] int NOT NULL,
[AverageRating] float NOT NULL,
[EarningsToday] decimal(18,2) NOT NULL,
[EarningsTotal] decimal(18,2) NOT NULL,
CONSTRAINT [PK_Couriers] PRIMARY KEY ([Id])
);
GO

CREATE TABLE [Warehouses] (


[Id] uniqueidentifier NOT NULL,
[Address] nvarchar(max) NOT NULL,
[PhoneNumber] nvarchar(max) NULL,
[Latitude] float NOT NULL,
[Longitude] float NOT NULL,
CONSTRAINT [PK_Warehouses] PRIMARY KEY ([Id])
);
GO

CREATE TABLE [Orders] (


[Id] uniqueidentifier NOT NULL,
[Status] nvarchar(max) NOT NULL,
[Details] nvarchar(max) NOT NULL,
[PaymentType] nvarchar(max) NOT NULL,
[TimeCreated] datetime2 NOT NULL,
[TimeDelived] datetime2 NULL,
[CourierEarnings] nvarchar(max) NOT NULL,
[CourierEarningsCurrency] nvarchar(max) NOT NULL,
[TotalCost] nvarchar(max) NOT NULL,
[TotalCostCurrency] nvarchar(max) NOT NULL,
[UserId] uniqueidentifier NOT NULL,
[WarehouseId] uniqueidentifier NOT NULL,
[CourierId] uniqueidentifier NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Orders_Couriers_CourierId] FOREIGN KEY ([CourierId]) REFERENCES
[Couriers] ([Id]) ON DELETE NO ACTION,
CONSTRAINT [FK_Orders_Users_UserId] FOREIGN KEY ([UserId]) REFERENCES [Users]
([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_Orders_Warehouses_WarehouseId] FOREIGN KEY ([WarehouseId])
REFERENCES [Warehouses] ([Id]) ON DELETE CASCADE
);
GO

CREATE INDEX [IX_Orders_CourierId] ON [Orders] ([CourierId]);


GO

CREATE INDEX [IX_Orders_UserId] ON [Orders] ([UserId]);


GO

CREATE INDEX [IX_Orders_WarehouseId] ON [Orders] ([WarehouseId]);


GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])


VALUES (N'20211117212946_ModelsCreated', N'5.0.12');
GO

COMMIT;
GO
BEGIN TRANSACTION;
GO

ALTER TABLE [Warehouses] ADD [LocationId] uniqueidentifier NULL;


GO

ALTER TABLE [Users] ADD [DefaultLocationId] uniqueidentifier NULL;


GO

ALTER TABLE [Orders] ADD [DeliveryLocationId] uniqueidentifier NULL;


GO

ALTER TABLE [Couriers] ADD [LastLocationId] uniqueidentifier NULL;


GO

CREATE TABLE [Location] (


[Id] uniqueidentifier NOT NULL,
[Longitude] float NOT NULL,
[Latitude] float NOT NULL,
[Altitude] float NULL,
[Speed] float NULL,
[Course] float NULL,
[AltitudeAccuracy] float NULL,
[DateTime] datetime2 NULL,
CONSTRAINT [PK_Location] PRIMARY KEY ([Id])
);
GO

CREATE INDEX [IX_Warehouses_LocationId] ON [Warehouses] ([LocationId]);


GO

CREATE INDEX [IX_Users_DefaultLocationId] ON [Users] ([DefaultLocationId]);


GO

CREATE INDEX [IX_Orders_DeliveryLocationId] ON [Orders] ([DeliveryLocationId]);


GO

CREATE INDEX [IX_Couriers_LastLocationId] ON [Couriers] ([LastLocationId]);


GO

ALTER TABLE [Couriers] ADD CONSTRAINT [FK_Couriers_Location_LastLocationId] FOREIGN


KEY ([LastLocationId]) REFERENCES [Location] ([Id]) ON DELETE NO ACTION;
GO

ALTER TABLE [Orders] ADD CONSTRAINT [FK_Orders_Location_DeliveryLocationId] FOREIGN


KEY ([DeliveryLocationId]) REFERENCES [Location] ([Id]) ON DELETE NO ACTION;
GO

ALTER TABLE [Users] ADD CONSTRAINT [FK_Users_Location_DefaultLocationId] FOREIGN


KEY ([DefaultLocationId]) REFERENCES [Location] ([Id]) ON DELETE NO ACTION;
GO

ALTER TABLE [Warehouses] ADD CONSTRAINT [FK_Warehouses_Location_LocationId] FOREIGN


KEY ([LocationId]) REFERENCES [Location] ([Id]) ON DELETE NO ACTION;
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])


VALUES (N'20211121133256_Locations', N'5.0.12');
GO
COMMIT;
GO

BEGIN TRANSACTION;
GO

DECLARE @var1 sysname;


SELECT @var1 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].
[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Orders]') AND [c].[name] =
N'TotalCost');
IF @var1 IS NOT NULL EXEC(N'ALTER TABLE [Orders] DROP CONSTRAINT [' + @var1 +
'];');
ALTER TABLE [Orders] ALTER COLUMN [TotalCost] decimal(12,4) NOT NULL;
GO

DECLARE @var2 sysname;


SELECT @var2 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].
[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Orders]') AND [c].[name] =
N'CourierEarnings');
IF @var2 IS NOT NULL EXEC(N'ALTER TABLE [Orders] DROP CONSTRAINT [' + @var2 +
'];');
ALTER TABLE [Orders] ALTER COLUMN [CourierEarnings] decimal(12,4) NOT NULL;
GO

DECLARE @var3 sysname;


SELECT @var3 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].
[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Couriers]') AND [c].[name] =
N'EarningsTotal');
IF @var3 IS NOT NULL EXEC(N'ALTER TABLE [Couriers] DROP CONSTRAINT [' + @var3 +
'];');
ALTER TABLE [Couriers] ALTER COLUMN [EarningsTotal] decimal(12,4) NOT NULL;
GO

DECLARE @var4 sysname;


SELECT @var4 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].
[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Couriers]') AND [c].[name] =
N'EarningsToday');
IF @var4 IS NOT NULL EXEC(N'ALTER TABLE [Couriers] DROP CONSTRAINT [' + @var4 +
'];');
ALTER TABLE [Couriers] ALTER COLUMN [EarningsToday] decimal(12,4) NOT NULL;
GO

ALTER TABLE [Couriers] ADD [EarningsCurrency] nvarchar(max) NOT NULL DEFAULT N'';
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])


VALUES (N'20211121134903_DecimalFixes', N'5.0.12');
GO

COMMIT;
GO

BEGIN TRANSACTION;
GO

DECLARE @var5 sysname;


SELECT @var5 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].
[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Warehouses]') AND [c].[name] =
N'Latitude');
IF @var5 IS NOT NULL EXEC(N'ALTER TABLE [Warehouses] DROP CONSTRAINT [' + @var5 +
'];');
ALTER TABLE [Warehouses] DROP COLUMN [Latitude];
GO

DECLARE @var6 sysname;


SELECT @var6 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].
[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Warehouses]') AND [c].[name] =
N'Longitude');
IF @var6 IS NOT NULL EXEC(N'ALTER TABLE [Warehouses] DROP CONSTRAINT [' + @var6 +
'];');
ALTER TABLE [Warehouses] DROP COLUMN [Longitude];
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])


VALUES (N'20211121150537_WarehouseLocation', N'5.0.12');
GO

COMMIT;
GO

You might also like