Solved

Keeping track of uploaded file versions?

Posted on 2015-02-09
9
132 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 110

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with mod_substitute 18 41
MySQL limit and not so limited 13 40
Star schema daily updates 2 37
CURL sending XML without spaces PHP 5 34
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

733 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