Rename IDs to nouns and join with USING

2 files changed, 24 insertions, 24 deletions

diff --git a/database.h b/database.h
index 2481262..eec6cb6 100644
--- a/database.h
+++ b/database.h
@@ -151,44 +151,44 @@ static inline int dbVersion(sqlite3 *db) {
 static const char *InitSQL = {
 	"BEGIN TRANSACTION;"
 	"CREATE TABLE networks ("
-		"id INTEGER PRIMARY KEY,"
+		"network INTEGER PRIMARY KEY,"
 		"name TEXT NOT NULL UNIQUE"
 	");"
 	"CREATE TABLE contexts ("
-		"id INTEGER PRIMARY KEY,"
-		"networkID INTEGER NOT NULL REFERENCES networks,"
+		"context INTEGER PRIMARY KEY,"
+		"network INTEGER NOT NULL REFERENCES networks,"
 		"name TEXT NOT NULL,"
 		"query BOOLEAN NOT NULL,"
-		"UNIQUE (networkID, name)"
+		"UNIQUE (network, name)"
 	");"
 	"CREATE TABLE names ("
-		"id INTEGER PRIMARY KEY,"
+		"name INTEGER PRIMARY KEY,"
 		"nick TEXT NOT NULL,"
 		"user TEXT NOT NULL,"
 		"host TEXT NOT NULL,"
 		"UNIQUE (nick, user, host)"
 	");"
 	"CREATE TABLE events ("
-		"id INTEGER PRIMARY KEY,"
+		"event INTEGER PRIMARY KEY,"
 		"time DATETIME NOT NULL,"
 		"type INTEGER NOT NULL,"
-		"contextID INTEGER NOT NULL REFERENCES contexts,"
-		"nameID INTEGER NOT NULL REFERENCES names,"
+		"context INTEGER NOT NULL REFERENCES contexts,"
+		"name INTEGER NOT NULL REFERENCES names,"
 		"target TEXT,"
 		"message TEXT"
 	");"
 	"CREATE VIRTUAL TABLE search USING fts5 ("
 		"message,"
 		"content = events,"
-		"content_rowid = id,"
+		"content_rowid = event,"
 		"tokenize = 'porter'"
 	");"
 	"CREATE TRIGGER eventsInsert AFTER INSERT ON events BEGIN"
-	" INSERT INTO search (rowid, message) VALUES (new.id, new.message);"
+	" INSERT INTO search (rowid, message) VALUES (new.event, new.message);"
 	"END;"
 	"CREATE TRIGGER eventsDelete AFTER DELETE ON events BEGIN"
 	" INSERT INTO search (search, rowid, message)"
-	" VALUES ('delete', old.id, old.message);"
+	" VALUES ('delete', old.event, old.message);"
 	"END;"
 	"COMMIT TRANSACTION;"
 };
diff --git a/unscoop.c b/unscoop.c
index b39ee14..8017f35 100644
--- a/unscoop.c
+++ b/unscoop.c
@@ -206,13 +206,13 @@ int main(int argc, char *argv[]) {
 		int error = sqlite3_exec(
 			db,
 			"WITH potentials AS ("
-			" SELECT events.id, events.id - first_value(events.id) OVER ("
-			"  PARTITION BY time, type, contextID, nick, target, message"
-			"  ORDER BY events.id"
+			" SELECT event, event - first_value(event) OVER ("
+			"  PARTITION BY time, type, context, nick, target, message"
+			"  ORDER BY event"
 			" ) AS diff"
-			" FROM events JOIN names ON (names.id = nameID)"
-			"), duplicates AS (SELECT id FROM potentials WHERE diff > 50)"
-			"DELETE FROM events WHERE id IN duplicates;",
+			" FROM events JOIN names USING (name)"
+			"), duplicates AS (SELECT event FROM potentials WHERE diff > 50)"
+			"DELETE FROM events WHERE event IN duplicates;",
 			NULL, NULL, NULL
 		);
 		if (error) {
@@ -242,8 +242,8 @@ int main(int argc, char *argv[]) {
 
 	sqlite3_stmt *insertContext = dbPrepare(
 		db, 0,
-		"INSERT OR IGNORE INTO contexts (networkID, name, query)"
-		"SELECT id, $context, $query FROM networks WHERE name = $network;"
+		"INSERT OR IGNORE INTO contexts (network, name, query)"
+		"SELECT network, $context, $query FROM networks WHERE name = $network;"
 	);
 	dbBindText(insertContext, 1, context, -1);
 	dbBindInt(insertContext, 2, context[0] != '#' && context[0] != '&');
@@ -254,8 +254,8 @@ int main(int argc, char *argv[]) {
 	int64_t contextID;
 	sqlite3_stmt *selectContext = dbPrepare(
 		db, 0,
-		"SELECT contexts.id FROM contexts"
-		" JOIN networks ON (networks.id = networkID)"
+		"SELECT context FROM contexts"
+		" JOIN networks USING (network)"
 		" WHERE networks.name = $network AND contexts.name = $context;"
 	);
 	dbBindText(selectContext, 1, network, -1);
@@ -272,11 +272,11 @@ int main(int argc, char *argv[]) {
 	// SQLite expects a colon in the timezone, but ISO8601 does not.
 	sqlite3_stmt *insertEvent = dbPrepare(
 		db, SQLITE_PREPARE_PERSISTENT,
-		"INSERT INTO events (contextID, type, time, nameID, target, message)"
+		"INSERT INTO events (context, type, time, name, target, message)"
 		"SELECT"
-		" $contextID, $type,"
+		" $context, $type,"
 		" datetime(substr($time, 1, 22) || ':' || substr($time, -2)),"
-		" id, $target, $message"
+		" name, $target, $message"
 		" FROM names WHERE nick = $nick AND user = $user AND host = $host;"
 	);
 	dbBindInt(insertEvent, 1, contextID);