fix: golangci-lint and gosec warnings

.drone.yml

@@ -13,21 +13,17 @@ steps:
     - pull_request
     - tag
 
-# - name: golangci-lint
+- name: trigger
-#   image: docker.io/golangci/golangci-lint:v1.40.1-alpine
+  image: plugins/downstream
-#   commands:
+  settings:
-#   - golangci-lint run --concurrency $(nproc --ignore=1)
+    server: https://drone.cryptic.systems
-#   when:
+    token:
-#     event:
+      from_secret: drone_token
-#     - push
+    params:
-#     - pull_request
+    - VERSION=${DRONE_COMMIT_HASH}
-
+    fork: true
-# - name: gosec
+    repositories:
-#   image: docker.io/securego/gosec:v2.8.0
+    - flucky/PKGBUILD@master
-#   when:
-#     event:
-#     - push
-#     - pull_request
 
 # steps:
 # - name: test-unit

.gitignore

@@ -1,8 +1,7 @@
 # absolute files
+bin
 .env
 
-flucky*
-
 # directories
 .vscode/
 

Makefile

@@ -7,9 +7,42 @@ PREFIX?=/usr/local
 
 # BINARIES
 # ==============================================================================
-all: ${EXECUTABLE}
+EXECUTABLE_TARGETS:= \
+	bin/linux/amd64/${EXECUTABLE} \
+	bin/linux/arm/5/${EXECUTABLE} \
+	bin/linux/arm/7/${EXECUTABLE} \
+	bin/tmp/${EXECUTABLE}
 
-${EXECUTABLE}:
+${EXECUTABLE}: bin/tmp/${EXECUTABLE}
+
+bin/linux/amd64/${EXECUTABLE}:
+	CGO_ENABLED=1 \
+	GOOS=linux \
+	GOARCH=amd64 \
+	GOPROXY=$(shell go env GOPROXY) \
+	GOPRIVATE=$(shell go env GOPRIVATE) \
+		go build -ldflags "-X main.version=${VERSION:v%=%}" -o ${@}
+
+bin/linux/arm/5/${EXECUTABLE}:
+	CGO_ENABLED=1 \
+	GOOS=linux \
+	GOARCH=arm \
+	GOARM=5 \
+	GOPROXY=$(shell go env GOPROXY) \
+	GOPRIVATE=$(shell go env GOPRIVATE) \
+		go build -ldflags "-X main.version=${VERSION:v%=%}" -o ${@}
+
+bin/linux/arm/7/${EXECUTABLE}:
+	CC=arm-linux-gnueabihf-gcc \
+	CGO_ENABLED=1 \
+	GOOS=linux \
+	GOARCH=arm \
+	GOARM=7 \
+	GOPROXY=$(shell go env GOPROXY) \
+	GOPRIVATE=$(shell go env GOPRIVATE) \
+		go build -ldflags "-X main.version=${VERSION:v%=%}" -o ${@}
+
+bin/tmp/${EXECUTABLE}:
 	CGO_ENABLED=1 \
 	GOPROXY=$(shell go env GOPROXY) \
 	GOPRIVATE=$(shell go env GOPRIVATE) \
@@ -17,48 +50,48 @@ ${EXECUTABLE}:
 
 # COMPLETIONS
 # ==============================================================================
-${EXECUTABLE}.sh: ${EXECUTABLE}
+bin/tmp/${EXECUTABLE}.sh: bin/tmp/${EXECUTABLE}
-	${EXECUTABLE} completion bash > ${@}
+	bin/tmp/${EXECUTABLE} completion bash > ${@}
 
-${EXECUTABLE}.fish: ${EXECUTABLE}
+bin/tmp/${EXECUTABLE}.fish: bin/tmp/${EXECUTABLE}
-	${EXECUTABLE} completion fish > ${@}
+	bin/tmp/${EXECUTABLE} completion fish > ${@}
 
-${EXECUTABLE}.zsh: ${EXECUTABLE}
+bin/tmp/${EXECUTABLE}.zsh: bin/tmp/${EXECUTABLE}
-	${EXECUTABLE} completion zsh > ${@}
+	bin/tmp/${EXECUTABLE} completion zsh > ${@}
 
 # UN/INSTALL
 # ==============================================================================
 PHONY+=install
-install: ${EXECUTABLE} ${EXECUTABLE}.sh ${EXECUTABLE}.fish ${EXECUTABLE}.zsh
+install: bin/tmp/${EXECUTABLE} bin/tmp/${EXECUTABLE}.sh bin/tmp/${EXECUTABLE}.fish bin/tmp/${EXECUTABLE}.zsh
 	install --directory ${DESTDIR}${PREFIX}/bin
-	install --mode 755 ${EXECUTABLE} ${DESTDIR}${PREFIX}/bin/${EXECUTABLE}
+	install --mode 755 bin/tmp/${EXECUTABLE} ${DESTDIR}${PREFIX}/bin/${EXECUTABLE}
+
+	install --directory ${DESTDIR}/etc/bash_completion.d
+	install --mode 755 bin/tmp/${EXECUTABLE}.sh ${DESTDIR}/etc/bash_completion.d/${EXECUTABLE}.sh
+
+	install --directory ${DESTDIR}/usr/share/fish/vendor_completions.d
+	install --mode 644 bin/tmp/${EXECUTABLE}.fish ${DESTDIR}/usr/share/fish/vendor_completions.d/${EXECUTABLE}.fish
 
 	install --directory ${DESTDIR}/usr/lib/systemd/system
 	install --mode 644 systemd/${EXECUTABLE}.service ${DESTDIR}/usr/lib/systemd/system/${EXECUTABLE}.service
 
-	install --directory ${DESTDIR}${PREFIX}/share/bash-completion/completions/
+	install --directory ${DESTDIR}/usr/share/licenses/${EXECUTABLE}
-	install --mode 644 ${EXECUTABLE}.sh ${DESTDIR}${PREFIX}/share/bash-completion/completions/${EXECUTABLE}.sh
+	install --mode 644 LICENSE ${DESTDIR}/usr/share/licenses/${EXECUTABLE}/LICENSE
-
-	install --directory ${DESTDIR}${PREFIX}/fish/vendor_completions.d
-	install --mode 644 ${EXECUTABLE}.fish ${DESTDIR}${PREFIX}/fish/vendor_completions.d/${EXECUTABLE}.fish
-
-	install --directory ${DESTDIR}${PREFIX}/licenses/${EXECUTABLE}
-	install --mode 644 LICENSE ${DESTDIR}${PREFIX}/licenses/${EXECUTABLE}/LICENSE
 
 PHONY+=uninstall
 uninstall:
 	-rm --recursive --force \
 		${DESTDIR}${PREFIX}/bin/${EXECUTABLE} \
+		${DESTDIR}/etc/bash_completion.d/${EXECUTABLE}.sh \
 		${DESTDIR}/usr/lib/systemd/system/${EXECUTABLE}.service \
-		${DESTDIR}${PREFIX}/share/bash-completion/completions/${EXECUTABLE}.sh \
+		${DESTDIR}/usr/share/fish/vendor_completions.d/${EXECUTABLE}.fish \
-		${DESTDIR}${PREFIX}/fish/vendor_completions.d/${EXECUTABLE}.fish \
+		${DESTDIR}/usr/share/licenses/${EXECUTABLE}/LICENSE
-		${DESTDIR}${PREFIX}/licenses/${EXECUTABLE}/LICENSE
 
 # CLEAN
 # ==============================================================================
 PHONY+=clean
 clean:
-	rm --force --recursive ${EXECUTABLE}* || true
+	rm --force --recursive bin/ || true
 
 # TEST
 # ==============================================================================

go.mod

@@ -11,9 +11,9 @@ require (
 	github.com/d2r2/go-logger v0.0.0-20181221090742-9998a510495e
 	github.com/golang-migrate/migrate/v4 v4.14.2-0.20201125065321-a53e6fc42574
 	github.com/johejo/golang-migrate-extra v0.0.0-20210217013041-51a992e50d16
-	github.com/lib/pq v1.10.2
+	github.com/lib/pq v1.9.0
 	github.com/mattn/go-sqlite3 v2.0.3+incompatible
 	github.com/satori/go.uuid v1.2.0
-	github.com/spf13/cobra v1.1.3
+	github.com/spf13/cobra v1.1.1
 	github.com/stretchr/testify v1.7.0
 )

go.sum

@@ -297,8 +297,6 @@ github.com/lib/pq v1.2.0/go.mod h1:5WUZQaWbwv1U+lTReE5YruASi9Al49XbQIvNi/34Woo=
 github.com/lib/pq v1.8.0/go.mod h1:AlVN5x4E4T544tWzH6hKfbfQvm3HdbOxrmggDNAPY9o=
 github.com/lib/pq v1.9.0 h1:L8nSXQQzAYByakOFMTwpjRoHsMJklur4Gi59b6VivR8=
 github.com/lib/pq v1.9.0/go.mod h1:AlVN5x4E4T544tWzH6hKfbfQvm3HdbOxrmggDNAPY9o=
-github.com/lib/pq v1.10.2 h1:AqzbZs4ZoCBp+GtejcpCpcxM3zlSMx29dXbUSeVtJb8=
-github.com/lib/pq v1.10.2/go.mod h1:AlVN5x4E4T544tWzH6hKfbfQvm3HdbOxrmggDNAPY9o=
 github.com/magiconair/properties v1.8.1/go.mod h1:PppfXfuXeibc/6YijjN8zIbojt8czPbwD3XqdrwzmxQ=
 github.com/markbates/pkger v0.15.1/go.mod h1:0JoVlrol20BSywW79rN3kdFFsE5xYM+rSCQDXbLhiuI=
 github.com/mattn/go-colorable v0.0.9/go.mod h1:9vuHe8Xs5qXnSaW/c/ABM9alt+Vo+STaOChaDxuIBZU=
@@ -390,8 +388,6 @@ github.com/spf13/afero v1.1.2/go.mod h1:j4pytiNVoe2o6bmDsKpLACNPDBIoEAkihy7loJ1B
 github.com/spf13/cast v1.3.0/go.mod h1:Qx5cxh0v+4UWYiBimWS+eyWzqEqokIECu5etghLkUJE=
 github.com/spf13/cobra v1.1.1 h1:KfztREH0tPxJJ+geloSLaAkaPkr4ki2Er5quFV1TDo4=
 github.com/spf13/cobra v1.1.1/go.mod h1:WnodtKOvamDL/PwE2M4iKs8aMDBZ5Q5klgD3qfVJQMI=
-github.com/spf13/cobra v1.1.3 h1:xghbfqPkxzxP3C/f3n5DdpAbdKLj4ZE4BWQI362l53M=
-github.com/spf13/cobra v1.1.3/go.mod h1:pGADOWyqRD/YMrPZigI/zbliZ2wVD/23d+is3pSWzOo=
 github.com/spf13/jwalterweatherman v1.0.0/go.mod h1:cQK4TGJAtQXfYWX+Ddv3mKDzgVb68N+wFjFa4jdeBTo=
 github.com/spf13/pflag v1.0.3/go.mod h1:DYY7MBk1bdzusC3SYhjObp+wFpr4gzcvqqNjLnInEg4=
 github.com/spf13/pflag v1.0.5 h1:iy+VFUOCP1a+8yFto/drg2CJ5u0yRoB7fZw3DKv/JXA=
@@ -399,7 +395,6 @@ github.com/spf13/pflag v1.0.5/go.mod h1:McXfInJRrz4CZXVZOBLb0bTZqETkiAhM9Iw0y3An
 github.com/spf13/viper v1.7.0/go.mod h1:8WkrPz2fc9jxqZNCJI/76HCieCp4Q8HaLFoCha5qpdg=
 github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
 github.com/stretchr/objx v0.1.1/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
-github.com/stretchr/objx v0.2.0 h1:Hbg2NidpLE8veEBkEZTL3CvlkUIVzuU9jDplZO54c48=
 github.com/stretchr/objx v0.2.0/go.mod h1:qt09Ya8vawLte6SNmTgCsAVtYtaKzEcn8ATUoHMkEqE=
 github.com/stretchr/testify v1.2.0/go.mod h1:a8OnRcib4nhh0OaRAV+Yts87kKdq0PP7pXfy6kDkUVs=
 github.com/stretchr/testify v1.2.2/go.mod h1:a8OnRcib4nhh0OaRAV+Yts87kKdq0PP7pXfy6kDkUVs=
@@ -750,7 +745,6 @@ gopkg.in/yaml.v2 v2.2.2/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
 gopkg.in/yaml.v2 v2.2.4/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
 gopkg.in/yaml.v2 v2.2.7/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
 gopkg.in/yaml.v2 v2.2.8/go.mod h1:hI93XBmqTisBFMUTm0b8Fm+jr3Dg1NNxqwp+5A1VGuI=
-gopkg.in/yaml.v2 v2.4.0/go.mod h1:RDklbk79AGWmwhnvt/jBztapEOGDOx6ZbXqjP6csGnQ=
 gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c h1:dUUwHk2QECo/6vqA44rthZ8ie2QXMNeKRTHCNY2nXvo=
 gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
 gotest.tools v2.2.0+incompatible h1:VsBPFP1AI068pPrMxtb/S8Zkgf9xEmTLJjfM+P5UIEo=

pkg/repository/postgres/ddl/8_table_measured_values.down.sql

@@ -1,95 +0,0 @@
--- Remove constraints to table measured_values
-ALTER TABLE humidities
-DROP CONSTRAINT fk_humidites_measured_value_id;
-
-ALTER TABLE pressures
-DROP CONSTRAINT fk_pressures_measured_value_id;
-
-ALTER TABLE temperatures
-DROP CONSTRAINT fk_temperatures_measured_value_id;
-
--- Rename measured_value_id to sensor_id
-ALTER TABLE humidities
-RENAME COLUMN measured_value_id TO sensor_id;
-
-ALTER TABLE pressures
-RENAME COLUMN measured_value_id TO sensor_id;
-
-ALTER TABLE temperatures
-RENAME COLUMN measured_value_id TO sensor_id;
-
--- Add new column measures_value_id with data from sensor_id
-ALTER TABLE humidities
-ADD COLUMN measured_value_id CHAR(36);
-
-UPDATE humidities
-SET measured_value_id=sensor_id;
-
-ALTER TABLE pressures
-ADD COLUMN measured_value_id CHAR(36);
-
-UPDATE pressures
-SET measured_value_id=sensor_id;
-
-ALTER TABLE temperatures
-ADD COLUMN measured_value_id CHAR(36);
-
-UPDATE temperatures
-SET measured_value_id=sensor_id;
-
--- Update sensor_id's
-UPDATE humidities h
-SET sensor_id = (
-  SELECT m.sensor_id
-  FROM measured_values m
-  WHERE m.id = h.measured_value_id
-);
-
-UPDATE pressures p
-SET sensor_id = (
-  SELECT m.sensor_id
-  FROM measured_values m
-  WHERE m.id = p.measured_value_id
-);
-
-UPDATE temperatures t
-SET sensor_id = (
-  SELECT m.sensor_id
-  FROM measured_values m
-  WHERE m.id = t.measured_value_id
-);
-
--- Delete sensor_id columns
-ALTER TABLE humidities
-DROP COLUMN measured_value_id;
-
-ALTER TABLE pressures
-DROP COLUMN measured_value_id;
-
-ALTER TABLE temperatures
-DROP COLUMN measured_value_id;
-
--- Add foreign keys
-ALTER TABLE humidities
-ADD CONSTRAINT fk_humidites_sensor_id
-FOREIGN KEY (sensor_id)
-REFERENCES sensors(sensor_id)
-ON DELETE CASCADE
-ON UPDATE CASCADE;
-
-ALTER TABLE pressures
-ADD CONSTRAINT fk_pressures_sensor_id
-FOREIGN KEY (sensor_id)
-REFERENCES sensors(sensor_id)
-ON DELETE CASCADE
-ON UPDATE CASCADE;
-
-ALTER TABLE temperatures
-ADD CONSTRAINT fk_temperatures_sensor_id
-FOREIGN KEY (sensor_id)
-REFERENCES sensors(sensor_id)
-ON DELETE CASCADE
-ON UPDATE CASCADE;
-
--- drop measured values table
-DROP TABLE measured_values CASCADE;

pkg/repository/postgres/ddl/8_table_measured_values.up.sql

@@ -1,130 +0,0 @@
--- Create measures_values table.
--- This table represent a collection of different measured values, because it
--- can be possible, that a sensor returns multiple measured values. For example
--- the BME280 semsor returns humidity, pressure and temperature values.
-CREATE TABLE measured_values (
-  id                      CHAR(36)                                                NOT NULL,
-  sensor_id               CHAR(36)                                                NOT NULL,
-  creation_date           TIMESTAMP WITH TIME ZONE    DEFAULT CURRENT_TIMESTAMP   NOT NULL,
-  update_date             TIMESTAMP WITH TIME ZONE,
-  CONSTRAINT pk_measured_value PRIMARY KEY(id),
-  CONSTRAINT pk_measured_value_sensor_id FOREIGN KEY(sensor_id) REFERENCES sensors(sensor_id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-
--- Remove constraints to table sensor
-ALTER TABLE humidities
-DROP CONSTRAINT fk_humidites_sensor_id;
-
-ALTER TABLE pressures
-DROP CONSTRAINT fk_pressures_sensor_id;
-
-ALTER TABLE temperatures
-DROP CONSTRAINT fk_temperatures_sensor_id;
-
--- Rename sensor_id to measured_value_id
-ALTER TABLE humidities
-RENAME COLUMN sensor_id TO measured_value_id;
-
-ALTER TABLE pressures
-RENAME COLUMN sensor_id TO measured_value_id;
-
-ALTER TABLE temperatures
-RENAME COLUMN sensor_id TO measured_value_id;
-
--- Add new column sensor_id with data from measures_value_id
-ALTER TABLE humidities
-ADD COLUMN sensor_id CHAR(36);
-
-UPDATE humidities
-SET sensor_id=measured_value_id;
-
-ALTER TABLE pressures
-ADD COLUMN sensor_id CHAR(36);
-
-UPDATE pressures
-SET sensor_id=measured_value_id;
-
-ALTER TABLE temperatures
-ADD COLUMN sensor_id CHAR(36);
-
-UPDATE temperatures
-SET sensor_id=measured_value_id;
-
-
-DO $$
-DECLARE
-  f record;
-BEGIN
-  FOR f IN (
-    SELECT
-      md5(random()::text || clock_timestamp()::text)::uuid AS measured_value_id,
-      h.id AS humidity_id,
-      p.id AS pressure_id,
-      t.id AS temperature_id,
-      CASE WHEN h.sensor_id IS NOT NULL THEN h.sensor_id
-           WHEN p.sensor_id IS NOT NULL THEN p.sensor_id
-           WHEN t.sensor_id IS NOT NULL THEN t.sensor_id
-           ELSE NULL
-      END sensor_id
-    FROM pressures p
-      FULL OUTER JOIN temperatures t ON (t.date = p.date AND t.sensor_id = p.sensor_id)
-      FULL OUTER JOIN humidities h ON (h.date = p.date AND h.sensor_id = p.sensor_id)
-  ) LOOP
-
-    -- MEASURED_VALUE
-    INSERT INTO measured_values(id, sensor_id, creation_date, update_date)
-    VALUES (f.measured_value_id, f.sensor_id, NOW(), NULL)
-    ON CONFLICT (
-      id
-    )
-    DO NOTHING;
-
-    -- HUMIDITY
-    UPDATE humidities
-    SET measured_value_id=f.measured_value_id
-    WHERE id=f.humidity_id;
-
-    -- PRESSURE
-    UPDATE pressures
-    SET measured_value_id=f.measured_value_id
-    WHERE id=f.pressure_id;
-
-    -- TEMPERATURES
-    UPDATE temperatures
-    SET measured_value_id=f.measured_value_id
-    WHERE id=f.temperature_id;
-  END LOOP;
-END;
-$$;
-
--- Delete sensor_id columns
-ALTER TABLE humidities
-DROP COLUMN sensor_id;
-
-ALTER TABLE pressures
-DROP COLUMN sensor_id;
-
-ALTER TABLE temperatures
-DROP COLUMN sensor_id;
-
--- Add foreign keys
-ALTER TABLE humidities
-ADD CONSTRAINT fk_humidites_measured_value_id
-FOREIGN KEY (measured_value_id)
-REFERENCES measured_values(id)
-ON DELETE CASCADE
-ON UPDATE CASCADE;
-
-ALTER TABLE pressures
-ADD CONSTRAINT fk_pressures_measured_value_id
-FOREIGN KEY (measured_value_id)
-REFERENCES measured_values(id)
-ON DELETE CASCADE
-ON UPDATE CASCADE;
-
-ALTER TABLE temperatures
-ADD CONSTRAINT fk_temperatures_measured_value_id
-FOREIGN KEY (measured_value_id)
-REFERENCES measured_values(id)
-ON DELETE CASCADE
-ON UPDATE CASCADE;

pkg/repository/sqlite3/ddl/6_table_measured_values.down.sql

@@ -1,90 +0,0 @@
--- All tables with the original data rows must be renamed, because it is not
--- possible to remove existing foreign keys. The tables will be recreated with
--- a new column measured_value_id which reference on the primary key of the
--- table measured values.
---
--- More about why it is not possible to delete a foreign key is described here:
--- https://stackoverflow.com/a/1884893/7652996
-ALTER TABLE humidities
-RENAME TO humidities_backup;
-
-CREATE TABLE humidities (
-  id                      CHAR(36)                                                  NOT NULL,
-  value                   NUMERIC(10,3)                                             NOT NULL,
-  date                    TIMESTAMP                                                 NOT NULL,
-  sensor_id               CHAR(36)                                                  NOT NULL,
-  creation_date           TIMESTAMP       DEFAULT (datetime('now', 'localtime'))    NOT NULL,
-  update_date             TIMESTAMP,
-  CONSTRAINT pk_humidities PRIMARY KEY(id),
-  CONSTRAINT fk_humidities_sensor_id FOREIGN KEY(sensor_id) REFERENCES sensors(sensor_id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-
-ALTER TABLE pressures
-RENAME TO pressures_backup;
-
-CREATE TABLE pressures (
-  id                      CHAR(36)                                                  NOT NULL,
-  value                   NUMERIC(10,3)                                             NOT NULL,
-  date                    TIMESTAMP                                                 NOT NULL,
-  sensor_id               CHAR(36)                                                  NOT NULL,
-  creation_date           TIMESTAMP       DEFAULT (datetime('now', 'localtime'))    NOT NULL,
-  update_date             TIMESTAMP,
-  CONSTRAINT pk_pressures PRIMARY KEY(id),
-  CONSTRAINT fk_pressures_sensor_id FOREIGN KEY(sensor_id) REFERENCES sensors(sensor_id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-
-ALTER TABLE temperatures
-RENAME TO temperatures_backup;
-
-CREATE TABLE temperatures (
-  id                      CHAR(36)                                                  NOT NULL,
-  value                   NUMERIC(10,3)                                             NOT NULL,
-  date                    TIMESTAMP                                                 NOT NULL,
-  sensor_id               CHAR(36)                                                  NOT NULL,
-  creation_date           TIMESTAMP       DEFAULT (datetime('now', 'localtime'))    NOT NULL,
-  update_date             TIMESTAMP,
-  CONSTRAINT pk_temperatures PRIMARY KEY(id),
-  CONSTRAINT fk_temperatures_id FOREIGN KEY(sensor_id) REFERENCES sensors(sensor_id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-
--- Filling tables humidities, pressures and temperatures with the data from the
--- backup table.
-INSERT INTO humidities
-SELECT
-  h.id,
-  h.value,
-  h.date,
-  m.sensor_id,
-  h.creation_date,
-  h.update_date
-FROM humidities_backup h
-  INNER JOIN measured_values m ON (m.id = h.measured_value_id);
-
-INSERT INTO pressures
-SELECT
-  p.id,
-  p.value,
-  p.date,
-  m.sensor_id,
-  p.creation_date,
-  p.update_date
-FROM pressures_backup p
-  INNER JOIN measured_values m ON (m.id = p.measured_value_id);
-
-INSERT INTO temperatures
-SELECT
-  t.id,
-  t.value,
-  t.date,
-  m.sensor_id,
-  t.creation_date,
-  t.update_date
-FROM temperatures_backup t
-  INNER JOIN measured_values m ON (m.id = t.measured_value_id);
-
--- The remaining tables, which were only necessary for the migration,
--- are now removed.
-DROP TABLE humidities_backup;
-DROP TABLE pressures_backup;
-DROP TABLE temperatures_backup;
-DROP TABLE measured_values;

pkg/repository/sqlite3/ddl/6_table_measured_values.up.sql

@@ -1,189 +0,0 @@
--- Create measures_values table.
--- This table represent a collection of different measured values, because it
--- can be possible, that a sensor returns multiple measured values. For example
--- the BME280 semsor returns humidity, pressure and temperature values.
-CREATE TABLE measured_values (
-  id                      CHAR(36)                                                NOT NULL,
-  sensor_id               CHAR(36)                                                NOT NULL,
-  creation_date           TIMESTAMP WITH TIME ZONE    DEFAULT CURRENT_TIMESTAMP   NOT NULL,
-  update_date             TIMESTAMP WITH TIME ZONE,
-  CONSTRAINT pk_measured_value PRIMARY KEY(id),
-  CONSTRAINT pk_measured_value_sensor_id FOREIGN KEY(sensor_id) REFERENCES sensors(sensor_id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-
--- All tables with the original data rows must be renamed, because it is not
--- possible to remove existing foreign keys. The tables will be recreated with
--- a new column measured_value_id which reference on the primary key of the
--- table measured values.
---
--- More about why it is not possible to delete a foreign key is described here:
--- https://stackoverflow.com/a/1884893/7652996
-ALTER TABLE humidities
-RENAME TO humidities_backup;
-
-CREATE TABLE humidities (
-  id                      CHAR(36)                                                  NOT NULL,
-  value                   NUMERIC(10,3)                                             NOT NULL,
-  date                    TIMESTAMP                                                 NOT NULL,
-  measured_value_id       CHAR(36)                                                  NOT NULL,
-  creation_date           TIMESTAMP       DEFAULT (datetime('now', 'localtime'))    NOT NULL,
-  update_date             TIMESTAMP,
-  CONSTRAINT pk_humidities PRIMARY KEY(id),
-  CONSTRAINT fk_humidities_measured_value_id FOREIGN KEY(measured_value_id) REFERENCES measured_values(id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-
-ALTER TABLE pressures
-RENAME TO pressures_backup;
-
-CREATE TABLE pressures (
-  id                      CHAR(36)                                                  NOT NULL,
-  value                   NUMERIC(10,3)                                             NOT NULL,
-  date                    TIMESTAMP                                                 NOT NULL,
-  measured_value_id       CHAR(36)                                                  NOT NULL,
-  creation_date           TIMESTAMP       DEFAULT (datetime('now', 'localtime'))    NOT NULL,
-  update_date             TIMESTAMP,
-  CONSTRAINT pk_pressures PRIMARY KEY(id),
-  CONSTRAINT fk_pressures_measured_value_id FOREIGN KEY(measured_value_id) REFERENCES measured_values(id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-
-ALTER TABLE temperatures
-RENAME TO temperatures_backup;
-
-CREATE TABLE temperatures (
-  id                      CHAR(36)                                                  NOT NULL,
-  value                   NUMERIC(10,3)                                             NOT NULL,
-  date                    TIMESTAMP                                                 NOT NULL,
-  measured_value_id       CHAR(36)                                                  NOT NULL,
-  creation_date           TIMESTAMP       DEFAULT (datetime('now', 'localtime'))    NOT NULL,
-  update_date             TIMESTAMP,
-  CONSTRAINT pk_temperatures PRIMARY KEY(id),
-  CONSTRAINT fk_temperatures_measured_value_id FOREIGN KEY(measured_value_id) REFERENCES measured_values(id) ON DELETE CASCADE ON UPDATE CASCADE
-);
-
--- The following two views are necessary to create a FULL OUTER JOIN via the
--- tables humidities, pressures and temperatures, as this is not supported in
--- SQLite. More about this here:
---
--- https://stackoverflow.com/questions/12759087/full-outer-join-in-sqlite-on-4-tables
-CREATE VIEW humidities_pressures AS
-SELECT
-  h.id AS humidity_id,
-  h.value AS humidity_value,
-  h.date AS humidity_date,
-  h.sensor_id AS humidity_sensor_id,
-  h.creation_date AS humidity_creation_date,
-  h.update_date AS humidity_update_date,
-  p.id AS pressure_id,
-  p.value AS pressure_value,
-  p.date AS pressure_date,
-  p.sensor_id AS pressure_sensor_id,
-  p.creation_date AS pressure_creation_date,
-  p.update_date AS pressure_update_date
-FROM humidities_backup h
-  LEFT JOIN pressures_backup p ON (h.date = p.date AND h.sensor_id = p.sensor_id)
-UNION ALL
-SELECT
-  h.id AS humidity_id,
-  h.value AS humidity_value,
-  h.date AS humidity_date,
-  h.sensor_id AS humidity_sensor_id,
-  h.creation_date AS humidity_creation_date,
-  h.update_date AS humidity_update_date,
-  p.id AS pressure_id,
-  p.value AS pressure_value,
-  p.date AS pressure_date,
-  p.sensor_id AS pressure_sensor_id,
-  p.creation_date AS pressure_creation_date,
-  p.update_date AS pressure_update_date
-FROM pressures_backup p
-  LEFT JOIN humidities_backup h ON (p.date = h.date AND p.sensor_id = h.sensor_id)
-WHERE h.date IS NULL
-AND h.sensor_id IS NULL;
-
-CREATE VIEW joined_values AS
-SELECT
-  hp.*,
-  t.id AS temperature_id,
-  t.value AS temperature_value,
-  t.date AS temperature_date,
-  t.sensor_id AS temperature_sensor_id,
-  t.creation_date AS temperature_creation_date,
-  t.update_date AS temperature_update_date
-FROM temperatures_backup t
-  LEFT JOIN humidities_pressures hp ON (t.date = hp.pressure_date AND t.sensor_id = hp.pressure_sensor_id)
-UNION ALL
-SELECT
-  hp.*,
-  t.id AS temperature_id,
-  t.value AS temperature_value,
-  t.date AS temperature_date,
-  t.sensor_id AS temperature_sensor_id,
-  t.creation_date AS temperature_creation_date,
-  t.update_date AS temperature_update_date
-FROM humidities_pressures hp
-  LEFT JOIN temperatures_backup t ON (t.date = hp.pressure_date  AND t.sensor_id = hp.pressure_sensor_id)
-WHERE t.date IS NULL
-AND t.sensor_id IS NULL;
-
--- The date and the SensorID are used as primary key between the tables
--- humidities, pressures and temperatures. Based on this information, a
--- MeasuredValueID is generated. The data records are then inserted into the
--- table.
-INSERT INTO measured_values (id, creation_date, sensor_id)
-SELECT
-  LOWER(HEX(RANDOMBLOB(4)) || '-' || HEX(RANDOMBLOB(2)) || '-' || '4' || SUBSTR(HEX(RANDOMBLOB(2)), 2) || '-' || SUBSTR('AB89', 1 + (ABS(random()) % 4) , 1) || SUBSTR(HEX(RANDOMBLOB(2)), 2) || '-' || HEX(RANDOMBLOB(6))) AS measured_value_id,
-  CASE WHEN humidity_date IS NOT NULL THEN humidity_date
-    WHEN pressure_date IS NOT NULL THEN pressure_date
-    WHEN temperature_date IS NOT NULL THEN temperature_date
-    ELSE NULL
-  END date,
-	CASE WHEN humidity_sensor_id IS NOT NULL THEN humidity_sensor_id
-      WHEN pressure_sensor_id IS NOT NULL THEN pressure_sensor_id
-      WHEN temperature_sensor_id IS NOT NULL THEN temperature_sensor_id
-      ELSE NULL
-  END sensor_id
-FROM joined_values;
-
--- In the following, the original data records from the backup are restored with
--- the difference that the MeasuredValueID is added as a foreign key based on
--- the date and the SensorID.
-INSERT INTO humidities (id, value, date, measured_value_id, creation_date, update_date)
-SELECT
-	jv.humidity_id,
-	jv.humidity_value,
-	jv.humidity_date,
-	(SELECT mv.id FROM measured_values mv WHERE mv.creation_date = jv.humidity_date AND mv.sensor_id = jv.humidity_sensor_id) AS measured_value,
-  jv.humidity_creation_date,
-  jv.humidity_update_date
-FROM joined_values jv
-WHERE humidity_id IS NOT NULL;
-
-INSERT INTO pressures (id, value, date, measured_value_id, creation_date, update_date)
-SELECT
-	jv.pressure_id,
-	jv.pressure_value,
-	jv.pressure_date,
-	(SELECT mv.id FROM measured_values mv WHERE mv.creation_date = jv.pressure_date AND mv.sensor_id = jv.pressure_sensor_id) AS measured_value,
-  jv.pressure_creation_date,
-  jv.pressure_update_date
-FROM joined_values jv
-WHERE pressure_id IS NOT NULL;
-
-INSERT INTO temperatures (id, value, date, measured_value_id, creation_date, update_date)
-SELECT
-	jv.temperature_id,
-	jv.temperature_value,
-	jv.temperature_date,
-	(SELECT mv.id FROM measured_values mv WHERE mv.creation_date = jv.temperature_date AND mv.sensor_id = jv.temperature_sensor_id) AS measured_value,
-  jv.temperature_creation_date,
-  jv.temperature_update_date
-FROM joined_values jv
-WHERE temperature_id IS NOT NULL;
-
--- The remaining views and tables, which were only necessary for the migration,
--- are now removed.
-DROP VIEW humidities_pressures;
-DROP VIEW joined_values;
-DROP TABLE humidities_backup;
-DROP TABLE pressures_backup;
-DROP TABLE temperatures_backup;