[RFC] Migrations

[gildonei]

Summary

Create a native integration with PHP Phinx to generate database sync and throught developement, tests and production servers

Motivation

Almost all developers have to maintain code and database up-to-date and it includes deploy databases fixes and new features through those different servers.

Compatibility with Ubiquity's philosophy

Indicate compatibility or improvements in:

• ease of use
• code standardization
• framework resources
• agility on database maintenance

Expected results

A easy way to deploy database modifications through different servers

Additional context

Project on Github - https://github.com/cakephp/phinx Docs - https://book.cakephp.org/3.0/en/phinx.html

[jcheron]

Ubiquity does not currently have a migration system, and it is a good thing to talk about it :) I don't know Phinx, but after looking at the documentation, I find that the developer has a lot to do.

I imagined for Ubiquity something more automated.

• If the database has been modified => generation of the class update code.
• If the models have been modified => generation of the database update code.
• These code generations could be modified by the developer, and can be executed on demand

[gildonei]

• If the database has been modified => generation of the class update code.
  • How do you imagine Ubiquity will check for modifications? Something like ubiquity migrate dbcheck in devtools?
• If the models have been modified => generation of the database update code.
  • How do you imagine Ubiquity will check for modifications? Something like ubiquity migrate modelcheck in devtools?
• These code generations could be modified by the developer, and can be executed on demand
  • By code generated you mean the result code generated and read to execute deploy

Also, i think it could have some Callbacks like beforeDeploy and afterDeploy where users could run some sql or script to execute something

[jcheron]

ping @suresh- who had already covered this subject in the tchat room

@gildonei Yes for devtools, and for before and after methods, they're good ideas. Also to be integrated in the Models part of the webtools

[UlasSAYGINIM]

What will be done for this issue? what is the plan? how migration will work and the beforeDeploy and afterDeploy how it will work? I wonder could you mention here more?

[jcheron]

It took a while, but better late than never. The 2.4.8 version of Ubiquity integrates a migration system. But it is not a migration system comparable to other frameworks, by choice: No php code generation, or code to write, but a simple database update system, from existing models.

Example : After adding the age and email members in the User model:

class User {
	...
	
	#[Column(name: "age",dbType: "int(11)")]
	private $age;
	
	#[Column(name: "email",dbType: "varchar(255)")]
	#[Validator(type: "email",constraints: ["notNull"=>true])]
	#[Validator(type: "length",constraints: ["max"=>255])]
	private $email;
	...
}

Note that devtools now have a command to generate or modify models from scratch:

Ubiquity model

Migration from the devtools :

Ubiquity info-migrations

to retrieve the list of modifications to do on the DB:

Ubiquity migrations

to implement these modifications:

Or from the Webtools

In the Models part, choose Reverse engineering, then Database migrations:

Update of models from the database

It is possible to generate the classes again, if the database has been restructured.

Ubiquity all-models

Note that the generation preserves the methods of the models that have been implemented/customized.

[gildonei]

Fantastic samples and implementation. I would like just to add a note about Integer Types in MySQL 8.0.17, that length attributes are now deprecated and will be removed

* https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html

As of MySQL 8.0.17, the display width attribute is deprecated for integer data types; you should expect support for it to be removed in a future version of MySQL.

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types; you should expect support for it to be removed in a future version of MySQL. Consider

[jcheron]

I would like just to add a note about Integer Types in MySQL 8.0.17, that length attributes are now deprecated and will be removed

I left unchanged for the moment. Mysql supports well the int at the declaration, but when we require the type, the returned value is for the moment int(11) with SHOW COLUMNS: