aboutsummaryrefslogtreecommitdiffstats
path: root/sql/fileshare.sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql/fileshare.sql')
-rw-r--r--sql/fileshare.sql49
1 files changed, 49 insertions, 0 deletions
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);