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

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