upgrade/0.9.16/06_google_apps.sql

   1 -- This SQL table comes from the 'gapps-daemon' project.
   2
   3 -- Table `gapps_reporting`.
   4 -- The table contains daily statistics of the Google Apps domain. They are
   5 -- obtained with the Summary and Actvity reports of the Google Apps Reorting
   6 -- API.
   7 CREATE TABLE IF NOT EXISTS `gapps_reporting` (
   8   date DATE NOT NULL,
   9   num_accounts INTEGER  UNSIGNED DEFAULT NULL,
  10   count_1_day_actives INTEGER UNSIGNED DEFAULT NULL,
  11   count_7_day_actives INTEGER UNSIGNED DEFAULT NULL,
  12   count_14_day_actives INTEGER UNSIGNED DEFAULT NULL,
  13   count_30_day_actives INTEGER UNSIGNED DEFAULT NULL,
  14   count_30_day_idle INTEGER UNSIGNED DEFAULT NULL,
  15   count_60_day_idle INTEGER UNSIGNED DEFAULT NULL,
  16   count_90_day_idle INTEGER UNSIGNED DEFAULT NULL,
  17   usage_in_bytes BIGINT UNSIGNED DEFAULT NULL,
  18   quota_in_mb INTEGER  UNSIGNED DEFAULT NULL,
  19   PRIMARY KEY(date)
  20 ) CHARSET=utf8;
  21
  22 -- Table `gapps_accounts`.
  23 -- Holds the Google Apps account list, ie. a list of all registered accounts on
  24 -- the Google Apps domain.
  25 CREATE TABLE IF NOT EXISTS `gapps_accounts` (
  26   -- Application-specific fields.
  27   l_userid SMALLINT UNSIGNED DEFAULT NULL,
  28   l_sync_password BOOL DEFAULT TRUE,
  29   l_activate_mail_redirection BOOL DEFAULT TRUE,
  30
  31   -- Shared fields.
  32   g_account_id CHAR(16) DEFAULT NULL,
  33   g_account_name VARCHAR(256) NOT NULL,
  34   g_first_name VARCHAR(40) NOT NULL,
  35   g_last_name VARCHAR(40) NOT NULL,
  36   g_status ENUM('unprovisioned', 'disabled', 'active') DEFAULT 'unprovisioned',
  37   g_admin BOOL DEFAULT NULL,
  38   g_suspension VARCHAR(256) DEFAULT NULL,
  39
  40   -- Google-owned fields.
  41   r_disk_usage BIGINT DEFAULT NULL,
  42   r_creation DATE DEFAULT NULL,
  43   r_last_login DATE DEFAULT NULL,
  44   r_last_webmail DATE DEFAULT NULL,
  45
  46   -- Indexes.
  47   PRIMARY KEY(g_account_name),
  48   INDEX l_userid(l_userid)
  49 ) CHARSET=utf8;
  50
  51 -- Table `gapps_queue`.
  52 -- Holds queued API requests that are to be processed by the gappsd. It also
  53 -- includes processed but not yet acknowledged requests.
  54 CREATE TABLE IF NOT EXISTS `gapps_queue` (
  55   -- Queue identification fields.
  56   q_id INT NOT NULL AUTO_INCREMENT,
  57   q_owner_id SMALLINT UNSIGNED DEFAULT NULL,
  58   q_recipient_id SMALLINT UNSIGNED DEFAULT NULL,
  59
  60   -- Queue management information.
  61   p_entry_date DATETIME NOT NULL,
  62   p_notbefore_date DATETIME NOT NULL,
  63   p_start_date DATETIME DEFAULT NULL,
  64   p_end_date DATETIME DEFAULT NULL,
  65   p_status ENUM('idle', 'active', 'success', 'hardfail', 'softfail') DEFAULT 'idle' NOT NULL,
  66   p_priority ENUM('immediate', 'normal', 'offline') DEFAULT 'offline' NOT NULL,
  67   p_admin_request BOOLEAN DEFAULT false NOT NULL,
  68
  69   -- Job content fields.
  70   j_type ENUM('r_activity', 'r_accounts', 'u_create', 'u_delete', 'u_update', 'u_sync') NOT NULL,
  71   j_parameters TEXT DEFAULT NULL,
  72
  73   -- Job execution result fields.
  74   r_softfail_date DATETIME DEFAULT NULL,
  75   r_softfail_count SMALLINT DEFAULT 0 NOT NULL,
  76   r_result VARCHAR(256) DEFAULT NULL,
  77
  78   -- Indexes.
  79   PRIMARY KEY(q_id),
  80   INDEX q_owner_id(q_owner_id),
  81   INDEX q_recipient_id(q_recipient_id),
  82   INDEX p_status(p_status),
  83   INDEX p_priority(p_priority)
  84 ) CHARSET=utf8;
  85
  86 -- vim:set syntax=mysql: