--
-- Main Elgg database
-- 
-- @link http://elgg.org/
-- @license http://www.gnu.org/licenses/old-licenses/gpl-2.0.html GNU Public License version 2
-- @author Curverider Ltd
-- @copyright Curverider Ltd 2008
-- @link http://elgg.org/
--

-- --------------------------------------------------------

--
-- *** The main tables ***
--

-- Site configuration.
CREATE TABLE `prefix_config` (
  `name` varchar(32) NOT NULL,
  `value` text NOT NULL,
  `site_guid` int(11) NOT NULL,
  PRIMARY KEY  (`name`,`site_guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Define entities. 
CREATE TABLE `prefix_entities` (
	`guid` bigint(20) unsigned  NOT NULL auto_increment,
	
	`type` enum ('object', 'user', 'group', 'site') NOT NULL,
	`subtype` int(11) NULL,
	
	`owner_guid` bigint(20) unsigned NOT NULL,
    `site_guid` bigint(20) unsigned NOT NULL,
    `container_guid` bigint(20) unsigned NOT NULL,
	`access_id` int(11) NOT NULL,
	
	`time_created` int(11) NOT NULL,
	`time_updated` int(11) NOT NULL,

	`enabled` enum ('yes', 'no') NOT NULL default 'yes',
	
	primary key (`guid`),
	KEY `type` (`type`),
	KEY `subtype` (`subtype`),
	KEY `owner_guid` (`owner_guid`),
	KEY `site_guid` (`site_guid`),
	KEY `container_guid` (`container_guid`),
	KEY `access_id` (`access_id`),
	KEY `time_created` (`time_created`),
	KEY `time_updated` (`time_updated`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Entity subtypes - lets you subtype one of the main objects (sites/objects/etc)
CREATE TABLE `prefix_entity_subtypes` (
	`id` int(11) NOT NULL auto_increment,
	
	`type` enum ('object', 'user', 'group', 'site') NOT NULL,
	`subtype` varchar(50) NOT NULL,
	
	class varchar(50) NOT NULL default '',
	
	PRIMARY KEY (`id`),
	UNIQUE KEY (`type`, `subtype`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Describe relationships between entities, can describe friendships but also site membership, depending on context
CREATE TABLE `prefix_entity_relationships` (
  `id` int(11) NOT NULL auto_increment,
  
  `guid_one` bigint(20) unsigned  NOT NULL,
  `relationship` varchar(50) NOT NULL,
  `guid_two` bigint(20) unsigned  NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY (`guid_one`,`relationship`,`guid_two`),
  KEY `relationship` (`relationship`),
  KEY `guid_two` (`guid_two`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- *** Access controls ***
--

-- Table structure for table `access_collections`
CREATE TABLE `prefix_access_collections` (
  `id` int(11) NOT NULL auto_increment,
  `name` text NOT NULL,
  `owner_guid` bigint(20) unsigned NOT NULL,
  `site_guid` bigint(20) unsigned NOT NULL default '0',

  PRIMARY KEY  (`id`),
  KEY `owner_guid` (`owner_guid`),
  KEY `site_guid` (`site_guid`)
) AUTO_INCREMENT=3  ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Access containers 
CREATE TABLE `prefix_access_collection_membership` (
  `user_guid` int(11) NOT NULL,
  `access_collection_id` int(11) NOT NULL,
  PRIMARY KEY  (`user_guid`,`access_collection_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


--
-- *** Entity superclass details ***
-- NB: Aside from GUID, these should now have any field names in common with the entities table.
--

-- Extra information relating to "objects"
CREATE TABLE `prefix_objects_entity` (
  `guid` bigint(20) unsigned  NOT NULL,
  
  `title` text NOT NULL,
  `description` text NOT NULL,

  PRIMARY KEY  (`guid`),
  FULLTEXT KEY (`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Extra information relating to "sites"
CREATE TABLE `prefix_sites_entity` (
  `guid` bigint(20) unsigned  NOT NULL,
  
  `name` text NOT NULL,
  `description` text NOT NULL,
  `url` varchar(255) NOT NULL, 
   
  PRIMARY KEY  (`guid`),
  UNIQUE KEY (`url`),
  FULLTEXT KEY (`name`,`description`, `url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Extra information relating to "users"
CREATE TABLE `prefix_users_entity` (
  `guid` bigint(20) unsigned  NOT NULL,
  
  `name` text NOT NULL,
  `username` varchar(128) NOT NULL default '',
  `password` varchar(32) NOT NULL default '',
  `salt`     varchar(8)  NOT NULL default '',
  `email` text NOT NULL,
  `language` varchar(6)  NOT NULL default '',
  `code` varchar(32) NOT NULL default '',
  

  `last_action` int(11) NOT NULL default '0',
  `prev_last_action` int(11) NOT NULL default '0',
  `last_login` int(11) NOT NULL default '0',
  `prev_last_login` int(11) NOT NULL default '0',
  
  PRIMARY KEY  (`guid`),
  UNIQUE KEY (`username`),
  KEY `password` (`password`),
  KEY `email` (`email`(50)),
  KEY `code` (`code`),
  FULLTEXT KEY `name` (`name`),
  FULLTEXT KEY (`name`,`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Extra information relating to "groups"
CREATE TABLE `prefix_groups_entity` (
  `guid` bigint(20) unsigned  NOT NULL,
  
  `name` text NOT NULL,
  `description` text NOT NULL,
   
  PRIMARY KEY  (`guid`),
  FULLTEXT KEY (`name`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- *** Annotations and tags ***
--

-- Table structure for annotations
CREATE TABLE `prefix_annotations` (
	`id` int(11) NOT NULL auto_increment,
	
	`entity_guid` bigint(20) unsigned  NOT NULL,
	
	`name_id` int(11) NOT NULL,
	`value_id` int(11) NOT NULL,
	`value_type` enum ('integer','text') NOT NULL,
	
	`owner_guid` bigint(20) unsigned NOT NULL,
	`access_id` int(11) NOT NULL,
	
	`time_created` int(11) NOT NULL,

	`enabled` enum ('yes', 'no') NOT NULL default 'yes',
	
	PRIMARY KEY (`id`),
	KEY `entity_guid` (`entity_guid`),
	KEY `name_id` (`name_id`),
	KEY `value_id` (`value_id`),
	KEY `owner_guid` (`owner_guid`),
	KEY `access_id` (`access_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Table structure for metadata
CREATE TABLE `prefix_metadata` (
	`id` int(11) NOT NULL auto_increment,
	
	`entity_guid` bigint(20) unsigned  NOT NULL,
	
	`name_id` int(11) NOT NULL,
	`value_id` int(11) NOT NULL,
	`value_type` enum ('integer','text') NOT NULL,

	`owner_guid` bigint(20) unsigned NOT NULL,
	`access_id` int(11) NOT NULL,
	
	`time_created` int(11) NOT NULL,

	`enabled` enum ('yes', 'no') NOT NULL default 'yes',
	
	PRIMARY KEY (`id`),
	KEY `entity_guid` (`entity_guid`),
	KEY `name_id` (`name_id`),
	KEY `value_id` (`value_id`),
	KEY `owner_guid` (`owner_guid`),
	KEY `access_id` (`access_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Meta strings table (avoids storing text strings more than once)
CREATE TABLE `prefix_metastrings` (
	`id` int(11) NOT NULL auto_increment,
	`string` TEXT NOT NULL,
	
	PRIMARY KEY (`id`),
	KEY `string` (`string`(50))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- *** Misc ***
--

-- API Users
CREATE TABLE `prefix_api_users` (
	id     int(11)     auto_increment,
	
	site_guid bigint(20) unsigned,
	
	api_key   varchar(40),
	secret    varchar(40) NOT NULL,
	active    int(1) default 1,
	
	unique key (api_key),
	primary key (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- API Sessions
CREATE TABLE `prefix_users_apisessions` (
	`id` int(11) NOT NULL auto_increment,
	`user_guid` bigint(20) unsigned NOT NULL,
  	`site_guid` bigint(20) unsigned NOT NULL,
  	
  	`token` varchar(40),
  	
  	`expires` int(11) NOT NULL,
	
	PRIMARY KEY  (`id`),
	UNIQUE KEY (`user_guid`,`site_guid`),
	KEY `token` (`token`)
) ENGINE=MEMORY;

-- HMAC Cache protecting against Replay attacks
CREATE TABLE `prefix_hmac_cache` (
	`hmac` varchar(255) NOT NULL,
	`ts` int(11) NOT NULL,

	PRIMARY KEY  (`hmac`),
	KEY `ts` (`ts`)
) ENGINE=MEMORY;

-- Geocode engine cache
CREATE TABLE `prefix_geocode_cache` (
	id     int(11)     auto_increment,
	location varchar(128),
	`lat`    varchar(20),
	`long`   varchar(20),
	
	PRIMARY KEY (`id`),
    KEY `location` (`location`)
	
) ENGINE=MEMORY;

-- PHP Session storage
CREATE TABLE `prefix_users_sessions` (
	`session` varchar(255) NOT NULL,
 	`ts` int(11) unsigned NOT NULL default '0',
	`data` mediumblob,
	
	PRIMARY KEY `session` (`session`),
	KEY `ts` (`ts`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Datalists for things like db version
CREATE TABLE `prefix_datalists` (
  `name` varchar(32) NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- Ultra-private system settings for entities
CREATE TABLE `prefix_private_settings` (
	`id` INT NOT NULL auto_increment,
	`entity_guid` INT NOT NULL ,
	`name` varchar(128) NOT NULL ,
	`value` TEXT NOT NULL ,
	PRIMARY KEY ( `id` ) ,
	UNIQUE KEY ( `entity_guid` , `name` ),
	KEY `name` (`name`),
	KEY `value` (`value` (50))
) ENGINE = MYISAM  DEFAULT CHARSET=utf8;

-- System log
CREATE TABLE `prefix_system_log` (
	`id` int(11) NOT NULL auto_increment,
	
	`object_id` int(11) NOT NULL,

	`object_class` varchar(50) NOT NULL,
	`object_type` varchar(50) NOT NULL,
	`object_subtype` varchar(50) NOT NULL,
	
	`event` varchar(50) NOT NULL,
	`performed_by_guid` int(11) NOT NULL,

	`owner_guid` int(11) NOT NULL,
	`access_id` int(11) NOT NULL,
	
	`enabled` enum ('yes', 'no') NOT NULL default 'yes',

	`time_created` int(11) NOT NULL,
	
	PRIMARY KEY  (`id`),
	KEY `object_id` (`object_id`),
	KEY `object_class` (`object_class`),
	KEY `object_type` (`object_type`),
	KEY `object_subtype` (`object_subtype`),
	KEY `event` (`event`),
	KEY `performed_by_guid` (`performed_by_guid`),
	KEY `access_id` (`access_id`),
	KEY `time_created` (`time_created`),
	KEY `river_key` (`object_type`, `object_subtype`, `event`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;