witten
/
luminotes
Archived
1
0
Fork 0
This repository has been archived on 2023-12-16. You can view files and clone it, but cannot push or open issues or pull requests.
luminotes/model/reap.sql

166 lines
3.3 KiB
SQL

-- Delete old demo users.
delete from
luminotes_user
where
username is null
and
luminotes_user.revision < now() - interval '1 day';
-- Delete permissions for users who no longer exist.
delete from
user_notebook
where
user_id not in (
select id from luminotes_user_current
);
-- Delete trash notebooks of forever-deleted notebooks, and all past revisions of them.
delete from
notebook
where
notebook.id in (
select
trash_id
from
notebook_current
where
id not in
( select notebook_id from user_notebook )
and
notebook_current.revision < now() - interval '1 day'
);
-- Delete forever-deleted notebooks, and all past revisions of them.
delete from
notebook
where
notebook.id in (
select
id
from
notebook_current
where
id not in
( select notebook_id from user_notebook )
and
notebook_current.revision < now() - interval '1 day'
);
-- Delete unused next ids, forever-deleted notes, and notes whose notebooks no longer exist.
-- Also delete all past revisions of these notes.
delete from
note
where
note.id in (
select
id
from
note_current
where (
notebook_id is null or notebook_id not in
( select notebook_id from notebook_current )
)
and
note_current.revision < now() - interval '1 day'
);
delete from
note_current
where (
notebook_id is null or notebook_id not in
( select notebook_id from notebook_current )
)
and
note_current.revision < now() - interval '1 day';
-- Delete unused file next ids and files whose notebooks or notes no longer exist.
delete from
file
where (
notebook_id is null or notebook_id not in
( select notebook_id from notebook_current )
or note_id not in
( select note_id from note_current )
)
and
file.revision < now() - interval '1 day';
-- Delete old notebook revisions.
delete from
notebook
where
revision not in (
SELECT
max( sub_notebook.revision ) as max
from
notebook sub_notebook
where
sub_notebook.id = notebook.id
)
and
notebook.revision < now() - interval '1 week';
-- Delete old group revisions.
delete from
luminotes_group
where
revision not in (
SELECT
max( sub_group.revision ) as max
from
luminotes_group sub_group
where
sub_group.id = luminotes_group.id
)
and
luminotes_group.revision < now() - interval '1 week';
-- Delete old user revisions.
delete from
luminotes_user
where
revision not in (
SELECT
max( sub_luminotes_user.revision ) as max
from
luminotes_user sub_luminotes_user
where
sub_luminotes_user.id = luminotes_user.id
)
and
luminotes_user.revision < now() - interval '1 week';
-- Delete permissions for notebooks that no longer exist.
delete from
user_notebook
where
notebook_id not in (
select id from notebook_current
);
-- Delete permissions for users that no longer exist.
delete from
user_notebook
where
user_id not in (
select id from luminotes_user_current
);
-- Delete memberships to groups that no longer exist.
delete from
user_group
where
group_id not in (
select id from luminotes_group_current
);
-- Delete memberships of users that no longer exist.
delete from
user_group
where
user_id not in (
select id from luminotes_user_current
);
vacuum analyze;