DBSlayer leeft weer! Nu met KV7 data

76 views

Skip to first unread message

Stefan de Konink

unread,

Mar 15, 2012, 10:14:34 PM3/15/12

to ope...@googlegroups.com

In de categorie er zijn meer wegen die naar Rome leiden hebben was het
na het JSON avontuur van gisteravond <http://kv7.openov.nl/json/> wel
weer tijd om ook een andere techniek aan te bieden.

De mensen die openkvk wel eens bezoeken weten dat daar de database
wagenwijd openstaat, en dat je niet eens sql injection hoeft te doen om
queries uit te voeren. Zoiets hadden we eind 2009 ook voor openOV, maar
dat raakt dan weer in de vergetelheid.

De volgende tabellen komen vrijwel rechtstreeks uit de KV7 documentatie.

CREATE TABLE "sys"."dataowner" (
"dataownercode" VARCHAR(10) NOT NULL,
"dataownertype" VARCHAR(10) NOT NULL,
"dataownername" VARCHAR(30) NOT NULL,
"dataownercompanynumber" DECIMAL(3),
CONSTRAINT "dataowner_dataownercode_pkey" PRIMARY KEY
("dataownercode")
);

CREATE TABLE "sys"."destination" (
"dataownercode" VARCHAR(10) NOT NULL,
"destinationcode" VARCHAR(10) NOT NULL,
"destinationname50" VARCHAR(50) NOT NULL,
"destinationname30" VARCHAR(30),
"destinationname24" VARCHAR(24),
"destinationname19" VARCHAR(19),
"destinationname16" VARCHAR(16) NOT NULL,
"destinationdetail24" VARCHAR(24),
"destinationdetail19" VARCHAR(19),
"destinationdetail16" VARCHAR(16),
"destinationdisplay16" VARCHAR(16),
CONSTRAINT "destination_dataownercode_destinationcode_pkey"
PRIMARY KEY ("dataownercode", "destinationcode")
);

CREATE TABLE "sys"."destinationvia" (
"dataownercode" VARCHAR(10) NOT NULL,
"destinationcodep" VARCHAR(10) NOT NULL,
"destinationcodec" VARCHAR(10) NOT NULL,
"destinationviaordernr" TINYINT NOT NULL,
CONSTRAINT
"destinationvia_dataownercode_destinationcodep_destinationcodec_pkey"
PRIMARY KEY ("dataownercode", "destinationcodep", "destinationcodec")
);

CREATE TABLE "sys"."line" (
"dataownercode" VARCHAR(10) NOT NULL,
"lineplanningnumber" VARCHAR(10) NOT NULL,
"linepublicnumber" VARCHAR(4) NOT NULL,
"linename" VARCHAR(50),
"linevetagnumber" DECIMAL(3),
"transporttype" VARCHAR(5) NOT NULL,
CONSTRAINT "line_dataownercode_lineplanningnumber_pkey" PRIMARY
KEY ("dataownercode", "lineplanningnumber")
);

CREATE TABLE "sys"."localservicegroup" (
"dataownercode" VARCHAR(10) NOT NULL,
"localservicelevelcode" VARCHAR(10) NOT NULL,
CONSTRAINT
"localservicegroup_dataownercode_localservicelevelcode_pkey" PRIMARY KEY
("dataownercode", "localservicelevelcode")
);

CREATE TABLE "sys"."localservicegrouppasstime" (
"dataownercode" VARCHAR(10) NOT NULL,
"localservicelevelcode" VARCHAR(10) NOT NULL,
"lineplanningnumber" VARCHAR(10) NOT NULL,
"journeynumber" DECIMAL(6) NOT NULL,
"fortifynumber" DECIMAL(2) NOT NULL,
"userstopcode" VARCHAR(10) NOT NULL,
"userstopordernumber" DECIMAL(3) NOT NULL,
"linedirection" DECIMAL NOT NULL,
"destinationcode" VARCHAR(10) NOT NULL,
"targetarrivaltime" VARCHAR(8) NOT NULL,
"targetdeparturetime" VARCHAR(8) NOT NULL,
"sidecode" VARCHAR(10) NOT NULL,
"wheelchairaccesible" BOOLEAN,
"journeystoptype" VARCHAR(12) NOT NULL,
"istimingstop" BOOLEAN NOT NULL,
"productformulatype" DECIMAL(4),
CONSTRAINT
"localservicegrouppasstime_dataownercode_localservicelevelcode_lineplanningnumber_journeynumber_fortifynumber_userstopcode_userstopordernumber_pkey"
PRIMARY KEY ("dataownercode", "localservicelevelcode",
"lineplanningnumber", "journeynumber", "fortifynumber", "userstopcode",
"userstopordernumber")
);

CREATE TABLE "sys"."localservicegroupvalidity" (
"dataownercode" VARCHAR(10) NOT NULL,
"localservicelevelcode" VARCHAR(10) NOT NULL,
"operationdate" DATE NOT NULL,
CONSTRAINT
"localservicegroupvalidity_dataownercode_localservicelevelcode_operationdate_pkey"
PRIMARY KEY ("dataownercode", "localservicelevelcode", "operationdate")
);

CREATE TABLE "sys"."timingpoint" (
"dataownercode" VARCHAR(10) NOT NULL,
"timingpointcode" VARCHAR(10) NOT NULL,
"timingpointname" VARCHAR(50) NOT NULL,
"timingpointtown" VARCHAR(50) NOT NULL,
"stopareacode" VARCHAR(10),
CONSTRAINT "timingpoint_dataownercode_timingpointcode_pkey"
PRIMARY KEY ("dataownercode", "timingpointcode")
);

CREATE TABLE "sys"."usertimingpoint" (
"dataownercode" VARCHAR(10) NOT NULL,
"userstopcode" VARCHAR(10) NOT NULL,
"timingpointdataownercode" VARCHAR(10) NOT NULL,
"timingpointcode" VARCHAR(10) NOT NULL,
CONSTRAINT "usertimingpoint_dataownercode_userstopcode_pkey"
PRIMARY KEY ("dataownercode", "userstopcode")
);

Paar snelle voorbeeldjes dan.

Ok we willen haltetijden van de Oostzanerdijk.

-- nummer op de bus, aankomst en vertrektijd
select linepublicnumber, targetarrivaltime, targetdeparturetime from

-- tabel met alle tijden, tabel met omnummering van dag naar sleutel,
-- tabel met vervoerder specifieke haltenummering, tabel met lijnen
localservicegrouppasstime as p, localservicegroupvalidity as v,
usertimingpoint as u, line as l

where

-- ik wil de sleutels koppelen
p.localservicelevelcode = v.localservicelevelcode and
p.userstopcode = u.userstopcode and
p.lineplanningnumber = l.lineplanningnumber and

-- ik wil vandaag
operationdate = '2012-03-16' and

-- ik wil vanaf halte
timingpointcode = '013632'

-- en het moet er goed uitzien
order by p.lineplanningnumber, linedirection, journeynumber,
fortifynumber limit 5;

+------------------+-------------------+---------------------+
| linepublicnumber | targetarrivaltime | targetdeparturetime |
+==================+===================+=====================+
| 245 | 04:42:16 | 04:42:34 |
| 245 | 04:57:16 | 04:57:34 |
| 245 | 05:52:16 | 05:52:34 |
| 38 | 06:23:00 | 00:00:00 |
| 38 | 06:38:00 | 00:00:00 |
+------------------+-------------------+---------------------+
5 tuples (31.840ms)

00:00:00 'schijnt' te zijn dat het de laatste halte op de lijn is. De
tijden staan gecodeerd in 32-urige dagen. Dat betekent dat 24:15:00,
kwart over 12 van de volgende dag is, gegeven de huidige operationdate.

En op verzoek van Stijn die die buurt graag verbetert ook dan nog maar
even hoe je alle haltes achter elkaar opzoekt;

SELECT linepublicnumber,
userstopordernumber,
timingpointname,
timingpointtown
FROM localservicegrouppasstime AS p, localservicegroupvalidity AS v,
line AS l, usertimingpoint AS u, timingpoint AS t
WHERE p.dataownercode = l.dataownercode
AND p.lineplanningnumber = l.lineplanningnumber
AND p.dataownercode = v.dataownercode
AND p.localservicelevelcode = v.localservicelevelcode
AND p.dataownercode = u.dataownercode
AND p.userstopcode = u.userstopcode
AND u.timingpointdataownercode = t.dataownercode
AND u.timingpointcode = t.timingpointcode
AND linepublicnumber = 245
AND linedirection = 1
AND operationdate = '2012-03-16'
AND journeynumber = 3
ORDER BY userstopordernumber;

Waarom al die 'dataownercode' attributen? Omdat in tegenstelling tot wat
normaal gesproken 'handig' is hier gebruik wordt gemaakt van
multi-column primary keys die een vervoerder en een dienst van een
vervoerder aangeeft. Dit op een andere manier normaliseren klinkt als
iets wat zeker nog eens gaat gebeuren.

En waar stuur je dit dan naar toe?

<http://api.openov.nl/json/select * from timingpoint limit 1;>

Andere gedachtes: we willen ook eens gaan kijken naar Redis of dat
directe replicatie toestaat van de gehele statische service. En daar
binnen dan aan de gang gaan met een 'handige' manier van toegang tot
data vanuit client perspectief.

Als er mensen op deze Google Group zitten die graag mee willen helpen,
in code of beheer, horen we dat natuurlijk graag.