wmcoredb


Namewmcoredb JSON
Version 0.8.7 PyPI version JSON
download
home_pageNone
SummaryWMCore Database Schema - Database schema definitions for WMCore components
upload_time2025-07-09 03:56:45
maintainerNone
docs_urlNone
authorNone
requires_python>=3.8
licenseNone
keywords database schema wmcore oracle mariadb cms workload-management
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls.
            [![SQL Linting](https://github.com/dmwm/wmcoredb/actions/workflows/sql-lint.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/sql-lint.yml)
[![MariaDB Schema Validation](https://github.com/dmwm/wmcoredb/actions/workflows/mariadb-schema-test.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/mariadb-schema-test.yml)
[![Oracle Schema Validation](https://github.com/dmwm/wmcoredb/actions/workflows/oracle-schema-test.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/oracle-schema-test.yml)
[![Test Package Build](https://github.com/dmwm/wmcoredb/actions/workflows/test-build.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/test-build.yml)
[![Release to PyPI](https://github.com/dmwm/wmcoredb/actions/workflows/release.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/release.yml)
[![PyPI](https://img.shields.io/pypi/v/wmcoredb.svg)](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": "[![SQL Linting](https://github.com/dmwm/wmcoredb/actions/workflows/sql-lint.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/sql-lint.yml)\n[![MariaDB Schema Validation](https://github.com/dmwm/wmcoredb/actions/workflows/mariadb-schema-test.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/mariadb-schema-test.yml)\n[![Oracle Schema Validation](https://github.com/dmwm/wmcoredb/actions/workflows/oracle-schema-test.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/oracle-schema-test.yml)\n[![Test Package Build](https://github.com/dmwm/wmcoredb/actions/workflows/test-build.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/test-build.yml)\n[![Release to PyPI](https://github.com/dmwm/wmcoredb/actions/workflows/release.yml/badge.svg)](https://github.com/dmwm/wmcoredb/actions/workflows/release.yml)\n[![PyPI](https://img.shields.io/pypi/v/wmcoredb.svg)](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"
}
        
Elapsed time: 0.48977s