From 4b91241e4b5ce01e3c958c797c055c4f49df032c Mon Sep 17 00:00:00 2001
From: 云 <2163098428@qq.com>
Date: 星期四, 04 六月 2026 15:53:50 +0800
Subject: [PATCH] feat(database): 添加附件数据迁移脚本从common_file到storage_blob_storage_attachment

---
 doc/update_blob_file.sh                                        |   32 ++++
 doc/20260604_common_file迁移到storage_blob_storage_attachment.sql |  283 ++++++++++++++++++++++++++++++++++++++++
 doc/update_file_size.sh                                        |   49 +++++++
 3 files changed, 364 insertions(+), 0 deletions(-)

diff --git "a/doc/20260604_common_file\350\277\201\347\247\273\345\210\260storage_blob_storage_attachment.sql" "b/doc/20260604_common_file\350\277\201\347\247\273\345\210\260storage_blob_storage_attachment.sql"
new file mode 100644
index 0000000..569b72c
--- /dev/null
+++ "b/doc/20260604_common_file\350\277\201\347\247\273\345\210\260storage_blob_storage_attachment.sql"
@@ -0,0 +1,283 @@
+-- ============================================================
+-- 闄勪欢鏁版嵁杩佺Щ鑴氭湰锛氫粠 common_file 杩佺Щ鍒� storage_blob + storage_attachment
+-- 鎵ц鍓嶈澶囦唤鏁版嵁锛�
+-- ============================================================
+
+-- 璇存槑锛�
+-- 1. common_file 琛細鏃ч檮浠惰〃锛屽寘鍚枃浠朵俊鎭�
+-- 2. storage_blob 琛細鏂版枃浠跺疄浣撹〃锛屽瓨鍌ㄦ枃浠跺厓淇℃伅
+-- 3. storage_attachment 琛細鏂伴檮浠跺叧鑱旇〃锛屽叧鑱斾笟鍔¤褰曚笌鏂囦欢
+
+-- 鏃ц〃瀛楁璇存槑锛�
+-- common_file.id         - 涓婚敭ID
+-- common_file.common_id  - 鍏宠仈涓氬姟ID
+-- common_file.name       - 鏂囦欢鍚嶇О
+-- common_file.url        - 鏂囦欢璺緞
+-- common_file.type       - 涓氬姟绫诲瀷锛堟暣鏁版灇涓撅級
+-- common_file.file_size  - 鏂囦欢澶у皬
+-- common_file.create_time, update_time, create_user, dept_id
+
+-- 鏂拌〃瀛楁璇存槑锛�
+-- storage_blob.id               - 涓婚敭ID
+-- storage_blob.resource_key     - 璧勬簮鍞竴鏍囪瘑锛堝彲鐢ㄥ師ID鎴朥UID锛�
+-- storage_blob.content_type     - MIME绫诲瀷锛堥渶鏍规嵁鏂囦欢鎵╁睍鍚嶆帹鏂級
+-- storage_blob.original_filename- 鍘熷鏂囦欢鍚�
+-- storage_blob.uid_filename     - 鍞竴鏂囦欢鍚嶏紙鍙敤鍘焠ame鎴栫敓鎴愶級
+-- storage_blob.byte_size        - 鏂囦欢澶у皬
+-- storage_blob.path             - 鏂囦欢璺緞
+
+-- storage_attachment.id         - 涓婚敭ID
+-- storage_attachment.record_type- 璁板綍绫诲瀷锛堝瓧绗︿覆鏋氫妇锛�
+-- storage_attachment.record_id  - 鍏宠仈涓氬姟ID
+-- storage_attachment.application- 鏂囦欢鐢ㄩ�旓紙榛樿 'file'锛�
+-- storage_attachment.storage_blob_id - 鍏宠仈 storage_blob.id
+-- storage_attachment.deleted    - 閫昏緫鍒犻櫎鏍囪
+-- storage_attachment.create_time, update_time
+
+-- ============================================================
+-- 绫诲瀷鏄犲皠锛氭棫 type(int) -> 鏂� record_type(string)
+-- 鏍规嵁 FileNameType 鏋氫妇瀹氫箟鏄犲皠鍏崇郴
+-- ============================================================
+
+-- 鏃х被鍨嬫灇涓惧�硷細
+-- 1  = SALE (閿�鍞�)
+-- 2  = PURCHASE (閲囪喘)
+-- 3  = INVOICE (鍙戠エ)
+-- 4  = PURCHASELEDGER (閲囪喘鍙拌处)
+-- 5  = MEASURING (璁￠噺鍣ㄥ叿鍙拌处)
+-- 6  = MEASURINGRecord (璁￠噺鍣ㄥ叿鍙拌处璁板綍)
+-- 7  = ApproveNode (鍗忓悓瀹℃壒鑺傜偣瀹℃牳)
+-- 8  = ApproveProcess (鍗忓悓瀹℃壒涓绘暟鎹�)
+-- 9  = SHIP (鍙戣揣鍙拌处)
+-- 10 = INSPECTION_PRODUCTION_BEFORE (鐢熶骇鍓嶅贰妫�)
+-- 11 = INSPECTION_PRODUCTION_AFTER (鐢熶骇鍚庡贰妫�)
+-- 12 = INSPECTION (宸℃)
+-- 13 = APP
+
+-- 鏂扮被鍨嬪搴旂殑 record_type 瀛楃涓诧紙鏍规嵁 RecordTypeEnum 鎺ㄦ柇锛夛細
+-- 1  -> 'sales_ledger'
+-- 2  -> 'purchase_ledger'
+-- 3  -> 'invoice_ledger'
+-- 4  -> 'purchase_ledger_file'
+-- 5  -> 'measuring_instrument_ledger'
+-- 6  -> 'measuring_instrument_ledger_record'
+-- 7  -> 'approve_node'
+-- 8  -> 'approve_process'
+-- 9  -> 'shipping_info'
+-- 10 -> 'inspection_task'
+-- 11 -> 'inspection_task'
+-- 12 -> 'inspection_task'
+-- 13 -> 'common_file'锛堥�氱敤绫诲瀷鍏滃簳锛�
+
+-- ============================================================
+-- 姝ラ1锛氳縼绉绘枃浠舵暟鎹埌 storage_blob
+-- 娉ㄦ剰锛歜yte_size 鍏堜娇鐢� common_file.file_size锛屽悗缁渶瑕佸湪鏈嶅姟鍣ㄤ笂鏇存柊瀹為檯澶у皬
+-- ============================================================
+
+INSERT INTO storage_blob (
+    resource_key,
+    content_type,
+    original_filename,
+    uid_filename,
+    byte_size,
+    path
+)
+SELECT
+    CONCAT('legacy_', cf.id) AS resource_key,
+    CASE
+        -- 鏍规嵁鏂囦欢鎵╁睍鍚嶆帹鏂� MIME 绫诲瀷
+        WHEN LOWER(cf.name) LIKE '%.jpg' OR LOWER(cf.name) LIKE '%.jpeg' THEN 'image/jpeg'
+        WHEN LOWER(cf.name) LIKE '%.png' THEN 'image/png'
+        WHEN LOWER(cf.name) LIKE '%.gif' THEN 'image/gif'
+        WHEN LOWER(cf.name) LIKE '%.bmp' THEN 'image/bmp'
+        WHEN LOWER(cf.name) LIKE '%.webp' THEN 'image/webp'
+        WHEN LOWER(cf.name) LIKE '%.pdf' THEN 'application/pdf'
+        WHEN LOWER(cf.name) LIKE '%.doc' THEN 'application/msword'
+        WHEN LOWER(cf.name) LIKE '%.docx' THEN 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
+        WHEN LOWER(cf.name) LIKE '%.xls' THEN 'application/vnd.ms-excel'
+        WHEN LOWER(cf.name) LIKE '%.xlsx' THEN 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
+        WHEN LOWER(cf.name) LIKE '%.ppt' THEN 'application/vnd.ms-powerpoint'
+        WHEN LOWER(cf.name) LIKE '%.pptx' THEN 'application/vnd.openxmlformats-officedocument.presentationml.presentation'
+        WHEN LOWER(cf.name) LIKE '%.txt' THEN 'text/plain'
+        WHEN LOWER(cf.name) LIKE '%.zip' THEN 'application/zip'
+        WHEN LOWER(cf.name) LIKE '%.rar' THEN 'application/x-rar-compressed'
+        WHEN LOWER(cf.name) LIKE '%.mp4' THEN 'video/mp4'
+        WHEN LOWER(cf.name) LIKE '%.mp3' THEN 'audio/mpeg'
+        WHEN LOWER(cf.name) LIKE '%.xml' THEN 'application/xml'
+        ELSE 'application/octet-stream'
+    END AS content_type,
+    cf.name AS original_filename,
+    -- 浠� url 涓埅鍙栨渶鍚庣殑鏂囦欢鍚嶉儴鍒�
+    -- url 绀轰緥: /javaWork/product-inventory-management/file/prod/uploads2026-05-19/1652_1779177065596_61abcb44.jpg
+    -- 鎴彇缁撴灉: 1652_1779177065596_61abcb44.jpg
+    CASE
+        WHEN cf.url IS NOT NULL AND cf.url != '' THEN
+            SUBSTRING_INDEX(cf.url, '/', -1)
+        ELSE cf.name
+    END AS uid_filename,
+    COALESCE(cf.file_size, 0) AS byte_size,
+    cf.url AS path
+FROM common_file cf
+WHERE NOT EXISTS (
+    SELECT 1 FROM storage_blob sb WHERE sb.resource_key = CONCAT('legacy_', cf.id)
+);
+
+-- ============================================================
+-- 姝ラ1.5锛氬湪Linux鏈嶅姟鍣ㄤ笂鏇存柊瀹為檯鏂囦欢澶у皬
+-- 鐩存帴澶嶅埗浠ヤ笅鍐呭鍒版湇鍔″櫒鎵ц
+-- ============================================================
+
+-- 鍦ㄦ湇鍔″櫒涓婃墽琛屼互涓嬪懡浠ゅ垱寤鸿剼鏈細
+-- cat > update_blob_file.sh << 'EOF'
+-- #!/bin/bash
+-- FILE_ROOT="/javaWork/product-inventory-management/file/prod"
+-- OUTPUT_SQL="update_blob_file_size.sql"
+-- DB_USER="root"
+-- DB_PASS="xd@123456.."
+-- DB_NAME="product_inventory_management"
+-- echo "寮�濮嬫壂鎻忕洰褰�: $FILE_ROOT"
+-- echo "-- 鏇存柊鏂囦欢澶у皬 SQL" > "$OUTPUT_SQL"
+-- find "$FILE_ROOT" -type f | while read filepath; do
+--     filesize=$(stat -c%s "$filepath" 2>/dev/null || echo "0")
+--     filename=$(basename "$filepath")
+--     filename_escaped=$(echo "$filename" | sed "s/'/''/g")
+--     echo "UPDATE storage_blob SET byte_size = $filesize WHERE uid_filename = '$filename_escaped';" >> "$OUTPUT_SQL"
+-- done
+-- echo "SQL鏂囦欢宸茬敓鎴�: $OUTPUT_SQL"
+-- docker exec -i mysql mysql -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$OUTPUT_SQL"
+-- echo "鏇存柊瀹屾垚!"
+-- EOF
+--
+-- 鐒跺悗鎵ц锛�
+-- chmod +x update_blob_file.sh && ./update_blob_file.sh
+
+-- ============================================================
+-- 姝ラ2锛氳縼绉婚檮浠跺叧鑱旀暟鎹埌 storage_attachment
+-- ============================================================
+
+INSERT INTO storage_attachment (
+    create_time,
+    update_time,
+    deleted,
+    record_type,
+    record_id,
+    application,
+    storage_blob_id
+)
+SELECT
+    cf.create_time,
+    cf.update_time,
+    0 AS deleted,
+    CASE cf.type
+        WHEN 1 THEN 'sales_ledger'
+        WHEN 2 THEN 'purchase_ledger'
+        WHEN 3 THEN 'invoice_ledger'
+        WHEN 4 THEN 'purchase_ledger_file'
+        WHEN 5 THEN 'measuring_instrument_ledger'
+        WHEN 6 THEN 'measuring_instrument_ledger_record'
+        WHEN 7 THEN 'approve_node'
+        WHEN 8 THEN 'approve_process'
+        WHEN 9 THEN 'shipping_info'
+        WHEN 10 THEN 'inspection_task'
+        WHEN 11 THEN 'inspection_task'
+        WHEN 12 THEN 'inspection_task'
+        WHEN 13 THEN 'common_file'
+        ELSE 'common_file'
+    END AS record_type,
+    cf.common_id AS record_id,
+    'file' AS application,
+    sb.id AS storage_blob_id
+FROM common_file cf
+INNER JOIN storage_blob sb ON sb.resource_key = CONCAT('legacy_', cf.id)
+WHERE NOT EXISTS (
+    SELECT 1 FROM storage_attachment sa
+    WHERE sa.storage_blob_id = sb.id
+);
+
+-- ============================================================
+-- 姝ラ3锛氶獙璇佽縼绉荤粨鏋�
+-- ============================================================
+
+-- 妫�鏌ヨ縼绉绘暟閲忔槸鍚︿竴鑷�
+SELECT
+    'common_file 鍘熷璁板綍鏁�' AS description,
+    COUNT(*) AS count
+FROM common_file
+UNION ALL
+SELECT
+    'storage_blob 杩佺Щ璁板綍鏁�' AS description,
+    COUNT(*) AS count
+FROM storage_blob
+WHERE resource_key LIKE 'legacy_%'
+UNION ALL
+SELECT
+    'storage_attachment 杩佺Щ璁板綍鏁�' AS description,
+    COUNT(*) AS count
+FROM storage_attachment sa
+WHERE EXISTS (
+    SELECT 1 FROM storage_blob sb
+    WHERE sb.id = sa.storage_blob_id
+    AND sb.resource_key LIKE 'legacy_%'
+);
+
+-- 鎸夌被鍨嬬粺璁¤縼绉绘暟閲�
+SELECT
+    cf.type AS old_type,
+    CASE cf.type
+        WHEN 1 THEN 'sales_ledger'
+        WHEN 2 THEN 'purchase_ledger'
+        WHEN 3 THEN 'invoice_ledger'
+        WHEN 4 THEN 'purchase_ledger_file'
+        WHEN 5 THEN 'measuring_instrument_ledger'
+        WHEN 6 THEN 'measuring_instrument_ledger_record'
+        WHEN 7 THEN 'approve_node'
+        WHEN 8 THEN 'approve_process'
+        WHEN 9 THEN 'shipping_info'
+        WHEN 10 THEN 'inspection_task'
+        WHEN 11 THEN 'inspection_task'
+        WHEN 12 THEN 'inspection_task'
+        WHEN 13 THEN 'common_file'
+        ELSE 'unknown'
+    END AS new_record_type,
+    COUNT(*) AS count
+FROM common_file cf
+GROUP BY cf.type
+ORDER BY cf.type;
+
+-- ============================================================
+-- 姝ラ4锛氭暟鎹牎楠岋紙鎵ц鍓嶆鏌ユ槸鍚︽湁寮傚父鏁版嵁锛�
+-- ============================================================
+
+-- 妫�鏌ユ槸鍚︽湁 common_id 涓虹┖鐨勮褰�
+SELECT COUNT(*) AS 'common_id涓虹┖鐨勮褰曟暟' FROM common_file WHERE common_id IS NULL;
+
+-- 妫�鏌ユ槸鍚︽湁 name 涓虹┖鐨勮褰�
+SELECT COUNT(*) AS 'name涓虹┖鐨勮褰曟暟' FROM common_file WHERE name IS NULL OR name = '';
+
+-- 妫�鏌ユ槸鍚︽湁 url 涓虹┖鐨勮褰�
+SELECT COUNT(*) AS 'url涓虹┖鐨勮褰曟暟' FROM common_file WHERE url IS NULL OR url = '';
+
+-- ============================================================
+-- 姝ラ5锛氳縼绉诲畬鎴愬悗鐨勬竻鐞嗭紙璋ㄦ厧鎵ц锛佸缓璁厛澶囦唤鏁版嵁锛�
+-- ============================================================
+
+-- 澶囦唤 common_file 琛紙鍙�夛級
+-- CREATE TABLE common_file_backup AS SELECT * FROM common_file;
+
+-- 娓呯┖ common_file 琛紙纭杩佺Щ鏃犺鍚庢墽琛岋級
+-- TRUNCATE TABLE common_file;
+
+-- 鎴栬�呭垹闄ゅ凡杩佺Щ鐨勮褰�
+-- DELETE FROM common_file WHERE EXISTS (
+--     SELECT 1 FROM storage_blob sb
+--     WHERE sb.resource_key = CONCAT('legacy_', common_file.id)
+-- );
+
+-- ============================================================
+-- 娉ㄦ剰浜嬮」锛�
+-- 1. 鎵ц鍓嶅姟蹇呭浠� common_file 琛ㄦ暟鎹�
+-- 2. 寤鸿鍦ㄦ祴璇曠幆澧冨厛楠岃瘉杩佺Щ鑴氭湰鐨勬纭��
+-- 3. 杩佺Щ瀹屾垚鍚庯紝妫�鏌ユ枃浠舵槸鍚﹁兘姝ｅ父璁块棶
+-- 4. 纭涓氬姟鍔熻兘姝ｅ父鍚庯紝鍐嶈�冭檻娓呯悊鏃ф暟鎹�
+-- 5. resource_key 浣跨敤 'legacy_' + 鍘烮D 鐨勬牸寮忥紝渚夸簬杩芥函鍜屽幓閲�
+-- ============================================================
\ No newline at end of file
diff --git a/doc/update_blob_file.sh b/doc/update_blob_file.sh
new file mode 100644
index 0000000..4da5849
--- /dev/null
+++ b/doc/update_blob_file.sh
@@ -0,0 +1,32 @@
+#!/bin/bash
+
+# 鏂囦欢瀛樺偍鏍圭洰褰�
+FILE_ROOT="/javaWork/product-inventory-management/file/prod"
+
+# 杈撳嚭SQL鏂囦欢鍚�
+OUTPUT_SQL="update_blob_file_size.sql"
+
+# 鏁版嵁搴撻厤缃�
+DB_USER="root"
+DB_PASS="xd@123456.."
+DB_NAME="product_inventory_management"
+
+echo "寮�濮嬫壂鎻忕洰褰�: $FILE_ROOT"
+echo "-- 鏇存柊鏂囦欢澶у皬 SQL" > "$OUTPUT_SQL"
+echo "-- 鐢熸垚鏃堕棿: $(date '+%Y-%m-%d %H:%M:%S')" >> "$OUTPUT_SQL"
+echo "" >> "$OUTPUT_SQL"
+
+# 鎵弿鐩綍涓嬫墍鏈夋枃浠跺苟鐢熸垚鏇存柊SQL
+find "$FILE_ROOT" -type f | while read filepath; do
+    filesize=$(stat -c%s "$filepath" 2>/dev/null || echo "0")
+    filename=$(basename "$filepath")
+    filename_escaped=$(echo "$filename" | sed "s/'/''/g")
+    echo "UPDATE storage_blob SET byte_size = $filesize WHERE uid_filename = '$filename_escaped';" >> "$OUTPUT_SQL"
+done
+
+echo "SQL鏂囦欢宸茬敓鎴�: $OUTPUT_SQL"
+echo "姝ｅ湪鎵цSQL鏇存柊..."
+
+docker exec -i mysql mysql -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$OUTPUT_SQL"
+
+echo "鏇存柊瀹屾垚!"
\ No newline at end of file
diff --git a/doc/update_file_size.sh b/doc/update_file_size.sh
new file mode 100644
index 0000000..254937c
--- /dev/null
+++ b/doc/update_file_size.sh
@@ -0,0 +1,49 @@
+#!/bin/bash
+
+# ============================================================
+# 鏇存柊 storage_blob 琛ㄧ殑鏂囦欢澶у皬瀛楁
+# 鎵弿鏂囦欢鐩綍鑾峰彇瀹為檯鏂囦欢澶у皬锛岀敓鎴愭洿鏂癝QL
+# ============================================================
+
+# 鏂囦欢瀛樺偍鏍圭洰褰曪紙鏍规嵁瀹為檯璺緞淇敼锛�
+FILE_ROOT="/javaWork/product-inventory-management/file/prod"
+
+# 杈撳嚭SQL鏂囦欢鍚�
+OUTPUT_SQL="update_blob_file_size.sql"
+
+echo "寮�濮嬫壂鎻忕洰褰�: $FILE_ROOT"
+echo "-- 鏇存柊鏂囦欢澶у皬 SQL" > "$OUTPUT_SQL"
+echo "-- 鐢熸垚鏃堕棿: $(date '+%Y-%m-%d %H:%M:%S')" >> "$OUTPUT_SQL"
+echo "" >> "$OUTPUT_SQL"
+
+# 缁熻璁℃暟
+count=0
+
+# 鎵弿鐩綍涓嬫墍鏈夋枃浠跺苟鐢熸垚鏇存柊SQL
+find "$FILE_ROOT" -type f | while read filepath; do
+    # 鑾峰彇鏂囦欢澶у皬锛堝瓧鑺傦級
+    filesize=$(stat -c%s "$filepath" 2>/dev/null || echo "0")
+
+    # 鑾峰彇鏂囦欢鍚嶏紙鏈�鍚庝竴娈佃矾寰勶級
+    filename=$(basename "$filepath")
+
+    # 杞箟鏂囦欢鍚嶄腑鐨勫崟寮曞彿锛圫QL璇硶瑕佹眰锛�
+    filename_escaped=$(echo "$filename" | sed "s/'/''/g")
+
+    # 鐢熸垚鏇存柊SQL
+    echo "UPDATE storage_blob SET byte_size = $filesize WHERE uid_filename = '$filename_escaped';" >> "$OUTPUT_SQL"
+
+    # 璁℃暟锛堝瓙shell涓棤娉曚紶閫掞紝浠呯敤浜庢樉绀鸿繘搴︼級
+    echo "宸插鐞�: $filename ($filesize bytes)"
+done
+
+echo ""
+echo "=========================================="
+echo "SQL鏂囦欢宸茬敓鎴�: $OUTPUT_SQL"
+echo "=========================================="
+echo ""
+echo "鎵ц鏂规硶:"
+echo "  mysql -u鐢ㄦ埛鍚� -p瀵嗙爜 鏁版嵁搴撳悕 < $OUTPUT_SQL"
+echo ""
+echo "鎴栬�呭厛妫�鏌QL鍐呭:"
+echo "  cat $OUTPUT_SQL"
\ No newline at end of file

--
Gitblit v1.9.3