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

1import enum 

2from datetime import datetime 

3from typing import TYPE_CHECKING 

4 

5from geoalchemy2 import Geometry 

6from sqlalchemy import ( 

7 BigInteger, 

8 Boolean, 

9 CheckConstraint, 

10 DateTime, 

11 Enum, 

12 ForeignKey, 

13 Index, 

14 String, 

15 UniqueConstraint, 

16 func, 

17 select, 

18 text, 

19) 

20from sqlalchemy.orm import ( 

21 DynamicMapped, 

22 Mapped, 

23 column_property, 

24 deferred, 

25 mapped_column, 

26 relationship, 

27) 

28from sqlalchemy.sql import expression 

29 

30from couchers.models.base import Base, Geom, communities_seq 

31from couchers.models.static import TimezoneArea 

32from couchers.utils import get_coordinates 

33 

34if TYPE_CHECKING: 

35 from couchers.models import Discussion, Event, Thread, Upload, User 

36 from couchers.models.public_trips import PublicTrip 

37 

38 

39class NodeType(enum.Enum): 

40 # Ordinal: lower values are broader geographic areas 

41 world = 1 

42 macroregion = 2 

43 region = 3 

44 subregion = 4 

45 locality = 5 

46 sublocality = 6 

47 

48 

49class Node(Base, kw_only=True): 

50 """ 

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

52 

53 Administered by the official cluster 

54 """ 

55 

56 __tablename__ = "nodes" 

57 

58 id: Mapped[int] = mapped_column( 

59 BigInteger, 

60 communities_seq, 

61 primary_key=True, 

62 server_default=communities_seq.next_value(), 

63 init=False, 

64 ) 

65 

66 node_type: Mapped[NodeType] = mapped_column(Enum(NodeType)) 

67 

68 # name and description come from the official cluster 

69 parent_node_id: Mapped[int | None] = mapped_column(ForeignKey("nodes.id"), default=None, index=True) 

70 geom: Mapped[Geom] = deferred(mapped_column(Geometry(geometry_type="MULTIPOLYGON", srid=4326), nullable=False)) 

71 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

72 

73 timezone = column_property( 

74 select(TimezoneArea.tzid) 

75 .where(func.ST_Contains(TimezoneArea.geom, func.ST_PointOnSurface(geom))) 

76 .limit(1) 

77 .scalar_subquery(), 

78 deferred=True, 

79 ) 

80 

81 parent_node: Mapped[Node] = relationship(init=False, back_populates="child_nodes", remote_side="Node.id") 

82 child_nodes: Mapped[list[Node]] = relationship(init=False) 

83 child_clusters: Mapped[list[Cluster]] = relationship( 

84 init=False, back_populates="parent_node", overlaps="official_cluster" 

85 ) 

86 official_cluster: Mapped[Cluster] = relationship( 

87 init=False, 

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

89 foreign_keys="[Cluster.parent_node_id]", 

90 uselist=False, 

91 viewonly=True, 

92 ) 

93 public_trips: Mapped[list[PublicTrip]] = relationship(init=False, back_populates="node") 

94 

95 

96class Cluster(Base, kw_only=True): 

97 """ 

98 Cluster, administered grouping of content 

99 """ 

100 

101 __tablename__ = "clusters" 

102 

103 id: Mapped[int] = mapped_column( 

104 BigInteger, 

105 communities_seq, 

106 primary_key=True, 

107 server_default=communities_seq.next_value(), 

108 init=False, 

109 ) 

110 parent_node_id: Mapped[int] = mapped_column(ForeignKey("nodes.id"), index=True) 

111 name: Mapped[str] = mapped_column(String) 

112 # short description 

113 description: Mapped[str] = mapped_column(String) 

114 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

115 

116 is_official_cluster: Mapped[bool] = mapped_column(Boolean, default=False) 

117 

118 # Toggles the "small community" feature set: discussions, events, and public trips. These are 

119 # grouped into a single flag because they're disabled together on large communities. 

120 small_community_features_enabled: Mapped[bool] = mapped_column( 

121 Boolean, default=True, server_default=expression.true() 

122 ) 

123 

124 slug: Mapped[str] = column_property(func.slugify(name)) 

125 

126 official_cluster_for_node: Mapped[Node] = relationship( 

127 init=False, 

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

129 back_populates="official_cluster", 

130 uselist=False, 

131 viewonly=True, 

132 ) 

133 

134 parent_node: Mapped[Node] = relationship( 

135 init=False, 

136 back_populates="child_clusters", 

137 remote_side="Node.id", 

138 foreign_keys="Cluster.parent_node_id", 

139 overlaps="official_cluster", 

140 ) 

141 

142 nodes: Mapped[list[Cluster]] = relationship( 

143 init=False, backref="clusters", secondary="node_cluster_associations", viewonly=True 

144 ) 

145 # all pages 

146 pages: DynamicMapped[Page] = relationship( 

147 init=False, backref="clusters", secondary="cluster_page_associations", lazy="dynamic", viewonly=True 

148 ) 

149 events: Mapped[Event] = relationship( 

150 init=False, backref="clusters", secondary="cluster_event_associations", viewonly=True 

151 ) 

152 discussions: Mapped[Discussion] = relationship( 

153 init=False, backref="clusters", secondary="cluster_discussion_associations", viewonly=True 

154 ) 

155 

156 # includes also admins 

157 members: DynamicMapped[User] = relationship( 

158 init=False, 

159 lazy="dynamic", 

160 backref="cluster_memberships", 

161 secondary="cluster_subscriptions", 

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

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

164 viewonly=True, 

165 ) 

166 

167 admins: DynamicMapped[User] = relationship( 

168 init=False, 

169 lazy="dynamic", 

170 backref="cluster_adminships", 

171 secondary="cluster_subscriptions", 

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

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

174 viewonly=True, 

175 ) 

176 

177 cluster_subscriptions: Mapped[list[ClusterSubscription]] = relationship(init=False) 

178 owned_pages: DynamicMapped[Page] = relationship(init=False, lazy="dynamic") 

179 owned_discussions: DynamicMapped[Discussion] = relationship(init=False, lazy="dynamic") 

180 

181 main_page: Mapped[Page] = relationship( 

182 init=False, 

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

184 viewonly=True, 

185 uselist=False, 

186 ) 

187 

188 @property 

189 def is_leaf(self) -> bool: 

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

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

192 

193 __table_args__ = ( 

194 # Each node can have at most one official cluster 

195 Index( 

196 "ix_clusters_owner_parent_node_id_is_official_cluster", 

197 parent_node_id, 

198 is_official_cluster, 

199 unique=True, 

200 postgresql_where=is_official_cluster, 

201 ), 

202 # trigram index on unaccented name 

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

204 Index( 

205 "idx_clusters_name_unaccented_trgm", 

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

207 postgresql_using="gin", 

208 ), 

209 ) 

210 

211 

212class NodeClusterAssociation(Base, kw_only=True): 

213 """ 

214 NodeClusterAssociation, grouping of nodes 

215 """ 

216 

217 __tablename__ = "node_cluster_associations" 

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

219 

220 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

221 

222 node_id: Mapped[int] = mapped_column(ForeignKey("nodes.id"), index=True) 

223 cluster_id: Mapped[int] = mapped_column(ForeignKey("clusters.id"), index=True) 

224 

225 node: Mapped[Node] = relationship(init=False, backref="node_cluster_associations") 

226 cluster: Mapped[Cluster] = relationship(init=False, backref="node_cluster_associations") 

227 

228 

229class ClusterRole(enum.Enum): 

230 member = enum.auto() 

231 admin = enum.auto() 

232 

233 

234class ClusterSubscription(Base, kw_only=True): 

235 """ 

236 ClusterSubscription of a user 

237 """ 

238 

239 __tablename__ = "cluster_subscriptions" 

240 

241 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

242 

243 user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

244 cluster_id: Mapped[int] = mapped_column(ForeignKey("clusters.id"), index=True) 

245 role: Mapped[ClusterRole] = mapped_column(Enum(ClusterRole)) 

246 

247 user: Mapped[User] = relationship(init=False, backref="cluster_subscriptions") 

248 cluster: Mapped[Cluster] = relationship(init=False, back_populates="cluster_subscriptions") 

249 

250 __table_args__ = ( 

251 UniqueConstraint("user_id", "cluster_id"), 

252 Index( 

253 "ix_cluster_subscriptions_members", 

254 cluster_id, 

255 user_id, 

256 ), 

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

258 Index( 

259 "ix_cluster_subscriptions_admins", 

260 user_id, 

261 cluster_id, 

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

263 ), 

264 ) 

265 

266 

267class ClusterPageAssociation(Base, kw_only=True): 

268 """ 

269 pages related to clusters 

270 """ 

271 

272 __tablename__ = "cluster_page_associations" 

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

274 

275 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

276 

277 page_id: Mapped[int] = mapped_column(ForeignKey("pages.id"), index=True) 

278 cluster_id: Mapped[int] = mapped_column(ForeignKey("clusters.id"), index=True) 

279 

280 page: Mapped[Page] = relationship(init=False, backref="cluster_page_associations") 

281 cluster: Mapped[Cluster] = relationship(init=False, backref="cluster_page_associations") 

282 

283 

284class PageType(enum.Enum): 

285 main_page = enum.auto() 

286 place = enum.auto() 

287 guide = enum.auto() 

288 

289 

290class Page(Base, kw_only=True): 

291 """ 

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

293 """ 

294 

295 __tablename__ = "pages" 

296 

297 id: Mapped[int] = mapped_column( 

298 BigInteger, communities_seq, primary_key=True, server_default=communities_seq.next_value(), init=False 

299 ) 

300 

301 parent_node_id: Mapped[int] = mapped_column(ForeignKey("nodes.id"), index=True) 

302 type: Mapped[PageType] = mapped_column(Enum(PageType)) 

303 creator_user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

304 owner_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"), default=None, index=True) 

305 owner_cluster_id: Mapped[int | None] = mapped_column(ForeignKey("clusters.id"), default=None, index=True) 

306 

307 thread_id: Mapped[int] = mapped_column(ForeignKey("threads.id"), unique=True) 

308 

309 parent_node: Mapped[Node] = relationship( 

310 init=False, backref="child_pages", remote_side="Node.id", foreign_keys="Page.parent_node_id" 

311 ) 

312 

313 thread: Mapped[Thread] = relationship(init=False, backref="page", uselist=False) 

314 creator_user: Mapped[User] = relationship(init=False, backref="created_pages", foreign_keys="Page.creator_user_id") 

315 owner_user: Mapped[User | None] = relationship(init=False, backref="owned_pages", foreign_keys="Page.owner_user_id") 

316 owner_cluster: Mapped[Cluster | None] = relationship( 

317 init=False, back_populates="owned_pages", uselist=False, foreign_keys="Page.owner_cluster_id" 

318 ) 

319 

320 editors: Mapped[list[User]] = relationship(init=False, secondary="page_versions", viewonly=True) 

321 versions: Mapped[list[PageVersion]] = relationship(init=False, back_populates="page", order_by="PageVersion.id") 

322 

323 __table_args__ = ( 

324 # Only one of owner_user and owner_cluster should be set 

325 CheckConstraint( 

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

327 name="one_owner", 

328 ), 

329 # Only clusters can own main pages 

330 CheckConstraint( 

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

332 name="main_page_owned_by_cluster", 

333 ), 

334 # Each cluster can have at most one main page 

335 Index( 

336 "ix_pages_owner_cluster_id_type", 

337 owner_cluster_id, 

338 type, 

339 unique=True, 

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

341 ), 

342 ) 

343 

344 def __repr__(self) -> str: 

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

346 

347 

348class PageVersion(Base, kw_only=True): 

349 """ 

350 version of page content 

351 """ 

352 

353 __tablename__ = "page_versions" 

354 

355 id: Mapped[int] = mapped_column(BigInteger, primary_key=True, init=False) 

356 

357 page_id: Mapped[int] = mapped_column(ForeignKey("pages.id"), index=True) 

358 editor_user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True) 

359 title: Mapped[str] = mapped_column(String) 

360 content: Mapped[str] = mapped_column(String) # CommonMark without images 

361 photo_key: Mapped[str | None] = mapped_column(ForeignKey("uploads.key"), default=None) 

362 geom: Mapped[Geom | None] = mapped_column(Geometry(geometry_type="POINT", srid=4326), default=None) 

363 # the human-readable address 

364 address: Mapped[str | None] = mapped_column(String, default=None) 

365 created: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), init=False) 

366 

367 slug: Mapped[str] = column_property(func.slugify(title)) 

368 

369 page: Mapped[Page] = relationship(init=False, back_populates="versions") 

370 editor_user: Mapped[User] = relationship(init=False, backref="edited_pages") 

371 photo: Mapped[Upload] = relationship(init=False) 

372 

373 __table_args__ = ( 

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

375 CheckConstraint( 

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

377 name="geom_iff_address", 

378 ), 

379 ) 

380 

381 @property 

382 def coordinates(self) -> tuple[float, float] | None: 

383 # returns (lat, lng) or None 

384 return get_coordinates(self.geom) 

385 

386 def __repr__(self) -> str: 

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