Jason noticed a bug in LUMP last week (just one I hear you cry? \ud83d\ude42 ). \u00a0When non-admin\/non-library staff created a structural unit for something like a book, there wouldn’t be any “Held by Library” flag data elements created, as these have a data type that lives in a data type group that the academics don’t have edit rights to. \u00a0This means that some of the backend scripts that were looking for works with this flag set to “N” were missing some records that don’t have the flag at all.<\/p>\n
The long term fix for this was relatively easy – I just got EditStructuralUnit to create data elements filled in with the default value if such a thing exists in the data type table for any data type groups associated with the structural unit type of the structural unit being created (and breathe… honestly it makes more sense than it sounds like it does!). \u00a0It does this even if the user creating the structural unit doesn’t normally have edit rights to data types in some data type groups. \u00a0This should be quite safe to do as we’re creating a new structural unit so having defaulted data elements where possible should make library staffs’ lives easier.<\/p>\n
However there was still a gotcha – we needed to go and find all the structural units that were missing their “Held by Library” data elements and create a suitable data element filled in with the default (in our case “N” as we assume we don’t hold works until proven otherwise). \u00a0Now I could have knocked out a Perl script with several selects and subqueries in it, but I decided to exercise the old SQL muscle and see if I could do it straight into the database, preferably in a single SQL statement. \u00a0So after some cogitating, here’s is the resulting SQL:<\/p>\n
insert into data_element (structural_unit_id, data_type_id, value)\nselect su.id, dt.id, dt.default_value\nfrom structural_unit as su\ninner join data_type_group as dtg\non su.structural_unit_type_id = dtg.structural_unit_type_id\ninner join data_type as dt\non dt.data_type_group_id = dtg.id\nand dt.name = \"Held by Library\"\nleft outer join data_element as de\non de.data_type_id = dt.id\nand de.structural_unit_id = su.id\nwhere su.structural_unit_type_id in (1)\nand de.id is null;<\/pre>\nHmm, that’s some serious joining going on there! \u00a0The basic idea is that we’re doing normal inner joins to link structural units, data type groups and data types together, and then a left outer join to spot data_elements. \u00a0As the left outer join has the data element columns set to NULL where the data element doesn’t match in a row, we use that in the where clause to limit the output to just the missing structural unit and data type information (plus a limitation on the types of structural unit we’re interested in – in this case just books). \u00a0This is then used directly in an insert.<\/p>\n
Phew! \u00a0Seems to work though – we found 434 books on the live system that this corrects. \u00a0Relatively quick as well – less than 20 seconds on our production database. If anyone can see any better way to do this (or issues with this one!) please leave a comment.<\/p>\n
Still, similar SQL can now be used on other structural unit types and other data types as well. \u00a0But I think I need a walk round the library first… \ud83d\ude09<\/p>\n","protected":false},"excerpt":{"rendered":"
Jason noticed a bug in LUMP last week (just one I hear you cry? \ud83d\ude42 ). \u00a0When non-admin\/non-library staff created a structural unit for something like a book, there wouldn’t be any “Held by Library” flag data elements created, as these have a data type that lives in a data type group that the academics […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[4],"tags":[42,75],"class_list":["post-852","post","type-post","status-publish","format-standard","hentry","category-lump","tag-joins","tag-sql","count-0","even alt","author-cojpk","last"],"_links":{"self":[{"href":"https:\/\/blog.lboro.ac.uk\/lorls\/wp-json\/wp\/v2\/posts\/852","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.lboro.ac.uk\/lorls\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.lboro.ac.uk\/lorls\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.lboro.ac.uk\/lorls\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.lboro.ac.uk\/lorls\/wp-json\/wp\/v2\/comments?post=852"}],"version-history":[{"count":0,"href":"https:\/\/blog.lboro.ac.uk\/lorls\/wp-json\/wp\/v2\/posts\/852\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.lboro.ac.uk\/lorls\/wp-json\/wp\/v2\/media?parent=852"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.lboro.ac.uk\/lorls\/wp-json\/wp\/v2\/categories?post=852"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.lboro.ac.uk\/lorls\/wp-json\/wp\/v2\/tags?post=852"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}