Creating the database and login

The database for the application will be called quake data. The login name used will be web quakeuser and the password will be Coco99nut (case sensitive and without quotes):

CREATE DATABASE quakedata
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_GB.UTF-8'
       LC_CTYPE = 'en_GB.UTF-8'
       CONNECTION LIMIT = -1;

The SQL script for creating the database is called CreateDatabase.sql and can be found in the SQL sub-folder of the sample code for this chapter:

CREATE ROLE webquakeuser LOGIN
  ENCRYPTED PASSWORD 'md504a29d543c1492922e76af320cae3190'
  SUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION;

The SQL script for creating the user is called CreateUser.sql.

Defining the table

The main table will be a simple storage of the JSON field, with two fields of meta-data, and the SQL to create it can be found in CreateTable.sql:

CREATE TABLE dm_quakefeed (
    quake_id serial PRIMARY KEY,
    geojson text NOT NULL,
    modified_date TIMESTAMP default CURRENT_TIMESTAMP
    );

This SQL defines a table with three fields. The quake_id field will be a unique identifier for each record, geojson will store the data retrieved from the service, and modified_date will record when the insertion into the database was made. Lets have a look at the following screenshot:

The empty database is now configured and ready to be connected to Dart in order to save the data. The table can be found in the tree in the left-hand side panel quakedata | Schemas | public | Tables.

Inserting data

The first step is to create a connection string to connect to the PostgreSQL server. The class DaoQuake in lib/quakedao.dart handles the database access. This is called by the revised DataMonitor class's fetchData method, as shown here:

      if (idPreviousFetch != newId) {
        idPreviousFetch = newId;

        //Save to database.
        await daoGeoJson.storeJson(dataset.toString());

        log.fine("Saved to db $newId - ${dataset.toString()}");
      }

The daoGeoJson instance handles the database interaction. The rest of the data fetching, such as accessing the web service is identical to the file-based version:

    storeJson(String jsonString) async {
    var dbConn;
    try {
      dbConn = await connect(uri);
      await dbConn.execute(
          'insert into dm_quakefeed (geojson) values (@geojson)', {
        'geojson': jsonString
      });
    } catch (exception, stacktrace) {
      log.severe("Exception storing JSON.", exception, stacktrace);
      print(exception);
      print(stacktrace);
    } finally {
      dbConn.close();
    }
  }

The first thing to take note of is that the implementation is wrapped in try{}, catch{} and finally{}. This is because we want a robust solution, even on error conditions. Databases fail and connections fail so the insert may not take place, so we catch exceptions. Once the dbConn object is connected to the database server, it runs the SQL statement to store the jsonString.

The application needs to manage system resources too, so we always want to close the database connections, which like memory and disk space, are finite resources. The finally clause is always executed after the try, catch and except blocks are complete:

String uri = 'postgres://webquakeuser:Coco99nut@localhost:5432/quakedata';

The connection to the database is defined in the uri connection string, which identifies the server, user name, password, and database. Typically this would be stored in a configuration file.

Running the program

Once the program has been run for a few minutes, data will start to build up in the database. This can be viewed in pgAdmin in the SQL editor, which can be run by clicking the SQL icon on the toolbar:

select * from dm_quakefeed;

This simple select statement will show the table contents:

Maintaining a database

Setting up a database and writing the accompanying application is only a small part of running a database. Like a garden, they need tending and pruning to get the best results.

We will take a look at writing a maintenance script for the data-monitoring application that will report on the data and clear out the data.

Open the bin/maintenance.dart file in the editor.

import 'package:QuakeMonitorDB/quakedao.dart';

main(List<String> arguments) {
  if (arguments.length == 0 || arguments.length > 1) {
    print("Please use either -info or -delete.");
    return 0;
  }

  if (arguments[0] == "-info") {
    var daoGeoJson = new DaoQuake();
    daoGeoJson.displayInfo();
  } else if (arguments[0] == "-delete") {
    var daoGeoJson = new DaoQuake();
    daoGeoJson.deleteRecords();
  }

  return 0;
}

Retrieving data

The -info option will return the following data from the database:

The method is implemented in lib/quakedao.dart with a short SQL query to retrieve the summarized data:

  displayInfo() async {
    var dbConn;
    try {
      dbConn = await connect(uri);

      var query = """select count(*) as Count,
       min(modified_date) as MinDate,
       max(modified_date) as MaxDate
       from dm_quakefeed
      """;
      var results = await dbConn.query(query).toList();
      print("Count   : ${results[0][0]}");
      print("MinDate : ${results[0][1]}");
      print("MaxDate : ${results[0][2]}");
    } catch (exception, stacktrace) {
      log.severe("Exception getting info.", exception, stacktrace);
      print(exception);
    } finally {
      dbConn.close();
    }
  }

The result set is returned from PostgreSQL and converted to a list. Each record returned is a list of the column values for that record:

Count   : 269
MinDate : 2015-05-26 00:00:00.000
MaxDate : 2015-05-27 00:00:00.000

In this instance, we are returned a single record (result[0]) containing a list with the three columns (accessed through indices) that contain the information that we need.

  deleteRecords() async {
    var dbConn;
    try {
      dbConn = await connect(uri);

      var query = "delete from dm_quakefeed";
      await dbConn.execute(query);
    } catch (exception, stacktrace) {
      log.severe("Exception getting info.", exception, stacktrace);
      print(exception);
    } finally {
      dbConn.close();
    }
  }

Count   : 0
MinDate : null
MaxDate : null