[EP-tech] Direct SQL updates for eprint records?

John Salter J.Salter at leeds.ac.uk
Mon Mar 20 15:32:17 GMT 2017

Hi Clinton,
There's two answers here: the right one (full of good practice), and the correct answer to your actual question.

The 'best practice' and 'right' answer is:
Do these things via the API.
If you want to write a script that e.g. read pairs of EPrintIDs / pagination data, it's not that difficult. Once you've done it a few times, it becomes much easier.

For your situation, I'd probably use a hash of EPrintIDs to pagination data, and iterate over that.
You then get revision info, and automatically updated abstract pages for free.
If you write a standalone script to do this, the revision data (in the history table) will also include the information about the script that caused the changes (this can be useful), so call the script something sensible that might mean something to someone else in the future!

The correct answer is:
If your changes are to a field that in no way has any calculated outcomes (e.g. changing a document 'security' flag may require the owning eprint's 'fulltext_status' field to be changed), then executing the SQL, and then regenerating the abstract pages (and views) etc. isn't a 'broken' option (this is how I used to do things, until I learnt to do them properly!).
The main thing that you have (probably) overlooked is how the data gets from the eprint table into the ordervalues / rindex tables. This is a 'calculated outcome' (I learnt this the hard way).

If you are a little unsure about writing a script like this, maybe post a gist on GitHub and ask for comments here?


From: eprints-tech-bounces at ecs.soton.ac.uk [mailto:eprints-tech-bounces at ecs.soton.ac.uk] On Behalf Of Graham, Clinton T
Sent: 20 March 2017 14:24
To: Eprints Tech Mailing List <eprints-tech at ecs.soton.ac.uk>
Subject: [EP-tech] Direct SQL updates for eprint records?

We have found a set of pagination errors in our records.  Can someone clarify the implications of directly updating the records via SQL  vs. writing a script to use the EPrints API to make the updates, including creating revisions?

Executing some SQL directly to fix these errors would be a trivial operation, but is a record of this change required as a revision on the filesystem?  Or can "known good" non-revisioned changes be made ad-hoc without concern?


- Clinton Graham
Systems Developer
University of Pittsburgh | University Library System

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20170320/e3db0fad/attachment.html 

More information about the Eprints-tech mailing list