# 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_-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 `` 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 ('', '') ORDER BY b.Vorgang, tn.IDBuchungTN, z.IDBuchungTNZuschlag; ``` **Caveat:** both captured cases were `Anfrage` 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.