support.install in Support Ticketing System 6

File

support.install

<?php

/**
 * Helpdesk / support ticket tracking system database schema.
 * Copyright (c) 2008-2009 Tag1 Consulting, Inc <[e-mail]>.
 */

/**
 * Support database schema definition.
 */
function support_schema() {
  $schema['support_client'] = array(
    'description' => 'Stores information about support clients.',
    'fields' => array(
      'clid' => array(
        'type' => 'serial',
        'not null' => TRUE,
        'unsigned' => TRUE,
      ),
      'name' => array(
        'type' => 'varchar',
        'length' => 128,
        'not null' => TRUE,
        'default' => '',
      ),
      'path' => array(
        'type' => 'varchar',
        'length' => 32,
        'not null' => TRUE,
        'default' => '',
      ),
      'status' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
        'default' => 0,
      ),
      'parent' => array(
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
      ),
      'integrate_email' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
        'default' => 0,
      ),
      'server_name' => array(
        'type' => 'varchar',
        'length' => 64,
        'not null' => TRUE,
        'default' => '',
      ),
      'server_username' => array(
        'type' => 'varchar',
        'length' => 64,
        'not null' => TRUE,
        'default' => '',
      ),
      'server_password' => array(
        'type' => 'varchar',
        'length' => 64,
        'not null' => TRUE,
        'default' => '',
      ),
      'mailfrom' => array(
        'type' => 'varchar',
        'length' => 64,
        'not null' => TRUE,
        'default' => '',
      ),
      'mailbox' => array(
        'type' => 'varchar',
        'length' => 64,
        'not null' => TRUE,
        'default' => '',
      ),
      'protocol' => array(
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'extra' => array(
        'type' => 'varchar',
        'length' => 128,
        'not null' => TRUE,
        'default' => '',
      ),
      'port' => array(
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'autoassign' => array(
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
        'description' => 'Optionally assign new tickets to this user.',
      ),
      'autosubscribe' => array(
        'type' => 'text',
      ),
      'domains' => array(
        'type' => 'text',
        'size' => 'big',
      ),
      'notes' => array(
        'type' => 'text',
        'size' => 'big',
      ),
      'thread_subject' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
        'default' => 0,
        'description' => 'Optionally configure per-client subject threading settings.',
      ),
      'user_creation' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
        'default' => 0,
        'description' => 'Optionally configure per-client user creation settings.',
      ),
    ),
    'primary key' => array(
      'clid',
    ),
    'indexes' => array(
      'status' => array(
        'status',
      ),
      'autoassign' => array(
        'autoassign',
      ),
    ),
    'unique keys' => array(
      'name' => array(
        'name',
      ),
    ),
  );
  $schema['support_ticket'] = array(
    'description' => 'Stores information about support tickets.',
    'fields' => array(
      'nid' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'message_id' => array(
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'state' => array(
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'priority' => array(
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'client' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'assigned' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array(
      'nid',
    ),
    'indexes' => array(
      'message_id' => array(
        'message_id',
      ),
      'state' => array(
        'state',
      ),
    ),
  );
  $schema['support_ticket_comment'] = array(
    'description' => 'Stores information about support ticket comments.',
    'fields' => array(
      'cid' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'message_id' => array(
        'type' => 'varchar',
        'length' => 255,
        'not null' => TRUE,
        'default' => '',
      ),
      'state' => array(
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'priority' => array(
        'type' => 'int',
        'size' => 'small',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'client' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'assigned' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array(
      'cid',
    ),
    'indexes' => array(
      'message_id' => array(
        'message_id',
      ),
    ),
  );
  $schema['support_assigned'] = array(
    'description' => 'Stores information about who tickets are assigned to.',
    'fields' => array(
      'nid' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'uid' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array(
      'nid',
      'uid',
    ),
    'indexes' => array(
      'support_assigned_uid' => array(
        'uid',
      ),
    ),
  );
  $schema['support_states'] = array(
    'description' => 'All available support states.',
    'fields' => array(
      'sid' => array(
        'type' => 'serial',
        'not null' => TRUE,
        'unsigned' => TRUE,
      ),
      'state' => array(
        'type' => 'varchar',
        'length' => 32,
        'not null' => TRUE,
      ),
      'phase1' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'phase2' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'isdefault' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'isclosed' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'weight' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array(
      'sid',
    ),
    'indexes' => array(
      'weight_state' => array(
        'weight',
        'state',
      ),
      'isdefault' => array(
        'isdefault',
      ),
      'isclosed' => array(
        'isclosed',
      ),
    ),
  );
  $schema['support_priority'] = array(
    'description' => 'All available support priorities.',
    'fields' => array(
      'pid' => array(
        'type' => 'serial',
        'not null' => TRUE,
        'unsigned' => TRUE,
      ),
      'priority' => array(
        'type' => 'varchar',
        'length' => 32,
        'not null' => TRUE,
      ),
      'isdefault' => array(
        'type' => 'int',
        'size' => 'tiny',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
      'weight' => array(
        'type' => 'int',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'default' => 0,
      ),
    ),
    'primary key' => array(
      'pid',
    ),
    'indexes' => array(
      'weight_priority' => array(
        'weight',
        'priority',
      ),
      'isdefault' => array(
        'isdefault',
      ),
    ),
  );
  return $schema;
}

/**
 * Install support database schema.
 */
function support_install() {

  // Check if we're upgrading a previous jobtrack installation.
  if (db_table_exists('jobtrack_client')) {
    $ret = array();
    db_rename_table($ret, 'jobtrack_client', 'support_client');
    db_rename_table($ret, 'jobtrack_ticket', 'support_ticket');
    db_rename_table($ret, 'jobtrack_ticket_comment', 'support_ticket_comment');
    db_rename_table($ret, 'jobtrack_assigned', 'support_assigned');
    db_rename_table($ret, 'jobtrack_states', 'support_states');
    db_rename_table($ret, 'jobtrack_priority', 'support_priority');
    db_query("UPDATE {node} SET type = 'support_ticket' WHERE type = 'jobtrack_ticket'");
    cache_clear_all();
    menu_rebuild();
  }
  else {
    drupal_install_schema('support');

    // default
    db_query("INSERT INTO {support_states} (state, weight, phase1, phase2, isdefault) VALUES('new', 0, 1, 0, 1)");
    db_query("INSERT INTO {support_states} (state, weight, phase1, phase2) VALUES('active', 1, 0, 1)");
    db_query("INSERT INTO {support_states} (state, weight, phase1, phase2) VALUES('pending', 2, 0, 1)");
    db_query("INSERT INTO {support_states} (state, weight, phase1, phase2, isclosed) VALUES('closed', 3, 0, 1, 1)");

    // default priorities
    db_query("INSERT INTO {support_priority} (priority, weight) VALUES('low', 0)");
    db_query("INSERT INTO {support_priority} (priority, weight, isdefault) VALUES('normal', 1, 1)");
    db_query("INSERT INTO {support_priority} (priority, weight) VALUES('high', 2)");
    db_query("INSERT INTO {support_priority} (priority, weight) VALUES('critical', 3)");

    // default comment settings
    variable_set('comment_default_mode_support_ticket', COMMENT_MODE_THREADED_EXPANDED);
    variable_set('comment_default_order_support_ticket', COMMENT_ORDER_OLDEST_FIRST);

    // Set default weight
    db_query("UPDATE {system} SET weight = 10 WHERE name = 'support'");
  }
}

/**
 * Cleanup.
 */
function support_uninstall() {
  $result = db_query("SELECT nid FROM {node} WHERE type = 'support_ticket'");
  while ($node = db_fetch_object($result)) {
    node_delete($node->nid);
  }
  drupal_uninstall_schema('support');
}

/**
 * Introduce "autoassign" field for storing a per-client autoassign value.
 */
function support_update_6001() {
  $ret = array();
  db_add_field($ret, 'support_client', 'autoassign', array(
    'type' => 'varchar',
    'length' => 255,
    'not null' => TRUE,
    'default' => '',
    'description' => 'Optionally assign new tickets to this user.',
  ), array(
    'indexes' => array(
      'autoassign' => array(
        'autoassign',
      ),
    ),
  ));
  if (variable_get('support_autoassign_ticket', 0) == 0) {
    variable_set('support_autoassign_ticket', '<nobody>');
  }
  else {
    if (variable_get('support_autoassign_ticket', 0) == 1) {
      variable_set('support_autoassign_ticket', '<creator>');
    }
    else {
      variable_del('support_autoassign_ticket');
    }
  }
  drupal_flush_all_caches();
  return $ret;
}

/**
 * Introduce "thread_subject" field for configured per-client thread settings.
 */
function support_update_6002() {
  $ret = array();
  db_add_field($ret, 'support_client', 'thread_subject', array(
    'type' => 'int',
    'size' => 'tiny',
    'not null' => TRUE,
    'default' => 0,
    'description' => 'Optionally configure per-client subject threading settings.',
  ));
  return $ret;
}

/**
 * Flush caches to support new theme functions
 */
function support_update_6003() {
  drupal_flush_all_caches();
  return array();
}

/**
 * Introduce "user_creation" field for configured per-client user creation
 * settings.
 */
function support_update_6004() {
  $ret = array();
  db_add_field($ret, 'support_client', 'user_creation', array(
    'type' => 'int',
    'size' => 'tiny',
    'not null' => TRUE,
    'default' => 0,
    'description' => 'Optionally configure per-client user creation settings.',
  ));
  return $ret;
}

/**
 * Flush caches to support updated theme functions
 */
function support_update_6005() {
  drupal_flush_all_caches();
  return array();
}

/**
 * Add support for nested clients.
 */
function support_update_6006() {
  $ret = array();
  db_add_field($ret, 'support_client', 'parent', array(
    'type' => 'int',
    'not null' => TRUE,
    'default' => 0,
    'description' => 'Optionally nest clients within clients.',
  ));
  drupal_flush_all_caches();
  return $ret;
}

/**
 * Rebuild menu to register new autosubscribe autocomplete permissions.
 */
function support_update_6007() {
  drupal_flush_all_caches();
  menu_rebuild();
  return array();
}

/**
 * Remove unnecessary 'active' field from {support_assigned} table.
 */
function support_update_6008() {
  $ret = array();

  // Remove inactive entries from table.
  $ret[] = update_sql('DELETE FROM {support_assigned} WHERE active = 0');

  // Remove unnecessary status field.
  db_drop_field($ret, 'support_assigned', 'active');
  return $ret;
}

/**
 * Make sure the 'active' field is removed from new installs.
 */
function support_update_6009() {
  $ret = array();
  if (db_column_exists('support_assigned', 'active')) {
    $ret = support_update_6008();
  }
  return $ret;
}

/**
 * Fix a performance issue with the primary key on {support_assigned}.
 * Add an index on uid to {support_assigned}.
 */
function support_update_6010() {
  $ret = array();
  db_drop_primary_key($ret, 'support_assigned');
  db_add_primary_key($ret, 'support_assigned', array(
    'nid',
    'uid',
  ));
  db_add_index($ret, 'support_assigned', 'support_assigned_uid', array(
    'uid',
  ));
  return $ret;
}

/**
 * Rebuild menu to register client deletion confirmation.
 */
function support_update_6011() {
  drupal_flush_all_caches();
  menu_rebuild();
  return array();
}

Functions

Name	Description
support_install	Install support database schema.
support_schema	Support database schema definition.
support_uninstall	Cleanup.
support_update_6001	Introduce "autoassign" field for storing a per-client autoassign value.
support_update_6002	Introduce "thread_subject" field for configured per-client thread settings.
support_update_6003	Flush caches to support new theme functions
support_update_6004	Introduce "user_creation" field for configured per-client user creation settings.
support_update_6005	Flush caches to support updated theme functions
support_update_6006	Add support for nested clients.
support_update_6007	Rebuild menu to register new autosubscribe autocomplete permissions.
support_update_6008	Remove unnecessary 'active' field from {support_assigned} table.
support_update_6009	Make sure the 'active' field is removed from new installs.
support_update_6010	Fix a performance issue with the primary key on {support_assigned}. Add an index on uid to {support_assigned}.
support_update_6011	Rebuild menu to register client deletion confirmation.