Open aaaaaa2493 opened 1 year ago
noahm
commented
1 year ago Hi, chiming in to say that I'll be around to help if I can as well. The PIU data from here I added ddr.tools a while ago has been a hit! It's already been used at tournaments like CEO Storm this past month!
aaaaaa2493
commented
1 year ago @noahm @AnyhowStep
Hi. I updated Pumpout SQLite database myself, both Phoenix v1.00.1 and patch Phoenix v1.01.0. So, I wrote some Python code that generates SQL-only updates, and here are my results:
Phoenix v1.00.1.sql with 80K lines (most of these are all the reratings)Phoenix v1.01.0.sql which is pretty light on SQL queries (3K lines only)The first update is meant to be executed on the latest XX v2.08 Pumpout SQLIte database. The second - after the first. If you don't want to execute all the requests, I already generated database version - pumpout.zip
I checked all songs and charts with the nevsister's full song list video and didn't spot any mistakes with my database version. However, there's a small chance that I didn't spot something.
Also, I don't know song identifiers for new songs, so I generated them in the format id=17XX_AABB where AA - patch index, BB - song index within a patch. Example: id=17XX_0106.
Also, I generated all cards in the following format: /img/card/{song_title}.png. Here are the cards1.zip
and cards2.zip (splitted because of github's 25MB size limit on attachments) with the same format, so you can just place them in the /img/card/ folder, but you may want to resize them to a smaller images first).
If there are some mistakes in the update, I can fix them. Also, I can provide my Python script which makes updating database with new patches easy.
noahm
commented
1 year ago Wow that looks like quite a bit of work! Thanks for sharing, too! I'll try importing this into ddr.tools soon
aaaaaa2493
commented
1 year ago For what exactly was changed here's the summary:
noahm
commented
1 year ago I just now got around to trying to use your newly updated database and it doesn't work out of the box. You didn't update the _derived_versionAncestor table which is part of how I would for which songs are in the latest version of PIU XX.
The core query of my import script uses that ancestry table to look up a larger list of versions that lets me query all songs present in a particular version.
In order to update the version ancestor table for this new mix, I think we can just reuse the ancestor list from XX? And I think the following queries will get the job done if executed right after each new Phoenix version is added the database? (large grain of salt: I'm not all that great at SQL and I find the complex schema of the pumpout database somewhat hard to reason about)
-- Copy previous list of ancestors
INSERT INTO _derived_versionAncestor (versionId, ancestorId, sortOrder, ancestorSortOrder)
SELECT
(SELECT MAX(versionId) from version),
ancestorId,
(SELECT MAX(sortOrder) from version),
ancestorSortOrder
FROM _derived_versionAncestor
WHERE versionId = (SELECT MAX(versionId) - 1 from version);
-- Add previous latest version as ancestor too
INSERT INTO _derived_versionAncestor (versionId, ancestorId, sortOrder, ancestorSortOrder)
SELECT
(SELECT MAX(versionId) from version),
(SELECT MAX(versionId) - 1 from version),
(SELECT MAX(sortOrder) from version),
(SELECT MAX(sortOrder) - 10 from version);
-- Add latest version as own ancestor
INSERT INTO _derived_versionAncestor (versionId, ancestorId, sortOrder, ancestorSortOrder)
SELECT
(SELECT MAX(versionId) from version),
(SELECT MAX(versionId) from version),
(SELECT MAX(sortOrder) from version),
(SELECT MAX(sortOrder) from version);EDIT: added one more sql query, each version must also be its own ancestor
noahm
commented
1 year ago Besides that previously mentioned issue of the ancestor table, all the other updates worked wonderfully and I have the phoenix data imported into my app as well! https://ddr.tools/#game-pump-phoenix
aaaaaa2493
commented
1 year ago @noahm I didn't even notice that this table exists... Thank you! In my code, I checked the ancestors by creating parentVersion list and parentMix list using info just from tables version and mix. But I see that this table is useful in a way that you don't need to search deep to find if the 1st mix is parent to XX for example.
So, since every version is its own ancestor then I guess you don't need the second SQL query because the latest version would be added in the first query already.
So, I generated two SQL queries in the following format
INSERT INTO _derived_versionAncestor (versionId, ancestorId, sortOrder, ancestorSortOrder)
SELECT
(SELECT versionId FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.00.1'),
d.ancestorId,
(SELECT sortOrder FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.00.1'),
d.ancestorSortOrder
FROM _derived_versionAncestor d
JOIN version v on d.versionId = v.versionId
JOIN mix m on v.mixId = m.mixId
WHERE m.internalTitle = 'XX' AND v.internalTitle = 'v2.08.0';
INSERT INTO _derived_versionAncestor (versionId, ancestorId, sortOrder, ancestorSortOrder)
SELECT
(SELECT versionId FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.00.1') as versionId,
(SELECT versionId FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.00.1') as ancestorId,
(SELECT sortOrder FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.00.1') as sortOrder,
(SELECT sortOrder FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.00.1') as ancestorSortOrder;And for the first patch
INSERT INTO _derived_versionAncestor (versionId, ancestorId, sortOrder, ancestorSortOrder)
SELECT
(SELECT versionId FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.01.0'),
d.ancestorId,
(SELECT sortOrder FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.01.0'),
d.ancestorSortOrder
FROM _derived_versionAncestor d
JOIN version v on d.versionId = v.versionId
JOIN mix m on v.mixId = m.mixId
WHERE m.internalTitle = 'Phoenix' AND v.internalTitle = 'v1.00.1';
INSERT INTO _derived_versionAncestor (versionId, ancestorId, sortOrder, ancestorSortOrder)
SELECT
(SELECT versionId FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.01.0') as versionId,
(SELECT versionId FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.01.0') as ancestorId,
(SELECT sortOrder FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.01.0') as sortOrder,
(SELECT sortOrder FROM version
WHERE mixId = (SELECT mixId FROM mix WHERE internalTitle = 'Phoenix')
AND internalTitle = 'v1.01.0') as ancestorSortOrder;Assets: Phoenix v1.00.1.zip Phoenix v1.01.0.zip pumpout.zip
Also, I updated a bit cards paths for new Phoenix songs to be in the following: /img/card/Phoenix_{song_title}.png so they will show in one place in the img folder and not all-over the img folder.
Here's images with updated names (also, already resized): img.zip
Please check if everything works with this database version, to ensure that I didn't mess something up. And then I would be ready to generate the next patch when it will be released.
noahm
commented
1 year ago Also, I can provide my Python script which makes updating database with new patches easy.
@aaaaaa2493 This would come in handy now that we have v1.02.0 as well!
aaaaaa2493
commented
1 year ago @noahm
Hi, sorry for the update being late, here's v1.02.0, that should be applied to v1.01.0 database. I will try to do the subsequent updates as fast as possible
Phoenix v1.02.0.zip pumpout.zip img.zip
For the script though, I will publish it later, in the new repo, but I will still do the updates
noahm
commented
1 year ago Thanks for sticking with it! I managed to manually update my own json data this time, but I know some members of the pump community still like using pumpout for all its deep options. One you publish your scripts I will do my best to help keep up with the updates too.
aaaaaa2493
commented
10 months ago @noahm Hi, there's new update. Also, I found a bug in the script which led to a Korean titles (from songTitle table) not being added to the songTitleVersion table. I guess this is a minor bug but still, I regenerated SQLs for every Phoenix version.
Phoenix v1.00.1.zip Phoenix v1.01.0.zip Phoenix v1.02.0.zip Phoenix v1.03.0.zip
The resulting database with 1.03 patch included:
pumpout.zip
Images for the 1.03 patch:
1.03.imgs.zip
aaaaaa2493
commented
10 months ago @noahm I've added 1.04 update. I've implemented patchnote parser (from piugame.com) which is one step closer to releasing the script, now it takes 5 minutes to generate SQLs instead of 30. Hopefully, I'll release the script soon, it's kinda messy and unintuitive for now (well, I thought created it just for myself...)
noahm
commented
10 months ago Thanks so much for continuing to keep this thread up to date! I'm keeping my app updated through your work as well!
aaaaaa2493
commented
9 months ago @noahm Hi, I've added 1.05 patch, in the my repo. There also an instruction, but I will continue to do this on my own, in this thread also https://github.com/aaaaaa2493/pumpout-db-updates
Hi! Do you need help with entering Phoenix data? I'm running https://piurandom.com/ using your database and I'm interested in updating DB to the new mix as fast as possible
So far, there's this table with all the reratings. https://docs.google.com/spreadsheets/d/1y4cZ_CTQJbj7KvuLq3gOB_MiHQUWFyhnUfKy3g18IN8/htmlview#
It's not final since it's from dev build but I want to create a script that generates SQL queries to update all the tables. This table is just for training and then I'll generate a script based on final data. I think entering data into excel sheet is far easier than manually creating SQL queries :)