Page MenuHomeDevCentral
Files F11716674

D3668.diff
No OneTemporary
Actions

D3668.diff
View Options

diff --git a/.gitignore b/.gitignore
new file mode 100644
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,2 @@
+*.egg-info/
+tools/*/dist/
diff --git a/README.md b/README.md
--- a/README.md
+++ b/README.md
@@ -14,3 +14,19 @@
- acquisitariat/ contains the queries for MySQL Docker container
used by dev & community services like DevCentral
+### Tools
+
+Tools and utilities to work with reports are located in the tools/ folder:
+
+* **[nasqueron-reports](tools/nasqueron-reports/README.md)**:
+ allows to run the MariaDB or MySQL query,
+ and format the result as expected, e.g. as MediaWiki table
+
+### Contribute
+
+This repository is intended to behave as a monorepo for reporting.
+
+You can so add any project to generate or use a report at Nasqueron here,
+regardless of the choice of technology stack.
+
+Software in tools/<name of the project> are intended to be built autonomously.
diff --git a/tools/nasqueron-reports/README.md b/tools/nasqueron-reports/README.md
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/README.md
@@ -0,0 +1,24 @@
+## nasqueron-reports
+
+The nasqueron_reports Python package allows to run the MariaDB or MySQL query,
+and format the result as expected.
+
+It's composed of a modular library in src/ and utilities in bin/.
+
+### 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/tools/nasqueron-reports/bin/run-report b/tools/nasqueron-reports/bin/run-report
new file mode 100755
--- /dev/null
+++ b/tools/nasqueron-reports/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/tools/nasqueron-reports/bin/sql-result-to-mediawiki-table b/tools/nasqueron-reports/bin/sql-result-to-mediawiki-table
new file mode 100755
--- /dev/null
+++ b/tools/nasqueron-reports/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/tools/nasqueron-reports/conf/reports.yaml b/tools/nasqueron-reports/conf/reports.yaml
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/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/tools/nasqueron-reports/pyproject.toml b/tools/nasqueron-reports/pyproject.toml
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/pyproject.toml
@@ -0,0 +1,14 @@
+# -------------------------------------------------------------
+# Nasqueron Reports
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+# Project: Nasqueron
+# License: BSD-2-Clause
+# -------------------------------------------------------------
+
+[build-system]
+requires = [
+ "setuptools>=42",
+ "wheel"
+]
+
+build-backend = "setuptools.build_meta"
diff --git a/tools/nasqueron-reports/requirements.txt b/tools/nasqueron-reports/requirements.txt
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/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/tools/nasqueron-reports/setup.cfg b/tools/nasqueron-reports/setup.cfg
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/setup.cfg
@@ -0,0 +1,35 @@
+[metadata]
+name = nasqueron-reports
+version = 0.1.0
+author = Sébastien Santoro
+author_email = dereckson@espace-win.org
+description = Run MariaDB or MySQL query and format as report
+long_description = file: README.md
+long_description_content_type = text/markdown
+license = BSD-2-Clause
+url = https://devcentral.nasqueron.org/source/reports/
+project_urls =
+ Bug Tracker = https://devcentral.nasqueron.org/
+classifiers =
+ Programming Language :: Python :: 3
+ Operating System :: OS Independent
+ Environment :: Console
+ Intended Audience :: Developers
+ Topic :: Software Development
+
+[options]
+package_dir =
+ = src
+packages = find:
+scripts =
+ bin/run-report
+ bin/sql-result-to-mediawiki-table
+python_requires = >=3.6
+install_requires =
+ PyYAML>=6.0,<7.0
+ hvac>=2.3,<3.0
+ mysql-connector-python>=9.4,<10.0
+ sqlparse>=0.5,<0.6
+
+[options.packages.find]
+where = src
diff --git a/tools/nasqueron-reports/src/nasqueron_reports/__init__.py b/tools/nasqueron-reports/src/nasqueron_reports/__init__.py
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/src/nasqueron_reports/__init__.py
@@ -0,0 +1,9 @@
+# -------------------------------------------------------------
+# Nasqueron Reports
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+# Project: Nasqueron
+# License: BSD-2-Clause
+# -------------------------------------------------------------
+
+from . import config
+from . import errors
diff --git a/tools/nasqueron-reports/src/nasqueron_reports/config.py b/tools/nasqueron-reports/src/nasqueron_reports/config.py
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/src/nasqueron_reports/config.py
@@ -0,0 +1,111 @@
+# -------------------------------------------------------------
+# Nasqueron Reports :: Config
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+# Project: Nasqueron
+# Description: Reports configuration
+# License: BSD-2-Clause
+# -------------------------------------------------------------
+
+
+import os
+
+import yaml
+
+from nasqueron_reports.credentials import vault
+from nasqueron_reports.errors import NasqueronReportConfigError
+
+
+# -------------------------------------------------------------
+# Configuration paths
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+
+
+DEFAULT_CONFIG_PATHS = [
+ "conf/reports.yaml",
+ ".reports.yaml",
+ "/usr/local/etc/reports.yaml",
+ "/etc/reports.yaml",
+]
+
+
+DEFAULT_SQL_PATHS = [
+ ".",
+ "/usr/local/share/nasqueron-reports",
+ "/usr/share/nasqueron-reports"
+]
+
+
+def get_config_path():
+ for config_path in DEFAULT_CONFIG_PATHS:
+ if os.path.exists(config_path):
+ return config_path
+
+ return None
+
+
+def resolve_sql_path(sql_path):
+ for sql_directory in DEFAULT_SQL_PATHS:
+ full_path = os.path.join(sql_directory, sql_path)
+ if os.path.exists(full_path):
+ return full_path
+
+ return sql_path
+
+
+# -------------------------------------------------------------
+# Main configuration
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+
+
+def get_config():
+ config_path = get_config_path()
+
+ if not config_path:
+ raise NasqueronReportConfigError("You need to create a reports.yaml config file")
+
+ 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}"
+ )
+
+ if "credentials" in report_config["service_options"]:
+ secret_path = report_config["service_options"]["credentials"]
+ credentials = vault.fetch_credentials(secret_path)
+ else:
+ credentials = {}
+
+ report_config["service_options"]["credentials"] = credentials
+
+
+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/tools/nasqueron-reports/src/nasqueron_reports/connectors/__init__.py b/tools/nasqueron-reports/src/nasqueron_reports/connectors/__init__.py
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/src/nasqueron_reports/connectors/__init__.py
@@ -0,0 +1,8 @@
+# -------------------------------------------------------------
+# Nasqueron Reports :: Connectors
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+# Project: Nasqueron
+# License: BSD-2-Clause
+# -------------------------------------------------------------
+
+from . import db_mysql
diff --git a/tools/nasqueron-reports/src/nasqueron_reports/connectors/db_mysql.py b/tools/nasqueron-reports/src/nasqueron_reports/connectors/db_mysql.py
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/src/nasqueron_reports/connectors/db_mysql.py
@@ -0,0 +1,120 @@
+# -------------------------------------------------------------
+# 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.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_db_config(config):
+ args = config["service_options"]
+
+ return {
+ "host": args.get("hostname", "localhost"),
+ "user": args["credentials"].get("username", ""),
+ "password": args["credentials"].get("password", ""),
+ "database": args.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/tools/nasqueron-reports/src/nasqueron_reports/credentials/__init__.py b/tools/nasqueron-reports/src/nasqueron_reports/credentials/__init__.py
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/src/nasqueron_reports/credentials/__init__.py
@@ -0,0 +1,8 @@
+# -------------------------------------------------------------
+# Nasqueron Reports :: Credentials
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+# Project: Nasqueron
+# License: BSD-2-Clause
+# -------------------------------------------------------------
+
+from . import vault
diff --git a/tools/nasqueron-reports/src/nasqueron_reports/credentials/vault.py b/tools/nasqueron-reports/src/nasqueron_reports/credentials/vault.py
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/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/tools/nasqueron-reports/src/nasqueron_reports/errors.py b/tools/nasqueron-reports/src/nasqueron_reports/errors.py
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/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/tools/nasqueron-reports/src/nasqueron_reports/formats/__init__.py b/tools/nasqueron-reports/src/nasqueron_reports/formats/__init__.py
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/src/nasqueron_reports/formats/__init__.py
@@ -0,0 +1,8 @@
+# -------------------------------------------------------------
+# Nasqueron Reports :: Formats
+# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
+# Project: Nasqueron
+# License: BSD-2-Clause
+# -------------------------------------------------------------
+
+from . import mediawiki
diff --git a/tools/nasqueron-reports/src/nasqueron_reports/formats/mediawiki.py b/tools/nasqueron-reports/src/nasqueron_reports/formats/mediawiki.py
new file mode 100644
--- /dev/null
+++ b/tools/nasqueron-reports/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, 14:07 (18 h, 20 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
2985209
Default Alt Text
D3668.diff (23 KB)

Event Timeline

Nasqueron DevCentral · If it had been much bigger the moon would have had a core of ice. · Powered by Phabricator