<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hola,<br>
<br>
If I may add to this.... You shouldn't use direct SQL unless there
are no other choices - this potentially breaks the relational
model.<br>
<br>
So instead, you should write a script that transfer objects from
one subject id to another, using the EPrints API.<br>
<br>
Seb.<br>
<br>
<br>
On 17/03/14 09:53, John Salter wrote:<br>
</div>
<blockquote
cite="mid:7154BCBB8909D642AE6F44CA713DBC200828ADDA4851@HERMES7.ds.leeds.ac.uk"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
<meta name="Generator" content="Microsoft Word 14 (filtered
medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]-->
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";
mso-fareast-language:EN-US;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">Hi Eliseo,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">There’s two
sides to this:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"> - getting the
new subjects created<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"> - moving
eprints from the old subjects to the new ones.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">If possible,
I’d look at getting the new subjects into order first – as
you’ve already identified, either via the web interface or
the import (you might want to look at the subject export too
– which might be useful to do, add your new subjects and
then re-import).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Creating a new
set of nodes is (IMO) easier than trying to rework the
existing ones as they are stored in the database as both
child and parent trees, so you’d possibly have to make
changes to stuff in all these tables:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">subject<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">subject__index<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">subject__index_grep<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">subject__ordervalues_en<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">subject__rindex<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">subject_ancestors<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">subject_name_lang<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">subject_name_name<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">subject_parents<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">To move the
eprints from one node to another, if it’s a straight swap
(e.g. subjectid_A is replaced by subjected_1; subjectid_B is
replaced by subjectid_2 etc.), it’s fairly easy to do in the
database (you could even clone the eprint_subjects table and
do the updates in the new copy to be on the safe side).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">mysql>
UPDATE eprint_subjects SET subject = newSubjectID where
subject = oldSubjectID;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">I find the
following query useful to see if there are any subjects use,
but not defined:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">mysql>
select count(*), subject, pos from eprint_subjects where
subject NOT IN (select subjectid from subject) group by
subject, pos;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Hope that helps
a bit – someone else might have some better/easier ways of
doing this!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Cheers,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">John<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";mso-fareast-language:EN-GB"
lang="EN-US">From:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif";mso-fareast-language:EN-GB"
lang="EN-US"> <a class="moz-txt-link-abbreviated" href="mailto:eprints-tech-bounces@ecs.soton.ac.uk">eprints-tech-bounces@ecs.soton.ac.uk</a>
[<a class="moz-txt-link-freetext" href="mailto:eprints-tech-bounces@ecs.soton.ac.uk">mailto:eprints-tech-bounces@ecs.soton.ac.uk</a>] <b>On
Behalf Of </b>Eliseo Gatchalian<br>
<b>Sent:</b> 17 March 2014 03:06<br>
<b>To:</b> <a class="moz-txt-link-abbreviated" href="mailto:eprints-tech@ecs.soton.ac.uk">eprints-tech@ecs.soton.ac.uk</a><br>
<b>Subject:</b> [EP-tech] Updating Subjects<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-NZ">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-NZ"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-NZ">I know that we can add
and edit the subjects by logging in as admin and can also
import a subject_xml file, but If we are to merge some
subjects with different subject code into one, is there an
automatic way to update the subject codes for the items
already submitted in the archive?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-NZ"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-NZ">I saw the
eprints_subject table wherein we can replace the subject
field with the new codes and update the items but I’m not
sure if there are anything else that we need to update?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-NZ"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-NZ">Thanks!<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-NZ"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:EN-NZ"
lang="EN-NZ"><br>
<o:p></o:p></span></p>
<table class="MsoNormalTable" style="width:97.0%" border="0"
cellpadding="0" width="97%">
<tbody>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt"
nowrap="nowrap" valign="bottom">
<p class="MsoNormal"><b><span
style="font-size:7.5pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:EN-NZ">Ellis
Gatchalian</span></b><span
style="font-size:7.5pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:EN-NZ"><br>
Systems Librarian<br>
Wintec<br>
Private Bag 3036, Waikato Mail Centre, Hamilton 3240<br>
Phone: +64-(0)7-834 8800 ext 8633<br>
Fax: +64-(0)7-838 8257<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:7.5pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:EN-NZ">Email:
<a moz-do-not-send="true"
href="mailto:ellis.gatchalian@wintec.ac.nz">ellis.gatchalian@wintec.ac.nz</a><o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:7.5pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:EN-NZ">Web:
<a moz-do-not-send="true"
href="http://www.wintec.ac.nz/"><span
style="color:#333333">http://www.wintec.ac.nz/</span></a><o:p></o:p></span></p>
</td>
<td style="padding:.75pt .75pt .75pt .75pt"
valign="bottom">
<p class="MsoNormal" style="text-align:right"
align="right"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:black;mso-fareast-language:EN-NZ"><img
id="Picture_x0020_3"
src="cid:part3.03020904.03030406@ecs.soton.ac.uk"
alt="WIN01" height="94" border="0" width="206"></span><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#333333;mso-fareast-language:EN-NZ"><o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt">
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#333333;mso-fareast-language:EN-NZ"> <o:p></o:p></span></p>
</td>
<td style="padding:.75pt .75pt .75pt .75pt">
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:#333333;mso-fareast-language:EN-NZ"> <o:p></o:p></span></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span style="mso-fareast-language:EN-NZ"
lang="EN-NZ"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-NZ"><o:p> </o:p></span></p>
<div class="MsoNormal" style="text-align:center" align="center"><span
style="font-size:12.0pt;font-family:"Times New
Roman","serif";mso-fareast-language:EN-GB"
lang="EN-NZ">
<hr align="center" size="2" width="100%"></span></div>
<div>
<p><span
style="font-size:8.0pt;font-family:"Calibri","sans-serif";color:#777777"
lang="EN-NZ">This electronic mail transmission is intended
for the named recipients only. It may contain private and
confidential information. If this has come to you in error
you must take no action based upon it, nor must you copy
it or show it to anyone; please telephone or email the
sender at Wintec immediately and return the original
email. We cannot accept any liability for any loss or
damage sustained as a result of software viruses. It is
your responsibility to carry out such virus checking as is
necessary before opening any attachment which may be
included with this message.<o:p></o:p></span></p>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">*** Options: <a class="moz-txt-link-freetext" href="http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech">http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech</a>
*** Archive: <a class="moz-txt-link-freetext" href="http://www.eprints.org/tech.php/">http://www.eprints.org/tech.php/</a>
*** EPrints community wiki: <a class="moz-txt-link-freetext" href="http://wiki.eprints.org/">http://wiki.eprints.org/</a>
*** EPrints developers Forum: <a class="moz-txt-link-freetext" href="http://forum.eprints.org/">http://forum.eprints.org/</a>
</pre>
</blockquote>
<br>
</body>
</html>