Personal wiki notebook (not under development)
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

schema.sql 8.7KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274
  1. --
  2. -- PostgreSQL database schema
  3. --
  4. SET client_encoding = 'UTF8';
  5. SET check_function_bodies = false;
  6. SET client_min_messages = warning;
  7. SET search_path = public, pg_catalog;
  8. SET default_tablespace = '';
  9. SET default_with_oids = false;
  10. CREATE FUNCTION drop_html_tags(text) RETURNS text
  11. AS $_$select regexp_replace( regexp_replace( $1, '</?(div|p|br|ul|ol|li|h3)( [^>]*?)?/?>', ' ', 'gi' ), '<[^>]+?>', '', 'g' );$_$
  12. LANGUAGE sql;
  13. ALTER FUNCTION public.drop_html_tags(text) OWNER TO luminotes;
  14. create function log_note_revision() returns trigger as $_$
  15. begin
  16. insert into note values
  17. ( NEW.id, NEW.revision, NEW.title, NEW.contents, NEW.notebook_id, NEW.startup,
  18. NEW.deleted_from_id, NEW.rank, NEW.user_id );
  19. return null;
  20. end;
  21. $_$ language plpgsql;
  22. ALTER FUNCTION public.log_note_revision() OWNER TO luminotes;
  23. CREATE FUNCTION friendly_id(text) RETURNS text
  24. AS $_$select trim( both '-' from
  25. regexp_replace(
  26. regexp_replace(
  27. regexp_replace(
  28. lower( $1 ),
  29. '&[a-zA-Z]+;|&#\\d+;', ' ', 'g'
  30. ),
  31. '\\s+', '-', 'g'
  32. ),
  33. '[^a-zA-Z0-9\\-]', '', 'g'
  34. )
  35. );$_$
  36. LANGUAGE sql IMMUTABLE;
  37. ALTER FUNCTION public.friendly_id(text) OWNER TO luminotes;
  38. CREATE TABLE file (
  39. id text NOT NULL,
  40. revision timestamp with time zone,
  41. notebook_id text,
  42. note_id text,
  43. filename text,
  44. size_bytes integer,
  45. content_type text
  46. );
  47. ALTER TABLE public.file OWNER TO luminotes;
  48. CREATE TABLE tag (
  49. id text NOT NULL,
  50. revision timestamp with time zone,
  51. notebook_id text,
  52. user_id text,
  53. name text,
  54. description text
  55. );
  56. ALTER TABLE public.tag OWNER TO luminotes;
  57. CREATE TABLE tag_notebook (
  58. notebook_id text,
  59. tag_id text,
  60. value text,
  61. user_id text
  62. );
  63. ALTER TABLE public.tag_notebook OWNER TO luminotes;
  64. CREATE TABLE tag_note (
  65. note_id text,
  66. tag_id text,
  67. value text
  68. );
  69. ALTER TABLE public.tag_note OWNER TO luminotes;
  70. CREATE TABLE invite (
  71. id text NOT NULL,
  72. revision timestamp with time zone NOT NULL,
  73. from_user_id text,
  74. notebook_id text,
  75. email_address text,
  76. read_write boolean,
  77. "owner" boolean,
  78. redeemed_user_id text
  79. );
  80. ALTER TABLE public.invite OWNER TO luminotes;
  81. CREATE TABLE luminotes_group (
  82. id text NOT NULL,
  83. revision timestamp with time zone NOT NULL,
  84. name text
  85. );
  86. ALTER TABLE public.luminotes_group OWNER TO luminotes;
  87. CREATE VIEW luminotes_group_current AS
  88. SELECT luminotes_group.id, luminotes_group.revision, luminotes_group.name FROM luminotes_group WHERE (luminotes_group.revision IN (SELECT max(sub_group.revision) AS max FROM luminotes_group sub_group WHERE (sub_group.id = luminotes_group.id)));
  89. ALTER TABLE public.luminotes_group_current OWNER TO luminotes;
  90. CREATE TABLE luminotes_user (
  91. id text NOT NULL,
  92. revision timestamp with time zone NOT NULL,
  93. username text,
  94. salt text,
  95. password_hash text,
  96. email_address text,
  97. storage_bytes integer,
  98. rate_plan integer
  99. );
  100. ALTER TABLE public.luminotes_user OWNER TO luminotes;
  101. CREATE VIEW luminotes_user_current AS
  102. SELECT luminotes_user.id, luminotes_user.revision, luminotes_user.username, luminotes_user.salt, luminotes_user.password_hash, luminotes_user.email_address, luminotes_user.storage_bytes, luminotes_user.rate_plan FROM luminotes_user WHERE (luminotes_user.revision IN (SELECT max(sub_user.revision) AS max FROM luminotes_user sub_user WHERE (sub_user.id = luminotes_user.id)));
  103. ALTER TABLE public.luminotes_user_current OWNER TO luminotes;
  104. CREATE TABLE note (
  105. id text NOT NULL,
  106. revision timestamp with time zone NOT NULL,
  107. title text,
  108. contents text,
  109. notebook_id text,
  110. startup boolean DEFAULT false,
  111. deleted_from_id text,
  112. rank numeric,
  113. user_id text
  114. );
  115. ALTER TABLE public.note OWNER TO luminotes;
  116. CREATE TABLE note_current (
  117. id text NOT NULL,
  118. revision timestamp with time zone NOT NULL,
  119. title text,
  120. contents text,
  121. notebook_id text,
  122. startup boolean DEFAULT false,
  123. deleted_from_id text,
  124. rank numeric,
  125. search tsvector,
  126. user_id text
  127. );
  128. ALTER TABLE public.note_current OWNER TO luminotes;
  129. CREATE TABLE notebook (
  130. id text NOT NULL,
  131. revision timestamp with time zone NOT NULL,
  132. name text,
  133. trash_id text,
  134. deleted boolean DEFAULT false,
  135. user_id text
  136. );
  137. ALTER TABLE public.notebook OWNER TO luminotes;
  138. CREATE VIEW notebook_current AS
  139. SELECT notebook.id, notebook.revision, notebook.name, notebook.trash_id, notebook.deleted, notebook.user_id FROM notebook WHERE (notebook.revision IN (SELECT max(sub_notebook.revision) AS max FROM notebook sub_notebook WHERE (sub_notebook.id = notebook.id)));
  140. ALTER TABLE public.notebook_current OWNER TO luminotes;
  141. CREATE TABLE password_reset (
  142. id text NOT NULL,
  143. revision timestamp with time zone NOT NULL,
  144. email_address text,
  145. redeemed boolean
  146. );
  147. ALTER TABLE public.password_reset OWNER TO luminotes;
  148. CREATE TABLE download_access (
  149. id text NOT NULL,
  150. revision timestamp with time zone NOT NULL,
  151. item_number text,
  152. transaction_id text
  153. );
  154. ALTER TABLE public.download_access OWNER TO luminotes;
  155. CREATE TABLE user_group (
  156. user_id text NOT NULL,
  157. group_id text NOT NULL,
  158. "admin" boolean DEFAULT false
  159. );
  160. ALTER TABLE public.user_group OWNER TO luminotes;
  161. CREATE TABLE user_notebook (
  162. user_id text NOT NULL,
  163. notebook_id text NOT NULL,
  164. read_write boolean DEFAULT false,
  165. "owner" boolean DEFAULT false,
  166. rank numeric,
  167. own_notes_only boolean DEFAULT false
  168. );
  169. ALTER TABLE public.user_notebook OWNER TO luminotes;
  170. CREATE TABLE session (
  171. id text,
  172. data text,
  173. expiration_time timestamp
  174. );
  175. ALTER TABLE public.session OWNER TO luminotes;
  176. ALTER TABLE ONLY file
  177. ADD CONSTRAINT file_pkey PRIMARY KEY (id);
  178. ALTER TABLE ONLY tag
  179. ADD CONSTRAINT tag_pkey PRIMARY KEY (id);
  180. ALTER TABLE ONLY tag_notebook
  181. ADD CONSTRAINT tag_notebook_pkey PRIMARY KEY (user_id, notebook_id, tag_id);
  182. ALTER TABLE ONLY tag_note
  183. ADD CONSTRAINT tag_note_pkey PRIMARY KEY (note_id, tag_id);
  184. ALTER TABLE ONLY invite
  185. ADD CONSTRAINT invite_pkey PRIMARY KEY (id);
  186. ALTER TABLE ONLY luminotes_user
  187. ADD CONSTRAINT luminotes_user_pkey PRIMARY KEY (id, revision);
  188. ALTER TABLE ONLY note
  189. ADD CONSTRAINT note_pkey PRIMARY KEY (id, revision);
  190. ALTER TABLE ONLY note_current
  191. ADD CONSTRAINT note_current_pkey PRIMARY KEY (id);
  192. ALTER TABLE ONLY notebook
  193. ADD CONSTRAINT notebook_pkey PRIMARY KEY (id, revision);
  194. ALTER TABLE ONLY password_reset
  195. ADD CONSTRAINT password_reset_pkey PRIMARY KEY (id);
  196. ALTER TABLE ONLY download_access
  197. ADD CONSTRAINT download_access_pkey PRIMARY KEY (id);
  198. ALTER TABLE ONLY user_notebook
  199. ADD CONSTRAINT user_notebook_pkey PRIMARY KEY (user_id, notebook_id);
  200. CREATE INDEX file_note_id_index ON file USING btree (note_id);
  201. CREATE INDEX file_notebook_id_index ON file USING btree (notebook_id);
  202. CREATE INDEX tag_notebook_id_index ON tag USING btree (notebook_id);
  203. CREATE INDEX tag_user_id_index ON tag USING btree (user_id);
  204. CREATE INDEX luminotes_group_pkey ON luminotes_group USING btree (id, revision);
  205. CREATE INDEX luminotes_user_email_address_index ON luminotes_user USING btree (email_address);
  206. CREATE INDEX luminotes_user_username_index ON luminotes_user USING btree (username);
  207. CREATE INDEX note_notebook_id_index ON note USING btree (notebook_id);
  208. CREATE INDEX note_notebook_id_startup_index ON note USING btree (notebook_id, startup);
  209. CREATE INDEX note_notebook_id_title_index ON note USING btree (notebook_id, md5(title));
  210. CREATE INDEX note_user_id_index ON note USING btree (user_id);
  211. CREATE INDEX note_current_notebook_id_index ON note_current USING btree (notebook_id);
  212. CREATE INDEX note_current_notebook_id_startup_index ON note_current USING btree (notebook_id, startup);
  213. CREATE INDEX note_current_notebook_id_title_index ON note_current USING btree (notebook_id, md5(title));
  214. CREATE INDEX note_current_user_id_index ON note_current USING btree (user_id);
  215. CREATE INDEX note_current_search_index ON note_current USING gist (search);
  216. CREATE INDEX notebook_friendly_id_index ON notebook USING btree (friendly_id(name));
  217. CREATE INDEX password_reset_email_address_index ON password_reset USING btree (email_address);
  218. CREATE INDEX download_access_transaction_id_index ON download_access USING btree (transaction_id);
  219. CREATE INDEX session_id_index on session using btree (id);
  220. CREATE TRIGGER search_update
  221. BEFORE INSERT OR UPDATE ON note_current
  222. FOR EACH ROW
  223. EXECUTE PROCEDURE tsearch2('search', 'drop_html_tags', 'title', 'contents');
  224. CREATE TRIGGER note_current_update
  225. AFTER INSERT OR UPDATE ON note_current
  226. FOR EACH ROW
  227. EXECUTE PROCEDURE log_note_revision();
  228. REVOKE ALL ON SCHEMA public FROM PUBLIC;
  229. REVOKE ALL ON SCHEMA public FROM postgres;
  230. GRANT ALL ON SCHEMA public TO postgres;
  231. GRANT ALL ON SCHEMA public TO PUBLIC;