weavatrix_parse/
sql.rs

1//! Structural extraction for SQL.
2//!
3//! SQL declares objects rather than functions and depends on other objects by
4//! name rather than by path, so the fact shapes mean something slightly
5//! different here: a `CREATE` is a declaration, and every table a statement
6//! reads or writes is an import whose specifier is the object name. That is
7//! what makes a view resolvable to the file that creates the table it selects
8//! from, which is the edge repository intelligence actually wants.
9//!
10//! Keywords are matched case-insensitively because SQL is written both ways,
11//! often in the same file.
12
13use crate::facts::{Declaration, DeclarationKind, Facts, Import, Reference, ReferenceKind, Span};
14use crate::syntax::Language;
15use crate::token::{Mode, Token, TokenKind, Tokenizer};
16
17/// Extracts structural facts from one SQL source.
18#[must_use]
19pub fn extract(source: &str) -> Facts {
20    let tokens = Tokenizer::new(source, Language::Sql)
21        .mode(Mode::Lite)
22        .collect::<Vec<_>>();
23    let mut state = Extractor {
24        source,
25        tokens: &tokens,
26        facts: Facts::default(),
27        object: None,
28    };
29    state.run();
30    state.facts
31}
32
33/// The object keyword to the kind it declares.
34const OBJECTS: &[(&str, DeclarationKind)] = &[
35    ("table", DeclarationKind::Table),
36    ("view", DeclarationKind::View),
37    ("function", DeclarationKind::Function),
38    ("procedure", DeclarationKind::Procedure),
39    ("trigger", DeclarationKind::Procedure),
40    ("schema", DeclarationKind::Module),
41    ("type", DeclarationKind::TypeAlias),
42];
43
44/// Words that qualify a `CREATE` without naming what it creates.
45const CREATE_MODIFIERS: &[&str] = &[
46    "or",
47    "replace",
48    "temp",
49    "temporary",
50    "unique",
51    "materialized",
52    "global",
53    "local",
54    "if",
55    "not",
56    "exists",
57];
58
59/// Keywords a referenced object name follows.
60const REFERENCES: &[&str] = &["from", "join", "into", "update", "references", "on"];
61
62/// Words that read as an object name but never are one.
63const NOT_A_NAME: &[&str] = &[
64    "select",
65    "lateral",
66    "only",
67    "delete",
68    "conflict",
69    "duplicate",
70    "set",
71    "values",
72    "all",
73    "distinct",
74];
75
76struct Extractor<'source, 'tokens> {
77    source: &'source str,
78    tokens: &'tokens [Token],
79    facts: Facts,
80    /// The object being created, which owns everything until the statement ends.
81    object: Option<String>,
82}
83
84impl Extractor<'_, '_> {
85    fn run(&mut self) {
86        let mut index = 0;
87        while index < self.tokens.len() {
88            index = self.step(index);
89        }
90    }
91
92    fn text(&self, index: usize) -> &str {
93        self.tokens
94            .get(index)
95            .map_or("", |token| token.text(self.source))
96    }
97
98    fn kind(&self, index: usize) -> Option<TokenKind> {
99        self.tokens.get(index).map(|token| token.kind)
100    }
101
102    fn word(&self, index: usize, keyword: &str) -> bool {
103        self.kind(index) == Some(TokenKind::Identifier)
104            && self.text(index).eq_ignore_ascii_case(keyword)
105    }
106
107    fn any_word(&self, index: usize, keywords: &[&str]) -> bool {
108        self.kind(index) == Some(TokenKind::Identifier)
109            && keywords
110                .iter()
111                .any(|keyword| self.text(index).eq_ignore_ascii_case(keyword))
112    }
113
114    fn punct(&self, index: usize, mark: &str) -> bool {
115        self.kind(index) == Some(TokenKind::Punctuation) && self.text(index) == mark
116    }
117
118    fn span(&self, start: usize, end: usize) -> Span {
119        let last_index = self.tokens.len().saturating_sub(1);
120        let first = &self.tokens[start.min(last_index)];
121        let last = &self.tokens[end.min(last_index)];
122        Span {
123            start: first.start,
124            end: last.end,
125            line: first.line,
126            column: first.column,
127            end_line: last.line,
128            end_column: last.column,
129        }
130    }
131
132    fn step(&mut self, index: usize) -> usize {
133        if self.punct(index, ";") {
134            self.object = None;
135            return index + 1;
136        }
137        if self.kind(index) != Some(TokenKind::Identifier) {
138            return index + 1;
139        }
140        if (self.word(index, "create") || self.word(index, "alter"))
141            && let Some(next) = self.create(index)
142        {
143            return next;
144        }
145        if self.any_word(index, REFERENCES)
146            && let Some(next) = self.reference(index)
147        {
148            return next;
149        }
150        if let Some(next) = self.call(index) {
151            return next;
152        }
153        index + 1
154    }
155
156    /// Reads a possibly qualified name: `schema.table`, `"quoted".name`.
157    /// Returns the joined name and the index after it.
158    fn qualified_name(&self, start: usize) -> Option<(String, usize)> {
159        if self.kind(start) != Some(TokenKind::Identifier)
160            && self.kind(start) != Some(TokenKind::String)
161        {
162            return None;
163        }
164        if self.any_word(start, NOT_A_NAME) {
165            return None;
166        }
167        let mut name = self
168            .text(start)
169            .trim_matches(['"', '`', '[', ']'])
170            .to_owned();
171        let mut cursor = start + 1;
172        while self.punct(cursor, ".") && self.kind(cursor + 1) == Some(TokenKind::Identifier) {
173            name.push('.');
174            name.push_str(self.text(cursor + 1).trim_matches(['"', '`', '[', ']']));
175            cursor += 2;
176        }
177        Some((name, cursor))
178    }
179
180    /// `CREATE [OR REPLACE] TABLE [IF NOT EXISTS] name`, and the `ALTER TABLE
181    /// name` form, which references an existing object rather than declaring one.
182    fn create(&mut self, index: usize) -> Option<usize> {
183        let altering = self.word(index, "alter");
184        let mut cursor = index + 1;
185        // `CREATE INDEX name ON table` declares nothing worth a node, but the
186        // table it indexes is a real dependency, so it falls through to the
187        // reference path via its `ON`.
188        while self.any_word(cursor, CREATE_MODIFIERS) {
189            cursor += 1;
190        }
191        let keyword = self.text(cursor);
192        let (_, kind) = OBJECTS
193            .iter()
194            .find(|(word, _)| keyword.eq_ignore_ascii_case(word))?;
195        cursor += 1;
196        while self.any_word(cursor, CREATE_MODIFIERS) {
197            cursor += 1;
198        }
199        let (name, after) = self.qualified_name(cursor)?;
200        if altering {
201            self.facts.imports.push(Import {
202                specifier: name,
203                span: self.span(index, after.saturating_sub(1)),
204                type_only: false,
205                reexport: false,
206                names: Vec::new(),
207                bindings: Vec::new(),
208            });
209            return Some(after);
210        }
211        self.facts.declarations.push(Declaration {
212            name: name.clone(),
213            kind: *kind,
214            span: self.span(index, after.saturating_sub(1)),
215            owner: None,
216            // Every SQL object is visible to whoever can reach the schema.
217            exported: true,
218        });
219        self.object = Some(name);
220        Some(after)
221    }
222
223    /// The object names after `FROM`, `JOIN`, `INTO`, `UPDATE`, `REFERENCES`
224    /// and `ON`, including comma-separated lists.
225    fn reference(&mut self, index: usize) -> Option<usize> {
226        let mut cursor = index + 1;
227        // A parenthesised subquery is read on its own, and `ON` outside a
228        // `CREATE INDEX` introduces a join condition rather than a table.
229        if self.punct(cursor, "(") {
230            return None;
231        }
232        if self.word(index, "on") && !self.creating_index(index) {
233            return None;
234        }
235        // Whether the statement reads the object or writes it is the whole
236        // point of the edge: a report that reads a table and a job that
237        // rewrites it are not the same dependency.
238        let writing = self.word(index, "into") || self.word(index, "update");
239        let mut recorded = 0_usize;
240        while let Some((name, after)) = self.qualified_name(cursor) {
241            self.facts.references.push(Reference {
242                name: name.clone(),
243                kind: if writing {
244                    ReferenceKind::Writes
245                } else {
246                    ReferenceKind::Reads
247                },
248                receiver: None,
249                span: self.span(cursor, after.saturating_sub(1)),
250                owner: self.object.clone(),
251                string_arguments: Vec::new(),
252                name_arguments: Vec::new(),
253            });
254            self.facts.imports.push(Import {
255                specifier: name,
256                span: self.span(cursor, after.saturating_sub(1)),
257                type_only: false,
258                reexport: false,
259                names: Vec::new(),
260                bindings: Vec::new(),
261            });
262            recorded += 1;
263            cursor = after;
264            // `FROM a, b` lists several tables; an alias before the comma is
265            // skipped because only the first name in an item is the object.
266            while self.kind(cursor) == Some(TokenKind::Identifier) && !self.punct(cursor, ",") {
267                if self.any_word(cursor, REFERENCES) || self.punct(cursor, ";") {
268                    break;
269                }
270                cursor += 1;
271            }
272            if !self.punct(cursor, ",") {
273                break;
274            }
275            cursor += 1;
276        }
277        (recorded > 0).then_some(cursor)
278    }
279
280    /// Whether this `ON` belongs to a `CREATE INDEX ... ON table` statement.
281    fn creating_index(&self, index: usize) -> bool {
282        let start = index.saturating_sub(8);
283        (start..index).any(|cursor| {
284            self.word(cursor, "index") && (start..cursor).any(|back| self.word(back, "create"))
285        })
286    }
287
288    fn call(&mut self, index: usize) -> Option<usize> {
289        if !self.punct(index + 1, "(") {
290            return None;
291        }
292        let name = self.text(index).to_owned();
293        // A type name or a clause keyword can be followed by a parenthesis
294        // without being a call.
295        if self.any_word(index, NOT_A_NAME)
296            || self.any_word(
297                index,
298                &[
299                    "varchar",
300                    "char",
301                    "decimal",
302                    "numeric",
303                    "in",
304                    "values",
305                    "table",
306                    "on",
307                    "using",
308                    "check",
309                    "primary",
310                    "foreign",
311                    "key",
312                    "references",
313                    "unique",
314                    "index",
315                ],
316            )
317        {
318            return None;
319        }
320        let mut arguments = Vec::new();
321        let mut scan = index + 2;
322        let mut depth = 1_i32;
323        let limit = (index + 256).min(self.tokens.len());
324        while scan < limit && depth > 0 {
325            if self.punct(scan, "(") {
326                depth += 1;
327            } else if self.punct(scan, ")") {
328                depth -= 1;
329            } else if depth == 1 && self.kind(scan) == Some(TokenKind::String) {
330                arguments.push(self.text(scan).trim_matches('\'').to_owned());
331            }
332            scan += 1;
333        }
334        self.facts.references.push(Reference {
335            kind: ReferenceKind::Call,
336            name,
337            receiver: None,
338            span: self.span(index, index),
339            owner: self.object.clone(),
340            string_arguments: arguments,
341            name_arguments: Vec::new(),
342        });
343        Some(index + 1)
344    }
345}
346
347#[cfg(test)]
348mod tests {
349    use super::extract;
350    use crate::facts::DeclarationKind;
351
352    fn specifiers(source: &str) -> Vec<String> {
353        extract(source)
354            .imports
355            .into_iter()
356            .map(|import| import.specifier)
357            .collect()
358    }
359
360    #[test]
361    fn created_objects_carry_their_kind() {
362        let source = "CREATE TABLE app.users (id INT);\n\
363             create or replace view active_users as select 1;\n\
364             CREATE OR REPLACE FUNCTION bump() RETURNS INT AS 'select 1';\n";
365        let declared = extract(source)
366            .declarations
367            .into_iter()
368            .map(|item| (item.name, item.kind))
369            .collect::<Vec<_>>();
370        assert_eq!(
371            declared,
372            [
373                ("app.users".to_owned(), DeclarationKind::Table),
374                ("active_users".to_owned(), DeclarationKind::View),
375                ("bump".to_owned(), DeclarationKind::Function),
376            ],
377            "keywords are matched whichever case they are written in"
378        );
379    }
380
381    #[test]
382    fn a_view_depends_on_every_table_it_reads() {
383        let source = "CREATE VIEW report AS\n\
384             SELECT o.id\n\
385             FROM orders o\n\
386             JOIN app.customers c ON c.id = o.customer_id\n\
387             LEFT JOIN payments ON payments.order_id = o.id;\n";
388        assert_eq!(
389            specifiers(source),
390            ["orders", "app.customers", "payments"],
391            "an ON inside a join is a condition, not another table"
392        );
393    }
394
395    #[test]
396    fn writes_and_alterations_are_dependencies_too() {
397        let source = "INSERT INTO events (id) VALUES (1);\n\
398             UPDATE accounts SET balance = 0;\n\
399             ALTER TABLE accounts ADD COLUMN note TEXT;\n\
400             CREATE INDEX idx_events_id ON events (id);\n";
401        assert_eq!(
402            specifiers(source),
403            ["events", "accounts", "accounts", "events"],
404            "an ON after CREATE INDEX names the indexed table"
405        );
406    }
407
408    #[test]
409    fn a_comment_or_string_never_declares_a_table() {
410        let source = "-- CREATE TABLE ghost (id INT);\n\
411             /* CREATE TABLE also_ghost (id INT); */\n\
412             INSERT INTO log (message) VALUES ('select * from phantom');\n\
413             CREATE TABLE real_one (id INT);\n";
414        let facts = extract(source);
415        assert_eq!(
416            facts
417                .declarations
418                .iter()
419                .map(|item| item.name.as_str())
420                .collect::<Vec<_>>(),
421            ["real_one"]
422        );
423        assert_eq!(
424            specifiers(source),
425            ["log"],
426            "the table named inside the string literal is text"
427        );
428    }
429
430    #[test]
431    fn a_comma_separated_from_list_names_every_table() {
432        assert_eq!(
433            specifiers("SELECT * FROM users u, orders o, app.items;"),
434            ["users", "orders", "app.items"],
435            "an alias is not a table"
436        );
437    }
438}
weavatrix_parse/sql.rs

weavatrix_parse/
sql.rs
