As part of my multi-company application I have the same table name (Ex: CUSTOMER) under different directories, one directory for each company. The data portion of the tables in each directory is different but the dictionary portion has to be identical.

With that in mind, I would like to make changes to the dictionary of a table (DICT.CUSTOMER) in one directory ( add fields, indexes, etc) and then be able to somehow move those changes over to the corresponding dictionaries in other directories.

Copying the dictionaty portion of the table over to the other directory works OK for non-index related changes. But the moment I add, remove or create an index, copying the dictionary over obviously does not add, remove or create an index ( !CUSTOMER) in the target directory.

Would there be a way to automatically or programatically register these changes in the target directory ?

Thanks

P.S. For a while I toyed with the idea of one shared dictionary and multiple data tables in different volumes, but indexing issues again defeated that idea. Maybe someone has a better idea …

David,

The only think I can think of would be a MFS that tracked changes in the one dir's dictionaries, and automatically updated the other directory.

This could be problematic if you change the ordering of data in the dictionary by accident.

If you don't need the changes to take place immediately you could also have a program loop through in the evening and compare the directories.

For Indexes, you would need to search for SI.MFS in the revmedia entries for the tables and if it does not exist on the target, you would need to get the index information for the first table, and trigger an index build on the new tables/columns.

John Revelation

John,

Thanks for the answer. One more question:

What happens if I add an index to a table that didn't have indexing on before? Can I just copy the dictionary over to corresponding table in the other volume, add SI.MFS and then issue an update_index ?

Or is there more to it ?

David,

I believe there is a command to attach tables during the upgrade … you could attach then copy the dict items in and repeat for each "customer". Otherwise you will have to build a custom solution or repeatedly run the upgrade for each customer.

Cameron Purdy

Revelation Software

I have faced this problem, with deploying Arev upgrades to runtime users across the country. I solved it by including a table called DICTIONARY_MODS (which has a compound key of tablename*fieldname) and including the code below in my upgrade program. Obviously, you need to attach and detach volumes for it to work. I create the records in DICTIONARY_MODS by:

TCL

COPY DICT.tablename fieldname TO:(DICTIONARY_MODS tablename*fieldname

Also, I have used this same code block to create Btree and Crossref indexes by ensuring that the value of field 6 is the dict is "1".

 PERFORM "SELECT DICTIONARY_MODS (S)"

 IF @RECCOUNT THEN

    NUM=@RECCOUNT

    SLIST=NULL$

    FOR J=1 TO NUM

       READNEXT SKEY THEN

          SLIST=SKEY

       END

    NEXT

    FOR J=1 TO NUM

       SKEY =SLIST

       DKEY =FIELD( SKEY , "*" , 2 )

       DFILE=DICT." : FIELD( SKEY , "*" , 1)

       CMD  =RECORDCOPY DICTIONARY_MODS " :  SKEY

       CMD  := " TO: (" : DFILE : SPACE(1)

       CMD  := DKEY : " (OS)"

       PERFORM CMD

    NEXT

 END

From Revmedia, Volume 4, Issue 6, Pages 12,13.

Using One Dictionary With Multiple Tables -

Aaron Kaplan - Softmart Inc

This article is in response to a thread a while back on Compuserve concerning the old Rev G ability to have more than one table accessing the same dictionary. That seemed like a functionality that I could use so I decided to think about a way to implement this. Suddenly the answer appeared in a vision. If I place an MFS on the dictionary table, I could intercept the open call, open a different table in its place, and the system would never know the difference. Simple, elegant, and a bit too easy. My first attempt was a stunning success, which frightened me. I decided to be brave and shoot for the whole works. Let's try indexing and create an index. Bad move. After the index was created on one table, the other table attempted to access it also (as it should). However, since there was not a !table for the second table, the system fell flat on its face. After that, I decided that this little utility of mine must have its own indexing capabilities that would alleviate these problems in the future.

So, during the code for the open, I had to check for the existence of

indexes. I also needed to check that the indexing had not changed, i.e. create delete or modify.

In the end, this is how my system is designed. I maintain a table called MULTI_DICT. Make sure this table is the global account or you could find yourself without access to a table. The key to this table is a three part key; OriginalTableName*OriginalVolume*OriginalAccount. This is the table name, volume and account as it really exists in Revelation (i.e. what the table looks like in the Revmedia table). The rows also contain three fields ; AlternativeName, AlternativeVolume, and AlternativeAccount. The rows are entered using a template. Existence of the MFS is the first thing that is checked after the template is saved. If MULTI_DICT_MFS is not on the table, we begin to install it.

Installation requires two steps. First we must add the MFS to the Revmedia row. This has been explained hundreds of times (see Revmedia passim.) and will not be described here. After the MFS is installed, I attach the table. Here is where the MFS comes into play. The MFS is surprisingly simple. The only code takes place during the open call. Here the system checks for the existence of a row in MULTI_DICT matching the table, volume and account of the table being opened. If it exists, the name, volume and account are changed to the information from the MULTI_DICT table and this is passed on to RTP57 to open. The table handle and all other pertinent information is then stored in the Tables table. (For purposes of space, the following MFS code has had all of the dummy stubs and a lot of white space removed - Ed).

This will work with the quick attach routines that are currently in use. Since the image will regenerate itself when ever a Revmedia.* table is altered, the next logon session will reflect the changed table handle.

At this point, the attached table's dictionary is the new, alternative

dictionary. I check through each dictionary row to determine if there is an index on the field. If the !table and the dictionary rows do not match, the row is updated through index.flush and make.index routines. See Revmedia passim for information on these functions.

If indexing has been changed on an existing table, one of two things will happen. The system will probably notice the addition of an index and will update the index the first time the table is accessed. But, for my own piece of mind, I installed a softkey off the entry template that will update the indexing rows using the methods described above.

  SUBROUTINE BFS(CODE, BFS, HANDLE, NAME, FMC, RECORD, STATUS)

  /*******************************

    ö VERSION    : 1.0

    ö PURPOSE    : Use another table's handle for this table

    ö AUTHOR     : Aaron Kaplan

    ö CREATED    :

    ö THEORY OF OPERATION :  The MFS checks a table called MULTI_DICTS for

                             the existance of a row.  If so, that table is

                             opened in place of the table calling this MFS.

                             This has the effect of substituting the data

                             in the alternative table with that of the

                             original.

     EQU COPYWRITE$  TO 'Copyright 1992 by Aaron Kaplan All rights reserved'

  */

    EQU TRUE$       TO 1

    EQU FALSE$      TO 0

    $INSERT INCLUDE, FILE.SYSTEM.EQUATES

    $INSERT INCLUDE, FSERRORS_HDR

    FS=DELETE(BFS,1,1,1)

    NEXTFS=FS

    @FILE.ERROR="

    $INSERT INCLUDE, FILE.SYSTEM.ONGOSUB

  RETURN

  OPEN.TABLE:

    VOLUME=XLATE('FILES', NAME1,'*', 1, 'X')

    MULTI_DICT_KEY=NAME : "*" : VOLUME

    OPEN 'MULTI_DICTS' TO MULTI_DICTS_FILE THEN

<QUOTE>       READ REC FROM MULTI_DICTS_FILE, MULTI_DICT_KEY THEN

<QUOTE>          NEW_NAME=REC

          NEW_VOLUME=REC

          NEW_ACCOUNT=REC

          EXISTS=XLATE('VOLUMES', NEW_VOLUME, '', 'X')

          IF LEN(EXISTS) THEN

<QUOTE>             VOLUME_FILES=EXISTS

             HANDLE=EXISTS

            *" : NEW_ACCOUNT

             CALL @NEXTFS(CODE, FS, HANDLE, NAME, FMC, RECORD, STATUS)</QUOTE>

          END ELSE

<QUOTE>             STATUS=FALSE$</QUOTE>

          END</QUOTE>

       END ELSE

<QUOTE>          FLAG=FALSE$</QUOTE>

       END</QUOTE>

    END ELSE</QUOTE>

<QUOTE>       STATUS=FALSE$</QUOTE>

    END

  RETURN

There is one thing you should remember while creating these tables. The MFS, template and all supporting programs work with the table name that is entered. Since this is primarily used for dictionaries, then

dict.tablename should be entered. The programs do not assume the table is a dictionary nor does the program add 'DICT.' to any table name entered. Failure to do so could create some problems, the very worst is rebuilding of all indexes in the tables.

View this thread on the Works forum...