Is there really no simpler way to solve this problem? "Moving a message needs to move all of the message's comments, and all of the comments' files, and all of the comments' files' versions." Why can't you just change some top level reference in the database? I'm imagining a Projects table and a TodoLists table. Each TodoList has something like a projectID foreign key right? Why can't you just change that and automat…
lets see:
- Moving a message needs to move all of the message's comments, and all of the comments' files, and all of the comments' files' versions.
Not really. comment should only by 'tied' to message, i.e. there should be comments.messages_id command, so moving massage somewhere else shouldn't require moving comments (unless you fucked up the db schema to begin with)
- Moving any file needs to move its thumbnail, too, if it's an image.
No need to move the file to begin with, see above.
- Moving a milestone needs to move any associated to-do list and messages. And of course those to-do lists can have to-do items with comments, and attached files, and multiple versions of those files.
This is probably the one that indeed needs special treatment even in reasonable designed db. There are ways to skip this if you anticipate the need at the very beginning but it will require some convoluted db hacks to allow O(1) simple move (i.e. make everything including project just an item and just have parent_id in each item, then have messages to be children of the milestone. but this will have other problems, performance and complexity etc. so I wouldn't go this way)
- Moving a to-do list whose to-do items have associated time tracking entries needs to move those time entries to the destination project too.
Not really. time tracking entries should not have project_id in it. just todo_id
- Moving a message or file needs to re-create its category in the destination project if it doesn't already exist.
Indeed.
- If a moved file is backed up on S3 we need to rename it there. If it's not, we need to make sure it doesn't get backed up with its old filename.
why the file path on S3 has project id in it. attachments.id would suffice to uniquely identify stuff. its not like you need to invent multilevel directories on S3 like you do on a file system.
- When someone posts a message to the wrong project, then moves it to the right place, we need to make sure that everyone who received an email notification from the original message can still reply to the message via email.
If the email link just has the message_id and not the project_id it solves itself. You might need to play with some ACL though.
- Similarly we need to make sure that when you follow a URL in an email notification for a moved message, comment or files, you are redirected to its new location.
Dont' include project_id in the url to begin with. Use 'flat' routes.
- Since moving one milestone could potentially result in hundreds of database operations, we need to perform the move asynchronously. This means storing information about the move, pushing it into a queue, and processing it with a pool of background workers.
Not really. Even moving a milestone should only require 2 transactions. move the milestone, and move all milestone's children. Thats might be a non-trivial UPDATE sql operation but its not hundreds of queries.
- We also have to build a new UI for displaying the progress of a move. It needs to poll the Basecamp servers periodically in the background to check to see if the move is done yet, and take you to the right place afterwards.
Probably right.
- We can't use database transactions because performing a big move would slow Basecamp down for everyone. So we have to log the process of each step of the move, and make it so any failure in the move can be rolled back gracefully. That means a move is actually a series of copies and deletions instead of just changing a field for each moved item.
If you eliminate most of the complications above then you can use transactions.