Builder Pattern

出自VFP Wiki

跳轉到: 導航, 搜尋

Builder Pattern

給予適當的建構資料,就可以得到我們所需要的物件.

詳細的說明可以參考後面所附的參考資料,那些資料就已經寫的很好了,我想我沒必要再畫蛇添足 ^_^.

最初製作這個 SQL Builder class 的時候是想說,我只要給予它一些欄位和條件,他就會自動幫我產生 SQL 敘述,而不必再去考量 SQL 敘述是否正確啊...等等的問題.剛好想到 Builder pattern 其實很適合,所以就實做看看.

目前這個 class 已經蠻齊全了,不過有些地方可以再加強.

  1. Join 部分
  2. 資料型態轉換,作的不好~應該可以再更精簡,更可靠.
  3. 傳入 select - sql 敘述,就能產生 delete-sql, update-sql.
  4. ...

參考資料:

範例程式: 

* 主程式
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 <= THIS.m_pairs.COUNT )
				lc_values=lc_values+","
			ENDIF
		NEXT
		lc_sql=lc_sql + "(" + lc_fields + ") values (" + lc_values + ")"
		RETURN lc_sql
	ENDPROC

	PROTECTED PROCEDURE getWhereSQL
		LOCAL lc_sql

		IF( THIS.m_where.COUNT >=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 <= THIS.m_pairs.COUNT )
				lc_sql=lc_sql+","
			ENDIF
		NEXT

		* handle where clause.
		lc_where=THIS.getWhereSQL()
		lc_sql=lc_sql+lc_where

		RETURN lc_sql
	ENDPROC
	
	PROCEDURE getKeyFieldList()
		LOCAL i
		LOCAL lc_fields
		IF( THIS.m_keys.COUNT >=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
* 類別定義結束
取自"http://wiki.sunyear.com.tw/index.php?title=Builder_Pattern"