An alternative to explaining to SQL analysts how Pick-style dictionaries work, present the dictionary as an SQL schema instead:
At some point in their careers, custodians of a UniVerse (or UniData, D3, Pick, Caché, etc.) database will invariably be tasked with extracting data to import into a relational database like Microsoft SQL Server or MySQL. In my experience trying to explain how multivalue data is stored in a relational database to someone with only 1NF or 2NF databases experience is no easy task. Especially if the data being exported has a lot of multivalued associations. The result of exploding the multivalued associations is usually many related tables, with varying "depths" of rows for any one foreign key. The first step I've found that helps is to present a schema for the exported data that is familiar to the person tasked with importing the data.I've created a new global dictionary item called COLUMN.CONVERSION that converts the native conversion codes into their equivalent SQL column types. Here are some native (to UniVerse) conversion codes and the SQL counterpart:
| Pick Conversions |
SQL Data Types |
| D4/ | date |
| MTS | time |
| MD2 | real |
A UniVerse dictionary I-type (S correlative in Pick) is not the easiest method for performing translations but is a reasonable place to start. One might be tempted to do the following:
001: I Output the corresponding SQL data type
002: "Date,Time,Real";"D4/ ,MTS ,MD2 ";IF LEN(F3) THEN INDEX(@2,F3,1) ELSE 0;IF @3 THEN @1[@3,4] ELSE ""
...
Note: All programming displayed on this site is for illustrative purposes only. Use at your own risk.
>ED DICT.DICT COLUMN.CONVERSION
0001: I
0002: TYPE;IF @1 = "D" OR @1 = "I" THEN F3 ELSE IF @1 = "A" OR @1 = "S" THEN F7 ELSE "";
IF @2 MATCHES "'UDATTIM(D'1X0X'MT'0X" THEN "Datetime" ELSE @2;
IF @3="D" OR @3 MATCHES "'D'1N0X" OR @3 MATCHES "'UDATTIM(D'0X" THEN "Date" ELSE @3;
IF @4[1,1]="U" THEN @4[2,5] ELSE @4;
IF @5 MATCHES "'MD'0X" THEN "Decimal" ELSE @5;
IF @6[1,2]="MC" THEN "Text" ELSE @6;
IF @7[1,2]="MT" THEN "Time" ELSE @7;
IF @8="" THEN "Varchar(":MATCHFIELD(F5,"0N0X",1):")" ELSE @8;
IF @9="CODE" THEN "Varchar(":MATCHFIELD(F5,"0N0X",1):")" ELSE @9
0003:
0004: Conversion}Code
0005: 12L
0006: S
...
The above I-type works using the simple concept that the prior semi-colon delimited section can be referenced with an "at" variable. The first semi-colon delimited section ("TYPE;") is evaluated to determine if it is a letter 'D', 'I', 'A' or 'S', in which case the second semi-colon delimited section is set to field three or field seven (or null). With each subsequent evaluation/assignment the @n (where 'n' is number representing the current number of semi-colon delimiters) is set. After nine of these evaluation/assignments the result is sussed out. The result of the final evaluation/assignment is then what the I-type outputs.
Note: The I-type checks for the string "UDATTIM" in field three which is a custom "user exit" conversion that stores (or outputs) a date+time value (think @DATE+@TIME).
Here is an example of the output:
>LIST DICT INVENTORY @ID ASSOC ASSOC.A.IND ASSOC.CUSTODIAN ASSOC.DEPT ASSOC.LOCATION ASSOC.NO ASSOC.NO1 ASSOC.PREMISE ASSOC.REPORT.DATE ASSOC.STATUS ASSOC1.IND BAD.COND BAD.LOC BAD.PERM.LOC BIN CAPACITY CHANGE.DATE CHECKED.IN.CUSTODIAN CHECKED.IN.DATE CHECKED.IN.DEPT CHECKED.IN.LOC CHECKED.IN.LOC.DESC CHECKED.IN.NAME CHECKED.IN.ORG CHECKED.IN.QUANTITY CHECKED.IN.TIME CHECKED.IN.UNIT CHECKED.OUT.DATE CHECKED.OUT.DEPT CHECKED.OUT.LOC CHECKED.OUT.ORG CHECKED.OUT.QUANTITY CHECKED.OUT.UNIT CI.CUSTODIAN CI.STATUS COL.SPCS COLUMN.CONVERSION COLUMN.HEADING CONDITION CONDITION.DATE CONV.COND CONV.COND.COMMENTS CUSTODIAN.BIN CUSTODIAN.COMMENT CUSTODIAN.DATE CUSTODIAN.DISP CUSTODIAN.FROM CUSTODIAN.INV.ID CUSTODIAN.LOC CUSTODIAN.TIME CUSTODIAN.TO DATE.CREATED DATE.UPDATED DEFAULT.BIN DEFAULT.CAT DEFAULT.CHECKED.IN.DEPT DEFAULT.CHECKED.IN.ORG DEFAULT.CHECKED.OUT.DATE DEFAULT.CHECKED.OUT.DEPT DEFAULT.CHECKED.OUT.ORG DEFAULT.CITY DEFAULT.COND DEFAULT.CSI.TYPE DEFAULT.CUSTODIAN.FROM DEFAULT.CUSTODIAN.LOC DEFAULT.CUSTODIAN.TO DEFAULT.OLS DEFAULT.RES.CITY DEFAULT.RES.ST DEFAULT.RES.ZIP DEFAULT.REVIEW DEFAULT.REVIEW.DATE DEFAULT.ST DEFAULT.ZIP DEPT DICT DMI.INVENTORY.DATA ENTRY.DATE FIELD FIELD.FMT IMAGE.COUNT IN.ASSOC IN.POSSESSION INSPECTION INSPECTION.DATE INV.TYPE INV.TYPE.DESC INVENTORY INVENTORY.ID ITEM ITEM.BARCODE.VALUE ITEM.CATEGORY ITEM.NO KEY0 KEY1 KEY2 KEY3 LABEL.DATE LETTER.DATE LIST.NO LOCATION LOST.DATE LOT MM.DEVELOP.DATE MODEL NOTFOUND.IND OAN OWNER OWNER.BUS.NAME OWNER.DOB OWNER.FIRSTNAME OWNER.FULLNAME OWNER.ID OWNER.LASTNAME OWNER.MIDDLENAME OWNER.OLN OWNER.OLS OWNER.RES.ADDRESS OWNER.RES.CITY OWNER.RES.PHONE OWNER.RES.STATE OWNER.RES.ZIP OWNER.SEX PERMANENT.LOC PERMANENT.LOC.ROOM PHARMA.SOURCE PHARMA.TYPE PRIVATE PROP.COUNT PROPERTY.COUNT PROPERTY.CUSTODIAN RECEIPT.ADDL.DOB RECEIPT.ADDL.FIRSTNAME RECEIPT.ADDL.LASTNAME RECEIPT.ADDL.MIDDLENAME RECEIPT.ARREST2.OFFICER RECEIPT.BOOK.CUSTODIAN RECEIPT.COMPARED RECEIPT.DATE RECEIPT.DOB RECEIPT.EVENT RECEIPT.EVENT2 RECEIPT.FIRSTNAME RECEIPT.HOURS RECEIPT.LASTNAME RECEIPT.MIDDLENAMME RECEIPT.OFFICER RECEIPT.RESULT RECEIPT.TRANS RELEASE.AUTH RELEASE.TO REMOVE.DATE REPORT.DATE RETENTION REVIEW.DATE SERIAL.NO SM.ITEM.BARCODE.VALUE SPECIAL STORED.BY STORED.DATE SYSTEM.MISC1 SYSTEM.MISC2 SYSTEM.MISC3 SYSTEM.MISC4 SYSTEM.MISC5 TODAY UPDESC USE.DATE USE.LOCATION USE.TIME USER.DEPT USER.MISC1 USER.MISC2 USER.MISC3 USER.MISC4 USER.PADEPT USERNAME VAR VAR.IN.USE WHO.CREATED WHO.UPDATED HDR.SUP ID.SUP COL.SPCS
Field............................ Conversion.. Output.........
Name............................. Code........ ColHdg............ Format.........
@ID Varchar(20) INVENTORY No 20L
ASSOC Varchar(15) ASSOC No 15L
ASSOC.A.IND Varchar(15) ASSOC DEPT Index 15L
ASSOC.CUSTODIAN Varchar(6) ASSOC CUSTODIAN 6R
ASSOC.DEPT Varchar(5) ASSOC DEPT 5L
ASSOC.LOCATION Varchar(30) Location 30L
ASSOC.NO Varchar(10) ASSOC.NO No 10R
ASSOC.NO1 Varchar(8) ASSOC No 8R
ASSOC.PREMISE Varchar(20) Premise 20L
ASSOC.REPORT.DATE Date ASSOC Report Date 10R
ASSOC.STATUS Varchar(2) ASSOC Status 2L
ASSOC1.IND Varchar(5) ASSOC DEPT Index 5L
BAD.COND Varchar(4) Bad Last COND 4L
BAD.LOC Varchar(3) Bad Loc 3R
BAD.PERM.LOC Varchar(3) Bad Loc 3R
BIN Varchar(4) Bin 4L
CALL Varchar(8) ASSOC.NO No 8R
CALL.TYPE Varchar(6) Call Type 6L
CAPACITY Varchar(3) CAPACITY Fired 3R
CHANGE.DATE Date Change Date 10R
CHECKED.IN.CUSTODIAN Varchar(6) Recov Off 6R
CHECKED.IN.DATE Date CHECKED.INy Date 10R
CHECKED.IN.DEPT Varchar(33) DEPT 33L
CHECKED.IN.LOC Varchar(30) Location 30L
CHECKED.IN.LOC.DESC Varchar(32) Desc 32L
CHECKED.IN.NAME Text CHECKED.IN Name 20L
CHECKED.IN.NAME Text CHECKED.IN Name 20L
CHECKED.IN.ORG Varchar(26) ORG 26L
CHECKED.IN.QUANTITY Decimal Quantity 8R
CHECKED.IN.TIME Time CHECKED.IN Time 5R
CHECKED.IN.UNIT Varchar(10) Unit 10L
CHECKED.OUT.DATE Date CHECKED.OUT Date 10R
CHECKED.OUT.DEPT Varchar(33) DEPT 33L
CHECKED.OUT.LOC Varchar(30) Location 30L
CHECKED.OUT.ORG Varchar(26) ORG 26L
CHECKED.OUT.QUANTITY Decimal Quantity 8R
CHECKED.OUT.UNIT Varchar(10) Unit 10L
CI.CUSTODIAN Varchar(6) CI CUSTODIAN 6R
CI.STATUS Varchar(2) Status 2L
CONDITION Varchar(12) COND 12L
CONDITION.DATE Date COND Date 10R
CONV.COND Varchar(40) COND'd 40L
CONV.COND Varchar(4) COND'd 4L
CONV.COND.COMMENTS Varchar(40) COND'd 40L
CUSTODIAN.BIN Varchar(4) Bin 4L
CUSTODIAN.COMMENT Varchar(60) Comments/Remarks 60T
CUSTODIAN.COND Varchar(2) COND 2L
CUSTODIAN.DATE Date CUSTODIANODIAN Date 10R
CUSTODIAN.FROM Varchar(15) Transfer From 15L
CUSTODIAN.INV.ID Varchar(6) CUSTODIAN INV No 6L
CUSTODIAN.LOC Varchar(12) Location 12L
CUSTODIAN.TIME Time Time 5L
CUSTODIAN.TO Varchar(15) Transfer To 15L
DATE.CREATED datetime Entered 16R
DATE.UPDATED datetime Changed 16R
DEFAULT.BIN Varchar(4) Dflt Bin 4L
DEFAULT.CAT Varchar(2) Dflt Cat Cod 2L
DEFAULT.CHECKED.IN.DEPT Varchar(4) DFLT REC AGCY 4L
DEFAULT.CHECKED.IN.ORG Varchar(2) DFLT RECOV ORG 2L
DEFAULT.CHECKED.OUT.DATE Date Dflt CHK.OUT Date 10R
DEFAULT.CHECKED.OUT.DEPT Varchar(4) DFLT STOL AGCY 4L
DEFAULT.CHECKED.OUT.ORG Varchar(2) DFLT CHK.OUT ORG 2L
DEFAULT.CITY Varchar(15) DFLT CITY 15L
DEFAULT.COND Varchar(2) Dflt COND 2L
DEFAULT.CSI.TYPE Varchar(3) Dflt CSI Type 3L
DEFAULT.CUSTODIAN.FROM Varchar(12) Dflt CUSTODIAN From 12L
DEFAULT.CUSTODIAN.LOC Varchar(12) Dflt CUSTODIAN Loc 12L
DEFAULT.CUSTODIAN.TO Varchar(12) Dflt CUSTODIAN To 12L
DEFAULT.OLS Varchar(2) DFLT OLS 2L
DEFAULT.RES.CITY Varchar(15) DFLT RES CITY 15L
DEFAULT.RES.ST Varchar(2) DFLT RES ST 2L
DEFAULT.RES.ZIP Varchar(10) DFLT RES ZIP 10L
DEFAULT.REVIEW Date Dflt Review Date 10R
DEFAULT.REVIEW.DATE Date Default Rev Date 10R
DEFAULT.ST Varchar(2) Dft St 2L
DEFAULT.ZIP Varchar(10) DFLT ZIP 10L
DEPT Varchar(4) DEPT 4L
DMI.INVENTORY.DATA Varchar(15) DMI DATA 15L
ENTRY.DATE Date Entry Date 10R
IMAGE.COUNT Decimal Image Count 5R
IN.ASSOC BOOL In ASSOC 3R
IN.POSSESSION Varchar(3) In Pos 3L
INSPECTION Varchar(25) INSPECTION 25L
INSPECTION.DATE Date INSPECTION Date 10R
INV.TYPE Varchar(2) INV Type 2L
INV.TYPE.DESC Varchar(25) INVENTORY Type 25L
INVENTORY.ID Varchar(6) INV No 6R
ITEM Varchar(4) Weap Type 4L
ITEM.BARCODE.VALUE Varchar(10) Item BC Value 10L
ITEM.CATEGORY Varchar(1) WEAP CAT 1L
ITEM.NO Varchar(3) Item No 3R
KEY0 Varchar(8) Prop/INV ID 8R
KEY1 Varchar(4) DEPT 4L
KEY2 Varchar(8) INVENTORY No 8R
KEY3 Varchar(4) Item No 4R
LABEL.DATE Varchar(10) Label Date 10R
LETTER.DATE Date Letter Date 10R
LIST.NO Varchar(5) List 5R
LOCATION Varchar(15) Current Location 15L
LOST.DATE Date Lost Date 10R
LOT Varchar(10) Lot Ref 10L
MM.DEVELOP.DATE Date MM DEVELOP DATE 10R
MODEL Varchar(4) MODEL 4L
NOTFOUND.IND Varchar(1) Not Rec 1L
OAN Varchar(20) Owner No 20L
OWNER Varchar(6) OWNER No 6R
OWNER.BUS.NAME Text Business Name 40L
OWNER.BUS.NAME Text Business Name 40L
OWNER.FIRSTNAME Varchar(15) First 15L
OWNER.FULLNAME Text OWNER Name 30L
OWNER.ID Varchar(11) Owner ID 11L###-##-####
OWNER.LASTNAME Varchar(15) Last Name 15L
OWNER.MIDDLENAME Varchar(10) Middle 10L
OWNER.OLN Varchar(10) Owner OLN 10L
OWNER.OLS Varchar(2) Owner OLS 2L
OWNER.RES.ADDRESS Varchar(20) Address 20L
OWNER.RES.CITY Varchar(12) City 12L
OWNER.RES.PHONE PHON Telephone 14L
OWNER.RES.STATE Varchar(2) St 2L
OWNER.RES.ZIP Varchar(10) ZIP 10L
PERMANENT.LOC Varchar(25) Permanent Location 25L
PERMANENT.LOC.ROOM Varchar(4) INV Room 4L
PHARMA.SOURCE Varchar(2) Source 2L
PHARMA.TYPE Varchar(20) PHARMA/Narcotics 20L
PRIVATE BOOL No Share 3R
PROP.COUNT Varchar(15) Prop Items 15L
PROPERTY.COUNT Decimal Prop Items 5R
PROPERTY.CUSTODIAN Varchar(6) Prop CUSTODIAN 6L
RECEIPT.ADDL.DOB Date DOB 10R
RECEIPT.ADDL.FIRSTNAME Varchar(12) First 12L
RECEIPT.ADDL.LASTNAME Varchar(12) ADDL Last Name 12L
RECEIPT.ADDL.MIDDLENAME Varchar(10) Mid 10L
RECEIPT.BOOK.CUSTODIAN Varchar(15) Jailer ID 15L
RECEIPT.COMPARED Varchar(15) Comparison 15L
RECEIPT.CUSTODIAN Varchar(15) CUSTODIAN 15L
RECEIPT.DATE Date Date 10R
RECEIPT.DOB Date DOB 10R
RECEIPT.EVENT Varchar(10) RBPD EVENT 10L
RECEIPT.EVENT2 Varchar(20) EVENT Other 20L
RECEIPT.EVENT2.CUSTODIAN Varchar(15) Jailer ID 15L
RECEIPT.FIRSTNAME Varchar(12) First 12L
RECEIPT.HOURS Decimal Hours 4L
RECEIPT.HOURS Decimal Hours 5L
RECEIPT.LASTNAME Varchar(12) Last Name 12L
RECEIPT.MIDDLENAME Varchar(10) Mid 10L
RECEIPT.RESULT Varchar(15) Results 15L
RECEIPT.TRANS Varchar(15) INVENTORY RECEIPT 15L
RELEASE.AUTH Varchar(13) Authorized By 13L
RELEASE.TO Varchar(12) Release To 12L
REMOVE.DATE Date Remove Date 10R
REPORT.DATE Date Report Date 10L
RETENTION Varchar(4) Ret Code 4L
REVIEW.DATE Date Review Date 10R
SERIAL.NO Varchar(6) SERIAL.NO 6L
SM.ITEM.BARCODE.VALUE Varchar(12) Item BC Value 12L
SPECIAL BOOL Pro tct 3R
STORED.BY Varchar(10) STORED BY 10L
STORED.DATE Date STORED DATE 10R
SYSMISC1 Varchar(6) Reserved System1 6L
SYSMISC2 Varchar(6) Reserved System2 6L
SYSMISC3 Varchar(6) Reserved System3 6L
SYSMISC4 Varchar(6) Reserved System4 6L
SYSMISC5 Varchar(6) Reserved System5 6L
TODAY Date Today 10R
UPDESC Varchar(45) DESC 45L
USE.DATE Date USE Date 10R
USE.LOCATION Varchar(35) USE Location 35L
USE.TIME Time USE Time 5R
USER.DEPT Varchar(4) User Agcy 4L
USER.DEPT Varchar(4) User DEPT 4L
USER.MISC1 Varchar(6) Reserved User1 6L
USER.MISC2 Varchar(6) Reserved User2 6L
USER.MISC3 Varchar(6) Reserved User3 6L
USER.MISC4 Varchar(6) Reserved User4 6L
USER.MISC5 Varchar(6) Reserved User5 6L
USERNAME Varchar(9) User ID 9L
VAR Varchar(12) VAR 12L
VAR.IN.USE BOOL Inv In Crim 3R
WHO.CREATED Varchar(9) By 9L
WHO.UPDATED Varchar(9) By 9L
185 records listed.
This dictionary-to-SQL-schema is more meaningful to the dbms analyst not versed in multivalued databases.
No comments:
Post a Comment