Index

public.pages

Description

A web page that either contains course material or information about a course. The content of the page is stored in the content field as as an array of blocks that have to be rendered before showing to users. Each page has a unique URL which can be customized if necessary.

Columns

Name	Type	Default	Nullable	Children	Parents	Comment
chapter_id	uuid		true		public.chapters	Which chapter this page is part of. If null, the page is considered a top level page which is meant for generic information about the course. A top level page could be for example the course front page (“/”) or a frequently asked questions page (“/faq”).
content	jsonb		false			Page content in an abstract form. It is an array of JSON objects that are blocks. For example,a block could be a paragraph, or a image or an exercise.
content_search	tsvector		true			Used to perform full text searches on the title of the page and the content of the page. Content is considered to be any value in the page content schema with the key “content” or “title”. Page title is ranked higher in the search results as the content. The value of this is autogenerated with trigger trigger_set_pages_content_search.
content_search_language	varchar(255)	‘simple’::character varying	false
content_search_original_text	text		true			Language that will be used for stemming for full text search. Has to be a value from pg_ts_config.cfgname. The same value can be also determined from a courses.content_search_language but the string is duplicated here because the triggers generating text pages.content_search need to have this also in this table.
copied_from	uuid		true		public.pages	The original page record of a copy.
course_id	uuid		true		public.courses	The course the page is associated with.
created_at	timestamp with time zone	now()	false			Timestamp when the record was created.
deleted_at	timestamp with time zone		true			Timestamp when the record was deleted. If null, the record is not deleted.
exam_id	uuid		true		public.exams	The exam the page is associated with.
hidden	boolean	false	false			Whether or not this page should be publicly visible.
id	uuid	uuid_generate_v4()	false	public.chapters public.chatbot_page_sync_statuses public.exercises public.feedback public.page_audio_files public.page_history public.page_visit_datum public.page_visit_datum_summary_by_pages public.pages public.proposed_page_edits public.url_redirections		A unique, stable identifier for the record.
order_number	integer		false			A number used for sorting the pages when listing them. Pages are sorted within a chapter, or within the group of top level pages.
page_language_group_id	uuid		true		public.page_language_groups	If the page is related to a course, this can be used to find this exercise in other languages. If two pages share the same id, they are the same page but in different languages. For pages not related to a course, like exam pages, this is always null.
title	varchar(255)		false			Showed as the main heading to the students visiting the page. Also used in the html title attribute.
updated_at	timestamp with time zone	now()	false			Timestamp when the record was last updated. The field is updated automatically by the set_timestamp trigger.
url_path	varchar(255)		false			Relative url to the page within a course. It is autogenerated by default in the frontend for ease of use, but can be customized if desired. Example: “/part-1/3-hawaiian-crow”.

Constraints

Name	Type	Definition	Comment
check_page_language_group_id_defined	CHECK	CHECK (((course_id IS NULL) OR ((course_id IS NOT NULL) AND (page_language_group_id IS NOT NULL))))
course_or_exam_id_set	CHECK	CHECK (((course_id IS NULL) <> (exam_id IS NULL)))	A page must be associated with either a course or an exam.
pages_chapter_id_fkey	FOREIGN KEY	FOREIGN KEY (chapter_id) REFERENCES chapters(id)
pages_copied_from_fkey	FOREIGN KEY	FOREIGN KEY (copied_from) REFERENCES pages(id)
pages_course_id_fkey	FOREIGN KEY	FOREIGN KEY (course_id) REFERENCES courses(id)
pages_exam_id_fkey	FOREIGN KEY	FOREIGN KEY (exam_id) REFERENCES exams(id)
pages_order_number_uniqueness	UNIQUE	UNIQUE NULLS NOT DISTINCT (course_id, exam_id, chapter_id, order_number, deleted_at)
pages_page_language_group_id_fkey	FOREIGN KEY	FOREIGN KEY (page_language_group_id) REFERENCES page_language_groups(id)
pages_pkey	PRIMARY KEY	PRIMARY KEY (id)
unique_pages_url_path_course_id_when_not_deleted	UNIQUE	UNIQUE NULLS NOT DISTINCT (url_path, exam_id, course_id, deleted_at)

Indexes

Name	Definition
pages_chapter_id_idx	CREATE INDEX pages_chapter_id_idx ON public.pages USING btree (chapter_id)
pages_content_search	CREATE INDEX pages_content_search ON public.pages USING gin (content_search)
pages_course_id_idx	CREATE INDEX pages_course_id_idx ON public.pages USING btree (course_id)
pages_order_number_uniqueness	CREATE UNIQUE INDEX pages_order_number_uniqueness ON public.pages USING btree (course_id, exam_id, chapter_id, order_number, deleted_at) NULLS NOT DISTINCT
pages_page_language_group_id_idx	CREATE INDEX pages_page_language_group_id_idx ON public.pages USING btree (page_language_group_id)
pages_pkey	CREATE UNIQUE INDEX pages_pkey ON public.pages USING btree (id)
pages_url_path_idx	CREATE INDEX pages_url_path_idx ON public.pages USING btree (url_path)
unique_pages_url_path_course_id_when_not_deleted	CREATE UNIQUE INDEX unique_pages_url_path_course_id_when_not_deleted ON public.pages USING btree (url_path, exam_id, course_id, deleted_at) NULLS NOT DISTINCT

Triggers

Name	Definition
set_timestamp	CREATE TRIGGER set_timestamp BEFORE UPDATE ON public.pages FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp()
trigger_set_pages_content_search	CREATE TRIGGER trigger_set_pages_content_search BEFORE INSERT OR UPDATE ON public.pages FOR EACH ROW EXECUTE FUNCTION trigger_set_pages_content_search()

Relations

Generated by tbls