[Sommaire] [Imprimer]

   1  set doc off;
   2  set sqlblanklines on;
   3  
   4  /**
   5   * Oracle Table Creation Scripts.
   6   * 
   7   * $Horde: horde/scripts/sql/create.oci8.sql,v 1.4.8.10 2006/06/29 16:29:03 jan Exp $
   8   * 
   9   * @author Miguel Ward <mward@aluar.com.ar>
  10   * 
  11   * This sql creates the Horde SQL tables in an Oracle 8.x database. Should
  12   * work with Oracle 9.x (and Oracle7 using varchar2).
  13   * 
  14   * Notes:
  15   * 
  16   *  * Obviously you must have Oracle installed on this machine AND you must
  17   *    have compiled PHP with Oracle (you included --with-oci8-instant
  18   *    --with-oci8 or in the build arguments for PHP, or uncommented the oci8
  19   *    extension in php.ini).
  20   * 
  21   *  * If you don't use the Instant Client, make sure that the user that starts
  22   *    up Apache (usually nobody or www-data) has the following environment
  23   *    variables defined:
  24   * 
  25   *    export ORACLE_HOME=/home/oracle/OraHome1
  26   *    export ORA_NLS=/home/oracle/OraHome1/ocommon/nls/admin/data
  27   *    export ORA_NLS33=/home/oracle/OraHome1/ocommon/nls/admin/data
  28   *    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
  29   * 
  30   *    YOU MUST CUSTOMIZE THESE VALUES TO BE APPROPRIATE TO YOUR INSTALLATION
  31   * 
  32   *    You can include these variables in the user's local .profile or in
  33   *    /etc/profile, etc.
  34   * 
  35   *  * No grants are necessary since we connect as the owner of the tables. If
  36   *    you wish you can adapt the creation of tables to include tablespace and
  37   *    storage information. Since we include none it will use the default
  38   *    tablespace values for the user creating these tables. Same with the
  39   *    indexes (in theory these should use a different tablespace).
  40   * 
  41   *  * There is no need to shut down and start up the database!
  42   */
  43  
  44  rem conn horde/&horde_password@database
  45  
  46  /**
  47   * This is the Horde users table, needed only if you are using SQL
  48   * authentication.  Note that passowrds in this table need to be md5-encoded.
  49   */
  50  
  51  CREATE TABLE horde_users (
  52      user_uid                    VARCHAR2(255) NOT NULL,
  53      user_pass                   VARCHAR2(255) NOT NULL,
  54      user_soft_expiration_date   NUMBER(16),
  55      user_hard_expiration_date   NUMBER(16),
  56  
  57      PRIMARY KEY (user_uid)
  58  );
  59  
  60  
  61  /**
  62   * This is the Horde preferences table, holding all of the user-specific
  63   * options for every Horde user.
  64   * 
  65   * pref_uid   is the username.
  66   * pref_scope is the application the pref belongs to.
  67   * pref_name  is the name of the variable to save.
  68   * pref_value is the value saved (can be very long).
  69   * 
  70   * We use a CLOB column so that longer column values are supported.
  71   * 
  72   * If still using Oracle 7 this should work but you have to use
  73   * VARCHAR2(2000) which is the limit imposed by said version.
  74   */
  75  
  76  CREATE TABLE horde_prefs (
  77      pref_uid    VARCHAR2(255) NOT NULL,
  78      pref_scope  VARCHAR2(16) NOT NULL,
  79      pref_name   VARCHAR2(32) NOT NULL,
  80  --  See above notes on CLOBs.
  81      pref_value  CLOB,
  82  
  83      PRIMARY KEY (pref_uid, pref_scope, pref_name)
  84  );
  85  
  86  CREATE INDEX pref_uid_idx ON horde_prefs (pref_uid);
  87  CREATE INDEX pref_scope_idx ON horde_prefs (pref_scope);
  88  
  89  
  90  /**
  91   * The DataTree tables are used for holding hierarchical data such as Groups,
  92   * Permissions, and data for some Horde applications.
  93   */
  94  
  95  CREATE TABLE horde_datatree (
  96      datatree_id          NUMBER(16) NOT NULL,
  97      group_uid            VARCHAR2(255) NOT NULL,
  98      user_uid             VARCHAR2(255),
  99      datatree_name        VARCHAR2(255) NOT NULL,
 100      datatree_parents     VARCHAR2(255),
 101      datatree_order       NUMBER(16),
 102      datatree_data        CLOB,
 103      datatree_serialized  NUMBER(8) DEFAULT 0 NOT NULL,
 104  
 105      PRIMARY KEY (datatree_id)
 106  );
 107  
 108  CREATE INDEX datatree_datatree_name_idx ON horde_datatree (datatree_name);
 109  CREATE INDEX datatree_group_idx ON horde_datatree (group_uid);
 110  CREATE INDEX datatree_user_idx ON horde_datatree (user_uid);
 111  CREATE INDEX datatree_order_idx ON horde_datatree (datatree_order);
 112  CREATE INDEX datatree_serialized_idx ON horde_datatree (datatree_serialized);
 113  
 114  CREATE TABLE horde_datatree_attributes (
 115      datatree_id      NUMBER(16) NOT NULL,
 116      attribute_name   VARCHAR2(255) NOT NULL,
 117      attribute_key    VARCHAR2(255),
 118      attribute_value  VARCHAR2(4000)
 119  );
 120  
 121  CREATE INDEX datatree_attribute_idx ON horde_datatree_attributes (datatree_id);
 122  CREATE INDEX datatree_attribute_name_idx ON horde_datatree_attributes (attribute_name);
 123  CREATE INDEX datatree_attribute_key_idx ON horde_datatree_attributes (attribute_key);
 124  
 125  
 126  CREATE TABLE horde_tokens (
 127      token_address    VARCHAR2(100) NOT NULL,
 128      token_id         VARCHAR2(32) NOT NULL,
 129      token_timestamp  NUMBER(16) NOT NULL,
 130  
 131      PRIMARY KEY (token_address, token_id)
 132  );
 133  
 134  
 135  CREATE TABLE horde_vfs (
 136      vfs_id        NUMBER(16) NOT NULL,
 137      vfs_type      NUMBER(8) NOT NULL,
 138      vfs_path      VARCHAR2(255),
 139      vfs_name      VARCHAR2(255) NOT NULL,
 140      vfs_modified  NUMBER(16) NOT NULL,
 141      vfs_owner     VARCHAR2(255),
 142      vfs_data      BLOB,
 143  
 144      PRIMARY KEY   (vfs_id)
 145  );
 146  
 147  CREATE INDEX vfs_path_idx ON horde_vfs (vfs_path);
 148  CREATE INDEX vfs_name_idx ON horde_vfs (vfs_name);
 149  
 150  
 151  CREATE TABLE horde_histories (
 152      history_id       NUMBER(16) NOT NULL,
 153      object_uid       VARCHAR2(255) NOT NULL,
 154      history_action   VARCHAR2(32) NOT NULL,
 155      history_ts       NUMBER(16) NOT NULL,
 156      history_desc     CLOB,
 157      history_who      VARCHAR2(255),
 158      history_extra    CLOB,
 159  
 160      PRIMARY KEY (history_id)
 161  );
 162  
 163  CREATE INDEX history_action_idx ON horde_histories (history_action);
 164  CREATE INDEX history_ts_idx ON horde_histories (history_ts);
 165  CREATE INDEX history_uid_idx ON horde_histories (object_uid);
 166  
 167  
 168  CREATE TABLE horde_sessionhandler (
 169      session_id             VARCHAR2(32) NOT NULL,
 170      session_lastmodified   INT NOT NULL,
 171      session_data           BLOB,
 172  
 173      PRIMARY KEY (session_id)
 174  );
 175  
 176  
 177  exit