create grain qt version '1.01';

create table accessToVariant(
VariantID varchar(36) not null,
mode varchar(30) not null,
sid varchar(100) not null,
dateTo datetime not null,
primary key(VariantID,mode,sid)
);

/**TestingVariantAssign - назначение пользователю теста. */
create table TestingVariantAssign (
VariantID varchar(36) not null primary key,
AlgorithmTestingID int not null,
PackageID varchar(36) not null,
LessonBRSCurriculumOccupancyID varchar(36),
DateAssign datetime not null,
WhenAdd datetime not null,
WhenEdit datetime,
WhenSend datetime,
RecStatus varchar(3) not null default 'add',
RecSend varchar(3) not null default 'dnt',
UserName varchar(255) not null,
ServerName varchar(255) not null,
LessonBRSID varchar(36),
TeacherID varchar(21),
PersonLogin varchar(255),
personUid varchar(255),
cycleid varchar(36),
eokid int,
testtype int,
relatedelement varchar(255),
testelemid varchar(255),
sid varchar(255)
)WITH NO VERSION CHECK;


----------------------- Контент ---------------------

/** Контент - лекции, ... */
create table Content(
ContentID varchar(36) NOT NULL primary key,
TypeContentID int NOT NULL,
ContentActualityNumber int NOT NULL,
ContentRedactionNumber int NOT NULL,
ContentName varchar(255) NOT NULL,
ContentDate datetime NOT NULL,
StatusContentID int NOT NULL,
ContentURL varchar(255),
ContentComment varchar(255),
WhenAdd datetime NOT NULL,
WhenEdit datetime,
WhenSend datetime,
RecStatus varchar(3),
RecSend varchar(3),
UserName varchar(255),
ServerName varchar(255),
PackageID varchar(36),
fileName varchar(255),
attachFile blob
)WITH NO VERSION CHECK;

/** Значения атрибутов контента*/
create table Content_AttributeValue(
ContentID varchar(36) NOT NULL,
AttributeContentID int not null,
AttributeValue text not null,
WhenAdd datetime not null,
WhenEdit datetime,
WhenSend datetime,
RecStatus varchar(3),
RecSend varchar(3),
UserName varchar(255),
ServerName varchar(255),

primary key(ContentID, AttributeContentID)
)WITH NO VERSION CHECK;

/** Список аттрибутов контента*/
create table listAttributeContent (
AttributeContentID int not null primary key,
AttributeContentName varchar(255) not null,
AttributeContentDescription varchar(255) not null,
RecStatus varchar(3) NOT NULL

)WITH NO VERSION CHECK;

------------------------------------------

----- Тестирование (БТЗ) -----

/** Таблица с атрибутами тестов. */
create table QtiPackageAttribute(
AttributeID varchar(36) not null primary key,
AttributeName varchar(255) not null,
AttributeValue varchar(255) not null,
PackageID varchar(36) not null

)WITH NO VERSION CHECK;

/** Таблица с тестами. */
create table QtiPackage(
PackageID varchar(36) not null primary key,
PackageName varchar(255) not null,
SourceName varchar(255),
QtiArchive blob,
ConversionStatus int not null default 0,
PackageVersion int not null default 1,
DateCreated datetime,
/** статус (Формируется, Сформирован) */
Status varchar(255),
/** ID редактора БТЗ */
EditorID varchar(255),
/** для чего разрабытывается */
for_what_dev text,
/** автор (отв. составитель) */
author varchar(255),
/** внешний автор */
extauthor varchar(255),
/** кафедра */
cath_id varchar(36),
/** специальности */
spec_id varchar(600),
/** утвержден автором */
confirm_author BIT default 0,
/** утвержден рецензентом */
confirm_reviewer BIT default 0,
/** использование для самоподготовки */
use_for_self bit default 0,
/** использование для контроля */
use_for_control bit default 1

)WITH NO VERSION CHECK;

/** Деструкторы */
create table QtiDestructor(
DestructorID varchar(36) not null primary key,
DestructorHtml text,
DestructorImg varchar(36),
QuestionID varchar(36) not null,
AnswerText text,
IsTrue bit,
DestructorHtml2 text,
DestructorOrder int

)WITH NO VERSION CHECK;

/** Вопросы */
create table QtiQuestion(
QuestionID varchar(36) not null primary key,
QuestionHtml text,
QuestionImg varchar(36),
QuestionType int not null,
ThemeID varchar(36) not null,
QuestionHtml2 text,
QuestionOrder int

)WITH NO VERSION CHECK;

/** Ресурсы: картинки */
create table QtiResource(
ResourceID varchar(36) not null primary key,
ResourceData blob not null,
ResourceName varchar(50) not null

)WITH NO VERSION CHECK;

/** Темы вопросов */
create table QtiTheme(
ThemeID varchar(36) not null primary key,
ThemeName varchar(255) not null,
ParentTheme varchar(36),
PackageID varchar(36),
ThemeOrder int

)WITH NO VERSION CHECK;

/** Вариант теста */
create table QtiVariant(
VariantID varchar(36) not null primary key,
PackageID varchar(36) not null,
VariantName varchar(255) not null,
DateCreated datetime not null,
VariantComment varchar(255)

)WITH NO VERSION CHECK;

/** Результат ответов пользователя на вариант теста. */
create table QtiVariantAnswer (
VariantAnswerID varchar(36) not null primary key,
VariantID varchar(36) not null,
TimeBegin datetime,
TimeEnd datetime,
Score int,
TestingMark int,
TimeFinish datetime,
PositiveCount int

)WITH NO VERSION CHECK;

/** Вариант-деструктор */
create table QtiVariantDestructor (
VariantDestructorID varchar(36) not null primary key,
VariantQuestionID varchar(36) not null,
DestructorID varchar(36) not null,
DestructorNumber int

)WITH NO VERSION CHECK;

/** Вариант-деструктор-ответ */
create table QtiVariantDestructorAnswer (
VariantDestructorAnswerID varchar(36) not null primary key,
VariantDestructorID varchar(36) not null,
DestructorID varchar(36),
DestructorText varchar(255),
IsTrue bit,
AnswerIP varchar(20)

)WITH NO VERSION CHECK;

/** Вопросы в варианте */
create table QtiVariantQuestion (
VariantQuestionID varchar(36) not null primary key,
QuestionID varchar(36) not null,
QuestionNumber int,
VariantID varchar(36) not null,
CorrectAnswer bit,
HasAnswer bit,
QuestionType int
)WITH NO VERSION CHECK;

create table QtiVariantParams(
VariantID varchar(36) not null,
ParamID varchar(100) not null,
Value text,
primary key(VariantID,ParamID)
) with no version check;

CREATE TABLE webServiceAccess
(
  address varchar(255) NOT NULL,
  pin varchar(255) NOT NULL,
  PRIMARY KEY (address, pin)
) with no version check;