-- afsdb.ddl -- DDL for the AFS reporting database.
-- $Id: afsdb.ddl 1213 2005-02-04 01:49:20Z eagle $
--
-- Written by Jonathan Pilat <jonpilat@stanford.edu>
--        and Russ Allbery <rra@stanford.edu>
-- Copyright 1998, 2001, 2005
--     Board of Trustees, Leland Stanford Jr. University
--
-- Permission is hereby granted, free of charge, to any person obtaining a
-- copy of this software and associated documentation files (the
-- "Software"), to deal in the Software without restriction, including
-- without limitation the rights to use, copy, modify, merge, publish,
-- distribute, sublicense, and/or sell copies of the Software, and to permit
-- persons to whom the Software is furnished to do so, subject to the
-- following conditions:
-- 
-- The above copyright notice and this permission notice shall be included
-- in all copies or substantial portions of the Software.
-- 
-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
-- OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
-- MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
-- IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
-- CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT
-- OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR
-- THE USE OR OTHER DEALINGS IN THE SOFTWARE.

create table volumes
   (volname             varchar2(31),
    volid               number,
    type                varchar2(2),
    used                number,
    status              varchar2(8),
    server              varchar2(8),
    part                varchar2(8),
    rwid                number,
    roid                number,
    bkid                number,
    quota               number,
    created             date,
    updated             date,
    accesses            number);

create table previous_volumes
   (volname             varchar2(31),
    volid               number,
    type                varchar2(2),
    used                number,
    status              varchar2(8),
    server              varchar2(8),
    part                varchar2(8),
    rwid                number,
    roid                number,
    bkid                number,
    quota               number,
    created             date,
    updated             date,
    accesses            number);

create table mountpoints
   (volname             varchar2(31),
    path                varchar2(255),
    volid               number);

create view unreleased_changes
    as select distinct a.volname
                  from volumes a, volumes b
                 where a.type = 'RW'
                   and a.roid = b.volid
                   and b.type = 'RO'
                   and a.updated > b.updated;

create view old_unreleased_changes
    as select distinct a.volname
                  from previous_volumes a, previous_volumes b
                 where a.type = 'RW'
                   and a.roid = b.volid
                   and b.type = 'RO'
                   and a.updated > b.updated;

create view volume_count
    as select volname, count(*) as count
         from volumes
        where type != 'RO'
     group by volname;

create view partition_count
    as select server, part, type, count(*) as count
         from volumes
     group by server, part, type;

create index curr_volumes_index on volumes(volname);
create index prev_volumes_index on previous_volumes(volname);
create index mtpt_volumes_index on mountpoints(volname);