mirror of
https://github.com/MCMi460/3DS-RPC.git
synced 2025-06-18 13:35:34 -04:00
1fa2402d0a
Minimal changes have been to flatten the `server/` subdirectory to the root of the repository. More testing will be required.
128 lines
3.3 KiB
SQL
128 lines
3.3 KiB
SQL
-- This migrates the old, handwritten database to the first revision managed by Alembic.
|
|
-- Please run `flask db upgrade` as soon as possible!
|
|
|
|
------------
|
|
-- Config --
|
|
------------
|
|
|
|
-- As config now uses a DATETIME, we will delete the old table entirely.
|
|
DROP TABLE config;
|
|
|
|
CREATE TABLE config (
|
|
network INTEGER NOT NULL,
|
|
backend_uptime DATETIME,
|
|
PRIMARY KEY (network)
|
|
);
|
|
|
|
-- Provide default uptime for our two defined networks.
|
|
INSERT INTO config VALUES (0, NULL);
|
|
INSERT INTO config VALUES (1, NULL);
|
|
|
|
-------------
|
|
-- Friends --
|
|
-------------
|
|
CREATE TABLE new_friends (
|
|
friend_code VARCHAR NOT NULL,
|
|
network INTEGER NOT NULL,
|
|
online BOOLEAN NOT NULL,
|
|
title_id VARCHAR NOT NULL,
|
|
upd_id VARCHAR NOT NULL,
|
|
last_accessed BIGINT NOT NULL,
|
|
account_creation BIGINT NOT NULL,
|
|
username VARCHAR,
|
|
message VARCHAR,
|
|
mii VARCHAR,
|
|
joinable BOOLEAN,
|
|
game_description VARCHAR,
|
|
last_online BIGINT NOT NULL,
|
|
favorite_game BIGINT NOT NULL,
|
|
PRIMARY KEY (friend_code),
|
|
UNIQUE (friend_code)
|
|
);
|
|
|
|
-- The primary difference between these two tables are the column names.
|
|
-- `jeuFavori` was renamed to its English version, `favorite_game`.
|
|
-- `friend_code` is no longer unique.
|
|
INSERT INTO new_friends
|
|
(friend_code, network, online, title_id,
|
|
upd_id, last_accessed, account_creation, username,
|
|
message, mii, joinable, game_description,
|
|
last_online, favorite_game)
|
|
SELECT
|
|
friendCode, network, online, titleID,
|
|
updID, lastAccessed, accountCreation,
|
|
username, message, mii, joinable,
|
|
gameDescription, lastOnline, jeuFavori
|
|
FROM friends;
|
|
|
|
-- Swap out.
|
|
DROP TABLE friends;
|
|
ALTER TABLE new_friends RENAME TO friends;
|
|
|
|
--------------------
|
|
-- discordFriends --
|
|
--------------------
|
|
|
|
-- This table was renamed to discordFriends.
|
|
CREATE TABLE discord_friends (
|
|
id BIGINT NOT NULL,
|
|
friend_code VARCHAR NOT NULL,
|
|
network INTEGER NOT NULL,
|
|
active BOOLEAN NOT NULL,
|
|
PRIMARY KEY (id, friend_code)
|
|
);
|
|
|
|
-- Note that we use `friend_code` instead of `friendCode`.
|
|
INSERT INTO discord_friends
|
|
(id, friend_code, network, active)
|
|
SELECT
|
|
ID, friendCode, network, active
|
|
FROM discordFriends;
|
|
|
|
-- Remove our old, poorly-named table.
|
|
DROP TABLE discordFriends;
|
|
|
|
-------------
|
|
-- discord --
|
|
-------------
|
|
CREATE TABLE new_discord (
|
|
id BIGINT NOT NULL,
|
|
refresh VARCHAR NOT NULL,
|
|
bearer VARCHAR NOT NULL,
|
|
session VARCHAR,
|
|
token VARCHAR,
|
|
last_accessed BIGINT NOT NULL,
|
|
generation_date BIGINT NOT NULL,
|
|
show_profile_button BOOLEAN NOT NULL,
|
|
show_small_image BOOLEAN NOT NULL,
|
|
PRIMARY KEY (id),
|
|
UNIQUE (id),
|
|
UNIQUE (token)
|
|
);
|
|
|
|
INSERT INTO new_discord
|
|
(id, refresh, bearer, session,
|
|
token, last_accessed, generation_date,
|
|
show_profile_button, show_small_image)
|
|
SELECT
|
|
ID, refresh, bearer, session, token, lastAccessed,
|
|
generationDate, showProfileButton, showSmallImage
|
|
FROM discord;
|
|
|
|
|
|
-- Swap them out.
|
|
DROP TABLE discord;
|
|
ALTER TABLE new_discord RENAME TO discord;
|
|
|
|
-------------
|
|
-- Alembic --
|
|
-------------
|
|
|
|
-- Lastly, spoof having our initial Alembic revision.
|
|
CREATE TABLE alembic_version (
|
|
version_num VARCHAR(32) NOT NULL
|
|
CONSTRAINT alembic_version_pkc
|
|
PRIMARY KEY
|
|
);
|
|
INSERT INTO alembic_version VALUES ('f2475122ee84');
|