Updating from MX Universal Editor to Assets

Question

I'm trying to upgrade an EE1 site that was using MX Universal Editor as it's file manager to use Assets 2 instead.

At the moment I'm dealing with Matrix fields that have values like /folder/subfolder/subsubfolder/filename.jpg in exp_matrix_data

If I change the column fieldtype to the native file manager, nothing shows up because the image isn't in the root level of the file upload directory, it's in a subfolder of that directory which is why I used the Universal Editor in the first place and why I want to change to Assets now.

If I change to Assets, again nothing shows up. If I click to browse files and add a file, the file is added succesfully, but if I then save the entry, all the other matrix rows that I haven't updated yet have their data wiped from the db.

After updating the entry by adding my own image with the Assets fieldtype, the value that ends up in db looks like filename.jpg but if I manually update the value to remove /folder/subfolder/subsubfolder/ and then check the entry, still nothing shows up. So is there some other piece of the puzzle missing?

I've got several hundred entries all with a handful of images each so I want to find an automatic way of updating all the entries to use Assets successfully.

Answer 1

Assets doesn't do its business in the exp_matrix_data or exp_channel_data tables. It uses its own exp_assets_selections table, where it references a file_id (entry in exp_assets_files), then entry_id and other relevant columns. That's the table you'll need to populate.

So for each file in a Matrix:

1. Grab the relevant entry ID, field ID, col ID and row ID.
2. Extract the file name and find the ID of that file in exp_assets_files.
3. Enter that data in exp_assets_selections.

Answer 2

Turns out it wasn't that hard after all. Here's the query if anyone's interested:

INSERT INTO exp_assets_selections (file_id, entry_id, field_id, col_id, row_id)
SELECT af.file_id, md.entry_id, 60, 5, md.row_id
FROM exp_matrix_data md
INNER JOIN exp_assets_files af
  WHERE INSTR(md.col_id_5, af.file_name) > 0