#lang scheme/base

(require mzlib/etc
         scheme/unit)

(require "../snooze.ss"
         "../test-base.ss"
         "../test-data.ss"
         "../generic/sql-query-helpers-sig.ss"
         "../generic/sql-query-sig.ss"
         "sql-data-unit.ss"
         "sql-name-unit.ss"
         "sql-query-unit.ss")

; Unit invocations -----------------------------

(define-compound-unit/infer query@
  (import)
  (export sql-query^ sql-query-helpers^)
  (link sql-name@ sql-data@ sql-query@))

(define-values/invoke-unit/infer query@)

; Helpers --------------------------------------

; (a b c ... output-port -> void) a b c ... -> string
;
; Calls the supplied SQL display procedure, captures its output
; in an output-string, and returns it as a string.
(define (capture-sql proc . args)
  (define out (open-output-string))
  (apply proc (append args (list out)))
  (get-output-string out))

; any (listof column) -> string
(define (distinct-sql val imported)   (capture-sql display-distinct   val imported))
(define (what-sql val imported)       (capture-sql display-what       val imported))
(define (from-sql val imported)       (capture-sql display-from       val imported))
(define (group-sql val imported)      (capture-sql display-group      val imported))
(define (order-sql val imported)      (capture-sql display-order      val imported))
(define (expression-sql val imported) (capture-sql display-expression val imported))

; entity-alias attribute-alias ...
(define-alias p1 person)
(define-alias p2 person)
(define-alias p3 person)

; expression-alias
(define count-star (sql:alias 'count-star (sql:count*)))
(define count-p1 (sql:alias 'count-p1 (sql:count* p1)))
(define count-p1-id (sql:alias 'count-p1-id (sql:count p1-id)))
(define count-p2-id (sql:alias 'count-p2-id (sql:count p2-id)))
(define sum-ids (sql:alias 'sum-ids (sql:+ p1-id p2-id)))

; Tests ----------------------------------------

; test-suite
(define sql-query-unit-tests
  (test-suite "sql-query-unit.ss"
    
    (test-case "display-distinct"
      (check-equal? (distinct-sql (list) (list)) 
                    "DISTINCT "
                    "no expressions")
      (check-equal? (distinct-sql (list (sql:= p1-id 123)) (list p1-id))
                    #<<ENDSQL
DISTINCT ON ([p1-id] = 123) 
ENDSQL
                    "single expression")
      (check-equal? (distinct-sql (list (sql:= p1-id 123) (sql:= p1-revision 123)) (list p1-id))
                    #<<ENDSQL
DISTINCT ON ([p1-id] = 123), ([p1].[revision] = 123) 
ENDSQL
                    "multiple expressions"))
    
    (test-case "display-what"
      (check-equal? (what-sql (list p1-id p1-revision p1-name) (list p1-name))
                    #<<ENDSQL
[p1].[id] AS [p1-id], [p1].[revision] AS [p1-revision], [p1-name]
ENDSQL
                    "attribute aliases")
      
      (check-equal? (what-sql (list count-star count-p1 count-p1-id count-p2-id sum-ids) (list count-p2-id))
                    #<<ENDSQL
COUNT(*) AS [count-star], COUNT([p1].*) AS [count-p1], COUNT([p1].[id]) AS [count-p1-id], [count-p2-id], ([p1].[id] + [p2].[id]) AS [sum-ids]
ENDSQL
                    "expression aliases"))
    
    (test-case "display-from"
      (check-equal? (from-sql p1 null)
                    #<<ENDSQL
[Person] AS [p1]
ENDSQL
                    "entity")
      
      (check-equal? (from-sql (sql:alias 'subq (sql:select #:from p1)) null)
                    #<<ENDSQL
(SELECT [p1].[id] AS [p1-id], [p1].[revision] AS [p1-revision], [p1].[name] AS [p1-name] FROM [Person] AS [p1]) AS [subq]
ENDSQL
                    "subquery")
      
      (check-equal? (from-sql (sql:inner p1 (sql:alias 'subq (sql:select #:from p2)) (sql:= p1-id p2-id))
                              (list p2-id p2-revision p2-name))
                    #<<ENDSQL
[Person] AS [p1] INNER JOIN (SELECT [p2].[id] AS [p2-id], [p2].[revision] AS [p2-revision], [p2].[name] AS [p2-name] FROM [Person] AS [p2]) AS [subq] ON ([p1].[id] = [p2-id])
ENDSQL
                    "inner join")
      
      (check-equal? (from-sql (sql:outer (sql:outer p1 p2) p3) null)
                    #<<ENDSQL
[Person] AS [p1] CROSS JOIN [Person] AS [p2] CROSS JOIN [Person] AS [p3]
ENDSQL
                    "unparenthesised nested join")
      
      (check-equal? (from-sql (sql:outer p1 (sql:outer p2 p3)) null)
                    #<<ENDSQL
((SELECT [p1].[id] AS [p1-id], [p1].[revision] AS [p1-revision], [p1].[name] AS [p1-name] FROM [Person] AS [p1]) CROSS JOIN ((SELECT [p2].[id] AS [p2-id], [p2].[revision] AS [p2-revision], [p2].[name] AS [p2-name] FROM [Person] AS [p2]) CROSS JOIN (SELECT [p3].[id] AS [p3-id], [p3].[revision] AS [p3-revision], [p3].[name] AS [p3-name] FROM [Person] AS [p3])))
ENDSQL
                    "parenthesised nested join"))
    
    (test-case "display-group"
      (check-equal? (group-sql (list p1-id p1-revision p1-name) (list p1-id p1-revision p1-name))
                    #<<ENDSQL
[p1-id], [p1-revision], [p1-name]
ENDSQL
                    "attribute aliases")
      
      (check-equal? (group-sql (list count-p1-id count-p2-id sum-ids) (list count-p1-id sum-ids count-p2-id))
                    #<<ENDSQL
[count-p1-id], [count-p2-id], [sum-ids]
ENDSQL
                    "expression aliases"))
    
    (test-case "display-order"
      (check-equal? (order-sql (list (sql:asc p1-id) (sql:desc p1-revision) (sql:order p1-name 'asc)) (list p1-name))
                    #<<ENDSQL
[p1].[id] ASC, [p1].[revision] DESC, [p1-name] ASC
ENDSQL
                    "attribute aliases")
      
      (check-equal? (order-sql (list (sql:asc count-p1-id) (sql:desc count-p2-id) (sql:order sum-ids 'asc)) (list count-p1-id count-p2-id sum-ids))
                    #<<ENDSQL
[count-p1-id] ASC, [count-p2-id] DESC, [sum-ids] ASC
ENDSQL
                    "expression aliases")
      
      (check-equal? (order-sql (list (sql:asc (sql:+ p1-id p2-id))) null)
                    #<<ENDSQL
([p1].[id] + [p2].[id]) ASC
ENDSQL
                    "expressions"))
    
    (test-case "display-expression"
      (check-equal? (expression-sql (sql:and (sql:= p1-id 123) 
                                             (sql:= (sql:string-append p1-name " of Loxley") "Robin of Loxley"))
                                    (list p1-name))
                    "(([p1].[id] = 123) AND (([p1-name] || ' of Loxley') = 'Robin of Loxley'))"
                    "nested expressions")
      (check-equal? (expression-sql (sql:in p1-id (sql:select #:what p1-id #:from p1)) null)
                    "([p1].[id] IN (SELECT [p1].[id] AS [p1-id] FROM [Person] AS [p1]))"
                    "sql:in")
      (check-equal? (expression-sql (sql:regexp-replace     "a" "b" "c") null)    "(regexp_replace('a', 'b', 'c'))"       "sql:regexp-replace")
      (check-equal? (expression-sql (sql:regexp-replace-ci  "a" "b" "c") null)    "(regexp_replace('a', 'b', 'c', 'i'))"  "sql:regexp-replace-ci")
      (check-equal? (expression-sql (sql:regexp-replace*    "a" "b" "c") null)    "(regexp_replace('a', 'b', 'c', 'g'))"  "sql:regexp-replace*")
      (check-equal? (expression-sql (sql:regexp-replace*-ci "a" "b" "c") null)    "(regexp_replace('a', 'b', 'c', 'gi'))" "sql:regexp-replace*-ci")
      (check-equal? (expression-sql (sql:cond [#t "a"] [#f "b"]) null)            "(CASE WHEN 1 THEN 'a' WHEN 0 THEN 'b' ELSE NULL END)" "sql:cond, no else")
      (check-equal? (expression-sql (sql:cond [#t "a"] [#f "b"] [else "c"]) null) "(CASE WHEN 1 THEN 'a' WHEN 0 THEN 'b' ELSE 'c' END)"  "sql:cond, with else"))
    
    (test-case "query-sql"
      (begin-with-definitions
        
        (define-alias a person)
        (define-alias b pet)
        (define-alias expr (sql:count* b))
        
        (define-alias expr2 
          (sql:+ p1-id p2-id p3-id))
        
        (define query1
          (sql:select #:what   (list a b)
                      #:from   (sql:inner a b (sql:= a-id b-owner-id))
                      #:where  (sql:= a-name "Jon Arbuckle")
                      #:order  (list (sql:asc a-name)
                                     (sql:asc b-name))
                      #:limit  10
                      #:offset 20))
        
        (define query2
          (sql:select #:what   (list a b)
                      #:from   (sql:inner (sql:alias 'subq (sql:select #:from a)) b (sql:= a-id b-owner-id))
                      #:where  (sql:= a-name b-name)
                      #:order  (list (sql:asc a-name)
                                     (sql:asc b-name))))
        
        (define query3
          (sql:select #:what  (list a expr)
                      #:from  (sql:inner a b (sql:= a-id b-owner-id))
                      #:group (list a)))
        
        ; Test the special aliasing behaviour with right-nested joins:
        (define query4
          (sql:select #:what (list p1-id p2-id p3-id expr2)
                      #:from (sql:outer p1 (sql:outer p2 p3))))
        
        (define sql1
          #<<ENDSQL
SELECT [a].[id] AS [a-id], [a].[revision] AS [a-revision], [a].[name] AS [a-name], [b].[id] AS [b-id], [b].[revision] AS [b-revision], [b].[ownerID] AS [b-owner-id], [b].[name] AS [b-name] FROM [Person] AS [a] INNER JOIN [Pet] AS [b] ON ([a].[id] = [b].[ownerID]) WHERE ([a].[name] = 'Jon Arbuckle') ORDER BY [a-name] ASC, [b-name] ASC LIMIT 10 OFFSET 20
ENDSQL
          )
        
        (define sql2
          #<<ENDSQL
SELECT [a-id], [a-revision], [a-name], [b].[id] AS [b-id], [b].[revision] AS [b-revision], [b].[ownerID] AS [b-owner-id], [b].[name] AS [b-name] FROM (SELECT [a].[id] AS [a-id], [a].[revision] AS [a-revision], [a].[name] AS [a-name] FROM [Person] AS [a]) AS [subq] INNER JOIN [Pet] AS [b] ON ([a-id] = [b].[ownerID]) WHERE ([a-name] = [b].[name]) ORDER BY [a-name] ASC, [b-name] ASC
ENDSQL
          )
        
        (define sql3
          #<<ENDSQL
SELECT [a].[id] AS [a-id], [a].[revision] AS [a-revision], [a].[name] AS [a-name], COUNT([b].*) AS [expr] FROM [Person] AS [a] INNER JOIN [Pet] AS [b] ON ([a].[id] = [b].[ownerID]) GROUP BY [a-id], [a-revision], [a-name]
ENDSQL
          )
        
        ; Test the special aliasing behaviour with right-nested joins:
        (define sql4
          #<<ENDSQL
SELECT [p1-id], [p2-id], [p3-id], ([p1-id] + [p2-id] + [p3-id]) AS [expr2] FROM ((SELECT [p1].[id] AS [p1-id], [p1].[revision] AS [p1-revision], [p1].[name] AS [p1-name] FROM [Person] AS [p1]) CROSS JOIN ((SELECT [p2].[id] AS [p2-id], [p2].[revision] AS [p2-revision], [p2].[name] AS [p2-name] FROM [Person] AS [p2]) CROSS JOIN (SELECT [p3].[id] AS [p3-id], [p3].[revision] AS [p3-revision], [p3].[name] AS [p3-name] FROM [Person] AS [p3])))
ENDSQL
          )
        
        (check-equal? (capture-sql display-query query1) sql1 "display-query-sql of query1")
        (check-equal? (query-sql query1) (string-append sql1 ";") "query-sql of query1")
        (check-equal? (capture-sql display-query query2) sql2 "display-query-sql of query2")
        (check-equal? (query-sql query2) (string-append sql2 ";") "query-sql of query2")
        (check-equal? (capture-sql display-query query3) sql3 "display-query-sql of query3")
        (check-equal? (query-sql query3) (string-append sql3 ";") "query-sql of query3")
        (check-equal? (capture-sql display-query query4) sql4 "display-query-sql of query4")
        (check-equal? (query-sql query4) (string-append sql4 ";") "query-sql of query4")))))

; Provide statements -----------------------------

(provide sql-query-unit-tests)