introducing directories

1 files changed, 33 insertions, 25 deletions

diff --git a/sql/fileshare.sql b/sql/fileshare.sql
index dfb9010..5740a48 100644
--- a/sql/fileshare.sql
+++ b/sql/fileshare.sql
@@ -1,32 +1,40 @@
+create table nodes (
+	node_id int not null auto_increment,
+	is_directory boolean default false,
+	relative_path varchar(500) not null,
+	type varchar(20) not null default 'data',
+	name varchar(100) not null default 'no name',
+	primary key (node_id)
+);
+
 /*base user information*/
 create table users (
-  user_id int not null auto_increment,
-  username varchar(50) not null unique,
-  password varchar(255) not null,
-  email varchar(50),
-  primary key (user_id)
+	user_id int not null auto_increment,
+	username varchar(50) not null unique,
+	password varchar(255) not null,
+	email varchar(50),
+	home_directory int not null,
+	primary key (user_id),
+	foreign key (home_directory) references nodes(node_id)
 );
 
-/*table has only one owner and is identifyed by a number*/
-create table files (
-  file_id int not null auto_increment,
-  owner_id int default null,
-  relative_path varchar(500) not null,
-  type varchar(20) not null default 'data',
-  primary key (file_id),
-  foreign key (owner_id) references users(user_id)
+create table node_access (
+	node_id int not null,
+	user_id int not null,
+
+	can_view boolean not null default true,
+	can_edit boolean not null default false,
+	check (can_view=true or can_edit=true) ,
+	foreign key (node_id) references nodes(node_id),
+	foreign key (user_id) references users(user_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 (
-  file_id int not null,
-  user_id int not null,
-   
-  can_view boolean not null default true,
-  can_remove boolean not null default false,
-  check (can_view=true or can_remove=true) ,
-  foreign key (file_id) references files(file_id),
-  foreign key (user_id) references users(user_id)
+create table node_links (
+	directory_id int not null,
+	node_id int not null,
+	check (directory_id != node_id), 
+	foreign key (directory_id) references nodes(node_id),
+	foreign key (node_id) references nodes(node_id)
 );
+
+