--/****************************************************************\
--** filename     *  sample.sql

--** description * Table, index, role and user creation samples.

--** Copyright (C) Solid Information Technology Ltd 1997
--\****************************************************************/

-- First create table TEST_TABLE
CREATE TABLE TEST_TABLE (
	ID INTEGER NOT NULL, 
	TEXT VARCHAR, 
	TODAY DATE, 
	VALUE FLOAT,
	PRIMARY KEY(ID)
);
-- Statements are ended with a semicolon.

-- Create another table named PROD_TABLE
CREATE TABLE PROD_TABLE (
	ID INTEGER NOT NULL UNIQUE, 
	TEXT VARCHAR, 
	TODAY DATE, 
	VALUE FLOAT, 
	PRIMARY KEY (ID)
);

-- Create few users
CREATE USER CALVIN   IDENTIFIED BY HOBBES;
CREATE USER DIANE     IDENTIFIED BY PASSWO1;
CREATE USER JILL          IDENTIFIED BY PASSWO2;
CREATE USER MARK      IDENTIFIED BY PASSWO3;
CREATE USER JAKE        IDENTIFIED BY PASSWO4;

-- Change Jakes password to ELWOOD
ALTER USER JAKE IDENTIFIED BY ELWOOD;

-- Delete user Calvin
DROP USER CALVIN;

-- Create TESTERS and PRODUCTION roles
CREATE ROLE TESTERS;
CREATE ROLE PRODUCTION;

-- Grant rights for these roles
GRANT INSERT, SELECT ON TEST_TABLE TO TESTERS;
GRANT INSERT, SELECT, DELETE ON PROD_TABLE TO PRODUCTION;

-- Grant roles to users. 
-- Diane and Mark are testers while the others are production users.
GRANT TESTERS TO DIANE;
GRANT TESTERS TO MARK;
GRANT PRODUCTION TO JILL;
GRANT PRODUCTION TO JAKE;

-- Grant Diane right to delete from test_table
GRANT DELETE ON TEST_TABLE TO JILL;

-- Grant Mark Production role
GRANT PRODUCTION TO MARK;

-- Revoke the delete right from Jill on PROD_TABLE
REVOKE DELETE ON PROD_TABLE FROM JILL;

-- Revoke the PRODUCTION role from JAKE
REVOKE PRODUCTION FROM JAKE;

-- Revoke the delete right on table TEST_TABLE from TESTERS role.
REVOKE DELETE ON TEST_TABLE FROM TESTERS;

-- Grant Jill the system administrator role. Jill will have all 
-- rights to all tables (except the system tables) from now on.
GRANT SYS_ADMIN_ROLE TO JILL;

-- Drop the GUEST_USERS role. When this role is dropped all users
-- granted this role have their rights removed.
DROP ROLE TESTERS;

-- Make the changes to tables, users and roles 
-- permanent by committing the work we have done.
-- If the autocommit set to ON, this has no effect.
COMMIT WORK;

-- Now we modify the tables and create few indexes.

-- Add another column to table TEST_TABLE
ALTER TABLE TEST_TABLE ADD COLUMN C CHAR(1);

COMMIT WORK;

-- Delete the added column
ALTER TABLE TEST_TABLE DROP COLUMN C;

-- Create a non-unique index on column ID to TEST_TABLE
CREATE INDEX X_TEST ON TEST_TABLE (ID);

-- Create a unique index on column VALUE to TEST_TABLE
CREATE UNIQUE INDEX UX_TEST ON TEST_TABLE (VALUE);

-- Drop the non-unique index
DROP INDEX X_TEST;

-- And again commit the work we have done.
COMMIT WORK;

