Thinking more…

給予適當的建構資料,就可以得到我們所需要的物件.
詳細的說明可以參考後面所附的參考資料,那些資料就已經寫的很好了,我想我沒必要再畫蛇添足.
最初製作 SQL Builder class 的時候是想說,我只要給予它一些欄位和條件,他就會自動幫我產生 SQL 敘述,而不必再去考量 SQL 敘述是否正確啊…等等的問題.剛好想到 Builder pattern 其實很適合,所以就實做看看.
目前這個 class 已經蠻齊全了,不過有些地方可以再加強.
1. Join 部分
2. 資料型態轉換,作的不好~應該可以再更精簡,更可靠.
3. 傳入 select – sql 敘述,就能產生 delete-sql, update-sql.
4. …
參考資料:
http://www.dotspace.idv.tw/Patterns/Jdon_Builder.htm
http://140.109.17.201/Jyemii/patternscolumn/articles/DesignPatternPart(2).htm
Design Pattern 中譯本 – 葉秉哲譯
範例程式:

* 主程式
LOCAL lc_sql
LOCAL lo_builder
lo_builder=CREATEOBJECT(“csqlbuilder”)
lo_builder.ADDCOLUMN(“u_id”)
lo_builder.ADDCOLUMN(“u_name”)
lo_builder.setTable(“users”)
lo_builder.addWhere(“”, “u_id”, “=”, “drury”)
lo_builder.addOrderby( “u_id” )
lo_builder.addGroupby( “u_id” )
? “=====”
? “select sql::”
?? lo_builder.getSelectSQL()
lo_builder.reconf()
lo_builder.addPair(“u_id”, “ellery”)
lo_builder.addPair(“u_name”, “ellery”)
lo_builder.setTable(“users”)
lo_builder.addWhere(“”, “u_id”, “=”, “drury”)
? “=====”
? “insert sql::” + lo_builder.getInsertSQL()
? “update sql::” + lo_builder.getUpdateSQL()
? “delete sql::” + lo_builder.getDeleteSQL()
*
* Builder pattern 類別實作
* 此類別適用環境: VFP 8.0
* 類別定義開始
*
DEFINE CLASS CSQLBuilder AS CUSTOM
  ADD OBJECT PROTECTED m_pairs AS COLLECTION
  ADD OBJECT PROTECTED m_where AS COLLECTION
  ADD OBJECT PROTECTED m_groupby AS COLLECTION
  ADD OBJECT PROTECTED m_orderby AS COLLECTION
  ADD OBJECT PROTECTED m_keys as collection
  m_table = “” && the table
  m_targettype=”” && cursor, table
  m_target=”” && name
  * Init
  PROCEDURE INIT
  ENDPROC
  * Destroy
  PROCEDURE DESTROY
  ENDPROC
  * Reconfigure
  PROCEDURE reconf
    * MSDN said: pass -1 will clear all items
    THIS.m_pairs.REMOVE( -1 )
    THIS.m_where.REMOVE( -1 )
    THIS.m_groupby.REMOVE( -1 )
    THIS.m_orderby.REMOVE( -1 )
    THIS.m_table = “”
    THIS.m_targettype=””
    THIS.m_target=””
  ENDPROC
  * addColumns
  PROCEDURE ADDCOLUMN( c_field AS STRING )
    THIS.m_pairs.ADD( .NULL., c_field )
  ENDPROC
  PROCEDURE addWhere( c_logical AS STRING, c_field AS STRING, c_operator AS STRING, o_value AS OBJECT )
    THIS.m_where.ADD( o_value, c_logical + c_field + c_operator )
  ENDPROC
  PROCEDURE addPair( c_field AS STRING, o_value AS OBJECT )
    THIS.m_pairs.ADD( o_value, c_field )
  ENDPROC
  PROCEDURE addGroupby( c_field AS STRING )
    THIS.m_groupby.ADD( .NULL., c_field )
  ENDPROC
  PROCEDURE addOrderby( c_field AS STRING )
    THIS.m_orderby.ADD( .NULL., c_field )
  ENDPROC
  PROCEDURE addKey( c_field as String )
    this.m_keys.add( .null., c_field )
  ENDPROC
  PROCEDURE addJoin
    * todo: this is the most hard part.
  ENDPROC
  PROCEDURE setTable( c_table AS STRING )
    THIS.m_table=c_table
  ENDPROC
  PROCEDURE setTarget( c_type AS STRING, c_target AS STRING )
    THIS.m_targettype=c_type
    THIS.m_target=c_string
  ENDPROC
  PROCEDURE getInsertSQL
    LOCAL i
    LOCAL lc_sql
    LOCAL lc_fields, lc_values, lc_type
    lc_fields=””
    lc_values=””
    lc_sql=”insert into ” + THIS.m_table + ” ”
    FOR i=1 TO THIS.m_pairs.COUNT
      lc_fields=lc_fields+THIS.m_pairs.GETKEY(i)
      IF( i+1 <= THIS.m_pairs.COUNT )
        lc_fields=lc_fields+","
      ENDIF
      lc_type=VARTYPE( THIS.m_pairs.ITEM(i) )
      DO CASE
        CASE lc_type="C"
          lc_values=lc_values+ "'" + THIS.m_pairs.ITEM(i)+ "'"
        CASE INLIST( lc_type, "N", "Y" )
          lc_values=lc_values + ALLTRIM( STR(THIS.m_pairs.ITEM(i) ) )
        CASE lc_type="D"
          lc_values=lc_values + "{^" + DTOC( THIS.m_pairs.ITEM(i) ) + "}"
        CASE lc_type="L"
          lc_values=lc_values + IIF( THIS.m_pairs.ITEM(i), ".T.", ".F." )
        CASE lc_type="X"
          lc_values=lc_values + ".null."
      ENDCASE
      IF( i+1 =1 )
      lc_sql=” where ”
    ELSE
      lc_sql=””
    ENDIF
    FOR i=1 TO THIS.m_where.COUNT
      lc_type=VARTYPE( THIS.m_where.ITEM(i) )
      DO CASE
        CASE lc_type=”C”
          lc_value= “‘” + THIS.m_where.ITEM(i)+ “‘”
        CASE INLIST( lc_type, “N”, “Y” )
          lc_value= ALLTRIM( STR(THIS.m_where.ITEM(i) ) )
        CASE lc_type=”D”
          lc_value=”{” + DTOC( THIS.m_where.ITEM(i) ) + “}”
        CASE lc_type=”L”
          lc_value=IIF( THIS.m_where.ITEM(i), “.T.”, “.F.” )
        CASE lc_type=”X”
          lc_value=”.null.”
      ENDCASE
      lc_sql=lc_sql + THIS.m_where.GETKEY(i) + lc_value
      IF( i+1 <= THIS.m_where.COUNT )
        lc_sql=lc_sql+","
      ENDIF
    NEXT
    RETURN lc_sql
  ENDPROC
  PROCEDURE getOnlyKey( o_collection AS COLLECTION )
    LOCAL i
    LOCAL lc_sql
    lc_sql=""
    FOR i=1 TO o_collection.COUNT
      lc_sql=lc_sql+o_collection.GETKEY(i)
      IF( i+1 <= o_collection.COUNT )
        lc_sql=lc_sql+","
      ENDIF
    NEXT
    RETURN lc_sql
  ENDPROC
  PROCEDURE getOrderBySQL
    LOCAL lc_sql
    lc_sql=THIS.getOnlyKey( THIS.m_orderby )
    IF( EMPTY(lc_sql) )
      RETURN lc_sql
    ELSE
      RETURN " order by " + lc_sql
    ENDIF
  ENDPROC
  PROCEDURE getGroupbySQL
    LOCAL lc_sql
    lc_sql=THIS.getOnlyKey( THIS.m_groupby )
    IF( EMPTY(lc_sql) )
      RETURN lc_sql
    ELSE
      RETURN " group by " + lc_sql
    ENDIF
  ENDPROC
  PROCEDURE getSelectSQL
    LOCAL i
    LOCAL lc_sql, lc_where, lc_orderby, lc_groupby
    lc_sql="select "
    FOR i=1 TO THIS.m_pairs.COUNT
      lc_sql=lc_sql+THIS.m_pairs.GETKEY(i)
      IF( i+1 <= THIS.m_pairs.COUNT )
        lc_sql=lc_sql+","
      ENDIF
    NEXT
    lc_sql=lc_sql + " from " + THIS.m_table
    lc_where=THIS.getWhereSQL()
    lc_sql=lc_sql + lc_where
    lc_orderby=THIS.getOrderBySQL()
    lc_sql=lc_sql+lc_orderby
    lc_groupby=THIS.getGroupbySQL()
    lc_sql=lc_sql+lc_groupby
    RETURN lc_sql
  ENDPROC
  PROCEDURE getDeleteSQL
    LOCAL lc_sql
    LOCAL i
    LOCAL lc_value, lc_type, lc_where
    lc_sql="delete from " + THIS.m_table
    lc_where=THIS.getWhereSQL()
    lc_sql=lc_sql+lc_where
    RETURN lc_sql
  ENDPROC
  PROCEDURE getUpdateSQL
    LOCAL i
    LOCAL lc_sql
    LOCAL lc_fields, lc_values, lc_type, lc_where
    lc_fields=""
    lc_values=""
    lc_sql="update " + THIS.m_table + " set "
    FOR i=1 TO THIS.m_pairs.COUNT
      lc_sql=lc_sql+THIS.m_pairs.GETKEY(i)+"="
      lc_type=VARTYPE( THIS.m_pairs.ITEM(i) )
      DO CASE
        CASE lc_type="C"
          lc_sql=lc_sql + "'" + THIS.m_pairs.ITEM(i) + "'"
        CASE INLIST( lc_type, "N", "Y" )
          lc_sql=lc_sql + ALLTRIM( STR(THIS.m_pairs.ITEM(i) ) )
        CASE lc_type="D"
          lc_sql=lc_sql + "{^" + DTOC( THIS.m_pairs.ITEM(i) ) + "}"
        CASE lc_type="L"
          lc_values=lc_sql + IIF( THIS.m_pairs.ITEM(i), ".T.", ".F." )
        CASE lc_type="X"
          lc_values=lc_sql + ".null."
      ENDCASE
      IF( i+1 =1 )
      lc_fields=””
    ELSE
      RETURN “”
    ENDIF
    FOR i=1 TO THIS.m_keys.COUNT
      lc_fields=lc_fields+this.m_keys.getKey(i)
      IF( i+1 <= THIS.m_keys.COUNT )
        lc_fields=lc_fields+","
      ENDIF
    NEXT
    RETURN lc_fields
  ENDPROC
  PROCEDURE getTable()
    RETURN THIS.m_table
  ENDPROC
  PROCEDURE getUpdatableFieldList()
    LOCAL i, lc_fields
    lc_fields=""
    FOR i=1 TO THIS.m_pairs.COUNT
      lc_fields=lc_fields+THIS.m_pairs.GETKEY(i)
      IF( i+1 <= THIS.m_pairs.COUNT )
        lc_fields=lc_fields+","
      ENDIF
    NEXT
    RETURN lc_fields
  ENDPROC
  PROCEDURE getUpdateNameList()
    LOCAL i, lc_fields
    lc_fields=""
    FOR i=1 TO THIS.m_pairs.COUNT
      lc_fields=lc_fields+THIS.m_pairs.GETKEY(i)+" "
      lc_fields=lc_fields+this.m_table+"."+this.m_pairs.getkey(i)
      IF( i+1 <= THIS.m_pairs.COUNT )
        lc_fields=lc_fields+","
      ENDIF
    NEXT
    RETURN lc_fields
  ENDPROC
ENDDEFINE
* 類別定義結束