From 6c745ea0f54e660577d070b2ddaf4977039a094a Mon Sep 17 00:00:00 2001 From: Alex Vitkov Date: Thu, 3 Dec 2020 21:10:17 +0200 Subject: initial --- sql/fileshare.sql | 49 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 49 insertions(+) create mode 100644 sql/fileshare.sql (limited to 'sql/fileshare.sql') diff --git a/sql/fileshare.sql b/sql/fileshare.sql new file mode 100644 index 0000000..f927ffc --- /dev/null +++ b/sql/fileshare.sql @@ -0,0 +1,49 @@ +drop database fileshare; + + + + + +create database fileshare; +use fileshare; + +/*base user information*/ +create table users ( + id int not null auto_increment, + username varchar(50) not null unique, + password varchar(100) not null unique, + primary key (id) +); + +/*table has only one owner and is identifyed by a number*/ +create table files ( + id int not null auto_increment, + owner int default null, + absolutepath varchar(500) not null, + type varchar(20) not null default 'data', + primary key (id), + foreign key (owner) references users(id) +); + +/*the user with userid is given some kind of access to the file with fileid*/ +/*there is no edit bit because it will be too dificult to implement prehaps a change bit is in order (but not an edit bit)*/ +/*might be beneficial to even go full minimalist and remove the remove bit and only have the view bit*/ +create table access ( + fileid int not null, + userid int not null, + canview boolean not null default true, + canremove boolean not null default false, + check (canview=true or canremove=true) , + foreign key (fileid) references files(id), + foreign key (userid) references users(id) +); + + + +/*basic info for testing purposes*/ +insert into users(username,password) values ("root","asdf"); +insert into users(username,password) values ("tester","tester"); +insert into files(owner,absolutepath,type) values (1,"/root/jiberish.sh","shell script"); +insert into access(fileid,userid,canview,canremove) values(1,2,true,false); +/*I am not sure why this passes ....*/ +insert into access(fileid,userid,canview,canremove) values(1,2,false,false); -- cgit v1.2.3