🎯 核心理念

• 數據存儲：原始 YAML 內容，無需手動縮進
• 格式處理：查詢時自動添加正確縮進
• 用戶友好：編輯時只需關注配置內容，不用擔心格式

📊 完整 SQL 建立腳本

sql

-- Docker 配置管理系統 v2.2 - 智能縮進版本

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 資料庫： `repiddeploy`
--

-- --------------------------------------------------------

--
-- Docker 配置主表 v2.2 - 智能縮進版
--
CREATE TABLE `docker_configs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `config_name` varchar(100) NOT NULL COMMENT '配置名稱 如：main-stack',
  `service` varchar(50) NOT NULL COMMENT '服務名稱：apache, mariadb, phpmyadmin, composer, redis 等',
  `description` text DEFAULT NULL COMMENT '服務描述',
  `config_data` longtext NOT NULL COMMENT '服務的原始YAML配置內容（無需手動縮進）',
  `service_order` tinyint(3) DEFAULT 0 COMMENT '服務在 compose 中的排序順序',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '是否啟用',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_config_service` (`config_name`, `service`),
  KEY `idx_config_active` (`config_name`, `is_active`),
  KEY `idx_service_type` (`service`),
  KEY `idx_order` (`config_name`, `service_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Docker配置主表-智能縮進版';

-- --------------------------------------------------------

--
-- Docker 檔案管理表 v2.2
--
CREATE TABLE `docker_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `config_name` varchar(100) NOT NULL COMMENT '配置名稱',
  `service` varchar(50) DEFAULT NULL COMMENT '關聯服務名稱（可為空）',
  `file_name` varchar(255) NOT NULL COMMENT '檔案名稱',
  `file_path` varchar(500) DEFAULT NULL COMMENT '檔案路徑',
  `file_type` enum('dockerfile','config','script','compose','other') NOT NULL DEFAULT 'other' COMMENT '檔案類型',
  `file_content` longtext NOT NULL COMMENT '檔案內容',
  `description` text DEFAULT NULL COMMENT '檔案描述',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '是否啟用',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  
  PRIMARY KEY (`id`),
  KEY `idx_config_service` (`config_name`, `service`),
  KEY `idx_type_active` (`file_type`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Docker相關檔案表';

-- --------------------------------------------------------

--
-- 配置備份表 v2.2
--
CREATE TABLE `docker_configs_backup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `config_name` varchar(100) NOT NULL COMMENT '配置名稱',
  `service` varchar(50) NOT NULL COMMENT '服務名稱',
  `config_data` longtext NOT NULL COMMENT '備份的配置數據',
  `backup_note` varchar(255) DEFAULT NULL COMMENT '備份備註',
  `backup_type` enum('manual','auto','pre_update') DEFAULT 'manual' COMMENT '備份類型',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '備份時間',
  
  PRIMARY KEY (`id`),
  KEY `idx_config_service_time` (`config_name`, `service`, `created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='配置備份表';

-- --------------------------------------------------------

--
-- 插入示例數據（原始格式，無需手動縮進）
--

-- 插入 main-stack 配置的各個服務
INSERT INTO `docker_configs` (`config_name`, `service`, `description`, `config_data`, `service_order`) VALUES
('main-stack', 'apache', 'PHP 8.4 Apache 服務', 
'build:
  context: .
  dockerfile: Dockerfile
container_name: apache
environment:
  - TZ=Asia/Hong_Kong
ports:
  - "8080:80"
volumes:
  - /home/docker/www:/var/www/html
  - /home/docker/php.ini:/usr/local/etc/php/php.ini
  - /etc/localtime:/etc/localtime:ro
  - /home/docker/logs/cron.log:/home/docker/logs/cron.log
depends_on:
  - mariadb
restart: always', 1),

('main-stack', 'mariadb', 'MariaDB 資料庫服務', 
'image: mariadb:latest
container_name: mariadb
restart: always
environment:
  MYSQL_ROOT_PASSWORD: ********
  MYSQL_DATABASE: repiddeploy
  MYSQL_USER: repiddeploy
  MYSQL_PASSWORD: ********
  TZ: Asia/Hong_Kong
ports:
  - "3306:3306"
volumes:
  - /home/docker/db_data:/var/lib/mysql
  - /etc/localtime:/etc/localtime:ro', 2),

('main-stack', 'phpmyadmin', 'phpMyAdmin 管理介面', 
'image: phpmyadmin/phpmyadmin
container_name: phpmyadmin
restart: always
environment:
  PMA_HOST: mariadb
  PMA_PORT: 3306
  MYSQL_ROOT_PASSWORD: ********
ports:
  - "8081:80"
depends_on:
  - mariadb', 3),

('main-stack', 'composer', 'Composer PHP 依賴管理', 
'image: composer:latest
container_name: composer
working_dir: /app
volumes:
  - /home/docker/www:/app', 4);

-- 插入 Dockerfile
INSERT INTO `docker_files` (`config_name`, `service`, `file_name`, `file_path`, `file_type`, `file_content`, `description`) VALUES 
('main-stack', 'apache', 'Dockerfile', './Dockerfile', 'dockerfile', 
'FROM php:8.4-apache

# 安裝 APCu、Nano 和其他 PHP 擴展
RUN apt-get update && apt-get install -y \\
    nano \\
    mariadb-client \\
    libicu-dev \\
    libzip-dev \\
    libfreetype6-dev \\
    libjpeg62-turbo-dev \\
    libpng-dev \\
    libwebp-dev \\
    unzip \\
    && docker-php-ext-install intl zip mysqli gd \\
    && pecl install apcu redis \\
    && docker-php-ext-enable apcu redis \\
    && apt-get clean && rm -rf /var/lib/apt/lists/*

RUN docker-php-ext-install opcache

# 設置 ServerName
RUN echo "ServerName localhost" >> /etc/apache2/apache2.conf

# 啟用必要的 Apache 模組，包括 mod_rewrite
RUN a2enmod rewrite

# 設置網站目錄權限（根據需要）
WORKDIR /var/www/html', 
'PHP 8.4 Apache with extensions');

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

🚀 核心查詢操作

📋 生成完整 docker-compose.yml（智能縮進）

sql

-- 智能縮進版：自動處理所有格式
SELECT 
  CONCAT(
    'services:\n',
    GROUP_CONCAT(
      CONCAT('  ', service, ':\n    ', REPLACE(config_data, '\n', '\n    '))
      ORDER BY service_order, service
      SEPARATOR '\n'
    )
  ) as docker_compose_yml
FROM docker_configs 
WHERE config_name = 'main-stack' AND is_active = 1;

🔍 查看所有服務

sql

-- 查看配置中的所有服務
SELECT 
  service,
  description,
  is_active,
  service_order,
  updated_at
FROM docker_configs 
WHERE config_name = 'main-stack'
ORDER BY service_order, service;

🎯 查看單一服務配置

sql

-- 查看 Apache 服務的詳細配置
SELECT 
  service,
  description,
  config_data,
  service_order,
  updated_at
FROM docker_configs 
WHERE config_name = 'main-stack' 
  AND service = 'apache' 
  AND is_active = 1;

📊 配置概覽

sql

-- 配置統計概覽
SELECT 
  config_name,
  COUNT(*) as total_services,
  COUNT(CASE WHEN is_active = 1 THEN 1 END) as active_services,
  MAX(updated_at) as last_modified
FROM docker_configs 
GROUP BY config_name;

✏️ 編輯操作 — 用戶友好版

➕ 添加新服務（原始格式，無需縮進）

sql

-- 添加 Redis 服務 - 用戶只需輸入原始內容
INSERT INTO docker_configs (config_name, service, description, config_data, service_order) VALUES
('main-stack', 'redis', 'Redis 記憶體資料庫', 
'image: redis:7-alpine
container_name: redis
restart: always
ports:
  - "6379:6379"
volumes:
  - /home/docker/redis-data:/data
command: redis-server --appendonly yes', 5);

🔧 編輯服務配置（原始格式）

sql

-- 修改 Apache 服務端口 - 直接編輯原始內容
UPDATE docker_configs 
SET config_data = 'build:
  context: .
  dockerfile: Dockerfile
container_name: apache
environment:
  - TZ=Asia/Hong_Kong
ports:
  - "8888:80"
volumes:
  - /home/docker/www:/var/www/html
  - /home/docker/php.ini:/usr/local/etc/php/php.ini
  - /etc/localtime:/etc/localtime:ro
depends_on:
  - mariadb
restart: always',
    updated_at = NOW()
WHERE config_name = 'main-stack' AND service = 'apache';

❌ 移除服務

sql

-- 停用服務（軟刪除）
UPDATE docker_configs 
SET is_active = 0, updated_at = NOW()
WHERE config_name = 'main-stack' AND service = 'phpmyadmin';

-- 永久刪除服務
DELETE FROM docker_configs 
WHERE config_name = 'main-stack' AND service = 'phpmyadmin';

🔄 調整服務順序

sql

-- 重新排列服務順序
UPDATE docker_configs SET service_order = 1 WHERE config_name = 'main-stack' AND service = 'mariadb';
UPDATE docker_configs SET service_order = 2 WHERE config_name = 'main-stack' AND service = 'apache';
UPDATE docker_configs SET service_order = 3 WHERE config_name = 'main-stack' AND service = 'phpmyadmin';
UPDATE docker_configs SET service_order = 4 WHERE config_name = 'main-stack' AND service = 'composer';

🔀 批量操作

sql

-- 批量端口替換
UPDATE docker_configs 
SET config_data = REPLACE(config_data, '"8080:80"', '"9080:80"'),
    updated_at = NOW()
WHERE config_name = 'main-stack' AND service = 'apache';

-- 批量環境變數替換
UPDATE docker_configs 
SET config_data = REPLACE(config_data, 'TZ: Asia/Hong_Kong', 'TZ: Asia/Taipei'),
    updated_at = NOW()
WHERE config_name = 'main-stack';

🔧 實用工具查詢

💾 備份操作

sql

-- 備份整個配置
INSERT INTO docker_configs_backup (config_name, service, config_data, backup_note, backup_type)
SELECT config_name, service, config_data, '升級前備份', 'pre_update'
FROM docker_configs 
WHERE config_name = 'main-stack' AND is_active = 1;

-- 備份單一服務
INSERT INTO docker_configs_backup (config_name, service, config_data, backup_note)
SELECT config_name, service, config_data, '手動備份 Apache'
FROM docker_configs 
WHERE config_name = 'main-stack' AND service = 'apache';

📦 生成完整部署包

sql

-- 獲取完整部署包（包含所有檔案）
SELECT 
  'docker-compose.yml' as filename,
  'compose' as file_type,
  (SELECT 
    CONCAT(
      'services:\n',
      GROUP_CONCAT(
        CONCAT('  ', service, ':\n    ', REPLACE(config_data, '\n', '\n    '))
        ORDER BY service_order, service
        SEPARATOR '\n'
      )
    )
    FROM docker_configs 
    WHERE config_name = 'main-stack' AND is_active = 1
  ) as file_content

UNION ALL

SELECT 
  df.file_name as filename,
  df.file_type,
  df.file_content
FROM docker_files df
WHERE df.config_name = 'main-stack' AND df.is_active = 1
ORDER BY 
  CASE WHEN filename = 'docker-compose.yml' THEN 1 ELSE 2 END,
  filename;

🔍 服務依賴分析

sql

-- 分析服務依賴關係
SELECT 
  service,
  CASE 
    WHEN config_data LIKE '%depends_on:%' THEN 
      TRIM(SUBSTRING(
        config_data, 
        LOCATE('depends_on:', config_data) + 11,
        CASE 
          WHEN LOCATE('\n', config_data, LOCATE('depends_on:', config_data) + 11) > 0
          THEN LOCATE('\n', config_data, LOCATE('depends_on:', config_data) + 11) - LOCATE('depends_on:', config_data) - 11
          ELSE LENGTH(config_data)
        END
      ))
    ELSE 'No dependencies'
  END as dependencies
FROM docker_configs
WHERE config_name = 'main-stack' AND is_active = 1;

📈 配置變更歷史

sql

-- 查看配置變更歷史
SELECT 
  service,
  backup_note,
  backup_type,
  created_at as backup_time
FROM docker_configs_backup
WHERE config_name = 'main-stack'
ORDER BY created_at DESC;

✅ 配置驗證

sql

-- 檢查配置完整性
SELECT 
  service,
  CASE 
    WHEN config_data LIKE '%image:%' OR config_data LIKE '%build:%' THEN '✓ 有'
    ELSE '✗ 缺少'
  END as has_source,
  CASE 
    WHEN config_data LIKE '%container_name:%' THEN '✓ 有'
    ELSE '✗ 缺少'
  END as has_container_name,
  CASE 
    WHEN config_data LIKE '%ports:%' THEN '✓ 有'
    ELSE '⚠ 無'
  END as has_ports,
  service_order,
  is_active
FROM docker_configs
WHERE config_name = 'main-stack'
ORDER BY service_order;

💡 基本 PHP 整合示例

php

<?php
class DockerConfigManager {
    private $pdo;
    
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    
    // 生成 docker-compose.yml
    public function generateCompose($configName) {
        $sql = "SELECT CONCAT('services:\n', GROUP_CONCAT(CONCAT('  ', service, ':\n    ', REPLACE(config_data, '\n', '\n    ')) ORDER BY service_order, service SEPARATOR '\n')) as compose FROM docker_configs WHERE config_name = ? AND is_active = 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$configName]);
        return $stmt->fetchColumn();
    }
    
    // 獲取所有服務
    public function getServices($configName) {
        $sql = "SELECT * FROM docker_configs WHERE config_name = ? ORDER BY service_order, service";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$configName]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    // 添加服務
    public function addService($configName, $service, $configData, $description = null, $order = 999) {
        $sql = "INSERT INTO docker_configs (config_name, service, description, config_data, service_order) VALUES (?, ?, ?, ?, ?)";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$configName, $service, $description, $configData, $order]);
    }
    
    // 更新服務
    public function updateService($configName, $service, $configData) {
        $sql = "UPDATE docker_configs SET config_data = ?, updated_at = NOW() WHERE config_name = ? AND service = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$configData, $configName, $service]);
    }
    
    // 刪除服務
    public function removeService($configName, $service) {
        $sql = "DELETE FROM docker_configs WHERE config_name = ? AND service = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$configName, $service]);
    }
}

// 使用示例
$pdo = new PDO("mysql:host=localhost;dbname=repiddeploy", $username, $password);
$manager = new DockerConfigManager($pdo);

// 生成 docker-compose.yml
echo $manager->generateCompose('main-stack');

// 添加新服務
$nginxConfig = 'image: nginx:alpine
container_name: nginx
ports:
  - "80:80"
volumes:
  - ./nginx.conf:/etc/nginx/nginx.conf';

$manager->addService('main-stack', 'nginx', $nginxConfig, 'Nginx reverse proxy');
?>

🎯 新版本特點

✅ 極簡設計

• 只保留核心功能和查詢
• 移除複雜的功能，專注實用性

✅ 用戶友好

• 編輯時無需考慮縮進格式
• 智能查詢自動處理所有格式

✅ 高效查詢

• 單一查詢生成完美 docker-compose.yml
• 簡潔的 SQL，易於維護

✅ 擴展便利

• 清晰的表結構
• 標準的操作模式
• 便於後續功能添加

這個精簡版本保持了核心功能的完整性，同時去除了不必要的複雜性！