Child pages
  • Gleaning the Site ID from the Event Table
Skip to end of metadata
Go to start of metadata

At Michigan, we do a lot of auditing, research, and support work using the event table. Over time, we found that searching for a particular site became a very long search since the only way to find a site ID in the event table is to search for the corresponding string in the REF field. Even with our REF field indexed, it sometimes took more than 30 minutes to find the events for one site for a time period as short as one month. So, we started an effort to create a new field in our archived event table that would glean the site id from the REF field so that searches could be run much faster.

Our Context:
We have a separate table for our events that we call sakai_event_archive that holds events from 1/1/2007 - present (we're in June 2008 at the time of writing this document). Our production data is copied over into the archive table every day and backed up weekly. We also have another event table called sakai_event_archive_old for events prior to 1/1/2007, but for the purposes of this effort, we concentrated only on the main archive table.

We use an Oracle Database and use either DB Visualizer or SQL Developer to query our logs.

The following steps were for Sakai 2.4.x.  We have since updated our SQL/PL for Sakai 2.5.x -- if you are interested in obtaining the most recent version, please contact ctops_at_umich.edu

Step 1:
We created a new field in the sakai_event_archive table called SITE_ID:

Alter table SAKAI_EVENT_ARCHIVE add SITE_ID VARCHAR2(99);
create index IDX_EVENT_ARCHIVE_SITE_ID on SAKAI_EVENT_ARCHIVE(SITE_ID) tablespace CTOOLS_EVENT_ARCHIVE;

Step 2:
We checked all events in the sakai_event_archive table to make sure we identified all of our events that have a Site ID in the REF field (or an identifying id that can be cross-referenced in another table):

SELECT EVENT, MAX(REF) FROM sakai_event_archive GROUP BY EVENT ORDER BY EVENT

*Note: We have some unique tools in our instance, including the Dissertation checklist that create some unique events. 

Step 3:
We identified all of the events that had a Site ID in the REF field and how to best design a case statement to identify where in the REF the Site ID was. Sometimes, depending on the event (e.g. content.* events), this required several statements depending on which tool was creating the event, and in what context.

At this point, we also identified two tools, gradebook and mneme, that contained a reference number in the sakai_event table, but contained the Site ID in a different table. We thus created one query for gradebook and four queries for mneme that used the identifying number in the REF field to identify the Site ID in the appropriate table.

Thus, below are our queries. The first query is for the gradebook tool, the next four are for mneme, and the remainder are for the other events in the table:

select gb_gradebook_t.gradebook_uid
from sakai_event_archive, GB_GRADEBOOK_T
where to_char(gb_gradebook_t.id) = to_char(substr(REF, INSTR(REF, '/', 1, 2) + 1, INSTR(REF, '/', 1, 3) - INSTR(REF, '/', 1, 2) - 1))
and EVENT in ('gradebook.comment', 'gradebook.deleteItem', 'gradebook.downloadCourseGrade', 'gradebook.downloadRoster', 'gradebook.importItem', 'gradebook.newItem', 'gradebook.updateCourseGrades', 'gradebook.updateItemScores')


select mneme_assessment.context
from sakai_event_archive, mneme_assessment
where to_char(mneme_assessment.id) = to_char(substr(REF, INSTR(REF, '/', 1, 3) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 3)))
and SUBSTR(REF, 1, 18) = '/mneme/assessment/'
and event_date>=to_date('03/01/2008','mm/dd/yyyy')


select mneme_pool.context
from sakai_event_archive, mneme_pool
where to_char(mneme_pool.id) = to_char(substr(REF, INSTR(REF, '/', 1, 3) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 3)))
and SUBSTR(REF, 1, 12) = '/mneme/pool/'
and event_date>=to_date('03/01/2008','mm/dd/yyyy')


select mneme_question.context
from sakai_event_archive, mneme_question
where to_char(mneme_question.id) = to_char(substr(REF, INSTR(REF, '/', 1, 3) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 3)))
and SUBSTR(REF, 1, 16) = '/mneme/question/'
and event_date>=to_date('03/01/2008','mm/dd/yyyy')


select mneme_submission.context
from sakai_event_archive, mneme_submission
where to_char(mneme_submission.id) = to_char(
decode(INSTR(REF, ':'), 0, substr(REF, INSTR(REF, '/', 1, 3) + 1),
                         substr(REF, INSTR(REF, '/', 1, 3) + 1, LENGTH(REF) - INSTR(REF, ':') - 1)))
and SUBSTR(REF, 1, 18) = '/mneme/submission/'
and event_date>=to_date('03/01/2008','mm/dd/yyyy')


SELECT EVENT,
CASE
WHEN EVENT in ('annc.delete.any', 'annc.delete.own', 'annc.new', 'annc.revise.any', 'annc.revise.own', 'annc.schInv.notify', 'asn.delete.assignment', 'asn.delete.assignmentcontent', 'asn.delete.submission', 'asn.grade.submission', 'asn.new.assignment', 'asn.new.assignmentcontent', 'asn.read', 'asn.read.assignment', 'asn.revise.assignment', 'asn.revise.assignmentcontent', 'asn.save.submission', 'asn.submit.submission', 'calendar.new', 'calendar.revise', 'chat.delete.any', 'chat.delete.channel', 'chat.delete.own', 'chat.new', 'disc.delete.any', 'disc.delete.category', 'disc.delete.own', 'disc.new', 'disc.new.category', 'disc.revise.own', 'mail.delete.any', 'mail.new', 'mail.revise.any', 'msgcntr.delete', 'msgcntr.new', 'msgcntr.read', 'msgcntr.response', 'msgcntr.revise', 'news.read', 'news.revise', 'schInv.delete', 'wiki.new', 'wiki.revise') THEN SUBSTR(REF, INSTR(REF, '/', 1, 3) + 1, INSTR(REF, '/', 1, 4) - INSTR(REF, '/', 1, 3) - 1)
WHEN EVENT in ('content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 18) = '/citation/content/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, INSTR(REF, '/', 1, 5) - INSTR(REF, '/', 1, 4) - 1)
WHEN EVENT in ('content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 14) = '/content/group' THEN SUBSTR(REF, INSTR(REF, '/', 1, 3) + 1, INSTR(REF, '/', 1, 4) - INSTR(REF, '/', 1, 3) - 1)
WHEN EVENT in ('content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 20) = '/content/attachment/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 3) + 1, INSTR(REF, '/', 1, 4) - INSTR(REF, '/', 1, 3) - 1)
WHEN EVENT in ('content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 14) = '/content/user/' THEN '~'||SUBSTR(REF, INSTR(REF, '/', 1, 3) + 1, INSTR(REF, '/', 1, 4) - INSTR(REF, '/', 1, 3) - 1)
WHEN EVENT in ('dis.dis.add', 'dis.dis.upd', 'dis.grp.add', 'dis.info.upd', 'dis.path.add', 'dis.path.del', 'dis.path.upd', 'dis.status.add', 'dis.status.del', 'dis.status.upd', 'dis.step.add', 'dis.step.del', 'dis.step.upd') THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, INSTR(REF, '/', 1, 5) - INSTR(REF, '/', 1, 4) - 1)
WHEN EVENT in ('podcast.generate.private', 'podcast.generate.public', 'poll.add', 'poll.delete', 'poll.vote', 'syllabus.delete', 'syllabus.draft.change', 'syllabus.draft.new', 'syllabus.post.change', 'syllabus.post.new', 'syllabus.read') THEN SUBSTR(REF, INSTR(REF, '/', 1, 2) + 1, INSTR(REF, '/', 1, 3) - INSTR(REF, '/', 1, 2) - 1)
WHEN EVENT in ('pres.begin', 'pres.end') THEN SUBSTR(REF, INSTR(REF, '/', 1, 2) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 2) - 9)
WHEN EVENT in ('realm.add', 'realm.del', 'realm.upd', 'realm.upd.own') and substr(REF, 1, 16) = '/realm//content/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 5) + 1, INSTR(REF, '/', 1, 6) - INSTR(REF, '/', 1, 5) - 1)
WHEN EVENT in ('realm.add', 'realm.del', 'realm.upd', 'realm.upd.own') and SUBSTR(REF, 50, 7) = '/group/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, INSTR(REF, '/', 1, 5) - INSTR(REF, '/', 1, 4) - 1)
WHEN EVENT in ('realm.add', 'realm.del', 'realm.upd', 'realm.upd.own') and SUBSTR(REF, 35, 7) = '/group/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, INSTR(REF, '/', 1, 5) - INSTR(REF, '/', 1, 4) - 1)
WHEN EVENT in ('realm.add', 'realm.del', 'realm.upd', 'realm.upd.own') and substr(REF, 1, 13) = '/realm//site/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 4))
WHEN EVENT in ('site.add', 'site.del', 'site.upd', 'site.upd.site.mbrshp') THEN SUBSTR(REF, INSTR(REF, '/', 1, 2) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 2))
ELSE ''
END
FROM sakai_event_archive
where site_id is null

Step 4:
At this point, our Operations team worked on turning these queries into a process that could be run on the sakai_event_archive table. This process will also be used during the daily archive activities when the production event table is archived. Below is the PL/SQL used for this process:

declare
i number;
begin
for si in (
select SAKAI_EVENT_ARCHIVE.rowid e_rowid,gb_gradebook_t.gradebook_uid site_id
from SAKAI_EVENT_ARCHIVE, GB_GRADEBOOK_T
where to_char(gb_gradebook_t.id) = to_char(substr(REF, INSTR(REF, '/', 1, 2) + 1, INSTR(REF, '/', 1, 3) - INSTR(REF, '/', 1, 2) - 1))
and event_date>=to_date('03/01/2008','mm/dd/yyyy')
and EVENT in ('gradebook.comment', 'gradebook.deleteItem', 'gradebook.downloadCourseGrade', 'gradebook.downloadRoster', 'gradebook.importItem', 'gradebook.newItem', 'gradebook.updateCourseGrades', 'gradebook.updateItemScores')
) loop
update SAKAI_EVENT_ARCHIVE set site_id=si.site_id where rowid=si.e_rowid;
i:=i+1;
if mod(i,10000)=0 then
  commit;
end if;
end loop;
commit;
end;
/



declare
i number;
begin
for si in (
select SAKAI_EVENT_ARCHIVE.rowid e_rowid,mneme_assessment.context site_id
from SAKAI_EVENT_ARCHIVE, mneme_assessment
where to_char(mneme_assessment.id) = to_char(substr(REF, INSTR(REF, '/', 1, 3) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 3)))
and SUBSTR(REF, 1, 18) = '/mneme/assessment/'
and event_date>=to_date('03/01/2008','mm/dd/yyyy')
) loop
update SAKAI_EVENT_ARCHIVE set site_id=si.site_id where rowid=si.e_rowid;
i:=i+1;
if mod(i,10000)=0 then
  commit;
end if;
end loop;
commit;
end;
/


declare
i number;
begin
for si in (
select SAKAI_EVENT_ARCHIVE.rowid e_rowid,mneme_pool.context site_id
from SAKAI_EVENT_ARCHIVE, mneme_pool
where to_char(mneme_pool.id) = to_char(substr(REF, INSTR(REF, '/', 1, 3) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 3)))
and SUBSTR(REF, 1, 12) = '/mneme/pool/'
and event_date>=to_date('03/01/2008','mm/dd/yyyy')
) loop
update SAKAI_EVENT_ARCHIVE set site_id=si.site_id where rowid=si.e_rowid;
i:=i+1;
if mod(i,10000)=0 then
  commit;
end if;
end loop;
commit;
end;
/


declare
i number;
begin
for si in (
select SAKAI_EVENT_ARCHIVE.rowid e_rowid,mneme_question.context site_id
from SAKAI_EVENT_ARCHIVE, mneme_question
where to_char(mneme_question.id) = to_char(substr(REF, INSTR(REF, '/', 1, 3) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 3)))
and SUBSTR(REF, 1, 16) = '/mneme/question/'
and event_date>=to_date('03/01/2008','mm/dd/yyyy')
) loop
update SAKAI_EVENT_ARCHIVE set site_id=si.site_id where rowid=si.e_rowid;
i:=i+1;
if mod(i,10000)=0 then
  commit;
end if;
end loop;
commit;
end;
/


declare
i number;
begin
for si in (
select SAKAI_EVENT_ARCHIVE.rowid e_rowid, mneme_submission.context site_id
from SAKAI_EVENT_ARCHIVE, mneme_submission
where to_char(mneme_submission.id) = to_char(
decode(INSTR(REF, ':'), 0, substr(REF, INSTR(REF, '/', 1, 3) + 1),
                        substr(REF, INSTR(REF, '/', 1, 3) + 1, LENGTH(REF) - INSTR(REF, ':') - 1)))
and SUBSTR(REF, 1, 18) = '/mneme/submission/'
and event_date>=to_date('03/01/2008','mm/dd/yyyy')
) loop
update SAKAI_EVENT_ARCHIVE set site_id=si.site_id where rowid=si.e_rowid;
i:=i+1;
if mod(i,10000)=0 then
  commit;
end if;
end loop;
commit;
end;
/



declare
i number;
begin
for si in (
SELECT SAKAI_EVENT_ARCHIVE.rowid e_rowid,
CASE
WHEN EVENT in ('annc.delete.any', 'annc.delete.own', 'annc.new', 'annc.revise.any', 'annc.revise.own', 'annc.schInv.notify', 'asn.delete.assignment', 'asn.delete.assignmentcontent', 'asn.delete.submission', 'asn.grade.submission', 'asn.new.assignment', 'asn.new.assignmentcontent', 'asn.read', 'asn.read.assignment', 'asn.revise.assignment', 'asn.revise.assignmentcontent', 'asn.save.submission', 'asn.submit.submission', 'calendar.new', 'calendar.revise', 'chat.delete.any', 'chat.delete.channel', 'chat.delete.own', 'chat.new', 'disc.delete.any', 'disc.delete.category', 'disc.delete.own', 'disc.new', 'disc.new.category', 'disc.revise.own', 'mail.delete.any', 'mail.new', 'mail.revise.any', 'msgcntr.delete', 'msgcntr.new', 'msgcntr.read', 'msgcntr.response', 'msgcntr.revise', 'news.read', 'news.revise', 'schInv.delete', 'wiki.new', 'wiki.revise') THEN SUBSTR(REF, INSTR(REF, '/', 1, 3) + 1, INSTR(REF, '/', 1, 4) - INSTR(REF, '/', 1, 3) - 1)
WHEN EVENT in ('content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 18) = '/citation/content/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, INSTR(REF, '/', 1, 5) - INSTR(REF, '/', 1, 4) - 1)
WHEN EVENT in ('content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 14) = '/content/group' THEN SUBSTR(REF, INSTR(REF, '/', 1, 3) + 1, INSTR(REF, '/', 1, 4) - INSTR(REF, '/', 1, 3) - 1)
WHEN EVENT in ('content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 20) = '/content/attachment/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 3) + 1, INSTR(REF, '/', 1, 4) - INSTR(REF, '/', 1, 3) - 1)
WHEN EVENT in ('content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 14) = '/content/user/' THEN '~'\|\|SUBSTR(REF, INSTR(REF, '/', 1, 3) + 1, INSTR(REF, '/', 1, 4) - INSTR(REF, '/', 1, 3) - 1)
WHEN EVENT in ('dis.dis.add', 'dis.dis.upd', 'dis.grp.add', 'dis.info.upd', 'dis.path.add', 'dis.path.del', 'dis.path.upd', 'dis.status.add', 'dis.status.del', 'dis.status.upd', 'dis.step.add', 'dis.step.del', 'dis.step.upd') THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, INSTR(REF, '/', 1, 5) - INSTR(REF, '/', 1, 4) - 1)
WHEN EVENT in ('podcast.generate.private', 'podcast.generate.public', 'poll.add', 'poll.delete', 'poll.vote', 'syllabus.delete', 'syllabus.draft.change', 'syllabus.draft.new', 'syllabus.post.change', 'syllabus.post.new', 'syllabus.read') THEN SUBSTR(REF, INSTR(REF, '/', 1, 2) + 1, INSTR(REF, '/', 1, 3) - INSTR(REF, '/', 1, 2) - 1)
WHEN EVENT in ('pres.begin', 'pres.end') THEN SUBSTR(REF, INSTR(REF, '/', 1, 2) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 2) - 9)
WHEN EVENT in ('realm.add', 'realm.del', 'realm.upd', 'realm.upd.own') and substr(REF, 1, 16) = '/realm//content/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 5) + 1, INSTR(REF, '/', 1, 6) - INSTR(REF, '/', 1, 5) - 1)
WHEN EVENT in ('realm.add', 'realm.del', 'realm.upd', 'realm.upd.own') and SUBSTR(REF, 50, 7) = '/group/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, INSTR(REF, '/', 1, 5) - INSTR(REF, '/', 1, 4) - 1)
WHEN EVENT in ('realm.add', 'realm.del', 'realm.upd', 'realm.upd.own') and SUBSTR(REF, 35, 7) = '/group/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, INSTR(REF, '/', 1, 5) - INSTR(REF, '/', 1, 4) - 1)
WHEN EVENT in ('realm.add', 'realm.del', 'realm.upd', 'realm.upd.own') and substr(REF, 1, 13) = '/realm//site/' THEN SUBSTR(REF, INSTR(REF, '/', 1, 4) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 4))
WHEN EVENT in ('site.add', 'site.del', 'site.upd', 'site.upd.site.mbrshp') THEN SUBSTR(REF, INSTR(REF, '/', 1, 2) + 1, LENGTH(REF) - INSTR(REF, '/', 1, 2))
ELSE ''
END site_id
FROM SAKAI_EVENT_ARCHIVE where site_id is null
\--and event_date>=to_date('03/01/2008','mm/dd/yyyy')
) loop
update SAKAI_EVENT_ARCHIVE set site_id=si.site_id where rowid=si.e_rowid;
i:=i+1;
if mod(i,10000)=0 then
  commit;
end if;
end loop;
commit;
end;
/

 If others in the Sakai community have any questions about this process, please contact ctops_at_umich.edu

  • No labels

1 Comment

  1. See http://jira.sakaiproject.org/jira/browse/SAK-10801 for the JIRA to add the context id to the SAKAI_EVENT record on creation.