link threads to calls

diff --git a/database/calls.sql b/database/calls.sql
new file mode 100644 (file)
index 0000000..fa9a7c9
--- /dev/null
+++ b/database/calls.sql
@@ -0,0 +1,29 @@
+INSERT INTO forum_boards (fcid,fbid,board) VALUES(9,-3,'Call logs');
+
+ALTER TABLE calls ADD COLUMN ftid INTEGER REFERENCES forum_threads(ftid);
+
+CREATE OR REPLACE FUNCTION add_call() RETURNS "trigger"
+    AS $_X$
+if ($_TD->{event} eq 'INSERT'){
+       $rv = spi_exec_query("SELECT nextval('public.forum_threads_ftid_seq') AS id");
+       if ($rv->{processed} != 1){
+               return 'SKIP';
+       }
+       $ftid = $rv->{rows}[0]->{id};
+       $query = spi_prepare('INSERT INTO forum_threads (fbid,ftid,subject,uid) VALUES(-3,$1,$2,-3)','int4','varchar');
+       $rv = spi_exec_prepared($query,$ftid,"$_TD->{new}{member}: $_TD->{new}{landing_tick}");
+       spi_freeplan($query);
+       if (rv->{status} != SPI_OK_INSERT){
+               return 'SKIP';
+       }
+       $_TD->{new}{ftid} = $ftid;
+       return 'MODIFY';
+}
+return 'SKIP';
+$_X$
+    LANGUAGE plperl;
+
+CREATE TRIGGER add_call
+    BEFORE INSERT ON calls
+    FOR EACH ROW
+    EXECUTE PROCEDURE add_call();

diff --git a/database/clean-webbie.sql b/database/clean-webbie.sql
index 24e469c5c17f6f010c91fbda2f3be709ef4b4ff6..14a26b27c422b5028022243f70812e790630d773 100644 (file)
--- a/database/clean-webbie.sql
+++ b/database/clean-webbie.sql
@@ -18,6 +18,7 @@ DELETE FROM planets ;
 DELETE FROM alliances WHERE id > 1;
 SELECT * FROM alliances;
 DELETE FROM forum_threads WHERE fbid = -2;
+DELETE FROM forum_threads WHERE fbid = -3;
 DELETE FROM forum_threads WHERE fbid = 12;
 DELETE FROM forum_posts WHERE ftid IN (SELECT ftid FROM forum_threads WHERE fbid = -1);
 ALTER SEQUENCE alliances_id_seq RESTART 2;