You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-user@james.apache.org by Mohammad Naghavi <mo...@gmail.com> on 2010/11/14 12:16:03 UTC

DB Structure

Hi every body,
I installed and worked with james 3 m1 with a mysql backend. now what I
wanted to ask is to get as much information about db structure as possible.
for example I sent an email from server to itself using telnet and
I received the mail correctly but now I find no where in db that sender and
receiver's email address are stored! or what I need to know is how to add a
user directly in database?

Thanks in advance,
MN
________________________________________
PHP & ASP .Net 3.5 web developer
Java & C# desktop developer
www.mohamnag.com

Re: DB Structure

Posted by Norman Maurer <no...@apache.org>.
No its not transfered as it is not part of the "Message". You could
write your own Mailet to store it somewhere..

Bye,
Nomrna


2010/11/15 Mohammad Naghavi <mo...@gmail.com>:
> yes but the question is that MAIL FROM and RCPT TO are not transfered in
> addition to normal email contents? if yes then it will be logical to store
> it some where in DB. otherwise yes this email can be shown as is.
>
> again thanks,
> MN
> ________________________________________
> PHP & ASP .Net 3.5 web developer
> Java & C# desktop developer
> www.mohamnag.com
>
>
>
> On Mon, Nov 15, 2010 at 11:12 AM, Eric Charles <er...@apache.org> wrote:
>
>> in thunderbird, yes... btw, I suppose it is conform to RFC (you should give
>> to mail client the content you effectively received, and not take ac
>

---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org


Re: DB Structure

Posted by Mohammad Naghavi <mo...@gmail.com>.
yes but the question is that MAIL FROM and RCPT TO are not transfered in
addition to normal email contents? if yes then it will be logical to store
it some where in DB. otherwise yes this email can be shown as is.

again thanks,
MN
________________________________________
PHP & ASP .Net 3.5 web developer
Java & C# desktop developer
www.mohamnag.com



On Mon, Nov 15, 2010 at 11:12 AM, Eric Charles <er...@apache.org> wrote:

> in thunderbird, yes... btw, I suppose it is conform to RFC (you should give
> to mail client the content you effectively received, and not take ac

Re: DB Structure

Posted by Eric Charles <er...@apache.org>.
Just a quick response: the mail has no headers, it should not really be 
valid, but we configured javax.mail to be permissive. So the mail will 
be in the user's mailbox, but may be without sender when viewed in 
thunderbird, yes... btw, I suppose it is conform to RFC (you should give 
to mail client the content you effectively received, and not take 
actions based on smtp preambule).

Tks,

Eric


On 14/11/2010 22:13, Mohammad Naghavi wrote:
> but wait a moment! you mean if there is no headers included, there will be
> no trace of sender of an email?
>
> thanks
> MN
> ________________________________________
> PHP&  ASP .Net 3.5 web developer
> Java&  C# desktop developer
> www.mohamnag.com
>
>
>
> On Sun, Nov 14, 2010 at 10:08 PM, Mohammad Naghavi<mo...@gmail.com>wrote:
>
>> OH! I got it! thanks too much.
>> mohammad
>> ________________________________________
>> PHP&  ASP .Net 3.5 web developer
>> Java&  C# desktop developer
>> www.mohamnag.com
>>
>>
>>
>> On Sun, Nov 14, 2010 at 10:06 PM, Norman Maurer<no...@apache.org>  wrote:
>>
>>> Hi Mohammad,
>>>
>>> this makes sense as the From and To headers are not related to the
>>> values given in MAIL FROM and RCPT TO.
>>>
>>> Bye,
>>> Norman
>>>
>>> 2010/11/14 Mohammad Naghavi<mo...@gmail.com>:
>>>> Hi,
>>>> but I sent an email using telnet directly to server, from user test at
>>> my
>>>> own server to user test1 in the same server but all the headers present
>>> in
>>>> HEADER table are these three: "Return-Path", "Received" and "Subject"
>>> and
>>>> nothing more! I tried it again but just those headers and nothing more.
>>> is
>>>> something gone wrong? is it possible that some headers are stored on
>>> disc?
>>>> thanks
>>>> MN
>>>> ________________________________________
>>>> PHP&  ASP .Net 3.5 web developer
>>>> Java&  C# desktop developer
>>>> www.mohamnag.com
>>>>
>>>>
>>>>
>>>> On Sun, Nov 14, 2010 at 5:03 PM, Eric Charles<er...@apache.org>  wrote:
>>>>
>>>>> n the MESSAGE_ID, and we have all the needed info in one query.
>>>>> You can also create your own custom query without prob
>>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
>>> For additional commands, e-mail: server-user-help@james.apache.org
>>>
>>>


---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org


Re: DB Structure

Posted by Mohammad Naghavi <mo...@gmail.com>.
but wait a moment! you mean if there is no headers included, there will be
no trace of sender of an email?

thanks
MN
________________________________________
PHP & ASP .Net 3.5 web developer
Java & C# desktop developer
www.mohamnag.com



On Sun, Nov 14, 2010 at 10:08 PM, Mohammad Naghavi <mo...@gmail.com>wrote:

> OH! I got it! thanks too much.
> mohammad
> ________________________________________
> PHP & ASP .Net 3.5 web developer
> Java & C# desktop developer
> www.mohamnag.com
>
>
>
> On Sun, Nov 14, 2010 at 10:06 PM, Norman Maurer <no...@apache.org> wrote:
>
>> Hi Mohammad,
>>
>> this makes sense as the From and To headers are not related to the
>> values given in MAIL FROM and RCPT TO.
>>
>> Bye,
>> Norman
>>
>> 2010/11/14 Mohammad Naghavi <mo...@gmail.com>:
>> > Hi,
>> > but I sent an email using telnet directly to server, from user test at
>> my
>> > own server to user test1 in the same server but all the headers present
>> in
>> > HEADER table are these three: "Return-Path", "Received" and "Subject"
>> and
>> > nothing more! I tried it again but just those headers and nothing more.
>> is
>> > something gone wrong? is it possible that some headers are stored on
>> disc?
>> >
>> > thanks
>> > MN
>> > ________________________________________
>> > PHP & ASP .Net 3.5 web developer
>> > Java & C# desktop developer
>> > www.mohamnag.com
>> >
>> >
>> >
>> > On Sun, Nov 14, 2010 at 5:03 PM, Eric Charles <er...@apache.org> wrote:
>> >
>> >> n the MESSAGE_ID, and we have all the needed info in one query.
>> >> You can also create your own custom query without prob
>> >>
>> >
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
>> For additional commands, e-mail: server-user-help@james.apache.org
>>
>>
>

Re: DB Structure

Posted by Mohammad Naghavi <mo...@gmail.com>.
OH! I got it! thanks too much.
mohammad
________________________________________
PHP & ASP .Net 3.5 web developer
Java & C# desktop developer
www.mohamnag.com



On Sun, Nov 14, 2010 at 10:06 PM, Norman Maurer <no...@apache.org> wrote:

> Hi Mohammad,
>
> this makes sense as the From and To headers are not related to the
> values given in MAIL FROM and RCPT TO.
>
> Bye,
> Norman
>
> 2010/11/14 Mohammad Naghavi <mo...@gmail.com>:
> > Hi,
> > but I sent an email using telnet directly to server, from user test at my
> > own server to user test1 in the same server but all the headers present
> in
> > HEADER table are these three: "Return-Path", "Received" and "Subject" and
> > nothing more! I tried it again but just those headers and nothing more.
> is
> > something gone wrong? is it possible that some headers are stored on
> disc?
> >
> > thanks
> > MN
> > ________________________________________
> > PHP & ASP .Net 3.5 web developer
> > Java & C# desktop developer
> > www.mohamnag.com
> >
> >
> >
> > On Sun, Nov 14, 2010 at 5:03 PM, Eric Charles <er...@apache.org> wrote:
> >
> >> n the MESSAGE_ID, and we have all the needed info in one query.
> >> You can also create your own custom query without prob
> >>
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
> For additional commands, e-mail: server-user-help@james.apache.org
>
>

Re: DB Structure

Posted by Norman Maurer <no...@apache.org>.
Hi Mohammad,

this makes sense as the From and To headers are not related to the
values given in MAIL FROM and RCPT TO.

Bye,
Norman

2010/11/14 Mohammad Naghavi <mo...@gmail.com>:
> Hi,
> but I sent an email using telnet directly to server, from user test at my
> own server to user test1 in the same server but all the headers present in
> HEADER table are these three: "Return-Path", "Received" and "Subject" and
> nothing more! I tried it again but just those headers and nothing more. is
> something gone wrong? is it possible that some headers are stored on disc?
>
> thanks
> MN
> ________________________________________
> PHP & ASP .Net 3.5 web developer
> Java & C# desktop developer
> www.mohamnag.com
>
>
>
> On Sun, Nov 14, 2010 at 5:03 PM, Eric Charles <er...@apache.org> wrote:
>
>> n the MESSAGE_ID, and we have all the needed info in one query.
>> You can also create your own custom query without prob
>>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org


Re: DB Structure

Posted by Mohammad Naghavi <mo...@gmail.com>.
Hi,
but I sent an email using telnet directly to server, from user test at my
own server to user test1 in the same server but all the headers present in
HEADER table are these three: "Return-Path", "Received" and "Subject" and
nothing more! I tried it again but just those headers and nothing more. is
something gone wrong? is it possible that some headers are stored on disc?

thanks
MN
________________________________________
PHP & ASP .Net 3.5 web developer
Java & C# desktop developer
www.mohamnag.com



On Sun, Nov 14, 2010 at 5:03 PM, Eric Charles <er...@apache.org> wrote:

> n the MESSAGE_ID, and we have all the needed info in one query.
> You can also create your own custom query without prob
>

Re: DB Structure

Posted by Eric Charles <er...@apache.org>.
Hi,
The HEADER table contains among others the "From" and "To" headers that 
indicates the from and to emails (see sample hereafter).

The problem is that you can have multiple To, Cc, so this would lead to 
a intermediate table in all cases.
Now, when we need a mail, we fetch the headers based on the MESSAGE_ID, 
and we have all the needed info in one query.
You can also create your own custom query without problem to filter and 
get for example "give me mails addressed to this email but without 
another email in cc"..

Tks,

Eric


154    Date    4    Sun, 14 Nov 2010 16:55:00 +0100
155    From    5    "eric@localhost.net" <er...@localhost.net>
157    MIME-Version    7    1.0
156    User-Agent    6    Mozilla/5.0 (Windows NT 6.1; WOW64; 
rv:2.0b8pre) Gecko/20101113 Thunderbird/3.3a1pre
159    Subject    9    qdsf
151    Return-Path    1 <er...@localhost.net>
160    Content-Type    10    text/plain; charset=ISO-8859-1; format=flowed
152    Received    2    from 127.0.0.1 ([127.0.0.1])
/n          by 192.168.1.14 (JAMES SMTP Server ) with ESMTP ID 345
/n          for <er...@localhost.net>;
/n          Sun, 14 Nov 2010 16:55:00 +0100 (CET)
153    Message-ID    3 <4C...@localhost.net>
158    To    8    eric@localhost.net
161    Content-Transfer-Encoding    11    7bit

On 14/11/2010 16:29, Mohammad Naghavi wrote:
> Hi Eric,
> Thanks, is actually looked up in Header table before (and now again) but the
> to and from parts are not present there. other headers are there like
> Subject and Return-Path, but no to or from headers. and it was also
> interesting for me to know why to store headers like this? why not to store
> them in columns in message table?
>
> Thanks
> MN
> ________________________________________
> PHP&  ASP .Net 3.5 web developer
> Java&  C# desktop developer
> www.mohamnag.com
>
>
>
> On Sun, Nov 14, 2010 at 3:59 PM, Eric Charles<er...@apache.org>  wrote:
>
>> Hi Mohammad,
>>
>> You can find the generated database schema here:
>>
>> https://svn.apache.org/repos/asf/james/server/trunk/src/site/resources/images/database/james-database-schema-derby.png
>> (will  be on M2 web site,  with more info)
>>
>> The users is in the JAMESUSER table.
>> Depending on your virtualHosting config, JAMESUSER.NAME is the email
>> address (or the user part that must be completed with a domain).
>>
>> The user has MAILBOXes via the SUBSCRIPTION table.
>>
>> The email are in MESSAGE (belonging to a MAILBOX via MEMBERSHIP). You have
>> to look in the HEADER table to get all the detailled infos (from, to, cc,
>> bcc,...).
>>
>> Tks,
>>
>> Eric
>>
>>
>>
>> On 14/11/2010 12:16, Mohammad Naghavi wrote:
>>
>>> Hi every body,
>>> I installed and worked with james 3 m1 with a mysql backend. now what I
>>> wanted to ask is to get as much information about db structure as
>>> possible.
>>> for example I sent an email from server to itself using telnet and
>>> I received the mail correctly but now I find no where in db that sender
>>> and
>>> receiver's email address are stored! or what I need to know is how to add
>>> a
>>> user directly in database?
>>>
>>> Thanks in advance,
>>> MN
>>> ________________________________________
>>> PHP&   ASP .Net 3.5 web developer
>>> Java&   C# desktop developer
>>> www.mohamnag.com
>>>
>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
>> For additional commands, e-mail: server-user-help@james.apache.org
>>
>>


---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org


Re: DB Structure

Posted by Mohammad Naghavi <mo...@gmail.com>.
Hi Eric,
Thanks, is actually looked up in Header table before (and now again) but the
to and from parts are not present there. other headers are there like
Subject and Return-Path, but no to or from headers. and it was also
interesting for me to know why to store headers like this? why not to store
them in columns in message table?

Thanks
MN
________________________________________
PHP & ASP .Net 3.5 web developer
Java & C# desktop developer
www.mohamnag.com



On Sun, Nov 14, 2010 at 3:59 PM, Eric Charles <er...@apache.org> wrote:

> Hi Mohammad,
>
> You can find the generated database schema here:
>
> https://svn.apache.org/repos/asf/james/server/trunk/src/site/resources/images/database/james-database-schema-derby.png
> (will  be on M2 web site,  with more info)
>
> The users is in the JAMESUSER table.
> Depending on your virtualHosting config, JAMESUSER.NAME is the email
> address (or the user part that must be completed with a domain).
>
> The user has MAILBOXes via the SUBSCRIPTION table.
>
> The email are in MESSAGE (belonging to a MAILBOX via MEMBERSHIP). You have
> to look in the HEADER table to get all the detailled infos (from, to, cc,
> bcc,...).
>
> Tks,
>
> Eric
>
>
>
> On 14/11/2010 12:16, Mohammad Naghavi wrote:
>
>> Hi every body,
>> I installed and worked with james 3 m1 with a mysql backend. now what I
>> wanted to ask is to get as much information about db structure as
>> possible.
>> for example I sent an email from server to itself using telnet and
>> I received the mail correctly but now I find no where in db that sender
>> and
>> receiver's email address are stored! or what I need to know is how to add
>> a
>> user directly in database?
>>
>> Thanks in advance,
>> MN
>> ________________________________________
>> PHP&  ASP .Net 3.5 web developer
>> Java&  C# desktop developer
>> www.mohamnag.com
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
> For additional commands, e-mail: server-user-help@james.apache.org
>
>

Re: DB Structure

Posted by Eric Charles <er...@apache.org>.
Hi Mohammad,

You can find the generated database schema here:
https://svn.apache.org/repos/asf/james/server/trunk/src/site/resources/images/database/james-database-schema-derby.png
(will  be on M2 web site,  with more info)

The users is in the JAMESUSER table.
Depending on your virtualHosting config, JAMESUSER.NAME is the email 
address (or the user part that must be completed with a domain).

The user has MAILBOXes via the SUBSCRIPTION table.

The email are in MESSAGE (belonging to a MAILBOX via MEMBERSHIP). You 
have to look in the HEADER table to get all the detailled infos (from, 
to, cc, bcc,...).

Tks,

Eric


On 14/11/2010 12:16, Mohammad Naghavi wrote:
> Hi every body,
> I installed and worked with james 3 m1 with a mysql backend. now what I
> wanted to ask is to get as much information about db structure as possible.
> for example I sent an email from server to itself using telnet and
> I received the mail correctly but now I find no where in db that sender and
> receiver's email address are stored! or what I need to know is how to add a
> user directly in database?
>
> Thanks in advance,
> MN
> ________________________________________
> PHP&  ASP .Net 3.5 web developer
> Java&  C# desktop developer
> www.mohamnag.com
>


---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscribe@james.apache.org
For additional commands, e-mail: server-user-help@james.apache.org