From 4eca0396465a27c1d2b1b38959bf3c8c4ea07dd6 Mon Sep 17 00:00:00 2001 From: Matthias Andreas Benkard Date: Sat, 2 Dec 2023 13:46:54 +0100 Subject: db: Fix syntax in various migration change sets. Change-Id: Ie6f7f2d8b475b00db58717a657c8946d27f2f880 --- src/main/resources/db/changeLog-1.0.xml | 81 ++++++++++++++++++--------------- 1 file changed, 45 insertions(+), 36 deletions(-) (limited to 'src/main/resources') diff --git a/src/main/resources/db/changeLog-1.0.xml b/src/main/resources/db/changeLog-1.0.xml index c4c911a..f12ad9c 100644 --- a/src/main/resources/db/changeLog-1.0.xml +++ b/src/main/resources/db/changeLog-1.0.xml @@ -428,8 +428,8 @@ referencedTableName="users" validate="true"/> - - SELECT a.id AS article, + + SELECT a.id AS article, count(c.*) AS comment_count FROM (articles a LEFT JOIN comments c ON (((c.article = a.id) AND (EXISTS ( SELECT comment_revisions.id, @@ -463,8 +463,8 @@ referencedTableName="articles" validate="true"/> - - SELECT + + SELECT article_revisions.article, article_revisions.id AS revision FROM (( SELECT article_revisions_1.id @@ -476,8 +476,8 @@ - - SELECT + + SELECT article_revisions.article, min(article_revisions.date) AS publishing_date FROM article_revisions @@ -501,24 +501,27 @@ referencedTableName="users" validate="true"/> - + + 9:1ddb31e4daad9a3e759f908348d585df + name="((setweight(to_tsvector('english'::regconfig, (title)::text), 'A'::"char") || setweight(to_tsvector('english'::regconfig, (content)::text), 'D'::"char")))"/> - + + 9:8e65575f838c89b5963c00deb9d41ca9 + name="((setweight(to_tsvector('french'::regconfig, (title)::text), 'A'::"char") || setweight(to_tsvector('french'::regconfig, (content)::text), 'D'::"char")))"/> - + + 9:b388ba620250e303e711546b7e8c79a7 + name="((setweight(to_tsvector('german'::regconfig, (title)::text), 'A'::"char") || setweight(to_tsvector('german'::regconfig, (content)::text), 'D'::"char")))"/> @@ -652,6 +655,12 @@ startValue="1"/> + + + CREATE SCHEMA benki + + + @@ -968,17 +977,17 @@ referencedTableName="users" referencedTableSchemaName="benki" validate="true"/> - - WITH + + WITH RECURSIVE t(superrole, subrole) AS ( SELECT roles.id, roles.id - FROM roles + FROM benki.roles roles UNION SELECT t_1.superrole, rs.subrole FROM (t t_1 - JOIN role_subroles rs ON ((rs.superrole = t_1.subrole))) + JOIN benki.role_subroles rs ON ((rs.superrole = t_1.subrole))) ) SELECT t.superrole, t.subrole @@ -1036,23 +1045,23 @@ referencedTableName="wiki_pages" referencedTableSchemaName="benki" validate="true"/> - - SELECT + + SELECT ur."user", er.subrole AS role - FROM (user_roles ur - JOIN effective_role_subroles er ON ((er.superrole = ur.role))) + FROM (benki.user_roles ur + JOIN benki.effective_role_subroles er ON ((er.superrole = ur.role))) UNION SELECT u.id AS "user", rt.role - FROM users u, - role_tags rt + FROM benki.users u, + benki.role_tags rt WHERE ((rt.tag)::text = ANY (ARRAY[('everyone'::character varying)::text, ('world'::character varying)::text])) UNION SELECT NULL::integer AS "user", rt.role - FROM role_tags rt + FROM benki.role_tags rt WHERE ((rt.tag)::text = 'world'::text); @@ -1071,17 +1080,17 @@ referencedTableName="roles" referencedTableSchemaName="benki" validate="true"/> - - SELECT + + SELECT eur."user", t.message - FROM effective_user_roles eur, - post_targets t + FROM benki.effective_user_roles eur, + benki.post_targets t WHERE (t.target = eur.role) UNION SELECT m.owner AS "user", m.id AS message - FROM posts m; + FROM benki.posts m; @@ -1099,12 +1108,12 @@ referencedTableName="users" referencedTableSchemaName="benki" validate="true"/> - - SELECT + + SELECT uvp."user", uvp.message - FROM (user_visible_posts uvp - JOIN bookmarks bm ON ((bm.id = uvp.message))); + FROM (benki.user_visible_posts uvp + JOIN benki.bookmarks bm ON ((bm.id = uvp.message))); @@ -1123,12 +1132,12 @@ referencedTableSchemaName="benki" validate="true"/> - - + + SELECT uvp."user", uvp.message - FROM (user_visible_posts uvp - JOIN lazychat_messages lm ON ((lm.id = uvp.message))); + FROM (benki.user_visible_posts uvp + JOIN benki.lazychat_messages lm ON ((lm.id = uvp.message))); -- cgit v1.2.3