translate.jl at 6049c2f ⋅ queryverse/QuerySQLite.jl

# The translation pass turns Julia expressions into SQL expressions
# Frustratingly, _primary tables are translated differently from secondary tables, so translate must propagate the `_primary` keyword
struct SQLExpression
    call::Symbol
    arguments::Vector{Any}
    SQLExpression(call, arguments...) = new(call, Any[arguments...])
end

function nest(sql_expression)
    SQLExpression(:AS, SQLExpression(:FROM, sql_expression), :__TABLE__)
end

function translate(something::Union{Char,AbstractString}; _primary=true)
    repr(something)
end

function translate(something; _primary=true)
    something
end
function translate(source_row::SourceRow; _primary=true)
    source_row.table_name
end
function translate(call::Expr; _primary=true)
    arguments, keywords = split_call(call)
    translate_call(arguments...; _primary=_primary, keywords...)
end

# A 1-1 mapping between Julia functions and SQL functions
function translate_default(location, function_type, SQL_call)
    result = :(
        function translate_call($function_type, arguments...; _primary=true)
            $SQLExpression($SQL_call, $map(
                argument -> $translate(argument; _primary=_primary),
                arguments
            )...)
        end
    )
    result.args[2].args[1] = location
    result
end

macro translate_default(a_function, SQL_call)
    translate_default(__source__, a_function, SQL_call) |> esc
end

@translate_default ::typeof(==) :(=)

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

@translate_default ::typeof(!) :NOT

@translate_default ::typeof(&) :AND

@translate_default ::typeof(|) :OR

@translate_default ::typeof(*) :*

@translate_default ::typeof(/) :/

@translate_default ::typeof(+) :+

@translate_default ::typeof(-) :-

@translate_default ::typeof(%) :%

@translate_default ::typeof(abs) :ABS

function as(pair; _primary=true)
    SQLExpression(:AS,
        translate(pair.second.code; _primary=_primary),
        pair.first
    )
end

@translate_default ::typeof(coalesce) :COALESCE

@translate_default ::typeof(char) :CHAR

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

@translate_default ::typeof(hex) :HEX

@translate_default ::typeof(QueryOperators.drop) :OFFSET

function translate_call(::typeof(QueryOperators.filter), iterator, call, call_expression; _primary=true)
    SQLExpression(:WHERE,
        translate(iterator; _primary=_primary),
        translate(call(model_row(iterator)).code; _primary=_primary)
    )
end

function translate_call(::typeof(format), time_type, format_string; _primary=true)
    SQLExpression(
        :STRFTIME,
        translate(format_string; _primary=_primary),
        translate(time_type; _primary=_primary)
    )
end

function translate_call(::typeof(getproperty), source_tables::Database, table_name; _primary=true)
    translated = translate(table_name; _primary=_primary)
    if _primary
        SQLExpression(:FROM, translated)
    else
        translated
    end
end
function translate_call(::typeof(getproperty), source_row::SourceRow, column_name; _primary=true)
    translated = translate(column_name; _primary=_primary)
    if _primary
        translated
    else
        SQLExpression(:., source_row.table_name, translated)
    end
end

function translate_call(::typeof(QueryOperators.groupby), ungrouped, group_function, group_function_expression, map_selector, map_function_expression; _primary=true)
    model = model_row(ungrouped)
    SQLExpression(Symbol("GROUP BY"),
        nest(translate_call(
            QueryOperators.map,
            ungrouped,
            map_selector, map_function_expression,
            _primary=_primary
        )),
        translate(group_function(model).code; _primary=_primary)
    )
end

@translate_default ::typeof(if_else) :CASE

@translate_default ::typeof(in) :IN

@translate_default ::typeof(instr) :INSTR

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

@translate_default ::typeof(isless) :<

@translate_default ::typeof(ismissing) Symbol("IS NULL")

@translate_default ::typeof(join) :GROUP_CONCAT

function translate_call(::typeof(QueryOperators.join), source1, source2, key1, key1_expression, key2, key2_expression, combine, combine_expression; _primary=true)
    model_row_1 = model_row(source1)
    model_row_2 = model_row(source2)
    SQLExpression(:SELECT,
        SQLExpression(:ON,
            SQLExpression(Symbol("INNER JOIN"),
                translate(source1),
                # mark as not _primary to suppress FROM
                translate(source2; _primary=false)
            ),
            # mark both as not _primary to always be explicit about table
            SQLExpression(:(=),
                translate(key1(model_row_1).code; _primary=false),
                translate(key2(model_row_2).code; _primary=false)
            )
        ),
        # mark both as not _primary to always be explicit about table
        Generator(
            pair -> as(pair; _primary=false),
            pairs(combine(model_row_1, model_row_2))
        )...
    )
end

@translate_default ::typeof(length) :COUNT

@translate_default ::typeof(lowercase) :LOWER

function translate_call(::typeof(QueryOperators.map), select_table, call, call_expression; _primary=true)
    inner = translate(select_table; _primary=_primary)
    if inner.call == :SELECT
        inner = nest(inner)
    end
    SQLExpression(
        :SELECT, inner,
        Generator(
            pair -> as(pair; _primary=_primary),
            pairs(call(model_row(select_table)))
        )...
    )
end

@translate_default ::typeof(max) :max
@translate_default ::typeof(maximum) :max

@translate_default ::typeof(mean) :AVG

@translate_default ::typeof(min) :min
@translate_default ::typeof(minimum) :min

translate_call(::typeof(occursin), needle, haystack; _primary=true) =
    SQLExpression(
        :LIKE,
        translate(haystack; _primary=_primary),
        translate(needle; _primary=_primary)
    )

function translate_call(::typeof(QueryOperators.orderby), unordered, key_function, key_function_expression; _primary=true)
    SQLExpression(Symbol("ORDER BY"),
        translate(unordered; _primary=_primary),
        translate(key_function(model_row(unordered)).code; _primary=_primary)
    )
end

function translate_call(::typeof(QueryOperators.orderby_descending), unordered, key_function, key_function_expression; _primary=true)
    SQLExpression(Symbol("ORDER BY"),
        translate(unordered; _primary=_primary),
        SQLExpression(:DESC,
            translate(key_function(model_row(unordered)).code; _primary=_primary)
        )
    )
end

@translate_default ::typeof(repr) :QUOTE

function translate_call(::typeof(rand), ::Type{Int}; _primary=true)
    SQLExpression(:RANDOM)
end

@translate_default ::typeof(randstring) :RANDOMBLOB

function translate_call(::typeof(replace), it, pair; _primary=true)
    SQLExpression(:REPLACE,
        translate(it; _primary=_primary),
        translate(pair.first; _primary=_primary),
        translate(pair.second; _primary=_primary)
    )
end

function translate_call(::typeof(round), it; _primary=true, digits=0)
    SQLExpression(:ROUND,
        translate(it; _primary=_primary),
        translate(digits; _primary=_primary)
    )
end

@translate_default ::typeof(string) :||

@translate_default ::typeof(strip) :TRIM

@translate_default ::Type{SubString} :SUBSTR

@translate_default ::typeof(sum) :SUM

@translate_default ::typeof(QueryOperators.take) :LIMIT

function translate_call(::typeof(QueryOperators.thenby), unordered, key_function, key_function_expression; _primary=true)
    original = translate(unordered; _primary=_primary)
    SQLExpression(original.call, original.arguments...,
        translate(key_function(model_row(unordered)).code; _primary=_primary)
    )
end

function translate_call(::typeof(QueryOperators.thenby_descending), unordered, key_function, key_function_expression; _primary=true)
    original = translate(unordered; _primary=_primary)
    SQLExpression(original.call, original.arguments...,
        SQLExpression(:DESC,
            translate(key_function(model_row(unordered)).code; _primary=_primary)
        )
    )
end

@translate_default ::typeof(type_of) :TYPEOF

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

@translate_default ::typeof(uppercase) :UPPER

Read our documentation on viewing source code .

Navigation	Overlay
`t` Navigate files	`h` Toggle hits
`y` Change url to tip of branch	`m` Toggle misses
`b / v` Jump to prev/next hit line	`p` Toggle partial
`z / x` Jump to prev/next missed or partial line	`1..9` Toggle flags
`shift + o` Open current page in GitHub	`a` Toggle all on
`/ or ?` Show keyboard shortcuts dialog	`c` Toggle context lines or commits

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