openspecimen Database

Parameters

Name	Type	Mode
data_table	text	IN
audit_table	text	IN

Definition

begin

set @set_creation_info = concat(
          'update ',
             data_table, ' dt ',
          '  inner join ', audit_table, ' at on at.identifier = dt.identifier and at.revtype = 0 ',
          '  inner join os_revisions rev on rev.rev = at.rev ',
          'set ',
          '  dt.creator = rev.user_id, ',
          '  dt.creation_time = rev.revtstmp'
        );

prepare set_creation_info_stmt from @set_creation_info;
        execute set_creation_info_stmt;
        deallocate prepare set_creation_info_stmt;

set @set_update_info = concat(
          'update ',
            data_table, ' dt ',
          ' inner join (
              select
                t1.*
              from (
                select
                  dt1.identifier, rev1.revtstmp, rev1.user_id
                from
                  os_revisions rev1
                  inner join ', audit_table, ' dt1 on dt1.rev = rev1.rev and dt1.revtype = 1
                ) t1 left join (
                select
                  dt2.identifier, rev2.revtstmp, rev2.user_id
                from
                  os_revisions rev2
                  inner join ', audit_table, ' dt2 on dt2.rev = rev2.rev and dt2.revtype = 1
                ) t2 on t1.identifier = t2.identifier and t1.revtstmp < t2.revtstmp
              where
                t2.identifier is null
            ) audit on audit.identifier = dt.identifier
          set
            dt.updater = audit.user_id,
            dt.update_time = audit.revtstmp'
        );

prepare set_update_info_stmt from @set_update_info;
        execute set_update_info_stmt;
        deallocate prepare set_update_info_stmt;
      end

update_records_audit_info

Parameters

Definition