From 6aa390385b179905b4dceca07ff8fea77d1dd669 Mon Sep 17 00:00:00 2001 From: icewing Date: Thu, 13 Mar 2008 10:30:58 +0000 Subject: Marcus Povey * 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') 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