| 1 |
;;; |
| 2 |
;;; text.sql - SQL parsing |
| 3 |
;;; |
| 4 |
;;; Copyright (c) 2005-2024 Shiro Kawai <[email protected]> |
| 5 |
;;; |
| 6 |
;;; Redistribution and use in source and binary forms, with or without |
| 7 |
;;; modification, are permitted provided that the following conditions |
| 8 |
;;; are met: |
| 9 |
;;; |
| 10 |
;;; 1. Redistributions of source code must retain the above copyright |
| 11 |
;;; notice, this list of conditions and the following disclaimer. |
| 12 |
;;; |
| 13 |
;;; 2. Redistributions in binary form must reproduce the above copyright |
| 14 |
;;; notice, this list of conditions and the following disclaimer in the |
| 15 |
;;; documentation and/or other materials provided with the distribution. |
| 16 |
;;; |
| 17 |
;;; 3. Neither the name of the authors nor the names of its contributors |
| 18 |
;;; may be used to endorse or promote products derived from this |
| 19 |
;;; software without specific prior written permission. |
| 20 |
;;; |
| 21 |
;;; THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
| 22 |
;;; "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
| 23 |
;;; LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR |
| 24 |
;;; A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT |
| 25 |
;;; OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, |
| 26 |
;;; SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED |
| 27 |
;;; TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR |
| 28 |
;;; PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF |
| 29 |
;;; LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING |
| 30 |
;;; NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS |
| 31 |
;;; SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
| 32 |
;;; |
| 33 |
|
| 34 |
;; *EXPERIMENTAL* |
| 35 |
;; This module will define an S-expr notation of SQL and conversion |
| 36 |
;; routines from/to the standard SQL syntax. |
| 37 |
;; For the time being, we only use tokenizer which is used by dbi/dbd |
| 38 |
;; modules. |
| 39 |
|
| 40 |
(define-module text.sql |
| 41 |
(use srfi.13) |
| 42 |
(use util.match) |
| 43 |
(export <sql-parse-error> |
| 44 |
sql-tokenize |
| 45 |
;sql->sxql |
| 46 |
)) |
| 47 |
(select-module text.sql) |
| 48 |
|
| 49 |
;;;----------------------------------------------------------------- |
| 50 |
;;; Conditions |
| 51 |
;;; |
| 52 |
|
| 53 |
(define-condition-type <sql-parse-error> <error> #f |
| 54 |
(sql-string)) ;; original SQL string |
| 55 |
|
| 56 |
;;;----------------------------------------------------------------- |
| 57 |
;;; Parser |
| 58 |
;;; |
| 59 |
|
| 60 |
;(define (sql->sxql sql-string) |
| 61 |
|
| 62 |
; (define (parse-stmt tokens) |
| 63 |
; (match tokens |
| 64 |
; (() (values '() '())) |
| 65 |
; (('select . rest) (parse-select (cdr tokens))) |
| 66 |
; (('insert . rest) (parse-insert (cdr tokens))) |
| 67 |
; (('delete . rest) (parse-delete (cdr tokens))) |
| 68 |
; (('create . rest) (parse-create (cdr tokens))) |
| 69 |
; (else |
| 70 |
; (err "Invalid or unsupported statement type '~a' in ~s" |
| 71 |
; (car tokens) sql-string)))) |
| 72 |
|
| 73 |
; (define (parse-select tokens) |
| 74 |
; (parse-select-columns tokens '())) |
| 75 |
|
| 76 |
; (define (parse-select-columns tokens columns) |
| 77 |
; (cond |
| 78 |
; ((or (null? tokens) (eqv? (car tokens) #\;)) |
| 79 |
; (err "Unterminated SELECT statement in ~s" sql-string)) |
| 80 |
; ((eq? (car tokens) 'from) |
| 81 |
; (parse-select-from (cdr tokens) (reverse! columns))) |
| 82 |
; (else |
| 83 |
; (parse-select-column tokens columns)))) |
| 84 |
|
| 85 |
; (define (parse-select-column tokens columns) |
| 86 |
; (cond |
| 87 |
; ((eq? (car tokens) |
| 88 |
; )))) |
| 89 |
|
| 90 |
; ;; error |
| 91 |
; (define (err fmt . args) |
| 92 |
; (apply errorf <sql-parse-error> :sql-string sql-string fmt args)) |
| 93 |
|
| 94 |
; ;; The body of sql->sxql |
| 95 |
; (parse-stmt (sql-tokenize sql-string))) |
| 96 |
|
| 97 |
;;;----------------------------------------------------------------- |
| 98 |
;;; Tokenizer |
| 99 |
;;; Returns a list of tokens. Each token is either one of the |
| 100 |
;;; following form. |
| 101 |
;;; |
| 102 |
;;; <string> Regular identifiers. |
| 103 |
;;; <symbol> Special delimiters: |
| 104 |
;;; + - * / < = > <> <= >= || |
| 105 |
;;; <char> Special delimiters: |
| 106 |
;;; , . ( ) ; |
| 107 |
;;; (delimited <string>) Delimited identifier |
| 108 |
;;; (parameter <num>) Positional parameter (?) |
| 109 |
;;; (parameter <string>) Named parameter (:foo) |
| 110 |
;;; (string <string>) Character string literal |
| 111 |
;;; (number <string>) Numeric literal |
| 112 |
;;; (bitstring <string>) Binary string. <string> is like "01101" |
| 113 |
;;; (hexstring <string>) Binary string. <string> is like "3AD20" |
| 114 |
;;; |
| 115 |
;;; NB: The difference of two kinds of special delimiters is that |
| 116 |
;;; the former is sometimes written with surrounding |
| 117 |
;;; whitespaces, while the latter is almost never written with them. |
| 118 |
|
| 119 |
(define (sql-tokenize sql-string) |
| 120 |
(define parameter-count 0) ;; positional parameter count |
| 121 |
;; |
| 122 |
;; skip whitespaces |
| 123 |
;; |
| 124 |
(define (skip-ws s) |
| 125 |
(cond ((#/^\s+(--)?/ s) |
| 126 |
=> (^m (if (m 1) (skip-comment s) (m 'after)))) |
| 127 |
(else s))) |
| 128 |
(define (skip-comment s) |
| 129 |
(cond ((string-scan s "\n" 'after)) |
| 130 |
(else ""))) |
| 131 |
;; |
| 132 |
;; main dispatcher |
| 133 |
;; |
| 134 |
(define (entry s r) |
| 135 |
(let1 s (skip-ws s) |
| 136 |
(if (string-null? s) |
| 137 |
(reverse! r) |
| 138 |
(let1 c (string-ref s 0) |
| 139 |
(cond |
| 140 |
[(char=? c #\') (scan-string s r)] |
| 141 |
[(char=? c #\") (scan-delimited s r)] |
| 142 |
[(char=? c #\?) |
| 143 |
(entry (string-drop s 1) |
| 144 |
(cons `(parameter ,(begin0 parameter-count |
| 145 |
(inc! parameter-count))) |
| 146 |
r))] |
| 147 |
[(#/^[+-]?(?:\d+(?:\.\d*)?|(?:\.\d+))(?:[eE][+-]?\d+)?/ s) |
| 148 |
=> (lambda (m) |
| 149 |
(entry (m 'after) (cons `(number ,(m)) r)))] |
| 150 |
[(#/^(<>|<=|>=|\|\|)/ s) |
| 151 |
(entry (string-drop s 2) |
| 152 |
(cons (string->symbol (string-take s 2)) r))] |
| 153 |
[(#/^[bB]'/ s) (scan-bitstring s r)] |
| 154 |
[(#/^[xX]'/ s) (scan-hexstring s r)] |
| 155 |
[(char-set-contains? #[-+*/<=>] c) |
| 156 |
(entry (string-drop s 1) (cons (string->symbol (string c)) r))] |
| 157 |
[(char-set-contains? #[,.()\;] c) |
| 158 |
(entry (string-drop s 1) (cons c r))] |
| 159 |
[(#/^:(\w+)/ s) |
| 160 |
=> (lambda (m) |
| 161 |
(entry (m 'after) (cons `(parameter ,(m 1)) r)))] |
| 162 |
[(#/^\w+/ s) |
| 163 |
=> (lambda (m) |
| 164 |
(entry (m 'after) (cons (m) r)))] |
| 165 |
[else (e "invalid SQL token beginning with ~s in: ~s" |
| 166 |
c sql-string)]))))) |
| 167 |
;; |
| 168 |
;; subscanners |
| 169 |
;; |
| 170 |
(define (scan-quote s r q succ efmt) |
| 171 |
(let loop ((pos 0)) |
| 172 |
(or (and-let* ([pos (string-index s q pos)]) |
| 173 |
(if (eqv? (string-ref s (+ pos 1) #f) q) |
| 174 |
(loop (+ pos 2)) |
| 175 |
(succ (string-take s pos) (string-drop s (+ pos 1))))) |
| 176 |
(e efmt sql-string)))) |
| 177 |
|
| 178 |
(define (scan-string s r) |
| 179 |
(scan-quote (string-drop s 1) r #\' |
| 180 |
(lambda (body rest) |
| 181 |
(entry rest |
| 182 |
`((string ,(regexp-replace-all #/''/ body "'")) |
| 183 |
. ,r))) |
| 184 |
"unterminated string literal in SQL: ~s")) |
| 185 |
|
| 186 |
(define (scan-delimited s r) |
| 187 |
(scan-quote (string-drop s 1) r #\" |
| 188 |
(lambda (body rest) |
| 189 |
(entry rest |
| 190 |
`((delimited ,(regexp-replace-all #/""/ body "\"")) |
| 191 |
. ,r))) |
| 192 |
"unterminated delimited identifier in SQL: ~s")) |
| 193 |
|
| 194 |
(define (scan-bitstring s r) |
| 195 |
(cond [(#/^.'([01]+)'/ s) |
| 196 |
=> (lambda (m) |
| 197 |
(entry (m 'after) |
| 198 |
(cons `(bitstring ,(m 1)) r)))] |
| 199 |
[else |
| 200 |
(e "unterminated bitstring literal in SQL: ~s" sql-string)])) |
| 201 |
(define (scan-hexstring s r) |
| 202 |
(cond [(#/^.'([\da-fA-F]+)'/ s) |
| 203 |
=> (lambda (m) |
| 204 |
(entry (m 'after) |
| 205 |
(cons `(hexstring ,(m 1)) r)))] |
| 206 |
[else |
| 207 |
(e "unterminated bitstring literal in SQL: ~s" sql-string)])) |
| 208 |
;; |
| 209 |
;; raising an error |
| 210 |
;; |
| 211 |
(define (e fmt . args) |
| 212 |
(apply errorf <sql-parse-error> :sql-string sql-string fmt args)) |
| 213 |
;; |
| 214 |
;; main entry |
| 215 |
;; |
| 216 |
(entry (skip-ws sql-string) '())) |