Difference between revisions of "SMS tables"

From ecology
Jump to: navigation, search
(Created page with "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....")
 
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
The eEcology SMS reciever (https://github.com/NLeSC/eEcology-SMS-reciever) service recieves SMS messages and stores them.
+
The eEcology SMS reciever (https://github.com/NLeSC/eEcology-SMS-reciever) service recieves SMS messages as http POST requests and stores them in the eEcology database.
 +
The SMS messages are converted to http POST requests by the SMSSync Android app (http://smssync.ushahidi.com/).
  
 
The messages are stored in the PostgreSQL server at db.e-ecology.sara.nl in the eecology database and '''sms''' schema.
 
The messages are stored in the PostgreSQL server at db.e-ecology.sara.nl in the eecology database and '''sms''' schema.
Line 6: Line 7:
  
 
Data is stored in 3 tables:
 
Data is stored in 3 tables:
* raw_message
 
* message
 
* position
 
  
= raw_message table =
+
= sms.raw_message table =
  
= message table =
+
Data received from sending party.
  
= position table =
+
<table xmlns="http://www.w3.org/1999/xhtml" border=1>
 +
        <tr>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Name</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Data type</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Not Null?</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Primary key?</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Default</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Comment</th>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">id</td>
 +
          <td class="ReportTableValueCell">integer</td>
 +
          <td class="ReportTableValueCell">Yes</td>
 +
          <td class="ReportTableValueCell">Yes</td>
 +
          <td class="ReportTableValueCell">Auto incrementing</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
        </tr>
 +
        <tr class="ReportDetailsOddDataRow">
 +
          <td class="ReportTableValueCell">message_id</td>
 +
          <td class="ReportTableValueCell">uuid</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">unique ID of the SMS</td>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">sent_from</td>
 +
          <td class="ReportTableValueCell">character varying</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">number that sent the SMS</td>
 +
        </tr>
 +
        <tr class="ReportDetailsOddDataRow">
 +
          <td class="ReportTableValueCell">body</td>
 +
          <td class="ReportTableValueCell">character varying</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">Body of the SMS</td>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">sent_to</td>
 +
          <td class="ReportTableValueCell">character varying</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">the phone number registered on the SIM card otherwise it's the value set on the app as device ID</td>
 +
        </tr>
 +
        <tr class="ReportDetailsOddDataRow">
 +
          <td class="ReportTableValueCell">gateway_id</td>
 +
          <td class="ReportTableValueCell">character varying</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">the unique id set on the device to be used by the server to identify which device is communicating with it </td>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">sent_timestamp</td>
 +
          <td class="ReportTableValueCell">timestamp with time zone</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">the timestamp the SMS was sent</td>
 +
        </tr>
 +
      </table>
 +
 
 +
= sms.message table =
 +
 
 +
The parsed message body.
 +
 
 +
<table xmlns="http://www.w3.org/1999/xhtml" border=1>
 +
        <tr>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Name</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Data type</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Not Null?</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Primary key?</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Default</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Comment</th>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">id</td>
 +
          <td class="ReportTableValueCell">integer</td>
 +
          <td class="ReportTableValueCell">Yes</td>
 +
          <td class="ReportTableValueCell">Yes</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">Foreign key sms.raw_message.id</td>
 +
        </tr>
 +
        <tr class="ReportDetailsOddDataRow">
 +
          <td class="ReportTableValueCell">device_info_serial</td>
 +
          <td class="ReportTableValueCell">integer</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">Tracker identifier</td>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">date_time</td>
 +
          <td class="ReportTableValueCell">timestamp with time zone</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">Datetime the message was recieved</td>
 +
        </tr>
 +
        <tr class="ReportDetailsOddDataRow">
 +
          <td class="ReportTableValueCell">battery_voltage</td>
 +
          <td class="ReportTableValueCell">real</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell"> </td>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">memory_usage</td>
 +
          <td class="ReportTableValueCell">real</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell"> </td>
 +
        </tr>
 +
        <tr class="ReportDetailsOddDataRow">
 +
          <td class="ReportTableValueCell">debug_info</td>
 +
          <td class="ReportTableValueCell">character varying</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell"> </td>
 +
        </tr>
 +
      </table>
 +
 
 +
= sms.position table =
 +
 
 +
The positions parsed form the message body.
 +
 
 +
<table xmlns="http://www.w3.org/1999/xhtml" border=1>
 +
        <tr>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Name</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Data type</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Not Null?</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Primary key?</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Default</th>
 +
          <th class="ReportTableHeaderCell" width="16,6666666666667%">Comment</th>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">id</td>
 +
          <td class="ReportTableValueCell">integer</td>
 +
          <td class="ReportTableValueCell">Yes</td>
 +
          <td class="ReportTableValueCell">Yes</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">Foreign key sms.message.id </td>
 +
        </tr>
 +
        <tr class="ReportDetailsOddDataRow">
 +
          <td class="ReportTableValueCell">device_info_serial</td>
 +
          <td class="ReportTableValueCell">integer</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">Tracker identifier</td>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">date_time</td>
 +
          <td class="ReportTableValueCell">timestamp with time zone</td>
 +
          <td class="ReportTableValueCell">Yes</td>
 +
          <td class="ReportTableValueCell">Yes</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">Timestamp at which location was taken</td>
 +
        </tr>
 +
        <tr class="ReportDetailsOddDataRow">
 +
          <td class="ReportTableValueCell">lon</td>
 +
          <td class="ReportTableValueCell">double precision</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">Longitude </td>
 +
        </tr>
 +
        <tr class="ReportDetailsEvenDataRow">
 +
          <td class="ReportTableValueCell">lat</td>
 +
          <td class="ReportTableValueCell">double precision</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">Latitude</td>
 +
        </tr>
 +
        <tr class="ReportDetailsOddDataRow">
 +
          <td class="ReportTableValueCell">location</td>
 +
          <td class="ReportTableValueCell">geometry(1107456)</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell">No</td>
 +
          <td class="ReportTableValueCell"> </td>
 +
          <td class="ReportTableValueCell">PostGIS location as a Point(lon, lat) </td>
 +
        </tr>
 +
      </table>

Latest revision as of 11:28, 11 May 2015

The eEcology SMS reciever (https://github.com/NLeSC/eEcology-SMS-reciever) service recieves SMS messages as http POST requests and stores them in the eEcology database. The SMS messages are converted to http POST requests by the SMSSync Android app (http://smssync.ushahidi.com/).

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:

sms.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

sms.message table

The parsed message body.

Name Data type Not Null? Primary key? Default Comment
id integer Yes Yes Foreign key sms.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

sms.position table

The positions parsed form the message body.

Name Data type Not Null? Primary key? Default Comment
id integer Yes Yes Foreign key sms.message.id
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)