natsim

commit 27076a4fa6b8a42e85386908136ae0058f6e0f6e
parent 39b66a1bdbc3721736b7d505b9526ad832ae40ae
Author: Natasha Kerensikova <natgh@instinctive.eu>
Date:   Sun, 22 Jun 2025 18:39:46 +0000

Reply subject is logged
Diffstat:
M
init.sql
 | 
37
++++++++++++++++++++++++++-----------
M
main.go
 | 
4
++--

2 files changed, 28 insertions(+), 13 deletions(-)
diff --git a/init.sql b/init.sql
@@ -7,7 +7,9 @@ CREATE TABLE received
    (timestamp REAL NOT NULL,
     subject_id INTEGER NOT NULL,
     data TEXT NOT NULL,
-    FOREIGN KEY (subject_id) REFERENCES subjects (id));
+    reply_subject_id INTEGER,
+    FOREIGN KEY (subject_id) REFERENCES subjects (id),
+    FOREIGN KEY (reply_subject_id) REFERENCES subjects (id));
 CREATE INDEX i_rtime ON received(timestamp);
 CREATE INDEX i_rsubjectid ON received(subject_id);
 
@@ -15,27 +17,35 @@ CREATE TABLE sent
    (timestamp REAL NOT NULL,
     subject_id INTEGER NOT NULL,
     data TEXT NOT NULL,
-    FOREIGN KEY (subject_id) REFERENCES subjects (id));
+    reply_subject_id INTEGER,
+    FOREIGN KEY (subject_id) REFERENCES subjects (id),
+    FOREIGN KEY (reply_subject_id) REFERENCES subjects (id));
 CREATE INDEX i_stime ON received(timestamp);
 CREATE INDEX i_ssubjectid ON received(subject_id);
 
-CREATE VIEW received_view (timestamp,subject,data)
- AS SELECT datetime(timestamp),subjects.name,data
-    FROM received LEFT OUTER JOIN subjects
-    ON subjects.id = subject_id;
+CREATE VIEW received_view (timestamp,subject,reply_subject,data)
+ AS SELECT datetime(timestamp),subjects.name,rsub.name,data
+    FROM received LEFT OUTER JOIN subjects ON subjects.id = subject_id
+                  LEFT OUTER JOIN subjects rsub ON rsub.id = reply_subject_id;
 
-CREATE VIEW sent_view (timestamp,subject,data)
- AS SELECT datetime(timestamp),subjects.name,data
-    FROM sent LEFT OUTER JOIN subjects ON subjects.id = subject_id;
+CREATE VIEW sent_view (timestamp,subject,reply_subject,data)
+ AS SELECT datetime(timestamp),subjects.name,rsub.name,data
+    FROM sent LEFT OUTER JOIN subjects ON subjects.id = subject_id
+              LEFT OUTER JOIN subjects rsub ON rsub.id = reply_subject_id;
 
 CREATE TRIGGER insert_received INSTEAD OF INSERT ON received_view
 BEGIN
     INSERT INTO subjects(name)
       SELECT NEW.subject WHERE NOT EXISTS
       (SELECT 1 FROM subjects WHERE name = NEW.subject);
-    INSERT INTO received(timestamp,subject_id,data)
+    INSERT INTO subjects(name)
+      SELECT NEW.reply_subject WHERE NOT EXISTS
+      (SELECT 1 FROM subjects WHERE name = NEW.reply_subject)
+      AND NEW.reply_subject <> '';
+    INSERT INTO received(timestamp,subject_id,reply_subject_id,data)
       VALUES (NEW.timestamp,
               (SELECT id FROM subjects WHERE name = NEW.subject),
+              (SELECT id FROM subjects WHERE name = NEW.reply_subject),
               NEW.data);
 END;
 
@@ -44,9 +54,14 @@ BEGIN
     INSERT INTO subjects(name)
       SELECT NEW.subject WHERE NOT EXISTS
       (SELECT 1 FROM subjects WHERE name = NEW.subject);
-    INSERT INTO sent(timestamp,subject_id,data)
+    INSERT INTO subjects(name)
+      SELECT NEW.reply_subject WHERE NOT EXISTS
+      (SELECT 1 FROM subjects WHERE name = NEW.reply_subject)
+      AND NEW.reply_subject <> '';
+    INSERT INTO sent(timestamp,subject_id,reply_subject_id,data)
       VALUES (NEW.timestamp,
               (SELECT id FROM subjects WHERE name = NEW.subject),
+              (SELECT id FROM subjects WHERE name = NEW.reply_subject),
               NEW.data);
 END;
 
diff --git a/main.go b/main.go
@@ -480,7 +480,7 @@ func (natsim *NatsIM) logInit() error {
 		return errors.New("unsupported database version")
 	}
 
-	natsim.insertReceived, err = natsim.db.Prepare("INSERT INTO received_view(timestamp,subject,data) VALUES (?,?,?);")
+	natsim.insertReceived, err = natsim.db.Prepare("INSERT INTO received_view(timestamp,subject,reply_subject,data) VALUES (?,?,?,?);")
 	if err != nil {
 		log.Println("Prepare insertReceived:", err)
 		return err
@@ -501,7 +501,7 @@ func (natsim *NatsIM) logReceived(msg *nats.Msg) {
 	}
 
 	t := float64(time.Now().UnixNano())/8.64e13 + 2440587.5
-	if _, err := natsim.insertReceived.Exec(t, msg.Subject, msg.Data); err != nil {
+	if _, err := natsim.insertReceived.Exec(t, msg.Subject, msg.Reply, msg.Data); err != nil {
 		natsim.ircSendError("insertReceived.Exec", err)
 	}
 }