Package com.studentgui.apphelpers

Class Database

java.lang.Object
com.studentgui.apphelpers.Database
public class Database extends Object
Centralized database helper for the normalized SQLite schema.

Provides convenience methods to get-or-create Students and ProgressTypes, create ProgressSessions, ensure AssessmentParts, insert/fetch assessment results, and save session-specific notes. Use these helpers instead of running per-page DDL throughout the codebase.

  • Method Details

    • getOrCreateStudent

      public static int getOrCreateStudent(String name) throws SQLException
      Get a student id by name, creating a new Student row when none exists.
      Parameters:
      name - student display name
      Returns:
      id of the existing or newly created student
      Throws:
      SQLException - on database errors

    • getOrCreateProgressType

      public static int getOrCreateProgressType(String name) throws SQLException
      Get or create a ProgressType row by name.
      Parameters:
      name - progress type display name
      Returns:
      database id of the progress type
      Throws:
      SQLException - on database errors

    • ensureAssessmentParts

      public static void ensureAssessmentParts(int progressTypeId, String[] codes) throws SQLException
      Ensure AssessmentPart rows exist for the given progress type. This uses SQL "INSERT OR IGNORE" so existing parts are preserved.
      Parameters:
      progressTypeId - id of the ProgressType
      codes - array of part codes to ensure
      Throws:
      SQLException - on database errors

    • cleanupAssessmentParts

      public static void cleanupAssessmentParts(int progressTypeId, String[] allowedCodes) throws SQLException
      Remove any AssessmentPart rows for the given progress type whose code is not present in the provided canonical codes array. This helps clean up legacy/malformed entries that could cause part ordering mismatches.
      Parameters:
      progressTypeId - id of the ProgressType
      allowedCodes - canonical set of codes to keep
      Throws:
      SQLException - on database errors

    • createProgressSession

      public static int createProgressSession(int studentId, int progressTypeId, LocalDate date) throws SQLException
      Create a ProgressSession for a student and progress type on the given date.
      Parameters:
      studentId - existing student id
      progressTypeId - existing progress type id
      date - session date
      Returns:
      generated ProgressSession id
      Throws:
      SQLException - on database errors

    • insertAssessmentResults

      public static void insertAssessmentResults(int sessionId, int progressTypeId, String[] codes, int[] scores) throws SQLException
      Insert assessment results for a session. The codes and scores arrays must be parallel and correspond to existing AssessmentPart codes. Unknown part codes are ignored.
      Parameters:
      sessionId - progress session id
      progressTypeId - progress type id
      codes - array of part codes
      scores - array of integer scores
      Throws:
      SQLException - on database errors

    • fetchLatestAssessmentResults

      public static List<List<Integer>> fetchLatestAssessmentResults(String studentName, String progressTypeName, int limit) throws SQLException
      Fetch the latest assessment result rows for a named student and progress type. Each returned row is a list of integer scores for the parts in canonical part order.
      Parameters:
      studentName - student display name
      progressTypeName - progress type display name
      limit - maximum number of recent sessions to fetch
      Returns:
      list of rows, each row is a list of integer scores
      Throws:
      SQLException - on database errors

    • fetchLatestAssessmentResultsWithDates

      public static Database.ResultsWithDates fetchLatestAssessmentResultsWithDates(String studentName, String progressTypeName, int limit) throws SQLException
      Fetch the latest assessment rows along with their session dates. Rows and dates are ordered oldest-first to facilitate time series plotting.
      Parameters:
      studentName - student display name to filter results for
      progressTypeName - progress type display name (e.g., "Braille")
      limit - maximum number of recent sessions to return
      Returns:
      ResultsWithDates holding an ordered list of session dates and parallel rows of scores
      Throws:
      SQLException - on database errors

    • insertKeyboardingResult

      public static void insertKeyboardingResult(int sessionId, String program, String topic, int speed, int accuracy) throws SQLException
      Insert a keyboarding-specific result linked to a ProgressSession.
      Parameters:
      sessionId - existing session id
      program - program or curriculum name
      topic - topic or lesson name
      speed - words-per-minute
      accuracy - accuracy percent
      Throws:
      SQLException - on database errors

    • saveSessionNotes

      public static void saveSessionNotes(int sessionId, String notes) throws SQLException
      Save free-form notes for a given ProgressSession.
      Parameters:
      sessionId - progress session id
      notes - free-form notes text
      Throws:
      SQLException - on database errors

    • saveContactLog

      public static void saveContactLog(int sessionId, String studentName, String date, String guardianName, String contactMethod, String phoneNumber, String emailAddress, String contactResponse, String contactGeneral, String contactSpecific, String contactNotes) throws SQLException
      Save structured contact log details for a given ProgressSession. This will insert or replace a single ContactLog row tied to the session.
      Parameters:
      sessionId - existing session id
      studentName - student display name
      date - session date as text
      guardianName - guardian or parent name
      contactMethod - method of contact (phone/email/etc)
      phoneNumber - phone number string
      emailAddress - email address string
      contactResponse - short description of response
      contactGeneral - general contact summary
      contactSpecific - specific items discussed
      contactNotes - free-form notes
      Throws:
      SQLException - on database errors

    • fetchLatestContactLog

      public static ContactPayload fetchLatestContactLog(String studentName) throws SQLException
      Fetch the most recent ContactLog entry for the given student name. Returns a map of column names to string values, or null if none found.
      Parameters:
      studentName - student display name to search for
      Returns:
      map of contact log columns to values or null when not found
      Throws:
      SQLException - on database errors