sql/taxonomy.sql

   1 -- -*- Mode: SQL; SQL-Product: Postgres; -*-
   2
   3 -- Terms, each associated with a taxonomy and a site
   4 -- Taxonomy is defined on Lisp side
   5 CREATE TABLE term (
   6     term_id SERIAL NOT NULL PRIMARY KEY,
   7     term_text VARCHAR(255) NOT NULL,
   8     term_slug VARCHAR(255) NOT NULL CHECK (term_slug SIMILAR TO '[a-z0-9-]+'),
   9     site_id INT NULL, -- REFERENCES site(site_id)
  10     term_taxonomy VARCHAR(32) NOT NULL DEFAULT '',
  11     term_description TEXT NULL
  12 );
  13 CREATE UNIQUE INDEX term_text_site_taxonomy_unique
  14                  ON term(term_text, site_id, term_taxonomy);
  15 CREATE UNIQUE INDEX term_slug_site_taxonomy_unique
  16                  ON term(term_slug, site_id, term_taxonomy);
  17
  18 -- item/term many-to-many
  19 CREATE TABLE item_term (
  20     item_id INT NOT NULL, -- REFERENCES item(item_id) ON DELETE CASCADE
  21     term_id INT NOT NULL REFERENCES term(term_id) ON DELETE CASCADE,
  22     item_term_value TEXT NULL,
  23     PRIMARY KEY (item_id, term_id)
  24 );
  25
  26 -- Create new term.  Signals error if term exists.
  27 -- FIXME: count existing slugs starting with new slug
  28 CREATE OR REPLACE FUNCTION new_term(
  29     the_text VARCHAR, the_slug VARCHAR, the_site_id INTEGER, the_taxonomy VARCHAR)
  30 RETURNS term
  31 AS $body$
  32 DECLARE
  33     res term;
  34     i INTEGER;
  35 BEGIN
  36     BEGIN
  37         INSERT INTO term(term_text, term_slug, site_id, term_taxonomy)
  38              VALUES (the_text, the_slug, the_site_id, the_taxonomy);
  39     EXCEPTION
  40     WHEN unique_violation THEN
  41         SELECT count(*) FROM term WHERE term_slug LIKE the_slug||'%' INTO i;
  42         LOOP
  43             i = i+1;
  44             IF i > 999 THEN RAISE EXCEPTION 'Afraid of infinite loop.';
  45             END IF;
  46             BEGIN
  47                 INSERT INTO term(term_text, term_slug, site_id, term_taxonomy)
  48                      VALUES (the_text, the_slug||'-'||i, the_site_id, the_taxonomy);
  49             EXCEPTION WHEN unique_violation THEN
  50                 IF POSITION('_slug_' IN SQLERRM) <> 0 THEN CONTINUE; END IF;
  51                 RAISE EXCEPTION 'Duplicate term.  How to re-raise from PL/PgSQL?';
  52             END;
  53             EXIT;               -- Exit loop when no unique_violation.
  54         END LOOP;
  55     END;
  56     SELECT INTO res * FROM term WHERE term_id=CURRVAL('term_term_id_seq');
  57     RETURN res;
  58 END;
  59 $body$ LANGUAGE plpgsql;
  60
  61 -- Selects or inserts term named `the_text' for site `the_site_id' in
  62 -- taxonomy `the_taxonomy'.  Returns found/new term.  No support for
  63 -- description or parent for new term, this function is intended to
  64 -- work with simple terms only (e.g. tags).
  65 CREATE OR REPLACE FUNCTION ensure_term(
  66     the_text VARCHAR,
  67     suggested_slug VARCHAR,
  68     the_site_id INTEGER,        -- CUSTOMIZE? function with the_site
  69                                 -- VARCHAR which selects id by slug
  70                                 -- and calls this one
  71     the_taxonomy VARCHAR)
  72 RETURNS term
  73 AS $body$
  74 DECLARE
  75     res term;
  76 BEGIN
  77     SELECT INTO res * FROM term
  78           WHERE term_text=the_text
  79             AND site_id=the_site_id
  80             AND term_taxonomy=the_taxonomy;
  81     IF NOT FOUND
  82     THEN res = new_term(the_text, suggested_slug, the_site_id, the_taxonomy);
  83     END IF;
  84     RETURN res;
  85 END;
  86 $body$ LANGUAGE plpgsql;
  87
  88 -- Sets term value for item (UPDATE or INSERT if needed).  When value
  89 -- is NULL, just ensures that an association is established.
  90 CREATE OR REPLACE FUNCTION set_item_term_value(
  91        the_item_id INTEGER,
  92        the_term_id INTEGER,
  93        the_new_value TEXT)
  94 RETURNS VOID
  95 AS $body$
  96 BEGIN
  97     UPDATE item_term
  98            SET item_term_value=the_new_value
  99            WHERE item_id=the_item_id AND term_id=the_term_id;
 100     IF NOT FOUND THEN
 101         INSERT INTO item_term(item_id, term_id, item_term_value)
 102             VALUES(the_item_id, the_term_id, the_new_value);
 103     END IF;
 104 END;
 105 $body$ LANGUAGE PLPGSQL;