Coverage for src/couchers/materialized_views.py: 100%

1import logging 

2 

3from sqlalchemy import Index 

4from sqlalchemy.sql import func 

5from sqlalchemy.sql import select as sa_select 

6from sqlalchemy_utils import create_materialized_view, refresh_materialized_view 

7 

8from couchers.db import session_scope 

9from couchers.models import Base, ClusterRole, ClusterSubscription, User 

10 

11logger = logging.getLogger(__name__) 

12 

13cluster_subscription_counts_selectable = ( 

14 sa_select( 

15 ClusterSubscription.cluster_id.label("cluster_id"), 

16 func.count().label("count"), 

17 ) 

18 .select_from(ClusterSubscription) 

19 .outerjoin(User, User.id == ClusterSubscription.user_id) 

20 .where(User.is_visible) 

21 .group_by(ClusterSubscription.cluster_id) 

22) 

23 

24cluster_subscription_counts = create_materialized_view( 

25 "cluster_subscription_counts", 

26 cluster_subscription_counts_selectable, 

27 Base.metadata, 

28 [ 

29 Index( 

30 "uq_cluster_subscription_counts_cluster_id", 

31 cluster_subscription_counts_selectable.c.cluster_id, 

32 unique=True, 

33 ) 

34 ], 

35) 

36 

37cluster_admin_counts_selectable = ( 

38 sa_select( 

39 ClusterSubscription.cluster_id.label("cluster_id"), 

40 func.count().label("count"), 

41 ) 

42 .select_from(ClusterSubscription) 

43 .outerjoin(User, User.id == ClusterSubscription.user_id) 

44 .where(ClusterSubscription.role == ClusterRole.admin) 

45 .where(User.is_visible) 

46 .group_by(ClusterSubscription.cluster_id) 

47) 

48 

49cluster_admin_counts = create_materialized_view( 

50 "cluster_admin_counts", 

51 cluster_admin_counts_selectable, 

52 Base.metadata, 

53 [Index("uq_cluster_admin_counts_cluster_id", cluster_admin_counts_selectable.c.cluster_id, unique=True)], 

54) 

55 

56 

57def refresh_materialized_views(): 

58 logger.info("Refreshing materialized views") 

59 with session_scope() as session: 

60 refresh_materialized_view(session, "cluster_subscription_counts", concurrently=True) 

61 refresh_materialized_view(session, "cluster_admin_counts", concurrently=True)