- DMSQF1 ;SFISC/JHM-INITIALIZE SQLI_FILE (CONT) ;5/7/98 14:53
- ;;22.0;VA FileMan;;Mar 30, 1999
- ;Per VHA Directive 10-93-142, this routine should not be modified.
- Q
- PK(TI) ;GENERATE PRIMARY KEY ELEMENTS AND COLUMNS FOR TABLE IDENTIFIER TI
- I '$G(DIFM) D ENV^DMSQU
- N X,N,G,PAR,PEI,TIEN,T,DIERR S T=1.5215,TIEN=TI_","
- K DIERR D GETS^DIQ(T,TIEN,".01;6;8","I","X")
- I $D(DIERR) D ERR^DMSQU(999999999,"","PRIMARY KEY: CAN'T GET TABLE DATA") Q ""
- S N=X(T,TIEN,.01,"I"),G=X(T,TIEN,8,"I"),F=X(T,TIEN,6,"I")
- K FDA S TT=1.5212,DI=$O(^DMSQ("DM","C",TI,"")) ;BUILD DOMAIN
- S IEN=$S(DI:DI,1:"+1")_"," ;TABLE DOMAIN
- S FDA(TT,IEN,.01)=N_"_ID" ;DOMAIN NAME
- S FDA(TT,IEN,1)=1 ;DATA TYPE = PRIMARY KEY
- S FDA(TT,IEN,2)="Domain of table "_N ;COMMENT
- S FDA(TT,IEN,3)=TI ;TABLE ID
- S DI=$$PUT^DMSQU(IEN,"FDA","ERR")
- I 'DI D ERR^DMSQU(F,"","PRIMARY KEY: DOMAIN INSERT FAILED") Q ""
- ; BUILD PRIMARY KEY TABLE ELEMENT
- S PEI=$O(^DMSQ("E","F",TI,"P","")),IEN=$S(PEI:PEI,1:"+1")_",",TT=1.5216
- S FDA(TT,IEN,.01)=N_"_PK" ;TABLE ELEMENT NAME
- S FDA(TT,IEN,1)=DI ;TE DOMAIN SAME AS TABLE
- S FDA(TT,IEN,2)=TI ;TABLE ID
- S FDA(TT,IEN,3)="P" ;TYPE = PRIMARY KEY
- S FDA(TT,IEN,4)="Primary key header for table "_N ;COMMENT
- S PEI=$$PUT^DMSQU(IEN,"FDA","ERR")
- I 'PEI D ERR^DMSQU(F,"","PRIMARY KEY: TABLE ELEMENT INSERT FAILED") Q ""
- ;BUILD TABLE PRIMARY KEYS
- S F=$$GET^DMSQU(1.5215,TI_",",6,"I","","ERR"),KL=$$KL^DMSQU(F),PAR=""
- I $D(ERR)!'F D ERR^DMSQU(F,"","PRIMARY KEY: CAN'T GET TABLE'S FILE #") Q ""
- F I=1:1:$L(G,"{K}")-1 D PKI(PEI,I,$P(KL,",",I),$P(G,"{K}",I),.PAR)
- Q CEI
- PKI(PEI,SEQ,F,GF,PAR) ;BUILD COLUMN ELEMENT, COLUMN AND PRIMARY KEY DEFS
- N TN,KI,KN,PI,CI,DM,W,S,TT,I,DI,ERR
- S TN=$$GET^DMSQU(1.5216,PEI_",",2,"","","ERR")
- I TN="" D ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: CAN'T GET DATA FOR MASTER TABLE") Q ""
- S KI=$O(^DMSQ("T","C",F,""))
- I 'KI D ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: MISSING TABLE RECORD") Q ""
- S KN=$$GET^DMSQU(1.5215,KI_",",.01),(CI,CEI)=""
- S PI=$O(^DMSQ("P","C",PEI,SEQ,""))
- I PI K ERR,DIERR D Q:'CI!'CEI
- . S CI=$$GET^DMSQU(1.5218,PI_",",1,"I","","ERR")
- . I $D(ERR) D ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: TABLE MISSING COLUMN POINTER") Q
- . I CI S CEI=$$GET^DMSQU(1.5217,CI_",",.01,"I","","ERR")
- . I $D(ERR) D ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: CAN'T GET COLUMN'S TABLE ELEMENT") Q
- . I 'CI!('CEI) D ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: MISSING COLUMN POINTER") Q
- ;DEFINE COLUMN ELEMENT
- S IEN=$S(CEI:CEI,1:"+1")_",",TT=1.5216
- S DM=$$DOM^DMSQU(F,.001),W=$P(DM,U,2),S=$P(DM,U,3),DM=$P(DM,U)
- I DM=""!("FM_DATE_TIME;FM_MOMENT;FM_DATE;INTEGER;NUMERIC"'[DM) S DM="INTEGER",W=10
- S DI=$O(^DMSQ("DM","B",DM,""))
- S FDA(TT,IEN,.01)=$$KWC^DMSQU(KN_"_ID") ;COLUMN NAME = TABLE_NAME_ID
- S FDA(TT,IEN,1)=DI ;DOMAIN
- S FDA(TT,IEN,2)=TI ;TABLE ID
- S FDA(TT,IEN,3)="C" ; TYPE = COLUMN
- S FDA(TT,IEN,4)="Primary key #"_SEQ_" of table "_TN ; COMMENT
- S CEI=$$PUT^DMSQU(IEN,"FDA","ERR")
- I $D(ERR) D ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: COLUMN ELEMENT INSERT FAILED") Q
- ;DEFINE COLUMN
- S TT=1.5217,CI=$O(^DMSQ("C","B",CEI,"")),IEN=$S(CI:CI,1:"+1")_","
- S FDA(TT,IEN,.01)=CEI ;COL ELEMENT ID
- S FDA(TT,IEN,2)=W ;WIDTH
- S:S FDA(TT,IEN,3)=S ;SCALE
- S FDA(TT,IEN,5)=1 ;REQUIRED
- S FDA(TT,IEN,7)=0 ;BASE COLUMN
- S FDA(TT,IEN,9)=GF ;GLOBAL FRAGMENT
- S FDA(TT,IEN,8)=PAR ;PARENT
- S CI=$$PUT^DMSQU(IEN,"FDA","ERR")
- I $D(ERR) D ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: COLUMN INSERT FAILED") Q
- S PAR=CI
- ;DEFINE PRIMARY KEY
- S TT=1.5218,PI=$O(^DMSQ("P","C",PEI,SEQ,"")),IEN=$S(PI:PI,1:"+1")_","
- S FDA(TT,IEN,.01)=PEI ; TABLE ELEMENT
- S FDA(TT,IEN,1)=CI ; COLUMN
- S FDA(TT,IEN,2)=SEQ ; SEQUENCE NUMBER
- S FDA(TT,IEN,3)=0 ; ALWAYS START AT 0
- S FDA(TT,IEN,4)="'{K}" ; STOP AT EXPRESSION
- S CI=$$PUT^DMSQU(IEN,"FDA","ERR")
- I $D(ERR) D ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: TABLE ELEMENT INSERT FAILED")
- Q
- FK(CI) ;DEFINE FOREIGN KEY FOR POINTER COLUMN CI
- N C,CEI,CE,CN,F,FI,TI,SP,FF,FIEN,FTI,FTN,FPEI,FPE,FPI,FNM,TT,IEN,FDA
- S C=^DMSQ("C",CI,0)
- S CEI=$P(C,U),CE=^DMSQ("E",CEI,0),CN=$P(CE,U) Q:$P(CE,U,2)'=13 ""
- S F=$P(C,U,5),FI=$P(C,U,6) I 'F!'FI Q ""
- S TI=$P(CE,U,3),SP=$P(^DD(F,FI,0),U,2)
- S FF=+$P(SP,"P",2)
- I 'FF D ERR^DMSQU(F,FI,"FOREIGN KEY: NO POINTED-TO FILE IN SPECIFIER") Q ""
- S FTI=$O(^DMSQ("T","C",FF,""))
- I 'FTI D ERR^DMSQU(F,FI,"FOREIGN KEY: NO TABLE FOR POINTED-TO FILE") Q ""
- S FTN=$P(^DMSQ("T",FTI,0),U)
- S FPEI=$O(^DMSQ("E","F",FTI,"P",""))
- I 'FPEI D ERR^DMSQU(F,FI,"FOREIGN KEY: NO PRIMARY KEY TABLE ELEMENT") Q ""
- S FPE=^DMSQ("E",FPEI,0),FDI=$P(FPE,U,2)
- S FPI=$O(^DMSQ("P","B",FPEI,""))
- I 'FPI D ERR^DMSQU(F,FI,"FOREIGN KEY: NO ASSOCIATED PRIMARY KEY") Q ""
- S FCI=$P(^DMSQ("P",FPI,0),U,2)
- S FNM=CN_"_FK",IEN=$O(^DMSQ("E","G",TI,FNM,""))
- ;BUILD FOREIGN KEY TABLE ELEMENT
- S TT=1.5216,IEN=$S(IEN:IEN,1:"+1")_","
- S FDA(TT,IEN,.01)=FNM ;FOREIGN KEY NAME
- S FDA(TT,IEN,1)=FDI ;DOMAIN = FOREIGN KEY TABLE DOMAIN
- S FDA(TT,IEN,2)=TI ;TABLE ID
- S FDA(TT,IEN,3)="F" ;TYPE F = FOREIGN KEY
- S FDA(TT,IEN,4)="Foreign key to "_FTN ;COMMENT
- S FIEN=$$PUT^DMSQU(IEN,"FDA","ERR")
- I $D(ERR) D ERR^DMSQU(F,FI,"FOREIGN KEY: TABLE ELEMENT INSERT FAILED") Q ""
- ;BUILD FOREIGN KEY COLUMN ELEMENT
- S TT=1.5219,IEN=$O(^DMSQ("F","B",FIEN,"")),IEN=$S(IEN:IEN,1:"+1")_","
- S FDA(TT,IEN,.01)=FIEN ;FK TABLE ELEMENT ID
- S FDA(TT,IEN,1)=FPI ;FOREIGN TABLE PRIMARY KEY ELEMENT ID
- S FDA(TT,IEN,2)=CI ;COLUMN ID IN LOCAL TABLE
- S FIEN=$$PUT^DMSQU(IEN,"FDA","ERR")
- I $D(ERR) D ERR^DMSQU(F,FI,"FOREIGN KEY: COLUMN ELEMENT INSERT FAILED")
- Q FIEN
- DMSQF1 ;SFISC/JHM-INITIALIZE SQLI_FILE (CONT) ;5/7/98 14:53
- +1 ;;22.0;VA FileMan;;Mar 30, 1999
- +2 ;Per VHA Directive 10-93-142, this routine should not be modified.
- +3 QUIT
- PK(TI) ;GENERATE PRIMARY KEY ELEMENTS AND COLUMNS FOR TABLE IDENTIFIER TI
- +1 IF '$GET(DIFM)
- DO ENV^DMSQU
- +2 NEW X,N,G,PAR,PEI,TIEN,T,DIERR
- SET T=1.5215
- SET TIEN=TI_","
- +3 KILL DIERR
- DO GETS^DIQ(T,TIEN,".01;6;8","I","X")
- +4 IF $DATA(DIERR)
- DO ERR^DMSQU(999999999,"","PRIMARY KEY: CAN'T GET TABLE DATA")
- QUIT ""
- +5 SET N=X(T,TIEN,.01,"I")
- SET G=X(T,TIEN,8,"I")
- SET F=X(T,TIEN,6,"I")
- +6 ;BUILD DOMAIN
- KILL FDA
- SET TT=1.5212
- SET DI=$ORDER(^DMSQ("DM","C",TI,""))
- +7 ;TABLE DOMAIN
- SET IEN=$SELECT(DI:DI,1:"+1")_","
- +8 ;DOMAIN NAME
- SET FDA(TT,IEN,.01)=N_"_ID"
- +9 ;DATA TYPE = PRIMARY KEY
- SET FDA(TT,IEN,1)=1
- +10 ;COMMENT
- SET FDA(TT,IEN,2)="Domain of table "_N
- +11 ;TABLE ID
- SET FDA(TT,IEN,3)=TI
- +12 SET DI=$$PUT^DMSQU(IEN,"FDA","ERR")
- +13 IF 'DI
- DO ERR^DMSQU(F,"","PRIMARY KEY: DOMAIN INSERT FAILED")
- QUIT ""
- +14 ; BUILD PRIMARY KEY TABLE ELEMENT
- +15 SET PEI=$ORDER(^DMSQ("E","F",TI,"P",""))
- SET IEN=$SELECT(PEI:PEI,1:"+1")_","
- SET TT=1.5216
- +16 ;TABLE ELEMENT NAME
- SET FDA(TT,IEN,.01)=N_"_PK"
- +17 ;TE DOMAIN SAME AS TABLE
- SET FDA(TT,IEN,1)=DI
- +18 ;TABLE ID
- SET FDA(TT,IEN,2)=TI
- +19 ;TYPE = PRIMARY KEY
- SET FDA(TT,IEN,3)="P"
- +20 ;COMMENT
- SET FDA(TT,IEN,4)="Primary key header for table "_N
- +21 SET PEI=$$PUT^DMSQU(IEN,"FDA","ERR")
- +22 IF 'PEI
- DO ERR^DMSQU(F,"","PRIMARY KEY: TABLE ELEMENT INSERT FAILED")
- QUIT ""
- +23 ;BUILD TABLE PRIMARY KEYS
- +24 SET F=$$GET^DMSQU(1.5215,TI_",",6,"I","","ERR")
- SET KL=$$KL^DMSQU(F)
- SET PAR=""
- +25 IF $DATA(ERR)!'F
- DO ERR^DMSQU(F,"","PRIMARY KEY: CAN'T GET TABLE'S FILE #")
- QUIT ""
- +26 FOR I=1:1:$LENGTH(G,"{K}")-1
- DO PKI(PEI,I,$PIECE(KL,",",I),$PIECE(G,"{K}",I),.PAR)
- +27 QUIT CEI
- PKI(PEI,SEQ,F,GF,PAR) ;BUILD COLUMN ELEMENT, COLUMN AND PRIMARY KEY DEFS
- +1 NEW TN,KI,KN,PI,CI,DM,W,S,TT,I,DI,ERR
- +2 SET TN=$$GET^DMSQU(1.5216,PEI_",",2,"","","ERR")
- +3 IF TN=""
- DO ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: CAN'T GET DATA FOR MASTER TABLE")
- QUIT ""
- +4 SET KI=$ORDER(^DMSQ("T","C",F,""))
- +5 IF 'KI
- DO ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: MISSING TABLE RECORD")
- QUIT ""
- +6 SET KN=$$GET^DMSQU(1.5215,KI_",",.01)
- SET (CI,CEI)=""
- +7 SET PI=$ORDER(^DMSQ("P","C",PEI,SEQ,""))
- +8 IF PI
- KILL ERR,DIERR
- Begin DoDot:1
- +9 SET CI=$$GET^DMSQU(1.5218,PI_",",1,"I","","ERR")
- +10 IF $DATA(ERR)
- DO ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: TABLE MISSING COLUMN POINTER")
- QUIT
- +11 IF CI
- SET CEI=$$GET^DMSQU(1.5217,CI_",",.01,"I","","ERR")
- +12 IF $DATA(ERR)
- DO ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: CAN'T GET COLUMN'S TABLE ELEMENT")
- QUIT
- +13 IF 'CI!('CEI)
- DO ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: MISSING COLUMN POINTER")
- QUIT
- End DoDot:1
- IF 'CI!'CEI
- QUIT
- +14 ;DEFINE COLUMN ELEMENT
- +15 SET IEN=$SELECT(CEI:CEI,1:"+1")_","
- SET TT=1.5216
- +16 SET DM=$$DOM^DMSQU(F,.001)
- SET W=$PIECE(DM,U,2)
- SET S=$PIECE(DM,U,3)
- SET DM=$PIECE(DM,U)
- +17 IF DM=""!("FM_DATE_TIME;FM_MOMENT;FM_DATE;INTEGER;NUMERIC"'[DM)
- SET DM="INTEGER"
- SET W=10
- +18 SET DI=$ORDER(^DMSQ("DM","B",DM,""))
- +19 ;COLUMN NAME = TABLE_NAME_ID
- SET FDA(TT,IEN,.01)=$$KWC^DMSQU(KN_"_ID")
- +20 ;DOMAIN
- SET FDA(TT,IEN,1)=DI
- +21 ;TABLE ID
- SET FDA(TT,IEN,2)=TI
- +22 ; TYPE = COLUMN
- SET FDA(TT,IEN,3)="C"
- +23 ; COMMENT
- SET FDA(TT,IEN,4)="Primary key #"_SEQ_" of table "_TN
- +24 SET CEI=$$PUT^DMSQU(IEN,"FDA","ERR")
- +25 IF $DATA(ERR)
- DO ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: COLUMN ELEMENT INSERT FAILED")
- QUIT
- +26 ;DEFINE COLUMN
- +27 SET TT=1.5217
- SET CI=$ORDER(^DMSQ("C","B",CEI,""))
- SET IEN=$SELECT(CI:CI,1:"+1")_","
- +28 ;COL ELEMENT ID
- SET FDA(TT,IEN,.01)=CEI
- +29 ;WIDTH
- SET FDA(TT,IEN,2)=W
- +30 ;SCALE
- IF S
- SET FDA(TT,IEN,3)=S
- +31 ;REQUIRED
- SET FDA(TT,IEN,5)=1
- +32 ;BASE COLUMN
- SET FDA(TT,IEN,7)=0
- +33 ;GLOBAL FRAGMENT
- SET FDA(TT,IEN,9)=GF
- +34 ;PARENT
- SET FDA(TT,IEN,8)=PAR
- +35 SET CI=$$PUT^DMSQU(IEN,"FDA","ERR")
- +36 IF $DATA(ERR)
- DO ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: COLUMN INSERT FAILED")
- QUIT
- +37 SET PAR=CI
- +38 ;DEFINE PRIMARY KEY
- +39 SET TT=1.5218
- SET PI=$ORDER(^DMSQ("P","C",PEI,SEQ,""))
- SET IEN=$SELECT(PI:PI,1:"+1")_","
- +40 ; TABLE ELEMENT
- SET FDA(TT,IEN,.01)=PEI
- +41 ; COLUMN
- SET FDA(TT,IEN,1)=CI
- +42 ; SEQUENCE NUMBER
- SET FDA(TT,IEN,2)=SEQ
- +43 ; ALWAYS START AT 0
- SET FDA(TT,IEN,3)=0
- +44 ; STOP AT EXPRESSION
- SET FDA(TT,IEN,4)="'{K}"
- +45 SET CI=$$PUT^DMSQU(IEN,"FDA","ERR")
- +46 IF $DATA(ERR)
- DO ERR^DMSQU(F,SEQ,"INDEX PRIMARY KEY: TABLE ELEMENT INSERT FAILED")
- +47 QUIT
- FK(CI) ;DEFINE FOREIGN KEY FOR POINTER COLUMN CI
- +1 NEW C,CEI,CE,CN,F,FI,TI,SP,FF,FIEN,FTI,FTN,FPEI,FPE,FPI,FNM,TT,IEN,FDA
- +2 SET C=^DMSQ("C",CI,0)
- +3 SET CEI=$PIECE(C,U)
- SET CE=^DMSQ("E",CEI,0)
- SET CN=$PIECE(CE,U)
- IF $PIECE(CE,U,2)'=13
- QUIT ""
- +4 SET F=$PIECE(C,U,5)
- SET FI=$PIECE(C,U,6)
- IF 'F!'FI
- QUIT ""
- +5 SET TI=$PIECE(CE,U,3)
- SET SP=$PIECE(^DD(F,FI,0),U,2)
- +6 SET FF=+$PIECE(SP,"P",2)
- +7 IF 'FF
- DO ERR^DMSQU(F,FI,"FOREIGN KEY: NO POINTED-TO FILE IN SPECIFIER")
- QUIT ""
- +8 SET FTI=$ORDER(^DMSQ("T","C",FF,""))
- +9 IF 'FTI
- DO ERR^DMSQU(F,FI,"FOREIGN KEY: NO TABLE FOR POINTED-TO FILE")
- QUIT ""
- +10 SET FTN=$PIECE(^DMSQ("T",FTI,0),U)
- +11 SET FPEI=$ORDER(^DMSQ("E","F",FTI,"P",""))
- +12 IF 'FPEI
- DO ERR^DMSQU(F,FI,"FOREIGN KEY: NO PRIMARY KEY TABLE ELEMENT")
- QUIT ""
- +13 SET FPE=^DMSQ("E",FPEI,0)
- SET FDI=$PIECE(FPE,U,2)
- +14 SET FPI=$ORDER(^DMSQ("P","B",FPEI,""))
- +15 IF 'FPI
- DO ERR^DMSQU(F,FI,"FOREIGN KEY: NO ASSOCIATED PRIMARY KEY")
- QUIT ""
- +16 SET FCI=$PIECE(^DMSQ("P",FPI,0),U,2)
- +17 SET FNM=CN_"_FK"
- SET IEN=$ORDER(^DMSQ("E","G",TI,FNM,""))
- +18 ;BUILD FOREIGN KEY TABLE ELEMENT
- +19 SET TT=1.5216
- SET IEN=$SELECT(IEN:IEN,1:"+1")_","
- +20 ;FOREIGN KEY NAME
- SET FDA(TT,IEN,.01)=FNM
- +21 ;DOMAIN = FOREIGN KEY TABLE DOMAIN
- SET FDA(TT,IEN,1)=FDI
- +22 ;TABLE ID
- SET FDA(TT,IEN,2)=TI
- +23 ;TYPE F = FOREIGN KEY
- SET FDA(TT,IEN,3)="F"
- +24 ;COMMENT
- SET FDA(TT,IEN,4)="Foreign key to "_FTN
- +25 SET FIEN=$$PUT^DMSQU(IEN,"FDA","ERR")
- +26 IF $DATA(ERR)
- DO ERR^DMSQU(F,FI,"FOREIGN KEY: TABLE ELEMENT INSERT FAILED")
- QUIT ""
- +27 ;BUILD FOREIGN KEY COLUMN ELEMENT
- +28 SET TT=1.5219
- SET IEN=$ORDER(^DMSQ("F","B",FIEN,""))
- SET IEN=$SELECT(IEN:IEN,1:"+1")_","
- +29 ;FK TABLE ELEMENT ID
- SET FDA(TT,IEN,.01)=FIEN
- +30 ;FOREIGN TABLE PRIMARY KEY ELEMENT ID
- SET FDA(TT,IEN,1)=FPI
- +31 ;COLUMN ID IN LOCAL TABLE
- SET FDA(TT,IEN,2)=CI
- +32 SET FIEN=$$PUT^DMSQU(IEN,"FDA","ERR")
- +33 IF $DATA(ERR)
- DO ERR^DMSQU(F,FI,"FOREIGN KEY: COLUMN ELEMENT INSERT FAILED")
- +34 QUIT FIEN
