-- Oracle, get a list of all tables in the user schema
select table_name from user_tables;


-- MSSQL, get a list of all tables in the user schema
select * 
from information_schema.tables
where type = 'base Table';


-- describe a table
describe <table_name>





-- Course Usage
-- -- Gradebook
select cm.course_id 
from course_main cm, (
  select crsmain_pk1
  from gradebook_main 
  group by crsmain_pk1
  having count(*) > 2;
) outer
where outer.crsmain_pk1=cm.pk1
;


-- -- Email (warning, possibly very slow)
select cm.course_id
from course_main cm, (
  select course_pk1
  from activity_accumulator
  where internal_handle like 'cp_send_email_%'
  group by course_pk1
  having count(*) > 5
)outer
where outer.course_pk1=cm.pk1
;

-- -- Dropbox
select distinct(u.email)
from course_user_uploads cuu
       ,course_users cu
       ,users u 
where cuu.course_users_pk1=cu.pk1 
and cu.users_pk1=u.pk1
and cu.role='P';

-- -- Assignments
select cm.course_id
from course_main cm, (
   select distinct(cc.crsmain_pk1)
   from course_contents cc
   where cnthndlr_handle='resource/x-bb-assignment'
) inner
where inner.crsmain_pk1=cm.pk1
;




-- Disable Course Tools
-- select all possible internal_handles, inspect list and write down
-- desired handles
select distinct(internal_handle) 
from course_navigation_item;

-- replace internal_handle and run to make tool unavailable.
update course_navigation_item
set enabled_ind='N'
where internal_handle='drop_box'
or internal_handle='cp_digital_dropbox';



-- Swap Portal Modules
-- List all portal modules on system
select pk1, title from module;

-- replace 
update module_layout
set module_pk1=<new_pk1>
where module_pk1=<old_pk1>;



-- Students not enrolled in any course
-- Output with name and email address
select out.email, out.firstname, out.lastname 
from
(
   select ui.pk1 from users ui
   minus
   select distinct(cu.users_pk1)
   from course_users cu
) inner, users out
where inner.pk1=out.pk1
;

-- output suitable for batch remove students text file
select out.user_id || ',' || out.lastname 
from
(
   select ui.pk1 from users ui
   minus
   select distinct(cu.users_pk1)
   from course_users cu
) inner, users out
where inner.pk1=out.pk1
;




-- Change a lot of passwords(Oracle specific)
-- This only work if passwords are stored locally, not LDAP, Active 
-- Directory, or other custom authentication schemes.
-- Also only work if system not running in SSL mode.  
declare
   v_input varchar2(2000) := 'EED6215';
   hexkey    varchar2(32) := null;
begin
   hexkey := rawtohex(dbms_obfuscation_toolkit.md5(
                           input => utl_raw.cast_to_raw(v_input))); 

   update users set passwd=hexkey where user_id='glparker';

end;
/




-- Student Course Activity by Month
-- Replace ? for user_pk1 and course_pk1 with appropriate 
-- user and course primary keys
select to_char(to_date(c.thisdate,'YYYYMM'), 'Month YY') as month, count(*)-1 as hits 
from (
     select to_char(aa.timestamp,'YYYYMM') as thisdate 
     from activity_accumulator aa
     where aa.timestamp > sysdate-365 
     and aa.user_pk1=? 
     and aa.course_pk1=?
       union all 
     select to_char(b.testdate,'YYYYMM') as thisdate 
     from (
          select add_months(sysdate-365,a.rnum -1) as testdate
          from ( 
               select rownum rnum from course_main where rownum <= 12
          )a 
     )b 
)c 
group by to_char(to_date(c.thisdate,'YYYYMM'), 'Month YY'), c.thisdate 
order by c.thisdate;



-- Add Index on activity_accumulator
CREATE INDEX activity_accumulator_usf_ie1
ON activity_accumulator (course_pk1, user_pk1)
TABLESPACE bb_bb60_indx;