database.js

Overview

The database.js file serves as the backend layer of the application, providing methods to interact with an SQLite database. It facilitates data fetching and manipulation, enabling the frontend to query and display relevant information. Below is a detailed analysis of its components and their roles.

1. Imports and Initialization

import sqlite3 from 'sqlite3'; // ES module import
import path from 'path';

const dbPath = path.resolve('DMS-Project.sqlite');

Purpose:

• sqlite3: The SQLite library used to interface with the database.
• path: Ensures a consistent file path for the SQLite database file, regardless of the operating system.

Function: Resolves the path to the SQLite file (DMS-Project.sqlite) that will store the application data.

---

2. Database Connection

const db = new sqlite3.Database(dbPath, (err) => {
    if (err) {
        console.error('Error opening database:', err.message);
    } else {
        console.log('Connected to the DMS-Project database.');
    }
});

• Purpose: Establishes a connection to the SQLite database.
• Error Handling: Logs errors if the database cannot be opened.
• Success Message: Confirms successful connection in the console.

---

3. getPlayersBySeason Function

export function getPlayersBySeason(season, filter = '') {
    return new Promise((resolve, reject) => {
        let query = 'SELECT Player, Tm FROM NFL_Player_Stats_1922 WHERE Season = ?';
        let params = [season];

        if (filter) {
            query += ' AND Player LIKE ?';
            params.push('%' + filter + '%');
        }

        db.all(query, params, (err, rows) => {
            if (err) {
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

Purpose: Retrieves a list of players for a specific NFL season.

Parameters:

• season: The season to query for.
• filter: An optional filter to narrow results based on player names.

SQL Query:

• Selects Player and Tm (team) columns for the specified season.
• Adds a LIKE clause if a filter is provided.

Usage in Frontend: Enables components like Players.vue to fetch and display player data dynamically.

---

4. getPlayerStats Function

export function getPlayerStats(player, sortBy = 'Season', sortOrder = 'ASC', filter = '') {
    return new Promise((resolve, reject) => {
        let query = 'SELECT * FROM NFL_Player_Stats_1922 WHERE Player = ?';
        let params = [player];

        if (filter) {
            query += ' AND Player LIKE ?';
            params.push('%' + filter + '%');
        }

        query += ` ORDER BY ${sortBy} ${sortOrder}`;

        db.all(query, params, (err, rows) => {
            if (err) {
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

Purpose: Fetches detailed statistics for a specific player.

Parameters:

• player: The player's name to query.
• sortBy: Column to sort the results (default: Season).
• sortOrder: Sorting order (ASC or DESC).
• filter: Optional filter to refine results.

SQL Query:

• Selects all columns for the specified player.
• Applies sorting and optional filtering.

Usage in Frontend: Allows for detailed stat displays and sorting in UI components.

---

5. Default Export

export default {
    getPlayersBySeason,
    getPlayerStats
};

• Purpose: Groups the exported functions for simpler import and usage.
• Frontend Interaction: These methods are accessed via window.api in the frontend (e.g., Players.vue).

---

Integration with Players.vue

The Players.vue component interacts with database.js using the exposed getPlayersBySeason function:

1. Fetching Players: The component calls window.api.getPlayersBySeason to retrieve data for the specified season.
2. Displaying Data: Player data is grouped by teams and rendered dynamically.
3. User Interaction: Features like toggling team visibility and filtering players enhance user experience.

---

Error Handling

• Database Connection: Logs connection errors to the console.
• SQL Queries: Rejects promises on query failures, ensuring error propagation to the calling component for appropriate handling.