[](https://github.com/dmwm/wmcoredb/actions/workflows/sql-lint.yml)
[](https://github.com/dmwm/wmcoredb/actions/workflows/mariadb-schema-test.yml)
[](https://github.com/dmwm/wmcoredb/actions/workflows/oracle-schema-test.yml)
[](https://github.com/dmwm/wmcoredb/actions/workflows/test-build.yml)
[](https://github.com/dmwm/wmcoredb/actions/workflows/release.yml)
[](https://pypi.org/project/wmcoredb/)
# WMCore Database Schema
Database schema definitions for WMCore components, including both MariaDB and Oracle backends.
WMBS (Workload Management Bookkeeping Service) provides the database schema for managing
workloads and jobs.
## Python Package
This repository is also available as a Python package on PyPI:
```bash
pip install wmcoredb
```
### Usage
This is a data-only package that provides SQL schema files. After installation, the SQL files are available in the package data directory.
To access the SQL files programmatically:
```python
import pkg_resources
# List all SQL files in the package
sql_files = pkg_resources.resource_listdir('wmcoredb', 'sql')
# Get the path to a specific SQL file
file_path = pkg_resources.resource_filename('wmcoredb', 'sql/mariadb/wmbs/create_wmbs_tables.sql')
# Read the SQL content
with open(file_path, 'r') as f:
sql_content = f.read()
```
## Development
For local development and testing:
```bash
# Build the package
python -m build
# Install locally for testing
pip install dist/wmcoredb-*.whl
```
## CI/CD Pipeline
The continuous integration pipeline is split into three workflows:
### SQL Linting
Validates SQL syntax and formatting using SQLFluff:
* MariaDB files using default SQLFluff rules
* Oracle files using custom rules defined in `.sqlfluff.oracle`
* Enforces consistent SQL style and formatting
* Runs on every push and pull request
### MariaDB Schema Validation
Automatically tests schema deployment in MariaDB:
* Runs only after successful linting
* Tests against multiple MariaDB versions:
- 10.6 (LTS)
- 10.11 (LTS)
- 11.4 (Latest)
* Deploys and validates:
- TestDB Schema
- WMBS Schema
- Agent Schema
- DBS3Buffer Schema
- BossAir Schema
- ResourceControl Schema
* Verifies table structures and relationships
* Checks for any critical database errors
### Oracle Schema Validation
Tests schema deployment in Oracle:
* Runs only after successful linting
* Uses Oracle XE 18.4.0-slim container
* Deploys and validates the same schemas as the MariaDB workflow:
- TestDB Schema
- WMBS Schema (tables, indexes, and initial data)
- Tier0 Schema (tables, indexes, functions, and initial data)
- Agent Schema
- DBS3Buffer Schema
- BossAir Schema
- ResourceControl Schema
* Comprehensive verification steps:
- Table structure validation
- Index creation and type verification
- Foreign key relationship checks
- Initial data population verification
- Cross-database compatibility with MariaDB
* Includes proper error handling and cleanup procedures
* Uses SQL*Plus for schema deployment and verification
## Directory Structure
The database schema files are organized as follows:
```
project_root/
├── src/
│ └── sql/ # Database schema files
│ ├── oracle/ # Oracle-specific SQL files
│ │ ├── wmbs/ # WMBS schema definitions
│ │ │ ├── create_wmbs_tables.sql # Table definitions with constraints
│ │ │ ├── create_wmbs_indexes.sql # Index definitions
│ │ │ └── initial_wmbs_data.sql # Static data for some tables
│ │ ├── agent/ # WMCore.Agent.Database schema
│ │ ├── bossair/ # WMCore.BossAir schema
│ │ ├── dbs3buffer/ # WMComponent.DBS3Buffer schema
│ │ ├── resourcecontrol/ # WMCore.ResourceControl schema
│ │ ├── testdb/ # WMQuality.TestDB schema
│ │ └── tier0/ # Tier0 schema definitions
│ │ ├── create_tier0_tables.sql # Table definitions with constraints
│ │ ├── create_tier0_indexes.sql # Index definitions
│ │ ├── create_tier0_functions.sql # Helper functions
│ │ └── initial_tier0_data.sql # Initial data for Tier0 tables
│ └── mariadb/ # MariaDB-specific SQL files
│ ├── wmbs/ # WMBS schema definitions
│ │ ├── create_wmbs_tables.sql # Table definitions with constraints
│ │ ├── create_wmbs_indexes.sql # Index definitions
│ │ └── initial_wmbs_data.sql # Static data for some tables
│ ├── agent/ # WMCore.Agent.Database schema
│ ├── bossair/ # WMCore.BossAir schema
│ ├── dbs3buffer/ # WMComponent.DBS3Buffer schema
│ ├── resourcecontrol/ # WMCore.ResourceControl schema
│ ├── testdb/ # WMQuality.TestDB schema
│ └── tier0/ # Tier0 schema definitions (NOT IMPLEMENTED)
└── src/python/ # Schema generation code (not included in package)
└── db/ # Legacy schema generation code
├── wmbs/
├── agent/
├── bossair/
├── dbs3buffer/
├── resourcecontrol/
└── testdb/
└── execute_wmbs_sql.py
```
## Schema Components
The WMAgent database schema consists of several components:
1. **WMBS** (`src/sql/{oracle,mariadb}/wmbs/`)
- Core workload and job management
- Tables for jobs, subscriptions, and file tracking
- Initial data for job states and subscription types
2. **Agent Database** (`src/sql/{oracle,mariadb}/agent/`)
- Core agent functionality
- Component and worker management
3. **BossAir** (`src/sql/{oracle,mariadb}/bossair/`)
- Job submission and tracking
- Grid and batch system integration
4. **DBS3Buffer** (`src/sql/{oracle,mariadb}/dbs3buffer/`)
- Dataset and file management
- Checksum and location tracking
5. **ResourceControl** (`src/sql/{oracle,mariadb}/resourcecontrol/`)
- Site and resource management
- Threshold control
6. **Test Database** (`src/sql/{oracle,mariadb}/testdb/`)
- Simple test tables for database validation
- Used for testing database connectivity and basic operations
- Includes tables with different data types and constraints
- Available for both Oracle and MariaDB backends
7. **Tier0 Schema** (`src/sql/{oracle,mariadb}/tier0/`)
- Run management and tracking
- Stream and dataset associations
- Lumi section processing
- Configuration management
- Workflow monitoring
## WMBS Schema Initialization
The WMBS schema is initialized first and consists of three files:
```
src/sql/{oracle,mariadb}/wmbs/
├── create_wmbs_tables.sql # Core WMBS tables
├── create_wmbs_indexes.sql # Indexes for performance
└── initial_wmbs_data.sql # Initial data for job states
```
These files are executed in order by `execute_wmbs_sql.py` to set up the base WMBS schema before other components are initialized.
## Database Backend Support
The schema supports two database backends:
- **Oracle** (`src/sql/oracle/`)
- Uses `NUMBER(11)` for integers
- Uses `VARCHAR2` for strings
- Uses `GENERATED BY DEFAULT AS IDENTITY` for auto-increment
- Includes sequences and functions where needed
- Uses slash (/) as statement terminator for DDL statements (CREATE TABLE, CREATE INDEX)
- Uses both semicolon (;) and slash (/) for PL/SQL blocks (functions, procedures, packages)
- Semicolon terminates the PL/SQL block
- Slash executes the block
- **MariaDB** (`src/sql/mariadb/`)
- Uses `INT` for integers
- Uses `VARCHAR` for strings
- Uses `AUTO_INCREMENT` for auto-increment
- Uses `ENGINE=InnoDB ROW_FORMAT=DYNAMIC`
- Includes equivalent functionality without sequences
## Database Compatibility
The SQL files are designed to be compatible with:
### MariaDB
- 10.6 (LTS)
- 10.11 (LTS)
- 11.4 (Latest)
### Oracle
- Oracle XE 18.4.0-slim container
- Oracle 19c
The CI pipeline automatically tests schema deployment against these versions to ensure compatibility.
## Database Documentation
For detailed database documentation, including Entity Relationship Diagrams (ERD), schema initialization flows, and module-specific diagrams, please refer to the [diagrams documentation](diagrams/README.md).
## Contributing
Please read [CONTRIBUTING.md](CONTRIBUTING.md) for details on our code of conduct and the process for submitting pull requests.
## Usage
To create the database schema:
1. For Oracle:
```sql
@src/sql/oracle/testdb/create_testdb.sql
@src/sql/oracle/tier0/create_tier0_tables.sql
@src/sql/oracle/tier0/create_tier0_indexes.sql
@src/sql/oracle/tier0/create_tier0_functions.sql
@src/sql/oracle/tier0/initial_tier0_data.sql
@src/sql/oracle/wmbs/create_wmbs_tables.sql
@src/sql/oracle/wmbs/create_wmbs_indexes.sql
@src/sql/oracle/wmbs/initial_wmbs_data.sql
```
2. For MariaDB:
```sql
source src/sql/mariadb/testdb/create_testdb.sql
source src/sql/mariadb/tier0/create_tier0_tables.sql
source src/sql/mariadb/tier0/create_tier0_indexes.sql
source src/sql/mariadb/tier0/create_tier0_functions.sql
source src/sql/mariadb/tier0/initial_tier0_data.sql
source src/sql/mariadb/wmbs/create_wmbs_tables.sql
source src/sql/mariadb/wmbs/create_wmbs_indexes.sql
source src/sql/mariadb/wmbs/initial_wmbs_data.sql
```
## Schema Generation
The SQL schema files are generated from Python code in `src/python/db/` (not included in the package). Each component has its own schema generation code:
```python
from WMCore.Database.DBCreator import DBCreator
class Create(DBCreator):
def __init__(self, logger=None, dbi=None, params=None):
# Schema definition in Python
```
The schema files can be executed using `execute_wmbs_sql.py`, which handles:
- Database backend detection
- Schema file location
- Transaction management
- Error handling
**Note:** The schema generation code in `src/python/db/` is for reference only and is not included in the PyPI package. The package only contains the final SQL files in `src/sql/`.
## Logs
Some relevant logs from the WMAgent 2.3.9.2 installation:
```
Start: Performing init_agent
init_agent: triggered.
Initializing WMAgent...
init_wmagent: MYSQL database: wmagent has been created
DEBUG:root:Log file ready
DEBUG:root:Using SQLAlchemy v.1.4.54
INFO:root:Instantiating base WM DBInterface
DEBUG:root:Tables for WMCore.WMBS created
DEBUG:root:Tables for WMCore.Agent.Database created
DEBUG:root:Tables for WMComponent.DBS3Buffer created
DEBUG:root:Tables for WMCore.BossAir created
DEBUG:root:Tables for WMCore.ResourceControl created
checking default database connection
default database connection tested
...
_sql_write_agentid: Preserving the current WMA_BUILD_ID and HostName at database: wmagent.
_sql_write_agentid: Creating wma_init table at database: wmagent
_sql_write_agentid: Inserting current Agent's build id and hostname at database: wmagent
_sql_dumpSchema: Dumping the current SQL schema of database: wmagent to /data/srv/wmagent/2.3.9/config/.wmaSchemaFile.sql
Done: Performing init_agent
```
## WMAgent DB Initialization
It starts in the CMSKubernetes [init.sh](https://github.com/dmwm/CMSKubernetes/blob/master/docker/pypi/wmagent/init.sh#L465) script, which executes `init_agent()` method from the CMSKubernetes [manage](https://github.com/dmwm/CMSKubernetes/blob/master/docker/pypi/wmagent/bin/manage#L112) script.
The database optios are enriched dependent on the database flavor, such as:
```bash
case $AGENT_FLAVOR in
'mysql')
_exec_mysql "create database if not exists $wmaDBName"
local database_options="--mysql_url=mysql://$MDB_USER:$MDB_PASS@$MDB_HOST/$wmaDBName "
'oracle')
local database_options="--coredb_url=oracle://$ORACLE_USER:$ORACLE_PASS@$ORACLE_TNS "
```
It then executes WMCore code, calling a script called [wmagent-mod-config](https://github.com/dmwm/WMCore/blob/master/bin/wmagent-mod-config).
with command line arguments like:
```bash
wmagent-mod-config $database_options \
--input=$WMA_CONFIG_DIR/config-template.py \
--output=$WMA_CONFIG_DIR/config.py \
```
which internally parses the command line arguments into `parameters` and modifies the standard [WMAgentConfig.py](https://github.com/dmwm/WMCore/blob/master/etc/WMAgentConfig.py), saving it out as the new WMAgent configuration file, with something like:
```
cfg = modifyConfiguration(cfg, **parameters)
saveConfiguration(cfg, outputFile)
```
With the WMAgent configuration file properly updated, named `config.py`, now the `manage` script calls [wmcore-db-init](https://github.com/dmwm/WMCore/blob/master/bin/wmcore-db-init), with arguments like:
```bash
wmcore-db-init --config $WMA_CONFIG_DIR/config.py --create --modules=WMCore.WMBS,WMCore.Agent.Database,WMComponent.DBS3Buffer,WMCore.BossAir,WMCore.ResourceControl;
```
This `wmcore-db-init` script itself calls the [WMInit.py](https://github.com/dmwm/WMCore/blob/master/src/python/WMCore/WMInit.py) script, executing basically the next four commands:
```python
wmInit = WMInit()
wmInit.setLogging('wmcoreD', 'wmcoreD', logExists = False, logLevel = logging.DEBUG)
wmInit.setDatabaseConnection(dbConfig=config.CoreDatabase.connectUrl, dialect=dialect, socketLoc = socket)
wmInit.setSchema(modules, params = params)
```
In summary, the WMAgent database schema is an aggregation of the schema defined under each of the following WMAgent python directories:
```
WMCore.WMBS --> originally under src/python/db/wmbs
WMCore.Agent.Database --> originally under src/python/db/agent
WMCore.BossAir --> originally under src/python/db/bossair
WMCore.ResourceControl --> originally under src/python/db/resourcecontrol
WMComponent.DBS3Buffer --> originally under src/python/db/dbs3buffer
```
The `wmcore-db-init` script itself calls the [WMInit.py](https://github.com/dmwm/WMCore/blob/master/src/python/WMCore/WMInit.py) script, executing basically the next four commands:
```python
wmInit = WMInit()
wmInit.setLogging('wmcoreD', 'wmcoreD', logExists = False, logLevel = logging.DEBUG)
```
## Tier0 Schema
The Tier0 schema is designed to support the Tier0 data processing system. It includes tables for:
- Run management and tracking
- Stream and dataset associations
- Lumi section processing
- Configuration management
- Workflow monitoring
### Oracle Implementation
The Oracle implementation uses modern features like:
- IDENTITY columns for auto-incrementing IDs
- Inline foreign key constraints
- Organization index tables for performance
- Deterministic functions for state validation
The schema initialization includes:
- Table definitions with constraints
- Index definitions for performance
- Helper functions for state validation
- Initial data for run states, processing styles, and event scenarios
### MariaDB Implementation
Tier0 system does not - yet - support multiple database backends. For the moment, we have not converted the Tier0 schema to be compliant with MariaDB/MySQL.
## Test Database Schema
The Test Database schema provides a simple set of tables for testing database connectivity and basic operations. It includes:
- Tables with different data types (INT, VARCHAR, DECIMAL)
- Primary key constraints
- Table and column comments
- Cross-database compatibility
### Oracle Implementation
The Oracle implementation uses:
- NUMBER for numeric columns
- VARCHAR2 for string columns
- Table and column comments
- Primary key constraints
### MariaDB Implementation
The MariaDB implementation provides equivalent functionality using:
- INT and DECIMAL for numeric columns
- VARCHAR for string columns
- InnoDB engine specification
- Compatible comment syntax
Raw data
{
"_id": null,
"home_page": null,
"name": "wmcoredb",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": "WMCore Team <cms-wmcore-team@cern.ch>",
"keywords": "database, schema, wmcore, oracle, mariadb, cms, workload-management",
"author": null,
"author_email": "WMCore Team <cms-wmcore-team@cern.ch>",
"download_url": "https://files.pythonhosted.org/packages/08/ef/828589c63e75fff033718ef3d01ecd8a7c3ac06234a2e4f1d31fce56ecfc/wmcoredb-0.8.7.tar.gz",
"platform": null,
"description": "[](https://github.com/dmwm/wmcoredb/actions/workflows/sql-lint.yml)\n[](https://github.com/dmwm/wmcoredb/actions/workflows/mariadb-schema-test.yml)\n[](https://github.com/dmwm/wmcoredb/actions/workflows/oracle-schema-test.yml)\n[](https://github.com/dmwm/wmcoredb/actions/workflows/test-build.yml)\n[](https://github.com/dmwm/wmcoredb/actions/workflows/release.yml)\n[](https://pypi.org/project/wmcoredb/)\n\n# WMCore Database Schema\n\nDatabase schema definitions for WMCore components, including both MariaDB and Oracle backends.\n\nWMBS (Workload Management Bookkeeping Service) provides the database schema for managing \nworkloads and jobs.\n\n## Python Package\n\nThis repository is also available as a Python package on PyPI:\n\n```bash\npip install wmcoredb\n```\n\n### Usage\n\nThis is a data-only package that provides SQL schema files. After installation, the SQL files are available in the package data directory.\n\nTo access the SQL files programmatically:\n\n```python\nimport pkg_resources\n\n# List all SQL files in the package\nsql_files = pkg_resources.resource_listdir('wmcoredb', 'sql')\n\n# Get the path to a specific SQL file\nfile_path = pkg_resources.resource_filename('wmcoredb', 'sql/mariadb/wmbs/create_wmbs_tables.sql')\n\n# Read the SQL content\nwith open(file_path, 'r') as f:\n sql_content = f.read()\n```\n\n## Development\n\nFor local development and testing:\n\n```bash\n# Build the package\npython -m build\n\n# Install locally for testing\npip install dist/wmcoredb-*.whl\n```\n\n## CI/CD Pipeline\n\nThe continuous integration pipeline is split into three workflows:\n\n### SQL Linting\nValidates SQL syntax and formatting using SQLFluff:\n* MariaDB files using default SQLFluff rules\n* Oracle files using custom rules defined in `.sqlfluff.oracle`\n* Enforces consistent SQL style and formatting\n* Runs on every push and pull request\n\n### MariaDB Schema Validation\nAutomatically tests schema deployment in MariaDB:\n* Runs only after successful linting\n* Tests against multiple MariaDB versions:\n - 10.6 (LTS)\n - 10.11 (LTS)\n - 11.4 (Latest)\n* Deploys and validates:\n - TestDB Schema\n - WMBS Schema\n - Agent Schema\n - DBS3Buffer Schema\n - BossAir Schema\n - ResourceControl Schema\n* Verifies table structures and relationships\n* Checks for any critical database errors\n\n### Oracle Schema Validation\nTests schema deployment in Oracle:\n* Runs only after successful linting\n* Uses Oracle XE 18.4.0-slim container\n* Deploys and validates the same schemas as the MariaDB workflow:\n - TestDB Schema\n - WMBS Schema (tables, indexes, and initial data)\n - Tier0 Schema (tables, indexes, functions, and initial data)\n - Agent Schema\n - DBS3Buffer Schema\n - BossAir Schema\n - ResourceControl Schema\n* Comprehensive verification steps:\n - Table structure validation\n - Index creation and type verification\n - Foreign key relationship checks\n - Initial data population verification\n - Cross-database compatibility with MariaDB\n* Includes proper error handling and cleanup procedures\n* Uses SQL*Plus for schema deployment and verification\n\n## Directory Structure\n\nThe database schema files are organized as follows:\n\n```\nproject_root/\n\u251c\u2500\u2500 src/\n\u2502 \u2514\u2500\u2500 sql/ # Database schema files\n\u2502 \u251c\u2500\u2500 oracle/ # Oracle-specific SQL files\n\u2502 \u2502 \u251c\u2500\u2500 wmbs/ # WMBS schema definitions\n\u2502 \u2502 \u2502 \u251c\u2500\u2500 create_wmbs_tables.sql # Table definitions with constraints\n\u2502 \u2502 \u2502 \u251c\u2500\u2500 create_wmbs_indexes.sql # Index definitions\n\u2502 \u2502 \u2502 \u2514\u2500\u2500 initial_wmbs_data.sql # Static data for some tables\n\u2502 \u2502 \u251c\u2500\u2500 agent/ # WMCore.Agent.Database schema\n\u2502 \u2502 \u251c\u2500\u2500 bossair/ # WMCore.BossAir schema\n\u2502 \u2502 \u251c\u2500\u2500 dbs3buffer/ # WMComponent.DBS3Buffer schema\n\u2502 \u2502 \u251c\u2500\u2500 resourcecontrol/ # WMCore.ResourceControl schema\n\u2502 \u2502 \u251c\u2500\u2500 testdb/ # WMQuality.TestDB schema\n\u2502 \u2502 \u2514\u2500\u2500 tier0/ # Tier0 schema definitions\n\u2502 \u2502 \u251c\u2500\u2500 create_tier0_tables.sql # Table definitions with constraints\n\u2502 \u2502 \u251c\u2500\u2500 create_tier0_indexes.sql # Index definitions\n\u2502 \u2502 \u251c\u2500\u2500 create_tier0_functions.sql # Helper functions\n\u2502 \u2502 \u2514\u2500\u2500 initial_tier0_data.sql # Initial data for Tier0 tables\n\u2502 \u2514\u2500\u2500 mariadb/ # MariaDB-specific SQL files\n\u2502 \u251c\u2500\u2500 wmbs/ # WMBS schema definitions\n\u2502 \u2502 \u251c\u2500\u2500 create_wmbs_tables.sql # Table definitions with constraints\n\u2502 \u2502 \u251c\u2500\u2500 create_wmbs_indexes.sql # Index definitions\n\u2502 \u2502 \u2514\u2500\u2500 initial_wmbs_data.sql # Static data for some tables\n\u2502 \u251c\u2500\u2500 agent/ # WMCore.Agent.Database schema\n\u2502 \u251c\u2500\u2500 bossair/ # WMCore.BossAir schema\n\u2502 \u251c\u2500\u2500 dbs3buffer/ # WMComponent.DBS3Buffer schema\n\u2502 \u251c\u2500\u2500 resourcecontrol/ # WMCore.ResourceControl schema\n\u2502 \u251c\u2500\u2500 testdb/ # WMQuality.TestDB schema\n\u2502 \u2514\u2500\u2500 tier0/ # Tier0 schema definitions (NOT IMPLEMENTED)\n\u2514\u2500\u2500 src/python/ # Schema generation code (not included in package)\n \u2514\u2500\u2500 db/ # Legacy schema generation code\n \u251c\u2500\u2500 wmbs/\n \u251c\u2500\u2500 agent/\n \u251c\u2500\u2500 bossair/\n \u251c\u2500\u2500 dbs3buffer/\n \u251c\u2500\u2500 resourcecontrol/\n \u2514\u2500\u2500 testdb/\n \u2514\u2500\u2500 execute_wmbs_sql.py\n```\n\n## Schema Components\n\nThe WMAgent database schema consists of several components:\n\n1. **WMBS** (`src/sql/{oracle,mariadb}/wmbs/`)\n - Core workload and job management\n - Tables for jobs, subscriptions, and file tracking\n - Initial data for job states and subscription types\n\n2. **Agent Database** (`src/sql/{oracle,mariadb}/agent/`)\n - Core agent functionality\n - Component and worker management\n\n3. **BossAir** (`src/sql/{oracle,mariadb}/bossair/`)\n - Job submission and tracking\n - Grid and batch system integration\n\n4. **DBS3Buffer** (`src/sql/{oracle,mariadb}/dbs3buffer/`)\n - Dataset and file management\n - Checksum and location tracking\n\n5. **ResourceControl** (`src/sql/{oracle,mariadb}/resourcecontrol/`)\n - Site and resource management\n - Threshold control\n\n6. **Test Database** (`src/sql/{oracle,mariadb}/testdb/`)\n - Simple test tables for database validation\n - Used for testing database connectivity and basic operations\n - Includes tables with different data types and constraints\n - Available for both Oracle and MariaDB backends\n\n7. **Tier0 Schema** (`src/sql/{oracle,mariadb}/tier0/`)\n - Run management and tracking\n - Stream and dataset associations\n - Lumi section processing\n - Configuration management\n - Workflow monitoring\n\n## WMBS Schema Initialization\n\nThe WMBS schema is initialized first and consists of three files:\n\n```\nsrc/sql/{oracle,mariadb}/wmbs/\n\u251c\u2500\u2500 create_wmbs_tables.sql # Core WMBS tables\n\u251c\u2500\u2500 create_wmbs_indexes.sql # Indexes for performance\n\u2514\u2500\u2500 initial_wmbs_data.sql # Initial data for job states\n```\n\nThese files are executed in order by `execute_wmbs_sql.py` to set up the base WMBS schema before other components are initialized.\n\n## Database Backend Support\n\nThe schema supports two database backends:\n\n- **Oracle** (`src/sql/oracle/`)\n - Uses `NUMBER(11)` for integers\n - Uses `VARCHAR2` for strings\n - Uses `GENERATED BY DEFAULT AS IDENTITY` for auto-increment\n - Includes sequences and functions where needed\n - Uses slash (/) as statement terminator for DDL statements (CREATE TABLE, CREATE INDEX)\n - Uses both semicolon (;) and slash (/) for PL/SQL blocks (functions, procedures, packages)\n - Semicolon terminates the PL/SQL block\n - Slash executes the block\n\n- **MariaDB** (`src/sql/mariadb/`)\n - Uses `INT` for integers\n - Uses `VARCHAR` for strings\n - Uses `AUTO_INCREMENT` for auto-increment\n - Uses `ENGINE=InnoDB ROW_FORMAT=DYNAMIC`\n - Includes equivalent functionality without sequences\n\n## Database Compatibility\n\nThe SQL files are designed to be compatible with:\n\n### MariaDB\n- 10.6 (LTS)\n- 10.11 (LTS)\n- 11.4 (Latest)\n\n### Oracle\n- Oracle XE 18.4.0-slim container\n- Oracle 19c\n\nThe CI pipeline automatically tests schema deployment against these versions to ensure compatibility.\n\n## Database Documentation\n\nFor detailed database documentation, including Entity Relationship Diagrams (ERD), schema initialization flows, and module-specific diagrams, please refer to the [diagrams documentation](diagrams/README.md).\n\n## Contributing\n\nPlease read [CONTRIBUTING.md](CONTRIBUTING.md) for details on our code of conduct and the process for submitting pull requests.\n\n## Usage\n\nTo create the database schema:\n\n1. For Oracle:\n```sql\n@src/sql/oracle/testdb/create_testdb.sql\n@src/sql/oracle/tier0/create_tier0_tables.sql\n@src/sql/oracle/tier0/create_tier0_indexes.sql\n@src/sql/oracle/tier0/create_tier0_functions.sql\n@src/sql/oracle/tier0/initial_tier0_data.sql\n@src/sql/oracle/wmbs/create_wmbs_tables.sql\n@src/sql/oracle/wmbs/create_wmbs_indexes.sql\n@src/sql/oracle/wmbs/initial_wmbs_data.sql\n```\n\n2. For MariaDB:\n```sql\nsource src/sql/mariadb/testdb/create_testdb.sql\nsource src/sql/mariadb/tier0/create_tier0_tables.sql\nsource src/sql/mariadb/tier0/create_tier0_indexes.sql\nsource src/sql/mariadb/tier0/create_tier0_functions.sql\nsource src/sql/mariadb/tier0/initial_tier0_data.sql\nsource src/sql/mariadb/wmbs/create_wmbs_tables.sql\nsource src/sql/mariadb/wmbs/create_wmbs_indexes.sql\nsource src/sql/mariadb/wmbs/initial_wmbs_data.sql\n```\n\n## Schema Generation\n\nThe SQL schema files are generated from Python code in `src/python/db/` (not included in the package). Each component has its own schema generation code:\n\n```python\nfrom WMCore.Database.DBCreator import DBCreator\n\nclass Create(DBCreator):\n def __init__(self, logger=None, dbi=None, params=None):\n # Schema definition in Python\n```\n\nThe schema files can be executed using `execute_wmbs_sql.py`, which handles:\n- Database backend detection\n- Schema file location\n- Transaction management\n- Error handling\n\n**Note:** The schema generation code in `src/python/db/` is for reference only and is not included in the PyPI package. The package only contains the final SQL files in `src/sql/`.\n\n## Logs\n\nSome relevant logs from the WMAgent 2.3.9.2 installation:\n```\nStart: Performing init_agent\ninit_agent: triggered.\nInitializing WMAgent...\ninit_wmagent: MYSQL database: wmagent has been created\nDEBUG:root:Log file ready\nDEBUG:root:Using SQLAlchemy v.1.4.54\nINFO:root:Instantiating base WM DBInterface\nDEBUG:root:Tables for WMCore.WMBS created\nDEBUG:root:Tables for WMCore.Agent.Database created\nDEBUG:root:Tables for WMComponent.DBS3Buffer created\nDEBUG:root:Tables for WMCore.BossAir created\nDEBUG:root:Tables for WMCore.ResourceControl created\nchecking default database connection\ndefault database connection tested\n...\n_sql_write_agentid: Preserving the current WMA_BUILD_ID and HostName at database: wmagent.\n_sql_write_agentid: Creating wma_init table at database: wmagent\n_sql_write_agentid: Inserting current Agent's build id and hostname at database: wmagent\n_sql_dumpSchema: Dumping the current SQL schema of database: wmagent to /data/srv/wmagent/2.3.9/config/.wmaSchemaFile.sql\nDone: Performing init_agent\n```\n## WMAgent DB Initialization\n\nIt starts in the CMSKubernetes [init.sh](https://github.com/dmwm/CMSKubernetes/blob/master/docker/pypi/wmagent/init.sh#L465) script, which executes `init_agent()` method from the CMSKubernetes [manage](https://github.com/dmwm/CMSKubernetes/blob/master/docker/pypi/wmagent/bin/manage#L112) script.\n\nThe database optios are enriched dependent on the database flavor, such as:\n```bash\n case $AGENT_FLAVOR in\n 'mysql')\n _exec_mysql \"create database if not exists $wmaDBName\"\n local database_options=\"--mysql_url=mysql://$MDB_USER:$MDB_PASS@$MDB_HOST/$wmaDBName \"\n 'oracle')\n local database_options=\"--coredb_url=oracle://$ORACLE_USER:$ORACLE_PASS@$ORACLE_TNS \"\n```\n\nIt then executes WMCore code, calling a script called [wmagent-mod-config](https://github.com/dmwm/WMCore/blob/master/bin/wmagent-mod-config).\n\nwith command line arguments like:\n```bash\n wmagent-mod-config $database_options \\\n --input=$WMA_CONFIG_DIR/config-template.py \\\n --output=$WMA_CONFIG_DIR/config.py \\\n```\n\nwhich internally parses the command line arguments into `parameters` and modifies the standard [WMAgentConfig.py](https://github.com/dmwm/WMCore/blob/master/etc/WMAgentConfig.py), saving it out as the new WMAgent configuration file, with something like:\n```\n cfg = modifyConfiguration(cfg, **parameters)\n saveConfiguration(cfg, outputFile)\n```\n\nWith the WMAgent configuration file properly updated, named `config.py`, now the `manage` script calls [wmcore-db-init](https://github.com/dmwm/WMCore/blob/master/bin/wmcore-db-init), with arguments like:\n\n```bash\nwmcore-db-init --config $WMA_CONFIG_DIR/config.py --create --modules=WMCore.WMBS,WMCore.Agent.Database,WMComponent.DBS3Buffer,WMCore.BossAir,WMCore.ResourceControl;\n```\n\nThis `wmcore-db-init` script itself calls the [WMInit.py](https://github.com/dmwm/WMCore/blob/master/src/python/WMCore/WMInit.py) script, executing basically the next four commands:\n```python\nwmInit = WMInit()\nwmInit.setLogging('wmcoreD', 'wmcoreD', logExists = False, logLevel = logging.DEBUG)\nwmInit.setDatabaseConnection(dbConfig=config.CoreDatabase.connectUrl, dialect=dialect, socketLoc = socket)\nwmInit.setSchema(modules, params = params)\n```\n\nIn summary, the WMAgent database schema is an aggregation of the schema defined under each of the following WMAgent python directories:\n```\nWMCore.WMBS --> originally under src/python/db/wmbs\nWMCore.Agent.Database --> originally under src/python/db/agent\nWMCore.BossAir --> originally under src/python/db/bossair\nWMCore.ResourceControl --> originally under src/python/db/resourcecontrol\nWMComponent.DBS3Buffer --> originally under src/python/db/dbs3buffer\n```\n\nThe `wmcore-db-init` script itself calls the [WMInit.py](https://github.com/dmwm/WMCore/blob/master/src/python/WMCore/WMInit.py) script, executing basically the next four commands:\n```python\nwmInit = WMInit()\nwmInit.setLogging('wmcoreD', 'wmcoreD', logExists = False, logLevel = logging.DEBUG)\n```\n\n## Tier0 Schema\n\nThe Tier0 schema is designed to support the Tier0 data processing system. It includes tables for:\n\n- Run management and tracking\n- Stream and dataset associations\n- Lumi section processing\n- Configuration management\n- Workflow monitoring\n\n### Oracle Implementation\n\nThe Oracle implementation uses modern features like:\n- IDENTITY columns for auto-incrementing IDs\n- Inline foreign key constraints\n- Organization index tables for performance\n- Deterministic functions for state validation\n\nThe schema initialization includes:\n- Table definitions with constraints\n- Index definitions for performance\n- Helper functions for state validation\n- Initial data for run states, processing styles, and event scenarios\n\n### MariaDB Implementation\n\nTier0 system does not - yet - support multiple database backends. For the moment, we have not converted the Tier0 schema to be compliant with MariaDB/MySQL.\n\n## Test Database Schema\n\nThe Test Database schema provides a simple set of tables for testing database connectivity and basic operations. It includes:\n\n- Tables with different data types (INT, VARCHAR, DECIMAL)\n- Primary key constraints\n- Table and column comments\n- Cross-database compatibility\n\n### Oracle Implementation\n\nThe Oracle implementation uses:\n- NUMBER for numeric columns\n- VARCHAR2 for string columns\n- Table and column comments\n- Primary key constraints\n\n### MariaDB Implementation\n\nThe MariaDB implementation provides equivalent functionality using:\n- INT and DECIMAL for numeric columns\n- VARCHAR for string columns\n- InnoDB engine specification\n- Compatible comment syntax\n",
"bugtrack_url": null,
"license": null,
"summary": "WMCore Database Schema - Database schema definitions for WMCore components",
"version": "0.8.7",
"project_urls": {
"Changelog": "https://github.com/dmwm/wmcoredb/releases",
"Documentation": "https://github.com/dmwm/wmcoredb#readme",
"Homepage": "https://github.com/dmwm/wmcoredb",
"Issues": "https://github.com/dmwm/wmcoredb/issues",
"Repository": "https://github.com/dmwm/wmcoredb"
},
"split_keywords": [
"database",
" schema",
" wmcore",
" oracle",
" mariadb",
" cms",
" workload-management"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "4650c34e1cfc87436124d22c734c68fcb418fede876b3c5abf72cbeda1feafda",
"md5": "bb9555a7eeb39342bea95eb1789bd037",
"sha256": "fe7823d3c77a48866042e764d4135fb220898953619a3834ce8dc2a685daf2ee"
},
"downloads": -1,
"filename": "wmcoredb-0.8.7-py3-none-any.whl",
"has_sig": false,
"md5_digest": "bb9555a7eeb39342bea95eb1789bd037",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 31214,
"upload_time": "2025-07-09T03:56:43",
"upload_time_iso_8601": "2025-07-09T03:56:43.175301Z",
"url": "https://files.pythonhosted.org/packages/46/50/c34e1cfc87436124d22c734c68fcb418fede876b3c5abf72cbeda1feafda/wmcoredb-0.8.7-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "08ef828589c63e75fff033718ef3d01ecd8a7c3ac06234a2e4f1d31fce56ecfc",
"md5": "ce1ebc7a1a8f1e81a4207606e55acf20",
"sha256": "6198f512308c115c24134ab47cea88f1ec69e06edd09b5e51d941768038c1c2b"
},
"downloads": -1,
"filename": "wmcoredb-0.8.7.tar.gz",
"has_sig": false,
"md5_digest": "ce1ebc7a1a8f1e81a4207606e55acf20",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 40332,
"upload_time": "2025-07-09T03:56:45",
"upload_time_iso_8601": "2025-07-09T03:56:45.949736Z",
"url": "https://files.pythonhosted.org/packages/08/ef/828589c63e75fff033718ef3d01ecd8a7c3ac06234a2e4f1d31fce56ecfc/wmcoredb-0.8.7.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-07-09 03:56:45",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "dmwm",
"github_project": "wmcoredb",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "wmcoredb"
}
JSON
