File size: 5,053 Bytes
a484243 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 | """
database.py
------------
SQLAlchemy models.
Student - a registered student. image_folder points to their
private photo directory (NOT under /static -- see
face_engine.py and the /media route in app.py for why).
AttendanceRecord - one row per "present" event.
Note on `confidence`: this is now a cosine-similarity score (roughly 0-1,
occasionally slightly negative for a very bad match) rather than a softmax
probability, since recognition switched from a trained classifier to
embedding similarity matching. See face_engine.py for details.
Note on location fields: latitude/longitude/distance_meters/location_verified
are nullable and only populated when geofencing is enabled (see geofence.py).
Existing databases from before this feature was added won't have these
columns -- run_lightweight_migrations() below adds them on startup via plain
ALTER TABLE statements, so there's no need for a full migration framework
for a handful of nullable columns on SQLite.
"""
from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text
db = SQLAlchemy()
class Student(db.Model):
__tablename__ = "students"
id = db.Column(db.Integer, primary_key=True)
student_id = db.Column(db.String(50), unique=True, nullable=False, index=True)
name = db.Column(db.String(100), nullable=False)
image_folder = db.Column(db.String(255), nullable=False)
created_at = db.Column(db.DateTime, default=datetime.utcnow)
# cascade="all, delete-orphan" so deleting a student also deletes their
# attendance history in one db.session.delete(student) call.
attendance_records = db.relationship(
"AttendanceRecord", backref="student", lazy=True, cascade="all, delete-orphan"
)
def __repr__(self):
return f"<Student {self.student_id} - {self.name}>"
class AttendanceRecord(db.Model):
__tablename__ = "attendance_records"
__table_args__ = (
# Belt-and-suspenders against the check-then-insert race in
# app.py's attendance route: two near-simultaneous requests for the
# same student on the same day can't both slip through as separate
# rows even if the app-level "already marked?" check races.
db.UniqueConstraint("student_pk", "date", name="uq_student_date"),
)
id = db.Column(db.Integer, primary_key=True)
student_pk = db.Column(db.Integer, db.ForeignKey("students.id"), nullable=False)
date = db.Column(db.Date, nullable=False, default=lambda: datetime.utcnow().date())
time = db.Column(db.Time, nullable=False, default=lambda: datetime.utcnow().time())
confidence = db.Column(db.Float, nullable=False) # cosine similarity score at recognition time
# Populated only when geofencing is enabled (see geofence.py).
latitude = db.Column(db.Float, nullable=True)
longitude = db.Column(db.Float, nullable=True)
distance_meters = db.Column(db.Float, nullable=True)
location_verified = db.Column(db.Boolean, nullable=True) # None = geofencing was off for this check-in
def __repr__(self):
return f"<AttendanceRecord student_pk={self.student_pk} date={self.date}>"
def run_lightweight_migrations(engine):
"""
Adds any columns/indexes that exist on the current models but not yet in
an existing SQLite database file -- covers upgrading a deployment that
predates the geofencing columns or the uq_student_date constraint,
without needing Alembic for a handful of nullable columns and one index.
Safe to call every startup; it's a no-op once caught up.
"""
with engine.connect() as conn:
existing = {row[1] for row in conn.execute(text("PRAGMA table_info(attendance_records)"))}
new_columns = {
"latitude": "FLOAT",
"longitude": "FLOAT",
"distance_meters": "FLOAT",
"location_verified": "BOOLEAN",
}
for name, col_type in new_columns.items():
if name not in existing:
conn.execute(text(f"ALTER TABLE attendance_records ADD COLUMN {name} {col_type}"))
conn.commit()
# SQLite can't ALTER TABLE to add a constraint after the fact, but a
# unique index enforces the same guarantee. If a pre-existing database
# somehow already has duplicate (student_pk, date) rows, this will
# fail -- caught and logged rather than blocking startup, since that
# would turn a data-quality issue into a launch-blocking outage.
try:
conn.execute(text(
"CREATE UNIQUE INDEX IF NOT EXISTS uq_student_date "
"ON attendance_records(student_pk, date)"
))
conn.commit()
except Exception as e:
print(f"[database] Could not create uq_student_date index (likely pre-existing duplicate "
f"rows) -- duplicate same-day attendance rows are not blocked at the DB level "
f"until this is resolved manually. Error: {e}") |