Combining OpenHAB and Traccar for monitoring vehicle pings

I have the following task:

1. I have installed a GPS tracker in car. While not moving the tracker sends to server a ping every 3 minutes.
2. On server I am using traccar
3. I have configured traccar to store its data in mysql
4. Now each time when I receive a ping or location info from the tracker I have to notify OpenHab
5. If there is not ping for approximately 20 minutes I have to send and alarm notification

So, here is what I have to do
(Useful article for reference)

1. Configure items and rules on OpenHab

Items

Group Car  (All)

DateTime Car_Last_ping   "Last Update [%1$td.%1$tm %1$tH:%1$tM]" (Car)

Switch Car_ping "Ping from car" (Car)

Rules

val Number NO_PING_DELAY = 20 // Minutes

  rule "Set last ping date"

  when

    Item Car_ping received update

  then

        postUpdate(Car_Last_ping, new DateTimeType())

  rule "Notify on car ping delay"

  when

    Time cron "0 0/10 * * * ?" // Every 10 minutes

  then

    var DateTimeType lastPingTime = Car_Last_ping.state as DateTimeType

    var Number delay = (now.millis - lastPingTime.getCalendar.getTimeInMillis) / 60000

    // logInfo("Solvek", "Lasted time "+delay)

    if delay>NO_PING_DELAY {

      sendTelegram("bot1", "No ping from car for a long time")

    }

  end

2. Install lib_mysqludf_sys library

In order to execute system commands from MySQL triggers we need UDF "sys_exec". It is defined in this library.

2.1. Install dependent library: sudo apt-get install libmysqlclient15-dev

2.2. Grab sys library sources: git clone https://github.com/mysqludf/lib_mysqludf_sys.git

2.3. cd lib_mysqludf_sys

2.4. Install using sudo ./install.sh
2.5. If you are getting errors during compilation modify Makefile as described here and goto 2.4.

2.6. If no errors happens then goto 2.9. However if you get error: "ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so' (errno: 0 /usr/lib/mysql/plugin/lib_mysqludf_sys.so: cannot open shared object file: No such file or directory) ERROR: unable to install the UDF" do the following steps.

2.7. Copy lib_mysqludf_sys.so from usr/lib to usr/lib/mysql/plugins

2.8. Return back to lib_mysqludf_sys sources directory and manually install UDF by running command: mysql -u root -p mysql < lib_mysqludf_sys.sql

You may test UDF function by quiring SELECT sys_exec('curl 

http://localhost:8080/CMD?Car_ping=ON')

2.9. If you get 0 in result then it is ok.

On Ubuntu apparmor can prevent executing of UDF (and you get 32512 instead of 0). In this case you have to disable apparmor it for mysql:

sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/

sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

More details here.

3. Create a trigger on positions table in traccar database

DROP TRIGGER IF EXISTS `insert_positions`;

CREATE DEFINER=`root`@`localhost` TRIGGER `insert_positions` 

AFTER INSERT ON `positions` 

FOR EACH ROW

 IF NEW.device_id = 2 THEN

 SET @exec_var = sys_exec('curl http://localhost:8080/CMD?Car_ping=ON');

 END IF

Note here NEW.device_id =2. "2" is the id of your device to monitor, you have to replace it with actual value.

Also shell command curl http://localhost:8080/CMD?Car_ping=ON allows to update an item on OpenHAB.