Jason noticed a bug in LUMP last week (just one I hear you cry? 🙂 ).  When 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.  This 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.

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!).  It does this even if the user creating the structural unit doesn’t normally have edit rights to data types in some data type groups.  This 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.

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).  Now 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.  So after some cogitating, here’s is the resulting SQL:

insert into data_element (structural_unit_id, data_type_id, value)
select su.id, dt.id, dt.default_value
from structural_unit as su
inner join data_type_group as dtg
on su.structural_unit_type_id = dtg.structural_unit_type_id
inner join data_type as dt
on dt.data_type_group_id = dtg.id
and dt.name = "Held by Library"
left outer join data_element as de
on de.data_type_id = dt.id
and de.structural_unit_id = su.id
where su.structural_unit_type_id in (1)
and de.id is null;

Hmm, that’s some serious joining going on there!  The 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.  As 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).  This is then used directly in an insert.

Phew!  Seems to work though – we found 434 books on the live system that this corrects.  Relatively 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.

Still, similar SQL can now be used on other structural unit types and other data types as well.  But I think I need a walk round the library first… 😉