five/_FiveSql2/src/TSqlSort.prg

/*
 * TSqlSort.prg — ORDER BY sorting and DISTINCT elimination
 *
 * FiveSql — SQL Engine for Harbour DBF/NTX
 *
 * Copyright (c) 2025 Charles KWON (Charles KWON OhJun)
 * Email: charleskwonohjun@gmail.com
 *
 * All rights reserved.
 */

#include "hbclass.ch"
#include "FiveSqlDef.ch"

/* Module-level state for the sort comparator callback */
STATIC s_aOBCols  := {}
STATIC s_aOBNames := {}

CLASS TSqlSort

   METHOD New() CONSTRUCTOR
   METHOD OrderBy( aRows, aFN, aOB, aTables, aParams )
   METHOD Distinct( aRows )
   METHOD RowKey( aR )

ENDCLASS


METHOD New() CLASS TSqlSort
RETURN SELF


METHOD OrderBy( aRows, aFN, aOB, aTables, aParams ) CLASS TSqlSort

   LOCAL i, nCol, cNulls

   IF Len( aRows ) < 2 .OR. Len( aOB ) == 0
      RETURN aRows
   ENDIF

   /* Pre-resolve column indexes. Third element carries the explicit
    * NULLS FIRST/LAST spec parsed by TSqlParser2:ParseOrderBy —
    * empty string means "use default (NIL as largest)". */
   s_aOBCols := {}
   s_aOBNames := aFN
   FOR i := 1 TO Len( aOB )
      nCol := SqlFindColIdx( aOB[ i ][ 1 ], aFN )
      IF nCol == 0
         nCol := SqlFindColIdx2( SqlExprName( aOB[ i ][ 1 ] ), aFN )
      ENDIF
      cNulls := iif( Len( aOB[ i ] ) >= 3, Upper( aOB[ i ][ 3 ] ), "" )
      AAdd( s_aOBCols, { nCol, aOB[ i ][ 2 ], cNulls } )
   NEXT

   ASort( aRows,,, {|a, b| SqlRowCompare( a, b ) < 0 } )

RETURN aRows


METHOD Distinct( aRows ) CLASS TSqlSort

   /* Go RTL SqlDistinct: single-pass dedup via Go map[string]bool.
    * Key construction matches prior PRG ::RowKey byte-for-byte (same
    * SqlValToStr mapping + '|' separator), so the output is identical
    * to the old PRG loop — just ~100x faster on large result sets. */
RETURN SqlDistinct( aRows )


METHOD RowKey( aR ) CLASS TSqlSort

   LOCAL c := "", i

   FOR i := 1 TO Len( aR )
      c += SqlValToStr( aR[ i ] ) + "|"
   NEXT

RETURN c


/* Find column index from expression in field name array */
FUNCTION SqlFindColIdx( xExpr, aFN )

   LOCAL cN, i

   IF xExpr != NIL .AND. xExpr[ 1 ] == ND_COL
      cN := Upper( xExpr[ 2 ] )
      IF "." $ cN
         cN := SubStr( cN, At( ".", cN ) + 1 )
      ENDIF
      FOR i := 1 TO Len( aFN )
         IF Upper( aFN[ i ] ) == cN
            RETURN i
         ENDIF
      NEXT
   ENDIF

RETURN 0

/* Find column index by name */
FUNCTION SqlFindColIdx2( cN, aFN )

   LOCAL i

   cN := Upper( cN )
   FOR i := 1 TO Len( aFN )
      IF Upper( aFN[ i ] ) == cN
         RETURN i
      ENDIF
   NEXT

RETURN 0


/* Multi-key row comparator for ASort */
FUNCTION SqlRowCompare( aRowA, aRowB )

   LOCAL i, nCol, cDir, cNulls, lNullsFirst, xA, xB, nCmp

   FOR i := 1 TO Len( s_aOBCols )
      nCol := s_aOBCols[ i ][ 1 ]
      cDir := s_aOBCols[ i ][ 2 ]
      cNulls := iif( Len( s_aOBCols[ i ] ) >= 3, s_aOBCols[ i ][ 3 ], "" )

      IF nCol <= 0 .OR. nCol > Len( aRowA ) .OR. nCol > Len( aRowB )
         LOOP
      ENDIF

      xA := aRowA[ nCol ]
      xB := aRowB[ nCol ]

      /* NULL ordering — default: NIL is largest (NULLs last in ASC,
       * NULLs first in DESC). Explicit NULLS FIRST/LAST (SQL:2003)
       * from the parser overrides direction. */
      IF xA == NIL .AND. xB == NIL
         LOOP
      ENDIF
      IF xA == NIL .OR. xB == NIL
         DO CASE
         CASE cNulls == "FIRST" ; lNullsFirst := .T.
         CASE cNulls == "LAST"  ; lNullsFirst := .F.
         OTHERWISE              ; lNullsFirst := ( cDir == "DESC" )
         ENDCASE
         IF xA == NIL
            RETURN iif( lNullsFirst, -1, 1 )
         ENDIF
         RETURN iif( lNullsFirst, 1, -1 )
      ENDIF

      nCmp := 0
      IF ValType( xA ) == ValType( xB )
         IF xA < xB
            nCmp := -1
         ELSEIF xA > xB
            nCmp := 1
         ENDIF
      ELSEIF ValType( xA ) == "N" .AND. ValType( xB ) == "C"
         nCmp := iif( xA < Val( AllTrim( xB ) ), -1, iif( xA > Val( AllTrim( xB ) ), 1, 0 ) )
      ELSEIF ValType( xA ) == "C" .AND. ValType( xB ) == "N"
         nCmp := iif( Val( AllTrim( xA ) ) < xB, -1, iif( Val( AllTrim( xA ) ) > xB, 1, 0 ) )
      ENDIF

      IF nCmp != 0
         IF cDir == "DESC"
            RETURN -nCmp
         ENDIF
         RETURN nCmp
      ENDIF
   NEXT

RETURN 0