eksopl/asagi — pull request #14 by woxxy (2012-04-08 00:54:43+00:00)
Title: Saving images only once and don't save banned images

Attempting at not having to save duplicates.

The real issue here is that __I am still working on an upgrade script that is solid enough__. I am resorting to asynchronous MySQL queries while images get _copied_, but, at least for /a/, this means the script will have to run for two to four days. For other boards it will probably just take few hours. The best I could reach on a server with MySQL not tuned for InnoDB was 170.000 images per hour with MySQL as bottleneck. I hope I can double that on our production server.

Meanwhile I also tested the fetcher for a bit with this fork, and for using it for a few hours it worked nicely. This has no reason to be merged into master before Fuuka or FFuuka can work with no image duplicates and there is a working migration script, but the changes are large enough that it needs to be discussed.

This changes the image saving procedure to the following:

* The post is first inserted in database so it updates the `_images` table
* After insertion of the post in the database the images to be downloaded are queued
* The image is checked against database before an attempt to download it
* If the banned column value is 1 the download is not performed
* If the file exists in its form (reply, OP or full image), download is not performed
* Normal download process

The changes are:

* Code:
* `Dumper.java`'s `updateTopic()` that was used only for queuing image downloads has been removed and `thetopicUpdates.add()` replaced all instances of `updateTopic()`
* `Dumper.java`'s `TopicInserter` now queues images for download after successfully inserting the topic to database
* `mediaFetcher` and `thumbFetcher` now get the `sqlBoard` variable for being able to check against DB
* added `Media.java` for mapping `_images` rows
* the classes implementing `SQL` now need a `getMediaRow(Post post)` to grab the `_images` row
* File system:
* folders look like `/boards/a/thumb/(filename.substr(0,4))/(filename.substr(4,6)))/filename.ext`
* Database:
* removed `parent`, `num`, `subnum`, `preview` columns from `_images` table
* added `media_filename`, `preview_op`, `preview_reply`, `banned` columns in `_images` table
* added `id` INT PRIMARY column in `_images` table and made `media_hash` UNIQUE
* added `media_id` INT column after `doc_id` to JOIN on `_images`'s `id`
* removed `after_ins_` trigger
* added `before_ins_` trigger that allows for `NEW.media_id` to be filled on insertion, as the `_images` table is updated first

`media_id` column is much faster to match on than `media_hash` (like 1:50?). This is mostly necessary to have because the migration script needs some speed in fetching same images, but of course it's really cool to use also to avoid the server hanging on `media_hash` searches.

What felt unruly after I edited the code:

* now it's sending the SQL variable to more threads
* I am not sure if the `before_ins_` is creating a race condition in #L9R154, where I am checking for existence before inserting. I believe it actually is because there's no requirement for the board table to be locked yet - in fact I am reading from it. The issue is otherwise that I can't stick in the `media_id` back into the board row.
* I can't into PostgreSQL so I couldn't write up a `getMediaRow()` function for it and left there a __@todo__