243 lines
5.4 KiB
Markdown
Executable File
243 lines
5.4 KiB
Markdown
Executable File
|
||
# 02 – Modèle de données (SQLite)
|
||
|
||
Objectif : définir le schéma relationnel pour SQLite, exploité par SQLAlchemy (ou équivalent).
|
||
|
||
## Vue d’ensemble
|
||
|
||
Tables principales :
|
||
- `devices`
|
||
- `hardware_snapshots`
|
||
- `benchmarks`
|
||
- `manufacturer_links`
|
||
- `documents`
|
||
|
||
Optionnel (plus tard) :
|
||
- `users` (si auth interne)
|
||
- `settings` (config globale de l’app)
|
||
|
||
---
|
||
|
||
## Table devices
|
||
|
||
Représente une machine (physique ou virtuelle).
|
||
|
||
Champs :
|
||
|
||
- `id` (INTEGER, PK, autoincrement)
|
||
- `hostname` (TEXT, non nul)
|
||
- `fqdn` (TEXT, nullable)
|
||
- `description` (TEXT, nullable)
|
||
- `asset_tag` (TEXT, nullable) – identifiant interne/inventaire
|
||
- `location` (TEXT, nullable)
|
||
- `owner` (TEXT, nullable)
|
||
- `tags` (TEXT, nullable) – liste séparée par virgules ou JSON
|
||
- `created_at` (DATETIME, non nul)
|
||
- `updated_at` (DATETIME, non nul)
|
||
|
||
Contraintes :
|
||
- `hostname` peut être utilisé comme identifiant logique si `device_identifier` n’est pas fourni par le client.
|
||
- Index recommandé : `idx_devices_hostname`.
|
||
|
||
---
|
||
|
||
## Table hardware_snapshots
|
||
|
||
Snapshot détaillé de la configuration matérielle et OS au moment d’un bench.
|
||
|
||
Champs :
|
||
|
||
- `id` (INTEGER, PK)
|
||
- `device_id` (INTEGER, FK -> devices.id)
|
||
- `captured_at` (DATETIME, non nul)
|
||
|
||
### CPU
|
||
|
||
- `cpu_vendor` (TEXT)
|
||
- `cpu_model` (TEXT)
|
||
- `cpu_microarchitecture` (TEXT, nullable)
|
||
- `cpu_cores` (INTEGER)
|
||
- `cpu_threads` (INTEGER)
|
||
- `cpu_base_freq_ghz` (REAL, nullable)
|
||
- `cpu_max_freq_ghz` (REAL, nullable)
|
||
- `cpu_cache_l1_kb` (INTEGER, nullable)
|
||
- `cpu_cache_l2_kb` (INTEGER, nullable)
|
||
- `cpu_cache_l3_kb` (INTEGER, nullable)
|
||
- `cpu_flags` (TEXT, nullable) – chaîne ou JSON (AVX, AES, etc.)
|
||
- `cpu_tdp_w` (REAL, nullable)
|
||
|
||
### RAM
|
||
|
||
- `ram_total_mb` (INTEGER)
|
||
- `ram_slots_total` (INTEGER, nullable)
|
||
- `ram_slots_used` (INTEGER, nullable)
|
||
- `ram_ecc` (BOOLEAN, nullable)
|
||
- `ram_layout_json` (TEXT, nullable)
|
||
Exemple JSON :
|
||
```json
|
||
[
|
||
{ "slot": "DIMM_A1", "size_mb": 16384, "type": "DDR4", "speed_mhz": 2133, "vendor": "Corsair", "part_number": "XXXX" }
|
||
]
|
||
```
|
||
|
||
### GPU
|
||
|
||
- `gpu_summary` (TEXT, nullable) – ex: "Intel UHD 630"
|
||
- `gpu_vendor` (TEXT, nullable)
|
||
- `gpu_model` (TEXT, nullable)
|
||
- `gpu_driver_version` (TEXT, nullable)
|
||
- `gpu_memory_dedicated_mb` (INTEGER, nullable)
|
||
- `gpu_memory_shared_mb` (INTEGER, nullable)
|
||
- `gpu_api_support` (TEXT, nullable) – ex: "OpenGL 4.6, Vulkan"
|
||
|
||
### Stockage
|
||
|
||
- `storage_summary` (TEXT, nullable) – résumé human readable
|
||
- `storage_devices_json` (TEXT, nullable)
|
||
Exemple JSON :
|
||
```json
|
||
[
|
||
{
|
||
"name": "/dev/nvme0n1",
|
||
"type": "NVMe",
|
||
"interface": "PCIe 3.0 x4",
|
||
"capacity_gb": 1000,
|
||
"vendor": "Samsung",
|
||
"model": "970 EVO Plus",
|
||
"smart_health": "PASSED",
|
||
"temperature_c": 42
|
||
}
|
||
]
|
||
```
|
||
- `partitions_json` (TEXT, nullable)
|
||
|
||
### Réseau
|
||
|
||
- `network_interfaces_json` (TEXT, nullable)
|
||
Exemple :
|
||
```json
|
||
[
|
||
{
|
||
"name": "eth0",
|
||
"type": "ethernet",
|
||
"mac": "aa:bb:cc:dd:ee:ff",
|
||
"ip": "10.0.0.10",
|
||
"speed_mbps": 1000,
|
||
"driver": "e1000e"
|
||
}
|
||
]
|
||
```
|
||
|
||
### OS / Carte mère
|
||
|
||
- `os_name` (TEXT)
|
||
- `os_version` (TEXT)
|
||
- `kernel_version` (TEXT)
|
||
- `architecture` (TEXT) – "x86_64", "arm64"
|
||
- `virtualization_type` (TEXT, nullable) – "kvm", "vmware", "none", etc.
|
||
- `motherboard_vendor` (TEXT, nullable)
|
||
- `motherboard_model` (TEXT, nullable)
|
||
- `bios_version` (TEXT, nullable)
|
||
- `bios_date` (TEXT, nullable)
|
||
|
||
### Divers
|
||
|
||
- `sensors_json` (TEXT, nullable) – valeurs de températures, etc.
|
||
- `raw_info_json` (TEXT, nullable) – dump brut de commandes (inxi, lshw, etc.)
|
||
|
||
Index recommandés :
|
||
- `idx_hw_snapshots_device_id`
|
||
- `idx_hw_snapshots_captured_at`
|
||
|
||
---
|
||
|
||
## Table benchmarks
|
||
|
||
Représente un run de bench.
|
||
|
||
Champs :
|
||
|
||
- `id` (INTEGER, PK)
|
||
- `device_id` (INTEGER, FK -> devices.id)
|
||
- `hardware_snapshot_id` (INTEGER, FK -> hardware_snapshots.id)
|
||
- `run_at` (DATETIME, non nul)
|
||
- `bench_script_version` (TEXT, non nul)
|
||
|
||
### Scores
|
||
|
||
- `global_score` (REAL, non nul)
|
||
- `cpu_score` (REAL, nullable)
|
||
- `memory_score` (REAL, nullable)
|
||
- `disk_score` (REAL, nullable)
|
||
- `network_score` (REAL, nullable)
|
||
- `gpu_score` (REAL, nullable)
|
||
|
||
### Détails
|
||
|
||
- `details_json` (TEXT, non nul)
|
||
Contient toutes les valeurs brutes :
|
||
- CPU events/sec, temps d’exécution, etc.
|
||
- Throughput mémoire.
|
||
- Read/write MB/s, IOPS.
|
||
- Vitesse iperf3, ping.
|
||
- Score GPU, etc.
|
||
|
||
- `notes` (TEXT, nullable)
|
||
|
||
Index :
|
||
- `idx_benchmarks_device_id`
|
||
- `idx_benchmarks_run_at`
|
||
|
||
---
|
||
|
||
## Table manufacturer_links
|
||
|
||
Liens vers les ressources en ligne du constructeur.
|
||
|
||
Champs :
|
||
|
||
- `id` (INTEGER, PK)
|
||
- `device_id` (INTEGER, FK -> devices.id)
|
||
- `label` (TEXT, non nul) – ex: "Support HP", "Page produit Lenovo"
|
||
- `url` (TEXT, non nul)
|
||
|
||
Index :
|
||
- `idx_links_device_id`
|
||
|
||
---
|
||
|
||
## Table documents
|
||
|
||
Fichiers (PDF, images, etc.) associés à un device.
|
||
|
||
Champs :
|
||
|
||
- `id` (INTEGER, PK)
|
||
- `device_id` (INTEGER, FK -> devices.id)
|
||
- `doc_type` (TEXT, non nul) – `manual`, `warranty`, `invoice`, `photo`, `other`
|
||
- `filename` (TEXT, non nul) – nom original
|
||
- `stored_path` (TEXT, non nul) – chemin relatif sur le serveur
|
||
- `mime_type` (TEXT, non nul)
|
||
- `size_bytes` (INTEGER, non nul)
|
||
- `uploaded_at` (DATETIME, non nul)
|
||
|
||
Index :
|
||
- `idx_documents_device_id`
|
||
|
||
---
|
||
|
||
## Éventuelle table settings (optionnelle)
|
||
|
||
Pour stocker des paramètres globaux de l’application.
|
||
|
||
Champs :
|
||
|
||
- `id` (INTEGER, PK)
|
||
- `key` (TEXT, unique)
|
||
- `value` (TEXT)
|
||
|
||
Exemples :
|
||
- `default_bench_server_url`
|
||
- `default_iperf_server`
|
||
- `score_weights_json` (pondération CPU/Mem/Disque/Réseau/GPU)
|