How do I find the column listing in UniVerse with RetrieVe or SQL?

Question

I've got an issue where a table (file) is set up to return column foo on LIST table and SELECT * FROM table. I need to know the other possible columns in table. I'm pretty sure this was achieved by setting @ (behavoir definition of unqualified LIST), and @select (behavoir definition of * with very SELECT) but I don't know how to get the full list of columns. How do I read the table schema in uvsh and query for the physical table columns?

Running LIST.ITEM on the table shows me a list of all of the field numbers and values, but how do i find the DISPLAY NAME and column name of the numbered fields?

Answer 1

A previous answer I received on SO had mentioned LIST DICT as a way to get some metadata. This was in fact what I think I wanted. The official documentation uses LIST DICT; however, on my system I thought there wasn't LIST DICT, there is. It requires a file argument. It simply wasn't a separate command either (many commands have spaces in them), instead in (UniVerse 10.1) list is defined as:

LIST [ DICT | USING [ DICT ] dictname ] filename [ records | FROM n ]
[ selection ] [ output.limiter ] [ sort ] [ output ] [ report.qualifiers ] [TOXML
[ELEMENTS] [WITHDTD] [XMLMAPPING mapping_file]]

So in summary, The same verb (LIST) to query data is used to query the schema, with the same destination file.

Originally when I presumed there wasn't a LIST DICT I went searching through the VOC file with RetrieVe using LIST VOC WITH NAME MATCHING LIST... I was able to identify a like-named LIST.DICT, a PAragraph that displays the contents of DICTIONARIES sorted by record type. This did exactly what I wanted except the result was a unmanageable list of 400 rows. I don't see the documentation for LIST.DICT anywhere, and it seems as if record qualifiers and report qualifiers don't work on the LIST.DICT like they do on LIST. This was all true and compounded my confusion, in UniVerse parlance: LIST.DICT is a phrase, a stored statement, LIST is the verb I needed.

So now back to my questions:

Any idea on how to make the output of LIST DICT manageable?

You can use the report qualifier and explicitly state columns by using the positional F# syntax, or by stating the names of the columns.

LIST DICT <file> <columns>

on my system you can get a listing of the field names and their display names for instance by issuing

LIST DICT <file> NAME

The NAME comes from the master dictionary, which can be queried using LIST DICT DICT.DICT.

Now, I can see the fields in a nice (fairly clean) list, but I haven't the slightest idea of how to query a file for all of its fields.

Answer 2

Here are the basic varients:

LIST DICT foo NAME

SELECT @ID, NAME FROM DICT foo;

These will give you a physical location that corresponds to the LIST-ITEM verb:

SORT DICT foo WITH TYPE EQ "D" BY LOC LOC NAME

SORT DICT foo WITH TYPE EQ "D" BY LOC LOC NAME TOXML

Note that the "column name" or @ID is displayed by default during a LIST or SORT. TOXML can be useful, but there are a host of other XML features built in.

Answer 3

In Universe every file has an associated dictionary file. The dictionary file is basically just a data file and can be treated exactly like a data file for a variety of purposes. There are 3 things that make a dictionary file special:

1. You access it via the "DICT" keyword in front of the data file name.
2. The LIST (and related commands) command will use it by default to process the associated data file.
3. It has a structure that is defined by the DICT.DICT file, which you need to follow in order for item 2 above to work.

Generally, the dictionaries are maintained by the programmers and database administrators manually. There's no controls in Universe to guarantee that DICT records are created for every field in the associated data file and no reason that you can't have many DICT records for each field. Dictionary items are used to control output formatting and conversions, so it's normal to have multiple DICT items for each data field.

Dictionary records can also join data fields together, perform operations on multiple fields and grab data from other files. So at times it's not even clear what data field a DICT record actually relates to.

The only way to come up with a simple list of dictionary items that correspond to a data file is by inspection. Use the LIST DICT {filename} command and find the entries with the least amount data manipulation in them in their formatting fields.