Оптимизация запросов из базы данных для мобильного приложения

  • Основным механизмом этого процесса является trigger в PostgreSql.Создается функция которая обновляет информацию о том, в каком состоянии находиться таблица. Состояние – это переменная в которой храниться время с типом данных double precision.
INSERT INTO core.sd_table_change (c_table_name, n_change)
VALUES (_c_table_name, (SELECT EXTRACT(EPOCH FROM now())))
ON CONFLICT (c_table_name) DO UPDATE
SET c_table_name = _c_table_name,
n_change = (SELECT EXTRACT(EPOCH FROM now()));
  • Далее создается триггер, который изменяет “состояние таблицы”
CREATE OR REPLACE FUNCTION core.cft_change_version() RETURNS trigger
   LANGUAGE plpgsql
   AS $$
BEGIN
   IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      select core.sf_table_change_update(t.c_table_name_ref) from  (select TG_TABLE_NAME as c_table_name_ref UNION select  c_table_name_ref from core.sd_table_change_ref where c_table_name = TG_TABLE_NAME) as t; END IF; RETURN NEW;
END
$$;
ALTER FUNCTION core.cft_change_version() OWNER TO mobnius;
COMMENT ON FUNCTION core.cft_change_version() IS 'Триггер. Обновление справочной версии';
  • Для хранения информации о состоянии, создается 2 таблицы:
  1. Состояние таблиц – имя таблицы и её числовое состояние
  2. Таблицы зависимых состояний
CREATE TABLE core.sd_table_change (
c_table_name text NOT NULL,
n_change double precision NOT NULL
);
ALTER TABLE core.sd_table_change OWNER TO mobnius;
COMMENT ON TABLE core.sd_table_change IS 'Изменение состояния таблицы';
COMMENT ON COLUMN core.sd_table_change.c_table_name IS 'Имя таблицы';
COMMENT ON COLUMN core.sd_table_change.n_change IS 'Версия изменения';

ALTER TABLE core.sd_table_change
ADD CONSTRAINT sd_table_change_pkey PRIMARY KEY (c_table_name);
CREATE TABLE core.sd_table_change_ref (
id smallint DEFAULT nextval('core.sd_table_change_ref_id_seq'::regclass) NOT NULL,
c_table_name text NOT NULL,
c_table_name_ref text NOT NULL
);
ALTER TABLE core.sd_table_change_ref OWNER TO mobnius;
COMMENT ON TABLE core.sd_table_change_ref IS 'Зависимость таблиц состояний';
COMMENT ON COLUMN core.sd_table_change_ref.c_table_name IS 'Таблица';
COMMENT ON COLUMN core.sd_table_change_ref.c_table_name_ref IS 'Зависимая таблица';

ALTER TABLE core.sd_table_change_ref
ADD CONSTRAINT sd_table_change_ref_pkey PRIMARY KEY (id);

ALTER TABLE core.sd_table_change_ref
ADD CONSTRAINT sd_table_change_ref_c_table_name_fkey FOREIGN KEY (c_table_name) REFERENCES core.sd_table_change(c_table_name) NOT VALID;

ALTER TABLE core.sd_table_change_ref
ADD CONSTRAINT "sd_table_change_ref_cTable_name_ref_fkey" FOREIGN KEY (c_table_name_ref) REFERENCES core.sd_table_change(c_table_name) NOT VALID;

Автоматическое документирование базы данных

Для документирование базы данных применяется открытое решение Autodoc.

Инструкция по установке и настройке на сайте appcode.pw

Основной скрипт для запуска генерации документации.

cd autodoc
postgresql_autodoc -d cic-dev-db -h 192.168.17.111 -u postgres --password=<pw> -t html -s "core|dbo" -f /var/www/html/cic-dev-db

Демонстрационный вариант документации можно посмотреть на сайте http://cic.it-serv.ru/cic-dev-db.html

Обновление

Актуализирована информация по базе данных, обновлена статусная схема маршрутов.

Статусная схема храниться в формате Visio 2019 и увидеть ее можно тут. В формате PNG ниже.

Обновленная Схема базы данных тут.

Читать далее «Обновление»

Интеграция

Информация актуальна для пилотной версии МРСК ЦиП на 08.10.2020

В пилотной версии МРСК ЦиП информация может быть импортирована в следующие таблицы:

  • dbo.ed_input_meter_readings – текущие контрольные показания
  • dbo.ed_registr_pts – точки учета
  • dbo.ed_device_types – типы прибора учета

Примечание: информацию по таблицам можно посмотреть тут

Точка учета

Информация о точках учета должна храниться в таблице dbo.ed_registr_pts. Здесь может храниться любая информация, но при этом должны быть заполнены следующие поля:

  • c_address: text – полный адрес
  • n_longitude: numeric – долгота
  • n_latitude: numeric – широта
  • f_division: integer – филиал/отделение
  • f_subdivision: integer – участок

Показания

Информация о показаниях храниться в двух таблица:

  • dbo.ed_input_meter_readings – входящие показания. Первоначальные показания в нашей системе. Если показания и дата неизвестны, то можно указать null. При этом информация о тарифной зоне и разрядности должна быть указана обязательно.
  • dbo.ed_output_meter_readings – исходящие показания, результат обхода.

Первичные показания должны быть загружены в систему в обе таблицы, так как генерация показания для новых обходов производится на основе предыдущих данных.

-- пример наличия первичных показаний
INSERT INTO dbo.ed_input_meter_readings(f_point, n_value_prev, d_date_prev, n_digit, f_time_zone, f_registr_pts, n_tariff)
VALUES ('b17c5973-615c-49c6-976f-7a491a41a160', null, null, 5.0, 2, '028d65a4-3621-408b-af0a-230242f41702', 1); -- создано показание с тарифом 1 и показанием "Сутки"

INSERT INTO dbo.ed_output_meter_readings(fn_meter_reading, fn_point, fn_route, fn_user_point, n_value, d_date)
VALUES ('394fb071-7d9f-4c13-ada6-3c45b0f1643b', 'b17c5973-615c-49c6-976f-7a491a41a160', 'c68b1a77-0a13-4c05-bc3e-c2504868f0ac', '547c39bd-d171-4cae-bf64-c30fae16210a', null, '2020-05-01');

Алгоритм обработки показаний тут.

Работа с WebSocket

Мобильные сервисы для передачи мгновенных сообщений использует протокол websocket.

Передача уведомлений осуществляется при помощи “пакетной” передачи данных – механизм аналогичный синхронизации.

NML175.........0{"stringSize":29,"binarySize":0,"attachments":[],"bufferBlockToLength":207,"bufferBlockFromLength":0,"transaction":false,"dataInfo":"mail","version":"v2","id":"1599022001522"}[{"name":"to0","length":207}]{"action":"cd_notifications","method":"Add","data":[[{"fn_user_to":null,"fn_user_from":null,"c_message":"Hello","c_title":"Уведомление","d_date":"2020-09-02T04:46:39.785Z"}]],"tid":0,"type":"rpc"}

Выше показан пример пакета для отправки уведомлений.

Создание слушателя в NodeJS

Переходим в каталог modules/socket и находим там файл main.js В этом файле выполняется регистрация нового слушателя.

Файл для регистрации слушателя
Читать далее «Работа с WebSocket»

Версия БД 1.32.0.760

Добавлена новая схема dbo, в нее включены следующие таблицы и функции:

  • es_time_zones – тарифная зона для ПУ
  • ed_registr_pts – список точек учета
  • ed_input_meter_readings – входная информация о показаниях ПУ
  • ed_output_meter_readings – выходная информация о показаниях ПУ

Удалены следующие таблицы:

  • cd_addresses
  • cd_contacts

Информация с данных таблиц была перенесена в ed_registr_pts

Переименованы или удалены следующие колонки:

  • колонки fn_adresses и fn_contact удалены из таблицы cd_points
  • колонка f_registr_pts добавлена в таблицу cd_points
  • колонка fn_route переименована в f_route – таб. cd_points
  • колонка fn_type переименована в f_type – cd_routes
  • колонка fn_route переименована в f_route – cd_routes
  • колонка fn_user переименована в f_user – cd_routes

Версия БД 0.19.0.3831

cd_feedbacks

  • колонка f_question_filedata переименована в fn_question_file и ссылается на cd_files;
  • колонка f_answer_filedata переименована в fn_answer_file и ссылается на cd_files;

pd_users

  • колонка c_icon_filebase64 переименована в fn_file и ссылается на cd_files;

Произошли изменения с работай файлов:

cd_files

  • id: uuid;
  • c_name: text;
  • c_mime: text;
  • c_extension: text;
  • n_size: integer;
  • d_date: timestamp with time zone;
  • f_filedata: integer;
  • j_data: json;
  • dx_created: timestamp with time zone;

cd_attachments

  • id: uuid;
  • n_longitude: numeric(20, 15);
  • n_latitude: numeric(20, 15);
  • gx_geodata: geography;
  • c_notice: text;
  • d_date: timestamp with time zone;
  • fn_type: integer;
  • fn_result: uuid;
  • fn_file: uuid;
  • b_preview: bytea;
  • n_size_preview: integer;
  • j_data: json;
  • dx_created: timestamp with time zone;

Таблица cs_file_types переименована в cs_attachment_types.