Stage 1:
Put the kettle on, make a brew - you may need it!
Background of SQL file function:
My understanding of the sqlite structure for assets for V1 and V2 is below, these files are located at …/storage/data/
V1 sql files
- Cockpit.sqlite asset_folders
- Gives folders
- States whether a folder is a sub folder under the {_p:…} attribute
- Cockpit.sqlite asset
- Gives the link/address to each asset within the uploads folder: i.e:
- /2020/04/07/”image or asset name”.svg
- Is contained in storage/uploads/2020/04/07/
- And is file: ”image or asset name”.svg
V2 sql files
- App.sqlite assets
- Gives the link/address to each asset within the uploads folder:
- Assets.sqlite folders
- Gives folder breakdown
- States whether a folder is a sub folder under the {_p:…} attribute
- States the user who created the folder
Copy over raw data:
Copy over whole storage/uploads folder from v1 to v2 install directory. Keeping all filenames and subfolders the same. This ensure the old raw data from v1 is still present in the v2 folder structure.
Create folder structure in Cockpit v2:
In v2 cockpit interface create the same asset folder and subfolders as your v1 interface. You can always look at the data in V1 Cockpit.sqlite if your not sure. It is worth adding in a dummy asset/image into the v2 files as you will need to grab some of it’s JSON attributes for later in the process.
Rename the new v2 folder IDs to match your old v1 folder IDs:
Rename all the folder id’s in Assets.sqlite folders to match the old folder id’s (“3dfe75**********18e”) in Cockpit.sqlite asset_folders. Make sure you rename both the folder name itself (the {id: …} attribute) and the parent folder attribute (the {_p:…} attribute).
Reconfiguring the asset link addresses:
Go into Cockpit.sqlite asset, export the table out of the Cockpit.sqlite asset table and import into App.sqlite database as a new table. Rename the old “assets” table so you still have a copy for reference. Rename the newly imported table as “assets”. Ensure that the new database tables have the same field heading, field heading types and primary keys:
Mending the Assets JSON:
The new assets JSON seems to have some additional information or has renamed some attributes as far as I can tell.
New asset properties that need manually adding:
“_hash”: “c4*******************465d8”, note sure what this attribute does, can anyone advise?
“colors”: null,
“height”: null,
“width”: null
“type”: “image”,
“_cby”: “3d*********18e”, created by username
“_hash”:, “_cby”: and “type” I believe are new attributes. For “_hash” and “_cby” I coped the same value for all the assets to import as the dummy asset I made in the “Create folder structure in Cockpit v2:” stage above. Depending on your asset types you may need to upload a few different dummy files to see how the attribute changes, all my assets to import were images so I was able to use “type”: “image”, throughout.
If any of the variables were already present in the v1 JSON data, the sql editor I was using automatically ignored a “null” assignment when I manually copied in the data above.
Upload updated databases:
Once both the Apps.sqlite and Assets.sqlite databases have been amended upload these back into the …/storage/data/ area. Move, rename or delete the old databases and supersede with your newly edited files. NOTE: it is not possible to overwrite app.sqlite while you are logged into Cockpit on a browser.
Further work:
Its worth noting that the above worked ok for me.
It may be possible to skip some of the steps, I haven’t had a chance to play around to understand what steps definitely are needed and what just worked coincidentally.
I am not 100% what the “_hash” attribute is used for, I am hoping using the same value for all assets doesn’t create problems further down the line. If it does I will try to update.
I hope this helps!
