Listing 1 The SQL description for the database

------------------------- count.sql -------------------------------
-- The dnsbl table keeps track of the dnsbls we know about. Each is
-- assigned a different id the first time we see it. This makes it
-- easy to add new dnsbls on the fly.

CREATE TABLE dnsbl (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          STRING          -- The name of the dnsbl
);

-- The samples table is responsible for storing each datapoint or hit,
-- that is, how many IPs matched against each list. The additional
-- 'source' column can be used to keep information about different
-- networks

CREATE TABLE sample (
  dnsbl_id      INTEGER,        -- Foreign key to dnsbl
  sample_utime  INTEGER,        -- The Unix time of this sample
  source        STRING,         -- Where this sample came from
  count         INTEGER,        -- The sample value
  PRIMARY KEY (dnsbl_id, sample_utime, source)
);