-
Notifications
You must be signed in to change notification settings - Fork 4
/
sql_scripts.py
95 lines (83 loc) · 2.91 KB
/
sql_scripts.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
from euphorie.client import model
from pkg_resources import resource_filename
from z3c.saconfig import Session
from Zope2.App import zcml
import logging
import sys
logging.basicConfig()
logger = logging.getLogger(__name__)
stdout = logging.StreamHandler(sys.stdout)
stderr = logging.StreamHandler(sys.stderr)
formatter = logging.Formatter(
"%(asctime)s %(levelname)s %(name)s %(message)s", "%Y-%m-%d %H:%M:%S"
)
stdout.setFormatter(formatter)
stdout.setLevel(logging.INFO)
logger.addHandler(stdout)
stderr.setFormatter(formatter)
stderr.setLevel(logging.ERROR)
logger.addHandler(stderr)
if sys.argv[0].endswith("/bin/test"):
config = resource_filename("euphorie.client.tests", "configure.zcml")
else:
config = "parts/instance/etc/package-includes/999-additional-overrides.zcml" # noqa: E501
zcml.load_config(config)
model.metadata.create_all(Session.bind, checkfirst=True)
session = Session()
class CleanUpGuestSessions:
sql = """
WITH old_guest_sessions AS (
SELECT session.id, count(tree.id) AS num_risks
FROM account, session LEFT JOIN tree
ON session.id = tree.session_id
WHERE session.account_id = account.id
AND account.account_type = 'guest'
AND session.created < current_date - interval '1 week'
GROUP BY session.id
)
DELETE FROM session USING old_guest_sessions
WHERE old_guest_sessions.id = session.id
AND old_guest_sessions.num_risks = 0;
WITH guest_accounts AS (
SELECT account.id, loginname, count(session.id) AS num_sessions
FROM account LEFT JOIN session
ON account.id = session.account_id OR account.id = session.last_modifier_id
WHERE account_type = 'guest' GROUP BY account.id
)
DELETE FROM account USING guest_accounts
WHERE guest_accounts.id = account.id
AND guest_accounts.num_sessions = 0;
"""
remove_obsolete_sessions_sql = """
select count(*) from account where account_type = 'guest' and loginname like 'guest-{year}-%';
delete from
account
where
id = any(
array(
select
id
from
account
where
account_type = 'guest'
and loginname like 'guest-{year}-%'
order by
id
limit
1000
)
);
select count(*) from account where account_type = 'guest' and loginname like 'guest-{year}-%';
""" # noqa: E501
def __call__(self):
session.execute("BEGIN;")
count_sql = "select count(*) from account where account_type = 'guest'"
old_count = session.execute(count_sql).first()
logger.warning(f"Current number of test sessions: {old_count[0]}")
session.execute(self.sql)
session.execute(self.remove_obsolete_sessions_sql.format(year=2020))
session.execute("COMMIT;")
new_count = session.execute(count_sql).first()
logger.warning(f"New number of test sessions: {new_count[0]}")
clean_up_guest_sessions = CleanUpGuestSessions()