REMARK
\
This file contains all of the necessary SQL commands to  initialize  a	new
template database like START.DBS.  SQLBase uses the  file  START.DBS  as  a
"template" for all new databases.  Each new database should originally be a
copy of START.DBS.  The file START.DBS contains all necessary catalog table
system information that SQLBase subsequently maintains.

SQLBase supports English as the standard language.  SQLBase  also  supports
many national languages including those  used  in  Europe  and	Asia.	You
specify the information that enables SQLBase to support another language in
the file called COUNTRY.SQL.

If you change the sort order of the standard ASCII sequence,  this  effects
the sorting of internal information within SQLBase.  Once you  establish  a
sort sequence for a SQLBase database, you cannot change the sort  sequence.
SQLBase depends on the original sort sequence for the proper  index  usage.
The database template file, START.DBS, is prepared using the standard ASCII
sort sequence.	If you wish to change the standard ASCII sort sequence	you
will need to prepare a new version of the template file, START.DBS.
/
PAUSE
/
REMARK
\
A new START.DBS  file  can  be	prepared  by  copying  COUNTRY.DBS  to	the
appriopriate database directory, connecting to it, and running this script.
COUNTRY.DBS is the predecessor to START.DBS.   COUNTRY.DBS  is	a  database
having only one data page and no system information.

After the first connect to the database, you will  need  to  run  this	SQL
script called COUNTRY.TLK.  The COUNTRY.TLK script creates the user  SYSSQL
and the generic views of the SQLBase system  catalog  tables.	The  script
also grants the PUBLIC access to these system catalog views.  Finally,	the
COUNTRY.TLK  script  updates  the  database  statistics  and  verifies	the
integrity of the database with the CHECK DATABASE command.
/
PAUSE
/
REMARK
\
Therefore, if you are changing the ASCII sort sequence, you should follow
the following steps:

(1) Copy COUNTRY.DBS to the appropriate database name and directory as you
    would normally use START.DBS, for example:

      COPY \SQLBASE\COUNTRY.DBS \SQLBASE\DEMO\DEMO.DBS

(2) Connect to the new one page database and run this script, COUNTRY.TLK,
    to initialize the SQLBase system catalog table information, for example:

      SQLTALK BAT DB=DEMO INPUT=\SQLBASE\COUNTRY.TLK OUTPUT=COUNTRY.OUT

(3) Make a copy of your new template database file for your ASCII sort
    sequence for usage with other databases, for example:

      COPY \SQLBASE\DEMO\DEMO.DBS \SQLBASE\NEWSTART.DBS
/
PAUSE
/
REMARK
\
1. Create user 'SYSSQL'.
/
GRANT CONNECT  TO SYSSQL IDENTIFIED BY SYSSQL
/
REMARK
\
2. Create generic views on system catalog tables.
   Grant PUBLIC select access to these views.
/
CREATE VIEW SYSSQL.SYSTABLES AS
  SELECT CREATOR, NAME, COLCOUNT, TYPE, REMARKS, PERCENTFREE, "LABEL",
         SYSTEM, ROWCOUNT, PAGECOUNT, ROWPAGECOUNT,
	 LONGPAGECOUNT, EXTENTPAGECOUNT, FREESLOTS, USEDSPACE, FREESPACE,
	 AVGROWLEN, AVGROWLONGLEN, GROUPNUM, TABLESCAN
    FROM  SYSADM.SYSTABLES
    WHERE USER = CREATOR
       OR USER = 'SYSADM'
       OR EXISTS (SELECT GRANTEE
		    FROM SYSADM.SYSTABAUTH
		    WHERE (GRANTEE = USER OR GRANTEE = 'PUBLIC')
		      AND TCREATOR = CREATOR
		      AND TTNAME = NAME
		      AND ((ALTERAUTH = 'Y') OR
			   (DELETEAUTH = 'Y') OR
			   (INDEXAUTH  = 'Y') OR
			   (INSERTAUTH = 'Y') OR
			   (SELECTAUTH = 'Y') OR
			   (UPDATEAUTH = 'Y')))
       OR EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
		    FROM SYSADM.SYSUSERAUTH
		    WHERE NAME = USER AND DBAAUTH = 'Y')
/
GRANT SELECT ON SYSSQL.SYSTABLES TO PUBLIC
/
CREATE VIEW SYSSQL.SYSCOLUMNS AS
  SELECT TBCREATOR, COL.NAME, TBNAME, COLNO, COLTYPE, LENGTH, NULLS,
	 UPDATES, COL.REMARKS, SCALE, COL."LABEL", AVGCOLLEN, AVGCOLLONGLEN
    FROM SYSADM.SYSCOLUMNS COL, SYSSQL.SYSTABLES TB
    WHERE (TBCREATOR = TB.CREATOR) AND (TBNAME = TB.NAME)
/
GRANT SELECT ON SYSSQL.SYSCOLUMNS TO PUBLIC
/
CREATE VIEW SYSSQL.SYSINDEXES  AS
  SELECT TBCREATOR, IX.NAME, TBNAME, IX.CREATOR, UNIQUERULE, IX.COLCOUNT,
	 IXTYPE, CLUSTERRULE, IXSIZE, IX.SYSTEM, IX.PERCENTFREE,
	 HEIGHT, INDEXPAGECOUNT, LEAFCOUNT, CLUSTERCOUNT, PRIMPAGECOUNT,
	 OVFLPAGECOUNT, AVGKEYLEN, IX.GROUPNUM, USECOUNT, COLLATION
    FROM SYSADM.SYSINDEXES IX, SYSSQL.SYSTABLES TB
    WHERE (TBCREATOR = TB.CREATOR) AND (TBNAME = TB.NAME)
/
GRANT SELECT ON SYSSQL.SYSINDEXES TO PUBLIC
/
CREATE VIEW SYSSQL.SYSKEYS AS
  SELECT IXCREATOR, IXNAME, COLNAME, COLNO, COLSEQ, ORDERING, FUNCTION,
         DISTINCTCOUNT
    FROM SYSADM.SYSKEYS, SYSSQL.SYSINDEXES IX
    WHERE (IXCREATOR = IX.CREATOR) AND (IXNAME = IX.NAME)
/
GRANT SELECT ON SYSSQL.SYSKEYS TO PUBLIC
/
CREATE VIEW SYSSQL.SYSSYNONYMS AS
  SELECT SYN.NAME, SYN.CREATOR, TBNAME, TBCREATOR
    FROM SYSADM.SYSSYNONYMS SYN, SYSSQL.SYSTABLES TB
    WHERE (TBCREATOR = TB.CREATOR) AND (TBNAME = TB.NAME)
/
GRANT SELECT ON SYSSQL.SYSSYNONYMS TO PUBLIC
/
CREATE VIEW SYSSQL.SYSTABAUTH AS
   SELECT GRANTEE, TCREATOR, TTNAME, UPDATECOLS, ALTERAUTH, DELETEAUTH,
	  INDEXAUTH, INSERTAUTH, SELECTAUTH, UPDATEAUTH
     FROM SYSADM.SYSTABAUTH
     WHERE (USER = 'SYSADM')
	OR (GRANTEE = USER)
	OR (GRANTEE = 'PUBLIC')
	OR EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
		     FROM SYSADM.SYSUSERAUTH
		     WHERE NAME = USER AND DBAAUTH = 'Y')
/
GRANT SELECT ON SYSSQL.SYSTABAUTH TO PUBLIC
/
CREATE VIEW SYSSQL.SYSCOLAUTH AS
   SELECT GRANTEE, CREATOR, TNAME, COLNAME
     FROM  SYSADM.SYSCOLAUTH
     WHERE (USER = 'SYSADM')
	OR (GRANTEE = USER)
	OR (GRANTEE = 'PUBLIC')
	OR EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
		     FROM SYSADM.SYSUSERAUTH
		     WHERE NAME = USER AND DBAAUTH = 'Y')
/
GRANT SELECT ON SYSSQL.SYSCOLAUTH TO PUBLIC
/
create view SYSSQL.SYSPARTTRANS (id, state, protocol, lastmodtime, db,
                                 username, "PASSWORD") as
            select id1, state, protocol, lastmodtime,
                   db, username, "PASSWORD"
            from   SYSADM.SYSPARTTRANS
/
grant select on SYSSQL.SYSPARTTRANS to public
/
CREATE VIEW SYSSQL.SYSCOMMANDS AS
	SELECT CREATOR, NAME, TYPE, SYSTEM, STATIC, VALID, AUTORECOMPILE, TEXT
	FROM   SYSADM.SYSCOMMANDS
	WHERE  (USER = CREATOR
	       OR
	       USER = 'SYSADM'
	       OR
	       EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
		       FROM SYSADM.SYSUSERAUTH
		       WHERE NAME = USER AND DBAAUTH = 'Y'
		      )
               )
/
GRANT SELECT ON SYSSQL.SYSCOMMANDS TO PUBLIC
/
CREATE VIEW SYSSQL.SYSEXECUTEAUTH AS
	SELECT CREATOR, NAME, GRANTEE, USECREATORPRIV, USEGRANTEEPRIV
	FROM   SYSADM.SYSEXECUTEAUTH
	WHERE  USER = CREATOR
	       OR
	       USER = 'SYSADM'
	       OR
	       EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
		       FROM SYSADM.SYSUSERAUTH
		       WHERE NAME = USER AND DBAAUTH = 'Y'
		      )
/
GRANT SELECT ON SYSSQL.SYSEXECUTEAUTH TO PUBLIC
/
REMARK
\
This SYSEVENTS view hides the column ACTIONCODE from the public.
/
CREATE VIEW SYSSQL.SYSEVENTS
	(CREATOR, NAME, TYPE, SPCREATOR, SPNAME,
	TYPEID, EVENTID, BEGINTIME, INTERVAL, TEXT)
	AS
        SELECT  A.CREATOR, A.NAME, A.TYPE, B.CREATOR, B.NAME, A.TYPEID, 
		A.EVENTID, A.BEGINTIME,	A.INTERVAL, A.TEXT
        FROM	SYSADM.SYSEVENTS A,
		SYSADM.SYSCOMMANDS B
        WHERE  	(USER = A.CREATOR
		OR
		USER = 'SYSADM'
		OR
		EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
                	       FROM 	SYSADM.SYSUSERAUTH
			       WHERE 	NAME = USER AND DBAAUTH = 'Y'))
		AND
		A.SPSNUM = B.SNUM
	
/
GRANT SELECT ON SYSSQL.SYSEVENTS TO PUBLIC
/
REMARK
\
This SYSTRIGGERS view hides the stored procedure serial number
/
CREATE VIEW SYSSQL.SYSTRIGGERS 
	(CREATOR, NAME, TBCREATOR, TBNAME, ACTIONTIME, TRIGGEREVENT, 
	FREQUENCY, SPCREATOR, SPNAME, NUMCOLUMNS, OLDVALUENAME, 
	NEWVALUENAME, ENABLED, ORDERVALUE, TEXT)
	AS
	SELECT 	A.CREATOR, A.NAME, B.CREATOR, B.NAME, A.ACTIONTIME, 
       		A.TRIGGEREVENT,	A.FREQUENCY, C.CREATOR, C.NAME,
		A.NUMCOLUMNS, A.OLDVALUENAME, A.NEWVALUENAME, A.ENABLED, 
		A.ORDERVALUE, A.TEXT
	FROM	SYSADM.SYSTRIGGERS A, 
		SYSADM.SYSTABLES B,
		SYSADM.SYSCOMMANDS C
	WHERE	(USER = A.CREATOR
		OR
		USER = 'SYSADM'
		OR
		EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
				FROM	SYSADM.SYSUSERAUTH
				WHERE	NAME = USER AND DBAAUTH = 'Y'))
		AND
		A.TBSNUM=B.SNUM
		AND
		A.SPSNUM=C.SNUM
/
GRANT SELECT ON SYSSQL.SYSTRIGGERS TO PUBLIC
/
CREATE VIEW SYSSQL.SYSTRGCOLS	
	(CREATOR, NAME, TBCREATOR, TBNAME, COLUMNNAME, ACTIONTIME)
	AS
	SELECT	A.CREATOR, A.NAME, B.CREATOR, B.NAME, 
		C.NAME, A.ACTIONTIME
	FROM	SYSADM.SYSTRGCOLS A,
		SYSADM.SYSTABLES B,
		SYSADM.SYSCOLUMNS C
	WHERE	(USER = A.CREATOR
		OR
		USER = 'SYSADM'
		OR
		EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
				FROM	SYSADM.SYSUSERAUTH
				WHERE	NAME = USER AND DBAAUTH = 'Y'))
		AND
		A.TBSNUM=B.SNUM
		AND
		B.CREATOR=C.TBCREATOR
		AND
		B.NAME=C.TBNAME
		AND
		A.COLNO=C.COLNO
/
GRANT SELECT ON SYSSQL.SYSTRGCOLS TO PUBLIC
/
CREATE VIEW SYSSQL.SYSEXTFUN AS
	SELECT CREATOR, NAME, EXTNAME, LIBNAME, EXPORD, NUMPARAMS, RETURN,
		   RETEXTTYP, EXECMODE, CALLSTYLE, REMARKS, "LABEL" 
	FROM   SYSADM.SYSEXTFUN
	WHERE  (USER = CREATOR
	       OR
	       USER = 'SYSADM'
	       OR
	       EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
		       FROM SYSADM.SYSUSERAUTH
		       WHERE NAME = USER AND DBAAUTH = 'Y'
		      )
               )
/
GRANT SELECT ON SYSSQL.SYSEXTFUN TO PUBLIC
/
CREATE VIEW SYSSQL.SYSEXTPARAM
	(CREATOR, NAME, POSITION, EXTTYP)
	AS
	SELECT	A.CREATOR, A.NAME, B.POSITION, B.EXTTYP 
	FROM	SYSADM.SYSEXTFUN A,
		SYSADM.SYSEXTPARAM B
	WHERE	(USER = A.CREATOR
		OR
		USER = 'SYSADM'
		OR
		EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
				FROM	SYSADM.SYSUSERAUTH
				WHERE	NAME = USER AND DBAAUTH = 'Y'))
		AND
		A.SNUM=B.FUNSNUM
/
GRANT SELECT ON SYSSQL.SYSEXTPARAM TO PUBLIC
/
CREATE VIEW SYSSQL.SYSDEPENDENCIES AS
	SELECT DETCREATOR, DETNAME, DETTYPE, DEPCREATOR, DEPNAME, DEPTYPE
	FROM   SYSADM.SYSDEPENDENCIES
	WHERE  (USER = DETCREATOR
	       OR
	       USER = DEPCREATOR
	       OR
	       USER = 'SYSADM'
	       OR
	       EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
		       FROM SYSADM.SYSUSERAUTH
		       WHERE NAME = USER AND DBAAUTH = 'Y'
		      ))
/
GRANT SELECT ON SYSSQL.SYSDEPENDENCIES TO PUBLIC
/
CREATE VIEW SYSSQL.SYSOBJSYN 
  AS
  SELECT NAME, CREATOR, OBJNAME, OBJCREATOR, OBJTYPE
    FROM SYSADM.SYSOBJSYN 
	WHERE  (USER = CREATOR
	       OR
	       USER = 'SYSADM'
	       OR
	       EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
		       FROM SYSADM.SYSUSERAUTH
		       WHERE NAME = USER AND DBAAUTH = 'Y'
		      )
               )
/
GRANT SELECT ON SYSSQL.SYSOBJSYN TO PUBLIC
/
CREATE VIEW SYSSQL.SYSOBJAUTH AS
	SELECT CREATOR, NAME, GRANTEE, OBJAUTH, OBJTYPE
	FROM   SYSADM.SYSOBJAUTH
	WHERE  (USER = CREATOR
	       OR
	       USER = 'SYSADM'
	       OR
	       EXISTS (SELECT SYSADM.SYSUSERAUTH.NAME
		       FROM SYSADM.SYSUSERAUTH
		       WHERE NAME = USER AND DBAAUTH = 'Y'
		      ))
/
GRANT SELECT ON SYSSQL.SYSOBJAUTH TO PUBLIC
/
REMARK
\
3. Update and commit the SQLBase system statistics.
/
UPDATE STATISTICS ON DATABASE
/
COMMIT
/
REMARK
\
4. Restore all SQLBase database control information to the defaults values.
/
SET NEWDB 1
/
REMARK
\
5. Verify the integrity of the newly created template database.
/
CHECK DATABASE
/
