[](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
The package provides utility functions to easily locate and access SQL schema files:
```python
import wmcoredb
# Get the path to a specific SQL file
file_path = wmcoredb.get_sql_file("wmbs", "create_wmbs_tables.sql", "mariadb")
# Get the content of a SQL file
sql_content = wmcoredb.get_sql_content("wmbs", "create_wmbs_tables.sql", "mariadb")
# List available modules
modules = wmcoredb.list_modules("mariadb") # ['agent', 'bossair', 'dbs3buffer', 'resourcecontrol', 'testdb', 'wmbs']
# List SQL files in a module
sql_files = wmcoredb.list_sql_files("wmbs", "mariadb") # ['create_wmbs_indexes.sql', 'create_wmbs_tables.sql', 'initial_wmbs_data.sql']
# List available backends
backends = wmcoredb.list_backends() # ['mariadb', 'oracle']
```
### API Reference
- `get_sql_file(module_name, file_name, backend="mariadb")` - Get file path
- `get_sql_content(module_name, file_name, backend="mariadb")` - Get file content
- `list_sql_files(module_name=None, backend="mariadb")` - List SQL files
- `list_modules(backend="mariadb")` - List available modules
- `list_backends()` - List available backends
## Development
For local development and testing:
```bash
# Build the package
python -m build
# Install locally for testing
pip install dist/wmcoredb-*.whl
# Test the package functionality
python -c "import wmcoredb; print(wmcoredb.list_backends())"
```
## CI/CD Pipeline
The continuous integration pipeline is split into five 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
### Test Package Build
Validates the Python package build process:
* Builds the package using modern Python packaging tools
* Tests package installation and import functionality
* Verifies SQL file accessibility through the package API
* Runs on every push and pull request
### Release to PyPI
Automatically publishes releases to PyPI:
* Triggers on git tags (e.g., `1.0.0`)
* Updates version in `pyproject.toml` during build
* Publishes to PyPI with proper metadata
* Creates GitHub releases with changelog
## Directory Structure
The database schema files are organized as follows:
```
project_root/
├── src/
│ └── wmcoredb/ # Python package
│ ├── __init__.py # Package API functions
│ └── 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
└── 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/wmcoredb/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/wmcoredb/sql/{oracle,mariadb}/agent/`)
- Core agent functionality
- Component and worker management
3. **BossAir** (`src/wmcoredb/sql/{oracle,mariadb}/bossair/`)
- Job submission and tracking
- Grid and batch system integration
4. **DBS3Buffer** (`src/wmcoredb/sql/{oracle,mariadb}/dbs3buffer/`)
- Dataset and file management
- Checksum and location tracking
5. **ResourceControl** (`src/wmcoredb/sql/{oracle,mariadb}/resourcecontrol/`)
- Site and resource management
- Threshold control
6. **Test Database** (`src/wmcoredb/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/wmcoredb/sql/{oracle,mariadb}/tier0/`)
- Run management and tracking
- Stream and dataset associations
- Lumi section processing
- Configuration management
- Workflow monitoring
- Available only for Oracle
## WMBS Schema Initialization
The WMBS schema is initialized first and consists of three files:
```
src/wmcoredb/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/wmcoredb/sql/oracle/`)
- Uses `NUMBER(11)` for integers
- Uses `VARCHAR2` 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/wmcoredb/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
### Using the Python Package
The recommended way to access SQL files is through the Python package:
```python
import wmcoredb
# Get SQL content for Oracle WMBS tables
sql_content = wmcoredb.get_sql_content("wmbs", "create_wmbs_tables.sql", "oracle")
# List all available modules for MariaDB
modules = wmcoredb.list_modules("mariadb")
# Get all SQL files for a specific module
files = wmcoredb.list_sql_files("tier0", "oracle")
```
### Direct File Access
To create the database schema directly from SQL files:
#### For Oracle:
```sql
@src/wmcoredb/sql/oracle/testdb/create_testdb.sql
@src/wmcoredb/sql/oracle/tier0/create_tier0_tables.sql
@src/wmcoredb/sql/oracle/tier0/create_tier0_indexes.sql
@src/wmcoredb/sql/oracle/tier0/create_tier0_functions.sql
@src/wmcoredb/sql/oracle/tier0/initial_tier0_data.sql
@src/wmcoredb/sql/oracle/wmbs/create_wmbs_tables.sql
@src/wmcoredb/sql/oracle/wmbs/create_wmbs_indexes.sql
@src/wmcoredb/sql/oracle/wmbs/initial_wmbs_data.sql
```
#### For MariaDB:
```sql
source src/wmcoredb/sql/mariadb/testdb/create_testdb.sql
source src/wmcoredb/sql/mariadb/tier0/create_tier0_tables.sql
source src/wmcoredb/sql/mariadb/tier0/create_tier0_indexes.sql
source src/wmcoredb/sql/mariadb/tier0/create_tier0_functions.sql
source src/wmcoredb/sql/mariadb/tier0/initial_tier0_data.sql
source src/wmcoredb/sql/mariadb/wmbs/create_wmbs_tables.sql
source src/wmcoredb/sql/mariadb/wmbs/create_wmbs_indexes.sql
source src/wmcoredb/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/wmcoredb/sql/`.
## Package Testing
To test the package functionality locally:
```bash
# Build the package
python -m build
# Install locally
pip install dist/wmcoredb-*.whl
# Test basic functionality
python -c
import wmcoredb
print('Backends:', wmcoredb.list_backends())
print('MariaDB modules:', wmcoredb.list_modules('mariadb'))
print(Oracle modules:', wmcoredb.list_modules(oracle'))
print(WMBS files:, wmcoredb.list_sql_files('wmbs, 'mariadb'))
"
# Test SQL content access
python -c
import wmcoredb
content = wmcoredb.get_sql_content('testdb', create_testdb.sql', mariadb')
print(TestDB SQL length:', len(content))
"
```
## Logs
Some relevant logs from the WMAgent 2.3.9 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 v0.10.54INFO: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#L465pt, 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 inmysql')
_exec_mysql "create database if not exists $wmaDBName"
local database_options=--mysql_url=mysql://$MDB_USER:$MDB_PASS@$MDB_HOST/$wmaDBNameoracle')
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 standardWMAgentConfig.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
```
## 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
The MariaDB implementation provides equivalent functionality using:
- INT and DECIMAL for numeric columns
- VARCHAR for string columns
- InnoDB engine specification
- Compatible comment syntax
- Auto-incrementing primary keys
## 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/83/4c/fae6ad52540b4ceaf1ef3bf939b9854251b734231754b7d825df903a9f57/wmcoredb-0.9.3.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\nThe package provides utility functions to easily locate and access SQL schema files:\n\n```python\nimport wmcoredb\n\n# Get the path to a specific SQL file\nfile_path = wmcoredb.get_sql_file(\"wmbs\", \"create_wmbs_tables.sql\", \"mariadb\")\n\n# Get the content of a SQL file\nsql_content = wmcoredb.get_sql_content(\"wmbs\", \"create_wmbs_tables.sql\", \"mariadb\")\n\n# List available modules\nmodules = wmcoredb.list_modules(\"mariadb\") # ['agent', 'bossair', 'dbs3buffer', 'resourcecontrol', 'testdb', 'wmbs']\n\n# List SQL files in a module\nsql_files = wmcoredb.list_sql_files(\"wmbs\", \"mariadb\") # ['create_wmbs_indexes.sql', 'create_wmbs_tables.sql', 'initial_wmbs_data.sql']\n\n# List available backends\nbackends = wmcoredb.list_backends() # ['mariadb', 'oracle']\n```\n\n### API Reference\n\n- `get_sql_file(module_name, file_name, backend=\"mariadb\")` - Get file path\n- `get_sql_content(module_name, file_name, backend=\"mariadb\")` - Get file content\n- `list_sql_files(module_name=None, backend=\"mariadb\")` - List SQL files\n- `list_modules(backend=\"mariadb\")` - List available modules\n- `list_backends()` - List available backends\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# Test the package functionality\npython -c \"import wmcoredb; print(wmcoredb.list_backends())\"\n```\n\n## CI/CD Pipeline\n\nThe continuous integration pipeline is split into five 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### Test Package Build\nValidates the Python package build process:\n* Builds the package using modern Python packaging tools\n* Tests package installation and import functionality\n* Verifies SQL file accessibility through the package API\n* Runs on every push and pull request\n\n### Release to PyPI\nAutomatically publishes releases to PyPI:\n* Triggers on git tags (e.g., `1.0.0`)\n* Updates version in `pyproject.toml` during build\n* Publishes to PyPI with proper metadata\n* Creates GitHub releases with changelog\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 wmcoredb/ # Python package\n\u2502 \u251c\u2500\u2500 __init__.py # Package API functions\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\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:\n1. **WMBS** (`src/wmcoredb/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/wmcoredb/sql/{oracle,mariadb}/agent/`)\n - Core agent functionality\n - Component and worker management\n\n3. **BossAir** (`src/wmcoredb/sql/{oracle,mariadb}/bossair/`)\n - Job submission and tracking\n - Grid and batch system integration\n\n4. **DBS3Buffer** (`src/wmcoredb/sql/{oracle,mariadb}/dbs3buffer/`)\n - Dataset and file management\n - Checksum and location tracking\n\n5. **ResourceControl** (`src/wmcoredb/sql/{oracle,mariadb}/resourcecontrol/`)\n - Site and resource management\n - Threshold control\n\n6. **Test Database** (`src/wmcoredb/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/wmcoredb/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 - Available only for Oracle\n\n## WMBS Schema Initialization\n\nThe WMBS schema is initialized first and consists of three files:\n\n```\nsrc/wmcoredb/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/wmcoredb/sql/oracle/`)\n - Uses `NUMBER(11)` for integers\n - Uses `VARCHAR2` 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/wmcoredb/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\n### Using the Python Package\n\nThe recommended way to access SQL files is through the Python package:\n\n```python\nimport wmcoredb\n\n# Get SQL content for Oracle WMBS tables\nsql_content = wmcoredb.get_sql_content(\"wmbs\", \"create_wmbs_tables.sql\", \"oracle\")\n\n# List all available modules for MariaDB\nmodules = wmcoredb.list_modules(\"mariadb\")\n\n# Get all SQL files for a specific module\nfiles = wmcoredb.list_sql_files(\"tier0\", \"oracle\")\n```\n\n### Direct File Access\n\nTo create the database schema directly from SQL files:\n\n#### For Oracle:\n```sql\n@src/wmcoredb/sql/oracle/testdb/create_testdb.sql\n@src/wmcoredb/sql/oracle/tier0/create_tier0_tables.sql\n@src/wmcoredb/sql/oracle/tier0/create_tier0_indexes.sql\n@src/wmcoredb/sql/oracle/tier0/create_tier0_functions.sql\n@src/wmcoredb/sql/oracle/tier0/initial_tier0_data.sql\n@src/wmcoredb/sql/oracle/wmbs/create_wmbs_tables.sql\n@src/wmcoredb/sql/oracle/wmbs/create_wmbs_indexes.sql\n@src/wmcoredb/sql/oracle/wmbs/initial_wmbs_data.sql\n```\n\n#### For MariaDB:\n```sql\nsource src/wmcoredb/sql/mariadb/testdb/create_testdb.sql\nsource src/wmcoredb/sql/mariadb/tier0/create_tier0_tables.sql\nsource src/wmcoredb/sql/mariadb/tier0/create_tier0_indexes.sql\nsource src/wmcoredb/sql/mariadb/tier0/create_tier0_functions.sql\nsource src/wmcoredb/sql/mariadb/tier0/initial_tier0_data.sql\nsource src/wmcoredb/sql/mariadb/wmbs/create_wmbs_tables.sql\nsource src/wmcoredb/sql/mariadb/wmbs/create_wmbs_indexes.sql\nsource src/wmcoredb/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/wmcoredb/sql/`.\n\n## Package Testing\n\nTo test the package functionality locally:\n\n```bash\n# Build the package\npython -m build\n\n# Install locally\npip install dist/wmcoredb-*.whl\n\n# Test basic functionality\npython -c\nimport wmcoredb\nprint('Backends:', wmcoredb.list_backends())\nprint('MariaDB modules:', wmcoredb.list_modules('mariadb'))\nprint(Oracle modules:', wmcoredb.list_modules(oracle'))\nprint(WMBS files:, wmcoredb.list_sql_files('wmbs, 'mariadb'))\n\"\n\n# Test SQL content access\npython -c\nimport wmcoredb\ncontent = wmcoredb.get_sql_content('testdb', create_testdb.sql', mariadb')\nprint(TestDB SQL length:', len(content))\n\"\n```\n\n## Logs\n\nSome relevant logs from the WMAgent 2.3.9 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 v0.10.54INFO: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#L465pt, 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 inmysql')\n _exec_mysql \"create database if not exists $wmaDBName\"\n local database_options=--mysql_url=mysql://$MDB_USER:$MDB_PASS@$MDB_HOST/$wmaDBNameoracle')\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 standardWMAgentConfig.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\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\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- Auto-incrementing primary keys\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.9.3",
"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": "2d303cd10686fa7c3195ce0024a97066d1bf62e36385b53e52f3b95a863e2df0",
"md5": "581fbf0a59085715a92fba4b2d271e90",
"sha256": "21a7a7e15fbf33e875835b9123908fe8b1c3ed9a0e73b5e7b9daf916e8563a7f"
},
"downloads": -1,
"filename": "wmcoredb-0.9.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "581fbf0a59085715a92fba4b2d271e90",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 57426,
"upload_time": "2025-07-18T03:30:00",
"upload_time_iso_8601": "2025-07-18T03:30:00.299667Z",
"url": "https://files.pythonhosted.org/packages/2d/30/3cd10686fa7c3195ce0024a97066d1bf62e36385b53e52f3b95a863e2df0/wmcoredb-0.9.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "834cfae6ad52540b4ceaf1ef3bf939b9854251b734231754b7d825df903a9f57",
"md5": "ce12b54c1ff3fc083c9edf94d7eb597b",
"sha256": "85d6bc8646840062be2ed51743782ccc133b87106fbf7c2ece9172fc042d897f"
},
"downloads": -1,
"filename": "wmcoredb-0.9.3.tar.gz",
"has_sig": false,
"md5_digest": "ce12b54c1ff3fc083c9edf94d7eb597b",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 60044,
"upload_time": "2025-07-18T03:30:02",
"upload_time_iso_8601": "2025-07-18T03:30:02.119435Z",
"url": "https://files.pythonhosted.org/packages/83/4c/fae6ad52540b4ceaf1ef3bf939b9854251b734231754b7d825df903a9f57/wmcoredb-0.9.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-07-18 03:30:02",
"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
