Вводные. Зачем мне это было нужно
Лично мне нужно было организовать мониторинг домашней солнечной электростанции.
Кратко о матчасти (хотя этот пост не про неё):
-
Инвертор МАП Энергия и 3 солнечных контроллера того же производителя.
-
Внутри инвертора установлен микрокомпьютер (производитель его называет "Малина"), который кое-что умеет в плане мониторинга, но не всё что мне нужно, и не очень удобно. Ценность микрокомпьютера в том, что он снимает данные с com-портов инвертора и контроллеров и публикует их насвоём http-сервере в виде Json. Данные веб-сервисов обновляются примерно каждую секунду. Также есть веб-сервисы для управления встроенными в контроллеры и инвертор реле
-
Парочка Ethernet-устройств SR-201 это такие платы с релюхами, используются для управления нагрузкой и кое-чем еще, управляются по протоколу tcp и udp.
-
Домашний сервер под управлением Centos-8, на нём установлен Oracle (разумеется Express Edition со всеми своими ограничениями, но для домашнего сервера достаточно)
-
В оракле крутятся 2 JOBa (на самом деле это persistent процессы, которые крутят бесконечный цикл и перезапускаются примерно раз в полчаса):
-
Раз в секуну снимает данные с вебсервисов "Малины", текущее состояние реле устройств SR-201 и пишет это всё в БД Oracle. С Малины снимает с помощью несложных функций на основе utl_http, с реюх - через utl_tcp. Собственно это и есть статистика, которую будем мониторить
-
Постоянно пересчитывает статистику за некоторый промежуток времени, и на основе полученных результатов, управляет нагрузкой и еще кое-чем через SR-201 и встроенные реле инвертора и контроллеров.
-
Вот это всё хозяйство мне нужно мониторить. Причем мониторить не события (событиями занимаетс Job2), а строить графики на основе накопленной статистической информации, визуализировать их на компе и мобилке. Сама "Малина" кое-что умеет, но во-первых не всё (про мои SR-201 она точно ничего не знает), во-вторых неудобный интерфейс - нельзя всё посмотреть на одном экране в удомном мне виде, а в третьих - в некоторых местах кривовато.
Вопросы: Почему Oracle а не Postgres например? Ну просто лень, хотелось сделать из того что умею... :-)
Выбор пал на Grafana https://grafana.com - довольно мощное средство визуализации статистики и прочей ерунды. Легко настраивается, удобно использовать. Работает с многими БД...
Собственно описание проекта
Данный пост ни разу не претендует на качественный пошаговый tutorial, я лишь хочу указать путь, по которому Вам будет легче идти.
Итак:
Устанавливаем grafana
$ sudo nano /etc/yum.repos.d/grafana.repo[grafana]name=grafanabaseurl=https://packages.grafana.com/oss/rpmrepo_gpgcheck=1enabled=1gpgcheck=1gpgkey=https://packages.grafana.com/gpg.keysslverify=1sslcacert=/etc/pki/tls/certs/ca-bundle.crt
dnf updatednf install grafanasystemctl daemon-reloadsystemctl enable --now grafana-serversystemctl status grafana-server
Selinux у меня отключен, файрвол тоже, так что в эти нюансы вдаваться не буду
Далее одна проблемка: Grafana конечно с Oracle работать умеет, но данная опция (плагин) предоставляется только в Enterprise версии, которая начинается от 24к$ и это в мои планы не входит. Устанавливаем плагин grafana-simple-json-datasource
grafana-cli plugins install grafana-simple-json-datasourcesystemctl restart grafana-server
То есть графана у нас в оракл ходить не будет. Она будет брать данные из вебсервиса, теперь дело за малым - вебсервис написать.
Вебсервис будем делать на apache + php
Для этого потребуется установить и настроить:
httpd, php и php-fpm (у меня php 7.2) установлен и сконфигрирован вместе с freepbx которая живёт на том же сервере :-)
Для php нужно подключить библиотеку oci8 - тут есть сложность в том, что для php 7.2 не получится поставить oci8 командой pecl.
В общем путь такой:
Подключаем репозиторий remi, и оттуда:
dnf install php-pecl-oci8
Подключаем oci8 к php
/etc/hp.d/20-oci8.ini
В принципе достаточно раскомментировать 1 строку
extension=oci8.so
Далее этот oci8 не очень хочет запускаться, тут помогут примерно такие строки в
/etc/php-fpm.d/www.conf
env[ORACLE_HOSTNAME] = myserver.localdomainenv[ORACLE_UNQNAME] = mydbenv[ORACLE_BASE] = /u01/app/oracleenv[ORACLE_HOME] = /u01/app/oracle/product/18.4.0/dbhome_1env[ORA_INVENTORY] = /u01/app/oraInventoryenv[ORACLE_SID] = mydbenv[LD_LIBRARY_PATH] = /u01/app/oracle/product/18.4.0/dbhome_1/lib:/lib:/usr/libenv[NLS_LANG] = AMERICAN_CIS.UTF8
Теперь при исполнении php-скрипта на вебсервере, oci8 прекрасно запускается
Выкладываем скрипт на вебсервер
/var/www/html/gr/gr.php
<?phpheader("Content-Type: application/json;");$conn = oci_pconnect('www', 'www$password', 'mydb', 'AL32UTF8');if (!$conn) { $e = oci_error(); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);}// Подготовка выражения$stid = oci_parse($conn, 'begin LGRAFANA.GetJson(:vPath, :vInp, :vOut); end;');if (!$stid) { $e = oci_error($conn); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);}// Создадим дескрипторы$vInp = oci_new_descriptor($conn, OCI_DTYPE_LOB);$vOut = oci_new_descriptor($conn, OCI_DTYPE_LOB);// Привяжем переменные$vPath = $_SERVER["PATH_INFO"];$postdata = file_get_contents("php://input");$vInp->writeTemporary($postdata, OCI_TEMP_BLOB);oci_bind_by_name($stid, ":vPath", $vPath);oci_bind_by_name($stid, ":vInp", $vInp, -1, OCI_B_BLOB);oci_bind_by_name($stid, ":vOut", $vOut, -1, OCI_B_BLOB);// Выполним логику запроса$r = oci_execute($stid);if (!$r) { $e = oci_error($stid); trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);}echo $vOut->load(); $vInp ->close();$vOut ->close();oci_free_statement($stid);oci_commit($conn);oci_close($conn);?>
Вебсервис готов.
В нашей БД есть пакет LGRAFANA, из которого наружу торчит только одна процедура
procedure GetJson(pPathInfo in varchar2, pInpPost in blob, pOutPost out blob);
она и будет обрабатывать Json - запросы от графаны. Эту процедуру целиком приведу чуть ниже, Вы понимаете что это лишь пример для моей электростанции, а в целом формат Json запросов-ответов описан тут
https://grafana.com/grafana/plugins/grafana-simple-json-datasource
Теперь настройка в самой графане:
Configuration - Data Sources - Add DataSource - Simple JSON
Дальше можно идти добавлять DashBoard и накидывать туда панели с нужными графиками
... Если у Вас уже есть реализация пакета LGRAFANA разумеется.
Да кстати про пакет. Он у меня написан не совсем на PL/SQL, но в целом Вы сможете это использовать для того чтобы понять, что надо написать в своём пакете. Это легко переводится на pl/sql.
Вкратце так:
-
Реализуем метод, который реагирует на pahinfo=/search и отдаёт массив имён метрик которые мы умеем считать
-
Реализуем метод /query который формирует массив данных по нужным метрикам
pragma include([DEBUG_TRIGGER]::[MACRO_LIB]);CPALL const varchar2(30) := 'Мощность нагр.';CPNET const varchar2(30) := 'Мощность сеть';CPACB const varchar2(30) := 'Мощность АКБ';CPI2C const varchar2(30) := 'Мощность I2C';CPADD const varchar2(30) := 'Доп. Нагрузка';CPMP1 const varchar2(30) := 'Мощность MPPT1';CPMP2 const varchar2(30) := 'Мощность MPPT2';CPMP3 const varchar2(30) := 'Мощность MPPT3';CEDAY const varchar2(30) := 'Выработка за день';CEMP1 const varchar2(30) := 'Выработка MPPT1';CEMP2 const varchar2(30) := 'Выработка MPPT2';CEMP3 const varchar2(30) := 'Выработка MPPT3';CETOB const varchar2(30) := 'На заряд батареи';CEFRB const varchar2(30) := 'Взято от батареи';CEFRN const varchar2(30) := 'Взято от сети';CUNET const varchar2(30) := 'Напряжение сети';CUOUT const varchar2(30) := 'Напряжение выход';CUACB const varchar2(30) := 'Напряжение АКБ';public function TsToUTs(v_Ts in timestamp) return number isv_Dt date;beginv_Dt := v_ts;return trunc((v_Dt - to_date('01.01.1970','DD.MM.YYYY')) -- Кол-во дней с 1 янв 1970 * (24 * 60 * 60)) -- Теперь это кол-во секунд * 1000 -- Теперь миллисекунд + to_number(to_char(v_ts,'FF3')); -- Добавили миллисекундыend;procedure get_query(pInp in out nocopy JSON_OBJECT_T, pOut in out nocopy JSON_ARRAY_T) istype rtflag is record ( fTp varchar2(30),fOb json_object_t,fAr json_array_t);type ttflag is table of rtflag index by string;tflag ttflag;vTmpOb json_object_t;vTmpAr json_array_t;vTmpId varchar2(30);vDBeg timestamp;vDEnd timestamp;vDDBeg date;vDDEnd date;num_tz number;curts number;function GetFlag(pFlagName in varchar2) return boolean isbeginif tflag.exists(pFlagName) thenreturn true;elsereturn false;end if;end;--function GetFlagType(pFlagName in varchar2) return varchar2 is--begin--if tflag.exists(pFlagName) then--return tflag(pFlagName).fTp;--else--pragma error('Нет значения ['||pFlagName||'] в мвссиве tflag');--end if;--end;procedure AddTrgData(pTrgName in varchar2, pStamp in number, pValue in number) isbeginvTmpAr := Json_Array_t;vTmpAr.append(pValue);vTmpAr.append(pStamp);tFlag(pTrgName).fAr.append(vTmpAr);end;begin&debug('pInp='||pInp.to_string())vTmpOb := pInp.get_Object('range');num_tz := to_number(::[GA_MAP_STAT].[LIB].GetSetting('MALINA_TIME_ZONE'));vDBeg := vTmpOb.get_Timestamp('from') + numtodsinterval(num_tz,'hour');vDEnd := vTmpOb.get_Timestamp('to') + numtodsinterval(num_tz,'hour');vDDBeg := to_date(to_char(vDBeg,'dd.mm.yyyy hh24:mi:ss'),'dd.mm.yyyy hh24:mi:ss');vDDEnd := to_date(to_char(vDEnd,'dd.mm.yyyy hh24:mi:ss'),'dd.mm.yyyy hh24:mi:ss');&debug('vDBeg='||to_char(vDBeg,'dd.mm.yyyy hh24:mi:ss:ff'))&debug('vDEnd='||to_char(vDEnd,'dd.mm.yyyy hh24:mi:ss:ff'))vTmpAr := pInp.get_Array('targets');for i in 0 .. vTmpAr.get_size - 1 loopvTmpOb := JSON_OBJECT_T(vTmpAr.get(i));vTmpId := vTmpOb.get_string('target');tflag(vTmpId).fTp := vTmpOb.get_string('type');tflag(vTmpId).fOb := Json_object_t;tflag(vTmpId).fAr := Json_array_t;tflag(vTmpId).fOb.put('target',vTmpId);end loop;-- Взять значения мощностей из статистики МАПif GetFlag(CPALL) or GetFlag(CPNET) or GetFlag(CPACB) or GetFlag(CPI2C) or GetFlag(CUNET) or GetFlag(CUOUT) or GetFlag(CUACB) thenfor (select x(x.[QTIME]:qtime, x.[F__PNET_CALC]:pnet -- Мощность сеть, - x.[F__PLOAD_CALC] + x.[F__PNET_CALC]:pall -- Мощность нагр., - x.[F__PLOAD_CALC]:pacb -- Мощность АКБ, x.[F__P_MPPT_AVG]:pi2c -- Мощность I2C, x.[F__UNET]:unet, x.[F__UOUTMED]:uout, x.[F__UACC]:uacb) in ::[GA_MAP_STAT] allwhere x.[QTIME] >= vDBeg and x.[QTIME] <= vDEndorder by x.[QTIME]) loopcurts := TsToUTs(x.qtime - numtodsinterval(num_tz,'hour'));vTmpId := tflag.first;while vTmpId is not null loopcase vTmpId of:CPALL: AddTrgData(vTmpId,curts,x.pall);:CPNET: AddTrgData(vTmpId,curts,x.pnet);:CPACB: AddTrgData(vTmpId,curts,x.pacb);:CPI2C: AddTrgData(vTmpId,curts,x.pi2c);:CUNET: AddTrgData(vTmpId,curts,x.unet);:CUOUT: AddTrgData(vTmpId,curts,x.uout);:CUACB: AddTrgData(vTmpId,curts,x.uacb);end;vTmpId := tflag.next(vTmpId);end loop;end loop;end if;-- Взять статистику панелейif GetFlag(CPMP1) or GetFlag(CPMP2) or GetFlag(CPMP3) thenfor (select x(x.[QTIME]:qtime,x.[F_UID]:fuid,x.[F_P_CURR]:fpower -- Мощность заряда) in ::[GA_MPPT_STAT] allwhere x.[QTIME] >= vDBeg and x.[QTIME] <= vDEndorder by x.[QTIME], x.[F_UID]) loopcurts := TsToUTs(x.qtime - numtodsinterval(num_tz,'hour'));case x.fuid of:1: if GetFlag(CPMP1) then AddTrgData(CPMP1,curts,x.fpower); end if;:2: if GetFlag(CPMP2) then AddTrgData(CPMP2,curts,x.fpower); end if;:3: if GetFlag(CPMP3) then AddTrgData(CPMP3,curts,x.fpower); end if;end;end loop;end if;-- Взять значения мощностей из статистики допнагрузкиif GetFlag(CPADD) thendeclaretqend timestamp;paend number;beginfor (select x( x.[QTIME]:qtime, x.[FPOWER]:padd -- Доп. Нагрузка) in ::[GA_LOAD_H] allwhere x.[QTIME] >= (select x(nvl(max(x.[QTIME]),to_timestamp('01.01.1970','dd.mm.yyyy')))in ::[GA_LOAD_H] allwhere x.[QTIME] < vDBeg)and x.[QTIME] < vDEndorder by x.[QTIME]) loopcurts := TsToUTs(x.qtime - numtodsinterval(num_tz,'hour'));tqend := x.qtime;paend := x.padd;AddTrgData(CPADD,curts,x.padd);end loop;curts := TsToUTs(vDEnd - numtodsinterval(num_tz,'hour'));AddTrgData(CPADD,curts,paend);end;end if;-- Взять значения выработки по датамif GetFlag(CEDAY) or GetFlag(CEMP1) or GetFlag(CEMP2) or GetFlag(CEMP3) or GetFlag(CEFRN) thendeclarevDEBeg date;vDEEnd date;vDECur date;curEn number;prven number;vTSCur timestamp;curEnToBat number;curEnFromBat number;procedure GetCeMp(vCeMp in varchar2, vMpUID in number) isbeginvDECur := vDEBeg;while vDECur <= vDEEnd loopvTSCur := to_timestamp(to_char(vDECur,'dd.mm.yyyy'),'dd.mm.yyyy');select x(nvl(max(x.[F_PWR_KW]),0)*1000) in ::[GA_MPPT_STAT] allwhere x.[qtime] >= vTSCurand x.[qtime] < (vTSCur + numtodsinterval(1,'day'))and x.[F_TIMESTAMP] >= vDECurand x.[F_TIMESTAMP] < (vDECur+1)and x.[F_UID] = vMpUIDinto curEn;curts := TsToUTs(vTsCur - numtodsinterval(num_tz,'hour'));AddTrgData(vCeMp,curts,curen);vDECur := vDECur + 1;end loop;end;beginvDEBeg := trunc(vDDBeg);vDEEnd := trunc(vDDEnd);if GetFlag(CEDAY) or GetFlag(CETOB) or GetFlag(CEFRB) thenvDECur := vDEBeg;while vDECur <= vDEEnd loopvTSCur := to_timestamp(to_char(vDECur,'dd.mm.yyyy'),'dd.mm.yyyy');select x( nvl(max(x.[S1].[F_MPPT_DAY_E]),0),nvl(max(x.[S1].[F_ESUM_TO_BAT]),0),nvl(max(x.[S1].[F_ESUM_FROM_BAT]),0)) in ::[GA_BAT_STAT] allwhere x.[qtime] >= vTSCurand x.[qtime] < (vTSCur + numtodsinterval(1,'day'))and x.[S1].[F_TIMESTAMP] >= vDECurand x.[S1].[F_TIMESTAMP] < (vDECur+1)into curEn,curEnToBat,curEnFromBat;curts := TsToUTs(vTsCur - numtodsinterval(num_tz,'hour'));if GetFlag(CEDAY) then AddTrgData(CEDAY,curts,curen); end if;if GetFlag(CETOB) then AddTrgData(CETOB,curts,curenToBat); end if;if GetFlag(CEFRB) then AddTrgData(CEFRB,curts,curenFromBat); end if;vDECur := vDECur + 1;end loop;end if;if GetFlag(CEMP1) thenGetCeMp(CEMP1,1);end if;if GetFlag(CEMP2) thenGetCeMp(CEMP2,2);end if;if GetFlag(CEMP3) thenGetCeMp(CEMP3,3);end if;-- Посчитать сколько взято от сетиif GetFlag(CEFRN) thenvDECur := vDEBeg-1;prven := null;while vDECur <= vDEEnd loopvTSCur := to_timestamp(to_char(vDECur,'dd.mm.yyyy'),'dd.mm.yyyy');curen := 0;for (select x(x.[F__E_NET_B]*10:enet)in ::[GA_MAP_STAT] allwhere x.[qtime] >= vTSCurand x.[qtime] < (vTSCur + numtodsinterval(1,'day'))and x.[F_TIMESTAMP] >= vDECurand x.[F_TIMESTAMP] < (vDECur+1)order by x.[qtime] desc) loopcuren := x.enet;exit;end loop;if curen = 0 and prven != 0 thencuren := prven;end if;if prven is null thenprven := curen;elseif prven = 0 thenprven := curen;end if;curts := TsToUTs(vTsCur - numtodsinterval(num_tz,'hour'));&debug('1. dcur = '||to_char(vDECur,'dd.mm.yyyy')||' prven = '||prven||' curen ='||curen||' diff='||to_char(curen - prven))AddTrgData(CEFRN,curts,curen - prven);prven := curen;end if;vDECur := vDECur + 1;end loop;end if;end;end if;-- Выгрузить собранные массивы ответvTmpId := tflag.first;while vTmpId is not null looptflag(vTmpId).fOb.put('datapoints',tflag(vTmpId).fAr);tflag(vTmpId).fAr := null;pOut.append(tflag(vTmpId).fOb);tflag(vTmpId).fOb := null;vTmpId := tflag.next(vTmpId);end loop;end;procedure get_search(pInp in out nocopy JSON_OBJECT_T, pOut in out nocopy JSON_ARRAY_T) isvTarget varchar2(100);begin&debug('pInp='||pInp.to_string())vTarget := trim(pInp.get_String('target'));if vTarget is null thenpOut.Append(CPALL);pOut.Append(CPNET);pOut.Append(CPACB);pOut.Append(CPI2C);pOut.Append(CPADD);pOut.Append(CPMP1);pOut.Append(CPMP2);pOut.Append(CPMP3);pOut.Append(CEDAY);pOut.Append(CEMP1);pOut.Append(CEMP2);pOut.Append(CEMP3);pOut.Append(CETOB);pOut.Append(CEFRB);pOut.Append(CEFRN);pOut.Append(CUNET);pOut.Append(CUOUT);pOut.Append(CUACB);end if;end;public procedure GetJson(pPathInfo in varchar2, pInpPost in blob, pOutPost out blob) isvInp JSON_OBJECT_T;vOut JSON_ARRAY_T;beginvInp := JSON_OBJECT_T(pInpPost);vOut := JSON_ARRAY_T();&debug('pPathInfo='||pPathInfo)-- Маршрутизация запроса в зависимости от pPathInfoif pPathInfo = '/search' thenget_search(vInp, vOut);elsif pPathInfo = '/query' thenget_query(vInp, vOut);end if;pOutPost := vOut.to_Blob;end;
Возможно это кому-то окажется полезным :-)
Вот такие результаты:
