Database dump survey

Started by byte[]
Posted
139 replies
Login to subscribe to responses
byte[]'s avatar
Posts: 1723
byte[]
Site Administrator
Site Developer
Hello all,

To make it easier for developers and analysts to access the information they want from the site, we are planning to offer database dumps for our public-facing data.1

Though this could change in the future, currently the plan is to offer nightly dumps in the PostgreSQL custom dump format. This will make it easy for anyone who wants to use the data to simply restore the dump and have a fully operational schema with indices and foreign key constraints. In addition, these dumps will be consistent and represent a perfect snapshot of the database at the time they are taken.

We would like to know from the community what data would be most useful to include in these dumps. Please comment below with the data you would like and your usecase for it, or any questions you may have.



1: We will not be offering any data that may deanonymize background ponies, or that would otherwise be considered personally identifying information.
Posted Report
NotADeliciousPotato's avatar
Posts: 293
NotADeliciousPotato
Artist
Wallet After Summer Sale

Equus ferus caballus
I’d be down with that.
(Don’t think it’d have anything that you can’t already see from the website’s normal user interface; it’ll just be stuff like # of favorites a picture has, etc.)
Posted Report
Luna's avatar
Posts: 793
Luna
Woona - Derpi Supporter
Artist
Not a Llama - green dA theme when
Happy Derpy! - For Patreon supporters
Perfect Pony Plot Provider - Uploader of 10+ images with 350 upvotes or more (Questionable/Explicit)
Hard Work - Merited Perfect Pony Plot Provider badge with only their own art
Birthday Cake - Celebrated MLP's 7th birthday
Always Codes Drunk - Apparently cats have nonexistent alcohol tolerance
Friendly Griffon (2018) - Drew someone's OC for 2018 collab but likes Gabby
Friendship, Art, and Magic (6 Years) - Celebrated Derpibooru's six year anniversary with friends.
Thread Starter - Started a thread with over 100 pages (soviet takeover thread)
Equality - In our state, we do not stand out.
Silver Bit
Sapphire
Best Artist - Providing quality, Derpibooru-exclusive artwork
The Power of Love - Given to a publicly verified artist with an image under their artist’s tag that has reached 1000 upvotes
Notoriously Divine Tagger - Consistently uploads images above and beyond the minimum tag requirements. And/or additionally, bringing over the original description from the source if the image has one. Does NOT apply to the uploader adding several to a dozen tags after originally uploading with minimum to bare tagging.
A Tale For The Ages - Celebrated MLP's 35th Anniversary and FiM's 8th Anniversary
An Artist Who Rocks - 100+ images under their artist tag
Friendship, Art, and Magic (7 Years) - Celebrated Derpibooru's seventh year anniversary with friends
Economist
Investor - Lootboxes are just another form of gambling
Toola Roola - For helping others attend the 2019 Community Collab

Site Moderator
Site Developer
Sitе Luna
Interactions, comments, image deduplication data, hashes, representation paths for image objects, tags, tag aliases/implications data.
Anything else is secondary in my book.
Posted Report
ZizzyDizzyMC's avatar
Posts: 28
ZizzyDizzyMC
A Perfectly Normal Pony - <ZizzyDizzyMC> you're really really triggering me
Not a Llama - Happy April Fools Day!
Friendship, Art, and Magic (6 Years) - Celebrated Derpibooru's six year anniversary with friends.
Friendship, Art, and Magic (7 Years) - Celebrated Derpibooru's seventh year anniversary with friends.
Economist

The Pony Archive Owner
I’d like this, you know, because it’d be easier on your servers.
edit: Full public facing dump, archiving everything. I already scrape every page recursively so this just makes it easier on your server with less queries from myself and like 50-60 other archivers that constantly download and update.
This would not stop new image queries however.

Deleted comments would be welcome in the dumps – it would take a lot of effort to stir up shit from a deleted comment pulled from a DB dump, it would also provide better transparency. If not, comment archivers will continue to run most likely resulting in more queries.
ZizzyDizzyMC's avatar
Posts: 28
ZizzyDizzyMC
A Perfectly Normal Pony - <ZizzyDizzyMC> you're really really triggering me
Not a Llama - Happy April Fools Day!
Friendship, Art, and Magic (6 Years) - Celebrated Derpibooru's six year anniversary with friends.
Friendship, Art, and Magic (7 Years) - Celebrated Derpibooru's seventh year anniversary with friends.
Economist

The Pony Archive Owner
@Communist Starlight
A database dump is just a large excel file.
A series of tables that have things that go to other things.

Key:value pairs generally but it can also be Key:key:key:Value:value:value:value:value Like this:

imageid|image title|image tags|image uploader|boolean nsfw

Please note that’s an dumb’d down example and is likely not anywhere close to their actual database.
Posted Report
Background Pony #67B5
I’d also be interested in one such thing. Including image data, comments, comment history, tags, changes in tags, forums, forum topics and forum posts.

Usecase would be archiving.
Posted Report
Darkest Lunar Flower's avatar
Posts: 334
Darkest Lunar Flower
Artist
Friendship, Art, and Magic (5 Years) - Celebrated Derpibooru's five year anniversary with friends.
Friendly Griffon - For helping others attend the fifth anniversary party
An Artist Who Rocks - 100+ images under their artist tag
Birthday Cake - Celebrated MLP's 7th birthday
Flexible - Bendy Pony
The Power of Love - Given to a publicly verified artist with an image under their artist’s tag that has reached 1000 upvotes
Helpful Owl - Drew someone's OC for the 2018 Community Collab
Friendship, Art, and Magic (6 Years) - Celebrated Derpibooru's six year anniversary with friends.
Equality - In our state, we do not stand out.
Best Artist - Providing quality, Derpibooru-exclusive artwork
A Really Classy Artist - 250+ images under their artist tag
A Tale For The Ages - Celebrated MLP's 35th Anniversary and FiM's 8th Anniversary
Friendship, Art, and Magic (7 Years Alt) - Celebrated Derpibooru's seventh year anniversary with friends
Woona - Best Princess
Happy Derpy! - For Patreon supporters
Wallet After Summer Sale
Toola Roola - For helping others attend the 2019 Community Collab
Bronze Bit

Your real life Luna.
Exact time of posts?

Also rating tags numbers and their relation to characters. For example who has a higher percentage of suggestive images Celestia or Luna.
Posted Report
SviMik's avatar
Posts: 1
SviMik
Speaking Fancy - Badge given to members that help with translations

Is it possible to share the code for reverse search along with the hashes it use?

Usecase:
I have my own picture archive, and I’d like to correlate them with derpibooru in order to
1. Automatically suggest a derpibooru link in my gallery
2. Maybe consider uploading images that are missing on derpibooru
Posted Report
Joey's avatar
Posts: 4167
Joey
Dawnsong - Derpi Supporter
The End wasn't The End - Found a new home after the great exodus of 2012
Happy Derpy!
Artist
Flexible - Bendy Pony
Best Artist - Best Artist - Providing quality, Derpibooru exclusive artwork
An Artist Who Rocks
Perfect Pony Plot Provider - 10+ uploads with over 350 upvotes or more (Questionable/Explicit)
Friendship, Art, and Magic (5 Years) - Celebrated Derpibooru's five year anniversary with friends.
The Power of Love - Given to a publicly verified artist with an image under their artist’s tag that has reached 1000 upvotes
Friendly Griffon - For helping others attend the fifth anniversary party
Not a Llama - Happy April Fools Day!
Gold Patron - Gold Patron
Birthday Cake - Celebrated MLP's 7th birthday
Helpful Owl - Drew someone's OC for the 2018 Community Collab
Friendship, Art, and Magic (6 Years) - Celebrated Derpibooru's six year anniversary with friends.
Equality - In our state, we do not stand out.
A Tale For The Ages - Celebrated MLP's 35th Anniversary and FiM's 8th Anniversary
Friendship, Art, and Magic (7 Years) - Celebrated Derpibooru's seventh year anniversary with friends
Friendship, Art, and Magic (7 Years) - Celebrated Derpibooru's seventh year anniversary with friends
Thread Starter - Started a thread with over 100 pages (DCC2019)
Silver Bit
Heart Gem
Magnificent Metadata Maniac - #1 Assistant
Wallet After Summer Sale
Toola Roola - For helping others attend the 2019 Community Collab

Site Developer
Nerd Pony
@Darkest Lunar Flower
You can find the exact time by hovering over the bit that says "Posted 3 minutes ago with your cursor", and it’ll show you the GMT timestamp of the post.

As for the other stuff – that’s statistics. People will be able to calculate that with this info (and I’m sure some people will), but we’ll just be providing the RAW data. Dumping a database is pretty easy and straightforward, but running a bunch of calculations and analytics against it is a bit more resource-intensive, and we don’t wanna put additional strain on the server.
Posted Report
Interested in advertising on Derpibooru? Click here for information!
Lunar Shine Store

Derpibooru costs over $25 a day to operate - help support us financially!

Syntax quick reference: *bold* _italic_ [spoiler]hide text[/spoiler] @code@ +underline+ -strike- ^sup^ ~sub~