mirror of
https://github.com/thingsboard/thingsboard-gateway.git
synced 2026-10-29 04:16:43 +08:00
144 lines
4.9 KiB
PL/PgSQL
144 lines
4.9 KiB
PL/PgSQL
-- # Copyright 2022. ThingsBoard
|
|
-- #
|
|
-- # Licensed under the Apache License, Version 2.0 (the "License");
|
|
-- # you may not use this file except in compliance with the License.
|
|
-- # You may obtain a copy of the License at
|
|
-- #
|
|
-- # http://www.apache.org/licenses/LICENSE-2.0
|
|
-- #
|
|
-- # Unless required by applicable law or agreed to in writing, software
|
|
-- # distributed under the License is distributed on an "AS IS" BASIS,
|
|
-- # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
|
-- # See the License for the specific language governing permissions and
|
|
-- # limitations under the License.
|
|
|
|
CREATE TABLE IF NOT EXISTS public.devices (
|
|
id smallint,
|
|
name character varying(32)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.attributes (
|
|
device_id smallint,
|
|
ts bigint not null,
|
|
key character varying(32),
|
|
bool_v boolean,
|
|
str_v character varying(32),
|
|
long_v bigint,
|
|
dbl_v double precision
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.timeseries (
|
|
device_id smallint,
|
|
ts bigint not null,
|
|
bool_v boolean,
|
|
str_v character varying(32),
|
|
long_v bigint,
|
|
dbl_v double precision
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS public.rpc (
|
|
inc_value smallint not null,
|
|
str_value character varying(32) not null,
|
|
int_value smallint not null
|
|
);
|
|
|
|
CREATE OR REPLACE PROCEDURE public.increment_value()
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
UPDATE public.rpc SET inc_value = inc_value + 1;
|
|
END
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.decrement_value()
|
|
RETURNS smallint
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
value smallint;
|
|
BEGIN
|
|
EXECUTE 'SELECT inc_value FROM public.rpc LIMIT 1' INTO value;
|
|
value := value - 1;
|
|
UPDATE public.rpc SET inc_value = value;
|
|
RETURN value;
|
|
END
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.reset_values(string_value character varying, integer_value smallint)
|
|
RETURNS smallint
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
UPDATE public.rpc SET str_value = string_value, int_value = integer_value;
|
|
RETURN 1;
|
|
END
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.secret_function(string_value character varying)
|
|
RETURNS smallint
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
UPDATE public.rpc SET str_value = string_value, inc_value = inc_value + 1;
|
|
RETURN 1;
|
|
END
|
|
$$;
|
|
|
|
CREATE OR REPLACE PROCEDURE public.update_values(string_value character varying, integer_value smallint)
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
UPDATE public.rpc SET str_value = string_value, int_value = integer_value;
|
|
END
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.get_integer_value(column_name character varying, OUT return_value smallint)
|
|
RETURNS smallint
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
EXECUTE format('SELECT %s FROM public.rpc', column_name) into return_value;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.get_string_value(column_name character varying)
|
|
RETURNS character varying
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
return_value character varying;
|
|
BEGIN
|
|
EXECUTE format('SELECT %s FROM public.rpc', column_name) into return_value;
|
|
RETURN return_value;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.get_values()
|
|
RETURNS SETOF public.rpc
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
RETURN QUERY SELECT * FROM public.rpc LIMIT 1;
|
|
END;
|
|
$$;
|
|
|
|
INSERT INTO public.rpc VALUES (1, 'test', 25);
|
|
|
|
INSERT INTO public.devices (name) VALUES ('Sensor 1');
|
|
INSERT INTO public.devices (name) VALUES ('Sensor 2');
|
|
INSERT INTO public.devices (name) VALUES ('Sensor 3');
|
|
INSERT INTO public.devices (name) VALUES ('Sensor 4');
|
|
|
|
INSERT INTO public.timeseries (device_id, ts, bool_v, str_v, long_v, dbl_v) VALUES (1, 1589476731000, NULL, NULL, 0, NULL);
|
|
INSERT INTO public.timeseries (device_id, ts, bool_v, str_v, long_v, dbl_v) VALUES (1, 1589476732000, NULL, NULL, 0, NULL);
|
|
INSERT INTO public.timeseries (device_id, ts, bool_v, str_v, long_v, dbl_v) VALUES (1, 1589476733000, NULL, NULL, 5, NULL);
|
|
INSERT INTO public.timeseries (device_id, ts, bool_v, str_v, long_v, dbl_v) VALUES (1, 1589476734000, NULL, NULL, 5, NULL);
|
|
INSERT INTO public.timeseries (device_id, ts, bool_v, str_v, long_v, dbl_v) VALUES (1, 1589476735000, NULL, NULL, 9, NULL);
|
|
INSERT INTO public.timeseries (device_id, ts, bool_v, str_v, long_v, dbl_v) VALUES (1, 1589476736000, NULL, NULL, 9, NULL);
|
|
|
|
INSERT INTO public.attributes (device_id, ts, key, bool_v, str_v, long_v, dbl_v) VALUES (2, 1589476731000, 'serialNumber', NULL, '123456789', NULL, NULL);
|
|
INSERT INTO public.attributes (device_id, ts, key, bool_v, str_v, long_v, dbl_v) VALUES (2, 1589476732000, 'enableUpgrade', True, NULL, NULL, NULL);
|
|
INSERT INTO public.attributes (device_id, ts, key, bool_v, str_v, long_v, dbl_v) VALUES (2, 1589476733000, 'latitude', NULL, NULL, NULL, 51.62);
|
|
INSERT INTO public.attributes (device_id, ts, key, bool_v, str_v, long_v, dbl_v) VALUES (2, 1589476734000, 'longitude', NULL, NULL, NULL, 35.63);
|
|
INSERT INTO public.attributes (device_id, ts, key, bool_v, str_v, long_v, dbl_v) VALUES (2, 1589476735000, 'softwareVersion', NULL, NULL, 465, NULL);
|