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
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
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'
> 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. ;)
> > 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?
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
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