Personal wiki notebook (not under development)

schema.sqlite 5.8KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216
  1. --
  2. -- SQLite database schema
  3. --
  4. CREATE TABLE file (
  5. id text NOT NULL,
  6. revision timestamp with time zone,
  7. notebook_id text,
  8. note_id text,
  9. filename text,
  10. size_bytes integer,
  11. content_type text
  12. );
  13. CREATE TABLE tag (
  14. id text NOT NULL,
  15. revision timestamp with time zone,
  16. notebook_id text,
  17. user_id text,
  18. name text,
  19. description text
  20. );
  21. CREATE TABLE tag_notebook (
  22. notebook_id text,
  23. tag_id text,
  24. value text,
  25. user_id text
  26. );
  27. CREATE TABLE tag_note (
  28. note_id text,
  29. tag_id text,
  30. value text
  31. );
  32. CREATE TABLE invite (
  33. id text NOT NULL,
  34. revision timestamp with time zone NOT NULL,
  35. from_user_id text,
  36. notebook_id text,
  37. email_address text,
  38. read_write boolean,
  39. "owner" boolean,
  40. redeemed_user_id text
  41. );
  42. CREATE TABLE luminotes_group (
  43. id text NOT NULL,
  44. revision timestamp with time zone NOT NULL,
  45. name text
  46. );
  47. CREATE VIEW luminotes_group_current AS
  48. SELECT id, revision, 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)));
  49. CREATE TABLE luminotes_user (
  50. id text NOT NULL,
  51. revision timestamp with time zone NOT NULL,
  52. username text,
  53. salt text,
  54. password_hash text,
  55. email_address text,
  56. storage_bytes integer,
  57. rate_plan integer
  58. );
  59. CREATE VIEW luminotes_user_current AS
  60. SELECT id, revision, username, salt, password_hash, email_address, storage_bytes, 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)));
  61. CREATE TABLE note (
  62. id text NOT NULL,
  63. revision timestamp with time zone NOT NULL,
  64. title text,
  65. contents text,
  66. notebook_id text,
  67. startup boolean DEFAULT false,
  68. deleted_from_id text,
  69. rank numeric,
  70. search tsvector,
  71. user_id text
  72. );
  73. CREATE TABLE note_current (
  74. id text NOT NULL,
  75. revision timestamp with time zone NOT NULL,
  76. title text,
  77. contents text,
  78. notebook_id text,
  79. startup boolean DEFAULT false,
  80. deleted_from_id text,
  81. rank numeric,
  82. user_id text
  83. );
  84. CREATE TABLE notebook (
  85. id text NOT NULL,
  86. revision timestamp with time zone NOT NULL,
  87. name text,
  88. trash_id text,
  89. deleted boolean DEFAULT false,
  90. user_id text
  91. );
  92. CREATE VIEW notebook_current AS
  93. SELECT id, revision, name, trash_id, deleted, 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))) and notebook.name is not null;
  94. CREATE TABLE password_reset (
  95. id text NOT NULL,
  96. revision timestamp with time zone NOT NULL,
  97. email_address text,
  98. redeemed boolean
  99. );
  100. CREATE TABLE download_access (
  101. id text NOT NULL,
  102. revision timestamp with time zone NOT NULL,
  103. item_number text,
  104. transaction_id text
  105. );
  106. CREATE TABLE user_group (
  107. user_id text NOT NULL,
  108. group_id text NOT NULL,
  109. "admin" boolean DEFAULT false
  110. );
  111. CREATE TABLE user_notebook (
  112. user_id text NOT NULL,
  113. notebook_id text NOT NULL,
  114. read_write boolean DEFAULT false,
  115. "owner" boolean DEFAULT false,
  116. rank numeric,
  117. own_notes_only boolean DEFAULT false
  118. );
  119. CREATE INDEX file_pkey ON file (id);
  120. CREATE INDEX tag_pkey ON tag (id);
  121. CREATE INDEX tag_notebook_pkey ON tag_notebook (user_id, notebook_id, tag_id);
  122. CREATE INDEX tag_note_pkey ON tag_note (note_id, tag_id);
  123. CREATE INDEX invite_pkey ON invite (id);
  124. CREATE INDEX luminotes_user_pkey ON luminotes_user (id, revision);
  125. CREATE INDEX note_pkey ON note (id, revision);
  126. CREATE INDEX note_current_pkey ON note_current (id);
  127. CREATE INDEX notebook_pkey ON notebook (id, revision);
  128. CREATE INDEX password_reset_pkey ON password_reset (id);
  129. CREATE INDEX download_access_pkey ON download_access (id);
  130. CREATE INDEX user_notebook_pkey ON user_notebook (user_id, notebook_id);
  131. CREATE INDEX file_note_id_index ON file (note_id);
  132. CREATE INDEX file_notebook_id_index ON file (notebook_id);
  133. CREATE INDEX tag_notebook_id_index ON tag (notebook_id);
  134. CREATE INDEX tag_user_id_index ON tag (user_id);
  135. CREATE INDEX luminotes_group_pkey ON luminotes_group (id, revision);
  136. CREATE INDEX luminotes_user_email_address_index ON luminotes_user (email_address);
  137. CREATE INDEX luminotes_user_username_index ON luminotes_user (username);
  138. CREATE INDEX note_notebook_id_index ON note (notebook_id);
  139. CREATE INDEX note_notebook_id_startup_index ON note (notebook_id, startup);
  140. CREATE INDEX note_notebook_id_title_index ON note (notebook_id, title);
  141. CREATE INDEX note_user_id_index ON note (user_id);
  142. CREATE INDEX note_current_notebook_id_index ON note_current (notebook_id);
  143. CREATE INDEX note_current_notebook_id_startup_index ON note_current (notebook_id, startup);
  144. CREATE INDEX note_current_notebook_id_title_index ON note_current (notebook_id, title);
  145. CREATE INDEX note_current_user_id_index ON note_current (user_id);
  146. CREATE INDEX password_reset_id_index ON password_reset (id);
  147. CREATE INDEX password_reset_email_address_index ON password_reset (email_address);
  148. CREATE INDEX download_access_id_index ON password_reset (id);
  149. CREATE INDEX download_access_transaction_id_index ON download_access (transaction_id);
  150. CREATE INDEX search_index ON note (search);
  151. create trigger note_current_insert
  152. after insert on note_current
  153. for each row
  154. begin
  155. insert into note values ( NEW.id, NEW.revision, NEW.title, NEW.contents, NEW.notebook_id,
  156. NEW.startup, NEW.deleted_from_id, NEW.rank, null, NEW.user_id );
  157. end;
  158. create trigger note_current_update
  159. after update on note_current
  160. for each row
  161. begin
  162. insert into note values ( NEW.id, NEW.revision, NEW.title, NEW.contents, NEW.notebook_id,
  163. NEW.startup, NEW.deleted_from_id, NEW.rank, null, NEW.user_id );
  164. end;