OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
 
Subject: Re: Postfix virtual transport
From: Andrew McNamara (andrewmconnect.com.au)
Date: Wed Apr 19 2000 - 02:07:08 CDT

>Yeah, I think you're right...
>The first gut reaction to a single table is 'My God, it's gonna be
>fscking *HUGE*!', which could be a problem. I figured the seperation
>would allow much better scalability.

Well, you're going to have the same amount of data in the database
either way, and mysql creates three files for every table, so if you
have thousands of tables, your data is going to be spread over a lot of
files, which is really going to hurt. The mysql authors talk about
tables with millions of rows not being a problem anyway (which is the
whole point of a database, I guess).

>Ugh.
>That's what I was afraid of...
>Would it be terribly painful (looking at the code quickly, it looks like
>it would be) to add another map and seperate the user and domain lookups,
>or at least provide seperate passed-in keys for them? Else, I'll have
>to add in an extra column in the 'login_auth' table for 'email address'
>in the full userdomain format. Ick, duplicated data; avoiding that is
>why we USE relational databases :(. I have columns for username and
>did (domain id, which is culled from the main table which provides
>information on all the domains, number of mails they allow, active status,
>uid to deliver as (1 per domain), etc. Oh well, guess it's time to bite
>the bullet and get this working solidly.

I just created a mock-up virtual user table with a schema that looks like:

    create table virtual (
        user varchar( 16 ) not null,
        domain varchar( 127 ) not null,
        target text not null,
        primary key( user, domain ));

And queries of the sort:

    select target from virtual
        where user = SUBSTRING_INDEX('andrewmenternet.com.au','',1)
        and domain = SUBSTRING_INDEX('andrewmenternet.com.au','',-1);

work fine from the msql command line tool. The problem is that the
postfix mysql map widget doesn't allow you to do queries this
complicated (and more importantly, has a constant "where" clause where
you can only specify the target column). Most disapointing.

The big question in my mind is why does the postfix mysql dict format
up the query for you - why not just let you specify the sql in the
config file? Maybe the authors of the map could comment?

 ---
Andrew McNamara (System Architect)

connect.com.au Pty Ltd
Lvl 3, 213 Miller St, North Sydney, NSW 2060, Australia
Phone: +61 2 9409 2117, Fax: +61 2 9409 2111