change() fails, loudly.

[IngwiePhoenix]

I have made a rather simple migration to initialize my DB. But it seems that Phinx can't handle the automatic rollback...

<?php

use Phinx\Migration\AbstractMigration;

class UserInit extends AbstractMigration
{
    /**
     * Change Method.
     *
     * Write your reversible migrations using this method.
     *
     * More information on writing migrations is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-abstractmigration-class
     */
    public function change() {
        $users = $this->table("users");
        $users
            ->addColumn("username","string",["limit"=>20, "null"=>false])
            ->addColumn("password","string",["limit"=>61, "null"=>false])
            ->addColumn("email","string",["limit"=>128, "null"=>false])
            # Generated during registration
            ->addColumn("activkey","string",["limit"=>128, "null"=>false])
            # Admin, Mod, VIP, User
            ->addColumn("superuser","integer",["null"=>false])
            # Active, Inactive, Banned
            ->addColumn("status","integer",["null"=>false])
            # Can see debug information
            ->addColumn("developer","boolean",["default"=>false])
            # Will be listed in the Credits page
            ->addColumn("supporter","boolean",["null"=>false, "default"=>false])
            # Timestamps for registration and last visit
            ->addColumn("create_at","timestamp",["null"=>false, "default"=>"CURRENT_TIMESTAMP"])
            ->addColumn("lastvisit_at","timestamp")

            ->addIndex(["username", "email"], ["unique"=>true])
        ->save();

        $userProfile = $this->table("user_profile", ["id"=>false]);
        $userProfile
            ->addColumn("uID","integer",["null"=>false])
            ->addColumn("about","text")
            ->addColumn("avvie_ext","string",["limit"=>5]);

        # Socialness
        foreach([
            "skype", "steam",
            "psn", "xboxlife",
            "twitter", "facebook",
            "sofurry", "furaffinity"
        ] as $service) $userProfile->addColumn($service, "string", ["limit"=>255]);

        $userProfile
            ->addForeignKey("uID", "users", "id", ["delete"=>"CASCADE", "update"=>"CASCADE"])
        ->save();

        $userSettings = $this->table("user_settings",["id"=>false, "primary_key"=>"id"]);
        $userSettings
            ->addColumn("id","integer",["null"=>false])
            # Enable adult content
            ->addColumn("adult","boolean",["default"=>false])
            # Enable newsletter via email
            ->addColumn("newsletter","boolean",["default"=>true])
            # The user's profile can be seen in public, i.e. non-registered members.
            ->addColumn("public","boolean",["default"=>true])
            # Show the user's eMail to the public.
            ->addColumn("showEmail","boolean",["default"=>false])

            ->addForeignKey("id", "users", "id", ["delete"=>"CASCADE", "update"=>"CASCADE"])
        ->save();

        $userPerms = $this->table("user_permissions", ["id"=>false, "primary_key"=>"id"]);
        $fields = [
            # User can contribute to the public blog.
            "publicBlog",
            # This user can add, modify and delete jobs. Admins always can.
            "manageJobs",
            # User can create, edit and delete places. Admins always can.
            "editPlaces",
            # User can edit OTHER people's chars/media. Includes deletion. Mods and Admins, always.
            "editChars", "editMedia",
            # User can edit/delete forum related stuff
            "editFPosts", "editFTopics", "editFSections",
            # This user may turn on or off Developer status on other users.
            "editDev",
            # This user can broadcast a message through the chat using /wall.
            "canBroadcast"
        ];
        foreach($fields as $f)
            $userPerms->addColumn($f,"boolean",["null"=>false, "default"=>false]);

        $userPerms
            ->addColumn("id", "integer", ["null"=>false])
            ->addForeignKey("id", "users", "id", ["delete"=>"CASCADE", "update"=>"CASCADE"])
        ->save();

        // Private messaging.  Now it gets complicated. :)

        /*
         User conversations

             ONE Conversation
             HAS MANY Conversation Members
                 HAS MANY Users
             HAS ONE Private Message
         */
        $convMembers = $this->table("user_pm_conv_members",["id"=>false, "primary_key"=>["user_id","conv_id"]]);
        $convMembers
            ->addColumn("user_id", "integer", ["null"=>false])
            ->addColumn("conv_id", "integer", ["null"=>false])
        ->save();
        $conv = $this->table("user_pm_conv");
        $conv
            ->addColumn("owner_id","integer",["null"=>false])
            ->addColumn("subject","string",["limit"=>255,"null"=>false])
        ->save();
        $msg = $this->table("user_pm_msg");
        $msg
            ->addColumn("conv_id","integer",["null"=>false])
            ->addColumn("from_id","integer",["null"=>false])
            ->addColumn("body","text",["null"=>false])
            ->addColumn("sent","timestamp",["null"=>false])
        ->save();

        # FK setup for PM
        $convMembers
            ->addForeignKey("user_id", "users", "id", ["delete"=>"NO_ACTION", "update"=>"NO_ACTION"])
            ->addForeignKey("conv_id", "user_pm_conv", "id", ["delete"=>"CASCADE", "update"=>"NO_ACTION"])
        ->save();

        // Other stuff
        $this->table("user_sub", ["id"=>false])
            # The subscribing user
            ->addColumn("sID", "integer", ["null"=>false])
            # The target user
            ->addColumn("tID", "integer", ["null"=>false])

            ->addForeignKey("sID", "users", "id", ["delete"=>"CASCADE", "update"=>"NO_ACTION"])
            ->addForeignKey("tID", "users", "id", ["delete"=>"CASCADE", "update"=>"NO_ACTION"])
        ->save();

        $this->table("user_update")
            # The user who gets this
            ->addColumn("tID", "integer", ["null"=>false])
            # the kind of update. A numeric identifier
            ->addColumn("type","integer", ["null"=>false])
            # The ID of the referenced content
            ->addColumn("contentID","integer", ["null"=>false])
            # Relate
            ->addForeignKey("tID", "users", "id", ["delete"=>"CASCADE", "update"=>"NO_ACTION"])
        ->save();
    }
}

And now, this is what happens.

Ingwie@Ingwies-MBP.Speedport_W723_V_Typ_A_1_01_010 ~/W/BIRD3 $ npm run phinx migrate

> BIRD3@3.0.0-dev.72 phinx /Users/Ingwie/Work/BIRD3
> node util/phinx_runner.js "migrate"

Phinx by Rob Morgan - https://phinx.org. version 0.4.4

using config file ./phinx.yml
using config parser yaml
using migration path /Users/Ingwie/Work/BIRD3/config/../migrations
warning no environment specified, defaulting to: BIRD3
using adapter mysql
using database bird3

 == 20150705040659 UserInit: migrating
 == 20150705040659 UserInit: migrated 0.1222s

All Done. Took 0.1511s
Ingwie@Ingwies-MBP.Speedport_W723_V_Typ_A_1_01_010 ~/W/BIRD3 $ npm run phinx rollback

> BIRD3@3.0.0-dev.72 phinx /Users/Ingwie/Work/BIRD3
> node util/phinx_runner.js "rollback"

Phinx by Rob Morgan - https://phinx.org. version 0.4.4

using config file ./phinx.yml
using config parser yaml
using migration path /Users/Ingwie/Work/BIRD3/config/../migrations
warning no environment specified, defaulting to: BIRD3
using adapter mysql
using database bird3

 == 20150705040659 UserInit: reverting

  [PDOException]                                                                                                                 
  SQLSTATE[42000]: Syntax error or access violation: 1090 You can't delete all columns with ALTER TABLE; use DROP TABLE instead  

... what do I do now? I have to clear the whole DB if I ever have to roll this one back...

[elliotwms]

Do these tables exist? If not, you should be using the ->create() method instead of the ->save() method

When you run the migrate method does everything work as expected?

[IngwiePhoenix]

When I migrate upwards, it works. Migrating downwards fails. I am going to change the methods from save() to create() and see if it does anything.

[IngwiePhoenix]

The change did the trick. I just had to take care to not call change() twice on $convMembers, but that was just a tiny fail on my end.

Thanks for the tipp! I must have overseen change()…

Is there a documentation about the various add*() methods somewhere? Like what extra parameters can I put into the array to an addColumn() method? Stuff like that would be good to know ;)

Kind regards, Ingwie

[elliotwms]

It's all on the documentation as far as I know (I linked specifically to the writing migrations section). There's a couple of nuances that you'll only pick up by making mistakes in the first place. Don't worry, we've all been there :)

[elliotwms]

Also there is documentation in the code itself, so you can always go in to the declaration of addColumn() and see what you can pass in from there

[IngwiePhoenix]

Alright. I managed to wrap my head around it. I am just not finding on emethod, but I probably can find it in the code - deleting a table.

Right now I am adjusting on my config, but I will open another issue for that one to not mix up topics. :)