Phinx: Need to be able to "updateData" when Migrating Existing Tables...

[BallisticPain]

I'm working on a PR for this, but basically we need the ability to migrate data in existing tables and rows. So we need a way to "update" a record.

Unfortunately I had this written on a colleague's system via paired programming, but we lost it when he did a composer update ... DOH!

Also @robmorgan, is this something you would consider if I provide all the necessary pieces in the PR? I'm going to submit regardless, just wanted to know.

Thanks!

[rquadling]

You can use $this->query('UPDATE table SET col=val WHERE col<>val;'); if your updates are simple enough.

Or interact with your applications service layer and get it to do it for you, simply using Phinx as a platform to initiate changes to your data.

[BallisticPain]

@rquadling I appreciate you sharing that, but I'm updating 144 thousand rows of data with many columns of data. I wanted to be able to use a method similar to the setData. We are morphing a legacy users table, and we want this managed by the migration. Thankfully the down is simple and it drops the columns. However, the up requires data to be input into each column that is unable to accept a null.

We actually don't yet have services to handle changing this data as we are starting by adding the columns.

Are you saying that this is unlikely to be accepted because I could use raw SQL or my own application to insert the data?

[rquadling]

Phinx supports seeds. Can these be coerced into providing what you need?

In terms of accepting any PRs, each will be examined on its own merits. Any PR will have to work across all the adapters to provide a standardised set of features (as much as possible).

[robmorgan]

We will be adding update() and delete() methods in the future. This might work for you.

[BallisticPain]

@robmorgan
I added an update method just for this purpose. So maybe you will be able to use/re-use my work. As I stated above I'm re-writing it as I have to run it through my tests again to be sure I have it set correctly. I also added an updateData method that grabs the data an uses the update method. It also clears the data when done as that's something I ran into an issue with.

I intend to provide all the proper documentation and tests to support these features. Which is obviously a bit more than I had originally written, but I'll get it done within a day or so (I hope).

It's good to hear this is something you're at least interested in and I may be able to save you some work.

Thanks for a great library!

@rquadling I am aware of seeds, but this isn't seed data. This is production data that needs to be handled in a migration, but thank you for another good suggestion. It is one I contemplated.

[BallisticPain]

@rquadling or @robmorgan
Should I submit a PR against the 5.x branch or the 6.x branch?

[rquadling]

0.6.x-dev branch please.

[paulrwest]

When will the updateData() be added to release? I would love this feature to be available.

[billystalnaker]

+1 for updateData()

[BallisticPain]

@paulrwest @billystalnaker please review the discussion on #879 ... I originally submitted a PR, but after some work/re-work we determined that at this point in time I didn't have enough time to properly create an updateData that would be flexible enough for the largest use cases.

If you have time to submit a PR, please feel free to do so.

I ended up just writing my updates using my original method call using my MigrationHelper class. So maybe that will help if you need a quick fix... Here's specifically the code I used in case this can help another.

    public static function update(Table $table, $row, PdoAdapter $adapter, $pKey = 'id')
    {
        // Grab identifier
        $key_value = $row[$pKey];
        unset($row[$pKey]);
        $columns = array_keys($row);
        $sql = sprintf(
            "UPDATE %s SET ",
            $adapter->quoteTableName($table->getName())
        );
        $setColumns = [];
        foreach ($columns as $col) {
            $setColumns[] = $adapter->quoteColumnName($col) . '=?';
        }
        $sql .= implode(', ', $setColumns);
        $sql .= " WHERE $pKey=?";
        $stmt = $adapter->getConnection()->prepare($sql);
        $key = [
            0 => $key_value,
        ];
        $stmt->execute(array_merge(array_values($row), array_values($key)));
    }

[dereuromark]

Anyone able to provide a patch with the suggested changes as PR?

[rahimnagori]

We will be adding update() and delete() methods in the future. This might work for you.

What is the progress of this ?

[lorenzo]

thanks for pinging this issue. This was already implemented, please check the documentation under "query builder"