Building further on Spiral s07 from the previous chapter, we will now add the functionality to store our data on the server in a MySQL database. The project is named todo_mysql and the code can be obtained from https://github.com/dzenanr/todo_mysql.

It contains three subprojects: one for the client, which is the same as the client portion of client_server_db_s07 in the project indexed_db_spirals from Chapter 11, Local Data and Client-Server Communication, and two server projects, both with MySQL. The server projects are equivalent in functionality: todo_server_mysql uses MySQL directly and todo_server_dartling_mysql is built with a task model in Dartling, to show how the database is updated by reacting to changes in the model. Both the server projects need to talk to MySQL, so they have the dependency sqljocky: any in their pubspec.yaml, to import the MySQL driver. But, of course, we also need the database software, so download and install the MySQL Community Server installer from http://dev.mysql.com/downloads/mysql/. This is straightforward, but in case you need any help with the installation, visit http://dev.mysql.com/doc/refman/5.7/en/installing.html.

Be sure that the MySQL server process (mysqld) is started before going further. Then start the MySQL Workbench tool (contained in the download) and create a new empty database (schema) with the name todo. We need only one table, task, which you can easily drag-and-drop using this tool; you can also run the Dart script from test/mysql_test.dart to create and populate the table with some initial data (recommended to avoid errors in a creation of the table).

You can now run the app as follows; run the server first:
todo_server_mysql/bin/server.dart 

Alternatively, you can run the following in the Dart Editor:

todo_server_dartling_mysql/bin/server.dart 

When you run, the output you see in the server.dart tab in Dart Editor is as follows: Server at http://127.0.0.1:8080.

If it does not run, navigate to Run | Manage Launches (put a path to the project folder; for example, d: odo_mysql odo_server_mysql in the working directory field in Run | Manage Launches in order to have access to the connection.options file). Run the first client in Dartium (todo_client_idb/web/app.html), and the second client as JavaScript (todo_client_idb/web/app.html) in Chrome.

Use the client app in Dartium to add, update, or remove a task. The screen hasn't changed since the screenshot showing the screen in Spiral s04 and the server communication in Spiral s06 (Chapter 11, Local Data and Client-Server Communication). Send it by pushing the To Server button where it is stored in the database; check this by viewing the task table data in MySQL Workbench. The second client can then retrieve this data using the From Server button. Experiment with the data by adding other clients; remember, that all the clients also store the data locally in IndexedDB.

How do we go about storing our data in MySQL? We'll first examine the todo_server_mysql project. The code that interacts with sqljocky sits in lib/model/mysql.dart and contains the classes TodoDb and TaskTable. The model, together with the data access layer, is contained in library todo_server_mysql, which is imported in bin/server.dart.

The code starts executing with the main() function in server.dart: a todoDb object is made on which (asynchronously) the open method is called, a connection is made with MySQL, and the task data is loaded in taskTable, and finally the webserver is started:

void main() {
  var todoDb = new TodoDb();
  todoDb.open().then((_) {
    taskTable = todoDb.taskTable;
    start();    // start webserver
  });
}

In order to connect to a MySQL server and database, we need a minimal set of configuration settings that our app needs to read: a username, password, database, hostname, and a port number. These are stored as the key=value pairs in the file connection.options:

# connection.options defines how to connect to a MySQL db
user=root
password=xyz # fill in your own password
port=3306
db=todo
host=localhost 

The connection.options file sits right beneath the project directory. In order to have access to it from our app, or any file with a main method that you run, we need to navigate to Run | Manage Launches from the Dart Editor menu, and put a path to the project folder (todo_mysql) in the working directory field. The quickest way to do this is to run it first; it will fail because it doesn't find the file, but then you have a readymade Manage Launch window for inserting the right path.

To read the contents of such a file in Dart, create an object of the class OptionsFile, passing it the filename (see line (1) in the following code). This class comes from the options_file package by James Ots, available from pub; in order to use it, we must include the following in our library file:

import 'package:options_file/options_file.dart';

Line (3) and onwards, the getString or getInt method is used, which, given the key name, provides the value. Here is how it is done in the TodoDb class from lib/model/mysql.dart:

class TodoDb {
  TaskTable _taskTable;
  TaskTable get taskTable => _taskTable;

  Future open() {
    var pool = getPool(new OptionsFile('connection.options')); (1)
    _taskTable = new TaskTable(pool);
    return _taskTable.load();
  }

  ConnectionPool getPool(OptionsFile options) {                (2)
    String user = options.getString('user'),                   (3)
    String password = options.getString('password'),
    int port = options.getInt('port', 3306);
    String db = options.getString('db'),
    String host = options.getString('host', 'localhost'),
    return new ConnectionPool(                                 (4)
      host: host, port: port, user: user, password: password,db: db);
  }
}

With these values, the method getPool (starting in line (2)) builds a ConnectionPool object in line (4), which knows everything needed to connect to the MySQL database. This is returned from this method and assigned to the variable pool in line (1). On the next line, a TaskTable object is constructed on which the load method is called. When the server starts, it loads data from MySQL to the model in the main memory. When the model changes, the database is updated. Our code continues executing in the class TaskTable:

class TaskTable {
  final ConnectionPool _pool;
  Tasks _tasks;

  TaskTable(this._pool) {
    _tasks = new Tasks.withTable(this);
  }

  Tasks get tasks => _tasks;
  bool get isEmpty => tasks.length == 0;

  Future load() {                                           (1)
    Completer completer = new Completer();                  (2)
    _pool.query(                                            (3)
      'select t.title, t.completed, t.updated '
      'from task t '
    ).then((rows) {                                         (4)
      var taskMap;
      rows.listen((row) {                                   (5)
        taskMap = {
          'title'    : '${row[0]}',                         (6)
          'completed': '${row[1]}',
          'updated'  : '${row[2]}'
        };
        var task = new Task.fromDb(taskMap);                 (7)
        tasks.load(task);
      },
        onError: (e) => print('data loading error: $e'),     (8)
        onDone: () {                                         
          completer.complete();                              (9)
          print('all tasks loaded'),
        }
      );
    });
    return completer.future;                                 (10)
  }

The load method in line (1) executes asynchronously, so it returns a Future (so do all methods accessing the database). But we also see, here in line (1), the use of an intermediate instance of the class Completer from the dart:async library.

When the asynchronous code becomes more complex, the use of a Completer method helps write more maintainable and readable code. Using the Completer method, you can explicitly indicate when the Future value will be available by calling the complete method as in line (9) in the onDone handler. This signals the Future that the asynchronous operation has been completed. At the end of the load method, the Future value is returned in line (10) as completer.future. The code of the load method can seem daunting at first sight; let's analyze it step-by-step with the help of the many built-in functions in the editor (another visual theme from Tools | Preferences can also be useful). MySQL is a relational database, so at some point, our code will have to construct the SQL statements to be sent to the database for execution. In line (3), the query method (defined in sqljocky) is called on the pool object with a SQL string as the parameter; the following is the signature taken from the pop-up window when the cursor hovers over query:

Future<Results> query(String sql)

As we expected, query returns a Future; so, a then clause has to follow in line (4). The following is the signature:

Future then( onValue(Results) -> dynamic, { onError(Object) -> dynamic}) 

From this we see that it has an onValue handler to process the results and an optional onError handler. In our code, we have an anonymous onValue handler that runs all the way down to stop just before line (10):

The onValue handler in the load method

The retrieved rows come in like a stream; the stream getter has a listen method to subscribe to this stream with the signature:

StreamSubscription<Row> listen( onData(Row) -> void, { onError(dynamic) -> 	void, onDone() -> void, bool  cancelOnError} )

It has an onData handler that is called for every row that comes in, and the optional onError and onDone handlers. The onData handler is again an anonymous method and stretches from line (4) just up to line (8). Here, the data is extracted using an indexing operator row[i]. With string interpolation, we construct a literal map taskMap from which a Task object is made and added to the tasks collection; so, all the task data is in the server memory. Then, we see onError, which prints possible errors, and onDone, which signals the completion of the async operation in line (9). The value of the Future is returned in line (10).

The _integrateDataFromClient method in bin/server.dart updates the data in the server memory and the database (we'll see this shortly):

_integrateDataFromClient(List<Map> jsonList) {
  var clientTasks = new Tasks.fromJson(jsonList);
  var serverTasks = taskTable.tasks;
  var serverTaskList = serverTasks.toList();
  for (var serverTask in serverTaskList) {
    if (!clientTasks.contains(serverTask.title)) {
      serverTasks.remove(serverTask);                         (1)
    }
  }

  for (var clientTask in clientTasks) {
    if (serverTasks.contains(clientTask.title)) {
      var serverTask = serverTasks.find(clientTask.title);
      if (serverTask.updated.millisecondsSinceEpoch <
          clientTask.updated.millisecondsSinceEpoch) {
        serverTask.completed = clientTask.completed;
        serverTask.updated = clientTask.updated;
      }
    } else {
      serverTasks.add(clientTask);                         (2)
    }
  }
}

The remove and add methods in lines (1) and (2) are found in model.dart (use Open Declaration from the context menu), and in turn they call the delete and insert methods from mysql.dart. The update method gets called in the completed and updated setters in model.dart. Let us look a bit deeper at these methods, for example, the insert method:

Future<Task> insert(Task task) {
    var completer = new Completer();
    var taskMap = task.toDb();
    _pool.prepare(                                             (1)
      'insert into task (title, completed, updated) values (?, ?, ?)'
    ).then((query) {
      print("prepared query insert into task");
      var params = new List();                                 (2)
       params.add(taskMap['title']);
       params.add(taskMap['completed']);
       params.add(taskMap['updated']);
       return query.execute(params);                          (3)
    }).then((_) {
      print("executed query insert into task");
      completer.complete();                                   (4)
    }).catchError(print);                                     (5)
    return completer.future;
  } 

Here, we see that the SQL of the query is first prepared in line (1) (a sort of parse step in the database), which returns a Future. In the returned result, the task data is inserted in the ? placeholders using a list that contains the parameter values. This list is constructed in line (2) and the subsequent lines. Then, the execute method is called on the query in line (3), again returning a Future. The async operation is marked as completed in line (4). Note, the error handling in line (5); always include this while dealing with the database access. Now, you'll be able to understand the update and delete methods by yourself, as shown in the following code:

Future<Task> update(Task task) {
    var completer = new Completer();
    var taskMap = task.toDb();
    _pool.prepare(
      'update task set completed = ?, updated = ? where title = ?').then((query) {
      print("prepared query update task");
	   var params = new List();
      params.add(taskMap['completed']);
      params.add(taskMap['updated']);
      params.add(taskMap['title']);
      return query.execute(params);
    }).then((_) {
      print("executed query update task");
      completer.complete();
    }).catchError(print);
    return completer.future;
  }

  Future<Task> delete(Task task) {
    var completer = new Completer();
    var taskMap = task.toDb();
    _pool.prepare(
      'delete from task where title = ?'
    ).then((query) {
      print("prepared query delete from task");
                  var params = new List();
        params.add(taskMap['title']);
      return query.execute(params);
    }).then((_) {
      print("executed query delete from task");
      completer.complete();
    }).catchError(print);
    return completer.future;
}

You have now seen a complete data access layer to a relational database in action!

The second project todo_server_dartling_mysql builds upon the dartling_todo_mvc_spirals project in Chapter 9, Modeling More Complex Applications with Dartling. Dartling was used to model tasks and generate a code basis; so the lib/gen code is the same. The data access code lives in lib/persistence/mysql.dart. With Dartling, a model is not dependent on MySQL—the model does not call insert, update, and delete methods (with the SQL code). The TodoDb class, in its constructor, starts listening to the actions of the Dartling model:

domain.startActionReaction(this);

In the react method, the TodoDb class reacts to actions in the Dartling model.

  react(ActionApi action) {
    if (action is AddAction) {
      taskTable.insert((action as AddAction).entity);
    } else if (action is RemoveAction) {
      taskTable.delete((action as RemoveAction).entity);
    } else if (action is SetAttributeAction) {
      taskTable.update((action as SetAttributeAction).entity);
    }
  }

In this way, one may use more than one database with Dartling without updating the model code. The _integrateDataFromClient method now works with the model's actions (such as AddAction and RemoveAction):

_integrateDataFromClient(List<Map> jsonList) {
  var clientTasks = new Tasks.fromJson(db.tasks.concept, jsonList);
  var serverTaskList = db.tasks.toList();
  for (var serverTask in serverTaskList) {
    var clientTask =
        clientTasks.singleWhereAttributeId('title', serverTask.title);
    if (clientTask == null) {
      new RemoveAction(db.session, db.tasks, serverTask).doit();
    }
  }
  for (var clientTask in clientTasks) {
    var serverTask =
        db.tasks.singleWhereAttributeId('title', clientTask.title);
    if (serverTask != null) {
      if (serverTask.updated.millisecondsSinceEpoch <
          clientTask.updated.millisecondsSinceEpoch) {
        new SetAttributeAction(
          db.session, serverTask, 'completed', clientTask.completed).doit();
      }
    } else {
      new AddAction(db.session, db.tasks, clientTask).doit();
    }
  }
}

To experiment with this version, first run the bin/server.dart server, and then one or more clients from todo_client_idb.