1. Why
  2. version 2?
  3. API
  4. Source
  5. Remark

1. Why

目前存在著根據SQL所製作資料庫管理程式

Oracle、MySQL、PostgresSQL、MsSQL......

提供程式語言存取的 API 相異已不在話下

SQL語言部分,雖然SQL語言制定了一套標準存在

但並非所有資料庫管理程式會完全實作所有的標準

為了自身的效率與功能,還會製作其特有的語法、語意

雖說發生機率不高,但換資料庫並非不可能

 

現今有許多程式直接在程式語言當中使用SQL字串

兩種語言並用,將造成程式碼紊亂

有些SQL語句是很長的

看到一坨分不清頭尾的東西在那,任誰都覺得噁心

雖然可以靠字串斷句解決,那也不過使情況稍微輕減罷了

況且字串當中可能還夾雜著變數值的串接

排版就不會很容易,排版不容易就影響維護的容易度

另外SQL有自己的字元逃脫語法,PHP有自己的字元逃脫

兩層的逃脫語法混雜在一起,一定很難看懂

因此,多種程式語言混雜使用的狀況一定要盡量避免

 

這是筆者剛進目前待的地方幾個禮拜後的想法

很不幸的一年來那裡已經開啟多套系統的更新案 (嚴格說是移植案)

有使用到資料庫的系統沒有一套是不換資料庫的,若當時規劃沒有做好

程式碼每個地方都要檢查SQL是否殘存舊資料庫的語句和API

當然又不幸的有大部分都是這樣的狀況

既然這是個大量會使用到的東西,為何不將這部分獨立出一個專門模組?

就筆者所見台灣當然不乏有志之士,可是不是力有不逮就是意見被壓住

目前接觸的幾個都是網頁系統,一定會大量用到資料庫

而且還是有足夠不受管控空間的單人獨立建置作業,就藉機做出來用看看吧

用到現在大概一年多,版本更新至第二版,感覺不錯用

再說上個月又不小心把想法洩漏...喔不是不是,是分享給 O 冠資 X

也該是時候把一些東西寫成文章啦


 

2. version 2?

本模組專門處理 SQL語句組裝的工作

嗯?版本2?沒錯就是版本2

那版本1呢?那不是見得了人的東西

而且 PHP startup 建立時,已決定廢除版本 1

版本 1存在一些大問題,要從版本 0 開始說起

 

版本 0 (2013/11/24 ~ 2013/12/13)

這版本主要只是提供一些特殊符號 (主要是逃脫字元) 供程式組裝SQL字串使用

甚至連正式模組都算不上

 

版本 1 (2013/12/13 ~ 2014/03/13)

雖然 2011 - 2013 年間碰觸過 PHP 與 MySQL

還是之後才真正的去比較深入的認識這些東西

隨著認識的越多,才發現有很多其實可以作單獨處理的部分

以版本0的想法,將一些特殊字元 (例如逃脫字元、字串...等)

以與各SQL資料庫管理程式相近的字元定義為常數提供程式存取

然後再掃描程式所輸入的字串,將特殊字元轉換為各SQL所提供的字元

本次另外引入物件導向多型讓各種SQL轉換模組能再有所分隔

後來還加入特定函式的轉換

 

版本 2 (2014/08/11 ~ )

版本1的問題

版本 1 就當時做的系統而言算夠用

不過錯誤百出,首先字串掃描就不是件簡單的事

必須檢查到底搜尋到的字元是不是某個 token的開頭

是,這已經是在做個 parser (也就是說自己創造了一套SQL語言)

這方面的經驗筆者本身太少,雖然後來勉強會動了

但面對後來要加入的系統功能需求,這裡反而變成非常大的瓶頸

而且上層的程式也非常不好維護

版本 2的思維

當時系統的系統由於不同功能需要從不同的資料表取得不同的資料欄位

每次皆以「*」存取是非常不明智的

因此關於此資料表建立了一套以資料結構動態組裝字串的存取模組

包含了SELECT 中較為複雜的 JOIN TABLE定義

只是做法粗糙與客製化,還有利用SQL打印JSON的功能 (前輩提供的SQL使用心得)

並且最困難的「WHERE語句組裝」並沒有實作

使得WHERE部分得靠 sprintf 補足,但是這已足夠造成程式繁雜

 

「以資料結構組裝SQL語句」這樣的想法為什麼不能和跨SQL的模組結合?

一者,資料結構欄位種類格格分明,存取有簡易的規則可循與建立

二者,資料結構重用容易

只消變更、增加與刪除特定欄位,比字串更容易產生類似的結構的SQL字串

隨著 LogicOperationBuilder 的想法浮現 (目前版本1.1,擇期撰文說明)

WHERE語句的組裝也一蹴可幾

 

版本 2 進一步將

「語句組裝 (SqlQueryBuilder)」從「客戶端存取 (SqlClient)」分離出來

因為這兩部分性質不同,且已經夠分量能獨立為不同的模組

因此 SqlQueryBuilder 模組事實上為第一版

SqlQueryBuilder處理語句組裝的任務

SqlClient則負責API 的存取與功能統整強化

SqlClient內建 SqlQueryBuilder 提供一體化的使用介面

 

版本 2 捨棄以提供 SQL打印JSON的功能

因這樣的想法等於要把兩件事情一次做完

這會造成模組邏輯變得非常繁雜

現今許多其他用途的處理模組採用Multi-pass 的方式處理事情

是為了使一項任務可以再做階段分工,各階段專心處理自身任務

雖然犧牲一點運算時間,但是工作單純化會為整體任務增加不少彈性

打印JSON並非SQL語言組合的工作,如何建立字串串接函式才是

 

對於版本 1 欲針對各系統相同功能卻不同存取介面

本模組將這些部分全部定義為函式呼叫

(但並非一定要有相對應的函式輸入資料庫內,有些簡易的部分可在PHP 部分解決)

並提供不同的轉換函式,透過函式的呼叫與參數傳遞

解除各SQL專屬語法所產生的問題

 

本模組目前僅負責最常使用的 SELECT、INSERT、UPDATE 與 DELETE

有這四類語法已夠應付大部分的使用情形

其他例如動態新增與刪除資料表的功能語句,目前僅適合由函式來完成

 

由於SqlQueryBuilder 無法決定用於何種SQL資料庫

因此以 abstract class的形式呈現,並由各SQL專屬的語句組裝模組繼承擴充之


 

3. API

Constants

JOIN TYPE常數

JOIN_TYPE_FULL_OUTER
JOIN_TYPE_LEFT_OUTER
JOIN_TYPE_RIGHT_OUTER
JOIN_TYPE_INNER

SORT TYPE常數

SORT_ASCENDING
SORT_DESCENDING

INSERT TYPE常數

INSERT_VALUE_TYPE_DEFAULT
INSERT_VALUE_TYPE_PURE
INSERT_VALUE_TYPE_PAIR
INSERT_VALUE_TYPE_MULTIROW

Methods

public __constructor(array setting)

功能

    建構子。建議作為各類SQL內部設定之用

參數

    setting
        設定參數,成員依各類SQL而定,但須包含下列成員
            esc : array。表逃脫字元,包函 left 與 right兩成員
                    代表逃脫字元對的左字元與右字元
            dateFormat : array。表日期各欄位之轉換字串
                    欄位名稱依SQL不同而定。但至少須有
                    year、month、day、hour、minute、second
            cast type : 忘記用途了

public string sqlTable
     (mixed table [, bool isSubQuery = false])

功能

    取得SQL格式之資料表表示語句
    本 method 不建議直接使用。

    各類SQL可選擇重新定義 _getSqlTable

參數

    table : 資料表

    isSubQuery : 傳入之字串是否為一個 query
        若為true,則table必須符合「select」資料結構

回傳

    SQL格式之資料表表示形式,若為query,以 () 括弧之;
    否則以逃脫字元括弧之

public string sqlColumn(string columnName [, string table])

功能

    取得SQL格式之資料欄位表示語句
    本 method 不建議直接使用。

    各類SQL可選擇重新定義 _getSqlColumn

參數

    column : 資料欄位名稱

    table : 欄位所屬資料表名稱

回傳

    SQL格式之資料欄位表示型式。含逃脫字元

public string sqlValue(string value)

功能

    取得SQL格式之資料值表示語句
    本 method 不建議直接使用。

    各類SQL可選擇重新定義 _getSqlValue

參數

    value : 資料值

回傳

    SQL格式之資料值表示語句,引單引號括弧之
    並逃脫資料值內之單引號

public string sqlFunction(string name, arguments...)

功能

    取得SQL形式之函式表示語句。若使用之函式
    為模組內部定義之函式,則呼叫該函式取得特定SQL語句

    內部函式名稱為「_constructSqlFunc_<函式名稱>」

參數

    name : 函式名稱

    arguments... : 函式參數列表,每個參數需符合「_getSqlWord」格式
        若使用函式為內部函式,預設值依內部函式而定
        若非內部函式,預設值為 value

回傳

    SQL形式之函式表示語句,若為內部函式,則回傳函式轉換結果

public string sprintf(format, arguments...)

功能

    SqlBuilder專用sprintf。提供使用者自由輸入任何字串
    並將特殊值標示處轉換為特定SQL語句

    特殊值字串格式為
        %[參數索引值][多參數結合字串格式]<種類標記>

    參數索引值 : 表此處將使用第 n個參數,可達成參數重複使用之目的
        若未標示則使用流水號,並取得流水號位置之參數
    多參數結合字串格式 : 若參數為包含多項數值之參數,則以註記之字串結合之
        表示格式為 m<結合字串>m,若字串包含 m,需以逃脫字元逃脫之
    種類標記 : 標示該參數將轉換為何種類之SQL語句
        c、C : 表示需要轉換為資料欄位
        t、T : 表示需要轉換為資料表
        v、V : 表示需要轉換為資料值
        f、F : 表示需要轉換為函式
        % : 表百分比符號

參數

    format :  格式字串

    arguments... :  參數列表,每個參數需符合「_getSqlWord」格式
        若參數為多參數陣列,則陣列內元素需符合「_getSqlWord」格式

回傳

    轉換後之字串。

public string select(array source)

功能

    取得SQL SELECT 語句格式之字串

    各類SQL可選擇擴充 _constructSelect

參數

    source : 包含下列成員
        columns : 陣列
            表示需選用之資料欄位值,若元素鍵值為非數字字串
            表資料欄位之別名。每項元素值需符合「_getSqlWord」格式
        table : 字串或陣列 (optional)
            若為字串,表示使用之單一資料表名稱。若為陣列,
            表示將使用多重資料,陣列元素鍵值表資料表名稱
            陣列元素值必須為陣列,包含 type 與 key 兩成員
            type,表JOIN之型態;key則表 JOIN之比較值,
            需符合LogicOperationBuilder格式,
            每項運算域需符合「_getSqlWord」格式
            第一運算域預設為 column,其餘預設為 value
        filter : 陣列 (optional)
            WHERE語句組裝
            需符合LogicOperationBuilder格式。
            每項運算域需符合「_getSqlWord」格式
            第一運算域預設為 column,其餘預設為 value
        sort : 陣列 (optional)
            ORDER BY語句組裝。
            元素鍵值表資料欄位名稱;元素值可為SORT_TYPE常數
            或陣列,陣列須包含下列成員:
                type : 值須為SORT_TYPE常數,預設為SQL_ASCENDING
                table : 資料欄位所屬之資料表 (optional)
                isNumber : 若為 true,且元素鍵值為數字並大於0,則代表
                    使用序數為鍵值之欄位
        amount : Integer (optional)
            表需取得之資料列數量。轉換結果依各SQL而異

回傳

    轉換完成之SELECT字串

public string insert(array source)

功能

    取得SQL INSERT 語句格式之字串

    各類SQL可選擇擴充 _constructInsert

參數

    source : 需包含下列成員
        table : 字串
            資料表名稱
        valueType : INSERT_VALUE_TYPE常數
        values : 陣列
            所需依valueType而異
            INSERT_VALUE_TYPE_DEFAULT
                將所有資料欄位設為預設值。本成員欄位將被忽略
            INSERT_VALUE_TYPE_PURE
                將陣列元素依資料表欄位順序填入。每項元素值須
                符合「_getSqlWord」格式
            INSERT_VALUE_TYPE_PAIR
                陣列元素鍵值表資料欄位名稱,元素值表資料欄位之
                填入值,需符合「_getSqlWord」格式
            INSERT_VALUE_TYPE_MULTIROW
                需符合 select格式

回傳
    
轉換完成之 INSERT 語句

public string update(array source)

功能

    取得SQL UPDATE 語句格式之字串

    各類SQL可選擇擴充 _constructUpdate

參數

    source : 須包含下列成員
        table : 資料表名稱
        values : 陣列
            元素鍵值表資料欄位名稱,元素值表資料欄位填入值,
            需符合「_getSqlWord」格式
         filter : 陣列 (optional)
             WHERE語句組裝
             需符合LogicOperationBuilder格式。
             每項運算域需符合「_getSqlWord」格式
             第一運算域預設為 column,其餘預設為 value

回傳
    轉換完成之UPDATE語句

public string delete(array source)

功能

    取得SQL DELETE 語句格式之字串

    各類SQL可選擇擴充 _constructDelete

參數

    source : 須包含下列成員
        table : 表資料表名稱
        filter : 陣列 (optional)
            WHERE語句組裝
            需符合LogicOperationBuilder格式。
            每項運算域需符合「_getSqlWord」格式
            第一運算域預設為 column,其餘預設為 value

回傳

    轉換完成之DELETE語句

public string invoke(array functionName, arguments...)

功能

    取得SQL 函式呼叫語句格式之字串

    各類SQL可選擇擴充 _constructInvoke

參數

    同 sqlFunction

回傳

    轉換完成之函式呼叫語句

public string transaction(array options)

功能

    取得SQL TRANSACTION語句格式之字串

    各類SQL可選擇擴充 _constructInvoke

參數

    options : 依SQL種類而異,各SQL可實作適當之預設值

回傳

    轉換完成之TRANSACTION語句

public string savePoint(string name)

功能

    取得SQL SAVEPOINT語句格式之字串

    各類SQL可選擇擴充 _constructSavePoint

參數

    name : Save Point名稱

回傳

    轉換完成之SAVEPOINT語句

public string commit()

功能

    取得SQL COMMIT語句格式之字串

    各類SQL可選擇擴充 _constructCommit 

回傳

    轉換完成之COMMIT語句

public string rollback([string savePoint])

功能

    取得SQL ROLLBACK語句格式之字串

    各類SQL可選擇擴充 _constructRollback

參數

    savePoint : 回復之save point名稱

回傳

    轉換完成之ROLLBACK名稱

protected _getSqlWord
    (mixed data, [string default = "none",
    [array exclude = [], [array addition = []]]])

功能

    將特定資料格式轉換為指定種類之SQL字串

參數

    data : 陣列或字串
        若為字串表資料值;若為陣列須包含下列成員 : 
            type : 資料種類,可為以下值
                none : 不做任何處理
                table : 資料表,調用 sqlTable method
                column : 資料欄位,調用 sqlColumn method
                value : 資料值,調用 sqlValue method
                function : 函式,調用 sqlFunction method
                query : 調用 select method
            0 : 陣列或非陣列數值
                若為非陣列,表調用method之第一參數
                若為陣列,表調用method之參數列表

  default : type為指定時之預設資料處理型別
    exclude :  指示不予受理之型別
        若資料指定不受理之型別時,將以none處置
    addition : 指定額外之處理方式
        陣列元素鍵值為型別名稱,元素值可為字串或匿名
        函式,指示的函式名稱為模組所擁有,則呼叫模組之method
        否則以一般函式呼叫處理。
        原有型別不會被覆寫

回傳 

    轉換完成之SQL語句


4. Porting for Other Languages?

此模組的功能是否能夠移植到其他語言上提供使用?

如果這是一個夠動態的語言 (可動態產生任意資料)

移植是沒什麼問題

最怕像是 C/C++、Java這類

語言本身沒有提供這樣的特性,彈性就比較低一些

但並非無法達成,C++、Java都有提供Map類的Class

沒有簡易的表示式,用起來稍微麻煩,但也是沒辦法中的辦法

C 就比較麻煩,因為它幾乎缺了很多便利的模組

不過只要有時間打造,都還可以克服

最近還聽到人說,Map的缺點就是沒辦法使用memory copy

其實這根本是無所謂的事情

複製辦法上百種,其實也沒必要非得靠記憶體操作

電腦速度很快,只要包成一個API也沒辦法感覺出什麼啦

當然不同的實作方式各有各的優缺點,要做也是不是不行

Map也可以靠一些方式,將資料動態轉換成事先定義的資料結構內

例如:讀取資料結構欄位位移量的設定檔

這些檔案可以放在檔案、資料庫、或者以陣列寫死在程式裡面

總之,要做到都有辦法的


 

5. Source

點擊連結觀看


 

6. Remark

  1. 本模組為版本 1.1
  2. 本文僅作版本 1.1之錯誤更正與改善,功能擴充之更新版本將擇期撰文說明
  3. 2015/06/18  修正select tables邏輯錯誤,支援function與sub-query。
    因應LogicOperationBuilder使用interface設計,變更程式邏輯
創作者介紹
創作者 wylokgo101 的頭像
wylokgo101

豆棚瓜架雨如絲 - WYLOKGO101

wylokgo101 發表在 痞客邦 留言(0) 人氣()