Skip to content

README.md

Latest commit

 

History

History
103 lines (66 loc) · 4.1 KB

README.md

File metadata and controls

103 lines (66 loc) · 4.1 KB

Database Schema Integration

This directory contains the database schema files for the MOLLER experiment analysis database.

Overview

The database schema is maintained in the separate MOLLER-parity-schema repository as a DBML (Database Markup Language) file. This mollerdb repository references it as a git submodule and generates the necessary artifacts for C++ integration.

Schema Files

  • qwparity_schema.sql: PostgreSQL SQL schema generated from the DBML source during build (not in repository)
  • custom_types.csv: Custom type mapping for sqlpp23 to handle PostgreSQL ENUMs
  • ../include/mollerdb/schema/qwparity_schema.h: C++ header file generated by sqlpp23 during build (not in repository)

Schema Generation

The schema files are automatically generated during the CMake build process:

  1. SQL Generation: The DBML file from the submodule is converted to PostgreSQL SQL using dbml2sql
  2. C++ Header Generation: The SQL file is processed by sqlpp23-ddl2cpp to generate type-safe C++ headers

These files are generated in the source tree and are excluded from version control via .gitignore.

Schema Update Process

The schema comes from the MOLLER-parity-schema repository which is included as a git submodule at thirdparty/MOLLER-parity-schema. To update the schema:

1. Update the Schema Submodule

cd thirdparty/MOLLER-parity-schema
git pull origin main
cd ../..
git add thirdparty/MOLLER-parity-schema
git commit -m "Update database schema submodule"

2. Rebuild

The schema files will be automatically regenerated during the next build:

pip install . --verbose

Requirements

The build process requires:

  • Node.js and npm: For the @dbml/cli package (provides dbml2sql)
  • Python 3: For the sqlpp23-ddl2cpp script

Install the required tools:

npm install -g @dbml/cli

Schema Workflow in MOLLER-parity-schema

The MOLLER-parity-schema repository has a CI workflow that automatically:

  1. Converts qwparity_schema.dbml to SQL (both MySQL and PostgreSQL)
  2. Generates SVG diagrams of the schema
  3. Publishes documentation to GitHub Pages

This mollerdb repository consumes the PostgreSQL version of the schema.

Custom Type Mappings

PostgreSQL ENUM types are mapped to sqlpp::text in C++ for simplicity. The mappings are defined in custom_types.csv.

Purpose of custom_types.csv

The MOLLER database schema uses PostgreSQL ENUM types for various fields (e.g., runlet_full_run_enum, analysis_beam_mode_enum). Since sqlpp23 does not natively support PostgreSQL ENUM types, the custom_types.csv file provides a mapping that tells the sqlpp23-ddl2cpp code generator how to represent these types in C++.

Without this mapping, the schema generation would fail when encountering ENUM types. The file is passed to sqlpp23-ddl2cpp via the --path-to-custom-types flag during the build process.

Format of custom_types.csv

The file follows the sqlpp23-ddl2cpp custom types format:

  • Each line: base_type,custom_type1,custom_type2,...
  • Base type is a sqlpp23 data type (text, integral, floating_point, etc.)
  • Custom types are comma-separated PostgreSQL types to map to that base type

Example:

text,runlet_full_run_enum,analysis_beam_mode_enum

This maps PostgreSQL ENUMs to C++ std::optional<::sqlpp::text> for maximum compatibility.

How It's Used

During the CMake build process:

  1. The DBML schema is converted to PostgreSQL SQL (which includes ENUM type definitions)
  2. The sqlpp23-ddl2cpp script reads the SQL and the custom_types.csv file
  3. For each PostgreSQL ENUM type listed in the CSV, the generator creates a C++ text field instead of failing
  4. The generated C++ header allows you to read/write ENUM values as strings

This approach provides maximum flexibility and compatibility while maintaining type information in the database.

sqlpp23 Integration

The generated C++ header (qwparity_schema.h) provides type-safe table and column representations for use with sqlpp23, a C++ SQL library. This allows for compile-time SQL query validation and type safety.
⚡