Coverage for datakiln/metadata/database.py: 52%

229 statements  

« prev     ^ index     » next       coverage.py v7.17.3, created at 2026-11-16 23:23 +0000

1""" 

2Database metadata. 

3""" 

4import logging 

5from pathlib import Path 

6from getpass import getuser 

7from textwrap import dedent 

8from functools import cached_property 

9 

10import sqlglot 

11import sqlparse 

12from sqlglot import exp 

13from sqlalchemy.orm.session import Session as SQLAlchemySession 

14from sqlalchemy import ( 

15 create_engine, inspect, select, text, URL, event, Engine 

16) 

17from sqlalchemy.exc import DBAPIError, ProgrammingError 

18from sqlalchemy.schema import CreateSchema 

19from decouple import Config, RepositoryEmpty, RepositoryEnv 

20from sqlalchemy_utils import create_database, database_exists 

21 

22from datakiln.metadata.table import TableMeta, Object, Script, Base 

23from datakiln.metadata.dialect import DIALECT_MAP 

24from datakiln.metadata.files import DataFiles 

25from datakiln.metadata.graph import DatabaseGraph 

26from datakiln.metadata.migrations import upgrade 

27from datakiln.metadata.script import ScriptMeta 

28 

29 

30DRIVER = { 

31 "postgresql": "psycopg2", 

32 "oracle": "oracledb" 

33} 

34logger = logging.getLogger(__name__) 

35 

36 

37class DatabaseMeta(): 

38 """Database metadata.""" 

39 

40 def __init__(self, directory, name): 

41 """ 

42 :param directory: Path for metadata directory. 

43 :type directory: str 

44 

45 :param name: Name of the database. 

46 :type name: str 

47 """ 

48 self.directory = Path(directory) 

49 self.name = name 

50 

51 # Objects built before that are no longer in the repo 

52 self.stale = [] 

53 

54 # Retrieve files to ignore 

55 self.ignore = [] 

56 if (path := self.directory / ".dataignore").exists(): 

57 with path.open("r", encoding="utf-8") as file: 

58 self.ignore = file.read().splitlines() 

59 

60 def _files(self, directory, pattern): 

61 """Retrieve files in a subdirectory, skipping the ignored ones. 

62 

63 :param directory: Subdirectory of the metadata directory. 

64 :type directory: str 

65 

66 :param pattern: Glob pattern of the files. 

67 :type pattern: str 

68 

69 :return: Paths of the files, sorted. 

70 :rtype: Iterator[pathlib.Path] 

71 """ 

72 for path in sorted((self.directory / directory).glob(pattern)): 

73 if str(path.relative_to(self.directory)) not in self.ignore: 

74 yield path 

75 

76 @cached_property 

77 def tables(self): 

78 """Retrieve database tables, in the order of their files. 

79 

80 :return: Table metadata. 

81 :rtype: list[datakiln.metadata.table.TableMeta] 

82 """ 

83 data_files = DataFiles(self.directory / "data") 

84 

85 tables = [] 

86 for path in self._files("metadata", "*.json"): 

87 tables.append(TableMeta.from_json(path, data_files)) 

88 

89 return tables 

90 

91 @cached_property 

92 def scripts(self): 

93 """Retrieve the derive scripts. 

94 

95 :return: Script metadata. 

96 :rtype: list[datakiln.metadata.script.ScriptMeta] 

97 """ 

98 scripts = [] 

99 for path in self._files("derive", "**/*.sql"): 

100 scripts.append( 

101 ScriptMeta.from_sql(path, self.directory, self.dialect)) 

102 return scripts 

103 

104 @cached_property 

105 def dialect(self): 

106 """SQL dialect, which is set in the environment of the database.""" 

107 dialect = self.load_env(self.directory, required=False)["dialect"] 

108 return DIALECT_MAP.get(dialect, dialect) 

109 

110 @cached_property 

111 def external(self): 

112 """Retrieve the tables and scripts of the repos in the dependencies 

113 folder, which those repos build. They have no data files here. 

114 

115 :return: Tables and scripts, marked as external. 

116 :rtype: list 

117 """ 

118 external = [] 

119 dependencies = self.directory / "dependencies" 

120 for path in sorted(dependencies.glob("**/metadata/*.json")): 

121 external.append(TableMeta.from_json(path, external=True)) 

122 for path in sorted(dependencies.glob("**/derive/**/*.sql")): 

123 external.append(ScriptMeta.from_sql( 

124 path, self.directory, self.dialect, external=True)) 

125 return external 

126 

127 @cached_property 

128 def graph(self): 

129 """Dependency graph of the tables (metadata) and scripts (derive), 

130 which gives them in the order they depend on one another. The objects 

131 of the dependencies folder are in it, so they can be used, but they 

132 are not built. 

133 

134 :return: Dependency graph. 

135 :rtype: datakiln.metadata.graph.DatabaseGraph 

136 """ 

137 return DatabaseGraph(self.tables, self.scripts, self.external) 

138 

139 def grant(self, session, users=None): 

140 """Grants read on the objects of the schemas of the tables and scripts, 

141 which excludes the stage and the metadata, to the database role, and 

142 the role to the users. 

143 

144 :param session: Database Session. 

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

146 

147 :param users: Users to give the role to. 

148 :type users: list[str] 

149 """ 

150 # The schemas of the repo that were built 

151 built = set(inspect(session.connection()).get_schema_names()) 

152 for schema in sorted((self.graph.schemas & built) - {"public"}): 

153 session.execute( 

154 f"GRANT USAGE ON SCHEMA {schema} TO {self.name}") 

155 session.execute( 

156 f"GRANT SELECT ON ALL SEQUENCES IN SCHEMA {schema} " 

157 f"TO {self.name}") 

158 session.execute( 

159 f"GRANT SELECT ON ALL TABLES IN SCHEMA {schema} " 

160 f"TO {self.name}") 

161 

162 # Grant permissions to users by proxy of the database role 

163 if users: 

164 session.execute(dedent(""" 

165 GRANT {role} TO "{users}" 

166 """).format( 

167 role=self.name, 

168 users='", "'.join(users) 

169 )) 

170 

171 def create_role(self, session): 

172 """Create database role. 

173 

174 :param session: Database Session. 

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

176 """ 

177 engine = session.get_bind() 

178 with engine.connect() as conn: 

179 try: 

180 conn.execute(text(f"CREATE ROLE {self.name}")) 

181 conn.commit() 

182 except ProgrammingError as error: 

183 message = f'role "{self.name}" already exists' 

184 if message in error._message(): # pylint: disable=protected-access # noqa: E501 

185 return 

186 raise error 

187 

188 def execute_directory(self, directory, session, **kwargs): 

189 """Executes SQL scripts in a given directory. 

190 

191 :param directory: Path for metadata directory. 

192 :type directory: str 

193 

194 :param session: Database Session. 

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

196 

197 :param ignore: List of files to ignore. 

198 :type ignore: str 

199 """ 

200 for file_name in sorted(self._files(directory, "**/*.sql")): 

201 logger.info("Script '%s': Running.", 

202 file_name.relative_to(self.directory)) 

203 

204 # Load file 

205 with open(file_name, "r", encoding="utf-8") as file: 

206 sql = file.read() 

207 

208 # Execute query 

209 session.execute(sql, **kwargs) 

210 

211 def load_stale(self, session): 

212 """Load what was built before and is no longer in the repo, because 

213 its metadata or script was removed or ignored. 

214 

215 :param session: Database Session. 

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

217 

218 :raises ValueError: If what is in the repo requires any of them. 

219 """ 

220 self.stale = [] 

221 for built in session.execute(select(Object)).scalars(): 

222 if built.name_ not in self.graph.nodes: 

223 # The pipeline commits, which would expire it 

224 session.expunge(built) 

225 self.stale.append(built) 

226 

227 # They are dropped at the end, so nothing in the repo can use them 

228 for built in self.stale: 

229 name = built.name_ 

230 

231 # A call names a function without its parameters, and it is not 

232 # known which overload it uses 

233 function = name.partition("(")[0] 

234 for user, node in self.graph.nodes.items(): 

235 if node.requires & {name, function}: 

236 raise ValueError( 

237 f"'{user}' requires '{name}', which was removed from " 

238 "the repo." 

239 ) 

240 

241 def forget_dependents(self, session, objects): 

242 """Deletes the records of the tables and scripts that need any of the 

243 objects. Rebuilding an object drops what is built on it, the foreign 

244 keys of the tables and the views of the scripts, so the next run has 

245 to build them again. 

246 

247 :param session: Database Session. 

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

249 

250 :param objects: Tables or scripts. 

251 :type objects: list[datakiln.metadata.table.TableMeta] 

252 """ 

253 for node in self.graph.affected(objects): 

254 record = node.record(session) 

255 if record: 

256 session.delete(record) 

257 

258 def execute_derive(self, session, force=False, record=True, include=None, 

259 **kwargs): 

260 """Executes the derive scripts, each one after the scripts that create 

261 the objects it uses. Only the scripts that changed, or that were not 

262 built since what they use was rebuilt, are executed. 

263 

264 :param session: Database Session. 

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

266 

267 :param force: Execute all scripts, even if they are up to date. 

268 :type force: bool 

269 

270 :param record: Whether to save the scripts executed to the metadata, 

271 which is what tells later runs they are up to date. 

272 :type record: bool 

273 

274 :param include: Scripts to execute, if not all of them. 

275 :type include: set[datakiln.metadata.script.ScriptMeta] 

276 """ 

277 for script in self.graph.scripts: 

278 name = script.full_name 

279 if include is not None and script not in include: 

280 continue 

281 saved = script.record(session) if record else None 

282 

283 if not force and saved and saved.checksum_ == script.checksum: 

284 logger.info("Script '%s': Already up to date.", script.path) 

285 continue 

286 

287 logger.info("Script '%s': Running.", script.path) 

288 session.execute(script.sql, **kwargs) 

289 

290 if record: 

291 session.merge(Script( 

292 name_=name, 

293 checksum_=script.checksum, 

294 drop_=script.drop 

295 )) 

296 self.forget_dependents(session, [script]) 

297 session.commit() 

298 

299 def prune(self, session, drop=False): 

300 """Drops what was built before and is no longer in the repo, because 

301 its metadata or script was removed or ignored, and the schemas that 

302 are left empty. 

303 

304 :param session: Database Session. 

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

306 

307 :param drop: Whether to drop them. If not, they are only listed. 

308 :type drop: bool 

309 """ 

310 verb = "Dropping." if drop else "Removed from the repository." 

311 schemas = set() 

312 

313 for built in self.stale: 

314 logger.warning("%s '%s': %s", built.kind_.capitalize(), 

315 "'.'".join(built.name_.split(".")), verb) 

316 if built.schema: 

317 schemas.add(built.schema) 

318 if not drop: 

319 continue 

320 

321 for statement in built.drops: 

322 session.execute(statement) 

323 

324 # What is under it in the metadata goes with it 

325 session.delete(built) 

326 

327 if not drop: 

328 return 

329 

330 # A schema is only dropped if nothing is left in it, even objects 

331 # that are not ours, so the database is the one to tell 

332 for schema in sorted(schemas - self.graph.schemas): 

333 for name in (schema, f"_{schema}"): 

334 try: 

335 with session.begin_nested(): 

336 session.execute(f"DROP SCHEMA IF EXISTS {name}") 

337 except DBAPIError: 

338 logger.info( 

339 "Schema '%s': Not in the repository, but not empty.", 

340 name) 

341 session.commit() 

342 

343 def create_metadata(self, session): 

344 """ 

345 :param session: Database Session. 

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

347 """ 

348 engine = session.get_bind() 

349 with engine.connect() as conn: 

350 schema = CreateSchema(Base.metadata.schema, if_not_exists=True) 

351 conn.execute(schema) 

352 upgrade(conn) 

353 conn.commit() 

354 

355 @classmethod 

356 def load_env(cls, directory, required=True): 

357 """Load environment variables. 

358 

359 :param directory: Path for metadata directory. 

360 :type directory: str 

361 

362 :param required: Whether the directory must have a `.env` file. If 

363 not, the environment variables and the defaults are 

364 used when it does not. 

365 :type required: bool 

366 """ 

367 db_params = {} 

368 

369 # Load .env file 

370 dotenv_file = Path(directory) / ".env" 

371 if required or dotenv_file.exists(): 

372 env_config = Config(RepositoryEnv(dotenv_file)) 

373 else: 

374 env_config = Config(RepositoryEmpty()) 

375 

376 # Get environment variables 

377 db_params["user"] = env_config("DB_USER", getuser()) 

378 db_params["host"] = env_config("DB_HOST", "localhost") 

379 db_params["port"] = env_config("DB_PORT", "5432") 

380 db_params["database"] = env_config("DB_NAME", "postgres") 

381 db_params["dialect"] = env_config("DB_DIALECT", "postgresql") 

382 db_params["isolation_level"] = env_config( 

383 "DB_ISOLATION_LEVEL", 

384 "READ COMMITTED" 

385 ) 

386 

387 return db_params 

388 

389 

390@event.listens_for(Engine, "before_cursor_execute", retval=True) 

391def before_cursor_execute(conn, cursor, statement, parameters, context, 

392 executemany): 

393 # pylint: disable=too-many-arguments, too-many-positional-arguments 

394 # pylint: disable=unused-argument, broad-exception-caught 

395 """Format SQL query before execution.""" 

396 

397 # Skip formatting if explicitly requested via execution options 

398 if getattr(context, "execution_options", {}).get("skip_formatting", False): 

399 return statement, parameters 

400 

401 try: 

402 statement = sqlparse.format( 

403 statement, 

404 reindent_aligned=True, 

405 keyword_case="upper" 

406 ) 

407 except Exception: 

408 pass 

409 return statement, parameters 

410 

411 

412@event.listens_for(Engine, "connect") 

413def set_oracle_nls(dbapi_connection, _connection_record): 

414 """Set standard NLS date/timestamp formats on Oracle connections.""" 

415 oracle_drivers = ("oracledb", "cx_Oracle") 

416 if dbapi_connection.__class__.__module__.startswith(oracle_drivers): 

417 cursor = dbapi_connection.cursor() 

418 try: 

419 formats = [ 

420 ("NLS_DATE_FORMAT", "YYYY-MM-DD HH24:MI:SS"), 

421 ("NLS_TIMESTAMP_FORMAT", "YYYY-MM-DD HH24:MI:SS"), 

422 ("NLS_TIMESTAMP_TZ_FORMAT", "YYYY-MM-DD HH24:MI:SSXFF TZR"), 

423 ] 

424 for param, value in formats: 

425 cursor.execute(f"ALTER SESSION SET {param} = '{value}'") 

426 finally: 

427 cursor.close() 

428 

429 

430class Session(SQLAlchemySession): 

431 # pylint: disable=too-few-public-methods 

432 """Manages persistence operations for ORM-mapped objects. 

433 """ 

434 

435 def __init__(self, **kwargs): 

436 r""" 

437 :param \**kwargs: SQLAlchemy session attributes. 

438 :type \**kwargs: dict 

439 

440 :return: Database session. 

441 :rtype: datakiln.metadata.database.Session 

442 

443 """ 

444 self._buffer = [] 

445 super().__init__(**kwargs) 

446 

447 def execute(self, statement, *args, buffer_size=1, max_statements=1, 

448 validate=True, **kwargs): 

449 # pylint: disable=too-many-locals 

450 r"""Executes a SQL statement construct and returns a CursorResult. 

451 

452 :param statement: Statement to be executed. 

453 :type statement: str 

454 

455 :param buffer_size: Buffer size. 

456 The statements will only be sent to the database 

457 if the number of statements in the buffer exceeds 

458 the value of this parameter. 

459 :type buffer_size: int 

460 

461 :param max_statements: Maximum number of statements to allow per 

462 execute call. 

463 :type max_statements: int 

464 

465 :param \**kwargs: SQLAlchemy execute parameters. 

466 :type \**kwargs: dict 

467 """ 

468 # If statement is not a SQL string, let sqlalchemy handle it 

469 if not isinstance(statement, str): 

470 return super().execute(statement, *args, **kwargs) 

471 

472 # Skip validation 

473 if not validate: 

474 statement = text(statement) 

475 return super().execute(statement, *args, **kwargs) 

476 

477 # Map dialects between SQLAlchemy and SQLGlot 

478 dialect = self.get_bind().dialect.name 

479 expressions = sqlglot.parse( 

480 statement, 

481 read=DIALECT_MAP.get(dialect, dialect) 

482 ) 

483 

484 # Validate expression sets 

485 match expressions: 

486 # DROP/CREATE pairs 

487 case [exp.Drop() as drop, exp.Create() as create]: 

488 # Should drop and create same object (kind) 

489 if drop.kind != create.kind: 

490 raise ValueError("DROP/CREATE have distinct types.") 

491 # Should drop and create same object (name) 

492 if drop.find(exp.Table).parts != create.find(exp.Table).parts: 

493 raise ValueError("DROP/CREATE have distinct identifiers.") 

494 # CREATE INDEX lists 

495 case [exp.Create(kind="INDEX") as index, *rest]: 

496 table = index.find(exp.Table) 

497 column_sets = [tuple(index.find_all(exp.Column))] 

498 # Sanity checks 

499 for expression in rest: 

500 # Expressions other than CREATE INDEX 

501 signature = (type(expression), expression.kind) 

502 if signature != (exp.Create, "INDEX"): 

503 raise ValueError( 

504 "Indexes should be created in a separate file." 

505 ) 

506 # Multiple tables 

507 if expression.find(exp.Table) != table: 

508 raise ValueError( 

509 "Only group indexes for the same table." 

510 ) 

511 column_set = tuple(expression.find_all(exp.Column)) 

512 column_sets.append(column_set) 

513 # Multiple indexes for same column set 

514 if len(set(column_sets)) != len(column_sets): 

515 raise ValueError( 

516 "Multiple indexes on the same set of columns." 

517 ) 

518 # Otherwise we only allow max_statements per file 

519 case _ if len(expressions) > max_statements > 0: 

520 raise ValueError( 

521 f"Only {max_statements} SQL statement(s) allowed per file." 

522 ) 

523 

524 # Add to buffer 

525 self._buffer.append(statement) 

526 if len(self._buffer) < buffer_size: 

527 return None 

528 

529 # Reset buffer 

530 statement = ";\n\n".join(self._buffer) 

531 self._buffer = [] 

532 

533 return super().execute(text(statement), *args, **kwargs) 

534 

535 @classmethod 

536 def create( 

537 cls, 

538 user, 

539 host, 

540 port, 

541 database, 

542 dialect, 

543 password=None, 

544 isolation_level="READ COMMITTED" 

545 ): 

546 # pylint: disable=too-many-arguments, too-many-positional-arguments 

547 """Connect to database using SQLAlchemy. 

548 

549 :param user: Name of the database user. 

550 :type user: str 

551 

552 :param host: Hostname of the database server. 

553 :type host: str 

554 

555 :param port: Network port of the database server. 

556 :type port: int 

557 

558 :param database: Name of the database. 

559 :type database: str 

560 

561 :param dialect: Dialect of the database. 

562 :type dialect: str 

563 

564 :param password: User's password. 

565 :type password: str 

566 

567 :param isolation_level: Transaction isolation level. 

568 :type isolation_level: str 

569 

570 :return: Database session. 

571 :rtype: datakiln.metadata.database.Session 

572 """ 

573 url_object = URL.create( 

574 f"{dialect}+{DRIVER[dialect]}", 

575 username=user, 

576 password=password, 

577 host=host, 

578 database=database, 

579 port=port 

580 ) 

581 

582 engine = create_engine( 

583 url_object, 

584 isolation_level=isolation_level, 

585 pool_recycle=60, 

586 pool_pre_ping=True 

587 ) 

588 

589 # Create database 

590 if dialect != "oracle" and not database_exists(url_object): 

591 create_database(url_object) 

592 

593 return Session(bind=engine, autoflush=True)