Querying Databases

Posted in

#51 by rampaa
2020-06-16 at 12:48
< report >I think the following query can be used for automated extraction of known "Non-standard" resolutions from the notes: link
#52 by yorhel
2020-06-16 at 13:37
< report >The challenge with automated extraction is more in removing the old information from the notes than in matching the data, and if that's going to need manual intervention anyway, it's better to do that in a single edit.
#53 by rampaa
2020-06-16 at 13:46
< report >How about link then? "Resolution: X" format should be pretty easy to remove from the note section, and it returns 505 results, which is more than half of the all instances.Last modified on 2020-06-16 at 13:47
#54 by yorhel
2020-06-16 at 13:51
< report >Try a regexp_replace() on the notes to see if you can get the desired effect.
#55 by rampaa
2020-06-16 at 16:12
< report >link
EDIT: Updated the query.
EDIT2: Updated the query for the last time. And wrote an additional query to cover "X resolution" format: link

I think the end result is fairly decent but what do you think?Last modified on 2020-06-16 at 21:02
#56 by yorhel
2020-06-17 at 05:40
< report >Better than some of my conversions. :)

Thanks, batch edits done.
#57 by rampaa
2020-06-24 at 18:11
< report >I can't find where, but I vividly remember Yorhel saying something along the lines of "Having no screenshot is better than having screenshots with wrong resolutions". In the light of that motto, here: link
#58 by briarmae
2020-07-01 at 20:40
< report >I'm trying to use the Advanced Search query but every time I try to un-comment-out the search by tag ID it breaks, giving me syntax errors. All I'm doing is deleting the /* before the first INTERSECT and changing the tag ID. Obviously- this is wrong. But... I can't figure out what's right. Any help would be appreciated.
#59 by rampaa
2020-07-02 at 10:57
< report >#58 The line with "get_child_tags" was missing a ")". It's fixed now.Last modified on 2020-07-02 at 10:58
#60 by briarmae
2020-07-02 at 15:24
< report >Thank's so much, that was really fast!
#61 by ninigi
2020-07-04 at 14:21
< report >Requesting a query for sorting all VNs without any tag and VNs with tag(s) that was/were rendered invalid.
#62 by rampaa
2020-07-04 at 15:19
< report >#61: link
#63 by ninigi
2020-07-04 at 16:07
< report >Thanks.
#64 by jazz957
2020-07-23 at 17:22
< report >I'd like to request a query for releases with no developer or publisher. I'd also like something that shows that only for DVD-PG/UMD-PG/BD-PG games.
#65 by foiegras
2020-07-23 at 18:30
< report >#64 link
Is this what you wanted?
You can comment out the media part to remove the filtering.
#66 by jazz957
2020-07-23 at 19:01
< report >Thanks. But it probably should filter by platform rather than media because otherwise Windows releases on DVD get picked up. Not sure what to do about separating PSP from UMD-PG, maybe only showing 18+ releases would be best. There are a few non-18+ DVD-PG/UMD-PG/BD-PG releases but not too many.

Edit: I managed to figure out how to do the rest.Last modified on 2020-07-23 at 19:31
#67 by elias67
2020-07-26 at 16:56
< report >I've always wanted to be able to see the English-translated VNs for a given seiyuu. Not sure if that functionality already exists, but my best effort for that is here: link
I'm not great at sql, so it's not perfect. Ideally I'd also be able to give the English VN title (e.g. Wonderful Everyday) but I'm not sure how. Using the release title breaks it a little.
#68 by yorhel
2020-08-21 at 11:51
< report >I'm currently making an attempt to upgrade & reconfigure SQLPad, expect some weirdness and downtime.

EDIT: Done. Appears to work alright.Last modified on 2020-08-21 at 12:43
#69 by rampaa
2020-08-21 at 16:47
< report >I am getting a "password authentication failed for user "vndb"" error instead of the DB schema. I also cannot successfully run any query, it doesn't even return a timeout error even though I've waited for 5+ minutes.Last modified on 2020-08-21 at 16:54
#70 by yorhel
2020-08-21 at 16:57
< report >Fixed, for some reason the DB password was reset when I was playing around with the settings earlier.
#71 by rampaa
2020-08-22 at 07:54
< report >I can't "save" my old queries. When I try to save them I am getting an "Internal Server Error". This doesn't happen with queries I created after the update.
#72 by yorhel
2020-08-24 at 09:52
< report >Seems to be that old queries cause a validation error: "chart.chartType must be a string". Maybe enabling/disabling a chart will fix that?
#73 by rampaa
2020-08-24 at 12:32
< report >Yeah, adding an arbitrary chart and removing it afterwards fixes the issue. Thanks!
#74 by wyldstrykr
2021-01-08 at 07:28
< report >question: it is possible to to calculate total length of vn in a query??
for eXample. fureraba length is 30-50 hours and its fan disc is 2-10 hours making 32-60 hours the total length combined?

EDIT: i manage to find the length (1-5) but cant find the upper length and i sucks at SQL that only 63/150 VN is only displayed with LIMIT 200. forgot that the database updates every day at a certain timeLast modified on 2021-01-08 at 08:41
#75 by wyldstrykr
2021-01-09 at 11:45
< report >also question: how to query vn you labeled finished?? it appears that if you voted, it is not counted as finished and you can vote without labeling it finished. or im blind that i didnt see the columns i need?

EDIT: it appears that a vn has multiple label and the entry is duplicated with a different label. so now it works.

let me try this: how do you know how long you read (estimated)
based on vndb length ranging from less than 2 hours to more than 50 hours.
my query simply add all the minimum hours and add all the maXimum hours then you get an estimate. the gap maybe be VERY HIGH but it should give you the number you needed.

1. it will only work on VN you labeled it as "finished"
2. stalled vn has varying time spent so i dont count them. same with dropped and probably blacklisted.
3. of couse it wont work on VN that dont have a length?? EX link (it should be 10-30 but idk)
4a. VN with very short length <2 hours. i just treat it with a length of .5 hours to 2 hours
4b. VN with very high length >50 hours. i just treat it with a length of 50-70 hours
4c. You can change those value in the first when case and the last one marked by the comments.
5. the resulting query will give you hours and hours and a total of vn read. you can divide it by 24 in the Select before the "as" to make it day.
6. i dont know coding etiquette and i sucks at coding.Last modified on 2021-01-11 at 07:45