---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第8章 ビュー
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 8.1 標準SQLビューの設計
---------------------------------------------------------------------

CREATE VIEW dbo.vInventory
AS
SELECT dbo.Inventory.Inventoryid,
       dbo.Equipment.Make,
       dbo.Equipment.Model,
       dbo.Location.Location,
       dbo.Status.Status,
       dbo.Contact.FirstName,
       dbo.Contact.LastName,
       dbo.Inventory.Cost,
       dbo.AcquisitionType.AcquisitionType,
       dbo.Location.Address,
       dbo.Location.City,
       dbo.Location.ProvinceId,
       dbo.Location.Country,
       dbo.EqType.EqType,
       dbo.Contact.Phone,
       dbo.Contact.Fax,
       dbo.Contact.Email,
       dbo.Contact.UserName,
       dbo.Inventory.Rent,
       dbo.Inventory.EqId,
       dbo.Inventory.LocationId,
       dbo.Inventory.StatusId,
       dbo.Inventory.OwnerId,
       dbo.Inventory.AcquisitionTypeID,
       dbo.Contact.OrgUnitId
FROM dbo.EqType
RIGHT OUTER JOIN dbo.Equipment
    ON dbo.EqType.EqTypeId = dbo.Equipment.EqTypeId
RIGHT OUTER JOIN dbo.Inventory
INNER JOIN dbo.Status
    ON dbo.Inventory.StatusId = dbo.Status.StatusId
LEFT OUTER JOIN dbo.AcquisitionType
    ON dbo.Inventory.AcquisitionTypeID = dbo.AcquisitionType.AcquisitionTypeId
    ON dbo.Equipment.EqId = dbo.Inventory.EqId
LEFT OUTER JOIN dbo.Location
    ON dbo.Inventory.LocationId = dbo.Location.LocationId
LEFT OUTER JOIN dbo.Contact
    ON dbo.Inventory.OwnerId = dbo.Contact.ContactId


SELECT dbo.vInventory.EqId,
       dbo.vInventory.Make,
       dbo.vInventory.Model,
       dbo.vInventory.Status
FROM dbo.vInventory
WHERE LocationId = 2


CREATE VIEW dbo.vInventoryCost
WITH SCHEMABINDING
AS
SELECT ET.EqType, e.Make, e.Model, SUM(Cost) TotalCost, COUNT(*) Count
FROM dbo.Inventory I
INNER JOIN dbo.Equipment e
    ON i.EqId = e.EqId
INNER JOIN dbo.EqType ET
    ON e.EqTypeId = ET.EqTypeId
WHERE Cost IS NOT NULL
GROUP BY ET.EqType, e.Make, e.Model


CREATE VIEW dbo.vInventory_Ordered
AS
SELECT TOP 100 PERCENT
dbo.Inventory.Inventoryid,
dbo.Equipment.Make,
dbo.Equipment.Model
FROM dbo.Equipment
RIGHT OUTER JOIN dbo.Inventory
    ON dbo.Equipment.EqId = dbo.Inventory.EqId
ORDER BY dbo.Equipment.Make, dbo.Equipment.Model


UPDATE dbo.vInventory
SET Cost = 2000
WHERE InventoryId = 1234


CREATE VIEW vInventoryTrigonTower
AS
SELECT *
FROM dbo.vInventory
WHERE LocationId = 2
WITH CHECK OPTION
GO

UPDATE dbo.vInventoryTrigonTower
SET LocationId = 10
WHERE InventoryId = 6

---------------------------------------------------------------------
-- 8.2 動的ビュー
---------------------------------------------------------------------

CREATE FUNCTION dbo.fnInventoryByLocationId(@LocationId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT *
        FROM dbo.vInventory
        WHERE LocationId = @LocationId
)


SELECT * FROM dbo.fnInventoryByLocationId(2)

---------------------------------------------------------------------
-- 8.3 一時ビュー── 共通テーブル式
---------------------------------------------------------------------

WITH CompOrg(ParentID, CompOrgCount) AS
(
    SELECT ParentOrgUnitID, COUNT(*)
    FROM dbo.OrgUnit
    WHERE ParentOrgUnitID IS NOT NULL
    GROUP BY ParentOrgUnitID
)
SELECT OrgUnit.OrgUnit, CompOrgCount
FROM CompOrg p
INNER JOIN dbo.OrgUnit OrgUnit
    ON p.ParentId = OrgUnit.OrgUnitId
ORDER BY OrgUnit.OrgUnit


WITH SubOrg(ParentOrgUnitID, OrgUnitID, OrgLevel) AS
(
    -- アンカーメンバ（最初の反復）
    SELECT ParentOrgUnitID, OrgUnitID, 0 AS OrgLevel
    FROM dbo.OrgUnit
    WHERE ParentOrgUnitID IS NULL
    UNION ALL
    -- 再帰メンバ（後続の反復）
    SELECT o.ParentOrgUnitID, o.OrgUnitID, p.OrgLevel + 1
    FROM dbo.OrgUnit o
    INNER JOIN SubOrg p
        ON o.ParentOrgUnitID = p.OrgUnitID
)
SELECT s.OrgUnitID, o.OrgUnit, s.ParentOrgUnitID, s.OrgLevel
FROM SubOrg s
INNER JOIN dbo.OrgUnit o
    ON o.OrgUnitId = s.OrgUnitId
ORDER BY s.OrgLevel


WITH SubOrg(ParentOrgUnitID, OrgUnitID, OrgLevel) AS
(
    SELECT ParentOrgUnitID, OrgUnitID, 0 AS OrgLevel
    FROM dbo.OrgUnit
    WHERE ParentOrgUnitID IS NULL
    UNION ALL
    SELECT o.ParentOrgUnitID, o.OrgUnitID, p.OrgLevel + 1
    FROM dbo.OrgUnit o
    INNER JOIN SubOrg p
        ON o.ParentOrgUnitID = p.OrgUnitID
)
SELECT s.OrgUnitID, o.OrgUnit, s.ParentOrgUnitID, s.OrgLevel
FROM SubOrg s
INNER JOIN dbo.OrgUnit o
    ON o.OrgUnitId = s.OrgUnitId
ORDER BY s.OrgLevel
OPTION (MAXRECURSION 10);

---------------------------------------------------------------------
-- 8.5 インデックス付きビュー
---------------------------------------------------------------------

CREATE VIEW vLaptopInventory
WITH SCHEMABINDING
AS
SELECT i.Inventoryid, i.EqId, i.StatusId, e.Make, e.Model
FROM dbo.Inventory i
INNER JOIN dbo.Equipment e
    ON i.EqId = e.EqId
WHERE EqTypeId = 1
GO

CREATE UNIQUE CLUSTERED INDEX idxvLaptopInventory
ON vLaptopInventory (InventoryID)


SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIERS ON
SET NUMERIC_ROUNDABORT OFF


CREATE INDEX idxvLaptopInventory_MakeModel
ON dbo.vLaptopInventory (Make, Model)

---------------------------------------------------------------------
-- 8.6 パーティションビュー
---------------------------------------------------------------------

CREATE VIEW dbo.vOrderItem
AS
SELECT * FROM dbo.OrderItem2005
UNION ALL
SELECT * FROM dbo.OrderItem2006
UNION ALL
SELECT * FROM dbo.OrderItem2007


CREATE TABLE dbo.InventoryPrim
(
    Inventoryid     INT         NOT NULL,
    Make            VARCHAR(50) NULL,
    Model           VARCHAR(50) NULL,
    Location        VARCHAR(50) NULL,
    FirstName       VARCHAR(30) NULL,
    LastName        VARCHAR(30) NULL,
    UserName        VARCHAR(50) NULL,
    EqType          VARCHAR(50) NULL,
    CONSTRAINT PK_InventoryPrim PRIMARY KEY CLUSTERED(Inventoryid) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE dbo.InventorySec
(
    Inventoryid     INT         NOT NULL,
    AcquisitionType VARCHAR(12) NULL,
    Address         VARCHAR(50) NULL,
    City            VARCHAR(50) NULL,
    ProvinceId      CHAR(3)     NULL,
    Country         VARCHAR(50) NULL,
    EqType          VARCHAR(50) NULL,
    Phone           typPhone    NULL,
    Fax             typPhone    NULL,
    Email           typEmail    NULL,
    CONSTRAINT PK_InventorySec PRIMARY KEY CLUSTERED(Inventoryid) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE VIEW dbo.vInventoryVertSplit
AS
SELECT IP.Inventoryid, IP.Make,      IP.Model,
       IP.Location,    IP.FirstName, IP.LastName,
       IP.UserName,    IP.EqType,    ISec.AcquisitionType,
       ISec.Address,   ISec.City,    ISec.ProvinceId,
       ISec.Country,   ISec.Phone,   ISec.Fax,
       ISec.Email
FROM dbo.InventoryPrim IP
FULL JOIN dbo.InventorySec ISec
    ON IP.Inventoryid = ISec.Inventoryid


SET STATISTICS PROFILE ON
SELECT Make, Model, Location, UserName
    FROM dbo.vInventoryVertSplit
WHERE Inventoryid = 1041


CREATE VIEW dbo.vSales
AS
SELECT * FROM Sales.dbo.OrderItem2007
UNION ALL
SELECT * FROM Beta.Sales.dbo.OrderItem2006
UNION ALL
SELECT * FROM Gamma.Sales.dbo.OrderItem2005


-- CANADAサーバーで実行
CREATE TABLE dbo.InventoryCanada
(
   Inventoryid     INT         NOT NULL,
   Make            VARCHAR(50) NULL,
   Model           VARCHAR(50) NULL,
   Location        VARCHAR(50) NULL,
   FirstName       VARCHAR(30) NULL,
   LastName        VARCHAR(30) NULL,
   AcquisitionType VARCHAR(12) NULL,
   Address         VARCHAR(50) NULL,
   City            VARCHAR(50) NULL,
   ProvinceId      CHAR(3)     NULL,
   Country         VARCHAR(50) NOT NULL,
   EqType          VARCHAR(50) NULL,
   Phone           typPhone    NULL,
   Fax             typPhone    NULL,
   Email           typEmail    NULL,
   UserName        VARCHAR(50) NULL,
   CONSTRAINT PK_InventoryCanada PRIMARY KEY CLUSTERED
   (
      Country,
      Inventoryid
   )  ON [PRIMARY],
   CONSTRAINT chkInventoryCanada CHECK (Country = 'Canada')
) ON [PRIMARY]
GO

-- USAサーバーで実行
CREATE TABLE dbo.InventoryUSA
(
   Inventoryid     INT         NOT NULL,
   Make            VARCHAR(50) NULL,
   Model           VARCHAR(50) NULL,
   Location        VARCHAR(50) NULL,
   FirstName       VARCHAR(30) NULL,
   LastName        VARCHAR(30) NULL,
   AcquisitionType VARCHAR(12) NULL,
   Address         VARCHAR(50) NULL,
   City            VARCHAR(50) NULL,
   ProvinceId      CHAR(3)     NULL,
   Country         VARCHAR(50) NOT NULL,
   EqType          VARCHAR(50) NULL,
   Phone           typPhone    NULL,
   Fax             typPhone    NULL,
   Email           typEmail    NULL,
   UserName        VARCHAR(50) NULL,
   CONSTRAINT PK_InventoryUS PRIMARY KEY CLUSTERED
   (
      Country,
      Inventoryid
   )  ON [PRIMARY],
   CONSTRAINT chkInventoryUSA CHECK (Country = 'USA')
) ON [PRIMARY]
GO

-- WORLDサーバーで実行
CREATE TABLE dbo.InventoryWorld
(
   Inventoryid     INT         NOT NULL,
   Make            VARCHAR(50) NULL,
   Model           VARCHAR(50) NULL,
   Location        VARCHAR(50) NULL,
   FirstName       VARCHAR(30) NULL,
   LastName        VARCHAR(30) NULL,
   AcquisitionType VARCHAR(12) NULL,
   Address         VARCHAR(50) NULL,
   City            VARCHAR(50) NULL,
   ProvinceId      CHAR(3)     NULL,
   Country         VARCHAR(50) NOT NULL,
   EqType          VARCHAR(50) NULL,
   Phone           typPhone    NULL,
   Fax             typPhone    NULL,
   Email           typEmail    NULL,
   UserName        VARCHAR(50) NULL,
   CONSTRAINT PK_InventoryWorld PRIMARY KEY CLUSTERED
   (
      Country,
      Inventoryid
   )  ON [PRIMARY],
   CONSTRAINT chkInventoryWorld CHECK (Country IN ('UK',
               'Ireland', 'Australia'))
) ON [PRIMARY]
GO


EXEC sp_addlinkedserver N'(local)\USA', N'SQL Server'
GO
EXEC sp_addlinkedserver N'(local)\WORLD', N'SQL Server'
GO


USE master
EXEC sp_serveroption '(local)\USA', 'lazy schema validation', 'true'
EXEC sp_serveroption '(local)\WORLD', 'lazy schema validation', 'true'


USE Asset5
GO
CREATE VIEW dbo.vInventoryDist
AS
SELECT * FROM Asset5.dbo.InventoryCanada
UNION ALL
SELECT * FROM [(local)\USA].Asset5.dbo.InventoryUSA
UNION ALL
SELECT * FROM [(local)\WORLD].Asset5.dbo.InventoryWorld
GO


SET STATISTICS PROFILE ON
SELECT * FROM dbo.vInventoryDist
WHERE Country = 'UK'


SET STATISTICS PROFILE ON
SELECT * FROM vInventoryDist
WHERE Country = 'CANADA'


CREATE PROCEDURE dbo.ap_Inventory_ListDist @chvCountry VARCHAR(50)
AS
SELECT * FROM dbo.vInventoryDist WHERE Country = @chvCountry


SET STATISTICS PROFILE ON
EXEC dbo.ap_Inventory_ListDist 'CANADA'


SET XACT_ABORT ON
UPDATE vInventoryDist
SET UserName = 'unknown'
WHERE UserName IS NULL
AND Country = 'Canada'

---------------------------------------------------------------------
-- 8.7 ビューの用途
---------------------------------------------------------------------

CREATE VIEW dbo.vInventoryTrigonTower
AS
SELECT * FROM dbo.vInventory
WHERE LocationId = 2


CREATE VIEW util.vSpaceUsed
AS
SELECT DISTINCT TOP 100 PERCENT
    DB_NAME()             AS TABLE_CATALOG,
    USER_NAME(obj.uid)    AS TABLE_SCHEMA,
    obj.name              AS TABLE_NAME,
    CASE obj.xtype
        WHEN 'U' THEN 'BASE TABLE'
        WHEN 'V' THEN 'VIEW'
    END                   AS TABLE_TYPE,
    obj.ID                AS TABLE_ID,
    COALESCE
    (
        (
            SELECT SUM(reserved)
            FROM sysindexes i1
            WHERE i1.id = obj.id
            AND i1.indid IN(0, 1, 255)
        ) *
        (
            SELECT d.low
            FROM master.dbo.spt_values d
            WHERE d.number = 1
            AND d.type = 'E'
        ),
        0
    )                     AS RESERVED,
    COALESCE
    (
        (
            SELECT SUM(reserved) - SUM(used)
            FROM sysindexes i2
            WHERE i2.indid IN(0, 1, 255)
            AND id = obj.id
        ) *
        (
            SELECT d.low
            FROM master.dbo.spt_values d
            WHERE d.number = 1
            AND d.type = 'E'
        ),
        0
    )                    AS UNUSED,
    CASE obj.xtype
        WHEN 'U' THEN COALESCE
                      (
                          (
                              SELECT i3.rows
                              FROM sysindexes i3
                              WHERE i3.indid < 2
                              AND i3.id = obj.id
                          ),
                          0
                      )
        WHEN 'V' THEN NULL
    END                   AS [ROWS],
    COALESCE
    (
        (
            (
                SELECT SUM(dpages)
                FROM sysindexes
                WHERE indid < 2
                AND id = obj.id
            ) +
            (
                SELECT ISNULL(SUM(used), 0)
                FROM sysindexes
                WHERE indid = 255
                AND id = obj.id
            )
        ) *
        (
            SELECT d.low
            FROM master.dbo.spt_values d
            WHERE d.number = 1
            AND d.type = 'E'
        ),
        0
    )                   AS [DATA],
    COALESCE
    (
        (
            (
                SELECT SUM(reserved)
                FROM sysindexes i1
                WHERE i1.id = obj.id
                AND i1.indid IN(0, 1, 255)
            ) -
            (
                (
                    SELECT SUM(dpages)
                    FROM sysindexes
                    WHERE indid < 2
                    AND id = obj.id
                ) +
                (
                    SELECT ISNULL(SUM(used), 0)
                    FROM sysindexes
                    WHERE indid = 255
                    AND id = obj.id
                )
            )
        ) *
        (
            SELECT d.low
            FROM master.dbo.spt_values d
            WHERE d.number = 1
            AND d.type = 'E'
        ),
        0
    )                 AS [INDEX]
FROM sysobjects obj
WHERE obj.xtype IN('U', 'V')
AND PERMISSIONS(obj.id) != 0
ORDER BY DB_NAME(), USER_NAME(obj.uid), obj.name

