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
#39 by kivandopulus
2020-01-05 at 03:49
I wonder if it's possible to put in condition of having description or description = "-", so far could not figure out.
#40 by skorpiondeath
2020-01-05 at 16:57
Hi kivandopulus care to explain what are you trying to do? I don't understand what you are trying to find... do you want to find VN with "-" as the only character and descriptions with at least one character?

Ok tell me if I'm wrong but maybe I understood what your problem is... the fact that desc column is also an ordering keyword, you can see that because it's blue in the editor.
You got two options
1) Fully qualify column name with table, now it can't be mistaken for a ordering keyword -> tablename.desc
2) Double quote fields with keyword names -> "desc"

Example: linkLast modified on 2020-01-05 at 17:19
#41 by kivandopulus
2020-01-06 at 20:46
Thanks a lot, so vn.desc it is. Now I know that there are 6054 vns without proper description. Sometimes "-" is put for description, had to include them as well, 159 such works atm.Last modified on 2020-01-06 at 20:48
#42 by rampaa
2020-01-06 at 22:56
Sometimes "." is used as well. So instead of adding all one characters that can be used to indicate no description, checking the description length might be a better solution. Here's what I've written before for this: linkLast modified on 2020-01-06 at 23:08
#43 by skorpiondeath
2020-01-06 at 23:09
Hi rampaa you should take into account a possible whitespace in the description, maybe there are 0 cases rightnow though. Otherwise length of trimmed desc is enough since trim function takes into account possibile whitespace, tabs, newlines and so on.Last modified on 2020-01-06 at 23:10
#44 by rampaa
2020-01-06 at 23:19
The query does take a single whitespace into account.
v.desc ~* '^.{0,1}$'
accepts every description that has the length of 0 or 1, including a single white space. But I guess it would fail if there were multiple whitescapes and whatnot. So
LENGTH(TRIM(vn.desc)) <= 1
might be the better approach here.
#45 by skorpiondeath
2020-01-06 at 23:32
Or you could work with regexp and \s.
#46 by captainchrno
2020-02-13 at 01:32
I didn't know there is an online tool to query the database dump directly lol, I ended up creating a Makefile to automate dumping the data into local dockerized postgres: github.com/Permagate/govin

This is the query that I made to create 100 most popular VN with dual releases in english and japanese and format it into reddit table that I posted here.

What's a good way to resync schema & data from the dump every day if I want to maintain a live database? One stupid way that came to my mind is to just dump into new database, switch the active database into the newly dumped database, then destroy the old one.Last modified on 2020-02-13 at 01:37
#47 by yorhel
2020-02-13 at 07:26
What's a good way to resync schema & data from the dump every day if I want to maintain a live database?
The stupid way is often the best way. :) Here's what I do with the query database:

psql -wqU vndbimport vndb\
-c 'CREATE SCHEMA import'\
-c 'SET search_path TO import'\
-f import.sql\
-f ../extras.sql\
-c 'GRANT USAGE ON SCHEMA import TO vndb'\
-c 'GRANT SELECT ON ALL TABLES IN SCHEMA import TO vndb'\
-c 'ALTER SCHEMA vndb RENAME TO old'\
-c 'ALTER SCHEMA import RENAME TO vndb'\
-c 'DROP SCHEMA old CASCADE'
extras.sql is optional and you can get rid of the GRANTs if you don't need user separation.
#48 by captainchrno
2020-02-13 at 15:11
Right, I can rotate the schema instead, thanks for the tips! extras.sql also looks very useful, I was wondering how the popularity is actually generated and now I know.Last modified on 2020-02-13 at 15:13
#49 by rampaa
2020-05-22 at 21:55
I'm getting a "Server responded not ok" error, regardless of which query I try to run.
#50 by yorhel
2020-05-23 at 06:56
Aaaaaaargh fuck this shit. Looks like nodejs 12 -> 14 update broke things and a npm update or rebuild isn't fixing it. I'm also several months behind on SQLPad changes itself and upgrading is going to be a pain.

Why does code I didn't write always break so often.

Maybe this is the perfect time for someone else to create a public query database so that I can close mine. >.>

EDIT: Rolled back to nodejs 12 for now as I see no other way to fix this on the short term. Sigh.Last modified on 2020-05-23 at 07:48

Reply

You must be logged in to reply to this thread.