Querying Databases

Posted in

#26 by yorhel
2019-09-19 at 19:19
As per d18#4
Those are great! Added to the page. I'd be happy to add your advanced querying tools as well, but they'll need some context and usage documentation. I think it'll work if you add a few more SQL comments.

my suggestion regarding finding a single original language
Awesome! We'll use that in the conversion.

Some characters have their names missing a quote ('), e.g., Shinichi (correct: Shin'ichi)
I'm finding about as many 'ichi's as nichi's... is that really a thing?
#27 by beliar
2019-09-19 at 19:23
Both Shinichi and Shin'ichi are correct romanizations for different kanji. There can be no automatic correction, as you have to manually check each character if it should be read as Shinichi or Shin'ichi.
#28 by roadi
2019-09-19 at 19:55
I'd bet a piece of my (neighbors) liver that over 95% of those "Shinichi"s, etc., are missing the quote.
I could go through them at some point, but it would be faster to correct the wrongly-added quotes than the missing ones.

edit:
Here's a more precise query: linkLast modified on 2019-09-19 at 20:04
#29 by rampaa
2019-09-19 at 23:19
I'd be happy to add your advanced querying tools as well, but they'll need some context and usage documentation. I think it'll work if you add a few more SQL comments.


Fully translated VNs of a given developer: link
VN search example: link

I tried to add lots of comments, hopefully it will be enough.
#30 by yorhel
2019-09-20 at 05:31
Yup, added.
#31 by rampaa
2019-09-20 at 16:52
VNs where original title is in Latin alphabet: link
Releases where original title is in Latin alphabet: link
VNs and releases that have half-width kana in their original title: linkLast modified on 2019-09-20 at 16:53
#32 by beliar
2019-09-20 at 17:04
VNs where original title is in Latin alphabet: link

Can this query be made a bit more specific? We do use Latin names in the original title field if the original name has improper capitalization. So, maybe it would be possible to remove titles that are spelled the same in the original title field, but in all uppercase or lowercase.

For example, it's okay to use the original field for VNs like these Abundant or All Out.Last modified on 2019-09-20 at 17:04
#33 by rampaa
2019-09-20 at 17:09
We do use Latin names in the original title field if the original name has improper capitalization.
I did not know that. I thought if there's any arbitrary capitalization we simply ditch them. See Chaos;Head and Air.
#34 by beliar
2019-09-20 at 17:19
It's applied a bit inconsistently. I remember there being back and forth regarding that, and if I'm not mistaken Chaos;Head was specifically mentioned back than, but I don't remember the outcome. And only recently we became more consistent with leaving non-standard capitalization in the orig title field. Frankly, there isn't even a hard rule, as it was not added to the faq either, but lately that's how we rolled.
#35 by rampaa
2019-09-20 at 17:24
Releases where original title is in Latin alphabet (Modified): link
VNs where original title is in Latin alphabet (Modified): link

Edit: If you don't care about spacing either, I can take that into account to further specify the query.Last modified on 2019-09-20 at 17:42
#36 by skorpiondeath
2019-09-23 at 21:46
Just wanna share a query to extract all tags with only negative votes: link. Those can probably be deleted at some point.
#37 by savagetiger
2019-11-03 at 07:02
If someone has time could they write a query for otome games without character entries and one for male voice actor entries without wikidata links?
I just can't figure out this SQL stuff.
#38 by skorpiondeath
2019-11-03 at 13:13
Here you are m8 link.
I added a third query for unknown voice actor entries, without wikidata links, attached to at least one male character.

Just replace ids. Have fun!!Last modified on 2019-11-03 at 13:17

Reply

You must be logged in to reply to this thread.