Nicollet.Net

← Item Model | Update and Delete →

Now that the underlying database structure is designed and installed, it’s time to move on to the PHP code that manipulates it. But first, a little aside about transactions.

For performance reasons, I separate database requests into two categories: the requests that care about the database being in a fresh and consistent state, and the requests that do not care about the database being in a fresh and consistent state. The first category needs to be executed on the master database with transactions, which is a twofold penalty hit. The second category can be executed on a slave database without transactions.

Therefore, I need to tell my requests whether they are being executed in a paranoid context (a transaction is currently active on the master database) or in a permissive context (no transaction is currently active, reads happen on the slave). To this end, I provide a final optional argument to model functions, which tells me whether the context is paranoid (which isn’t the case by default). The model function may then decide to remain in the permissive context (most read functions do this) or escalate to a paranoid context (all write functions do this) by managing a transaction and notifying every sub-request that the context is paranoid.

By using this approach, I end up doing two things a lot:

• If I need a paranoid context, I will be starting and ending transactions only if the context is not already paranoid. This is the typical “if not paranoid start transaction”, “if not paranoid commit”, “if not paranoid rollback”. To avoid all these conditionals, I add a new parameter to the start/commit/rollback functions which tells them whether a paranoid context is active. The functions do nothing if the context is already paranoid.

• Depending on the context, I will be using the master or slave database for reading. This is the typical “if paranoid then master else slave” choice, so I add a new function to the database configuration class that returns the appropriate database for a context.

These modifications are, in class DBUtils:

  public static function Get($transaction)
  {
      return $transaction ? self::Master() : self::Slave();
  }

And in class DBConfig:

  public static function Begin(mysqli $sql, $transaction = false)
  {
      if (!$transaction)
          $sql->query('START TRANSACTION');
  }

  public static function Commit(mysqli $sql, $transaction = false)
  {
      if (!$transaction)
          $sql->query('COMMIT');
  }

  public static function Rollback(mysqli $sql, $transaction = false)
  {
      if (!$transaction)
          $sql->query('ROLLBACK');
  }

Back to our objects, then. My system will need to manage Authentications, Users, Groups, User-Group associations, Items, and Item Revisions. I will subdivide the system into:

• UserModel: identifies who the users are and what their name is. This is the only way of creating a new user.
• AuthenticationModel: identifies what users can log in, and what their login/password is. This is the only way of registering an user.
• GroupModel: identifies what the groups are and what their name is. This is the only way of creating a new group.
• GroupUsersModel: describes which users are present in which groups and with which rights. This is the only way to grant an user privileges within a model.
• ItemModel: identifies what the items are, allows item creation, and querying for items in various ways. All item queries are limited by user permissions. Items cannot be modified from this model, since all modifications exist as revisions.
• ItemRevisionModel: identifies modifications applied to items, allows the creation of new modifications (that update the item state accordingly), with a creation function for every possible modification.

Right now, only the authentication model exists and it includes some part of the user model. The first step would be to extract all of that, and have the authentication model call the user model to create an user. The modified registration function:

  {
      $query = '`authentication`(`user_id`, `auth_mail`, `auth_pass`, `auth_salt`)
       VALUES (?, ?, MD5(CONCAT(?,?)), ?)';

      $salt = md5(uniqid());
      $salt = substr($salt, 0, 2);

      $master = DBConfig::Master();

      DBUtils::Begin($master, $trans);

      if (!self::isMailAvailable($mail, true)) {
          DBUtils::Rollback($master, $trans);
          return null;
      }

      $user = UserModel::Create($name, true);

      DBUtils::Insert($master, $query, 'issss', $user, $mail, $salt, $pass, $salt);
      DBUtils::Commit($master, $trans);
   
      $token = $user;

      return $token;
  }

And the new user model:

<?php // models/user.php

  class UserModel
  {
      // Create a new user with a name. Return user identifier.
      public static function Create($name, $transaction = false)
      {
          $query = '`user`(`user_name`) VALUES (?)';
          $user = DBUtils::Insert(DBConfig::Master(), $query, 's', $name);
          return $user;
      }

      // Get the name of a user.
      public static function GetName($id, $transaction = false)
      {
          $query = '`user_name` FROM `user` WHERE `user_id` = ?';
          $database = DBConfig::Get($transaction);
          return DBUtils::SelectOne($database, $query, 'i', $id);
      }

      // Get all active users (and their names)
      // Columns: id, name
      public static function GetActiveUsers($transaction = false)
      {
          $query = '`user_id` AS `id`, ' .
                   '`user_name` AS `name` ' .
                   'FROM `user`';

          $database = DBConfig::Get($transaction);
          return DBUtils::Select($database, $query);
      }
  }

There are no inherent difficulties to this model, and the implementation is fairly straightforward. A little more complex is the group model, which needs to register the creator of a group as a moderator for that group, which implies some level of interaction with the group-users model. So, I design the group-users model first to allow adding a creator, a moderator or a contributor to a group, or removing an user from a group altogether:

<?php // model/groupusers.php

  class GroupUsersModel
  {
      const MODERATOR = 2;
      const CONTRIBUTOR = 1;

      // Set an user as a moderator
      // Return false if the author is not allowed to do that
      public static function SetModerator($group, $user, $author, 
                                          $transaction = false)
      {
          $query = '`group_users(`group_id`,`user_id`,`rights`) ' . 
                   'VALUES (?,?,?)';

          DBUtils::Begin(DBConfig::Master(), $transaction);

          if (self::GetRights($group, $user, true) < self::MODERATOR) {
              DBUtils::Rollback(DBConfig::Master(), $transaction);
              return false;
          }

          DBUtils::Replace(DBConfig::Master(), $query, 'iii', 
                           $group, $user, self::MODERATOR);

          DBUtils::Commit(DBConfig::Master(), $transaction);

          return true;
      }

      // Set an user as a contributor
      // Return false if the author is not allowed to do that
      public static function SetContributor($group, $user, $author,
                                            $transaction = false)
      {
          $query = '`group_users(`group_id`,`user_id`,`rights`) ' .
                   'VALUES (?,?,?)';

          DBUtils::Begin(DBConfig::Master(), $transaction);

          if (self::GetRights($group, $user, true) < self::MODERATOR) {
              DBUtils::Rollback(DBConfig::Master(), $transaction);
              return false;
          }

          DBUtils::Replace(DBConfig::Master(), $query, 'iii', 
                           $group, $user, self::CONTRIBUTOR);

          DBUtils::Commit(DBConfig::Master(), $transaction);

          return true;
      }

      // Return false if the author is not allowed to do that
      // Exclude an user from a group
      public static function Exclude($group, $user, $author, $transaction = false)
      {
          $query = '`group_users` WHERE `group_id` = ? AND `user_id` = ?';

          DBUtils::Begin(DBConfig::Master(), $transaction);

          if (self::GetRights($group, $user, true) < self::MODERATOR) {
              DBUtils::Rollback(DBConfig::Master(), $transaction);
              return false;
          }

          DBUtils::Delete(DBConfig::Master(), $query, 'ii', $group, $user);

          DBUtils::Commit(DBConfig::Master(), $transaction);

          return true;
      }

      // Set creator. Automatic success. Does not check user
      // rights, do not use unless group is new!
      public static function SetCreator($group, $creator, $transaction = false)
      {
          $query = '`group_users(`group_id`,`user_id`,`rights`) ' .
                   'VALUES (?,?,?)';

          DBUtils::Insert(DBConfig::Master(), $query, 'iii',
                          $group, $user, self::MODERATOR);
      }

      // Determine user rights over a group. NULL if none.
      public static function GetRights($group, $user, $transaction = false)
      {
          $query = '`rights` FROM `group_users` WHERE `group_id` = ? ' .
                   'AND `user_id` = ?';

          $database = DBConfig::Get($transaction);

          return DBUtils::SelectOne($database, $query, 'ii',
                                    $group, $user);
      }
  }

Notice how the function GroupUsersModel::GetRights works on the slave when called in permissive mode from outside the model, but works on the master and within a transaction when called in paranoid mode from within other model functions. The “SetCreator” function is somewhat of a safety fault, since it can be used to turn any user into a moderator. This is why it should only be called when a group is created. As the system improves, it will be possible to add a constraint for creating groups (user is website moderator) that can be checked when doing this.

This provides the necessary code for the group model:

<?php // models/group.php

  class GroupModel
  {
      // Create a new group and set a moderator
      public static function Create($name, $creator, $transaction = false)
      {
          $master = DBConfig::Master();

          DBUtils::Begin($master, $transaction);

          $query = '`group`(`group_name`) VALUES (?)';
          $group = DBUtils::Insert($master, $query, 's', $name);

          GroupUsersModel::SetCreator($group, $creator, true);

          DBUtils::Commit($master, $transaction);

          return $group;
      }

      // Get the name of a group
      public static function GetName($id, $transaction = false)
      {
          $query = '`group_name` FROM `group` WHERE `group_id` = ?';
          $database = DBConfig::Get($transaction);
          return DBUtils::SelectOne($database, $query, 'i', $id);
      }

      // Get the active groups
      public static function GetActiveGroups($transaction = false)
      {
          $query = '`group_id` AS `id`, ' . 
                   '`group_name` AS `name` ' .
                   'FROM `group`';

          $database = DBConfig::Get($transaction);
          return DBUtils::Select($database, $query);
      }
  }

Aside from wrapping the GroupUsersModel::SetCreator call within a transaction, there’s nothing surprising about this code.

The real meat of the subject appears when dealing with the item and revision models. In fact, it’s complex enough to warrant some serious planning ahead.

• The item model is responsible for creating and querying items. Creation involves only a creating user and an initial piece of text (since that is all that is provided by the front-end input form anyway). Post-creation events, such as assigning the item to a group or user, only happen later on and would be performed using the item revision model.

• Querying for items is a complex task that involves three sub-tasks: identifying which items the user wants to see, keeping only the items that the user is allowed to see, and extracting the data that has to be displayed. Of the three, only the first is expected to change significantly, which means the other two should be factored out in one way or another.

• It can be useful, for the purpose of applying or canceling revision, to determine whether a certain user has contributor or moderator rights over a certain item beyond the mere restriction of read access.

Creating an object is nothing difficult…

  public static function Create($author, $text, $transaction = false)
  {
      $master = DBConfig::Master();

      DBUtils::Begin($master, $transaction);

      $query = '`item`(`author_id`, `user_id`, `group_id`, `created_on`, ' . 
               '`altered_on`, `item_text`) VALUES (?, NULL, NULL, NOW(), ' .
               'NOW(), ?)';

      $item = DBUtils::Insert($master, $query, 'is', $author, $text);

      ItemRevisionModel::Create($item, $author, true);

      DBUtils::Commit($master, $transaction);

      return $item;
  }

… as long as you assume that the item revision model is available and can insert the “item created” event appropriately into the item history.

On the other hand, querying is complex. In fact, it’s so complex that I decided to give up on stored procedures precisely because they could not correctly (and elegantly) handle the vast complexity that queries can achieve. A search through the items will usually be filtered as such:

• Only items visible by the user (always, because that’s an elementary security feature).
• Only active (non-deleted, non-archived) items.
• Only archived (non-deleted) items.
• Only deleted items.
• Only unsolved (non-deleted, non-archived) items.
• Ordered by last modification date.
• Only items created by a certain user.
• Only items assigned to a certain user (or no user).
• Only items assigned to a certain group (or no group).

Even worse, as the tag, workflow and search features go online in the future, searching for an item might start involving other tables. Not to mention that when this happens, it might be interesting to extract other information from the query, such as search relevance or highlighted keywords.

So, given this complexity, it becomes necessary to start constructing queries. The most elementary building block would be the main item extraction query, in the item model:

  public static function GetItems($user, array $filter, $fields = null,
                                  $transaction = false)
  {
      $fields = $fields ? ',' . $fields : '';

      $finish = array_shift($filter);
      $types  = array_shift($filter);
      $params = $filter;

      $query  =
<<<SQL
      item.item_id      as `id`,
      item.author_id    as author,
      JAUTH.user_name   as author_name,
      item.user_id      as `user`,
      JUSER.user_name   as user_name,
      item.group_id     as `group`,
      JGROUP.group_name as group_name,
      item.created_on   as `reated_on,
      item.altered_on   as altered_on,
      item.is_solved    as solved,
      item.is_deleted   as deleted,
      item.is_public    as public,
      item.is_archive   as archived,
      item.item_text    as `text`,
      item.attached     as attached,
      item.comments     as comments
      $fields
      FROM ( SELECT * FROM item
             WHERE author_id = ?
             OR user_id = ?
             OR is_public = 1
             OR group_id IN ( SELECT group_id FROM group_users
                                WHERE user_id = ?)
             OR item_id IN ( SELECT item_id FROM revision
                               WHERE `type` = 'show_to'
                               AND moderated_by IS NULL
                               AND user_to = ?)
             ) item
      LEFT OUTER JOIN `user` JAUTH ON JAUTH.user_id = item.author_id
      LEFT OUTER JOIN `user` JUSER ON JUSER.user_id = item.user_id
      LEFT OUTER JOIN `group` JGROUP ON JGROUP.group_id = item.group_id
      $finish
SQL;

      $types = 'iiii' . $types;
      $params = array($user, $user, $user, $user, $params);

      $database = DBConfig::Get($transaction);
      return DBUtils::Select($database, $query, $types, $params);
  }

This is a configurable data extraction query. It constructs the table of all items visible by the current user (by means of a subquery), joins that table with the user and group tables to extract the names (if any) of its author, assigned user, and group, then filters it based on a subquery provided by the user to keep only the interesting data, and finally selects a predetermined set of fields plus any additional fields the user has requested.

There is inherent danger in letting the users of the model have their way with the underlying SQL. They could get some bits wrong (leading to runtime errors that the model should protect against) and any changes to the underlying model would have to be propagated throughout the system when the model should contain them. Yet tightly wrapping all the possible search flexibility behind the model would require re-designing almost all of SQL from scratch, not to mention the difficulty of keeping things reasonably optimal (after all, getting a list of items regardless of filtering is a single request).

The middle ground is to provide query building blocks as part of the model so that as much structural information as possible is hidden away. So, for instance:

const ARCHIVED =
  'item.is_archive = 1 AND item.is_deleted = 0';

const DELETED  =
  'item.is_deleted = 1';

const SOLVED   = 
  'item.is_archive = 0 AND item.is_deleted = 0 AND item.s_solved = 1';

const UNSOLVED =
  'item.is_archive = 0 AND item.is_deleted = 0 AND item.is_solved = 0';

const ACTIVE   =
  'item.is_archive = 0 AND item.is_deleted = 0';

public static function FilterAuthor($author)
{
    return array('item.author_id = ?', 'i', $author);
}

public static function FilterUser($user)
{
    return array('item.user_id = ?', 'i', $user);
}

public static function FilterGroup($group)
{
    return array('item.group_id = ?', 'i', $user);
}

The user can then construct these “query pieces” and concatenate them together into a proper query they can run, while also adding SQL of their own to the mix. Ultimately, it’s designed to look like this:

  $filter = array(
    'WHERE',
    ItemModel::ACTIVE, 
    'AND',
    ItemModel::FilterAuthor($user), 
    'GROUP BY `altered_on` DESC',
    'LIMIT 30'
  );

  $filter = DBUtils::Combine($filter);

  $data = ItemModel::GetItems($user, $filter);

Which happens thanks to the DBUtils::Combine:

  public static function Combine(array $filters)
  {
      $query = ' ';
      $types = '';
      $param = array();

      foreach ($filters as $filter) {
          if (!is_array($filter)) {
              $query .= $filter . ' ';
              continue;
          }

          $query .= array_shift($filter) . ' ';
          $types .= array_shift($filter);
          $param[] = $filter;
      }

      return array($query, $types, $param);
  }

So how does this work? I decide that an SQL query is a concatenation of SQL segments, and every SQL segment can be itself a concatenation of smaller SQL segments. To construct a segment, there are therefore three possibilities:

• A simple string can be a segment. This is interesting when creating simple segments like “WHILE”, “AND”, ORDER BY `column`” and so on. This is quite unsurprisingly represented in PHP as a normal string.

• A string that contains parameters that may be provided by an external user or some other unreliable and dangerous source. To escape these parameters, one uses the classic parameter syntax and inserts question marks where the parameters should be, provides the types of the parameters (‘i’, ‘s’) and the values. This is all provided as an array, like so:array("id = ? AND name = ?", "is", $id, $name)

• Concatenate one or more segments, by putting them into an array and passing them to DBUtil::Combine. The function constructs a new segment by appending the segments in order, and even inserts spaces in-between segments so you don’t have to remember adding them yourself.

All of this remains perfectly safe in terms of incoming user data, which is always appropriately handled by the prepared statement implementation. The danger is a mismatch between parameters and arguments, but this should be fairly easy to detect unless there are two opposite mismatches in two distinct segments.

While we’re at it, an elementary application that could get its own function is extracting a single item:

  public static function GetItem($item, $user, $transaction = false)
  {
      $query = 'WHERE `item`.`item_id` = ?';
      return self::GetItems($user, array($query, 'i', $id), null,
                            $transaction);
  }

And of course, as promised, the code to determine whether an user has contributor or moderator rights over an item:

  public static function IsItemModerator($item, $user, $transaction = false)
  {
      $query = '`item` I
WHERE `item_id` = ?
  AND (    `author_id` = ?
        OR `user_id`   = ?
        OR EXISTS(SELECT * FROM `group_users` G
                   WHERE G.`group_id` = I.`group_id`
                   WHERE `user_id` = ?
                     AND `rights`  = ?))';

      $database = DBConfig::Get($transaction);
      $count = DBUtils::Count($database, $query, 'iiiii', 
                              $item, $user, $user, $user, 
                              GroupUsersModel::MODERATOR);

      return($count != 0);
  }

  public static function IsItemContributor($item, $user, $transaction = false)
  {
      $query = '`item` I
WHERE `item_id` = ?
  AND (    `author_id` = ?
        OR `user_id`   = ?
        OR `is_public` = 1
        OR EXISTS(SELECT `group_id` FROM `group_users` G
                   WHERE G.`group_id` = I.`group_id`
                     AND `user_id` = ?)
        OR EXISTS(SELECT * FROM `revision`
                   WHERE `item_id` = ?
                     AND `type` = \'show_to\'
                     AND `moderated_by` IS NULL
                     AND `user_to` = ?))';

      $database = DBConfig::Get($transaction);
      $count = DBUtils::Count($database, $query, 'iiiiii',
                              $item, $user, $user, $user, $item, $user);

      return($count != 0);
  }

So items can be created and extracted—but how are they modified?

← Item Model | Update and Delete →