Solved

Keeping track of uploaded file versions?

Posted on 2015-02-09
9
129 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 82

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
 
LVL 108

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 82

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

747 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

10 Experts available now in Live!

Get 1:1 Help Now