Difference between revisions of "SMS tables"
From ecology
| Line 7: | Line 7: | ||
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 78: | ||
</table> | </table> | ||
| − | = message table = | + | = sms.message table = |
The parsed message body. | The parsed message body. | ||
| Line 97: | Line 97: | ||
<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 141: | ||
</table> | </table> | ||
| − | = position table = | + | = sms.position table = |
The positions parsed form the message body. | The positions parsed form the message body. | ||
| Line 160: | Line 160: | ||
<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"> | ||
Revision as of 11:21, 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 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) |
