Sensor log storage and queries

Use the device log structure to check whether storing the same key again creates a new record or changes the existing record.

Uses Python's sqlite3; no DB server or account is required.

Starting conditions

The measurements for device m1 at $t\in\{0,1,1,2\}$ s are 20,21,22,23. Update entries with the same device and time to the latest value.

Procedure

1. Check SQLite's composite primary key and store the four provided measurements.
2. Check the number of stored rows and the value of $t=1$, and explain whether the duplicate handling rule is correct.

Results to retain

• Storage results and 1 duplicate handling example.

Starting conditions

Add $t\in\{0,1,2\}$ with values 30,31,32 for device m2. Query only m1 for 1≤t≤2.

Procedure

1. Write a query function that takes a device, start time, and end time as arguments.
2. Test the specified interval and an interval with no values once each.
3. Plot query results as time versus measurement.

Results to retain

• A query function, two query results, and a time series plot.

Starting conditions

Assume the HTTP API in the next unit calls the query function. The request is device=m1,start=0,end=2.

Procedure

1. Define the field structure for passing query results as JSON.
2. Define response rules that distinguish a nonexistent device from start>end.
3. Identify the data and query rules to retain when switching to an external storage system such as DynamoDB.

Results to retain

• Two response examples for valid and invalid requests, and rules to retain when replacing storage.

• Python · SQLite