Something went wrong. Try again.
Game sync and live services for independent game developers (targeting itch.io)
Something went wrong. Try again.
2.7 kB · 92 lines
SQL
at main
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192CREATE TABLE IF NOT EXISTS developers ( id TEXT PRIMARY KEY, email TEXT UNIQUE, password_hash TEXT, name TEXT NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS games ( id TEXT PRIMARY KEY, developer_id TEXT NOT NULL, name TEXT NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS api_keys ( id TEXT PRIMARY KEY, developer_id TEXT NOT NULL, game_id TEXT, key_hash TEXT NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP, revoked_at TEXT);
CREATE TABLE IF NOT EXISTS gamers ( id TEXT PRIMARY KEY, itch_user_id INTEGER NOT NULL UNIQUE, username TEXT, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS sessions ( id TEXT PRIMARY KEY, gamer_id TEXT NOT NULL REFERENCES gamers(id), expires_at TEXT NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS invites ( code TEXT PRIMARY KEY, used_by TEXT REFERENCES gamers(id), used_at TEXT, created_by TEXT NOT NULL, expires_at TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS saves ( id TEXT PRIMARY KEY, gamer_id TEXT NOT NULL, game_id TEXT NOT NULL, slot_id TEXT NOT NULL, current_cid TEXT, created_at TEXT DEFAULT CURRENT_TIMESTAMP, updated_at TEXT DEFAULT CURRENT_TIMESTAMP, UNIQUE(gamer_id, game_id, slot_id));
CREATE TABLE IF NOT EXISTS save_versions ( id TEXT PRIMARY KEY, save_id TEXT NOT NULL, version_number INTEGER NOT NULL, cid TEXT NOT NULL, milestone INTEGER DEFAULT 0, size_bytes INTEGER NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS dpop_tokens ( token_id TEXT PRIMARY KEY, gamer_id TEXT NOT NULL, nonce TEXT NOT NULL, expires_at TEXT NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE INDEX IF NOT EXISTS idx_games_developer ON games(developer_id);CREATE INDEX IF NOT EXISTS idx_saves_gamer ON saves(gamer_id);CREATE INDEX IF NOT EXISTS idx_saves_game ON saves(game_id);CREATE INDEX IF NOT EXISTS idx_save_versions_save ON save_versions(save_id);
CREATE TABLE IF NOT EXISTS gamer_quotas ( gamer_id TEXT PRIMARY KEY, used_bytes INTEGER NOT NULL DEFAULT 0, limit_bytes INTEGER NOT NULL DEFAULT 2147483648, warning_sent INTEGER NOT NULL DEFAULT 0);
-- Insert default developer if not existsINSERT OR IGNORE INTO developers (id, name) VALUES ('default-developer', 'Default Developer');