git.basschouten.com Git - openhab-addons.git/blob

   1 /**
   2  * Copyright (c) 2010-2023 Contributors to the openHAB project
   3  *
   4  * See the NOTICE file(s) distributed with this work for additional
   5  * information.
   6  *
   7  * This program and the accompanying materials are made available under the
   8  * terms of the Eclipse Public License 2.0 which is available at
   9  * http://www.eclipse.org/legal/epl-2.0
  10  *
  11  * SPDX-License-Identifier: EPL-2.0
  12  */
  13 package org.openhab.persistence.jdbc.internal.db;
  14
  15 import static org.hamcrest.CoreMatchers.is;
  16 import static org.hamcrest.MatcherAssert.assertThat;
  17 import static org.junit.jupiter.api.Assertions.assertEquals;
  18 import static org.junit.jupiter.api.Assertions.assertInstanceOf;
  19 import static org.junit.jupiter.api.Assertions.assertThrows;
  20
  21 import java.time.Instant;
  22 import java.time.LocalDateTime;
  23 import java.time.ZoneId;
  24 import java.time.ZonedDateTime;
  25 import java.time.format.DateTimeFormatter;
  26 import java.util.Objects;
  27 import java.util.stream.Stream;
  28
  29 import javax.measure.Quantity;
  30 import javax.measure.Unit;
  31
  32 import org.eclipse.jdt.annotation.NonNullByDefault;
  33 import org.eclipse.jdt.annotation.Nullable;
  34 import org.junit.jupiter.api.BeforeEach;
  35 import org.junit.jupiter.api.Test;
  36 import org.junit.jupiter.params.ParameterizedTest;
  37 import org.junit.jupiter.params.provider.Arguments;
  38 import org.junit.jupiter.params.provider.MethodSource;
  39 import org.openhab.core.items.Item;
  40 import org.openhab.core.library.items.CallItem;
  41 import org.openhab.core.library.items.ColorItem;
  42 import org.openhab.core.library.items.ContactItem;
  43 import org.openhab.core.library.items.DateTimeItem;
  44 import org.openhab.core.library.items.DimmerItem;
  45 import org.openhab.core.library.items.ImageItem;
  46 import org.openhab.core.library.items.LocationItem;
  47 import org.openhab.core.library.items.NumberItem;
  48 import org.openhab.core.library.items.PlayerItem;
  49 import org.openhab.core.library.items.RollershutterItem;
  50 import org.openhab.core.library.items.StringItem;
  51 import org.openhab.core.library.items.SwitchItem;
  52 import org.openhab.core.library.types.DateTimeType;
  53 import org.openhab.core.library.types.DecimalType;
  54 import org.openhab.core.library.types.HSBType;
  55 import org.openhab.core.library.types.OnOffType;
  56 import org.openhab.core.library.types.OpenClosedType;
  57 import org.openhab.core.library.types.PercentType;
  58 import org.openhab.core.library.types.PlayPauseType;
  59 import org.openhab.core.library.types.PointType;
  60 import org.openhab.core.library.types.QuantityType;
  61 import org.openhab.core.library.types.RawType;
  62 import org.openhab.core.library.types.RewindFastforwardType;
  63 import org.openhab.core.library.types.StringListType;
  64 import org.openhab.core.library.types.StringType;
  65 import org.openhab.core.library.unit.SIUnits;
  66 import org.openhab.core.persistence.FilterCriteria;
  67 import org.openhab.core.persistence.FilterCriteria.Ordering;
  68 import org.openhab.core.types.State;
  69
  70 /**
  71  * Tests the {@link JdbcBaseDAO}.
  72  *
  73  * @author Christoph Weitkamp - Initial contribution
  74  */
  75 @NonNullByDefault
  76 public class JdbcBaseDAOTest {
  77
  78     private static final String DATE_PATTERN = "yyyy-MM-dd'T'HH:mm:ss";
  79     private static final DateTimeFormatter DATE_PARSER = DateTimeFormatter.ofPattern(DATE_PATTERN);
  80     private static final ZoneId UTC_ZONE_ID = ZoneId.of("UTC");
  81     private static final String DB_TABLE_NAME = "testitem";
  82
  83     private final JdbcBaseDAO jdbcBaseDAO = new JdbcBaseDAO();
  84     private @NonNullByDefault({}) FilterCriteria filter;
  85
  86     @BeforeEach
  87     void setup() {
  88         filter = new FilterCriteria();
  89     }
  90
  91     @ParameterizedTest
  92     @MethodSource("provideTestCasesForObjectAsStateValid")
  93     void objectAsStateReturnsValidStateForCompatibleType(Item item, Object value,
  94             @Nullable Unit<? extends Quantity<?>> unit, Object expected) {
  95         State actual = jdbcBaseDAO.objectAsState(item, unit, value);
  96         assertInstanceOf(expected.getClass(), actual, item.getName());
  97         assertEquals(expected, actual, item.getName());
  98     }
  99
 100     private static Stream<Arguments> provideTestCasesForObjectAsStateValid() {
 101         return Stream.of( //
 102                 Arguments.of(new ImageItem("String_ImageItem"),
 103                         new RawType(new byte[0], "application/octet-stream").toFullString(), null,
 104                         new RawType(new byte[0], "application/octet-stream")),
 105                 Arguments.of(new NumberItem("Float_NumberItem"), 7.3, null, DecimalType.valueOf("7.3")),
 106                 Arguments.of(new NumberItem("Float_NumberItem_Unit"), 7.3, SIUnits.CELSIUS,
 107                         QuantityType.valueOf("7.3 °C")),
 108                 Arguments.of(new ContactItem("String_ContactItem"), "OPEN", null, OpenClosedType.OPEN),
 109                 Arguments.of(new PlayerItem("String_PlayerItem_Play"), "PLAY", null, PlayPauseType.PLAY),
 110                 Arguments.of(new PlayerItem("String_PlayerItem_Rewind"), "REWIND", null, RewindFastforwardType.REWIND),
 111                 Arguments.of(new CallItem("String_CallItem"), "0699222222,0179999998", null,
 112                         StringListType.valueOf("0699222222,0179999998")),
 113                 Arguments.of(new StringItem("String_StringItem"), "String", null, StringType.valueOf("String")),
 114                 Arguments.of(new SwitchItem("String_SwitchItem"), "ON", null, OnOffType.ON),
 115                 Arguments.of(new DimmerItem("Integer_DimmerItem"), 52, null, PercentType.valueOf("52")),
 116                 Arguments.of(new RollershutterItem("Integer_RollershutterItem"), 39, null, PercentType.valueOf("39")),
 117                 Arguments.of(new ColorItem("CharArray_ColorItem"),
 118                         new byte[] { (byte) '1', (byte) '8', (byte) '4', (byte) ',', (byte) '1', (byte) '0', (byte) '0',
 119                                 (byte) ',', (byte) '5', (byte) '2' },
 120                         null, HSBType.valueOf("184,100,52")),
 121                 Arguments.of(new ColorItem("String_ColorItem"), "184,100,52", null, HSBType.valueOf("184,100,52")),
 122                 Arguments.of(new LocationItem("String_LocationItem"), "1,2,3", null, PointType.valueOf("1,2,3")),
 123                 Arguments.of(new DateTimeItem("Timestamp_DateTimeItem"),
 124                         java.sql.Timestamp.valueOf("2021-02-01 23:30:02.049"), null,
 125                         DateTimeType.valueOf("2021-02-01T23:30:02.049")),
 126                 Arguments.of(new DateTimeItem("LocalDateTime_DateTimeItem"),
 127                         LocalDateTime.parse("2021-02-01T23:30:02.049"), null,
 128                         DateTimeType.valueOf("2021-02-01T23:30:02.049")),
 129                 Arguments.of(new DateTimeItem("Long_DateTimeItem"), Long.valueOf("1612222202049"), null,
 130                         new DateTimeType(ZonedDateTime.ofInstant(Instant.parse("2021-02-01T23:30:02.049Z"),
 131                                 ZoneId.systemDefault()))),
 132                 Arguments.of(new DateTimeItem("Date_DateTimeItem"), java.sql.Date.valueOf("2021-02-01"), null,
 133                         DateTimeType.valueOf("2021-02-01T00:00:00.000")),
 134                 Arguments.of(new DateTimeItem("Instant_DateTimeItem"), Instant.parse("2021-02-01T23:30:02.049Z"), null,
 135                         new DateTimeType(ZonedDateTime.ofInstant(Instant.parse("2021-02-01T23:30:02.049Z"),
 136                                 ZoneId.systemDefault()))),
 137                 Arguments.of(new DateTimeItem("String_DateTimeItem"), "2021-02-01 23:30:02.049", null,
 138                         DateTimeType.valueOf("2021-02-01T23:30:02.049")));
 139     }
 140
 141     @ParameterizedTest
 142     @MethodSource("provideTestCasesForObjectAsStateInvalid")
 143     void objectAsStateThrowsUnsupportedOperationExceptionForIncompatibleType(Item item, Object value,
 144             @Nullable Unit<? extends Quantity<?>> unit) {
 145         assertThrows(UnsupportedOperationException.class, () -> {
 146             jdbcBaseDAO.objectAsState(item, unit, value);
 147         }, item.getName());
 148     }
 149
 150     private static Stream<Arguments> provideTestCasesForObjectAsStateInvalid() {
 151         return Stream.of( //
 152                 Arguments.of(new SwitchItem("Integer_SwitchItem"), 1, null),
 153                 Arguments.of(new RollershutterItem("String_RollershutterItem"), "39", null),
 154                 Arguments.of(new ColorItem("Integer_ColorItem"), 5, null), //
 155                 Arguments.of(new NumberItem("Timestamp_NumberItem"), java.sql.Timestamp.valueOf("2023-08-15 21:02:06"),
 156                         null),
 157                 Arguments.of(new NumberItem("Timestamp_NumberItem_Unit"),
 158                         java.sql.Timestamp.valueOf("2023-08-15 21:02:06"), SIUnits.CELSIUS),
 159                 Arguments.of(new LocationItem("Timestamp_LocationItem"),
 160                         java.sql.Timestamp.valueOf("2023-08-15 21:02:06"), null));
 161     }
 162
 163     @Test
 164     void testHistItemFilterQueryProviderReturnsSelectQueryWithoutWhereClauseDescendingOrder() {
 165         String sql = jdbcBaseDAO.histItemFilterQueryProvider(filter, 0, DB_TABLE_NAME, "TEST", UTC_ZONE_ID);
 166         assertThat(sql, is("SELECT time, value FROM " + DB_TABLE_NAME + " ORDER BY time DESC"));
 167     }
 168
 169     @Test
 170     void testHistItemFilterQueryProviderReturnsSelectQueryWithoutWhereClauseAscendingOrder() {
 171         filter.setOrdering(Ordering.ASCENDING);
 172
 173         String sql = jdbcBaseDAO.histItemFilterQueryProvider(filter, 0, DB_TABLE_NAME, "TEST", UTC_ZONE_ID);
 174         assertThat(sql, is("SELECT time, value FROM " + DB_TABLE_NAME + " ORDER BY time ASC"));
 175     }
 176
 177     @Test
 178     void testHistItemFilterQueryProviderWithStartAndEndDateReturnsDeleteQueryWithWhereClauseDescendingOrder() {
 179         filter.setBeginDate(parseDateTimeString("2022-01-10T15:01:44"));
 180         filter.setEndDate(parseDateTimeString("2022-01-15T15:01:44"));
 181
 182         String sql = jdbcBaseDAO.histItemFilterQueryProvider(filter, 0, DB_TABLE_NAME, "TEST", UTC_ZONE_ID);
 183         assertThat(sql, is("SELECT time, value FROM " + DB_TABLE_NAME + " WHERE TIME>='" //
 184                 + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getBeginDate())) + "'" //
 185                 + " AND TIME<='" + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getEndDate()))
 186                 + "' ORDER BY time DESC"));
 187     }
 188
 189     @Test
 190     void testHistItemFilterQueryProviderReturnsSelectQueryWithoutWhereClauseDescendingOrderAndLimit() {
 191         filter.setPageSize(1);
 192
 193         String sql = jdbcBaseDAO.histItemFilterQueryProvider(filter, 0, DB_TABLE_NAME, "TEST", UTC_ZONE_ID);
 194         assertThat(sql, is("SELECT time, value FROM " + DB_TABLE_NAME + " ORDER BY time DESC LIMIT 0,1"));
 195     }
 196
 197     @Test
 198     void testHistItemFilterDeleteProviderReturnsDeleteQueryWithoutWhereClause() {
 199         String sql = jdbcBaseDAO.histItemFilterDeleteProvider(filter, DB_TABLE_NAME, UTC_ZONE_ID);
 200         assertThat(sql, is("TRUNCATE TABLE " + DB_TABLE_NAME));
 201     }
 202
 203     @Test
 204     void testHistItemFilterDeleteProviderWithStartAndEndDateReturnsDeleteQueryWithWhereClause() {
 205         filter.setBeginDate(parseDateTimeString("2022-01-10T15:01:44"));
 206         filter.setEndDate(parseDateTimeString("2022-01-15T15:01:44"));
 207
 208         String sql = jdbcBaseDAO.histItemFilterDeleteProvider(filter, DB_TABLE_NAME, UTC_ZONE_ID);
 209         assertThat(sql, is("DELETE FROM " + DB_TABLE_NAME + " WHERE TIME>='" //
 210                 + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getBeginDate())) + "'" //
 211                 + " AND TIME<='" + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getEndDate()))
 212                 + "'"));
 213     }
 214
 215     @Test
 216     void testResolveTimeFilterWithNoDatesReturnsEmptyString() {
 217         String sql = jdbcBaseDAO.resolveTimeFilter(filter, UTC_ZONE_ID);
 218         assertThat(sql, is(""));
 219     }
 220
 221     @Test
 222     void testResolveTimeFilterWithStartDateOnlyReturnsWhereClause() {
 223         filter.setBeginDate(parseDateTimeString("2022-01-10T15:01:44"));
 224
 225         String sql = jdbcBaseDAO.resolveTimeFilter(filter, UTC_ZONE_ID);
 226         assertThat(sql, is(" WHERE TIME>='"
 227                 + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getBeginDate())) + "'"));
 228     }
 229
 230     @Test
 231     void testResolveTimeFilterWithEndDateOnlyReturnsWhereClause() {
 232         filter.setEndDate(parseDateTimeString("2022-01-15T15:01:44"));
 233
 234         String sql = jdbcBaseDAO.resolveTimeFilter(filter, UTC_ZONE_ID);
 235         assertThat(sql, is(" WHERE TIME<='"
 236                 + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getEndDate())) + "'"));
 237     }
 238
 239     @Test
 240     void testResolveTimeFilterWithStartAndEndDateReturnsWhereClauseWithTwoConditions() {
 241         filter.setBeginDate(parseDateTimeString("2022-01-10T15:01:44"));
 242         filter.setEndDate(parseDateTimeString("2022-01-15T15:01:44"));
 243
 244         String sql = jdbcBaseDAO.resolveTimeFilter(filter, UTC_ZONE_ID);
 245         assertThat(sql,
 246                 is(" WHERE TIME>='" + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getBeginDate()))
 247                         + "'" //
 248                         + " AND TIME<='"
 249                         + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getEndDate())) + "'"));
 250     }
 251
 252     private ZonedDateTime parseDateTimeString(String dts) {
 253         return ZonedDateTime.of(LocalDateTime.parse(dts, DATE_PARSER), UTC_ZONE_ID);
 254     }
 255 }