腳本撰寫簡介：以資料更新為例

手機版選單

下方連結

腳本撰寫簡介：以資料更新為例

卷期：v0050
出版日期：2019-09-20

作者：許凱平/臺灣大學計算機及資訊網路中心作業管理組副組長

腳本語言(Scripting Language)又譯手稿語言[1]，源自系統管理者將一些例行的管理工作編寫於一個文字檔，稱為手稿(Script)，執行這個Script，就可將這一批工作一次完成。本文將以進行異質資料庫唯讀副本的更新為例，將Node.js[2]作為主程式，結合批次檔(Batch file)[3]呼叫資料庫工具程式，並以VIM Script[4]編寫SQL Script[5]，介紹腳本語言以及其應用情境。

前言

隨著WWW的興起、人工智慧的再度復興，腳本語言從系統管理員的最愛變成了網頁應用工程師、人工智慧工程師的必備語言。相較於C、C++與Java等需要編譯的語言，執行的是編譯後的二進位檔，腳本語言的程式(Script)只是簡單的文字檔，用普通的編輯器撰寫存檔後，就可以用直接執行。

腳本語言

常見的腳本語言如執行於瀏覽器的JavaScript[6]、科學計算常用的Python[7]、系統管理用的PowerShell[8]與處理資料的SQL Script等等，共通點都是一樣不需編譯即可執行。點選腳本檔，作業系統藉由副檔名繫結到相應的直譯器，如.ps1會交由PowerShell執行，.bat會交由cmd.exe執行，就如同.docx檔案會交由Word等文字處理器處理一樣。

腳本語言按照維基百科，有以下的分類

膠水語言(Glue language)
工作控制語言和殼層(Job control language and shells)：批次檔
GUI指令碼(GUI scripting)
應用程式客製化的手稿語言(Application-specific languages)
擴充/可嵌入語言(Extension/embeddable languages)
Web編程指令碼
文字處理語言：VIM Script
通用動態語言

常見的電腦程式以介面區分，大概就是GUI圖形使用者介面(Graphic User Interface)與CLI命令列介面(Command Line Interface)兩種[9]。GUI的方式直覺易學，但通常需要人為操作，較不利於自動化。腳本語言的直譯器通常都有CLI的介面，CLI的方式不但可以撰寫腳本自動化，還可以透過輸入輸出導向等方式與其它工具程式協作，完成更複雜的工作。

批次檔

批次檔直譯器就是CMD.EXE，在微軟DOS或Windows作業系統，如果需要在開機時執行設定路徑、掃毒或清理磁碟等工作，就可以將這些命令寫在腳本檔，執行這個腳本檔就可以完成多個工作。例如資料庫備份常有儲存空間不足的問題，如以下指令，就可以將備份軟體(pg_dumpall)的輸出串流(|)到壓縮軟體(7z)做即時壓縮；另外也可將這些指令存成批次檔方便下次執行。

dumpall.bat

pg_dumpall | 7z a -si db.zip

以匯入和匯出資料的方法進行更新

每一個類型的資料庫，都有一些匯入和匯出資料的工具，資料更新若是發生在同型的資料庫，通常可以用一個軟體做完匯出與匯入。異質資料庫通常需要分別操作不同的工具程式，分別做匯出與匯入[10,11]。

案例

說明：從「課程網」資料庫取得課程相關資料表以提供「行動網頁課程查詢」來源資料庫(Provider)：db1, PostgreSQL

目的資料庫(Consumer)：db2, Microsoft SQL Server

權限設定：Provider提供具讀取權限之資料庫帳號account給Consumer使用

更新的步驟

1. 異動偵測：比對目標物件於兩端是否存在差異，有異動再發動更新。

2. 匯出：從來源端讀取後，存到暫存檔或暫存資料表。

3. 處理：檢查匯出資料的正確性與轉換。

4. 匯入：將資料放入目的端正式資料表。

異動偵測：以psql執行SQL Script檢查資料庫狀態

無條件的更新最簡單，直接複製即可；更新越頻繁，資料越一致。但是由於複製需要耗用運算、網路與儲存資源，給自己與合作夥伴的系統帶來負荷，所以應該避免過於頻繁的更新，應該是在有必要的時候才進行，例如遠端來源物件與本地副本有差異之時。

偵測方式
檢查雜湊值(Hash Value)
檢查筆數，

在我們的案例中，因為來源資料庫的版本較舊(v8)，不支援雜湊函數，所以我們就以筆數檢查代替。但是檢查筆數的偵測方式有可能會漏掉一些須更新的情境，例如正好新增一筆又刪除一筆，所以最好要搭配定期無條件更新。

count.sql

SELECT 'table1' as tbl, COUNT(*) as cnt FROM table1 UNION

SELECT 'table2' as tbl, COUNT(*) as cnt FROM table2 UNION

SELECT 'table3' as tbl, COUNT(*) as cnt FROM table3 UNION

SELECT 'table4' as tbl, COUNT(*) as cnt FROM table4 UNION

SELECT 'table5' as tbl, COUNT(*) as cnt FROM table5 UNION

ORDER BY tbl

利用UNION 一次檢查多個資料表

s.count.bat呼叫PostgreSQL 的工具程式psql執行SQL Script

echo off

set PGPASSWORD=password

FOR /f %%a in ('WMIC OS GET LocalDateTime ^| find "."') DO set DTS=%%a

set CUR_DATE=%DTS:~0,4%-%DTS:~4,2%-%DTS:~6,2%

set OUTFILE=source\%CUR_DATE%.txt

psql --file=count.sql --output=%OUTFILE% "postgresql://account@host/db1"

type %OUTFILE%

假設每天執行一次，執行結果以yyyy-mm-dd.txt 命名存檔，如2019-09-09.txt，藉由比對昨天與今天的檔案差異，決定是否要進行接下來的資料更新後續動作。注意，以WMIC OS GET LocalDateTime取代Date指令取回的時間，日期格式不受Locale設定影響。

以pg_dump 產生SQL Script匯出資料

以PostgreSQL為例，我們可以使用pg_dump工具程式將特定資料表從來源資料庫中取出。

export.bat

set PGPASSWORD=password

pg_dump -t TABLE1 --inserts -f export.sql "postgresql://account@host/db1"

-t TABLE1 轉存特定資料表

--inserts 產生SQL INSERT 指令，相容性佳

-f export.sql轉存檔檔名

export.sql

-- PostgreSQL database dump

SET statement_timeout = 0;

….

CREATE TABLE public.table1(

…

INSERT INTO public.table1VALUES (…);

…

INSERT INTO public.table1VALUES (…);

….

GRANT

…

以VIM Scripts處理SQL Script

VIM Script基本上就是vi指令，加上VIM Script的一些輔助函數。資料庫工具程式產生的SQL Script通常需要做一些編輯，才能塞到目的資料庫。例如移除來源端資料庫專屬的設定指令、移除目的端無法接受的特殊字元、多餘的空白與分行，語法改寫等等。這些重複的編輯動作，我們可以利用VIM Script進行自動化。

template.vim

:%s/^INSERT INTO public/INSERT INTO dbo/g

:let ln_start = search('INSERT')

:$

:let ln_end = search('INSERT', 'b')

:execute ln_start . "," . ln_end . " w inserts.sql "

:wq!

將Schema Name由來源端的public改為目的端的 dbo。

找到INSERT指令的起訖行號(ln_start, ln_end)，將所有的INSERT statement寫到insert.txt。

:wq!後的空白行必須保留(模擬送出Enter)，才會將寫入離開的指令送出。

如果最後一個INSERT指令的資料中有換行，可能會導致INSERT錯誤。

對export.sql執行自動編輯，並將結果輸出到inserts.sql。