CREATE TABLE facilities (
	id int NOT NULL ,
	name char (32) NOT NULL ,
	class char (128) NOT NULL ,
	offline tinyint NOT NULL ,
	engineclass varchar (128) NULL 
)
GO


INSERT INTO facilities VALUES ( 1,    'default'      ,   'jmaber.building.Facility'             ,  0,       'jmaber.building.EngineSession'  )
GO
INSERT INTO facilities VALUES ( 2,    'query'        ,   'jmaber.building.QueryFacility'        ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 3,    'floor'        ,   'jmaber.building.FloorFacility'        ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 4,    'suite'        ,   'jmaber.building.SuiteFacility'        ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 5,    'room'         ,   'jmaber.building.RoomFacility'         ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 6,    'permission'   ,   'jmaber.building.PermissionFacility'   ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 7,    'document'     ,   'jmaber.building.DocumentFacility'     ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 9,    'employee'     ,   'jmaber.building.EmployeeFacility'     ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 10,   'communication',   'jmaber.building.CommunicationFacility',  0,       null  )
GO
INSERT INTO facilities VALUES ( 11,   'mcq'          ,   'jmaber.building.MCQFacility'          ,  0,       'jmaber.building.EngineSessionMCQ'  )
GO
INSERT INTO facilities VALUES ( 12,   'questionnaire',   'jmaber.building.QuestionnaireFacility',  0,       null  )
GO
INSERT INTO facilities VALUES ( 13,   'pigeonhole'   ,   'jmaber.building.PigeonHoleFacility'   ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 14,   'workshopsuite',   'jmaber.building.SuiteFacility'        ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 15,   'notification' ,   'jmaber.building.NotificationFacility' ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 16,   'textq'        ,   'jmaber.building.TextQFacility'        ,  0,       null )
GO
INSERT INTO facilities VALUES ( 17,   'cblstring'    ,   'jmaber.building.StringFacility'       ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 20,   'personalpage' ,   'jmaber.building.PersonalPageFacility' ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 21,   'mediadocument',   'jmaber.building.MediaDocumentFacility',  0,       null  )
GO
INSERT INTO facilities VALUES ( 22,   'link'         ,   'jmaber.building.LinkFacility'         ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 23,   'building'     ,   'jmaber.building.BuildingFacility'     ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 24,   'site'         ,   'jmaber.building.SiteFacility'         ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 25,   'alias'        ,   'jmaber.building.AliasFacility'        ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 26,   'user'         ,   'jmaber.building.UserCreationFacility' ,  0,       null  )
GO
INSERT INTO facilities VALUES ( 27,   'groupcontain' ,   'jmaber.building.GroupContainerFacility' ,  0,     null  )
GO
INSERT INTO facilities VALUES ( 28,   'directory'    ,   'jmaber.building.UserDirectoryFacility' ,  0,     null  )
GO
INSERT INTO facilities VALUES ( 29,   'messagingroom',   'jmaber.building.MessagingRoomFacility' ,  0,     null  )
GO
INSERT INTO facilities VALUES ( 30,   'recycler',        'jmaber.building.BuildingFacility'      ,  0,     null  )
GO
INSERT INTO facilities VALUES ( 31,   'heading',         'jmaber.building.HeadingFacility'       ,  0,     null  )
GO





CREATE TABLE dbo.document_pages (
	id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	ordinal_1 int NOT NULL DEFAULT (0),
	html LONG VARCHAR NULL ,
	title char (255) NOT NULL DEFAULT ('untitled'),
	ordinal_2 int NOT NULL DEFAULT (0),
	ordinal_3 int NOT NULL DEFAULT (0),
	ordinal_4 int NOT NULL DEFAULT (0),
	CONSTRAINT PK___1__11 PRIMARY KEY  CLUSTERED 
	(
		id
	)
)
GO

CREATE TABLE dbo.links (
	id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	url varchar (255) NOT NULL ,
	style tinyint NOT NULL ,
	who_created char (12) NOT NULL ,
	when_created TIMESTAMP NOT NULL 
)
GO

CREATE TABLE dbo.mcq_cards (
	id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	result_id int NULL ,
	total_rows int NOT NULL ,
	id_rows tinyint NOT NULL ,
	columns tinyint NOT NULL ,
	when_completed TIMESTAMP NULL ,
	when_read TIMESTAMP NULL ,
	when_edited TIMESTAMP NULL ,
	written_information LONG VARCHAR NULL ,
	operator_information LONG VARCHAR NULL ,
	feedback_to_student LONG VARCHAR NULL ,
	score int NOT NULL ,
	available int NOT NULL ,
	deductions int NOT NULL ,
	wrong int NOT NULL ,
	right int NOT NULL ,
	image LONG VARBINARY NULL ,
	dubious LONG VARBINARY NULL ,
	CONSTRAINT PK___11__10 PRIMARY KEY  CLUSTERED 
	(
		id
	)
)
GO

CREATE TABLE dbo.mcq_questions (
	question_id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	ordinal int NOT NULL ,
	one_true bit NOT NULL ,
	answer_a bit NOT NULL ,
	answer_b bit NOT NULL ,
	answer_c bit NOT NULL ,
	answer_d bit NOT NULL ,
	answer_e bit NOT NULL ,
	statement_a LONG VARCHAR NOT NULL ,
	statement_b LONG VARCHAR NOT NULL ,
	statement_c LONG VARCHAR NOT NULL ,
	statement_d LONG VARCHAR NOT NULL ,
	statement_e LONG VARCHAR NOT NULL ,
	question LONG VARCHAR NOT NULL ,
	explanation LONG VARCHAR NOT NULL ,
	CONSTRAINT PK___10__10 PRIMARY KEY  CLUSTERED 
	(
		question_id
	)
)
GO

CREATE TABLE dbo.mcq_responses (
	response_id int IDENTITY (1, 1) NOT NULL ,
	result_id int NOT NULL ,
	question_id int NOT NULL ,
	response_a bit NOT NULL ,
	response_b bit NOT NULL ,
	response_c bit NOT NULL ,
	response_d bit NOT NULL ,
	response_e bit NOT NULL ,
	CONSTRAINT PK___3__13 PRIMARY KEY  CLUSTERED 
	(
		response_id
	)
)
GO

CREATE TABLE dbo.mcq_results (
	result_id int IDENTITY (1, 1) NOT NULL ,
	user_id char (12) NOT NULL ,
	location int NOT NULL ,
	available int NULL ,
	deductions int NULL ,
	wrong int NULL ,
	right int NULL ,
	when_done TIMESTAMP NULL ,
	when_marked TIMESTAMP NULL ,
	CONSTRAINT PK___2__13 PRIMARY KEY  CLUSTERED 
	(
		result_id
	)
)
GO

CREATE TABLE dbo.mcq_tests (
	id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	once_only bit NOT NULL ,
	on_line bit NOT NULL ,
	deadline TIMESTAMP NULL ,
	CONSTRAINT PK___9__10 PRIMARY KEY  CLUSTERED 
	(
		id
	)
)
GO

CREATE TABLE dbo.media_document_entries (
	id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	file_name varchar (64) NOT NULL ,
	sub_folder varchar (64) NULL ,
	mime_type varchar (64) NOT NULL ,
	file_size int NOT NULL ,
	who_uploaded char (12) NOT NULL ,
	when_uploaded TIMESTAMP NOT NULL ,
	deleted bit NOT NULL ,
	CONSTRAINT PK_media_document_entries1__25 PRIMARY KEY  CLUSTERED 
	(
		id
	)
)
GO

CREATE TABLE dbo.messages (
	id int IDENTITY (1, 1) NOT NULL ,
	created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	location int NOT NULL ,
	author char (12) NOT NULL ,
	replyto int NULL DEFAULT (null),
	depth int NOT NULL DEFAULT (0),
	subject char (255) NOT NULL DEFAULT ('no subject'),
	message LONG VARCHAR NOT NULL DEFAULT ('no message'),
	section int NULL DEFAULT (null),
	style tinyint NULL DEFAULT (0),
	seen tinyint NULL DEFAULT (0),
	CONSTRAINT PK_messages_3__14 PRIMARY KEY  CLUSTERED 
	(
		id
	)
)
GO

 CREATE  INDEX created ON dbo.messages(created)
GO

 CREATE  INDEX depth ON dbo.messages(depth)
GO

 CREATE  INDEX location ON dbo.messages(location)
GO

CREATE TABLE dbo.personal_pages (
	id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	user_id char (12) NOT NULL ,
	when_first_seen TIMESTAMP NULL ,
	when_last_seen TIMESTAMP NULL ,
	when_saved TIMESTAMP NULL ,
	html LONG VARCHAR NOT NULL ,
	CONSTRAINT PK_personal_pages_1__12 PRIMARY KEY  CLUSTERED 
	(
		id
	)
)
GO

CREATE TABLE dbo.pigeon_hole_entries (
	id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	user_id char (12) NOT NULL ,
	upload LONG VARBINARY NULL DEFAULT (null),
	download LONG VARBINARY NULL DEFAULT (null),
	up_changed TIMESTAMP NULL DEFAULT (null),
	down_changed TIMESTAMP NULL DEFAULT (null),
	deleted bit NOT NULL DEFAULT (0),
	marker char (12) NULL ,
	when_marked TIMESTAMP NULL ,
	mark int NULL ,
	penalty int NULL ,
	adjusted_mark int NULL ,
	feedback LONG VARCHAR NULL ,
	comments LONG VARCHAR NULL ,
	status_upload int NULL ,
	file_name varchar (255) NULL ,
	mime_type varchar (255) NULL ,
	CONSTRAINT PK_pigeon_hole_entries_1__10 PRIMARY KEY  CLUSTERED 
	(
		id
	)
)
GO

CREATE TABLE dbo.pigeon_hole_questions (
	question_id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	ordinal int NOT NULL ,
	available int NOT NULL ,
	weight float NOT NULL ,
	notes LONG VARCHAR NOT NULL ,
	CONSTRAINT PK___1__16 PRIMARY KEY  CLUSTERED 
	(
		question_id
	)
)
GO

CREATE TABLE dbo.pigeon_hole_responses (
	response_id int IDENTITY (1, 1) NOT NULL ,
	result_id int NOT NULL ,
	question_id int NOT NULL ,
	mark int NULL ,
	feedback LONG VARCHAR NULL ,
	CONSTRAINT PK___2__16 PRIMARY KEY  CLUSTERED 
	(
		response_id
	)
)
GO

CREATE TABLE dbo.pigeon_holes (
	id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	mime_type varchar (255) NULL ,
	date_open TIMESTAMP NULL ,
	date_deadline TIMESTAMP NULL ,
	date_penalty TIMESTAMP NULL ,
	locked bit NOT NULL ,
	download bit NOT NULL ,
	assessed bit NOT NULL ,
	CONSTRAINT PK___1__39 PRIMARY KEY  CLUSTERED 
	(
		id
	)
)
GO

CREATE TABLE dbo.queries (
	location int NOT NULL DEFAULT (1),
	indexsql char (200) NULL ,
	mainsql char (200) NULL ,
	idfield char (20) NULL ,
	sortfield char (20) NULL ,
	indexfields char (100) NULL ,
	displayfields char (100) NULL ,
	updatesql char (200) NULL ,
	insertsql char (200) NULL ,
	deletesql char (200) NULL ,
	idfields char (100) NULL ,
	submainsql char (100) NULL ,
	subupdatesql char (100) NULL ,
	subdeletesql char (100) NULL ,
	subdisplayfields char (100) NULL ,
	subinsertsql char (100) NULL ,
	CONSTRAINT PK___7__10 PRIMARY KEY  CLUSTERED 
	(
		location
	)
)
GO

CREATE TABLE dbo.questionnaire_questions (
	question_id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	ordinal int NOT NULL ,
	anonymity int NOT NULL ,
	statements int NOT NULL ,
	can_comment bit NOT NULL ,
	is_standard bit NOT NULL ,
	introduction LONG VARCHAR NOT NULL ,
	statement_a LONG VARCHAR NULL ,
	statement_b LONG VARCHAR NULL ,
	statement_c LONG VARCHAR NULL ,
	statement_d LONG VARCHAR NULL ,
	statement_e LONG VARCHAR NULL ,
	statement_f LONG VARCHAR NULL ,
	statement_g LONG VARCHAR NULL ,
	statement_h LONG VARCHAR NULL ,
	statement_i LONG VARCHAR NULL ,
	statement_j LONG VARCHAR NULL ,
	CONSTRAINT PK_questionnaire_question1__65 PRIMARY KEY  CLUSTERED 
	(
		question_id
	)
)
GO

CREATE TABLE dbo.questionnaire_responses (
	response_id int IDENTITY (1, 1) NOT NULL ,
	result_id int NOT NULL ,
	question_id int NOT NULL ,
	item int NULL ,
	comments LONG VARCHAR NULL ,
	CONSTRAINT PK_questionnaire_response1__65 PRIMARY KEY  CLUSTERED 
	(
		response_id
	)
)
GO

CREATE TABLE dbo.questionnaire_results (
	result_id int IDENTITY (1, 1) NOT NULL ,
	user_id char (12) NOT NULL ,
	location int NOT NULL ,
	when_started TIMESTAMP NOT NULL ,
	when_saved TIMESTAMP NOT NULL ,
	CONSTRAINT PK_questionnaire_results_1__65 PRIMARY KEY  CLUSTERED 
	(
		result_id
	)
)
GO

CREATE TABLE dbo.textq_questions (
	question_id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	ordinal int NOT NULL ,
	available int NOT NULL ,
	weight float NOT NULL ,
	question LONG VARCHAR NOT NULL ,
	explanation LONG VARCHAR NOT NULL ,
	notes LONG VARCHAR NOT NULL ,
	CONSTRAINT PK_textq_questions_1__35 PRIMARY KEY  CLUSTERED 
	(
		question_id
	)
)
GO

CREATE TABLE dbo.textq_responses (
	response_id int IDENTITY (1, 1) NOT NULL ,
	result_id int NOT NULL ,
	question_id int NOT NULL ,
	response LONG VARCHAR NOT NULL ,
	feedback LONG VARCHAR NULL ,
	mark int NULL ,
	CONSTRAINT PK_textq_responses_1__35 PRIMARY KEY  CLUSTERED 
	(
		response_id
	)
)
GO

CREATE TABLE dbo.textq_results (
	result_id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	user_id char (12) NOT NULL ,
	when_saved TIMESTAMP NOT NULL ,
	when_marked TIMESTAMP NULL ,
	marker char (12) NULL ,
	general_mark int NULL ,
	mark int NULL ,
	penalty int NULL ,
	adjusted_mark int NULL ,
	feedback LONG VARCHAR NULL ,
	comments LONG VARCHAR NULL ,
	status_upload int NULL ,
	CONSTRAINT PK_textq_results_1__35 PRIMARY KEY  CLUSTERED 
	(
		result_id
	)
)
GO

CREATE TABLE dbo.textq_tests (
	id int IDENTITY (1, 1) NOT NULL ,
	location int NOT NULL ,
	date_open TIMESTAMP NULL ,
	date_deadline TIMESTAMP NULL ,
	date_penalty TIMESTAMP NULL ,
	maxmark int NOT NULL ,
	general_available int NOT NULL ,
	general_weight float NOT NULL ,
	general_notes LONG VARCHAR NOT NULL ,
	locked bit NOT NULL 
)
GO

CREATE VIEW mcq_results_detail AS
SELECT mcq_results.result_id, mcq_results.user_id, mcq_results.location, mcq_results.available, mcq_results.deductions, mcq_results.wrong, mcq_results.right, mcq_results.when_done, mcq_results.when_marked, users.name
FROM building.dbo.mcq_results mcq_results, building.dbo.users users
WHERE mcq_results.user_id = users.id

GO

CREATE VIEW media_document_entries_detail AS
SELECT media_document_entries.id, media_document_entries.location, media_document_entries.file_name, media_document_entries.sub_folder, media_document_entries.mime_type, media_document_entries.file_size, media_document_entries.who_uploaded, media_document_entries.when_uploaded, media_document_entries.deleted, users.name
FROM building.dbo.media_document_entries media_document_entries, building.dbo.users users
WHERE media_document_entries.who_uploaded = users.id

GO

CREATE VIEW messages_detail AS 
SELECT messages.id, messages.created, messages.location, messages.author, messages.replyto, messages.depth, messages.subject, messages.message, messages.section, messages.style, messages.seen, users.name
FROM building.dbo.messages messages LEFT OUTER JOIN building.dbo.users users ON messages.author = users.id

GO

