phalcon / migrations Goto Github PK
View Code? Open in Web Editor NEWGenerate or migrate database changes via migrations.
Home Page: https://docs.phalcon.io/latest/en/db-migrations
License: BSD 3-Clause "New" or "Revised" License
Generate or migrate database changes via migrations.
Home Page: https://docs.phalcon.io/latest/en/db-migrations
License: BSD 3-Clause "New" or "Revised" License
Ignore generation of size for current column types
In some cases, it is good to have possibility to change default table name phalcon_migrations
to something another.
Instead of creating new folder .phalcon
inside, use phalcon_migrations
table, by default.
When running:
vendor/bin/phalcon-migrations migration run ---log-in-db
A new .phalcon/migration-version is created and migrations are executed.
No phalcon_migrations table is created.
Expected result:
New table with migration log. Preferable without writing any files at all.
When generating a migration for a table in PostgreSql database the output is like this for the id primary key:
new Index('users_pkey', ['id'], null)
which should be
new Index('users_pkey', ['id'], 'PRIMARY KEY')
I believe this is because of this line:
https://github.com/phalcon/cphalcon/blob/a0b25edcfc143b780ec69d5b92cae9c0c37c1987/phalcon/Db/Dialect/Postgresql.zep#L93
PostgreSQL version: 10.*
CHANGELOG.md
Some names inside config file and CLI options are different, for example:
Config | CLI |
---|---|
logInDb | log-in-db |
migrationsDir | migrations |
migrationsTsBased | ts-based |
Some actions points
It is really annoying to remove 'AUTO_INCREMENT' inside each migration file if I forgot to put option to not include 'AUTO_INCREMENT'.
Now, with current PR merged - phalcon/cphalcon#14804 it is possible to create table columns with NULL definition.
But, generate command do not create files with 'notNull' => false
parameter inside definition array.
If I have field name "create_date" of type "datetime" (or "date") and when I run command:
vendor/bin/phalcon migration run --verbose
I see that:
1576145901.8674: ALTER TABLE test
MODIFY create_date
DATE NOT NULL AFTER name
=> 1576145901.89 (0.022550106048584)
regardless of whether something has been changed or not
Finally if the table has a lot of records, execution time of SQL queries it is very long.
My database: MySQL 5.5
PHP version: 7.2
Phalcon version: 3.4.1
Phalcon phalcon-devtools version: 3.4.9
During migration generation with foreign keys, it generated with schema name (referencedSchema
). The problem is that the name can be different from environment to environment. And migration can not be ran if name is different.
'references' => [
new Reference(
'fk_accessToken_client_1',
[
'referencedTable' => 'client',
'referencedSchema' => 'public',
'columns' => ['clientId'],
'referencedColumns' => ['id'],
'onUpdate' => 'NO ACTION',
'onDelete' => 'NO ACTION',
]
),
],
It would be nice if there is an option to clean/clear all tables or table data using migrations.
You can choose the whole schema or specific table.
vendor/bin/phalcon-migrations migration clean/clear
Introduction
I'm running migrations with --log-in-db
option (storing list of done migrations in database, not in local files). This option use phalcon_migrations
table for storing this information about migration and date of executing.
Issue description
I think, that in table phalcon_migrations
, the column version
should become PRIMARY KEY
(or UNIQUE KEY
). Two reasons about that:
prohibits use of DML command on a table (my_database.phalcon_migrations) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER
Current status
At this moment, column version
in table phalcon_migrations
is normal, non unique key, so multiple entries can have the same value. Table with migrations is defined in https://github.com/phalcon/migrations/blob/master/src/Migrations.php#L511
new Column(
'version',
[
'type' => Column::TYPE_VARCHAR,
'size' => 255,
'notNull' => true,
]
And it looks:
mysql> describe phalcon_migrations;
+------------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-------+
| version | varchar(255) | NO | MUL | NULL | |
| start_time | timestamp | NO | | CURRENT_TIMESTAMP | |
| end_time | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------+--------------+------+-----+-------------------+-------+
I tested both: setting version
as PRIMARY KEY
and UNIQUE KEY
, and migrations worked fine. Generally, both options (primary/unique) should give the same result, because if in MySQL table there is no PRIMARY KEY
, then value with UNIQUE KEY
is "promoted" to be PRIMARY KEY
. So perhaps PRIMARY KEY
is better option to make table structure more transparent.
Expected result:
Column version
on table phalcon_migrations
should be PRIMARY KEY
(or UNIQUE KEY
).
Unnecessary additional opinion
BTW, in my opinion, in future, the option --log-in-db
should be default option. It is necessary when using phalcon/migrations with multiple instances of application working with single database, useful in testing and CI.. I don't see benefits of storing this information outside the DB.
Instead current old implementation, use common one:
Add possibility to generate migration files with pre-defined namespace.
Currently the name of file is a name of table, and the class is some sort of joining table name with its version.
Example:
companies.php
class CompaniesMigration_100 extends Migration
With new approach the filename and the class name with be equal.
Example:
CompaniesMigration_100.php
class CompaniesMigration_100 extends Migration
master
4.0.x
and 4.1.x
Build phalcon and run all tests. To make sure that some changes are catched as soon as possible.
Migration enum columns changes the type from enum(str,str2...) to char(1), please fix it.
With 'migrationsDir' => 'db/migrations'
and 'descr' => true
configurations it shows:
PHP Notice: Array to string conversion in /srv/vendor/phalcon/migrations/src/Migrations.php on line 272
Questions should go to https://forum.phalconphp.com
Documentation issues should go to https://github.com/phalcon/docs/issues
Describe what you are trying to achieve and what goes wrong.
I dumped my SQL to the database (Postgres 9.4) and used devtools to generate ts-based migration files stored in db afterwards. All went well. However, after dropping the database and creating afresh, then attempting to run the same migrations generated by devtools (just simulating a fresh deployment), the migration fails with an error :
ERROR: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "public.client" does not exist"
I observed, the migrations may have been generated in a random order and so in an attempt to run on a fresh environment, the foreign table referenced may not have been created. Thus leading to errors.
References to similar issue:
Expected behaviour:
Generate migration files from db with reference to defined foreign relationships, ensuring referenced tables get created first in the hierarchy.
Provide output if related
`ERROR: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "public.client" does not exist" `
Provide minimal script to reproduce the issue
Files listed in the order they were generated by phalcon devtools
<?php
use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;
/**
* Class AccesstokenMigration_1532019814860526
*/
class AccesstokenMigration_1532019814860526 extends Migration
{
/**
* Define the table structure
*
* @return void
*/
public function morph()
{
$this->morphTable('accessToken', [
'columns' => [
new Column(
'token',
[
'type' => Column::TYPE_VARCHAR,
'notNull' => TRUE,
'size' => 80,
'first' => TRUE,
]
),
new Column(
'clientId',
[
'type' => Column::TYPE_VARCHAR,
'size' => 80,
'after' => 'token',
]
),
new Column(
'userId',
[
'type' => Column::TYPE_VARCHAR,
'size' => 80,
'after' => 'clientId',
]
),
],
'indexes' => [
new Index('accessToken_pkey', ['token'], NULL),
],
'references' => [
new Reference(
'fk_accessToken_client_1',
[
'referencedTable' => 'client',
'referencedSchema' => 'public',
'columns' => ['clientId'],
'referencedColumns' => ['id'],
'onUpdate' => 'NO ACTION',
'onDelete' => 'NO ACTION',
]
),
],
]
);
}
/**
* Run the migrations
*
* @return void
*/
public function up()
{
}
/**
* Reverse the migrations
*
* @return void
*/
public function down()
{
$this->getConnection()->dropTable('accessToken');
}
}
<?php
use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;
/**
* Class ClientMigration_1532019814860526
*/
class ClientMigration_1532019814860526 extends Migration
{
/**
* Define the table structure
*
* @return void
*/
public function morph()
{
$this->morphTable('client', [
'columns' => [
new Column(
'id',
[
'type' => Column::TYPE_VARCHAR,
'notNull' => TRUE,
'size' => 80,
'first' => TRUE,
]
),
new Column(
'name',
[
'type' => Column::TYPE_VARCHAR,
'notNull' => TRUE,
'size' => 80,
'first' => TRUE,
]
),
],
'indexes' => [
new Index('client_pkey', ['id'], NULL),
],
]
);
}
/**
* Run the migrations
*
* @return void
*/
public function up()
{
}
/**
* Reverse the migrations
*
* @return void
*/
public function down()
{
$this->getConnection()->dropTable('client');
}
}
System info and versions (if possible): (phalcon info
)
Environment:
OS: Linux edcef3d10fee 4.9.93-linuxkit-aufs phalcon/phalcon-devtools#1 SMP Wed Jun 6 16:55:56 UTC 2018 x86_64
PHP Version: 7.2.7-1+ubuntu16.04.1+deb.sury.org+1
PHP SAPI: cli
PHP Bin: /usr/bin/php7.2
PHP Extension Dir: /usr/lib/php/20170718
PHP Bin Dir: /usr/bin
Loaded PHP config: /etc/php/7.2/cli/php.ini
Versions:
Phalcon DevTools Version: 3.4.0
Phalcon Version: 3.4.0
AdminLTE Version: 2.3.6
Phalcon Framework version: (php --ri phalcon
)
phalcon
Web framework delivered as a C-extension for PHP
phalcon => enabled
Author => Phalcon Team and contributors
Version => 3.4.0
Build Date => Jun 6 2018 04:54:09
Powered by Zephir => Version 0.10.10-d1b4cc68d9
Directive => Local Value => Master Value
phalcon.db.escape_identifiers => On => On
phalcon.db.force_casting => Off => Off
phalcon.orm.events => On => On
phalcon.orm.virtual_foreign_keys => On => On
phalcon.orm.column_renaming => On => On
phalcon.orm.not_null_validations => On => On
phalcon.orm.exception_on_failed_save => Off => Off
phalcon.orm.enable_literals => On => On
phalcon.orm.late_state_binding => Off => Off
phalcon.orm.enable_implicit_joins => On => On
phalcon.orm.cast_on_hydrate => Off => Off
phalcon.orm.ignore_unknown_columns => Off => Off
phalcon.orm.update_snapshot_on_save => On => On
phalcon.orm.disable_assign_setters => Off => Off
php -v
)PHP 7.2.7-1+ubuntu16.04.1+deb.sury.org+1 (cli) (built: Jun 22 2018 08:44:50) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
with Xdebug v2.7.0alpha1, Copyright (c) 2002-2018, by Derick Rethans
with Zend OPcache v7.2.7-1+ubuntu16.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies
It would be nice if there is an option to skip the generation when de database structure isn't changed.
For example when there is a migration (v1.0.0) and nothing has changed running the generate will result in something like:
vendor/bin/phalcon-migrations migration generate
Database hasn't changed. Skipped generation. Run migration generate --force to force generating a new version.
To make migrations standalone package it is left to implement CLI commands with migrations.
Pick working code from devtools.
// TODO
Sometimes it can be useful to have an export function to export a schema.
A use case could be using your migrations for your testing framework.
To setup the database for the test you want to import an sql dump. It would be great if migrations can export the latest schema without the need to run all the migrations from version 1.
For example:
vendor/bin/phalcon-migrations migrations export
Will output a full sql dump of the latest migration. For example this could be used with Codeception in the following way.
modules:
enabled:
- Db:
dsn: 'mysql:host=localhost;dbname=testdb'
user: 'root'
password: ''
populate: true # run populator before all tests
cleanup: true # run populator before each test
populator: 'mysql -u $user -h $host $dbname < `vendor/bin/phalcon-migrations migrations export `'
Or save a specific version (1.2.1) like
vendor/bin/phalcon-migrations migrations export 1.2.1 > export.sql
At that moment, option descr
can be passed only from CLI. It will be ignored during reading configuration file.
It is not possible to run migration with Time Based (tsBased) format
Phalcon\Mvc\Model\Exception: Migrations were not found at:
/srv/tests/var/output/timestamp-single-version
/srv/src/Migrations.php:268
If I have table "test" with field type of "ENUM" and run command:
vendor/bin/phalcon migration generate --table=test
then is generate such file:
new Column(
'type',
[
'type' => Column::TYPE_CHAR,
'notNull' => true,
'size' => 1,
'after' => 'id'
]
)
where type = "char" and size = 1
Next when I run command:
vendor/bin/phalcon migration run --verbose
is doing such SQL:
1577088221.3511: ALTER TABLE test
MODIFY type
CHAR(1) NOT NULL AFTER id
=> 1577088221.3776 (0.026492834091187)
Finally my type of field was changed from "ENUM" to "CHAR".
Rewrite configurations, make them agnostic to main config.php
. And more adaptive to the config adapter: ini, php, etc.
Hi,
When creating a new column it appears that there is no option to use the TIME
datatype.
i.e
new Column('Time', [
'type' => Column::TYPE_TIME,
'size' => 10,
'notNull' => true,
]);
I suspect that this is down to a constant not being defined for it.
Column type: user_details VARCHAR(50)
ALTER TABLE user_details MODIFY COLUMN password varchar(49) DEFAULT NULL;
vendor/bin/phalcon-migrations generate --config=migrations.php
vendor/bin/phalcon-migrations run --config=migrations.php
During execution it takes too long for running...
Let's assume that we have in current database some foreign key on location.instrument_id
. Lets assume that we are deleting this column. This won't work because there is existing foriegn key.
ERROR: SQLSTATE[HY000]: General error: 1828 Cannot drop c
olumn 'instrument_id': needed in a foreign key constraint 'suzuki_test/location_ibfk_2'
Phalcon migration should check if there is any foreign key like:
select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = :currentTable: AND COLUMN_NAME = :currentColumn: AND REFERENCED_COLUMN_NAME IS NOT NULL;
Then on result from it it should delete all constraint with CONSTRAINT_NAME.
Currently i'm upgrading to devtools 4.0.0 and running in some strange issues.
Migrations are running fine with the old version
I have 2 migrations for the a table: 0.0.1 create table
$this->getConnection()->createTable( 'task_jobs', '', [ 'columns' => [ new Column( 'id', [ 'type' => Column::TYPE_INTEGER, 'size' => 20, 'notNull' => true, 'autoIncrement' => true, ] ), ], 'indexes' => [ new Index( 'PRIMARY', [ 'id' ], 'PRIMARY' ) ], 'options' => [ 'TABLE_TYPE' => 'BASE TABLE', 'AUTO_INCREMENT' => '1', 'ENGINE' => 'InnoDB', 'TABLE_COLLATION' => 'utf8mb4_general_ci' ], ] );
Second 0.1.0 creates the rest
$this->morphTable( 'task_jobs', [ 'columns' => [ new Column( 'id', [ 'type' => Column::TYPE_INTEGER, 'notNull' => true, 'autoIncrement' => true, 'size' => 20, 'first' => true ] ), new Column( 'task_id', [ 'type' => Column::TYPE_INTEGER, 'size' => 20, 'after' => 'id' ] ), new Column( 'run_at', [ 'type' => Column::TYPE_DATETIME, 'notNull' => true, 'size' => 1, 'after' => 'task_id' ] ), new Column( 'status', [ 'type' => Column::TYPE_INTEGER, 'default' => "0", 'size' => 1, 'after' => 'run_at' ] ), new Column( 'result', [ 'type' => Column::TYPE_TEXT, 'size' => 1, 'after' => 'status' ] ) ], 'indexes' => [ new Index( 'PRIMARY', [ 'id' ], 'PRIMARY' ), new Index( 'task_jobs_id_uindex', [ 'id' ], 'UNIQUE' ), new Index( 'task_jobs_tasks_id_fk', [ 'task_id' ], '' ) ], 'references' => [ new Reference( 'task_jobs_tasks_id_fk', [ 'referencedTable' => 'tasks', 'referencedSchema' => '', 'columns' => [ 'task_id' ], 'referencedColumns' => [ 'id' ], 'onUpdate' => 'RESTRICT', 'onDelete' => 'SET NULL' ] ) ], 'options' => [ 'TABLE_TYPE' => 'BASE TABLE', 'AUTO_INCREMENT' => '103706', 'ENGINE' => 'InnoDB', 'TABLE_COLLATION' => 'utf8mb4_general_ci' ], ] );
But now i get the error:
PHP Fatal error: Uncaught TypeError: Argument 2 passed to Phalcon\Db\Adapter\Pdo\Mysql::addForeignKey() must be of the type string, null given in /var/www/xxx/public_html/vendor/phalcon/migrations/src/Mvc/Model/Migration.php:728
if i change $tableReference->getSchemaName()
to $tableReference->getSchemaName() ?? ''
it works fine.
What do i need to set/enable what ever to fix that?
And as well from annotations like manytoone etc. I guess it will need some work. But i really like this feature from doctrine like: doctrine:schema:update.
When runnning the migrations under 7.4 I get
PHP Deprecated: Unparenthesized `a ? b : c ? d : e` is deprecated. Use either `(a ? b : c) ? d : e` or `a ? b : (c ? d : e)` in /vendor/phalcon/migrations/src/Migrations.php on line 538
Looks like the ternary operator need to be upgraded. https://www.php.net/manual/en/migration74.deprecated.php
Currently, the version is stored in .phalcon/migration-version
which requires a persistent storage. In a container environment this adds a lot of complexity.
If the version would be stored in the database itself, the containers running Phalcon could be fully immutable, while I don't see any real disadvantage. Even on the contrary, imagine that someone imports a database backup which is older than the version in file, leading to problems which could have been avoided if the version was in the dump itself.
My proposal is to give users a choice and keep the current file-based approach as default.
From PSR-2 to PSR-12
Inside tests/
folder create readme with guide how to configure local environment for efficient testing.
I hope in the next version of phalcon-devtools, we can have the multiple databases support
My webapp should connect to 2 differents DB ( not the same tables)
I want control the migration with multiple differents DB, actually, i found 2 solution
migration with differents config.ini => but i can't separate the migration log/file in .phalcon/migration-version
modify the code of Migrations.php
If some one has the same idea with me
Many thanks
Such options can not be specified from configuration file:
Actual Behavior:
When creating a migration on a table that has a column of type
json
the migration adds it asvarchar(1)
Expected:
Migration created with the type
json
Our current implementation depends on writing to .phalcon/migrations for running the migrations.
For running migrations in production this isn't an ideal solution.
I would like to request changes to be able to run the migrations without the need for writing a file.
Some one can hardly cache theirs models metadata, which can cause showing errors after migrations were applied.
Probably make sense to implement metadata cleaner...
When we migrating some tables, we don't control order of this tables.
And if we have foreign keys of that tables, we have errors.
So we should have some mechanic to disable and enable foreign key checks.
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE ...
SET FOREIGN_KEY_CHECKS=1;
When using the db migration I'm missing some mysql specific fields like enum.
Probably caused by using describeColumns on the database adapter that has no support for database specific fields.
Hi i was testing migrations from different branches onto development and this happened
'1582539287636860_0000100','2020-03-10 11:52:33','2020-03-10 11:52:33'
'1582539471102635_0000100','2020-03-10 11:52:33','2020-03-10 11:52:33'
'1582560343774480_0000100','2020-03-10 11:52:33','2020-03-10 11:52:34'
'1582712574839699_0000100','2020-03-10 11:52:34','2020-03-10 11:52:34'
'1582737171386285_0000100','2020-03-10 11:52:34','2020-03-10 11:52:34'
'1582738412346286_0000100','2020-03-10 11:52:34','2020-03-10 11:52:34'
'1583253607256544_0000100','2020-03-10 11:52:34','2020-03-10 11:52:34'
'1583774030720287_0000100','2020-03-10 11:52:34','2020-03-10 11:52:34'
'1583921033737407_0000100','2020-03-11 10:05:01','2020-03-11 10:05:01'
But there was one missing
1583773658223923_0000100
Executing on console
vendor/bin/phalcon-migrations migration run --config=migrations.php
the result was
Phalcon Migrations
Info: Everything is up to date
The migration that wasn't running had a previous date than my migration
1583774030720287_0000100
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.