2

I would like to use database migration tool in my current project. It's a php-project in zf3, therefore i decided for doctrine migrations. Here is pretty good example, how to use this tool.

The issue is, im adding tables from my code, which then are saved in a separate table. On init (first initial migration) I would like to remove all tables, that were added by program (from my code - the amount differs) and just create the table, in which the software saves created tables. I didn't find a simple solution, how to remove all tables except one from a database, therefore i thought, that i flush the whole database and then create the table, I need.

In order to not delete the migrations table, I created separated database, where only this one table exists. Now on up i would like to change the database, flush it, create the table, where all created tables will be saved, and change the database to migration, so that migration will be saved.

Here is the code, that will clarify, what I want to do:

public function preUp(Schema $schema) {
    $this->addSql('use migration');
    $this->addSql('drop database osm');
    $this->addSql('create database osm');
    $this->addSql('use osm');
}

public function up(Schema $schema){
    $table = $schema->createTable('axx');
    $table->addColumn('id', 'integer', ['autoincrement'=>true]);        
    $table->addColumn('title', 'text', ['notnull'=>true]);
    $table->addColumn('date_created', 'datetime', ['notnull'=>true]);
    $table->setPrimaryKey(['id']);
    $table->addOption('engine' , 'InnoDB');
}

public function postUp(Schema $schema){
    $this->addSql('use migration');
}

This code isn't working properly - the table post will be created in migration database. Why? Besides of that, I suppose there is in general a better solution for this problem, which i would like to hear.

tookie009
  • 186
  • 1
  • 14
  • please attach the entire code – PRO_gramista Jul 20 '17 at 07:43
  • in my code above we are only missing the down method, which isn't important in this case, cause its empty. – tookie009 Jul 20 '17 at 07:51
  • Im using doctrine migrations 1.5 – tookie009 Jul 20 '17 at 08:52
  • after executing the migration with --write-sql option, I can see all the sql-commands, that normally shoudl be executed. That's, how it looks like: `use migration; drop database osm; create database osm; use osm; CREATE TABLE axx(..) use migration; INSERT INTO main (version) VALUES ('20170720063432');` so the order is proper and if I excecute those commands in mysql, it's working properly. However, if I execute if over migrations:execute, it's not working properly - the main table will be created in osm-db. – tookie009 Jul 20 '17 at 09:50
  • 1
    If the problem is the migrations table, couldn't you create it again after the drop of the entire db and remove the preUp and postUp? You are talking about only the first migration, so that table should be empty. – ste Jul 21 '17 at 00:35
  • @ste but what if I want to migrate to newer revisions afterwards? will it be possible? I will give it a try, but the idea sounds good. – tookie009 Jul 21 '17 at 05:03
  • 1
    Yes you can do a `doctrine:migrations:migrate next` after generating the migration classes. When you get the migration class with `doctrine:migrations:diff` you should simply remove some drops in the `up` script and some create in the `down` script which corresponds to table created by the code of your application. – ste Jul 21 '17 at 05:14
  • Unfortunately after second migration (executing twice the same, in my case initial migration) i get `Migration 20170720063432 failed during Execution. Error The table with name 'osm.wwww' already exists.` It seems, that doctrine checks/tries to create the new table before the whole database was recreated. So the solution descibed here is still not working. – tookie009 Jul 21 '17 at 06:10
  • But did you generate the first migrations class using `doctrine:migrations:diff`? That command should output many single native SQL statements – ste Jul 21 '17 at 18:20
  • @ste sorry for not answering for so long, but i had vacation ;) Unfortunatelly I'm using the latest version 1.5 and there is no diff-command ;( – tookie009 Aug 14 '17 at 13:46
  • @tookie009 nor `migrations:diff`? – ste Aug 14 '17 at 19:37
  • @ste unfortunatelly, nope. What can I additional do is checking if the table exists before creating the table again, but that's additional work and soon it will be overcomplicated. I still believe, there is a better solution – tookie009 Aug 15 '17 at 13:04

0 Answers0