--
--   Installation script for "Schema Version Control for Oracle" (SVCO)
--   http://www.sumsoftsolutions.com
--
--   File Name: install.sql
--   SVCO Version: 3.0.0
--   DB: Oracle 11g and later
--   OS: non-specific
--   Comments: For details please see readme.txt
--

SET SERVEROUTPUT ON SIZE UNLIMITED
SET PAGESIZE 0
SET VERIFY OFF
WHENEVER OSERROR EXIT
WHENEVER SQLERROR EXIT

PROMPT .............................................................
PROMPT ... Installing "Schema Version Control for Oracle" (SVCO) ...
PROMPT ...         http://www.sumsoftsolutions.com               ...
PROMPT ... PLEASE READ LICENSE AGREEMENT CAREFULLY               ...
PROMPT ...                 (file license.txt) BEFORE INSTALLING  ...
PROMPT .............................................................
PROMPT
PROMPT ... Checking installation requirements ......................

declare
    v_ver   varchar2(64);
    v_comp  varchar2(64);
    v_count number;
begin	
	if  sys_context('USERENV', 'ISDBA') <> 'TRUE' then
		RAISE_APPLICATION_ERROR(-20700, '!!! Please login AS SYSDBA !!!');
	end if;	
	if upper(sys_context('USERENV', 'SESSION_USER')) <> 'SYS' then
		RAISE_APPLICATION_ERROR(-20700, '!!! Please login as SYS user !!!');
	end if;
	sys.dbms_utility.db_version(version => v_ver, compatibility => v_comp);
	if to_number(replace(v_ver, '.', null)) < 111060 then
		RAISE_APPLICATION_ERROR(-20700, '!!! Oracle version should be 11.1.0.6.0 or greater !!!');
  end if;
  select count(1)
  into v_count
  from all_users
  where username = 'SVCO';
  if v_count > 0 then
		RAISE_APPLICATION_ERROR(-20700, '!!! SVCO user allready exists, run upgrade.sql !!!');
  end if;
end;
/
PROMPT ... Done ....................................................
PROMPT
ACCEPT tabtbs DEFAULT 'USERS' PROMPT 'Please enter TABLESPACE name for SVCO TABLES (Default is USERS) : '
ACCEPT idxtbs DEFAULT 'INDEXES' PROMPT 'Please enter TABLESPACE name for SVCO INDEXES (Default is INDEXES) : '
ACCEPT pwd DEFAULT 'SVCO' PROMPT 'Please enter PASSWORD for SVCO USER (Default is SVCO) : ' HIDE
ACCEPT tmptbs DEFAULT 'TEMP' PROMPT 'Please enter TEMPORARY TABLESPACE name for SVCO USER (Default is TEMP) : '
PROMPT
PROMPT ... Creating required Roles .................................
CREATE ROLE SVCO_REP_ADMIN NOT IDENTIFIED
/
CREATE ROLE SVCO_REP_OPERATOR NOT IDENTIFIED
/
GRANT SVCO_REP_OPERATOR TO SVCO_REP_ADMIN
/
GRANT SELECT_CATALOG_ROLE TO SVCO_REP_ADMIN
/
PROMPT ... Done ....................................................
PROMPT
PROMPT ... Installing SYS objects .................................
@data/dbms_metadata_dvco.spc
@data/dbms_metadata_dvco.bdy
PROMPT ... Done ....................................................
PROMPT
PROMPT ... Creating SVCO USER ......................................
CREATE USER SVCO IDENTIFIED BY "&pwd"
    DEFAULT TABLESPACE &tabtbs
    TEMPORARY TABLESPACE &tmptbs
    PROFILE DEFAULT
    ACCOUNT UNLOCK
/
GRANT CONNECT TO SVCO
/
GRANT SVCO_REP_ADMIN TO SVCO WITH ADMIN OPTION
/
GRANT SVCO_REP_OPERATOR TO SVCO WITH ADMIN OPTION
/
GRANT AQ_ADMINISTRATOR_ROLE TO SVCO
/
ALTER USER SVCO DEFAULT ROLE CONNECT,
                             SVCO_REP_ADMIN,
                             SVCO_REP_OPERATOR,
                             AQ_ADMINISTRATOR_ROLE
/
GRANT ADMINISTER DATABASE TRIGGER TO SVCO
/
GRANT UNLIMITED TABLESPACE TO SVCO
/
GRANT EXECUTE ON SYS.DBMS_AQ TO SVCO
/
GRANT EXECUTE ON SYS.DBMS_AQADM TO SVCO
/
GRANT EXECUTE ON SYS.DBMS_AQ_BQVIEW TO SVCO
/
GRANT SELECT ON SYS.DBA_RECYCLEBIN TO SVCO
/
GRANT EXECUTE ON SYS.DBMS_LOCK TO SVCO
/
GRANT EXECUTE ON SYS.DBMS_METADATA_DVCO TO SVCO
/
GRANT SELECT ON SYS.DBA_OBJECTS TO SVCO
/
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO SVCO
/
-- new for Oracle 12c
GRANT SELECT ON SYS.V_$PARAMETER TO SVCO
/
PROMPT ... Done ....................................................
PROMPT
PROMPT ... Installing SVCO objects .................................
@data/sequences
@data/sequences_upgrade
@data/types
@data/tables
@data/tables_upgrade
@data/exceptions_.spc
@data/vcontrol_util.spc
@data/vcontrol_util.bdy
@data/vcontrol_int.spc
@data/vcontrol_int.bdy
@data/vcontrol.spc
@data/vcontrol.bdy
@data/vcontrol_admin.spc
@data/vcontrol_admin.bdy
@data/views
@data/views_upgrade
@data/triggers
@data/grants
@data/grants_upgrade
PROMPT ... Done ....................................................
PROMPT
PROMPT ... Installing SVCO data ....................................
@data/data
PROMPT ... Done ....................................................
PROMPT
PROMPT ... Starting SVCO ...........................................
exec SVCO.VCONTROL_ADMIN.START_VERSION_CONTROL;
PROMPT ... Done ....................................................
PROMPT
PROMPT ... All done ...
PROMPT
exit
