/* Table contains data fields, a primary key constraint and a foreign key constraints       */
/* The constraints are designed to limit damage to data integrity in the event of           */ 
/* programmer error or other unforeseen circumstances.                                      */

/* Log book pages are a type of object hence the foreign key reference to objects and        */
/* should only occur with reference to specific log books and specific users hence foreign   */
/* key reference to those tables.                                                            */

CREATE TABLE log_book_pages (
	log_book_page_id int NOT NULL ,
	log_book_id int NOT NULL ,
	user_id int NOT NULL ,
	flags int NOT NULL,
	when_first TIMESTAMP NOT NULL ,
	when_last TIMESTAMP NULL ,
	when_user_first TIMESTAMP NULL ,
	when_user_last TIMESTAMP NULL ,
	section_first_big_string_id int NULL,
	section_last_big_string_id int NULL,
	section_user_first_big_string_id int NULL,
	section_user_last_big_string_id int NULL,
	question_first_big_string_id int NULL,
	question_last_big_string_id int NULL,
	question_user_first_big_string_id int NULL,
	question_user_last_big_string_id int NULL,
        acl_id int NULL,
	CONSTRAINT PK_log_book_pages PRIMARY KEY
	(
		log_book_page_id
	),
	CONSTRAINT FK_log_book_pages_to_objects FOREIGN KEY
	(
		log_book_page_id
	) REFERENCES objects (
		id
	),
	CONSTRAINT FK_pages_to_log_books FOREIGN KEY
	(
		log_book_id
	) REFERENCES log_books (
		log_book_id
	),
	CONSTRAINT FK_log_book_pages_to_users FOREIGN KEY
	(
		user_id
	) REFERENCES users (
		user_id
	),
	CONSTRAINT FK_pages_s_f_to_bgstrs FOREIGN KEY
	(
		section_first_big_string_id
	) REFERENCES big_strings (
		big_string_id
	),
	CONSTRAINT FK_pages_s_l_to_bgstrs FOREIGN KEY
	(
		section_last_big_string_id
	) REFERENCES big_strings (
		big_string_id
	),
	CONSTRAINT FK_pages_s_u_f_to_bgstrs FOREIGN KEY
	(
		section_user_first_big_string_id
	) REFERENCES big_strings (
		big_string_id
	),
	CONSTRAINT FK_pages_s_u_l_to_bgstrs FOREIGN KEY
	(
		section_user_last_big_string_id
	) REFERENCES big_strings (
		big_string_id
	),
	CONSTRAINT FK_pages_q_f_to_bgstrs FOREIGN KEY
	(
		question_first_big_string_id
	) REFERENCES big_strings (
		big_string_id
	),
	CONSTRAINT FK_pages_q_l_to_bgstrs FOREIGN KEY
	(
		question_last_big_string_id
	) REFERENCES big_strings (
		big_string_id
	),
	CONSTRAINT FK_pages_q_u_f_to_bgstrs FOREIGN KEY
	(
		question_user_first_big_string_id
	) REFERENCES big_strings (
		big_string_id
	),
	CONSTRAINT FK_pages_q_u_l_to_bgstrs FOREIGN KEY
	(
		question_user_last_big_string_id
	) REFERENCES big_strings (
		big_string_id
	),
	CONSTRAINT FK_pages_to_acls FOREIGN KEY
	(
		acl_id
	) REFERENCES acls (
		acl_id
	)
)
GO

/* The rest of this SQL is deployment information as described in LogBook.sql */


INSERT INTO classes (type, super_type, db_name, table_name, java_class)
	VALUES(52, null, null, 'log_book_pages', 'org.bodington.logbook.server.LogBookPage')
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 1, 'log_book_page_id', 'setLogBookPageId', 'getLogBookPageId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 1, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 2, 'log_book_id', 'setLogBookId', 'getLogBookId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 3, 'user_id', 'setUserId', 'getUserId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 4, 'flags', 'setFlags', 'getFlags', 'int', 'int', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 5, 'when_first', 'setWhenFirst', 'getWhenFirst', 'java.sql.Timestamp', 'java.sql.Timestamp', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 6, 'when_last', 'setWhenLast', 'getWhenLast', 'java.sql.Timestamp', 'java.sql.Timestamp', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 7, 'when_user_first', 'setWhenUserFirst', 'getWhenUserFirst', 'java.sql.Timestamp', 'java.sql.Timestamp', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 8, 'when_user_last', 'setWhenUserLast', 'getWhenUserLast', 'java.sql.Timestamp', 'java.sql.Timestamp', 0, null, null, null )
GO

/* Section and question information added by PJC 2004-04-13 */
INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 9, 'section_first_big_string_id', 'setSectionFirstBigStringId', 'getSectionFirstBigStringId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 10, 'section_last_big_string_id', 'setSectionLastBigStringId', 'getSectionLastBigStringId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 11, 'section_user_first_big_string_id', 'setSectionUserFirstBigStringId', 'getSectionUserFirstBigStringId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 12, 'section_user_last_big_string_id', 'setSectionUserLastBigStringId', 'getSectionUserLastBigStringId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 13, 'question_first_big_string_id', 'setQuestionFirstBigStringId', 'getQuestionFirstBigStringId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 14, 'question_last_big_string_id', 'setQuestionLastBigStringId', 'getQuestionLastBigStringId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 15, 'question_user_first_big_string_id', 'setQuestionUserFirstBigStringId', 'getQuestionUserFirstBigStringId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 16, 'question_user_last_big_string_id', 'setQuestionUserLastBigStringId', 'getQuestionUserLastBigStringId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

INSERT INTO fields (type, sequence, field, set_method_name, get_method_name, set_java_class, get_java_class, flags, foreign_type, set_method_name_foreign, get_method_name_foreign )
	VALUES(52, 17, 'acl_id', 'setAclId', 'getAclId', 'org.bodington.database.PrimaryKey', 'org.bodington.database.PrimaryKey', 0, null, null, null )
GO

