Context Navigation

Back to Ticket #8384

Ticket #8384: pages.sql

File pages.sql, 21.8 KB (added by Denis-de-Bernardy, 17 years ago)

Line
1	/*
2	* Table: pages
3	*/
4
5	CREATE TABLE pages (
6	id uuid PRIMARY KEY REFERENCES nodes ( id )
7	DEFERRABLE INITIALLY DEFERRED,
8	main_id uuid NOT NULL REFERENCES pages ( id ) ON DELETE CASCADE
9	DEFERRABLE INITIALLY DEFERRED,
10	r boolean,
11	w boolean,
12	status node_status NOT NULL DEFAULT 'draft',
13	published timestamp(0) with time zone,
14	modified timestamp(0) with time zone
15	NOT NULL DEFAULT now(),
16	user_id uuid REFERENCES users ( id ) ON DELETE SET NULL,
17	parent_id uuid REFERENCES pages ( id ),
18	order_id int NOT NULL,
19	lft int,
20	rgt int,
21	depth int,
22	sort_num int[],
23	key varchar NOT NULL DEFAULT '',
24	title varchar NOT NULL,
25	sort_name varchar NOT NULL,
26	scan_name varchar(1) NOT NULL,
27	abstract text NOT NULL DEFAULT '',
28	content text NOT NULL DEFAULT '',
29	tsv tsvector NOT NULL,
30	memo text NOT NULL DEFAULT '',
31	CHECK ( key <> '' OR status <> 'active' ),
32	CHECK ( title <> '' ),
33	CHECK ( order_id > 0 ),
34	CHECK ( lft IS NULL AND rgt IS NULL AND sort_num IS NULL AND depth IS NULL
35	OR lft IS NOT NULL AND lft > 0 AND rgt IS NOT NULL AND rgt > 0 AND sort_num IS NOT NULL AND sort_num >= '{1}'
36	),
37	CHECK ( r IS NULL AND w IS NULL OR r IS NOT NULL AND w IS NOT NULL ),
38	CHECK ( NOT ( parent_id IS NULL AND r IS NULL ) ),
39	CHECK ( NOT ( w AND NOT r ) ),
40	CONSTRAINT pages_main_id_key UNIQUE ( main_id, id ),
41	CONSTRAINT pages_valid_main_id_fkey FOREIGN KEY ( main_id, main_id )
42	REFERENCES pages ( main_id, id ) ON DELETE CASCADE
43	DEFERRABLE INITIALLY DEFERRED,
44	CONSTRAINT pages_valid_parent_id_fkey FOREIGN KEY ( parent_id, parent_id )
45	REFERENCES pages ( main_id, id ) ON DELETE CASCADE
46	DEFERRABLE INITIALLY DEFERRED
47	) WITH ( FILLFACTOR = 90 );
48
49	CREATE UNIQUE INDEX pages_key_key ON pages ( key, parent_id )
50	WHERE id = main_id AND key <> '';
51
52	CREATE INDEX pages_sort ON pages ( sort_name );
53	CREATE INDEX pages_scan ON pages ( scan_name, sort_name );
54	CREATE INDEX pages_tree ON pages ( lft, rgt DESC );
55	CREATE INDEX pages_tsv ON pages USING GIN ( tsv );
56
57	CREATE INDEX pages_parent ON pages ( parent_id ) WHERE id = main_id;
58	CREATE INDEX pages_latest ON pages ( main_id ) WHERE status <> 'revision';
59	CREATE INDEX pages_modified ON pages ( modified DESC, main_id );
60
61	CREATE INDEX pages_current ON pages ( id ) WHERE id = main_id;
62	CREATE INDEX pages_current_tree ON pages ( lft, rgt DESC ) WHERE id = main_id;
63
64	CREATE TRIGGER __insert_page BEFORE INSERT ON pages
65	FOR EACH ROW EXECUTE PROCEDURE __insert_node('page');
66
67	CREATE TRIGGER __delete_page AFTER DELETE ON pages
68	FOR EACH ROW EXECUTE PROCEDURE __delete_node();
69
70
71	/*
72	* Function: page_children(parent_id)
73	*/
74
75	CREATE OR REPLACE FUNCTION page_children(uuid)
76	RETURNS SETOF tree_nodes
77	AS $$
78	DECLARE
79	p_id alias for $1;
80	branch tree_nodes;
81	leaf tree_nodes;
82	i int := 0;
83	max_i int;
84	num int := 0;
85	BEGIN
86	FOR branch IN
87	SELECT main_id as id,
88	parent_id,
89	0 as order_id,
90	0 as lft,
91	0 as rgt,
92	'{}' as sort_num
93	FROM pages
94	WHERE id = main_id
95	AND parent_id = p_id
96	ORDER BY order_id
97	LOOP
98	num := num + 1;
99
100	branch.sort_num[1] = num;
101
102	i := i + 1;
103	branch.lft := i;
104
105	SELECT rgt
106	INTO max_i
107	FROM page_children(branch.id)
108	ORDER BY rgt DESC
109	LIMIT 1;
110
111	IF max_i IS NULL
112	THEN
113	branch.rgt := i + 1;
114
115	RETURN NEXT branch;
116
117	i := i + 1;
118	ELSE
119	branch.rgt := i + max_i + 1;
120
121	RETURN NEXT branch;
122
123	FOR leaf IN
124	SELECT id,
125	parent_id,
126	order_id,
127	lft + i,
128	rgt + i,
129	array_prepend(num, sort_num) as sort_num
130	FROM page_children(branch.id)
131	LOOP
132	RETURN NEXT leaf;
133	END LOOP;
134
135	i := i + max_i + 1;
136	END IF;
137	END LOOP;
138
139	RETURN;
140	END;
141	$$ LANGUAGE plpgsql STABLE STRICT;
142
143
144	/*
145	* Function: page_tree()
146	*/
147
148	CREATE OR REPLACE FUNCTION page_tree()
149	RETURNS SETOF tree_nodes
150	AS $$
151	DECLARE
152	branch tree_nodes;
153	leaf tree_nodes;
154	o_id int := 0;
155	i int := 0;
156	max_i int;
157	num int := 0;
158	BEGIN
159	FOR branch IN
160	SELECT main_id as id,
161	parent_id,
162	0 as order_id,
163	0 as lft,
164	0 as rgt,
165	'{}' as sort_num
166	FROM pages
167	WHERE parent_id IS NULL
168	AND id = main_id
169	ORDER BY order_id
170	LOOP
171	num := num + 1;
172	o_id := o_id + 1;
173	i := i + 1;
174
175	branch.sort_num[1] := num;
176	branch.order_id := o_id;
177	branch.lft := i;
178
179	SELECT rgt
180	INTO max_i
181	FROM page_children(branch.id)
182	ORDER BY rgt DESC
183	LIMIT 1;
184
185	IF max_i IS NULL
186	THEN
187	branch.rgt := i + 1;
188
189	RETURN NEXT branch;
190
191	i := i + 1;
192	ELSE
193	branch.rgt := i + max_i + 1;
194
195	RETURN NEXT branch;
196
197	FOR leaf IN
198	SELECT id,
199	parent_id,
200	order_id,
201	lft + i as lft,
202	rgt + i as rgt,
203	array_prepend(num, sort_num) as sort_num
204	FROM page_children(branch.id)
205	LOOP
206	o_id := o_id + 1;
207	leaf.order_id := leaf.order_id + o_id;
208
209	RETURN NEXT leaf;
210	END LOOP;
211
212	i := i + max_i + 1;
213	END IF;
214	END LOOP;
215
216	RETURN;
217	END;
218	$$ LANGUAGE plpgsql STABLE STRICT;
219
220
221	/*
222	* Trigger: _save_page()
223	*/
224
225	CREATE OR REPLACE FUNCTION _save_page()
226	RETURNS trigger
227	AS $$
228	DECLARE
229	do_stuff boolean := false;
230	str varchar;
231	arr int[];
232	i integer;
233	new_key varchar;
234	BEGIN
235	-- sanitize fields
236	NEW.title := trim(NEW.title);
237
238	-- sort_name, scan_name
239	NEW.sort_name := lower(NEW.title);
240	NEW.scan_name := upper(substr(NEW.title, 1, 1));
241
242	-- key
243	IF NEW.status = 'active' OR NEW.key <> ''
244	THEN
245	NEW.key := str2key(NEW.key);
246
247	IF NEW.key = ''
248	THEN
249	NEW.key := str2key(NEW.title);
250	END IF;
251
252	-- catch key conflict
253	new_key := NEW.key;
254
255	IF EXISTS (
256	SELECT NULL
257	FROM pages
258	WHERE key = new_key
259	AND parent_id IS NOT DISTINCT FROM NEW.parent_id
260	AND main_id <> NEW.main_id
261	)
262	THEN
263	i := 2;
264
265	IF new_key ~ ( '^' \|\| str2key(NEW.title) \|\| E'_\\d+$' )
266	THEN
267	NEW.key := str2key(NEW.title);
268	ELSE
269	NEW.key := new_key;
270	END IF;
271
272	LOOP
273	new_key := NEW.key \|\| '_' \|\| i;
274
275	IF NOT EXISTS (
276	SELECT NULL
277	FROM pages
278	WHERE key = new_key
279	AND parent_id IS NOT DISTINCT FROM NEW.parent_id
280	AND main_id <> NEW.main_id
281	)
282	THEN
283	NEW.key := new_key;
284	EXIT;
285	END IF;
286
287	i := i + 1;
288	END LOOP;
289	END IF;
290	END IF;
291
292	-- tsv
293	IF NEW.status <> 'revision'
294	THEN
295	NEW.tsv := setweight(to_tsvector(str2keywords(NEW.title)), 'A')
296	\|\| setweight(to_tsvector(tags2keywords(NEW.id)), 'B')
297	\|\| setweight(to_tsvector(xml2text(NEW.abstract)), 'C')
298	\|\| setweight(to_tsvector(xml2text(NEW.content)), 'D');
299	ELSE
300	NEW.tsv := '';
301	END IF;
302
303	RETURN NEW;
304	END;
305	$$ LANGUAGE plpgsql;
306
307	CREATE TRIGGER _save_page BEFORE INSERT OR UPDATE ON pages
308	FOR EACH ROW EXECUTE PROCEDURE _save_page();
309
310
311	/*
312	* Trigger: _insert_page()
313	*/
314
315	CREATE OR REPLACE FUNCTION _insert_page()
316	RETURNS trigger
317	AS $$
318	BEGIN
319	-- main_id
320	IF NEW.main_id IS NULL
321	THEN
322	NEW.main_id := NEW.id;
323	END IF;
324
325	-- active
326	IF NEW.status = 'active' AND NEW.published IS NULL
327	THEN
328	NEW.published := now();
329	ELSEIF NEW.status = 'future' AND NEW.published IS NULL
330	THEN
331	NEW.published := date_trunc('day', now() + interval '1 day');
332	ELSEIF NEW.status = 'future' AND NEW.published <= now()
333	THEN
334	NEW.status = 'active';
335	END IF;
336
337	-- lft, rgt, sort_num, depth
338	IF NEW.main_id <> NEW.id
339	THEN
340	SELECT lft,
341	rgt,
342	sort_num,
343	depth
344	INTO NEW.lft,
345	NEW.rgt,
346	NEW.sort_num,
347	NEW.depth
348	FROM pages
349	WHERE id = NEW.main_id;
350
351	IF NEW.status = 'active'
352	THEN
353	NEW.status := 'revision';
354	END IF;
355	END IF;
356
357	-- order_id
358	IF NEW.order_id IS NULL
359	THEN
360	SELECT order_id + 1
361	INTO NEW.order_id
362	FROM pages
363	WHERE parent_id IS NOT DISTINCT FROM NEW.parent_id
364	AND order_id IS NOT NULL
365	ORDER BY order_id DESC
366	LIMIT 1;
367
368	IF NOT FOUND
369	THEN
370	NEW.order_id := 1;
371	END IF;
372	END IF;
373
374	RETURN NEW;
375	END;
376	$$ LANGUAGE plpgsql;
377
378	CREATE TRIGGER _insert_page BEFORE INSERT ON pages
379	FOR EACH ROW EXECUTE PROCEDURE _insert_page();
380
381
382	/*
383	* Trigger: insert_page()
384	*/
385
386	CREATE OR REPLACE FUNCTION insert_page()
387	RETURNS trigger
388	AS $$
389	DECLARE
390	do_perms boolean := false;
391	profile_rec record;
392	page_rec record;
393	BEGIN
394	IF NEW.id <> NEW.main_id
395	THEN
396	IF NEW.status IN ('obsolete', 'revision', 'future')
397	THEN
398	-- publish
399	IF NEW.status <> 'future'
400	THEN
401	do_perms := NEW.status = 'obsolete'
402	OR NEW.status = 'revision'
403	AND EXISTS (
404	SELECT NULL
405	FROM pages
406	WHERE id = NEW.main_id
407	AND ROW(r, w, parent_id) IS DISTINCT FROM ROW(NEW.r, NEW.w, NEW.parent_id)
408	);
409
410	UPDATE pages
411	SET status = CASE NEW.status
412	WHEN 'revision'
413	THEN
414	'active'::node_status
415	ELSE
416	'obsolete'::node_status
417	END,
418	published = NEW.published,
419	user_id = NEW.user_id,
420	modified = now(),
421	key = NEW.key,
422	title = NEW.title,
423	abstract = NEW.abstract,
424	content = NEW.content,
425	memo = NEW.memo,
426	r = NEW.r,
427	w = NEW.w
428	WHERE id = NEW.main_id;
429	END IF;
430
431	-- auto-clean
432	DELETE FROM pages
433	WHERE main_id = NEW.main_id
434	AND id <> main_id
435	AND status IN ( 'obsolete', 'draft', 'pending' );
436	ELSE
437	do_perms := EXISTS (
438	SELECT NULL
439	FROM pages
440	WHERE id = NEW.main_id
441	AND ROW(r, w) IS DISTINCT FROM ROW(NEW.r, NEW.w)
442	);
443
444	IF do_perms
445	THEN
446	-- update perms
447	UPDATE pages
448	SET r = NEW.r,
449	w = NEW.w
450	WHERE main_id = NEW.main_id
451	AND id <> NEW.id;
452	END IF;
453
454	-- auto-clean
455	UPDATE pages
456	SET status = 'draft'
457	WHERE status IN ('pending', 'future')
458	AND main_id = NEW.main_id
459	AND id <> NEW.id;
460	END IF;
461	END IF;
462
463	-- enforce unique order_id
464	IF NEW.parent_id IS NULL
465	THEN
466	IF EXISTS (
467	SELECT NULL
468	FROM pages
469	WHERE parent_id IS NULL
470	AND main_id <> NEW.main_id
471	AND order_id = NEW.order_id
472	)
473	THEN
474	UPDATE pages
475	SET order_id = order_id + 1
476	WHERE parent_id IS NULL
477	AND main_id <> NEW.main_id
478	AND order_id >= NEW.order_id;
479	END IF;
480	ELSE
481	IF EXISTS (
482	SELECT NULL
483	FROM pages
484	WHERE parent_id = NEW.parent_id
485	AND main_id <> NEW.main_id
486	AND order_id = NEW.order_id
487	)
488	THEN
489	UPDATE pages
490	SET order_id = order_id + 1
491	WHERE parent_id = NEW.parent_id
492	AND main_id <> NEW.main_id
493	AND order_id >= NEW.order_id;
494	END IF;
495	END IF;
496
497	-- propagate parent_id and order_id
498	UPDATE pages
499	SET parent_id = NEW.parent_id,
500	order_id = NEW.order_id
501	WHERE main_id = NEW.main_id
502	AND ROW(parent_id, order_id) IS DISTINCT FROM ROW(NEW.parent_id, NEW.order_id);
503
504	IF FOUND OR NEW.id = NEW.main_id
505	THEN
506	-- reindex doc tree
507	UPDATE pages
508	SET lft = tree.lft,
509	rgt = tree.rgt,
510	sort_num = tree.sort_num,
511	order_id = tree.sort_num[array_upper(tree.sort_num, 1)],
512	depth = array_upper(tree.sort_num, 1) - 1
513	FROM page_tree() as tree
514	WHERE main_id = tree.id;
515	END IF;
516
517	IF NEW.id = NEW.main_id
518	THEN
519	-- insert into guest_pages
520	INSERT INTO guest_nodes
521	SELECT pages.id as id,
522	bool_and(parents.r IS NOT FALSE AND parents.status = 'active')
523	OR bool_or(parents.w IS TRUE) as read,
524	bool_or(parents.w IS TRUE) as write
525	FROM pages
526	JOIN pages as parents
527	ON parents.id = parents.main_id
528	AND parents.lft <= pages.lft
529	AND parents.rgt >= pages.rgt
530	WHERE pages.id = NEW.id
531	GROUP BY pages.id;
532	ELSEIF do_perms
533	THEN
534	-- update guest_pages
535	UPDATE guest_nodes as guest_pages
536	SET read = new_perms.read,
537	write = new_perms.write
538	FROM (
539	SELECT children.id,
540	bool_and(parents.r IS NOT FALSE AND parents.status = 'active')
541	OR bool_or(parents.w IS TRUE) as read,
542	bool_or(parents.w IS TRUE) as write
543	FROM pages
544	JOIN pages as children
545	ON children.id = children.main_id
546	AND children.lft >= pages.lft
547	AND children.rgt <= pages.rgt
548	JOIN pages as parents
549	ON parents.id = parents.main_id
550	AND parents.lft <= children.lft
551	AND parents.rgt >= children.rgt
552	WHERE pages.id = NEW.main_id
553	GROUP BY children.id
554	) as new_perms
555	WHERE guest_pages.id = new_perms.id
556	AND ROW(guest_pages.read, guest_pages.write) <> ROW(new_perms.read, new_perms.write);
557
558	FOR profile_rec IN
559	SELECT perms.profile_id
560	FROM pages
561	JOIN pages as children
562	ON children.id = children.main_id
563	AND children.lft >= pages.lft
564	AND children.rgt <= pages.rgt
565	JOIN profile2node as perms
566	ON perms.id = children.id
567	WHERE pages.id = NEW.main_id
568	GROUP BY perms.profile_id
569	LOOP
570	SELECT parents.lft,
571	parents.rgt
572	INTO page_rec
573	FROM pages
574	JOIN pages as children
575	ON children.id = children.main_id
576	AND children.lft >= pages.lft
577	AND children.rgt <= pages.rgt
578	JOIN pages as parents
579	ON parents.id = parents.main_id
580	AND parents.lft <= children.lft
581	AND parents.rgt >= children.rgt
582	JOIN page_perms as perms
583	ON perms.profile_id = profile_rec.profile_id
584	AND perms.id = parents.id
585	WHERE pages.id = NEW.main_id
586	ORDER BY parents.lft
587	LIMIT 1;
588
589	IF NOT FOUND
590	THEN
591	-- delete from profile_pages
592	DELETE FROM profile2node as perms
593	USING pages
594	JOIN pages as children
595	ON children.id = children.main_id
596	AND children.lft >= pages.lft
597	AND children.rgt <= pages.rgt
598	WHERE perms.profile_id = profile_rec.profile_id
599	AND perms.id = children.id
600	AND pages.id = NEW.main_id;
601	ELSE
602	-- delete from profile_pages
603	DELETE FROM profile2node as perms
604	USING pages as parents
605	WHERE perms.profile_id = profile_rec.profile_id
606	AND perms.id = parents.id
607	AND parents.id = parents.main_id
608	AND parents.lft < page_rec.lft
609	AND parents.rgt > page_rec.rgt;
610
611	-- update profile_pages
612	UPDATE profile2node as perms
613	SET read = new_perms.read,
614	write = new_perms.write,
615	sticky = new_perms.sticky
616	FROM (
617	SELECT children.id,
618	bool_and(perms.read AND parents.status = 'active')
619	OR bool_or(perms.write) as read,
620	bool_or(perms.write) as write,
621	bool_or(perms.sticky) as sticky
622	FROM pages
623	JOIN pages as children
624	ON children.id = children.main_id
625	AND children.lft >= pages.lft
626	AND children.rgt <= pages.rgt
627	JOIN pages as parents
628	ON parents.id = parents.main_id
629	AND parents.lft <= children.lft
630	AND parents.rgt >= children.rgt
631	JOIN page_perms as perms
632	ON perms.profile_id = profile_rec.profile_id
633	AND perms.id = parents.id
634	WHERE pages.id = NEW.main_id
635	GROUP BY children.id
636	) as new_perms
637	WHERE perms.profile_id = profile_rec.profile_id
638	AND perms.id = new_perms.id
639	AND ROW(perms.read, perms.write, perms.sticky) <> ROW(new_perms.read, new_perms.write, new_perms.sticky);
640	END IF;
641	END LOOP;
642	END IF;
643
644	RETURN NULL;
645	END;
646	$$ LANGUAGE plpgsql;
647
648	CREATE TRIGGER insert_page AFTER INSERT ON pages
649	FOR EACH ROW EXECUTE PROCEDURE insert_page();
650
651
652	/*
653	* Trigger: _cascade_page()
654	*/
655
656	CREATE OR REPLACE FUNCTION _cascade_page()
657	RETURNS trigger
658	AS $$
659	BEGIN
660	IF OLD.id = OLD.main_id
661	THEN
662	-- delete related pages
663	DELETE FROM pages
664	WHERE main_id = OLD.main_id
665	AND id <> OLD.id;
666	END IF;
667
668	RETURN OLD;
669	END;
670	$$ LANGUAGE plpgsql;
671
672	CREATE TRIGGER _cascade_page BEFORE DELETE ON pages
673	FOR EACH ROW EXECUTE PROCEDURE _cascade_page();
674
675
676	/*
677	* Trigger: delete_page()
678	*/
679
680	CREATE OR REPLACE FUNCTION delete_page()
681	RETURNS trigger
682	AS $$
683	BEGIN
684	IF OLD.id = OLD.main_id
685	THEN
686	-- reindex tree
687	UPDATE pages
688	SET lft = tree.lft,
689	rgt = tree.rgt,
690	sort_num = tree.sort_num,
691	order_id = tree.sort_num[array_upper(tree.sort_num, 1)],
692	depth = array_upper(tree.sort_num, 1) - 1
693	FROM page_tree() as tree
694	WHERE main_id = tree.id;
695	END IF;
696
697	RETURN NULL;
698	END;
699	$$ LANGUAGE plpgsql;
700
701	CREATE TRIGGER delete_page AFTER DELETE ON pages
702	FOR EACH ROW EXECUTE PROCEDURE delete_page();
703
704
705	/*
706	* Table: page_perms
707	*/
708
709	CREATE TABLE page_perms (
710	profile_id uuid NOT NULL REFERENCES profiles ( id ) ON DELETE CASCADE
711	DEFERRABLE INITIALLY DEFERRED,
712	id uuid NOT NULL REFERENCES pages ( id ) ON DELETE CASCADE
713	DEFERRABLE INITIALLY DEFERRED,
714	read boolean NOT NULL DEFAULT true,
715	write boolean NOT NULL DEFAULT false,
716	sticky boolean NOT NULL DEFAULT false,
717	PRIMARY KEY ( profile_id, id ),
718	CHECK ( NOT ( NOT read AND write ) ),
719	CHECK ( NOT ( NOT sticky AND NOT read ) ),
720	CHECK ( NOT ( NOT sticky AND write ) ),
721	CONSTRAINT page_perms_valid_id_fkey FOREIGN KEY ( id, id )
722	REFERENCES pages ( main_id, id ) ON DELETE CASCADE
723	DEFERRABLE INITIALLY DEFERRED
724	) WITH ( FILLFACTOR = 80 );
725
726	CREATE UNIQUE INDEX page_perms_reverse ON page_perms ( id, profile_id );
727	CREATE INDEX page_perms_profile_id ON page_perms ( profile_id );
728	CREATE INDEX page_perms_id ON page_perms ( id );
729
730
731	/*
732	* Trigger: insert_page_perms()
733	*/
734
735	CREATE OR REPLACE FUNCTION insert_page_perms()
736	RETURNS trigger
737	AS $$
738	BEGIN
739	-- insert into profile_pages
740	INSERT INTO profile2node
741	SELECT NEW.profile_id,
742	children.id,
743	bool_and(perms.read AND parents.status = 'active')
744	OR bool_or(perms.write) as read,
745	bool_or(perms.write) as write,
746	bool_or(perms.sticky) as sticky
747	FROM pages
748	JOIN pages as children
749	ON children.id = children.main_id
750	AND children.lft >= pages.lft
751	AND children.rgt <= pages.rgt
752	JOIN pages as parents
753	ON parents.id = parents.main_id
754	AND parents.lft <= children.lft
755	AND parents.rgt >= children.rgt
756	JOIN page_perms as perms
757	ON perms.profile_id = NEW.profile_id
758	AND perms.id = parents.id
759	LEFT JOIN profile2node
760	ON profile2node.profile_id = perms.profile_id
761	AND profile2node.id = children.id
762	WHERE pages.id = NEW.id
763	AND profile2node.id IS NULL
764	GROUP BY children.id;
765
766	-- update profile_pages
767	UPDATE profile2node as perms
768	SET read = new_perms.read,
769	write = new_perms.write,
770	sticky = new_perms.sticky
771	FROM (
772	SELECT children.id,
773	bool_and(perms.read AND parents.status = 'active')
774	OR bool_or(perms.write) as read,
775	bool_or(perms.write) as write,
776	bool_or(perms.sticky) as sticky
777	FROM pages
778	JOIN pages as children
779	ON children.id = children.main_id
780	AND children.lft >= pages.lft
781	AND children.rgt <= pages.rgt
782	JOIN pages as parents
783	ON parents.id = parents.main_id
784	AND parents.lft <= children.lft
785	AND parents.rgt >= children.rgt
786	JOIN page_perms as perms
787	ON perms.profile_id = NEW.profile_id
788	AND perms.id = parents.id
789	WHERE pages.id = NEW.id
790	GROUP BY children.id
791	) as new_perms
792	WHERE perms.profile_id = NEW.profile_id
793	AND perms.id = new_perms.id
794	AND ROW(perms.read, perms.write, perms.sticky) <> ROW(new_perms.read, new_perms.write, new_perms.sticky);
795
796	RETURN NULL;
797	END;
798	$$ LANGUAGE plpgsql;
799
800	CREATE TRIGGER insert_page_perms AFTER INSERT ON page_perms
801	FOR EACH ROW EXECUTE PROCEDURE insert_page_perms();
802
803
804	/*
805	* Trigger: update_page_perms()
806	*/
807
808	CREATE OR REPLACE FUNCTION update_page_perms()
809	RETURNS trigger
810	AS $$
811	BEGIN
812	IF ROW(NEW.read, NEW.write, NEW.sticky) <> ROW(OLD.read, OLD.write, OLD.sticky)
813	THEN
814	-- update profile_pages
815	UPDATE profile2node as perms
816	SET read = new_perms.read,
817	write = new_perms.write,
818	sticky = new_perms.sticky
819	FROM (
820	SELECT children.id,
821	bool_and(perms.read AND parents.status = 'active')
822	OR bool_or(perms.write) as read,
823	bool_or(perms.write) as write,
824	bool_or(perms.sticky) as sticky
825	FROM pages
826	JOIN pages as children
827	ON children.id = children.main_id
828	AND children.lft >= pages.lft
829	AND children.rgt <= pages.rgt
830	JOIN pages as parents
831	ON parents.id = parents.main_id
832	AND parents.lft <= children.lft
833	AND parents.rgt >= children.rgt
834	JOIN page_perms as perms
835	ON perms.profile_id = NEW.profile_id
836	AND perms.id = parents.id
837	WHERE pages.id = NEW.id
838	GROUP BY children.id
839	) as new_perms
840	WHERE perms.profile_id = NEW.profile_id
841	AND perms.id = new_perms.id
842	AND ROW(perms.read, perms.write, perms.sticky) <> ROW(new_perms.read, new_perms.write, new_perms.sticky);
843	END IF;
844
845	RETURN NULL;
846	END;
847	$$ LANGUAGE plpgsql;
848
849	CREATE TRIGGER update_page_perms AFTER UPDATE ON page_perms
850	FOR EACH ROW EXECUTE PROCEDURE update_page_perms();
851
852
853	/*
854	* Trigger: delete_page_perms()
855	*/
856
857	CREATE OR REPLACE FUNCTION delete_page_perms()
858	RETURNS trigger
859	AS $$
860	DECLARE
861	rec record;
862	BEGIN
863	SELECT parents.lft,
864	parents.rgt
865	INTO rec
866	FROM pages
867	JOIN pages as children
868	ON children.id = children.main_id
869	AND children.lft >= pages.lft
870	AND children.rgt <= pages.rgt
871	JOIN pages as parents
872	ON parents.id = parents.main_id
873	AND parents.lft <= children.lft
874	AND parents.rgt >= children.rgt
875	JOIN page_perms as perms
876	ON perms.profile_id = OLD.profile_id
877	AND perms.id = parents.id
878	WHERE pages.id = OLD.id
879	ORDER BY parents.lft
880	LIMIT 1;
881
882	IF NOT FOUND
883	THEN
884	-- delete from profile_pages
885	DELETE FROM profile2node as perms
886	USING pages
887	JOIN pages as children
888	ON children.id = children.main_id
889	AND children.lft >= pages.lft
890	AND children.rgt <= pages.rgt
891	WHERE perms.profile_id = OLD.profile_id
892	AND perms.id = children.id
893	AND pages.id = OLD.id;
894	ELSE
895	-- delete from profile_pages
896	DELETE FROM profile2node as perms
897	USING pages as parents
898	WHERE perms.profile_id = OLD.profile_id
899	AND perms.id = parents.id
900	AND parents.id = parents.main_id
901	AND parents.lft < rec.lft
902	AND parents.rgt > rec.rgt;
903
904	-- update profile_pages
905	UPDATE profile2node as perms
906	SET read = new_perms.read,
907	write = new_perms.write,
908	sticky = new_perms.sticky
909	FROM (
910	SELECT children.id,
911	bool_and(perms.read AND parents.status = 'active')
912	OR bool_or(perms.write) as read,
913	bool_or(perms.write) as write,
914	bool_or(perms.sticky) as sticky
915	FROM pages
916	JOIN pages as children
917	ON children.id = children.main_id
918	AND children.lft >= pages.lft
919	AND children.rgt <= pages.rgt
920	JOIN pages as parents
921	ON parents.id = parents.main_id
922	AND parents.lft <= children.lft
923	AND parents.rgt >= children.rgt
924	JOIN page_perms as perms
925	ON perms.profile_id = OLD.profile_id
926	AND perms.id = parents.id
927	WHERE pages.id = OLD.id
928	GROUP BY children.id
929	) as new_perms
930	WHERE perms.profile_id = OLD.profile_id
931	AND perms.id = new_perms.id
932	AND ROW(perms.read, perms.write, perms.sticky) <> ROW(new_perms.read, new_perms.write, new_perms.sticky);
933	END IF;
934
935	RETURN NULL;
936	END;
937	$$ LANGUAGE plpgsql;
938
939	CREATE TRIGGER delete_page_perms AFTER DELETE ON page_perms
940	FOR EACH ROW EXECUTE PROCEDURE delete_page_perms();

Download in other formats:

Original Format

Make WordPress Core

Context Navigation

Ticket #8384: pages.sql

Download in other formats: