aboutsummaryrefslogtreecommitdiff
path: root/engine/schema/mysql.sql
diff options
context:
space:
mode:
Diffstat (limited to 'engine/schema/mysql.sql')
-rw-r--r--engine/schema/mysql.sql269
1 files changed, 105 insertions, 164 deletions
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;
+
generated by cgit v1.2.3 (git 2.39.1) at 2024-10-30 18:07:39 +0000