queryverse / QuerySQLite.jl
1
# The translation pass turns Julia expressions into SQL expressions
2
# Frustratingly, _primary tables are translated differently from secondary tables, so translate must propagate the `_primary` keyword
3
struct SQLExpression
4
    call::Symbol
5
    arguments::Vector{Any}
6 1
    SQLExpression(call, arguments...) = new(call, Any[arguments...])
7
end
8

9
function nest(sql_expression)
10 1
    SQLExpression(:AS, SQLExpression(:FROM, sql_expression), :__TABLE__)
11
end
12

13
function translate(something::Union{Char,AbstractString}; _primary=true)
14 1
    repr(something)
15
end
16

17
function translate(something; _primary=true)
18 1
    something
19
end
20
function translate(source_row::SourceRow; _primary=true)
21 0
    source_row.table_name
22
end
23
function translate(call::Expr; _primary=true)
24 1
    arguments, keywords = split_call(call)
25 1
    translate_call(arguments...; _primary=_primary, keywords...)
26
end
27

28
# A 1-1 mapping between Julia functions and SQL functions
29
function translate_default(location, function_type, SQL_call)
30 1
    result = :(
31
        function translate_call($function_type, arguments...; _primary=true)
32
            $SQLExpression($SQL_call, $map(
33 1
                argument -> $translate(argument; _primary=_primary),
34
                arguments
35
            )...)
36
        end
37
    )
38 1
    result.args[2].args[1] = location
39 1
    result
40
end
41

42
macro translate_default(a_function, SQL_call)
43 1
    translate_default(__source__, a_function, SQL_call) |> esc
44
end
45

46 1
@translate_default ::typeof(==) :(=)
47

48 1
@translate_default ::typeof(!=) Symbol("<>")
49

50 1
@translate_default ::typeof(!) :NOT
51

52 1
@translate_default ::typeof(&) :AND
53

54 1
@translate_default ::typeof(|) :OR
55

56 1
@translate_default ::typeof(*) :*
57

58 1
@translate_default ::typeof(/) :/
59

60 1
@translate_default ::typeof(+) :+
61

62 1
@translate_default ::typeof(-) :-
63

64 1
@translate_default ::typeof(%) :%
65

66 1
@translate_default ::typeof(abs) :ABS
67

68
function as(pair; _primary=true)
69 1
    SQLExpression(:AS,
70
        translate(pair.second.code; _primary=_primary),
71
        pair.first
72
    )
73
end
74

75 1
@translate_default ::typeof(coalesce) :COALESCE
76

77 1
@translate_default ::typeof(char) :CHAR
78

79
function translate_call(::typeof(convert), ::Type{Int}, it; _primary=true)
80 1
    SQLExpression(:UNICODE, translate(it))
81
end
82

83 1
@translate_default ::typeof(hex) :HEX
84

85 1
@translate_default ::typeof(QueryOperators.drop) :OFFSET
86

87
function translate_call(::typeof(QueryOperators.filter), iterator, call, call_expression; _primary=true)
88 1
    SQLExpression(:WHERE,
89
        translate(iterator; _primary=_primary),
90
        translate(call(model_row(iterator)).code; _primary=_primary)
91
    )
92
end
93

94
function translate_call(::typeof(format), time_type, format_string; _primary=true)
95 1
    SQLExpression(
96
        :STRFTIME,
97
        translate(format_string; _primary=_primary),
98
        translate(time_type; _primary=_primary)
99
    )
100
end
101

102
function translate_call(::typeof(getproperty), source_tables::Database, table_name; _primary=true)
103 1
    translated = translate(table_name; _primary=_primary)
104 1
    if _primary
105 1
        SQLExpression(:FROM, translated)
106
    else
107 1
        translated
108
    end
109
end
110
function translate_call(::typeof(getproperty), source_row::SourceRow, column_name; _primary=true)
111 1
    translated = translate(column_name; _primary=_primary)
112 1
    if _primary
113 1
        translated
114
    else
115 1
        SQLExpression(:., source_row.table_name, translated)
116
    end
117
end
118

119
function translate_call(::typeof(QueryOperators.groupby), ungrouped, group_function, group_function_expression, map_selector, map_function_expression; _primary=true)
120 1
    model = model_row(ungrouped)
121 1
    SQLExpression(Symbol("GROUP BY"),
122
        nest(translate_call(
123
            QueryOperators.map,
124
            ungrouped,
125
            map_selector, map_function_expression,
126
            _primary=_primary
127
        )),
128
        translate(group_function(model).code; _primary=_primary)
129
    )
130
end
131

132 1
@translate_default ::typeof(if_else) :CASE
133

134 1
@translate_default ::typeof(in) :IN
135

136 1
@translate_default ::typeof(instr) :INSTR
137

138
@translate_default ::typeof(isequal) Symbol("IS NOT DISTINCT FROM")
139

140
@translate_default ::typeof(isless) :<
141

142 1
@translate_default ::typeof(ismissing) Symbol("IS NULL")
143

144 1
@translate_default ::typeof(join) :GROUP_CONCAT
145

146
function translate_call(::typeof(QueryOperators.join), source1, source2, key1, key1_expression, key2, key2_expression, combine, combine_expression; _primary=true)
147 1
    model_row_1 = model_row(source1)
148 1
    model_row_2 = model_row(source2)
149 1
    SQLExpression(:SELECT,
150
        SQLExpression(:ON,
151
            SQLExpression(Symbol("INNER JOIN"),
152
                translate(source1),
153
                # mark as not _primary to suppress FROM
154
                translate(source2; _primary=false)
155
            ),
156
            # mark both as not _primary to always be explicit about table
157
            SQLExpression(:(=),
158
                translate(key1(model_row_1).code; _primary=false),
159
                translate(key2(model_row_2).code; _primary=false)
160
            )
161
        ),
162
        # mark both as not _primary to always be explicit about table
163
        Generator(
164
            pair -> as(pair; _primary=false),
165
            pairs(combine(model_row_1, model_row_2))
166
        )...
167
    )
168
end
169

170 1
@translate_default ::typeof(length) :COUNT
171

172 1
@translate_default ::typeof(lowercase) :LOWER
173

174
function translate_call(::typeof(QueryOperators.map), select_table, call, call_expression; _primary=true)
175 1
    inner = translate(select_table; _primary=_primary)
176 1
    if inner.call == :SELECT
177 1
        inner = nest(inner)
178
    end
179 1
    SQLExpression(
180
        :SELECT, inner,
181
        Generator(
182
            pair -> as(pair; _primary=_primary),
183
            pairs(call(model_row(select_table)))
184
        )...
185
    )
186
end
187

188 1
@translate_default ::typeof(max) :max
189 1
@translate_default ::typeof(maximum) :max
190

191 1
@translate_default ::typeof(mean) :AVG
192

193 1
@translate_default ::typeof(min) :min
194 1
@translate_default ::typeof(minimum) :min
195

196 1
translate_call(::typeof(occursin), needle, haystack; _primary=true) =
197
    SQLExpression(
198
        :LIKE,
199
        translate(haystack; _primary=_primary),
200
        translate(needle; _primary=_primary)
201
    )
202

203
function translate_call(::typeof(QueryOperators.orderby), unordered, key_function, key_function_expression; _primary=true)
204 1
    SQLExpression(Symbol("ORDER BY"),
205
        translate(unordered; _primary=_primary),
206
        translate(key_function(model_row(unordered)).code; _primary=_primary)
207
    )
208
end
209

210
function translate_call(::typeof(QueryOperators.orderby_descending), unordered, key_function, key_function_expression; _primary=true)
211 1
    SQLExpression(Symbol("ORDER BY"),
212
        translate(unordered; _primary=_primary),
213
        SQLExpression(:DESC,
214
            translate(key_function(model_row(unordered)).code; _primary=_primary)
215
        )
216
    )
217
end
218

219 1
@translate_default ::typeof(repr) :QUOTE
220

221
function translate_call(::typeof(rand), ::Type{Int}; _primary=true)
222 1
    SQLExpression(:RANDOM)
223
end
224

225
@translate_default ::typeof(randstring) :RANDOMBLOB
226

227
function translate_call(::typeof(replace), it, pair; _primary=true)
228 1
    SQLExpression(:REPLACE,
229
        translate(it; _primary=_primary),
230
        translate(pair.first; _primary=_primary),
231
        translate(pair.second; _primary=_primary)
232
    )
233
end
234

235
function translate_call(::typeof(round), it; _primary=true, digits=0)
236 1
    SQLExpression(:ROUND,
237
        translate(it; _primary=_primary),
238
        translate(digits; _primary=_primary)
239
    )
240
end
241

242 1
@translate_default ::typeof(string) :||
243

244 1
@translate_default ::typeof(strip) :TRIM
245

246 1
@translate_default ::Type{SubString} :SUBSTR
247

248 1
@translate_default ::typeof(sum) :SUM
249

250 1
@translate_default ::typeof(QueryOperators.take) :LIMIT
251

252
function translate_call(::typeof(QueryOperators.thenby), unordered, key_function, key_function_expression; _primary=true)
253 1
    original = translate(unordered; _primary=_primary)
254 1
    SQLExpression(original.call, original.arguments...,
255
        translate(key_function(model_row(unordered)).code; _primary=_primary)
256
    )
257
end
258

259
function translate_call(::typeof(QueryOperators.thenby_descending), unordered, key_function, key_function_expression; _primary=true)
260 1
    original = translate(unordered; _primary=_primary)
261 1
    SQLExpression(original.call, original.arguments...,
262
        SQLExpression(:DESC,
263
            translate(key_function(model_row(unordered)).code; _primary=_primary)
264
        )
265
    )
266
end
267

268 1
@translate_default ::typeof(type_of) :TYPEOF
269

270
function translate_call(::typeof(QueryOperators.unique), repeated, key_function, key_function_expression; _primary=true)
271 1
    result = translate(repeated; _primary=_primary)
272 1
    SQLExpression(Symbol(string(result.call, " DISTINCT")), result.arguments...)
273
end
274

275 1
@translate_default ::typeof(uppercase) :UPPER

Read our documentation on viewing source code .

Loading