123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154 |
- import Database from 'better-sqlite3';
- const db = new Database('orl.db');
- db.pragma('foreign_keys = ON');
- db.pragma('journal_mode = WAL');
- db.pragma('temp_store = MEMORY');
- db.exec(`
- CREATE TABLE IF NOT EXISTS rating (
- user_id INTEGER NOT NULL,
- mode INTEGER NOT NULL, -- 0 = std, 1 = taiko, 2 = ctb, 3 = mania
- s1_scores INTEGER DEFAULT 0,
- s2_scores INTEGER DEFAULT 0,
- s3_scores INTEGER NOT NULL,
- total_scores INTEGER NOT NULL,
- elo REAL NOT NULL,
- division TEXT NOT NULL,
- s1_division TEXT,
- s2_division TEXT,
- UNIQUE(user_id, mode) ON CONFLICT ROLLBACK
- );
- CREATE INDEX IF NOT EXISTS rating_elo_idx ON rating (elo, mode);
- CREATE TABLE IF NOT EXISTS map (
- map_id INTEGER PRIMARY KEY,
- -- info from .osu file
- name TEXT NOT NULL,
- mode INTEGER NOT NULL, -- (0 = std, 1 = taiko, 2 = ctb, 3 = mania)
- ar REAL NOT NULL,
- cs REAL NOT NULL,
- hp REAL NOT NULL,
- od REAL NOT NULL,
- bpm REAL NOT NULL,
- -- info from osu!api or from osu.db scan (https://git.kiwec.net/kiwec/orl-maps-db-generator)
- set_id INTEGER NOT NULL,
- length REAL NOT NULL,
- ranked INTEGER NOT NULL, -- not a boolean but an enum
- dmca INTEGER NOT NULL
- );
- CREATE TABLE IF NOT EXISTS pp (
- map_id INTEGER NOT NULL,
- mods INTEGER NOT NULL,
- stars REAL NOT NULL,
- pp REAL NOT NULL,
- FOREIGN KEY(map_id) REFERENCES map(map_id),
- UNIQUE(map_id, mods) ON CONFLICT ROLLBACK
- );
- CREATE TABLE IF NOT EXISTS user (
- user_id INTEGER PRIMARY KEY,
- username TEXT NOT NULL,
- country_code TEXT NOT NULL,
- profile_data TEXT NOT NULL,
- discord_user_id TEXT
- );
- CREATE TABLE IF NOT EXISTS map_pool (
- season INTEGER NOT NULL,
- collection_id INTEGER NOT NULL,
- user_id INTEGER NOT NULL,
- added_tms INTEGER NOT NULL,
- data TEXT NOT NULL
- );
- CREATE TABLE IF NOT EXISTS match (
- match_id INTEGER PRIMARY KEY,
- invite_id INTEGER,
- name TEXT,
- ruleset INTEGER NOT NULL DEFAULT 0,
- data TEXT NOT NULL DEFAULT '{}',
- start_time INTEGER NOT NULL,
- end_time INTEGER,
- reopened_as INTEGER
- );
- CREATE INDEX IF NOT EXISTS match_endtms_idx ON match (end_time);
- CREATE TABLE IF NOT EXISTS game (
- game_id INTEGER PRIMARY KEY,
- match_id INTEGER NOT NULL, -- "match" means lobby
- start_time INTEGER NOT NULL,
- end_time INTEGER NOT NULL,
- beatmap_id INTEGER NOT NULL,
- play_mode INTEGER NOT NULL,
- scoring_type TEXT NOT NULL,
- team_type TEXT NOT NULL,
- mods TEXT NOT NULL,
- FOREIGN KEY(match_id) REFERENCES match(match_id),
- FOREIGN KEY(beatmap_id) REFERENCES map(map_id)
- );
- CREATE TABLE IF NOT EXISTS score (
- game_id INTEGER NOT NULL,
- user_id INTEGER NOT NULL,
- mode INTEGER NOT NULL,
- score INTEGER NOT NULL,
- accuracy REAL,
- max_combo INTEGER,
- count_50 INTEGER,
- count_100 INTEGER,
- count_300 INTEGER,
- count_miss INTEGER,
- count_geki INTEGER,
- count_katu INTEGER,
- perfect INTEGER,
- enabled_mods TEXT NOT NULL,
- placement INTEGER NOT NULL,
- dodged INTEGER NOT NULL,
- elo_diff REAL NOT NULL,
- created_at INTEGER NOT NULL,
- beatmap_id INTEGER NOT NULL,
- FOREIGN KEY(game_id) REFERENCES game(game_id),
- FOREIGN KEY(user_id) REFERENCES user(user_id),
- FOREIGN KEY(beatmap_id) REFERENCES map(map_id),
- UNIQUE(game_id, user_id) ON CONFLICT ROLLBACK
- );
- -- We get all scores from user X on their profile page
- CREATE INDEX IF NOT EXISTS score_userid_idx ON score (user_id, created_at);
- -- For MAX(placement)
- CREATE INDEX IF NOT EXISTS score_placement_idx ON score (game_id);
- CREATE TABLE IF NOT EXISTS token (
- token TEXT NOT NULL,
- created_at INTEGER NOT NULL,
- osu_id INTEGER,
- last_match INTEGER,
- discord_id TEXT
- );
- `);
- export default db;
|