設計図: サイト情報
DESIGN
■ TABLE
StationCounter
各ステーションのアクセス数を格納します。
テーブル設計
FIELD TYPE KEY NULL DEFAULT NOTE CounterID longkey PRIMARY - AUTO 主キー StationID key FOREIGN - ステーション ID RegistYear smallint - DATEPART(YEAR, GETDATE()) 登録された年 RegistMonth tinyint - DATEPART(MONTH, GETDATE()) 登録された月 RegistDay tinyint - DATEPART(DAY, GETDATE()) 登録された日 RegistHour tinyint - DATEPART(HOUR, GETDATE()) 登録された時間 TotalCount int - 1 １時間毎のアクセス数
外部キー
FIELD REFERENCE NOTE StationID StationInformation.StationID
StationInformation
各ステーションの情報です。
テーブル設計
FIELD TYPE KEY NULL DEFAULT NOTE StationID key PRIMARY - 主キー Name nvarchar/64 - 名称 Homepage varchar/256 OK サイトのアドレス Note nvarchar/200 OK 備考欄 BasicColor varchar/8 OK 基調となる色 LastUpdate datetime - GETDATE() 各種データベース更新時にトリガによって自動更新
StationMessage
各ステーションのショートメッセージです。
テーブル設計
FIELD TYPE KEY NULL DEFAULT NOTE MessageID longkey PRIMARY - IDENTITY(1, ) 主キー StationID key FOREIGN - ステーション ID RegistDate datetime - GETDATE() 登録日 Message nvarchar/200 - メッセージ本文
外部キー
FIELD REFERENCE NOTE StationID StationInformation.StationID
トリガ
メッセージが追加された場合に、StationInformation の LastUpdate の日付を自動的に更新します。 このテーブルは追加が一般的なため、削除および再編集では更新日時の変更は行いません。
CREATE TRIGGER [tr_StationMessage]
ON dbo.StationMessage
FOR INSERT
NOT FOR REPLICATION
AS
UPDATE StationInformation
SET LastUpdate = GETDATE()
WHERE StationID IN (SELECT DISTINCT StationID FROM INSERTED)
RETURN
■ VIEW
ViewStationCounterTotalAccess
各ステーションの総アクセス数を集計します。
ビュー設計
FIELD ORG-TABLE ORG-FIELD NOTE StationID StationCounter TotalCount StationCounter SUM(TotalCount) アクセス総数
実装
SELECT StationID, SUM(TotalCount) AS TotalCount
FROM dbo.StationCounter
GROUP BY StationID
ViewStationCounterYearlyAccess
年別のアクセス数を集計します。
ビュー設計
FIELD ORG-TABLE ORG-FIELD NOTE StationID StationCounter TotalCount StationCounter SUM(TotalCount) アクセス総数／年 RegistYear StationCounter
実装
SELECT StationID, SUM(TotalCount) AS TotalCount, RegistYear
FROM dbo.StationCounter
GROUP BY StationID, RegistYear
ViewStationCounterMonthlyAccess
月別のアクセス数を集計します。
ビュー設計
FIELD ORG-TABLE ORG-FIELD NOTE StationID StationCounter TotalCount StationCounter SUM(TotalCount) アクセス総数／月 RegistYear StationCounter RegistMonth StationCounter
実装
SELECT StationID, SUM(TotalCount) AS TotalCount, RegistYear, RegistMonth
FROM dbo.StationCounter
GROUP BY StationID, RegistYear, RegistMonth
ViewStationCounterDailyAccess
日別のアクセス数を集計します。
ビュー設計
FIELD ORG-TABLE ORG-FIELD NOTE StationID StationCounter TotalCount StationCounter SUM(TotalCount) アクセス総数／日 RegistYear StationCounter RegistMonth StationCounter RegistDay StationCounter
実装
SELECT StationID, SUM(TotalCount) AS TotalCount, RegistYear, RegistMonth, RegistDay
FROM dbo.StationCounter
GROUP BY StationID, RegistYear, RegistMonth, RegistDay
■ STORED PROCEDURE
sp_StationCounterTotalAccess
指定したステーションの総アクセス数を取得します。ステーションを指定しなかった場合には、全てのステーションの総アクセス数を取得します。
引数
PARAM TYPE DEFAULT NOTE @StationID key NULL 対象の StationID
戻り値
PARAM TYPE NOTE TotalCount int 総アクセス数
実装
CREATE PROCEDURE [sp_StationCounterTotalAccess]
@StationID INT = NULL
AS
SET NOCOUNT ON
DECLARE @Count INT
IF @StationID IS NULL
SELECT @Count = SUM(TotalCount) FROM StationCounter
ELSE
SELECT @Count = SUM(TotalCount) FROM StationCounter WHERE StationID = @StationID
SELECT @Count AS TotalCount
sp_StationCounterAdd
指定したステーションのアクセスカウントを増加させます。
引数
PARAM TYPE DEFAULT NOTE @StationID key 対象の StationID @AddValue int 1 アクセスカウンタの増加値
戻り値
PARAM TYPE NOTE NewCount int 増加後の通算アクセス数
実装
CREATE PROCEDURE [sp_StationCounterAdd]
@StationID [key],
@AddValue INT = 1
AS
SET NOCOUNT ON
DECLARE @Year SMALLINT
DECLARE @Month TINYINT
DECLARE @Day TINYINT
DECLARE @Hour TINYINT
DECLARE @Time DATETIME
DECLARE @CounterID [longkey]
SET @Time = GETDATE()
SET @Year = DATEPART(YEAR, @Time)
SET @Month = DATEPART(MONTH, @Time)
SET @Day = DATEPART(DAY, @Time)
SET @Hour = DATEPART(HOUR, @Time)
SELECT @CounterID = CounterID FROM StationCounter
WHERE StationID = @StationID
AND RegistYear = @Year AND RegistMonth = @Month
AND RegistDay = @Day AND RegistHour = @Hour
IF @CounterID IS NULL
INSERT INTO StationCounter
(
StationID, RegistYear, RegistMonth, RegistDay, RegistHour
)
VALUES
(
@StationID, @Year, @Month, @Day, @Hour
)
ELSE
UPDATE StationCounter
SET TotalCount = TotalCount + @AddValue
WHERE CounterID = @CounterID
-- 最終出力
SELECT SUM(TotalCount) AS NewCount
FROM StationCounter
WHERE StationID = @StationID