dbscripts/dupetrigger/dupetrigger.c

   1 //
   2 //
   3 // This File contructs a PostgreSQL trigger.
   4 //
   5 // USAGE:
   6 // dupecheck(debug, column1, column2, ...);
   7 //
   8 // debug:   if "yes" dupecheck generates debug output
   9 // columns: the names of the columns that are used to calculate the
  10 //          checksum
  11 //
  12 //
  13 // The trigger is normally fired BEFORE an INSERT or UPDATE of an
  14 // article or comment. It first calculates a CRC-32 checksum of the
  15 // specified columns of the row to be inserted or updated and
  16 // checks whether there exists already another row with the same
  17 // checksum. In this case, the trigger returns NULL and thus,
  18 // aborts the SQL command.
  19 //
  20 // Author: Matthias Jordan <mjordan@code-fu.de>
  21 //
  22
  23
  24 #include "executor/spi.h"
  25 #include "commands/trigger.h"
  26 #include "string.h"
  27
  28
  29
  30 #ifndef PG70
  31         extern Datum dupecheck(PG_FUNCTION_ARGS);
  32         PG_FUNCTION_INFO_V1(dupecheck);
  33 #else
  34         extern Datum dupecheck(void);
  35 #endif
  36
  37
  38
  39
  40
  41 //
  42 //
  43 // CRC32 stuff
  44 //
  45
  46 // crc32 table based on the following polynom
  47 #define CRC32_POLY 0x04c11db7     /* AUTODIN II, Ethernet, & FDDI */
  48
  49 u_long crc32_table[256] =
  50         {0x0000000, 0x04c11db7, 0x09823b6e, 0x0d4326d9, 0x130476dc, 0x17c56b6b,
  51         0x1a864db2, 0x1e475005, 0x2608edb8, 0x22c9f00f, 0x2f8ad6d6, 0x2b4bcb61,
  52         0x350c9b64, 0x31cd86d3, 0x3c8ea00a, 0x384fbdbd, 0x4c11db70, 0x48d0c6c7,
  53         0x4593e01e, 0x4152fda9, 0x5f15adac, 0x5bd4b01b, 0x569796c2, 0x52568b75,
  54         0x6a1936c8, 0x6ed82b7f, 0x639b0da6, 0x675a1011, 0x791d4014, 0x7ddc5da3,
  55         0x709f7b7a, 0x745e66cd, 0x9823b6e0, 0x9ce2ab57, 0x91a18d8e, 0x95609039,
  56         0x8b27c03c, 0x8fe6dd8b, 0x82a5fb52, 0x8664e6e5, 0xbe2b5b58, 0xbaea46ef,
  57         0xb7a96036, 0xb3687d81, 0xad2f2d84, 0xa9ee3033, 0xa4ad16ea, 0xa06c0b5d,
  58         0xd4326d90, 0xd0f37027, 0xddb056fe, 0xd9714b49, 0xc7361b4c, 0xc3f706fb,
  59         0xceb42022, 0xca753d95, 0xf23a8028, 0xf6fb9d9f, 0xfbb8bb46, 0xff79a6f1,
  60         0xe13ef6f4, 0xe5ffeb43, 0xe8bccd9a, 0xec7dd02d, 0x34867077, 0x30476dc0,
  61         0x3d044b19, 0x39c556ae, 0x278206ab, 0x23431b1c, 0x2e003dc5, 0x2ac12072,
  62         0x128e9dcf, 0x164f8078, 0x1b0ca6a1, 0x1fcdbb16, 0x018aeb13, 0x54bf6a4,
  63         0x0808d07d, 0x0cc9cdca, 0x7897ab07, 0x7c56b6b0, 0x71159069, 0x75d48dde,
  64         0x6b93dddb, 0x6f52c06c, 0x6211e6b5, 0x66d0fb02, 0x5e9f46bf, 0x5a5e5b08,
  65         0x571d7dd1, 0x53dc6066, 0x4d9b3063, 0x495a2dd4, 0x44190b0d, 0x40d816ba,
  66         0xaca5c697, 0xa864db20, 0xa527fdf9, 0xa1e6e04e, 0xbfa1b04b, 0xbb60adfc,
  67         0xb6238b25, 0xb2e29692, 0x8aad2b2f, 0x8e6c3698, 0x832f1041, 0x87ee0df6,
  68         0x99a95df3, 0x9d684044, 0x902b669d, 0x94ea7b2a, 0xe0b41de7, 0xe4750050,
  69         0xe9362689, 0xedf73b3e, 0xf3b06b3b, 0xf771768c, 0xfa325055, 0xfef34de2,
  70         0xc6bcf05f, 0xc27dede8, 0xcf3ecb31, 0xcbffd686, 0xd5b88683, 0xd1799b34,
  71         0xdc3abded, 0xd8fba05a, 0x690ce0ee, 0x6dcdfd59, 0x608edb80, 0x644fc637,
  72         0x7a089632, 0x7ec98b85, 0x738aad5c, 0x774bb0eb, 0x4f040d56, 0x4bc510e1,
  73         0x46863638, 0x42472b8f, 0x5c007b8a, 0x58c1663d, 0x558240e4, 0x51435d53,
  74         0x251d3b9e, 0x21dc2629, 0x2c9f00f0, 0x285e1d47, 0x36194d42, 0x32d850f5,
  75         0x3f9b762c, 0x3b5a6b9b, 0x0315d626, 0x07d4cb91, 0x0a97ed48, 0xe56f0ff,
  76         0x1011a0fa, 0x14d0bd4d, 0x19939b94, 0x1d528623, 0xf12f560e, 0xf5ee4bb9,
  77         0xf8ad6d60, 0xfc6c70d7, 0xe22b20d2, 0xe6ea3d65, 0xeba91bbc, 0xef68060b,
  78         0xd727bbb6, 0xd3e6a601, 0xdea580d8, 0xda649d6f, 0xc423cd6a, 0xc0e2d0dd,
  79         0xcda1f604, 0xc960ebb3, 0xbd3e8d7e, 0xb9ff90c9, 0xb4bcb610, 0xb07daba7,
  80         0xae3afba2, 0xaafbe615, 0xa7b8c0cc, 0xa379dd7b, 0x9b3660c6, 0x9ff77d71,
  81         0x92b45ba8, 0x9675461f, 0x8832161a, 0x8cf30bad, 0x81b02d74, 0x857130c3,
  82         0x5d8a9099, 0x594b8d2e, 0x5408abf7, 0x50c9b640, 0x4e8ee645, 0x4a4ffbf2,
  83         0x470cdd2b, 0x43cdc09c, 0x7b827d21, 0x7f436096, 0x7200464f, 0x76c15bf8,
  84         0x68860bfd, 0x6c47164a, 0x61043093, 0x65c52d24, 0x119b4be9, 0x155a565e,
  85         0x18197087, 0x1cd86d30, 0x029f3d35, 0x065e2082, 0x0b1d065b, 0xfdc1bec,
  86         0x3793a651, 0x3352bbe6, 0x3e119d3f, 0x3ad08088, 0x2497d08d, 0x2056cd3a,
  87         0x2d15ebe3, 0x29d4f654, 0xc5a92679, 0xc1683bce, 0xcc2b1d17, 0xc8ea00a0,
  88         0xd6ad50a5, 0xd26c4d12, 0xdf2f6bcb, 0xdbee767c, 0xe3a1cbc1, 0xe760d676,
  89         0xea23f0af, 0xeee2ed18, 0xf0a5bd1d, 0xf464a0aa, 0xf9278673, 0xfde69bc4,
  90         0x89b8fd09, 0x8d79e0be, 0x803ac667, 0x84fbdbd0, 0x9abc8bd5, 0x9e7d9662,
  91         0x933eb0bb, 0x97ffad0c, 0xafb010b1, 0xab710d06, 0xa6322bdf, 0xa2f33668,
  92         0xbcb4666d, 0xb8757bda, 0xb5365d03, 0xb1f740b4};
  93
  94
  95 static u_long crc32(u_char *buf, unsigned len)
  96 {
  97         u_char *p;
  98         u_long  crc;
  99
 100         crc = 0xffffffff;       /* preload shift register, per CRC-32 spec */
 101         for (p = buf; len > 0; ++p, --len)
 102         {
 103                 crc = (crc << 8) ^ crc32_table[(crc >> 24) ^ *p];
 104         }
 105         return ~crc;            /* transmit complement, per CRC-32 spec */
 106 } /* crc32 */
 107
 108
 109
 110
 111
 112 //
 113 //
 114 // Trigger stuff
 115 //
 116
 117
 118
 119
 120 #ifndef PG70
 121 Datum dupecheck(PG_FUNCTION_ARGS)
 122 {
 123         TriggerData *trigdata = (TriggerData *) fcinfo->context;
 124 #else
 125 Datum dupecheck(void)
 126 {
 127         TriggerData *trigdata = CurrentTriggerData;
 128 #endif
 129         TupleDesc       tupdesc;
 130         HeapTuple       rettuple;
 131         bool            isnull,
 132                                 debug_on;
 133         int                     ret = 0, i, fnumber;
 134         u_long rowstrlen = 0,
 135                 crc;
 136         char **items, // will point to a malloc'ed array
 137                 *rowstring,
 138                 *query,
 139                 **args,
 140                 *relation;
 141         int num,
 142                 nargs,
 143                 nitems;
 144
 145         /* Make sure trigdata is pointing at what I expect */
 146 #ifdef PG70
 147         if (!CurrentTriggerData)
 148 #else
 149         if (!CALLED_AS_TRIGGER(fcinfo))
 150 #endif
 151                 elog(ERROR, "dupecheck: not fired by trigger manager");
 152         /* tuple to return to Executor */
 153         if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
 154                 rettuple = trigdata->tg_newtuple;
 155         else
 156                 rettuple = trigdata->tg_trigtuple;
 157
 158         if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
 159                 return PointerGetDatum(NULL);
 160
 161
 162         // Initialize arguments and see whether the arguments are ok.
 163         args = trigdata->tg_trigger->tgargs;
 164         nargs = trigdata->tg_trigger->tgnargs;
 165
 166         if (nargs < 2)
 167         {
 168                 elog(DEBUG, "dupecheck: USAGE: dupeckeck(debug, col1, col2, ...)");
 169                 return PointerGetDatum(NULL);
 170         }
 171
 172         debug_on = !strcmp("yes", args[0]);
 173
 174         tupdesc = trigdata->tg_relation->rd_att;
 175
 176         /* Connect to SPI manager */
 177         if ((ret = SPI_connect()) != SPI_OK_CONNECT)
 178         {
 179                 elog(NOTICE, "dupecheck: SPI_connect returned error %d", ret);
 180         }
 181
 182         // Now we are connected to the database's SPI manager
 183         // We will now construct a string of some important row values.
 184         // First, we determine how long the string will be to allocate memory
 185         // To include an additional item, add another SPI_getvalue line and
 186         // increase maxitems above by 1
 187
 188
 189         // Allocate array for the argument pointers
 190         nitems = nargs - 1; // don't take the debug parameter as a row name;
 191         items = (char **) malloc(sizeof(char*) * nitems);
 192         if (items == NULL)
 193         {
 194                 SPI_finish();
 195                 return PointerGetDatum(NULL);
 196         }
 197
 198         // Collect arguments
 199         for (i = 1; (i < nargs); i++)
 200         {
 201                 items[i-1] = SPI_getvalue(rettuple, tupdesc, SPI_fnumber(tupdesc, args[i]));
 202                 if (debug_on)
 203                 {
 204                         elog(DEBUG, "dupecheck: Argument %d: row %s -> %s\n", i, args[i], items[i-1]);
 205                 }
 206         }
 207
 208
 209         // Find out length of row string to be constructed
 210         for (i=0; (i < nitems); i++)
 211         {
 212                 if (items[i] != NULL)
 213                 {
 214                         rowstrlen += strlen(items[i]);
 215                 }
 216         }
 217
 218         rowstring = malloc(rowstrlen+1); // add space for 0-terminator
 219         if (rowstring == NULL)
 220         {
 221                 free(items);
 222                 SPI_finish();
 223                 return PointerGetDatum(NULL);
 224         }
 225
 226         // Construct row string
 227         *rowstring = 0;
 228         for (i=0; (i < nitems); i++)
 229         {
 230                 if (items[i] != NULL)
 231                 {
 232                         strcat(rowstring, items[i]);
 233                 }
 234         }
 235
 236         // rowstring now contains the data of the maxitems important
 237         // items of the table record. Now we calculate the CRC-32 checksum
 238         // of the rowstring
 239
 240         crc = crc32(rowstring, rowstrlen);
 241
 242         // Now we allocate some space and construct the SQL query
 243         relation = SPI_getrelname(trigdata->tg_relation);
 244         query = malloc(40 + strlen(relation) + 11 + 2 + 1); // SELECT part + relation + crc32 + "';" + 0-term
 245         if (query == NULL)
 246         {
 247                 // Big problem
 248                 free(items);
 249                 free(rowstring);
 250                 SPI_finish();
 251                 return PointerGetDatum(NULL);
 252         }
 253
 254         sprintf(query, "SELECT count(*) FROM %s WHERE checksum='%ld';", relation, crc);
 255         if (debug_on)
 256         {
 257                 elog(DEBUG, "dupecheck: %s", query);
 258         }
 259         ret = SPI_exec(query, 2);
 260
 261         if ((ret == SPI_OK_SELECT) && (SPI_processed > 0))
 262         {
 263 #ifdef PG72
 264                 num = (int) DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull));
 265 #else
 266                 num = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);
 267 #endif
 268         }
 269
 270         if ((ret == SPI_OK_SELECT) && (num > 0) && !(TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)))
 271         {
 272                 elog(NOTICE, "dupecheck: UBD in %s detected, dupe dropped", relation);
 273                 rettuple = NULL; // reject operation
 274         }
 275         else
 276         {
 277                 // The checksum wasn't found. So we modify the tuple to be
 278                 // inserted and add the fresh checksum.
 279                 int attnum;
 280                 Datum value;
 281                 char nulls = 0;
 282
 283                 if (debug_on)
 284                 {
 285                         elog(NOTICE, "dupecheck: Adding checksum to row");
 286                 }
 287                 attnum = SPI_fnumber(tupdesc, "checksum");
 288                 value = (Datum) crc;
 289                 rettuple = SPI_modifytuple(trigdata->tg_relation, rettuple, 1, &attnum, &value, &nulls);
 290         }
 291
 292         free(items);
 293         free(rowstring);
 294         free(query);
 295         SPI_finish();
 296         return PointerGetDatum(rettuple);
 297 }
 298