設計図: 履歴情報ヘッダー

---

■ TABLE

HistoryHeader

各ステーションごとの HTTP ヘッダログです。

　

テーブル設計

FIELD	TYPE	KEY	NULL	DEFAULT	NOTE
HistoryID	longkey	PRIMARY	-	AUTO	主キー
RecordTime	datetime		-	GETDATE()	履歴登録日
StationID	key	FOREIGN	OK		対象となるステーション ID
RemoteAddr	IPv4		OK		クライアント IP
AgentID	longkey	FOREIGN	OK		ユーザーエージェント

　

外部キー

FIELD	REFERENCE	NOTE
AgentID	HistoryHeaderAgent.AgentID
StationID	StationInformation.StationID

　

HistoryHeaderRelay

各 HistoryHeader ごとの以降のログです。

　

テーブル設計

FIELD	TYPE	KEY	NULL	DEFAULT	NOTE
HistoryID	longkey	PRIMARY	-		主キー / 履歴 ID
RelayID	key	PRIMARY	-		主キー
RegistDate	datetime		-	GETDATE()	記録日時
TargetPath	varchar/256		OK		要求 URL
Referer	varchar/256		OK		リンク元 URL
QueryString	varchar/128		OK		引数
RequestMethod	varchar/16		OK		要求メソッド

　

外部キー

FIELD	REFERENCE	NOTE
HistoryID	HistoryHeader.ID

　

　

HistoryHeaderAgent

HTTP ヘッダから取得した UserAgent の履歴です。

　

テーブル設計

FIELD	TYPE	KEY	NULL	DEFAULT	NOTE
AgentID	longkey	PRIMARY	-		主キー
Name	varchar/128		-		ユーザーエージェント
Note	nvarchar/200		OK		備考欄
URL	varchar/256		OK		関連 URL
AccessCount	int		-	1	アクセス数
UpdateTime	datetime		-	GETDATE()	更新日

　

■ VIEW

ViewHistoryHerderListing

履歴の一覧表示用ビューです。HistoryHeaderRelay テーブルも参照し、そのなかの RelayID = 1 のものを採用します。

　

ビュー設計

FIELD	ORG-TABLE	ORG-FIELD	NOTE
HistoryID	HistoryHeader
RecordTime	HistoryHeader
StationID	StationInformation
StationName	StationInformation	Name
StationURL	StationInformation	Homepage
BasicColor	StationInformation
RemoteAddr	HistoryHeader
AgentID	HistoryHeaderAgent
AgentName	HistoryHeaderAgent	Name
AgentURL	HistoryHeaderAgent	URL
TargetPath	HistoryHeaderRelay
Referer	HistoryHeaderRelay
QueryString	HistoryHeaderRelay
RequestMethod	HistoryHeaderRelay

　

実装

SELECT dbo.HistoryHeader.HistoryID, dbo.HistoryHeader.RecordTime,
dbo.StationInformation.StationID, dbo.StationInformation.Name,
dbo.StationInformation.Homepage, dbo.StationInformation.BasicColor,
dbo.HistoryHeader.RemoteAddr, dbo.HistoryHeaderAgent.AgentID,
dbo.HistoryHeaderAgent.Name AS AgentName, dbo.HistoryHeaderAgent.URL,
dbo.HistoryHeaderRelay.TargetPath, dbo.HistoryHeaderRelay.Referer,
dbo.HistoryHeaderRelay.QueryString,
dbo.HistoryHeaderRelay.RequestMethod
FROM dbo.HistoryHeader LEFT OUTER JOIN
dbo.StationInformation ON
dbo.HistoryHeader.StationID = dbo.StationInformation.StationID LEFT OUTER JOIN
dbo.HistoryHeaderAgent ON
dbo.HistoryHeader.AgentID = dbo.HistoryHeaderAgent.AgentID LEFT OUTER JOIN
dbo.HistoryHeaderRelay ON
dbo.HistoryHeader.HistoryID = dbo.HistoryHeaderRelay.HistoryID AND
dbo.HistoryHeaderRelay.RelayID = 1

　

■ STORED PROCEDURE

sp_HistoryHeaderRegist

ヘッダログを記録します。

　

引数

PARAM	TYPE	DEFAULT	NOTE
@StationID	key		対象のステーション
@RemoteAddr	IPv4	NULL	要求元クライアント
@Agent	varchar/128	NULL	要求元エージェント

　

戻り値

PARAM	TYPE	NOTE
HistoryID	longkey	記録された履歴 ID

　

実装

CREATE PROCEDURE [sp_HistoryHeaderRegist]

@StationID [key],
@RemoteAddr IPv4 = NULL,
@Agent VARCHAR(128) = NULL

AS
SET NOCOUNT ON

DECLARE @AgentID [longkey]


-- AgentID の決定処理

SELECT @AgentID = AgentID FROM [HistoryHeaderAgent] WHERE [Name] = @Agent

IF @AgentID IS NULL
BEGIN
INSERT INTO [HistoryHeaderAgent] ([Name]) VALUES (@Agent)
SET @AgentID = @@IDENTITY
END
ELSE
BEGIN
UPDATE [HistoryHeaderAgent] SET [AccessCount] = [AccessCount] + 1, [UpdateTime] = GETDATE()
WHERE [AgentID] = @AgentID
END

-- Header ログの記録

INSERT INTO [HistoryHeader]
(
[StationID], [RemoteAddr], [AgentID]
)
VALUES
(
@StationID, @RemoteAddr, @AgentID
)

SELECT @@IDENTITY AS [HistoryID]

sp_HistoryHeaderRelayRegist

ヘッダログの追尾を記録します。

　

引数

PARAM	TYPE	DEFAULT	NOTE
@HistoryID	longkey		元の履歴 ID
@RelayID	key	0	追尾 ID
@TargetPath	varchar/256	NULL	要求 URL
@Referer	varchar/256	NULL	リンク元
@QueryString	varchar/128	NULL	引数
@RequestMethod	varchar/16	NULL	メソッド

　

戻り値

PARAM	TYPE	NOTE
RelayID	key	登録した ID

　

実装

CREATE PROCEDURE [sp_HistoryHeaderRelayRegist]

@HistoryID [longkey],
@RelayID [key] = 0,
@TargetPath VARCHAR(256) = NULL,
@Referer VARCHAR(256) = NULL,
@QueryString VARCHAR(128) = NULL,
@RequestMethod VARCHAR(16) = NULL

AS
SET NOCOUNT ON

-- HistoryID が適切な場合のみ追記

IF @HistoryID > 0
BEGIN

INSERT INTO [HistoryHeaderRelay]
(
[HistoryID], [RelayID], [TargetPath], [Referer], [QueryString], [RequestMethod]
)
VALUES
(
@HistoryID, @RelayID, @TargetPath, @Referer, @QueryString, @RequestMethod
)

SET @RelayID = @RelayID + 1
END

SELECT @RelayID AS [RelayID]