summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorMatthias Andreas Benkard <code@mail.matthias.benkard.de>2011-03-19 23:12:31 +0100
committerMatthias Andreas Benkard <code@mail.matthias.benkard.de>2011-03-19 23:12:31 +0100
commit46c531b57631bc33d49fbf7ec90a10eaa69e69d5 (patch)
treea7798438c558812899aa9557f2126237bd8069e3
parent024c5fedce14509b4763f452d60cb9545c76dbb4 (diff)
Completely rethink the way articles are retrieved from the database based on the requested characteristics.
-rw-r--r--schema.sql74
-rw-r--r--site.lisp1
2 files changed, 27 insertions, 48 deletions
diff --git a/schema.sql b/schema.sql
index 35bf0b5..ccd8569 100644
--- a/schema.sql
+++ b/schema.sql
@@ -191,61 +191,39 @@ CREATE TYPE characteristic_list AS (
characteristics characteristic[]
);
-CREATE FUNCTION article_revisions_for_characteristics_with_fixed_ones(
+
+CREATE OR REPLACE FUNCTION article_revisions_for_characteristics(
article INTEGER,
- characteristic_lists characteristic_list[],
- fixed_characteristics characteristic_list
-) RETURNS INTEGER[] AS $BODY$
+ characteristic_lists characteristic_list[]
+) RETURNS SETOF INTEGER AS $BODY$
DECLARE
- query VARCHAR := $$SELECT id FROM article_revisions
- WHERE article = $$
- || quote_literal(article) ||
- $$ AND status IN ('published', 'syndicated')$$;
- fchar RECORD;
- revisions INTEGER[];
- specific_revisions INTEGER[];
- dummy INTEGER;
+ query VARCHAR;
+ required_chars RECORD;
+ charac RECORD;
BEGIN
- FOR fchar IN SELECT unnest(fixed_characteristics.characteristics) AS val LOOP
- query := query || $$AND EXISTS
- (SELECT 1
- FROM article_revision_characteristics
- WHERE revision = article_revisions.id
- AND characteristic = $$ || quote_literal((fchar.val::characteristic).characteristic) || $$
- AND value = $$ || quote_literal((fchar.val::characteristic).value) || $$)$$;
- END LOOP;
- query := query || $$ORDER BY date DESC$$;
- EXECUTE ('SELECT array(' || query || ')') INTO revisions;
- IF array_length(revisions, 1) > 0 THEN
- IF array_length(characteristic_lists, 1) > 0 THEN
- FOR fchar IN SELECT unnest((characteristic_lists[1]).characteristics) AS val LOOP
- specific_revisions := article_revisions_for_characteristics_with_fixed_ones(
- article,
- characteristic_lists[2:array_upper(characteristic_lists, 1)],
- ROW(array_prepend(fchar.val, fixed_characteristics.characteristics))::characteristic_list
- );
- IF array_length(specific_revisions, 1) > 0 THEN
- RETURN specific_revisions;
- END IF;
- END LOOP;
- RETURN revisions;
- ELSE
- RETURN revisions;
+ FOR required_chars IN SELECT unnest(characteristic_lists) AS val LOOP
+ query := $$SELECT id FROM article_revisions
+ WHERE article = $$
+ || quote_literal(article) ||
+ $$ AND status IN ('published', 'syndicated')$$;
+ FOR charac IN SELECT unnest((required_chars.val::characteristic_list).characteristics) AS val LOOP
+ query := query || $$ AND EXISTS
+ (SELECT 1
+ FROM article_revision_characteristics
+ WHERE revision = article_revisions.id
+ AND characteristic = $$ || quote_literal((charac.val::characteristic).characteristic) || $$
+ AND value = $$ || quote_literal((charac.val::characteristic).value) || $$)$$;
+ END LOOP;
+ query := query || $$ ORDER BY DATE DESC$$;
+ FOUND := false;
+ RETURN QUERY EXECUTE query;
+ IF FOUND THEN
+ RETURN;
END IF;
- ELSE
- RETURN ARRAY[]::INTEGER[];
- END IF;
+ END LOOP;
END
$BODY$ LANGUAGE plpgsql STABLE;
-CREATE FUNCTION article_revisions_for_characteristics(
- article INTEGER,
- characteristic_lists characteristic_list[]
-) RETURNS SETOF INTEGER AS $$
-BEGIN
- RETURN QUERY SELECT unnest(article_revisions_for_characteristics_with_fixed_ones(article, characteristic_lists, ROW(ARRAY[]::characteristic[])::characteristic_list));
-END
-$$ LANGUAGE plpgsql STABLE;
-- Usage example:
-- SELECT article_revisions_for_characteristics(70, ARRAY[ROW(ARRAY[ROW('language', 'de')::characteristic])]::characteristic_list[]);
diff --git a/site.lisp b/site.lisp
index 1d15521..502eb71 100644
--- a/site.lisp
+++ b/site.lisp
@@ -31,4 +31,5 @@
'((("language" . "en"))
(("language" . "la"))
(("language" . "de") ("s" . "long"))
+ (("language" . "de"))
(("language" . "fr"))))