Coverage for src/couchers/models/clusters.py: 98%

1import enum 

2 

3from geoalchemy2 import Geometry 

4from sqlalchemy import ( 

5 BigInteger, 

6 Boolean, 

7 CheckConstraint, 

8 Column, 

9 DateTime, 

10 Enum, 

11 ForeignKey, 

12 Index, 

13 String, 

14 UniqueConstraint, 

15 func, 

16 text, 

17) 

18from sqlalchemy.ext.associationproxy import association_proxy 

19from sqlalchemy.orm import backref, column_property, deferred, relationship 

20from sqlalchemy.sql import expression 

21 

22from couchers.models.base import Base, communities_seq 

23from couchers.utils import get_coordinates 

24 

25 

26class Node(Base): 

27 """ 

28 Node, i.e., geographical subdivision of the world 

29 

30 Administered by the official cluster 

31 """ 

32 

33 __tablename__ = "nodes" 

34 

35 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value()) 

36 

37 # name and description come from official cluster 

38 parent_node_id = Column(ForeignKey("nodes.id"), nullable=True, index=True) 

39 geom = deferred(Column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False)) 

40 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

41 

42 parent_node = relationship("Node", backref="child_nodes", remote_side="Node.id") 

43 

44 contained_users = relationship( 

45 "User", 

46 primaryjoin="func.ST_Contains(foreign(Node.geom), User.geom).as_comparison(1, 2)", 

47 viewonly=True, 

48 uselist=True, 

49 ) 

50 

51 contained_user_ids = association_proxy("contained_users", "id") 

52 

53 

54class Cluster(Base): 

55 """ 

56 Cluster, administered grouping of content 

57 """ 

58 

59 __tablename__ = "clusters" 

60 

61 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value()) 

62 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True) 

63 name = Column(String, nullable=False) 

64 # short description 

65 description = Column(String, nullable=False) 

66 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

67 

68 is_official_cluster = Column(Boolean, nullable=False, default=False) 

69 

70 discussions_enabled = Column(Boolean, nullable=False, default=True, server_default=expression.true()) 

71 events_enabled = Column(Boolean, nullable=False, default=True, server_default=expression.true()) 

72 

73 slug = column_property(func.slugify(name)) 

74 

75 official_cluster_for_node = relationship( 

76 "Node", 

77 primaryjoin="and_(Cluster.parent_node_id == Node.id, Cluster.is_official_cluster)", 

78 backref=backref("official_cluster", uselist=False), 

79 uselist=False, 

80 viewonly=True, 

81 ) 

82 

83 parent_node = relationship( 

84 "Node", backref="child_clusters", remote_side="Node.id", foreign_keys="Cluster.parent_node_id" 

85 ) 

86 

87 nodes = relationship("Cluster", backref="clusters", secondary="node_cluster_associations", viewonly=True) 

88 # all pages 

89 pages = relationship( 

90 "Page", backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True 

91 ) 

92 events = relationship("Event", backref="clusters", secondary="cluster_event_associations", viewonly=True) 

93 discussions = relationship( 

94 "Discussion", backref="clusters", secondary="cluster_discussion_associations", viewonly=True 

95 ) 

96 

97 # includes also admins 

98 members = relationship( 

99 "User", 

100 lazy="dynamic", 

101 backref="cluster_memberships", 

102 secondary="cluster_subscriptions", 

103 primaryjoin="Cluster.id == ClusterSubscription.cluster_id", 

104 secondaryjoin="User.id == ClusterSubscription.user_id", 

105 viewonly=True, 

106 ) 

107 

108 admins = relationship( 

109 "User", 

110 lazy="dynamic", 

111 backref="cluster_adminships", 

112 secondary="cluster_subscriptions", 

113 primaryjoin="Cluster.id == ClusterSubscription.cluster_id", 

114 secondaryjoin="and_(User.id == ClusterSubscription.user_id, ClusterSubscription.role == 'admin')", 

115 viewonly=True, 

116 ) 

117 

118 main_page = relationship( 

119 "Page", 

120 primaryjoin="and_(Cluster.id == Page.owner_cluster_id, Page.type == 'main_page')", 

121 viewonly=True, 

122 uselist=False, 

123 ) 

124 

125 @property 

126 def is_leaf(self) -> bool: 

127 """Whether the cluster is a leaf node in the cluster hierarchy.""" 

128 return len(self.parent_node.child_nodes) == 0 

129 

130 __table_args__ = ( 

131 # Each node can have at most one official cluster 

132 Index( 

133 "ix_clusters_owner_parent_node_id_is_official_cluster", 

134 parent_node_id, 

135 is_official_cluster, 

136 unique=True, 

137 postgresql_where=is_official_cluster, 

138 ), 

139 # trigram index on unaccented name 

140 # note that the function `unaccent` is not immutable so cannot be used in an index, that's why we wrap it 

141 Index( 

142 "idx_clusters_name_unaccented_trgm", 

143 text("immutable_unaccent(name) gin_trgm_ops"), 

144 postgresql_using="gin", 

145 ), 

146 ) 

147 

148 

149class NodeClusterAssociation(Base): 

150 """ 

151 NodeClusterAssociation, grouping of nodes 

152 """ 

153 

154 __tablename__ = "node_cluster_associations" 

155 __table_args__ = (UniqueConstraint("node_id", "cluster_id"),) 

156 

157 id = Column(BigInteger, primary_key=True) 

158 

159 node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True) 

160 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True) 

161 

162 node = relationship("Node", backref="node_cluster_associations") 

163 cluster = relationship("Cluster", backref="node_cluster_associations") 

164 

165 

166class ClusterRole(enum.Enum): 

167 member = enum.auto() 

168 admin = enum.auto() 

169 

170 

171class ClusterSubscription(Base): 

172 """ 

173 ClusterSubscription of a user 

174 """ 

175 

176 __tablename__ = "cluster_subscriptions" 

177 

178 id = Column(BigInteger, primary_key=True) 

179 

180 user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

181 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True) 

182 role = Column(Enum(ClusterRole), nullable=False) 

183 

184 user = relationship("User", backref="cluster_subscriptions") 

185 cluster = relationship("Cluster", backref="cluster_subscriptions") 

186 

187 __table_args__ = ( 

188 UniqueConstraint("user_id", "cluster_id"), 

189 Index( 

190 "ix_cluster_subscriptions_members", 

191 cluster_id, 

192 user_id, 

193 ), 

194 # For fast lookup of nodes this user is an admin of 

195 Index( 

196 "ix_cluster_subscriptions_admins", 

197 user_id, 

198 cluster_id, 

199 postgresql_where=(role == ClusterRole.admin), 

200 ), 

201 ) 

202 

203 

204class ClusterPageAssociation(Base): 

205 """ 

206 pages related to clusters 

207 """ 

208 

209 __tablename__ = "cluster_page_associations" 

210 __table_args__ = (UniqueConstraint("page_id", "cluster_id"),) 

211 

212 id = Column(BigInteger, primary_key=True) 

213 

214 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True) 

215 cluster_id = Column(ForeignKey("clusters.id"), nullable=False, index=True) 

216 

217 page = relationship("Page", backref="cluster_page_associations") 

218 cluster = relationship("Cluster", backref="cluster_page_associations") 

219 

220 

221class PageType(enum.Enum): 

222 main_page = enum.auto() 

223 place = enum.auto() 

224 guide = enum.auto() 

225 

226 

227class Page(Base): 

228 """ 

229 similar to a wiki page about a community, POI or guide 

230 """ 

231 

232 __tablename__ = "pages" 

233 

234 id = Column(BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value()) 

235 

236 parent_node_id = Column(ForeignKey("nodes.id"), nullable=False, index=True) 

237 type = Column(Enum(PageType), nullable=False) 

238 creator_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

239 owner_user_id = Column(ForeignKey("users.id"), nullable=True, index=True) 

240 owner_cluster_id = Column(ForeignKey("clusters.id"), nullable=True, index=True) 

241 

242 thread_id = Column(ForeignKey("threads.id"), nullable=False, unique=True) 

243 

244 parent_node = relationship("Node", backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id") 

245 

246 thread = relationship("Thread", backref="page", uselist=False) 

247 creator_user = relationship("User", backref="created_pages", foreign_keys="Page.creator_user_id") 

248 owner_user = relationship("User", backref="owned_pages", foreign_keys="Page.owner_user_id") 

249 owner_cluster = relationship( 

250 "Cluster", backref=backref("owned_pages", lazy="dynamic"), uselist=False, foreign_keys="Page.owner_cluster_id" 

251 ) 

252 

253 editors = relationship("User", secondary="page_versions", viewonly=True) 

254 

255 __table_args__ = ( 

256 # Only one of owner_user and owner_cluster should be set 

257 CheckConstraint( 

258 "(owner_user_id IS NULL) <> (owner_cluster_id IS NULL)", 

259 name="one_owner", 

260 ), 

261 # Only clusters can own main pages 

262 CheckConstraint( 

263 "NOT (owner_cluster_id IS NULL AND type = 'main_page')", 

264 name="main_page_owned_by_cluster", 

265 ), 

266 # Each cluster can have at most one main page 

267 Index( 

268 "ix_pages_owner_cluster_id_type", 

269 owner_cluster_id, 

270 type, 

271 unique=True, 

272 postgresql_where=(type == PageType.main_page), 

273 ), 

274 ) 

275 

276 def __repr__(self): 

277 return f"Page({self.id=})" 

278 

279 

280class PageVersion(Base): 

281 """ 

282 version of page content 

283 """ 

284 

285 __tablename__ = "page_versions" 

286 

287 id = Column(BigInteger, primary_key=True) 

288 

289 page_id = Column(ForeignKey("pages.id"), nullable=False, index=True) 

290 editor_user_id = Column(ForeignKey("users.id"), nullable=False, index=True) 

291 title = Column(String, nullable=False) 

292 content = Column(String, nullable=False) # CommonMark without images 

293 photo_key = Column(ForeignKey("uploads.key"), nullable=True) 

294 geom = Column(Geometry(geometry_type="POINT", srid=4326), nullable=True) 

295 # the human-readable address 

296 address = Column(String, nullable=True) 

297 created = Column(DateTime(timezone=True), nullable=False, server_default=func.now()) 

298 

299 slug = column_property(func.slugify(title)) 

300 

301 page = relationship("Page", backref="versions", order_by="PageVersion.id") 

302 editor_user = relationship("User", backref="edited_pages") 

303 photo = relationship("Upload") 

304 

305 __table_args__ = ( 

306 # Geom and address must either both be null or both be set 

307 CheckConstraint( 

308 "(geom IS NULL) = (address IS NULL)", 

309 name="geom_iff_address", 

310 ), 

311 ) 

312 

313 @property 

314 def coordinates(self): 

315 # returns (lat, lng) or None 

316 return get_coordinates(self.geom) 

317 

318 def __repr__(self): 

319 return f"PageVersion({self.id=}, {self.page_id=})"