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
|
-- psql -U wzdftpd -f tables.sql wzdftpd
--
-- Table structure for table `ugroups`
--
CREATE TABLE groups (
ref serial PRIMARY KEY,
groupname varchar(255) UNIQUE NOT NULL,
gid serial UNIQUE,
defaultpath varchar(255) NOT NULL,
tagline varchar(255) DEFAULT NULL,
groupperms integer default NULL,
max_idle_time integer default NULL,
num_logins integer default NULL,
max_ul_speed bigint NULL,
max_dl_speed bigint NULL,
ratio integer NULL
);
--
-- Dumping data for table `groups`
--
INSERT INTO groups (groupname,gid,defaultpath,tagline) VALUES ('admin',nextval('groups_gid_seq'),'/','admin group');
--
-- Table structure for table `users`
--
CREATE TABLE users (
ref serial PRIMARY KEY,
username varchar(255) NOT NULL,
userpass varchar(48) default NULL,
rootpath varchar(255) NOT NULL,
tagline varchar(255) default NULL,
uid serial UNIQUE,
flags varchar(32) default NULL,
max_idle_time integer default NULL,
max_ul_speed bigint default NULL,
max_dl_speed bigint default NULL,
num_logins integer default NULL,
ratio integer default NULL,
user_slots integer default NULL,
leech_slots integer default NULL,
perms bigint default NULL,
credits bigint default NULL,
last_login time default NULL
);
--
-- Dumping data for table `users`
--
INSERT INTO users (username,rootpath,tagline,uid,flags,perms) VALUES ('wzdftpd','/','local admin',nextval('users_uid_seq'),'OIstH',cast (X'ffffffff' as integer));
INSERT INTO users (username,rootpath,uid,flags,perms) VALUES
('novel','/usr/home/novel',nextval('users_uid_seq'),'OIstH',cast (X'ffffffff' as integer));
INSERT INTO users (username,rootpath,uid,perms) VALUES ('anonymous','/tmp',nextval('users_uid_seq'),cast (X'ffffffff' as integer));
--
-- Table structure for table `UGR` (User-Group Relations)
--
CREATE TABLE ugr (
uref integer NOT NULL,
gref integer NOT NULL,
PRIMARY KEY(uref,gref)
);
ALTER TABLE ONLY ugr ADD CONSTRAINT "$1" FOREIGN KEY (uref) REFERENCES users(ref);
ALTER TABLE ONLY ugr ADD CONSTRAINT "$2" FOREIGN KEY (gref) REFERENCES groups(ref);
--
-- Table structure for table `IP` (User/Group IPs)
--
CREATE TABLE groupip (
ref integer NOT NULL,
ip VARCHAR(255) NOT NULL,
PRIMARY KEY(ref,ip)
);
ALTER TABLE ONLY groupip ADD CONSTRAINT "$1" FOREIGN KEY (ref) REFERENCES groups(ref);
--
-- Table structure for table `IP` (User/Group IPs)
--
CREATE TABLE userip (
ref integer NOT NULL,
ip VARCHAR(255) NOT NULL,
PRIMARY KEY(ref,ip)
);
ALTER TABLE ONLY userip ADD CONSTRAINT "$1" FOREIGN KEY (ref) REFERENCES users(ref);
INSERT into userip (ref,ip) SELECT users.ref,'127.0.0.1' FROM users WHERE users.uid=1;
INSERT into userip (ref,ip) SELECT users.ref,'foobar@localhost' FROM users WHERE users.uid=2;
INSERT into userip (ref,ip) SELECT users.ref,'127.0.0.1' FROM users WHERE users.uid=2;
--
-- Table structure for table `Stats`
--
CREATE TABLE stats (
ref serial NOT NULL,
bytes_ul_total bigint default 0,
bytes_dl_total bigint default 0,
files_ul_total integer default 0,
files_dl_total integer default 0,
PRIMARY KEY(ref)
);
ALTER TABLE ONLY stats ADD CONSTRAINT "$1" FOREIGN KEY (ref) REFERENCES users(ref);
INSERT INTO stats (ref) SELECT users.ref FROM users where uid=1;
INSERT INTO stats (ref) SELECT users.ref FROM users where uid=2;
INSERT INTO stats (ref) SELECT users.ref FROM users where uid=3;
--
-- 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;
--
|
