REBOL [
    Title:  "Multi-table lookup table"
    Purpose: {Combine multiple simple lookup tables into one structure.}
]

;; [---------------------------------------------------------------------------]
;; [ This is yet another module for making lookup tables.                      ]
;; [ In this case, we make a file that contains multiple lookup tables,        ]
;; [ each identified by some identifier.  Like this:                           ]
;; [     TABLE-ID-1 [CODE-1-1 VAL-1-1 CODE-1-2 VAL-1-2...]                     ]
;; [     TABLE-ID-2 [CODE-2-1 VAL-2-1 CODE-2-2 VAL-2-2...]                     ]
;; [     TABLE-ID-3 [CODE-3-1 VAL-3-1 CODE-3-2 VAL-3-2...]                     ]
;; [     ...                                                                   ]
;; [ This was created for simple lookup tables where one code item             ]
;; [ represents one value, and we want to find that value when we know         ]
;; [ the code.  But, we have several such tables, and we want to combine       ]
;; [ them in one file.  So in the example above, there is a table              ]
;; [ identified by TABLE-ID-1 and that table has a bunch of codes and,         ]
;; [ for each code, a value.  We will want to answer question like,            ]
;; [ "Give me the value for CODE-1-2 in table TABLE-ID-1."                     ]
;; [ This is very similar to other such schemes, just a touch different.       ]
;; [                                                                           ]
;; [ A function is provided to build up this multi-table table structure       ]
;; [ from an input file in a particular format.  It is a text file,            ]
;; [ similar to a CSV file but with the pipe symbol as a delimiter,            ]
;; [ and each record containing three fields.  The first field is a            ]
;; [ table ID, the second is a code, and the third as the value for the        ]
;; [ code.  There normally would be several records for the same table ID.     ]
;; [ This is how you might get data out of a database.  For example:           ]
;; [     CAC|0|None                                                            ]
;; [     CAC|1|Package                                                         ]
;; [     CAC|2|Central Air                                                     ]
;; [     CAC|3|Wall                                                            ]
;; [     CAR|1|Plus 2                                                          ]
;; [     CAR|2|Plus 1                                                          ]
;; [     CAR|3|None                                                            ]
;; [     CAR|4|Minus 1                                                         ]
;; [     CAR|5|Minus 2                                                         ]
;; [ The above data represents two lookup tables.  The first table is          ]
;; [ called CAC and has codes 0 through 3, each representing some value        ]
;; [ as indicated.  Similarly for the table called CAR.                        ]
;; [ The function will parse this data and load it into our structure.         ]
;; [ IT IS ASSUMED THAT THE DATA WILL COME IN ORDERED BY THE TABLE ID          ]
;; [ AND THE CODE (fields 1 and 2).  Normally, one would extract such          ]
;; [ data out of a database and order it by those fields.  Then one would      ]
;; [ manage somehow to put it into a pipe-delimied file like the sample        ]
;; [ above.                                                                    ]
;; [                                                                           ]
;; [ Another function is provided to search our structure when given a         ]
;; [ table ID and a code number.  For example, search the CAR table for        ]
;; [ the meaning of code 2, and you get back the string "Plus 1."              ]
;; [                                                                           ]
;; [ Note that these functions are for people who know what they are doing.    ]
;; [ It is assumed the data is clean, it is assumed you will not search        ]
;; [ for something before you have loaded data to be searched, and so on.      ]
;; [---------------------------------------------------------------------------]

MTLT: make object! [

    FILE-ID: %PicklistRawData.txt
    SAVE-ID: %PicklistTables.txt
    TABLES: []     ;; The whole multi-table table
    RAWLINES: []   ;; The whole file of raw pipe-delimited data
    REC: []        ;; One parsed line of raw data
    TBL: []        ;; One lookup table from the multi-table structure
    TBL-ID: ""     ;; ID of table under construction 
    FIRSTREC: true ;; Switch to suppress control break on first record
    
    BUILD-TABLES: does [
        RAWLINES: read/lines FILE-ID 
        foreach LINE RAWLINES [
            REC: copy []
            REC: parse/all LINE "|"
            if FIRSTREC [
                TBL-ID: REC/1
                FIRSTREC: false
            ]
            if not-equal? REC/1 TBL-ID [
                append TABLES TBL-ID
                append/only TABLES TBL
                TBL-ID: REC/1
                TBL: copy []
            ]
            append TBL REC/2
            append TBL REC/3
        ]
;;      When we hit the end, we will not have written the last table.
        append TABLES TBL-ID
        append/only TABLES TBL
    ]

    SAVE-TABLES: does [
        save SAVE-ID TABLES
    ]

    LOAD-TABLES: does [
        TABLES: copy []
        TABLES: load SAVE-ID
    ]

    SEARCH-TABLES: func [
        TABLEID
        CODE
        /local VAL 
    ] [
        either TBL: select TABLES TABLEID [
            either VAL: select TBL CODE [
                return VAL
            ] [
                VAL: none
                return VAL
            ]
        ] [
            VAL: none
            return VAL
        ]
    ]
]

;;Uncomment to test
;MTLT/BUILD-TABLES
;MTLT/SAVE-TABLES
;MTLT/LOAD-TABLES
;print ["ABT/IN: " MTLT/SEARCH-TABLES "ABT" "IN"]
;print ["GCND/3: " MTLT/SEARCH-TABLES "GCND" "3"]
;halt