It seems that you're using an outdated browser. Some things may not work as they should (or don't work at all).
We suggest you upgrade newer and better browser like: Chrome, Firefox, Internet Explorer or Opera

×
avatar
phaolo: Didn't you implement a filter feature for the (extended) changelog in the end?
I can't even seem to use wget anymore to grab the changed bonuses info now :'(
The old site is shut down now, because I received no additional requests I couldn't satisfy. Filtering never got implemented, but you can do your own filtering as I described in this response https://www.gog.com/forum/general/gog_database_a_website_that_collects_data_on_gog_games/post559
avatar
Yepoleb: The old site is shut down now, because I received no additional requests I couldn't satisfy. Filtering never got implemented, but you can do your own filtering as I described in this response https://www.gog.com/forum/general/gog_database_a_website_that_collects_data_on_gog_games/post559
Oh, I must have missed that.
Too bad for the filtering.

But would I need to install all the SQL bloat to parse the file index.sqlite3 ? :\
EDIT: ah nevermind, found the site about SQLite.
Post edited July 06, 2021 by phaolo
avatar
phaolo: But would I need to install all the SQL bloat to parse the file index.sqlite3 ? :\
EDIT: ah nevermind, found the site about SQLite.
Just add this to your toolbox ;) : https://sqlitebrowser.org/
avatar
WinterSnowfall: Just add this to your toolbox ;) : https://sqlitebrowser.org/
Thank you, that's quite useful.
Meanwhile I managed to add parameters and read commands from a file.

But I forgot the PITA of the SQL date formats..
How do I select a date >= 2021-01-01 with a timestamp formatted like "2017-10-07T19:24:10.409053+00:00" ?

--

For now I tried these and they didn't work:
- cast.. as datetime
- strptime (missing?)

--

Ok, I temporarly made it with:
strftime('%Y-%m-%d %H:%M:%S',timestamp,'unixepoch')

..even if it would be better to convert a single date to unixepoch, than the opposite for the full column.

--

Ah maybe it's:
strftime('%s','2021-01-01')
Post edited July 06, 2021 by phaolo
avatar
phaolo: But I forgot the PITA of the SQL date formats..
How do I select a date >= 2021-01-01 with a timestamp formatted like "2017-10-07T19:24:10.409053+00:00" ?
Oh no, I forgot I actually did something nonstandard there because SQLite is so horrible in that regard. So I assume you've read the page about datatypes in SQLite, in particular section 2.2 about date and time datatypes. I was initially using the TEXT type to store dates, because INTEGER didn't have the resolution required - changelog entries get added way faster than single second increments and they would be out of order this way. Unfortunately storing datetime as a string makes it very slow to query, which made the TEXT type also a bad choice, because the whole point of having an index table is it being very fast. The only option left was therefore REAL, the quirkiest one of the three because it is defined as the number of days since November 24, 4714 B.C.. I was really not feeling that and decided to throw away datetime handling in SQLite altogether and just store floating point Unix timestamps in the database with no special handling.

TL;DR:
Dates in SQLite are poop. Convert your timestamp to Unix time with something like this (Python example):
datetime.datetime.fromisoformat("2017-10-07T19:24:10.409053+00:00").timestamp()
Now use that value to query. Don't touch any SQL native date function, make sure to do everything in Unix time while in database land. Sorry for putting you through this :(.
Post edited July 06, 2021 by Yepoleb
avatar
Yepoleb: Convert your timestamp to Unix time with something like this:
datetime.datetime.fromisoformat("2017-10-07T19:24:10.409053+00:00").timestamp()
Now use that value to query. Don't touch any SQL native date function, make sure to do everything in Unix time while in database land. Sorry for putting you through this :(.
Oh, no problem.
Btw, I ended up using this:

SELECT strftime('%Y-%m-%d %H:%M:%S',timestamp,'unixepoch') "Date", product_id, product_title, dl_type, bonus_type, action, serialized_record
FROM changelog
WHERE "dl_type" = 'bonus' AND timestamp >= strftime('%s','2021-01-01 00:00:00');

It seems to work correctly, should I really use that huge date conversion? O_o
Now I wonder if I could extract also the data I need from "serialized_record"..
Post edited July 06, 2021 by phaolo
avatar
phaolo: It seems to work correctly, should I really use that huge date conversion? O_o
Now I wonder if I could extract also the data I need from "serialized_record"..
I can't say if this will work correctly, too much implicit conversion going on here. serialized_record contains additional details that I don't need to select for, but I do show, like the old and new value. I can't tell you if it's necessary to load it.
avatar
Yepoleb: I can't say if this will work correctly, too much implicit conversion going on here. serialized_record contains additional details that I don't need to select for, but I do show, like the old and new value. I can't tell you if it's necessary to load it.
Serialized_record seems to contain id,name,size,count of the affected bonus content, though.
They don't have their own columns.

EDIT: ok, it seems I can use substr() and instr() to retrieve them.
Not ideal, but better than nothing.

--

Ok, for now this seems to be working (despite Gog messing up the view):

SELECT product_id, product_title,
substr(serialized_record, instr(serialized_record,'"id": "')+7, instr( substr(serialized_record,instr(serialized_record,'"id": "')+7,length(serialized_record)) ,'"')-1) "Bonus_id",
strftime('%Y-%m-%d %H:%M:%S',timestamp,'unixepoch') "Date", action,
substr(serialized_record, instr(serialized_record,'"name": "')+9, instr( substr(serialized_record,instr(serialized_record,'"name": "')+9,length(serialized_record)) ,'"')-1) "Bonus_name",
bonus_type,
substr(serialized_record, instr(serialized_record,'"count": ')+9, instr( substr(serialized_record,instr(serialized_record,'"count": ')+9,length(serialized_record)) ,',')-1) "Bonus_count",
substr(serialized_record, instr(serialized_record,'"total_size": ')+14, instr( substr(serialized_record,instr(serialized_record,'"total_size": ')+14,length(serialized_record)) ,'},')-1) "Bonus_size"
FROM changelog
WHERE "dl_type" = 'bonus' AND timestamp >= strftime('%s','2021-01-12')
ORDER BY product_title asc, Bonus_id asc, Date asc;
Post edited July 07, 2021 by phaolo
avatar
phaolo: Ok, for now this seems to be working (despite Gog messing up the view):
This is worse than anything GOG has ever made, but I congratulate you on getting it working this way :D
avatar
phaolo: Ok, for now this seems to be working (despite Gog messing up the view):
avatar
Yepoleb: This is worse than anything GOG has ever made, but I congratulate you on getting it working this way :D
Wait, do you mean the messed up text or my query (T_T)
Without proper columns, my only chance was performing string operations. SQLite has very limited functions.
Btw it's just to export the list to a file once in a while, when I feel like updating my bonuses.
Post edited July 07, 2021 by phaolo
avatar
phaolo: Without proper columns, my only chance was performing string operations. SQLite has very limited functions.
I never imagined anyone would try to process this data with pure SQL and assumed you knew scripting because you mentioned using a script to download webpages. So the resulting query is quite ugly in my opinion. I wrote a script to do what I think you intended to do, which I guess I should have done right from the beginning.
https://gist.github.com/Yepoleb/c357a5a4e9ef7f3f8fdef23432b6994a
avatar
phaolo: SQLite has very limited functions.
For a good reason :P. You're only supposed to fetch the data and do your processing in some other language.
avatar
phaolo: Without proper columns, my only chance was performing string operations. SQLite has very limited functions.
avatar
Yepoleb: I never imagined anyone would try to process this data with pure SQL and assumed you knew scripting because you mentioned using a script to download webpages. So the resulting query is quite ugly in my opinion. I wrote a script to do what I think you intended to do, which I guess I should have done right from the beginning.
https://gist.github.com/Yepoleb/c357a5a4e9ef7f3f8fdef23432b6994a
I'm not a dev and I just used a batch script, before having to switch to SQL.
Why use 2 methods when one works already on its own?
Not sure what "ugly" matters exactly in this context..
Also I don't know much about python.

Thank you, but at this point I already made the effort ¯\_(O_o)_/¯

--

avatar
phaolo: SQLite has very limited functions.
avatar
WinterSnowfall: For a good reason :P. You're only supposed to fetch the data and do your processing in some other language.
I see, but I didn't even expect having to process data from a db O_o
Post edited July 07, 2021 by phaolo
Yepoleb,
Can you, or anyone else, explain what the labels on gogdb.org/changelog mean?
I can take a guess at some of them, "Build' is probably a galaxy update of the game. "Download" is probably the offline installer has been updated. But what are "Product" and "Property"?
An explanation or a URL of an explanation would be appreciated. Adding an "About" page with a legend could help, too.
avatar
drrhodes: But what are "Product" and "Property"?
"Product" is product added or removed. "Property" refers to the game properties like title and supported systems.