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 Michael Loyd <mi...@loydnet.com> on 2001/07/24 10:09:30 UTC
Database Structure
Hi. Sorry for posting a novice question that might be obvious to everyone.
But I'm just starting out with James and email applications. I tried
searching the archives but couldn't find the answer.
What are the table structures for all of the tables that James could
utilize? I'm using MySQL and I saw the script to create the Message
table... but what about the table for managing users via a database table,
spools, repositories, etc. Is there any documentation on this?
I understand how to modify the JAMES.conf.xml document to use the database
but it's just not painfully obvious to me how to set up the database tables.
Thanks for the help,
Michael
---------------------------------------------------------------------
To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: james-user-help@jakarta.apache.org
Re: Database Structure
Posted by Darrell DeBoer <li...@bigdaz.com>.
G'day
This only applies to the latest code, and not the 1.2 release.
I developed a pure JDBC implementation of the User Repository some time back.
This is separate to the "town" based implementation. It's still in a pretty
basic state, but I haven't had time to do any more on it.
The required Users table is generated automagically on startup if it isn't
found in the database. The SQL is all hard-coded, and it's been tested on
MySQL, M$SQL, and Oracle (the exact versions and drivers tested are in
Javadoc).
This is different to the way the Mail repository works, so the convenience of
automatic table generation is probably just confusing. And probably not the
way we want to go.
ciao
Daz
On Wed, 25 Jul 2001 07:56, Michael Loyd wrote:
> I guess I didn't ask my question correctly. I understand the Message
> table. James and MySQL are chatting just fine... but my question was in
> regards to any documentation, or philosophy, when setting up the structure
> of the users table, or any other tables, and how JAMES.conf.xml can be
> configured to interface with the db. The only documented structure was
> that of the helper scripts for MySQL and SQL Server in the doc directory.
> I'm not that familiar with workingdogs so sifting through the src doesn't
> provide much help.
>
> But from what I'm reading in your message, James will use the table
> 'Message' in the database I specify in the maildatabase file. Then the
> different repositories are specified by the
> <spoolRepository> town://mainspool </spoolRepository>
> <inboxRepository> town://inbox </inboxRepository>
> lines in JAMES.conf.xml and they are differentiated in the 'Message' table
> by the repository_name field.
>
> But how do I specify the table for which the username/password combinations
> are stored in the JAMES.xml.conf file? When I say
> <userRepository> town://users </userRepository>
> does that explicitly say to use the table 'users' in the database I specify
> in the maildatabase file and James requires only the fields 'username' and
> 'password'?
>
> Sorry for the novice questions... email applications are not my forte.
>
> ----- Original Message -----
> From: "Serge Knystautas" <se...@lokitech.com>
> To: <ja...@jakarta.apache.org>
> Sent: Tuesday, July 24, 2001 8:52 AM
> Subject: Re: Database Structure
>
>
> I send this message periodically to the list. Sorry you couldn't find it.
>
> Message table looks like this (at least in my database server's
> fieldtypes... M$ SQL Server)
>
> CREATE TABLE [dbo].[Message] (
> [message_name] [varchar] (200) NOT NULL ,
> [repository_name] [varchar] (200) NOT NULL ,
> [message_state] [varchar] (30) NOT NULL ,
> [error_message] [text] NULL ,
> [sender] [varchar] (200) NOT NULL ,
> [recipients] [text] NOT NULL ,
> [remote_host] [varchar] (100) NOT NULL ,
> [remote_addr] [varchar] (20) NOT NULL ,
> [message_body] [image] NOT NULL ,
> [last_updated] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[Message] WITH NOCHECK ADD
> CONSTRAINT [PK_Message] PRIMARY KEY NONCLUSTERED
> (
> [message_name],
> [repository_name]
> ) ON [PRIMARY]
> GO
>
> 'text' is arbitrarily long char data, and 'image' is arbitrarily long
> binary data.
>
> The Message is used for the spool and mail repositories. You can point
> multiple repositories at this single table, so long as you use different
> repository_names (part of the town:// construct)
>
> The User table can be any table that has username and password in it. Town
> will detect the primary key and assuming username is unique in the table,
> it doesn't have to be the primary key. This will probably change in the
> future, but that's how it works for now.
>
> Serge Knystautas
> Loki Technologies
> http://www.lokitech.com/
> ----- Original Message -----
> From: "Michael Loyd" <mi...@loydnet.com>
> To: <ja...@jakarta.apache.org>
> Sent: Tuesday, July 24, 2001 4:09 AM
> Subject: Database Structure
>
> > Hi. Sorry for posting a novice question that might be obvious to
>
> everyone.
>
> > But I'm just starting out with James and email applications. I tried
> > searching the archives but couldn't find the answer.
> >
> > What are the table structures for all of the tables that James could
> > utilize? I'm using MySQL and I saw the script to create the Message
> > table... but what about the table for managing users via a database
> > table, spools, repositories, etc. Is there any documentation on this?
> >
> > I understand how to modify the JAMES.conf.xml document to use the
> > database but it's just not painfully obvious to me how to set up the
> > database
>
> tables.
>
> > Thanks for the help,
> > Michael
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: james-user-help@jakarta.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: james-user-help@jakarta.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: james-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: james-user-help@jakarta.apache.org
Re: Database Structure
Posted by Michael Loyd <mi...@loydnet.com>.
I guess I didn't ask my question correctly. I understand the Message table.
James and MySQL are chatting just fine... but my question was in regards to
any documentation, or philosophy, when setting up the structure of the users
table, or any other tables, and how JAMES.conf.xml can be configured to
interface with the db. The only documented structure was that of the helper
scripts for MySQL and SQL Server in the doc directory. I'm not that
familiar with workingdogs so sifting through the src doesn't provide much
help.
But from what I'm reading in your message, James will use the table
'Message' in the database I specify in the maildatabase file. Then the
different repositories are specified by the
<spoolRepository> town://mainspool </spoolRepository>
<inboxRepository> town://inbox </inboxRepository>
lines in JAMES.conf.xml and they are differentiated in the 'Message' table
by the repository_name field.
But how do I specify the table for which the username/password combinations
are stored in the JAMES.xml.conf file? When I say
<userRepository> town://users </userRepository>
does that explicitly say to use the table 'users' in the database I specify
in the maildatabase file and James requires only the fields 'username' and
'password'?
Sorry for the novice questions... email applications are not my forte.
----- Original Message -----
From: "Serge Knystautas" <se...@lokitech.com>
To: <ja...@jakarta.apache.org>
Sent: Tuesday, July 24, 2001 8:52 AM
Subject: Re: Database Structure
I send this message periodically to the list. Sorry you couldn't find it.
Message table looks like this (at least in my database server's
fieldtypes... M$ SQL Server)
CREATE TABLE [dbo].[Message] (
[message_name] [varchar] (200) NOT NULL ,
[repository_name] [varchar] (200) NOT NULL ,
[message_state] [varchar] (30) NOT NULL ,
[error_message] [text] NULL ,
[sender] [varchar] (200) NOT NULL ,
[recipients] [text] NOT NULL ,
[remote_host] [varchar] (100) NOT NULL ,
[remote_addr] [varchar] (20) NOT NULL ,
[message_body] [image] NOT NULL ,
[last_updated] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Message] WITH NOCHECK ADD
CONSTRAINT [PK_Message] PRIMARY KEY NONCLUSTERED
(
[message_name],
[repository_name]
) ON [PRIMARY]
GO
'text' is arbitrarily long char data, and 'image' is arbitrarily long binary
data.
The Message is used for the spool and mail repositories. You can point
multiple repositories at this single table, so long as you use different
repository_names (part of the town:// construct)
The User table can be any table that has username and password in it. Town
will detect the primary key and assuming username is unique in the table, it
doesn't have to be the primary key. This will probably change in the
future, but that's how it works for now.
Serge Knystautas
Loki Technologies
http://www.lokitech.com/
----- Original Message -----
From: "Michael Loyd" <mi...@loydnet.com>
To: <ja...@jakarta.apache.org>
Sent: Tuesday, July 24, 2001 4:09 AM
Subject: Database Structure
> Hi. Sorry for posting a novice question that might be obvious to
everyone.
> But I'm just starting out with James and email applications. I tried
> searching the archives but couldn't find the answer.
>
> What are the table structures for all of the tables that James could
> utilize? I'm using MySQL and I saw the script to create the Message
> table... but what about the table for managing users via a database table,
> spools, repositories, etc. Is there any documentation on this?
>
> I understand how to modify the JAMES.conf.xml document to use the database
> but it's just not painfully obvious to me how to set up the database
tables.
>
> Thanks for the help,
> Michael
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: james-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: james-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: james-user-help@jakarta.apache.org
Re: Database Structure
Posted by Serge Knystautas <se...@lokitech.com>.
I send this message periodically to the list. Sorry you couldn't find it.
Message table looks like this (at least in my database server's
fieldtypes... M$ SQL Server)
CREATE TABLE [dbo].[Message] (
[message_name] [varchar] (200) NOT NULL ,
[repository_name] [varchar] (200) NOT NULL ,
[message_state] [varchar] (30) NOT NULL ,
[error_message] [text] NULL ,
[sender] [varchar] (200) NOT NULL ,
[recipients] [text] NOT NULL ,
[remote_host] [varchar] (100) NOT NULL ,
[remote_addr] [varchar] (20) NOT NULL ,
[message_body] [image] NOT NULL ,
[last_updated] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Message] WITH NOCHECK ADD
CONSTRAINT [PK_Message] PRIMARY KEY NONCLUSTERED
(
[message_name],
[repository_name]
) ON [PRIMARY]
GO
'text' is arbitrarily long char data, and 'image' is arbitrarily long binary
data.
The Message is used for the spool and mail repositories. You can point
multiple repositories at this single table, so long as you use different
repository_names (part of the town:// construct)
The User table can be any table that has username and password in it. Town
will detect the primary key and assuming username is unique in the table, it
doesn't have to be the primary key. This will probably change in the
future, but that's how it works for now.
Serge Knystautas
Loki Technologies
http://www.lokitech.com/
----- Original Message -----
From: "Michael Loyd" <mi...@loydnet.com>
To: <ja...@jakarta.apache.org>
Sent: Tuesday, July 24, 2001 4:09 AM
Subject: Database Structure
> Hi. Sorry for posting a novice question that might be obvious to
everyone.
> But I'm just starting out with James and email applications. I tried
> searching the archives but couldn't find the answer.
>
> What are the table structures for all of the tables that James could
> utilize? I'm using MySQL and I saw the script to create the Message
> table... but what about the table for managing users via a database table,
> spools, repositories, etc. Is there any documentation on this?
>
> I understand how to modify the JAMES.conf.xml document to use the database
> but it's just not painfully obvious to me how to set up the database
tables.
>
> Thanks for the help,
> Michael
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: james-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: james-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: james-user-help@jakarta.apache.org