Skip to content

Commit ad17187

Browse files
goekaygoekay
committed
store reservations with connector id info in database
even though the connector id must be specified for a ReserveNow operation, for whatever reason, we did not store this information in the reservation table. and since charge_box_id and connector_id pair is unique, we can just store connector_pk. with this update, we also present the connector id in the web ui.
1 parent e5f6312 commit ad17187

7 files changed

Lines changed: 129 additions & 42 deletions

File tree

‎src/main/java/de/rwth/idsg/steve/repository/ReservationRepository.java‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,6 @@
11
package de.rwth.idsg.steve.repository;
22

3+
import de.rwth.idsg.steve.repository.dto.InsertReservationParams;
34
import de.rwth.idsg.steve.repository.dto.Reservation;
45
import de.rwth.idsg.steve.web.dto.ReservationQueryForm;
56
import org.joda.time.DateTime;
@@ -18,7 +19,7 @@ public interface ReservationRepository {
1819
/**
1920
* Returns the id of the reservation, if the reservation is inserted.
2021
*/
21-
int insert(String idTag, String chargeBoxId, DateTime startTimestamp, DateTime expiryTimestamp);
22+
int insert(InsertReservationParams params);
2223

2324
/**
2425
* Deletes the temporarily inserted reservation, when

‎src/main/java/de/rwth/idsg/steve/repository/dto/InsertReservationParams.java‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
package de.rwth.idsg.steve.repository.dto;
2+
3+
import lombok.Builder;
4+
import lombok.Getter;
5+
import org.joda.time.DateTime;
6+
7+
/**
8+
* @author Sevket Goekay <goekay@dbis.rwth-aachen.de>
9+
* @since 21.03.2016
10+
*/
11+
@Getter
12+
@Builder
13+
public class InsertReservationParams {
14+
private final String idTag, chargeBoxId;
15+
private final int connectorId;
16+
private final DateTime startTimestamp, expiryTimestamp;
17+
}

‎src/main/java/de/rwth/idsg/steve/repository/dto/Reservation.java‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,7 @@
1212
@Builder
1313
public final class Reservation {
1414
private final int id, ocppTagPk, chargeBoxPk;
15+
private final int connectorId;
1516
private final Integer transactionId;
1617
private final String ocppIdTag, chargeBoxId, startDatetime, expiryDatetime, status;
1718
}

‎src/main/java/de/rwth/idsg/steve/repository/impl/ReservationRepositoryImpl.java‎

Lines changed: 52 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -3,23 +3,28 @@
33
import de.rwth.idsg.steve.SteveException;
44
import de.rwth.idsg.steve.repository.ReservationRepository;
55
import de.rwth.idsg.steve.repository.ReservationStatus;
6+
import de.rwth.idsg.steve.repository.dto.InsertReservationParams;
67
import de.rwth.idsg.steve.repository.dto.Reservation;
78
import de.rwth.idsg.steve.utils.CustomDSL;
89
import de.rwth.idsg.steve.utils.DateTimeUtils;
910
import de.rwth.idsg.steve.web.dto.ReservationQueryForm;
1011
import lombok.extern.slf4j.Slf4j;
1112
import org.joda.time.DateTime;
1213
import org.jooq.DSLContext;
13-
import org.jooq.Record9;
14+
import org.jooq.Record1;
15+
import org.jooq.Record10;
1416
import org.jooq.RecordMapper;
17+
import org.jooq.SelectConditionStep;
1518
import org.jooq.SelectQuery;
1619
import org.jooq.exception.DataAccessException;
20+
import org.jooq.impl.DSL;
1721
import org.springframework.beans.factory.annotation.Autowired;
1822
import org.springframework.stereotype.Repository;
1923

2024
import java.util.List;
2125

2226
import static jooq.steve.db.tables.ChargeBox.CHARGE_BOX;
27+
import static jooq.steve.db.tables.Connector.CONNECTOR;
2328
import static jooq.steve.db.tables.OcppTag.OCPP_TAG;
2429
import static jooq.steve.db.tables.Reservation.RESERVATION;
2530

@@ -39,7 +44,9 @@ public List<Reservation> getReservations(ReservationQueryForm form) {
3944
SelectQuery selectQuery = ctx.selectQuery();
4045
selectQuery.addFrom(RESERVATION);
4146
selectQuery.addJoin(OCPP_TAG, OCPP_TAG.ID_TAG.eq(RESERVATION.ID_TAG));
42-
selectQuery.addJoin(CHARGE_BOX, CHARGE_BOX.CHARGE_BOX_ID.eq(RESERVATION.CHARGE_BOX_ID));
47+
selectQuery.addJoin(CONNECTOR, CONNECTOR.CONNECTOR_PK.eq(RESERVATION.CONNECTOR_PK));
48+
selectQuery.addJoin(CHARGE_BOX, CONNECTOR.CHARGE_BOX_ID.eq(CHARGE_BOX.CHARGE_BOX_ID));
49+
4350
selectQuery.addSelect(
4451
RESERVATION.RESERVATION_PK,
4552
RESERVATION.TRANSACTION_PK,
@@ -49,11 +56,12 @@ public List<Reservation> getReservations(ReservationQueryForm form) {
4956
CHARGE_BOX.CHARGE_BOX_ID,
5057
RESERVATION.START_DATETIME,
5158
RESERVATION.EXPIRY_DATETIME,
52-
RESERVATION.STATUS
59+
RESERVATION.STATUS,
60+
CONNECTOR.CONNECTOR_ID
5361
);
5462

5563
if (form.isChargeBoxIdSet()) {
56-
selectQuery.addConditions(RESERVATION.CHARGE_BOX_ID.eq(form.getChargeBoxId()));
64+
selectQuery.addConditions(CHARGE_BOX.CHARGE_BOX_ID.eq(form.getChargeBoxId()));
5765
}
5866

5967
if (form.isOcppIdTagSet()) {
@@ -76,24 +84,31 @@ public List<Reservation> getReservations(ReservationQueryForm form) {
7684
public List<Integer> getActiveReservationIds(String chargeBoxId) {
7785
return ctx.select(RESERVATION.RESERVATION_PK)
7886
.from(RESERVATION)
79-
.where(RESERVATION.CHARGE_BOX_ID.equal(chargeBoxId))
80-
.and(RESERVATION.EXPIRY_DATETIME.greaterThan(CustomDSL.utcTimestamp()))
81-
.and(RESERVATION.STATUS.equal(ReservationStatus.ACCEPTED.name()))
87+
.where(RESERVATION.CONNECTOR_PK.in(DSL.select(CONNECTOR.CONNECTOR_PK)
88+
.from(CONNECTOR)
89+
.where(CONNECTOR.CHARGE_BOX_ID.equal(chargeBoxId))))
90+
.and(RESERVATION.EXPIRY_DATETIME.greaterThan(CustomDSL.utcTimestamp()))
91+
.and(RESERVATION.STATUS.equal(ReservationStatus.ACCEPTED.name()))
8292
.fetch(RESERVATION.RESERVATION_PK);
8393
}
8494

8595
@Override
86-
public int insert(String idTag, String chargeBoxId, DateTime startTimestamp, DateTime expiryTimestamp) {
96+
public int insert(InsertReservationParams params) {
8797
// Check overlapping
8898
//isOverlapping(startTimestamp, expiryTimestamp, chargeBoxId);
8999

90-
int reservationId = ctx.insertInto(RESERVATION,
91-
RESERVATION.ID_TAG, RESERVATION.CHARGE_BOX_ID,
92-
RESERVATION.START_DATETIME, RESERVATION.EXPIRY_DATETIME,
93-
RESERVATION.STATUS)
94-
.values(idTag, chargeBoxId,
95-
startTimestamp, expiryTimestamp,
96-
ReservationStatus.WAITING.name())
100+
SelectConditionStep<Record1<Integer>> connectorPkQuery =
101+
DSL.select(CONNECTOR.CONNECTOR_PK)
102+
.from(CONNECTOR)
103+
.where(CONNECTOR.CHARGE_BOX_ID.equal(params.getChargeBoxId()))
104+
.and(CONNECTOR.CONNECTOR_ID.equal(params.getConnectorId()));
105+
106+
int reservationId = ctx.insertInto(RESERVATION)
107+
.set(RESERVATION.CONNECTOR_PK, connectorPkQuery)
108+
.set(RESERVATION.ID_TAG, params.getIdTag())
109+
.set(RESERVATION.START_DATETIME, params.getStartTimestamp())
110+
.set(RESERVATION.EXPIRY_DATETIME, params.getExpiryTimestamp())
111+
.set(RESERVATION.STATUS, ReservationStatus.WAITING.name())
97112
.returning(RESERVATION.RESERVATION_PK)
98113
.fetchOne()
99114
.getReservationPk();
@@ -135,9 +150,11 @@ public void used(int reservationId, int transactionId) {
135150
// -------------------------------------------------------------------------
136151

137152
private static class ReservationMapper implements
138-
RecordMapper<Record9<Integer, Integer, Integer, Integer, String, String, DateTime, DateTime, String>, Reservation> {
153+
RecordMapper<Record10<Integer, Integer, Integer, Integer, String,
154+
String, DateTime, DateTime, String, Integer>, Reservation> {
139155
@Override
140-
public Reservation map(Record9<Integer, Integer, Integer, Integer, String, String, DateTime, DateTime, String> r) {
156+
public Reservation map(Record10<Integer, Integer, Integer, Integer, String,
157+
String, DateTime, DateTime, String, Integer> r) {
141158
return Reservation.builder()
142159
.id(r.value1())
143160
.transactionId(r.value2())
@@ -148,6 +165,7 @@ public Reservation map(Record9<Integer, Integer, Integer, Integer, String, Strin
148165
.startDatetime(DateTimeUtils.humanize(r.value7()))
149166
.expiryDatetime(DateTimeUtils.humanize(r.value8()))
150167
.status(r.value9())
168+
.connectorId(r.value10())
151169
.build();
152170
}
153171
}
@@ -184,21 +202,21 @@ private void processType(SelectQuery selectQuery, ReservationQueryForm form) {
184202
/**
185203
* Throws exception, if there are rows whose date/time ranges overlap with the input
186204
*/
187-
private void isOverlapping(DateTime start, DateTime stop, String chargeBoxId) {
188-
try {
189-
int count = ctx.selectOne()
190-
.from(RESERVATION)
191-
.where(RESERVATION.EXPIRY_DATETIME.greaterOrEqual(start))
192-
.and(RESERVATION.START_DATETIME.lessOrEqual(stop))
193-
.and(RESERVATION.CHARGE_BOX_ID.equal(chargeBoxId))
194-
.execute();
195-
196-
if (count != 1) {
197-
throw new SteveException("The desired reservation overlaps with another reservation");
198-
}
199-
200-
} catch (DataAccessException e) {
201-
log.error("Exception occurred", e);
202-
}
203-
}
205+
// private void isOverlapping(DateTime start, DateTime stop, String chargeBoxId) {
206+
// try {
207+
// int count = ctx.selectOne()
208+
// .from(RESERVATION)
209+
// .where(RESERVATION.EXPIRY_DATETIME.greaterOrEqual(start))
210+
// .and(RESERVATION.START_DATETIME.lessOrEqual(stop))
211+
// .and(RESERVATION.CHARGE_BOX_ID.equal(chargeBoxId))
212+
// .execute();
213+
//
214+
// if (count != 1) {
215+
// throw new SteveException("The desired reservation overlaps with another reservation");
216+
// }
217+
//
218+
// } catch (DataAccessException e) {
219+
// log.error("Exception occurred", e);
220+
// }
221+
// }
204222
}

‎src/main/java/de/rwth/idsg/steve/service/ChargePointService15_Client.java‎

Lines changed: 12 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -16,11 +16,11 @@
1616
import de.rwth.idsg.steve.handler.ocpp15.UnlockConnectorResponseHandler;
1717
import de.rwth.idsg.steve.handler.ocpp15.UpdateFirmwareResponseHandler;
1818
import de.rwth.idsg.steve.ocpp.OcppVersion;
19+
import de.rwth.idsg.steve.repository.OcppTagRepository;
1920
import de.rwth.idsg.steve.repository.RequestTaskStore;
2021
import de.rwth.idsg.steve.repository.ReservationRepository;
21-
import de.rwth.idsg.steve.repository.OcppTagRepository;
2222
import de.rwth.idsg.steve.repository.dto.ChargePointSelect;
23-
import de.rwth.idsg.steve.web.dto.task.RequestTask;
23+
import de.rwth.idsg.steve.repository.dto.InsertReservationParams;
2424
import de.rwth.idsg.steve.web.dto.common.GetDiagnosticsParams;
2525
import de.rwth.idsg.steve.web.dto.common.MultipleChargePointSelect;
2626
import de.rwth.idsg.steve.web.dto.common.RemoteStartTransactionParams;
@@ -36,6 +36,7 @@
3636
import de.rwth.idsg.steve.web.dto.ocpp15.ReserveNowParams;
3737
import de.rwth.idsg.steve.web.dto.ocpp15.ResetParams;
3838
import de.rwth.idsg.steve.web.dto.ocpp15.SendLocalListParams;
39+
import de.rwth.idsg.steve.web.dto.task.RequestTask;
3940
import lombok.extern.slf4j.Slf4j;
4041
import ocpp.cp._2012._06.AuthorisationData;
4142
import ocpp.cp._2012._06.CancelReservationRequest;
@@ -377,11 +378,15 @@ public int reserveNow(ReserveNowParams params) {
377378
ChargePointSelect c = list.get(0);
378379
String chargeBoxId = c.getChargeBoxId();
379380

380-
// Insert into DB
381-
DateTime startTimestamp = DateTime.now();
382-
DateTime expiryTimestamp = params.getExpiry().toDateTime();
383-
int reservationId = reservationRepository.insert(params.getIdTag(), chargeBoxId,
384-
startTimestamp, expiryTimestamp);
381+
InsertReservationParams res = InsertReservationParams.builder()
382+
.idTag(params.getIdTag())
383+
.chargeBoxId(chargeBoxId)
384+
.connectorId(params.getConnectorId())
385+
.startTimestamp(DateTime.now())
386+
.expiryTimestamp(params.getExpiry().toDateTime())
387+
.build();
388+
389+
int reservationId = reservationRepository.insert(res);
385390

386391
ReserveNowRequest req = this.prepareReserveNow(params, reservationId);
387392
RequestTask task = new RequestTask(VERSION, req, list);

‎src/main/resources/db/migration/V0_9_0__update.sql‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
--
2+
-- store connector_pk in reservation table.
3+
--
4+
5+
START TRANSACTION;
6+
7+
-- add column without constraints
8+
9+
ALTER TABLE `reservation`
10+
ADD COLUMN `connector_pk` INT(11) UNSIGNED AFTER `reservation_pk`;
11+
12+
13+
-- set connector_pk of existing reservations to connector 0 of the corresponding charge box.
14+
15+
UPDATE `reservation`
16+
SET `connector_pk` = (
17+
SELECT `connector`.`connector_pk`
18+
FROM `connector`
19+
WHERE `connector`.`charge_box_id` = `reservation`.`charge_box_id` AND `connector`.`connector_id` = 0
20+
);
21+
22+
23+
-- now that all connector_pk columns have values set, add constraints
24+
25+
ALTER TABLE `reservation`
26+
MODIFY COLUMN `connector_pk` INT(11) UNSIGNED NOT NULL AFTER `reservation_pk`,
27+
ADD INDEX `FK_connector_pk_reserv_idx` (`connector_pk` ASC);
28+
29+
ALTER TABLE `reservation`
30+
ADD CONSTRAINT `FK_connector_pk_reserv` FOREIGN KEY (`connector_pk`)
31+
REFERENCES `connector` (`connector_pk`)
32+
ON DELETE CASCADE
33+
ON UPDATE NO ACTION;
34+
35+
36+
-- charge_box_id column is redundant, remove it.
37+
38+
ALTER TABLE `reservation`
39+
DROP FOREIGN KEY `FK_reservation_charge_box_cbid`,
40+
DROP COLUMN `charge_box_id`,
41+
DROP INDEX `FK_chargeBoxId_r_idx` ;
42+
43+
COMMIT;

‎src/main/resources/webapp/WEB-INF/views/data-man/reservations.jsp‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,7 @@
7777
<th>Transaction ID</th>
7878
<th>OCPP ID Tag</th>
7979
<th>ChargeBox ID</th>
80+
<th>Connector ID</th>
8081
<th>Start Date/Time</th>
8182
<th>Expiry Date/Time</th>
8283
<th>Status</th>
@@ -93,6 +94,7 @@
9394
</td>
9495
<td><a href="/steve/manager/ocppTags/details/${res.ocppTagPk}">${res.ocppIdTag}</a></td>
9596
<td><a href="/steve/manager/chargepoints/details/${res.chargeBoxPk}">${res.chargeBoxId}</a></td>
97+
<td>${res.connectorId}</td>
9698
<td>${res.startDatetime}</td>
9799
<td>${res.expiryDatetime}</td>
98100
<td>${res.status}</td>

0 commit comments

Comments
 (0)