Difference between revisions of "SMS tables"

From ecology
Jump to: navigation, search
 
(2 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 7: Line 8:
 
Data is stored in 3 tables:
 
Data is stored in 3 tables:
  
= raw_message table =
+
= sms.raw_message table =
  
 
Data received from sending party.  
 
Data received from sending party.  
Line 78: Line 79:
 
       </table>
 
       </table>
  
= message table =
+
= sms.message table =
  
 
The parsed message body.
 
The parsed message body.
Line 97: Line 98:
 
           <td class="ReportTableValueCell">Yes</td>
 
           <td class="ReportTableValueCell">Yes</td>
 
           <td class="ReportTableValueCell"> </td>
 
           <td class="ReportTableValueCell"> </td>
           <td class="ReportTableValueCell">Foreign key raw_message.id</td>
+
           <td class="ReportTableValueCell">Foreign key sms.raw_message.id</td>
 
         </tr>
 
         </tr>
 
         <tr class="ReportDetailsOddDataRow">
 
         <tr class="ReportDetailsOddDataRow">
Line 141: Line 142:
 
       </table>
 
       </table>
  
= position table =
+
= sms.position table =
  
 
The positions parsed form the message body.
 
The positions parsed form the message body.
Line 160: Line 161:
 
           <td class="ReportTableValueCell">Yes</td>
 
           <td class="ReportTableValueCell">Yes</td>
 
           <td class="ReportTableValueCell"> </td>
 
           <td class="ReportTableValueCell"> </td>
           <td class="ReportTableValueCell"> </td>
+
           <td class="ReportTableValueCell">Foreign key sms.message.id </td>
 
         </tr>
 
         </tr>
 
         <tr class="ReportDetailsOddDataRow">
 
         <tr class="ReportDetailsOddDataRow">

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)