On Tuesday 19 July 2005 16:14, Charles J. Boening wrote:
> Ya ... I think having a .sql file would be cool.  It's not there
> though (unless I've missed it).


> How about making [a default .sql schema] and submitting it?

I plan to as part of my work.

> Don't forget to include statements for indexes and foreign keys.

Of course, I'm no dummy when it comes to PostgreSQL. :)

> Another thought comes to mind and I think it's where you'd like
> to see things go.  Take pure-ftp for example.  In it's
> configuration file you define database connectivity and sql
> statements for returning data.

Pure-FTPd integrates nicely with our database using a view which is 
the only thing the pure-ftpd user can read.  mod_auth_pgsql for 
apache also works well in the same manner.  This is the approach I 
had in mind originally before you brought up efficiency problems of 
reading configuration files.

> I guess the direction that could be taken with vpopmail is to
> abstract the database layer and let the user define how the data
> is stored and returned.

Yes, but while the way that pure-ftpd and mod_auth_pgqsl work is 
handy, it's completely unecessary for postgresql like it is for 
mysql, because postgresql supports stored procedures and views, so 
one could simple customize them instead of customizing a 
configuration file.  I don't think having the procedure and view 
names hardcoded would be a bad thing, as I said before, and would 
be more efficient.

> Of course example/recommended database structure could be included
> (.sql file) for those wishing to not customize.


> One could continue this to include things like runtime
> configuration of logging options.

I think this is better handled with an extra configure option, I 
think --enable-mysql-logging already exists.

> Guess this is a whole different topic for discussion though.  :)

Not really. :)  It relates directly to my extra features commentary 

> The settings from vpgsql.h are compiled in.  This file is not
> used at runtime.

Well if the only thing hardcoded is select columnname from view and 
stored_prodedure (value), then compiling in in fine, IMHO.

> I wouldn't say ugly side.  Qmail even calls external programs
> such as qmail-remote, qmail-local, and qmail-inject.

You're right, I recalled that AFTER writing the last mail. ;-)

> I seem to recall on the list someone talking about a vpopmail
> daemon.

Well it's completely unecessary with the stored procedure approach 
because then the "configuration" (which lies inside the stored 
procedures and views) is already stored in a daemon (postgresql).  
I don't think I really like the idea of vpopmail becoming a daemon, 
come to think of it.

> Doesn't matter.  If you create views and tables in your database
> to match what vpopmail needs, then you're all set.  I don't think
> it matters if vpopmail officially works off of views, stored
> procedures or tables.

Well except you can't update a view.  I didn't look that extensively 
at how vpopmail handled the vpopmail (and other) tables where 
writes were performed as well as reads...and assumed that it needed 
an object with the expected columns that behaved exactly like a 
table (it could select, update, delete, or insert).  This may have 
been a wrong guess on my part, but I don't have time to look at the 
code again just now (will later though).

> > The problem I see is that certain features (i.e. clearpasswd)
> > require extra columns in the database that are not present
> > otherwise.  One wouldn't want a stored procedure taking a
> > different number of arguments based on what features were
> > present - this would probably be best handled by adding
> > additional procedures to handle extra features, and add
> > columns for them to the default table definitions which allow
> > null values (but with a comment on the extra columns and
> > functions that say they can be dropped safely if you're not
> > using X feature).
> You don't have to enable clear passwords in vpopmail.  That's a
> configuration option.

Exactly why I said "extra features" above (okay so I said "certain 
features" first, but what I meant was "certain optional features".  
Optional features that require additional database support (i.e. 
clearpasswd, db-logging, etc.) would best be handled by default 
with additional tables rather than additional columns.

> How often do you change hostname, database, username and
> password?  I don't change mine very often.  If I do it would
> probably just be the password.

It's a matter of administration.  I can't reasonably compile these 
values in, because I want to use my distributions package 
management to install new releases of vpopmail, and just maintain a 
common configuration across them.

> The multiple host is where I see an issue coming in.  I don't
> believe vpopmail has support for multiple postgresql database
> servers.

Well, I'm personally not too concerned about this as we use DRBD, 
but it would be nice to add at the same time for other users' 
setups, perhaps.

> I know ... Write a script that monitors the main server and if
> it's offline modify vpgsql.h and recompile and reinstall
> vpopmail.  ;) 
> See above ... Modify vpqsql.h and recompile and reinstall
> automagically. ;)

Ugh.  No.

> > On the other hand on the busiest mail server this file is
> > probably going to just sit in buffer memory all the time
> > anyways, so does it really matter?
> True.

Okay, well what I'm proposing with the stored procedure approach is 
that everything is hardcoded except for the connection string 
information.  This could even be compiled-in for ricers with some 
extra configure options or just using some defaults if a config 
file doesn't exist, and normal users like me could use the simple 
format already used by vpopmail.mysql (ala vpopmail.pgsql).  I'm 
sure the performance hit for checking for the file is miniscule, 
since the mysql module already does this.

The only real question is whether or not to add support for multiple 
failover servers in that configuration file.

> Just keep in mind that the vpopmail postgresql users that are out
> there probably don't want to go changing their structure unless
> they're going to benefit.

Right.  The default .sql schema could create tables EXACTLY like the 
ones that vpopmail uses now, with the addition of views and stored 
procedures to access them (upgrading users would only have to 
create these views and functions, which could be a simple 

Unfortunately things get a bit tricky when you consider optional 
features, because based on optional features, the current 
implementation will create different tables.  The pgsql-upgrade.sql 
could handle making the necessary changes to the database, but 
there is risk that if a user is querying/updating the db with 
another application/script (i.e. your web interface), some of those 
queries might break with the upgrade.

(This is probably of minor concern though - I don't see any way 
around it)

I think I have this pretty well sorted out in my head now, the next 
step is just to hack it together and submit a big patch I suppose - 
that's probably when people will come out of the woodwork 
complaining about it. :P

Casey Allen Shobe | http://casey.shobe.info
[EMAIL PROTECTED] | cell 425-443-4653
AIM & Yahoo:  SomeLinuxGuy | ICQ:  1494523
SeattleServer.com, Inc. | http://www.seattleserver.com

Reply via email to