CE6 SQL Snippets

Oracle のデータベースへ直接問合せを発行する事で、WebCT CE6 に関する情報を得る事ができる。もちろん保証された使い方ではないので At your own risk で。

コース一覧を取得

SELECT id,                   -- 内部ID
  source_name,               -- IMS Source
  source_id,                 -- IMS ID
  name,                      -- コースタイトル
  create_ts,                 -- 作成日時
  lastmodify_ts              -- 更新日時
FROM learning_context
WHERE deleted_flag IS NULL
 AND type_code = 'Course'
ORDER BY name

セクション一覧を取得

SELECT s.id,        -- セクションの内部ID
  s.source_name,    --             IMS source
  s.source_id,      --             IMS ID
  s.name,           --             名称
  s.create_ts,      --             作成日時
  s.lastmodify_ts,  --             更新日時
  p.id,             -- 親コースの内部ID
  p.source_name,    --           IMS source
  p.source_id,      --           IMS ID
  p.name            --           名称
FROM learning_context s,
  learning_context p
WHERE s.type_code = 'Section'
 AND s.deleted_flag IS NULL
 AND s.parent_id = p.id
ORDER BY s.id

学期名一覧を取得

SELECT id,
  name,
  source_name,
  source_id
FROM lc_term

セクション内部IDから学期名を得る

SELECT lc_term.name,
  lc_term.id
FROM lc_term_mapping JOIN lc_term ON(lc_term_mapping.lc_term_id = lc_term.id)
WHERE assigned_lcid = ?

WebCT ID からユーザ情報を得る

SELECT sourcedid_source,
  remote_userid,
  webct_id,
  name_n_given,
  name_n_family,
  email,
  org
FROM
  (SELECT *
   FROM person
   WHERE deletestatus IS NULL
   AND demouser = 0
   AND defaultuser = 0
   AND learning_context_id IN
    (SELECT id
     FROM learning_context
     WHERE type_code = 'Institution')
  AND webct_id = ?)
t LEFT JOIN
  (SELECT person_id,
     short_string_value AS
   org
   FROM col_value
   WHERE col_meta_id = ?)
t2 ON t.id = t2.person_id

WebCT ID が存在するかを確認

SELECT COUNT(*)
FROM person
WHERE deletestatus IS NULL
 AND demouser = 0
 AND webct_id = ?

セクション内部IDから登録ユーザリストを取得

SELECT p.webct_id,         -- ユーザの WebCT ID
  p.name_n_given,          --          姓
  p.name_n_family,         --          名
  role.role_definition_id  --          役割
FROM role,
  member,
  person p
WHERE role.member_id = member.id
 AND member.delete_status = 0
 AND role.delete_status = 0
 AND p.demouser = 0
 AND member.learning_context_id = ?
 AND p.id = member.person_id
ORDER BY p.webct_id

役割は以下のいずれか。複数の役割を持つ場合は、複数レコードが帰る。

  10010 - デザイナ
  10011 - TA
  10012 - 教師
  10013 - 学生

セクション内部IDから登録学生数を取得

SELECT COUNT(*)
FROM role,
  member,
  person p
WHERE role.member_id = member.id
 AND member.delete_status = 0
 AND role.delete_status = 0
 AND p.demouser = 0
 AND member.learning_context_id = ?
 AND role_definition_id = 10013
 AND p.id = member.person_id
ORDER BY p.webct_id

WebCT ID から、登録されているセクション一覧を取得

SELECT lc.id,                 -- セクション内部ID
  role.role_definition_id,    -- 役割
  role.primary_flag,          -- 役割が教員の場合、プライマリか
  member.status_flag,         -- アクセス可否
  lc.name                     -- セクション名称
FROM role,
  member,
  learning_context lc
WHERE role.member_id = member.id
 AND member.learning_context_id = lc.id
 AND lc.type_code = 'Section'
 AND lc.deleted_flag IS NULL
 AND member.delete_status = 0
 AND role.delete_status = 0
 AND member.person_id =
  (SELECT id
   FROM person
   WHERE deletestatus IS NULL
   AND webct_id = ?)

グレードブックから特定の名称のカラムの値を抽出

SELECT 
--  g.id,                 -- grade book 内部ID
  g.section_id,           -- セクションID
  lc.name,                -- セクション名
--  g.section_column_id,  -- grade book 内部カラムID
  g.section_column_label, -- カラム名
--  g.member_person_id,   -- gradebook 内部学生ID
  p.webct_id,             -- 学生の WebCT ID
  g.member_first_name,    -- 学生の名
  g.member_last_name,     -- 学生の姓
  v.new_value             -- カラムの値
FROM grade_book_audit_log g 
  JOIN person p ON g.member_person_id=p.id 
  JOIN grade_book_audit_log_value v ON g.id=v.id
  JOIN learning_context lc ON g.section_id=lc.id
WHERE (g.section_column_id,g.member_person_id,g.lastmodify_ts) IN (
    SELECT section_column_id,
	  member_person_id,
	  max(lastmodify_ts) as lastmodify_ts
    FROM grade_book_audit_log
    WHERE section_id IN
      (セクションIDのリスト)
      AND section_column_label='カラム名'
    GROUP BY section_column_id,member_person_id
  )
ORDER BY g.section_id,p.webct_id
;

テーブルのフィールド定義

ROLE
   	"ID" NUMBER(20,0) NOT NULL ENABLE, 
	"MEMBER_ID" NUMBER(20,0) NOT NULL ENABLE, 
	"ROLE_DEFINITION_ID" NUMBER(20,0) NOT NULL ENABLE, 
	"PRIMARY_FLAG" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"DELETE_STATUS" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"ROLE_STATUS" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, 
	"CREATE_TS" NUMBER(20,0) NOT NULL ENABLE, 
	"LASTMODIFY_TS" NUMBER(20,0) NOT NULL ENABLE, 

MEMBER
   	"ID" NUMBER(20,0) NOT NULL ENABLE, 
	"PERSON_ID" NUMBER(20,0) NOT NULL ENABLE, 
	"LEARNING_CONTEXT_ID" NUMBER(20,0) NOT NULL ENABLE, 
	"CREATE_TS" NUMBER(20,0) NOT NULL ENABLE, 
	"LASTMODIFY_TS" NUMBER(20,0) NOT NULL ENABLE, 
	"STATUS_FLAG" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, 
	"START_RESTRICT" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"END_RESTRICT" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"DELETE_STATUS" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"HIDDEN_LC" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"DISPLAY_ORDER" NUMBER(16,4) DEFAULT 0 NOT NULL ENABLE, 
	"STARTDATE_TIME" NUMBER(20,0), 
	"ENDDATE_TIME" NUMBER(20,0), 
	"CHILD_MEMBER_ID" NUMBER(20,0), 


LEARNING_CONTEXT
	"ID" NUMBER(20,0) NOT NULL ENABLE, 
	"NAME" VARCHAR2(1000 BYTE) NOT NULL ENABLE, 
	"TYPE_CODE" VARCHAR2(128 BYTE) NOT NULL ENABLE, 
	"SOURCE_ID" VARCHAR2(768 BYTE) NOT NULL ENABLE, 
	"SOURCE_NAME" VARCHAR2(510 BYTE) NOT NULL ENABLE, 
	"GLCID" VARCHAR2(256 BYTE) NOT NULL ENABLE, 
	"STATUS_TIMESTAMP" NUMBER(20,0) NOT NULL ENABLE, 
	"DELETABLE_FLAG" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, 
	"ENROLLACCEPT_FLAG" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, 
	"ENROLLALLOWED_FLAG" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, 
	"NAME_LOWERCASE" VARCHAR2(1000 BYTE) NOT NULL ENABLE, 
	"LICENSE_GENERATED" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"CREATE_TS" NUMBER(20,0) NOT NULL ENABLE, 
	"LASTMODIFY_TS" NUMBER(20,0) NOT NULL ENABLE, 
	"START_RESTRICT" NUMBER(1,0), 
	"END_RESTRICT" NUMBER(1,0), 
	"PARENT_ID" NUMBER(20,0), 
	"DESCRIPTION" CLOB, 
	"DELETED_FLAG" NUMBER(20,0), 
	"STATUS" NUMBER(1,0), 
	"FULLDESCRIPTION" CLOB, 
	"ORGNAME" VARCHAR2(768 BYTE), 
	"ORGUNIT" VARCHAR2(768 BYTE), 
	"ORGTYPE" VARCHAR2(96 BYTE), 
	"ORGID" VARCHAR2(768 BYTE), 
	"ADMINPERIOD" VARCHAR2(96 BYTE), 
	"EMAIL" VARCHAR2(320 BYTE), 
	"URL" VARCHAR2(1000 BYTE), 
	"DATASOURCE" VARCHAR2(768 BYTE), 
	"LASTARCHIVED" NUMBER(20,0), 
	"HOMEFOLDER_ID" NUMBER(20,0), 
	"STARTDATE_TIME" NUMBER(20,0), 
	"ENDDATE_TIME" NUMBER(20,0), 
	"DELIVERY_UNIT_TYPE" VARCHAR2(20 BYTE), 
	"INST_LCID" NUMBER(20,0), 
	
PERSON
	"ID" NUMBER(20,0) NOT NULL ENABLE, 
	"LEARNING_CONTEXT_ID" NUMBER(20,0) NOT NULL ENABLE, 
	"WEBCT_ID" VARCHAR2(768 BYTE) NOT NULL ENABLE, 
	"WEBCT_ID_LOWERCASE" VARCHAR2(768 BYTE) NOT NULL ENABLE, 
	"CREATE_TS" NUMBER(20,0) NOT NULL ENABLE, 
	"LASTMODIFY_TS" NUMBER(20,0) NOT NULL ENABLE, 
	"ACTIVESTATUS" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE, 
	"DEFAULTUSER" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"ENABLE_EXTERNAL_MAIL" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"DEMOUSER" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"FTIME_LOGIN_PWREQ_OK" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
	"REMOTE_USERID" VARCHAR2(768 BYTE) NOT NULL ENABLE, 
	"SOURCEDID_SOURCE" VARCHAR2(510 BYTE) NOT NULL ENABLE, 
	"SMS_GROUP_ID" NUMBER(20,0), 
	"WEBCT_PASSWORD" VARCHAR2(128 BYTE), 
	"NAME_N_FAMILY" VARCHAR2(768 BYTE), 
	"NAME_N_GIVEN" VARCHAR2(768 BYTE), 
	"NAME_N_OTHER" VARCHAR2(768 BYTE), 
	"NAME_N_PREFIX" VARCHAR2(96 BYTE), 
	"NAME_N_SUFFIX" VARCHAR2(96 BYTE), 
	"NAME_FN" VARCHAR2(768 BYTE), 
	"NAME_SORT" VARCHAR2(768 BYTE), 
	"NAME_NICKNAME" VARCHAR2(768 BYTE), 
	"HINT_QUESTION" VARCHAR2(1000 BYTE), 
	"HINT_ANSWER" VARCHAR2(300 BYTE), 
	"DEMOGRAPHICS_GENDER" VARCHAR2(64 BYTE), 
	"DEMOGRAPHICS_BDAY" NUMBER(20,0), 
	"EMAIL" VARCHAR2(320 BYTE), 
	"LASTLOGIN_SESSIONID" VARCHAR2(256 BYTE), 
	"LASTLOGINTIME" NUMBER(20,0), 
	"LASTLOGOUT_SESSIONID" VARCHAR2(256 BYTE), 
	"LASTLOGOUTTIME" NUMBER(20,0), 
	"DELETESTATUS" NUMBER(20,0), 
	"DATASOURCE" VARCHAR2(510 BYTE) DEFAULT 'WebCT' NOT NULL ENABLE, 
	"LASTPALMSYNCTIME" NUMBER(20,0), 
	"LAST_PW_CHANGE_TIME" NUMBER(20,0), 
	"HOMEFOLDER_ID" NUMBER(20,0), 
	"EXPIRATION_DATE" NUMBER(20,0), 
	"PORTFOLIO_ID" NUMBER(20,0),

トップ   差分 バックアップ リロード   一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2010-02-24 (水) 01:41:32 (2679d)