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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
--
-- Create database
--
CREATE DATABASE IF NOT EXISTS wzdftpd;
GRANT ALL ON `wzdftpd`.* TO "wzdftpd"@"localhost" IDENTIFIED BY "wzdftpd";
FLUSH PRIVILEGES;
use wzdftpd;
--
-- Table structure for table `ugroups`
--
CREATE TABLE groups (
ref INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
groupname TINYTEXT NOT NULL,
gid int(10) unsigned NOT NULL UNIQUE,
defaultpath TINYTEXT NOT NULL,
tagline TINYTEXT default NULL,
groupperms int(10) unsigned default NULL,
max_idle_time int(10) unsigned default NULL,
num_logins smallint(5) unsigned default NULL,
max_ul_speed double unsigned default NULL,
max_dl_speed double unsigned default NULL,
ratio int(10) unsigned default NULL,
PRIMARY KEY (ref,gid)
) TYPE=MyISAM;
--
-- Dumping data for table `groups`
--
INSERT INTO groups (groupname,gid,defaultpath,tagline) VALUES ('admin',1,'/','admin group');
--
-- Table structure for table `users`
--
CREATE TABLE users (
ref INT(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
username TINYTEXT NOT NULL,
userpass varchar(48) default NULL,
rootpath TINYTEXT NOT NULL,
tagline TINYTEXT default NULL,
uid int(10) unsigned NOT NULL UNIQUE,
flags varchar(32) default NULL,
max_idle_time int(10) unsigned default NULL,
max_ul_speed double unsigned default NULL,
max_dl_speed double unsigned default NULL,
num_logins smallint(5) unsigned default NULL,
ratio int(10) unsigned default NULL,
user_slots int(10) unsigned default NULL,
leech_slots int(10) unsigned default NULL,
perms int(10) unsigned default NULL,
credits bigint unsigned default NULL,
last_login time default NULL
) TYPE=MyISAM;
--
-- Dumping data for table `users`
--
INSERT INTO users VALUES ('','wzdftpd',NULL,'/','local admin',1,"OIstH",NULL,NULL,NULL,NULL,NULL,NULL,NULL,0xffffffff,NULL,NULL);
INSERT INTO users VALUES ('','novel',NULL,'/usr/home/novel',NULL,2,"OIstH",NULL,NULL,NULL,NULL,NULL,NULL,NULL,0xffffffff,NULL,NULL);
INSERT INTO users VALUES ('','anonymous',NULL,'/tmp',NULL,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0xffffffff,NULL,NULL);
--
-- Table structure for table `ugr` (User-Group Relations)
--
CREATE TABLE ugr (
uref int(10) unsigned NOT NULL,
gref int(10) unsigned NOT NULL,
PRIMARY KEY(uref,gref)
) TYPE=MyISAM;
--
-- Table structure for table `IP` (User/Group IPs)
--
CREATE TABLE groupip (
ref int(10) unsigned NOT NULL,
ip VARCHAR(255) NOT NULL,
PRIMARY KEY(ref,ip)
) TYPE=MyISAM;
--
-- Table structure for table `IP` (User/Group IPs)
--
CREATE TABLE userip (
ref int(10) unsigned NOT NULL,
ip VARCHAR(255) NOT NULL,
PRIMARY KEY(ref,ip)
) TYPE=MyISAM;
INSERT INTO userip VALUES(1,"127.0.0.1");
INSERT INTO userip VALUES(2,"foobar@localhost");
INSERT INTO userip VALUES(2,"127.0.0.1");
--
-- Table structure for table `stats`
--
CREATE TABLE stats (
ref int(10) unsigned NOT NULL,
bytes_ul_total bigint unsigned default NULL,
bytes_dl_total bigint unsigned default NULL,
files_ul_total int(10) unsigned default NULL,
files_dl_total int(10) unsigned default NULL,
PRIMARY KEY(ref)
) TYPE=MyISAM;
INSERT INTO stats (ref) VALUES (1);
INSERT INTO stats (ref) VALUES (2);
--
-- hmm - moo, moo; I'm trying to insert references into ugr
--
INSERT into ugr (uref,gref) SELECT users.ref,groups.ref FROM users,groups WHERE users.uid=1 AND groups.gid=1;
-- insert novel into admin group (he's a good friend !)
INSERT into ugr (uref,gref) SELECT users.ref,groups.ref FROM users,groups WHERE users.uid=2 AND groups.gid=1;
--
-- find all groups (gid) given a uid
-- select gid from groups,users,ugr where users.uid=1 and users.ref=ugr.uref and groups.ref=ugr.gref;
--
-- find all infos given a uid (not very usable)
-- select * from groups,users,ugr,userip where users.uid=1 and users.ref=ugr.uref and groups.ref=ugr.gref AND userip.ref=users.ref;
--
-- find all ip for a user
-- select userip.ip from userip,users where users.ref=1 AND users.ref=userip.ref;
--
--
-- reset auto-increment: ALTER TABLE users AUTO_INCREMENT=4;
--
|
