Skip to content

SQL_SETUP_GUIDE.md

Latest commit

 

History

History
147 lines (110 loc) · 4.55 KB

SQL_SETUP_GUIDE.md

File metadata and controls

147 lines (110 loc) · 4.55 KB

College Complaint Management System - SQL Setup Guide

Overview

Your College Complaint Management System now uses SQLite database for persistent storage instead of IndexedDB. This means all data (complaints, users, chats) is saved permanently in a SQL database file (college_complaints.db) and will persist even after browser sessions end.

Architecture Changes

  • Frontend: HTML, CSS, JavaScript (unchanged user interface)
  • Backend: Node.js with Express.js server
  • Database: SQLite3 with permanent file storage
  • Communication: REST API between frontend and backend

Setup Instructions

1. Install Node.js Dependencies

Navigate to your project directory and install required packages:

cd /Users/ntggamer1/Desktop/Project
npm install

This installs:

  • express: Web server framework
  • sqlite3: SQL database
  • cors: Cross-origin request handling
  • body-parser: JSON request parsing

2. Start the Server

Run the server in the terminal:

npm start

You should see output like:

🚀 Server running on http://localhost:3001
💾 SQLite database: college_complaints.db

3. Open the Application

Open index.html in your web browser:

file:///Users/ntggamer1/Desktop/Project/index.html

The application will now communicate with the SQL database through the backend server.

Database Structure

Tables Created:

users Table

  • Stores student and official accounts
  • Fields: id, username, password, name, email, type, student_id, registered_date

complaints Table

  • Stores all complaints submitted by students
  • Fields: id, student_id, student_name, title, category, description, status, timestamp
  • Indexes on: student_id, status, category, timestamp

chats Table

  • Stores communication between students and officials
  • Fields: id, complaint_id, sender_name, sender_id, sender_role, text, timestamp
  • Indexes on: complaint_id, timestamp

Default Demo Accounts

Role Username Password
Student student1 1234
Student student2 1234
Official admin admin123

Data Persistence

✅ Data persists across sessions - All data is saved in the SQLite database file ✅ Even after closing browser - Database remains on disk ✅ Cross-browser access - Multiple browsers can access the same database (via the server)

API Endpoints

Complaints

  • GET /api/complaints - Get all complaints
  • GET /api/complaints/:id - Get single complaint
  • GET /api/complaints/student/:studentId - Get student's complaints
  • GET /api/complaints/status/:status - Filter by status
  • GET /api/complaints/category/:category - Filter by category
  • POST /api/complaints - Create new complaint
  • PUT /api/complaints/:id - Update complaint
  • DELETE /api/complaints/:id - Delete complaint

Users

  • GET /api/users/student/:username - Get student info
  • GET /api/users/students/all - Get all students
  • POST /api/users/student - Register new student
  • PUT /api/users/student/:username - Update student info
  • DELETE /api/users/student/:username - Delete student

Chats

  • GET /api/chats - Get all chats
  • GET /api/chats/complaint/:complaintId - Get chats for complaint
  • POST /api/chats - Send chat message
  • DELETE /api/chats/complaint/:complaintId - Delete complaint chats

Files Modified/Created

New Files:

  • server.js - Express backend server with SQLite
  • api-client.js - Frontend API client for making HTTP requests
  • package.json - Node.js dependencies

Modified Files:

  • script.js - Updated to use API instead of IndexedDB
  • index.html - Added api-client.js script reference

Kept for Compatibility:

  • database.js - No longer used (IndexedDB code) but kept in project
  • styles.css - Unchanged
  • styles.css - Unchanged

Troubleshooting

"Cannot GET /api/complaints"

  • Make sure server is running (npm start)
  • Check that port 3001 is available

"CORS error" or "Failed to fetch"

  • Server may not be running
  • Check browser console for detailed error messages

Database file missing

  • Database is created automatically on first server run
  • Check that you have write permissions in the project directory

Development Notes

  • The old database.js (IndexedDB) is still in the project but not used
  • You can safely delete it if desired
  • All API calls in api-client.js have error handling and logging
  • Server includes proper indexes for fast queries

To Stop the Server

In the terminal where the server is running, press Ctrl+C

The database file remains saved and data persists for the next session.