/*
 * Copyright 1990 by Baylor College of Medicine ALL RIGHTS RESERVED. 
 *
 * This program is subject to a license agreement between 
 * Baylor College of Medicine and MIT. Any use inconsistent with
 * said license and any use by persons other than the faculty, 
 * students and staff at MIT or any use on a computer not operated 
 * as part of the Athena Computing Environment (ACE) is expressly 
 * prohibited.
 */
use master
go
create database DB_NAME on default=4
go
sp_dboption DB_STRING,"select into/bulkcopy",true
go
sp_dboption DB_STRING,"trunc. log on chkpt.",true
go
use DB_NAME
go
checkpoint
go

/* Notebooks are at the top */
create table Notebooks (
	N_Id			int				not null,
	N_Title			varchar(255)	not null,
	N_Author		int				not null,
	N_Create_Date	datetime		not null,
	N_Modify_Date	datetime		not null,
	N_Home_Page		int				not null
)
go
sp_primarykey Notebooks,N_Id
go
create unique index Notebook_Index on Notebooks(N_Id)
go

/* A Page belongs to a particular notebook */
create table Pages (
	P_Notebook_Id	int				not null,
	P_Id			int				not null,
	P_Title			varchar(255)	not null,
	P_Author		int				not null,
	P_Create_Date	datetime		not null,
	P_Modify_Date	datetime		not null,
	P_Width			int				not null,
	P_Height		int				not null,
	P_Color			varchar(255)	not null,
)
go
sp_primarykey Pages,P_Notebook_Id,P_Id
go
create unique clustered index Page_Index on Pages(P_Notebook_Id,P_Id)
go

/* An object belongs to a particular page */
create table Objects (
	O_Notebook_Id	int				not null,
	O_Page_Id		int				not null,
	O_Id			int				not null,
	O_Author		int				not null,
	O_Create_Date	datetime		not null,
	O_Modify_Date	datetime		not null,
	O_Type			int				not null,
	O_X				int				not null,
	O_Y				int				not null,
	O_Width			int				not null,
	O_Height		int				not null,
)
go
sp_primarykey Objects,O_Notebook_Id,O_Page_Id,O_Id
go
create unique clustered index Object_Index on Objects(O_Notebook_Id,O_Page_Id,O_Id)
go

/* A Link is tied to an object and points to a page */
create table Links (
	L_Notebook_Id	int				not null,
	L_Page_Id		int				not null,
	L_Object_Id		int				not null,
	L_Dest_Database	varchar(255)	not null,
	L_Dest_Notebook	int				not null,
	L_Dest_Page_Id	int				not null,
	L_Title			varchar(255)	not null,
	L_Font			varchar(255)	not null,
	L_Color			varchar(255)	not null,
)
go
sp_primarykey Links,L_Notebook_Id,L_Object_Id,L_Object_Id
go
create unique clustered index Link_Index on Links(L_Notebook_Id,L_Page_Id,L_Object_Id)
go

/* A Txt is tied to an object */
create table Txts (
	T_Notebook_Id	int				not null,
	T_Page_Id		int				not null,
	T_Object_Id		int				not null,
	T_Font			varchar(255)	not null,
	T_Color			varchar(255)	not null,
	T_Contents		varchar(255)	null,
)
go
sp_primarykey Txts,T_Notebook_Id,T_Object_Id,T_Object_Id
go
create unique clustered index Txt_Index on Txts(T_Notebook_Id,T_Page_Id,T_Object_Id)
go

/* A Img is tied to an object */
create table Imgs (
	I_Notebook_Id	int				not null,
	I_Page_Id		int				not null,
	I_Object_Id		int				not null,
	I_Create_Cmap	int				not null,
	I_Low_Res_Sw	int				not null,
)
go
sp_primarykey Imgs,I_Notebook_Id,I_Object_Id,I_Object_Id
go
create unique clustered index Img_Index on Imgs(I_Notebook_Id,I_Page_Id,I_Object_Id)
go

/* An Action Link is tied to an object */
create table Action_Links (
	AL_Notebook_Id	int				not null,
	AL_Page_Id		int				not null,
	AL_Object_Id	int				not null,
	AL_Command		varchar(255)	not null,
	AL_Title		varchar(255)	not null,
	AL_Font			varchar(255)	not null,
	AL_Color		varchar(255)	not null,
)
go
sp_primarykey Action_Links,AL_Notebook_Id,AL_Object_Id,AL_Object_Id
go
create unique clustered index AL_Index on Action_Links(AL_Notebook_Id,AL_Page_Id,AL_Object_Id)
go

/* An author is a unix user number */
create table Authors (
	A_Id			int				not null,
)
go
sp_primarykey Authors,A_Id
go
create unique index Author_Index on Authors(A_Id)
go

/* Notebook access ties authors and notebooks */
create table Notebook_Access (
	RA_Notebook_Id	int				not null,
	RA_User			int				not null,
	RA_Access		int				not null,
)
go
sp_primarykey Notebook_Access,RA_Notebook_Id,RA_User
go
create unique clustered index NA_Acess_Index on Notebook_Access(RA_Notebook_Id,RA_User)
go

/*
 *
 *	Procedures:
 *
 */
create procedure Delete_Notebook
	@N_Id		int
as
begin
	delete Notebooks where N_Id=@N_Id
end
go

create procedure Delete_Page
	@P_Notebook_Id		int,
	@P_Id				int
as
begin
	delete Pages		where  P_Notebook_Id=@P_Notebook_Id and P_Id=@P_Id
end
go

create procedure Delete_Text_Object
	@O_Notebook_Id		int,
	@O_Page_Id			int,
	@O_Id				int
as
begin
	delete Objects		where  O_Notebook_Id=@O_Notebook_Id and  O_Page_Id=@O_Page_Id and
								O_Id=@O_Id
	delete Txts		where  T_Notebook_Id=@O_Notebook_Id and  T_Page_Id=@O_Page_Id and
								T_Object_Id=@O_Id
end
go

create procedure Delete_Image_Object
	@O_Notebook_Id		int,
	@O_Page_Id			int,
	@O_Id				int
as
begin
	delete Objects		where  O_Notebook_Id=@O_Notebook_Id and  O_Page_Id=@O_Page_Id and
								O_Id=@O_Id
	delete Imgs		where  I_Notebook_Id=@O_Notebook_Id and  I_Page_Id=@O_Page_Id and
								I_Object_Id=@O_Id
end
go

create procedure Delete_Link_Object
	@O_Notebook_Id		int,
	@O_Page_Id			int,
	@O_Id				int
as
begin
	delete Objects		where  O_Notebook_Id=@O_Notebook_Id and  O_Page_Id=@O_Page_Id and
								O_Id=@O_Id
	delete Links		where  L_Notebook_Id=@O_Notebook_Id and  L_Page_Id=@O_Page_Id and
								L_Object_Id=@O_Id
end
go

create procedure Delete_Action_Link_Object
	@O_Notebook_Id		int,
	@O_Page_Id			int,
	@O_Id				int
as
begin
	delete Objects		where  O_Notebook_Id=@O_Notebook_Id and  O_Page_Id=@O_Page_Id and
								O_Id=@O_Id
	delete Action_Links		where  AL_Notebook_Id=@O_Notebook_Id and  AL_Page_Id=@O_Page_Id and
								AL_Object_Id=@O_Id
end
go

create procedure Create_Notebook
	@id				int,
	@author			int,
	@date			datetime,
	@title			varchar(255),
	@home_page		int
as
begin
	insert into Notebooks(N_Id,N_Title,N_Author,N_Create_Date,N_Modify_Date,N_Home_Page)
		values(@id,@title,@author,@date,@date,@home_page)
end
go

create procedure Update_Notebook
	@nid			int,
	@title			varchar(255),
	@date			datetime,
	@home_page		int
as
begin
	update Notebooks set N_Title=@title,N_Modify_Date=@date,N_Home_Page=@home_page where N_Id=@nid
end
go

create procedure Create_Page
	@id				int,
	@author			int,
	@notebook		int,
	@title			varchar(255),
	@width			int,
	@height			int,
	@color			varchar(255),
	@date			datetime
as
begin
	insert into Pages(P_Notebook_Id,P_Id,P_Title,P_Width,P_Height,P_Author,P_Create_Date,P_Modify_Date,P_Color)
		values(@notebook,@id,@title,@width,@height,@author,@date,@date,@color)
end
go

create procedure Update_Page
	@notebook		int,
	@pid			int,
	@title			varchar(255),
	@width			int,
	@height			int,
	@color			varchar(255),
	@date	datetime
as
begin
	update Pages set P_Title=@title,P_Width=@width,P_Height=@height,P_Modify_Date=@date,P_Color=@color
		where P_Notebook_Id=@notebook and P_Id=@pid
end
go

create procedure Create_Image_Object
	@id				int,
	@author			int,
	@notebook		int,
	@page			int,
	@x				int,
	@y				int,
	@width			int,
	@height			int,
	@create_cmap	int,
	@low_res_sw		int,
	@date			datetime
as
begin
	insert into Objects(O_Notebook_Id,O_Page_Id,O_Id,O_Type,O_X,O_Y,O_Width,O_Height,O_Author,O_Create_Date,O_Modify_Date)
		values(@notebook,@page,@id,0,@x,@y,@width,@height,@author,@date,@date)
	insert into Imgs(I_Notebook_Id,I_Page_Id,I_Object_Id,I_Create_Cmap,I_Low_Res_Sw)
		values(@notebook,@page,@id,@create_cmap,@low_res_sw)
end
go

create procedure Update_Image_Object
	@notebook		int,
	@page			int,
	@oid			int,
	@x				int,
	@y				int,
	@width			int,
	@height			int,
	@create_cmap	int,
	@low_res_sw		int,
	@date	datetime
as
begin
	update Objects set O_X=@x,O_Y=@y,O_Width=@width,O_Height=@height,O_Modify_Date=@date
		where O_Notebook_Id=@notebook and O_Page_Id=@page and O_Id=@oid
	update Imgs set I_Create_Cmap=@create_cmap,I_Low_Res_Sw=@low_res_sw
		where I_Notebook_Id=@notebook and I_Page_Id=@page and I_Object_Id=@oid
end
go

create procedure Create_Txt_Object
	@id				int,
	@author			int,
	@notebook		int,
	@page			int,
	@x				int,
	@y				int,
	@width			int,
	@height			int,
	@font			varchar(255),
	@color			varchar(255),
	@date			datetime
as
begin
	insert into Objects(O_Notebook_Id,O_Page_Id,O_Id,O_Type,O_X,O_Y,O_Width,O_Height,O_Author,O_Create_Date,O_Modify_Date)
		values(@notebook,@page,@id,1,@x,@y,@width,@height,@author,@date,@date)
	insert into Txts(T_Notebook_Id,T_Page_Id,T_Object_Id,T_Font,T_Color)
		values(@notebook,@page,@id,@font,@color)
end
go

create procedure Update_Txt_Contents
	@notebook		int,
	@page			int,
	@oid			int,
	@contents		varchar(255)
as
begin
	update Txts set T_Contents=@contents
		where T_Notebook_Id=@notebook and T_Page_Id=@page and T_Object_Id=@oid
end
go

create procedure Update_Txt_Object
	@notebook		int,
	@page			int,
	@oid			int,
	@x				int,
	@y				int,
	@width			int,
	@height			int,
	@font			varchar(255),
	@color			varchar(255),
	@date	datetime
as
begin
	update Objects set O_X=@x,O_Y=@y,O_Width=@width,O_Height=@height,O_Modify_Date=@date
		where O_Notebook_Id=@notebook and O_Page_Id=@page and O_Id=@oid
	update Txts set T_Font=@font,T_Color=@color
		where T_Notebook_Id=@notebook and T_Page_Id=@page and T_Object_Id=@oid
end
go

create procedure Create_Link_Object
	@id				int,
	@author			int,
	@notebook		int,
	@page			int,
	@x				int,
	@y				int,
	@width			int,
	@height			int,
	@dest_note		int,
	@dest_page		int,
	@label			varchar(255),
	@font			varchar(255),
	@color			varchar(255),
	@date			datetime,
	@dest_db		varchar(255)
as
begin
	insert into Objects(O_Notebook_Id,O_Page_Id,O_Id,O_Type,O_X,O_Y,O_Width,O_Height,O_Author,O_Create_Date,O_Modify_Date)
		values(@notebook,@page,@id,2,@x,@y,@width,@height,@author,@date,@date)
	insert into Links(L_Notebook_Id,L_Page_Id,L_Object_Id,L_Dest_Notebook,L_Dest_Page_Id,L_Title,L_Font,L_Color,L_Dest_Database)
		values(@notebook,@page,@id,@dest_note,@dest_page,@label,@font,@color,@dest_db)
end
go

create procedure Update_Link_Object
	@notebook		int,
	@page			int,
	@oid			int,
	@x				int,
	@y				int,
	@width			int,
	@height			int,
	@dest_note		int,
	@dest_page		int,
	@label			varchar(255),
	@font			varchar(255),
	@color			varchar(255),
	@date			datetime,
	@dest_db		varchar(255)
as
begin
	update Objects set O_X=@x,O_Y=@y,O_Width=@width,O_Height=@height,O_Modify_Date=@date
		where O_Notebook_Id=@notebook and O_Page_Id=@page and O_Id=@oid
	update Links set L_Font=@font,L_Color=@color,L_Title=@label,L_Dest_Notebook=@dest_note,L_Dest_Page_Id=@dest_page,L_Dest_Database=@dest_db
		where L_Notebook_Id=@notebook and L_Page_Id=@page and L_Object_Id=@oid
end
go

create procedure Create_Action_Link_Object
	@id				int,
	@author			int,
	@notebook		int,
	@page			int,
	@x				int,
	@y				int,
	@width			int,
	@height			int,
	@command		varchar(255),
	@label			varchar(255),
	@font			varchar(255),
	@color			varchar(255),
	@date			datetime
as
begin
	insert into Objects(O_Notebook_Id,O_Page_Id,O_Id,O_Type,O_X,O_Y,O_Width,O_Height,O_Author,O_Create_Date,O_Modify_Date)
		values(@notebook,@page,@id,3,@x,@y,@width,@height,@author,@date,@date)
	insert into Action_Links(AL_Notebook_Id,AL_Page_Id,AL_Object_Id,AL_Command,AL_Title,AL_Font,AL_Color)
		values(@notebook,@page,@id,@command,@label,@font,@color)
end
go

create procedure Update_Action_Link_Object
	@notebook		int,
	@page			int,
	@oid			int,
	@x				int,
	@y				int,
	@width			int,
	@height			int,
	@command		varchar(255),
	@label			varchar(255),
	@font			varchar(255),
	@color			varchar(255),
	@date			datetime
as
begin
	update Objects set O_X=@x,O_Y=@y,O_Width=@width,O_Height=@height,O_Modify_Date=@date
		where O_Notebook_Id=@notebook and O_Page_Id=@page and O_Id=@oid
	update Action_Links set AL_Font=@font,AL_Color=@color,AL_Title=@label,AL_Command=@command
		where AL_Notebook_Id=@notebook and AL_Page_Id=@page and AL_Object_Id=@oid
end
go

create procedure Add_Author
	@aid		int
as
begin
	insert into Authors(A_Id) values(@aid)
end
go

create procedure Add_Access
	@nid		int,
	@aid		int,
	@access		int
as
begin
		insert into Notebook_Access(RA_Notebook_Id,RA_User,RA_Access) values(@nid,@aid,@access)
end
go

create procedure Update_Access
	@nid		int,
	@aid		int,
	@access		int
as
begin
		update Notebook_Access set RA_Access=@access where RA_Notebook_Id=@nid and RA_User=@aid
end
go

create procedure Rmv_Access
	@nid		int,
	@aid		int
as
begin
	delete Notebook_Access where RA_Notebook_Id=@nid and RA_User=@aid
end
go

create procedure Update_Object_LW
	@nid	int,
	@pid	int,
	@oid	int,
	@date	datetime
as
begin
	update Objects set O_Modify_Date=@date where
		O_Notebook_Id=@nid and O_Page_Id=@pid and O_Id=@oid
end
go

create procedure Retrieve_Notebooks
as
begin
	select N_Id,N_Title,N_Author,N_Create_Date,N_Modify_Date,N_Home_Page from Notebooks
end
go

create procedure Retrieve_Pages
as
begin
	select P_Notebook_Id,P_Id,P_Title,P_Author,P_Create_Date,P_Modify_Date,P_Width,P_Height,P_Color from Pages
end
go

create procedure Retrieve_Image_Objects
as
begin
	select O_Notebook_Id,O_Page_Id,O_Id,O_Author,O_Create_Date,O_Modify_Date,O_Type,O_X,O_Y,O_Width,O_Height,I_Create_Cmap,I_Low_Res_Sw from Objects,Imgs
		where O_Notebook_Id=I_Notebook_Id and O_Page_Id = I_Page_Id and O_Id=I_Object_Id
end
go

create procedure Retrieve_Txt_Objects
as
begin
	select O_Notebook_Id,O_Page_Id,O_Id,O_Author,O_Create_Date,O_Modify_Date,O_Type,O_X,O_Y,O_Width,O_Height,
		T_Font,T_Color,T_Contents from Txts,Objects
		where O_Notebook_Id=T_Notebook_Id and O_Page_Id = T_Page_Id and O_Id=T_Object_Id
end
go

create procedure Retrieve_Link_Objects
as
begin
	select O_Notebook_Id,O_Page_Id,O_Id,O_Author,O_Create_Date,O_Modify_Date,O_Type,O_X,O_Y,O_Width,O_Height,
		L_Dest_Notebook,L_Dest_Page_Id,L_Title,L_Font,L_Color,L_Dest_Database from Links,Objects
		where O_Notebook_Id=L_Notebook_Id and O_Page_Id = L_Page_Id and O_Id=L_Object_Id
end
go

create procedure Retrieve_Action_Link_Objects
as
begin
	select O_Notebook_Id,O_Page_Id,O_Id,O_Author,O_Create_Date,O_Modify_Date,O_Type,O_X,O_Y,O_Width,O_Height,
		AL_Command,AL_Title,AL_Font,AL_Color from Action_Links,Objects
		where O_Notebook_Id=AL_Notebook_Id and O_Page_Id = AL_Page_Id and O_Id=AL_Object_Id
end
go

create procedure Retrieve_Authors
as
begin
	select A_Id from Authors
end
go

create procedure Retrieve_Accesses
as
begin
	select RA_Access,RA_Notebook_Id,RA_User from Notebook_Access
end
go
