Bloated SQLite DB data size

[testpushpleaseignore]

Local DBs in the '%localappdata%/acquisition/data' sometimes are growing to extremely large sizes, and will use this issue ticket to see if I can find out why.

@aiolos01 - could you open your database in the DB Browser for SQLite program, and see how many records are in the items table? If it's more than the sum of stash tabs and characters that you have, then there might be some old data sticking around.

[testpushpleaseignore]

@aiolos01 Okay so I definitely found the issue, I can try to add some cleanup code and NULL checking the code to prevent this from bloating up in my next release.

[testpushpleaseignore]

released v0.9.4 to fix this issue (partially), will figure out cause of the NULL tab location info in the future.

[aiolos01]

Was this supposed to stop the db from growing even larger or reduce its size? Because in my case it's still 2,5GB after upgrading. It actually grew from 2,52 to 2,54.

[testpushpleaseignore]

Strange, I wonder if something else is growing in your db file. Are you able to tell if a certain table is much larger than the others?

[aiolos01]

Well the items table has more than 21000 rows. If I understand it correctly each one is an entire tab so it's no wonder the db is so large. The last few rows are my characters and those exist only once in the db. It seems every tab is in there multiple times.

I'm pretty sure this has a lot to do with issue #16 since the items of my chars are loaded at app startup but the contents of all tabs need to be loaded from the site. Seems that somehow acquisition ignores everything in the db except the character rows and re;loads everything from the site so every time I run it (or even every time it refreshes) all my tabs are re-added to the db.

As a test I searched for an item that I only have in one tab and found 140+ copies of it. On the other hand I searched for an item that is on a char and there was only 1 copy of it. (Edit: I'm talking about searching the db directly. Acquisition shows each item only once).

[aiolos01]

I found the last record of my first tab and deleted everything above it. Seems to have worked well. The db is now 60MB and all the prices are still there, It'd be nice if you could fix it though because it grows very quickly.

[testpushpleaseignore]

Would you happen to know if the records that you just deleted were NULL in the "loc" column? V.0.9.4 should now auto delete those NULL records, but wasn't sure what you saw.

[aiolos01]

Don't worry new records are being created all the time so I can check. The database already grew from 50MB to 150 in a couple of hours. It definitely grows every time it updates the items from the site. Some of the records have binary values in loc and others have text. At least according to DB browser. No null values though. I don't know how db browser decides what to interpret as text or binary so it could be that there is just one kind of loc values that is interpreted differently for some reason. Where are the prices saved at? I certainly don't want to mess those up.

[testpushpleaseignore]

As far as the prices, they are stored in the 'data' table, in the 'buyouts' record. The long hash at the beginning of each buyout object in 'buyouts' links to an item in the 'items' table. So as long as you don't touch that 'buyouts' record, then you shouldn't lose your prices.

"0fa6f3e01d7e8f980c10f93dca40f46f": {
  "value": 30.0,
  "last_update": 1656644402,
  "type": "price",
  "currency": "chaos",
  "source": "manual",
  "inherited": false
}

Otherwise the 'items' table will have two types of data for the loc column: a 64-character tab hash id (when it's items in a stash tab), and a character name (which are items that your character are wearing, holding, or have socketed in the passive tree).

Otherwise I think that I may have just recreated the bug that you are probably seeing. I can do some testing to see how these weird loc names are showing up.

[aiolos01]

Yeah I found them in the end by looking at the database. It's good to know so I can re-insert them if something goes wrong.

As I said the loc column has some values interpreted as text and others as binary. I think this is just how sqlite browser sees them because of the first 2 chars. Otherwise they both look the same, as you said 64 char strings.

[testpushpleaseignore]

Yeah the ones being interpreted as binary were an issue to how something was being destroyed in the program before the query would actually run. So therefore it would set the loc value to random garbage in memory, instead of the real name. Should have that fix in place to make sure the loc name doesn't get destroyed too soon, and am almost done with adding some cleanup code to remove those "BLOB" records, since they shouldn't exist.

[testpushpleaseignore]

Should hopefully have this fixed, as well as including code to cleanup the database from here on out. Could you see if this fixes your issue?

https://github.com/testpushpleaseignore/acquisition/releases/tag/v0.9.5

[aiolos01]

I can confirm this fixes the issue. The loc column is now all text and the tabs appear only once after several updates. The entire database is now 67MB. The only issue is that although the records were deleted by acquisition I had to manually compact the database to gain back the space they occupied.

Out of curiosity: in the same folder I have several database files. I assume those are from past leagues, right?

Thanks a lot for fixing this.

[testpushpleaseignore]

Oh interesting, yeah now that you mention it, it looks like they never enabled the vacuum for SQLite, but I can update it here shortly to Vacuum the db file at each launch of the program. Thanks for thinking of that! I can include that code in a future release.

Otherwise yeah those other files are databases from previous leagues. But yeah glad to help!