aboutsummaryrefslogtreecommitdiffstats
path: root/INIT_DATABASE.sql
blob: d96a4b07061359df4b9293117fc8b97b31c0aa28 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
create database if not exists fileup;
use fileup;

/*BEWARE!*/
drop table if exists node_access;
drop table if exists users;
drop table if exists node_links;
drop table if exists trash;
drop table if exists super_trash;
drop table if exists shared_nodes;
drop table if exists nodes;



drop trigger if exists delete_on_zero_links;
drop trigger if exists delete_links;
drop trigger if exists del_node;
drop trigger if exists supper_del_node;





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',
	code varchar(100) not null default "error",
	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),
	home_directory int not null,
	primary key (user_id),
	foreign key (home_directory) references nodes(node_id) on delete cascade
);

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,
	foreign key (node_id) references nodes(node_id) on delete cascade,
	foreign key (user_id) references users(user_id) on delete cascade
);
/*we can name a node in many different ways */
create table node_links (
	directory_id int not null,
	node_id int not null,
	name varchar(100) not null default 'no name',
	note varchar(200) not null default "",
	foreign key (directory_id) references nodes(node_id) on delete cascade,
	foreign key (node_id) references nodes(node_id) on delete cascade
);

/*we store passwords for the shared links here, it doesn't really have anything to do with the filesystem*/
create table shared_nodes (
	node_id int not null,
	passcode varchar(100) default "",
	code varchar(100) default "",
	foreign key (node_id) references nodes(node_id) on delete cascade
);

create table trash (
	node_id int not null
);
create table super_trash (
	node_id int not null
);


create trigger delete_on_zero_links
	after delete
	on node_links
	for each row 
		insert into trash
		select nodes.node_id
		from nodes
		where nodes.node_id not in (select node_id from node_links) and 
					(nodes.node_id=old.node_id );
			
create trigger del_node
	after delete
	on trash
	for each row
		insert into super_trash(node_id) 
			select node_id
			from nodes
			where nodes.node_id=old.node_id;
	

create trigger supper_del_node
	after delete
	on super_trash
	for each row
		insert into trash 
		select node_id
		from nodes
		where nodes.node_id=old.node_id;

create trigger delete_links
	before delete
	on super_trash
	for each row
		delete from node_links
		where directory_id=old.node_id;