Coverage for backend/app/models.py: 99%

1"""

2This module defines the database table models for the application using SQLAlchemy ORM. Each class represents a table in

3the database, with its fields defining the table's columns and relationships. The module utilizes a `CommonBase` class

4to provide a shared structure for all models, including common attributes like `id`, `created_at`, and `created_by`."""

6import re

8from sqlalchemy import (

9 Column,

10 Integer,

11 String,

12 ForeignKey,

13 Float,

14 Boolean,

15 TIMESTAMP,

16 text,

17 CheckConstraint,

18 Table,

19 func,

20)

21from sqlalchemy.ext.declarative import declared_attr

22from sqlalchemy.ext.hybrid import hybrid_property

23from sqlalchemy.orm import relationship

24from sqlalchemy.sql import expression

26from app.database import Base

27from app.config import settings

29# ------------------------------------------------------ MAPPINGS ------------------------------------------------------

32job_keyword_mapping = Table(

33 "job_keyword_mapping",

34 Base.metadata,

35 Column("job_id", Integer, ForeignKey("job.id", ondelete="CASCADE"), primary_key=True),

36 Column("keyword_id", Integer, ForeignKey("keyword.id", ondelete="CASCADE"), primary_key=True),

37)

39interview_interviewer_mapping = Table(

40 "interview_interviewer_mapping",

41 Base.metadata,

42 Column("interview_id", Integer, ForeignKey("interview.id", ondelete="CASCADE"), primary_key=True),

43 Column("person_id", Integer, ForeignKey("person.id", ondelete="CASCADE"), primary_key=True),

44)

46job_contact_mapping = Table(

47 "job_contact_mapping",

48 Base.metadata,

49 Column("job_id", Integer, ForeignKey("job.id", ondelete="CASCADE"), primary_key=True),

50 Column("person_id", Integer, ForeignKey("person.id", ondelete="CASCADE"), primary_key=True),

51)

54# -------------------------------------------------------- BASES -------------------------------------------------------

57class CommonBase(object):

58 """A base class that contains common attributes shared by all tables.

60 Attributes:

61 -----------

62 - `id` (int): Primary key of the record. Automatically populated upon creation.

63 - `created_at` (datetime): The timestamp of when the record was created. Automatically populated upon creation.

64 - `modified_at` (datetime): The timestamp of when the record was modified. Automatically updated upon updates."""

66 # noinspection PyMethodParameters

67 @declared_attr

68 def __tablename__(cls) -> str:

69 """Return the class name as table name e.g. JobApplication -> job_application"""

71 name = re.sub("(.)([A-Z][a-z]+)", r"\1_\2", cls.__name__)

72 return re.sub("([a-z0-9])([A-Z])", r"\1_\2", name).lower()

74 id = Column(Integer, primary_key=True, nullable=False)

75 created_at = Column(TIMESTAMP(timezone=True), server_default=text("now()"), nullable=False)

76 modified_at = Column(TIMESTAMP(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)

79class Owned(CommonBase):

80 """A base class that contains common attributes shared by tables which entries have an owner.

82 Attributes:

83 -----------

84 - `owner_id` (int): Foreign key linking the record to the user table."""

86 owner_id = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"), nullable=False)

89# --------------------------------------------------------- APP --------------------------------------------------------

92class Setting(CommonBase, Base):

93 """Represents the application settings

95 Attributes:

96 -----------

97 - `name` (str): The name of the setting.

98 - `value` (float): The value of the setting.

99 - `description` (str): A description of the setting."""

100

101 name = Column(String, nullable=False, unique=True)

102 value = Column(String, nullable=False)

103 description = Column(String, nullable=True)

104

105

106class User(CommonBase, Base):

107 """Represents users of the application.

108

109 Attributes:

110 -----------

111 - `password` (str): Encrypted password for authentication.

112 - `email` (str): User's email address (must be unique).

113 - `theme` (str): The theme of the application.

114 - `is_admin` (bool): Indicates whether the user is an administrator.

115 - `last_login` (datetime): The timestamp of the last login.

116 - `chase_threshold` (int): The threshold for chasing jobs in the dashboard.

117 - `deadline_threshold` (int): The threshold for deadlines in the dashboard.

118 - `update_limit` (int): Max number updates displayed in the dashboard."""

119

120 password = Column(String, nullable=False)

121 email = Column(String, nullable=False, unique=True)

122 theme = Column(String, nullable=False, server_default="mixed-berry")

123 is_admin = Column(Boolean, nullable=False, server_default=expression.false())

124 last_login = Column(TIMESTAMP(timezone=True), nullable=True)

125 chase_threshold = Column(Integer, nullable=False, server_default="30")

126 deadline_threshold = Column(Integer, nullable=False, server_default="30")

127 update_limit = Column(Integer, nullable=False, server_default="10")

128

129 __table_args__ = (

130 CheckConstraint(f"length(password) >= {settings.min_password_length}", name="minimum_password_length"),

131 )

132

133

134# -------------------------------------------------------- DATA --------------------------------------------------------

135

136

137class Keyword(Owned, Base):

138 """Represents keywords associated with job postings.

139

140 Attributes:

141 -----------

142 - `name` (str): The keyword name.

143

144 Relationships:

145 --------------

146 - `jobs` (list of Job): List of jobs associated with the keyword."""

147

148 name = Column(String, nullable=False)

149

150 # Relationships

151 jobs = relationship("Job", secondary=job_keyword_mapping, back_populates="keywords")

152

153

154class Aggregator(Owned, Base):

155 """Represents an aggregator website (e.g. LinkedIn, Indeed).

156

157 Attributes:

158 -----------

159 - `name` (str): The website's name.

160 - `url` (str): The website's URL.

161

162 Relationships:

163 --------------

164 - `jobs` (list of Job): List of jobs associated with the aggregator.

165 - `job_applications` (list of Job): List of jobs associated with the aggregator."""

166

167 name = Column(String, nullable=False)

168 url = Column(String, nullable=False)

169

170 jobs = relationship("Job", foreign_keys="Job.source_id", back_populates="source")

171 job_applications = relationship(

172 "Job", foreign_keys="Job.application_aggregator_id", back_populates="application_aggregator"

173 )

174

175

176class Company(Owned, Base):

177 """Represents a company or organisation.

178

179 Attributes:

180 -----------

181 - `name` (str): Name of the company.

182 - `description` (str, optional): Description or details about the company.

183 - `url` (str, optional): Web link to the company's website.

184

185 Relationships:

186 --------------

187 - `jobs` (list of Job): List of jobs associated with the company.

188 - `persons` (list of Person): List of people linked to the company."""

189

190 name = Column(String, nullable=False)

191 description = Column(String, nullable=True)

192 url = Column(String, nullable=True)

193

194 # Relationships

195 jobs = relationship("Job", back_populates="company")

196 persons = relationship("Person", back_populates="company")

197

198

199class Location(Owned, Base):

200 """Represents geographical locations.

201

202 Attributes:

203 -----------

204 - `postcode` (str, optional): Postcode of the location.

205 - `city` (str, optional): City of the location.

206 - `country` (str, optional): Country where the location resides.

207 - `name` (str): Computed property combining city, country, and postcode

208

209 Relationships:

210 --------------

211 - `jobs` (list of Job): List of jobs associated with the location.

212 - `interviews` (list of Interview): List of interviews associated with the location."""

213

214 postcode = Column(String, nullable=True)

215 city = Column(String, nullable=True)

216 country = Column(String, nullable=True)

217

218 # Relationships

219 jobs = relationship("Job", back_populates="location")

220 interviews = relationship("Interview", back_populates="location")

221

222 @hybrid_property

223 def name(self) -> str:

224 """Computed property that combines city, country, and postcode into a readable location name"""

225

226 parts = []

227 if self.city:

228 parts.append(self.city)

229 if self.country:

230 parts.append(self.country)

231 if self.postcode:

232 parts.append(self.postcode)

233

234 return ", ".join(parts)

235

236 __table_args__ = (

237 CheckConstraint(

238 "postcode IS NOT NULL OR city IS NOT NULL OR country IS NOT NULL",

239 name=f"location_data_required",

240 ),

241 )

242

243

244class File(Owned, Base):

245 """Represents files uploaded by the users.

246

247 Attributes:

248 -----------

249 - `filename` (str): Name of the file.

250 - `content` (bytes): Content of the file.

251 - `type` (str): MIME type of the file.

252 - `size` (int): Size of the file in bytes."""

253

254 filename = Column(String, nullable=False)

255 content = Column(String, nullable=False)

256 type = Column(String, nullable=False)

257 size = Column(Integer, nullable=False)

258

259

260class Person(Owned, Base):

261 """Represents a person

262

263 Attributes:

264 -----------

265 - `first_name` (str): First name of the person.

266 - `last_name` (str): Last name of the person.

267 - `email` (str, optional): Email address of the person.

268 - `phone` (str, optional): Phone number of the person.

269 - `role` (str, optional): Role or position held by the person within the company.

270 - `linkedin_url` (str, optional): LinkedIn profile URL of the person.

271 - `name` (str): Computed property combining first and last name.

272 - `name_company` (str): Computed property combining first name, last name, and company name.

273

274 Foreign keys:

275 -------------

276 - `company_id` (int): Foreign key linking the person to a company.

277

278 Relationships:

279 --------------

280 - `company` (Company): Relationship to access the associated company.

281 - `interviews` (list of Interview): List of interviews performed by the person within the company.

282 - `jobs` (list of Job): List of jobs linked to the person within the company."""

283

284 first_name = Column(String, nullable=False)

285 last_name = Column(String, nullable=False)

286 email = Column(String, nullable=True)

287 phone = Column(String, nullable=True)

288 role = Column(String, nullable=True)

289 linkedin_url = Column(String, nullable=True)

290

291 # Foreign keys

292 company_id = Column(Integer, ForeignKey("company.id", ondelete="SET NULL"), nullable=True, index=True)

293

294 # Relationships

295 company = relationship("Company", back_populates="persons")

296 interviews = relationship("Interview", secondary=interview_interviewer_mapping, back_populates="interviewers")

297 jobs = relationship("Job", secondary=job_contact_mapping, back_populates="contacts")

298

299 @hybrid_property

300 def name(self) -> str:

301 """Computed property that combines the first and last name"""

302

303 return f"{self.first_name} {self.last_name}"

304

305 @hybrid_property

306 def name_company(self) -> str:

307 """Computed property that combines the first name, last name, and the company name"""

308

309 if self.company:

310 return f"{self.first_name} {self.last_name} - {self.company.name}"

311 else:

312 return self.name

313

314

315class Job(Owned, Base):

316 """Represents job postings within the application.

317

318 Attributes:

319 -----------

320 - `title` (str): The job title.

321 - `description` (str, optional): Description or details about the job.

322 - `salary_min` (float, optional): Minimum salary offered for the job (in GBP).

323 - `salary_max` (float, optional): Maximum salary offered for the job (in GBP).

324 - `url` (str, optional): Web link to the job posting.

325 - `personal_rating` (int, optional): Personalised rating given to the job.

326 - `note` (str, optional): Additional note about the job posting.

327 - `deadline` (datetime, optional): Deadline for the job application.

328 - `attendance_type` (str, optional): Type of attendance offered for the job (on-site, remote, hybrid).

329 - `name` (str): Computed property combining the job title and company name.

330

331 Foreign keys:

332 -------------

333 - `company_id` (int): Identifier for the company offering the job.

334 - `location_id` (int, optional): Identifier for the geographical location where the job is located.

335 - `duplicate_id` (int, optional): Identifier for a duplicate job posting.

336 - `source_id` (int, optional): Identifier for the aggregator website where the job was posted.

337

338 Relationships:

339 --------------

340 - `company` (Company): Company object associated with the job posting.

341 - `location` (Location): Location object associated with the job posting.

342 - `keywords` (list of Keyword): List of keywords associated with the job posting.

343 - `contacts` (list of Person): List of people linked to the company that may be interested in the job posting.

344 - `source` (Aggregator): Source of the job posting (e.g. LinkedIn, Indeed, etc.)."""

345

346 title = Column(String, nullable=False)

347 description = Column(String, nullable=True)

348 salary_min = Column(Float, nullable=True)

349 salary_max = Column(Float, nullable=True)

350 url = Column(String, nullable=True)

351 personal_rating = Column(Integer, nullable=True)

352 note = Column(String, nullable=True)

353 deadline = Column(TIMESTAMP(timezone=True), nullable=True)

354 attendance_type = Column(String, nullable=True)

355 application_date = Column(TIMESTAMP(timezone=True), nullable=True)

356 application_url = Column(String, nullable=True)

357 application_status = Column(String, nullable=True)

358 applied_via = Column(String, nullable=True)

359 application_note = Column(String, nullable=True)

360

361 # Foreign keys

362 company_id = Column(Integer, ForeignKey("company.id", ondelete="SET NULL"), nullable=True, index=True)

363 location_id = Column(Integer, ForeignKey("location.id", ondelete="SET NULL"), nullable=True, index=True)

364 duplicate_id = Column(Integer, ForeignKey("job.id", ondelete="SET NULL"), nullable=True, index=True)

365 source_id = Column(Integer, ForeignKey("aggregator.id", ondelete="SET NULL"), nullable=True, index=True)

366 application_aggregator_id = Column(

367 Integer, ForeignKey("aggregator.id", ondelete="SET NULL"), nullable=True, index=True

368 )

369 cv_id = Column(Integer, ForeignKey("file.id", ondelete="SET NULL"), nullable=True, index=True)

370 cover_letter_id = Column(Integer, ForeignKey("file.id", ondelete="SET NULL"), nullable=True, index=True)

371

372 # Relationships

373 company = relationship("Company", back_populates="jobs")

374 location = relationship("Location", back_populates="jobs")

375 keywords = relationship("Keyword", secondary=job_keyword_mapping, back_populates="jobs", lazy="selectin")

376 contacts = relationship("Person", secondary=job_contact_mapping, back_populates="jobs", lazy="selectin")

377 source = relationship("Aggregator", foreign_keys=[source_id], back_populates="jobs")

378 interviews = relationship("Interview", back_populates="job")

379 updates = relationship("JobApplicationUpdate", back_populates="job")

380 application_aggregator = relationship(

381 "Aggregator", foreign_keys=[application_aggregator_id], back_populates="job_applications"

382 )

383 application_cv = relationship("File", foreign_keys=[cv_id], lazy="select")

384 application_cover_letter = relationship("File", foreign_keys=[cover_letter_id], lazy="select")

385

386 @hybrid_property

387 def name(self) -> str | Column[str]:

388 """Computed property that combines the job title and company name"""

389

390 if hasattr(self, "company") and self.title and self.company and self.company.name:

391 return f"{self.title} - {self.company.name}"

392 elif self.title:

393 return self.title

394 else:

395 return "Unknown Job"

396

397 __table_args__ = (

398 CheckConstraint("personal_rating >= 1 AND personal_rating <= 5", name=f"valid_rating_range"),

399 CheckConstraint("salary_min <= salary_max", name=f"valid_salary_range"),

400 CheckConstraint("attendance_type IN ('on-site', 'remote', 'hybrid')", name="valid_attendance_type_values"),

401 )

402

403

404class Interview(Owned, Base):

405 """Represents interviews for job applications.

406

407 Attributes:

408 -----------

409 - `date` (datetime): The date and time of the interview.

410 - `type` (str): Type of the interview (HR, technical, management, ...)

411 - `note` (str, optional): Additional notes or comments about the interview.

412

413 Foreign keys:

414 -------------

415 - `location_id` (int): Identifier for the location of the interview.

416 - `job_id` (int): Identifier for the job application associated with the interview.

417

418 Relationships:

419 --------------

420 - `job` (Job): Job object related to the interview.

421 - `interviewers` (list of Person): List of people who participated in the interview.

422 - `location` (Location): Location object related to the interview."""

423

424 date = Column(TIMESTAMP(timezone=True), server_default=text("now()"), nullable=False)

425 type = Column(String, nullable=False)

426 note = Column(String, nullable=True)

427 attendance_type = Column(String, nullable=True)

428

429 # Foreign keys

430 location_id = Column(Integer, ForeignKey("location.id", ondelete="SET NULL"), nullable=True, index=True)

431 job_id = Column(Integer, ForeignKey("job.id", ondelete="CASCADE"), nullable=False, index=True)

432

433 # Relationships

434 location = relationship("Location", back_populates="interviews")

435 job = relationship("Job", back_populates="interviews")

436 interviewers = relationship("Person", secondary=interview_interviewer_mapping, back_populates="interviews")

437

438 __table_args__ = (CheckConstraint("attendance_type IN ('on-site', 'remote')", name="valid_attendance_type_values"),)

439

440

441class JobApplicationUpdate(Owned, Base):

442 """Represents an update to a job application.

443

444 Attributes:

445 -----------

446 - `date` (datetime): The date and time of the update.

447 - `note` (str, optional): Additional notes or comments about the update.

448 - `type` (str): The type of the update (received, sent).

449

450 Foreign keys:

451 -------------

452 - job_id (int): Identifier for the job application associated with the update.

453

454 Relationships:

455 --------------

456 - `job` (Job): Job object related to the update."""

457

458 date = Column(TIMESTAMP(timezone=True), server_default=text("now()"), nullable=False)

459 note = Column(String, nullable=True)

460 type = Column(String, nullable=False)

461

462 # Foreign keys

463 job_id = Column(Integer, ForeignKey("job.id", ondelete="CASCADE"), nullable=False)

464

465 # Relationships

466 job = relationship("Job", back_populates="updates")