//
//
// 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>
//
#include "commands/trigger.h"
#include "string.h"
-extern Datum dupecheck(PG_FUNCTION_ARGS);
-PG_FUNCTION_INFO_V1(dupecheck);
+
+#ifdef PG71
+ extern Datum dupecheck(PG_FUNCTION_ARGS);
+ PG_FUNCTION_INFO_V1(dupecheck);
+#else
+ extern Datum dupecheck(void);
+#endif
+
+#ifdef PG71
Datum dupecheck(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
+#else
+Datum dupecheck(void)
+{
+ TriggerData *trigdata = CurrentTriggerData;
+#endif
TupleDesc tupdesc;
HeapTuple rettuple;
- bool isnull;
+ bool isnull,
+ debug_on;
int ret = 0, i, fnumber;
long rowstrlen = 0,
crc;
- int maxitems = 3,
- num;
- char *items[maxitems],
+ char **items, // will point to a malloc'ed array
*rowstring,
- *query;
+ *query,
+ **args,
+ *relation;
+ int num,
+ nargs,
+ nitems;
-
/* Make sure trigdata is pointing at what I expect */
+#ifdef PG70
+ if (!CurrentTriggerData)
+#else
if (!CALLED_AS_TRIGGER(fcinfo))
+#endif
elog(ERROR, "dupecheck: not fired by trigger manager");
-
/* tuple to return to Executor */
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
rettuple = trigdata->tg_newtuple;
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 */
- if ((ret = SPI_connect()) < 0)
- ret = SPI_connect();
- elog(NOTICE, "dupecheck: SPI_connect returned %d", ret);
+ if ((ret = SPI_connect()) != SPI_OK_CONNECT)
+ {
+ elog(NOTICE, "dupecheck: SPI_connect returned error %d", ret);
+ }
// Now we are connected to the database's SPI manager
// We will now construct a string of some important row values.
// 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"));
- 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]);
+ }
}
- rowstrlen++; // add space for 0-terminator
- rowstring = malloc(rowstrlen);
+ 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
// items of the table record. Now we calculate the CRC-32 checksum
// of the rowstring
- crc = crc32(rowstring, rowstrlen - 1);
+ 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 prevented (dupe dropped)");
+ elog(NOTICE, "dupecheck: UBD in %s detected, dupe dropped", relation);
+ free(items);
+ SPI_finish();
return PointerGetDatum(NULL);
}
else
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);