Database Migration

Hi,

I hope someone can help?

I've spent an awful lot of time trying to  Database Migrations working, I've followed the user manual closely and eventually got a prototype working with latest() method.

It is my intention to not only use Migration to manage changes to the DB going forward but also to onboard new customers with a their own set of tables with a combination of Migration and Seed.

I believe I would need to use the force() method to create customers with a their own set of tables, and the latest() method to implement changes across the entire DB?

However, I do not know if this is the right approach? And, and if it is, I cannot fathom how to call a specific migration file using the force method. Can force() process a migration not stored in App\DatabaseMigrations\  ???

My controller (below) will not find the file using force() whether the file is named "create_new_tables" like below, or "2022-01-21-223112_create_new_tables"

PHP Code:

namespace App\Controllers;

class Migrate extends \CodeIgniter\Controller
{
    public function index()
    { 
       $migrate = \Config\Services::migrations();
       $data['lang'] = 'Companies';
       $data['page_name'] = lang($data['lang'].'.title');
       $data['action'] = 'newco';
 
       if($this->request->getServer('REQUEST_METHOD') == 'POST' && $this->request->getPost('db_conn') && $this->request->getPost('db_prefix')){
 
          helper(['form', 'url']);
 
         if ($this->request->getPost('db_conn')) { 
             $data['db_conn'] = $this->request->getPost('db_conn', FILTER_SANITIZE_STRING);
         } 
 
         if ($this->request->getPost('db_prefix')) { 
             $data['db_prefix'] = $this->request->getPost('db_prefix', FILTER_SANITIZE_STRING);
         }
 
         $sessUpdate = [
          'dbprefixtemp' => $data['db_prefix'],
          'dbconntemp' => $data['db_conn']
         ];

         session()->set($sessUpdate);

         try {
           $migrate->force('App\Database\Migrations\create_new_tables');
           return redirect()->back()->withInput()->with('success', lang($data['lang'].'.success'));
         } catch (\Throwable $e) {
           return redirect()->back()->withInput()->with('error', lang($data['lang'].'.notadded')); 
         }
       }
 
       echo view('includes/headers/page_header', $data);
       echo view('companies/new_company_form', $data);
       echo view('includes/footers/copyright', $data);
       echo view('includes/js/coreJS');
       echo view('includes/footers/page_end_tags');
    }
} 


Sorry I'm no help in this, but I'm really curious about the logic of having customers with their own tables. It's that scalable? What if you need to do any analytics across all of the customers?

I would use all the same tables but give it also a customer_id.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Thank you BilltheCat and InsiteFX for your feedback, your comments are much appreciated.
My development is an application that allows companies to track progress of their own jobs, quotes and ultimately the profitability of their jobs, rather than a traditional website, blog or shopping site. The app runs on a subscription basis with companies opting in and out on an ad-hoc basis. I maintain a single (separate) database for all users (company, contact, login details) and a seperate database of prefixed tables for each companies data. This approach is very scalable as I can add/remove sets of prefixed tables as customers subscribe/unsubscribe, and can create new databases/connections as the number of customers increase. Regarding analytics, I have little need to perform analytics on the customer data, as in effect this is their property, and the data means little to me except total number of customers (from user database) and total number of jobs/quotes processed which is run periodically run using a pretty simple foreach query using the connection and prefix stored which is stored with each customer record.
My questions was really centered on the use of Migrations: whether I am using them correctly, i.e. 1 routine to create new tables, and another to update existing tables, and secondly should I/how do I use the force method to reuse the create routine. Any help with this will be much appreciated as I cannot fathom how to do this? 
Also, if you have any knowledge on this is there any additional documentation as I do not understand Up and Down, whether both should be specified and what they are used for?
Any help is very much appreciated.

Again thanks.

[quote pid="393220" dateline="1642861515"]
After running numerous tests and trying to follow the documentation, I don't believe Migrations will work for me at this time. Not only is it very unclear how to use Migrations (the documentation on the use of UP, DOWN and USE CASE examples using Migratins with a controller), Migrations fail when you try to determine default values for TIMESTAMP.
For example, Migratins will fail when running something like: Migrations cannot handle the default values for TIMESTAMP 

PHP Code:

$this->forge->addField([            
    'alert_id'              => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true,],
    'alert_profit'        => ['type' => 'tinyint', 'constraint' => 3,],
    'update_date'      => ['type' => 'timestamp', 'default' => 'current_timestamp() on update current_timestamp()',],
    'create_date'      => ['type' => 'timestamp', 'default' => 'current_timestamp()',],
 ]);
 
$this->forge->addPrimaryKey('alert_id', true);
$this->forge->createTable(alerts', true); 


If I run the following Migrations creates its own Default values. I also tried modify the default values created by Migrations using forge->modifyColumn(), but again Migrations fail when trying to set default values of either CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP() or CURRENT_TIMESTAMP()

PHP Code:

$this->forge->addField([            
    'alert_id'              => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true,],
    'alert_profit'        => ['type' => 'tinyint', 'constraint' => 3,],
    'update_date'      => ['type' => 'timestamp',],
    'create_date'      => ['type' => 'timestamp',],
 ]);
 
$this->forge->addPrimaryKey('alert_id', true);
$this->forge->createTable(alerts', true); 


Output:
Column: update_date, Data type: TIMESTAMP, Default: CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()

Column: create_date, Data type: TIMESTAMP, Default: '0000-00-00 00:00:00'

Referencing the MYSQL documentation "TIMESTAMP, CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP() and TIMESTAMP,  CURRENT_TIMESTAMP() is allowed in the same table (min ver 5.65), I'm running 5.7. Furthermore, if I run mysql create table quorey directly in workbench or via php script, or create the table via cPanel, absolutely not problem.
I'm not sure if Migrations is something new to Ci4, and/or is still under development but unfortunately it lacks in functionality and documentation.
[/quote]

There is currently a problem with the default value. The key only supports a string value.
You can define a database field as a string. Passing strings as fields

The force method expects an absolute path to the migration file.
Force

I'm not sure if migrations are what you need.
Perhaps it will be easier to use a SQL query template to generate tables.

I think you are going to wrong way.

The force() method is testing purpose only.
https://codeigniter4.github.io/userguide...html#force
> This method is recommended only for testing and could cause data consistency issues.

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

There is currently a problem with the default value. The key only supports a string value.
You can define a database field as a string. Passing strings as fields

The force method expects an absolute path to the migration file.
Force

I'm not sure if migrations are what you need.
Perhaps it will be easier to use a SQL query template to generate tables.

Many thanks, I'll keep an eye out for the fix as this type of functionality is more than handy.

---

I think you are going to wrong way.

The force() method is testing purpose only.
https://codeigniter4.github.io/userguide...html#force
> This method is recommended only for testing and could cause data consistency issues.

Thanks kenjis, I did note this but could not see another way utilising the Migrations to create a new set of empty table's. I suppose it's back to the tried and tested MySql create method, utilising Migrations for updates, unless you a have suggestions? I'm open to suggestions that use Ci4 functions like Migrations as the more I use Ci4 the more powerful I find it for all types of project development. Thanks again.

Maybe you are not aware of the distinction between DB Migrations and DBForge.
https://codeigniter4.github.io/userguide...forge.html

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Maybe you are not aware of the distinction between DB Migrations and DBForge.
https://codeigniter4.github.io/userguide...forge.html

OMG, I can't believe I am so stupid - LOL. I genuinely thought, for no logical reason at all, DB forge was part and the same as Migrations and could only be used with Migrations. Apologies for being such a fool and thank you for putting me straight.
Cheers.