Optimized attachments in vB2.X.X

Hi vB Troopers,

*****************
* Hack Idea:
*****************
This hack is designed to improve 2 things:
1. Prevent duplicate attachments altogether.
2. Speed up the query for duplicates and avoid errors.

What does vB do now?
---------------------------------
If you have "Allow Duplicate Images" set to "No" in your attachments options and your database has a very large amount of attachments, at worst, the server will timeout or the query to check for duplicate attachments takes an extreme amount of time. The duplicates query also checks only for duplicates from a particular user so actually it is possible to have duplicate attachments in the database.

What does vB do differently after the hack?
------------------------
With the hack installed, each attachment receives a hash which is stored in the attachment table. This hash is then used to find duplicate attachments regardless of whether or not the attachment is from the same user or not. So duplicate attachments are not possible.

Why vB works faster with this hack?
------------------------------------
Since the hash field and not the actual file content is being compared, then much less server resources are needed for MySql to run the query. Also the hash field is indexed. This in turn speeds up the saving of the attachments when checking for duplicates.

Please be aware that this hack alters the database and when carried out incorrectly, could cause data loss. Installation is at your own risk.

That said, enjoy!

Scott

This hack was written by pogo.

Also note, a system similiar to this will be part of vB3, so no need to ask if it will be added.

[Boofo]

If you add the code Xenon pointed out, wouldn't it run the same speed as it does now?

to solve the problem, you have to compare the full data after selecting it with the hash comparison.

[pogo]

No. It is only one additional comparison that should run fast.

[Boofo]

So, as the hack writer, do you recommend to add it?

[Xenon]

can i answer?
it's not recommended, but it prevents a bug which can occur (normally one on every million board, but a bug, so in vb3 i think this would be in..)

[Boofo]

Ok, then, just for curiosities sake, what would happen if the bug ever did occur? Would it only happen once in a long while and what would it do or cause?

[pogo]

If the hashes of two different attachments are the same the new attachment wouldn't make it into the database because it is assumed that it is the same file like the one that is already in the database.

So the bug would be that the wrong attachment is shown in the post. I don't think that you will encounter this bug.

[Boofo]

Thanks for explaining that. I will go ahead and add the original version then. Thanks! And great hack, by the way.

[Boofo]

This is bugging the heck out of me for some reason. What does this come out to?

1 to 3.4 * 10^38

I tried to do it on the windows Scientific Calculator and I only come up with something like 149.5.

[Xenon]

it is 0.000000000000000000000000000000000000002941

[pogo]

The chances that you want to upload a file that has the same hash like a file that is already in the database are 1:340.000.000.000.000.000.000.000.000.000.000.000. 000

That means you have to upload this many files before the "bug" will occur. Theoretically.