====== Converting a Pick Application to OpenInsight (Migration) ====== ====== ====== ==== Created at 23 AUG 2002 03:54PM ==== **//Converting a Pick/Multivalued application platform to Revelations OpenInsight for the Multivalued application developer.//** **By Bruce Cameron, Alternative Systems.  May 2002** ****   **Background** Having been a traditional Mainframe/Super Micro Pick/multivalued programmer for many years; I have had the privilege to have come in contact with most of the multivalued platforms come, gone and still around.  Shortly after the introduction of the Personal computer, I was introduced to the Revelation G product (at that time it was known as Revelation from Cosmos). I have since continued working with traditional Multivalued systems and with Revelation; Advanced Rev (Arev) and OpenInsight as well as other products from Revelation Technologies.   **Introduction/Purpose** This paper will attempt to explain how one would migrate a typical Pick/MultiValued application to Revelation Technologies Software s environment and some of the differences and similarities between OpenInsight and other MultiValue Database management systems, and what to expect in a typical conversion/migration.   Since the target audience of this report will be the non-OpenInsight person, I will not spend time outlining the differences between Multivalued flavors.   There are some language terminology differences to point out when discussing other Multivalued systems and OpenInsight. Some of these are as follows. Within this paper I will try to mention both when applicable.   **                                                                                ** ****   |**__OpenInsight__**|**__Pick/Multivalued__** | |//Table// |//File// | |//Row        // |//Item/Attribute// | |//Field// |//Field/Attribute// | |//Symbolic Field// |//Dictionary item with correlative//| |//Column// |//Attribute/Field// | |//Script// |//Program// | |//Field Mark// |//Attribute Mark// | |//Procedure// |//Program// | |//Form// |//Entry screen// | |//Rlist// |//English, Recall, Access, etc.// | ////   ////   **__Things you'll need to do for the conversion/migration.__** **____**                                   1. Decide what platform you will be using so you can choose the proper Network product from Revelation that will work with                                           OpenInsight. Current choices are Novell or NT/Windows 2000; Unix is expected in February 2003.                                 2. Re-create your tables manually or use OpenInsights import routine or a custom one.(Editor s Note: A File Converter for D3 is                                       in the works and should be available in mid-2002)                                 3. Re-create your dictionaries or use OpenInsight s import routine or a custom one.                                 4. Re-create your index references for your system.                                 5. Create popups to facilitate your lookup screens and code tables references.                                 6. Re-create your entry screens using the Form designer with or without using some of your source code.                                 7. Create a menu system using the MDI (Multiple Document Interface) frame.                                 8. Convert your programs to subroutines, functions or event scripts (same as program code).                                 9. Re-enter your data using the forms you created or using an import program.                                 10. Re-create your reports using the forms you created or using an import program.                                 11. Wrap it up with a login entry point and shortcut on your desktop.     **Your Pick/Multivalued conversion/migration preparation **To facilitate a conversion process I recommend doing the following prior to starting any conversion or migration. **//Dictionaries   clean them up//** -               A-types, S-types, B-correlatives,A-correlatives, F-correlatives; go through your dictionaries and get rid ofredundancies.  Make sure you                 only have one A type dict item for each field (change the extra A's to S's). -               Depending upon your flavor and version of Pick/MV, keep a log or spreadsheet of your indexed fields by name and file. -               Print out each of your dictionary files by type by attribute mark count.   **//Data to export or not to export//** -               Decide if you are going to manually re-enter your data and dictionaries or export or a combination of both. -               If export, write a routine to export your data files to Dos/Windows text format.  The export should convert your data to flat text files.                          Leave your delimiters char(254), char(253), char(252) in place. Make each line in the text file an item, append a char(13) to the end of item                   and prefix the item with the item id and a then a char(254)    **//Programs oh where, oh where can that source code be .//** -               Get them organized for your own benefit, what is used, what isn't? -               Figure which are used and which are not. Only plan on moving production code. -               Categorize your program code. Make a spreadsheet or a copy of each program in another file but write a routine to write each program to                 the new file and replace any periods in the program name (item id) with an underscore. -               use your export routine to export the file to a Dos/Windows text file as mentioned. **//   //** **//   Screens print screen em if you can//** -               Get screen shots of your entry screens and print them out. These will help you identify what fields go where and what your tab order                       entry will be when you recreate the screens in the form designer.   **//Menus   I ll be having the //** -               Get screen shots of all your menus.  You will be recreating them.   **//  Reports   and we re not talking Excel//** -              Get copies of your applications main reports. You will be recreating some of them with OpenInsight's reporting tools or using existing                       code that you will have to convert.   **Network Product** You will need to decide what OS Network platform you are going to use if you don't already know. OpenInsight can run standalone or as a networked multi-user system with Revelations network products (NLM, NPP, etc). Current network choices are Novell or NT/Windows2000; Unix is expected in February 2003. The NLM (Novell Loadable Module) or NT/2000 Service is essential for multi-user application environments using OpenInsight.  The proper network product will dramatically increase your applications speed and maintain a high level,if not 100% data integrity (i.e. no GFE's)   **Creating the Application** After installing and launching OpenInsight, you will need to login to SYSPROG and create your new application.  It is important to understand that applications in OpenInsight can be inherited from other applications.  Inherited means that components from one account can be used and executed in another account, but in an upward manner only (e.g.- you can t use components from applications that are on the same level as your application). This is a lot like automatically setting up q-pointers. The is basically SYSPROG, but if you click the drop down arrow you will get a list of the other existing accounts. Once the application is created you will be able to create and edit users in your application.   **Re-creating Tables and Dictionaries** The first thing you should do is re-create your tables/files.  To facilitate this process create a subdirectory on your PC such as c:\myapp, c:\myapp\codetbls,c:\myapp\general,  c:\myapp\acting, etc.   Assuming you have a print out of your dictionaries start with you re A-type items;simply enter in the name of the dict item, remembering to change all periods to underscores in item names. (Note: It is a good practice to use underscores instead of periods in all your data items in OpenInsight)   Doubleclick each item to set the attribute/row details. These are data type,justification, conversion values, and length. If the attribute you re working on is multivalued be sure to click the multivalue check.    Next enter in your synonym/symbolic type dictionary items/rows. The first thing you may notice is that there are no field mark counts.  Do not worry about this, but follow the same as above to enter in the item/row details and your correlative.  In the formula box is where you will enter your code.   The main thing to understand here is that any value that you want the symbolic to return will be passed with a global system variable @ANS.   To help explain what you can do with symbolics here are some examples,   1              - write whole programs and/or functions. 2              - call existing programs/subroutines or functions.         Example: CALL DICTSUB.001   vs.   B; BP DICTSUB.001 3              - pass static values.  Example: @ANS = "Active" vs.A;"Active" 4              - do calculations.         Example: @ANS =@RECORD<1> + @RECORD<2> vs. MV A;(1+2) or F;2;1;:+   @ANS = {FLDNAME1} + {FLDNAME2} vs.A;N(FLDNAME1) + N(FLDNAME2) 5              - do TRANSLATES.        Syntax: Value =XLATE("{dict}.Filename",key,field,control[,expression])                          Value =T{Dict}Filename;Control{vmc};In AMC;Out AMC {;Break AMC}     The following is a page out of the OpenInsight online context-sensitive help to explain the XLATE function and its arguments/parameters.   Please see the Help for more information.   **__Parameter__                            __Description__** **Table name                          **The name of the table from which the record is to be read.   **Key**                                        The key or keys of the row(s) containing the column values to be returned. Can be multiple keys. Separate multiple keys with a system delimiter. Each key will return the value of that row in the specified column. These values will be separated by whatever system delimiter you use to separate the keys during the call to Xlate. Any values (@VM-delimited) or subvalues (@SVM-delimited) will be delimited with their own system delimiters.   **Field**                                      The number of the field or name of the field in quotes (example:  "COMPANY") to be extracted. If a null is specified, the entire record is returned. If a zero is specified, the key is returned.   **Control**                                 Must be either of the literal values X or C. These keywords indicate what the function should return if the record does not exist. An X indicates that a null should be returned, while a C will cause Xlate to return the key value.   **Expression**                           Indicates the number of times the system delimiters will be converted to the next lower delimiter. The default is 1. The column returned from the row can contain system delimiters. If the column is a multi-valued string (@VM-delimited), it becomes a subvalue string (@SVM-delimited) after one conversion. This feature becomes important when key yields a multi-valued string. After the conversion,each value contains a string of subvalues. A text mark (ASCII character 251) is the lowest delimiter.    **Indexes** Since you should already know what columns/fields in your table/file are indexed, setting up indexes is a snap. Indexed columns/fields are maintained through the OpenInsight interface or programmatically. You can add, update, delete, rebuild three types of indexes: Btrees; Cross-references; and relational or you can create a custom index schema yourself. OpenInsight uses what are called MFS s (modified filing systems), to maintain indexes on your files/tables. The SI.MFS is a routine that is called just prior to any updating to a table/file by the system.     To increase efficiency and reduce workload on the server(s), Revelation recommends having a separate machine on the network to handle index processing. This machine will process all of the indexed requests off a queue that is updated by the oengine.exe.    The Indexer as it is referred to, is a unique login id and is activated programmatically and is best used with a shortcut.   **File/TableSetup **Revelation s tables are auto-sizing; so there is no need to resize your tables. You may resize tables to tune them for performance. There are a number of Technical Bulletins at Revelation s Website(__www.Revelation.com__) that tell how to do this.   **Popups** Popups are an invaluable part of any application these days.   Use the OpenInsight User Interface (UI)workspace option off the Application Manager window to quickly create all kinds of popups. This incredibly powerful tool will allow you to quickly create custom popups, search lists, help boxes and messages. If you have lookup screens prior to entry into a screen on your current system,the UI interface is where you will create your list of search results.  If you have indexes use the IXLookup event or the IndexLookup function to combine your searches with a popup to locate items/rows.   In the UI workspace you can also create custom generic message boxes (i.e. //No items found! Click Ok to continue, Answer Yes and No//).   Once you create these objects you can call them directly from your programs, windows or even dictionary items/attributes!   **Forms** Forms/screens are the main recreation of your traditional legacy application to a windows OpenInsight (GUI) application.  With the form designer you will build or  paint  your existing screens into windows screens/forms. With OpenInsight you can have several types of forms.   1.             MDI (multiple document interface).This basically means that you can have windows within a window; the ones inside the MDI defined window do not go outside the borders of the MDI frame. For each application you would generally create one of these.  It will become the main window you call when a user logins into the application.  It can also house your menuing system. 2.             Native table/file bound form.  This form allows you to bind the form to a particular table/file in your system. The form will perform the reading, writing and deleting to                   the table/file of a row/item without you having to write any programming code. 3.             Non-bound form. This type has no particular connection to a file and allows you free form creativity.   This type is good for running special processes or custom                                                code. 4.             Bound to foreign tables/files. Same as bound.   Within these forms and the Form Designer, you can add your own program code to the form/window, controls on the form, or call system procedure code. See the OpenInsight Basic+ and Form Designer help for more information.   **Menus** Menus can be an applied to any bound or MDI frame.  OpenInsight provides an excellent section within the Form Designer to help you create and edit menus.   You may also find it useful to create your own menuing system using a combination of controls, such as hierarchical boxes and/or push button controls.     **Programming** Because OpenInsight is a MS-Windows product and thereby complies with the MS Windows API; OpenInsight forms and code processes in an event driven manner. Event driven programming is unique and significantly different than traditional top-down methodology.  Fortunately it is not difficult to learn and become productive. OpenInsight provides an extensive on-line help system that will quickly become an invaluable tool in helping you understand the syntax and function of RBasic commands.  Simply place your cursor over a word or command in the System editor and hit the F1 key for help.   **Programming differences**                In OpenInsight you will have one file that holds your executable code. This is SYSPROCS. Your source code will be contained in SYSPROCS. When using the System Editor to create a program use the File->New->Stored Procedure option.  This will make an entry in the SYSPROCS table/file of your code by name by owning application, by default. You can however, have as many program files as you like; as they are no different than other data tables/files.                Your programs will become either a declared Subroutine or Function in OpenInsight. A function call can have many arguments but will only return one value (alphanumeric) and or the argument(s).   A subroutine call can have many arguments and will return a numeric value generally 1 or 0 or pass back its arguments.                Many of the functions you are used to in your traditional Multivalued system are available, but some are not. BUT, you can simply create them as a function in OpenInsight and declare them at top of your program source prior to compilation.  Ex. DCOUNT etc.                EXECUTE/PERFORM.  You should use the RLIST routine for these processes.  OpenInsight has several ways to shell and perform TCL like commands and return the results or error messages to your program.                Using OCONV.   You can use OCONV/ICONV as normal with the exception of using  L#nn / R#nn  (use the FMT command instead) and you will need to change any  MR type oconv/iconv to  MD .                Printing. OpenInsight provides many utilities for you to use for printing.  The most basic form of printing in OpenInsight is the Print_Text(//text//) function.  This is somewhat synonymous with the PRINT command.  If you try to use this function you will need to turn your printer on and off just like your regular programs. BUT, before you do, look into the OIPI (OpenInsight Printer Interface plug-in) that is bundled with OI and these functions Set_Printer() and Get_Printer(). These are much more powerful and flexible.                CRT/INPUT commands.  No more CRT/INPUT commands.  BUT, even better is the MSG function.  The MSG function allows you to display information in virtually any format, prompt for input, choose optional buttons etc. See the OI documentation or the User Interface messaging for more.                Locking.  Locking in OpenInsight is quite a bit different than in traditional MV/Pick systems.  To lock a row/item/record in OpenInsight use the lock statement prior to a read statement.  You will then need to use the unlock statement after your processing to release the lock.                 Writing.  Writing records in OpenInsight is the same except reads, and writes follow the if then else structure.  You can append an  Else Null  to the end of your writes to speed up your conversion.                OpenInsight can have up to 8  Select by  lists in a program and should be cleared with a clearselect in the beginning of a routine.                Select and select using are the same Error capture and process. OpenInsight uses a different method for process errors during program execution.  See the error.dat file, FS_Error, Set_Status, etc. for more detailed explanations.                Using/referencing dictionary items in a program in OpenInsight is very powerful and useful (i.e. symbolics-{Calculate}). To use you must first open the data table/file, then the dict table/file and reference using the global variables @ID and @RECORD.  See the OpenInsight Basic+ help for more information.                Opening files; you cannot use  //Open filename to Handle else stop,xxx// . You will simply need to change the stop,xxx to  Return 0  ( //Open filename to Handle else return 0// ).                Compiling   since all your run time code is in SYSPROCS, when working on code in the System Editor,simply hit the F9 key to save and compile your program.  Compiler errors will display in a receiver window in the System Editor for you to reference when fixing and re-compiling.       **Debugger** OpenInsight comes with an exceptional debugger to run test code, traces, check variables, step through processing etc.   The debugger can be turned off and on during runtime or development.  The debugger is very easy to learn and use.  Just like traditional Pick/Multivalue Basic, you may add a DEBUG line to your code to invoke the debugger during run time.  I would suggest running it on a test program as soon as possible to become familiar.  The following will create a test program you can use. (Example: Create a new program to test debugger) 1.             Launch the SYSTEM Editor tool from the Application Manger (Tools->System Editor) 2.             Go to File->New->StoredProcedure 3.             Start typing from the top.. 4.             Subroutine TEST_DEBUG(Void) A = 1 B = 2 C = a + b Debug Call MSG(@WINDOW, Cequal  :C) Return 0 5.             Hit F9 to save and compile.  (If this is the first compile the system willput the program in the SYSPROCS table/file.) 6.             Restore the Application Manager(OpenInsight) from the task bar. 7.             Launch the System Monitor; Tools->Advanced->SystemMonitor 8.             Type RUN TEST_DEBUG.   This will execute your program and start the debugger when it hits the debug line.  In the debugger you can see the three variable values and your source code. Hit F5 to continue processing the program.  Close the debugger. **Reporting** **OIPI** The OpenInsight printer interface (OIPI) is a set of functions plugged into OpenInsight that you can use to create virtually any type of report. You can add and remove graphics, bitmaps, shading, boxes, borders, etc.  OIPI has an optional Preview window and supports color. See the OIPI.hlp file for more instructions.   **System Editor** The System Editor  EXEC  line control can be used to display basic reports in  a results view window or to the printer. This functionality gives you the ability to run ACCESS/Recall/English etc. type sentences.  See the RLIST command for syntax.   **OR_MAIN** OR_Main is an inherent feature that allows you to build TCL like statements using a graphical user interface.   **OR_VIEW** OR_View lets you pass TCL like statements that display in a easy to use print preview window.  This is where you may want to replace PROC s that generated reports for your application.   **Revelation Reporter** OpenInsight comes complete with a  drag and drop  report generator.  This utility will allow you or your users to quickly and easily create custom reports that can be called from within your windows/forms or code.   **3rd Party** I don't know all of the 3rd party tools/utilities available for OpenInsight.  Check Revelation s Web site or call Revelation for more information. One commonly used third party tool is SLIST, from __www.sprezzatura.com__.  It extends Rlist to allow specification of fonts, shading, color and more to RLIST reports.   **SystemEditor/TCL** OpenInsight does not have what we have all come to know and rely upon as TCL, but the System Editor does provide tools that can help you get the job done. 1.             Create, edit and compile your external programs. 2.             Create, edit data items/rows,fields/columns. 3.             Create, edit dictionary items/rows,fields/columns. 4.             Copy rows or data using cut, copy and paste. 5.             Perform multiple tasks by having more than 1 window open.   6.             Execute TCL like commands using RLIST. Example from the EXEC:                 i.           RUN RLIST  LIST myfile WITH CITY EQ  mytown  NAMECITY ,1 Execute system functions/routines. Example from EXEC:                 ii.             RUN COPY_ROW fromfile , keys , tofile , keys ,options, lock                 iii.            RUN DELETE_ROW  myfile , key ,lock                 iv.            RUN myprogram     **Application Execution** Probably one of the most important aspects to your application and to your users is how to execute your application.  Traditionally, your applications have users login to an account with an account name or user id or both.  OpenInsight is no different.  You can create your own login windows or use OpenInsight's standard. Either way the easiest way is to create a shortcut that you can copy to your users desktop to launch your application and provide access.   **Conclusion** As you can tell, there are differences to consider in converting a traditional multivalued application to a GUI environment such as OpenInsight. This paper has tried to at least touch on the majority.  Fortunately, OpenInsight s roots are based on the Pick model, and is either completely compatible in many areas, or is flexible enough to allow you to solve any conversion/migration issue quickly and easily. Good luck!   **//Bruce Cameron is an application developer/consultant with Alternative Systems a consulting firm specializing in database design and development for Multivalue platforms. Mr. Cameron can be reached at (603)433-0014; by email at [[mailto:Info@AlternativeSystems.net|Info@AlternativeSystems.net]].//** **////**   **////**   Revelation 2002 About Revelation Software Founded in 1987, Revelation Software delivers a suite of application development tools and companion services that take full advantage of leading network computing architectures, messaging, groupware, and client server platforms. Today, the company's flagship product is OpenInsight, an advanced repository-based development environment for line-of-business applications. The suite addresses the entire range of workgroup development needs, from power users creating reports to the professional development team creating business-critical applications. Today, there are more than 1.5 million licensed users of Revelation products. The company has offices in Westwood, New Jersey, as well as an Asia Pacific subsidiary in Australia, and __www.revelation.com__,their website.