diff options
Diffstat (limited to 'examples')
| -rw-r--r-- | examples/database.class.php | 73 | ||||
| -rw-r--r-- | examples/mysql.class.php | 74 | ||||
| -rw-r--r-- | examples/schema.old.sql | 164 | ||||
| -rw-r--r-- | examples/schema.sql | 309 |
4 files changed, 485 insertions, 135 deletions
diff --git a/examples/database.class.php b/examples/database.class.php new file mode 100644 index 0000000..c7ef65b --- /dev/null +++ b/examples/database.class.php @@ -0,0 +1,73 @@ +<?php + +/* + * SCROTT IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR + * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF + * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. + * IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR + * OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, + * ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR + * OTHER DEALINGS IN THE SOFTWARE. + * + * For more information, please refer to UNLICENSE + */ + +/* + * This class provides a common interface to a database. Scrott provides + * facilities for interacting with any DBMS that we can use in PHP. To + * provide support for a new DBMS, extend this class and implement the + * abstract functions. Additionally, update the getInstance() function + * in this class so that the new implementation is used under appropriate + * configuration. + */ +abstract class Database +{ + private static $instance = NULL; + protected $db; + + /* + * Return the database instance object, creating it if this is the + * first call to this function. This function will need maintained + * as new DBMSs are supported. + */ + public static function getInstance() : Database + { + // todo + } + + /* + * These functions are to be implemented by DBMS extensions, + * providing a uniform interface to database engines. + */ + public abstract function close(); + public abstract function query(string $query) : array; + public abstract function esc(string $str) : string; + + /* + * This function will lookup the row from the database on the given + * table containing the given GUID and initialize the class properties + * on this object based on the given field list. + */ + public function initObj(string $table, array $fields, string $guid = NULL) + { + if (is_null($guid)) + return; + + $guid = $this->esc($guid); + $query = "SELECT * FROM " . $table . " WHERE guid = '" . $guid . "'"; + $res = $this->query($query); + + if (!count($res)) + return; + + $res = $res[0]; + + foreach ($fields as $field) + { + if (isset($res[$field])) + $this->$field = $res[$field]; + } + } +} + +?> diff --git a/examples/mysql.class.php b/examples/mysql.class.php new file mode 100644 index 0000000..90a4016 --- /dev/null +++ b/examples/mysql.class.php @@ -0,0 +1,74 @@ +<?php + +/* + * SCROTT IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR + * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF + * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. + * IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR + * OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, + * ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR + * OTHER DEALINGS IN THE SOFTWARE. + * + * For more information, please refer to UNLICENSE + */ + +require_once "class/database.class.php"; + +/* + * This class implements support for MySQL and MariaDB via PHP's mysqli + * driver. + */ +class Mysql extends Database +{ + /* + * Constructor + */ + public function __construct(string $host, string $username, string $password, string $dbName) + { + $this->db = new mysqli($host, $username, $password, $dbName); + + if ($this->db->connect_error) + throw new Exception("Can not connect to MySQL database. Please check your configuration."); + } + + /* + * Destructor + */ + public function __destruct() + { + $this->close(); + } + + /* + * Close connection to DB + */ + public function close() + { + $this->db->close(); + } + + /* + * Make a query of the database. Return data as an array of arrays. + */ + public function query(string $query) : array + { + $arr = array(); + $res = $this->db->query($query); + + if ($res === true || $res === false) + return $arr; + + while (($arr[] = $res->fetch_assoc())); + return $arr; + } + + /* + * Escape a string for use in a query + */ + public function esc(string $str) : string + { + return $this->db->real_escape_string($str); + } +} + +?> diff --git a/examples/schema.old.sql b/examples/schema.old.sql new file mode 100644 index 0000000..9770ffa --- /dev/null +++ b/examples/schema.old.sql @@ -0,0 +1,164 @@ +/* + * SCROTT Copyright (C) 2016 Malf Furious + * + * Scrott is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published + * by the Free Software Foundation, either version 3 of the License, + * or (at your option) any later version. + * + * Scrott is distributed in the hope that it will be useful, but + * WITHOUT ANY WARRANTY; even the implied warranty of MERCHANTABILITY + * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public + * License for more details. + */ + +CREATE DATABASE IF NOT EXISTS `db_scrott`; +USE `db_scrott`; + +/* + * Description of SCROTT object permissions system + * ----------------------------------------------- + * Every object has an owner, zero or more additional members, and then there is the general + * public. + * + * OWNER = user OR group + * MEMBER = user + * OWNER MEMBERS OTHERS + * Permissions are: ------------------------------------------------ + * 1. Access object GRANTED GRANTED MB #6 + * 2. Modify object GRANTED MB #0 DENIED + * 3. Modify members GRANTED MB #1 DENIED + * 4. Modify permissions GRANTED DENIED DENIED + * 5. Access sub-objects GRANTED MB #2 MB #7 + * 6. Create sub-objects GRANTED MB #3 MB #8 + * 7. Modify sub-objects GRANTED MB #4 DENIED + * 8. Modify sub-objects' members GRANTED MB #5 DENIED + * 9. Modify sub-objects' permissions GRANTED DENIED DENIED + * + * 012345678 + * EG: 001111000 = 120 --implies--> that members may access,create,and + * fully modify sub-objects. All other + * options are DENIED. This mask value + * of 120 is a recommended starting point. + * + * Permissions can be overridden down-stream, but permissions will cascade otherwise. + */ + +/* System-wide admin settings */ +DROP TABLE IF EXISTS `setting`; +CREATE TABLE `setting` ( + `key` varchar(50) NOT NULL, + `value` varchar(50) DEFAULT NULL, + PRIMARY KEY (`key`) +); + +/* 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) DEFAULT NULL, + `parent` varchar(10) DEFAULT NULL, + `name` varchar(50) NOT NULL, + `timeCreated` datetime NOT NULL, + `timeUpdated` datetime NOT NULL, + `type` enum( + 'user', + 'extern-user', + 'group', + 'pad', + 'stage', + 'issue', + 'message', + 'log' + ) NOT NULL, + PRIMARY KEY (`guid`) +); + +/* 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`) +); + +/* Scheme for logging if a message has been seen by a user on a wide scale */ +DROP TABLE IF EXISTS `msg_read`; +CREATE TABLE `msg_read` ( + `guid` varchar(10) NOT NULL, + `user` varchar(10) NOT NULL, + PRIMARY KEY (`guid`,`user`) +); + +/* User objects - special attributes */ +/* Notice: 'key' field is the user's hashed and salted password -- SHA256 */ +/* Notice: 'salt' is a random SHA256 output, used as salt for user's password */ +DROP TABLE IF EXISTS `user`; +CREATE TABLE `user` ( + `guid` varchar(10) NOT NULL, + `key` varchar(64) NOT NULL, + `salt` varchar(64) NOT NULL, + `alias` varchar(50) DEFAULT NULL, + `admin` int(10) unsigned NOT NULL DEFAULT 0, + `email` varchar(50) DEFAULT NULL, + `emailConf` int(10) unsigned NOT NULL DEFAULT 0, + `emailConfKey` varchar(64) NOT NULL, + PRIMARY KEY (`guid`) +); + +/* Extern-user objects - special attributes */ +/* Notice: these objects are contructred on portal pages or if a scrott user gives ownership of an object to an external user. */ +/* Notice: This table should be searched for addresses while creating a new normal user object. If in the event the users email address exists here, it should be promoted to a full user account */ +DROP TABLE IF EXISTS `extern-user`; +CREATE TABLE `extern-user` ( + `guid` varchar(10) NOT NULL, + `email` varchar(50) NOT NULL, + `emailConfKey` varchar(64) NOT NULL, + PRIMARY KEY (`guid`) +); + +/* object type 'group' has no unique attributes */ + +/* Pad objects - special attributes */ +DROP TABLE IF EXISTS `pad`; +CREATE TABLE `pad` ( + `guid` varchar(10) NOT NULL, + `stage` varchar(10) DEFAULT NULL, + `nextIssueNumber` int(10) NOT NULL DEFAULT 0, + PRIMARY KEY (`guid`) +); + +/* Stage objects - special attributes */ +DROP TABLE IF EXISTS `stage`; +CREATE TABLE `stage` ( + `guid` varchar(10) NOT NULL, + `stage` varchar(10) DEFAULT NULL, + PRIMARY KEY (`guid`) +); + +/* Issue objects - special attributes */ +/* Notice: 'closed' issues should be denoted by being a child of the pad directly, and not the child of a pad's stages */ +/* Notice: an issue is 'unread' if the assignee has yet to review the issue. Self assigned issues, by definition, cannot be in unread state. */ +DROP TABLE IF EXISTS `issue`; +CREATE TABLE `issue` ( + `guid` varchar(10) NOT NULL, + `number` int(10) unsigned NOT NULL, + `assignee` varchar(10) DEFAULT NULL, + `unread` int(10) unsigned NOT NULL DEFAULT 1, + `desc` text DEFAULT NULL, + `due` datetime DEFAULT NULL, + `tags` text DEFAULT NULL, + PRIMARY KEY (`guid`) +); + +/* Message objects - special attributes */ +/* Notice: These are discussion messages, comments on issues, and also private messages sent directly to users */ +/* Notice: System log enteries use this struct to store data as well */ +DROP TABLE IF EXISTS `message`; +CREATE TABLE `message` ( + `guid` varchar(10) NOT NULL, + `author` varchar(10) NOT NULL, + `message` text DEFAULT NULL, + PRIMARY KEY (`guid`) +); diff --git a/examples/schema.sql b/examples/schema.sql index 9770ffa..e6826cf 100644 --- a/examples/schema.sql +++ b/examples/schema.sql @@ -1,164 +1,203 @@ /* - * SCROTT Copyright (C) 2016 Malf Furious + * SCROTT IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR + * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF + * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. + * IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR + * OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, + * ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR + * OTHER DEALINGS IN THE SOFTWARE. * - * Scrott is free software: you can redistribute it and/or modify - * it under the terms of the GNU General Public License as published - * by the Free Software Foundation, either version 3 of the License, - * or (at your option) any later version. + * For more information, please refer to UNLICENSE + */ + +/* + * Execute this SQL script file to create or recreate a relational + * database schema for use with Scrott. This file targets MySQL (MariaDB), + * however may be applicable to other DBMSs as well. If necessary, + * alternative versions of this file may be tracked to support those + * systems. * - * Scrott is distributed in the hope that it will be useful, but - * WITHOUT ANY WARRANTY; even the implied warranty of MERCHANTABILITY - * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public - * License for more details. + * Scrott's data model emulates object-oriented structures in the database + * schema and features a generic "object" table containing fields pertinent + * all object types. Additional tables extend this for each class of + * objects, referencing a row in the object table by GUID. All objects are + * uniquely identifiable by a GUID which is, in our case, an 8-digit + * hexadecimal string. */ -CREATE DATABASE IF NOT EXISTS `db_scrott`; -USE `db_scrott`; +CREATE DATABASE IF NOT EXISTS db_scrott; +USE db_scrott; /* - * Description of SCROTT object permissions system - * ----------------------------------------------- - * Every object has an owner, zero or more additional members, and then there is the general - * public. - * - * OWNER = user OR group - * MEMBER = user - * OWNER MEMBERS OTHERS - * Permissions are: ------------------------------------------------ - * 1. Access object GRANTED GRANTED MB #6 - * 2. Modify object GRANTED MB #0 DENIED - * 3. Modify members GRANTED MB #1 DENIED - * 4. Modify permissions GRANTED DENIED DENIED - * 5. Access sub-objects GRANTED MB #2 MB #7 - * 6. Create sub-objects GRANTED MB #3 MB #8 - * 7. Modify sub-objects GRANTED MB #4 DENIED - * 8. Modify sub-objects' members GRANTED MB #5 DENIED - * 9. Modify sub-objects' permissions GRANTED DENIED DENIED - * - * 012345678 - * EG: 001111000 = 120 --implies--> that members may access,create,and - * fully modify sub-objects. All other - * options are DENIED. This mask value - * of 120 is a recommended starting point. - * - * Permissions can be overridden down-stream, but permissions will cascade otherwise. + * This table describes a dictionary used for recording system settings + * and configuration. These are application-wide and only writable by + * administrators. This table will only hold values which have been + * changed and the app will use built-in defaults for settings *not* + * defined by this table. */ +DROP TABLE IF EXISTS settings; +CREATE TABLE settings ( + name varchar(64) NOT NULL, + value varchar(64) DEFAULT NULL, -/* System-wide admin settings */ -DROP TABLE IF EXISTS `setting`; -CREATE TABLE `setting` ( - `key` varchar(50) NOT NULL, - `value` varchar(50) DEFAULT NULL, - PRIMARY KEY (`key`) + PRIMARY KEY (name) ); -/* 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) DEFAULT NULL, - `parent` varchar(10) DEFAULT NULL, - `name` varchar(50) NOT NULL, - `timeCreated` datetime NOT NULL, - `timeUpdated` datetime NOT NULL, - `type` enum( - 'user', - 'extern-user', - 'group', - 'pad', - 'stage', - 'issue', - 'message', - 'log' - ) NOT NULL, - PRIMARY KEY (`guid`) +/* + * This table tracks membership. Users may be members of groups, pads, + * and issues as part of the permissions system. This table is a many- + * to-many relationship between users and other objects that is + * representative of existing memberships. + */ +DROP TABLE IF EXISTS members; +CREATE TABLE members ( + guid varchar(8) NOT NULL, + member varchar(8) NOT NULL, + + PRIMARY KEY (guid, 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`) +/* + * This table records whether messages have been seen by any user. This + * table is a many-to-many relationship between users and messages. + */ +DROP TABLE IF EXISTS views; +CREATE TABLE views ( + guid varchar(8) NOT NULL, + viewer varchar(8) NOT NULL, + + PRIMARY KEY (guid, viewer) ); -/* Scheme for logging if a message has been seen by a user on a wide scale */ -DROP TABLE IF EXISTS `msg_read`; -CREATE TABLE `msg_read` ( - `guid` varchar(10) NOT NULL, - `user` varchar(10) NOT NULL, - PRIMARY KEY (`guid`,`user`) +/* + * Base table for Scrott objects + * + * Explaination of permissions... + * Every object has an owner, zero or more additional members, and then there is the general + * public. + * OWNER MEMBERS PUBLIC + * Permissions are: ------------------------------------------ + * Access object GRANTED GRANTED config + * Modify object GRANTED config DENIED + * Modify members GRANTED config DENIED + * Modify permissions GRANTED DENIED DENIED + * Access sub-objects GRANTED config config + * Create sub-objects GRANTED config config + * Modify sub-objects GRANTED config DENIED + * Modify sub-objects' members GRANTED config DENIED + * Modify sub-objects' permissions GRANTED DENIED DENIED + * + * Permissions can be overridden in sub-objects, but will cascade otherwise. + */ +DROP TABLE IF EXISTS objects; +CREATE TABLE objects ( + guid varchar(8) NOT NULL, + owner varchar(8) DEFAULT NULL, + parent varchar(8) DEFAULT NULL, + name varchar(64) NOT NULL, + created datetime NOT NULL, + updated datetime NOT NULL, + + membModify int(1) NOT NULL DEFAULT 0, /* members can modify object */ + membMemb int(1) NOT NULL DEFAULT 0, /* members can modify members */ + membAccs int(1) NOT NULL DEFAULT 1, /* members can access sub-objects */ + membCres int(1) NOT NULL DEFAULT 1, /* members can create sub-objects */ + membModifys int(1) NOT NULL DEFAULT 1, /* members can modify sub-objects */ + membMembs int(1) NOT NULL DEFAULT 1, /* members can modify sub-obj members */ + pubAcc int(1) NOT NULL DEFAULT 0, /* public can access object */ + pubAccs int(1) NOT NULL DEFAULT 0, /* public can access sub-objects */ + pubCres int(1) NOT NULL DEFAULT 0, /* public can create sub-objects */ + + objtype enum ( + 'user', + 'group', + 'pad', + 'stage', + 'issue', + 'message', + 'log' + ) NOT NULL, + + PRIMARY KEY (guid) ); -/* User objects - special attributes */ -/* Notice: 'key' field is the user's hashed and salted password -- SHA256 */ -/* Notice: 'salt' is a random SHA256 output, used as salt for user's password */ -DROP TABLE IF EXISTS `user`; -CREATE TABLE `user` ( - `guid` varchar(10) NOT NULL, - `key` varchar(64) NOT NULL, - `salt` varchar(64) NOT NULL, - `alias` varchar(50) DEFAULT NULL, - `admin` int(10) unsigned NOT NULL DEFAULT 0, - `email` varchar(50) DEFAULT NULL, - `emailConf` int(10) unsigned NOT NULL DEFAULT 0, - `emailConfKey` varchar(64) NOT NULL, - PRIMARY KEY (`guid`) +/* + * Users -- extends objects + */ +DROP TABLE IF EXISTS users; +CREATE TABLE users ( + guid varchar(8) NOT NULL, + auth varchar(64) NOT NULL, /* user's salted and hashed passwd -- SHA256 */ + salt varchar(64) NOT NULL, /* random SHA256 output, used as salt for auth */ + alias varchar(64) DEFAULT NULL, + email varchar(64) DEFAULT NULL, + emailVer varchar(8) NOT NULL, + admin int(1) NOT NULL DEFAULT 0, + reg int(1) NOT NULL DEFAULT 0, /* if false, user doesn't have valid credentials */ + emailConf int(1) NOT NULL DEFAULT 0, + + PRIMARY KEY (guid) ); -/* Extern-user objects - special attributes */ -/* Notice: these objects are contructred on portal pages or if a scrott user gives ownership of an object to an external user. */ -/* Notice: This table should be searched for addresses while creating a new normal user object. If in the event the users email address exists here, it should be promoted to a full user account */ -DROP TABLE IF EXISTS `extern-user`; -CREATE TABLE `extern-user` ( - `guid` varchar(10) NOT NULL, - `email` varchar(50) NOT NULL, - `emailConfKey` varchar(64) NOT NULL, - PRIMARY KEY (`guid`) +/* + * Groups -- extends objects + */ +DROP TABLE IF EXISTS groups; +CREATE TABLE groups ( + guid varchar(8) NOT NULL, + + PRIMARY KEY (guid) ); -/* object type 'group' has no unique attributes */ +/* + * Pads -- extends objects + */ +DROP TABLE IF EXISTS pads; +CREATE TABLE pads ( + guid varchar(8) NOT NULL, + stage varchar(8) DEFAULT NULL, + issueNumb int(32) NOT NULL DEFAULT 0, -/* Pad objects - special attributes */ -DROP TABLE IF EXISTS `pad`; -CREATE TABLE `pad` ( - `guid` varchar(10) NOT NULL, - `stage` varchar(10) DEFAULT NULL, - `nextIssueNumber` int(10) NOT NULL DEFAULT 0, - PRIMARY KEY (`guid`) + PRIMARY KEY (guid) ); -/* Stage objects - special attributes */ -DROP TABLE IF EXISTS `stage`; -CREATE TABLE `stage` ( - `guid` varchar(10) NOT NULL, - `stage` varchar(10) DEFAULT NULL, - PRIMARY KEY (`guid`) +/* + * Stages -- extends objects + */ +DROP TABLE IF EXISTS stages; +CREATE TABLE stages ( + guid varchar(8) NOT NULL, + stage varchar(8) DEFAULT NULL, + + PRIMARY KEY (guid) ); -/* Issue objects - special attributes */ -/* Notice: 'closed' issues should be denoted by being a child of the pad directly, and not the child of a pad's stages */ -/* Notice: an issue is 'unread' if the assignee has yet to review the issue. Self assigned issues, by definition, cannot be in unread state. */ -DROP TABLE IF EXISTS `issue`; -CREATE TABLE `issue` ( - `guid` varchar(10) NOT NULL, - `number` int(10) unsigned NOT NULL, - `assignee` varchar(10) DEFAULT NULL, - `unread` int(10) unsigned NOT NULL DEFAULT 1, - `desc` text DEFAULT NULL, - `due` datetime DEFAULT NULL, - `tags` text DEFAULT NULL, - PRIMARY KEY (`guid`) +/* + * Issues -- extends objects + */ +DROP TABLE IF EXISTS issues; +CREATE TABLE issues ( + guid varchar(8) NOT NULL, + numb int(32) NOT NULL, + assignee varchar(8) DEFAULT NULL, + seen int(1) NOT NULL DEFAULT 0, /* has the assignee seen this yet? */ + description text DEFAULT NULL, + due datetime DEFAULT NULL, + tags varchar(64) DEFAULT NULL, + + PRIMARY KEY (guid) ); -/* Message objects - special attributes */ -/* Notice: These are discussion messages, comments on issues, and also private messages sent directly to users */ -/* Notice: System log enteries use this struct to store data as well */ -DROP TABLE IF EXISTS `message`; -CREATE TABLE `message` ( - `guid` varchar(10) NOT NULL, - `author` varchar(10) NOT NULL, - `message` text DEFAULT NULL, - PRIMARY KEY (`guid`) +/* + * Messages / logs -- extends objects + */ +DROP TABLE IF EXISTS mesgs; +CREATE TABLE mesgs ( + guid varchar(8) NOT NULL, + author varchar(8) NOT NULL, + mesg text DEFAULT NULL, + attachName varchar(64) DEFAULT NULL, + attachMime varchar(64) DEFAULT NULL, + + PRIMARY KEY (guid) ); |