mirror of
https://github.com/absmach/magistrala.git
synced 2026-08-07 07:14:46 +00:00
Arvindh
16ba29cf4a
Property Based Tests / api-test (push) Has been cancelled
Continuous Delivery / lint-and-build (push) Has been cancelled
Deploy GitHub Pages / swagger-ui (push) Has been cancelled
CI Pipeline / Lint Proto (push) Has been cancelled
CI Pipeline / Detect Changes (push) Has been cancelled
Continuous Delivery / Build and Push Docker Images (push) Has been cancelled
CI Pipeline / lint-and-build (push) Has been cancelled
CI Pipeline / Test ${{ matrix.module }} (push) Has been cancelled
CI Pipeline / Upload Coverage (push) Has been cancelled
Signed-off-by: Arvindh <arvindh91@gmail.com> Signed-off-by: dusan <borovcanindusan1@gmail.com> Signed-off-by: Rodney Osodo <socials@rodneyosodo.com> Co-authored-by: Dušan Borovčanin <dusan.borovcanin@absmach.eu> Co-authored-by: Rodney Osodo <socials@rodneyosodo.com> Co-authored-by: dusan <borovcanindusan1@gmail.com>
101 lines
2.7 KiB
Go
101 lines
2.7 KiB
Go
// Copyright (c) Abstract Machines
|
|
// SPDX-License-Identifier: Apache-2.0
|
|
|
|
package postgres
|
|
|
|
import (
|
|
_ "github.com/jackc/pgx/v5/stdlib" // required for SQL access
|
|
migrate "github.com/rubenv/sql-migrate"
|
|
)
|
|
|
|
func Migration() (*migrate.MemoryMigrationSource, error) {
|
|
reportsMigration := &migrate.MemoryMigrationSource{
|
|
Migrations: []*migrate.Migration{
|
|
{
|
|
Id: "reports_01",
|
|
Up: []string{
|
|
`CREATE TABLE IF NOT EXISTS report_config (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
name VARCHAR(1024),
|
|
description TEXT,
|
|
domain_id VARCHAR(36) NOT NULL,
|
|
status SMALLINT NOT NULL DEFAULT 0 CHECK (status >= 0),
|
|
created_at TIMESTAMP,
|
|
created_by VARCHAR(254),
|
|
updated_at TIMESTAMP,
|
|
updated_by VARCHAR(254),
|
|
due TIMESTAMPTZ,
|
|
recurring SMALLINT,
|
|
recurring_period SMALLINT,
|
|
start_datetime TIMESTAMP,
|
|
config JSONB,
|
|
email JSONB,
|
|
metrics JSONB
|
|
);`,
|
|
},
|
|
Down: []string{
|
|
`DROP TABLE IF EXISTS report_config;`,
|
|
},
|
|
},
|
|
{
|
|
Id: "reports_02",
|
|
Up: []string{
|
|
`ALTER TABLE report_config ADD COLUMN report_template TEXT;`,
|
|
},
|
|
Down: []string{
|
|
`ALTER TABLE report_config DROP COLUMN report_template;`,
|
|
},
|
|
},
|
|
{
|
|
Id: "reports_03",
|
|
Up: []string{
|
|
// Canonicalize legacy report metric subtopics from dot/NATS wildcards
|
|
// to slash/MQTT wildcards.
|
|
`UPDATE report_config AS rc
|
|
SET metrics = COALESCE((
|
|
SELECT jsonb_agg(
|
|
CASE
|
|
WHEN metric.elem ? 'subtopic'
|
|
AND jsonb_typeof(metric.elem->'subtopic') = 'string'
|
|
THEN jsonb_set(
|
|
metric.elem,
|
|
'{subtopic}',
|
|
to_jsonb(REPLACE(REPLACE(REPLACE(metric.elem->>'subtopic', '>', '#'), '*', '+'), '.', '/')),
|
|
false
|
|
)
|
|
ELSE metric.elem
|
|
END
|
|
ORDER BY metric.ord
|
|
)
|
|
FROM jsonb_array_elements(rc.metrics) WITH ORDINALITY AS metric(elem, ord)
|
|
), '[]'::jsonb)
|
|
WHERE jsonb_typeof(rc.metrics) = 'array'`,
|
|
},
|
|
Down: []string{
|
|
`UPDATE report_config AS rc
|
|
SET metrics = COALESCE((
|
|
SELECT jsonb_agg(
|
|
CASE
|
|
WHEN metric.elem ? 'subtopic'
|
|
AND jsonb_typeof(metric.elem->'subtopic') = 'string'
|
|
THEN jsonb_set(
|
|
metric.elem,
|
|
'{subtopic}',
|
|
to_jsonb(REPLACE(REPLACE(REPLACE(metric.elem->>'subtopic', '#', '>'), '+', '*'), '/', '.')),
|
|
false
|
|
)
|
|
ELSE metric.elem
|
|
END
|
|
ORDER BY metric.ord
|
|
)
|
|
FROM jsonb_array_elements(rc.metrics) WITH ORDINALITY AS metric(elem, ord)
|
|
), '[]'::jsonb)
|
|
WHERE jsonb_typeof(rc.metrics) = 'array'`,
|
|
},
|
|
},
|
|
},
|
|
}
|
|
|
|
return reportsMigration, nil
|
|
}
|