set termout off
/* Copyright (c) 1991, 1994 by Oracle Corporation.  All Rights Reserved */
/*
$Id: brwins60.sql,v 1.2 1998/04/28 18:22:42 isimek Exp $
*/
/***
  CREATE Oracle Query Builder Saved Query Tables

Note:  This script uses Oracle7 syntax and should not be run on an Oracle
       V6 database.  If you want to install these tables on an Oracle V6
       database, use the script Oracle Data Browser 1.0 script install.sql
***/
REM   MODIFIED
Rem     dyee       09/11/97 -  Update version to 6.0.
Rem     dtlai      10/16/96 -  Update changes for 2.5. 
Rem     rshoup     08/24/95 -  Bug #281086:  Fix browser_all_docs to check
Rem                            user's roles. 
Rem     dtlai      04/03/95 -  . 
Rem     dtlai      03/31/95 -  . 
Rem     dtlai      02/28/95 - Removed drop table/view/index/sequence 
Rem                           statements.  Reorder drop synonym statements.
Rem     rshoup     10/11/94 -  Remove exit. 
Rem     gpancha    06/23/94 - Fixed bug for browser_grants_ comment. 
REM     rshoup     04/21/94 - Removed typos. 
REM     gpancha    03/22/94 - Added names for primary key constraints,
REM			      incorporated comments
REM     gpancha    01/06/94 - Fixed to work on Oracle7 (disabled foreign key
REM			      for browser_grants_, removed index creation for
REM			      browser_docs_ and browser_grants_). 
REM     wfenton    03/03/93 - Added exit command at the bottom.
REM     rshoup     12/02/91 - Added synonyms; tables need no longer be owned
REM			      by SYSTEM. Still must be run from an account with
REM			      DBA priv, though.
REM     rshoup     04/03/91 - Created.


REM  
REM    First, test for existence of browser saved query tables.
REM    Issue a message saying that warnings about creating tables on
REM    top of existing ones can be ignored.
REM


select 'start brwstw60.sql' from dual 
where exists (select null from user_tables where table_name
		= 'BROWSER_DOCS_')
or exists (select null from user_tables where table_name
		= 'BROWSER_GRANTS_')

set echo off
spool brwtmp3.sql
/
spool off
set echo on

start brwtmp3.sql

REM
REM    Continue with install:
REM    If we get here that means the user would like to continue
REM    with the install.
REM  


set echo off
set termout on
PROMPT
PROMPT Installing the Saved Query tables...
PROMPT

set termout off
spool brwins60.log

create table browser_docs_
	(query_id       number          not null        /* ID of query */
					constraint browser_docs_idx
					primary key, 
	owner           varchar(30)     not null,       /* Owner of query */
	query_name      varchar(30)     not null,       /* Name of query */
	comments        varchar(255)    null,           /* Comments */
	create_date     date            not null,       /* Date created */
	modified_date   date            null,           /* Date modified */
	modified_by     varchar(30)     null,           /* Last modifier */
	valid           char(1)         null,           /* Bool: Valid */
	doc_length      number          not null,       /* Document length */
	document        long raw        not null,       /* Document */
	version         varchar(5)      not null        /* Version */);

comment on table browser_docs_ is
  'Contains information for all Oracle Query Builder Saved queries';
comment on column browser_docs_.query_id is
  'ID of query';
comment on column browser_docs_.owner is
  'Owner of query';
comment on column browser_docs_.query_name is
  'Name of Query';
comment on column browser_docs_.comments is
  'Comment on query, can be NULL';
comment on column browser_docs_.create_date is
  'Date Created';
comment on column browser_docs_.modified_date is
  'Date Modified, can be NULL';
comment on column browser_docs_.modified_by is
  'Last modifier, can be NULL';
comment on column browser_docs_.valid is
  'Validity state of query (Y/N)';
comment on column browser_docs_.doc_length is
  'Size of query';
comment on column browser_docs_.document is
  'Query information in binary format';
comment on column browser_docs_.version is
  'Version of file format';

set echo off
PROMPT
PROMPT   QUERY BUILDER GRANTS
PROMPT
set echo on

create table browser_grants_
	(query_id       number          not null        /* ID of query */
					references browser_docs_(query_id)
					disable,
	owner           varchar(30)     not null,       /* Owner of query */
	grantee         varchar(30)     not null,       /* Grant recipient */
					constraint browser_grants_idx
					primary key (query_id, grantee) );

comment on table browser_grants_ is
  'Contains security information for all Oracle Query Builder queries';
comment on column browser_grants_.query_id is
  'Disabled foreign key referencing browser_docs_.query_id';
comment on column browser_grants_.grantee is
  'Grant recipient of query';
comment on column browser_grants_.owner is
  'Owner of query';

create view browser_grants as
  select * 
  from browser_grants_
  where owner = user
  with check option;

comment on table browser_grants is
  'Contains security information for Oracle Query Builder queries owned by user';

create view browser_docs as
  select * 
  from browser_docs_
  where owner = user
  with check option;

comment on table browser_docs is
  'Contains information for Oracle Query Builder saved queries owned by user';

create view browser_all_docs as
  select * 
  from browser_docs_ a
  where ((owner = user)
      or exists(select b.query_id
		from browser_grants_ b
		where a.query_id = b.query_id
		and (upper(grantee) = 'PUBLIC' 
		     or user like upper(grantee)
		     or upper(grantee) in (select role
		                           from session_roles))))
  with check option;

comment on table browser_all_docs is
  'Contains information for Oracle Query Builder saved queries owned by or granted to user';

create sequence browser_seq;

set echo off
PROMPT
PROMPT Grant access on new tables 
PROMPT
set echo on

grant select, insert, delete on browser_grants to public;
grant select, insert, update, delete on browser_docs to public;
grant select on browser_all_docs to public;

grant select on browser_seq to public;


set echo off
PROMPT
PROMPT Before creating public synonyms, we want to drop old ones.
PROMPT There might be warnings about non-existent synonyms, and they 
PROMPT can be ignored.
PROMPT

set echo on

drop public synonym browser_seq;

drop public synonym browser_grants;
drop public synonym browser_docs;
drop public synonym browser_all_docs;


set echo off
PROMPT
PROMPT Create public synonyms
PROMPT
set echo on

create public synonym browser_grants for browser_grants;
create public synonym browser_docs for browser_docs;
create public synonym browser_all_docs for browser_all_docs;

create public synonym browser_seq for browser_seq;

commit;

@brwupg60.sql

set echo off

set termout on
PROMPT
PROMPT  Done.
PROMPT
PROMPT  The log file for reference is brwins60.log.
PROMPT
set termout off

spool off
REM  spool off for brwins60.log


