#set_tags(webct,webct 管理,sql)
RIGHT:[[CE6 admin tips]]

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

#contents

** コース一覧を取得 [#aaba6935]

 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
** セクション一覧を取得 [#ba88b55b]
 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

** 学期名一覧を取得 [#b5a1af9c]
 SELECT id,
   name,
   source_name,
   source_id
 FROM lc_term

** セクション内部IDから学期名を得る [#q979f6bb]
 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 からユーザ情報を得る [#wed5fb49]
 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 が存在するかを確認 [#qbabdc05]
 SELECT COUNT(*)
 FROM person
 WHERE deletestatus IS NULL
  AND demouser = 0
  AND webct_id = ?

** セクション内部IDから登録ユーザリストを取得 [#ze2ce046]
 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から登録学生数を取得 [#i9d9ecae]
 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 から、登録されているセクション一覧を取得 [#qc7d0fa1]
 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 = ?)

** グレードブックから特定の名称のカラムの値を抽出 [#c5281e44]
 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
 ;


** テーブルのフィールド定義 [#a8de212f]
 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