From 5b50f0490b6f177cfabc5646c1786d1140598507 Mon Sep 17 00:00:00 2001 From: Malf Furious Date: Tue, 6 Feb 2018 02:40:27 -0500 Subject: Add sample nginx configuration I've recently been fighting my dev server to actually work with PHP. It seems like everytime I hop a system, or install nginx from scratch, something in my config is broken. So, I'm finally committing a 'working' (TM) baseline config to work off of when working with servers in the future. The idea is to add an Apache one at some point, too. --- srvs/nginx.conf | 81 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 81 insertions(+) create mode 100644 srvs/nginx.conf (limited to 'srvs') diff --git a/srvs/nginx.conf b/srvs/nginx.conf new file mode 100644 index 0000000..a5e81f1 --- /dev/null +++ b/srvs/nginx.conf @@ -0,0 +1,81 @@ +## +# 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 file is for configuring NGINX to work with Scrott. This is not +# a complete configuration, just a simple and genaric server {} block +# to use as a starting-point for setting up your server. +## + +server +{ + listen 80; + listen [::]:80 ipv6only=on; + listen 443 ssl; + listen [::]:443 ssl ipv6only=on; + + # set to your instance # + server_name localhost; + root /usr/share/nginx/scrott; + + index index.php; + keepalive_timeout 70; + + ## + # Completely deny access to user-supplied content. This content + # should instead be served by the df.php script, to enforce + # access permissions and prevent code-execution. + ## + location ^~ /dynmic + { + deny all; + return 404; + } + + ## + # Allow direct access to built-in static content, instead of + # passing these requests to the PHP system. + ## + location ^~ /static + { + try_files $uri $uri/ =404; + } + + ## + # Process normal requests via (clean) url rewriting. Unless + # the requested path exists on disk, pass it to index.php + # for processing. + ## + location / + { + rewrite ^(.*)$ /index.php$1; + } + + ## + # Hand-off completed php rewrites (or direct requests) to + # php-fpm for processing. + ## + location ~ [^/]\.php(/|$) + { + fastcgi_split_path_info ^((?U).+\.php)(.*)$; + + try_files $fastcgi_script_name =404; + + include fastcgi_params; + set $path_info $fastcgi_path_info; + fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name; + fastcgi_param PATH_INFO $path_info; + + fastcgi_pass unix:/var/run/php-fpm/php-fpm.sock; + fastcgi_index index.php; + } +} -- cgit v1.2.3 From b9bdf474f838d1435dc0cba1f6ffa9e1e26520b6 Mon Sep 17 00:00:00 2001 From: Malf Furious Date: Thu, 20 Sep 2018 19:05:56 -0400 Subject: mysql: Move schema file into srvs/ This places all (both) of the service configuration files together and cleans up the top-level directory. The file is renamed to 'mysql.sql' to indicate the particular service it corresponds to. --- srvs/mysql.sql | 202 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 202 insertions(+) create mode 100644 srvs/mysql.sql (limited to 'srvs') diff --git a/srvs/mysql.sql b/srvs/mysql.sql new file mode 100644 index 0000000..13db8c7 --- /dev/null +++ b/srvs/mysql.sql @@ -0,0 +1,202 @@ +/* + * 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 + */ + +/* + * 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'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; + +/* + * 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) NOT NULL DEFAULT '', + + PRIMARY KEY (name) +); + +/* + * 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) +); + +/* + * 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) +); + +/* + * 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) NOT NULL DEFAULT '', + parent varchar(8) NOT NULL DEFAULT '', + name varchar(64) NOT NULL DEFAULT '', + 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', + 'mesg', + 'log' + ) 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) NOT NULL DEFAULT '', + email varchar(64) NOT NULL DEFAULT '', + emailVer varchar(8) NOT NULL DEFAULT '', + 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) +); + +/* + * Groups -- extends objects + */ +DROP TABLE IF EXISTS groups; +CREATE TABLE groups ( + guid varchar(8) NOT NULL, + + PRIMARY KEY (guid) +); + +/* + * Pads -- extends objects + */ +DROP TABLE IF EXISTS pads; +CREATE TABLE pads ( + guid varchar(8) NOT NULL, + stage varchar(8) NOT NULL DEFAULT '', + issueNumb int(32) NOT NULL DEFAULT 0, + + PRIMARY KEY (guid) +); + +/* + * Stages -- extends objects + */ +DROP TABLE IF EXISTS stages; +CREATE TABLE stages ( + guid varchar(8) NOT NULL, + stage varchar(8) NOT NULL DEFAULT '', + + PRIMARY KEY (guid) +); + +/* + * Issues -- extends objects + */ +DROP TABLE IF EXISTS issues; +CREATE TABLE issues ( + guid varchar(8) NOT NULL, + numb int(32) NOT NULL DEFAULT 0, + assignee varchar(8) NOT NULL DEFAULT '', + seen int(1) NOT NULL DEFAULT 0, /* has the assignee seen this yet? */ + description text NOT NULL, + due varchar(64) NOT NULL DEFAULT '', + tags text NOT 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 DEFAULT '', + mesg text NOT NULL, + attachment varchar(64) NOT NULL DEFAULT '', + + PRIMARY KEY (guid) +); -- cgit v1.2.3 From 9df5344050ec0a2b8bec03c7a89fff9d7d41ce2f Mon Sep 17 00:00:00 2001 From: Malf Furious Date: Sat, 20 Oct 2018 21:24:01 -0400 Subject: issue: Add author and authored fields --- srvs/mysql.sql | 2 ++ 1 file changed, 2 insertions(+) (limited to 'srvs') diff --git a/srvs/mysql.sql b/srvs/mysql.sql index 13db8c7..178fb57 100644 --- a/srvs/mysql.sql +++ b/srvs/mysql.sql @@ -180,8 +180,10 @@ CREATE TABLE issues ( guid varchar(8) NOT NULL, numb int(32) NOT NULL DEFAULT 0, assignee varchar(8) NOT NULL DEFAULT '', + author varchar(8) NOT NULL DEFAULT '', seen int(1) NOT NULL DEFAULT 0, /* has the assignee seen this yet? */ description text NOT NULL, + authored varchar(64) NOT NULL DEFAULT '', due varchar(64) NOT NULL DEFAULT '', tags text NOT NULL, -- cgit v1.2.3 From b093b3affe3ac6878e2242bff310dc466687a825 Mon Sep 17 00:00:00 2001 From: Malf Furious Date: Sat, 20 Oct 2018 21:43:46 -0400 Subject: issue: Add open/close data --- srvs/mysql.sql | 3 +++ 1 file changed, 3 insertions(+) (limited to 'srvs') diff --git a/srvs/mysql.sql b/srvs/mysql.sql index 178fb57..9f3193b 100644 --- a/srvs/mysql.sql +++ b/srvs/mysql.sql @@ -181,9 +181,12 @@ CREATE TABLE issues ( numb int(32) NOT NULL DEFAULT 0, assignee varchar(8) NOT NULL DEFAULT '', author varchar(8) NOT NULL DEFAULT '', + closer varchar(8) NOT NULL DEFAULT '', seen int(1) NOT NULL DEFAULT 0, /* has the assignee seen this yet? */ description text NOT NULL, + opened varchar(64) NOT NULL DEFAULT '', authored varchar(64) NOT NULL DEFAULT '', + closed varchar(64) NOT NULL DEFAULT '', due varchar(64) NOT NULL DEFAULT '', tags text NOT NULL, -- cgit v1.2.3 From b690505b0e1e255e5081adcf49c724186bb831c2 Mon Sep 17 00:00:00 2001 From: Malf Furious Date: Sat, 20 Oct 2018 21:49:32 -0400 Subject: issue: Add assigned timestamp --- srvs/mysql.sql | 1 + 1 file changed, 1 insertion(+) (limited to 'srvs') diff --git a/srvs/mysql.sql b/srvs/mysql.sql index 9f3193b..9bad437 100644 --- a/srvs/mysql.sql +++ b/srvs/mysql.sql @@ -185,6 +185,7 @@ CREATE TABLE issues ( seen int(1) NOT NULL DEFAULT 0, /* has the assignee seen this yet? */ description text NOT NULL, opened varchar(64) NOT NULL DEFAULT '', + assigned varchar(64) NOT NULL DEFAULT '', authored varchar(64) NOT NULL DEFAULT '', closed varchar(64) NOT NULL DEFAULT '', due varchar(64) NOT NULL DEFAULT '', -- cgit v1.2.3 From 479fa31398d18f105616de83b5b5108278b75c59 Mon Sep 17 00:00:00 2001 From: Malf Furious Date: Sun, 21 Oct 2018 21:18:21 -0400 Subject: issue: Redesign schema I found myself complicating the data model of this class of objects and wanted to take a clean approach to its design. The key differences are as follows: * We now reference a message object for the issue's OP, as opposed to directly containing the message data This affords the OP _all_ of the standard features of a Scrott message, including separately tracked authorship data, file attachments. * Multiple assignees is implemented in the design Finally. * Seen flag is removed This can be implicitly tracked via all sub-object messages and the views meta-table. --- srvs/mysql.sql | 31 +++++++++++++++++++++---------- 1 file changed, 21 insertions(+), 10 deletions(-) (limited to 'srvs') diff --git a/srvs/mysql.sql b/srvs/mysql.sql index 9bad437..72167d4 100644 --- a/srvs/mysql.sql +++ b/srvs/mysql.sql @@ -69,6 +69,23 @@ CREATE TABLE views ( PRIMARY KEY (guid, viewer) ); +/* + * Scrott issues may have multiple assignees. This table is used to + * co-relate assignees and issues along with additional meta-data. + */ +DROP TABLE IF EXISTS assignees; +CREATE TABLE assignees ( + guid varchar(8) NOT NULL, /* guid of issue */ + assignee varchar(8) NOT NULL, /* user */ + assigner varchar(8) NOT NULL, /* user */ + assigned datetime NOT NULL, /* timestamp */ + dismisser varchar(8) NOT NULL DEFAULT '', /* user */ + dismissed varchar(64) NOT NULL DEFAULT '', /* timestamp */ + signedoff varchar(64) NOT NULL DEFAULT '', /* timestamp */ + + PRIMARY KEY (guid, assignee) +); + /* * Base table for Scrott objects * @@ -179,16 +196,10 @@ DROP TABLE IF EXISTS issues; CREATE TABLE issues ( guid varchar(8) NOT NULL, numb int(32) NOT NULL DEFAULT 0, - assignee varchar(8) NOT NULL DEFAULT '', - author varchar(8) NOT NULL DEFAULT '', - closer varchar(8) NOT NULL DEFAULT '', - seen int(1) NOT NULL DEFAULT 0, /* has the assignee seen this yet? */ - description text NOT NULL, - opened varchar(64) NOT NULL DEFAULT '', - assigned varchar(64) NOT NULL DEFAULT '', - authored varchar(64) NOT NULL DEFAULT '', - closed varchar(64) NOT NULL DEFAULT '', - due varchar(64) NOT NULL DEFAULT '', + mesg varchar(8) NOT NULL DEFAULT '', + closer varchar(8) NOT NULL DEFAULT '', /* user */ + closed varchar(64) NOT NULL DEFAULT '', /* timestamp */ + due varchar(64) NOT NULL DEFAULT '', /* timestamp */ tags text NOT NULL, PRIMARY KEY (guid) -- cgit v1.2.3 From 8ee07502cc84bb878efaf637947d543689eb38bf Mon Sep 17 00:00:00 2001 From: Malf Furious Date: Sat, 27 Oct 2018 17:09:09 -0400 Subject: Fix bug in database schema These text fields need a default of NULL aparently. Signed-off-by: Malf Furious --- srvs/mysql.sql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'srvs') diff --git a/srvs/mysql.sql b/srvs/mysql.sql index 72167d4..de5ce85 100644 --- a/srvs/mysql.sql +++ b/srvs/mysql.sql @@ -200,7 +200,7 @@ CREATE TABLE issues ( closer varchar(8) NOT NULL DEFAULT '', /* user */ closed varchar(64) NOT NULL DEFAULT '', /* timestamp */ due varchar(64) NOT NULL DEFAULT '', /* timestamp */ - tags text NOT NULL, + tags text DEFAULT NULL, PRIMARY KEY (guid) ); @@ -212,7 +212,7 @@ DROP TABLE IF EXISTS mesgs; CREATE TABLE mesgs ( guid varchar(8) NOT NULL, author varchar(8) NOT NULL DEFAULT '', - mesg text NOT NULL, + mesg text DEFAULT NULL, attachment varchar(64) NOT NULL DEFAULT '', PRIMARY KEY (guid) -- cgit v1.2.3