Review and feedback from converting from qb_tk_housing 1.0.5:
Negatives:
- House ID’s are not retained in their original assignment. I had to drop the primary key constraint and manually update the database to re-align to old house numbers.
- No default garage interior on conversion, had to manually update database to give all the old houses a garage interior
- Stash/Storage ID system is changed drastically resulting in a manual scripting process to convert and retain stashes, need to have highly advanced SQL knowledge to convert the stashes, I am fortunate to do data science as part of my day job.
Positives:
- Works mostly very well out of the box
- The new interfaces and systems are great
- Gives more for realestate agents to do in their spare time when waiting for people to find and wanting to buy a house
- Garage sql is exposed so was easy to ensure consistency between our garage script and the housing(jg-advancedgarages)
- With some fancy footwork can also support own target systems
Great work, thank you, however please look at the impact of change from index to id on furniture and what this does to stash conversion.
Here is my script for converting the stashes after I have aligned the house ID’s(Using PS-Inventory):
CREATE OR REPLACE TABLE storage_migration AS
SELECT
old_house.id AS house_id,
COALESCE(JSON_EXTRACT(old_furniture.furniture, '$.index'), 'unknown') AS old_index,
COALESCE(JSON_EXTRACT(new_furniture.furniture_house, '$.id'), 'unknown') AS new_id,
CONCAT('qb_tk_housing_storage_house_', old_house.id, '_',
COALESCE(JSON_EXTRACT(old_furniture.furniture, '$.index'), 'unknown')) AS old_stash_name,
CONCAT('tk_housing_storage_', old_house.id, '_house_',
COALESCE(JSON_EXTRACT(new_furniture.furniture_house, '$.id'), 'unknown')) AS new_stash_name
FROM
owned_houses old_house
JOIN
tk_housing_properties new_house ON old_house.id = new_house.id
JOIN
JSON_TABLE(
old_house.furniture,
'$[*]' COLUMNS (
old_model VARCHAR(100) PATH '$.model',
old_x DOUBLE PATH '$.coords.x',
old_y DOUBLE PATH '$.coords.y',
old_z DOUBLE PATH '$.coords.z',
old_index VARCHAR(20) PATH '$.index',
furniture JSON PATH '$'
)
) old_furniture
JOIN
JSON_TABLE(
new_house.furniture_house,
'$[*]' COLUMNS (
new_model VARCHAR(100) PATH '$.model',
new_x DOUBLE PATH '$.coords.x',
new_y DOUBLE PATH '$.coords.y',
new_z DOUBLE PATH '$.coords.z',
new_id CHAR PATH '$.id',
furniture_house JSON PATH '$'
)
) new_furniture
ON
ABS(old_furniture.old_x - new_furniture.new_x) < 0.2
AND ABS(old_furniture.old_y - new_furniture.new_y) < 0.2
JOIN stashitems c on c.stash = CONCAT('qb_tk_housing_storage_house_', old_house.id, '_', COALESCE(JSON_EXTRACT(old_furniture.furniture, '$.index'), 'unknown'))
WHERE
old_house.furniture IS NOT NULL
AND new_house.furniture_house IS NOT NULL
and JSON_EXTRACT(old_furniture.furniture, '$.index') is not null;
CREATE OR REPLACE TABLE storage_backup as
select s.id, s.stash, s.items, REPLACE(a.new_stash_name,'"','') as new_name from stashitems s
join storage_migration a on(s.stash = a.old_stash_name);
UPDATE stashitems a
JOIN storage_backup b on(a.id = b.id)
SET a.stash = b.new_name
WHERE a.stash = b.stash;
