Personal wiki notebook (not under development)

reap.sql 3.3KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165
  1. -- Delete old demo users.
  2. delete from
  3. luminotes_user
  4. where
  5. username is null
  6. and
  7. luminotes_user.revision < now() - interval '1 day';
  8. -- Delete permissions for users who no longer exist.
  9. delete from
  10. user_notebook
  11. where
  12. user_id not in (
  13. select id from luminotes_user_current
  14. );
  15. -- Delete trash notebooks of forever-deleted notebooks, and all past revisions of them.
  16. delete from
  17. notebook
  18. where
  19. notebook.id in (
  20. select
  21. trash_id
  22. from
  23. notebook_current
  24. where
  25. id not in
  26. ( select notebook_id from user_notebook )
  27. and
  28. notebook_current.revision < now() - interval '1 day'
  29. );
  30. -- Delete forever-deleted notebooks, and all past revisions of them.
  31. delete from
  32. notebook
  33. where
  34. notebook.id in (
  35. select
  36. id
  37. from
  38. notebook_current
  39. where
  40. id not in
  41. ( select notebook_id from user_notebook )
  42. and
  43. notebook_current.revision < now() - interval '1 day'
  44. );
  45. -- Delete unused next ids, forever-deleted notes, and notes whose notebooks no longer exist.
  46. -- Also delete all past revisions of these notes.
  47. delete from
  48. note
  49. where
  50. note.id in (
  51. select
  52. id
  53. from
  54. note_current
  55. where (
  56. notebook_id is null or notebook_id not in
  57. ( select notebook_id from notebook_current )
  58. )
  59. and
  60. note_current.revision < now() - interval '1 day'
  61. );
  62. delete from
  63. note_current
  64. where (
  65. notebook_id is null or notebook_id not in
  66. ( select notebook_id from notebook_current )
  67. )
  68. and
  69. note_current.revision < now() - interval '1 day';
  70. -- Delete unused file next ids and files whose notebooks or notes no longer exist.
  71. delete from
  72. file
  73. where (
  74. notebook_id is null or notebook_id not in
  75. ( select notebook_id from notebook_current )
  76. or note_id not in
  77. ( select note_id from note_current )
  78. )
  79. and
  80. file.revision < now() - interval '1 day';
  81. -- Delete old notebook revisions.
  82. delete from
  83. notebook
  84. where
  85. revision not in (
  86. SELECT
  87. max( sub_notebook.revision ) as max
  88. from
  89. notebook sub_notebook
  90. where
  91. sub_notebook.id = notebook.id
  92. )
  93. and
  94. notebook.revision < now() - interval '1 week';
  95. -- Delete old group revisions.
  96. delete from
  97. luminotes_group
  98. where
  99. revision not in (
  100. SELECT
  101. max( sub_group.revision ) as max
  102. from
  103. luminotes_group sub_group
  104. where
  105. sub_group.id = luminotes_group.id
  106. )
  107. and
  108. luminotes_group.revision < now() - interval '1 week';
  109. -- Delete old user revisions.
  110. delete from
  111. luminotes_user
  112. where
  113. revision not in (
  114. SELECT
  115. max( sub_luminotes_user.revision ) as max
  116. from
  117. luminotes_user sub_luminotes_user
  118. where
  119. sub_luminotes_user.id = luminotes_user.id
  120. )
  121. and
  122. luminotes_user.revision < now() - interval '1 week';
  123. -- Delete permissions for notebooks that no longer exist.
  124. delete from
  125. user_notebook
  126. where
  127. notebook_id not in (
  128. select id from notebook_current
  129. );
  130. -- Delete permissions for users that no longer exist.
  131. delete from
  132. user_notebook
  133. where
  134. user_id not in (
  135. select id from luminotes_user_current
  136. );
  137. -- Delete memberships to groups that no longer exist.
  138. delete from
  139. user_group
  140. where
  141. group_id not in (
  142. select id from luminotes_group_current
  143. );
  144. -- Delete memberships of users that no longer exist.
  145. delete from
  146. user_group
  147. where
  148. user_id not in (
  149. select id from luminotes_user_current
  150. );
  151. vacuum analyze;