248 lines
9.6 KiB
Markdown
248 lines
9.6 KiB
Markdown
# Schema refresh & Zustieg-pricing verification queries
|
||
|
||
> **Superseded by [`docs/buspro-database-reference.md`](../buspro-database-reference.md) §12**,
|
||
> which carries the refresh queries and the verification log. Retained only for the
|
||
> 2026-08-04 run detail; safe to delete once that is no longer of interest.
|
||
|
||
Read-only queries to (a) check whether the June schema dump still matches
|
||
production after a BusPro update, (b) regenerate it if not, and (c) resolve the
|
||
open `Abfahrtsortzuschlag (Rückfahrt)` question from the database side.
|
||
|
||
Current dump: `BusPronet_20260601-1517.csv` (columns),
|
||
`BusPronet_20260601-1518.csv` (foreign keys), exported 2026-06-01.
|
||
|
||
**All queries are `SELECT`-only.** Run in order — Query 1 decides whether 2–4 are
|
||
needed at all.
|
||
|
||
---
|
||
|
||
## 1. Drift check — run this first
|
||
|
||
Cheapest, highest signal. If it returns nothing, the June dump is still accurate
|
||
and the re-export can be skipped.
|
||
|
||
```sql
|
||
SELECT s.name AS schema_name, o.name AS object_name, o.type_desc,
|
||
o.create_date, o.modify_date
|
||
FROM sys.objects o
|
||
JOIN sys.schemas s ON s.schema_id = o.schema_id
|
||
WHERE o.is_ms_shipped = 0
|
||
AND (o.create_date > '2026-06-01' OR o.modify_date > '2026-06-01')
|
||
ORDER BY o.modify_date DESC;
|
||
```
|
||
|
||
Note: `modify_date` moves on metadata changes, so a long list is expected after a
|
||
version upgrade. What matters is whether anything under `Zustieg*`, `Leistung*`,
|
||
`Buchung*` appears.
|
||
|
||
**Result 2026-08-04.** Every object reports `create_date` **2026-06-03** — the
|
||
database was restored/rebuilt that day, so `create_date` is a restore artifact and
|
||
the `> '2026-06-01'` filter matches everything. Use `modify_date` after the restore
|
||
instead. Only two genuine clusters exist:
|
||
|
||
- **2026-06-11** — six new tables, all absent from `…-1517.csv`: `Reichweite`,
|
||
`ReichweiteZeitraum`, `ReichweiteZeitraumVonBis`, `ReichweiteRestFarbe`,
|
||
`LadungTankungBetriebshof`, `ConnectViafintechSlip`.
|
||
- **2026-07-02** — modified: `Tachostand`, `GutscheinAktion`,
|
||
`AdressePersonalZusatz`, `BlockPlanung`, `Block`, `LeistungZusatz`,
|
||
`ConnectViafintechSlip`, `TourPlanung`.
|
||
|
||
**Nothing under `Zustieg*`, `ZustiegPreis`, `ArtZustiegPreis`,
|
||
`LeistungBefoerderung` or `BuchungTNZuschlag` changed after the restore.** The June
|
||
dump is accurate for the booking and boarding areas; the only staleness is the six
|
||
fleet/payment tables above. For future runs, filter on `o.modify_date > '2026-06-03'`
|
||
to skip the restore baseline.
|
||
|
||
---
|
||
|
||
## 2. Columns — regenerates `…-1517.csv`
|
||
|
||
Column order and names match the existing header exactly, so the output is a
|
||
drop-in replacement. Export semicolon-separated with quoted values, same settings
|
||
as the June file.
|
||
|
||
```sql
|
||
SELECT s.name AS schema_name,
|
||
t.name AS table_name,
|
||
c.name AS column_name,
|
||
ty.name AS data_type,
|
||
c.max_length,
|
||
c.precision,
|
||
c.scale,
|
||
c.is_nullable,
|
||
c.is_identity,
|
||
dc.definition AS default_definition
|
||
FROM sys.tables t
|
||
JOIN sys.schemas s ON s.schema_id = t.schema_id
|
||
JOIN sys.columns c ON c.object_id = t.object_id
|
||
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
|
||
LEFT JOIN sys.default_constraints dc ON dc.object_id = c.default_object_id
|
||
ORDER BY t.name, c.column_id;
|
||
```
|
||
|
||
## 3. Foreign keys — regenerates `…-1518.csv`
|
||
|
||
```sql
|
||
SELECT fk.name AS foreign_key_name,
|
||
cs.name AS child_schema, ct.name AS child_table, cc.name AS child_column,
|
||
ps.name AS parent_schema, pt.name AS parent_table, pc.name AS parent_column
|
||
FROM sys.foreign_keys fk
|
||
JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
|
||
JOIN sys.tables ct ON ct.object_id = fkc.parent_object_id
|
||
JOIN sys.schemas cs ON cs.schema_id = ct.schema_id
|
||
JOIN sys.columns cc ON cc.object_id = fkc.parent_object_id
|
||
AND cc.column_id = fkc.parent_column_id
|
||
JOIN sys.tables pt ON pt.object_id = fkc.referenced_object_id
|
||
JOIN sys.schemas ps ON ps.schema_id = pt.schema_id
|
||
JOIN sys.columns pc ON pc.object_id = fkc.referenced_object_id
|
||
AND pc.column_id = fkc.referenced_column_id
|
||
ORDER BY ct.name, fk.name, fkc.constraint_column_id;
|
||
```
|
||
|
||
## 4. Primary keys & indexes — the documented gap
|
||
|
||
`bpn-connect-query-reference.md` records that PK/index data was expected but
|
||
missing, and that identity flags are not a reliable PK substitute here. This closes
|
||
it. Suggested filename: `BusPronet_<date>-pk-index.csv`.
|
||
|
||
```sql
|
||
SELECT s.name AS schema_name,
|
||
t.name AS table_name,
|
||
i.name AS index_name,
|
||
i.type_desc,
|
||
i.is_primary_key,
|
||
i.is_unique,
|
||
i.is_unique_constraint,
|
||
ic.key_ordinal,
|
||
c.name AS column_name,
|
||
ic.is_included_column,
|
||
ic.is_descending_key
|
||
FROM sys.indexes i
|
||
JOIN sys.tables t ON t.object_id = i.object_id
|
||
JOIN sys.schemas s ON s.schema_id = t.schema_id
|
||
JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
|
||
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
|
||
WHERE i.type > 0 -- exclude heaps
|
||
ORDER BY t.name, i.is_primary_key DESC, i.name, ic.key_ordinal;
|
||
```
|
||
|
||
---
|
||
|
||
## 5. Boarding-price types — `ArtZustiegPreis`
|
||
|
||
**Answered 2026-08-04:** this is a **tariff-scheme** lookup, *not* a direction
|
||
lookup. Active schemes are 36 `Woche26/27`, 37 `WE26/27`, 38 `Event26/27`,
|
||
39 `SER26/27` (plus legacy A–T at `Status = 'L'`). There is no Hinfahrt/Rückfahrt
|
||
concept in this table.
|
||
|
||
```sql
|
||
SELECT IDArtZustiegPreis, Code, Kurztext, Langtext, Status, Hierarchie
|
||
FROM dbo.ArtZustiegPreis
|
||
ORDER BY Hierarchie, IDArtZustiegPreis;
|
||
```
|
||
|
||
## 6. Configured prices for boarding point 18
|
||
|
||
```sql
|
||
SELECT zp.IDZustiegPreis,
|
||
zp.IDZustieg_FS,
|
||
z.Zustiegcode, z.Ort, z.Plz,
|
||
zp.IDArtZustiegPreis_FS,
|
||
ap.Code AS PreisArtCode, ap.Kurztext AS PreisArt,
|
||
zp.Preis, zp.PreisProvisionPflichtig,
|
||
zp.Sockelbetrag, zp.SockelbetragProvisionPflichtig
|
||
FROM dbo.ZustiegPreis zp
|
||
JOIN dbo.Zustieg z ON z.IDZustieg = zp.IDZustieg_FS
|
||
LEFT JOIN dbo.ArtZustiegPreis ap ON ap.IDArtZustiegPreis = zp.IDArtZustiegPreis_FS
|
||
WHERE zp.IDZustieg_FS = 18
|
||
ORDER BY zp.IDArtZustiegPreis_FS;
|
||
```
|
||
|
||
**Answered 2026-08-04:** three rows for point 18 (`ESS-Route`, Essen), one per
|
||
active tariff scheme — 36 → **17,90**, 37 → 12,90, 38 → 19,90. Prices are keyed by
|
||
(point, tariff scheme), **not** by direction. The export's
|
||
`<zustiege_rueck preis="17,90">` is therefore the same row surfaced through the
|
||
return service, not a separately configured return price.
|
||
|
||
## 7. How the two bus services resolve their boarding price — the mechanism
|
||
|
||
`LeistungBefoerderung` carries **`IDArtZustiegPreis_FS`** and
|
||
**`Richtung_Hin_Rueck`**, so each transport service selects which price type
|
||
applies. This query shows what outbound service 194779 and return service 194776
|
||
are configured to use.
|
||
|
||
```sql
|
||
SELECT l.IDLeistung, l.Bezeichnung, l.Art, l.Unterart,
|
||
lb.Richtung_Hin_Rueck,
|
||
lb.IDArtZustiegPreis_FS,
|
||
ap.Code AS PreisArtCode, ap.Kurztext AS PreisArt,
|
||
lb.IDArtZustiegZeitplan_FS,
|
||
lb.ZustiegeGesperrt,
|
||
lb.IDZustieg_FS_Basisstelle,
|
||
lb.ZustiegIDs
|
||
FROM dbo.LeistungBefoerderung lb
|
||
JOIN dbo.Leistung l ON l.IDLeistung = lb.IDLeistung_FS
|
||
LEFT JOIN dbo.ArtZustiegPreis ap ON ap.IDArtZustiegPreis = lb.IDArtZustiegPreis_FS
|
||
WHERE l.IDLeistung IN (194779, 194776);
|
||
```
|
||
|
||
**Answered 2026-08-04 — configuration is symmetric, config cause excluded:**
|
||
|
||
| Leistung | Richtung | `IDArtZustiegPreis_FS` | `ZustiegeGesperrt` | `ZustiegIDs` |
|
||
|---|---|---|---|---|
|
||
| 194779 Bus-Hinfahrt | H | 36 (Woche26/27) | 0 | 3,4,18,86,320,470,473 |
|
||
| 194776 Bus-Rückfahrt | R | 36 (Woche26/27) | 0 | 3,4,18,86,320,470,473 |
|
||
|
||
Both legs resolve to the same 17,90 and both have point 18 enabled. The differing
|
||
BusPro behaviour is therefore not explained by product configuration.
|
||
|
||
## 8. Surcharge types — `ArtZuschlag`
|
||
|
||
**Answered 2026-08-04:** a discounts/fees lookup (Zwei-Wochen-Rabatt,
|
||
Kinderermäßigung, Bearbeitungsgebühr, Preiskorrektur, …). No direction concept and
|
||
**no `Abfahrtsortzuschlag` entry** — so the `SON`/`TRA` positions in the booking
|
||
response are computed transport surcharges, not `BuchungTNZuschlag` rows. Query 9
|
||
below is aimed at the wrong table and can be skipped.
|
||
|
||
```sql
|
||
SELECT IDArtZuschlag, Code, Kurztext, Langtext, Status, Hierarchie
|
||
FROM dbo.ArtZuschlag
|
||
ORDER BY Hierarchie, IDArtZuschlag;
|
||
```
|
||
|
||
## 9. Booked surcharge rows for an affected booking
|
||
|
||
The decisive one *if* a completed booking exhibiting the fault is available: does
|
||
the working booking persist two `BuchungTNZuschlag` rows and the failing one only
|
||
one?
|
||
|
||
```sql
|
||
SELECT b.Vorgang,
|
||
tn.IDBuchungTN,
|
||
z.IDBuchungTNZuschlag,
|
||
z.BZText, z.Text,
|
||
z.Art,
|
||
z.IDZustieg_FS,
|
||
az.Code AS ZuschlagArtCode, az.Kurztext AS ZuschlagArt,
|
||
z.BZPreisMitProvision, z.BZPreisOhneProvision
|
||
FROM dbo.BuchungTNZuschlag z
|
||
JOIN dbo.BuchungTN tn ON tn.IDBuchungTN = z.IDBuchungTN_FS
|
||
JOIN dbo.Buchung b ON b.IDBuchung = tn.IDBuchung_FS
|
||
LEFT JOIN dbo.ArtZuschlag az ON az.IDArtZuschlag = z.IDArtZuschlag_FS
|
||
WHERE b.Vorgang IN ('<Vorgang-1>', '<Vorgang-2>')
|
||
ORDER BY b.Vorgang, tn.IDBuchungTN, z.IDBuchungTNZuschlag;
|
||
```
|
||
|
||
**Caveat:** both captured cases were `<buchungsart>Anfrage</buchungsart>` inquiries,
|
||
and our flow only commits at step 4 — so these rows may not exist for them. Use the
|
||
`Vorgang` of an actually completed booking that showed the short total. If none
|
||
exists, skip this query; queries 5–7 carry the argument on their own.
|
||
|
||
---
|
||
|
||
## What to send back
|
||
|
||
Query 1's result, plus 5, 6, 7 and 8 as plain result sets — those four are small and
|
||
answer the pricing question directly. Queries 2–4 only if query 1 shows drift, as
|
||
CSV files replacing the ones in this directory.
|