D3668.id9496.diff
No OneTemporary
Actions

Size

16 KB

Referenced Files

None

Subscribers

None

D3668.id9496.diff
View Options

	diff --git a/README.md b/README.md
	--- a/README.md
	+++ b/README.md
	@@ -14,3 +14,24 @@
	- acquisitariat/ contains the queries for MySQL Docker container
	used by dev & community services like DevCentral

	+### Tools
	+
	+Tools are located in the bin/ folder.
	+
	+#### run-report
	+The `bin/run-report` tool is a full solution to connect to a specific data source,
	+fetch data and output it following a configuration.
	+
	+Default configuration can be found in conf/ folder.
	+
	+For example, `run-report agora-operations-grimoire-older-pages`
	+will connect to MariaDB, run query and format as MediaWiki table.
	+
	+#### sql-result-to-mediawiki-table
	+
	+For one shot queries to produce wiki tables,
	+instead of adding a SQL query here and a configuration,
	+you can use `mysql ... \| bin/sql-result-to-mediawiki-table`.
	+
	+The tabulation is used as separator, so could be best to use
	+mysql --raw --batch, but that seems to work decently without them.
	diff --git a/bin/run-report b/bin/run-report
	new file mode 100755
	--- /dev/null
	+++ b/bin/run-report
	@@ -0,0 +1,110 @@
	+#!/usr/bin/env python3
	+
	+# -------------------------------------------------------------
	+# Run report
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+# Project: Nasqueron
	+# Description: Run a specific report and format data accordingly
	+# License: BSD-2-Clause
	+# -------------------------------------------------------------
	+
	+
	+import sys
	+
	+from nasqueron_reports.config import parse_report_config
	+from nasqueron_reports.connectors import db_mysql
	+from nasqueron_reports.errors import *
	+from nasqueron_reports.formats import mediawiki
	+
	+
	+# -------------------------------------------------------------
	+# Connectors wiring
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def fetch_mysql_report(config):
	+ try:
	+ return db_mysql.fetch_report(config)
	+ except NasqueronReportDatabaseError as e:
	+ print(f"An error occurred at database level: {e}", file=sys.stderr)
	+ sys.exit(8)
	+ except NasqueronReportQueryError as e:
	+ print(e, file=sys.stderr)
	+ sys.exit(4)
	+
	+
	+CONNECTORS_MAP = {
	+ "MariaDB": fetch_mysql_report,
	+ "MySQL": fetch_mysql_report,
	+}
	+
	+
	+# -------------------------------------------------------------
	+# Formatters wiring
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+FORMATS_MAP = {
	+ "mediawiki": mediawiki.to_table,
	+}
	+
	+
	+# -------------------------------------------------------------
	+# Application entry point
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def wire(report_config):
	+ if "connector" not in report_config["service_options"]:
	+ service_name = report_config["service"]
	+ print(
	+ f"Service connector missing in configuration for service {service_name}",
	+ file=sys.stderr,
	+ )
	+ sys.exit(16)
	+
	+ if "format" not in report_config:
	+ print(f"Format missing in report configuration", file=sys.stderr)
	+ sys.exit(16)
	+
	+ report_connector = report_config["service_options"]["connector"]
	+ if report_connector not in CONNECTORS_MAP:
	+ print(f"Unknown connector: {report_connector}", file=sys.stderr)
	+ sys.exit(32)
	+
	+ report_format = report_config["format"]
	+ if report_format not in FORMATS_MAP:
	+ print(f"Unknown format: {report_format}", file=sys.stderr)
	+ sys.exit(32)
	+
	+ return CONNECTORS_MAP[report_connector], FORMATS_MAP[report_format]
	+
	+
	+def generate_report(report_config):
	+ connector_cb, format_cb = wire(report_config)
	+
	+ headers, rows = connector_cb(report_config)
	+
	+ format_options = report_config["format_options"]
	+ return format_cb(headers, rows, format_options)
	+
	+
	+def run(report_name):
	+ try:
	+ report_config = parse_report_config(report_name)
	+ except NasqueronReportConfigError as e:
	+ print(e, file=sys.stderr)
	+ sys.exit(2)
	+
	+ output = generate_report(report_config)
	+ print(output)
	+
	+
	+if __name__ == "__main__":
	+ argc = len(sys.argv)
	+
	+ if argc < 2:
	+ print(f"Usage: {sys.argv[0]} <report name>", file=sys.stderr)
	+ sys.exit(1)
	+
	+ run(sys.argv[1])
	diff --git a/bin/sql-result-to-mediawiki-table b/bin/sql-result-to-mediawiki-table
	new file mode 100755
	--- /dev/null
	+++ b/bin/sql-result-to-mediawiki-table
	@@ -0,0 +1,79 @@
	+#!/usr/bin/env python3
	+
	+# -------------------------------------------------------------
	+# SQL result to MediaWiki table
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+# Project: Nasqueron
	+# Description: Format a SQL query output as a MediaWiki table
	+# Currently supports MySQL/MariaDB format
	+# Usage: mysql ... \| sql-result-to-mediawiki-table
	+# License: BSD-2-Clause
	+# -------------------------------------------------------------
	+
	+
	+import sys
	+
	+from nasqueron_reports.formats import mediawiki
	+
	+
	+# -------------------------------------------------------------
	+# MySQL client parser
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def parse_mysql_client_output(stream):
	+ """
	+ Parse MySQL client tabular output into list of rows.
	+ Safer version: assumes MySQL output uses tab as delimiter.
	+ """
	+ rows = []
	+
	+ for line in stream.splitlines():
	+ line = line.rstrip()
	+ if not line:
	+ continue
	+
	+ parts = line.split("\t")
	+ rows.append(parts)
	+
	+ return rows
	+
	+
	+def format_header(header):
	+ return header.replace("_", " ").capitalize()
	+
	+
	+def infer_headers_map(headers):
	+ """
	+ Given a list of raw SQL column names, return a dict mapping
	+ to human-friendly MediaWiki table headers.
	+
	+ Example:
	+ ["page_link", "age"] ->
	+ {"page_link": "Page link", "age": "Age"}
	+ """
	+ return {header: format_header(header) for header in headers}
	+
	+
	+# -------------------------------------------------------------
	+# Application entry point
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def run():
	+ stream = sys.stdin.read()
	+
	+ rows = parse_mysql_client_output(stream)
	+ headers = rows[0]
	+ rows = rows[1:]
	+
	+ options = {
	+ "cols": infer_headers_map(headers),
	+ }
	+
	+ output = mediawiki.to_table(headers, rows, options)
	+ print(output)
	+
	+
	+if __name__ == "__main__":
	+ run()
	diff --git a/conf/reports.yaml b/conf/reports.yaml
	new file mode 100644
	--- /dev/null
	+++ b/conf/reports.yaml
	@@ -0,0 +1,23 @@
	+# -------------------------------------------------------------
	+# Reports configuration
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+# Project: Nasqueron
	+# -------------------------------------------------------------
	+
	+services:
	+ db-B:
	+ connector: MariaDB
	+ hostname: db-B-001
	+ credentials: ops/secrets/dbserver/cluster-B/users/rhyne-wyse
	+
	+reports:
	+ agora-operations-grimoire-older-pages:
	+ path: sql/db-B/agora/operations-grimoire-older-pages.sql
	+ service: db-B
	+
	+ format: mediawiki
	+ format_options:
	+ # Mapping between query columns and table headings
	+ cols:
	+ page_link: Article
	+ age: Age (days)
	diff --git a/requirements.txt b/requirements.txt
	new file mode 100644
	--- /dev/null
	+++ b/requirements.txt
	@@ -0,0 +1,4 @@
	+hvac~=2.3.0
	+mysql-connector-python~=9.4.0
	+PyYAML~=6.0.2
	+sqlparse~=0.5.3
	diff --git a/src/nasqueron_reports/config.py b/src/nasqueron_reports/config.py
	new file mode 100644
	--- /dev/null
	+++ b/src/nasqueron_reports/config.py
	@@ -0,0 +1,80 @@
	+# -------------------------------------------------------------
	+# Nasqueron Reports :: Config
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+# Project: Nasqueron
	+# Description: Reports configuration
	+# License: BSD-2-Clause
	+# -------------------------------------------------------------
	+
	+
	+import yaml
	+
	+from nasqueron_reports.errors import NasqueronReportConfigError
	+
	+
	+# -------------------------------------------------------------
	+# Configuration paths
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+DEFAULT_CONFIG_PATH = "conf/reports.yaml"
	+
	+
	+def get_config_path():
	+ return DEFAULT_CONFIG_PATH
	+
	+
	+def resolve_report_path(report_path):
	+ return report_path
	+
	+
	+def resolve_sql_path(sql_path):
	+ return sql_path
	+
	+
	+# -------------------------------------------------------------
	+# Main configuration
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def get_config():
	+ config_path = get_config_path()
	+
	+ with open(config_path) as fd:
	+ config = yaml.safe_load(fd)
	+
	+ return config
	+
	+
	+# -------------------------------------------------------------
	+# Report configuration
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def inject_service_config(config, report_config):
	+ try:
	+ service_name = report_config["service"]
	+ except KeyError:
	+ raise NasqueronReportConfigError(
	+ f"Service parameter missing in report configuration"
	+ )
	+
	+ try:
	+ report_config["service_options"] = config["services"][service_name]
	+ except KeyError:
	+ raise NasqueronReportConfigError(
	+ f"Service not declared in configuration: {service_name}"
	+ )
	+
	+
	+def parse_report_config(report_name):
	+ config = get_config()
	+
	+ try:
	+ report_config = config["reports"][report_name]
	+ except KeyError:
	+ raise NasqueronReportConfigError(f"Report not found: {report_name}")
	+
	+ inject_service_config(config, report_config)
	+
	+ return report_config
	diff --git a/src/nasqueron_reports/connectors/db_mysql.py b/src/nasqueron_reports/connectors/db_mysql.py
	new file mode 100644
	--- /dev/null
	+++ b/src/nasqueron_reports/connectors/db_mysql.py
	@@ -0,0 +1,126 @@
	+# -------------------------------------------------------------
	+# Nasqueron Reports :: Connectors :: MySQL
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+# Project: Nasqueron
	+# Description: Connect to MySQL or MariaDB
	+# License: BSD-2-Clause
	+# -------------------------------------------------------------
	+
	+
	+import mysql.connector
	+import sqlparse
	+
	+from nasqueron_reports.config import resolve_sql_path
	+from nasqueron_reports.credentials import vault
	+from nasqueron_reports.errors import *
	+
	+
	+# -------------------------------------------------------------
	+# Statements parser
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def clean_statement(statement):
	+ return sqlparse.format(statement, strip_comments=True).strip()
	+
	+
	+def split_statements(queries):
	+ return [clean_statement(stmt) for stmt in sqlparse.split(queries)]
	+
	+
	+def extract_database(query):
	+ if query.startswith("USE "):
	+ tokens = query.split()
	+ try:
	+ return tokens[1].rstrip(";")
	+ except IndexError:
	+ raise NasqueronReportQueryError("Malformed USE statement", query)
	+
	+ raise NasqueronReportQueryError(
	+ "When a report query contains two statements, the first statement is expected to be USE.",
	+ query,
	+ )
	+
	+
	+def parse_statements(query):
	+ """Parse SELECT or USE; SELECT; statements.
	+ as a main SELECT query and a database."""
	+ statements = split_statements(query)
	+
	+ n = len(statements)
	+ if n == 0:
	+ raise NasqueronReportQueryError("Empty query", query)
	+ if n > 2:
	+ raise NasqueronReportQueryError("Too many statements in query", query)
	+
	+ if n == 1:
	+ query = statements[0]
	+ database = None
	+ else:
	+ query = statements[1]
	+ database = extract_database(statements[0])
	+
	+ return query, database
	+
	+
	+# -------------------------------------------------------------
	+# Queries
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def run_query(query, db_config):
	+ query, database = parse_statements(query)
	+
	+ conn = mysql.connector.connect(**db_config)
	+ cursor = conn.cursor()
	+ if database:
	+ conn.database = database
	+
	+ cursor.execute(query)
	+
	+ rows = cursor.fetchall()
	+ col_names = [desc[0] for desc in cursor.description]
	+
	+ cursor.close()
	+ conn.close()
	+
	+ return col_names, rows
	+
	+
	+# -------------------------------------------------------------
	+# Database configuration
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def get_credentials(config):
	+ secret_path = config["service_options"]["credentials"]
	+ return vault.fetch_credentials(secret_path)
	+
	+
	+def get_db_config(config):
	+ credentials = get_credentials(config)
	+
	+ return {
	+ "host": config["service_options"].get("hostname", "localhost"),
	+ "user": credentials["username"],
	+ "password": credentials["password"],
	+ "database": config["service_options"].get("database", ""),
	+ }
	+
	+
	+# -------------------------------------------------------------
	+# Reports
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+
	+
	+def fetch_report(config):
	+ query_path = resolve_sql_path(config["path"])
	+ with open(query_path, "r") as fd:
	+ query = fd.read()
	+
	+ db_config = get_db_config(config)
	+
	+ try:
	+ return run_query(query, db_config)
	+ except mysql.connector.Error as e:
	+ raise NasqueronReportDatabaseError(str(e))
	diff --git a/src/nasqueron_reports/credentials/vault.py b/src/nasqueron_reports/credentials/vault.py
	new file mode 100644
	--- /dev/null
	+++ b/src/nasqueron_reports/credentials/vault.py
	@@ -0,0 +1,31 @@
	+# -------------------------------------------------------------
	+# Nasqueron Reports :: Credentials :: Vault
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+# Project: Nasqueron
	+# Description: Read credentials from Vault or OpenBao
	+# License: BSD-2-Clause
	+# -------------------------------------------------------------
	+
	+
	+import hvac
	+
	+
	+VAULT_CA_CERTIFICATE = "/usr/local/share/certs/nasqueron-vault-ca.crt"
	+
	+
	+def fetch_credentials(secret_path):
	+ vault_client = hvac.Client(
	+ verify=VAULT_CA_CERTIFICATE,
	+ )
	+
	+ tokens = secret_path.split("/")
	+ secret_mount = tokens[0]
	+ secret_path = "/".join(tokens[1:])
	+
	+ secret = vault_client.secrets.kv.read_secret_version(
	+ mount_point=secret_mount,
	+ path=secret_path,
	+ raise_on_deleted_version=True,
	+ )
	+
	+ return secret["data"]["data"]
	diff --git a/src/nasqueron_reports/errors.py b/src/nasqueron_reports/errors.py
	new file mode 100644
	--- /dev/null
	+++ b/src/nasqueron_reports/errors.py
	@@ -0,0 +1,29 @@
	+# -------------------------------------------------------------
	+# Nasqueron Reports :: Errors
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+# Project: Nasqueron
	+# Description: Errors raised by reports
	+# License: BSD-2-Clause
	+# -------------------------------------------------------------
	+
	+
	+class NasqueronReportError(Exception):
	+ """Base exception for all report-related errors."""
	+
	+ pass
	+
	+
	+class NasqueronReportConfigError(NasqueronReportError):
	+ pass
	+
	+
	+class NasqueronReportDatabaseError(NasqueronReportError):
	+ pass
	+
	+
	+class NasqueronReportQueryError(NasqueronReportError):
	+ """Raised when an SQL query file does not match the expected patterns."""
	+
	+ def __init__(self, message=None, query=None):
	+ super(NasqueronReportQueryError, self).__init__(message)
	+ self.query = query
	diff --git a/src/nasqueron_reports/formats/mediawiki.py b/src/nasqueron_reports/formats/mediawiki.py
	new file mode 100644
	--- /dev/null
	+++ b/src/nasqueron_reports/formats/mediawiki.py
	@@ -0,0 +1,42 @@
	+# -------------------------------------------------------------
	+# Nasqueron Reports :: Formats :: MediaWiki
	+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
	+# Project: Nasqueron
	+# Description: Format report as MediaWiki table
	+# License: BSD-2-Clause
	+# -------------------------------------------------------------
	+
	+
	+from datetime import date
	+
	+
	+def read_as_str(value):
	+ if type(value) == bytes:
	+ return value.decode()
	+
	+ return str(value)
	+
	+
	+def to_row(row):
	+ return ["\|-", "\| " + " \|\| ".join(read_as_str(val) for val in row)]
	+
	+
	+def to_rows(rows):
	+ mediawiki_rows = [to_row(row) for row in rows]
	+ return [line for lines in mediawiki_rows for line in lines]
	+
	+
	+def to_table(columns_names, rows, options):
	+ """Format query result as MediaWiki table."""
	+ today = date.today().isoformat()
	+ lines = ['{\| class="wikitable sortable"', f"\|+ {today} report", "\|-"]
	+
	+ columns_map = options.get("cols", {})
	+ headers = [columns_map.get(c, c) for c in columns_names]
	+ lines.append("! " + " !! ".join(headers))
	+
	+ lines.extend(to_rows(rows))
	+
	+ lines.append("\|}")
	+
	+ return "\n".join(lines)

File Metadata

Mime Type: text/plain
Expires: Mon, Sep 15, 17:54 (20 h, 33 m)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 2986343
Default Alt Text: D3668.id9496.diff (16 KB)

D3668.id9496.diffNo OneTemporaryActions

D3668.id9496.diffView Options

File Metadata

Event Timeline

D3668.id9496.diff
No OneTemporary
Actions

D3668.id9496.diff
View Options