LTA DATABASE

build instruction

First create a database

CREATE DATABASE lta;

Then create a user

CREATE USER 'ltadev'@'localhost' IDENTIFIED BY '';

Grant database ownership to the new user

GRANT ALL PRIVILEGES ON lta.* TO 'ltadev'@'localhost' WITH GRANT OPTION;

Run the build script

LTA_DB_NAME=lta LTA_DB_USER=ltadev ./db_build

Run a migration

LTA_DB_NAME=lta LTA_DB_USER=ltadev ./db_run 069 

Clear the DB

DROP DATABASE lta; CREATE DATABASE lta;

MySQL Stored Procedure API — Documentation Index

⚠️ Note: This documentation was generated by an AI assistant based on the stored procedure source code. Please verify details before use in production.

---

Overview

This documentation describes the MySQL stored procedure API for a sports club management system. Procedures are grouped by domain and documented in the style of a RESTful API reference.

---

Modules

Module	Description
Authentication	User signup, login, token issuance/validation, secrets
Users	User account management, roles, and statuses
Clubs	Club creation, retrieval, coach assignment, and deletion
Players	Player registration, transfers, updates, and removal
Competitions	Competition lifecycle, team enrollment, and standings
Matches	Match creation, rosters, scoring, tries, referees, and stadiums

---

Common Conventions

OUT Parameters

Several procedures use MySQL OUT parameters to return generated IDs. Callers must declare session variables before calling:

CALL insert_player('John', 'Doe', '2000-01-01', 'M', '12345678', @playerid);
SELECT @playerid;

Status & Role Names

Statuses and roles are stored by name in lookup tables and resolved internally. Commonly used values:

User statuses: 'Actif', 'Waiting Approval', 'Deleted'
Club statuses: 'Actif', 'Deleted'
Player statuses: 'Actif', 'Deleted'
Competition statuses: 'Actif', 'Deleted'

⚠️ Status name lookups are case-sensitive in several procedures. Ensure data in lookup tables is consistent.

Error Handling

Procedures that perform validation raise errors using MySQL's SIGNAL mechanism:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error description';

Callers should handle these using application-level try/catch or MySQL DECLARE HANDLER blocks.

Token Security

Tokens are stored as UNHEX(SHA2(token, 256)) — raw token strings are never stored. The caller is responsible for providing sufficiently random token values and for hashing passwords before passing them to insert_user / signup_user.