Setting Up the Database

The database for Warm Mail is fairly simple because we aren't actually going to store any of the emails in it.

We will need to store users of the system. For each user, we will need to store the following fields:

• username—   Their preferred username for Warm Mail

• password—   Their preferred password for Warm Mail

• address—   Their preferred email address, which will appear in the From field of emails they send from the system

• displayname—   The "human-readable" name that they would like displayed in emails from them to others

We will also need to store each account that users would like to check with the system. For each account, we will need to store the following information:

• username—   The Warm Mail user who this account belongs to.

• server—   The machine on which the account resides, for example, localhost or mail.tangledweb.com.au.

• port—   The port to connect to when using this account. Usually this will be 110 for POP3 servers and 143 for IMAP servers.

• type—   The protocol used to connect to this server, either 'POP3' or 'IMAP'.

• remoteuser—   The username for connecting to the mail server.

• remotepassword—   The password for connecting to the mail server.

• accountid—   A unique key for identifying accounts.

You can set up the database for this application by running the SQL shown in Listing 27.1.

Listing 27.1 create_database.sql—SQL to Create the Mail Database

create database mail;

use mail;

create table users
(
  username char(16) not null primary key,
  password char(16) not null,
  address char(100) not null,
  displayname char(100) not null
);

create table accounts
(
  username char(16) not null,
  server char(100) not null,
  port int not null,
  type char(4) not null,
  remoteuser char(50) not null,
  remotepassword char(50) not null,
  accountid int unsigned not null auto_increment primary key
);

grant select, insert, update, delete
on mail.*
to mail@localhost identified by 'password';

Remember that you can execute this SQL by typing

					
mysql -u root -p < create_database.sql

				

You will need to supply your root password. You should change the password for the mail user in create_database.sql and in db_fns.php before running it.

On the CD-ROM, we have also provided an SQL file called populate.sql. In this application, we are not going to create a user registration or administration process. You can add one yourself if you want to use this software on a larger scale, but if you want it for personal use, you will just need to insert yourself into the database. The populate.sql script provides a proforma for doing this, so insert your details into it and run it to set yourself up as a user.