The following content is adapted from the Dada Mail Manual. The Dada Mail Manual is available through the purchase of Pro Dada.

Support Dada Mail by purchasing Pro Dada!

Professional installation is available.

Configuring Dada Mail's SQL Backend

Configuring Dada Mail's SQL Backend

A lot of the neat new features of Dada Mail require that you use the SQL backend. We do suggest that in 3.0 you always use the SQL backend, if given a choice.

Dada Mail, by default, does not use an SQL backend. There's various reasons for this.

One is historical: when Dada Mail was first created in 1999, the tools to easily administrate an SQL database weren't really available and access to an SQL server wasn't a part of a basic web hosting package.

Another reason is ease-of use: without requiring you to set up an SQL backend, the amount of steps to set up and install Dada Mail is lower, as are the requirements for installing Dada Mail.

Anyways, this chapter will be about setting up the SQL backend. Before we start, make sure you've installed Dada Mail, using either the basic or advanced installation methods. We do suggest using the advanced method, as the amount of variables that need to change starts to grow and you will one day want to upgrade Dada Mail and not have to go picking through the Config.pm file, trying to figure out what variables you've changed.

This chapter will go through the process using a outside config file, ala the Advanced Installation method. We'll also be using the, MySQL backend, as it's probably the most widely used backend when configuring Dada Mail for SQL.

We'll also be performing the SQL configuration using a tool called, phpMyAdmin, which allows you to administration a MySQL server through your web browser. We'll also be using cPanel, to initially create our database and user. If you're comfortable, you may do all these steps, using the mysql shell. We're going to go slow and use the above methods to better illustrate the process.

Step #1 Setting up the SQL Database and User

The first thing you'll need to do is set up a new MySQL Database and a new MySQL User. We're using cPanel to do this. In cPanel, click on the, icon entitled, MySQL Databases


Finding and click on the, "MySQL Databases" Icon

Create a New Database

You'll first need to create a new database. In this example, we're going to use, dadamail as the name of our database.


Creating a new database called, "dadamail"

A note on naming databases and users using cPanel

cPanel has this interesting trait of appending your account's username to the beginning of your database name and user names. So, for example, my account's username is, dadabh and the name of the database I've created is, dadamail, but cPanel's wizard will change the database's name to be, dadabhco_dadamail. Strange - take note, as this may not happen to you, but in these examples, this does happen for both the database's username and table name.

Add New User

The next thing we need is to add a new user. In this example, we're going to use, dada as the name of our user. Under the, MySQL Users heading, enter, dada as our user. Set the password to whatever you'd like. We're going to use, dadapass as our user's password. Again, the database username will be changed to, dadabhco_dada, in our examples


Creating a new user called, "dada"

Add User To Database

Next, we're going to add the user we just created, to the database we just created. Under the heading labeled, Add User To Database select the user, dadabhco_dada and the database, dadabhco_dadamail


Adding the, "dadabhco_dadamail" user to the, "dadabhco_dada" database

If asked, give the user, All Privileges to this database.


Giving, "All Privileges to the, "dadabhco_dada" user for the, "dadabhco_dadamail" table

Step #2 Create the necessary SQL tables

In the, Home screen of cPanel, click on the icon labled, phpMyAdmin


Finding and click on the, "phpMyAdmin" Icon

Once phpMyAdmin is loaded, select your, dadabhco_dadamail database on the left hand menu:


Selecting the, "dadabhco_dadamail" database

There's many ways to create new tables using phpMyAdmin, but we're going to do it using the, SQL tab. Click on the, SQL tab on the top of the phpMyAdmin screen:


Finding and clicking on the, "SQL" tab

The SQL that we need to run is located in the Dada Mail distribution itself, in the directory, dada/extras/SQL. There should be a few files, but the one we're interested in is the one named, mysql_schema.sql.


Finding and selecting the, "mysql_schema.sql" file

Open this file up in a text editor, and copy its contents.


Copying the contents of the, "mysql_schema.sql file"

In phpMyAdmin, paste the contents of this file into the text area labled, Run SQL query/queries on database... and click on the button labled, Go


Pasting the SQL schema into phpMyAdmin

Your SQL Database, User and Tables should all be set up.

Step #3 Configure Dada Mail to use the SQL backend.

Since we're using the outside configuration file, the needed variables that need to be configured should already be in the, .dada_config file, located on your hosting account. The chunk we'll need to change will look something like this:

        # start cut for SQL Backend
        =cut
        
        $SUBSCRIBER_DB_TYPE       = 'SQL'; 
        $ARCHIVE_DB_TYPE          = 'SQL'; 
        $SETTINGS_DB_TYPE         = 'SQL'; 
        $SESSION_DB_TYPE          = 'SQL'; 
        $BOUNCE_SCORECARD_DB_TYPE = 'SQL';
        $CLICKTHROUGH_DB_TYPE     = 'SQL'; 
        
        
        %SQL_PARAMS = ( 
        
                # May just be, "localhost" 
                dbserver         => 'localhost',
                
                database         => '',
                
                # MySQL:      3306
                # PostgreSQL: 5432      
                port             => '3306',
                
                # MySQL:      mysql 
                # PostgreSQL: Pg
                # SQLite:     SQLite
                dbtype           => 'mysql',  
                
                user             => '',          
                pass             => '',
                
                subscriber_table                => 'dada_subscribers',
                profile_table                   => 'dada_profiles', 
                profile_fields_table            => 'dada_profile_fields', 
                profile_fields_attributes_table => 'dada_profile_fields_attributes',
                archives_table                  => 'dada_archives', 
                settings_table                  => 'dada_settings', 
                session_table                   => 'dada_sessions', 
                bounce_scores_table             => 'dada_bounce_scores',
                clickthrough_urls_table         => 'dada_clickthrough_urls', 
                
        );
        
        $LIST_IN_ORDER = 1; 
        
        
        =cut
        # end cut for SQL Backend

We'll be editing the copy of the .dada_config file that lives on your hosting account. You may have to download the file to your Desktop, edit the file and then upload the file back to your hosting account. My FTP client, Transmit has a very useful editing feature, where double-clicking on a file will download the file automatically and open it up into my text editor, TextMate. When I save the file, it's then uploaded to the server automatically. Your FTP Client may have a similar feature.

Remove the, =cut lines

To enable the use of these variables, we first need to remove the lines:

        # start cut for SQL Backend
        =cut

and,

        =cut
        # end cut for SQL Backend

Anything between two, =cuts is invisible to Perl and thus won't run. Once you've removed those lines, we can configure the SQL backend.

*_DB_TYPES

The variables, $SUBSCRIBER_DB_TYPE, $ARCHIVE_DB_TYPE, $SETTINGS_DB_TYPE, $SESSION_DB_TYPE, $BOUNCE_SCORECARD_DB_TYPE and $CLICKTHROUGH_DB_TYPE are already filled out with the value, SQL, so there's nothing we need to change!

%SQL_PARAMS

The, %SQL_PARAMS hash needs four key/values to be changed:


Editing the .dada_config file for the SQL configuration

And, that's it! Save the .dada_config file and Dada Mail should be all set up for the SQL backend.

Verifying the SQL Backend is enabled.

To verify that your SQL Backend is enabled, log into a list (create one, if you haven't already) using your Dada Mail Root Password. On the left hand menu, click on, Profile Fields, under the heading, Profiles. If the SQL backend is enabled, you'll be able to add/edit and remove Subscriber Profile Fields.


Verifying if the SQL Backend is activated, by seeing if Subscriber Profile Fields can be added

See Also

The Config.pm file has embedded documentation on how to configure Dada Mail for an SQL backend. It's useful if you're not using the outside config file or have a slightly different Dada Mail setup, as the one we're describing.

Dada Mail Project

Download

Installation

Support