--[!] 테이블 설계
--[1] 게시판 관리용 테이블
Create TABLE [dbo].[Boards]
(
[TID] [int] IDENTITY(1,1) NOT NULL Primary Key, --일련번호
[BoardAlias] [varchar](50) NULL, --게시판이름(별칭):Notice,Free,News...
[Title] [varchar](50) NULL, --게시판 제목 : 공지사항, 자유게시판
[Description] [varchar](200) NULL,--게시판 설명
--
[SysopUID] [int] NULL DEFAULT ((0)),--회원제 연동시 시삽 권한 부여
[tPublic] [bit] NULL DEFAULT ((1)),--익명사용자(1) / 회원 전용(0)게시판 구분
[GroupName] [varchar](50) NULL, --그룹으로 묶어서 관리하고자 할 때
[GroupOrder] [int] NULL DEFAULT ((0)),--그룹내 순서
[MailEnable] [bit] NULL DEFAULT ((0))--게시물 작성시 메일 전송 여부...
-- 더 필요한 항목이 있으면 추가(확장)...
)
Go
--[2] Domains 테이블 : 회원 필수 정보
Create Table dbo.Domains
(
UID Int Identity(1, 1) Not Null Primary Key, --일련번호
DomainID VarChar(30) Null, --아이디/그룹아이디
Name VarChar(50) Null, --이름/그룹명
Type VarChar(10) Null, --User/Group
Description VarChar(255) Null --설명(소개)
)
Go
--[3] UserProfiles 테이블 : 회원 기타 정보
Create Table dbo.UserProfiles
(
UID Int Not Null, -- Domains 테이블의 UID값(User)
Password VarChar(50) Null, -- 암호
Email VarChar(100) Null, -- 이메일
LastLoginDate DateTime Null, -- 마지막 로그인 시간
LastLoginIP VarChar(16) Null, -- 마지막 로그인 IP주소
VisitedCount Int Default(0), -- 방문 횟수
Blocked Bit Default(0) -- 계정 사용(0) 또는 잠금(1)
-- 기타 필요한 정보가 있으면 사용자가 더 추가
)
Go
--[4] Membership 테이블 : 회원에 대한 역할(Role)/그룹권한 처리
Create Table dbo.Membership
(
UserUID Int Default(0), -- Domains의 레코드 중 Type = 'User'
GroupUID Int Default(0), -- Domains의 레코드 중 Type = 'Group'
Primary Key(UserUID, GroupUID)
)
Go
--Membership(6, 1) : 6(Red)번 사용자는 1(Administrators)번 그룹에 포함되어져 있다
--Membership(234, 3) : 234번 사용자는 3번 그룹에 포함되어져 있다
--[5] 사용자(그룹/역할)에 대한 권한 설정 : NTFS 파일 시스템의 보안 탭 모방
CREATE TABLE [dbo].[Permission]
(
[TID] [int] , -- 테이블 일련번호(Boards 테이블의 TID)
[UID] [int] , -- 사용자 일련번호
[NoAccess] [bit] Default(0) , -- 접근거부
[List] [bit] Default(1) , -- 리스트 보기
[ReadArticle] [bit] Default(1) , -- 상세 보기
[Download] [bit] Default(1) , -- 파일 다운로드
[Write] [bit] Default(1) , -- 글쓰기
[Upload] [bit] Default(1) , -- 업로드
[Extra] [bit] Default(0) , -- 관리
[Admin] [bit] Default(0) , -- 모든권한
Primary Key(TID, UID)
)
GO
-- 공지사항(1번테이블) 게시판은 Administrators(1번그룹)만 모든 권한
-- 일반사용자(Users:3번그룹)은 내용 읽기까지
-- 손님(Anonymous:4번그룹)은 리스트만 보기
--Permission(1, 1, 0, 1, 1, 1, 1, 1, 1, 1)
--Permission(1, 3, 0, 1, 1, 0, 0, 0, 0, 0)
--Permission(1, 4, 0, 1, 0, 0, 0, 0, 0, 0)
--[6] 게시물에 대한 메일링리스트 설정
CREATE TABLE [dbo].[MailList]
(
[TID] [int] , -- 테이블TID
[UID] [int] , -- 사용자UID
Primary Key (TID, UID)
)
GO
--[7] 게시판 코멘트 저장 : DotNetComments와 동일(타이핑 하지 마세요..)
CREATE TABLE [dbo].[Opinions]
(
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 DateTime Default(GetDate()), -- 작성일
Password VarChar(20) Not Null -- 코멘트 삭제용 암호
)
GO
--[!] 예시문 : 필수 데이터 입력
--[1] 그룹 권한 부여 : Administrators, Everyone, Users, Guests
Insert Domains (DomainID, Name, Type, Description)
Values('Administrators','관리자 그룹','Group','게시판을 총 관리하는 관리 그룹 계정')
Go
Insert Domains (DomainID, Name, Type, Description)
Values('Everyone','전체 사용자 그룹','Group','게시판을 사용하는 모든 사용자 그룹 계정')
Go
Insert Domains (DomainID, Name, Type, Description)
Values('Users', '일반 사용자 그룹', 'Group', '일반 사용자 그룹 계정')
Go
Insert Domains (DomainID, Name, Type, Description)
Values('Guests', '관리자 그룹', 'Group', '게스트 사용자 그룹 계정')
Go
--[2] 관리용 사용자 입력 : Administrator, Guest, Anonymous
Insert Domains (DomainID, Name, Type, Description)
Values('Administrator', '관리자', 'User', '게시판을 총 관리하는 사용자 계정')
Go
Insert Domains (DomainID, Name, Type, Description)
Values('Guest', '게스트 사용자', 'User', '게스트 사용자 계정')
Go
Insert Domains (DomainID, Name, Type, Description)
Values('Anonymous', '익명 사용자', 'User', '익명 사용자 계정')
Go
--[3] 관리용 사용자에 대한 추가 프로필 정보 입력
Insert UserProfiles
(UID, Password, Email, LastLoginDate, LastLoginIP, VisitedCount, Blocked)
Values(5, '1234', null, null, null, 0, 0)
Go
Insert UserProfiles
(UID, Password, Email, LastLoginDate, LastLoginIP, VisitedCount, Blocked)
Values(6, '1234', null, null, null, 0, 1)
Go
Insert UserProfiles
(UID, Password, Email, LastLoginDate, LastLoginIP, VisitedCount, Blocked)
Values(7, '1234', null, null, null, 0, 1)
Go
--[4] 관리용 사용자에 대한 권한 부여
Insert Membership (UserUID, GroupUID)
Values(5, 1) -- Administrators <- Administrator
Go
Insert Membership (UserUID, GroupUID)
Values(5, 3) -- Users <- Administrator
Go
Insert Membership (UserUID, GroupUID)
Values(6, 4) -- Guests <- Guest
Go
Insert Membership (UserUID, GroupUID)
Values(7, 4) -- Guests <- Anonymous
Go
--[!] 뷰(View) 설계
--[1] 그룹 리스트 : Groups
Create View dbo.Groups
As
Select UID, DomainID, Name, Type, Description From Domains
Where Type = 'Group' With Check Option
Go
Select * From Groups
Go
--[2] 사용자 리스트 : Users
Create View dbo.Users
As
Select
Domains.UID As UID,
Domains.DomainID As DomainID,
Domains.Name As Name,
UserProfiles.Password As Password,
UserProfiles.Email As Email,
UserProfiles.LastLoginDate As LastLoginDate,
UserProfiles.LastLoginIP As LastLoginIP,
UserProfiles.VisitedCount As VisitedCount,
Domains.Description As Description,
UserProfiles.Blocked As Blocked,
Domains.Type As Type
From
Domains, UserProfiles
Where
Domains.Type = 'User' And Domains.UID = UserProfiles.UID
With Check Option
Go
Select * From Users
Go
------------------------------------------------------------------------
--[!] 저장 프로시저(Stored Procedure)
--[1] 회원 가입 저장 프로시저 : AddUser : Register.ascx에서 사용(완성)
Create Proc dbo.AddUser
@DomainID VarChar(30),
@Name VarChar(50),
@Description VarChar(255),
--
@Password VarChar(50),
@Email VarChar(100),
@Blocked Bit,
@GroupUID Int,
--
@UID Int Output -- 반환형 매개변수
As
--[!] 해당 아이디가 이미 회원가입되어있으면
Declare @Result Int
Select @Result = Count(*) From Domains
Where DomainID = @DomainID
If @Result > 0
Return - 1 -- -1이 반환되면 회원가입 안됨...
--[a] Domains 테이블에 데이터 입력
If @Description <> ''
Insert Domains (DomainID, Name, Type, Description) Values(@DomainID, @Name, 'User', @Description)
Else
Insert Domains (DomainID, Name, Type, Description) Values(@DomainID, @Name, 'User', NULL)
Select @UID = @@Identity -- Domains 테이블의 가장 마지막 UID값
--[b] UserProfiles 테이블에 데이터 입력
If @Email <> ''
Insert UserProfiles (UID, Password, Email, LastLoginDate, LastLoginIP, VisitedCount, Blocked)
Values (@UID, @Password, @Email, Null, Null, 0, @Blocked)
Else
Insert UserProfiles (UID, Password, Email, LastLoginDate, LastLoginIP, VisitedCount, Blocked)
Values (@UID, @Password, Null, Null, Null, 0, @Blocked)
--[c] Membership 테이블에 데이터 입력
Insert Membership (UserUID, GroupUID)
Values(@UID, @GroupUID) -- 모든 회원은 기본이 Users 권한
--[!] UID값 반환
Select @UID
Go
--[2] 회원 로그인 처리 저장 프로시저 : LoginUser : Login.ascx에서 사용
--매개변수 : 아이디/암호,...
--반환값 : 0이면 인증되지않고, 1을 반환하면 인증처리
Create Proc dbo.LoginUser
@DomainID VarChar(30),
@Password VarChar(50),
--
@LastLoginIP VarChar(15), -- IP주소(입력)
@OriginLastLoginIP VarChar(15) Output, -- 이전 IP주소 반환용
@OriginLastLoginDate DateTime Output -- 마지막 로그인 시간
As
--[a] 아이디 및 암호가 맞는 레코드의 카운트 값 반환
Declare @Result Int
Declare @UID Int -- UID 고유번호
Select @Result = Count(*), @UID = UID From Users -- 뷰에서 검색
Where DomainID = @DomainID And Password = @Password
Group By UID
--[b] 카운트가 1이면(맞음) 방문수(어사아님) 증가 후 1반환, 그렇지 않으면 0반환
If @Result > 0 -- 로그인 정보 남기기
Begin
-- 이전 자료 읽어서 반환
Select @OriginLastLoginIP = LastLoginIP,
@OriginLastLoginDate = LastLoginDate
From Users Where UID = @UID
-- 새로운 값으로 업데이트
Update UserProfiles
Set
LastLoginIP = @LastLoginIP,
LastLoginDate = GetDate(),
VisitedCount = VisitedCount + 1
Where UID = @UID
Select '1'
End
Else
Select '0' -- 0 : 아이디 및 암호 틀림, 1 : Correct
Go
--[3] 현재 접속자에 대한 모든 정보를 출력 : GetUser : UserInfor.ascx에서 사용
Create Proc dbo.GetUser
@DomainID VarChar(30)
As
Select * From Users Where DomainID = @DomainID
Go
--[4] 현재 접속자에 대한 회원탈퇴 기능 구현 : DeleteUser
Create Proc dbo.DeleteUser
@DomainID VarChar(30)
As
Declare @UID Int
Select @UID = UID From Users Where DomainID = @DomainID
If @UID is Not Null
Begin
Delete Domains Where UID = @UID
Delete UserProfiles Where UID = @UID
Delete Membership Where UserUID = @UID
-- 게시판 연동시 2개 정도의 테이블에 있는 정보도 삭제
End
Go
--[5] 현재 사용자에 대한 암호 변경 기능 구현 : ChangePassword
Create Proc dbo.ChangePassword
@OriginalPassword VarChar(20), -- 이전 암호
@NewPassword VarChar(20), -- 새 암호
@DomainID VarChar(30) -- 현재 접속자 아이디
As
Declare @UID Int
Select @UID = UID From Users
Where DomainID = @DomainID And Password = @OriginalPassword
If @UID > 0
Begin
Update UserProfiles
Set Password = @NewPassword
Where UID = @UID
Return 1 -- 업데이트 함
--Select @@RowCount
End
Else
Return -1 -- -1이 반환되면 업데이트 미적용
Go
--[6] 현재 접속자의 프로필 정보를 업데이트 : UpdateUser
Create Proc dbo.UpdateUser
@Email VarChar(100), -- 이메일
@Description VarChar(255), -- 설명(소개)
@DomainID VarChar(30) -- 현재 접속자
As
Declare @UID Int
Select @UID = UID From Domains Where DomainID = @DomainID
If @UID Is Not Null
Begin
Update Domains Set Description = @Description
Where UID = @UID
Update UserProfiles Set Email = @Email
Where UID = @UID
End
Select @@RowCount -- 업데이트된 내용이 있으면 1반환
Go
--[7] 관리자가 사용자 정보 업데이트
Create Proc dbo.UpdateUserAdmin
@DomainID VarChar(30), -- 현재 사용자
@Name VarChar(50), -- 이름
@Password VarChar(50), -- 암호
@Email VarChar(100), -- 이메일
@Description VarChar(255), -- 설명(소개)
@Blocked Bit, -- 잠금 여부(0 또는 1)
@UID Int -- 현재 사용자 UID
As
Begin
Update Domains -- 아이디, 이름, 설명
Set
DomainID = @DomainID, Name = @Name,
Description = @Description
Where UID = @UID
Update UserProfiles -- 이메일, 암호, 잠금 여부
Set Email = @Email, Password = @Password,
Blocked = @Blocked
Where UID = @UID
End
Select @@RowCount -- 업데이트된 내용이 있으면 1반환
Go
--[8] 현재 접속자에 포함된 그룹 정보 읽어오기
Create Proc dbo.GetGroupByUID
@UID Int
As
Select GroupUID, DomainID, Name
From Membership, Groups
Where
Membership.GroupUID = Groups.UID
And
Membership.UserUID = @UID
Order By DomainID Asc
Go
GetGroupByUID 8
Go
--[9] 현재 사용자가 취득하지 못한 정보를 읽어오기 : Everyone 제외
Create Proc dbo.GetGroupNotInUse
@UID Int
As
Select UID As GroupUID, DomainID, Name
From Groups
Where UID NOT IN (
Select GroupUID From Membership
Where UserUID = @UID
)
And
DomainID <> 'Everyone'
Order By DomainID
Go
Exec GetGroupNotInUse 5
Go
Select * From Membership Where GroupUID In (1, 2, 3)
Go
--[10] 그룹을 추가하는 저장 프로시저 : AddNewGroup
Create Proc dbo.AddNewGroup
@GroupUID VarChar(50),
@Name VarChar(50),
@Description VarChar(255)
As
Insert Domains(DomainID, Name, Description, Type)
Values(@GroupUID, @Name, @Description, 'Group')
Go
--[10] 그룹을 추가하는 저장 프로시저 : AddNewGroup
Create Proc dbo.AddNewGroup
@GroupUID VarChar(50),
@Name VarChar(50),
@Description VarChar(255),
--
@UID Int Output
As
--[!] 해당 그룹이 이미 등록되어있으면
Declare @Result Int
Select @Result = Count(*) From Domains Where DomainID = @GroupUID
If @Result > 0
Return -1 -- -1이 반환되면 회원가입 안됨...
Insert Domains(DomainID, Name, Description, Type)
Values(@GroupUID, @Name, @Description, 'Group')
Select @UID = @@Identity -- Domains 테이블의 가장 마지막 입력된 UID값
--[!] UID값 반환
Select @UID
Go
--[11] 그룹리스트를 출력하는 저장 프로시저 : GetGroups
Create Proc dbo.GetGroups
As
Select * From Groups
Order By UID Asc
Go