Database dump survey

@byte[]  
It would be for importing into an existing table. Indices and constraints will already be set on the database it gets added to.

Very cool.
 
Thank you for all the work on this, and for making it available. ♥

Ooh, nice! Going to have fun with this.
 
EDIT: Took a while, but now I have some numbers. Shameless self-promotion alert!
 
Long ago, I wondered what are the tags I most often add to images. Well, assuming I didn’t screw up (which is a possibility), here’s the top-20 (extracted from the 2019-07-25 dump):
 
@18543 solo  
12340 simple background  
11779 transparent background  
10129 oc only  
7417 twilight sparkle (alicorn)  
6309 alicorn  
5549 striped socks  
5383 traditional art  
4106 floppy ears  
2314 socks  
1985 clothes  
1876 wingding eyes  
1815 tree  
1463 heart eyes  
1380 white background  
1134 oc  
1128 book  
1098 tongue out  
1017 princess twilight  
1014 chest fluff@
 
Some of them are implications (“striped socks” also adds “socks”, “oc only” adds “oc”, “transparent background” adds “simple background” and so on), but there’s no way to filter them, because some of them are legit (not added by implications).
 
^{(This is of course slightly off-topic, so I won’t do this kind of post again unless someone asks me to.)}

Some notes from Windows user, who doesn’t have any experience with PostgresSQL and extracted data with googling and coping recipes from StackOverflow.  
I wanted just to dump everything I need to compact .txt files  
It seems I have solved all my problems, so no any useful information and no help requests here  
Derpi developers/admins can skip this or read without much attention
 
It could be useful for other Windows users
 
I tried to run postgresSQL from .zip-file with binaries, but no success, I got errors like “can’t connect to localhost:5432” ( I have read https://feilerdev.wordpress.com/2017/12/05/installing-postgresql-on-windows-using-zip-archive-without-the-installer/ , initdb and pg_ctl start run fine, but subsequent commands fails with the same connection error )
 
 
I had installed service from usual installer ( https://www.enterprisedb.com/downloads/postgres-postgresql-downloads ), it asked me question about password.  
I have to add username with ‘-U postgres’ and type password every time I run command line tool.  
I have put the password to PGPASSWORD environment variable to avoid to type it every time.
 
For example:  
c:\soft\psql\10\bin\createdb.exe -U postgres derpibooru
 
“public.” from table names list on derpibooru page is not part of table name.
 
The full command I used to extract only image-related statistics tags and faves, without user comments and without galleries to save disk space:
 
c:\soft\psql\10\bin\pg_restore -O -U postgres -d derpibooru -t “image_faves” -t “image_duplicates” -t “duplicate_reports” -t “image_intensities” -t “image_sources” -t “image_taggings” -t “images” -t “tag_aliases” -t “source_changes” -t “tag_implications” -t “tags” -t “user_links” -t “users” derpibooru_public_dump_2019_07_28.pgdump
 
Example of data extraction:  
c:\soft\psql\10\bin\psql -U postgres -d derpibooru -c “SELECT * FROM tags” > derpi_tags.txt  
c:\soft\psql\10\bin\psql –no-align -U postgres -d derpibooru -c “SELECT created_at,image_size,score,hides,image_format,image_name,version_path FROM images” > derpi_image_info.txt  
c:\soft\psql\10\bin\psql –no-align -U postgres -d derpibooru -c “SELECT * FROM images WHERE id=123”
 
I have hope I get no users and tag names with ‘|’, but if this happens I’ll reread documentation about psql.exe output separators ( https://www.postgresql.org/docs/10/app-psql.html )

Thank you a lot for the dump! I have learned how to extract data from the dump, I can play with it tomorrow.
 
What is “image_intensities image_id|nw_intensity|ne_intensity|sw_intensity|se_intensity|avg_intensity” table?

@xbi  

@xbi  
Reverse search data.

I have spent some time to deduce how to generate thumbnail URLS. it is not evident how image file extension is generated in URL. It is not derived from image_mime_type and simple concatenation with image_format doesn’t work too when it is in uppercase.
 
Seems it is “image_format”, transformed to lower case.  
“image_format” is taken from user uploaded file name and can be quite weird
 
Also, I decided don’t keep URL in my text files, because it can be generated from creation_time, with leading zeroes in month/day removed.

How can I detect that image was deleted? Can’t see any sign of this in ‘images’ data table, all image attributes are kept.

@xbi  
Funny you ask. There seem to be no rows in the database that contain data about deleted images (not even the data that were available through API).  
Take for example:  
select * from images as i where i.id between 16 and 18;  
which returns only rows 2 for 16 and 18 and I know for sure >>17 is deleted.  
Or:  
select * from images as i where i.id in (17, 238, 249, 280);  
which returned no rows for me.
 
 
@byte[]  
You guys making changes to the API is fine and dandy. However: CAN YOU AT LEAST LET PEOPLE KNOW WHEN YOU ROLL OUT THE FUCKING CHANGES?! Make a stupid mailing list or something FFS.

@Background Pony #67B5  
… what did we change?

@byte[]  
Going to https://derpibooru.org/17.json or other deleted images used to return empty json structure, not a redirect to https://derpibooru.org/
 
I also apologize for being impolite, I forgot to put “Please” at the end of the post.

@Background Pony #67B5  
Oh, that is a bug then.

@byte[]  
The “before” was a bug, or the “now” is a bug?

@Background Pony #67B5  
The now is. It should return 404 status and an empty object.

@Background Pony #67B5  

 
There are some hard-deleted entries. These are mostly old____+ ones.
 
 

 
I have noticed no such problems with the API. What endpoint are you having issues with?

@Background Pony #67B5
 
I see only 565 rows are missing from the database in the range from 0 to the last id. Of course there were much more deleted images due legal reasons or artist requests. Also derpibooru org behaves in very different way for images which are deleted from database and for hidden images. For example, >>411160 (deleted) is hidden and shows reason of deletion, but has the database entry. And >>17 or >>964900 are really deleted from the database, and derpibooru site redirects to the main page. It is same behavior for my user point of view, I can’t write simple code which skips these deleted images thumbnails in html generation without going online.

@Damaged  
I had a problem with EDIT: https://derpibooru.org/1206148.json https://derpibooru.org/1206149.json or https://derpibooru.org/17.json does the same thing.
 
@byte[]  
Both of the above return 302 and then go to https://derpibooru.org/

@Damaged  
@byte[]
 
It could be more comfortable to have some sign of unavailable image, so when I fail to show the thumbnail - i can start searching for bug in my code or fixing my internet connection. Also it is useful to avoid them in statistics, they could be some type of hard to debug outliers and hard to take decisions with my own eyes about image content.

@Background Pony #67B5  

 
The first one, 1206148, works just fine for me. The second one is one of the hard-deleted images I mentioned. I’d suggest coding to allow for not getting JSON. This is particularly important when the site goes into DDOS protection mode, as you will NEVER get JSON when that is active.

@Damaged  
Thanks for the suggestion.  
@byte[]  
Sorry, it should have been https://derpibooru.org/1206149.json I forgot to change the number.

@Background Pony #67B5  
Like I said, it’s just one of the handful of hard-deleted images.
 
Your app needs to catch when application/json is not in the returned Content-Type header.
 
Personally, I use (Python):
 
assert 'application/json' in headers['content-type'].lower()
 
And then catch when an error is thrown.

@xbi  
I believe you’re looking for the image_hides table. (It might be new, since it’s not mentioned on the dumps page.)

@Rene_Z
 
Thank you!  
Yes, this table is missing from derpibooru_public_dump_2019_07_28.pgdump , but it is available in derpibooru_public_dump_2019_08_03.pgdump

Where can I find the dumps?