Ümit Tunç
85 lines
3.0 KiB
Markdown
85 lines
3.0 KiB
Markdown
# Mariavel Backend - Database Management API
|
|
|
|
This is the backend API for Mariavel, built with Laravel 11. It provides a secure and robust abstraction layer for interacting with MariaDB/MySQL databases, managing schema metadata, and performing administrative operations.
|
|
|
|
## 🛠️ Core Responsibilities
|
|
|
|
- **Schema Discovery**: Dynamically fetches databases, tables, and column metadata using system catalogs (`information_schema`).
|
|
- **SQL Execution**: Executes raw SQL queries and returns structured data with row counts and execution metrics.
|
|
- **Administrative Operations**: Handles bulk table maintenance (Truncate, Drop, Optimize) and technical specifications.
|
|
- **Database Backups**: Integrates with system tools like `mysqldump` to generate full database exports.
|
|
- **Import Management**: Processes SQL files and ZIP archives, handling file extraction and sequence execution.
|
|
- **Data Transformation**: Maps database-specific types to frontend-friendly formats.
|
|
|
|
## 🏗️ Architecture & Structure
|
|
|
|
The backend follows a service-oriented architecture to ensure separation of concerns and database-agnostic potential:
|
|
|
|
### Controller Layer
|
|
- `app/Http/Controllers/Api/SchemaController.php`: Exposes REST endpoints for the frontend, handling request validation and response formatting.
|
|
|
|
### Service Layer (Abstraction)
|
|
- `app/Services/DatabaseService.php`: The main entry point for database operations. It orchestrates high-level tasks and manages driver interactions.
|
|
|
|
### Driver Layer (Implementation)
|
|
- `app/Services/Database/MySqlDriver.php`: Contains the concrete logic for MySQL/MariaDB. It encapsulates raw SQL generation and system command execution.
|
|
|
|
## 📂 Directory Structure
|
|
|
|
```
|
|
app/
|
|
├── Http/
|
|
│ └── Controllers/
|
|
│ └── Api/ # API Endpoints (SchemaController)
|
|
├── Services/
|
|
│ ├── Database/ # Driver implementations (MySqlDriver)
|
|
│ └── DatabaseService.php # Main database abstraction service
|
|
├── Models/ # Eloquent models (if applicable)
|
|
└── Providers/ # Service providers for dependency injection
|
|
routes/
|
|
└── api.php # API route definitions
|
|
storage/
|
|
└── app/backups/ # Temporary storage for export/import files
|
|
```
|
|
|
|
## 🚥 Getting Started
|
|
|
|
### Prerequisites
|
|
- PHP 8.2+
|
|
- Composer
|
|
- MySQL/MariaDB server
|
|
|
|
### Installation
|
|
|
|
1. Install dependencies:
|
|
```bash
|
|
composer install
|
|
```
|
|
|
|
2. Configure environment:
|
|
```bash
|
|
cp .env.example .env
|
|
php artisan key:generate
|
|
```
|
|
|
|
3. Update `.env` with your database credentials:
|
|
```env
|
|
DB_CONNECTION=mysql
|
|
DB_HOST=127.0.0.1
|
|
DB_PORT=3306
|
|
DB_DATABASE=your_database
|
|
DB_USERNAME=your_username
|
|
DB_PASSWORD=your_password
|
|
```
|
|
|
|
4. Run the development server:
|
|
```bash
|
|
php artisan serve
|
|
```
|
|
|
|
## 🔒 Security
|
|
|
|
- All raw SQL execution is handled with caution.
|
|
- File operations are restricted to specific storage directories.
|
|
- Ensure the API is protected by authentication in production environments.
|