diff options
| -rw-r--r-- | database.h | 22 | ||||
| -rw-r--r-- | unscoop.c | 26 |
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); |