Direct database access

How to reach a hub's database from the command line, and the one convention that will confuse you if nobody tells you about it: the #__ table prefix.

You will want a mysql prompt sooner than you expect — to see what a migration actually did, or what a form saved. Read this before you write a query into an extension, because the prefix rule is not optional and the mistake it prevents does not show up until someone installs your work on a hub configured differently from yours.

The credentials

A hub keeps its database credentials in app/config/database.php, which returns a plain PHP array:

return array(
	'dbtype'   => 'mysql',
	'host'     => 'localhost',
	'user'     => 'hubuser',
	'password' => '…',
	'db'       => 'hubdatabase',
	'dbprefix' => 'jos_',
	'port'     => '',
);

The file is written by the installer and is not in the repository. Read it over SSH; it is only readable by the web server user and root.

mysql -u hubuser -p hubdatabase

The table prefix

Every Hubzero table name in the database carries a prefix — jos_ on a default install, whatever dbprefix says on yours. Code never writes that prefix. It writes #__, and the driver substitutes the configured value as the statement goes out:

	public function replacePrefix($sql, $prefix = '#__')
	{
		// As we replace strings of different lengths, subsequent prefix positions will become invalid.
		// Thus, we track that differential here to account for the shifting locations
		$differential = strlen($this->tablePrefix) - strlen($prefix);
		$count        = 0;

		foreach (Str::findLiteral($prefix, $sql) as $prefixPosition)
		{
			$sql = substr_replace($sql, $this->tablePrefix, $prefixPosition + ($differential*$count), strlen($prefix));
			$count++;
		}

		return $sql;
	}

So #__users in a query is jos_users in the database, and a query with a literal jos_ in it is a bug: it breaks on any hub whose prefix differs. It will not break on yours, which is the problem — the failure arrives on somebody else's hub as Table 'theirdb.jos_booking_instruments' doesn't exist, naming a prefix they have never used. Copying a working statement out of a mysql prompt and into a model is how it happens; put the #__ back on the way in. The same placeholder is the default table name a model derives for itself — #__{namespace}_{plural model name} — so a Post model in the blog namespace reads and writes #__blog_posts without being told to.

Translate in your head when you move between a mysql prompt and the code: SELECT * FROM jos_users at the prompt is #__users in a query.

Dumping and loading

Muse wraps the two operations you actually need, using the hub's own credentials so you do not have to find them:

php core/bin/muse database dump
php core/bin/muse database load <file>

dump writes to your home directory. See the muse database reference.

From code

Do not open your own connection. The hub's driver is App::get('db'), and above it sit a query builder and an ORM that bind every value they are given:

$rows = Post::all()
	->whereEquals('state', 1)
	->order('created', 'desc')
	->rows();

The Database book covers all three layers: the query builder, the ORM, and migrations for schema changes. Schema changes belong in a migration, not in a mysql prompt — a change made by hand is a change the next hub to run the migrations will not have.