code_moniker_core/lang/sql/
mod.rs

1mod body;
2mod canonicalize;
3mod kinds;
4mod plpgsql_grammar;
5mod sdk_pipeline;
6
7use tree_sitter::Tree;
8
9use crate::core::code_graph::CodeGraph;
10use crate::core::moniker::Moniker;
11use crate::core::shape::Shape;
12
13use crate::lang::{ExtractionContext, KindSpec, LangExtractor, ParsedDocument};
14
15#[derive(Clone, Debug, Default)]
16pub struct Presets {
17	pub external_schemas: Vec<String>,
18}
19
20pub fn parse(source: &str) -> Tree {
21	sdk_pipeline::discover::parse(source)
22}
23
24pub fn parse_plpgsql(source: &str) -> ParsedDocument {
25	ParsedDocument::new(body::parse_plpgsql(source))
26}
27
28pub fn extract(
29	uri: &str,
30	source: &str,
31	anchor: &Moniker,
32	deep: bool,
33	presets: &Presets,
34) -> CodeGraph {
35	<Lang as LangExtractor>::extract(uri, source, anchor, deep, presets)
36}
37
38pub struct Lang;
39
40const DEF_KINDS: &[&str] = &[
41	"function",
42	"procedure",
43	"view",
44	"table",
45	"column",
46	"constraint",
47	"trigger",
48	"type",
49	"schema",
50];
51
52const DEF_KIND_SPECS: &[KindSpec] = &[
53	KindSpec::new("schema", Shape::Namespace, 10, "schema"),
54	KindSpec::new("table", Shape::Type, 20, "table"),
55	KindSpec::new("view", Shape::Type, 21, "view"),
56	KindSpec::new("type", Shape::Type, 22, "type"),
57	KindSpec::new("column", Shape::Value, 30, "column"),
58	KindSpec::new("constraint", Shape::Value, 31, "constraint"),
59	KindSpec::new("trigger", Shape::Value, 32, "trigger"),
60	KindSpec::new("function", Shape::Callable, 40, "function"),
61	KindSpec::new("procedure", Shape::Callable, 41, "procedure"),
62];
63
64impl crate::lang::LangExtractor for Lang {
65	type Presets = Presets;
66	const LANG_TAG: &'static str = "sql";
67	const ALLOWED_KINDS: &'static [&'static str] = DEF_KINDS;
68	const KIND_SPECS: &'static [KindSpec] = DEF_KIND_SPECS;
69	const ALLOWED_VISIBILITIES: &'static [&'static str] = &[];
70
71	fn parse(_uri: &str, source: &str) -> ParsedDocument {
72		body::parse_document(parse(source), source)
73	}
74
75	fn file_root(uri: &str, anchor: &Moniker) -> Option<Moniker> {
76		Some(canonicalize::compute_module_moniker(anchor, uri))
77	}
78
79	fn extract_parsed(
80		context: ExtractionContext<'_, Self::Presets>,
81		document: &ParsedDocument,
82	) -> CodeGraph {
83		sdk_pipeline::extract(
84			context.uri,
85			context.source,
86			document,
87			context.anchor,
88			context.deep,
89			context.presets,
90		)
91	}
92}
93
94#[cfg(test)]
95mod tests {
96	use super::*;
97	use crate::core::moniker::MonikerBuilder;
98
99	fn anchor() -> Moniker {
100		MonikerBuilder::new().project(b"app").build()
101	}
102
103	fn run(uri: &str, src: &str) -> CodeGraph {
104		extract(uri, src, &anchor(), false, &Presets::default())
105	}
106
107	fn def_monikers(g: &CodeGraph) -> Vec<String> {
108		g.defs()
109			.map(|d| crate::core::uri::to_uri(&d.moniker, &Default::default()))
110			.collect()
111	}
112
113	fn ref_targets(g: &CodeGraph) -> Vec<String> {
114		g.refs()
115			.map(|r| crate::core::uri::to_uri(&r.target, &Default::default()))
116			.collect()
117	}
118
119	fn relation_edges(g: &CodeGraph, kind: &[u8]) -> Vec<(String, String)> {
120		g.refs()
121			.filter(|reference| reference.kind == kind)
122			.map(|reference| {
123				(
124					crate::core::uri::to_uri(
125						&g.def_at(reference.source).moniker,
126						&Default::default(),
127					),
128					crate::core::uri::to_uri(&reference.target, &Default::default()),
129				)
130			})
131			.collect()
132	}
133
134	#[test]
135	fn parsed_document_keeps_plpgsql_as_shared_syntax() {
136		let source = "CREATE FUNCTION account_balance(p_id bigint) RETURNS numeric \
137			LANGUAGE plpgsql AS $$\n\
138			DECLARE total numeric;\n\
139			BEGIN\n\
140			  SELECT sum(amount) INTO total FROM ledger_entry WHERE account_id = p_id;\n\
141			  IF total IS NULL THEN RETURN 0; END IF;\n\
142			  RETURN total;\n\
143			END;\n\
144			$$;";
145		let document = <Lang as crate::lang::LangExtractor>::parse("account.sql", source);
146		assert_eq!(document.primary().root_node().kind(), "source_file");
147		let injection = document
148			.injections()
149			.iter()
150			.find(|injection| injection.language() == "plpgsql")
151			.expect("PL/pgSQL injection");
152		assert_eq!(injection.tree().root_node().kind(), "source_file");
153		assert!(!injection.tree().root_node().has_error());
154
155		let sql_source = "CREATE FUNCTION recent_accounts() RETURNS SETOF account LANGUAGE sql AS \
156			 $$ SELECT * FROM account ORDER BY created_at DESC $$;";
157		let sql_document = <Lang as crate::lang::LangExtractor>::parse("account.sql", sql_source);
158		let sql_injection = sql_document
159			.injections()
160			.iter()
161			.find(|injection| injection.language() == "sql")
162			.expect("SQL-language injection");
163		assert_eq!(sql_injection.tree().root_node().kind(), "source_file");
164		assert!(!sql_injection.tree().root_node().has_error());
165	}
166
167	#[test]
168	fn dollar_quoted_parameter_default_is_distinct_from_plpgsql_body() {
169		let source = "CREATE FUNCTION app.with_default(value text DEFAULT $$fallback$$)\n\
170			RETURNS text\n\
171			LANGUAGE plpgsql\n\
172			AS $body$ BEGIN RETURN value; END; $body$;";
173		let document = <Lang as crate::lang::LangExtractor>::parse("default.sql", source);
174		let root = document.primary().root_node();
175		assert!(!root.has_error(), "{}", root.to_sexp());
176
177		let function = crate::lang::tree_util::find_descendant(root, "CreateFunctionStmt")
178			.expect("function declaration");
179		let parameter = crate::lang::tree_util::find_descendant(function, "func_arg_with_default")
180			.expect("parameter with default");
181		let default = crate::lang::tree_util::find_descendant(parameter, "dollar_quoted_string")
182			.expect("dollar-quoted default expression");
183		assert_eq!(
184			&source[default.start_byte()..default.end_byte()],
185			"$$fallback$$"
186		);
187
188		let injection = document
189			.injections()
190			.iter()
191			.find(|injection| injection.language() == "plpgsql")
192			.expect("PL/pgSQL body injection");
193		let host_range = injection.host_byte_range();
194		assert_eq!(
195			&source[host_range],
196			"$body$ BEGIN RETURN value; END; $body$"
197		);
198		let content_range = injection.content_byte_range();
199		assert_eq!(&source[content_range], " BEGIN RETURN value; END; ");
200		assert!(!injection.tree().root_node().has_error());
201	}
202
203	#[test]
204	fn multiple_routines_keep_their_own_dollar_quoted_bodies() {
205		let source = "CREATE FUNCTION app.first(value text DEFAULT $$fallback$$) RETURNS text \
206			LANGUAGE plpgsql AS $first$ BEGIN RETURN value; END; $first$;\n\
207			CREATE FUNCTION app.second(value text DEFAULT $default$other$default$) RETURNS text \
208			LANGUAGE plpgsql AS $second$ BEGIN RETURN value; END; $second$;";
209		let document = <Lang as crate::lang::LangExtractor>::parse("routines.sql", source);
210		assert!(!document.primary().root_node().has_error());
211		let bodies = document
212			.injections()
213			.iter()
214			.map(|injection| &source[injection.host_byte_range()])
215			.collect::<Vec<_>>();
216		assert_eq!(
217			bodies,
218			[
219				"$first$ BEGIN RETURN value; END; $first$",
220				"$second$ BEGIN RETURN value; END; $second$",
221			]
222		);
223	}
224
225	#[test]
226	fn qualified_function_emits_full_signature() {
227		let g = run(
228			"foo.sql",
229			"CREATE FUNCTION public.bar(a int, b text) RETURNS int LANGUAGE sql AS $$ SELECT 1 $$;",
230		);
231		assert!(
232			def_monikers(&g).iter().any(|m| m
233				== "code+moniker://app/lang:sql/module:foo/schema:public/function:bar(a:int4,b:text)"),
234			"got defs: {:?}",
235			def_monikers(&g)
236		);
237		let func = g
238			.defs()
239			.find(|d| d.kind == b"function")
240			.expect("function def");
241		assert_eq!(func.signature, b"a:int4,b:text");
242	}
243
244	#[test]
245	fn overloads_with_different_types_both_land() {
246		let g = run(
247			"foo.sql",
248			"CREATE FUNCTION m(x int) RETURNS int LANGUAGE sql AS $$ SELECT x $$;\
249			 CREATE FUNCTION m(x text) RETURNS text LANGUAGE sql AS $$ SELECT x $$;",
250		);
251		assert_eq!(g.defs().filter(|d| d.kind == b"function").count(), 2);
252	}
253
254	#[test]
255	fn top_level_select_emits_qualified_call() {
256		let g = run("foo.sql", "SELECT public.bar(1, 2);");
257		assert!(
258			ref_targets(&g).iter().any(|t| t
259				== "code+moniker://app/lang:sql/module:foo/schema:public/function:bar(int4,int4)"),
260			"got refs: {:?}",
261			ref_targets(&g)
262		);
263	}
264
265	#[test]
266	fn empty_source_yields_only_module_root() {
267		let g = run("db/functions/plan/create_plan.sql", "");
268		let defs: Vec<_> = g.defs().collect();
269		assert_eq!(defs.len(), 1);
270		assert_eq!(
271			crate::core::uri::to_uri(&defs[0].moniker, &Default::default()),
272			"code+moniker://app/lang:sql/dir:db/dir:functions/dir:plan/module:create_plan"
273		);
274	}
275
276	#[test]
277	fn nested_calls_preserve_unknown_argument_slots() {
278		let g = run("foo.sql", "SELECT f(g(a, b));");
279		assert!(
280			ref_targets(&g)
281				.iter()
282				.any(|t| t == "code+moniker://app/lang:sql/module:foo/function:f(_)"),
283			"outer call f should preserve one unknown slot, got refs: {:?}",
284			ref_targets(&g)
285		);
286		assert!(
287			ref_targets(&g)
288				.iter()
289				.any(|t| t == "code+moniker://app/lang:sql/module:foo/function:g(_,_)"),
290			"inner call g should preserve two unknown slots, got refs: {:?}",
291			ref_targets(&g)
292		);
293	}
294
295	#[test]
296	fn call_argument_types_come_from_casts_literals_and_parameters() {
297		let g = run(
298			"foo.sql",
299			"CREATE FUNCTION public.wrapper(p_id uuid, p_enabled bool) RETURNS int LANGUAGE sql AS $$ SELECT public.choose(p_id, 42::bigint, p_enabled, 'x'::text, NULL) $$;",
300		);
301		assert!(
302			ref_targets(&g).iter().any(|target| target
303				== "code+moniker://app/lang:sql/module:foo/schema:public/function:choose(uuid,int8,bool,text,_)") ,
304			"typed target missing from {:?}",
305			ref_targets(&g)
306		);
307	}
308
309	#[test]
310	fn casts_inside_expressions_do_not_type_the_whole_argument() {
311		let g = run(
312			"foo.sql",
313			"CREATE FUNCTION public.wrapper(p_id int) RETURNS int LANGUAGE sql AS $$ SELECT public.choose(p_id = 1::int, (p_id = 1)::text) $$;",
314		);
315		assert!(
316			ref_targets(&g).iter().any(|target| target
317				== "code+moniker://app/lang:sql/module:foo/schema:public/function:choose(_,text)"),
318			"an inner cast must not type its enclosing expression: {:?}",
319			ref_targets(&g)
320		);
321	}
322
323	#[test]
324	fn named_call_arguments_preserve_names_and_types() {
325		let g = run(
326			"foo.sql",
327			"CREATE FUNCTION public.wrapper(p_id uuid) RETURNS int LANGUAGE sql AS $$ SELECT public.choose(label => 'x'::text, value => p_id) $$;",
328		);
329		assert!(
330			ref_targets(&g).iter().any(|target| target
331				== "code+moniker://app/lang:sql/module:foo/schema:public/function:choose(label:text,value:uuid)"),
332			"named typed target missing from {:?}",
333			ref_targets(&g)
334		);
335	}
336
337	#[test]
338	fn unquoted_parameter_names_are_canonical_in_definitions_and_calls() {
339		let g = run(
340			"foo.sql",
341			"CREATE FUNCTION public.choose(Value int) RETURNS int LANGUAGE sql AS $$ SELECT Value $$; SELECT public.choose(value => 1::int);",
342		);
343		assert!(def_monikers(&g).iter().any(|definition| definition
344			== "code+moniker://app/lang:sql/module:foo/schema:public/function:choose(value:int4)"));
345		assert!(ref_targets(&g).iter().any(|target| target
346			== "code+moniker://app/lang:sql/module:foo/schema:public/function:choose(value:int4)"));
347	}
348
349	#[test]
350	fn static_function_search_path_qualifies_unqualified_calls() {
351		let g = run(
352			"foo.sql",
353			"CREATE FUNCTION public.wrapper(p_id uuid) RETURNS int LANGUAGE sql SET search_path = jobs, pg_temp AS $$ SELECT refresh(p_id) $$;",
354		);
355		assert!(
356			ref_targets(&g).iter().any(|target| target
357				== "code+moniker://app/lang:sql/module:foo/schema:jobs/function:refresh(uuid)"),
358			"search-path-qualified target missing from {:?}",
359			ref_targets(&g)
360		);
361	}
362
363	#[test]
364	fn dynamic_or_catalog_search_path_does_not_claim_one_schema() {
365		let g = run(
366			"foo.sql",
367			"CREATE FUNCTION public.from_role(p_id uuid) RETURNS int LANGUAGE sql SET search_path = \"$user\", jobs, pg_temp AS $$ SELECT refresh(p_id) $$; CREATE FUNCTION public.from_catalog(p_id uuid) RETURNS int LANGUAGE sql SET search_path = pg_catalog, jobs, pg_temp AS $$ SELECT refresh(p_id) $$;",
368		);
369		let refresh_targets = ref_targets(&g)
370			.into_iter()
371			.filter(|target| target.contains("function:refresh"))
372			.collect::<Vec<_>>();
373		assert_eq!(refresh_targets.len(), 2, "got {refresh_targets:?}");
374		assert!(
375			refresh_targets
376				.iter()
377				.all(|target| !target.contains("schema:jobs")),
378			"dynamic/catalog search paths must stay unqualified: {refresh_targets:?}"
379		);
380	}
381
382	#[test]
383	fn duplicate_routine_keeps_the_first_search_path_with_the_first_body() {
384		let g = run(
385			"foo.sql",
386			"CREATE OR REPLACE FUNCTION public.wrapper(p_id uuid) RETURNS int LANGUAGE sql SET search_path = first_schema, pg_temp AS $$ SELECT refresh(p_id) $$; CREATE OR REPLACE FUNCTION public.wrapper(p_id uuid) RETURNS int LANGUAGE sql SET search_path = second_schema, pg_temp AS $$ SELECT refresh(p_id) $$;",
387		);
388		let refresh_targets = ref_targets(&g)
389			.into_iter()
390			.filter(|target| target.contains("function:refresh"))
391			.collect::<Vec<_>>();
392		assert_eq!(refresh_targets.len(), 1, "got {refresh_targets:?}");
393		assert!(refresh_targets[0].contains("schema:first_schema"));
394	}
395
396	#[test]
397	fn duplicate_routine_keeps_the_last_callable_arity_metadata() {
398		let g = run(
399			"foo.sql",
400			"CREATE OR REPLACE FUNCTION public.wrapper(p_id uuid) RETURNS int LANGUAGE sql AS $$ SELECT 1 $$; CREATE OR REPLACE FUNCTION public.wrapper(p_id uuid DEFAULT NULL) RETURNS int LANGUAGE sql AS $$ SELECT 2 $$;",
401		);
402		let wrapper = g
403			.defs()
404			.find(|definition| definition.call_name == b"wrapper")
405			.expect("wrapper definition");
406		assert_eq!(wrapper.call_arity, Some(0));
407	}
408
409	#[test]
410	fn ddl_in_sql_body_does_not_emit_a_definition_with_an_invalid_parent() {
411		let g = run(
412			"foo.sql",
413			"CREATE FUNCTION public.wrapper() RETURNS void LANGUAGE sql AS $$ CREATE TABLE public.inner_table(id int); $$;",
414		);
415		assert_eq!(
416			g.defs()
417				.filter(|definition| definition.kind == b"function")
418				.count(),
419			1
420		);
421		assert_eq!(
422			g.defs()
423				.filter(|definition| definition.kind == b"table")
424				.count(),
425			0
426		);
427	}
428
429	#[test]
430	fn parser_recovery_keywords_do_not_become_calls() {
431		let g = run(
432			"scratch.sql",
433			"SELECT * FROM (SELECT id FROM things WHERE id =) broken; SELECT DISTINCT id FROM things;",
434		);
435		let names = g
436			.refs()
437			.filter(|reference| reference.kind == b"calls")
438			.map(|reference| String::from_utf8_lossy(&reference.call_name).into_owned())
439			.collect::<Vec<_>>();
440		assert!(
441			names
442				.iter()
443				.all(|name| !matches!(name.as_str(), "from" | "distinct" | "as" | "is" | "any")),
444			"parser recovery emitted SQL keywords as calls: {names:?}"
445		);
446	}
447
448	#[test]
449	fn comment_def_bytes_are_a_real_comment_in_outer_source() {
450		let src = r#"CREATE OR REPLACE FUNCTION foo.bar(
451  p_a uuid,
452  p_b text
453)
454RETURNS void
455LANGUAGE plpgsql
456SECURITY DEFINER
457SET search_path = foo, pg_temp
458AS $$
459DECLARE
460  v_x text;
461BEGIN
462  -- real comment, do not lose
463  v_x := 'hello';
464END;
465$$;
466"#;
467		let g = run("fixture.sql", src);
468		for d in g.defs().filter(|d| d.kind == b"comment") {
469			let (s, e) = d.position.expect("comment def must have a position");
470			let slice = &src.as_bytes()[s as usize..e as usize];
471			assert!(
472				slice.starts_with(b"--") || slice.starts_with(b"/*"),
473				"comment def bytes {s}..{e} are not a real comment: {:?}",
474				std::str::from_utf8(slice).unwrap_or("?")
475			);
476		}
477	}
478
479	#[test]
480	fn function_param_emits_uses_type_with_pg_catalog_target() {
481		let g = run(
482			"pkg.sql",
483			"CREATE FUNCTION f(x int, y text) RETURNS bigint LANGUAGE sql AS $$ SELECT 1 $$;",
484		);
485		let int_target = "code+moniker://app/sdk:sql/path:pg_catalog/path:int4";
486		let text_target = "code+moniker://app/sdk:sql/path:pg_catalog/path:text";
487		let bigint_target = "code+moniker://app/sdk:sql/path:pg_catalog/path:int8";
488		let targets = ref_targets(&g);
489		assert!(
490			targets.iter().any(|t| t == int_target),
491			"int param must emit uses_type → pg_catalog/path:int4, got: {targets:?}"
492		);
493		assert!(
494			targets.iter().any(|t| t == text_target),
495			"text param must emit uses_type → pg_catalog/path:text"
496		);
497		assert!(
498			targets.iter().any(|t| t == bigint_target),
499			"bigint return must emit uses_type → pg_catalog/path:int8"
500		);
501		let uses_type_count = g.refs().filter(|r| r.kind == b"uses_type").count();
502		assert!(
503			uses_type_count >= 3,
504			"expected at least 3 uses_type refs (2 params + 1 return), got {uses_type_count}"
505		);
506	}
507
508	#[test]
509	fn user_defined_types_are_definitions_and_qualified_type_targets() {
510		let g = run(
511			"types.sql",
512			"CREATE TYPE app.order_state AS ENUM ('new', 'done'); CREATE DOMAIN app.order_code AS text; CREATE FUNCTION app.accept(value app.order_state) RETURNS app.order_code LANGUAGE sql AS $$ SELECT value::text $$;",
513		);
514		let definitions = def_monikers(&g);
515		assert!(definitions.iter().any(|definition| definition
516			== "code+moniker://app/lang:sql/module:types/schema:app/type:order_state"));
517		assert!(definitions.iter().any(|definition| definition
518			== "code+moniker://app/lang:sql/module:types/schema:app/type:order_code"));
519		let targets = ref_targets(&g);
520		assert!(targets.iter().any(|target| target
521			== "code+moniker://app/lang:sql/module:types/schema:app/type:order_state"));
522		assert!(targets.iter().any(|target| target
523			== "code+moniker://app/lang:sql/module:types/schema:app/type:order_code"));
524	}
525
526	#[test]
527	fn quoted_user_type_targets_fold_only_unquoted_identifiers() {
528		let g = run(
529			"quoted_types.sql",
530			r#"
531CREATE TYPE Sales."OrderRow" AS (id uuid);
532CREATE FUNCTION Sales.load_orders()
533RETURNS SETOF Sales."OrderRow"
534LANGUAGE sql
535AS $$ SELECT NULL::Sales."OrderRow" $$;
536"#,
537		);
538		let targets = ref_targets(&g);
539		assert!(
540			targets.iter().any(|target| target
541				== "code+moniker://app/lang:sql/module:quoted_types/schema:sales/type:OrderRow"),
542			"quoted components preserve case while unquoted components fold: {targets:?}"
543		);
544		assert!(
545			targets
546				.iter()
547				.all(|target| !target.contains("schema:`SETOF Sales`")
548					&& !target.contains("type:%22OrderRow%22"))
549		);
550	}
551
552	#[test]
553	fn builtin_function_call_carries_external_confidence() {
554		let g = run("pkg.sql", "SELECT now();");
555		let r = g
556			.refs()
557			.find(|r| r.kind == b"calls")
558			.expect("calls ref for now()");
559		assert_eq!(
560			r.confidence,
561			b"external".to_vec(),
562			"builtin functions like now() must be marked external, got {:?}",
563			std::str::from_utf8(&r.confidence).unwrap_or("?")
564		);
565	}
566
567	#[test]
568	fn corpus_builtin_families_are_external() {
569		let g = run(
570			"pkg.sql",
571			"SELECT chr(65), json_build_object('id', 1), array_append(ARRAY[1], 2), row_number() OVER (), gen_random_uuid(), plainto_tsquery('code moniker'), quote_ident('table'), pg_tablespace_location(1), txid_current(), jsonb_array_length('[]'), array_upper(ARRAY[1], 1), ts_rank_cd(to_tsvector('code'), plainto_tsquery('code')), inet_client_addr(), split_part('a.b', '.', 1);",
572		);
573		let calls = g
574			.refs()
575			.filter(|reference| reference.kind == b"calls")
576			.collect::<Vec<_>>();
577		assert_eq!(calls.len(), 16, "got {calls:?}");
578		assert!(
579			calls
580				.iter()
581				.all(|reference| reference.confidence == b"external")
582		);
583	}
584
585	#[test]
586	fn callable_metadata_uses_sql_argument_nodes() {
587		let g = run(
588			"foo.sql",
589			"CREATE FUNCTION Public.Combine(a int, b numeric(10, 2)) RETURNS int LANGUAGE sql AS $$ SELECT 1 $$; SELECT PUBLIC.combine(1, 2); SELECT nested(2, 3);",
590		);
591		let definition = g
592			.defs()
593			.find(|def| def.kind == b"function" && def.call_name == b"combine")
594			.expect("combine definition");
595		assert_eq!(definition.call_arity, Some(2));
596		let combine = g
597			.refs()
598			.find(|reference| reference.call_name == b"combine")
599			.expect("combine call");
600		let nested = g
601			.refs()
602			.find(|reference| reference.call_name == b"nested")
603			.expect("nested call");
604		assert_eq!(combine.call_arity, Some(2));
605		assert_eq!(nested.call_arity, Some(2));
606	}
607
608	#[test]
609	fn callable_metadata_models_defaults_and_out_parameters() {
610		let g = run(
611			"foo.sql",
612			"CREATE FUNCTION optional_arg(value int DEFAULT 1) RETURNS int LANGUAGE sql AS $$ SELECT value $$; CREATE FUNCTION parse_type(value text, OUT type_id oid, OUT modifier int) RETURNS record LANGUAGE sql AS $$ SELECT 1, 2 $$;",
613		);
614		let optional = g
615			.defs()
616			.find(|def| def.call_name == b"optional_arg")
617			.expect("optional_arg definition");
618		let parse_type = g
619			.defs()
620			.find(|def| def.call_name == b"parse_type")
621			.expect("parse_type definition");
622		assert_eq!(optional.call_arity, Some(0));
623		assert_eq!(parse_type.call_arity, Some(1));
624	}
625
626	#[test]
627	fn variadic_parameters_are_explicit_in_callable_identity() {
628		let g = run(
629			"foo.sql",
630			"CREATE FUNCTION concat_all(VARIADIC items text[]) RETURNS text LANGUAGE sql AS $$ SELECT '' $$;",
631		);
632		assert!(
633			def_monikers(&g).iter().any(|definition| definition
634				== "code+moniker://app/lang:sql/module:foo/function:concat_all(items:text[]...)"),
635			"got defs: {:?}",
636			def_monikers(&g)
637		);
638	}
639
640	#[test]
641	fn uppercase_builtin_types_and_catalog_calls_are_external() {
642		let g = run(
643			"foo.sql",
644			"CREATE FUNCTION answer() RETURNS NUMERIC LANGUAGE sql AS $$ SELECT 1 $$; SELECT pg_catalog.current_setting('search_path');",
645		);
646		let type_ref = g
647			.refs()
648			.find(|reference| reference.kind == b"uses_type")
649			.expect("return type");
650		assert_eq!(type_ref.confidence, b"external");
651		let catalog_call = g
652			.refs()
653			.find(|reference| reference.kind == b"calls")
654			.expect("catalog call");
655		assert_eq!(catalog_call.confidence, b"external");
656		assert_eq!(
657			crate::core::uri::to_uri(&catalog_call.target, &Default::default()),
658			"code+moniker://app/sdk:sql/path:pg_catalog/path:current_setting"
659		);
660	}
661
662	#[test]
663	fn create_procedure_emits_procedure_callable() {
664		let src = "CREATE PROCEDURE refresh(value int) LANGUAGE sql AS $$ SELECT 1 $$;";
665		let g = run("foo.sql", src);
666		let procedure = g
667			.defs()
668			.find(|def| def.kind == b"procedure")
669			.expect("procedure definition");
670		assert_eq!(procedure.call_name, b"refresh");
671		assert_eq!(procedure.call_arity, Some(1));
672	}
673
674	#[test]
675	fn call_statements_target_procedures_and_quoted_names_are_canonical() {
676		let g = run(
677			"foo.sql",
678			"CREATE PROCEDURE public.\"RefreshCache\"() LANGUAGE sql AS $$ SELECT 1 $$; CALL public.\"RefreshCache\"(); SELECT PUBLIC.REFRESHCACHE();",
679		);
680		let procedure_call = g
681			.refs()
682			.find(|reference| reference.call_name == b"RefreshCache")
683			.expect("quoted procedure call");
684		assert_eq!(
685			procedure_call
686				.target
687				.as_view()
688				.segments()
689				.last()
690				.unwrap()
691				.kind,
692			b"procedure"
693		);
694		let unquoted_call = g
695			.refs()
696			.find(|reference| reference.call_name == b"refreshcache")
697			.expect("unquoted function call");
698		assert_eq!(
699			unquoted_call
700				.target
701				.as_view()
702				.segments()
703				.last()
704				.unwrap()
705				.kind,
706			b"function"
707		);
708	}
709
710	#[test]
711	fn relational_ddl_emits_schemas_columns_constraints_and_column_types() {
712		let g = run(
713			"relational.sql",
714			r#"
715CREATE SCHEMA IF NOT EXISTS Sales;
716CREATE TABLE Sales."Orders" (
717  "ID" uuid CONSTRAINT "Orders_PK" PRIMARY KEY,
718  customer_id uuid NOT NULL,
719  CONSTRAINT orders_customer_fk
720    FOREIGN KEY (customer_id) REFERENCES crm.customers(id)
721);
722"#,
723		);
724		let definitions = def_monikers(&g);
725		assert!(
726			definitions.iter().any(|definition| definition
727				== "code+moniker://app/lang:sql/module:relational/schema:sales")
728		);
729		assert!(definitions.iter().any(|definition| definition
730			== "code+moniker://app/lang:sql/module:relational/schema:sales/table:Orders/column:ID"));
731		assert!(definitions.iter().any(|definition| definition
732			== "code+moniker://app/lang:sql/module:relational/schema:sales/table:Orders/column:customer_id"));
733		assert!(definitions.iter().any(|definition| definition
734			== "code+moniker://app/lang:sql/module:relational/schema:sales/table:Orders/constraint:Orders_PK"));
735		assert!(definitions.iter().any(|definition| definition
736			== "code+moniker://app/lang:sql/module:relational/schema:sales/table:Orders/constraint:orders_customer_fk"));
737		assert_eq!(
738			g.defs()
739				.filter(|definition| definition.kind == b"constraint")
740				.count(),
741			3,
742			"named and anonymous column/table constraints must all be definitions"
743		);
744		let customer = g
745			.defs()
746			.find(|definition| {
747				definition.kind == b"column"
748					&& definition
749						.moniker
750						.as_view()
751						.segments()
752						.last()
753						.is_some_and(|segment| segment.name == b"customer_id")
754			})
755			.expect("customer_id column");
756		assert_eq!(customer.signature, b"uuid");
757		let uses_type = relation_edges(&g, b"uses_type");
758		assert!(uses_type.iter().any(|(source, target)| {
759			source.ends_with("/table:Orders/column:customer_id")
760				&& target == "code+moniker://app/sdk:sql/path:pg_catalog/path:uuid"
761		}));
762		assert!(
763			g.defs()
764				.filter(|definition| matches!(definition.kind.as_ref(), b"column" | b"constraint"))
765				.all(|definition| definition.position.is_some())
766		);
767	}
768
769	#[test]
770	fn foreign_keys_reference_the_target_table_and_available_columns() {
771		let g = run(
772			"foreign_keys.sql",
773			r#"
774CREATE TABLE sales.orders (
775  customer_id uuid,
776  CONSTRAINT orders_customer_fk
777    FOREIGN KEY (customer_id) REFERENCES crm.customers(id)
778);
779"#,
780		);
781		let edges = relation_edges(&g, b"references");
782		let source = "code+moniker://app/lang:sql/module:foreign_keys/schema:sales/table:orders/constraint:orders_customer_fk";
783		assert!(edges.iter().any(|(from, to)| {
784			from == source
785				&& to
786					== "code+moniker://app/lang:sql/module:foreign_keys/schema:crm/table:customers"
787		}));
788		assert!(edges.iter().any(|(from, to)| {
789			from == source
790				&& to
791					== "code+moniker://app/lang:sql/module:foreign_keys/schema:crm/table:customers/column:id"
792		}));
793		assert!(
794			g.refs()
795				.filter(|reference| reference.kind == b"references")
796				.all(|reference| reference.position.is_some())
797		);
798	}
799
800	#[test]
801	fn triggers_reference_relations_and_call_zero_argument_trigger_functions() {
802		let g = run(
803			"triggers.sql",
804			r#"
805CREATE TRIGGER "AuditOrders"
806AFTER INSERT OR UPDATE ON Sales."Orders"
807FOR EACH ROW EXECUTE FUNCTION audit.log_order('orders');
808
809CREATE CONSTRAINT TRIGGER orders_customer_check
810AFTER UPDATE ON Sales."Orders"
811FROM crm.customers
812DEFERRABLE INITIALLY DEFERRED
813FOR EACH ROW EXECUTE PROCEDURE Sales.check_customer();
814
815CREATE TRIGGER route_order_update
816INSTEAD OF UPDATE ON Sales.open_orders
817FOR EACH ROW EXECUTE FUNCTION Sales.route_order_update();
818"#,
819		);
820		let definitions = def_monikers(&g);
821		let audit_trigger = "code+moniker://app/lang:sql/module:triggers/schema:sales/table:Orders/trigger:AuditOrders";
822		let constraint_trigger = "code+moniker://app/lang:sql/module:triggers/schema:sales/table:Orders/trigger:orders_customer_check";
823		let view_trigger = "code+moniker://app/lang:sql/module:triggers/schema:sales/view:open_orders/trigger:route_order_update";
824		assert!(
825			definitions
826				.iter()
827				.any(|definition| definition == audit_trigger)
828		);
829		assert!(
830			definitions
831				.iter()
832				.any(|definition| definition == constraint_trigger)
833		);
834		assert!(
835			definitions
836				.iter()
837				.any(|definition| definition == view_trigger)
838		);
839
840		let references = relation_edges(&g, b"references");
841		assert!(references.iter().any(|(source, target)| {
842			source == audit_trigger
843				&& target == "code+moniker://app/lang:sql/module:triggers/schema:sales/table:Orders"
844		}));
845		assert!(references.iter().any(|(source, target)| {
846			source == constraint_trigger
847				&& target
848					== "code+moniker://app/lang:sql/module:triggers/schema:crm/table:customers"
849		}));
850		assert!(references.iter().any(|(source, target)| {
851			source == view_trigger
852				&& target
853					== "code+moniker://app/lang:sql/module:triggers/schema:sales/view:open_orders"
854		}));
855
856		let calls = relation_edges(&g, b"calls");
857		assert!(calls.iter().any(|(source, target)| {
858			source == audit_trigger
859				&& target
860					== "code+moniker://app/lang:sql/module:triggers/schema:audit/function:log_order()"
861		}));
862		assert!(calls.iter().any(|(source, target)| {
863			source == constraint_trigger
864				&& target
865					== "code+moniker://app/lang:sql/module:triggers/schema:sales/function:check_customer()"
866		}));
867		assert!(calls.iter().any(|(source, target)| {
868			source == view_trigger
869				&& target
870					== "code+moniker://app/lang:sql/module:triggers/schema:sales/function:route_order_update()"
871		}));
872		assert!(
873			g.refs()
874				.filter(|reference| {
875					reference.kind == b"calls"
876						&& (reference.call_name == b"log_order"
877							|| reference.call_name == b"check_customer"
878							|| reference.call_name == b"route_order_update")
879				})
880				.all(|reference| reference.call_arity == Some(0)),
881			"trigger arguments populate TG_ARGV and do not change the trigger function signature"
882		);
883	}
884
885	#[test]
886	fn views_and_routines_read_physical_relations_but_not_ctes_or_aliases() {
887		let g = run(
888			"queries.sql",
889			r#"
890CREATE VIEW sales.open_orders AS
891WITH recent AS (
892  SELECT * FROM sales.orders
893)
894SELECT r.id
895FROM recent r
896JOIN crm.customers c ON c.id = r.customer_id;
897
898CREATE FUNCTION sales.customer_orders()
899RETURNS SETOF sales.orders
900LANGUAGE sql
901SET search_path = sales, pg_temp
902AS $$ SELECT * FROM orders o JOIN crm.customers c ON c.id = o.customer_id $$;
903"#,
904		);
905		let reads = relation_edges(&g, b"reads");
906		assert!(
907			reads.iter().any(|(source, target)| {
908				source.ends_with("/schema:sales/view:open_orders")
909					&& target.ends_with("/schema:sales/table:orders")
910			}),
911			"missing view -> orders read: {reads:?}"
912		);
913		assert!(
914			reads.iter().any(|(source, target)| {
915				source.ends_with("/schema:sales/view:open_orders")
916					&& target.ends_with("/schema:crm/table:customers")
917			}),
918			"missing view -> customers read: {reads:?}"
919		);
920		assert!(reads.iter().any(|(source, target)| {
921			source.contains("/schema:sales/function:customer_orders(")
922				&& target.ends_with("/schema:sales/table:orders")
923		}));
924		assert!(
925			reads
926				.iter()
927				.all(|(_, target)| !target.ends_with("/table:recent")
928					&& !target.ends_with("/table:r")
929					&& !target.ends_with("/table:c")),
930			"CTEs and aliases are not physical relations: {reads:?}"
931		);
932	}
933
934	#[test]
935	fn dml_and_create_table_as_emit_writes_and_select_reads() {
936		let g = run(
937			"mutations.sql",
938			r#"
939INSERT INTO audit.events SELECT * FROM sales.orders;
940UPDATE sales.orders o SET customer_id = c.id FROM crm.customers c;
941DELETE FROM sales.orders o USING crm.customers c;
942CREATE TABLE sales.orders_copy AS SELECT * FROM sales.orders;
943"#,
944		);
945		let writes = relation_edges(&g, b"writes");
946		assert!(writes.iter().any(|(source, target)| {
947			source.ends_with("/module:mutations") && target.ends_with("/schema:audit/table:events")
948		}));
949		assert!(writes.iter().any(|(source, target)| {
950			source.ends_with("/module:mutations") && target.ends_with("/schema:sales/table:orders")
951		}));
952		assert!(writes.iter().any(|(source, target)| {
953			source.ends_with("/module:mutations")
954				&& target.ends_with("/schema:sales/table:orders_copy")
955		}));
956		let reads = relation_edges(&g, b"reads");
957		assert!(
958			reads
959				.iter()
960				.any(|(_, target)| { target.ends_with("/schema:sales/table:orders") })
961		);
962		assert!(
963			reads
964				.iter()
965				.any(|(_, target)| { target.ends_with("/schema:crm/table:customers") })
966		);
967	}
968
969	#[test]
970	fn plpgsql_static_statements_are_relational_but_dynamic_sql_is_not_certain() {
971		let g = run(
972			"procedures.sql",
973			r#"
974CREATE PROCEDURE sales.refresh(target_table text)
975LANGUAGE plpgsql AS $$
976BEGIN
977  INSERT INTO audit.events SELECT * FROM sales.orders;
978  EXECUTE 'INSERT INTO audit.' || quote_ident(target_table) || ' SELECT * FROM sales.orders';
979END;
980$$;
981"#,
982		);
983		let writes = relation_edges(&g, b"writes");
984		assert_eq!(
985			writes
986				.iter()
987				.filter(|(_, target)| target.ends_with("/schema:audit/table:events"))
988				.count(),
989			1,
990			"only the static statement may claim the concrete write: {writes:?}"
991		);
992		let reads = relation_edges(&g, b"reads");
993		assert_eq!(
994			reads
995				.iter()
996				.filter(|(_, target)| target.ends_with("/schema:sales/table:orders"))
997				.count(),
998			1,
999			"dynamic SQL text must not fabricate a second certain read: {reads:?}"
1000		);
1001	}
1002}
code_moniker_core/lang/sql/mod.rs

code_moniker_core/lang/sql/
mod.rs
