跳到主要內容區塊

計資中心電子報C&INC E-paper

專題報導

腳本撰寫簡介:以資料更新為例
  • 卷期: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 (…);

 

  …

 

  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。

process.bat

  vim -s template.vim export.sql

sqlcmd執行SQL Script匯入MSSQL資料庫

sqlcmd是Microsoft SQL Server的公用程式,執行inserts.sql,匯入到sql1伺服器的資料庫db1。

import.bat

  sqlcmd -S sql1 -d db2 -i inserts.sql -o msg.txt  -f 65001

以Node.js 串接

Node.js的模組node-cmd可以用來執行批次檔,再依據執行結果決定下一步動作。

coupd.js

const nodeCmd = require('node-cmd');

nodeCmd.get('cd detect&&count.bat', (err, data, stderr) => {

        (async () => {

           // Compare files

           // export.bat

           // process.bat

          // import.bat

        })();

    });

 

結語

處理各項電腦化工作時,常常會搜尋到以不同程式語言處理的解決方案。各種腳本語言都有其功能,透過膠水語言的作用,可以解決單一語言可能不是那麼容易處理的複雜工作。腳本語言的程式以往常被詬病執行效率不如編譯語言,但其帶來的方便性、普及性與豐富的函式庫,值得大家投入時間學習。

 

參考資料

[1] Scripting language, https://en.wikipedia.org/wiki/Scripting_language

[2] Node.js, https://nodejs.org/

[3] Batch file, https://en.wikipedia.org/wiki/Batch_file

[4] VIM Script, https://www.ibm.com/developerworks/cn/linux/l-vim-script-1/

[5] SQL, https://zh.wikipedia.org/wiki/SQL

[6] JavaScript, https://zh.wikipedia.org/wiki/JavaScript

[7] Python, https://www.python.org/

[8] Powershell, https://en.wikipedia.org/wiki/PowerShell

[9] User Interface, https://en.wikipedia.org/wiki/User_interface

[10] pg_dump & pgsql, https://www.postgresql.org/docs/

[11] sqlcmd, https://docs.microsoft.com/zh-tw/sql/tools/sqlcmd-utility

[12] node-cmd, https://www.npmjs.com/package/node-cmd