Index: iknow-api/Data/AppDbContext.cs
===================================================================
--- iknow-api/Data/AppDbContext.cs (revision aee2b88c7a15b0b0d323790d18c133b41446ff41)
+++ iknow-api/Data/AppDbContext.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097)
@@ -4,6 +4,17 @@
namespace iknow_api.Data
{
+ ///
+ /// The schema is owned by sql/ddl.sql, not by EF migrations. Every entity
+ /// below is mapped explicitly onto the table and column names in that
+ /// script; nothing here may rely on EF naming conventions.
+ ///
public class AppDbContext : DbContext
{
+ ///
+ /// The schema created by sql/schema_creation.sql. Every table and enum
+ /// type is qualified with it, so nothing depends on search_path.
+ ///
+ public const string ProjectSchema = "project";
+
public AppDbContext(DbContextOptions options)
: base(options) { }
@@ -12,5 +23,4 @@
public DbSet ContactInfo { get; set; }
public DbSet HighSchool { get; set; }
- public DbSet EnrollmentInfo { get; set; }
public DbSet RefreshToken { get; set; }
public DbSet Subjects { get; set; }
@@ -27,169 +37,342 @@
public DbSet ProfessorSubjects { get; set; }
-
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
- modelBuilder.Entity().HasData(
- new Major { Id = 1, Name = "PIT" },
- new Major { Id = 2, Name = "SIIS" },
- new Major { Id = 3, Name = "SEIS" },
- new Major { Id = 4, Name = "KN" }
- );
-
-
- // -------------------------
- // DependencySubject (self-referencing many-to-many)
- // -------------------------
- modelBuilder.Entity()
- .HasKey(ds => new { ds.SubjectId, ds.DependencyId });
-
- modelBuilder.Entity()
- .HasOne(ds => ds.Subject)
- .WithMany(s => s.Dependencies)
- .HasForeignKey(ds => ds.SubjectId)
- .OnDelete(DeleteBehavior.Restrict);
-
- modelBuilder.Entity()
- .HasOne(ds => ds.Dependency)
- .WithMany(s => s.Dependents)
- .HasForeignKey(ds => ds.DependencyId)
- .OnDelete(DeleteBehavior.Restrict);
-
- // -------------------------
- // MajorSubjects (many-to-many)
- // -------------------------
- modelBuilder.Entity()
- .HasKey(ab => new { ab.MajorId, ab.SubjectId });
-
- modelBuilder.Entity()
- .HasOne(ab => ab.Majors)
- .WithMany(a => a.Subjects)
- .HasForeignKey(ab => ab.MajorId)
- .OnDelete(DeleteBehavior.Cascade);
-
- modelBuilder.Entity()
- .HasOne(ab => ab.Subjects)
- .WithMany(b => b.Majors)
- .HasForeignKey(ab => ab.SubjectId)
- .OnDelete(DeleteBehavior.Cascade);
-
- // -------------------------
- // UserDocuments (many-to-many)
- // -------------------------
- modelBuilder.Entity()
- .HasKey(ab => new { ab.UserId, ab.DocumentId });
-
- modelBuilder.Entity()
- .HasOne(ab => ab.User)
- .WithMany(a => a.Documents)
- .HasForeignKey(ab => ab.UserId)
- .OnDelete(DeleteBehavior.Cascade);
-
- modelBuilder.Entity()
- .HasOne(ab => ab.Documents)
- .WithMany(b => b.User)
- .HasForeignKey(ab => ab.DocumentId)
- .OnDelete(DeleteBehavior.Cascade);
-
- // -------------------------
- // Payment (user enrollment)
- // -------------------------
- modelBuilder.Entity()
- .HasKey(p => p.Id);
-
- modelBuilder.Entity()
- .HasOne(p => p.User)
- .WithMany(u => u.EnrolledSemesters)
- .HasForeignKey(p => p.UserId)
- .OnDelete(DeleteBehavior.Cascade);
-
- modelBuilder.Entity()
- .HasOne(p => p.EnrolledSemesters)
- .WithMany(es => es.Users)
- .HasForeignKey(p => p.EnrollmentInfoId)
- .OnDelete(DeleteBehavior.Cascade);
-
- // -------------------------
- // SemesterSubject (student enrollments and professor assignments)
- // -------------------------
- modelBuilder.Entity()
- .HasKey(ss => ss.Id); // or composite key if you prefer {UserId, EnrolledSemesterId, SubjectId}
-
- // Student enrollment
- modelBuilder.Entity()
- .HasOne(ss => ss.User)
- .WithMany(u => u.EnrolledSemesterUserSubjects)
- .HasForeignKey(ss => ss.UserId)
- .OnDelete(DeleteBehavior.Restrict);
-
- // Professor teaching
- modelBuilder.Entity()
- .HasOne(ss => ss.Professor)
- .WithMany(u => u.TeachingSubjects)
- .HasForeignKey(ss => ss.ProfessorId)
- .OnDelete(DeleteBehavior.Restrict);
-
- // Subject relation
- modelBuilder.Entity()
- .HasOne(ss => ss.Subject)
- .WithMany(s => s.SemesterSubjects)
- .HasForeignKey(ss => ss.SubjectId)
- .OnDelete(DeleteBehavior.Restrict);
-
- // EnrolledSemester relation
- modelBuilder.Entity()
- .HasOne(ss => ss.EnrolledSemester)
- .WithMany(es => es.SemesterSubjects)
- .HasForeignKey(ss => ss.EnrolledSemesterId)
- .OnDelete(DeleteBehavior.Restrict);
-
- // -------------------------
- // PassedSubject (one-to-one with SemesterSubject)
- // -------------------------
- modelBuilder.Entity()
- .HasKey(ps => ps.Id);
-
- modelBuilder.Entity()
- .HasOne(ps => ps.SemesterSubject)
- .WithOne(ss => ss.PassedSubject)
- .HasForeignKey(ps => ps.SemesterSubjectId)
- .OnDelete(DeleteBehavior.Cascade);
-
- // -------------------------
- // ActiveSemesters to EnrolledSemesters (one-to-many)
- // -------------------------
- modelBuilder.Entity()
- .HasOne(es => es.Semester)
- .WithMany(a => a.EnrolledSemesters)
- .HasForeignKey(es => es.Id)
- .OnDelete(DeleteBehavior.Restrict);
-
- // -------------------------
- // ProfessorSubjects (many-to-many: Professor -> Semester -> Subject)
- // -------------------------
- modelBuilder.Entity()
- .HasKey(ps => new { ps.ProfessorId, ps.SemesterId, ps.SubjectId });
-
- modelBuilder.Entity()
- .HasOne(ps => ps.Professor)
- .WithMany()
- .HasForeignKey(ps => ps.ProfessorId)
- .OnDelete(DeleteBehavior.Cascade);
-
- modelBuilder.Entity()
- .HasOne(ps => ps.Semester)
- .WithMany()
- .HasForeignKey(ps => ps.SemesterId)
- .OnDelete(DeleteBehavior.Cascade);
-
- modelBuilder.Entity()
- .HasOne(ps => ps.Subject)
- .WithMany()
- .HasForeignKey(ps => ps.SubjectId)
- .OnDelete(DeleteBehavior.Cascade);
+ // The enum types themselves are declared in ddl.sql; this tells EF
+ // they exist so it does not try to treat the columns as text.
+ modelBuilder.HasPostgresEnum(ProjectSchema, "user_role");
+ modelBuilder.HasPostgresEnum(ProjectSchema, "hs_type");
+ modelBuilder.HasPostgresEnum(ProjectSchema, "quota_type");
+ modelBuilder.HasPostgresEnum(ProjectSchema, "semester_type");
+ modelBuilder.HasPostgresEnum(ProjectSchema, "grade_type");
+
+ // -------------------------
+ // users
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("users", ProjectSchema);
+ e.HasKey(u => u.Id);
+ e.Property(u => u.Id).HasColumnName("id");
+ e.Property(u => u.Name).HasColumnName("name");
+ e.Property(u => u.EMBG).HasColumnName("embg");
+ e.Property(u => u.Surname).HasColumnName("surname");
+ e.Property(u => u.Index).HasColumnName("index");
+ e.Property(u => u.Bday).HasColumnName("bday").HasColumnType("timestamp without time zone");
+ e.Property(u => u.Email).HasColumnName("email");
+ e.Property(u => u.PasswordHash).HasColumnName("password");
+ e.Property(u => u.Role).HasColumnName("role");
+ e.Property(u => u.Quota).HasColumnName("quota");
+ e.Property(u => u.EnrollmentYear).HasColumnName("enrollment_year");
+ e.Property(u => u.CreatedAt).HasColumnName("created_at").HasColumnType("timestamp without time zone");
+ e.HasIndex(u => u.Email).IsUnique();
+ });
+
+ // -------------------------
+ // high_school
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("high_school", ProjectSchema);
+ e.HasKey(h => h.Id);
+ e.Property(h => h.Id).HasColumnName("id");
+ e.Property(h => h.UserId).HasColumnName("user_id");
+ e.Property(h => h.GPA).HasColumnName("gpa");
+ e.Property(h => h.HighSchoolType).HasColumnName("type");
+ e.HasOne(h => h.User)
+ .WithOne(u => u.HighSchool)
+ .HasForeignKey(h => h.UserId)
+ .OnDelete(DeleteBehavior.Restrict);
+ });
+
+ // -------------------------
+ // contact
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("contact", ProjectSchema);
+ e.HasKey(c => c.Id);
+ e.Property(c => c.Id).HasColumnName("id");
+ e.Property(c => c.UserId).HasColumnName("user_id");
+ e.Property(c => c.City).HasColumnName("city");
+ e.Property(c => c.Municipality).HasColumnName("municipality");
+ e.Property(c => c.Address).HasColumnName("address");
+ e.Property(c => c.PhoneNumber).HasColumnName("number");
+ e.Property(c => c.MicrosoftEmail).HasColumnName("microsoft_email");
+ e.HasOne(c => c.User)
+ .WithOne(u => u.ContactInfo)
+ .HasForeignKey(c => c.UserId)
+ .OnDelete(DeleteBehavior.Restrict);
+ });
+
+ // -------------------------
+ // major
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("major", ProjectSchema);
+ e.HasKey(m => m.Id);
+ e.Property(m => m.Id).HasColumnName("id");
+ e.Property(m => m.Name).HasColumnName("name");
+ });
+
+ // -------------------------
+ // active_semesters
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("active_semesters", ProjectSchema);
+ e.HasKey(a => a.Id);
+ e.Property(a => a.Id).HasColumnName("id");
+ e.Property(a => a.Year).HasColumnName("year");
+ e.Property(a => a.Type).HasColumnName("type");
+ });
+
+ // -------------------------
+ // subjects
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("subjects", ProjectSchema);
+ e.HasKey(s => s.Id);
+ e.Property(s => s.Id).HasColumnName("id");
+ e.Property(s => s.Name).HasColumnName("name");
+ e.Property(s => s.Code).HasColumnName("code");
+ e.Property(s => s.AwardedCredits).HasColumnName("awarded_credits");
+ e.Property(s => s.DependencyCredit).HasColumnName("dependency_credit");
+ });
+
+ // -------------------------
+ // enrolled_semesters
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("enrolled_semesters", ProjectSchema);
+ e.HasKey(es => es.Id);
+ e.Property(es => es.Id).HasColumnName("id");
+ e.Property(es => es.UserId).HasColumnName("user_id");
+ e.Property(es => es.QuotaType).HasColumnName("quota");
+ e.Property(es => es.MajorId).HasColumnName("major_id");
+ e.Property(es => es.Note).HasColumnName("note");
+ e.Property(es => es.StudentComment).HasColumnName("student_comment");
+ e.Property(es => es.CratedAt).HasColumnName("created_at").HasColumnType("timestamp without time zone");
+ e.Property(es => es.LastChange).HasColumnName("last_change").HasColumnType("timestamp without time zone");
+ e.Property(es => es.Verified).HasColumnName("completed").HasColumnType("timestamp without time zone");
+ e.Property(es => es.SemesterId).HasColumnName("semester_id");
+
+ e.HasOne(es => es.User)
+ .WithMany(u => u.Enrolments)
+ .HasForeignKey(es => es.UserId)
+ .OnDelete(DeleteBehavior.Restrict);
+
+ // Note: this deliberately keys off SemesterId. An earlier version
+ // pointed the foreign key at es.Id, which silently tied every
+ // enrolment to the semester that happened to share its id.
+ e.HasOne(es => es.Semester)
+ .WithMany(a => a.EnrolledSemesters)
+ .HasForeignKey(es => es.SemesterId)
+ .OnDelete(DeleteBehavior.Restrict);
+
+ e.HasOne(es => es.Major)
+ .WithMany()
+ .HasForeignKey(es => es.MajorId)
+ .OnDelete(DeleteBehavior.Restrict);
+ });
+
+ // -------------------------
+ // semesters_subjects
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("semesters_subjects", ProjectSchema);
+ e.HasKey(ss => ss.Id);
+ e.Property(ss => ss.Id).HasColumnName("id");
+ e.Property(ss => ss.EnrolledSemesterId).HasColumnName("enrolled_semesters_id");
+ e.Property(ss => ss.SubjectId).HasColumnName("subjects_id");
+ e.Property(ss => ss.ProfessorId).HasColumnName("professor_id");
+ e.Property(ss => ss.Signature).HasColumnName("signature");
+
+ e.HasOne(ss => ss.Professor)
+ .WithMany(u => u.TeachingSubjects)
+ .HasForeignKey(ss => ss.ProfessorId)
+ .OnDelete(DeleteBehavior.Restrict);
+
+ e.HasOne(ss => ss.Subject)
+ .WithMany(s => s.SemesterSubjects)
+ .HasForeignKey(ss => ss.SubjectId)
+ .OnDelete(DeleteBehavior.Restrict);
+
+ e.HasOne(ss => ss.EnrolledSemester)
+ .WithMany(es => es.SemesterSubjects)
+ .HasForeignKey(ss => ss.EnrolledSemesterId)
+ .OnDelete(DeleteBehavior.Restrict);
+ });
+
+ // -------------------------
+ // passed_subjects
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("passed_subjects", ProjectSchema);
+ e.HasKey(ps => ps.Id);
+ e.Property(ps => ps.Id).HasColumnName("id");
+ e.Property(ps => ps.SemesterSubjectId).HasColumnName("enrolled_id");
+ e.Property(ps => ps.Grade).HasColumnName("grade");
+ e.Property(ps => ps.DatePassed).HasColumnName("date_passed").HasColumnType("timestamp without time zone");
+
+ e.HasOne(ps => ps.SemesterSubject)
+ .WithOne(ss => ss.PassedSubject)
+ .HasForeignKey(ps => ps.SemesterSubjectId)
+ .OnDelete(DeleteBehavior.Cascade);
+ });
+
+ // -------------------------
+ // documents
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("documents", ProjectSchema);
+ e.HasKey(d => d.Id);
+ e.Property(d => d.Id).HasColumnName("id");
+ e.Property(d => d.Name).HasColumnName("type");
+ e.Property(d => d.Body).HasColumnName("body");
+ e.Property(d => d.Cost).HasColumnName("cost");
+ });
+
+ // -------------------------
+ // user_documents
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("user_documents", ProjectSchema);
+ e.HasKey(ud => new { ud.UserId, ud.DocumentId });
+ e.Property(ud => ud.UserId).HasColumnName("user_id");
+ e.Property(ud => ud.DocumentId).HasColumnName("document_id");
+
+ e.HasOne(ud => ud.User)
+ .WithMany(u => u.Documents)
+ .HasForeignKey(ud => ud.UserId)
+ .OnDelete(DeleteBehavior.Cascade);
+
+ e.HasOne(ud => ud.Documents)
+ .WithMany(d => d.User)
+ .HasForeignKey(ud => ud.DocumentId)
+ .OnDelete(DeleteBehavior.Cascade);
+ });
+
+ // -------------------------
+ // token
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("token", ProjectSchema);
+ e.HasKey(t => t.Id);
+ e.Property(t => t.Id).HasColumnName("id");
+ e.Property(t => t.UserId).HasColumnName("user_id");
+ e.Property(t => t.Token).HasColumnName("token");
+ e.Property(t => t.ExpiresAt).HasColumnName("expires_at").HasColumnType("timestamp without time zone");
+ e.Property(t => t.IsValid).HasColumnName("is_valid");
+
+ e.HasOne(t => t.User)
+ .WithMany()
+ .HasForeignKey(t => t.UserId)
+ .OnDelete(DeleteBehavior.Cascade);
+ });
+
+ // -------------------------
+ // payment
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("payment", ProjectSchema);
+ e.HasKey(p => p.Id);
+ e.Property(p => p.Id).HasColumnName("id");
+ e.Property(p => p.UserId).HasColumnName("user_id");
+ e.Property(p => p.EnrollmentInfoId).HasColumnName("enrollment_id");
+ e.Property(p => p.Amount).HasColumnName("amount");
+
+ e.HasOne(p => p.User)
+ .WithMany(u => u.Payments)
+ .HasForeignKey(p => p.UserId)
+ .OnDelete(DeleteBehavior.Cascade);
+
+ e.HasOne(p => p.EnrolledSemesters)
+ .WithMany(es => es.Users)
+ .HasForeignKey(p => p.EnrollmentInfoId)
+ .OnDelete(DeleteBehavior.Cascade);
+ });
+
+ // -------------------------
+ // major_subjects
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("major_subjects", ProjectSchema);
+ e.HasKey(ms => new { ms.MajorId, ms.SubjectId });
+ e.Property(ms => ms.MajorId).HasColumnName("major_id");
+ e.Property(ms => ms.SubjectId).HasColumnName("subject_id");
+ e.Property(ms => ms.MandatorySemester).HasColumnName("mandatory_semester");
+
+ e.HasOne(ms => ms.Majors)
+ .WithMany(m => m.Subjects)
+ .HasForeignKey(ms => ms.MajorId)
+ .OnDelete(DeleteBehavior.Cascade);
+
+ e.HasOne(ms => ms.Subjects)
+ .WithMany(s => s.Majors)
+ .HasForeignKey(ms => ms.SubjectId)
+ .OnDelete(DeleteBehavior.Cascade);
+ });
+
+ // -------------------------
+ // dependency_subject
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("dependency_subject", ProjectSchema);
+ e.HasKey(ds => new { ds.SubjectId, ds.DependencyId });
+ e.Property(ds => ds.SubjectId).HasColumnName("subject_id");
+ e.Property(ds => ds.DependencyId).HasColumnName("dependency_id");
+
+ e.HasOne(ds => ds.Subject)
+ .WithMany(s => s.Dependencies)
+ .HasForeignKey(ds => ds.SubjectId)
+ .OnDelete(DeleteBehavior.Restrict);
+
+ e.HasOne(ds => ds.Dependency)
+ .WithMany(s => s.Dependents)
+ .HasForeignKey(ds => ds.DependencyId)
+ .OnDelete(DeleteBehavior.Restrict);
+ });
+
+ // -------------------------
+ // professour_subjects (spelling matches the table in ddl.sql)
+ // -------------------------
+ modelBuilder.Entity(e =>
+ {
+ e.ToTable("professour_subjects", ProjectSchema);
+ e.HasKey(ps => new { ps.ProfessorId, ps.SemesterId, ps.SubjectId });
+ e.Property(ps => ps.ProfessorId).HasColumnName("prof_id");
+ e.Property(ps => ps.SemesterId).HasColumnName("active_semester_id");
+ e.Property(ps => ps.SubjectId).HasColumnName("subject_id");
+
+ e.HasOne(ps => ps.Professor)
+ .WithMany()
+ .HasForeignKey(ps => ps.ProfessorId)
+ .OnDelete(DeleteBehavior.Cascade);
+
+ e.HasOne(ps => ps.Semester)
+ .WithMany()
+ .HasForeignKey(ps => ps.SemesterId)
+ .OnDelete(DeleteBehavior.Cascade);
+
+ e.HasOne(ps => ps.Subject)
+ .WithMany()
+ .HasForeignKey(ps => ps.SubjectId)
+ .OnDelete(DeleteBehavior.Cascade);
+ });
}
-
}
}
Index: iknow-api/Data/PgEnumLabels.cs
===================================================================
--- iknow-api/Data/PgEnumLabels.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097)
+++ iknow-api/Data/PgEnumLabels.cs (revision ea405569ef29f56fbfc4d77708b6b3faf9286097)
@@ -0,0 +1,49 @@
+using iknow_api.Models;
+using Npgsql;
+
+namespace iknow_api.Data
+{
+ ///
+ /// Maps CLR enum member names onto the labels declared in sql/ddl.sql.
+ /// Most labels are just the lowercased member name; the ones that are not
+ /// (UserRole.Professor -> 'prof', Grade.Ten -> '10') are listed explicitly.
+ ///
+ /// The instances are static on purpose. EF Core caches its internal service
+ /// provider against the DbContext options, and a fresh translator instance
+ /// on every resolution makes each set of options look different - EF then
+ /// builds a new provider per resolution and throws
+ /// ManyServiceProvidersCreatedWarning once it has built twenty.
+ ///
+ public sealed class PgEnumLabels : INpgsqlNameTranslator
+ {
+ public static readonly PgEnumLabels UserRole = new(
+ new Dictionary
+ {
+ [nameof(Models.UserRole.Admin)] = "admin",
+ [nameof(Models.UserRole.Professor)] = "prof",
+ [nameof(Models.UserRole.Student)] = "student",
+ });
+
+ public static readonly PgEnumLabels Grade = new(
+ new Dictionary
+ {
+ [nameof(Models.Grade.Six)] = "6",
+ [nameof(Models.Grade.Seven)] = "7",
+ [nameof(Models.Grade.Eight)] = "8",
+ [nameof(Models.Grade.Nine)] = "9",
+ [nameof(Models.Grade.Ten)] = "10",
+ });
+
+ /// Lowercases the member name, which is all the other enums need.
+ public static readonly PgEnumLabels Lowercase = new(new Dictionary());
+
+ private readonly IReadOnlyDictionary _labels;
+
+ private PgEnumLabels(IReadOnlyDictionary labels) => _labels = labels;
+
+ public string TranslateTypeName(string clrName) => clrName;
+
+ public string TranslateMemberName(string clrName) =>
+ _labels.TryGetValue(clrName, out var label) ? label : clrName.ToLowerInvariant();
+ }
+}