Relation isn't hardcoded anymore but determined on runtime. Added user

defineable attributes (so they, too, aren't hardcoded anymore). The checksum
now gets updated if the row is updated.

diff --git a/dbscripts/dupetrigger/dupetrigger.c b/dbscripts/dupetrigger/dupetrigger.c
index d780bbd..1b0993b 100755 (executable)
--- a/dbscripts/dupetrigger/dupetrigger.c
+++ b/dbscripts/dupetrigger/dupetrigger.c
@@ -1,11 +1,21 @@
 //
 //
 // This File contructs a PostgreSQL trigger.
-// The trigger is fired BEFORE an INSERT or UPDATE of an article
-// or comment. It first calculates a CRC32 checksum of the row to be
-// inserted or updated and checks whether there exists already another
-// row with the same checksum and the same content. In this case,
-// the trigger returns NULL and thus, aborts the SQL command.
+//
+// USAGE:
+// dupecheck(debug, column1, column2, ...);
+//
+// debug:   if "yes" dupecheck generates debug output
+// columns: the names of the columns that are used to calculate the
+//          checksum
+//
+//
+// The trigger is normally fired BEFORE an INSERT or UPDATE of an
+// article or comment. It first calculates a CRC-32 checksum of the
+// specified columns of the row to be inserted or updated and
+// checks whether there exists already another row with the same
+// checksum. In this case, the trigger returns NULL and thus,
+// aborts the SQL command.
 //
 // Author: Matthias Jordan <mjordan@code-fu.de>
 //
@@ -16,6 +26,7 @@
 #include "string.h"
 
 
+
 #ifdef PG71
        extern Datum dupecheck(PG_FUNCTION_ARGS);
        PG_FUNCTION_INFO_V1(dupecheck);
@@ -117,15 +128,19 @@ Datum dupecheck(void)
 #endif
        TupleDesc       tupdesc;
        HeapTuple       rettuple;
-       bool            isnull;
+       bool            isnull,
+                               debug_on;
        int                     ret = 0, i, fnumber;
        long rowstrlen = 0,
                crc;
-       int maxitems = 4;
-       char *items[maxitems],
+       char **items, // will point to a malloc'ed array
                *rowstring,
-               *query;
-       int num;
+               *query,
+               **args,
+               *relation;
+       int num,
+               nargs,
+               nitems;
        
        /* Make sure trigdata is pointing at what I expect */
 #ifdef PG70
@@ -142,7 +157,20 @@ Datum dupecheck(void)
        
        if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
                return PointerGetDatum(NULL);
-       
+
+
+       // Initialize arguments and see whether the arguments are ok.
+       args = trigdata->tg_trigger->tgargs;
+       nargs = trigdata->tg_trigger->tgnargs;
+
+       if (nargs < 2)
+       {
+               elog(DEBUG, "dupecheck: USAGE: dupeckeck(debug, col1, col2, ...)");
+               return PointerGetDatum(NULL);
+       }
+
+       debug_on = !strcmp("yes", args[0]);
+
        tupdesc = trigdata->tg_relation->rd_att;
        
        /* Connect to SPI manager */
@@ -157,28 +185,52 @@ Datum dupecheck(void)
        // To include an additional item, add another SPI_getvalue line and
        // increase maxitems above by 1
        
-       items[0] = SPI_getvalue(rettuple, tupdesc, SPI_fnumber(tupdesc, "creator"));
-       items[1] = SPI_getvalue(rettuple, tupdesc, SPI_fnumber(tupdesc, "title"));
-       items[2] = SPI_getvalue(rettuple, tupdesc, SPI_fnumber(tupdesc, "description"));
-       items[3] = SPI_getvalue(rettuple, tupdesc, SPI_fnumber(tupdesc, "to_media"));
 
-       for (i=0; (i < maxitems); i++)
+       // Allocate array for the argument pointers
+       nitems = nargs - 1; // don't take the debug parameter as a row name;
+       items = (char **) malloc(sizeof(char*) * nitems);
+       if (items == NULL)
+       {
+               SPI_finish();
+               return PointerGetDatum(NULL);
+       }
+
+       // Collect arguments
+       for (i = 1; (i < nargs); i++)
+       {
+               items[i-1] = SPI_getvalue(rettuple, tupdesc, SPI_fnumber(tupdesc, args[i]));
+               if (debug_on)
+               {
+                       elog(DEBUG, "dupecheck: Argument %d: row %s -> %s\n", i, args[i], items[i-1]);
+               }
+       }
+       
+
+       // Find out length of row string to be constructed
+       for (i=0; (i < nitems); i++)
        {
-               rowstrlen += strlen(items[i]);
+               if (items[i] != NULL)
+               {
+                       rowstrlen += strlen(items[i]);
+               }
        }
        
        rowstring = malloc(rowstrlen+1); // add space for 0-terminator
        if (rowstring == NULL)
        {
-               // Big problem.
+               free(items);
                SPI_finish();
                return PointerGetDatum(NULL);
        }
 
+       // Construct row string
        *rowstring = 0;
-       for (i=0; (i < maxitems); i++)
+       for (i=0; (i < nitems); i++)
        {
-               strcat(rowstring, items[i]);
+               if (items[i] != NULL)
+               {
+                       strcat(rowstring, items[i]);
+               }
        }
 
        // rowstring now contains the data of the maxitems important
@@ -188,21 +240,25 @@ Datum dupecheck(void)
        crc = crc32(rowstring, rowstrlen); 
 
        // Now we allocate some space and construct the SQL query
-       query = malloc(47 + 11 + 2 + 1); // SELECT part + crc32 + "';" + 0-term
+       relation = SPI_getrelname(trigdata->tg_relation);
+       query = malloc(40 + strlen(relation) + 11 + 2 + 1); // SELECT part + relation + crc32 + "';" + 0-term
        if (query == NULL)
        {
                // Big problem
+               free(items);
                SPI_finish();
                return PointerGetDatum(NULL);
        }
        
-       sprintf(query, "SELECT count(*) FROM comment WHERE checksum='%ld';", crc);
+       sprintf(query, "SELECT count(*) FROM %s WHERE checksum='%ld';", relation, crc);
        ret = SPI_exec(query, 2);
        num = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);
+       elog(DEBUG, "dupecheck: %s", query);
 
-       if ((ret == SPI_OK_SELECT) && (num > 0))
+       if ((ret == SPI_OK_SELECT) && (num > 0) && !(TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)))
        {
-               elog(NOTICE, "dupecheck: UBD detected, dupe dropped");
+               elog(NOTICE, "dupecheck: UBD in %s detected, dupe dropped", relation);
+               free(items);
                SPI_finish();
                return PointerGetDatum(NULL);
        }
@@ -214,7 +270,10 @@ Datum dupecheck(void)
                Datum value;
                char nulls = 0;
                
-//             elog(NOTICE, "dupecheck: Adding checksum to INSERT");
+               if (debug_on)
+               {               
+                       elog(NOTICE, "dupecheck: Adding checksum to row");
+               }
                attnum = SPI_fnumber(tupdesc, "checksum");
                value = (Datum) crc;
                rettuple = SPI_modifytuple(trigdata->tg_relation, rettuple, 1, &attnum, &value, &nulls);

diff --git a/dbscripts/dupetrigger/postgresql.sript b/dbscripts/dupetrigger/postgresql.sript
index 69b3d36..220016b 100755 (executable)
--- a/dbscripts/dupetrigger/postgresql.sript
+++ b/dbscripts/dupetrigger/postgresql.sript
@@ -1,5 +1,7 @@
 \connect - postgres
 DROP FUNCTION dupecheck();
-DROP TRIGGER dupetrigger ON comment;
-CREATE FUNCTION dupecheck() RETURNS OPAQUE AS '/path/to/dupetrigger.so' LANGUAGE 'C';
-CREATE TRIGGER dupetrigger BEFORE INSERT OR UPDATE ON comment FOR EACH ROW EXECUTE PROCEDURE dupecheck();
+DROP TRIGGER commdupetrigger ON comment;
+DROP TRIGGER contentdupetrigger ON content;
+CREATE FUNCTION dupecheck() RETURNS OPAQUE AS '/pub/Dokumente/Indymedia/de-tech/Mir/src/dupetrigger.so' LANGUAGE 'C';
+CREATE TRIGGER contentdupetrigger BEFORE INSERT OR UPDATE ON content FOR EACH ROW EXECUTE PROCEDURE dupecheck("no", "title", "subtitle", "creator", "description", "content_data");
+CREATE TRIGGER commdupetrigger BEFORE INSERT OR UPDATE ON comment FOR EACH ROW EXECUTE PROCEDURE dupecheck("no", "creator", "to_media", "description", "title");