Solved

Keeping track of uploaded file versions?

Posted on 2015-02-09
9
130 Views
Last Modified: 2015-02-15
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?
0
Comment
Question by:DrDamnit
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 40598953
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40598971
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
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 40598984
LOL @  Just to see if they remember what they asked for.

Cd&
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40599624
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40599655
Cd&... I avoid looking at the files until they have stopped sending them.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40602200
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
 
LVL 32

Author Comment

by:DrDamnit
ID: 40602209
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
 
LVL 32

Author Comment

by:DrDamnit
ID: 40602214
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
 
LVL 32

Author Comment

by:DrDamnit
ID: 40605685
*ping*....Should I abandon prepared statements for this?
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now