[Sommaire] [Imprimer]

   1  -- $Horde: horde/scripts/sql/create.mysql.sql,v 1.4.6.9 2006/07/05 15:53:10 jan Exp $
   2  --
   3  -- If you are installing Horde for the first time, you can simply
   4  -- direct this file to mysql as STDIN:
   5  --
   6  -- $ mysql --user=root --password=<MySQL-root-password> < create.mysql.sql
   7  --
   8  -- If you are upgrading from a previous version, you will need to comment
   9  -- out the the user creation steps below, as well as the schemas for any
  10  -- tables that already exist.
  11  --
  12  -- If you choose to grant permissions manually, note that with MySQL, PEAR DB
  13  -- emulates sequences by automatically creating extra tables ending in _seq,
  14  -- so the MySQL "horde" user must have CREATE privilege on the "horde"
  15  -- database.
  16  --
  17  -- If you are upgrading from Horde 1.x, the Horde tables you have from
  18  -- that version are no longer used; you may wish to either delete those
  19  -- tables or simply recreate the database anew.
  20  
  21  USE mysql;
  22  
  23  REPLACE INTO user (host, user, password)
  24      VALUES (
  25          'localhost',
  26          'horde',
  27  -- IMPORTANT: Change this password!
  28          PASSWORD('horde')
  29  );
  30  
  31  REPLACE INTO db (host, db, user, select_priv, insert_priv, update_priv,
  32                   delete_priv, create_priv, drop_priv, index_priv)
  33      VALUES (
  34          'localhost',
  35          'horde',
  36          'horde',
  37          'Y', 'Y', 'Y', 'Y',
  38          'Y', 'Y', 'Y'
  39  );
  40  
  41  -- Make sure that priviliges are reloaded.
  42  FLUSH PRIVILEGES;
  43  
  44  -- MySQL 3.23.x appears to have "CREATE DATABASE IF NOT EXISTS" and
  45  -- "CREATE TABLE IF NOT EXISTS" which would be a nice way to handle
  46  -- reinstalls gracefully (someday).  For now, drop the database
  47  -- manually to avoid CREATE errors.
  48  CREATE DATABASE horde;
  49  
  50  USE horde;
  51  
  52  CREATE TABLE horde_users (
  53      user_uid                    VARCHAR(255) NOT NULL,
  54      user_pass                   VARCHAR(255) NOT NULL,
  55      user_soft_expiration_date   INT,
  56      user_hard_expiration_date   INT,
  57  
  58      PRIMARY KEY (user_uid)
  59  );
  60  
  61  GRANT SELECT, INSERT, UPDATE, DELETE ON horde_users TO horde@localhost;
  62  
  63  CREATE TABLE horde_prefs (
  64      pref_uid        VARCHAR(200) NOT NULL,
  65      pref_scope      VARCHAR(16) NOT NULL DEFAULT '',
  66      pref_name       VARCHAR(32) NOT NULL,
  67      pref_value      LONGTEXT NULL,
  68  
  69      PRIMARY KEY (pref_uid, pref_scope, pref_name)
  70  );
  71  
  72  CREATE INDEX pref_uid_idx ON horde_prefs (pref_uid);
  73  CREATE INDEX pref_scope_idx ON horde_prefs (pref_scope);
  74  
  75  GRANT SELECT, INSERT, UPDATE, DELETE ON horde_prefs TO horde@localhost;
  76  
  77  CREATE TABLE horde_datatree (
  78         datatree_id INT NOT NULL,
  79         group_uid VARCHAR(255) NOT NULL,
  80         user_uid VARCHAR(255) NOT NULL,
  81         datatree_name VARCHAR(255) NOT NULL,
  82         datatree_parents VARCHAR(255) NOT NULL,
  83         datatree_order INT,
  84         datatree_data TEXT,
  85         datatree_serialized SMALLINT DEFAULT 0 NOT NULL,
  86  
  87         PRIMARY KEY (datatree_id)
  88  );
  89  
  90  CREATE INDEX datatree_datatree_name_idx ON horde_datatree (datatree_name);
  91  CREATE INDEX datatree_group_idx ON horde_datatree (group_uid);
  92  CREATE INDEX datatree_user_idx ON horde_datatree (user_uid);
  93  CREATE INDEX datatree_serialized_idx ON horde_datatree (datatree_serialized);
  94  
  95  CREATE TABLE horde_datatree_attributes (
  96      datatree_id INT NOT NULL,
  97      attribute_name VARCHAR(255) NOT NULL,
  98      attribute_key VARCHAR(255) DEFAULT '' NOT NULL,
  99      attribute_value TEXT
 100  );
 101  
 102  CREATE INDEX datatree_attribute_idx ON horde_datatree_attributes (datatree_id);
 103  CREATE INDEX datatree_attribute_name_idx ON horde_datatree_attributes (attribute_name);
 104  CREATE INDEX datatree_attribute_key_idx ON horde_datatree_attributes (attribute_key);
 105  
 106  GRANT SELECT, INSERT, UPDATE, DELETE ON horde_datatree TO horde@localhost;
 107  GRANT SELECT, INSERT, UPDATE, DELETE ON horde_datatree_attributes TO horde@localhost;
 108  
 109  CREATE TABLE horde_tokens (
 110      token_address    VARCHAR(100) NOT NULL,
 111      token_id         VARCHAR(32) NOT NULL,
 112      token_timestamp  BIGINT NOT NULL,
 113  
 114      PRIMARY KEY (token_address, token_id)
 115  );
 116  
 117  GRANT SELECT, INSERT, UPDATE, DELETE ON horde_tokens TO horde@localhost;
 118  
 119  CREATE TABLE horde_vfs (
 120      vfs_id        BIGINT NOT NULL,
 121      vfs_type      SMALLINT NOT NULL,
 122      vfs_path      VARCHAR(255) NOT NULL,
 123      vfs_name      VARCHAR(255) NOT NULL,
 124      vfs_modified  BIGINT NOT NULL,
 125      vfs_owner     VARCHAR(255) NOT NULL,
 126      vfs_data      LONGBLOB,
 127  
 128      PRIMARY KEY (vfs_id)
 129  );
 130  
 131  CREATE INDEX vfs_path_idx ON horde_vfs (vfs_path);
 132  CREATE INDEX vfs_name_idx ON horde_vfs (vfs_name);
 133  
 134  GRANT SELECT, INSERT, UPDATE, DELETE ON horde_vfs TO horde@localhost;
 135  
 136  CREATE TABLE horde_histories (
 137      history_id       BIGINT NOT NULL,
 138      object_uid       VARCHAR(255) NOT NULL,
 139      history_action   VARCHAR(32) NOT NULL,
 140      history_ts       BIGINT NOT NULL,
 141      history_desc     TEXT,
 142      history_who      VARCHAR(255),
 143      history_extra    TEXT,
 144  
 145      PRIMARY KEY (history_id)
 146  );
 147  
 148  CREATE INDEX history_action_idx ON horde_histories (history_action);
 149  CREATE INDEX history_ts_idx ON horde_histories (history_ts);
 150  CREATE INDEX history_uid_idx ON horde_histories (object_uid);
 151  
 152  GRANT SELECT, INSERT, UPDATE, DELETE ON horde_histories TO horde@localhost;
 153  
 154  CREATE TABLE horde_sessionhandler (
 155      session_id             VARCHAR(32) NOT NULL,
 156      session_lastmodified   INT NOT NULL,
 157      session_data           LONGBLOB,
 158  
 159      PRIMARY KEY (session_id)
 160  ) ENGINE = InnoDB;
 161  
 162  GRANT SELECT, INSERT, UPDATE, DELETE ON horde_sessionhandler TO horde@localhost;
 163  
 164  FLUSH PRIVILEGES;
 165  
 166  -- Done!