HTML Logo by World Wide Web Consortium (www.w3.org). Click to learn more about our commitment to accessibility and standards.

Moving forward with Composr

ocPortal has been relaunched as Composr CMS, which is now in beta. ocPortal 9 will be superseded by Composr 10.

Head over to compo.sr for our new site, and to our migration roadmap. Existing ocPortal member accounts have been mirrored.


Change 'Added' date to 'Validation' Date, best approch?

Login / Search

 [ Join | More ]
 Add topic 
Posted
Rating:
#109807 (In Topic #21744)
TQ
Avatar

Honoured member

Hi All,

I am changing the 'Added' date of each of my new downloads to the date I validate them to bump them to the top of various lists (eg. Recent download additions) by running an SQL query on the server. This is tedious as I already have a back-log of additions and it adds even more time to the validation process (I download each item, collect various bit of info, edit the text and test the software).

I would appreciate suggestions on how best to speed up this process.

TQ
Back to the top
 
Posted
Rating:
#109819
Avatar

Maybe just copy the edit_date's.

This should (in theory) copy the edit date for every download edited within the last week by user #123.

Code

UPDATE ocp_download_downloads d SET add_date=edit_date WHERE edit_date>UNIX_TIMESTAMP()-7*60*60*24 AND EXISTS(SELECT * FROM ocp_adminlogs WHERE the_user=123 AND the_type='EDIT_DOWNLOAD' AND param_a=d.id);


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#109823
TQ
Avatar

Honoured member

As always, thank you Chris for being the first one to answer.

Couple of observations/questions;

the_user, how is this derived? I see it appears in 5 tables in m_name but I can't work out how it gets there. I don't want to take up your time so could you point me to the docs that covers this (especially as it may be beyond my very basic skill level).

Secondly, if I understand your query correctly, even if I change the date to last 24 hours not last 7 days, it's still going to process a record if I edit it in the future (which I may well do as more info becomes available).

What it did do is make me think a bit more laterally and this is where I am at the moment.

When I'm editing the up/download I add 'updateadded' to the notes section. I then have this query run every hour as a Windows task:

Code

USE MyDatabaseName;
UPDATE ocp_download_downloads d
SET add_date = UNIX_TIMESTAMP() , d.notes = ''
WHERE d.notes like '%updateadded%';


I haven't got a clue how to add it to the OCP CRON jobs but feel free to point me in the right direction for the docs for that too :).

It would have been perfect if I could have added a checkbox as a Downloads Field but I don't think it's possible to hide (via permissions) this checkbox to ordinary users/uploaders.

I am still open to better idea's and always open to practical criticism.

TQ
Back to the top
 
Posted
Rating:
#109825
Avatar

the_user is just a member ID#. Technically a user is a member or a guest, but practically it makes zero difference as member#1 is guest anyway.

Your notes idea makes sense.

I thought about this myself, longer-term, and concluded that as v10 has meta-data editing, it sorts itself out for the future in a reasonably satisfactory way (i.e. you can manually change date when validating).

No need to do CRON via ocPortal, why not just do it direct? Write a simple bat file that sends a command through to the mysql program (IIRC you're on Windows).


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#109828
TQ
Avatar

Honoured member

Thanks for the clarity on the_user.

I have already created a .cmd file that is triggered by task manager every hour like:

Code

"mysql" -u MyUser -p MyPassword < "\path_to_snippet\update-added.sql"
so I'm good to go. I can enable it when doing a batch of validations and disable it if I ever catch up :lol:

Thinking out loud: The idea of having Custom Download fields with permissions is growing on me as I think about how I could use them in conjunction with SQL queries eg. as part of the validation process I repeatedly 1, Give the uploader points with a generic message, 2, Post the DL to Facebook Group, 3, Promote the uploader to a new group if they are not already in it, 4, If it's a manual (PDF) I give it the same image(1) every time.

As I step through these thoughts, I can probably do most of this with a refined query to my existing method but ...

Thanks as always Chris, appreciated!

TQ
Back to the top
 
There are too many online users to list.
Control functions:

Quick reply   Contract

Your name:
Your message: