\CayciV2Bot\db.sql

Return Back
CREATE DATABASE CUSTOM_DB
GO

USE CUSTOM_DB
GO

IF NOT EXISTS
(SELECT name
FROM master.sys.server_principals
WHERE name = 'CUSTOM_ADMIN')
BEGIN
CREATE LOGIN CUSTOM_ADMIN WITH PASSWORD = 'CUSTOM_PASSWORD', DEFAULT_DATABASE = CUSTOM_DB
CREATE USER CUSTOM_ADMIN FOR LOGIN CUSTOM_ADMIN
EXEC sp_addrolemember N'db_owner', N'CUSTOM_ADMIN'
END
GO

DROP TABLE AlbionPlayerDiscordGuild
DROP TABLE AlbionPlayer
DROP TABLE Log
DROP TABLE Music
DROP TABLE DiscordUser
DROP TABLE DiscordChannel
DROP TABLE DiscordGuild

CREATE TABLE DiscordUser
(
Id INT PRIMARY KEY IDENTITY(1,1),
Username NVARCHAR(256) NOT NULL,
Discriminator NVARCHAR(16) NOT NULL,
DiscordId DECIMAL(24,0) NOT NULL,
JoinDate DATETIME NOT NULL,
CreationDate DATETIME NOT NULL,
UniqueId UNIQUEIDENTIFIER NOT NULL,
)
GO


CREATE TABLE DiscordGuild
(
Id INT PRIMARY KEY IDENTITY(1,1),
"Name" NVARCHAR(256) NOT NULL,
"Description" NVARCHAR(2048),
DiscordId DECIMAL(24,0) NOT NULL,
OwnerDiscordId DECIMAL(24,0) NOT NULL,
CreationDate DATETIME NOT NULL,
UniqueId UNIQUEIDENTIFIER NOT NULL
)
GO

CREATE TABLE DiscordChannel
(
Id INT PRIMARY KEY IDENTITY(1,1),
"Name" NVARCHAR(256) NOT NULL,
DiscordId DECIMAL(24,0) NOT NULL,
"Type" NVARCHAR(64) NOT NULL,
CreationDate DATETIME NOT NULL,
UniqueId UNIQUEIDENTIFIER NOT NULL
)
GO

CREATE TABLE Music
(
Id INT PRIMARY KEY IDENTITY(1,1),
Title NVARCHAR(1024) NOT NULL,
VideoFullName NVARCHAR(1024) NOT NULL,
Author NVARCHAR(1024),
Duration INT,
"URL" NVARCHAR(1024) NOT NULL,
CreationDate DATETIME NOT NULL,
UniqueId UNIQUEIDENTIFIER NOT NULL
)
GO

CREATE TABLE Log
(
Id INT PRIMARY KEY IDENTITY(1,1),
UserId INT FOREIGN KEY REFERENCES DiscordUser(Id) NOT NULL,
GuildId INT FOREIGN KEY REFERENCES DiscordGuild (Id) NOT NULL,
ChannelId INT FOREIGN KEY REFERENCES DiscordChannel(Id) NOT NULL,
MusicId INT FOREIGN KEY REFERENCES Music (Id),
Operation NVARCHAR(1024),
"Message" NVARCHAR(1024),
"TimeStamp" DATETIME NOT NULL,
)
GO

CREATE TABLE AlbionPlayer
(
Id INT PRIMARY KEY IDENTITY(1,1),
AlbionId NVARCHAR(256) NOT NULL,
"Name" NVARCHAR(64) NOT NULL,
CreationDate DATETIME NOT NULL
)
GO

CREATE TABLE AlbionPlayerDiscordGuild
(
Id INT PRIMARY KEY IDENTITY(1,1),
AlbionPlayerId INT FOREIGN KEY REFERENCES AlbionPlayer(Id) NOT NULL,
DiscordGuildId INT FOREIGN KEY REFERENCES DiscordGuild(Id) NOT NULL,
DiscordChannelId INT FOREIGN KEY REFERENCES DiscordChannel(Id) NOT NULL,
IsActive BIT NOT NULL
)
GO