Update Database Configuration page to reflect simplified array from Backdrop 1.30.0

[yorkshire-pudding]

In backdrop/backdrop-issues#2231 we introduced a simplified array for the database settings. This will be supported from 1.30.0 and will also be the default method used, replacing the db string.

The Database Configuration page needs to be updated. I have created this issue to share drafts of the revised page.

[izmeez]

Just found this issue as I saw the docs on this need improving. I'll come back with something.

[yorkshire-pudding]

@izmeez - I've started doing this offline as the editor experience on the page was a nightmare. Here is the HTML as far as I had got:

Revision so far

Most Backdrop sites have a single MySQL database to which they connect. If your site only needs to connect to a single database, it should be able to use the simple database configuration array at the top of the default settings.php file:

<code>
  $database = array(
    'database' => 'database_name',
    'username' => 'user',
    'password' => 'pass',
    'host' => 'localhost',
  );
</code>

Replace "user", "pass", "localhost", and "database_name" with the applicable information from your server. If you do not have a database, check with your hosting provider on how to create one.

The array supports several optional elements that have a default value and only need to be entered if your database does not use the default value:
<ol>
  <li>'port' = NULL  </li>
  <li>'prefix' = ''</li>
  <li>'charset', 'collation' or 'driver'</li>
</ol>

Prior to version 1.30.0, Backdrop used a database configuration string by default. See the <a href="/change-records/database-configuration-can-now-be-stored-in-a-simple-array">change record</a> for details.

<h2 id="port">Port</h2>
The 'port' setting is optional. If your database server is not running on the default port (3306 for MySQL), you can specify the port number here.

<h2 id="prefix">Prefix</h2>
Adding a 'prefix' setting to the array is optional. It allows you to install Backdrop on the same database as other pieces of software. Using a prefix such as "backdrop_" will prepend that string to all database tables so that they do not collide with other tables within the same database. Using a separate database for each Backdrop installation is recommended, but if you are limited in the number of databases on your server, the prefix setting may help you install more sites on a single server.

The prefix can also be an array to specify different prefixes for different tables. For example:

<code>
  $database = array(
    'database' => 'database_name',
    'username' => 'user',
    'password' => 'pass',
    'host' => 'localhost',
    'prefix' => array(
      'default'   => 'backdrop_',
      'users'     => 'shared_',
      'sessions'  => 'shared_',
    ),
  );
</code>

In this example, the 'default' prefix will be used for all tables except for the 'users' and 'sessions' tables, which will use their respective prefixes.

<h2 id="charset"></h2>
By default, Backdrop uses the 'utf8mb4' character set. If you need to use a different character set, you can specify it in the database configuration array. For example, to use the 'latin1' character set, you would add the following line to the database configuration array:

<h2 id="utf8mb4">4-byte UTF-8 character support (Emoji, Mathematical symbols, and extended Chinese characters)</h2>

Starting in Backdrop 1.5.0, the installer within Backdrop will automatically attempt to determine if your server supports storing 4-byte characters in the database. Without enabling this ability, characters such as emoji may show up as an unknown character when viewed, or sometimes two separate characters. There are specific server requirements for supporting these characters:

<ol>
<li>
The MySQL server must support the utf8mb4 charset (5.5.3 and up).

You can check your MySQL version by running the command <code>mysql --version</code>.
</li>

<li>
The PHP MySQL driver must support the utf8mb4 charset (libmysqlclient 5.5.3 and up or mysqlnd 5.0.9 and up).

The MySQL driver may be determined by visiting <code>admin/reports/status/php</code> on your site and searching for "<code>Client API version</code>" under the "<code>pdo_mysql</code>" section.
</li>

<li>In order to allow for large indexes, MySQL must be set up with the following <code>my.cnf</code> settings:

<code>
[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true
</code>

These settings are available as of MySQL 5.5.14, and are defaults in MySQL 5.7.7 and up.
</li>
<li>The following line must be present in your <code>settings.php</code> file:
<code>
$database_charset = 'utf8mb4';
</code>
</li>
</ol>

<h3 id="utf8mb4-upgrade">Upgrading an existing database to 4-byte UTF-8 encoding</h3>

If you are upgrading an existing Backdrop site, this feature may be enabled by doing the following:

<ol>
<li>Add the <code>$database_charset</code> variable to your <code>settings.php</code> file:
<code>
$database_charset = 'utf8mb4';
</code>
</li>
<li>
Visit your site status report at <code>admin/reports/status</code>. An entry will be present showing the current status of your database under the "Database system 4 bit UTF-8 support" section. If an upgrade is possible, click the link for "Database tables need conversion". If your database does not support 4-byte UTF-8, check the server configuration as described above. If unable to upgrade, remove the line for <code>$database_charset</code> in settings.php to fallback to the default.
</li>
<li>
<strong>Make a database backup.</strong> The upgrade process is irreversible and in the event of an error, you will want to restore a previous version.
</li>
<li>
The link in the status report will take you to the database upgrade tool at <code>admin/config/development/utf8mb4-upgrade</code>. Click the button for "Upgrade database to 4 byte UTF-8". This process will put your site into offline mode during the upgrade. The time of the upgrade depends on the size of your database.
</li>
</ol>

After upgrading the database, you should be able to insert Emoji into any value in Backdrop and it should be saved and displayed properly, including node titles, user names, any custom fields, block titles, etc.

<h2 id="primary-secondary">Setting up a Primary/Secondary Database Configuration</h2>

Sites that experience heavy amounts of database queries or require redundancy to improve up-time may set up Backdrop to use multiple database servers. Most queries in Backdrop are done against the primary server, but when doing a query either in code or through the Views module, queries can be marked as "safe" for the secondary server, offloading a substantial amount of queries from the primary server onto the (read-only) secondary server.

To configure Backdrop to use a Primary/Secondary configuration, do not specify a simple <code>$database</code> string. Instead, specify an array of database configuration using the <code>$databases</code> (note the plural form) variable.

This configuration is equivalent to the single database string:
<code>
$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'database_name',
  'username' => 'user',
  'password' => 'pass',
  'host' => 'dbserver1.example.com',
);
</code>

And a second (secondary) database may be added by additionally specifying:

<code>
$databases['default']['secondary'][] = array(
  'driver' => 'mysql',
  'database' => 'database_name',
  'username' => 'user',
  'password' => 'pass',
  'host' => 'dbserver2.example.com',
);
</code>

Note that the "secondary" property is a multiple value array. This allows setting up multiple secondary servers, over which secondary-safe queries will be distributed.

<h2 id="other-databases">Non MySQL Databases</h2>

Backdrop only supports MySQL databases within core. This means that it may make assumptions about MySQL-specific syntax and use non-ANSI standard functions. Backdrop core also assumes MySQL DATETIME fields for its core date.module.

However, Backdrop may still connect to non-MySQL databases for specialized applications, such as using PostgreSQL for spatial searches or querying a corporate MS SQL server. To connect Backdrop to these databases, specify them as a separate "target" in the configuration:

<code>
$databases['other_server']['default'] = array(
  'driver' => 'pgsql',
  'database' => 'database_name',
  'username' => 'user',
  'password' => 'pass',
  'host' => 'dbserver3.example.com',
);
</code>

Then when executing queries, simply specify the target name ("other_server" in this example):

<code>
$result = db_query("SELECT * FROM custom_table", array('target' => 'other_server'));
</code>

Backdrop may be connected to any type of database so long as it is supported by a PHP PDO Driver (<a href="http://php.net/manual/en/pdo.drivers.php">see the list of available drivers at PHP.net</a>).

<h2>Pre-configuring Databases at the Server-Level</h2>

Some specialized applications of Backdrop may find it more convenient to configure the database connection information through a server environment variable rather than modifying settings.php. For example in Apache, environment variables may be set within virtual host configurations with:

<code>
SetEnv BACKDROP_SETTINGS {"databases":{"default":{"default":{"driver":"mysql","database":"database_name","username":"user","password":"password","host":"localhost"}}},}
</code>

These settings are specified as a JSON array. If these server-level settings are found, they will override the values in settings.php with the same keys. Other settings besides database configuration (such as <code>$settings['config_directories']</code> may also be modified in this way.

[izmeez]

@yorkshire-pudding Thanks, I was just thinking about how best to approach it. I just copied your revised draft and pasted it into a new post in Backdrop as raw html. Saved it and there it is ready to work on locally. I'll have to make sure revisions are showing with diff. Thanks, this really helps.