# datacharmer/test_db architecture

> Architecture diagram of the GitHub repository datacharmer/test_db, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-04-28). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/datacharmer/test_db
- Repository: https://github.com/datacharmer/test_db

## Overview

##### Project Type and Purpose
- This is a database-focused “test_db” repository: a sample MySQL database plus an integrated test suite.
- Its primary goal is to provide realistic sample data (employees, departments, salaries, etc.) and verification scripts to validate a MySQL installation or to run data-cleaning exercises.

##### File-Structure Analysis
Top-level contents:
- SQL load scripts: employees.sql, employees_partitioned.sql (and versioned variants)
- Data dumps: load_*.dump files for each table
- Test scripts: test_employees_md5.sql, test_employees_sha.sql, test_versions.sh
- Helper shell script: sql_test.sh
- Sample “sakila” schema and data in sakila/
- Utility scripts: show_elapsed.sql, Changelog, README.md

##### Architectural Insights from README
- Depends on a MySQL 5.0+ server
- Installation is a simple “mysql < employees.sql” (or partitioned script)
- Testing involves running SQL files that compute row counts + CRCs to verify data integrity

##### Components
a. Data Sources (Input)
- Raw relational exports (employees.sql, sakila-mv-schema.sql + sakila-mv-data.sql)
- Partitioned variants (employees_partitioned*.sql)
b. Loader Component
- mysql CLI client invoked by sql_test.sh or manual “mysql < …” commands
- Loads dumps into the MySQL server, creating schemas and populating tables
c. MySQL Server (Core)
- The running database engine hosting schemas: employees, sakila
- Tables: employees, departments, dept_emp, dept_manager, titles, salaries, etc.
d. Test Harness
- Shell scripts (sql_test.sh, test_versions.sh) orchestrate test execution
- SQL test files compute MD5/SHA checksums, row counts, CRCs (test_employees_md5.sql, test_employees_sha.sql)
e. Outputs / Reports
- Console output of test results (OK / CRC match)
- Optional logs or elapsed-time reports (show_elapsed.sql)

##### Architectural Patterns & Principles
- Single responsibility: separate load scripts vs. test scripts
- Extensibility point: drop-in new test SQL files into the top level or add new dump files

##### Technology Stack
- MySQL Server 5.0+ (database engine)
- mysql CLI (data loading & query execution)
- Bash/shell scripts (or any POSIX shell)
- SQL dialect: MySQL procedural & DDL statements

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Data Sources (Static Artifacts)
    subgraph "Data Sources (Input)"
        direction TB
        empSQL["employees.sql"]:::artifact
        empPartSQL["employees_partitioned.sql"]:::artifact
        empPart51SQL["employees_partitioned_5.1.sql"]:::artifact
        sakilaSchema["sakila-mv-schema.sql"]:::artifact
        sakilaData["sakila-mv-data.sql"]:::artifact
        subgraph "Table Data Dumps"
            direction TB
            depDump["load_departments.dump"]:::artifact
            deptEmpDump["load_dept_emp.dump"]:::artifact
            deptMgrDump["load_dept_manager.dump"]:::artifact
            empDump["load_employees.dump"]:::artifact
            sal1Dump["load_salaries1.dump"]:::artifact
            sal2Dump["load_salaries2.dump"]:::artifact
            sal3Dump["load_salaries3.dump"]:::artifact
            titlesDump["load_titles.dump"]:::artifact
        end
    end

    %% Loader Component
    subgraph "Installation Phase"
        direction TB
        loader["sql_test.sh (Loader)"]:::runtime
    end

    %% Core Database
    subgraph "MySQL Server (Core)" 
        direction TB
        db["MySQL Server 5.0+ Engine"]:::service
    end

    %% Testing Phase
    subgraph "Test Harness"
        direction TB
        versioner["test_versions.sh"]:::runtime
        md5Test["test_employees_md5.sql"]:::artifact
        shaTest["test_employees_sha.sql"]:::artifact
    end

    %% Outputs / Reports
    subgraph "Outputs / Reports"
        direction TB
        console["Console Output"]:::output
        elapsed["show_elapsed.sql"]:::artifact
    end

    %% Connections
    empSQL -->|"load"| loader
    empPartSQL -.->|"optional load"| loader
    empPart51SQL -.->|"optional load"| loader
    sakilaSchema -->|"load"| loader
    sakilaData -->|"load"| loader
    depDump -->|"load"| loader
    deptEmpDump -->|"load"| loader
    deptMgrDump -->|"load"| loader
    empDump -->|"load"| loader
    sal1Dump -->|"load"| loader
    sal2Dump -->|"load"| loader
    sal3Dump -->|"load"| loader
    titlesDump -->|"load"| loader

    loader -->|"mysql CLI"| db

    db -->|"execute tests"| versioner
    versioner -->|"run"| md5Test
    versioner -->|"run"| shaTest
    versioner -->|"report via"| console
    versioner -->|"measure time"| elapsed

    %% Click Events
    click empSQL "https://github.com/datacharmer/test_db/blob/master/employees.sql"
    click empPartSQL "https://github.com/datacharmer/test_db/blob/master/employees_partitioned.sql"
    click empPart51SQL "https://github.com/datacharmer/test_db/blob/master/employees_partitioned_5.1.sql"
    click sakilaSchema "https://github.com/datacharmer/test_db/blob/master/sakila/sakila-mv-schema.sql"
    click sakilaData "https://github.com/datacharmer/test_db/blob/master/sakila/sakila-mv-data.sql"
    click depDump "https://github.com/datacharmer/test_db/blob/master/load_departments.dump"
    click deptEmpDump "https://github.com/datacharmer/test_db/blob/master/load_dept_emp.dump"
    click deptMgrDump "https://github.com/datacharmer/test_db/blob/master/load_dept_manager.dump"
    click empDump "https://github.com/datacharmer/test_db/blob/master/load_employees.dump"
    click sal1Dump "https://github.com/datacharmer/test_db/blob/master/load_salaries1.dump"
    click sal2Dump "https://github.com/datacharmer/test_db/blob/master/load_salaries2.dump"
    click sal3Dump "https://github.com/datacharmer/test_db/blob/master/load_salaries3.dump"
    click titlesDump "https://github.com/datacharmer/test_db/blob/master/load_titles.dump"
    click loader "https://github.com/datacharmer/test_db/blob/master/sql_test.sh"
    click versioner "https://github.com/datacharmer/test_db/blob/master/test_versions.sh"
    click md5Test "https://github.com/datacharmer/test_db/blob/master/test_employees_md5.sql"
    click shaTest "https://github.com/datacharmer/test_db/blob/master/test_employees_sha.sql"
    click elapsed "https://github.com/datacharmer/test_db/blob/master/show_elapsed.sql"

    %% Styles
    classDef artifact fill:#ADD8E6,stroke:#000,stroke-width:1px
    classDef runtime fill:#FFA500,stroke:#000,stroke-width:1px
    classDef service fill:#90EE90,stroke:#000,stroke-width:1px
    classDef output fill:#D3D3D3,stroke:#000,stroke-width:1px
    class empPartSQL,empPart51SQL dashedArrow
    classDef dashedArrow stroke-dasharray: 5 5,stroke:#800080
```
