Docker 配置管理系統 – 完整SQL方案

🎯 核心理念回歸

保持你的原始設計思路：

• docker_configs – JSON 管理服務名稱 + YAML 文字儲存配置
• docker_files – 管理 Dockerfile 等相關檔案
• 簡單實用 – 不過度設計，PHP 層處理複雜邏輯

---

📊 主配置表 (docker_configs)

sql

-- Docker 配置主表 - 簡化實用版
CREATE TABLE `docker_configs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `config_name` varchar(100) NOT NULL COMMENT '配置名稱 如：main-stack',
  `description` text DEFAULT NULL COMMENT '配置描述',
  `config_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'JSON格式：服務名稱對應YAML文字內容' CHECK (json_valid(`config_data`)),
  `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_name` (`config_name`),
  KEY `idx_active_updated` (`is_active`, `updated_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Docker配置主表';

---

📁 檔案管理表 (docker_files)

sql

-- Docker 檔案管理表 - 簡化實用版
CREATE TABLE `docker_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `config_id` int(11) NOT NULL COMMENT '關聯配置ID',
  `file_name` varchar(255) NOT NULL COMMENT '檔案名稱',
  `file_path` varchar(500) DEFAULT NULL COMMENT '檔案路徑',
  `file_type` enum('dockerfile','config','script','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 `fk_files_config` (`config_id`),
  KEY `idx_type_active` (`file_type`, `is_active`),
  CONSTRAINT `fk_files_config` FOREIGN KEY (`config_id`) REFERENCES `docker_configs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Docker相關檔案表';

---

🔄 配置備份表 (可選)

sql

-- 簡單的備份表 - 只在需要時使用
CREATE TABLE `docker_configs_backup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `original_id` int(11) NOT NULL COMMENT '原配置ID',
  `config_name` varchar(100) NOT NULL COMMENT '配置名稱',
  `config_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '備份的配置數據' CHECK (json_valid(`config_data`)),
  `backup_note` varchar(255) DEFAULT NULL COMMENT '備份備註',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '備份時間',
  PRIMARY KEY (`id`),
  KEY `idx_original_created` (`original_id`, `created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='配置備份表';

---

🚀 插入你的示例資料

sql

-- 插入主配置（使用你的混合方案）
INSERT INTO `docker_configs` (`config_name`, `description`, `config_data`) VALUES 
('main-stack', 'LAMP Stack with phpMyAdmin', 
JSON_OBJECT(
  'services', JSON_OBJECT(
    'apache', '    build:\n      context: .\n      dockerfile: Dockerfile\n    container_name: apache\n    environment:\n      - TZ=Asia/Hong_Kong\n    ports:\n      - "8080:80"\n    volumes:\n      - /home/docker/www:/var/www/html\n      - /home/docker/php.ini:/usr/local/etc/php/php.ini\n      - /etc/localtime:/etc/localtime:ro\n      - /home/docker/logs/cron.log:/home/docker/logs/cron.log\n    depends_on:\n      - mariadb\n    restart: always',
    
    'mariadb', '    image: mariadb:latest\n    container_name: mariadb\n    restart: always\n    environment:\n      MYSQL_ROOT_PASSWORD: ********\n      MYSQL_DATABASE: repiddeploy\n      MYSQL_USER: repiddeploy\n      MYSQL_PASSWORD: ********\n      TZ: Asia/Hong_Kong\n    ports:\n      - "3306:3306"\n    volumes:\n      - /home/docker/db_data:/var/lib/mysql\n      - /etc/localtime:/etc/localtime:ro',
    
    'phpmyadmin', '    image: phpmyadmin/phpmyadmin\n    container_name: phpmyadmin\n    restart: always\n    environment:\n      PMA_HOST: mariadb\n      PMA_PORT: 3306\n      MYSQL_ROOT_PASSWORD: ********\n    ports:\n      - "8081:80"\n    depends_on:\n      - mariadb',

     "composer": "    image: composer:latest\n    container_name: composer\n    working_dir: /app\n    volumes:\n      - /home/docker/www:/app"
  )
));

-- 插入 Dockerfile
INSERT INTO `docker_files` (`config_id`, `file_name`, `file_path`, `file_type`, `file_content`, `description`) VALUES 
((SELECT id FROM docker_configs WHERE config_name = 'main-stack'), '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');

---

🎯 核心查詢 – 超簡單版本

生成 docker-compose.yml

sql

-- 一鍵生成完整的 docker-compose.yml
SELECT 
CONCAT(
  'services:\n',
  GROUP_CONCAT(
    CONCAT('  ', service_name, ':\n', service_yaml)
    ORDER BY service_name
    SEPARATOR '\n'
  )
) as docker_compose_yml
FROM (
  SELECT 
    service_key as service_name,
    JSON_UNQUOTE(JSON_EXTRACT(config_data, CONCAT('$.services.', service_key))) as service_yaml
  FROM docker_configs, 
       JSON_TABLE(JSON_KEYS(JSON_EXTRACT(config_data, '$.services')), '$[*]' 
         COLUMNS (service_key VARCHAR(50) PATH '$')
       ) as services
  WHERE config_name = 'main-stack' AND is_active = 1
) as service_data;

查看所有服務

sql

-- 查看所有服務列表
SELECT 
  service_name,
  service_yaml
FROM (
  SELECT 
    service_key as service_name,
    JSON_UNQUOTE(JSON_EXTRACT(config_data, CONCAT('$.services.', service_key))) as service_yaml
  FROM docker_configs, 
       JSON_TABLE(JSON_KEYS(JSON_EXTRACT(config_data, '$.services')), '$[*]' 
         COLUMNS (service_key VARCHAR(50) PATH '$')
       ) as services
  WHERE config_name = 'main-stack' AND is_active = 1
) as service_data
ORDER BY service_name;

查看單一服務配置

sql

-- 查看 Apache 服務的 YAML 配置
SELECT JSON_UNQUOTE(JSON_EXTRACT(config_data, '$.services.apache')) as apache_yaml
FROM docker_configs 
WHERE config_name = 'main-stack' AND is_active = 1;

---

✏️ 編輯操作 – 直接實用

編輯服務配置

sql

-- 編輯 Apache 服務
UPDATE docker_configs 
SET config_data = JSON_SET(
  config_data, 
  '$.services.apache', 
  '    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';

添加新服務

sql

-- 添加 Redis 服務
UPDATE docker_configs 
SET config_data = JSON_SET(
  config_data, 
  '$.services.redis', 
  '    image: redis:7-alpine
    container_name: redis
    restart: always
    ports:
      - "6379:6379"
    volumes:
      - /home/docker/redis-data:/data'
),
updated_at = NOW()
WHERE config_name = 'main-stack';

移除服務

sql

-- 移除服務
UPDATE docker_configs 
SET config_data = JSON_REMOVE(config_data, '$.services.phpmyadmin'),
    updated_at = NOW()
WHERE config_name = 'main-stack';

---

🔧 實用工具查詢

備份當前配置

sql

-- 創建備份
INSERT INTO docker_configs_backup (original_id, config_name, config_data, backup_note)
SELECT id, config_name, config_data, '手動備份'
FROM docker_configs 
WHERE config_name = 'main-stack';

獲取所有檔案

sql

-- 獲取完整部署包
SELECT 
  'docker-compose.yml' as filename,
  (SELECT 
    CONCAT(
      'services:\n',
      GROUP_CONCAT(
        CONCAT('  ', service_name, ':\n', service_yaml)
        ORDER BY service_name
        SEPARATOR '\n'
      )
    )
    FROM (
      SELECT 
        service_key as service_name,
        JSON_UNQUOTE(JSON_EXTRACT(config_data, CONCAT('$.services.', service_key))) as service_yaml
      FROM docker_configs, 
           JSON_TABLE(JSON_KEYS(JSON_EXTRACT(config_data, '$.services')), '$[*]' 
             COLUMNS (service_key VARCHAR(50) PATH '$')
           ) as services
      WHERE config_name = 'main-stack' AND is_active = 1
    ) as service_data
  ) as file_content

UNION ALL

SELECT 
  df.file_name as filename,
  df.file_content
FROM docker_files df
JOIN docker_configs dc ON df.config_id = dc.id
WHERE dc.config_name = 'main-stack' 
  AND dc.is_active = 1 
  AND df.is_active = 1
ORDER BY filename;

---

💡 這個版本的特點

✅ 極簡設計 – 只有 2-3 個核心表
✅ 保持你的理念 – JSON 管理服務名稱，YAML 文字儲存配置
✅ 直接可用 – 不需要複雜的設置
✅ PHP 友好 – 簡單的查詢，PHP 容易處理
✅ 擴展性 – 需要時可以輕鬆添加功能

這就是你原始想法的實現 – 簡單、直接、有效！