--[1] 완성형 게시판(DotNetNote)용 테이블 설계
Create Table dbo.DotNetNote
(
 Num Int Identity(1, 1) Not Null Primary Key,  --번호
 Name VarChar(25) Not Null,                    --이름
 Email VarChar(100) Null,                      --이메일 
 Title VarChar(150) Not Null,                  --제목
 PostDate DateTime Default GetDate() Not Null, --작성일 
 PostIP VarChar(15) Not Null,                  --작성IP
 Content Text Not Null,                        --내용
 Password VarChar(20) Null,                    --비밀번호
 ReadCount Int Default 0,                      --조회수
 Encoding VarChar(10) Not Null,                --인코딩(HTML/Text)
 Homepage VarChar(100) Null,                   --홈페이지
 ModifyDate DateTime Null,                     --수정일 
 ModifyIP VarChar(15) Null,                    --수정IP
 FileName VarChar(255) Null,                   --파일명
 FileSize Int Default 0,                       --파일크기
 DownCount Int Default 0,                      --다운수 
 Ref Int Not Null,                             --참조(부모글)
 Step Int Default 0,                           --답변깊이(레벨)
 RefOrder Int Default 0,                       --답변순서
 AnswerNum Int Default 0,                      --답변수
 ParentNum Int Default 0,                      --부모글번호
 CommentCount Int Default 0,                   --코멘트수(확장을 위해서...)
 Category VarChar(10) Null                     --카테고리(확장을 위해서...)
)
Go

-- BoardWrite.aspx
--[2] 완성형 게시판(DotNetNote)에 글을 작성 : WriteDotNetNote
Create Proc dbo.WriteDotNetNote
 @Name VarChar(25),
 @Email VarChar(100),
 @Title VarChar(150),
 @PostIP VarChar(15),
 @Content Text,
 @Password VarChar(20),
 @Encoding VarChar(10),
 @Homepage VarChar(100),
 -- @Ref Int,
 @FileName VarChar(255),
 @FileSize Int
As
 Declare @MaxRef Int
 Select @MaxRef = Max(Ref) From DotNetNote
 
 If @MaxRef is Null
  Set @MaxRef = 1 -- 테이블 생성 후 처음만 비교
 Else
  Set @MaxRef = @MaxRef + 1

 Insert DotNetNote(Name, Email, Title, PostIP, Content, Password, Encoding, Homepage, Ref, FileName, FileSize)
 Values(@Name, @Email, @Title, @PostIP, @Content, @Password, @Encoding, @Homepage, @MaxRef, @FileName, @FileSize)
Go

-- BoardList.aspx
--[3][1] 완성형 게시판(DotNetNote)에서 데이터 출력 : ListDotNetNote
Create Procedure dbo.ListDotNetNote
As
 Select * From DotNetNote Order By Ref Desc, RefOrder Asc
Go

--[3][2] 고급 페이징
Alter Procedure dbo.ListDotNetNote
 @Page Int
As
 Select Top 10 *
 From DotNetNote
 Where Num Not In (Select Top (10 * @Page) Num From DotNetNote Order By Ref Desc, RefOrder Asc)
 Order By Ref Desc, RefOrder Asc
Go

--[3][3] 고급 페이징(SQL Server 2000/2005 공용 쿼리문)
Alter Procedure dbo.ListDotNetNote
 @Page Int
As
 Declare @strSql VarChar(500)
 Set @strSql = '
 Select Top 10 *
 From DotNetNote
 Where Num Not In (Select Top (10 * ' + Convert(VarChar,@Page) + ') Num
 From DotNetNote Order By Ref Desc, RefOrder Asc)
 Order By Ref Desc, RefOrder Asc
 '
 Exec(@strSql)
Go

Exec ListDotNetNote 0
Go

--[4] 해당 글을 세부적으로 읽어오는 저장 프로시저 : ViewDotNetNote
Create Procedure dbo.ViewDotNetNote
 @Num Int
As
 Update DotNetNote Set ReadCount = ReadCount + 1 Where Num = @Num
 
 Select * From DotNetNote Where Num = @Num
Go

--BoardReply.aspx
--[5] 완성형 게시판(DotNetNote)에 글을 답변 : ReplyDotNetNote
Create Proc dbo.ReplyDotNetNote
 @Name VarChar(25),
 @Email VarChar(100),
 @Title VarChar(150),
 @PostIP VarChar(15),
 @Content Text,
 @Password VarChar(20),
 @Encoding VarChar(10),
 @Homepage VarChar(100),
 @Ref Int,
 @Step Int,
 @RefOrder Int,
 @ParentNum Int,
 @FileName VarChar(255),
 @FileSize Int
As
 Insert DotNetNote
 (Name, Email, Title, PostIP, Content,
   Password, Encoding, Homepage, Ref, Step, RefOrder,
   ParentNum, FileName, FileSize)
 Values
 (@Name, @Email, @Title, @PostIP, @Content,
   @Password, @Encoding, @Homepage, @Ref, @Step, @RefOrder,
     @ParentNum, @FileName, @FileSize)
Go
--[5] 답변하기 : SqlHelper 방식
Alter Proc dbo.ReplyDotNetNote
 @Name VarChar(25),
 @Email VarChar(100),
 @Title VarChar(150),
 @PostIP VarChar(15),
 @Content Text,
 @Password VarChar(20),
 @Encoding VarChar(10),
 @Homepage VarChar(100),
 @ParentNum Int, -- 부모글의 고유번호(Num)
 @FileName VarChar(255),
 @FileSize Int
As
 --[0] 변수 선언
 Declare @MaxRefOrder Int
 Declare @MaxRefAnswerNum Int
 Declare @ParentRef Int
 Declare @ParentStep Int
 Declare @ParentRefOrder Int

 --[1] 부모글의 답변수(AnswerNum)를 1증가
 Update DotNetNote Set AnswerNum = AnswerNum + 1 Where Num = @ParentNum

 --[2] 같은 글에 대해서 답변을 두 번 이상하면 먼저 답변한 게 위에 나타나게 한다.
 Select @MaxRefOrder = RefOrder, @MaxRefAnswerNum = AnswerNum From DotNetNote
 Where ParentNum = @ParentNum And RefOrder = (Select Max(RefOrder) From DotNetNote Where ParentNum = @ParentNum)

 If @MaxRefOrder Is Null
 Begin
  Select @MaxRefOrder = RefOrder From DotNetNote Where Num = @ParentNum
  Set @MaxRefAnswerNum = 0  
 End 

 --[3] 중간에 답변달 때(비집고 들어갈 자리 마련)
 Select @ParentRef = Ref, @ParentStep = Step From DotNetNote Where Num = @ParentNum
 Update DotNetNote Set RefOrder = RefOrder + 1 Where Ref = @ParentRef And RefOrder > (@MaxRefOrder + @MaxRefAnswerNum)

 --[4] 최종저장
 Insert DotNetNote(Name, Email, Title, PostIP, Content, Password, Encoding, Homepage, Ref, Step, RefOrder, ParentNum, FileName, FileSize)
 Values(@Name, @Email, @Title, @PostIP, @Content, @Password, @Encoding, @Homepage, @ParentRef, @ParentStep + 1, @MaxRefOrder + @MaxRefAnswerNum + 1, @ParentNum, @FileName, @FileSize)
Go

--[6] DotNetNote 테이블에 있는 레코드의 개수를 구하는 저장 프로시저
Create Proc dbo.GetCountDotNetNote
As
 Select Count(*) From DotNetNote
Go

--[8] 해당 글을 지우는 저장 프로시저
-- 답변글이 있으면 업데이트하고 없으면 지운다.
--Drop Proc dbo.DeleteDotNetNote
Create Proc dbo.DeleteDotNetNote
 @Num Int,
 @Password VarChar(30) -- 암호 매개변수 추가
As

Declare @cnt Int
Select @cnt = Count(*) From DotNetNote
Where Num = @Num And Password = @Password
If @cnt = 0
Begin
 Return 0 -- 번호와 암호가 맞는게 없으면 0을 반환
End 

Declare @AnswerNum Int
Declare @RefOrder Int
Declare @Ref Int
Declare @ParentNum Int

Select
 @AnswerNum = AnswerNum,
 @RefOrder = RefOrder,
 @Ref = Ref,
 @ParentNum = ParentNum
From
 DotNetNote
Where Num = @Num

If @AnswerNum = 0
Begin
 If @RefOrder > 0
 Begin
  UPDATE DotNetNote SET RefOrder = RefOrder - 1
    WHERE Ref = @Ref AND RefOrder > @RefOrder
  UPDATE DotNetNote SET AnswerNum = AnswerNum - 1
    WHERE Num = @ParentNum
 End
 Delete DotNetNote Where Num = @Num
 Delete DotNetNote WHERE Num = @ParentNum AND
   ModifyIP = '((DELETED))' AND AnswerNum = 0 
End
Else
Begin
 Update DotNetNote
 Set Name = '(Unknown)', Email = NULL, Password = NULL,
  Title = '(삭제된 글입니다.)',
  Content = '(삭제된 글입니다. '
    + '현재 답변이 포함되어 있기 때문에 내용만 삭제되었습니다.)',
  ModifyIP = '((DELETED))', FileName = NULL,
  FileSize = NULL, CommentCount = 0
 Where Num = @Num 
End
Go

-- BoardModify.aspx
--[9] 해당 글을 수정하는 저장 프로시저 : ModifyDotNetNote
Create Proc dbo.ModifyDotNetNote
 @Name VarChar(25),
 @Email VarChar(100),
 @Title VarChar(150),
 @ModifyIP VarChar(15),
 @Content  VarChar(8000),
 @Password VarChar(30), -- 추가
 @Encoding VarChar(10),
 @Homepage VarChar(100),
 @Num Int
As
 Declare @cnt Int
 Select @cnt = Count(*) From DotNetNote
 Where Num = @Num And Password = @Password
 If @cnt > 0 -- 번호와 암호가 맞는게 있다면...
 Begin
  Update DotNetNote
  Set
   Name = @Name, Email = @Email, Title = @Title,
   ModifyIP = @ModifyIP, ModifyDate = GetDate(),
   Content = @Content, Encoding = @Encoding,
   Homepage = @Homepage
  Where Num = @Num
  Select '1'
 End
 Else
  Select '0'
Go

--BoardSearch.aspx
--[10] 필드명에서 레코드를 검색하는 저장프로시저
Create Proc dbo.SearchDotNetNote
 @SearchField VarChar(25),
 @SearchQuery VarChar(25)
As
 Set @SearchQuery = '%' + @SearchQuery + '%'
 Select *
 From DotNetNote
 Where
  (
   Case @SearchField
   When 'Name' Then [Name]
   When 'Email' Then Email
   When 'Title' Then Title
   Else @SearchQuery
   End
  )
  Like
   @SearchQuery
 Order By Num Desc
Go

-- 동적 쿼리문으로 변경
Alter Proc dbo.SearchDotNetNote
 @SearchField VarChar(25),
 @SearchQuery VarChar(25),
 @Page Int
As
 -- 순수문자열 : Name -> ' + 해당변수 + '
 Declare @strSql VarChar(500)
 Set @strSql = '
 Select Top 10 * From DotNetNote
 Where ' + @SearchField + ' Like ''%' + @SearchQuery + '%'' And Num Not In
 (
  Select Top (10 * ' + Convert(VarChar, @Page) + ') Num From DotNetNote
  Where ' + @SearchField + ' Like ''%' + @SearchQuery + '%''
  Order By Ref Desc, RefOrder Asc
 )
 Order By Ref Desc, RefOrder Asc 
 '
 --Print @strSql
 Exec(@strSql)
Go

-- 코멘트 테이블 생성
Create Table dbo.DotNetNoteComments
(
 Num Int Identity(1, 1) Not Null Primary Key, -- 일련번호
 BoardName VarChar(50) Null, -- 게시판이름(확장) : Notice, Free, Qna
 BoardNum Int Not Null, -- 해당 게시판의 게시물 번호
 Name VarChar(25) Not Null, -- 작성자
 Opinion VarChar(8000) Not Null, -- 코멘트 내용
 PostDate SmallDateTime Default(GetDate()), -- 작성일
 Password VarChar(20) Not Null -- 코멘트 삭제용 암호
)
Go