Too Many Terms in Compound Select (500 Game limit)

[MussSyke]

Thanks for this great endeavor. However, I'm having an issue:

I run the script, It seems to be working because it starts listing titles, but then I get this error:

Processing table: tbl_appbrowse_0273516421
Traceback (most recent call last):
File "C:\Python34\Scripts\fix_db.py", line 75, in
cursor.execute("SELECT T.titleid FROM (%s) T WHERE T.titleid NOT IN (SELECT titleid FROM %s);" % (files_joined, tbl[0]))
sqlite3.OperationalError: too many terms in compound SELECT

I looked up this error and understand that apparently sqlite has a limit of 500 for this operation. There are apparently workarounds by doing the operation three times and having them add to the same list. I made some extremely feeble attempts to get this to work for myself, but I just do not have the experience. I suspect lines 89-90 would have to be amended for this to work. I need it to work for just over 1000 items.

I would appreciate it greatly if you are willing to help me get this working. Thank you.

[MussSyke]

I managed to get the majority of my games back by doing the following:

I made 11 different scripts by using the "replace all" command to replace CUSA with CUSA00, CUSA01, CUSA02...CUSA1. I also had to delete the parenthesis off of the end of line 36 in each of the scripts. Now all scripts only had to process less than 500 games and I could run them all in a row without logging out.

It appears that only the games on my first external hard drive (the one I filled up using App2USB before extended storage was working with HEN) have reappeared. I assume I need to change the directory that this script scrapes to get the games on extended storage to come back (but in my case, I should only need to run the original script - instead of 11 copies - to get the remainder of the games back once I change the directory.

I'm still happy for any help, because this has been a challenge, but I've only been motivated to work on it because my work buddy kmc has been giving me a programming tutorial (many thanks to kmc as well as Zer0xFF). Will post back if I get the extended storage games to show up...

[Zer0xFF]

sorry, it seems I didn't get the initial notification for your post. (or maybe i was half asleep at the time).

for the 500 limit, a quick look at the code, you can probably get around it by creating a for loop from line 70 until line 91.
the for loop would look something like this

import math 
def chunk(a, n):
    if(len(a) > n):
        chunks = math.ceil(len(a)/ (n * 1.0))
        a_chunked = []
        while chunks > 0:
            a_chunked.append(a[:n])
            a = a[n:]
            chunks -= 1
        return a_chunked
    else:
        return [a]

for files_sublist in chunk(files, 400):
     files_joined = "SELECT %s AS titleid " % ' AS titleid UNION SELECT '.join(files_sublist)
     [...]

as for the 2nd part, look at this #2

[MussSyke]

Thank you for the reply.

I had a similar experience to lnsfrht where my extended storage games reappeared as if by magic (I had been screwing around with debug settings, so maybe I just reminded the PS4 to check for those games).

Now I am in the strange situation where all my games show up, but it seems that all the games on the app2USB hard drive are corrupted (dead links, I think), and I'm guessing that's because I did not change your script to point to where the app.pkg file actually is (I thought there was a link in each folder, not in the database). Now I have all these games and only about half of them are working while the rest are dead links. Do you have any suggestions how to fix only the dead links in the database (I have some vague ideas, but am hesitant to try to write a script that could really mess my system up further).

[Zer0xFF]

sorry for taking so long to reply, (not sure if again, i didnt get notified) but I was also away at the time.

for future reference, there is no magic fix, the easiest fix would be to have a script that queries the database for pkg location, then check if the pkg exist, if it doesn,'t have the script check some other locations (where app2USB pkgs are stored), and if it find the game there, to update the database to that location.

[ArtgeistDragomir]

I'm trying to restore my apps on my external hdd I am following the instructions from modded warfare and veer tech info on youtube which lead me here but I got this error and other errors as well and I dont want to download all 100+ games again

PS C:\Users....\Downloads\FixDB_672_ext> py fix_db.py 192.168.1.21
Traceback (most recent call last):
File "C:\Users\Joshua Brownson\Downloads\FixDB_672_ext\fix_db.py", line 10, in
from sfo.sfo import SfoFile as SfoFile
ModuleNotFoundError: No module named 'sfo'

I am also getting this error

PS C:\Users\Joshua Brownson\Downloads\PS4\DB Rebuilder> py fix_db.py 192.168.1.21
Traceback (most recent call last):
File "C:\Users\Joshua Brownson\Downloads\PS4\DB Rebuilder\fix_db.py", line 50, in
ftp.connect(PS4_IP, 1337, timeout=30)
File "C:\Users\Joshua Brownson\AppData\Local\Programs\Python\Python39\lib\ftplib.py", line 158, in connect
self.sock = socket.create_connection((self.host, self.port), self.timeout,
File "C:\Users\Joshua Brownson\AppData\Local\Programs\Python\Python39\lib\socket.py", line 843, in create_connection
raise err
File "C:\Users\Joshua Brownson\AppData\Local\Programs\Python\Python39\lib\socket.py", line 831, in create_connection
sock.connect(sa)
ConnectionRefusedError: [WinError 10061] No connection could be made because the target machine actively refused it
PS C:\Users\Joshua Brownson\Downloads\PS4\DB Rebuilder>

please help me

[Pharaoh2k]

Use Itemzflow, which works better than any script:
https://github.com/LightningMods/Itemzflow