zkt25/sk1

PHP + PostgreSQL Visit Counter

This project implements a PHP web application for counting visits, backed by a PostgreSQL database. It is containerized using Docker and Docker Compose, and provides automated deployment scripts (to Plesk via Git) as well as deployment cleanup scripts.

---

Table of Contents

1. Overview
2. Features
3. Prerequisites
4. Installation & Configuration
   • Project Structure
   • Environment Variables
   • Docker and Docker Compose
   • Prepare Script (prepare-app.sh)
5. Usage
   • Application Entry Point
   • Table Reset (drop.php)
6. Deployment to Plesk
7. Deployment Cleanup (remove-app.sh)
8. Detailed File Structure
9. Troubleshooting

---

Overview

This simple application counts the number of visits to a web page. On each page load, a new entry is inserted into the visitors table in PostgreSQL, and the total count is displayed.

A button allows to choose between light and dark themes via JavaScript and localStorage.

The project includes:

• A PostgreSQL container with automatic table initialization via db.sql.
• A PHP 8.2 + Apache container serving the application.
• A Dockerfile to build the web image (with the pdo_pgsql extension installed).
• A docker-compose.yml to orchestrate both services.
• A prepare-app.sh script for force-pushing deployments to Plesk via Git.
• A remove-app.sh script for cleaning up the remote deployment.

---

Features

• Automatically inserts a row into the visitors table on each load (INSERT INTO visitors DEFAULT VALUES).
• Automatically creates the table if it doesn’t exist (CREATE TABLE IF NOT EXISTS visitors …).
• Displays the visit counter.
• Light/dark theme toggle, persisted in localStorage.
• Automated Git deployments (Plesk).
• Remote cleanup of pushed content.

---

Prerequisites

• Docker (>= 20.x)
• Docker Compose (>= 1.27.x)
• Git (for the deployment scripts)
• Access to a Plesk instance with a Git-configured repository (for prepare-app.sh and remove-app.sh)

---

Installation & Configuration

Project Structure

├── db.sql               # SQL script to initialize the `visitors` table
├── Dockerfile           # Builds the PHP + Apache + pdo_pgsql image
├── docker-compose.yml   # Docker orchestration (db + web)
├── prepare-app.sh       # Deployment script to Plesk
├── remove-app.sh        # Remote cleanup script
├── index.php        # Main page (insertion + count + front-end)
└── drop.php         # Script to drop the `visitors` table

Environment Variables

In docker-compose.yml, for the db service:

POSTGRES_DB: visitors
POSTGRES_USER: visitor_user
POSTGRES_PASSWORD: secret

And for the web service:

DB_HOST: db
DB_PORT: 5432
DB_NAME: visitors
DB_USER: visitor_user
DB_PASS: secret

Prepare Script (prepare-app.sh)

This script initializes the Git repo (if needed), adds a tuke remote pointing to the Plesk instance, commits all changes, then force-pushes the branch (master or main).

chmod +x prepare-app.sh
./prepare-app.sh

---

Usage

Application Entry Point

index.php performs:

1. Connects to PostgreSQL via PDO (pgsql:host=$dbHost;port=$dbPort;dbname=$dbName).
2. Runs an embedded SQL migration to create the visitors table if it doesn’t exist.
3. Inserts a new row:

   $pdo->exec("INSERT INTO visitors DEFAULT VALUES");
   

4. Retrieves the total count:

   $count = $pdo->query("SELECT COUNT(*) FROM visitors")->fetchColumn();
   

5. Generates the HTML/CSS/JS to display the counter and manage the theme.

Table Reset (drop.php)

The script drop.php connects to the same database and runs:

$pdo->exec("DROP TABLE IF EXISTS visitors");
echo "Table visitors dropped.";

On error it returns HTTP 500 and displays the exception message.

---

Deployment to Plesk

To deploy automatically:

1. Configure GIT_REMOTE_URL and BRANCH in prepare-app.sh.
2. Make it executable and run:

   chmod +x prepare-app.sh
   ./prepare-app.sh
   

This force-pushes the branch to Plesk’s httpdocs, updating the remote site.

---

Deployment Cleanup (remove-app.sh)

To clear the remote content without touching the local repository:

1. Make it executable:

   chmod +x remove-app.sh
   

2. Run:

   ./remove-app.sh
   

This clones the remote into a temp directory, removes all tracked files, commits, and force-pushes, then cleans up.

---

Detailed File Structure

• docker-compose.yml: Coordinates two services:
  • db (PostgreSQL 13) initialized via ./db.sql.
  • web (PHP 8.2 + Apache with pdo_pgsql) linked to db, exposing port 8080.
• Dockerfile: Base php:8.2-apache, installs libpq-dev and pdo_pgsql, copies src/ to /var/www/html/.
• db.sql: SQL to auto-create the visitors table.
• prepare-app.sh & remove-app.sh: Bash scripts for Git-based deployment and cleanup on Plesk.
• index.php: Main page logic (connect, migrate, insert, count, render).
• drop.php: Drops the visitors table.

---

Troubleshooting

• PostgreSQL connection error: Ensure the db service is running; check logs:

  docker-compose logs db
  

• Table not created: Make sure db.sql is mounted under /docker-entrypoint-initdb.d/ and that the db_data volume is empty (initialization only runs on a fresh volume).
• Git push rejected: Verify remote URL and credentials in prepare-app.sh.