Coverage for datakiln/metadata/script.py: 18%
179 statements
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
1"""
2SQL script metadata.
3"""
4import re
5from hashlib import md5
6from pathlib import Path
7from typing import List, Set, Type, Union
9import sqlglot
10from more_itertools import sliding_window
11from sqlglot import exp
12from sqlglot.dialects.dialect import Dialect
13from sqlglot.errors import ParseError, SqlglotError
14from sqlglot.parser import Parser
15from sqlglot.tokens import Token, TokenType
17from datakiln.metadata.dialect import ExtendedPostgres
18from datakiln.metadata.table import Script
21PUBLIC = "public"
22FUNCTION_SCHEMA = (
23 "functions and procedures cannot specify a schema, they use the default "
24 "one."
25)
28class ScriptError(ValueError):
29 """Error in a script."""
31 def __init__(self, path: Path, message: str):
32 super().__init__(f"Script '{path}': {message}")
35class ScriptMeta:
36 # pylint: disable=too-few-public-methods, too-many-instance-attributes
37 """SQL script and the database objects it creates and uses."""
39 _functions = {"FUNCTION", "PROCEDURE", "AGGREGATE"}
41 # Statement kinds that create an object other statements can reference
42 # (materialized views are parsed as views)
43 _creates = {
44 "TABLE", "VIEW", "INDEX", "TYPE", "SEQUENCE", "SCHEMA"
45 } | _functions
47 # Tokens after which sqlglot makes the rest of the statement one string
48 _commands = {TokenType.COMMAND, TokenType.EXECUTE}
50 def __init__(self, path: Path, sql: str,
51 dialect: Union[str, Type[Dialect]] = ExtendedPostgres,
52 external: bool = False):
53 """
54 :param path: Path of the script.
55 :type path: Path
57 :param sql: Content of the script.
58 :type sql: str
60 :param dialect: SQLGlot dialect of the script, by name or class.
61 :type dialect: str | type[sqlglot.dialects.dialect.Dialect]
63 :param external: Whether another repo builds the script, which is in
64 the dependencies folder.
65 :type external: bool
66 """
67 self.path = Path(path)
68 self.sql = sql
69 self.checksum = md5(sql.encode()).hexdigest() # noqa: DUO130
70 self.dialect = dialect
71 self.external = external
72 self.full_name = ""
73 self.drop = ""
74 self.requires: Set[str] = set()
76 for statement in self._parse(sql):
77 if statement is not None:
78 self._inspect(statement)
80 if not self.full_name:
81 raise ScriptError(self.path, "it must create one object.")
82 self.requires.discard(self.full_name)
84 @classmethod
85 def from_sql(cls, path: Path, directory: Path,
86 dialect: Union[str, Type[Dialect]] = ExtendedPostgres,
87 external: bool = False) -> 'ScriptMeta':
88 """Load script metadata from a SQL file.
90 :param path: Path of the script.
91 :type path: Path
93 :param directory: Directory the script path is relative to.
94 :type directory: Path
96 :param dialect: SQLGlot dialect of the script, by name or class.
97 :type dialect: str | type[sqlglot.dialects.dialect.Dialect]
99 :param external: Whether another repo builds the script.
100 :type external: bool
102 :return: Script metadata.
103 :rtype: ScriptMeta
104 """
105 with open(path, "r", encoding="utf-8") as file:
106 return cls(path.relative_to(directory), file.read(), dialect,
107 external=external)
109 def record(self, session):
110 """Record of the script in the metadata, if it was built.
112 :param session: Database Session.
113 :type session: datakiln.metadata.database.Session
115 :rtype: datakiln.metadata.table.Script
116 """
117 return session.get(Script, self.full_name)
119 def _parse(self, sql: str) -> List[exp.Expression]:
120 """Parse SQL, reporting the syntax that sqlglot cannot parse.
122 The SQL is read in lowercase, so that all the names are, since the
123 database folds unquoted names. It is only read, not executed.
124 """
125 try:
126 return sqlglot.parse(sql.lower(), read=self.dialect)
127 except ParseError as error:
128 message = "it has unsupported syntax."
129 raise ScriptError(self.path, message) from error
131 def _body_names(self, text: str) -> Set[str]:
132 """Returns the objects used in the body of a function: schema.name
133 pairs and the names of called functions, and what the strings in it
134 need.
136 Comments are not tokens, so they are skipped. Built-in functions and
137 words that look like calls, such as the statements of a procedural
138 language, are returned too and match no object.
139 """
140 names = set()
142 # A token is read with its neighbors, so the first and the last, as
143 # a string that is all the text, have a stand-in for each
144 stop = Token(TokenType.SEMICOLON, ";")
145 tokens = [stop, *sqlglot.tokenize(text, read=self.dialect), stop]
147 for left, center, right in sliding_window(tokens, 3):
148 match (left.token_type, center.token_type, right.token_type):
149 # `s.f(`: a call that names a schema, which is not allowed
150 case (TokenType.DOT, TokenType.VAR, TokenType.L_PAREN):
151 raise ScriptError(self.path, FUNCTION_SCHEMA)
152 # `f(`: a call, which is named by the token before the
153 # parenthesis
154 case (TokenType.VAR, TokenType.L_PAREN, _):
155 names.add(left.text)
156 # `EXECUTE '...'`: what follows CALL, DECLARE... or EXECUTE is
157 # one string token, whose text is code to read in turn
158 case (cmd, TokenType.STRING, _) if cmd in self._commands:
159 names |= self._body_names(center.text)
160 # Any other string, such as a query given to a function, which
161 # is either SQL or only text
162 case (_, TokenType.STRING, _):
163 names |= self._text(center.text)
164 # `s.t`: an object of a schema
165 case (l, TokenType.DOT, r) if {l, r} <= Parser.ID_VAR_TOKENS:
166 names.add(f"{left.text}.{right.text}")
167 return names
169 def _drop(self, statement: exp.Create) -> str:
170 """Returns the statement that drops the object a statement creates,
171 along with whatever depends on it."""
172 # A schema is only dropped if it is empty
173 if statement.kind == "SCHEMA":
174 return ""
176 kind = statement.kind
177 if statement.find(exp.MaterializedProperty):
178 kind = f"MATERIALIZED {kind}"
180 drop = f"DROP {kind} IF EXISTS {self.full_name} CASCADE"
181 return sqlglot.parse_one(drop, read=self.dialect).sql(self.dialect)
183 def _create(self, statement: exp.Create, kind: str):
184 """Register the object created by a statement."""
185 if self.full_name:
186 raise ScriptError(self.path, "it creates more than one object.")
188 if kind == "SCHEMA":
189 # A schema is named by itself, and the objects in it require it
190 self.full_name = statement.this.db
191 return
193 if kind == "INDEX":
194 index = statement.this
195 if not index.name:
196 raise ScriptError(self.path, "indexes must be named.")
198 # An index is named in the schema of its table
199 self.full_name = f"{index.args['table'].db}.{index.name}"
200 self.requires.add(index.args["table"].db)
201 return
203 if kind in self._functions:
204 # Functions are created in the default schema
205 function = statement.this.find(exp.Table)
206 if function.db:
207 raise ScriptError(self.path, FUNCTION_SCHEMA)
209 # Overloads are told apart by the types of their parameters
210 types = []
211 for param in statement.this.expressions:
212 # OUT parameters are results, not part of the signature
213 mode = param.find(exp.InOutColumnConstraint)
214 if mode and mode.sql() == "OUT":
215 continue
217 # A named parameter is a column definition with a type, and
218 # an unnamed one is the type itself
219 dtype = param
220 if isinstance(param, exp.ColumnDef):
221 dtype = param.args["kind"]
222 types.append(dtype.sql(self.dialect).lower())
224 # A function is named by its name and the types of its parameters
225 self.full_name = f"{function.name}({', '.join(types)})"
226 return
228 # The table may be wrapped, e.g. by its column definitions
229 table = statement.this.find(exp.Table)
231 # Anything else is named by its schema and its name
232 self.full_name = f"{table.db}.{table.name}"
233 self.requires.add(table.db)
235 def _references(self, statement: exp.Expression) -> Set[str]:
236 """Returns the tables and functions referenced by a statement."""
237 names = set()
238 ctes = {cte.alias for cte in statement.find_all(exp.CTE)}
239 for table in statement.find_all(exp.Table):
240 # A function called in FROM has no name, and a name without a
241 # schema may be defined by a WITH clause
242 if not table.name or (not table.db and table.name in ctes):
243 continue
245 if table.db in ("", PUBLIC):
246 raise ScriptError(self.path, "it needs a non-public schema.")
247 names |= {table.db, f"{table.db}.{table.name}"}
249 # Calls to functions, which are named without a schema. A user
250 # function can be named like one that sqlglot knows, so those calls
251 # are needed too.
252 for call in statement.find_all(exp.Func):
253 name = call.sql_name().lower()
254 if isinstance(call, exp.Anonymous):
255 parent = call.parent
256 if isinstance(parent, exp.Dot) or getattr(parent, "db", ""):
257 raise ScriptError(self.path, FUNCTION_SCHEMA)
258 name = call.name
259 names.add(name)
261 # A string can hold a query that the object runs, as the ones a
262 # crosstab takes, which is only text to sqlglot
263 for text in statement.find_all(exp.Heredoc, exp.RawString,
264 exp.Literal):
265 if not isinstance(text, exp.Literal) or text.is_string:
266 names |= self._text(text.name)
267 return names
269 def _text(self, text: str) -> Set[str]:
270 """Returns what text that holds code needs: a query that runs, or
271 something that is only code to read. It is read as SQL if it parses
272 as a query, and by its tokens if not, with the same rules as the code
273 of the script.
274 """
275 names = set()
276 try:
277 for statement in sqlglot.parse(text, read=self.dialect):
278 # An empty statement is None
279 if statement is None:
280 continue
281 if not isinstance(statement, (exp.Query, exp.DML)):
282 break
283 names |= self._references(statement)
284 else:
285 return names
286 except SqlglotError:
287 pass
289 # Text that cannot be tokenized, such as a word with a quote in it,
290 # is not code
291 try:
292 return self._body_names(text)
293 except SqlglotError:
294 return set()
296 def _body(self, statement: exp.Create):
297 """Collect what the body of a statement needs."""
298 body = statement.args.get("expression")
300 # The body of a function is quoted text ($$...$$ or a string
301 # literal), which sqlglot does not parse, so it is read by its
302 # tokens.
303 if isinstance(body, (exp.Heredoc, exp.Literal)):
304 self.requires |= self._body_names(body.name)
306 # A list of options in place of a body (`name = value`, as an
307 # aggregate has), whose values name the functions and the types that
308 # the object needs.
309 elif isinstance(body, exp.Tuple):
310 for option in body.find_all(exp.EQ):
311 for name in option.expression.find_all(exp.Column):
312 self.requires.add(name.sql(self.dialect))
314 elif statement.kind in self._functions:
315 raise ScriptError(self.path, "it has unsupported syntax.")
317 # Anything else is a tree, which is walked.
318 else:
319 self.requires |= self._references(statement)
321 def _inspect(self, statement: exp.Expression):
322 """Collect the objects created and referenced by a statement."""
323 # Dropping an object does not require it to exist
324 if isinstance(statement, exp.Drop):
325 return
327 # A definition that sqlglot cannot parse is kept as text
328 if isinstance(statement, exp.Command):
329 # The signature ends at the last ")" before the first AS, and the
330 # expression is the literal that follows AS, up to its closing
331 # $ or ', which leaves out any option that comes after it. What is
332 # between them has the return type and the options.
333 pattern = r"(.*?\))([^)]*?)\bAS\b\s*(.*[$'])"
334 found = re.match(pattern, statement.expression, re.I | re.S)
335 if not found:
336 raise ScriptError(self.path, "it has unsupported syntax.")
338 # What is left between them is what sqlglot cannot parse
339 signature, between, expression = found.groups()
340 self.requires |= self._body_names(between)
341 sql = f"CREATE {signature} AS {expression}"
342 statement = self._parse(sql)[0]
344 # If it still cannot be parsed, the rest was not what stopped it
345 if isinstance(statement, exp.Command):
346 raise ScriptError(self.path, "it has unsupported syntax.")
348 # Nothing but the creation of an object is allowed
349 if not isinstance(statement, exp.Create):
350 raise ScriptError(self.path, "it must create one object.")
352 # Only some of the objects that can be created are tracked
353 if statement.kind not in self._creates:
354 kind = statement.kind.lower()
355 raise ScriptError(self.path, f"{kind}s are not supported.")
357 self._create(statement, statement.kind)
358 self.drop = self._drop(statement)
360 # The type of a parameter or of the result can be a table
361 for datatype in statement.find_all(exp.DataType):
362 kind = datatype.args.get("kind")
363 if isinstance(kind, exp.Dot):
364 self.requires.add(f"{kind.this.name}.{kind.expression.name}")
366 self._body(statement)