commit 72b90bc833f1803baee604d83726ce8b61df4adb
parent 2071dcb3bfa317e643a134bad0743f74b4d7da2f
Author: Jacob R. Edwards <jacob@jacobedwards.org>
Date: Sun, 17 Mar 2024 16:19:32 -0700
Use columns instead of a json blob for times
I'm still learning SQL and am not sure if this is the most correct
method of doing things, but it's at least better than a json blob.
This also prepares the project for the next step of separating
timesheets into periods.
Diffstat:
| M | pages/main.c | | | 2 | -- |
| M | stmt.c | | | 59 | ++++++++++++++++++++++++++++++++++++----------------------- |
| M | stmt.h | | | 6 | ++++-- |
| M | times.c | | | 78 | ++++++++++++++++++++++++++++++++++++++++-------------------------------------- |
| M | times.h | | | 1 | - |
5 files changed, 80 insertions(+), 66 deletions(-)
diff --git a/pages/main.c b/pages/main.c
@@ -345,8 +345,6 @@ pagemain(struct pagedata *pd)
tf = gettf(pd->req.fieldmap[KeyTime]->parsed.s);
if (tf < 0)
err(1, "Invalid time field");
- if (tf == StartTime && newrow(pd, pd->user->hash) != SQLBOX_CODE_OK)
- err(1, "New time");
if (settime(pd, pd->user->hash, tf, time(NULL)))
return KCGI_SYSTEM;
}
diff --git a/stmt.c b/stmt.c
@@ -13,20 +13,25 @@ struct sqlbox_pstmt pstmts[] = {
")"
},
[StmtInit2] = { .stmt =
- "CREATE TABLE IF NOT EXISTS userdata (\n"
- " id TEXT PRIMARY KEY REFERENCES auth (hash),\n"
- " times BLOB DEFAULT '[]'\n"
+ "CREATE TABLE IF NOT EXISTS times (\n"
+ " userid TEXT REFERENCES auth (hash),\n"
+ " entry INTEGER DEFAULT 0 NOT NULL,\n"
+ " start INTEGER,\n"
+ " startbreak INTEGER,\n"
+ " endbreak INTEGER,\n"
+ " end INTEGER,\n"
+ " CONSTRAINT entry_id PRIMARY KEY (userid, entry)\n"
")"
},
- [StmtInit3] = { .stmt = "CREATE TRIGGER IF NOT EXISTS userdatahash\n"
- " AFTER INSERT ON auth FOR EACH ROW BEGIN"
- " INSERT INTO userdata (id) VALUES (NEW.hash);\n"
- "END"
+ [StmtInit3] = { .stmt =
+ "CREATE VIEW IF NOT EXISTS current_entries (userid, entry) AS\n"
+ " SELECT userid, max(entry)\n"
+ " FROM times GROUP BY userid"
},
[StmtInitLast] = { .stmt =
"CREATE TRIGGER IF NOT EXISTS userdelete\n"
" AFTER DELETE ON auth FOR EACH ROW BEGIN\n"
- " DELETE FROM userdata WHERE id IS OLD.hash;\n
+ " DELETE FROM times WHERE userid IS OLD.hash;\n"
"END"
},
[StmtAddUser] = {
@@ -35,27 +40,35 @@ struct sqlbox_pstmt pstmts[] = {
[StmtGetUserByName] = { .stmt = "SELECT * FROM auth WHERE name IS ?" },
[StmtGetUserByHash] = { .stmt = "SELECT * FROM auth WHERE hash IS ?" },
[StmtDeleteUser] = { .stmt = "DELETE FROM auth WHERE hash IS ?" },
- [StmtNewTimeRow] = { .stmt =
- "UPDATE userdata SET times =\n"
- " json_insert(times, '$[#]', json_array(null, null, null, null))\n"
- " WHERE id IS ? AND (json_array_length(times) = 0 OR\n"
- " json_extract(times, '$[#-1][3]') IS NOT null)"
- },
- /* Currently, the user can set the end time with a
+ /*
+ * Currently, the user can set the end time with a
* break open and the break won't be ended. This is
* intentional, but also it should set the break end
* time to the end time in that case.
*/
- [StmtSetTime] = { .stmt =
- "UPDATE userdata SET times =\n"
- " json_replace(times, '$[#-1][' || ? || ']', ?)\n"
- " WHERE id IS ? AND\n"
- " json_extract(times, '$[#-1][' || ? || ']') IS null"
+ [StmtStartTime] = { .stmt =
+ "INSERT OR IGNORE INTO times (userid, entry, start)\n"
+ " SELECT ?, IFNULL(max(entry) + 1, 0), ? FROM times\n"
+ " WHERE userid IS ? AND end IS NOT NULL"
+ },
+ [StmtStartBreakTime] = { .stmt =
+ "UPDATE times SET startbreak = ?\n"
+ " WHERE userid IS ? AND entry IS\n"
+ " (SELECT (entry) FROM current_entries WHERE userid IS ?)"
+ },
+ [StmtEndBreakTime] = { .stmt =
+ "UPDATE times SET endbreak = ?\n"
+ " WHERE userid IS ? AND entry IS\n"
+ " (SELECT (entry) FROM current_entries WHERE userid IS ?)"
+ },
+ [StmtEndTime] = { .stmt =
+ "UPDATE times SET end = ?\n"
+ " WHERE userid IS ? AND entry IS\n"
+ " (SELECT (entry) FROM current_entries WHERE userid IS ?)"
},
-
[StmtGetTimes] = { .stmt =
- "SELECT value FROM userdata, json_tree(times)\n"
- " WHERE userdata.id IS ? AND type IS NOT 'array'"
+ "SELECT start, startbreak, endbreak, end FROM times\n"
+ " WHERE userid IS ?"
}
};
diff --git a/stmt.h b/stmt.h
@@ -8,8 +8,10 @@ enum StmtID {
StmtGetUserByHash,
StmtGetUserByName,
StmtDeleteUser,
- StmtNewTimeRow,
- StmtSetTime,
+ StmtStartTime,
+ StmtStartBreakTime,
+ StmtEndBreakTime,
+ StmtEndTime,
StmtGetTimes,
StmtMax
};
diff --git a/times.c b/times.c
@@ -34,30 +34,32 @@ enum time_flag timeflagmap[] = {
};
enum sqlbox_code
-newrow(struct pagedata *pd, char *hash)
+settime(struct pagedata *pd, char *hash, enum time_field f, time_t time)
{
- struct sqlbox_parm ps[] = {
- { .sparm = hash, .type = SQLBOX_PARM_STRING }
+ static enum StmtID map[] = {
+ [StartTime] = StmtStartTime,
+ [BreakStartTime] = StmtStartBreakTime,
+ [BreakEndTime] = StmtEndBreakTime,
+ [EndTime] = StmtEndTime
};
- return sqlbox_exec(pd->db, pd->dbid, StmtNewTimeRow,
- Len(ps), ps, 0) != SQLBOX_CODE_OK;
-}
-
-enum sqlbox_code
-settime(struct pagedata *pd, char *hash, enum time_field f, time_t time)
-{
struct sqlbox_parm ps[] = {
- { .iparm = f, .type = SQLBOX_PARM_INT },
+ { .sparm = hash, .type = SQLBOX_PARM_STRING },
{ .iparm = time, .type = SQLBOX_PARM_INT },
{ .sparm = hash, .type = SQLBOX_PARM_STRING },
- { .iparm = f, .type = SQLBOX_PARM_INT }
+ { .sparm = hash, .type = SQLBOX_PARM_STRING }
};
+ assert(f >= StartTime && f <= EndTime);
+
if (!time)
return SQLBOX_CODE_ERROR;
- return sqlbox_exec(pd->db, pd->dbid, StmtSetTime,
- Len(ps), ps, 0) != SQLBOX_CODE_OK;
+ if (f == StartTime) {
+ return sqlbox_exec(pd->db, pd->dbid, map[f],
+ Len(ps) - 1, ps, 0) != SQLBOX_CODE_OK;
+ }
+ return sqlbox_exec(pd->db, pd->dbid, map[f],
+ Len(ps) - 1, ps + 1, 0) != SQLBOX_CODE_OK;
}
void
@@ -104,37 +106,37 @@ gettimes(struct pagedata *pd, char *hash, struct timesheet **rtimes)
};
struct sqlbox_parmset *r;
struct timesheet *times, *oldtime, *time;
- unsigned int n;
+ unsigned int i;
stmtid = sqlbox_prepare_bind(pd->db, pd->dbid, StmtGetTimes, 1, &p, 0);
if (stmtid == 0)
err(1, "prepare bind");
times = oldtime = NULL;
- for (n = 0; (r = sqlbox_step(pd->db, stmtid)) &&
- !(!r->psz && r->code == SQLBOX_CODE_OK); n = (n + 1) % 4) {
- if (n % 4 == 0) {
- oldtime = time;
- time = newtimesheet();
- if (!time) {
- freetimesheet(times);
- return 1;
- }
- if (!times)
- times = time;
- else if (oldtime)
- inserttimesheet(time, oldtime);
+ while ((r = sqlbox_step(pd->db, stmtid)) &&
+ !(!r->psz && r->code == SQLBOX_CODE_OK)) {
+ assert(r->psz == 4);
+ oldtime = time;
+ time = newtimesheet();
+ if (!time) {
+ freetimesheet(times);
+ return 1;
}
-
- assert(r->psz == 1);
- switch (r->ps[0].type) {
- case SQLBOX_PARM_NULL:
- break;
- case SQLBOX_PARM_INT:
- timesheet_set(time, n, r->ps[0].iparm);
- break;
- default:
- err(1, "This is a stmt.c bug");
+ if (!times)
+ times = time;
+ else if (oldtime)
+ inserttimesheet(time, oldtime);
+
+ for (i = 0; i < 4; ++i) {
+ switch (r->ps[i].type) {
+ case SQLBOX_PARM_NULL:
+ break;
+ case SQLBOX_PARM_INT:
+ timesheet_set(time, i, r->ps[i].iparm);
+ break;
+ default:
+ err(1, "This is a stmt.c bug");
+ }
}
}
diff --git a/times.h b/times.h
@@ -23,7 +23,6 @@ struct timesheet {
extern char *timefields[];
extern enum time_flag timeflagmap[];
-enum sqlbox_code newrow(struct pagedata *pd, char *hash);
enum sqlbox_code settime(struct pagedata *pd, char *hash, enum time_field f, time_t time);
void freetimesheet(struct timesheet *ts);
struct timesheet *newtimesheet(void);
