設計図: 履歴情報
DESIGN
■ TABLE
HistoryUpdate
各ステーションごとの更新履歴を記録します。
テーブル設計
FIELD TYPE KEY NULL DEFAULT NOTE UpdateID longkey PRIMARY - AUTO 主キー StationID key FOREIGN OK 対象となるステーション ID SectionID shortkey OK 対象となる履歴の区分 ID UpdateTime datetime - GETDATE() 履歴登録日 TypeID shortkey FOREIGN - 種別 ID Caption nvarchar/32 - 履歴の見出し文字列 Detail nvarchar/200 - 履歴の詳細情報 URL varchar/256 OK 関連 URL ViewCount int - 0 参照カウント LastViewDate datetime OK 最終参照日
外部キー
FIELD REFERENCE NOTE TypeID HistoryType.TypeID StationID HistorySection.StationID SectionID HistorySection.SectionID
トリガ
更新履歴が追加された場合に、StationInformation の LastUpdate の日付を自動的に更新します。 このテーブルは追加が一般的なため、削除および再編集では更新日時の変更は行いません。
CREATE TRIGGER [tr_HistoryUpdate]
ON dbo.HistoryUpdate
FOR INSERT
NOT FOR REPLICATION
AS
-- 新規追加された履歴に対する StationInformation を更新します。
UPDATE StationInformation
SET LastUpdate = GETDATE()
WHERE StationID IN (SELECT DISTINCT StationID FROM INSERTED)
RETURN
HistoryType
履歴情報の種別です。
テーブル設計
FIELD TYPE KEY NULL DEFAULT NOTE TypeID shortkey PRIMARY - 主キー Name nvarchar/16 - 種別名 Note nvarchar/200 OK 備考欄
HistorySection
履歴情報の種別です。
テーブル設計
FIELD TYPE KEY NULL DEFAULT NOTE StationID key PRIMARY - 主キー SectionID shortkey PRIMARY - 主キー Name nvarchar/16 - 種別名 Note nvarchar/200 OK 備考欄
外部キー
FIELD REFERENCE NOTE StationID StationInformation.StationID
■ VIEW
ViewHistorySection
履歴区分表示用のビューです。
ビュー設計
FIELD ORG-TABLE ORG-FIELD NOTE StationID HistorySection SectionID HistorySection SectionName HistorySection Name StationName StationInformation Name StationURL StationInformation Homepage BasicColor StationInformation Note HistorySection
実装
SELECT dbo.HistorySection.StationID, dbo.HistorySection.SectionID,
dbo.HistorySection.Name AS SectionName,
dbo.StationInformation.Name AS StationName,
dbo.StationInformation.Homepage AS StationURL, dbo.StationInformation.BasicColor,
dbo.HistorySection.Note
FROM dbo.HistorySection LEFT OUTER JOIN
dbo.StationInformation ON
dbo.HistorySection.StationID = dbo.StationInformation.StationID
ViewHistoryUpdateListing
更新履歴の一覧表示用ビューです。
ビュー設計
FIELD ORG-TABLE ORG-FIELD NOTE UpdateID HistoryUpdate UpdateTime HistoryUpdate TypeID HistoryType TypeName HistoryType Name StationID StationInformation StationName StationInformation Name StationURL StationInformation Homepage BasicColor StationInformation Caption HistoryUpdate Detail HistoryUpdate HistoryURL HistoryUpdate URL ViewCount HistoryUpdate LastViewDate HistoryUpdate
実装
SELECT dbo.HistoryUpdate.UpdateID, dbo.HistoryUpdate.UpdateTime,
dbo.HistoryType.TypeID, dbo.HistoryType.Name AS TypeName,
dbo.StationInformation.StationID, dbo.StationInformation.Name AS StationName,
dbo.StationInformation.Homepage AS StationURL, dbo.StationInformation.BasicColor,
dbo.HistoryUpdate.Caption, dbo.HistoryUpdate.Detail,
dbo.HistoryUpdate.URL AS HistoryURL, dbo.HistoryUpdate.ViewCount
dbo.HistoryUpdate.LastViewDate
FROM dbo.HistoryUpdate LEFT OUTER JOIN
dbo.StationInformation ON
dbo.HistoryUpdate.StationID = dbo.StationInformation.StationID LEFT OUTER JOIN
dbo.HistoryType ON dbo.HistoryUpdate.TypeID = dbo.HistoryType.TypeID
■ STORED PROCEDURE
sp_HistoryUpdateGet
更新履歴の一覧表示用ビューである ViewHistoryUpdateListing から、指定した情報を取得します。その際、参照カウント [ViewCount] の値を増加させます。
引数
PARAM TYPE DEFAULT NOTE @UpdateID longkey 対象の更新履歴 ID @StationID key NULL 対象のステーション
戻り値
PARAM TYPE NOTE ViewHistoryUpdateListing.* -
実装
CREATE PROCEDURE [sp_HistoryUpdateGet]
@HistoryID [longkey],
@StationID [key] = NULL
AS
SET NOCOUNT ON
DECLARE @HistoryExist BIT
SET @HistoryExist = 0
-- 目的の履歴が存在するかを調べる。
IF @StationID IS NULL
BEGIN
IF EXISTS
(
SELECT UpdateID FROM HistoryUpdate
WHERE UpdateID = @HistoryID
)
SET @HistoryExist = 1
END
ELSE
BEGIN
IF EXISTS
(
SELECT UpdateID FROM HistoryUpdate
WHERE UpdateID = @HistoryID AND StationID = @StationID
)
SET @HistoryExist = 1
END
IF @HistoryExist = 1
BEGIN
-- 参照回数の増加
UPDATE HistoryUpdate SET ViewCount = ViewCount + 1, LastViewDate = GETDATE()
WHERE UpdateID = @HistoryID
END
-- 目的の結果を返す
IF @StationID IS NULL
SELECT * FROM ViewHistoryUpdateListing
WHERE UpdateID = @HistoryID
ELSE
SELECT * FROM ViewHistoryUpdateListing
WHERE UpdateID = @HistoryID AND StationID = @StationID