Getting additional data values from Xirgo devices

Xirgo devices report more data than is exposed in the default application, such as fuel economy data. In order to get at this data, you must create a database procedure to transfer it into your application database. Database procedures are contained in the file db_procs.sql which is located in the root directory of you application code. In order to pull additional data, you must first add additional columns to your readings table to hold the data. Create a migration such as the following:

class AddGeofenceToReadings < ActiveRecord::Migration
  def self.up
    add_column :readings, :avg_fuel_consumption, :float
  end

  def self.down
    remove_column :readings, :avg_fuel_consumption
  end
end

Which created a new column called average_fuel_consumption in the readings table. In order to get this field populated, add a snippet like the following to db_procs.sql to create a procedure name “custom_after_insert_xirgo_obd”, note that this procedure is referenced by name, so the name must be exactly that. Also keep in mind that “xirgo_slicehost” is the name of the gateway database. The two arguments to this procedure are the id of the reading in the gateway database, and the id of the reading in the rails application database. In most cases, the body of the procedure will simply select a values from the gateway database and use them to update the application database, as is the case here.

DROP PROCEDURE IF EXISTS custom_after_insert_xirgo_obd;;
CREATE PROCEDURE custom_after_insert_xirgo_obd (IN gateway_reading_id INT(11),
									  IN rails_reading_id INT(11) ) 
BEGIN
	UPDATE readings SET avg_fuel_consumption=(SELECT averageFuelConsumption FROM xirgo_slicehost.readings WHERE id=gateway_reading_id) WHERE id=rails_reading_id ;
END;;

db_procs.sql will need to then get executed to add this procedure to your database. This should already be a part of your deployment if you are using the supplied capistrano file.