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
  20 import java.time.Instant;
  21 import java.time.LocalDateTime;
  22 import java.time.ZoneId;
  23 import java.time.ZonedDateTime;
  24 import java.time.format.DateTimeFormatter;
  25 import java.util.Objects;
  26
  27 import org.eclipse.jdt.annotation.NonNullByDefault;
  28 import org.junit.jupiter.api.BeforeEach;
  29 import org.junit.jupiter.api.Test;
  30 import org.openhab.core.library.items.CallItem;
  31 import org.openhab.core.library.items.ColorItem;
  32 import org.openhab.core.library.items.ContactItem;
  33 import org.openhab.core.library.items.DateTimeItem;
  34 import org.openhab.core.library.items.DimmerItem;
  35 import org.openhab.core.library.items.ImageItem;
  36 import org.openhab.core.library.items.LocationItem;
  37 import org.openhab.core.library.items.NumberItem;
  38 import org.openhab.core.library.items.PlayerItem;
  39 import org.openhab.core.library.items.RollershutterItem;
  40 import org.openhab.core.library.items.StringItem;
  41 import org.openhab.core.library.items.SwitchItem;
  42 import org.openhab.core.library.types.DateTimeType;
  43 import org.openhab.core.library.types.DecimalType;
  44 import org.openhab.core.library.types.HSBType;
  45 import org.openhab.core.library.types.OnOffType;
  46 import org.openhab.core.library.types.OpenClosedType;
  47 import org.openhab.core.library.types.PercentType;
  48 import org.openhab.core.library.types.PlayPauseType;
  49 import org.openhab.core.library.types.PointType;
  50 import org.openhab.core.library.types.QuantityType;
  51 import org.openhab.core.library.types.RawType;
  52 import org.openhab.core.library.types.RewindFastforwardType;
  53 import org.openhab.core.library.types.StringListType;
  54 import org.openhab.core.library.types.StringType;
  55 import org.openhab.core.library.unit.SIUnits;
  56 import org.openhab.core.persistence.FilterCriteria;
  57 import org.openhab.core.persistence.FilterCriteria.Ordering;
  58 import org.openhab.core.types.State;
  59
  60 /**
  61  * Tests the {@link JdbcBaseDAO}.
  62  *
  63  * @author Christoph Weitkamp - Initial contribution
  64  */
  65 @NonNullByDefault
  66 public class JdbcBaseDAOTest {
  67
  68     private static final String DATE_PATTERN = "yyyy-MM-dd'T'HH:mm:ss";
  69     private static final DateTimeFormatter DATE_PARSER = DateTimeFormatter.ofPattern(DATE_PATTERN);
  70     private static final ZoneId UTC_ZONE_ID = ZoneId.of("UTC");
  71     private static final String DB_TABLE_NAME = "testitem";
  72
  73     private final JdbcBaseDAO jdbcBaseDAO = new JdbcBaseDAO();
  74     private @NonNullByDefault({}) FilterCriteria filter;
  75
  76     @BeforeEach
  77     public void setup() {
  78         filter = new FilterCriteria();
  79     }
  80
  81     @Test
  82     public void testObjectAsStateReturnsValidState() {
  83         State decimalType = jdbcBaseDAO.objectAsState(new NumberItem("testNumberItem"), null, 7.3);
  84         assertInstanceOf(DecimalType.class, decimalType);
  85         assertEquals(DecimalType.valueOf("7.3"), decimalType);
  86         State quantityType = jdbcBaseDAO.objectAsState(new NumberItem("testNumberItem"), SIUnits.CELSIUS, 7.3);
  87         assertInstanceOf(QuantityType.class, quantityType);
  88         assertEquals(QuantityType.valueOf("7.3 °C"), quantityType);
  89
  90         State hsbType = jdbcBaseDAO.objectAsState(new ColorItem("testColorItem"), null, "184,100,52");
  91         assertInstanceOf(HSBType.class, hsbType);
  92         assertEquals(HSBType.valueOf("184,100,52"), hsbType);
  93
  94         State percentType = jdbcBaseDAO.objectAsState(new DimmerItem("testDimmerItem"), null, 52);
  95         assertInstanceOf(PercentType.class, percentType);
  96         assertEquals(PercentType.valueOf("52"), percentType);
  97
  98         percentType = jdbcBaseDAO.objectAsState(new RollershutterItem("testRollershutterItem"), null, 39);
  99         assertInstanceOf(PercentType.class, percentType);
 100         assertEquals(PercentType.valueOf("39"), percentType);
 101
 102         State openClosedType = jdbcBaseDAO.objectAsState(new ContactItem("testContactItem"), null, "OPEN");
 103         assertInstanceOf(OpenClosedType.class, openClosedType);
 104         assertThat(openClosedType, is(OpenClosedType.OPEN));
 105
 106         State playPauseType = jdbcBaseDAO.objectAsState(new PlayerItem("testPlayerItem"), null, "PLAY");
 107         assertInstanceOf(PlayPauseType.class, playPauseType);
 108         assertThat(playPauseType, is(PlayPauseType.PLAY));
 109         State rewindFastforwardType = jdbcBaseDAO.objectAsState(new PlayerItem("testPlayerItem"), null, "REWIND");
 110         assertInstanceOf(RewindFastforwardType.class, rewindFastforwardType);
 111         assertThat(rewindFastforwardType, is(RewindFastforwardType.REWIND));
 112
 113         State onOffType = jdbcBaseDAO.objectAsState(new SwitchItem("testSwitchItem"), null, "ON");
 114         assertInstanceOf(OnOffType.class, onOffType);
 115         assertThat(onOffType, is(OnOffType.ON));
 116
 117         State stringListType = jdbcBaseDAO.objectAsState(new CallItem("testCallItem"), null, "0699222222,0179999998");
 118         assertInstanceOf(StringListType.class, stringListType);
 119         assertEquals(StringListType.valueOf("0699222222,0179999998"), stringListType);
 120
 121         State expectedRawType = new RawType(new byte[0], "application/octet-stream");
 122         State rawType = jdbcBaseDAO.objectAsState(new ImageItem("testImageItem"), null, expectedRawType.toFullString());
 123         assertInstanceOf(RawType.class, rawType);
 124         assertThat(rawType, is(expectedRawType));
 125
 126         State pointType = jdbcBaseDAO.objectAsState(new LocationItem("testLocationItem"), null, "1,2,3");
 127         assertInstanceOf(PointType.class, pointType);
 128         assertEquals(PointType.valueOf("1,2,3"), pointType);
 129
 130         State stringType = jdbcBaseDAO.objectAsState(new StringItem("testStringItem"), null, "String");
 131         assertInstanceOf(StringType.class, stringType);
 132         assertEquals(StringType.valueOf("String"), stringType);
 133     }
 134
 135     @Test
 136     public void objectAsStateReturnsValiDateTimeTypeForTimestamp() {
 137         State dateTimeType = jdbcBaseDAO.objectAsState(new DateTimeItem("testDateTimeItem"), null,
 138                 java.sql.Timestamp.valueOf("2021-02-01 23:30:02.049"));
 139         assertInstanceOf(DateTimeType.class, dateTimeType);
 140         assertEquals(DateTimeType.valueOf("2021-02-01T23:30:02.049"), dateTimeType);
 141     }
 142
 143     @Test
 144     public void objectAsStateReturnsValidDateTimeTypeForLocalDateTime() {
 145         State dateTimeType = jdbcBaseDAO.objectAsState(new DateTimeItem("testDateTimeItem"), null,
 146                 LocalDateTime.parse("2021-02-01T23:30:02.049"));
 147         assertInstanceOf(DateTimeType.class, dateTimeType);
 148         assertEquals(DateTimeType.valueOf("2021-02-01T23:30:02.049"), dateTimeType);
 149     }
 150
 151     @Test
 152     public void objectAsStateReturnsValidDateTimeTypeForLong() {
 153         State dateTimeType = jdbcBaseDAO.objectAsState(new DateTimeItem("testDateTimeItem"), null,
 154                 Long.valueOf("1612222202049"));
 155         assertInstanceOf(DateTimeType.class, dateTimeType);
 156         assertEquals(
 157                 new DateTimeType(
 158                         ZonedDateTime.ofInstant(Instant.parse("2021-02-01T23:30:02.049Z"), ZoneId.systemDefault())),
 159                 dateTimeType);
 160     }
 161
 162     @Test
 163     public void objectAsStateReturnsValidDateTimeTypeForSqlDate() {
 164         State dateTimeType = jdbcBaseDAO.objectAsState(new DateTimeItem("testDateTimeItem"), null,
 165                 java.sql.Date.valueOf("2021-02-01"));
 166         assertInstanceOf(DateTimeType.class, dateTimeType);
 167         assertEquals(DateTimeType.valueOf("2021-02-01T00:00:00.000"), dateTimeType);
 168     }
 169
 170     @Test
 171     public void objectAsStateReturnsValidDateTimeTypeForInstant() {
 172         State dateTimeType = jdbcBaseDAO.objectAsState(new DateTimeItem("testDateTimeItem"), null,
 173                 Instant.parse("2021-02-01T23:30:02.049Z"));
 174         assertInstanceOf(DateTimeType.class, dateTimeType);
 175         assertEquals(
 176                 new DateTimeType(
 177                         ZonedDateTime.ofInstant(Instant.parse("2021-02-01T23:30:02.049Z"), ZoneId.systemDefault())),
 178                 dateTimeType);
 179     }
 180
 181     @Test
 182     public void objectAsStateReturnsValidDateTimeTypeForString() {
 183         State dateTimeType = jdbcBaseDAO.objectAsState(new DateTimeItem("testDateTimeItem"), null,
 184                 "2021-02-01 23:30:02.049");
 185         assertInstanceOf(DateTimeType.class, dateTimeType);
 186         assertEquals(DateTimeType.valueOf("2021-02-01T23:30:02.049"), dateTimeType);
 187     }
 188
 189     @Test
 190     public void testHistItemFilterQueryProviderReturnsSelectQueryWithoutWhereClauseDescendingOrder() {
 191         String sql = jdbcBaseDAO.histItemFilterQueryProvider(filter, 0, DB_TABLE_NAME, "TEST", UTC_ZONE_ID);
 192         assertThat(sql, is("SELECT time, value FROM " + DB_TABLE_NAME + " ORDER BY time DESC"));
 193     }
 194
 195     @Test
 196     public void testHistItemFilterQueryProviderReturnsSelectQueryWithoutWhereClauseAscendingOrder() {
 197         filter.setOrdering(Ordering.ASCENDING);
 198
 199         String sql = jdbcBaseDAO.histItemFilterQueryProvider(filter, 0, DB_TABLE_NAME, "TEST", UTC_ZONE_ID);
 200         assertThat(sql, is("SELECT time, value FROM " + DB_TABLE_NAME + " ORDER BY time ASC"));
 201     }
 202
 203     @Test
 204     public void testHistItemFilterQueryProviderWithStartAndEndDateReturnsDeleteQueryWithWhereClauseDescendingOrder() {
 205         filter.setBeginDate(parseDateTimeString("2022-01-10T15:01:44"));
 206         filter.setEndDate(parseDateTimeString("2022-01-15T15:01:44"));
 207
 208         String sql = jdbcBaseDAO.histItemFilterQueryProvider(filter, 0, DB_TABLE_NAME, "TEST", UTC_ZONE_ID);
 209         assertThat(sql, is("SELECT time, value 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                 + "' ORDER BY time DESC"));
 213     }
 214
 215     @Test
 216     public void testHistItemFilterQueryProviderReturnsSelectQueryWithoutWhereClauseDescendingOrderAndLimit() {
 217         filter.setPageSize(1);
 218
 219         String sql = jdbcBaseDAO.histItemFilterQueryProvider(filter, 0, DB_TABLE_NAME, "TEST", UTC_ZONE_ID);
 220         assertThat(sql, is("SELECT time, value FROM " + DB_TABLE_NAME + " ORDER BY time DESC LIMIT 0,1"));
 221     }
 222
 223     @Test
 224     public void testHistItemFilterDeleteProviderReturnsDeleteQueryWithoutWhereClause() {
 225         String sql = jdbcBaseDAO.histItemFilterDeleteProvider(filter, DB_TABLE_NAME, UTC_ZONE_ID);
 226         assertThat(sql, is("TRUNCATE TABLE " + DB_TABLE_NAME));
 227     }
 228
 229     @Test
 230     public void testHistItemFilterDeleteProviderWithStartAndEndDateReturnsDeleteQueryWithWhereClause() {
 231         filter.setBeginDate(parseDateTimeString("2022-01-10T15:01:44"));
 232         filter.setEndDate(parseDateTimeString("2022-01-15T15:01:44"));
 233
 234         String sql = jdbcBaseDAO.histItemFilterDeleteProvider(filter, DB_TABLE_NAME, UTC_ZONE_ID);
 235         assertThat(sql, is("DELETE FROM " + DB_TABLE_NAME + " WHERE TIME>='" //
 236                 + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getBeginDate())) + "'" //
 237                 + " AND TIME<='" + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getEndDate()))
 238                 + "'"));
 239     }
 240
 241     @Test
 242     public void testResolveTimeFilterWithNoDatesReturnsEmptyString() {
 243         String sql = jdbcBaseDAO.resolveTimeFilter(filter, UTC_ZONE_ID);
 244         assertThat(sql, is(""));
 245     }
 246
 247     @Test
 248     public void testResolveTimeFilterWithStartDateOnlyReturnsWhereClause() {
 249         filter.setBeginDate(parseDateTimeString("2022-01-10T15:01:44"));
 250
 251         String sql = jdbcBaseDAO.resolveTimeFilter(filter, UTC_ZONE_ID);
 252         assertThat(sql, is(" WHERE TIME>='"
 253                 + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getBeginDate())) + "'"));
 254     }
 255
 256     @Test
 257     public void testResolveTimeFilterWithEndDateOnlyReturnsWhereClause() {
 258         filter.setEndDate(parseDateTimeString("2022-01-15T15:01:44"));
 259
 260         String sql = jdbcBaseDAO.resolveTimeFilter(filter, UTC_ZONE_ID);
 261         assertThat(sql, is(" WHERE TIME<='"
 262                 + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getEndDate())) + "'"));
 263     }
 264
 265     @Test
 266     public void testResolveTimeFilterWithStartAndEndDateReturnsWhereClauseWithTwoConditions() {
 267         filter.setBeginDate(parseDateTimeString("2022-01-10T15:01:44"));
 268         filter.setEndDate(parseDateTimeString("2022-01-15T15:01:44"));
 269
 270         String sql = jdbcBaseDAO.resolveTimeFilter(filter, UTC_ZONE_ID);
 271         assertThat(sql,
 272                 is(" WHERE TIME>='" + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getBeginDate()))
 273                         + "'" //
 274                         + " AND TIME<='"
 275                         + JdbcBaseDAO.JDBC_DATE_FORMAT.format(Objects.requireNonNull(filter.getEndDate())) + "'"));
 276     }
 277
 278     private ZonedDateTime parseDateTimeString(String dts) {
 279         return ZonedDateTime.of(LocalDateTime.parse(dts, DATE_PARSER), UTC_ZONE_ID);
 280     }
 281 }