stmt.c (3981B)
1 #include <stddef.h> 2 #include <stdint.h> 3 #include <sqlbox.h> 4 5 #include "stmt.h" 6 7 #include "config.h" 8 9 #define StringHelper(X) #X 10 #define String(X) StringHelper(X) 11 12 struct sqlbox_pstmt pstmts[] = { 13 [StmtInit0] = { .stmt = "PRAGMA foreign_keys = ON" }, 14 [StmtInit1] = { .stmt = 15 "CREATE TABLE IF NOT EXISTS auth (\n" 16 " hash TEXT PRIMARY KEY,\n" 17 " name TEXT UNIQUE\n" 18 ")" 19 }, 20 [StmtInit2] = { .stmt = 21 "CREATE TABLE IF NOT EXISTS times (\n" 22 " userid TEXT REFERENCES auth (hash) NOT NULL,\n" 23 " period INTEGER,\n" 24 " entry INTEGER DEFAULT 0 NOT NULL,\n" 25 " start INTEGER,\n" 26 " startbreak INTEGER,\n" 27 " endbreak INTEGER,\n" 28 " end INTEGER,\n" 29 " CONSTRAINT entry_id PRIMARY KEY (userid, entry)\n" 30 ")" 31 }, 32 [StmtInit3] = { .stmt = 33 "CREATE VIEW IF NOT EXISTS current_entries (userid, entry) AS\n" 34 " SELECT userid, max(entry)\n" 35 " FROM times GROUP BY userid" 36 }, 37 [StmtInit4] = { .stmt = 38 "CREATE TABLE IF NOT EXISTS authtokens (\n" 39 " userid TEXT REFERENCES auth (hash),\n" 40 " token TEXT NOT NULL PRIMARY KEY,\n" 41 " expires INTEGER NOT NULL,\n" 42 " created INTEGER NOT NULL\n" 43 ")" 44 }, 45 [StmtInit5] = { .stmt = 46 "CREATE TRIGGER IF NOT EXISTS prune_authtokens\n" 47 " AFTER INSERT ON authtokens FOR EACH ROW BEGIN\n" 48 " DELETE FROM authtokens WHERE expires <= unixepoch();\n" 49 " DELETE FROM authtokens WHERE token IN (\n" 50 " SELECT token FROM authtokens WHERE userid IS NEW.userid\n" 51 " ORDER BY created DESC LIMIT -1 OFFSET " String(MaxLogins) "\n" 52 " );\n" 53 "END" 54 }, 55 [StmtInit6] = { .stmt = 56 "CREATE TRIGGER IF NOT EXISTS end_break_on_end\n" 57 " AFTER UPDATE OF end ON times FOR EACH ROW BEGIN\n" 58 " UPDATE times SET endbreak = new.end\n" 59 " WHERE entry IS old.entry AND endbreak ISNULL;\n" 60 "END" 61 }, 62 [StmtInitLast] = { .stmt = 63 "CREATE TRIGGER IF NOT EXISTS userdelete\n" 64 " AFTER DELETE ON auth FOR EACH ROW BEGIN\n" 65 " DELETE FROM times WHERE userid IS OLD.hash;\n" 66 "END" 67 }, 68 [StmtAddUser] = { 69 .stmt = "INSERT INTO auth (hash, name) VALUES (?, ?)" 70 }, 71 [StmtGetUserByName] = { .stmt = "SELECT * FROM auth WHERE name IS ?" }, 72 [StmtGetUserByHash] = { .stmt = "SELECT * FROM auth WHERE hash IS ?" }, 73 [StmtGetUserByToken] = { .stmt = 74 "SELECT * FROM auth WHERE hash IS\n" 75 " (SELECT (userid) FROM authtokens WHERE token IS ? AND\n" 76 " expires > unixepoch())" 77 }, 78 [StmtDeleteUser] = { .stmt = "DELETE FROM auth WHERE hash IS ?" }, 79 [StmtAddAuthToken] = { .stmt = 80 "INSERT into authtokens VALUES (?, ?, ?, unixepoch())" 81 }, 82 [StmtRevokeAuthTokens] = { .stmt = 83 "DELETE FROM authtokens WHERE userid IS ?" 84 }, 85 [StmtStartTime] = { .stmt = 86 "INSERT OR IGNORE INTO times (userid, entry, start)\n" 87 " SELECT ?, IFNULL(max(entry) + 1, 1), ? FROM times\n" 88 " WHERE userid IS ? AND end NOTNULL" 89 }, 90 [StmtStartBreakTime] = { .stmt = 91 "UPDATE times SET startbreak = ?\n" 92 " WHERE userid IS ? AND entry IS\n" 93 " (SELECT (entry) FROM current_entries WHERE userid IS ?) AND\n" 94 " startbreak ISNULL" 95 }, 96 [StmtEndBreakTime] = { .stmt = 97 "UPDATE times SET endbreak = ?\n" 98 " WHERE userid IS ? AND entry IS\n" 99 " (SELECT (entry) FROM current_entries WHERE userid IS ?) AND\n" 100 " startbreak NOTNULL and endbreak ISNULL" 101 }, 102 [StmtEndTime] = { .stmt = 103 "UPDATE times SET end = ?\n" 104 " WHERE userid IS ? AND entry IS\n" 105 " (SELECT (entry) FROM current_entries WHERE userid IS ?) AND\n" 106 " start NOTNULL AND end ISNULL" 107 }, 108 [StmtGetTimes] = { .stmt = 109 "SELECT period, entry, start, startbreak, endbreak, end FROM times\n" 110 " WHERE userid IS ? ORDER BY period DESC, entry" 111 }, 112 [StmtGetTimePeriod] = { .stmt = 113 "SELECT period, entry, start, startbreak, endbreak, end FROM times\n" 114 " WHERE userid IS ? AND period IS ? ORDER BY entry" 115 }, 116 [StmtBreakTime] = { .stmt = 117 "UPDATE times SET period =\n" 118 " (SELECT IFNULL(max(period) + 1, 1)\n" 119 " FROM times WHERE userid IS ?)\n" 120 " WHERE userid IS ? AND period ISNULL" 121 }, 122 [StmtDeleteTime] = { .stmt = 123 "DELETE FROM times WHERE userid IS ? AND entry IS ?" 124 } 125 };