/*
--  CopyRight(C) 2003，NTT All rights reserved
--
--  XMS2データベーステーブルの作成
--
--  スクリプト概要

--      XMS2データベースのテーブルを新規に作成します

--
--  変更履歴
--     2004.01.08  新規作成 箕輪英司
--     2004.02.01  変更     野沢智也
--                 SCORM LO 追加に伴うテーブル追加
*/

CREATE TABLE PropagateRelation (
    LOID char (10) NULL ,
    RelatedID char (10) NULL
);

CREATE TABLE AiccComment (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    course_id varchar (255) NULL ,
    student_id char (10) NULL ,
    lesson_id varchar (255) NULL ,
    "DATE" timestamp NULL ,
    time char (16) NULL ,
    location varchar (255) NULL ,
    "COMMENT" varchar (4096) NULL
);

CREATE TABLE AiccCore (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    Lesson_Status varchar (30) NULL ,
    RawScore float NULL ,
    MaxScore float NULL ,
    MinScore float NULL ,
    Time char (16) NULL ,
    Lesson_Location varchar (255) NULL ,
    exist char (10) NULL
);

CREATE TABLE SectionLog (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    Lesson_Status varchar (30) NULL ,
    Time char (16) NULL
);


CREATE TABLE AiccInteraction (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    course_id varchar (255) NULL ,
    student_id char (10) NULL ,
    lesson_id varchar (255) NULL ,
    response_times int NOT NULL ,
    "DATE" timestamp NULL ,
    time char (16) NULL ,
    interaction_id varchar (255) NULL ,
    objective_id varchar (255) NULL ,
    type_interaction char (30) NULL ,
    correct_response varchar (4000) NULL ,
    response_value char (30) NULL ,
    student_response varchar (255) NULL ,
    result char (30) NULL ,
    weighting float NULL ,
    latency char (16) NULL
);


CREATE TABLE AiccObjectives (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    course_id varchar (255) NULL ,
    student_id char (10) NULL ,
    lesson_id varchar (255) NULL ,
    "DATE" timestamp NULL ,
    time char (16) NULL ,
    objective_id varchar (255) NULL ,
    RawScore float NULL ,
    MaxScore float NULL ,
    MinScore float NULL ,
    status char (30) NULL ,
    mastery_time char (16) NULL
);


CREATE TABLE Assignments (
    assignID char (10) NOT NULL ,
    memberShipID char (30) NULL ,
    materialID char (20) NULL ,
    study_start_date timestamp NULL ,
    enddate timestamp NULL ,
    assigner char (15) NULL ,
    learn_authority char (1) NULL ,
    management_authority char (1) NULL ,
    opening_date timestamp NULL ,
    closing_date timestamp NULL ,
    study_limit_cnt int NULL ,
    study_cnt int NULL ,
    method_flag char (1) NULL 
);


CREATE TABLE ConferenceLo (
    LOID char (10) NOT NULL ,
    UrlPath varchar (256) NOT NULL ,
    MeetingTitle varchar (64) NOT NULL ,
    StartDate timestamp NOT NULL ,
    EndDate timestamp NOT NULL ,
    ResourceMail varchar (128) NOT NULL ,
    LogonUser varchar (128) NOT NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE Evaluate (
    user_id char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    score_date timestamp NOT NULL ,
    score int NULL ,
    custom_msg_dir varchar (256) NULL
);


CREATE TABLE ExchangeLo (
    LOID char (10) NOT NULL ,
    UrlPath varchar (256) NOT NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE Face2FaceLo (
    LOID char (10) NOT NULL ,
    TeacherID char (16) NULL
);


CREATE TABLE GroupAndPeople (
    memberShipID char (30) NOT NULL ,
    sort_no int DEFAULT 0 NOT NULL ,
    parentID char (15) NOT NULL ,
    childID char (15) NOT NULL
);


CREATE TABLE Groups (
    group_id char (8) NOT NULL ,
    group_name varchar (64) NULL ,
    group_type char (4) NOT NULL ,
    delete_flag char (1) NULL ,
    foreignID varchar (256) NULL ,
    custom_msg_dir varchar (128) NULL ,
    "OID" varchar (256) NULL ,
    ad_dn varchar (256) NULL
);


CREATE TABLE LO (
    LOID char (10) NOT NULL ,
    LOType char (3) NULL ,
    LOTitle varchar (64) NULL ,
    Loexplanation varchar (256) NULL ,
    foreignID varchar (256) NULL ,
    KeyWord varchar (256) NULL ,
    delete_flag char (1) NULL ,
    LOVersion varchar (10) NULL ,
    conclusion varchar (1024) NULL ,
    RealLOID char (10) NOT NULL ,
    Prere varchar (1024) NULL,
    MentorEmailAddress varchar (256) NULL
);


CREATE TABLE LOLog (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    study_start_date timestamp NULL ,
    residanceTime float NULL ,
    passday timestamp NULL ,
    study_end_date timestamp NULL ,
    highScore float NULL ,
    lowsScore float NULL ,
    execise_point float NULL ,
    fullscore float NULL,
    Access_cnt int NULL,
    last_access timestamp NULL
);


CREATE TABLE LOLogSession (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    study_start_date timestamp NULL ,
    residanceTime float NULL ,
    passday timestamp NULL ,
    study_end_date timestamp NULL ,
    highScore float NULL ,
    lowsScore float NULL ,
    execise_point float NULL ,
    fullscore float NULL,
    Lunchtimes int DEFAULT 0
);


CREATE TABLE LORelation (
    materialID char (20) NOT NULL ,
    sort_no int DEFAULT 0 NOT NULL ,
    parentID char (10) NULL ,
    childID char (10) NULL
);


CREATE TABLE LOType (
    LOType char (3) NOT NULL ,
    TypeName varchar (64) NULL ,
    LoSubType char (3) NULL
);


CREATE TABLE OfficeLo (
    LOID char (10) NOT NULL ,
    LOUrl varchar (256) NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE People (
    user_id char (8) NOT NULL ,
    user_name varchar (64) NULL ,
    encrypt_password char (32) NULL ,
    contact_address varchar (256) NULL ,
    sex char (1) NULL ,
    contact_email varchar (128) NULL ,
    contact_email2 varchar (128) NULL ,
    reading_name varchar (64) NULL ,
    birthday timestamp NULL ,
    contact_tel char (13) NULL ,
    custom_msg_dir varchar (128) NULL ,
    delete_flag char (1) NULL ,
    foreignID varchar (256) NULL ,
    loginID varchar (16) NULL ,
    ad_user_id varchar (255) NULL ,
    user_type char (4) NOT NULL ,
    "OID" varchar (256) NULL ,
    ad_dn varchar (256) NULL
);


CREATE TABLE RoleAssignments (
    Role_assign_id char (15) NOT NULL ,
    user_id char (8) NULL ,
    Role_id char (10) NULL ,
    GroupID char (8) NULL
);


CREATE TABLE Roles (
    RoleID char (10) NOT NULL ,
    RoleName varchar (64) NULL ,
    Deleted char (1) NULL
);


CREATE TABLE XmoRelation (
    xmo_id char (15) NOT NULL ,
    related_id char (15) NOT NULL
);


CREATE TABLE RolesFunction (
    RoleID char (10) NOT NULL ,
    Function char (15) NOT NULL
);


CREATE TABLE SPSLo (
    LOID char (10) NOT NULL ,
    UrlPath varchar (256) NOT NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE ServerInformation (
    server_id char (8) NOT NULL ,
    server_name varchar (128) NULL ,
    ip_address char (15) NULL ,
    os_kind char (1) NULL ,
    server_kind char (1) NULL ,
    access_account varchar (32) NULL ,
    access_password varchar (32) NULL ,
    proxy_server_name varchar (32) NULL ,
    access_timeout int NULL ,
    http_port_number int NULL ,
    ftp_port_number int NULL ,
    protocol_kind int NULL ,
    access_type int NULL ,
    url_host_name varchar (32) NULL ,
    virtual_dir_name varchar (128) NULL ,
    W3S_interface char (1) NULL
);


CREATE TABLE LaunchForgetLo (
    LOID char (10) NOT NULL ,
    LOUrl varchar (256) NOT NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE SmartForceLo (
    LOID char (10) NOT NULL ,
    CourseCode varchar (255) NOT NULL
);


CREATE TABLE URLLo (
    LOID char (10) NOT NULL ,
    LOUrl varchar (256) NOT NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE WbtLo (
    LOID char (10) NOT NULL ,
    materialID varchar (32) NOT NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE XMOEntry (
    xmo_id char (15) NOT NULL ,
    xmo_name varchar (64) NULL ,
    xmo_out_line varchar (100) NULL ,
    xmo_uri varchar (100) NULL ,
    xmo_on_flg char (1) DEFAULT '1' NULL ,
    class_name varchar (256) NULL ,
    tree_view_type char (1) NULL ,
    ini_menu varchar (64) NULL ,
    priority int NULL
);


CREATE TABLE PerceptionLo (
    LOID char (10) NOT NULL ,
    assessment_id varchar (16) NOT NULL ,
    session_name char (50) NOT NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE LOHistory (
    LOID char (10) NOT NULL ,
    time_stamp timestamp NOT NULL ,
    UserID char (8) NOT NULL
);


CREATE TABLE OL_AiccComment (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    course_id varchar (255) NULL ,
    student_id char (10) NULL ,
    lesson_id varchar (255) NULL ,
    "DATE" timestamp NULL ,
    time char (16) NULL ,
    location varchar (255) NULL ,
    "COMMENT" varchar (4096) NULL
);


CREATE TABLE OL_AiccCore (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    Lesson_Status varchar (30) NULL ,
    RawScore float NULL ,
    MaxScore float NULL ,
    MinScore float NULL ,
    Time char (16) NULL ,
    Lesson_Location varchar (255) NULL ,
    exist char (10) NULL
);


CREATE TABLE OL_SectionLog (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    Lesson_Status varchar (30) NULL ,
    Time char (16) NULL
);


CREATE TABLE OL_AiccInteraction (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    course_id varchar (255) NULL ,
    student_id char (10) NULL ,
    lesson_id varchar (255) NULL ,
    response_times int NOT NULL ,
    "DATE" timestamp NULL ,
    time char (16) NULL ,
    interaction_id varchar (255) NULL ,
    objective_id varchar (255) NULL ,
    type_interaction char (30) NULL ,
    correct_response varchar (4000) NULL ,
    response_value char (30) NULL ,
    student_response varchar (255) NULL ,
    result char (30) NULL ,
    weighting float NULL ,
    latency char (16) NULL
);


CREATE TABLE OL_AiccObjectives (
    UserID char (8) NOT NULL ,
    LOID char (10) NOT NULL ,
    SessionID char (128) NOT NULL ,
    "UID" char (255) NOT NULL ,
    course_id varchar (255) NULL ,
    student_id char (10) NULL ,
    lesson_id varchar (255) NULL ,
    "DATE" timestamp NULL ,
    time char (16) NULL ,
    objective_id varchar (255) NULL ,
    RawScore float NULL ,
    MaxScore float NULL ,
    MinScore float NULL ,
    status char (30) NULL ,
    mastery_time char (16) NULL
);


CREATE TABLE OfflineLo (
    LOID char (10) NOT NULL ,
    materialID varchar (32) NOT NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE CatalogLo (
    LOID char (10) NOT NULL ,
    w_height NUMERIC(4) NOT NULL ,
    w_width NUMERIC(4) NOT NULL
);


CREATE TABLE CatalogLo_LoList (
    LOID char (10) NOT NULL ,
    CatalogListLOID char (10) NOT NULL ,
    sort_no NUMERIC(4) NOT NULL
);


CREATE TABLE prerequisite (
    LOID char (10) NULL ,
    PrereID char (10) NULL
);


CREATE TABLE ScormLo (
	LOID 					char(10) NOT NULL ,
	materialID 				varchar(32) NOT NULL ,
	w_height 				int NOT NULL ,
	w_width 				int NOT NULL
);


CREATE TABLE Scorm_Attempt (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		int	NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			int NOT NULL,
	LearnSessionCount		int NOT NULL,
	SessionID				char(128) NOT NULL,
	LearnSession_Date		timestamp NOT NULL,
	Completion_Status 		varchar(30) NULL,
	ScaledScore 			numeric(17,7) NULL,
	RawScore 				numeric(17,7) NULL,
	MaxScore 				numeric(17,7) NULL,
	MinScore 				numeric(17,7) NULL,
	Attempt_Session_Time 	numeric(15,3) NULL,
	Success_Status 			varchar(30) NULL,
	location				varchar(1000) NULL,
	"exit"					varchar(10) NULL
);


CREATE TABLE Scorm_Interaction (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		int	NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			int NOT NULL,
	LearnSessionCount		int NOT NULL,
	InteractionCount 		int NOT NULL,
	SessionID				char(128) NOT NULL,
	InteractionID 			varchar(4096) NULL,
	InteractionType 		varchar(30) NULL,
	ObjectiveId 			varchar(4096) NULL,
	Timestamp 				timestamp NULL,
	CorrectResponse1 		varchar(4096) NULL,
	CorrectResponse2 		varchar(4096) NULL,
	CorrectResponse3 		varchar(4096) NULL,
	CorrectResponse4 		varchar(4096) NULL,
	CorrectResponse5 		varchar(4096) NULL,
	Weighting 				numeric(17,7) NULL,
	LearnerResponse 		varchar(4096) NULL,
	Learner_Result 			varchar(30) NULL,
	Latency	 				numeric(15,3) NULL,
	Description 			varchar(255) NULL
);


CREATE TABLE Scorm_LearnerComment (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		int NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			int NOT NULL,
	LearnSessionCount		int NOT NULL,
	LearnerCommentCount 	int NOT NULL,
	SessionID				char(128) NOT NULL,
	LearnerComment 			varchar(4096) NULL,
	LearnerCommentLocation 	varchar(255) NULL,
	LearnerCommentTime 		varchar(255) NULL
);


CREATE TABLE Scorm_LMSComment (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		int NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			int NOT NULL,
	LearnSessionCount		int NOT NULL,
	LMSCommentCount 		int NOT NULL,
	SessionID				char(128) NOT NULL,
	LMSComment 				varchar(4096) NULL,
	LMSCommentLocation 		varchar(255) NULL,
	LMSCommentTime 			varchar(255) NULL
);


CREATE TABLE Scorm_Objective (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		int NOT NULL,
	ActivityID 				char(255) NOT NULL,
	AttemptCount 			int NOT NULL,
	LearnSessionCount		int NOT NULL,
	ObjectiveCount			int NOT NULL,
	SessionID				char(128) NOT NULL,
	ObjectiveId				varchar(4096) NULL,
	ScaledScore 			numeric(17,7) NULL,
	RawScore 				numeric(17,7) NULL,
	MaxScore 				numeric(17,7) NULL,
	MinScore 				numeric(17,7) NULL,
	SuccessStatus			varchar(30) NULL,
	CompletionStatus		varchar(30) NULL
);


CREATE TABLE ScormSession (
	UserID 					char(8) NOT NULL,
	LOID 					char(10) NOT NULL,
	StudySessionCount 		int NOT NULL,
	SessionID	 			char(128) NOT NULL
);


