6.8 Creating the Delivery Database
We need to create two tables for this project. The first will store both the history of force sensitive resistor triggers that occur when packages are delivered and removed as well as the value of the exceeded threshold value. The second table will store known tracking numbers of inbound package deliveries and a date delivery field that will contain the time and date of when the delivery was confirmed by the courier. We’ve done something like this before in Chapter 5, Tweeting Bird Feeder, so we’ll apply that same approach to the creation of the package delivery database.
We will first create the database file using the
sqlite3 tool, followed by the
creation of the two tables within the packagedelivery database. Recall that we
need to capture the force sensitive resistor’s trigger actions and
record the time and date of when those actions take place. Here’s
the structure of the database:
| Column Name | Data Type | Primary Key? | Autoinc? | Allow Null? | Unique? |
|---|---|---|---|---|---|
|
id |
INTEGER |
YES |
YES |
NO |
YES |
|
time |
DATETIME |
NO |
NO |
NO |
NO |
|
event |
TEXT |
NO |
NO |
NO |
NO |
Does this look familiar? Yes, it’s very similar to the structure of the table we created for the Tweeting Bird Feeder project. The general principles are the same: namely, we need to capture an event and record when it occurred in a structured format. This time the event is when a package arrives.
Create this table by submitting the following SQL statement to the sqlite command line:
[~]$
sqlite3 packagedelivery.sqlite |
|
SQLite
version 3.7.6 |
|
Enter
".help" for instructions |
|
Enter
SQL statements terminated with a ";" |
|
sqlite> CREATE
TABLE "deliverystatus" ("id" INTEGER PRIMARY KEY NOT NULL
UNIQUE, |
|
"time"
DATETIME NOT NULL,"event" TEXT NOT NULL); |
We still need a table called tracking to hold assigned tracking numbers,
a description of the package contents, and the package’s delivery
status and the date of delivery as confirmed by the courier’s own
records. The structure of this table should be as follows:
| Column Name | Data Type | Primary Key? | Autoinc? | Allow Null? | Unique? |
|---|---|---|---|---|---|
|
id |
INTEGER |
YES |
YES |
NO |
YES |
|
tracking_number |
TEXT |
NO |
NO |
NO |
NO |
|
description |
TEXT |
NO |
NO |
NO |
NO |
|
delivery_status |
BOOLEAN |
NO |
NO |
NO |
NO |
|
delivery_date |
DATETIME |
NO |
NO |
NO |
NO |
Run the following SQL statement in the sqlite3 command-line tool to create this
second table structure in the packagedelivery database:
[~]$
sqlite3 packagedelivery.sqlite |
|
SQLite
version 3.7.6 |
|
Enter
".help" for instructions |
|
Enter
SQL statements terminated with a ";" |
|
sqlite> CREATE
TABLE "tracking" ("id" INTEGER PRIMARY KEY NOT NULL
UNIQUE, |
|
"tracking_number" TEXT NOT NULL,
"description" TEXT NOT NULL, |
|
"delivery_status" BOOL NOT NULL,
"delivery_date" DATETIME); |
Now that our database tables have been created, we can proceed with the next step of obtaining the Python package dependencies we will use in the delivery processor script.