If you’re following along, I covered the database basics yesterday. So, today, for today’s WHMCS Database tips and tricks entry, we’ll take a look at the database schema when working with WHMCS. This is a slight bit more complicated. If you didn’t go through the schema link from yesterday, I strongly encourage you to do so now. This will help you out a good bit, and provides a bit of reference material for you to go with. Make sure you take a look through the schema link, for the types of columns you can use in Laravel.
Let’s assume you’re going to create a new WHMCS addon. We’ll call it mynewaddon. In modules/whmcs-addons/mynewaddon/mynewaddon.php , you’ll want something like documented here. I won’t cover the addon module basics, just the upgrade, deactivate and activate functions. We’ll use this to determine whether or not you need to upgrade the database, and if you do, how to do it.
As with before, we’ll need to make sure to initialize Capsule:
use Illuminate\Database\Capsule\Manager as Capsule;
Let’s start with mynewaddon_activate :
function mynewaddon_activate()
{
//let's drop the table if it exists
Capsule::schema()->dropIfExists('mynewaddon');
//now we create it again
Capsule::schema()->create(
'mynewaddon',
function ($table) {
//incremented id
$table->increments('myid');
//a unique column
$table->string('uniquecolumn')->unique();
$table->integer('myintegercolumn');
}
);
}Great, now we have a working table right? Oh no! I forgot to add a column. That’s just wrong. I guess we need to do an update. But, how can we do it while making sure that everything is as it should be? Good question. Here’s a great way.
<?php
function mynewaddon_upgrade($vars) {
$version = $vars['version'];
# Run SQL Updates for V1.0 to V1.1
if ($version < 1.0.1) {
if(!Capsule::schema()->hasColumn('mynewaddon', 'iforgotcolumn'))
{
//good, we don't exist
Capsule::schema()->table('mynewaddon', function($table)
{
$table->string('iforgotcolumn');
});
}
}
//let's add a new table in 1.1, but make sure it doesn't exist already!
if ($version < 1.1) {
if (Capsule::schema()->hasTable('mysecondtable'))
{
return;
}
else
{
Capsule::schema()->create(
'mysecondtable',
function ($table) {
//incremented id
$table->increments('myid');
//a unique column
$table->string('uniquecolumn')->unique();
$table->integer('myintegercolumn');
}
);
}
}
}Up next? More ‘wheres’ and what to do with all this data! Check back later
