/*
|| Script:  ADMINSetup.sql
|| Version: 11.1.0.6.0
|| Object:  Creates database objects that illustrate best practices
||          (and not-so-best practices!) for controlling generation
||          of primary key values.
|| Author:  Jim Czuprynski (Fujitsu Consulting)
*/

-----
-- Create new ADMIN user account
-- Grant appropriate system privileges
-----
DROP USER admin CASCADE;
CREATE USER admin
    IDENTIFIED BY secret
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    PROFILE DEFAULT
    QUOTA UNLIMITED ON example 
;

GRANT CONNECT, RESOURCE TO admin;
GRANT CREATE PROCEDURE TO admin;
GRANT CREATE PUBLIC SYNONYM TO admin;
GRANT CREATE SEQUENCE TO admin;
GRANT CREATE SESSION TO admin;
GRANT CREATE SYNONYM TO admin;
GRANT CREATE TABLE TO admin;
GRANT DROP PUBLIC SYNONYM TO admin;
GRANT EXECUTE ANY PROCEDURE TO admin;

-----
-- Create table to hold Next IDs (simulating sequence processing,
-- but using a table to store the current value)
-----
DROP TABLE admin.next_ids PURGE;
CREATE TABLE admin.next_ids (
     owner      VARCHAR2(30)
    ,table_name VARCHAR2(30)
    ,key_id     VARCHAR2(30)
    ,value      NUMBER
);

ALTER TABLE admin.next_ids 
  ADD CONSTRAINT next_ids_pk 
  PRIMARY KEY (owner, table_name, key_id)
;

-----
-- Populate the Next IDs table with initial key values
-----
INSERT INTO admin.next_ids VALUES('AP', 'VENDORS', 'VENDOR_ID', 0);
INSERT INTO admin.next_ids VALUES('AP', 'INVOICES', 'INVOICE_ID', 0);
COMMIT;

-----
-- Create package to control generation of next IDs 
---- 
@pkg_sequencing.spc;
@pkg_sequencing.bdy;

-----
-- Grant privileges for all these objects to PUBLIC role
-----
GRANT SELECT, INSERT, UPDATE ON admin.next_ids TO PUBLIC;
GRANT EXECUTE ON admin.pkg_sequencing TO PUBLIC;