# mysql_create_all.sql 
# All the mysql scripts rolled into one
# you can simply direct this file to mysql as STDIN:
# mysql (user/pass/host args) < mysql_create.sql
# 

\r mysql

INSERT INTO user ( host, user, password )
   VALUES (
      'localhost',
      'hordemgr',
      password( 'hordemgr' )
      );
INSERT INTO user ( host, user, password )
   VALUES (
      'localhost',
      'impmgr',
      password( 'impmgr' )
      );

INSERT INTO db (
      host, db, user,
         Select_priv, Insert_priv, Update_priv, Delete_priv,
         Create_priv, Drop_priv )
      VALUES (
      'localhost',
      'horde',
      'hordemgr',
      'Y', 'Y', 'Y', 'Y',
      'Y', 'Y' 
        );

INSERT INTO db (
      host, db, user,
         Select_priv, Insert_priv, Update_priv, Delete_priv,
         Create_priv, Drop_priv )
      VALUES (
      'localhost',
      'imp',
      'hordemgr',
      'Y', 'Y', 'Y', 'Y',
      'Y', 'Y' 
        );

INSERT INTO db (
      host, db, user,
         Select_priv, Insert_priv, Update_priv, Delete_priv,
         Create_priv, Drop_priv )
      VALUES (
      'localhost',
      'imp',
      'impmgr',
      'Y', 'Y', 'Y', 'Y',
      'Y', 'Y' 
        );

INSERT INTO db (
      host, db, user,
         Select_priv, Insert_priv, Update_priv, Delete_priv,
         Create_priv, Drop_priv )
      VALUES (
      'localhost',
      'horde',
      'impmgr',
      'Y', 'Y', 'Y', 'Y',
      'N', 'N' 
        );

# done!
# mysql_create.sql
#
# This script creates the databases used within the horde system.
# The horde, database is the common database within which all of the
# rest of the databses derive their functionality off of.
#
# you can simply direct this file to mysql as STDIN:
# mysql (user/pass/host args) < mysql_create.sql



CREATE DATABASE horde;

\r horde

# -- User Specific items --
# Create the primary user table
CREATE TABLE horde_user_table (
	horde_user_id					BIGINT 	NOT NULL AUTO_INCREMENT,
	horde_user_name				TEXT 		NOT NULL DEFAULT '',
	horde_password					TEXT,
   login_deny                 INT( 1 ) NOT NULL DEFAULT 0,
	PRIMARY KEY( horde_user_id ),
	INDEX( horde_user_id )
	);

# -- Group Specific items --
# create the primary group table
CREATE TABLE horde_group_table (
   horde_group_id             BIGINT   NOT NULL AUTO_INCREMENT,
   group_name                 TEXT     NOT NULL DEFAULT '',
   PRIMARY KEY( horde_group_id ),
   INDEX( horde_group_id )
   );

# create the group member table
CREATE TABLE horde_group_members_table (
   horde_group_members_id     BIGINT   NOT NULL AUTO_INCREMENT,
   horde_group_id             BIGINT   NOT NULL,
   horde_user_id              BIGINT   NOT NULL,
   PRIMARY KEY( horde_group_members_id ),
   KEY( horde_group_id ),
   KEY( horde_user_id ),
   INDEX( horde_group_id, horde_user_id )
   );

# -- Form Grants  -- To users and to groups
CREATE TABLE horde_user_form_grants_table (
	horde_user_form_grants_id 		BIGINT		NOT NULL AUTO_INCREMENT,
	horde_form_id						BIGINT,
	horde_user_id						BIGINT,
	PRIMARY KEY( horde_user_form_grants_id )
);
CREATE TABLE horde_group_form_grants_table (
	horde_group_form_grants_id		BIGINT		NOT NULL AUTO_INCREMENT,
	horde_form_id						BIGINT,
	horde_group_id						BIGINT,
	PRIMARY KEY( horde_group_form_grants_id )
);
CREATE TABLE horde_user_forms_table (
	horde_form_id						BIGINT		NOT NULL AUTO_INCREMENT,
	title									TEXT,
	url									TEXT,
	action_id							TEXT,
	description							TEXT,
	PRIMARY KEY( horde_form_id )
);

# -- Horde Module Grants --
CREATE TABLE horde_modules_table (
	horde_module_id					BIGINT		NOT NULL AUTO_INCREMENT,
	horde_module_name					TEXT,
	horde_module_name_full			TEXT,
	PRIMARY KEY( horde_module_id )
);

INSERT INTO horde_modules_table ( horde_module_name, horde_module_name_full ) VALUES ( 'IMP', 				'Imap webMail Program' );
INSERT INTO horde_modules_table ( horde_module_name, horde_module_name_full ) VALUES ( 'Gollem', 			'File Manager' );
INSERT INTO horde_modules_table ( horde_module_name, horde_module_name_full ) VALUES ( 'Kronolith',		'Calendar / Scheduling' );
INSERT INTO horde_modules_table ( horde_module_name, horde_module_name_full ) VALUES ( 'NAG',				'Todo/Task List' );

CREATE TABLE horde_user_module_grants_table (
	horde_module_grant_id			BIGINT		NOT NULL AUTO_INCREMENT,
	horde_module_id					BIGINT,
	horde_user_id						BIGINT,
	PRIMARY KEY( horde_module_grant_id )
);
CREATE TABLE horde_group_module_grants_table (
	horde_module_grant_id			BIGINT		NOT NULL AUTO_INCREMENT,
	horde_module_id					BIGINT,
	horde_group_id						BIGINT,
	PRIMARY KEY( horde_module_grant_id )
);

# -- Admin items --
# create the admin table
CREATE TABLE admin_privlage_grants_table (
   admin_privlage_grants_table   BIGINT      NOT NULL AUTO_INCREMENT,
   horde_user_id                 BIGINT      NOT NULL,
   admin_privlage_id             BIGINT      NOT NULL,
   PRIMARY KEY( admin_privlage_grants_table ),
   KEY( horde_user_id ),
   KEY( admin_privlage_id ),
   INDEX( horde_user_id, admin_privlage_id )
   );

# create the admin privlage table 
CREATE TABLE admin_privlage_table (
   admin_privlage_id          BIGINT   NOT NULL AUTO_INCREMENT,
   privlage_name              TEXT,
   privlage_state_tag         TEXT,
   privlage_icon_url          TEXT,
   privlage_alt_url           TEXT,
   privlage_description       TEXT,
   PRIMARY KEY( admin_privlage_id ),
   INDEX( admin_privlage_id )
   );

# create the datagrant table
CREATE TABLE data_grant_table (
   data_grant_id              BIGINT   NOT NULL AUTO_INCREMENT,
   horde_user_id              BIGINT   NOT NULL,
   data_grant_recipient_id    BIGINT   NOT NULL,
   grant_type                 BIGINT   NOT NULL,
   public                     BIGINT   NOT NULL,
   with_grant_option          BIGINT   NOT NULL,
   PRIMARY KEY( data_grant_id ),
   INDEX( data_grant_id, data_grant_recipient_id, horde_user_id )
   );

# -- Imap server table --
# create the imap server table
CREATE TABLE imap_server_table (
	horde_user_id					BIGINT 	NOT NULL AUTO_INCREMENT,
	user_name						TEXT,
	password							TEXT,
	server							TEXT,
	server_type_id					BIGINT,
   	/* default                    BIGINT, */
	PRIMARY KEY( horde_user_id ),
	INDEX( horde_user_id )
	);

# create the document configuration table
CREATE TABLE document_config_table (
	document_id						BIGINT 	NOT NULL AUTO_INCREMENT,
	horde_user_id					BIGINT 	NOT NULL DEFAULT '',
	body_bg_color					TEXT,
	body_background_file			TEXT,
	body_text_color				TEXT,
	body_link_color				TEXT,
	body_vlink_color				TEXT,
	body_alink_color				TEXT,
	PRIMARY KEY( document_id ),
	KEY( horde_user_id ),
	INDEX( horde_user_id, document_id )
	);
	
# Now create the imp specific tables.
#CREATE DATABASE imp;
#\r imp

# imp signatures
CREATE TABLE imp_signature_table (
	horde_user_id					BIGINT 	NOT NULL,
	signature						TEXT,
	PRIMARY KEY( horde_user_id ),
	INDEX( horde_user_id )
);

# -- kronolith : Tables --
CREATE TABLE event_table (
   event_id                   BIGINT   NOT NULL AUTO_INCREMENT,
   horde_user_id              BIGINT   NOT NULL,
   horde_group_id             BIGINT   NOT NULL,
   start_time                 DATE,
   end_time                   DATE,
   title                      TEXT,
   comments                   TEXT,
   PRIMARY KEY( event_id ),
   INDEX( event_id, horde_user_id, horde_group_id )
);

# -- Troll : Tables --
# done!
# script to create the necessary mysql database and tables
# you can simply direct this file to mysql as STDIN:
# mysql (user/pass/host args) < mysql_create.sql

# change the database and table names to whatever you'd like
# them to be - just make sure the IMP config matches.

# create the database
CREATE DATABASE imp;

# now connect to the new database
\r imp

# create the address table
CREATE TABLE imp_addr (
  user text,
  address text,
  nickname text,
  fullname text
);

# now create the pref table
CREATE TABLE imp_pref (
  user text,
  fullname text,
  replyto text,
  lang varchar(30),
  sig text
);

# done!
# script to create the necessary mysql database and tables
# you can simply direct this file to mysql as STDIN:
# mysql (user/pass/host args) < mysql_create.sql

# now connect to the new database
\r imp

#
# Table structure for table 'ssl_to_imp'
#
CREATE TABLE ssl_to_imp (
  DN char(255) DEFAULT '' NOT NULL,
  username char(64) DEFAULT '' NOT NULL,
  password char(64),
  server char(64),
  folder char(64),
  PRIMARY KEY (DN),
  KEY username (username)
);

#
# Table structure for table 'imp_log'
#
CREATE TABLE imp_log (
  username char(255) DEFAULT '' NOT NULL,
  ts timestamp(14),
  event int(11) DEFAULT '0' NOT NULL,
  target char(255) DEFAULT '' NOT NULL,
  log char(255),
  md5 char(128),
  KEY (username),
  KEY ts (ts),
  KEY event (event),
  KEY target (target)
);

# create the active_sessions table
CREATE TABLE imp_sessions (
  sid varchar(32),
  val longtext,
  expire datetime
);

/* done! */
