Keeping track of uploaded file versions?

Client sends you a word doc with data on it about the widget you want to build. You save said word doc. Two days later, they change it, and send you a new version. Over the weekend, they realized that they need to scrap two items on the second revision, so they trash that and send you a new version.

Now, I have three word docs. And, presumably, I'll have a dozen more before they are done sending me their stuff on the widget they want built.

So, I want to keep track of these in a database. I have two tables, one is "opportunity" (because that's what "Can you build me this?" is until they write a check), and the other is opportunity_uploads.

Screenshot.
My current thought is to add a column called: "opportunity_uploads_replaces", which would contain the opportunity_uploads_id of the previous version of the document.

Then, to find the most current version of this document, I can just sort DESC based on this "replaces" column.

But what if I want to see and download all versions? Would I just do a series of joins and join the same column? The relationship is only from opportunity_uploads_replaces -> opportunity_uploads_id. If opportunity_uploads_replaces IS NULL, then it is considered to be the first version. Else, it replaces opportunity_uploads_id.

Is there a better (more elegant) way to do this?
LVL 32
DrDamnitAsked:
Who is Participating?
 
Ray PaseurCommented:
Three columns that work well with the Eloquent ORM, and should work well for you, too.

created_at, updated_at, deleted_at.

These are all of data type = TIMESTAMP.  They allow for "soft deletes" by setting the deleted_at value.

You can coordinate the uploaded files with the database records with a naming convention that makes use of a user_key and a document_key.   It is easy to ORDER BY the timestamps and GROUP BY the user_key.  The highest document_key numbers are the most recent.

For a bit finer granularity of control you might ask your clients to name their projects or choose from an existing list of projects.
0
 
COBOLdinosaurCommented:
I'm not sure I actually understand the problem. Why not just time stamp everything with a timestamp column that gets a value of NOW() on inserts. I used to have similar issues until I adopted a log that gets written for uploads from clients, and sends an email to the project manager for the specific project.

I keep the upload log for the project in the top level directory for the project, so it always contains every uploaded change in the sequence they were uploaded.

Cd&
0
 
Dave BaldwinFixer of ProblemsCommented:
I usually add a time stamp to the file name when I save it.  Sometimes I make them send me a completely new version that list everything they want.  Just to see if they remember what they asked for.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
COBOLdinosaurCommented:
LOL @  Just to see if they remember what they asked for.

Cd&
0
 
Dave BaldwinFixer of ProblemsCommented:
Cd&... I avoid looking at the files until they have stopped sending them.
0
 
DrDamnitAuthor Commented:
OK. I headed Ray's advice, and added created_at, updated_at, deleted_at. Sort of.

I added the deleted column, and "uploaded" is the same as created. "updated" isn't really relevant because I want to keep numbered version of all received files. (On large projects that involve lots of different departments all over the country, I can't just wait until they stop sending files... as Dave does... and as I envy). I need to track this.

I also added unique_id, which is set using uniqid(). This serves as the document identifier, which I can use in a GROUP BY clause.

Here's the catch:
I need to do a GROUP BY that always gives me the record (the full record) with the highest version number. I have attached four sample records, which give a good example.

The four records manage 4 files, but only 2 unique files: kazam-1.4.5.tar.gz and php-imap-master.zip. There are two versions of each. So, to create a list of "current" versions of each files, I want to show opportunity_uploads_id 7 for kazam-1.4.5.tar.gz and id#9 for php-imap-master.zip.

But, I know GROUP BY is not affected by ORDER BY clauses, so (@Ray) how would I ORDER BY opportunity_uploads_version DESC and GROUP BY opporunity_uploads_unique_id?
0
 
DrDamnitAuthor Commented:
Current thought is this, but seems inelegant and may (potentially) fail from time to time. Thoughts?

SELECT 
    opportunity_uploads_id,
    opportunity_uploads_version,
    opportunity_uploads_unique_id
FROM
    (SELECT 
        *
    FROM
        bugreport.opportunity_uploads
    WHERE
        opportunity_uploads.opportunity_uploads_opportunity = 89 AND opportunity_uploads.opportunity_uploads_deleted = 0
    ORDER BY opportunity_uploads.opportunity_uploads_version DESC) AS tmpTable
GROUP BY opportunity_uploads_unique_id

Open in new window

0
 
DrDamnitAuthor Commented:
Update: this seems to make mysqli::prepare()'d statements upset.

	$sql = '
			SELECT 
			    opportunity_uploads_id,
			    opportunity_uploads_version,
			    opportunity_uploads_unique_id
			FROM
			    (SELECT 
			    opportunity_uploads_id,
			    opportunity_uploads_version,
			    opportunity_uploads_unique_id,
			    opportunity_uploads_opportunity,
			    opportunity_uploads_deleted
			    FROM
			        bugreport.opportunity_uploads
			    WHERE
			        opportunity_uploads.opportunity_uploads_opportunity = ? AND opportunity_uploads.opportunity_uploads_deleted = 0
			    ORDER BY opportunity_uploads.opportunity_uploads_version DESC) AS tmpTable
			GROUP BY opportunity_uploads_unique_id	
	';

Open in new window


Yields:
Error Binding Results: (MySQL Error Number: 2014) Error: Commands out of sync; you can't run this command now
0
 
DrDamnitAuthor Commented:
*ping*....Should I abandon prepared statements for this?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.