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

1""" 

2SQL script metadata. 

3""" 

4import re 

5from hashlib import md5 

6from pathlib import Path 

7from typing import List, Set, Type, Union 

8 

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 

16 

17from datakiln.metadata.dialect import ExtendedPostgres 

18from datakiln.metadata.table import Script 

19 

20 

21PUBLIC = "public" 

22FUNCTION_SCHEMA = ( 

23 "functions and procedures cannot specify a schema, they use the default " 

24 "one." 

25) 

26 

27 

28class ScriptError(ValueError): 

29 """Error in a script.""" 

30 

31 def __init__(self, path: Path, message: str): 

32 super().__init__(f"Script '{path}': {message}") 

33 

34 

35class ScriptMeta: 

36 # pylint: disable=too-few-public-methods, too-many-instance-attributes 

37 """SQL script and the database objects it creates and uses.""" 

38 

39 _functions = {"FUNCTION", "PROCEDURE", "AGGREGATE"} 

40 

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 

46 

47 # Tokens after which sqlglot makes the rest of the statement one string 

48 _commands = {TokenType.COMMAND, TokenType.EXECUTE} 

49 

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 

56 

57 :param sql: Content of the script. 

58 :type sql: str 

59 

60 :param dialect: SQLGlot dialect of the script, by name or class. 

61 :type dialect: str | type[sqlglot.dialects.dialect.Dialect] 

62 

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() 

75 

76 for statement in self._parse(sql): 

77 if statement is not None: 

78 self._inspect(statement) 

79 

80 if not self.full_name: 

81 raise ScriptError(self.path, "it must create one object.") 

82 self.requires.discard(self.full_name) 

83 

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. 

89 

90 :param path: Path of the script. 

91 :type path: Path 

92 

93 :param directory: Directory the script path is relative to. 

94 :type directory: Path 

95 

96 :param dialect: SQLGlot dialect of the script, by name or class. 

97 :type dialect: str | type[sqlglot.dialects.dialect.Dialect] 

98 

99 :param external: Whether another repo builds the script. 

100 :type external: bool 

101 

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) 

108 

109 def record(self, session): 

110 """Record of the script in the metadata, if it was built. 

111 

112 :param session: Database Session. 

113 :type session: datakiln.metadata.database.Session 

114 

115 :rtype: datakiln.metadata.table.Script 

116 """ 

117 return session.get(Script, self.full_name) 

118 

119 def _parse(self, sql: str) -> List[exp.Expression]: 

120 """Parse SQL, reporting the syntax that sqlglot cannot parse. 

121 

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 

130 

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. 

135 

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() 

141 

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] 

146 

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 

168 

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 "" 

175 

176 kind = statement.kind 

177 if statement.find(exp.MaterializedProperty): 

178 kind = f"MATERIALIZED {kind}" 

179 

180 drop = f"DROP {kind} IF EXISTS {self.full_name} CASCADE" 

181 return sqlglot.parse_one(drop, read=self.dialect).sql(self.dialect) 

182 

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.") 

187 

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 

192 

193 if kind == "INDEX": 

194 index = statement.this 

195 if not index.name: 

196 raise ScriptError(self.path, "indexes must be named.") 

197 

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 

202 

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) 

208 

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 

216 

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()) 

223 

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 

227 

228 # The table may be wrapped, e.g. by its column definitions 

229 table = statement.this.find(exp.Table) 

230 

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) 

234 

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 

244 

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}"} 

248 

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) 

260 

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 

268 

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 

288 

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() 

295 

296 def _body(self, statement: exp.Create): 

297 """Collect what the body of a statement needs.""" 

298 body = statement.args.get("expression") 

299 

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) 

305 

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)) 

313 

314 elif statement.kind in self._functions: 

315 raise ScriptError(self.path, "it has unsupported syntax.") 

316 

317 # Anything else is a tree, which is walked. 

318 else: 

319 self.requires |= self._references(statement) 

320 

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 

326 

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.") 

337 

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] 

343 

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.") 

347 

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.") 

351 

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.") 

356 

357 self._create(statement, statement.kind) 

358 self.drop = self._drop(statement) 

359 

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}") 

365 

366 self._body(statement)