วิธีที่ฉัน Localize ฐานข้อมูลตัวอย่าง dvdrental เป็นภาษาญี่ปุ่น: ใช้ SQL และ CSV ร่วมกันเพื่อสร้างข้อมูลสำหรับหน้า Admin

เพื่อให้แอป DVD rental admin ใช้งานได้สะดวกในภาษาญี่ปุ่น ฉัน localize ข้อมูลที่แสดงบนหน้าจอทีละขั้นตอน แทนที่จะใช้ฐานข้อมูลตัวอย่าง PostgreSQL dvdrental ตรงๆ

สิ่งที่ฉันต้องการไม่ใช่การแทนที่แบบ bulk อย่างง่ายๆ ฉันแทนที่ชื่อนักแสดง ชื่อลูกค้า ชื่อพนักงาน ที่อยู่ ชื่อเมือง และคำอธิบายภาพยนตร์ที่แสดงบนหน้า admin ทีละขั้นตอนให้สอดคล้องกับ UI ภาษาญี่ปุ่น

นอกจากนี้ เนื่องจากจำนวนเงินใน dvdrental เป็นข้อมูลตัวอย่างที่อิงกับดอลลาร์ การปรับสำหรับ UI ภาษาญี่ปุ่นหมายความว่าต้องแปลงสกุลเงินเป็นเยนด้วย การแปลง columns จำนวนเงินจากดอลลาร์เป็นเยน ไม่ใช่แค่แปลสตริง ก็เป็นประเด็นสำคัญในงานนี้

ดังนั้นฉันจึงใช้วิธี 2 ขั้นตอน: ใช้ SQL CASE expressions สำหรับข้อมูล master ที่มีระเบียนน้อยและการแปลที่คงที่ และ export ชื่อคน ที่อยู่ และคำอธิบายภาพยนตร์จำนวนมากไปยัง CSV เพื่อแปล แล้ว import กลับด้วย \copy

บทความนี้สำหรับผู้ที่ต้องการรู้วิธี localize ฐานข้อมูลตัวอย่าง PostgreSQL dvdrental เป็นภาษาญี่ปุ่น วิธีแปลข้อมูล PostgreSQL โดยใช้ SQL และ CSV และวิธีแทนที่ข้อมูล dvdrental ด้วยข้อมูลภาษาญี่ปุ่นสำหรับหน้า admin

ฐานข้อมูลตัวอย่าง PostgreSQL ที่ใช้เป็นฐานสามารถดูได้ที่:

PostgreSQL Sample Database

สิ่งที่คุณจะได้เรียนรู้

• Columns ใดที่เป็นเป้าหมายการแปลเมื่อ localize dvdrental
• วิธีแปลข้อมูล master คงที่โดยใช้แค่ SQL
• วิธี export ข้อมูลที่ต้องการคนแปลไปยัง CSV และประมวลผล
• วิธีเปลี่ยนจำนวนเงินใน dvdrental จากอิงดอลลาร์เป็นเยน
• วิธี apply การเปลี่ยนแปลงอย่างปลอดภัยโดยใช้ \copy และ temporary tables
• วิธีที่ข้อมูลที่ localized แล้วถูกสะท้อนไปยัง Docker full dataset

การตัดสินใจครั้งแรก

การตัดสินใจครั้งแรกคือนโยบาย: “แปลเฉพาะภาษาธรรมชาติที่แสดงบนหน้าจอ”

เป้าหมายหลักคือ:

• ชื่อนักแสดง
• ชื่อลูกค้า
• ชื่อพนักงาน
• ที่อยู่
• ชื่อเขต
• ชื่อเมือง
• ชื่อประเทศ
• ชื่อหมวดหมู่
• ชื่อภาษา
• ชื่อภาพยนตร์
• คำอธิบายภาพยนตร์

การตัดสินใจนี้ล่วงหน้าทำให้ง่ายต่อการจัดลำดับความสำคัญและ localize ภาษาธรรมชาติที่ต้องการแสดงบนหน้าจอ

นอกจากนี้ columns จำนวนเงินยังถือเป็นเป้าหมาย localization แยกจากสตริง film.rental_rate, film.replacement_cost และ payment.amount เป็นข้อมูลตัวอย่างอิงดอลลาร์ ดังนั้นแทนที่จะแสดง 円 ถัดจากค่าเหล่านั้น นโยบายคือแปลงเป็นค่าเยนก่อนแสดงบนหน้าจอ

นโยบาย: ไม่ทำให้ทุกอย่างเสร็จด้วยแค่ SQL

ในทางปฏิบัติมีเป้าหมายการแปล 2 ประเภท:

1. ที่มีระเบียนน้อยและการแปลส่วนใหญ่คงที่
2. ที่มีระเบียนมากและต้องการตรวจสอบ context ขณะแปล

อย่างแรกเร็วกว่าที่จะเขียนใน SQL โดยตรง ในขณะที่อย่างหลัง export ไปยัง CSV เพื่อแปลจัดการได้ง่ายกว่ามาก

ดังนั้นในทางปฏิบัติฉันแบ่งดังนี้:

• ชื่อหมวดหมู่ ชื่อภาษา ชื่อประเทศ: อัพเดตด้วย SQL CASE expressions
• ชื่อนักแสดง ชื่อลูกค้า ที่อยู่ ชื่อเมือง ชื่อเขต คำอธิบายภาพยนตร์: export ไปยัง CSV เพื่อแปล
• ชื่อพนักงานมีระเบียนน้อย ดังนั้นแทนที่จะสร้าง CSV แยก จึงรวมไว้เป็นเป้าหมายการแปลใน sql/ja_localization_patch.sql

Flow โดยรวม:

flowchart TD
    A[เลือก columns เป้าหมายการแปลใน dvdrental] --> B[Localize ข้อมูล master คงที่ด้วย SQL]
    A --> C[Export ข้อมูลจำนวนมากไปยัง CSV]
    C --> D[กรอกภาษาญี่ปุ่นใน CSV]
    D --> E[\copy ไปยัง temporary table]
    B --> F[Apply ไปยัง local DB]
    E --> F
    F --> G[ตรวจสอบบนหน้าจอ]
    G --> H[อัพเดต full dataset ด้วย pg_dump]
    H --> I[ใช้ซ้ำสำหรับการ initialize Docker และ AWS]

ก่อนอื่น: แปลข้อมูล Master คงที่ด้วย SQL

สำหรับสิ่งอย่างชื่อหมวดหมู่และชื่อภาษาที่มีระเบียนน้อยและการแปลคงที่ ฉันใช้ sql/ja_localization_stage1.sql

ง่ายๆ แค่แทนที่ค่าภาษาอังกฤษด้วยภาษาญี่ปุ่นโดยใช้ CASE expressions

update public.category
set name = case name
    when 'Action' then 'アクション'
    when 'Animation' then 'アニメーション'
    when 'Children' then '子ども向け'
    when 'Comedy' then 'コメディ'
    else name
end;

ชื่อภาษาก็จัดการในแบบเดียวกัน แทนที่ English ด้วย 英語, Japanese ด้วย 日本語 ฯลฯ

ขั้นตอนนี้ใกล้เคียงกับ “การกำหนดชื่อ” มากกว่า “การแปล” ดังนั้นการจัดการในไฟล์ SQL แทนที่จะ escape ไปยัง CSV จึงชัดเจนกว่า

ชื่อประเทศ: แยกไปยังไฟล์ SQL ต่างหาก

ชื่อประเทศมีระเบียนมากและ statement CASE ยาว จึงแยกไว้ใน sql/ja_localization_stage2_country.sql

update public.country
set country = case country
    when 'Japan' then '日本'
    when 'United States' then 'アメリカ合衆国'
    when 'France' then 'フランス'
    else country
end;

การแยก categories/languages จากชื่อประเทศเป็นไฟล์ต่างกันให้ visibility ที่ดีกว่า และทำให้การตรวจสอบในภายหลังง่ายกว่าการยัดทุกอย่างไว้ในไฟล์เดียว

ข้อมูลปริมาณมาก: Export ไปยัง CSV เพื่อแปล

สำหรับข้อมูลที่มีระเบียนมากอย่างชื่อนักแสดง ชื่อลูกค้า ชื่อพนักงาน ที่อยู่ ชื่อเมือง และคำอธิบายภาพยนตร์ ฉันไม่ได้เขียนการแปลโดยตรงใน SQL

Flow ที่นี่คือ:

1. Export ข้อมูลเป้าหมายด้วย copy (...) to stdout with csv header
2. กรอกภาษาญี่ปุ่นใน columns การแปลที่ว่างเปล่าของ CSV
3. บันทึกเป็น CSV แบบ UTF-8
4. Import ไปยัง temporary table ด้วย \copy
5. อัพเดตเฉพาะ rows ที่กรอก column การแปลแล้ว

ชื่อนักแสดง ชื่อลูกค้า ชื่อพนักงาน: จัดการด้วย ID

ตัวอย่างเช่น ชื่อนักแสดง export ไปยัง CSV ผ่าน sql/export_actor_name_translation_targets.sql โดย output actor_id และชื่อภาษาอังกฤษ

copy (
    select a.actor_id,
           a.first_name as source_first_name,
           a.last_name as source_last_name,
           '' as translated_first_name,
           '' as translated_last_name
    from public.actor a
    order by a.actor_id
) to stdout with csv header;

ด้วยวิธีนี้ งานการแปลต้องการแค่กรอก translated_first_name และ translated_last_name

เมื่อ apply sql/apply_actor_name_translation_from_csv.sql สร้าง temporary table และอัพเดตโดยใช้ actor_id เป็น key

\copy tmp_actor_name_translation (actor_id, source_first_name, source_last_name, translated_first_name, translated_last_name) from 'c:/Users/y_104/git/dvd-rental-admin/sql/actor_name_translation_work_utf8.csv' with (format csv, header true, encoding 'UTF8');

update public.actor a
set first_name = case
                     when t.translated_first_name is not null and btrim(t.translated_first_name) <> '' then t.translated_first_name
                     else a.first_name
                 end,
    last_name = case
                    when t.translated_last_name is not null and btrim(t.translated_last_name) <> '' then t.translated_last_name
                    else a.last_name
                end
from tmp_actor_name_translation t
where a.actor_id = t.actor_id;

Columns ที่ยังว่างเปล่าจะไม่ถูกอัพเดต ดังนั้น CSV ที่แปลแล้วบางส่วนก็สามารถ apply ได้อย่างปลอดภัย

หมายเหตุ staff.first_name และ staff.last_name ก็ใช้แนวทางเดียวกัน เนื่องจากมีพนักงานแค่ 2 คน ไฟล์ CSV แยกจึงไม่ได้สร้างเหมือนชื่อนักแสดง/ลูกค้า แต่รวมไว้เป็นเป้าหมายการอัพเดตใน sql/ja_localization_patch.sql

ที่อยู่และชื่อเมือง: แปลพร้อม Context

เนื่องจากการแปลสตริงที่อยู่เพียงอย่างเดียวอาจเป็นอันตราย sql/export_address_translation_targets.sql และ sql/export_city_translation_targets.sql จึงรวม columns context ด้วย

CSV ที่อยู่รวม:

• address_id
• source_address
• source_address2
• source_district
• source_city
• source_country

copy (
    select a.address_id,
           a.address as source_address,
           coalesce(a.address2, '') as source_address2,
           a.district as source_district,
           ci.city as source_city,
           co.country as source_country,
           '' as translated_address,
           '' as translated_address2,
           '' as translated_district
    from public.address a
    join public.city ci on ci.city_id = a.city_id
    join public.country co on co.country_id = ci.country_id
    order by a.address_id
) to stdout with csv header;

ชื่อเมืองก็ export พร้อมชื่อประเทศด้วย ทำให้ง่ายต่อการจัดการเมืองที่มีชื่อซ้ำหรือการแสดงที่คล้ายกัน

ชื่อเขตจัดการด้วยการแปลที่ deduplicated แทนที่จะเป็นแบบ per-row ใน table address โดยใช้ source_district เป็น primary key สำหรับการอัพเดต

คำอธิบายภาพยนตร์: แยก CSV เพื่อแปล

ปริมาณสูงสุดคือ film.description การทำงานกับไฟล์เดียวไม่สะดวก จึงแยก CSV

ใน directory sql จะพบไฟล์แยกอย่าง:

• film_description_translation_chunk1_utf8.csv
• film_description_translation_chunk2_utf8.csv
• film_description_translation_chunk3_utf8.csv
• ...
• film_description_translation_chunk8_utf8.csv

การแปลทำเป็น chunks แล้วรวมไว้ใน film_description_translation_work_utf8.csv สำหรับการ apply ครั้งสุดท้าย

ฝั่ง application เรียบง่ายมาก แค่ใส่ film_id และคำอธิบายที่แปลแล้วลงใน temporary table แล้วอัพเดต

\copy tmp_film_description_translation (film_id, translated_description) from 'c:/Users/y_104/git/dvd-rental-admin/sql/film_description_translation_work_utf8.csv' with (format csv, header true, encoding 'UTF8');

update public.film f
set description = t.translated_description
from tmp_film_description_translation t
where f.film_id = t.film_id
  and t.translated_description is not null
  and btrim(t.translated_description) <> '';

ข้อความอิสระต้องการการตรวจสอบจากมนุษย์ ดังนั้นการแบ่งเป็น chunks ตั้งแต่แรกเป็นการตัดสินใจที่ถูกต้อง

จำนวนเงิน: ไม่ทิ้งไว้แบบอิงดอลลาร์

ในการ localization นี้ การจัดการสกุลเงินสำคัญพอๆ กับการแปลสตริง

dvdrental เป็นฐานข้อมูลตัวอย่างอิงดอลลาร์ดั้งเดิม ดังนั้น film.rental_rate, film.replacement_cost, payment.amount ฯลฯ มีค่าอิงดอลลาร์ การแสดง 円 ถัดจาก 4.99 หรือ 19.99 บนหน้า admin ดูไม่เป็นธรรมชาติ

ดังนั้น repository นี้ใช้ src/main/resources/db/migration/postgresql/V4__convert_currency_to_jpy.sql เพื่อแปลง columns จำนวนเงินเป็นเยน

alter table film
    alter column rental_rate type numeric(10,0) using ceil(rental_rate * 150),
    alter column replacement_cost type numeric(10,0) using ceil(replacement_cost * 150),
    alter column rental_rate set default 749,
    alter column replacement_cost set default 2999;

alter table payment
    alter column amount type numeric(10,0) using ceil(amount * 150);

การแปลงที่ 1 ดอลลาร์ = 150 เยน แบบ ceiling ทำให้สามารถใช้ความรู้สึกราคาของฐานข้อมูลตัวอย่างบนหน้า admin แบบญี่ปุ่นได้อย่างเป็นธรรมชาติ การเพิ่มการแปลงดอลลาร์เป็นเยนควบคู่กับการ localize สตริงทำให้การแสดงผลของหน้าจอรายการ หน้าจอรายละเอียด และหน้าจอสรุปเป็นธรรมชาติมากขึ้น

Patch การ Localize แบบ Batch ก็ถูกจัดเตรียมด้วย

เมื่อต้องการตรวจสอบเป้าหมายการแปลทั้งหมดพร้อมกัน ฉันจัดเตรียม sql/ja_localization_patch.sql

SQL นี้สร้าง temporary table tmp_ja_translation_map เพื่อ extract สตริงภาษาอังกฤษทั้งหมดที่เป็นเป้าหมายการแปล

create temporary table tmp_ja_translation_map (
    table_name text not null,
    column_name text not null,
    source_text text not null,
    translated_text text,
    primary key (table_name, column_name, source_text)
);

จากนั้น update หมวดหมู่และภาษาด้วยการแปลเริ่มต้น และสุดท้าย reflect ไปยัง base tables

ไฟล์นี้ทำหน้าที่เป็นฐานสำหรับตรวจสอบ “อะไรที่เป็นเป้าหมายการแปล” อย่างครอบคลุม มากกว่าเป็นศูนย์กลางของการดำเนินงานครั้งสุดท้าย

ข้อควรระวังเมื่อ Apply

ในระหว่างการ localize นี้ ฉันมีสติต่อ “ความสามารถในการ revert โดยไม่ทำลาย” มากกว่าตัวการแปลเอง

ดังนั้นฉันจึงปรับให้สอดคล้องกับจุดเหล่านี้:

• อย่าเขียนตรงไปยัง tables ดั้งเดิม ให้ผ่าน temporary tables
• อย่าอัพเดตถ้า column การแปลว่างเปล่า
• Standardize CSVs เป็น UTF-8
• สำหรับสิ่งที่มี ID ให้อัพเดตตาม ID
• สำหรับสิ่งที่ต้องการ context อย่างที่อยู่และเมือง ให้รวม helper columns

ด้วยแนวทางนี้ การอัพเดตมีโอกาสน้อยที่จะพัง แม้แต่การแปลที่ยังไม่เสร็จ และการทำใหม่ก็ง่าย

สุดท้าย: ทำให้ DB ที่ Localized แล้วเป็น Full Dataset

ข้อมูลที่ localized แล้วไม่ได้ทิ้งไว้แค่ใน local DB แต่สุดท้ายถูก reflect ไปยัง docker/postgres/init/01-dvdrental-full.sql

ใน repository นี้ Docker โหลด full dataset SQL นี้ตอน startup ทำให้สามารถใช้สถานะที่ localized แล้วเป็นข้อมูลเริ่มต้นของแอปได้โดยตรง

Flow:

1. จัดระเบียบเป้าหมายการแปลตาม dvdrental
2. Localize โดยใช้ SQL และ CSV
3. Apply ไปยัง local DB และตรวจสอบ
4. สร้าง full dataset SQL ใหม่ด้วย pg_dump
5. ใช้ข้อมูลเดียวกันซ้ำสำหรับการ initialize Docker และ AWS

ด้วยการตั้งค่านี้ การ localize ไม่จบแค่กระบวนการครั้งเดียว แต่สามารถนำไปใช้ซ้ำสำหรับทั้ง development environment และข้อมูลที่ distribute

สรุป

การ localize dvdrental ไม่ใช่การบังคับทุกอย่างผ่านไฟล์ SQL เดียว

ในทางปฏิบัติฉันแบ่งดังนี้:

• หมวดหมู่ ภาษา ชื่อประเทศ: แปลด้วย SQL CASE expressions
• ชื่อนักแสดง ชื่อลูกค้า ชื่อพนักงาน ที่อยู่ ชื่อเมือง ชื่อเขต คำอธิบายภาพยนตร์: จัดระเบียบเป็นเป้าหมายการแปล
• Columns จำนวนเงิน: แปลงจากค่าอิงดอลลาร์เป็นเยน
• เมื่อ apply: อัพเดตอย่างปลอดภัยโดยใช้ temporary tables และ \copy
• สุดท้าย: นำ DB ที่ localized แล้วไปใช้ซ้ำเป็น full dataset

สำหรับผู้ที่ต้องการ localize ฐานข้อมูลตัวอย่าง PostgreSQL dvdrental เป็นภาษาญี่ปุ่นสำหรับแอป admin screen แนวทาง “ข้อมูล master คงที่ใน SQL ข้อมูลจำนวนมากที่ต้องการคนทำใน CSV” จัดการได้ง่ายมาก

สำหรับวิธีสร้างตัวแอปเอง ดูที่บทความนี้:

การสร้างแอป DVD Rental Admin ด้วย Spring Boot + Thymeleaf โดยอิงจากฐานข้อมูลตัวอย่าง PostgreSQL dvdrental

สำหรับ configuration และแนวทางเมื่อ deploy ไปยัง AWS ดูที่บทความนี้:

Configuration, Operations, and Security When Deploying a Spring Boot + Thymeleaf + PostgreSQL Admin App to AWS ECS/Fargate + RDS