From 6aa390385b179905b4dceca07ff8fea77d1dd669 Mon Sep 17 00:00:00 2001
From: icewing <icewing@36083f99-b078-4883-b0ff-0f9b5a30f544>
Date: Thu, 13 Mar 2008 10:30:58 +0000
Subject: Marcus Povey <marcus@dushka.co.uk> * New database schema. THIS IS A
 FLAG DAY!

git-svn-id: https://code.elgg.org/elgg/trunk@191 36083f99-b078-4883-b0ff-0f9b5a30f544
---
 engine/schema/mysql.sql | 269 +++++++++++++++++++-----------------------------
 1 file changed, 105 insertions(+), 164 deletions(-)

(limited to 'engine/schema/mysql.sql')

diff --git a/engine/schema/mysql.sql b/engine/schema/mysql.sql
index 66f1745da..9caa2a81e 100644
--- a/engine/schema/mysql.sql
+++ b/engine/schema/mysql.sql
@@ -11,258 +11,199 @@
 -- --------------------------------------------------------
 
 --
--- Table structure for table `access_groups`
---
-
-CREATE TABLE `prefix_access_groups` (
-  `id` int(11) NOT NULL,
-  `name` varchar(16) NOT NULL,
-  `owner_id` int(11) NOT NULL,
-  `site_id` int(11) NOT NULL default '0',
-  PRIMARY KEY  (`id`),
-  KEY `name` (`name`)
-) ENGINE=MyISAM ;
-
---
--- Dumping data for table `access_groups`
+-- *** The main tables ***
 --
 
-INSERT INTO `prefix_access_groups` (`id`, `name`, `site_id`) VALUES
-(0, 'PRIVATE', 0),
-(1, 'LOGGED_IN', 0),
-(2, 'PUBLIC', 0);
-
--- --------------------------------------------------------
+-- Define entities. 
+CREATE TABLE `prefix_entities` (
+	`guid` bigint(20) unsigned  NOT NULL auto_increment,
+	
+	`type` enum ('object', 'user', 'collection', 'site') NOT NULL,
+	`subtype` int(11) NULL,
+	
+	`owner_guid` bigint(20) unsigned NOT NULL,
+  	`site_guid` bigint(20) unsigned NOT NULL default '0',
+	`access_id` int(11) NOT NULL,
+	
+	`time_created` int(11) NOT NULL,
+	`time_updated` int(11) NOT NULL,
+	
+	primary key (`guid`)
+);
 
---
--- Table structure for table `access_group_membership`
---
+-- 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,
+	`guid` bigint(20) unsigned  NOT NULL,
+	
+	`subtype` varchar(50) NOT NULL,
+	
+	PRIMARY KEY (`id`),
+	UNIQUE KEY (`guid`)
+) ENGINE=MyISAM;
 
-CREATE TABLE `prefix_access_group_membership` (
-  `user_id` int(11) NOT NULL,
-  `access_group_id` int(11) NOT NULL,
-  PRIMARY KEY  (`user_id`,`access_group_id`)
+-- 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_type` varchar(50) NOT NULL,
+  `guid_two` bigint(20) unsigned  NOT NULL,
+  PRIMARY KEY  (`id`),
+  UNIQUE KEY (`guid_one`,`relationship_type`,`guid_two`)
 ) ENGINE=MyISAM ;
 
-
--- --------------------------------------------------------
-
 --
--- Table structure for table `configuration`
+-- *** Access controls ***
 --
 
-CREATE TABLE `prefix_configuration` (
+-- Table structure for table `access_groups`
+CREATE TABLE `prefix_access_groups` (
   `id` int(11) NOT NULL,
-  `name` varchar(32) NOT NULL,
-  `value` text NOT NULL,
-  `site_id` int(11) NOT NULL default '0',
+  `name` varchar(16) NOT NULL,
+  `owner_guid` bigint(20) unsigned NOT NULL,
+  `site_guid` bigint(20) unsigned NOT NULL default '0',
   PRIMARY KEY  (`id`),
   KEY `name` (`name`)
 ) ENGINE=MyISAM ;
 
-
--- --------------------------------------------------------
+-- Dumping data for table `access_groups`
+INSERT INTO `prefix_access_groups` (`id`, `name`, `site_guid`) VALUES
+(0, 'PRIVATE', 0),
+(1, 'LOGGED_IN', 0),
+(2, 'PUBLIC', 0);
 
 --
--- Table structure for table `objects`
+-- *** Entity superclass details ***
 --
 
+-- Extra information relating to "objects"
 CREATE TABLE `prefix_objects` (
-  `id` int(11) NOT NULL auto_increment,
-  `owner_id` int(11) NOT NULL,
-  `site_id` int(11) NOT NULL default '0',
-  `type_id` int(11) NOT NULL,
-  `access_id` int(11) NOT NULL default '0',
+  `guid` bigint(20) unsigned  NOT NULL,
+  
   `title` text NOT NULL,
   `description` text NOT NULL,
-  `time_created` int(11) NOT NULL,
-  `time_updated` int(11) NOT NULL,
-  PRIMARY KEY  (`id`),
-  KEY `time_created` (`time_created`,`time_updated`)
-) ENGINE=MyISAM ;
-
--- --------------------------------------------------------
 
---
--- Table structure for table `object_types`
---
-
-CREATE TABLE `prefix_object_types` (
-  `id` int(11) NOT NULL auto_increment,
-  `name` varchar(16) NOT NULL,
-  PRIMARY KEY  (`id`),
-  KEY `name` (`name`)
+  PRIMARY KEY  (`guid`)
 ) ENGINE=MyISAM ;
 
--- --------------------------------------------------------
-
---
--- Table structure for table `users`
---
+-- 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` text NOT NULL, 
+   
+  PRIMARY KEY  (`guid`)
+) ENGINE=MyISAM ;
 
+-- Extra information relating to "users"
 CREATE TABLE `prefix_users` (
-  `id` int(11) NOT NULL auto_increment,
+  `guid` bigint(20) unsigned  NOT NULL,
+  
   `name` text NOT NULL,
   `username` varchar(12) NOT NULL default '',
   `password` varchar(32) NOT NULL default '',
   `email` text NOT NULL,
   `language` varchar(6)  NOT NULL default '',
   `code` varchar(32) NOT NULL default '',
-  `created` int(11) NOT NULL default '0',
+  
+
   `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  (`id`),
+  
+  PRIMARY KEY  (`guid`),
   KEY `password` (`password`),
   FULLTEXT KEY `name` (`name`)
 ) ENGINE=MyISAM ;
 
 
---
--- User sessions
--- 
-
-CREATE TABLE `prefix_users_apisessions` (
-	`id` int(11) NOT NULL auto_increment,
-	`user_id` int(11) NOT NULL,
-  	`site_id` int(11) NOT NULL,
-  	
-  	`token` varchar(40),
-  	
-  	`expires` int(11) NOT NULL,
-	
-	PRIMARY KEY  (`id`),
-	UNIQUE KEY (`user_id`,`site_id`)
-) ENGINE=MyISAM;
-
--- --------------------------------------------------------
-
---
--- Table structure for table `sites`
---
-
-CREATE TABLE `prefix_sites` (
-  `id` int(11) NOT NULL auto_increment,
-  `name` text NOT NULL,
-  `description` text NOT NULL,
-  `url` text NOT NULL,
-  
-  `owner_id` int(11) NOT NULL,
-  `created` int(11) NOT NULL,
-  `last_updated` int(11) NOT NULL,
-  
-  `access_id` int(11) NOT NULL,
-  
-  PRIMARY KEY  (`id`)
-) ENGINE=MyISAM ;
-
---
--- Link table between users and sites
---
-
-CREATE TABLE `prefix_users_sites` (
-  `user_id` int(11) NOT NULL,
-  `site_id` int(11) NOT NULL,
-  PRIMARY KEY  (`user_id`,`site_id`)
-) ENGINE=MyISAM ;
-
---
--- Table structure for friends
---
+-- TODO: Collection
 
-CREATE TABLE `prefix_friends` (
-  `id` int(11) NOT NULL auto_increment,
-  `user_id` int(11) NOT NULL,
-  `friend_id` int(11) NOT NULL,
-  `site_id` int(11) NOT NULL,
-  PRIMARY KEY  (`id`),
-  KEY `user_id` (`user_id`),
-  KEY `friend_id` (`friend_id`)
-) ENGINE=MyISAM;
 
 --
--- Entity subtypes - lets you subtype one of the main objects (sites/objects/etc)
+-- *** Annotations and tags ***
 --
-CREATE TABLE `prefix_entity_subtypes` (
-	`id` int(11) NOT NULL auto_increment,
-	`entity_id` int(11) NOT NULL,
-	`entity_type` enum ('object', 'user', 'collection', 'site') NOT NULL,
-	
-	`entity_subtype` varchar(50) NOT NULL,
-	
-	PRIMARY KEY (`id`),
-	UNIQUE KEY (`entity_id`,`entity_type`)
-) ENGINE=MyISAM;
 
--- 
 -- Table structure for annotations
---
 CREATE TABLE `prefix_annotations` (
 	`id` int(11) NOT NULL auto_increment,
 	
-	`entity_id` int(11) NOT NULL,
-	`entity_type` enum ('object', 'user', 'collection', 'site') NOT NULL,
+	`entity_guid` bigint(20) unsigned  NOT NULL,
 	
 	`name` varchar(255) NOT NULL,
-	`value` text NOT NULL,
+	`value` int(11) NOT NULL,
 	`value_type` enum ('integer','tag','text','file') NOT NULL,
 	
-	`owner_id` int(11) NOT NULL,
-	`created` int(11) NOT NULL,
-	
+	`owner_guid` bigint(20) unsigned NOT NULL,
 	`access_id` int(11) NOT NULL,
 	
+	`time_created` int(11) NOT NULL,
+	
 	PRIMARY KEY (`id`)
 ) ENGINE=MyISAM;
 
---
 -- Table structure for metadata
---
 CREATE TABLE `prefix_metadata` (
 	`id` int(11) NOT NULL auto_increment,
 	
-	`entity_id` int(11) NOT NULL,
-	`entity_type` enum ('object', 'user', 'collection', 'site') NOT NULL,
+	`entity_guid` bigint(20) unsigned  NOT NULL,
 	
 	`name` varchar(255) NOT NULL,
 	`value` int(11) NOT NULL,
 	`value_type` enum ('integer','tag','text','file') NOT NULL,
 
-	`owner_id` int(11) NOT NULL,
-	`created` int(11) NOT NULL,
-
+	`owner_guid` bigint(20) unsigned NOT NULL,
 	`access_id` int(11) NOT NULL,
 	
+	`time_created` int(11) NOT NULL,
+	
 	PRIMARY KEY (`id`),
-	UNIQUE KEY (`entity_id`,`entity_type`, `name`)
+	UNIQUE KEY (`entity_guid`, `name`)
 	
 ) ENGINE=MyISAM;
 
---
--- Meta strings table
---
+-- Meta strings table (avoids storing text strings more than once)
 CREATE TABLE `prefix_metastrings` (
 	`id` int(11) NOT NULL auto_increment,
-	`tag` varchar(255) NOT NULL,
+	`string` varchar(255) NOT NULL,
 	
 	PRIMARY KEY (`id`),
-	UNIQUE KEY (`tag`)
+	UNIQUE KEY (`string`)
 ) ENGINE=MyISAM;
 
 --
--- API Users - Users who have access to the api (may not be real users)
+-- *** Misc ***
 --
+
+-- API Users
 CREATE TABLE `prefix_api_users` (
 	id     int(11)     auto_increment,
 	
-	email_address varchar(128),
-	site_id	  int(11),
+	site_guid bigint(20) unsigned,
+	
 	api_key   varchar(40),
 	secret    varchar(40) NOT NULL,
-	active    int default 1,
+	active    int(1) default 1,
 	
-	unique key (email_address),
 	unique key (api_key),
 	primary key (id)
 );
+
+-- 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`)
+) ENGINE=MyISAM;
+
-- 
cgit v1.2.3

