//-----------------------------------------------------------------------------
/* @(#)QP.ins 10.01.00  1998-01-30
//-----------------------------------------------------------------------------
//
//
SAY --- Installing system tables for QueryPlus
//
SQLMODE ADABAS
//
INIT SERVERDB
//
AUTOCOMMIT OFF
//
//
//-----------------------------------------------------------------------------
// Systemtables of the Conversational Component QueryPlus
//-----------------------------------------------------------------------------
//
//-----------------------------------------------------------------------------
// Create table for the object management
//-----------------------------------------------------------------------------
IF $RC (EXISTS TABLE SYSQP_ALL_OBJECTS) <> 0
THEN
    BEGIN
//
        CREATE TABLE SYSQP_ALL_OBJECTS  (
            OWNER       CHAR(18)    KEY                 ,
            OBJTYPE     CHAR(18)    KEY                 ,
            NAME        CHAR(150)   KEY                 ,
            ID          CHAR(8)     BYTE DEFAULT STAMP  ,
            OBJDESC     LONG        BYTE )
//
    END
//
//
//
//
//-----------------------------------------------------------------------------
// Create table for the user INI entries
//-----------------------------------------------------------------------------
IF $RC (EXISTS TABLE SYSQP_INI) <> 0
THEN
    BEGIN
//
	CREATE TABLE SYSQP_INI (
		UserName		CHAR(18) KEY,
		LongInfo		INT,
		WithHeader		INT,
		SelectAll		INT,
		FetchFirstPage		INT,
		FetchNextPage		INT,
		ShowFetchedRows		INT,
		ShowTables		INT,
		ShowViews		INT,
		ShowSynonyms		INT,
		ShowResults		INT,
		ShowSnapShots		INT,
		ShowSYSTables		INT,
		ShowOwnObjects		INT,
		ConfDelSQL		INT,
		ConfDelQueries		INT,
		ConfDelQTables		INT,
		ConfDelQColumns		INT,
		ConfDelQJoins		INT,
		ConfDelWordLinks	INT,
		ConfDelExcelLinks	INT,
		ConfDelLinkedData	INT,
		ConfDelSharings		INT,
		ConfSaving		INT,
		AutoOpen		INT,
		OpenDirect		INT,
		OpenSQL			INT,
		OpenQuery		INT,
		OpenWordLink		INT,
		OpenExcelLink		INT,
		AODirect		INT,
		AOSQL			INT,
		AOQuery			INT,
		AOWordLink		INT,
		AOExcelLink		INT	)
//
    END
//
    IF $RC (ALTER TABLE SYSQP_INI ADD RepresentingNull CHAR(254)) = 0
    THEN
//
        ALTER TABLE SYSQP_INI COLUMN RepresentingNull ADD DEFAULT '?'
//
//-----------------------------------------------------------------------------
// Views of the Conversational Component QueryPlus
//-----------------------------------------------------------------------------
//
//
//-----------------------------------------------------------------------------
// Create the view of the object management for each user
//-----------------------------------------------------------------------------
IF $RC (SHOW VIEW SYSQP_MY_OBJECTS) = 0
THEN
    BEGIN
//
        DROP VIEW SYSQP_MY_OBJECTS
//
    END
//
CREATE VIEW SYSQP_MY_OBJECTS AS
        SELECT  OWNER       ,
                OBJTYPE     ,
                NAME        ,
                ID          ,
                OBJDESC
        FROM    SYSQP_ALL_OBJECTS
        WHERE   OWNER = USERGROUP
WITH CHECK OPTION
//
//
//-----------------------------------------------------------------------------
// Create the view for the shared QPC objects management
//-----------------------------------------------------------------------------
IF $RC (SHOW VIEW SYSQP_SC_OBJECTS) = 0
THEN
    BEGIN
//
        DROP VIEW SYSQP_SC_OBJECTS
//
    END
//
CREATE VIEW SYSQP_SC_OBJECTS (OWNER, GRANTEE, OBJTYPE, NAME, PERMISSION, GRANTEE_GROUP) AS
        SELECT REFOWNER        ,
               DEFUSERNAME     ,
               REFOBJTYPE      ,
               REFCOMMANDNAME  ,
               PRIVILEGES      ,
               DEFGROUPNAME
        FROM   DOMAIN.USR_USES_QPC
//
//
//-----------------------------------------------------------------------------
// Create the view for the shared QPQ objects management
//-----------------------------------------------------------------------------
IF $RC (SHOW VIEW SYSQP_SQ_OBJECTS) = 0
THEN
    BEGIN
//
        DROP VIEW SYSQP_SQ_OBJECTS
//
    END
//
CREATE VIEW SYSQP_SQ_OBJECTS (OWNER, GRANTEE, OBJTYPE, NAME, PERMISSION, GRANTEE_GROUP) AS
        SELECT REFOWNER        ,
               DEFUSERNAME     ,
               REFOBJTYPE      ,
               REFQUERYNAME    ,
               PRIVILEGES      ,
               DEFGROUPNAME
        FROM   DOMAIN.USR_USES_QPQ
//
//
//-----------------------------------------------------------------------------
// Create the view for the shared QPW objects management
//-----------------------------------------------------------------------------
IF $RC (SHOW VIEW SYSQP_SW_OBJECTS) = 0
THEN
    BEGIN
//
        DROP VIEW SYSQP_SW_OBJECTS
//
    END
//
CREATE VIEW SYSQP_SW_OBJECTS (OWNER, GRANTEE, OBJTYPE, NAME, PERMISSION, GRANTEE_GROUP) AS
        SELECT REFOWNER        ,
               DEFUSERNAME     ,
               REFOBJTYPE      ,
               REFWORDLINKNAME ,
               PRIVILEGES      ,
               DEFGROUPNAME
        FROM   DOMAIN.USR_USES_QPW
//
//
//-----------------------------------------------------------------------------
// Create the view for the shared QPE objects management
//-----------------------------------------------------------------------------
IF $RC (SHOW VIEW SYSQP_SE_OBJECTS) = 0
THEN
    BEGIN
//
        DROP VIEW SYSQP_SE_OBJECTS
//
    END
//
CREATE VIEW SYSQP_SE_OBJECTS (OWNER, GRANTEE, OBJTYPE, NAME, PERMISSION, GRANTEE_GROUP) AS
        SELECT REFOWNER        ,
               DEFUSERNAME     ,
               REFOBJTYPE      ,
               REFEXCELLINKNAME,
               PRIVILEGES      ,
               DEFGROUPNAME
        FROM   DOMAIN.USR_USES_QPE
//
//
//-----------------------------------------------------------------------------
// Grants of the Conversational Component QueryPlus
//-----------------------------------------------------------------------------
//
//
//-----------------------------------------------------------------------------
// Grant for the system table SYSQP_ALL_OBJECTS
//-----------------------------------------------------------------------------
GRANT   SELECT, UPDATE, DELETE, INSERT
ON      SYSQP_ALL_OBJECTS
TO      DOMAIN
//
GRANT   SELECT
ON      SYSQP_ALL_OBJECTS
TO      PUBLIC
//-----------------------------------------------------------------------------
// Grant for the system table SYSQP_INI
//-----------------------------------------------------------------------------
GRANT   SELECT, UPDATE, DELETE, INSERT
ON      SYSQP_INI
TO      PUBLIC
//-----------------------------------------------------------------------------
// Grant for the view SYSQP_MY_OBJECTS
//-----------------------------------------------------------------------------
GRANT   SELECT, UPDATE, DELETE, INSERT
ON      SYSQP_MY_OBJECTS
TO      PUBLIC
//-----------------------------------------------------------------------------
// Grant for the view SYSQP_SC_OBJECTS
//-----------------------------------------------------------------------------
GRANT   SELECT
ON      SYSQP_SC_OBJECTS
TO      PUBLIC
//-----------------------------------------------------------------------------
// Grant for the view SYSQP_SQ_OBJECTS
//-----------------------------------------------------------------------------
GRANT   SELECT
ON      SYSQP_SQ_OBJECTS
TO      PUBLIC
//-----------------------------------------------------------------------------
// Grant for the view SYSQP_SW_OBJECTS
//-----------------------------------------------------------------------------
GRANT   SELECT
ON      SYSQP_SW_OBJECTS
TO      PUBLIC
//-----------------------------------------------------------------------------
// Grant for the view SYSQP_SE_OBJECTS
//-----------------------------------------------------------------------------
GRANT   SELECT
ON      SYSQP_SE_OBJECTS
TO      PUBLIC
//
//
//-----------------------------------------------------------------------------
// Commit the data definitions
//-----------------------------------------------------------------------------
//
//
COMMIT WORK
//
//
END INIT SERVERDB
//
SQLMODE ADABAS
//
//-----------------------------------------------------------------------------
// END
//-----------------------------------------------------------------------------
