SMS tables

From ecology
Jump to: navigation, search

The eEcology SMS reciever (https://github.com/NLeSC/eEcology-SMS-reciever) service recieves SMS messages and stores them.

The messages are stored in the PostgreSQL server at db.e-ecology.sara.nl in the eecology database and sms schema. The data can be inserted by the eEcology SMS reciever service. The data can be selected by users with the gps_read role.

Data is stored in 3 tables:

raw_message table

Data received from sending party.

Name Data type Not Null? Primary key? Default Comment
id integer Yes Yes Auto incrementing
message_id uuid No No unique ID of the SMS
sent_from character varying No No number that sent the SMS
body character varying No No Body of the SMS
sent_to character varying No No the phone number registered on the SIM card otherwise it's the value set on the app as device ID
gateway_id character varying No No the unique id set on the device to be used by the server to identify which device is communicating with it
sent_timestamp timestamp with time zone No No the timestamp the SMS was sent

message table

The parsed message body.

Name Data type Not Null? Primary key? Default Comment
id integer Yes Yes Foreign key raw_message.id
device_info_serial integer No No Tracker identifier
date_time timestamp with time zone No No Datetime the message was recieved
battery_voltage real No No
memory_usage real No No
debug_info character varying No No

position table

The positions parsed form the message body.

Name Data type Not Null? Primary key? Default Comment
id integer Yes Yes
device_info_serial integer No No Tracker identifier
date_time timestamp with time zone Yes Yes Timestamp at which location was taken
lon double precision No No Longitude
lat double precision No No Latitude
location geometry(1107456) No No PostGIS location as a Point(lon, lat)