Skip to content

Instantly share code, notes, and snippets.

@haskellcamargo
Created October 8, 2018 15:01
Show Gist options
  • Save haskellcamargo/f8392facbfb4ed13192ed427be444838 to your computer and use it in GitHub Desktop.
Save haskellcamargo/f8392facbfb4ed13192ed427be444838 to your computer and use it in GitHub Desktop.
#ifdef __HARBOUR__
#include 'hbclass.ch'
#else
#include 'protheus.ch'
#endif
#define CRLF Chr( 13 ) + Chr( 10 )
#define OP_SELECT 1
#define OP_ORDER 2
#define OP_FROM 3
#define SELECT_PREFIX 1
#define SELECT_VALUE 2
#define SELECT_AS 3
#define SELECT_FUNCTION 4
#define FROM_VALUE 1
#define FROM_AS 2
#define WHERE_LEFT 1
#define WHERE_OPERATOR 2
#define WHERE_RIGHT 3
#define ORDER_VALUE 1
#define ORDER_MODE 2
#define SELECT_FUNCTION_COUNT "COUNT"
#define SELECT_FUNCTION_SUM "SUM"
#define ORDER_MODE_ASC "ASC"
#define ORDER_MODE_DESC "DESC"
Class QueryBuilder
Data aFrom As Array
Data aOrderBy As Array
Data aSelect As Array
Data aWhere As Array
Data nLastOp As Numeric
Data cLastOp As String
Data aLastOp As Array
Data nCurrAlias
Data nNextAlias
Method New() Constructor
Method _As( cAs )
Method Asc()
Method Count( xExpr )
Method Desc()
Method From( cTable )
Method OrderBy( xOrderBy )
Method Select( xSelect )
Method Sum( xExpr )
Method GetSql()
Method NextAlias()
EndClass
Method New() Class QueryBuilder
::nNextAlias := 0
::aSelect := {}
::aOrderBy := {}
::aWhere := { { "D_E_L_E_T", "<>", "'*'" } }
Return Self
Method NextAlias() Class QueryBuilder
Local cAlias := "TB" + AllTrim( Str( ::nNextAlias ) )
::nNextAlias++
Return cAlias
/**
* :Select( "T9_CODBEM" ):_As( "NAME" )
* :From( "ST9" ):_As( "T9" )
*/
Method _As( cAs ) Class QueryBuilder
Do Case
Case ::nLastOp == OP_SELECT
::aLastOp[ SELECT_AS ] := cAs
Case ::nLastOp == OP_FROM
::aLastOp[ FROM_AS ] := cAs
EndCase
Return Self
/**
* :OrderBy( "C" ):Asc()
* :OrderBy( { "A", "B" } ):Asc()
*/
Method Asc() Class QueryBuilder
If ::nLastOp == OP_ORDER
::aLastOp[ ORDER_MODE ] := ORDER_MODE_ASC
Else
UserException( "ASC: can only be applied to an ORDER BY clause" )
EndIf
Return Self
/**
* :Select( "A" ):Count( "B" )
* :Count( "C")
* :Count()
*/
Method Count( xExpr ) Class QueryBuilder
Local aValue
Default xExpr := '1'
aValue := { Nil, xExpr, Nil, SELECT_FUNCTION_COUNT }
AAdd( ::aSelect, aValue )
::nLastOp := OP_SELECT
::aLastOp := aValue
Return Self
/**
* :OrderBy( "C" ):Desc()
* :OrderBy( { "A", "B" } ):Desc()
*/
Method Desc() Class QueryBuilder
If ::nLastOp == OP_ORDER
::aLastOp[ ORDER_MODE ] := ORDER_MODE_DESC
Else
UserException( "DESC: can only be applied to an ORDER BY clause" )
EndIf
Return Self
/**
* :From( "ST9" )
*/
Method From( cFrom ) Class QueryBuilder
Local nIndex
Local aFrom
If ValType( cFrom ) <> "C"
UserException( "FROM: expected string" )
EndIf
aFrom := { cFrom, Nil }
::aFrom := aFrom
::nLastOp := OP_FROM
::aLastOp := aFrom
Return Self
/**
* :OrderBy( { "A", "B" } )
* :OrderBy( "C" )
*/
Method OrderBy( xOrderBy ) Class QueryBuilder
Local nIndex
Local nLength
Local cType
Local aValue
cType := ValType( xOrderBy )
// Normalize string to array
If cType == "C"
xOrderBy := { xOrderBy }
ElseIf cType <> "A"
UserException( "ORDER BY: expected string or array" )
EndIf
nLength := Len( xOrderBy )
For nIndex := 1 To nLength
If ValType( xOrderBy[ nIndex ] ) <> "C"
UserException( "ORDER BY: expected string" )
EndIf
aValue := { xOrderBy[ nIndex ], Nil }
AAdd( ::aOrderBy, aValue )
If nIndex == nLength
::nLastOp := OP_ORDER
::aLastOp := aValue
EndIf
Next
Return Self
/**
* :Select( "NAME" )
* :Select( { "NAME", "AGE", "0" } )
*/
Method Select( xSelect ) Class QueryBuilder
Local nIndex
Local nLength
Local cType
Local aValue
cType := ValType( xSelect )
// Normalize string to array
If cType == "C"
xSelect := { xSelect }
ElseIf cType <> "A"
UserException( "SELECT: expected string or array" )
EndIf
// Append each field with specialized anonymous alias and tell from
nLength := Len( xSelect )
For nIndex := 1 To nLength
If ValType( xSelect[ nIndex ] ) <> "C"
UserException( "SELECT: expected string" )
EndIf
aValue := { Nil, xSelect[ nIndex ], Nil, Nil }
AAdd( ::aSelect, aValue )
If nIndex == nLength
::nLastOp := OP_SELECT
::aLastOp := aValue
EndIf
Next
Return Self
/**
* :Select( "A" ):Sum( "B" )
* :Sum( "C" )
*/
Method Sum( xExpr ) Class QueryBuilder
Local aValue
aValue := { Nil, xExpr, Nil, SELECT_FUNCTION_SUM }
AAdd( ::aSelect, aValue )
::nLastOp := OP_SELECT
::aLastOp := aValue
Return Self
Method GetSql() Class QueryBuilder
Local cSql
cSql := GenSelect( ::aSelect )
cSql += GenFrom( ::aFrom )
cSql += GenWhere( ::aWhere )
cSql += GenOrderBy( ::aOrderBy )
Return cSql
Static Function GenSelect( aSelect )
Local cSelect
Local nLength
Local nIndex
Local aField
Local cSeparator
cSelect := "SELECT "
// Create separator with given spacing
cSeparator := "," + CRLF + Space( Len( cSelect ) )
nLength := Len( aSelect )
// Empty select picks all fields
If nLength == 0
cSelect += "*" + CRLF
Else
For nIndex := 1 To nLength
aField := aSelect[ nIndex ]
// Prefix is given
If !Empty( aField[ SELECT_PREFIX ] )
cSelect += aField[ SELECT_PREFIX ] + "."
EndIf
// Check wrapping function and append field name
If !Empty( aField[ SELECT_FUNCTION ] )
cSelect += aField[ SELECT_FUNCTION ] + "("
cSelect += aField[ SELECT_VALUE ]
cSelect += ")"
Else
cSelect += aField[ SELECT_VALUE ]
EndIf
// Alias is given
If !Empty( aField[ SELECT_AS ] )
cSelect += " AS " + aField[ SELECT_AS ]
EndIf
// Append separator between fields
If nIndex < nLength
cSelect += cSeparator
EndIf
Next
cSelect += CRLF
EndIf
Return cSelect
Static Function GenFrom( aFrom )
Local cFrom
cFrom := "FROM "
cFrom += aFrom[ FROM_VALUE ]
If !Empty( aFrom[ FROM_AS ] )
cFrom += " " + aFrom[ FROM_AS ]
EndIf
cFrom += CRLF
Return cFrom
Static Function GenOrderBy( aOrderBy, nSpaces, lNewline )
Local cOrderBy
Local nIndex
Local nLength
nLength := Len( aOrderBy )
// Empty order generates no code
If nLength == 0
cOrderBy := ""
Else
cOrderBy := "ORDER BY "
For nIndex := 1 To nLength
// Append order field name
cOrderBy += aOrderBy[ nIndex, ORDER_VALUE ]
// Append order mode for field, if possible
If !Empty( aOrderBy[ nIndex, ORDER_MODE ] )
cOrderBy += " " + aOrderBy[ nIndex, ORDER_MODE ]
EndIf
If nIndex < nLength
cOrderBy += ", "
EndIf
Next
cOrderBy += CRLF
EndIf
Return cOrderBy
Static Function GenWhere( aWhere )
Local cWhere
Local nIndex
Local nLength
Local cSeparator
nLength := Len( aWhere )
If nLength == 0
cWhere := ""
Else
cWhere := "WHERE "
cSeparator := "," + Space( Len( cWhere ) ) + CRLF
For nIndex := 1 To nLength
cWhere += aWhere[ nIndex, WHERE_LEFT ]
cWhere += " " + aWhere[ nIndex, WHERE_OPERATOR ] + " "
cWhere += aWhere[ nIndex, WHERE_RIGHT ]
If nIndex < nLength
cOrderBy += ", "
EndIf
Next
cWhere += CRLF
EndIf
Return cWhere
#ifdef __HARBOUR__
Procedure Main()
#else
User Function Build()
#endif
Local oQuery
oQuery := QueryBuilder():New()
oQuery:Select( { "T9_FILIAL", "T9_CODBEM", "T9_CONTACU" } )
oQuery:Sum( "T9_CONTACU" )
oQuery:Count()
oQuery:Select( "T9_HORINI" ):_As( "START_HOUR" )
oQuery:Select( "T9_NOME" ):_As( "NAME" )
oQuery:From( "ST9" ):_As( "T9" )
oQuery:OrderBy( { "T9_CODBEM" } )
oQuery:OrderBy( "T9_NOME" ):Desc()
ConOut( oQuery:GetSql() )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment