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