143 lines
2.8 KiB
Markdown
143 lines
2.8 KiB
Markdown
# Local DB Migration: Circuit-First Schema
|
|
|
|
This project uses SQLite at `data/leistungsbilanz.db` and Drizzle migrations in `src/db/migrations`.
|
|
|
|
## Safe command order
|
|
|
|
1. Backup local DB (required before schema/data migration)
|
|
|
|
```bash
|
|
npm run db:backup
|
|
```
|
|
|
|
2. Apply pending schema migrations (includes `0008_circuit_first_model`)
|
|
|
|
```bash
|
|
npm run db:migrate
|
|
```
|
|
|
|
3. Verify circuit-first tables exist
|
|
|
|
```bash
|
|
npm run db:verify:circuit-schema
|
|
```
|
|
|
|
4. Backfill default sections for existing `circuit_lists`
|
|
|
|
```bash
|
|
npm run db:backfill:sections
|
|
```
|
|
|
|
5. Run legacy consumer -> circuit/device-row migration explicitly
|
|
|
|
```bash
|
|
npm run db:migrate:legacy-consumers
|
|
```
|
|
|
|
## Verification SQL
|
|
|
|
Run these against `data/leistungsbilanz.db`:
|
|
|
|
```sql
|
|
SELECT name
|
|
FROM sqlite_master
|
|
WHERE type = 'table'
|
|
AND name IN (
|
|
'circuit_sections',
|
|
'circuits',
|
|
'circuit_device_rows',
|
|
'legacy_consumer_circuit_migrations',
|
|
'legacy_consumer_migration_reports'
|
|
)
|
|
ORDER BY name;
|
|
```
|
|
|
|
```sql
|
|
SELECT circuit_list_id, key, prefix, sort_order
|
|
FROM circuit_sections
|
|
ORDER BY circuit_list_id, sort_order;
|
|
```
|
|
|
|
```sql
|
|
SELECT circuit_list_id, COUNT(*) AS circuits
|
|
FROM circuits
|
|
GROUP BY circuit_list_id;
|
|
```
|
|
|
|
```sql
|
|
SELECT c.circuit_list_id, COUNT(r.id) AS device_rows
|
|
FROM circuits c
|
|
LEFT JOIN circuit_device_rows r ON r.circuit_id = c.id
|
|
GROUP BY c.circuit_list_id;
|
|
```
|
|
|
|
## API verification
|
|
|
|
After the steps above:
|
|
|
|
`GET /api/projects/:projectId/circuit-lists/:circuitListId/tree`
|
|
|
|
Expected response shape:
|
|
|
|
```json
|
|
{
|
|
"circuitListId": "string",
|
|
"sections": [
|
|
{
|
|
"id": "string",
|
|
"key": "lighting|single_phase|three_phase|unassigned|...",
|
|
"displayName": "string",
|
|
"prefix": "-1F|-2F|-3F|-UF|...",
|
|
"sortOrder": 10,
|
|
"circuits": [
|
|
{
|
|
"id": "string",
|
|
"equipmentIdentifier": "-2F1",
|
|
"displayName": "string",
|
|
"sortOrder": 10,
|
|
"isReserve": false,
|
|
"circuitTotalPower": 1.23,
|
|
"deviceRows": [
|
|
{
|
|
"id": "string",
|
|
"displayName": "string",
|
|
"quantity": 1,
|
|
"powerPerUnit": 0.3,
|
|
"simultaneityFactor": 1,
|
|
"rowTotalPower": 0.3
|
|
}
|
|
]
|
|
}
|
|
]
|
|
}
|
|
]
|
|
}
|
|
```
|
|
|
|
If migrations were not applied, endpoint may return an empty fallback with a warning.
|
|
|
|
## Dev-only visual test helper (multi-device circuit)
|
|
|
|
Add one extra manual device row to an existing circuit:
|
|
|
|
```bash
|
|
npm run dev:add-manual-circuit-row -- <circuitId>
|
|
```
|
|
|
|
Default inserted values:
|
|
- `name`: `Test sub device`
|
|
- `displayName`: `Beleuchtung WC`
|
|
- `phaseType`: `single_phase`
|
|
- `quantity`: `1`
|
|
- `powerPerUnit`: `0.05`
|
|
- `simultaneityFactor`: `1`
|
|
- `cosPhi`: `1`
|
|
|
|
The script prints the created row id.
|
|
|
|
Delete the test row again:
|
|
|
|
```bash
|
|
npm run dev:delete-circuit-row -- <rowId>
|
|
```
|