19. Update and Delete

← Create and Read | AJAX (Telamonian) →

I’m sort of cheating with this section title: there is no deletion. For obvious data loss reasons, I want to make deletion as difficult as possible—of course, it will still be possible to simulate “move to recycle bin” deletion that keeps the items around where they can’t annoy you, but that kind of deletion is actually a simple update.

This does mean that there will be no way to decrease memory usage in the system, as the tables will get larger and any attempt to delete data will only result in more lines being added to the revision table. The problem with deletion is that:

Items can be linked to from other items. What happens to the links when the referenced item is permanently deleted?
Who can delete items? What if someone wanted to keep a copy of the item because it was interesting? The entire JITBrain philosophy is that you can cancel any mistakes your coworkers make, which is incompatible with deletion.

These problems will eventually have to be solved—not everyone has several gigabytes of storage available—but they are not a priority.

The real priority is allowing the user to actually do things with their items. Namely:

Create items (ItemModel references ItemRevisionModel for this, so it doesn’t work yet).
Solve items or mark them back as unsolved.
Delete and un-delete items.
Archive or un-archive items.
Make public, or remove public status.
Attach a file to an item.
Comment on an item.
Assign an item to an user.
Assign an item to a group.
Reference an item from another (see also).
Mark two items as duplicates of one another.
Show an item to an user.
Edit the original text of an item.
Shake an item to reset its “last modified” timestamp.

Others will come later, but these are the elemental, fundamental operations we want to let the users do with their items. But what happens, exactly, when one of these operations is performed?

Save the operation as a line in the revision table. This includes at the very least the item, the user performing the operation and the type of operation. It may also include additional data, if the operation has parameters.

If the operation has any visible effects on items, update the corresponding fields in the item table. For instance, editing an item changes the text in the item table, attaching a file increments the attachment counter in the item table, and showing an item to an user has no effect on the item table.

If the operation is considered relevant, also change the ‘last modified’ timestamp in the item table. The key idea is, do you want the operation to push the item up to the top of the active item list? I know that an un-deleted item deserves being pushed up, but I’m not certain that adding duplicates, publishing or even solving items should push them up. Relevance is something to be determined when working on the system—and if your favourite operation doesn’t push your item up, you can always shake it.

Some operations, such as editing the original text of an item, may require moderator access to the item. Other operations merely require contributor access. In both cases, the access level must be checked (within a transaction, of course) right before the modification is applied.

So, it turns out, most operations will be performing these four steps, so I should provide a single database-altering function that performs them, and simply let every operation configure that function through an argument to have it behave appropriately. That function is ItemRevisionModel::_apply:

  // -> revision = null    : the type of revision, with arguments
  // -> relevant = false   : whether the altered_on is updated
  // -> update   = null    : the update query, with parameters and types
  // -> restrict = false   : moderators only?
  private static function _apply($item, $user, $config, $transaction = false)
  {
      $master = DBConfig::Master();
      DBUtils::Begin($master, $transaction);

      $restrict = array_key_exists('restrict', $config)
                ? $config['restrict'] : false;

      if ($restrict) {
          if (!ItemModel::IsItemModerator($item, $user)) {
              DBUtils::Rollback($master, $transaction);
              return false;
          }
      }

      else {
          if (!ItemModel::IsItemContributor($item, $user)) {
              DBUtils::Rollback($master, $transaction);
              return false;
          }
      }

      $revision = array_key_exists('revision', $config)
                ? $config['revision'] : null;

      if (isset($revision)) {
          $type = array_shift($revision);
          $fields = self::Fields($type);

          $params = '';
          $inserted = array();
          $args = '';
          $types = 'iis';
          foreach ($fields as $field => $type) {
              $params    .= ',?';
              $args      .= ",$field";
              $types     .= $type;
              $inserted []= array_shift($revision);
          }

          $query = "`revision` (`item_id`, `user_id`, `type`, `date`$args) " .
                   "VALUES(?,?,?,NOW()$params)";

          DBUtils::Insert($master, $query, $types,
                          $item, $user, $type, $inserted);
      }

      $relevant = array_key_exists('relevant', $config)
                ? $config['relevant'] : null;

      $update   = array_key_exists('update', $config)
                ? $config['update'] : null;

      if ($relevant || $update) {
          $do = array();
          if ($relevant) $do[] = '`altered_on` = NOW()';
          if ($update)   $do[] = array_shift($update);

          $query = "`item` SET " . implode(',', $do) . " WHERE `item_id` = ?";
          $types = ($update ? array_shift($update) : '') . 'i';
          $param = $update ? $update : array();

          DBUtils::Update($master, $query, $types, $param, $item);
      }

      DBUtils::Commit($master, $transaction);
      return true;
  }

This function determines dynamically which fields of the revision table should be updated based on the type of revision being inserted. This is done using the ItemRevisionModel::Fields function:

  public static function Fields($type)
  {
      switch ($type) {
          case self::CREATED:
              return array();
          case self::SHOWN_TO_USER:
          case self::ASSIGNED_TO_USER:
              return array('user_to' => 'i');
          case self::ASSIGNED_TO_GROUP:
              return array('group_to' => 'i');
          case self::COMMENTED_ON:
          case self::FILE_ATTACHED:
          case self::EDITED:
              return array('text' => 's');
          case self::SOLVED:
          case self::DELETED:
          case self::PUBLISHED:
          case self::ARCHIVED:
              return array('status' => 'i');
          case self::RELATED_TO_ITEM:
              return array('relationship' => 's', 'item_to' => 'i');
          default:
              assert(false);
      }
  }

Once all of this has been set up, the functions that perform the actual operations tend to be a boring sequence of configuring the _apply function:

  public static function Create($item, $user, $transaction = false)
  {
      $config = array(
        'revision' => array(self::CREATED)
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function Solve($item, $user, $solved, $transaction = false)
  {
      $config = array(
        'revision' => array(self::SOLVED, (int)$solved),
        'update'   => array('`is_solved` = ?', 'i', (int)$solved)
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function Delete($item, $user, $deleted, $transaction = false)
  {
      $config = array(
        'revision' => array(self::DELETED, (int)$deleted),
        'update'   => array('`is_deleted` = ?', 'i', (int)$deleted), 
        'relevant' => true
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function Archive($item, $user, $archived, $transaction = false)
  {
      $config = array(
        'revision' => array(self::ARCHIVED, (int)$archived),
        'update'   => array('`is_archive` = ?', 'i', (int)$archived),
        'relevant' => true
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function Publish($item, $user, $public, $transaction = false)
  {
      $config = array(
        'revision' => array(self::PUBLISHED, (int)$public), 
        'update'   => array('`is_public` = ?', 'i', (int)$public), 
        'relevant' => true
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function Attach($item, $user, $uri, $transaction = false)
  {
      $config = array(
        'revision' => array(self::FILE_ATTACHED, $uri),
        'update'   => array('`attached` = 1 + `attached`'),
        'relevant' => true
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function Comment($item, $user, $comment, $transaction = false)
  {
      $config = array(
        'revision' => array(self::COMMENTED_ON, $comment), 
        'update'   => array('`comments` = 1 + `comments`'), 
        'relevant' => true
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function AssignToUser($item, $user, $to, $transaction = false)
  {
      $config = array(
        'revision' => array(self::ASSIGN_TO_USER, $to),
        'update'   => array('`user_id` = ?', 'i', $to), 
        'relevant' => true
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function AssignToGroup($item, $user, $to, $transaction = false)
  {
      $config = array(
        'revision' => array(self::ASSIGN_TO_GROUP, $to),
        'update'   => array('`group_id` = ?', 'i', $to), 
        'relevant' => true
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function SeeAlso($item, $user, $also, $transaction = false)
  {
      $config = array(
        'revision' => array(self::RELATED_TO_ITEM, self::SEE_ALSO, $also)
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function Duplicate($item, $user, $dup, $transaction = false)
  {
      $master = DBConfig::Get($transaction);
      DBUtils::Begin($master, $transaction);

      $config_a = array(
        'revision' => array(self::RELATED_TO_ITEM, self::DUPLICATE, $dup)
      );

      $config_b = array(
        'revision' => array(self::RELATED_TO_ITEM, self::DUPLICATE, $item)
      );

      if (self::_apply($item, $user, $config_a, true) && 
          self::_apply($dup, $user, $config_b, true))
      {
          DBUtils::Commit($master, $transaction);
          return true;
      }

      DBUtils::Rollback($master, $transaction);
      return false;
  }

  public static function Show($item, $user, $to, $transaction = false)
  {
      $config = array(
        'revision' => array(self::SHOWN_TO_USER, $to)
      );

      return self::_apply($item, $user, $config, $transaction);
  }

  public static function Edit($item, $user, $text, $transaction = false)
  {
      $master = DBConfig::Master();

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

      $current = ItemModel::GetItem($item, $user, true);

      if (count($current) != 1) {
          DBUtils::Rollback($master, $transaction);
          return false;
      }

      $config = array(
        'revision' => array(self::EDITED, $current[0]['text']), 
        'update'   => array('`text` = ?', 's', $text), 
        'relevant' => true,
        'restrict' => true
      );

      $success = self::_apply($item, $user, $config, true);

      if ($success)
          DBUtils::Commit($master, $transaction);
      else
          DBUtils::Rollback($master, $transaction);

      return $success;
  }

  public static function Shake($item, $user, $transaction = false)
  {
      $config = array(
        'relevant' => true,
        'revision' => array(self::SHAKEN)
      );

      return self::_apply($item, $user, $config, $transaction);
  }

The only creative bits are the “edit” function which needs to extract the current contents of the item so that they can be stored in the revision, and the “duplicate” function which needs to mark two items as duplicates instead of only one.

The ability to perform the operations in a transaction is golden: you don’t need to check beforehand that both items are accessible, since if one of the two attempts fails the other (if already done) will be canceled automatically.

Finally, since it will become useful soon, the ability to get all revisions for an item:

  // Get revisions for item. Limit applies if not null. Returns
  // null if item is not visible.
  public static function Get($item, $user, $limit = null, $transaction = false)
  {
      $database = DBConfig::Get($transaction);

      $visible = IsItemContributor($user, $item, $transaction);

      if (!$visible)
          return null;

      $query = '* FROM `revision` WHERE `item_id` = ? ' .
               'ORDER BY `revision_id` DESC' .
               (is_int($limit) ? " LIMIT $limit" : '');

      return DBUtils::Select($database, $query, 'i', $item);
  }

Since a given item may have a lot of revisions, it’s possible to specify a limit to get only the latest elements. The function returns elements in reverse order (most recent first) .

So, how does all this data get displayed, and how does the user perform the changes?

← Create and Read | AJAX (Telamonian) →

Nicollet.Net

19. Update and Delete

0 Responses to “19. Update and Delete”

Leave a Reply Cancel reply

New Here? Read These First!

Recent Comments

Recent Ramblings

The Good Old Days