newsbot-api/internal/database/migrations/20240425083756_init.sql

116 lines
3.1 KiB
MySQL
Raw Permalink Normal View History

-- +goose Up
-- +goose StatementBegin
SELECT 'up SQL query';
CREATE TABLE Articles (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
CreatedAt DATETIME NOT NULL,
UpdatedAt DATETIME NOT NULL,
2024-04-28 10:02:57 -07:00
DeletedAt DATETIME NOT NULL,
SourceId NUMBER NOT NULL,
Tags TEXT NOT NULL,
Title TEXT NOT NULL,
Url TEXT NOT NULL,
PubDate DATETIME NOT NULL,
IsVideo TEXT NOT NULL,
ThumbnailUrl TEXT NOT NULL,
Description TEXT NOT NULL,
AuthorName TEXT NOT NULL,
AuthorImageUrl TEXT NOT NULL
);
CREATE Table DiscordWebHooks (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
CreatedAt DATETIME NOT NULL,
UpdatedAt DATETIME NOT NULL,
2024-04-28 10:02:57 -07:00
DeletedAt DATETIME NOT NULL,
UserID INTEGER NOT NULL,
Url TEXT NOT NULL, -- Webhook Url
2024-04-28 10:02:57 -07:00
Server TEXT NOT NULL, -- Defines the server its bound it. Used for reference
Channel TEXT NOT NULL, -- Defines the channel its bound to. Used for reference
Enabled BOOLEAN NOT NULL
);
CREATE Table Icons (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
CreatedAt DATETIME NOT NULL,
UpdatedAt DATETIME NOT NULL,
DeletedAt DATETIME,
FileName TEXT NOT NULL,
Site TEXT NOT NULL
);
Create Table Settings (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
CreatedAt DATETIME NOT NULL,
UpdatedAt DATETIME NOT NULL,
DeletedAt DATETIME,
Key TEXT NOT NULL, -- How you search for a entry
Value TEXT NOT NULL, -- The value for one
Options TEXT -- any notes about the entry
);
CREATE Table Sources (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
CreatedAt DATETIME NOT NULL,
UpdatedAt DATETIME NOT NULL,
2024-04-28 10:02:57 -07:00
DeletedAt DATETIME NOT NULL,
DisplayName TEXT NOT NULL, -- Vanity name
Source TEXT NOT NULL, -- Defines the service that will use this record. IE reddit or youtube
Enabled BOOLEAN NOT NULL,
Url TEXT NOT NULL,
Tags TEXT NOT NULL
);
CREATE TABLE UserSourceSubscriptions (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
CreatedAt DATETIME NOT NULL,
UpdatedAt DATETIME NOT NULL,
DeletedAt DATETIME NOT NULL,
UserID NUMBER NOT NULL,
SourceID NUMBER NOT NULL
);
CREATE TABLE AlertDiscord (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
CreatedAt DATETIME NOT NULL,
UpdatedAt DATETIME NOT NULL,
DeletedAt DATETIME NOT NULL,
UserID NUMBER NOT NULL,
SourceID NUMBER NOT NULL,
DiscordWebHookID NUMBER NOT NULL
);
CREATE TABLE Users (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
CreatedAt DATETIME NOT NULL,
UpdatedAt DATETIME NOT NULL,
DeletedAt DATETIME NOT NULL,
Name TEXT NOT NULL,
Hash TEXT NOT NULL,
Scopes TEXT NOT NULL
);
CREATE TABLE RefreshTokens (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
CreatedAt DATETIME NOT NULL,
UpdatedAt DATETIME NOT NULL,
DeletedAt DATETIME NOT NULL,
Username TEXT NOT NULL,
Token TEXT NOT NULL
);
-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
DROP TABLE AlertDiscord;
Drop Table Articles;
Drop Table DiscordWebHooks;
Drop Table Icons;
DROP TABLE RefreshTokens;
Drop Table Sources;
DROP TABLE Users;
DROP TABLE UserSourceSubscriptions;
-- +goose StatementEnd