2675 lines
108 KiB
Plaintext
2675 lines
108 KiB
Plaintext
|
*******************************************************************************
|
||
|
|
*
|
||
|
|
* 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.........................................
|
||
|
|
* 11/03/98 23807 JBG update message for correct account
|
||
|
|
* 10/29/98 23807 JBG Better output for WRITE errors on UV_COLUMNS
|
||
|
|
* 10/14/98 23801 SAP Change copyrights.
|
||
|
|
* 10/14/98 23802 JBG Fix FileName
|
||
|
|
* 04/17/97 20202 MGM Fix for DATA and DICT keywords
|
||
|
|
* 11/07/96 19580 AGM Fix execute of UVfile in EXISTS on NT
|
||
|
|
* 07/11/96 18810 HSB Remove references to old style UV_ASSOC.ORDERING data.
|
||
|
|
* 04/24/96 15080 JBG PK go into ASSOC when UV_TABLES built.
|
||
|
|
* 04/24/96 17683 JBG Use SYSTEM(1031,string) to quote tablename).
|
||
|
|
* 03/26/96 17337 JBG UV_VIEWS table col is multi-valued
|
||
|
|
* 03/13/96 17797 AGM Replace SH and DOS with OS.EXEC
|
||
|
|
* 01/25/96 17671 LAG Windows NT port
|
||
|
|
* 01/05/96 17337 JBG New UV_VIEWS cols, fix ASSOC, multiple primary ketys
|
||
|
|
* 05/03/95 15080 JBG ASSOC def different then SICA
|
||
|
|
* 03/06/95 15080 JBG Put Primary keys in assoc UV_TABLES COLUMNS entry
|
||
|
|
* 09/29/94 14846 MGM Coordinate UVfile output change for views
|
||
|
|
* 08/12/94 14217 MGM Add view/association support for Rev. 8
|
||
|
|
* 08/27/93 11990 DPB VERIFY.SQL changed to run with breaks diabled.
|
||
|
|
* 07/18/93 10131 DPB Initial creation of catalog verification tool.
|
||
|
|
*
|
||
|
|
*******************************************************************************
|
||
|
|
*
|
||
|
|
* VERIFY.TABLE(NameOrPath, Schema, Fix, Brief, IsaView)
|
||
|
|
*
|
||
|
|
* This subroutine will verify the SQL catalog contents for a specific table.
|
||
|
|
*
|
||
|
|
* NameOrPath This variable is either the Name of the table to be
|
||
|
|
* verified, or the Full Path to the table to be verified.
|
||
|
|
* Schema This variable tells you what schema we expect the table
|
||
|
|
* to be located in.
|
||
|
|
* Fix This boolean variable indicates whether or not any
|
||
|
|
* discrepencies found should be fixed.
|
||
|
|
* Brief This boolean variable indicates the amout of output
|
||
|
|
* expected from the subroutine. When turned on only
|
||
|
|
* extrememly critical problems are reported.
|
||
|
|
* IsaView This boolean variable indicates whether or not
|
||
|
|
* a view is being verified
|
||
|
|
*******************************************************************************
|
||
|
|
SUBROUTINE VERIFY.TABLE(NameOrPath, SchemaL, Fix, Brief, IsaView)
|
||
|
|
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"
|
||
|
|
**
|
||
|
|
******************
|
||
|
|
|
||
|
|
Schema = SchemaL ; * Get around writting to @Schema problem.
|
||
|
|
schemachange = 0 ; * The schema name has changed for this table.
|
||
|
|
namechange = 0 ; * The table name has changed for this table.
|
||
|
|
nodata = 0 ; * There is no SQL catalog data for this table.
|
||
|
|
copydata = 0 ; * The SQL catalog data for this table needs
|
||
|
|
; * to be copied.
|
||
|
|
movedata = 0 ; * The SQL catalog data for this table points
|
||
|
|
; * to the wrong place. We must move it.
|
||
|
|
Verbose = NOT(Brief) ; * Opposite of Brief. (Saves opcodes)
|
||
|
|
IsaTable = NOT(IsaView) ; * Opposite of IsaView
|
||
|
|
CatChange = 0 ; * The catalog data for the file has been
|
||
|
|
; * changed
|
||
|
|
HaveData = 0 ; * The catalog data for the table exists.
|
||
|
|
CrossSchema = 0 ; * For views which span schemas
|
||
|
|
|
||
|
|
IF IsaView THEN SQLobject = 'view'
|
||
|
|
ELSE SQLobject = 'table'
|
||
|
|
|
||
|
|
IF IS.FULLPATH(NameOrPath)
|
||
|
|
THEN
|
||
|
|
|
||
|
|
TablePath = NameOrPath
|
||
|
|
TableName = GET.BASENAME(NameOrPath)
|
||
|
|
FileName = TableName
|
||
|
|
path = GET.DIRNAME(NameOrPath)
|
||
|
|
|
||
|
|
IF COUNT(Schema,@FM)
|
||
|
|
THEN
|
||
|
|
path = Schema<1>
|
||
|
|
Schema = Schema<2>
|
||
|
|
if path # GET.DIRNAME(NameOrPath) THEN
|
||
|
|
path = GET.DIRNAME(NameOrPath)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
********************************************************************
|
||
|
|
* Test the pathname, check for permissions, and see if the file is *
|
||
|
|
* an SQL table. *
|
||
|
|
********************************************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Checking file permissions.",Width,0,0)
|
||
|
|
CHKNAME = NameOrPath
|
||
|
|
GOSUB EXISTS
|
||
|
|
|
||
|
|
*****************************************************************
|
||
|
|
* These are all the error conditions. EXVAL = 2 is an SQL file. *
|
||
|
|
*****************************************************************
|
||
|
|
BEGIN CASE
|
||
|
|
CASE EXVAL = 0
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** There is no ":SQLobject:" '":NameOrPath:"'.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
CASE EXVAL = 1
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** '":NameOrPath:"' is not an SQL ":SQLobject:".",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
CASE EXVAL = 3
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** Unable to verify '":NameOrPath:"'.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END CASE
|
||
|
|
|
||
|
|
******************************
|
||
|
|
* Get the SICA for the file. *
|
||
|
|
******************************
|
||
|
|
GOSUB GETSICA
|
||
|
|
IF Sica = 0 THEN
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** There are problems with the SICA of the ":SQLobject:" '":NameOrPath:"'.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
*********************************************************************
|
||
|
|
* Get the Schema name for the directory in which the table resides. *
|
||
|
|
*********************************************************************
|
||
|
|
IF FileName # Sica<NAME,TABLENAME> THEN
|
||
|
|
TableName = Sica<NAME,TABLENAME>
|
||
|
|
END
|
||
|
|
|
||
|
|
VocPath = GET.DIRNAME(NameOrPath):"/VOC"
|
||
|
|
key = Sica<NAME,SCHEMANAME>
|
||
|
|
READ catrec FROM uvschema,key
|
||
|
|
THEN
|
||
|
|
IF catrec<2> # path THEN
|
||
|
|
VocPath = catrec<2>:"/VOC"
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** No SQL catalog data for schema '":key:"' in UV_SCHEMA.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
EXECUTE OS.EXEC:" '":UV.ROOT:"/bin/UVfile -s ":VocPath:"'" CAPTURING junk
|
||
|
|
IF UPCASE(junk<2>[1,13]) = "UNIVERSE/SQL:"
|
||
|
|
THEN
|
||
|
|
junk = CONVERT(" ",@FM,junk<2>)
|
||
|
|
Schema = junk<DCOUNT(junk,@FM)>
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** The ":SQLobject:" '":catrec<2>:"' is not located in a valid schema.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
*********************************************************
|
||
|
|
* Open the VOC file and read a record of the same name. *
|
||
|
|
*********************************************************
|
||
|
|
OPENPATH VocPath TO vocfile
|
||
|
|
ELSE
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** Unable to open the VOC file.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
NeedVoc = 0
|
||
|
|
READ namerec FROM vocfile,TableName
|
||
|
|
THEN
|
||
|
|
IF namerec<1>[1,1] # 'F'
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
CALL *WRAP.PRINT("* Item '":TableName:"' in the VOC file is not an 'F' pointer.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF namerec<2> # NameOrPath[LEN(NameOrPath)-LEN(namerec<2>)+1,LEN(NameOrPath)]
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Another file already has a VOC F pointer called '":TableName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Adding VOC F pointer for the ":SQLobject:" '":TableName:"' in the schema '":Schema:"'.",Width,0,2)
|
||
|
|
NeedVoc = 1
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* There is no VOC F pointer for the ":SQLobject:" '":TableName:"' in the schema '":Schema:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
key = Schema:@TM:TableName
|
||
|
|
|
||
|
|
***************************************
|
||
|
|
* Get the catalog data for the table. *
|
||
|
|
***************************************
|
||
|
|
READ catrec FROM uvtables,key
|
||
|
|
**********************************************
|
||
|
|
* we have the SQL catalog data for the table *
|
||
|
|
**********************************************
|
||
|
|
THEN
|
||
|
|
HaveData = 1
|
||
|
|
IF NOT(IS.EQPATHS(NameOrPath, catrec<6>)) THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Existing catalog data points to another ":SQLobject:".",80,5,0)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
key = Sica<NAME,SCHEMANAME>:@TM:Sica<NAME,TABLENAME>
|
||
|
|
READ catrec FROM uvtables,key
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Possible Moved or Copied ":SQLobject:".",Width,0,0)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
CALL *WRAP.PRINT("* No SQL catalog data for the ":SQLobject:" '":TableName:" (":Schema:")' in UV_TABLES.",Width,0,2)
|
||
|
|
nodata = 1
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
IF NeedVoc = 1
|
||
|
|
THEN
|
||
|
|
namerec = "F":@FM:catrec<6>:@FM:catrec<7>
|
||
|
|
WRITE namerec ON vocfile,TableName
|
||
|
|
END
|
||
|
|
END
|
||
|
|
******************************************************************************
|
||
|
|
*
|
||
|
|
* This section is for calling verify.table with a table name.
|
||
|
|
*
|
||
|
|
******************************************************************************
|
||
|
|
ELSE
|
||
|
|
|
||
|
|
IF COUNT(Schema,@FM)
|
||
|
|
THEN
|
||
|
|
path = Schema<1>
|
||
|
|
Schema = Schema<2>
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
**************************************
|
||
|
|
* Get the current working directory. *
|
||
|
|
**************************************
|
||
|
|
path = @PATH
|
||
|
|
END
|
||
|
|
|
||
|
|
IF Schema = ""
|
||
|
|
THEN
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** The current directory is not a valid schema.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
IF IsaView
|
||
|
|
THEN
|
||
|
|
IF (@SCHEMA # Schema) THEN CrossSchema = 1
|
||
|
|
END
|
||
|
|
key = Schema:@TM:NameOrPath
|
||
|
|
|
||
|
|
***************************************
|
||
|
|
* Get the catalog data for the table. *
|
||
|
|
***************************************
|
||
|
|
READ catrec FROM uvtables,key
|
||
|
|
**********************************************
|
||
|
|
* we have the SQL catalog data for the table *
|
||
|
|
**********************************************
|
||
|
|
THEN
|
||
|
|
|
||
|
|
HaveData = 1
|
||
|
|
|
||
|
|
IF (catrec<2> # "BASE TABLE") AND (IsaTable)
|
||
|
|
THEN
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** '":NameOrPath:"' is not an SQL base table.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
***********************************************
|
||
|
|
* Check file existence, type and permissions. *
|
||
|
|
***********************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Checking file permissions.",Width,0,0)
|
||
|
|
CHKNAME = catrec<6>
|
||
|
|
GOSUB EXISTS
|
||
|
|
|
||
|
|
BEGIN CASE
|
||
|
|
CASE EXVAL = 0
|
||
|
|
ECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* There is no ":SQLobject:" '":NameOrPath:"' in the schema '":Schema:"'.",Width,0,2)
|
||
|
|
|
||
|
|
IF NOT(Fix)
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* SQL catalog data for ":SQLobject:" '":NameOrPath:" (":Schema:")' should be deleted.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting SQL catalog data for ":SQLobject:" '":NameOrPath:"'.",Width,0,2)
|
||
|
|
GOSUB DELETE.DATA
|
||
|
|
END
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
CASE EXVAL = 1
|
||
|
|
ECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* '":NameOrPath:"' is not an SQL ":SQLobject:".",Width,0,2)
|
||
|
|
IF NOT(Fix)
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* SQL catalog data for ":SQLobject:" '":NameOrPath:" (":Schema:")' should be deleted.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting SQL catalog data for ":SQLobject:" '":NameOrPath:"'.",Width,0,2)
|
||
|
|
GOSUB DELETE.DATA
|
||
|
|
END
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
CASE EXVAL = 3
|
||
|
|
FECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("** Unable to verify '":NameOrPath:"'.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END CASE
|
||
|
|
|
||
|
|
*********************************************************
|
||
|
|
* Open the VOC file and read a record of the same name. *
|
||
|
|
*********************************************************
|
||
|
|
OPEN "VOC" TO vocfile
|
||
|
|
ELSE
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** Unable to open the VOC file.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
IF CrossSchema = 0
|
||
|
|
THEN
|
||
|
|
READ namerec FROM vocfile,NameOrPath
|
||
|
|
THEN
|
||
|
|
IF namerec<1>[1,1] # 'F'
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
CALL *WRAP.PRINT("* Item '":NameOrPath:"' in the VOC file is not an 'F' pointer.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Adding VOC F pointer for the ":SQLobject:" '":NameOrPath:"' in the schema '":Schema:"'.",Width,0,2)
|
||
|
|
namerec = "F":@FM:catrec<6>:@FM:catrec<7>
|
||
|
|
WRITE namerec ON vocfile,NameOrPath
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
CALL *WRAP.PRINT("* There is no VOC F pointer for the ":SQLobject:" '":NameOrPath:"' in the schema '":Schema:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
***************************************
|
||
|
|
* Get the SICA for the current table. *
|
||
|
|
***************************************
|
||
|
|
GOSUB GETSICA
|
||
|
|
|
||
|
|
IF Sica = 0 THEN
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** There are problems with the SICA of the ":SQLobject:" '":catrec<6>,Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
TablePath = catrec<6>
|
||
|
|
TableName = NameOrPath
|
||
|
|
FileName = GET.BASENAME(catrec<6>)
|
||
|
|
path = GET.DIRNAME(catrec<6>)
|
||
|
|
END
|
||
|
|
**********************************************
|
||
|
|
* There is NO SQL catalog data for the table *
|
||
|
|
**********************************************
|
||
|
|
ELSE
|
||
|
|
|
||
|
|
*********************************************************
|
||
|
|
* Open the VOC file and read a record of the same name. *
|
||
|
|
*********************************************************
|
||
|
|
OPEN "VOC" TO vocfile
|
||
|
|
ELSE
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** Unable to open the VOC file.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
IF CrossSchema
|
||
|
|
THEN
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** There is no view '":NameOrPath:"' in the local schema.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
READ namerec FROM vocfile,NameOrPath
|
||
|
|
THEN
|
||
|
|
IF namerec<1>[1,1] # 'F'
|
||
|
|
THEN
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** Item '":NameOrPath:"' in the VOC file is not an 'F' pointer.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** There is no ":SQLobject:" '":NameOrPath:"' in the schema '":Schema:"'.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
|
||
|
|
**************************************************
|
||
|
|
* Make sure that the file found is a local file. *
|
||
|
|
**************************************************
|
||
|
|
IF (IS.FULLPATH(namerec<2>)) OR (namerec<2>[1,1] = ".") OR (COUNT(namerec<2>, "!"))
|
||
|
|
THEN
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** '":NameOrPath:"' is not a local file.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
***********************************************
|
||
|
|
* Check file existence, type and permissions. *
|
||
|
|
***********************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Checking file permissions.",Width,0,0)
|
||
|
|
CHKNAME = namerec<2>
|
||
|
|
GOSUB EXISTS
|
||
|
|
BEGIN CASE
|
||
|
|
CASE EXVAL = 0
|
||
|
|
FECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("** The VOC entry '":NameOrPath:"' points to a non-existent file.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
CASE EXVAL = 1
|
||
|
|
FECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("** '":NameOrPath:"' is not an SQL ":SQLobject:".",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
CASE EXVAL = 3
|
||
|
|
FECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("** Unable to verify '":NameOrPath:"'.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END CASE
|
||
|
|
|
||
|
|
******************************************************
|
||
|
|
* Check for duplicate VOC pointers to the same file. *
|
||
|
|
******************************************************
|
||
|
|
IF IsaTable
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Checking for duplicate VOC F pointers.",Width,0,0)
|
||
|
|
GOSUB DUPFPOINTER
|
||
|
|
|
||
|
|
IF DFVAL # 0
|
||
|
|
THEN
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** There are more than one VOC F pointers to the table '":path:"/":namerec<2>:"'.",Width,0,3)
|
||
|
|
CALL *WRAP.PRINT("** To verify this table use VERIFY.SQL TABLE <fullpath>.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
***************************************
|
||
|
|
* Get the SICA for the current table. *
|
||
|
|
***************************************
|
||
|
|
GOSUB GETSICA
|
||
|
|
IF Sica = 0 THEN
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** There are problems with the SICA of the ":SQLobject:" '":path:"/":namerec<2>:"'.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
***********************************************
|
||
|
|
* Get the catalog data for the current table. *
|
||
|
|
***********************************************
|
||
|
|
key = Sica<NAME,SCHEMANAME>:@TM:Sica<NAME,TABLENAME>
|
||
|
|
READ catrec FROM uvtables,key
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Possible Moved or Copied ":SQLobject:".",Width,0,0)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
CALL *WRAP.PRINT("* No SQL catalog data for the ":SQLobject:" '":NameOrPath:" (":Schema:")' in UV_TABLES.",Width,0,2)
|
||
|
|
nodata = 1
|
||
|
|
END
|
||
|
|
TablePath = namerec<2>
|
||
|
|
TableName = NameOrPath
|
||
|
|
* fix 23802 below *
|
||
|
|
FileName = TableName
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
**********************************************************
|
||
|
|
* This section is for after we have the SQL catalog data *
|
||
|
|
* and the SICA information for a file. *
|
||
|
|
**********************************************************
|
||
|
|
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Doing verify on ":SQLobject:" '":NameOrPath:"'.",Width,0,0)
|
||
|
|
|
||
|
|
**********************************************
|
||
|
|
* check to see if the table name is the same *
|
||
|
|
**********************************************
|
||
|
|
IF Sica<NAME,TABLENAME> # TableName
|
||
|
|
THEN
|
||
|
|
IF Verbose
|
||
|
|
THEN
|
||
|
|
IECount += 1
|
||
|
|
CALL *WRAP.PRINT("! The OS ":SQLobject:" name '":TableName:"' does not match ":SQLobject:" name '":Sica<NAME,TABLENAME>:"' found in the SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
namechange = 1
|
||
|
|
IF IsaView THEN RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
***********************************************
|
||
|
|
* check to see if the schema name is the same *
|
||
|
|
***********************************************
|
||
|
|
IF Sica<NAME,SCHEMANAME> # Schema
|
||
|
|
THEN
|
||
|
|
IF Verbose
|
||
|
|
THEN
|
||
|
|
IECount += 1
|
||
|
|
CALL *WRAP.PRINT("! The ":SQLobject:" '":NameOrPath:"' is located in the schema '":Schema:"' rather than in the schema '":Sica<NAME,SCHEMANAME>:"' as specified in the SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
schemachange = 1
|
||
|
|
IF IsaView THEN RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
*******************************************************
|
||
|
|
* Check to see if the table has been moved or copied. *
|
||
|
|
*******************************************************
|
||
|
|
GOSUB CHKCOPY
|
||
|
|
|
||
|
|
IF nodata
|
||
|
|
THEN
|
||
|
|
*******************************************************
|
||
|
|
* If no catalog data exists then build the data from *
|
||
|
|
* the SICA of the file. *
|
||
|
|
*******************************************************
|
||
|
|
|
||
|
|
*********************************************
|
||
|
|
* If we are not fixing the print a message. *
|
||
|
|
*********************************************
|
||
|
|
ECount += 1
|
||
|
|
IF NOT(Fix)
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* SQL catalog data for ":SQLobject:" '":TableName:" (":Schema:")' should be created.",Width,0,2)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
IF Fix THEN GOSUB BUILDDATA
|
||
|
|
END
|
||
|
|
ELSE IF movedata
|
||
|
|
THEN
|
||
|
|
***************************************************
|
||
|
|
* If the catalog data must be moved then move it, *
|
||
|
|
* and then verify the data. *
|
||
|
|
***************************************************
|
||
|
|
|
||
|
|
*********************************************
|
||
|
|
* If we are not fixing the print a message. *
|
||
|
|
*********************************************
|
||
|
|
ECount += 1
|
||
|
|
IF NOT(Fix)
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* SQL catalog data for ":SQLobject:" '":Sica<NAME,TABLENAME>:" (":Sica<NAME,SCHEMANAME>:")' should be moved.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF Fix THEN GOSUB MOVEDATA
|
||
|
|
END
|
||
|
|
ELSE IF copydata
|
||
|
|
THEN
|
||
|
|
****************************************************
|
||
|
|
* If the catalog data must be copied then copy it, *
|
||
|
|
* and then verify the data. *
|
||
|
|
****************************************************
|
||
|
|
|
||
|
|
*********************************************
|
||
|
|
* If we are not fixing the print a message. *
|
||
|
|
*********************************************
|
||
|
|
ECount += 1
|
||
|
|
IF NOT(Fix)
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* SQL catalog data for ":SQLobject:" '":Sica<NAME,TABLENAME>:" (":Sica<NAME,SCHEMANAME>:")' should be copied.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF Fix THEN GOSUB COPYDATA
|
||
|
|
END
|
||
|
|
|
||
|
|
************************************************************************
|
||
|
|
* Open the file and get the status. Use the status to verify the owner *
|
||
|
|
* of the file against the SICA and the catalog data. *
|
||
|
|
* OPENPATH will fail when it tries to open a view. So make this *
|
||
|
|
* a conditional test for tables only. *
|
||
|
|
************************************************************************
|
||
|
|
IF IsaTable
|
||
|
|
THEN
|
||
|
|
OPENPATH TablePath TO tmpfile ELSE
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** You don't have the correct permissions to verify this file.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
|
||
|
|
STATUS statrec FROM tmpfile ELSE
|
||
|
|
FECount += 1
|
||
|
|
CALL *WRAP.PRINT("** Cannot get status.",Width,0,3)
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
CLOSE tmpfile
|
||
|
|
|
||
|
|
****************************************
|
||
|
|
* Check actual owner against the SICA. *
|
||
|
|
****************************************
|
||
|
|
IF statrec<8> # Sica<NAME,OWNER>
|
||
|
|
THEN
|
||
|
|
IECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("! OS owner does not agree with SICA specified owner.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
*****************************************
|
||
|
|
* Check catalog against the SICA owner. *
|
||
|
|
*****************************************
|
||
|
|
IF catrec<1> # Sica<NAME,OWNER>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing the owner in the catalog data to match the owner from the ":SQLobject:"'s SICA.",Width,0,2)
|
||
|
|
catrec<1> = Sica<NAME,OWNER>
|
||
|
|
CatChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Catalog specified owner does not agree with SICA specified owner.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
***********************************
|
||
|
|
* Check that this is a BASE TABLE *
|
||
|
|
***********************************
|
||
|
|
IF IsaTable
|
||
|
|
THEN
|
||
|
|
IF catrec<2> # "BASE TABLE"
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing the TABLE_TYPE field of the catalog data.",Width,0,2)
|
||
|
|
catrec<2> = "BASE TABLE"
|
||
|
|
CatChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* SQL catalog TABLE_TYPE should be 'BASE TABLE' and is actually '":catrec<2>:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
***********************************
|
||
|
|
* Check that this is a VIEW *
|
||
|
|
***********************************
|
||
|
|
IF IsaView
|
||
|
|
THEN
|
||
|
|
IF catrec<2> # "VIEW"
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing the TABLE_TYPE field of the catalog data.",Width,0,2)
|
||
|
|
catrec<2> = "VIEW"
|
||
|
|
CatChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* SQL catalog TABLE_TYPE should be 'VIEW' and is actually '":catrec<2>:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Checking VIEW '":NameOrPath:"'.",Width,0,0)
|
||
|
|
|
||
|
|
*********************************************************
|
||
|
|
* Read data for the view from the UV_VIEWS table. *
|
||
|
|
*********************************************************
|
||
|
|
READ VWrec FROM uvview,key
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
VWrec = -1
|
||
|
|
END
|
||
|
|
|
||
|
|
*******************************
|
||
|
|
* Verify the view data *
|
||
|
|
*******************************
|
||
|
|
GOSUB VIEWCHECK
|
||
|
|
END
|
||
|
|
|
||
|
|
********************************************
|
||
|
|
* Check that the BASE_TABLE field is blank *
|
||
|
|
********************************************
|
||
|
|
IF catrec<3> # ""
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing the BASE_TABLE field of the catalog data.",Width,0,2)
|
||
|
|
catrec<3> = ""
|
||
|
|
CatChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* SQL catalog BASE_TABLE should be '' and is '":catrec<3>:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
*******************************************************************
|
||
|
|
* Run through all columns that are in the SICA and make sure that *
|
||
|
|
* they are in the catalog entry for this table. If they are then *
|
||
|
|
* verify them, if they aren't then add them. *
|
||
|
|
*******************************************************************
|
||
|
|
tmprec = catrec
|
||
|
|
Alst = ""
|
||
|
|
Anlst = ""
|
||
|
|
Anullst = ""
|
||
|
|
PKlst = ""
|
||
|
|
MaxCols = (Sica<NUMBERS,COLCOUNT>+Sica<NUMBERS,KEYCOUNT>)-1
|
||
|
|
|
||
|
|
FOR Colnum = 0 TO MaxCols
|
||
|
|
|
||
|
|
**************************************************************
|
||
|
|
* Build two lists. One with associations found in the file, *
|
||
|
|
* the other containing lists of columns in each association. *
|
||
|
|
* For example, if there are two associations on the table *
|
||
|
|
* A1 and A2, the array Aname would be: *
|
||
|
|
* "A1":@FM:"A2" *
|
||
|
|
* If A1 contained columns C1 and C2, and A2 contained *
|
||
|
|
* columns C3 and C4 then the array Alst would contain: *
|
||
|
|
* "C1":@VM:"C2":@FM:"C3":@VM:"C4" *
|
||
|
|
* This building is done in a seperate loop, so that we will *
|
||
|
|
* have decent association information when we need to build *
|
||
|
|
* catalog information for a specific column. *
|
||
|
|
**************************************************************
|
||
|
|
|
||
|
|
**************************************************************
|
||
|
|
* Also build a list of Primary Key names in PKlst *
|
||
|
|
**************************************************************
|
||
|
|
|
||
|
|
|
||
|
|
IF Sica<COLUMN+Colnum,ASSOC> # ""
|
||
|
|
THEN
|
||
|
|
Aname = Sica<COLUMN+Colnum,ASSOC>
|
||
|
|
LOCATE(Aname,Alst;iloc)
|
||
|
|
THEN
|
||
|
|
INS Sica<COLUMN+Colnum,COLNAME> BEFORE Anlst<iloc,-1>
|
||
|
|
INS Sica<COLUMN+Colnum,NOTNULL> BEFORE Anullst<iloc,-1>
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
Alst = INSERT(Alst,iloc;Aname)
|
||
|
|
INS Sica<COLUMN+Colnum,COLNAME> BEFORE Anlst<iloc,-1>
|
||
|
|
INS Sica<COLUMN+Colnum,NOTNULL> BEFORE Anullst<iloc,-1>
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
|
||
|
|
IF Sica<COLUMN+Colnum,POSITION> = 0
|
||
|
|
THEN
|
||
|
|
PKname = Sica<COLUMN+Colnum,COLNAME>
|
||
|
|
PKlst = INSERT(PKlst, 1, -1;PKname)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
NEXT Colnum
|
||
|
|
|
||
|
|
For Colnum = 0 to MaxCols
|
||
|
|
|
||
|
|
IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("Checking Column '":Sica<COLUMN+Colnum,COLNAME>:"'.",Width,0,0)
|
||
|
|
END
|
||
|
|
|
||
|
|
**************************************************************
|
||
|
|
* Check to see if the column has data in the UV_TABLES table *
|
||
|
|
**************************************************************
|
||
|
|
LOCATE(Sica<COLUMN+Colnum,COLNAME>,tmprec,4;tmp)
|
||
|
|
THEN
|
||
|
|
|
||
|
|
*******************************************************
|
||
|
|
* The column had data in the UV_TABLES record, so now *
|
||
|
|
* read the data from the UV_COLUMNS table. *
|
||
|
|
*******************************************************
|
||
|
|
READ columnrec FROM uvcolumns,key:@TM:Sica<COLUMN+Colnum,COLNAME>
|
||
|
|
THEN
|
||
|
|
***************************************************
|
||
|
|
* Column data was found in UV_COLUMNS, verify it. *
|
||
|
|
***************************************************
|
||
|
|
GOSUB COLCHECK
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
****************************************************
|
||
|
|
* Column data was not found in UV_COLUMNs. Fix it. *
|
||
|
|
****************************************************
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
GOSUB COLBUILD
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* No SQL catalog data for column '":Sica<COLUMN+Colnum,COLNAME>:"' in UV_COLUMNS.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
*******************************************************
|
||
|
|
* Mark the columns as having been found in UV_TABLES. *
|
||
|
|
*******************************************************
|
||
|
|
DEL tmprec<4,tmp>
|
||
|
|
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Adding SQL catalog data for column '":Sica<COLUMN+Colnum,COLNAME>:"' to UV_TABLES.",Width,0,2)
|
||
|
|
INS Sica<COLUMN+Colnum,COLNAME> BEFORE catrec<4,-1>
|
||
|
|
CatChange = 1
|
||
|
|
END
|
||
|
|
*************************************************************
|
||
|
|
* The column had NO data in the UV_TABLES record. Fix it if *
|
||
|
|
* wanted. Then Read the data from the UV_COLUMNS table. *
|
||
|
|
*************************************************************
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* No SQL catalog data for column '":Sica<COLUMN+Colnum,COLNAME>:"' in UV_TABLES.",Width,0,2)
|
||
|
|
|
||
|
|
READ columnrec FROM uvcolumns,key:@TM:Sica<COLUMN+Colnum,COLNAME>
|
||
|
|
THEN
|
||
|
|
***************************************************
|
||
|
|
* Column data was found in UV_COLUMNS, verify it. *
|
||
|
|
***************************************************
|
||
|
|
GOSUB COLCHECK
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
****************************************************
|
||
|
|
* Column data was not found in UV_COLUMNs. Fix it. *
|
||
|
|
****************************************************
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
GOSUB COLBUILD
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* No SQL catalog data for column '":Sica<COLUMN+Colnum,COLNAME>:"' in UV_COLUMNS.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
END
|
||
|
|
NEXT Colnum
|
||
|
|
|
||
|
|
*****************************************************************
|
||
|
|
* Run through the list of all column defined in UV_TABLES for *
|
||
|
|
* this table that are not defined in the SICA of this table. If *
|
||
|
|
* catalog data exists in UV_COLUMNS for these columns indicate *
|
||
|
|
* that it should be deleted. *
|
||
|
|
*****************************************************************
|
||
|
|
TmpMax = DCOUNT(tmprec<4>,@VM)
|
||
|
|
FOR I = 1 to TmpMax
|
||
|
|
|
||
|
|
******************************************************
|
||
|
|
* Delete the column from the UV_TABLES catalog data. *
|
||
|
|
******************************************************
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
LOCATE(tmprec<4,I>,catrec,4;tmp)
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting column data for '":tmprec<4,I>:"' from UV_TABLES because it does not exist in the SICA.",Width,0,2)
|
||
|
|
DEL catrec<4,tmp>
|
||
|
|
CatChange = 1
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE IF NOT (Brief) THEN CALL *WRAP.PRINT("* Column '":tmprec<4,I>:"' found in UV_TABLES does not exist in the SICA.",Width,0,2)
|
||
|
|
|
||
|
|
******************************************************************
|
||
|
|
* Check for UV_COLUMNS catalog data, and delete it if it exists. *
|
||
|
|
******************************************************************
|
||
|
|
READ columnrec FROM uvcolumns,key:@TM:tmprec<4,I>
|
||
|
|
THEN
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Column data for column '":tmprec<4,I>:"' is being deleted from UV_COLUMNS.",Width,0,2)
|
||
|
|
DELETE uvcolumns,key:@TM:tmprec<4,I>
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Column data for column '":tmprec<4,I>:"' needs to be deleted from UV_COLUMNS.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE NULL
|
||
|
|
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
IF CrossSchema = 0
|
||
|
|
THEN
|
||
|
|
IF NOT(IS.EQPATHS(catrec<6>, path:"/":FileName))
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Correcting the path in the UV_TABLES catalog data for the ":SQLobject:" '":TableName:"'.",Width,0,2)
|
||
|
|
catrec<6> = path:"/":FileName
|
||
|
|
CatChange = 1
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF movedata OR copydata
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* The path in the catalog data for '":Sica<NAME,TABLENAME>:" (":Sica<NAME,SCHEMANAME>:")' doesn't match the path to the ":SQLobject:" '":TableName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* The path in the catalog doesn't match the path to the ":SQLobject:" '":TableName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
******************************************************************
|
||
|
|
* Run through the associtions defined for the file. For each one *
|
||
|
|
* try to read a record from the UV_TABLES file and the UV_ASSOC *
|
||
|
|
* file. After reading the information then check it using the *
|
||
|
|
* association data compiled while verifying the columns. *
|
||
|
|
******************************************************************
|
||
|
|
TmpMax = DCOUNT(Alst,@FM)
|
||
|
|
FOR Acount = 1 to TmpMax
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Checking Association '":Alst<Acount>:"'.",Width,0,0)
|
||
|
|
|
||
|
|
**********************************************************
|
||
|
|
* Read data for the assocition from the UV_TABLES table. *
|
||
|
|
**********************************************************
|
||
|
|
READ Atrec FROM uvtables,key:"_":Alst<Acount>
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
Atrec = -1
|
||
|
|
END
|
||
|
|
|
||
|
|
*********************************************************
|
||
|
|
* Read data for the assocition from the UV_ASSOC table. *
|
||
|
|
*********************************************************
|
||
|
|
READ Arec FROM uvassoc,key:"_":Alst<Acount>
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
Arec = -1
|
||
|
|
END
|
||
|
|
|
||
|
|
*******************************
|
||
|
|
* Verify the association data *
|
||
|
|
*******************************
|
||
|
|
GOSUB ASSOCCHECK
|
||
|
|
|
||
|
|
LOCATE(Alst<Acount>,catrec,8;tmp)
|
||
|
|
ELSE
|
||
|
|
INS Alst<Acount> BEFORE catrec<8,-1>
|
||
|
|
CatChange = 1
|
||
|
|
END
|
||
|
|
|
||
|
|
NEXT Acount
|
||
|
|
|
||
|
|
IF CatChange AND Fix
|
||
|
|
THEN
|
||
|
|
WRITE catrec ON uvtables,key
|
||
|
|
END
|
||
|
|
|
||
|
|
**********************************************
|
||
|
|
* Check that the table/schema pair is in the *
|
||
|
|
* UV_USERS table for the file's owner. *
|
||
|
|
**********************************************
|
||
|
|
UID = Sica<NAME,OWNER>
|
||
|
|
GOSUB GETUSER
|
||
|
|
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Checking UV_USERS data for owner '":Uname:"'.",Width,0,0)
|
||
|
|
|
||
|
|
**********************************
|
||
|
|
* Read user record from UV_USERS *
|
||
|
|
**********************************
|
||
|
|
READ UserRec FROM uvusers,Uname
|
||
|
|
THEN
|
||
|
|
TmpMax = DCOUNT(UserRec<5>,@VM)
|
||
|
|
|
||
|
|
******************************************************
|
||
|
|
* Loop through all tables in TABLE_LIST association *
|
||
|
|
* If the table is there and the schema matches, then *
|
||
|
|
* break out of the loop, otherwise continue. *
|
||
|
|
******************************************************
|
||
|
|
FOR tmp = 1 to TmpMax
|
||
|
|
IF UserRec<5,tmp> = TableName
|
||
|
|
THEN
|
||
|
|
IF UserRec<4,tmp> = Schema
|
||
|
|
THEN GOTO UserLoop
|
||
|
|
END
|
||
|
|
NEXT tmp
|
||
|
|
|
||
|
|
UserLoop:
|
||
|
|
*************************************************
|
||
|
|
* If we made it through the entire list with no *
|
||
|
|
* matches then it is not in the TABLE_LIST. *
|
||
|
|
*************************************************
|
||
|
|
IF tmp > TmpMax
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* Adding Catalog data for ":SQLobject:" '":NameOrPath:"' to the UV_USERS record for user '":Uname:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
INS TableName BEFORE UserRec<5,-1>
|
||
|
|
INS Schema BEFORE UserRec<4,-1>
|
||
|
|
WRITE UserRec ON uvusers,Uname
|
||
|
|
END
|
||
|
|
ELSE IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* No SQL catalog data for ":SQLobject:" '":NameOrPath:"' in the UV_USERS record for user '":Uname:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
***********************************************************
|
||
|
|
* If there is no USER info for the owner, he is not valid *
|
||
|
|
***********************************************************
|
||
|
|
ECount += 1
|
||
|
|
CALL *WRAP.PRINT("* The owner of the ":SQLobject:" '":NameOrPath:"' is not a valid SQL user.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
*******************************************************
|
||
|
|
* Check that all users with permission have an entry *
|
||
|
|
* in the PERM_LIST association in the UV_USERS table. *
|
||
|
|
*******************************************************
|
||
|
|
IF Verbose AND Sica<NUMBERS,PERMCOUNT>
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("Checking UV_USERS data for users with permissions on ":SQLobject:" '":NameOrPath:"'.",Width,0,0)
|
||
|
|
END
|
||
|
|
|
||
|
|
Offset = COLUMN+Sica<NUMBERS,COLCOUNT>+Sica<NUMBERS,KEYCOUNT>+Sica<NUMBERS,CHECKCOUNT>+Sica<NUMBERS,ASSCOUNT>
|
||
|
|
TmpMax = Sica<NUMBERS,PERMCOUNT>-1
|
||
|
|
|
||
|
|
**************************************************
|
||
|
|
* Loop through all users with perms in the SICA. *
|
||
|
|
**************************************************
|
||
|
|
FOR I = 0 to TmpMax
|
||
|
|
IF Sica<Offset+I,1> = -1
|
||
|
|
THEN NULL
|
||
|
|
ELSE
|
||
|
|
UID = Sica<Offset+I,1>
|
||
|
|
GOSUB GETUSER
|
||
|
|
|
||
|
|
**********************************
|
||
|
|
* Read user record from UV_USERS *
|
||
|
|
**********************************
|
||
|
|
READ UserRec FROM uvusers,Uname
|
||
|
|
THEN
|
||
|
|
******************************************************
|
||
|
|
* Loop through all tables in PERM_LIST association *
|
||
|
|
* If the table is there and the schema matches, then *
|
||
|
|
* break out of the loop, otherwise continue. *
|
||
|
|
******************************************************
|
||
|
|
TmpMax2 = DCOUNT(UserRec<7>,@VM)
|
||
|
|
FOR tmp = 1 to TmpMax2
|
||
|
|
IF UserRec<7,tmp> = TableName
|
||
|
|
THEN
|
||
|
|
IF UserRec<6,tmp> = Schema
|
||
|
|
THEN GOTO UserLoop2
|
||
|
|
END
|
||
|
|
NEXT tmp
|
||
|
|
|
||
|
|
UserLoop2:
|
||
|
|
*************************************************
|
||
|
|
* If we made it through the entire list with no *
|
||
|
|
* matches then it is not in the TABLE_LIST. *
|
||
|
|
*************************************************
|
||
|
|
IF tmp > TmpMax2
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* Adding Catalog data for ":SQLobject:" '":NameOrPath:"' to the UV_USERS record for user '":Uname:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
INS TableName BEFORE UserRec<7,-1>
|
||
|
|
INS Schema BEFORE UserRec<6,-1>
|
||
|
|
WRITE UserRec ON uvusers,Uname
|
||
|
|
END
|
||
|
|
ELSE IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* No SQL catalog data for ":SQLobject:" '":NameOrPath:"' in the UV_USERS record for user '":Uname:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
**********************************************************
|
||
|
|
* If there is no USER info for the user, he is not valid *
|
||
|
|
**********************************************************
|
||
|
|
ECount += 1
|
||
|
|
CALL *WRAP.PRINT("* The user '":Uname:"' has permissions on ":SQLobject:" '":NameOrPath:"' but is not a valid SQL user.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
******************************************************************
|
||
|
|
* Check the UV_TABLES data for any defined Views. *
|
||
|
|
******************************************************************
|
||
|
|
IF catrec<5> # ""
|
||
|
|
THEN
|
||
|
|
FOR I = 1 to DCOUNT(catrec<5>,@VM)
|
||
|
|
View = catrec<5,I>[INDEX(catrec<5,I>,".",1)+1,LEN(catrec<5,I>)]
|
||
|
|
ViewSchema = catrec<5,I>[1,INDEX(catrec<5,I>,".",1)-1]
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT(" ",Width,5,0)
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Verifying view '":catrec<5,I>:"'.",Width,0,0)
|
||
|
|
CALL *VERIFY.TABLE(View,ViewSchema,Fix,Brief,1)
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("Verifying view '":catrec<5,I>:"' completed.",Width,0,0)
|
||
|
|
NEXT I
|
||
|
|
END
|
||
|
|
|
||
|
|
****************************************
|
||
|
|
* Finished. Return to the main program *
|
||
|
|
****************************************
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
|
||
|
|
*******************************************************************************
|
||
|
|
* *
|
||
|
|
* Given a file name, run UVfile on it and return one of the following values: *
|
||
|
|
* 0 The file doesn't exists, or is not a uniVerse file. *
|
||
|
|
* 1 This is a normal uniVerse file. *
|
||
|
|
* 2 This is a uniVerse SQL file. *
|
||
|
|
* 3 We do not have the correct permissions to access this file. *
|
||
|
|
* *
|
||
|
|
* Note: The table filename needs quoting before passing to the EXECUTE as it *
|
||
|
|
* may contain quotes itself. We use system(1031) to do the quoting. On *
|
||
|
|
* UNIX the table name may contain either single or double quotes hence *
|
||
|
|
* the EXECUTE string must be quoted using backslashes. On NT backslashes*
|
||
|
|
* canot be used for the EXECUTE quoting as these get confused with *
|
||
|
|
* pathname seperators, but luckily double quotes are not allowed in *
|
||
|
|
* tablenames on NT so system(1031) will always use these to quote the *
|
||
|
|
* filename... but even more luckily we do not need to quote the actual *
|
||
|
|
* command because if we did the double quotes around the filename would *
|
||
|
|
* get munged by few_process() !!!! *
|
||
|
|
*******************************************************************************
|
||
|
|
EXISTS:
|
||
|
|
newfull = SYSTEM(1031, CHKNAME)
|
||
|
|
|
||
|
|
$IFDEF UV.MSWIN
|
||
|
|
EXCMD = OS.EXEC:" ":UV.ROOT:"/bin/UVfile ":newfull
|
||
|
|
$ELSE
|
||
|
|
EXCMD = OS.EXEC:' \':UV.ROOT:"/bin/UVfile ":newfull:'\'
|
||
|
|
$ENDIF
|
||
|
|
|
||
|
|
EXECUTE EXCMD CAPTURING junk
|
||
|
|
EXVAL = 1
|
||
|
|
IF junk[1,8] = "[EACCES]" THEN
|
||
|
|
CALL *WRAP.PRINT("* Permission denied on table '":CHKNAME:"'.",Width,0,2)
|
||
|
|
EXVAL = 3
|
||
|
|
END
|
||
|
|
IF junk[1,18] = "Permission Denied." THEN
|
||
|
|
CALL *WRAP.PRINT("* Permission denied on table '":CHKNAME:"'.",Width,0,2)
|
||
|
|
EXVAL = 3
|
||
|
|
END
|
||
|
|
IF junk<1> = CHKNAME:": No such file or directory" THEN EXVAL = 0
|
||
|
|
IF junk<1> = CHKNAME:": Not a uniVerse file." THEN EXVAL = 0
|
||
|
|
IF junk<1>[LEN(CHKNAME)+1,28] = ": Dynamic file (uniVerse SQL" THEN EXVAL = 2
|
||
|
|
IF junk<1>[LEN(CHKNAME)+1,27] = ": Hashed file (uniVerse SQL" THEN EXVAL = 2
|
||
|
|
IF IsaView THEN
|
||
|
|
IF junk<1>[LEN(CHKNAME)+9,20] = ": View (UV type 41)."
|
||
|
|
THEN EXVAL = 2
|
||
|
|
END
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
|
||
|
|
***************************************************************************
|
||
|
|
* This routine checks to see if there are multiple F pointers for a file. *
|
||
|
|
* We return: *
|
||
|
|
* 0 If there is only one F pointer to a file. *
|
||
|
|
* 1 If there are more than one F pointers for a file. *
|
||
|
|
***************************************************************************
|
||
|
|
DUPFPOINTER:
|
||
|
|
DFVAL = 0
|
||
|
|
EXECUTE "COUNT VOC WITH TYPE = 'F' AND F2 = '":CHKNAME:"'" CAPTURING junk
|
||
|
|
junk = junk<2>
|
||
|
|
junk = CONVERT(' ',@FM,junk)
|
||
|
|
IF junk<1> # 1 THEN DFVAL = 1
|
||
|
|
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. *
|
||
|
|
***************************************************************************
|
||
|
|
GETSICA:
|
||
|
|
*******************************
|
||
|
|
* Get the SICA for the table. *
|
||
|
|
*******************************
|
||
|
|
quotedname = SYSTEM(1031, CHKNAME)
|
||
|
|
SicaCMD = "LIST.SICA ":quotedname:" DATA"
|
||
|
|
EXECUTE SicaCMD CAPTURING Sica
|
||
|
|
|
||
|
|
********************************************************
|
||
|
|
* If there is a problem getting the SICA then Sica = 0 *
|
||
|
|
********************************************************
|
||
|
|
IF ((Sica[1,9] = "Unable to") OR (Sica[1,9] = "Could not") OR (Sica[20,9] = "Could not") OR (Sica[20,9] = "Unable to"))
|
||
|
|
THEN
|
||
|
|
Sica = 0
|
||
|
|
END
|
||
|
|
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
|
||
|
|
|
||
|
|
CHKCOPY:
|
||
|
|
|
||
|
|
IF HaveData THEN RETURN
|
||
|
|
|
||
|
|
******************************************************************
|
||
|
|
* If the schema name and table name have both been changed then *
|
||
|
|
* try to read a catalog entry for the old table and schema name. *
|
||
|
|
* If there is catalog data under the old name then check to see *
|
||
|
|
* if a file exists where the catalog data says. If a file does *
|
||
|
|
* exist, then we have a copy, else we have a moved and renamed *
|
||
|
|
* table. *
|
||
|
|
******************************************************************
|
||
|
|
IF schemachange AND namechange
|
||
|
|
THEN
|
||
|
|
tkey = Sica<NAME,SCHEMANAME>:@TM:Sica<NAME,TABLENAME>
|
||
|
|
READ record FROM uvtables,tkey
|
||
|
|
THEN
|
||
|
|
CHKNAME = record<6>
|
||
|
|
GOSUB EXISTS
|
||
|
|
IF EXVAL > 1
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* ":SQLobject:" has been copied.",Width,0,2)
|
||
|
|
copydata = 1
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* ":SQLobject:" has been both moved and renamed.",Width,0,2)
|
||
|
|
movedata = 1
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
nodata = 1
|
||
|
|
IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* SQL catalog data does not exist for this ":SQLobject:", even under another name.",Width,0,2)
|
||
|
|
END
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
*******************************************************************
|
||
|
|
* If only the schema name has changed then try to read a catalog *
|
||
|
|
* entry for the table in the old schema. If there is catalog data *
|
||
|
|
* under the old name, then check to see if a file exists where *
|
||
|
|
* the catalog data says. If a file does exist, then we have copy, *
|
||
|
|
* else we have a moved table. *
|
||
|
|
*******************************************************************
|
||
|
|
ELSE IF schemachange
|
||
|
|
THEN
|
||
|
|
tkey = Sica<NAME,SCHEMANAME>:@TM:TableName
|
||
|
|
READ record FROM uvtables,tkey
|
||
|
|
THEN
|
||
|
|
CHKNAME = record<6>
|
||
|
|
GOSUB EXISTS
|
||
|
|
IF EXVAL > 1
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* ":SQLobject:" has been copied.",Width,0,2)
|
||
|
|
copydata = 1
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* ":SQLobject:" has been moved.",Width,0,2)
|
||
|
|
movedata = 1
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
nodata = 1
|
||
|
|
IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* SQL catalog data does not exist for this ":SQLobject:", even under another name.",Width,0,2)
|
||
|
|
END
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
END
|
||
|
|
*******************************************************************
|
||
|
|
* If only the table name has changed then try to read a catalog *
|
||
|
|
* entry for the old table in the schema. If there is catalog data *
|
||
|
|
* under the old name, then check to see if a file exists where *
|
||
|
|
* the catalog data says. If a file does exist, then we have copy, *
|
||
|
|
* else we have a renamed table. *
|
||
|
|
*******************************************************************
|
||
|
|
ELSE IF namechange
|
||
|
|
THEN
|
||
|
|
tkey = Schema:@TM:Sica<NAME,TABLENAME>
|
||
|
|
READ record FROM uvtables,tkey
|
||
|
|
THEN
|
||
|
|
CHKNAME = record<6>
|
||
|
|
GOSUB EXISTS
|
||
|
|
IF EXVAL > 1
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* ":SQLobject:" has been copied.",Width,0,2)
|
||
|
|
copydata = 1
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* ":SQLobject:" has been renamed.",Width,0,2)
|
||
|
|
movedata = 1
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
nodata = 1
|
||
|
|
IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* SQL catalog data does not exist for this ":SQLobject:", even under another name.",Width,0,2)
|
||
|
|
END
|
||
|
|
RETURN
|
||
|
|
END
|
||
|
|
END
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
*****************************************************************************
|
||
|
|
* When give the data for a columns from a SICA, and the data for the same *
|
||
|
|
* column from the UV_COLUMNS file, compare the values of the two and report *
|
||
|
|
* any discrepencies. (FIX still needs to be implimented) *
|
||
|
|
*****************************************************************************
|
||
|
|
COLCHECK:
|
||
|
|
ColName = Sica<COLUMN+Colnum,COLNAME>
|
||
|
|
CrecChange = 0
|
||
|
|
**********************************
|
||
|
|
* Check the column's association *
|
||
|
|
**********************************
|
||
|
|
IF columnrec<1> # Sica<COLUMN+Colnum,ASSOC>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
columnrec<1> = Sica<COLUMN+Colnum,ASSOC>
|
||
|
|
CrecChange = 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing association name for column '":ColName:"' to '":Sica<COLUMN+Colnum,ASSOC>:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* Incorrect association name for column '":ColName:"'. It should be '":Sica<COLUMN+Colnum,ASSOC>:"' and it is '":columnrec<1>:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
**************************
|
||
|
|
* Check the column's AMC *
|
||
|
|
**************************
|
||
|
|
sicamc = Sica<COLUMN+Colnum,POSITION>:@VM:Sica<COLUMN+Colnum,KEYPOS>
|
||
|
|
IF columnrec<2> # sicamc
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
columnrec<2> = sicamc
|
||
|
|
CrecChange = 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing AMC values for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Incorrect AMC values for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
***************************************************************************
|
||
|
|
* Check the column's association numbering. * columnrec<3>
|
||
|
|
*
|
||
|
|
* Unfortunaltely we can't check this at the current release of uniVerse.
|
||
|
|
***************************************************************************
|
||
|
|
|
||
|
|
***************************************
|
||
|
|
* Check if the column is MultiValued. *
|
||
|
|
***************************************
|
||
|
|
IF columnrec<4> = 'S' THEN tempval = 0 ELSE tempval = 1
|
||
|
|
IF tempval # Sica<COLUMN+Colnum,MULTIVALUED>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing 'Multivalued' value for column '":ColName:"'.",Width,0,2)
|
||
|
|
CrecChange = 1
|
||
|
|
IF Sica<COLUMN+Colnum,MULTIVALUED> THEN columnrec<4> = "M" ELSE columnrec<4> = "S"
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Incorrect 'Multivalued' value for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
*********************************
|
||
|
|
* Check the column's data type. *
|
||
|
|
*********************************
|
||
|
|
IF columnrec<5> # DATATYPES<Sica<COLUMN+Colnum,DATATYPE>+1>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Data Type for column '":ColName:"'.",Width,0,2)
|
||
|
|
CrecChange = 1
|
||
|
|
columnrec<5> = DATATYPES<Sica<COLUMN+Colnum,DATATYPE>+1>
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Incorrect Data Type for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
***********************************
|
||
|
|
* Check the column's data length. *
|
||
|
|
***********************************
|
||
|
|
IF columnrec<6> = "" THEN tmpval = 0 ELSE tmpval = columnrec<6>
|
||
|
|
IF (tmpval # Sica<COLUMN+Colnum,DATALEN>) AND (Sica<COLUMN+Colnum,DATATYPE> = 2 OR Sica<COLUMN+Colnum,DATATYPE> = 6)
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Data Length for column '":ColName:"'.",Width,0,2)
|
||
|
|
CrecChange = 1
|
||
|
|
columnrec<6> = Sica<COLUMN+Colnum,DATALEN>
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Incorrect Data Length for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
*******************************************
|
||
|
|
* Check the column's data length. (float) *
|
||
|
|
*******************************************
|
||
|
|
IF columnrec<7> = "" THEN tmpval = 0 ELSE tmpval = columnrec<7>
|
||
|
|
IF (tmpval # Sica<COLUMN+Colnum,DATALEN>) AND (Sica<COLUMN+Colnum,DATATYPE> = 3) AND (Sica<COLUMN+Colnum,DATALEN> # 0)
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Data Precision for column '":ColName:"'.",Width,0,2)
|
||
|
|
CrecChange = 1
|
||
|
|
columnrec<7> = Sica<COLUMN+Colnum,DATALEN>
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Incorrect Data Precision for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
*****************************************************
|
||
|
|
* Check the column's data scale. (decimal,numeric) *
|
||
|
|
*****************************************************
|
||
|
|
IF columnrec<9> = "" THEN tmpval = 0 ELSE tmpval = columnrec<9>
|
||
|
|
IF (tmpval # Sica<COLUMN+Colnum,DATALEN>) AND (Sica<COLUMN+Colnum,DATATYPE> = 7 OR Sica<COLUMN+Colnum,DATATYPE> = 8 ) AND Sica<COLUMN+Colnum,DATALEN> # 0
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Data Scale for column '":ColName:"'.",Width,0,2)
|
||
|
|
CrecChange = 1
|
||
|
|
columnrec<9> = Sica<COLUMN+Colnum,DATALEN>
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Incorrect Data Scale for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
************************************
|
||
|
|
* Check if the columns is nullable *
|
||
|
|
************************************
|
||
|
|
IF (columnrec<10> = "YES") THEN tempval = 0 ELSE tempval = 1
|
||
|
|
IF tempval # Sica<COLUMN+Colnum,NOTNULL>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing NOT NULL flag for column '":ColName:"'.",Width,0,2)
|
||
|
|
CrecChange = 1
|
||
|
|
IF Sica<COLUMN+Colnum,NOTNULL> THEN columnrec<10> = "NO" ELSE columnrec<10> = "YES"
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Incorrect NOT NULL flag for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
************************************
|
||
|
|
* Check the column's default data. *
|
||
|
|
************************************
|
||
|
|
IF (Sica<COLUMN+Colnum,DEFTYPE> # 3) AND (columnrec<11> # "")
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Default Data for column '":ColName:"'.",Width,0,2)
|
||
|
|
CrecChange = 1
|
||
|
|
columnrec<11> = ""
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Incorrect Default Data for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
************************************
|
||
|
|
* Check the column's default data. *
|
||
|
|
************************************
|
||
|
|
IF (Sica<COLUMN+Colnum,DEFTYPE> = 3) AND (columnrec<11> # Sica<COLUMN+Colnum,DEFAULT>)
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Default Data for column '":ColName:"'.",Width,0,2)
|
||
|
|
CrecChange = 1
|
||
|
|
columnrec<11> = Sica<COLUMN+Colnum,DEFAULT>
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Incorrect Default Data for column '":ColName:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF Fix AND CrecChange = 1
|
||
|
|
THEN
|
||
|
|
WRITE columnrec ON uvcolumns,key:@TM:Sica<COLUMN+Colnum,COLNAME>
|
||
|
|
* 23807 Better output for WRITE errors on UV_COLUMNS
|
||
|
|
ON ERROR
|
||
|
|
CALL *WRAP.PRINT("* WRITE error to UV_COLUMNS, STATUS = ":STATUS(),Width,0,2)
|
||
|
|
icheckret = ICHECK(columnrec, uvcolumns,key:@TM:Sica<COLUMN+Colnum,COLNAME>)
|
||
|
|
IF icheckret<1> = 8 AND icheckret<2> = 5
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* UV_COLUMNS DATA_TYPE CHECK constraint is out of date.",Width,0,2)
|
||
|
|
CALL *WRAP.PRINT("* Execute UPDATE.UV_COLUMNS in the sql/catalog account before retrying.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
CALL *WRAP.PRINT("* ICHECK result = ":icheckret,Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
ASSOCCHECK:
|
||
|
|
|
||
|
|
**************************************************
|
||
|
|
* If there is an association record in UV_TABLES *
|
||
|
|
**************************************************
|
||
|
|
IF Atrec # -1
|
||
|
|
THEN
|
||
|
|
|
||
|
|
******************************************************************
|
||
|
|
* IF a keyless VIEW, DELETE UV_TABLES entry for ASSOCIATION *
|
||
|
|
******************************************************************
|
||
|
|
IF Sica<NUMBERS,KEYCOUNT> = 0
|
||
|
|
THEN
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Table data for association '":Alst<Acount>:"' is being deleted from UV_TABLES.",Width,0,2)
|
||
|
|
DELETE uvtables,key:"_":Alst<Acount>
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Table data for association '":Alst<Acount>:"' needs to be deleted from UV_TABLES.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
|
||
|
|
ArecChange = 0
|
||
|
|
|
||
|
|
*********************************************
|
||
|
|
* Verify that the association owner matches *
|
||
|
|
* the table's owner. *
|
||
|
|
*********************************************
|
||
|
|
IF Atrec<1> # catrec<1>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing the owner of the association '":Alst<Acount>:"' to match the ":SQLobject:"'s owner.",Width,0,2)
|
||
|
|
Atrec<1> = catrec<1>
|
||
|
|
ArecChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* The owner of the association '":Alst<Acount>:"' does not agree with the ":SQLobject:"'s owner.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
*****************************************
|
||
|
|
* Verify that the association record is *
|
||
|
|
* marked as an association. *
|
||
|
|
*****************************************
|
||
|
|
IF Atrec<2> # "ASSOCIATION"
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing the TABLE_TYPE of the association '":Alst<Acount>:"' to 'ASSOCIATION'.",Width,0,2)
|
||
|
|
Atrec<2> = "ASSOCIATION"
|
||
|
|
ArecChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* The TABLE_TYPE of the association '":Alst<Acount>:"' is incorrect.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
*******************************************
|
||
|
|
* Verify that the BASE_TABLE field points *
|
||
|
|
* to the correct table *
|
||
|
|
*******************************************
|
||
|
|
IF Atrec<3> # TableName
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing the BASE_TABLE for the association '":Alst<Acount>:"' to be '":TableName:"'.",Width,0,2)
|
||
|
|
Atrec<3> = TableName
|
||
|
|
ArecChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* The BASE_TABLE for the association '":Alst<Acount>:"' should be '":TableName:"' and is actually '":Atrec<3>:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
AsocCols = Anlst<Acount>
|
||
|
|
AcCols = Atrec<4>
|
||
|
|
|
||
|
|
******************************************************************
|
||
|
|
* Run through the list of columns marked in the SICA as being in *
|
||
|
|
* the association, and verify that they are in the UV_TABLES *
|
||
|
|
* entry for the association. If they are, then delete them from *
|
||
|
|
* the list. If they aren't, the print a message. *
|
||
|
|
******************************************************************
|
||
|
|
|
||
|
|
******************************************************************
|
||
|
|
* First pass, association columns *
|
||
|
|
******************************************************************
|
||
|
|
TmpAcols = DCOUNT(AsocCols,@VM)
|
||
|
|
FOR I = 1 TO TmpAcols
|
||
|
|
Tname = AsocCols<1,I>
|
||
|
|
LOCATE(Tname,AcCols,1;tmp)
|
||
|
|
THEN
|
||
|
|
DEL AcCols<1,tmp>
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
If Verbose THEN CALL *WRAP.PRINT("* Adding column '":Tname:"' to the association '":Alst<Acount>:"'.",Width,0,2)
|
||
|
|
INS Tname BEFORE Atrec<4,-1>
|
||
|
|
ArecChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* SICA data for column '":Tname:"' specifies that it should be in the association '":Alst<Acount>:"' but it is not in the Catalog data for that association.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
****************************************************************
|
||
|
|
* Second, use base table Primary Keys *
|
||
|
|
****************************************************************
|
||
|
|
|
||
|
|
TmpPKcols = DCOUNT(PKlst,@VM)
|
||
|
|
FOR I = 1 TO TmpPKcols
|
||
|
|
Tname = PKlst<1,I>
|
||
|
|
LOCATE(Tname,AcCols,1;tmp)
|
||
|
|
THEN
|
||
|
|
DEL AcCols<1,tmp>
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
If Verbose THEN CALL *WRAP.PRINT("* Adding column '":Tname:"' to the association '":Alst<Acount>:"'.",Width,0,2)
|
||
|
|
INS Tname BEFORE Atrec<4,-1>
|
||
|
|
ArecChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* SICA data for column '":Tname:"' specifies that it should be in the association '":Alst<Acount>:"' but it is not in the Catalog data for that association.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
****************************************************************
|
||
|
|
* Run through the list of all columns marked in the UV_TABLES *
|
||
|
|
* entry for the association as being in the association, and *
|
||
|
|
* print a message. The columns in this list are ones that were *
|
||
|
|
* left after the previous loop was completed. We know that *
|
||
|
|
* thier presence here is a problem. *
|
||
|
|
****************************************************************
|
||
|
|
|
||
|
|
TmpAcols = DCOUNT(AcCols,@VM)
|
||
|
|
FOR I = 1 TO TmpAcols
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
LOCATE(AcCols<1,I>, Atrec, 4;tmp)
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting column '":AcCols<1,I>:"' from the association '":Alst<Acount>:"'.",Width,0,2)
|
||
|
|
DEL Atrec<4,tmp>
|
||
|
|
ArecChange = 1
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE IF Verbose
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* The column '":AcCols<1,I>:"' is found in the Catalog data for the association '":Alst<Acount>:"', however the SICA data for the column does not reflect that.",Width,0,2)
|
||
|
|
END
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
******************************************************
|
||
|
|
* Verify that the path for the BASE TABLE is correct *
|
||
|
|
******************************************************
|
||
|
|
IF NOT(IS.EQPATHS(Atrec<6>, catrec<6>))
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing the path for the association '":Alst<Acount>:"' to be the same as the path of the base ":SQLobject:" '":NameOrPath:"'.",Width,0,2)
|
||
|
|
Atrec<6> = catrec<6>
|
||
|
|
ArecChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* The path for the association '":Alst<Acount>:"' is not the same as the path of the base ":SQLobject:" '":NameOrPath:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
*************************************************************
|
||
|
|
* Verify that the path for the BASE TABLE's DICT is correct *
|
||
|
|
*************************************************************
|
||
|
|
IF Atrec<7> # catrec<7>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing the dictionary path for the association '":Alst<Acount>:"' to be the same as the dictionary path of the base table '":NameOrPath:"'.",Width,0,2)
|
||
|
|
Atrec<7> = catrec<7>
|
||
|
|
ArecChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* The dictionary path for the association '":Alst<Acount>:"' is not the same as the dictionary path of the base table '":NameOrPath:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF ArecChange AND Fix
|
||
|
|
THEN
|
||
|
|
WRITE Atrec ON uvtables,key:"_":Alst<Acount>
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE IF Fix
|
||
|
|
THEN
|
||
|
|
****************************************************************
|
||
|
|
* There is not association data in UV_TABLES and *
|
||
|
|
* Fix has been specified *
|
||
|
|
*****************************************************************
|
||
|
|
GOSUB ATABUILD
|
||
|
|
END
|
||
|
|
ELSE IF Verbose AND Sica<NUMBERS,KEYCOUNT> # 0 THEN CALL *WRAP.PRINT("* No data for association '":Alst<Acount>:"' in UV_TABLES.",Width,0,2)
|
||
|
|
|
||
|
|
*************************************************
|
||
|
|
* If there is an association record in UV_ASSOC *
|
||
|
|
*************************************************
|
||
|
|
IF Arec # -1
|
||
|
|
THEN
|
||
|
|
|
||
|
|
AssChange = 0
|
||
|
|
|
||
|
|
FoundAss = -1
|
||
|
|
************************************
|
||
|
|
* find the association in the sica *
|
||
|
|
************************************
|
||
|
|
AOffset = COLUMN+Sica<NUMBERS,COLCOUNT>+Sica<NUMBERS,KEYCOUNT>+Sica<NUMBERS,CHECKCOUNT>
|
||
|
|
ATmpMax = Sica<NUMBERS,ASSCOUNT>-1
|
||
|
|
|
||
|
|
FOR I = 0 to ATmpMax
|
||
|
|
IF Sica<AOffset+I,1> = Alst<Acount> THEN FoundAss = I
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
IF FoundAss # -1
|
||
|
|
THEN
|
||
|
|
************************************************
|
||
|
|
* Verify that the association ordering matches *
|
||
|
|
* the sicas association ordering. *
|
||
|
|
************************************************
|
||
|
|
IF Arec<1> # Sica<AOffset+FoundAss,2>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing ORDERING in UV_ASSOC to match the ":SQLobject:"'s SICA.",Width,0,2)
|
||
|
|
Arec<1> = Sica<AOffset+FoundAss,2>
|
||
|
|
AssChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* ORDERING in UV_ASSOC does not agree with ":SQLobject:"'s SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
****************************************************
|
||
|
|
* Verify that the association order column matches *
|
||
|
|
* the sicas association order column. *
|
||
|
|
****************************************************
|
||
|
|
IF Arec<2> # Sica<AOffset+FoundAss,3>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing ORDER_COLUMN in UV_ASSOC to match the ":SQLobject:"'s SICA.",Width,0,2)
|
||
|
|
Arec<2> = Sica<AOffset+FoundAss,3>
|
||
|
|
AssChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* ORDER_COLUMN in UV_ASSOC does not agree with ":SQLobject:"'s SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
****************************************************
|
||
|
|
* Verify that the association order type matches *
|
||
|
|
* the sicas association order type. *
|
||
|
|
****************************************************
|
||
|
|
IF Arec<3> # Sica<AOffset+FoundAss,4>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing ORDER_TYPE in UV_ASSOC to match the ":SQLobject:"'s SICA.",Width,0,2)
|
||
|
|
Arec<3> = Sica<AOffset+FoundAss,4>
|
||
|
|
AssChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* ORDER_TYPE in UV_ASSOC does not agree with ":SQLobject:"'s SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
****************************************************
|
||
|
|
* Verify that the association empty row matches *
|
||
|
|
* the sicas association empty row. *
|
||
|
|
****************************************************
|
||
|
|
IF Arec<4> # Sica<AOffset+FoundAss,5>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing EMPTY_ROW in UV_ASSOC to match the ":SQLobject:"'s SICA.",Width,0,2)
|
||
|
|
Arec<4> = Sica<AOffset+FoundAss,5>
|
||
|
|
AssChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* EMPTY_ROW in UV_ASSOC does not agree with ":SQLobject:"'s SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
|
||
|
|
IF AssChange AND Fix
|
||
|
|
THEN
|
||
|
|
WRITE Arec ON uvassoc,key:"_":Alst<Acount>
|
||
|
|
* 23807; UV_ASSOC ORDERING constraint may be wrong
|
||
|
|
ON ERROR
|
||
|
|
CALL *WRAP.PRINT("* WRITE error to UV_ASSOC, STATUS = ": STATUS(),Width,0,2)
|
||
|
|
icheckret = ICHECK(Arec, uvassoc,key:"_":Alst<Acount>)
|
||
|
|
IF icheckret<1> = 8 AND icheckret<2> = 1
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* UV_ASSOC ORDERING CHECK constraint is out of date.",Width,0,2)
|
||
|
|
CALL *WRAP.PRINT("* Execute UPDATE.UV_ASSOC in the sql/catalog account before retrying.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
CALL *WRAP.PRINT("* ICHECK result = ":icheckret,Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE IF Fix
|
||
|
|
THEN
|
||
|
|
************************************************
|
||
|
|
* There is not association data in UV_ASSOC *
|
||
|
|
* and Fix has been specified *
|
||
|
|
************************************************
|
||
|
|
GOSUB ATBUILD
|
||
|
|
END
|
||
|
|
ELSE IF Verbose AND Sica<NUMBERS,KEYCOUNT> # 0 THEN CALL *WRAP.PRINT("* No SQL catalog data for association '":Alst<Acount>:"' in UV_ASSOC.",Width,0,2)
|
||
|
|
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
VIEWCHECK:
|
||
|
|
|
||
|
|
VWOffset = COLUMN+Sica<NUMBERS,COLCOUNT>+Sica<NUMBERS,KEYCOUNT>+Sica<NUMBERS,CHECKCOUNT>+Sica<NUMBERS,PERMCOUNT>+Sica<NUMBERS,ASSCOUNT>
|
||
|
|
|
||
|
|
*************************************************
|
||
|
|
* If there is a view record in UV_VIEWS *
|
||
|
|
*************************************************
|
||
|
|
IF VWrec # -1
|
||
|
|
THEN
|
||
|
|
|
||
|
|
VWChange = 0
|
||
|
|
|
||
|
|
************************************************
|
||
|
|
* Verify that the view VIEW_TEXT matches *
|
||
|
|
* the sicas query specification. *
|
||
|
|
************************************************
|
||
|
|
IF VWrec<1> # Sica<VWOffset,1>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing VIEW_TEXT in UV_VIEWS to match the view's SICA.",Width,0,2)
|
||
|
|
VWrec<1> = Sica<VWOffset,1>
|
||
|
|
VWChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* VIEW_TEXT in UV_VIEWS does not agree with view's SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
************************************************
|
||
|
|
* Verify that the view TABLES column matches *
|
||
|
|
* the sicas Reference Tables. *
|
||
|
|
************************************************
|
||
|
|
** Rel 9 Sica uses @TM between schema and tablename **
|
||
|
|
** @VM between entries, sent as @SVM **
|
||
|
|
** UV_VIEWS still uses period JBG 17337 **
|
||
|
|
uvviewtable = CONVERT(@TM, ".", Sica<VWOffset,2>)
|
||
|
|
uvviewtable = CONVERT(@SVM,@VM, uvviewtable)
|
||
|
|
IF VWrec<2> # uvviewtable
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Tables in UV_VIEWS to match the view's SICA.",Width,0,2)
|
||
|
|
VWrec<2> = uvviewtable
|
||
|
|
VWChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Tables in UV_VIEWS does not agree with view's SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF VWChange AND Fix
|
||
|
|
THEN
|
||
|
|
WRITE VWrec ON uvview,key
|
||
|
|
END
|
||
|
|
|
||
|
|
************************************************
|
||
|
|
* Verify that the view Column Map column *
|
||
|
|
* matches the sicas Reference Tables. *
|
||
|
|
************************************************
|
||
|
|
IF VWrec<3> # Sica<VWOffset,3>
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Column Map in UV_VIEWS to match the view's SICA.",Width,0,2)
|
||
|
|
VWrec<3> = Sica<VWOffset,3>
|
||
|
|
VWChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Column Map in UV_VIEWS does not agree with view's SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF VWChange AND Fix
|
||
|
|
THEN
|
||
|
|
WRITE VWrec ON uvview,key
|
||
|
|
END
|
||
|
|
|
||
|
|
************************************************
|
||
|
|
* Verify that the view Updatable column *
|
||
|
|
* matches the sicas Reference Tables. *
|
||
|
|
************************************************
|
||
|
|
IF VWrec<4> # Sica<VWOffset,4> AND NOT (VWrec<4> = "no" OR Sica<VWOffset,4> = "")
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Updatable in UV_VIEWS to match the view's SICA.",Width,0,2)
|
||
|
|
VWrec<4> = Sica<VWOffset,4>
|
||
|
|
VWChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Updatable in UV_VIEWS does not agree with view's SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF VWChange AND Fix
|
||
|
|
THEN
|
||
|
|
WRITE VWrec ON uvview,key
|
||
|
|
END
|
||
|
|
|
||
|
|
************************************************
|
||
|
|
* Verify that the view CHECK_OPTION column *
|
||
|
|
* matches the sicas Reference Tables. *
|
||
|
|
************************************************
|
||
|
|
IF VWrec<5> # Sica<VWOffset,5> AND NOT (VWrec<5> = "no" OR Sica<VWOffset,5> = "")
|
||
|
|
THEN
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Changing Check Option in UV_VIEWS to match the view's SICA.",Width,0,2)
|
||
|
|
VWrec<5> = Sica<VWOffset,5>
|
||
|
|
VWChange = 1
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* Check Option in UV_VIEWS does not agree with view's SICA.",Width,0,2)
|
||
|
|
END
|
||
|
|
|
||
|
|
IF VWChange AND Fix
|
||
|
|
THEN
|
||
|
|
WRITE VWrec ON uvview,key
|
||
|
|
END
|
||
|
|
|
||
|
|
**********************************************************
|
||
|
|
* Verify that the view TABLES point to a UV_TABLE entry *
|
||
|
|
* and if so that UV_TABLE entry in VIEWS points back. *
|
||
|
|
**********************************************************
|
||
|
|
vname1 = CONVERT(@TM,".",key)
|
||
|
|
** Rel 9 uses @VM as seperator, not space JBG 17337 **
|
||
|
|
IF COUNT(VWrec<2>, @VM) > 0 OR VWrec<5> # "no"
|
||
|
|
THEN remarks1 = CONVERT(@VM,@FM,VWrec<2>)
|
||
|
|
ELSE remarks1 = CONVERT(" ",@FM,VWrec<2>)
|
||
|
|
FOR I = 1 to DCOUNT(remarks1,@FM)
|
||
|
|
** ONLY convert first period to @TM JBG 17337 **
|
||
|
|
remarks2 = remarks1<I>
|
||
|
|
dotsep = INDEX(remarks2, ".", 1)
|
||
|
|
remarks2[dotsep, 1] = @TM
|
||
|
|
READ tablerec1 FROM uvtables,remarks2
|
||
|
|
THEN
|
||
|
|
LOCATE(vname1,tablerec1,5;tmp)
|
||
|
|
THEN NULL
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
IF Fix
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Adding '":vname1:"' to (column VIEWS) for '":remarks1<I>:"'.",Width,0,2)
|
||
|
|
INS vname1 BEFORE tablerec1<5,-1>
|
||
|
|
WRITE tablerec1 ON uvtables,remarks2
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* '":remarks1<I>:"' (column VIEWS) does not point to view '":vname1:"'.",Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
NEXT I
|
||
|
|
END
|
||
|
|
ELSE IF Fix
|
||
|
|
THEN
|
||
|
|
*************************************************************************
|
||
|
|
* There is not view data in UV_VIEWS and Fix has been specified *
|
||
|
|
*************************************************************************
|
||
|
|
GOSUB VWBUILD
|
||
|
|
vname1 = CONVERT(@TM,".",key)
|
||
|
|
remarks1 = CONVERT(" ",@FM,Sica<VWOffset,2>)
|
||
|
|
FOR I = 1 to DCOUNT(remarks1,@FM)
|
||
|
|
*** ONLY first period converts to @TM JBG 17337 **
|
||
|
|
remarks2 = remarks1<I>
|
||
|
|
dotsep = INDEX(remarks2, ".", 1)
|
||
|
|
remarks2[dotsep, 1] = @TM
|
||
|
|
READ tablerec1 FROM uvtables,remarks2
|
||
|
|
THEN
|
||
|
|
LOCATE(vname1,tablerec1,5;tmp)
|
||
|
|
THEN NULL
|
||
|
|
ELSE
|
||
|
|
ECount += 1
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Adding '":vname1:"' to (column VIEWS) for '":remarks1<I>:"'.",Width,0,2)
|
||
|
|
INS vname1 BEFORE tablerec1<5,-1>
|
||
|
|
WRITE tablerec1 ON uvtables,remarks2
|
||
|
|
END
|
||
|
|
END
|
||
|
|
NEXT I
|
||
|
|
END
|
||
|
|
ELSE IF Verbose THEN CALL *WRAP.PRINT("* No SQL catalog data for view '":NameOrPath:"' in UV_VIEWS.",Width,0,2)
|
||
|
|
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
********************************************************************
|
||
|
|
* This routine is called when a table does not exist, but it still *
|
||
|
|
* has SQL catalog data. An easy way for this to happen if for *
|
||
|
|
* someone to create a table and then delete it with DELETE.FILE. *
|
||
|
|
********************************************************************
|
||
|
|
DELETE.DATA:
|
||
|
|
********************************************
|
||
|
|
* Delete the UV_TABLES data for the table. *
|
||
|
|
********************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting table data for ":SQLobject:" '":NameOrPath:"'.",Width,0,2)
|
||
|
|
DELETE uvtables,key
|
||
|
|
|
||
|
|
**********************************************
|
||
|
|
* Delete the UV_COLUMNS data for this table. *
|
||
|
|
**********************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting column data for ":SQLobject:" '":NameOrPath:"'.",Width,0,2)
|
||
|
|
FOR I = 1 to DCOUNT(catrec<4>, @VM)
|
||
|
|
DELETE uvcolumns,key:@TM:catrec<4,I>
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
**********************************************
|
||
|
|
* Delete the association data for this file. *
|
||
|
|
* This is both UV_TABLES and UV_ASSOC data. *
|
||
|
|
**********************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting association data for ":SQLobject:" '":NameOrPath:"'.",Width,0,2)
|
||
|
|
FOR I = 1 to DCOUNT(catrec<8>, @VM)
|
||
|
|
DELETE uvtables,key:"_":catrec<8,I>
|
||
|
|
DELETE uvassoc,key:"_":catrec<8,I>
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
**********************************************
|
||
|
|
* Delete the view data for this file. *
|
||
|
|
* This is both UV_TABLES column VIEWS *
|
||
|
|
* and UV_VIEWS *
|
||
|
|
* in Rel 8, UV_VIEWS TABLE use " " seperator *
|
||
|
|
* 9, use @VM " *
|
||
|
|
**********************************************
|
||
|
|
IF IsaView
|
||
|
|
THEN
|
||
|
|
READ viewrec FROM uvview,key
|
||
|
|
THEN
|
||
|
|
IF COUNT(viewrec<2>, @VM) > 0 OR viewrec<2> # "no"
|
||
|
|
THEN remarks = CONVERT(@VM,@FM,viewrec<2>)
|
||
|
|
ELSE remarks = CONVERT(" ",@FM,viewrec<2>)
|
||
|
|
vname = CONVERT(@TM,".",key)
|
||
|
|
FOR I = 1 to DCOUNT(remarks,@FM)
|
||
|
|
remarks2 = remarks<I>
|
||
|
|
dotsep = INDEX(remarks2, ".", 1)
|
||
|
|
remarks2[dotsep, 1] = @TM
|
||
|
|
READ tablerec FROM uvtables,remarks2
|
||
|
|
THEN
|
||
|
|
LOCATE(vname,tablerec,5;tmp)
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting table data (column VIEWS) for view '":NameOrPath:"'.",Width,0,2)
|
||
|
|
DEL tablerec<5,tmp>
|
||
|
|
WRITE tablerec ON uvtables,remarks2
|
||
|
|
END
|
||
|
|
END
|
||
|
|
NEXT I
|
||
|
|
END
|
||
|
|
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting view data for view '":NameOrPath:"'.",Width,0,2)
|
||
|
|
DELETE uvview,key
|
||
|
|
END
|
||
|
|
|
||
|
|
******************************************************
|
||
|
|
* Delete all UV_USERS ownership data for this table. *
|
||
|
|
******************************************************
|
||
|
|
EXECUTE "SELECT UV_USERS WITH SCHEMAS LIKE ...":Schema:"..." RTNLIST rlst CAPTURING junk
|
||
|
|
|
||
|
|
SavRec = 0
|
||
|
|
loopa:
|
||
|
|
READNEXT id FROM rlst ELSE GOTO loopb
|
||
|
|
READ delrec FROM uvusers,id ELSE GOTO loopa
|
||
|
|
CNT = 1
|
||
|
|
BOUNDS = DCOUNT(delrec<5>,@VM)
|
||
|
|
LOOP WHILE CNT <= BOUNDS DO
|
||
|
|
IF delrec<5,CNT> = NameOrPath AND delrec<4,CNT> = Schema
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting ownership record for user '":id:"'.",Width,0,2)
|
||
|
|
DEL delrec<5,CNT>
|
||
|
|
DEL delrec<4,CNT>
|
||
|
|
BOUNDS = BOUNDS - 1
|
||
|
|
SavRec = 1
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
CNT = CNT + 1
|
||
|
|
END
|
||
|
|
REPEAT
|
||
|
|
IF SavRec
|
||
|
|
THEN
|
||
|
|
WRITE delrec ON uvusers,id ELSE CALL *WRAP.PRINT("* Problems with the write. Unable to delete ownership record for user '":id:"'.",Width,5,0)
|
||
|
|
SavRec = 0
|
||
|
|
END
|
||
|
|
GOTO loopa
|
||
|
|
loopb:
|
||
|
|
|
||
|
|
*******************************************************
|
||
|
|
* Delete all UV_USERS permission data for this table. *
|
||
|
|
*******************************************************
|
||
|
|
EXECUTE "SELECT UV_USERS WITH PERM_SCHEMAS LIKE ...":Schema:"..." RTNLIST rlst CAPTURING junk
|
||
|
|
|
||
|
|
loopc:
|
||
|
|
READNEXT id FROM rlst ELSE GOTO loopd
|
||
|
|
READ delrec FROM uvusers,id ELSE GOTO loopc
|
||
|
|
BOUNDS = DCOUNT(delrec<7>,@VM)
|
||
|
|
CNT = 1
|
||
|
|
LOOP WHILE CNT <= BOUNDS DO
|
||
|
|
IF delrec<7,CNT> = NameOrPath AND delrec<6,CNT> = Schema
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Deleting permission record for user '":id:"'.",Width,0,2)
|
||
|
|
DEL delrec<7,CNT>
|
||
|
|
DEL delrec<6,CNT>
|
||
|
|
BOUNDS = BOUNDS - 1
|
||
|
|
SavRec = 1
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
CNT = CNT + 1
|
||
|
|
END
|
||
|
|
REPEAT
|
||
|
|
IF SavRec
|
||
|
|
THEN
|
||
|
|
WRITE delrec ON uvusers,id ELSE CALL *WRAP.PRINT("* Problems with the write. Unable to delete permission record for user '":id:"'.",Width,0,2)
|
||
|
|
SavRec = 0
|
||
|
|
END
|
||
|
|
GOTO loopc
|
||
|
|
loopd:
|
||
|
|
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
|
||
|
|
MOVEDATA:
|
||
|
|
key2 = Schema:@TM:TableName
|
||
|
|
Oschema = CONVERT(@TM,@FM,key)
|
||
|
|
Otable = Oschema<2>
|
||
|
|
Oschema = Oschema<1>
|
||
|
|
|
||
|
|
******************************************
|
||
|
|
* Move the UV_TABLES data for the table. *
|
||
|
|
******************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Moving table data from table '":Sica<NAME,TABLENAME>:" (":Sica<NAME,SCHEMANAME>:")' to table '":NameOrPath:" (":Schema:")'.",Width,0,2)
|
||
|
|
READ nrec FROM uvtables,key
|
||
|
|
THEN
|
||
|
|
IF IS.FULLPATH(TablePath)
|
||
|
|
THEN nrec<6> = TablePath
|
||
|
|
ELSE nrec<6> = path:"/":TablePath
|
||
|
|
nrec<7> = path:"/D_":TableName
|
||
|
|
WRITE nrec ON uvtables,key2
|
||
|
|
DELETE uvtables,key
|
||
|
|
END
|
||
|
|
ELSE NULL
|
||
|
|
|
||
|
|
********************************************
|
||
|
|
* Move the UV_COLUMNS data for this table. *
|
||
|
|
********************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Moving column data from table '":Sica<NAME,TABLENAME>:" (":Sica<NAME,SCHEMANAME>:")' to table '":NameOrPath:" (":Schema:")'.",Width,0,2)
|
||
|
|
FOR I = 1 to DCOUNT(catrec<4>, @VM)
|
||
|
|
READ nrec FROM uvcolumns,key:@TM:catrec<4,I>
|
||
|
|
THEN
|
||
|
|
WRITE nrec ON uvcolumns,key2:@TM:catrec<4,I>
|
||
|
|
* 23807; May get UV_COLUMNS constraint error
|
||
|
|
ON ERROR
|
||
|
|
CALL *WRAP.PRINT("* WRITE error to UV_COLUMNS, STATUS = ":STATUS(),Width,0,2)
|
||
|
|
icheckret = ICHECK(nrec, uvcolumns,key2:@TM:catrec<4,I>)
|
||
|
|
IF icheckret<1> = 8 AND icheckret<2> = 5
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* UV_COLUMNS DATA_TYPE CHECK constraint is out of date.",Width,0,2)
|
||
|
|
CALL *WRAP.PRINT("* Execute UPDATE.UV_COLUMNS in the sql/catalog account before retrying.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
CALL *WRAP.PRINT("* ICHECK result = ":icheckret,Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE NULL
|
||
|
|
DELETE uvcolumns,key:@TM:catrec<4,I>
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
*********************************************
|
||
|
|
* Move the association data for this file. *
|
||
|
|
* This is both UV_TABLES and UV_ASSOC data. *
|
||
|
|
*********************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Moving association data from table '":Sica<NAME,TABLENAME>:" (":Sica<NAME,SCHEMANAME>:")' to table '":NameOrPath:" (":Schema:")'.",Width,0,2)
|
||
|
|
FOR I = 1 to DCOUNT(catrec<8>, @VM)
|
||
|
|
READ nrec FROM uvtables,key:"_":catrec<8,I>
|
||
|
|
THEN
|
||
|
|
nrec<3> = TableName
|
||
|
|
IF IS.FULLPATH(TablePath)
|
||
|
|
THEN nrec<6> = TablePath
|
||
|
|
ELSE nrec<6> = path:"/":TablePath
|
||
|
|
nrec<7> = path:"/D_":TableName
|
||
|
|
WRITE nrec ON uvtables,key2:"_":catrec<8,I>
|
||
|
|
DELETE uvtables,key:"_":catrec<8,I>
|
||
|
|
END
|
||
|
|
ELSE NULL
|
||
|
|
|
||
|
|
READ nrec FROM uvassoc,key:"_":catrec<8,I>
|
||
|
|
THEN
|
||
|
|
WRITE nrec ON uvassoc,key2:"_":catrec<8,I>
|
||
|
|
DELETE uvassoc,key:"_":catrec<8,I>
|
||
|
|
END
|
||
|
|
ELSE NULL
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
****************************************************
|
||
|
|
* Move all UV_USERS ownership data for this table. *
|
||
|
|
****************************************************
|
||
|
|
EXECUTE "SELECT UV_USERS WITH SCHEMAS LIKE ...":Oschema:"..." RTNLIST rlst CAPTURING junk
|
||
|
|
|
||
|
|
SavRec = 0
|
||
|
|
loope:
|
||
|
|
READNEXT id FROM rlst ELSE GOTO loopf
|
||
|
|
READ movrec FROM uvusers,id ELSE GOTO loope
|
||
|
|
CNT = 1
|
||
|
|
BOUNDS = DCOUNT(movrec<5>,@VM)
|
||
|
|
LOOP WHILE CNT <= BOUNDS DO
|
||
|
|
IF movrec<5,CNT> = Otable AND movrec<4,CNT> = Oschema
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Moving ownership record for user '":id:"'.",Width,0,2)
|
||
|
|
movrec<5,CNT> = TableName
|
||
|
|
movrec<4,CNT> = Schema
|
||
|
|
SavRec = 1
|
||
|
|
END
|
||
|
|
CNT = CNT + 1
|
||
|
|
REPEAT
|
||
|
|
IF SavRec
|
||
|
|
THEN
|
||
|
|
WRITE movrec ON uvusers,id ELSE CALL *WRAP.PRINT("* Problems with the write. Unable to move ownership record for user '":id:"'.",Width,0,2)
|
||
|
|
SavRec = 0
|
||
|
|
END
|
||
|
|
GOTO loope
|
||
|
|
loopf:
|
||
|
|
|
||
|
|
*****************************************************
|
||
|
|
* Move all UV_USERS permission data for this table. *
|
||
|
|
*****************************************************
|
||
|
|
EXECUTE "SELECT UV_USERS WITH PERM_SCHEMAS LIKE ...":Oschema:"..." RTNLIST rlst CAPTURING junk
|
||
|
|
|
||
|
|
loopg:
|
||
|
|
READNEXT id FROM rlst ELSE GOTO looph
|
||
|
|
READ movrec FROM uvusers,id ELSE GOTO loopg
|
||
|
|
BOUNDS = DCOUNT(movrec<7>,@VM)
|
||
|
|
CNT = 1
|
||
|
|
LOOP WHILE CNT <= BOUNDS DO
|
||
|
|
IF movrec<7,CNT> = Otable AND movrec<6,CNT> = Oschema
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Moving permission record for user '":id:"'.",Width,0,2)
|
||
|
|
movrec<7,CNT> = TableName
|
||
|
|
movrec<6,CNT> = Schema
|
||
|
|
SavRec = 1
|
||
|
|
END
|
||
|
|
CNT = CNT + 1
|
||
|
|
REPEAT
|
||
|
|
IF SavRec
|
||
|
|
THEN
|
||
|
|
WRITE movrec ON uvusers,id ELSE CALL *WRAP.PRINT("* Problems with the write. Unable to move permission record for user '":id:"'.",Width,0,2)
|
||
|
|
SavRec = 0
|
||
|
|
END
|
||
|
|
GOTO loopg
|
||
|
|
looph:
|
||
|
|
|
||
|
|
READ catrec FROM uvtables,key2
|
||
|
|
ELSE
|
||
|
|
CRT "Problem with read."
|
||
|
|
BREAK ON
|
||
|
|
STOP
|
||
|
|
END
|
||
|
|
key = key2
|
||
|
|
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
COPYDATA:
|
||
|
|
key2 = Schema:@TM:TableName
|
||
|
|
Oschema = CONVERT(@TM,@FM,key)
|
||
|
|
Otable = Oschema<2>
|
||
|
|
Oschema = Oschema<1>
|
||
|
|
|
||
|
|
******************************************
|
||
|
|
* Copy the UV_TABLES data for the table. *
|
||
|
|
******************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Copying table data from table '":Sica<NAME,TABLENAME>:" (":Sica<NAME,SCHEMANAME>:")' to table '":NameOrPath:" (":Schema:")'.",Width,0,2)
|
||
|
|
READ nrec FROM uvtables,key
|
||
|
|
THEN
|
||
|
|
IF IS.FULLPATH(TablePath)
|
||
|
|
THEN nrec<6> = TablePath
|
||
|
|
ELSE nrec<6> = path:"/":TablePath
|
||
|
|
nrec<7> = path:"/D_":TableName
|
||
|
|
WRITE nrec ON uvtables,key2
|
||
|
|
END
|
||
|
|
ELSE NULL
|
||
|
|
|
||
|
|
********************************************
|
||
|
|
* Copy the UV_COLUMNS data for this table. *
|
||
|
|
********************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Copying column data from table '":Sica<NAME,TABLENAME>:" (":Sica<NAME,SCHEMANAME>:")' to table '":NameOrPath:" (":Schema:")'.",Width,0,2)
|
||
|
|
FOR I = 1 to DCOUNT(catrec<4>, @VM)
|
||
|
|
READ nrec FROM uvcolumns,key:@TM:catrec<4,I>
|
||
|
|
THEN
|
||
|
|
WRITE nrec ON uvcolumns,key2:@TM:catrec<4,I>
|
||
|
|
* 23807; May get UV_COLUMNS constraint error
|
||
|
|
ON ERROR
|
||
|
|
CALL *WRAP.PRINT("* WRITE error to UV_COLUMNS, STATUS = ":STATUS(),Width,0,2)
|
||
|
|
icheckret = ICHECK(nrec, uvcolumns,key2:@TM:catrec<4,I>)
|
||
|
|
IF icheckret<1> = 8 AND icheckret<2> = 5
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* UV_COLUMNS DATA_TYPE CHECK constraint is out of date.",Width,0,2)
|
||
|
|
CALL *WRAP.PRINT("* Execute UPDATE.UV_COLUMNS in the sql/catalog account before retrying.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
CALL *WRAP.PRINT("* ICHECK result is ":icheckret,Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE NULL
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
*********************************************
|
||
|
|
* Copy the association data for this file. *
|
||
|
|
* This is both UV_TABLES and UV_ASSOC data. *
|
||
|
|
*********************************************
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Copying association data from table '":Sica<NAME,TABLENAME>:" (":Sica<NAME,SCHEMANAME>:")' to table '":NameOrPath:" (":Schema:")'.",Width,0,2)
|
||
|
|
FOR I = 1 to DCOUNT(catrec<8>, @VM)
|
||
|
|
READ nrec FROM uvtables,key:"_":catrec<8,I>
|
||
|
|
THEN
|
||
|
|
nrec<3> = TableName
|
||
|
|
IF IS.FULLPATH(TablePath)
|
||
|
|
THEN nrec<6> = TablePath
|
||
|
|
ELSE nrec<6> = path:"/":TablePath
|
||
|
|
nrec<7> = path:"/D_":TableName
|
||
|
|
WRITE nrec ON uvtables,key2:"_":catrec<8,I>
|
||
|
|
END
|
||
|
|
ELSE NULL
|
||
|
|
|
||
|
|
READ nrec FROM uvassoc,key:"_":catrec<8,I>
|
||
|
|
THEN
|
||
|
|
WRITE nrec ON uvassoc,key2:"_":catrec<8,I>
|
||
|
|
* 23807; UV_ASSOC ORDERING constraint may be wrong
|
||
|
|
ON ERROR
|
||
|
|
CALL *WRAP.PRINT("* WRITE error to UV_ASSOC, STATUS = ":STATUS(),Width,0,2)
|
||
|
|
icheckret = ICHECK(nrec, uvcolumns,key2:"_":catrec<8,I>)
|
||
|
|
IF icheckret<1> = 8 AND icheckret<2> = 1
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* UV_ASSOC ORDERING CHECK constraint is out of date.",Width,0,2)
|
||
|
|
CALL *WRAP.PRINT("* Execute UPDATE.UV_ASSOC in the sql/catalog account before retrying.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
CALL *WRAP.PRINT("* ICHECK result is ":icheckret,Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
END
|
||
|
|
ELSE NULL
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
****************************************************
|
||
|
|
* Move all UV_USERS ownership data for this table. *
|
||
|
|
****************************************************
|
||
|
|
EXECUTE "SELECT UV_USERS WITH SCHEMAS LIKE ...":Oschema:"..." RTNLIST rlst CAPTURING junk
|
||
|
|
|
||
|
|
SavRec = 0
|
||
|
|
loopi:
|
||
|
|
READNEXT id FROM rlst ELSE GOTO loopj
|
||
|
|
READ movrec FROM uvusers,id ELSE GOTO loopi
|
||
|
|
CNT = 1
|
||
|
|
BOUNDS = DCOUNT(movrec<5>,@VM)
|
||
|
|
LOOP WHILE CNT <= BOUNDS DO
|
||
|
|
IF movrec<5,CNT> = Otable AND movrec<4,CNT> = Oschema
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Copying ownership record for user '":id:"'.",Width,0,2)
|
||
|
|
INS TableName BEFORE movrec<5,-1>
|
||
|
|
INS Schema BEFORE movrec<4,-1>
|
||
|
|
SavRec = 1
|
||
|
|
END
|
||
|
|
CNT = CNT + 1
|
||
|
|
REPEAT
|
||
|
|
IF SavRec
|
||
|
|
THEN
|
||
|
|
WRITE movrec ON uvusers,id ELSE CALL *WRAP.PRINT("* Problems with the write. Unable to copy ownership record for user '":id:"'.",Width,0,2)
|
||
|
|
SavRec = 0
|
||
|
|
END
|
||
|
|
GOTO loopi
|
||
|
|
loopj:
|
||
|
|
|
||
|
|
*****************************************************
|
||
|
|
* Move all UV_USERS permission data for this table. *
|
||
|
|
*****************************************************
|
||
|
|
EXECUTE "SELECT UV_USERS WITH PERM_SCHEMAS LIKE ...":Oschema:"..." RTNLIST rlst CAPTURING junk
|
||
|
|
|
||
|
|
loopk:
|
||
|
|
READNEXT id FROM rlst ELSE GOTO loopl
|
||
|
|
READ movrec FROM uvusers,id ELSE GOTO loopk
|
||
|
|
BOUNDS = DCOUNT(movrec<7>,@VM)
|
||
|
|
CNT = 1
|
||
|
|
LOOP WHILE CNT <= BOUNDS DO
|
||
|
|
IF movrec<7,CNT> = Otable AND movrec<6,CNT> = Oschema
|
||
|
|
THEN
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Copying permission record for user '":id:"'.",Width,0,2)
|
||
|
|
INS TableName BEFORE movrec<7,-1>
|
||
|
|
INS Schema BEFORE movrec<6,-1>
|
||
|
|
SavRec = 1
|
||
|
|
END
|
||
|
|
CNT = CNT + 1
|
||
|
|
REPEAT
|
||
|
|
IF SavRec
|
||
|
|
THEN
|
||
|
|
WRITE movrec ON uvusers,id ELSE CALL *WRAP.PRINT("* Problems with the write. Unable to copy permission record for user '":id:"'.",Width,0,2)
|
||
|
|
SavRec = 0
|
||
|
|
END
|
||
|
|
GOTO loopk
|
||
|
|
loopl:
|
||
|
|
|
||
|
|
READ catrec FROM uvtables,key2
|
||
|
|
ELSE
|
||
|
|
CRT "Problem with read."
|
||
|
|
BREAK ON
|
||
|
|
STOP
|
||
|
|
END
|
||
|
|
key = key2
|
||
|
|
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
COLBUILD:
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Building SQL catalog data for column '":Sica<COLUMN+Colnum,COLNAME>:"' in UV_COLUMNS.",Width,0,2)
|
||
|
|
Cnum = COLUMN+Colnum
|
||
|
|
columnrec = ""
|
||
|
|
|
||
|
|
columnrec<1> = Sica<Cnum,ASSOC>
|
||
|
|
|
||
|
|
sicamc = Sica<Cnum,POSITION>:@VM:Sica<Cnum,KEYPOS>
|
||
|
|
columnrec<2> = sicamc
|
||
|
|
|
||
|
|
IF columnrec<1> # ""
|
||
|
|
THEN
|
||
|
|
FOR AsocCnt = 1 to Dcount(Alst,@FM)
|
||
|
|
IF Alst<AsocCnt> = columnrec<1>
|
||
|
|
THEN
|
||
|
|
Keyfound = 0
|
||
|
|
FOR J = 1 to Dcount(Anlst<AsocCnt>,@VM)
|
||
|
|
IF Anullst<AsocCnt,J> = 1 AND NOT(Keyfound)
|
||
|
|
THEN
|
||
|
|
IF Anlst<AsocCnt,J> = Sica<Cnum,COLNAME>
|
||
|
|
THEN
|
||
|
|
columnrec<3,1> = 0
|
||
|
|
columnrec<3,2> = Sica<NUMBERS,KEYCOUNT>+1
|
||
|
|
END
|
||
|
|
ELSE Keyfound = 1
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
IF Anlst<AsocCnt,J> = Sica<Cnum,COLNAME>
|
||
|
|
THEN
|
||
|
|
IF Keyfound = 1
|
||
|
|
THEN columnrec<3,1> = J-1
|
||
|
|
ELSE columnrec<3,1> = J
|
||
|
|
columnrec<3,2> = 0
|
||
|
|
END
|
||
|
|
END
|
||
|
|
NEXT J
|
||
|
|
END
|
||
|
|
NEXT AsocCnt
|
||
|
|
END
|
||
|
|
ELSE IF Sica<Cnum,PRIMARYKEY> THEN columnrec<3> = sicamc
|
||
|
|
ELSE columnrec<3> = 0
|
||
|
|
|
||
|
|
IF Sica<Cnum,MULTIVALUED> THEN columnrec<4> = "M" ELSE columnrec<4> = "S"
|
||
|
|
|
||
|
|
dtype = Sica<Cnum,DATATYPE>
|
||
|
|
columnrec<5> = DATATYPES<dtype+1>
|
||
|
|
|
||
|
|
IF dtype = 2 OR dtype = 6
|
||
|
|
THEN columnrec<6> = Sica<Cnum,DATALEN>
|
||
|
|
ELSE columnrec<6> = 0
|
||
|
|
|
||
|
|
IF dtype = 3
|
||
|
|
THEN
|
||
|
|
IF Sica<Cnum,DATALEN> # ""
|
||
|
|
THEN columnrec<7> = Sica<Cnum,DATALEN>
|
||
|
|
ELSE columnrec<7> = 15
|
||
|
|
END
|
||
|
|
ELSE columnrec<7> = 0
|
||
|
|
|
||
|
|
columnrec<8> = ""
|
||
|
|
|
||
|
|
IF dtype = 7 OR dtype = 8
|
||
|
|
THEN columnrec<9> = Sica<Cnum,DATALEN>
|
||
|
|
ELSE columnrec<9> = 0
|
||
|
|
|
||
|
|
IF Sica<Cnum,NOTNULL> = 0
|
||
|
|
THEN columnrec<10> = "YES"
|
||
|
|
ELSE columnrec<10> = "NO"
|
||
|
|
|
||
|
|
IF Sica<Cnum,DEFTYPE> = 3
|
||
|
|
THEN columnrec<11> = Sica<Cnum,DEFAULT>
|
||
|
|
ELSE columnrec<11> = ""
|
||
|
|
|
||
|
|
WRITE columnrec ON uvcolumns,key:@TM:Sica<Cnum,COLNAME>
|
||
|
|
* 23807; UV_COLUMNS DATA_TYPE constraint may be wrong
|
||
|
|
ON ERROR
|
||
|
|
CALL *WRAP.PRINT("* WRITE error to UV_COLUMNS, STATUS = ":STATUS(),Width,0,2)
|
||
|
|
icheckret = ICHECK(columnrec, uvcolumns,key:@TM:Sica<Cnum,COLNAME>)
|
||
|
|
IF icheckret<1> = 8 AND icheckret<2> = 5
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* UV_COLUMNS DATA_TYPE CHECK constraint is out of date.",Width,0,2)
|
||
|
|
CALL *WRAP.PRINT("* Execute UPDATE.UV_COLUMNS in the sql/catalog account before retrying.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
CALL *WRAP.PRINT("* ICHECK result is ":icheckret,Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
* Build the UV_TABLES data for an Association. *
|
||
|
|
|
||
|
|
ATABUILD:
|
||
|
|
***********************************************************
|
||
|
|
* RETURN IF NO Primary Key(s) *
|
||
|
|
***********************************************************
|
||
|
|
IF Sica<NUMBERS,KEYCOUNT> = 0 THEN RETURN
|
||
|
|
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Building UV_TABLES data for association '":Alst<Acount>:"'.",Width,0,2)
|
||
|
|
************************************
|
||
|
|
* Set the owner of the association *
|
||
|
|
************************************
|
||
|
|
Atrec<1> = catrec<1>
|
||
|
|
|
||
|
|
**************************************
|
||
|
|
* Set the TABLE_TYPE = "ASSOCIATION" *
|
||
|
|
**************************************
|
||
|
|
Atrec<2> = "ASSOCIATION"
|
||
|
|
|
||
|
|
*******************************************
|
||
|
|
* Set the BASE_TABLE to the current table *
|
||
|
|
*******************************************
|
||
|
|
Atrec<3> = TableName
|
||
|
|
|
||
|
|
****************************************15080******
|
||
|
|
* Set the COLUMNS field to all PKs + ASSOC column *
|
||
|
|
***************************************************
|
||
|
|
|
||
|
|
TmpPKcols = DCOUNT(PKlst,@VM)
|
||
|
|
FOR I = 1 TO TmpPKcols
|
||
|
|
Atrec<4,I> = PKlst<I>
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
TmpAcols = DCOUNT(Anlst<Acount>,@VM)
|
||
|
|
FOR I = 1 TO TmpAcols
|
||
|
|
Atrec<4,I + TmpPKcols> = Anlst<Acount,I>
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
**********************
|
||
|
|
* Set the table path *
|
||
|
|
**********************
|
||
|
|
Atrec<6> = catrec<6>
|
||
|
|
|
||
|
|
***************************
|
||
|
|
* Set the dictionary path *
|
||
|
|
***************************
|
||
|
|
Atrec<7> = catrec<7>
|
||
|
|
|
||
|
|
WRITE Atrec ON uvtables,key:"_":Alst<Acount>
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
ATBUILD:
|
||
|
|
IF Sica<NUMBERS,KEYCOUNT> = 0 THEN RETURN
|
||
|
|
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Building UV_ASSOC data for association '":Alst<Acount>:"'.",Width,0,2)
|
||
|
|
Arec = ""
|
||
|
|
FoundAss = -1
|
||
|
|
************************************
|
||
|
|
* find the association in the sica *
|
||
|
|
************************************
|
||
|
|
AOffset = COLUMN+Sica<NUMBERS,COLCOUNT>+Sica<NUMBERS,KEYCOUNT>+Sica<NUMBERS,CHECKCOUNT>
|
||
|
|
ATmpMax = Sica<NUMBERS,ASSCOUNT>-1
|
||
|
|
|
||
|
|
FOR I = 0 to ATmpMax
|
||
|
|
IF Sica<AOffset+I,1> = Alst<Acount> THEN FoundAss = I
|
||
|
|
NEXT I
|
||
|
|
|
||
|
|
IF FoundAss # -1
|
||
|
|
THEN
|
||
|
|
Arec<1> = Sica<AOffset+FoundAss,2>
|
||
|
|
Arec<2> = Sica<AOffset+FoundAss,3>
|
||
|
|
Arec<3> = Sica<AOffset+FoundAss,4>
|
||
|
|
Arec<4> = Sica<AOffset+FoundAss,5>
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
Arec<1> = "UNORDERED"
|
||
|
|
Arec<4> = "NO"
|
||
|
|
END
|
||
|
|
|
||
|
|
WRITE Arec ON uvassoc,key:"_":Alst<Acount>
|
||
|
|
* 23807; UV_ASSOC ORDERING constraint may be wrong
|
||
|
|
ON ERROR
|
||
|
|
CALL *WRAP.PRINT("* WRITE error to UV_ASSOC, STATUS = ":STATUS(),Width,0,2)
|
||
|
|
icheckret = ICHECK(Arec, uvassoc,key:"_":Alst<Acount>)
|
||
|
|
IF icheckret<1> = 8 AND icheckret<2> = 1
|
||
|
|
THEN
|
||
|
|
CALL *WRAP.PRINT("* UV_ASSOC ORDERING CHECK constraint is out of date.",Width,0,2)
|
||
|
|
CALL *WRAP.PRINT("* Execute UPDATE.UV_ASSOC in the sql/catalog account before retrying.",Width,0,2)
|
||
|
|
END
|
||
|
|
ELSE
|
||
|
|
CALL *WRAP.PRINT("* ICHECK result is ":icheckret,Width,0,2)
|
||
|
|
END
|
||
|
|
END
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
VWBUILD:
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Building UV_VIEWS data for view '":NameOrPath:"'.",Width,0,2)
|
||
|
|
VWrec = ""
|
||
|
|
|
||
|
|
VWrec<1> = Sica<VWOffset,1>
|
||
|
|
IF Sica<VWOffset,5> = "no"
|
||
|
|
THEN VWrec<2> = Sica<VWOffset,2>
|
||
|
|
ELSE
|
||
|
|
** Rel 9 has more fields, @TM between schema and tablename JBG 17337 ***
|
||
|
|
** @SVM sent over for table seperator, not @VM **
|
||
|
|
VWrec<2> = CONVERT(@TM, ".", Sica<VWOffset,2>)
|
||
|
|
VWrec<2> = CONVERT(@SVM,@VM, VWrec<2>)
|
||
|
|
VWrec<3> = Sica<VWOffset,3>
|
||
|
|
VWrec<4> = Sica<VWOffset,4>
|
||
|
|
VWrec<5> = Sica<VWOffset,5>
|
||
|
|
END
|
||
|
|
|
||
|
|
WRITE VWrec ON uvview,key
|
||
|
|
RETURN
|
||
|
|
|
||
|
|
BUILDDATA:
|
||
|
|
|
||
|
|
IF Verbose THEN CALL *WRAP.PRINT("* Building table data for ":SQLobject:" '":NameOrPath:"'.",Width,0,2)
|
||
|
|
|
||
|
|
catrec = ""
|
||
|
|
Alst = ""
|
||
|
|
Anlst = ""
|
||
|
|
Anullst = ""
|
||
|
|
MaxCols = (Sica<NUMBERS,COLCOUNT>+Sica<NUMBERS,KEYCOUNT>)-1
|
||
|
|
|
||
|
|
*************************
|
||
|
|
* Build UV_TABLES data. *
|
||
|
|
*************************
|
||
|
|
catrec<1> = Sica<NAME,OWNER>
|
||
|
|
IF IsaView THEN catrec<2> = "VIEW"
|
||
|
|
IF IsaTable THEN catrec<2> = "BASE TABLE"
|
||
|
|
catrec<3> = ""
|
||
|
|
|
||
|
|
FOR Colnum = 0 TO MaxCols
|
||
|
|
INS Sica<COLUMN+Colnum,COLNAME> BEFORE catrec<4,-1>
|
||
|
|
NEXT Colnum
|
||
|
|
|
||
|
|
***************************************
|
||
|
|
* No view infornmation is in the sica *
|
||
|
|
* Its lost at this point catrec<5> *
|
||
|
|
***************************************
|
||
|
|
catrec<6> = path:"/":TableName
|
||
|
|
catrec<7> = path:"/D_":TableName
|
||
|
|
|
||
|
|
WRITE catrec ON uvtables,key
|
||
|
|
|
||
|
|
RETURN
|
||
|
|
END
|