I have a database table that contains id, filename, userId
- id is unique identifier
- filename should also be unique
- table may contain >10000 records
When a user uploads a file it should be entered in database with given
rules:
- If there is no record with same filename, it should be added as it is (Ex. foobar.pdf)
- If there is record with same filename, it should be added as uploadedName(2).ext (foobar(2).pdf)
- If there are n records with same base filename (foobar), it should be added as uploadedName(n+1).ext (foobar(20).pdf)
- Now if foobar(2).pdf is uploaded, it should be added as foobar(2)(2).pdf & so on
This pattern needs to be followed because the file is already being
uploaded at client side using ajax before sending the details to
server and the file hosting service follows the above rules to name
the files.
My solution:
- maintain a file that contains all the names and the number of times
it has occurred. - if a filename that exists in file is entered, increase occurrence count and new name is generated, else add to it to file
- if the new name generated is in database, add it to file and generate new name
3
If the original filename is not significant then do as others have suggested and generate a synthetic name — a UUID would be a good candidate or use a sequence like your primary key. If the name is significant, i.e. you want to store it, then simply add a column to make it possible for the user to distinguish between duplicates possible and meaningful, and to reduce/eliminate duplicates. For instance, a timestamp indicating when the file was uploaded.
It’s not entirely clear to me what you’re asking — your title has a question, but your post does not. If you’re looking to update the column name of a table look into alter table — it’s simple. If you’re looking for how to massage existing data then see above. Otherwise you’ll need to rephrase your request so it’s a bit clearer.
Anyway, hope this helps!