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
|