Re: [vchkpw] mysql - valias sequence?
John Simpson wrote: howdy- while answering a question for somebody on another mailing list i happened to notice that, at least as of vpopmail 5.4.10, the structure of the valias table created in a mysql database does not have any provision for sequencing the entries attached to a given alias. No it does not. in many cases this shouldn't make a difference, but occasionally somebody may do filtering or something similar, where the sequence that the items are processed is important. (i'm doing this with my own .qmail file to sort mailing list traffic into separate folders.) Yes, I do this occasionaly, it can bite you. first, how does the vdelivermail program decide in which order to process the entries when there are multiple records? does it use them in the order that mysql delivers them? and if so, does mysql have any guaranteed order that it will present the records with an explicit ORDER BY clause in the query? That I recall, it takes them in the order they are returned by mysql, there is not ORDER BY in the SQL, the statements it uses are in the source. I have thought of expreimenting with changing this statement. second, if a user has one or more existing valias records, the valias -i command would seem to work by sending an INSERT query with the new line. is the new line added to the beginning of the sequence, the end of the sequence, or is there in fact no guaranteed sequence at all? None at all. i wanted to ask this here before reporting it as a bug on the sourceforge site. Not really a bug as I believe the valias was intended as a forwarding tool, I may be wrong. Ken or Tom would be able to answer better. I only know what I know as I had discussions with Tom Collins about this same topic a while back. DAve
Re: [vchkpw] mysql - valias sequence?
John Simpson wrote: howdy- while answering a question for somebody on another mailing list i happened to notice that, at least as of vpopmail 5.4.10, the structure of the valias table created in a mysql database does not have any provision for sequencing the entries attached to a given alias. Correct. first, how does the vdelivermail program decide in which order to process the entries when there are multiple records? It doesn't. does it use them in the order that mysql delivers them? Yes. and if so, does mysql have any guaranteed order that it will present the records with an explicit ORDER BY clause in the query? There is no ORDER BY clause in the query, so the order the records is random. They may appear to follow the order they were entered in, but adds and deletes can change the order. second, if a user has one or more existing valias records, the valias -i command would seem to work by sending an INSERT query with the new line. is the new line added to the beginning of the sequence, the end of the sequence, or is there in fact no guaranteed sequence at all? If there is no ORDER BY clause in a query the order the records are returned in is random, and subject to change without notice. We need to add an order field, and an ORDER BY clause, but it hasn't happened yet. -- | Mac OS X proves that it's easier to make UNIX | | pretty than it is to make Windows secure. | -- grin
Re: [vchkpw] mysql - valias sequence?
DAve wrote: John Simpson wrote: i wanted to ask this here before reporting it as a bug on the sourceforge site. Not really a bug as I believe the valias was intended as a forwarding tool, I may be wrong. Ken or Tom would be able to answer better. I only know what I know as I had discussions with Tom Collins about this same topic a while back. Yep. Its a feature request maybe, but not a bug. I believe its considered a good idea, but no one has had the time to code it.
Re: [vchkpw] mysql - valias sequence?
On Jul 19, 2005, at 6:03 PM, Rick Widmer wrote: John Simpson wrote: and if so, does mysql have any guaranteed order that it will present the records with an explicit ORDER BY clause in the query? There is no ORDER BY clause in the query, so the order the records is random. They may appear to follow the order they were entered in, but adds and deletes can change the order. that's what i figured from quickly glancing through the code- it's nice to know i didn't miss anything. second, if a user has one or more existing valias records, the valias -i command would seem to work by sending an INSERT query with the new line. is the new line added to the beginning of the sequence, the end of the sequence, or is there in fact no guaranteed sequence at all? If there is no ORDER BY clause in a query the order the records are returned in is random, and subject to change without notice. We need to add an order field, and an ORDER BY clause, but it hasn't happened yet. i don't know that i'll have the free time, but i'm certainly willing to look at it. so what's actually needed would be: - a sequence field in the table, integral type, 32-bit unsigned integer (i would say limit it to 8 or 16 bits, but you never know...) - vdelivermail.c needs an ORDER BY clause. it may also need to be modified to treat exit code 99 from a program delivery the same way qmail-local does (i.e. treat it as a success but stop processing alias lines) unless i'm missing something and it already does this? - need a script to convert a non-sequence table into a sequence table. this involves creating a new table with the sequence field, copying the old table to the new one (manually creating the sequence for all entries), deleting the old table, and renaming the new one to the old name. it could also identify any problem mailboxes- those with multiple lines, at least one of which is a program delivery. - valias.c needs the same ORDER BY clause as vdelivermail.c (for use when listing aliases.) we may also want a -n command line flag which makes -s show the seqence numbers in addition to the current output (but don't change the output, that would break scripts which depend on this output.) - the valias_insert() functions in vmysql.c, vpalias.c, and vpgsql.c need a fourth parameter, a sequence number (unsigned). technically it's how many lines to skip before adding the new entry- 0 means add at the beginning, 1 means skip the first line and then insert the new one, and so forth... anything = number of lines in the file is treated as add to the end. the functions need to be able to re- number the existing rules in the database as needed to maintain the correct sequence. - dotqmail2valias.c needs to make all of its calls with -1 as the sequence, meaning that all rules added would be added to the end of whatever instructions are already there for that alias. - valias.c would use the new -n command line option to specify the sequence for the new rule added by the -i option. (i'm assuming that -i and -s are mutually exclusive in the code.) am i missing anything? -- | John M. Simpson - KG4ZOW - Programmer At Large | | http://www.jms1.net/ [EMAIL PROTECTED] | -- | Mac OS X proves that it's easier to make UNIX | | pretty than it is to make Windows secure. | -- PGP.sig Description: This is a digitally signed message part
Re: [vchkpw] mysql - valias sequence?
This is a known limitation of valiases. I've wanted to do something
about it for a long time, but don't have the spare time to do so. It
would be great if John and perhaps others could pitch in to make it
happen.
On Jul 19, 2005, at 3:57 PM, John Simpson wrote:
i don't know that i'll have the free time, but i'm certainly willing
to look at it.
Excellent.
so what's actually needed would be:
- a sequence field in the table, integral type, 32-bit unsigned
integer (i would say limit it to 8 or 16 bits, but you never know...)
- vdelivermail.c needs an ORDER BY clause. it may also need to be
modified to treat exit code 99 from a program delivery the same way
qmail-local does (i.e. treat it as a success but stop processing alias
lines) unless i'm missing something and it already does this?
Actually, vdelivermail won't require changes. Just update
valias_select() in vmysql.c
- need a script to convert a non-sequence table into a sequence table.
this involves creating a new table with the sequence field, copying
the old table to the new one (manually creating the sequence for all
entries), deleting the old table, and renaming the new one to the old
name. it could also identify any problem mailboxes- those with
multiple lines, at least one of which is a program delivery.
You could just add the column to the existing table and then populate
it with 0. I agree that a tool to identify problematic aliases would
be good, but setting it to 0 would match current behavior.
- valias.c needs the same ORDER BY clause as vdelivermail.c (for use
when listing aliases.) we may also want a -n command line flag which
makes -s show the seqence numbers in addition to the current output
(but don't change the output, that would break scripts which depend on
this output.)
Sounds good. You might need to modify linklist_add() in vpopmail.c and
update the calls to it in vmysql.c to store the sequence number.
- the valias_insert() functions in vmysql.c, vpalias.c, and vpgsql.c
need a fourth parameter, a sequence number (unsigned). technically
it's how many lines to skip before adding the new entry- 0 means add
at the beginning, 1 means skip the first line and then insert the
new one, and so forth... anything = number of lines in the file is
treated as add to the end. the functions need to be able to
re-number the existing rules in the database as needed to maintain the
correct sequence.
Like this?
update valias set seq = seq + 1 where alias = '[EMAIL PROTECTED]' and seq
4
insert into valias (alias, alias_line, seq) values ('[EMAIL PROTECTED]',
'[EMAIL PROTECTED]', 4)
- dotqmail2valias.c needs to make all of its calls with -1 as the
sequence, meaning that all rules added would be added to the end of
whatever instructions are already there for that alias.
Yep.
- valias.c would use the new -n command line option to specify the
sequence for the new rule added by the -i option. (i'm assuming that
-i and -s are mutually exclusive in the code.)
Sounds good to me.
am i missing anything?
Very thorough. We'd have to update qmailadmin as well, but since it
presently always adds to the end, it wouldn't be a big change.
Ultimately, we would want to update the UI in QmailAdmin to allow you
to change the order of lines in an alias (kind of like how Netflix
manages your queue).
Perhaps when creating the new valias_insert() command, we should give
it a new name and keep valias_insert() with the same parameters.
valias_insert() would just call the new command and pass in -1 for the
sequence. That way, old programs wouldn't break.
We might also want to define a macro to indicate that the extended
insert is available, so programs like QmailAdmin can have conditional
compilation depending on whether they're linked to the old or new
vpopmail.
--
Tom Collins - [EMAIL PROTECTED]
QmailAdmin: http://qmailadmin.sf.net/ Vpopmail: http://vpopmail.sf.net/
You don't need a laptop to troubleshoot high-speed Internet:
sniffter.com
