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
« 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
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
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
30DRIVER = {
31 "postgresql": "psycopg2",
32 "oracle": "oracledb"
33}
34logger = logging.getLogger(__name__)
37class DatabaseMeta():
38 """Database metadata."""
40 def __init__(self, directory, name):
41 """
42 :param directory: Path for metadata directory.
43 :type directory: str
45 :param name: Name of the database.
46 :type name: str
47 """
48 self.directory = Path(directory)
49 self.name = name
51 # Objects built before that are no longer in the repo
52 self.stale = []
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()
60 def _files(self, directory, pattern):
61 """Retrieve files in a subdirectory, skipping the ignored ones.
63 :param directory: Subdirectory of the metadata directory.
64 :type directory: str
66 :param pattern: Glob pattern of the files.
67 :type pattern: str
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
76 @cached_property
77 def tables(self):
78 """Retrieve database tables, in the order of their files.
80 :return: Table metadata.
81 :rtype: list[datakiln.metadata.table.TableMeta]
82 """
83 data_files = DataFiles(self.directory / "data")
85 tables = []
86 for path in self._files("metadata", "*.json"):
87 tables.append(TableMeta.from_json(path, data_files))
89 return tables
91 @cached_property
92 def scripts(self):
93 """Retrieve the derive scripts.
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
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)
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.
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
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.
134 :return: Dependency graph.
135 :rtype: datakiln.metadata.graph.DatabaseGraph
136 """
137 return DatabaseGraph(self.tables, self.scripts, self.external)
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.
144 :param session: Database Session.
145 :type session: datakiln.metadata.database.Session
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}")
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 ))
171 def create_role(self, session):
172 """Create database role.
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
188 def execute_directory(self, directory, session, **kwargs):
189 """Executes SQL scripts in a given directory.
191 :param directory: Path for metadata directory.
192 :type directory: str
194 :param session: Database Session.
195 :type session: datakiln.metadata.database.Session
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))
204 # Load file
205 with open(file_name, "r", encoding="utf-8") as file:
206 sql = file.read()
208 # Execute query
209 session.execute(sql, **kwargs)
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.
215 :param session: Database Session.
216 :type session: datakiln.metadata.database.Session
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)
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_
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 )
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.
247 :param session: Database Session.
248 :type session: datakiln.metadata.database.Session
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)
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.
264 :param session: Database Session.
265 :type session: datakiln.metadata.database.Session
267 :param force: Execute all scripts, even if they are up to date.
268 :type force: bool
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
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
283 if not force and saved and saved.checksum_ == script.checksum:
284 logger.info("Script '%s': Already up to date.", script.path)
285 continue
287 logger.info("Script '%s': Running.", script.path)
288 session.execute(script.sql, **kwargs)
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()
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.
304 :param session: Database Session.
305 :type session: datakiln.metadata.database.Session
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()
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
321 for statement in built.drops:
322 session.execute(statement)
324 # What is under it in the metadata goes with it
325 session.delete(built)
327 if not drop:
328 return
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()
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()
355 @classmethod
356 def load_env(cls, directory, required=True):
357 """Load environment variables.
359 :param directory: Path for metadata directory.
360 :type directory: str
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 = {}
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())
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 )
387 return db_params
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."""
397 # Skip formatting if explicitly requested via execution options
398 if getattr(context, "execution_options", {}).get("skip_formatting", False):
399 return statement, parameters
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
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()
430class Session(SQLAlchemySession):
431 # pylint: disable=too-few-public-methods
432 """Manages persistence operations for ORM-mapped objects.
433 """
435 def __init__(self, **kwargs):
436 r"""
437 :param \**kwargs: SQLAlchemy session attributes.
438 :type \**kwargs: dict
440 :return: Database session.
441 :rtype: datakiln.metadata.database.Session
443 """
444 self._buffer = []
445 super().__init__(**kwargs)
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.
452 :param statement: Statement to be executed.
453 :type statement: str
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
461 :param max_statements: Maximum number of statements to allow per
462 execute call.
463 :type max_statements: int
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)
472 # Skip validation
473 if not validate:
474 statement = text(statement)
475 return super().execute(statement, *args, **kwargs)
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 )
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 )
524 # Add to buffer
525 self._buffer.append(statement)
526 if len(self._buffer) < buffer_size:
527 return None
529 # Reset buffer
530 statement = ";\n\n".join(self._buffer)
531 self._buffer = []
533 return super().execute(text(statement), *args, **kwargs)
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.
549 :param user: Name of the database user.
550 :type user: str
552 :param host: Hostname of the database server.
553 :type host: str
555 :param port: Network port of the database server.
556 :type port: int
558 :param database: Name of the database.
559 :type database: str
561 :param dialect: Dialect of the database.
562 :type dialect: str
564 :param password: User's password.
565 :type password: str
567 :param isolation_level: Transaction isolation level.
568 :type isolation_level: str
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 )
582 engine = create_engine(
583 url_object,
584 isolation_level=isolation_level,
585 pool_recycle=60,
586 pool_pre_ping=True
587 )
589 # Create database
590 if dialect != "oracle" and not database_exists(url_object):
591 create_database(url_object)
593 return Session(bind=engine, autoflush=True)