Omni_kernel.db/basecore/zen_create_table.txt

DELIMITER //

CREATE OR REPLACE PROCEDURE `zen_create_table` (
  IN p_tablename VARCHAR(100),
  IN p_source_type VARCHAR(1)  -- 1:zen_newtable_d  2:zen_fields join zen_function
)
/*
  Function   : 配合 api 可以直接產生 table 的 sp
  Description: 因為只能在相同的 db 執行, 所以在 zenWinzard 或 zenBusiness 都要有此 sp 及 zen_newtable_m/zen_newtable_d
               在平台的架構中雖然放在 Wizard Menu 中, 但手改 json 連到 zenBusiness(zenEIS) 等 ERP DB,
               因為通常需要新增的 table 都是 MIS 要擴充功能用的
               CALL zen_create_table('tmp_test01','2');
               
               2023-07-07
               因為希望參考 iCoder 的作法, 匯入 zen_fields 後, 直接就產生程式(接近一鍵產生程式碼), 所以新增 p_source_typ=2 
               但是 zen_fields 是 zenWinzard 的 table, 如果是分離型 db 架構, 就無法支援 p_source_typ=2 這很傷腦筋, 怎麼辦? 如果傳入 db ??
               看樣子只能先獨立建立 table (p_source_typ=1), 然後匯入 zen_fields, 再產生 Code, 先這樣吧
               
               2023-08-26 用資料庫的同義字功能, 已解決分離式資料庫的問題
               
  Build Date : 2022/02/16
  
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    1    Michael    2022/02/16 Init 
    2    Michael    2023/06/28 新增也可以根據 zen_function/zen_fields 來產生 Table 的寫法(必須配合 zen_function 也已經新增定義才行)
    3    Michael    2023/07/29 create table 後, 要多 select 0 as code 才不會報錯
    4    [改寫者]    2023-11-15 改寫為 MariaDB 版本並最佳化
*/
BEGIN
  DECLARE v_sql LONGTEXT;
  DECLARE v_rets LONGTEXT;
  DECLARE v_maxcount INT;
  DECLARE v_currid INT;
  DECLARE v_nextrowid INT;
  
  DECLARE v_fieldname VARCHAR(100);
  DECLARE v_field_type VARCHAR(100);
  DECLARE v_field_len SMALLINT;
  DECLARE v_is_pk CHAR(1);
  DECLARE v_is_auto CHAR(1);
  DECLARE v_sql_tail VARCHAR(100);
  DECLARE v_sql_pk VARCHAR(100);
  DECLARE v_row_count INT;
  
  -- 創建臨時表來存儲字段信息
  DROP TEMPORARY TABLE IF EXISTS temp_table_fields;
  CREATE TEMPORARY TABLE temp_table_fields (
    fieldname VARCHAR(100),
    field_type VARCHAR(100),
    field_len SMALLINT,
    is_pk CHAR(1),
    is_auto CHAR(1),
    rowid INT AUTO_INCREMENT PRIMARY KEY
  );

  -- 根據來源類型填充臨時表
  IF IFNULL(p_source_type, '1') = '1' THEN
    INSERT INTO temp_table_fields (fieldname, field_type, field_len, is_pk, is_auto)
    SELECT fieldname, field_type, field_len, is_pk, is_auto 
    FROM zen_newtable_d 
    WHERE tablename = p_tablename;
  ELSE
    -- 使用同義字(Synonym)解決跨資料庫問題
    INSERT INTO temp_table_fields (fieldname, field_type, field_len, is_pk, is_auto)
    SELECT 
      x1.fieldname,
      CASE 
        WHEN x1.fieldobject IN ('textarea','input-file','link-file','input-image','nvarchar') THEN 'varchar'
        WHEN x1.fieldobject IN ('int','decimal','smallint') THEN x1.fieldobject
        WHEN x1.fieldobject IN ('radios','progress') THEN 'smallint'
        WHEN x1.fieldobject = 'input-number' THEN 'int'
        WHEN x1.fieldobject IN ('input-datetime','datetime') THEN 'datetime'
        WHEN x1.fieldobject = 'input-date' THEN 'date'
        WHEN LOCATE('cycle', x1.fieldobject) > 0 THEN x1.fieldobject  -- 獨立判斷
        ELSE 'varchar'
      END field_type,
      x1.fieldlength AS field_len,
      CASE WHEN LOCATE(x1.fieldname, x2.mainkey) > 0 THEN 'Y' ELSE 'N' END is_pk,
      x2.isautoinc AS is_auto 
    FROM zen_fields x1
    LEFT JOIN zen_function x2 ON x1.functiontag = x2.functiontag
    WHERE x1.tablename = p_tablename AND x1.fieldobject <> 'formula';
  END IF;	

  -- 初始化變量
  SELECT MAX(rowid), MIN(rowid) INTO v_maxcount, v_currid FROM temp_table_fields;
  
  SET v_rets = '';
  SET v_sql_pk = '';
  
  -- 構建字段定義
  WHILE v_currid <= v_maxcount DO
    -- 取得當前字段信息
    SELECT fieldname, field_type, field_len, is_pk, is_auto 
    INTO v_fieldname, v_field_type, v_field_len, v_is_pk, v_is_auto
    FROM temp_table_fields 
    WHERE rowid = v_currid;
    
    SET v_sql_tail = '';
    IF v_is_pk = 'Y' THEN
      SET v_sql_tail = ' NOT NULL';
      SET v_sql_pk = CONCAT(v_sql_pk, v_fieldname, ',');
    END IF;
    
    IF v_is_auto = 'Y' THEN 
      SET v_sql_tail = CONCAT(v_sql_tail, ' AUTO_INCREMENT');
    END IF;
    
    IF LOCATE('transfer', v_field_type) > 0 OR v_field_len < 0 THEN
      SET v_rets = CONCAT(v_rets, '  ', v_fieldname, ' ', v_field_type, '(255)', v_sql_tail, ',');
    ELSEIF LOCATE('cycle', v_field_type) > 0 THEN
      IF v_field_type IN ('cycle-frequency','cycle-weeks','cycle-months') THEN
        SET v_rets = CONCAT(v_rets, '  ', v_fieldname, ' varchar(200)', v_sql_tail, ',');
      ELSE 
        SET v_rets = CONCAT(v_rets, '  ', v_fieldname, ' datetime ', v_sql_tail, ',');
      END IF;
    ELSEIF LOCATE('char', v_field_type) > 0 THEN
      IF v_field_len <= 0 THEN
        SET v_rets = CONCAT(v_rets, '  ', v_fieldname, ' ', v_field_type, '(200)', v_sql_tail, ',');
      ELSE 
        SET v_rets = CONCAT(v_rets, '  ', v_fieldname, ' ', v_field_type, '(', v_field_len, ')', v_sql_tail, ',');
      END IF;
    ELSE 
      SET v_rets = CONCAT(v_rets, '  ', v_fieldname, ' ', v_field_type, v_sql_tail, ',');
    END IF;
    
    -- 取得下一筆資料
    SELECT MIN(rowid) INTO v_nextrowid FROM temp_table_fields WHERE rowid > v_currid;
    SET v_currid = v_nextrowid;
  END WHILE;

  -- 處理主鍵定義
  IF v_sql_pk = '' THEN
    SET v_sql_pk = '';
    SET v_rets = SUBSTRING(v_rets, 1, CHAR_LENGTH(v_rets) - 1);
  ELSE 
    SET v_sql_pk = CONCAT('  PRIMARY KEY (', SUBSTRING(v_sql_pk, 1, CHAR_LENGTH(v_sql_pk) - 1), ')');
  END IF;

  -- 只有當有字段定義時才創建表
  IF v_rets <> '' THEN
    -- 檢查表是否存在，如果存在且無數據則刪除
    SET @sql_check = CONCAT('SELECT COUNT(*) INTO @row_count FROM information_schema.tables ',
                           'WHERE table_schema = DATABASE() AND table_name = \'', p_tablename, '\'');
    PREPARE stmt FROM @sql_check;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    IF @row_count > 0 THEN
      SET @sql_check = CONCAT('SELECT COUNT(*) INTO @row_count FROM ', p_tablename);
      PREPARE stmt FROM @sql_check;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      
      IF @row_count <= 0 THEN
        SET @sql = CONCAT('DROP TABLE ', p_tablename);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
      END IF;
    END IF;
  
    -- 創建新表
    SET v_sql = CONCAT('CREATE TABLE ', p_tablename, '(', 
                      v_rets, 
                      v_sql_pk, ');');
    
    -- 執行創建表的SQL
    PREPARE stmt FROM v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 返回成功信息
    SELECT 0 AS code, 'OK' AS msg;
  END IF;
END //

DELIMITER ;