Game sync and live services for independent game developers (targeting itch.io)
1use anyhow::{Context, Result};
2use rusqlite::Connection;
3
4pub async fn migrate(database: &std::path::Path) -> Result<()> {
5 let conn = Connection::open(database).context("Failed to open database")?;
6
7 conn.execute_batch(
8 r#"
9 CREATE TABLE IF NOT EXISTS developers (
10 id TEXT PRIMARY KEY,
11 email TEXT NOT NULL UNIQUE,
12 password_hash TEXT NOT NULL,
13 name TEXT NOT NULL,
14 created_at TEXT DEFAULT CURRENT_TIMESTAMP,
15 updated_at TEXT DEFAULT CURRENT_TIMESTAMP
16 );
17
18 CREATE TABLE IF NOT EXISTS games (
19 id TEXT PRIMARY KEY,
20 developer_id TEXT NOT NULL REFERENCES developers(id),
21 name TEXT NOT NULL,
22 created_at TEXT DEFAULT CURRENT_TIMESTAMP,
23 updated_at TEXT DEFAULT CURRENT_TIMESTAMP
24 );
25
26 CREATE TABLE IF NOT EXISTS api_keys (
27 id TEXT PRIMARY KEY,
28 developer_id TEXT NOT NULL REFERENCES developers(id),
29 game_id TEXT REFERENCES games(id),
30 key_hash TEXT NOT NULL,
31 created_at TEXT DEFAULT CURRENT_TIMESTAMP,
32 revoked_at TEXT
33 );
34
35 CREATE TABLE IF NOT EXISTS gamers (
36 id TEXT PRIMARY KEY,
37 itch_user_id INTEGER NOT NULL UNIQUE,
38 created_at TEXT DEFAULT CURRENT_TIMESTAMP
39 );
40
41 CREATE TABLE IF NOT EXISTS sessions (
42 id TEXT PRIMARY KEY,
43 gamer_id TEXT NOT NULL REFERENCES gamers(id),
44 expires_at TEXT NOT NULL,
45 created_at TEXT DEFAULT CURRENT_TIMESTAMP
46 );
47
48 CREATE TABLE IF NOT EXISTS invites (
49 code TEXT PRIMARY KEY,
50 used_by TEXT REFERENCES gamers(id),
51 used_at TEXT,
52 created_by TEXT NOT NULL,
53 expires_at TEXT NOT NULL
54 );
55
56 CREATE TABLE IF NOT EXISTS saves (
57 id TEXT PRIMARY KEY,
58 gamer_id TEXT NOT NULL REFERENCES gamers(id),
59 game_id TEXT NOT NULL REFERENCES games(id),
60 slot_id TEXT NOT NULL,
61 current_cid TEXT,
62 created_at TEXT DEFAULT CURRENT_TIMESTAMP,
63 updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
64 UNIQUE(gamer_id, game_id, slot_id)
65 );
66
67 CREATE TABLE IF NOT EXISTS save_versions (
68 id TEXT PRIMARY KEY,
69 save_id TEXT NOT NULL REFERENCES saves(id),
70 version_number INTEGER NOT NULL,
71 cid TEXT NOT NULL,
72 milestone INTEGER DEFAULT 0,
73 size_bytes INTEGER NOT NULL,
74 created_at TEXT DEFAULT CURRENT_TIMESTAMP
75 );
76
77 CREATE TABLE IF NOT EXISTS dpop_tokens (
78 token_id TEXT PRIMARY KEY,
79 gamer_id TEXT NOT NULL REFERENCES gamers(id),
80 nonce TEXT NOT NULL,
81 expires_at TEXT NOT NULL,
82 created_at TEXT DEFAULT CURRENT_TIMESTAMP
83 );
84
85 CREATE INDEX IF NOT EXISTS idx_games_developer ON games(developer_id);
86 CREATE INDEX IF NOT EXISTS idx_saves_gamer ON saves(gamer_id);
87 CREATE INDEX IF NOT EXISTS idx_saves_game ON saves(game_id);
88 CREATE INDEX IF NOT EXISTS idx_save_versions_save ON save_versions(save_id);
89
90 CREATE TABLE IF NOT EXISTS gamer_quotas (
91 gamer_id TEXT PRIMARY KEY,
92 used_bytes INTEGER NOT NULL DEFAULT 0,
93 limit_bytes INTEGER NOT NULL DEFAULT 2147483648,
94 warning_sent INTEGER NOT NULL DEFAULT 0
95 );
96 "#,
97 )?;
98
99 println!("Migration completed successfully");
100 Ok(())
101}