I have been thinking on mysql schemes available over internet and some know=
n howtos as howtoforge... but as far as I seen (perhaps I'm wrong OR I'm ig=
noring something) tables are not well optimized... because by the queries p=
roftpd does and the existing tables all the searchs will be sequential thro=
ugh this tables... and I think that if you use mysql is for obtaining a bet=
ter performance when having a quite high number of users... so I wanted to =
share with you my proftpd tables and his descriptions and to know wich is y=
ou're opinion of them or if they could break the server... for the moment h=
as worked to me... but wanted to know you're opinion... here they are :

CREATE TABLE ftpgroup (
groupname varchar(15) NOT NULL default '',
gid smallint(6) NOT NULL default '5500',
members varchar(16) NOT NULL default '',
KEY groupname (groupname)
) TYPE=3DMyISAM COMMENT=3D'ProFTP group table';


CREATE TABLE ftpuser (
id int(10) unsigned NOT NULL auto_increment,
userid varchar(10) NOT NULL default '',
passwd varchar(32) NOT NULL default '',
uid smallint(6) NOT NULL default '5500',
gid smallint(6) NOT NULL default '5500',
homedir varchar(255) NOT NULL default '',
shell varchar(16) NOT NULL default '/sbin/nologin',
count int(11) NOT NULL default '0',
accessed datetime NOT NULL default '0000-00-00 00:00:00',
modified datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id,userid)
) TYPE=3DMyISAM COMMENT=3D'ProFTP user table';

CREATE TABLE ftpquotalimits (
name varchar(10) default NULL,
quota_type enum('user','group','class','all') NOT NULL default 'user',
per_session enum('false','true') NOT NULL default 'false',
limit_type enum('soft','hard') NOT NULL default 'soft',
bytes_in_avail int(10) unsigned NOT NULL default '0',
bytes_out_avail int(10) unsigned NOT NULL default '0',
bytes_xfer_avail int(10) unsigned NOT NULL default '0',
files_in_avail int(10) unsigned NOT NULL default '0',
files_out_avail int(10) unsigned NOT NULL default '0',
files_xfer_avail int(10) unsigned NOT NULL default '0',
PRIMARY KEY (name,quota_type)
) TYPE=3DMyISAM;

CREATE TABLE ftpquotatallies (
name varchar(10) NOT NULL default '',
quota_type enum('user','group','class','all') NOT NULL default 'user',
bytes_in_used int(10) unsigned NOT NULL default '0',
bytes_out_used int(10) unsigned NOT NULL default '0',
bytes_xfer_used int(10) unsigned NOT NULL default '0',
files_in_used int(10) unsigned NOT NULL default '0',
files_out_used int(10) unsigned NOT NULL default '0',
files_xfer_used int(10) unsigned NOT NULL default '0',
PRIMARY KEY (name,quota_type)
) TYPE=3Dinnodb;



and now my queries :

# used to connect to the database
# databasename@host database_user user_password
SQLConnectInfo ftp@localhost proftpd password


# Here we tell ProFTPd the names of the database columns in the "usertable"
# we want it to interact with. Match the names with those in the db
SQLUserInfo ftpuser userid passwd uid gid homedir shell

# Here we tell ProFTPd the names of the database columns in the "grouptable"
# we want it to interact with. Again the names match with those in the db
SQLGroupInfo ftpgroup groupname gid members

# set min UID and GID - otherwise these are 999 each
SQLMinID 500

# create a user's home directory on demand if it doesn't exist
SQLHomedirOnDemand on

# Update count every time user logs in
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=3Dcount+1, accessed=3Dnow() WHERE u=
serid=3D'%u'" ftpuser

# Update modified everytime user uploads or deletes a file
SQLLog STOR,DELE modified
SQLNamedQuery modified UPDATE "modified=3Dnow() WHERE userid=3D'%u'" ftpuser

# User quotas
# =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
QuotaEngine on
QuotaDirectoryTally on
QuotaDisplayUnits Mb
QuotaShowQuotas on

SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, limit_=
type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, fi=
les_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name =3D '%{0}' A=
ND quota_type =3D '%{1}'"

SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, byte=
s_out_used, bytes_xfer_used, files_in_used, files_out_used, files_xfer_used=
FROM ftpquotatallies WHERE name =3D '%{0}' AND quota_type =3D '%{1}'"

SQLNamedQuery update-quota-tally UPDATE "bytes_in_used =3D bytes_in_used + =
%{0}, bytes_out_used =3D bytes_out_used + %{1}, bytes_xfer_used =3D bytes_x=
fer_used + %{2}, files_in_used =3D files_in_used + %{3}, files_out_used =3D=
files_out_used + %{4}, files_xfer_used =3D files_xfer_used + %{5} WHERE na=
me =3D '%{6}' AND quota_type =3D '%{7}'" ftpquotatallies

SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}=
, %{6}, %{7}" ftpquotatallies

QuotaLimitTable sql:/get-quota-limit
QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally

RootLogin off
RequireValidShell off
[...]

what do you think please? do you think this could improve performance in pr=
oftpd? or do you think this could break the server at some moment??


Thanks a lot

__

Egoitz Aurrekoetxea Aurre
e-Mail: egoitz.aurrekoetxea@nht-norwick.net

NHT-Norwick=AE
Avda. Txorierri 9, 104
48160 Derio (Vizcaya)
Tel: +34 94 435 54 40
Fax: +34 94 423 38 44
M=F3vil : 647 37 21 56

Este mensaje va dirigido s=F3lo al destinatario del mismo y puede contener =
informaci=F3n privilegiada, de propiedad intelectual o cualquier otro tipo =
de datos de car=E1cter privado. Si lo ha recibido por error, notif=EDquesel=
o inmediatamente al emisario y borre el original. Est=E1 prohibido cualquie=
r otro empleo de este e-mail por su parte.

This message is for the designated recipient only and may contain privilege=
d, proprietary, or otherwise private information. If you have received it i=
n error, please notify the sender immediately and delete the original. Any =
other use of the email by you is prohibited.





-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
ProFTPD Users List
Unsubscribe problems?
http://www.proftpd.org/list-unsub.html