From 6c660e97ebc4b3f9395c3d8489accb8fce103572 Mon Sep 17 00:00:00 2001 From: M Date: Thu, 19 Nov 2015 19:57:11 -0500 Subject: * Reworked core datamodel -- now using a common main table for holding ALL objects (and their common attributes). Additional, supplemental tables may be employed to maintain data attributes which are specific to a given object type --- schema.sql | 54 ++++++++++++++++++++++++++++++------------------------ 1 file changed, 30 insertions(+), 24 deletions(-) (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql index 26f7d8e..c6a6370 100644 --- a/schema.sql +++ b/schema.sql @@ -34,38 +34,44 @@ USE `db_scrott`; * Permissions can be overridden down-stream, but permissions will cascade otherwise. */ -DROP TABLE IF EXISTS `member`; -CREATE TABLE `member` ( - `object` varchar(10) NOT NULL, - `member` varchar(10) NOT NULL, +/* Common object structure attributes */ +DROP TABLE IF EXISTS `object`; +CREATE TABLE `object` ( + `guid` varchar(10) NOT NULL, + `perms` int(10) unsigned NOT NULL DEFAULT 0, + `owner` varchar(10) NOT NULL, + `parent` varchar(10) DEFAULT NULL, + `name` varchar(50) NOT NULL, + `type` enum( + 'user', + 'group' + ) NOT NULL, + PRIMARY KEY (`guid`) +); - PRIMARY KEY (`object`,`member`) +/* Scheme for associating objects with members */ +DROP TABLE IF EXISTS `obj_member`; +CREATE TABLE `obj_member` ( + `guid` varchar(10) NOT NULL, + `member` varchar(10) NOT NULL, + PRIMARY KEY (`guid`,`member`) ); +/* User objects - special attributes */ DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `guid` varchar(10) NOT NULL, - `owner` varchar(10) NOT NULL, - `perms` int(10) unsigned NOT NULL, - `name` varchar(50) NOT NULL, - `alias` varchar(50) DEFAULT NULL, - `key` varchar(64) NOT NULL, - `salt` varchar(64) NOT NULL, - - PRIMARY KEY (`id`) + `guid` varchar(10) NOT NULL, + `key` varchar(64) NOT NULL, + `salt` varchar(64) NOT NULL, + `alias` varchar(50) DEFAULT NULL, + PRIMARY KEY (`guid`) ); -DROP TABLE IF EXISTS `group`; -CREATE TABLE `group` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `guid` varchar(10) NOT NULL, - `owner` varchar(10) NOT NULL, - `perms` int(10) unsigned NOT NULL, - `name` varchar(50) NOT NULL, +/* object type 'group' has no unique attributes */ - PRIMARY KEY (`id`) -); + + +/* TODO -- WIP FROM HERE */ DROP TABLE IF EXISTS `pad`; CREATE TABLE `pad` ( -- cgit v1.2.3