In Part 1, we should have been able to make a successful call to the KIA Service using curl. Now, we need an Instant Client and a Wallet to establish a secure connection to the database.
The Linux instance requires an Instant Client and Wallet, thanks to Dimitri Gielis: dgielis.blogspot.com/2019/10/free-oracle-cl..
Setting Up Oracle Database Tables
Since we receive a JSON string from our queries, we will store it as a JSON data type:
sqlplus user/pwd@db23120321xxxx_low
CREATE TABLE "ENIRO" // next time i call it CAR_RESPONSE
( "STATUS_DATE" TIMESTAMP (6) NOT NULL ENABLE,
"JSON_DATA" JSON,
"JSON_TYPE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
CONSTRAINT "ENIRO_PK" PRIMARY KEY ("STATUS_DATE", "JSON_TYPE")
);
Once you have a central location for storing JSON responses, you can easily create various views that make this data more readable as needed. Now, in the carcommunications.js file (see Part 1), you can activate the database calls by removing the comments. It's a good idea to test again with the Curl statements, and data should now be entered into the Eniro table. Within the database, I created numerous views to make the different JSON responses of the vehicle evaluable via SQL.
/* Die Drive History liefert 2 mal am Tag neue Daten für die letzten 30 Tage, da ich sie natürlich öfters Abfragen kann zeige ich in der View nur den letzten Eintrag pro Tag an */
CREATE OR REPLACE VIEW "DRIVE_HISTORY" AS
with rawdata as (
SELECT status_date,
trunc(to_date(datetime,'YYYYMMDD'),'YYYY') drivingyear,
trunc(to_date(datetime,'YYYYMMDD'),'MM') drivingmonth,
trunc(to_date(datetime,'YYYYMMDD'),'IW') drivingweek,
to_date(datetime,'YYYYMMDD') drivingday,
distance,
engine/1000 engine_kwh,
total/1000 kwh,
round((total/1000/distance)*100,2)*1.1 kwh_pro_100km,
round(regeneration/1000,2) regenerated_kwh
FROM eniro e ,
JSON_TABLE (json_data, '$'
columns (
NESTED PATH '$.history[*]'
COLUMNS (
datetime NUMBER PATH '$.rawDate',
distance NUMBER PATH '$.distance',
regeneration NUMBER PATH '$.regen',
NESTED PATH '$.consumption[*]'
columns ( total NUMBER PATH '$.total',
engine NUMBER PATH '$.engine')
)
)) jt
where e.json_type = 'DRIVEHISTORY'),
lsd as (select max(status_date) maxstatus,
drivingday
from rawdata
group by drivingday)
select
r."STATUS_DATE",
r."DRIVINGDAY",
r."DISTANCE",
r."ENGINE_KWH",
r."KWH",
r."KWH_PRO_100KM",
r."REGENERATED_KWH"
from rawdata r join lsd
on (r.status_date = lsd.maxstatus
and r.drivingday = lsd.drivingday);
/* Alle 30min wird der Status abgefragt hier eine View für die Status Daten des Fahrzeugs */
CREATE OR REPLACE VIEW "STATUS" AS
SELECT status_date,
time,
batstatus12v ,
batterycharge,
batterystatus,
evModeRange theoreticalRange,
doorlock,
climate,
frunkopen,
trunkOpen,
tirePressureLampAll,
plugin,
case when batterycharge = 'true' then remaintime else 0 end remaintime
FROM eniro e ,
JSON_TABLE (json_data, '$'
columns (
time NUMBER PATH '$.time',
batstatus12v NUMBER PATH '$.battery.batSoc',
tirePressureLampAll NUMBER PATH '$.tirePressureLamp.tirePressureLampAll',
climate varchar2 PATH '$.airCtrlOn',
frunkopen varchar2 PATH '$.hoodOpen',
trunkOpen varchar2 PATH '$.trunkOpen',
doorlock varchar2 PATH '$.doorLock',
NESTED PATH '$.evStatus[*]'
columns ( batterycharge varchar2 PATH '$.batteryCharge',
batterystatus NUMBER PATH '$.batteryStatus',
plugin NUMBER PATH '$.batteryPlugin',
remaintime NUMBER PATH '$.remainTime2.atc.value',
evModeRange NUMBER PATH '$.drvDistance.rangeByFuel.evModeRange.value'
)
)
) jt
where e.json_type = 'STATUS';
My approach: Smart DB, which means: all logic is in the database and APEX serves as the "only" frontend.
In the Git repository, you'll find a plethora of additional views that were helpful for the app.
Through Scheduler Jobs, various tasks such as status updates and drive history are transmitted to KIA via NGINX+NODE.JS. For command executions like opening doors and turning the air conditioning on/off, these database jobs are simply enabled as needed within the app.
Continuing in Part 3: