558 lines
16 KiB
Plaintext
Executable File
558 lines
16 KiB
Plaintext
Executable File
*******************************************************************************
|
|
*
|
|
* SQL catalog verification tool.
|
|
*
|
|
* Module %M% Version %I% Date %H%
|
|
*
|
|
* (c) Copyright 1998 Ardent Software Inc. - All Rights Reserved
|
|
* This is unpublished proprietary source code of Ardent Software Inc.
|
|
* The copyright notice above does not evidence any actual or intended
|
|
* publication of such source code.
|
|
*
|
|
*******************************************************************************
|
|
*
|
|
* Maintenance log - insert most recent change descriptions at top
|
|
*
|
|
* Date.... GTAR# WHO Description.........................................
|
|
* 10/14/98 23801 SAP Change copyrights.
|
|
* 04/19/96 17683 JBG Parse input to support Quoted Identifiers
|
|
* 03/13/96 17797 AGM Replace SH and DOS with OS.EXEC
|
|
* 01/25/96 17671 LAG Windows NT port
|
|
* 01/05/96 17661 JBG Support VIEW name, fix LPTR page no
|
|
* 09/30/94 14846 MGM FIX when no schema name multiple options
|
|
* 09/02/94 14804 MGM Add locking if FIX
|
|
* 08/12/94 14217 MGM Add view/association support for Rev. 8
|
|
* 08/27/93 11990 DPB VERIFY.SQL now runs with breaks disabled.
|
|
* 08/02/93 11940 DPB Remove Unsecuring/Securing messages.
|
|
* 07/18/93 10131 DPB Initial creation of catalog verification tool.
|
|
*
|
|
*******************************************************************************
|
|
INCLUDE UNIVERSE.INCLUDE VERIFY.COM
|
|
INCLUDE UNIVERSE.INCLUDE MACHINE.NAME
|
|
|
|
******************
|
|
** Windows NT port
|
|
**
|
|
DEFFUN IS.FULLPATH(FILESPEC) CALLING "*IS.FULLPATH"
|
|
DEFFUN IS.EQPATHS(FILESPEC.A, FILESPEC.B) CALLING "*IS.EQPATHS"
|
|
DEFFUN GET.DIRNAME(FILESPEC) CALLING "*GET.DIRNAME"
|
|
DEFFUN GET.BASENAME(FILESPEC) CALLING "*GET.BASENAME"
|
|
**
|
|
******************
|
|
|
|
argv = SYSTEM(1030)
|
|
argc = DCOUNT(argv,@FM)
|
|
|
|
BREAK OFF ;* Turn breaks off.
|
|
ASSIGN 1 TO SYSTEM(999) ;* Turn off 'Q' to quit at 'Press a Key'.
|
|
|
|
******************
|
|
** Windows NT port
|
|
**
|
|
UV.ROOT = SYSTEM(32)
|
|
**
|
|
******************
|
|
|
|
Fix = 0
|
|
Brief = 0
|
|
Nopage = 0
|
|
LPTR = 0
|
|
Pchan = 0
|
|
NFIX = 0
|
|
IsaVIEW = 0
|
|
MAXSLEEP = 150 ; * Number of 2-second intervals to sleep awaiting freeing of lock
|
|
|
|
ECount = 0
|
|
FECount = 0
|
|
IECount = 0
|
|
|
|
UID = SYSTEM(28)
|
|
GOSUB GETUSER
|
|
|
|
OPEN "UV_USERS" TO uvusers
|
|
ELSE
|
|
CRT "Can't Open UV_USERS"
|
|
BREAK ON
|
|
STOP
|
|
END
|
|
|
|
READ urec FROM uvusers,Uname
|
|
THEN
|
|
IF urec<1> = "YES" THEN DBA = 1 ELSE DBA = 0
|
|
END
|
|
ELSE
|
|
Fix = 0
|
|
FECount += 1
|
|
CALL *WRAP.PRINT("** '":Uname:"' is not a valid SQL user.",Width,0,3)
|
|
GOTO CLOSEFILES
|
|
END
|
|
CLOSE uvusers
|
|
|
|
BEGIN CASE
|
|
CASE UPCASE(argv<2>) = "TABLE" OR UPCASE(argv<2>) = "VIEW"
|
|
IF UPCASE(argv<2>) = "VIEW" then IsaVIEW = 1
|
|
IF argc < 3
|
|
THEN
|
|
CALL *WRAP.PRINT("** Invalid number of arguments.",Width,0,3)
|
|
FECount += 1
|
|
NFIX = 0
|
|
GOTO CLOSEFILES
|
|
END
|
|
File = argv<3>
|
|
FOR I = 4 to argc
|
|
IF UPCASE(argv<I>) = "FIX" THEN Fix = 1
|
|
ELSE IF UPCASE(argv<I>) = "BRIEF" THEN Brief = 1
|
|
ELSE IF UPCASE(argv<I>) = "NOPAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "NO.PAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "LPTR"
|
|
THEN
|
|
LPTR = 1
|
|
IF NUM(argv<I+1>)
|
|
THEN
|
|
Pchan = argv<I+1>
|
|
I = I +1
|
|
END
|
|
ELSE Pchan = 0
|
|
END
|
|
ELSE
|
|
CALL *WRAP.PRINT("Ignoring unknown keyword '":argv<I>:"'.",Width,0,0)
|
|
END
|
|
NEXT I
|
|
NFIX = Fix
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.TABLE(File,@SCHEMA,Fix,Brief,IsaVIEW)
|
|
GOSUB CLOSEFILES
|
|
CASE UPCASE(argv<2>) = "SCHEMA"
|
|
IF argc = 2
|
|
THEN
|
|
NFIX = Fix
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.SCHEMA("",Fix,Brief,1)
|
|
GOSUB CLOSEFILES
|
|
END
|
|
ELSE IF argc = 3
|
|
THEN
|
|
IF UPCASE(argv<3>) = "FIX" THEN Fix = 1
|
|
ELSE IF UPCASE(argv<3>) = "BRIEF" THEN Brief = 1
|
|
ELSE IF UPCASE(argv<3>) = "NOPAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<3>) = "NO.PAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<3>) = "LPTR" THEN LPTR = 1
|
|
IF Fix OR Brief OR LPTR OR Nopage
|
|
THEN
|
|
NFIX = Fix
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.SCHEMA("",Fix,Brief,1)
|
|
GOSUB CLOSEFILES
|
|
END
|
|
ELSE
|
|
NFIX = Fix
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.SCHEMA(argv<3>,Fix,Brief,1)
|
|
GOSUB CLOSEFILES
|
|
END
|
|
END
|
|
ELSE IF argc > 3
|
|
THEN
|
|
SCHNAME = 0
|
|
FOR I = 3 to argc
|
|
IF UPCASE(argv<I>) = "FIX" THEN Fix = 1
|
|
ELSE IF UPCASE(argv<I>) = "BRIEF" THEN Brief = 1
|
|
ELSE IF UPCASE(argv<I>) = "NOPAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "NO.PAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "LPTR"
|
|
THEN
|
|
LPTR = 1
|
|
IF NUM(argv<I+1>)
|
|
THEN
|
|
Pchan = argv<I+1>
|
|
I = I +1
|
|
END
|
|
ELSE Pchan = 0
|
|
END
|
|
ELSE
|
|
IF (I = 3)
|
|
THEN SCHNAME = 1
|
|
ELSE CALL *WRAP.PRINT("Ignoring unknown keyword '":argv<I>:"'.",Width,0,0)
|
|
END
|
|
NEXT I
|
|
NFIX = Fix
|
|
GOSUB OPENFILES
|
|
IF (SCHNAME)
|
|
THEN CALL *VERIFY.SCHEMA(argv<3>,Fix,Brief,1)
|
|
ELSE CALL *VERIFY.SCHEMA("",Fix,Brief,1)
|
|
GOSUB CLOSEFILES
|
|
END
|
|
CASE UPCASE(argv<2>) = "SCHEMAS"
|
|
IF argc = 2
|
|
THEN
|
|
NFIX = Fix
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.SCHEMAS(Fix,Brief,0)
|
|
GOSUB CLOSEFILES
|
|
END
|
|
ELSE IF argc > 2
|
|
THEN
|
|
FOR I = 3 to argc
|
|
IF UPCASE(argv<I>) = "FIX"
|
|
THEN
|
|
CALL *WRAP.PRINT("** VERIFY.SQL SCHEMAS may not be run with the FIX option.",Width,0,3)
|
|
FECount += 1
|
|
NFIX = 0
|
|
GOTO CLOSEFILES
|
|
END
|
|
IF UPCASE(argv<I>) = "NOPAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "NO.PAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "LPTR"
|
|
THEN
|
|
LPTR = 1
|
|
IF NUM(argv<I+1>)
|
|
THEN
|
|
Pchan = argv<I+1>
|
|
I = I +1
|
|
END
|
|
ELSE Pchan = 0
|
|
END
|
|
ELSE
|
|
CALL *WRAP.PRINT("Ignoring unknown keyword '":argv<3>:"'.",Width,0,0)
|
|
END
|
|
NEXT I
|
|
NFIX = Fix
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.SCHEMAS(Fix,Brief,0)
|
|
GOSUB CLOSEFILES
|
|
END
|
|
CASE UPCASE(argv<2>) = "CATALOG"
|
|
|
|
IF argc = 2
|
|
THEN
|
|
NFIX = Fix
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.CATALOG(Fix,Brief)
|
|
GOSUB CLOSEFILES
|
|
END
|
|
ELSE IF argc > 2
|
|
THEN
|
|
FOR I = 3 to argc
|
|
IF UPCASE(argv<I>) = "FIX" THEN Fix = 1
|
|
ELSE IF UPCASE(argv<I>) = "BRIEF" THEN Brief = 1
|
|
ELSE IF UPCASE(argv<I>) = "NOPAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "NO.PAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "LPTR"
|
|
THEN
|
|
LPTR = 1
|
|
IF NUM(argv<I+1>)
|
|
THEN
|
|
Pchan = argv<I+1>
|
|
I = I +1
|
|
END
|
|
ELSE Pchan = 0
|
|
END
|
|
ELSE
|
|
CALL *WRAP.PRINT("Ignoring unknown keyword '":argv<3>:"'.",Width,0,0)
|
|
END
|
|
NEXT I
|
|
NFIX = Fix
|
|
IF Fix AND NOT(DBA)
|
|
THEN
|
|
FECount += 1
|
|
CALL *WRAP.PRINT("** You must be an SQL DBA to use the FIX option with VERIFY.SQL CATALOG",Width,0,3)
|
|
NFIX = 0
|
|
END
|
|
ELSE
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.CATALOG(Fix,Brief)
|
|
END
|
|
GOSUB CLOSEFILES
|
|
END
|
|
CASE UPCASE(argv<2>) = "ALL"
|
|
IF argc = 2
|
|
THEN
|
|
NFIX = Fix
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.SCHEMAS(Fix,Brief,1)
|
|
CALL *VERIFY.CATALOG(Fix,Brief)
|
|
GOSUB CLOSEFILES
|
|
END
|
|
ELSE IF argc > 2
|
|
THEN
|
|
FOR I = 3 to argc
|
|
IF UPCASE(argv<I>) = "FIX" THEN Fix = 1
|
|
ELSE IF UPCASE(argv<I>) = "BRIEF" THEN Brief = 1
|
|
ELSE IF UPCASE(argv<I>) = "NOPAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "NO.PAGE" THEN Nopage = 1
|
|
ELSE IF UPCASE(argv<I>) = "LPTR"
|
|
THEN
|
|
LPTR = 1
|
|
IF NUM(argv<I+1>)
|
|
THEN
|
|
Pchan = argv<I+1>
|
|
I = I +1
|
|
END
|
|
ELSE Pchan = 0
|
|
END
|
|
ELSE
|
|
CALL *WRAP.PRINT("Ignoring unknown keyword '":argv<3>:"'.",Width,0,0)
|
|
END
|
|
NEXT I
|
|
NFIX = Fix
|
|
IF Fix AND NOT(DBA)
|
|
THEN
|
|
FECount += 1
|
|
CALL *WRAP.PRINT("** You must be an SQL DBA to use the FIX option with VERIFY.SQL ALL",Width,0,3)
|
|
NFIX = 0
|
|
END
|
|
ELSE
|
|
GOSUB OPENFILES
|
|
CALL *VERIFY.SCHEMAS(Fix,Brief,1)
|
|
CALL *VERIFY.CATALOG(Fix,Brief)
|
|
END
|
|
GOSUB CLOSEFILES
|
|
END
|
|
CASE 1
|
|
CALL *WRAP.PRINT("** Invalid VERIFY.SQL operation.",Width,0,3)
|
|
FECount += 1
|
|
NFIX = 0
|
|
GOSUB CLOSEFILES
|
|
END CASE
|
|
|
|
RETURN
|
|
|
|
OPENFILES:
|
|
IF (NFIX)
|
|
THEN
|
|
CMD = OS.EXEC:" '":UV.ROOT:"/bin/mksecure -nobobo ":UV.ROOT:"/sql/catalog/"
|
|
|
|
EXECUTE CMD:"UV_TABLES'"
|
|
EXECUTE CMD:"UV_COLUMNS'"
|
|
EXECUTE CMD:"UV_ASSOC'"
|
|
EXECUTE CMD:"UV_USERS'"
|
|
EXECUTE CMD:"UV_SCHEMA'"
|
|
EXECUTE CMD:"UV_VIEWS'"
|
|
END
|
|
|
|
IF LPTR
|
|
THEN
|
|
PRINTER ON
|
|
HEADING @SENTENCE:" 'T' PAGE 'P3''L'"
|
|
END
|
|
|
|
IF Nopage
|
|
THEN
|
|
DUMMY = @(0,0)
|
|
END
|
|
|
|
OPEN "UV_TABLES" TO uvtables
|
|
ELSE
|
|
CRT "Can't Open UV_TABLES"
|
|
BREAK ON
|
|
STOP
|
|
END
|
|
|
|
OPEN "UV_COLUMNS" TO uvcolumns
|
|
ELSE
|
|
CRT "Can't Open UV_COLUMNS"
|
|
BREAK ON
|
|
STOP
|
|
END
|
|
|
|
OPEN "UV_ASSOC" TO uvassoc
|
|
ELSE
|
|
CRT "Can't Open UV_ASSOC"
|
|
BREAK ON
|
|
STOP
|
|
END
|
|
|
|
OPEN "UV_USERS" TO uvusers
|
|
ELSE
|
|
CRT "Can't Open UV_USERS"
|
|
BREAK ON
|
|
STOP
|
|
END
|
|
|
|
OPEN "UV_SCHEMA" TO uvschema
|
|
ELSE
|
|
CRT "Can't Open UV_SCHEMA"
|
|
BREAK ON
|
|
STOP
|
|
END
|
|
|
|
OPEN "UV_VIEWS" TO uvview
|
|
ELSE
|
|
CRT "Can't Open UV_VIEWS"
|
|
BREAK ON
|
|
STOP
|
|
END
|
|
|
|
IF (NFIX)
|
|
THEN
|
|
GOSUB LOCKTABLES
|
|
END
|
|
RETURN
|
|
|
|
CLOSEFILES:
|
|
|
|
IF FECount OR ECount OR IECount
|
|
THEN
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT(" ",Width,0,0)
|
|
END
|
|
|
|
IF IECount
|
|
THEN
|
|
IF IECount = 1 THEN ew = " condition" ELSE ew = " conditions"
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT(IECount:" information-only":ew:" found.",Width,0,0)
|
|
END
|
|
|
|
IF ECount
|
|
THEN
|
|
IF (NFIX)
|
|
THEN
|
|
IF ECount = 1 THEN ew = " error" ELSE ew = " errors"
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT(ECount:ew:" fixed.",Width,0,0)
|
|
END
|
|
ELSE
|
|
IF ECount = 1 THEN ew = " error" ELSE ew = " errors"
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT(ECount:" fixable":ew:" found.",Width,0,0)
|
|
END
|
|
END
|
|
|
|
IF FECount
|
|
THEN
|
|
IF FECount = 1 THEN ew = " operation" ELSE ew = " operations"
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT(FECount:" verify":ew:" discontinued.",Width,0,0)
|
|
END
|
|
|
|
IF ECount OR FECount OR IECount
|
|
THEN
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT(" ",Width,0,0)
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT("Items marked with a '!' are information messages only.",Width,0,0)
|
|
IF NFIX
|
|
THEN
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT("Items marked with a '*' have been fixed.",Width,0,0)
|
|
END
|
|
ELSE
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT("Items marked with a '*' can be fixed by using the FIX option to VERIFY.SQL.",Width,0,0)
|
|
END
|
|
IF NOT(Brief) THEN CALL *WRAP.PRINT("Items marked with a '**' are situations where VERIFY.SQL could not continue.",Width,0,0)
|
|
END
|
|
|
|
IF (NFIX)
|
|
THEN
|
|
FILEUNLOCK uvtables
|
|
FILEUNLOCK uvcolumns
|
|
FILEUNLOCK uvassoc
|
|
FILEUNLOCK uvusers
|
|
FILEUNLOCK uvschema
|
|
FILEUNLOCK uvview
|
|
END
|
|
|
|
CLOSE uvtables
|
|
CLOSE uvcolumns
|
|
CLOSE uvassoc
|
|
CLOSE uvusers
|
|
CLOSE uvschema
|
|
CLOSE uvview
|
|
|
|
IF (NFIX)
|
|
THEN
|
|
CMD = OS.EXEC:" '":UV.ROOT:"/bin/mksecure ":UV.ROOT:"/sql/catalog/"
|
|
|
|
EXECUTE CMD:"UV_TABLES'"
|
|
EXECUTE CMD:"UV_COLUMNS'"
|
|
EXECUTE CMD:"UV_ASSOC'"
|
|
EXECUTE CMD:"UV_USERS'"
|
|
EXECUTE CMD:"UV_SCHEMA'"
|
|
EXECUTE CMD:"UV_VIEWS'"
|
|
END
|
|
|
|
IF LPTR
|
|
THEN
|
|
PRINTER CLOSE ON Pchan
|
|
END
|
|
|
|
BREAK ON ;* Turn breaks back on.
|
|
ASSIGN 0 TO SYSTEM(999) ;* Turn on 'Q' to quit at 'Press a key'.
|
|
|
|
RETURN
|
|
|
|
***************************************************************************
|
|
* This routine gets a SICA from an SQL file. We return the SICA in the *
|
|
* variable Sica or 0 if the is no sica in the file. *
|
|
***************************************************************************
|
|
GETUSER:
|
|
******************
|
|
** Windows NT port
|
|
**
|
|
Uname = OCONV(UID, "PW")
|
|
IF STATUS() # 0 THEN Uname = -1
|
|
**
|
|
******************
|
|
RETURN
|
|
|
|
*****************************************************************************
|
|
* This routine will try to aquire the 6 file locks VERIFY.SQL needs to run *
|
|
* in FIX mode. *
|
|
*****************************************************************************
|
|
LOCKTABLES:
|
|
LSLEEP = MAXSLEEP
|
|
GOTO FIRSTRY
|
|
LTABLES:
|
|
FILEUNLOCK uvtables
|
|
FILEUNLOCK uvcolumns
|
|
FILEUNLOCK uvassoc
|
|
FILEUNLOCK uvusers
|
|
FILEUNLOCK uvschema
|
|
FILEUNLOCK uvview
|
|
|
|
SLEEP 2
|
|
LSLEEP -= 1
|
|
IF LSLEEP <= 0
|
|
THEN
|
|
CRT "VERIFY.SQL failed to acquire the SQL catalog locks needed to run FIX."
|
|
GOSUB CLOSEFILES
|
|
STOP
|
|
END
|
|
IF NOT(MOD(LSLEEP,15))
|
|
THEN
|
|
CALL *WRAP.PRINT("Failed to acquire (":MISSED:") catalog lock needed to run FIX. ":LSLEEP:" more retries.",Width,0,0)
|
|
END
|
|
|
|
FIRSTRY:
|
|
FILELOCK uvtables
|
|
LOCKED
|
|
MISSED = 'UV_TABLES'
|
|
GOTO LTABLES
|
|
END
|
|
|
|
FILELOCK uvusers
|
|
LOCKED
|
|
MISSED = 'UV_USERS'
|
|
GOTO LTABLES
|
|
END
|
|
|
|
FILELOCK uvcolumns
|
|
LOCKED
|
|
MISSED = 'UV_COLUMNS'
|
|
GOTO LTABLES
|
|
END
|
|
|
|
FILELOCK uvschema
|
|
LOCKED
|
|
MISSED = 'UV_SCHEMA'
|
|
GOTO LTABLES
|
|
END
|
|
|
|
FILELOCK uvassoc
|
|
LOCKED
|
|
MISSED = 'UV_ASSOC'
|
|
GOTO LTABLES
|
|
END
|
|
|
|
FILELOCK uvview
|
|
LOCKED
|
|
MISSED = 'UV_VIEWS'
|
|
GOTO LTABLES
|
|
END
|
|
|
|
RETURN
|
|
|