Meta-Data

Meta-data is information about the database tables and columns. The StudioWorks Meta-Data Editor is used to edit and view the meta-data.

Meta-data is stored with each SQL class (schema or query class) and with each schema or query class column. The meta-data is stored in a row variable which is copied to the $userinfo property of the respect schema or query class, or the schema class column.

Much of what you need to know about meta-data is in tooltips of the various fields in the SQL Meta-Data Editor.

Admin Column Types

There are several types of admin columns which you can specify in the meta-data.

1. editnum - This column is used for optimistic record locking. The EditNum is incremented +1 each time the records is updated in the database. If the old record EditNum does not match the current EditNum of the record in the database, it means another user has updated the record. The update is aborted.
2. insby - This column is used store the primary key of the user which inserted the record.
3. insdatetime - This column is used store the date and time which the record was inserted.
4. modby - This column is used store the primary key of the user which last updated the record.
5. moddatetime - This column is used store the date and time which the record was last updated.
6. endby - This column is used store the primary key of the user which ended the record.
7. enddatetime - This column is used store the date and time which the record was ended.
8. active - This column is a 0 or 1 flag to indicate whether or not the record is active.

All of the above admin columns are optional. You can use as many or as few of the above columns as suits your requirements. It is strongly recommended that you include an editnum column in all of your schema classes.

I tend to use the editnum, insby, insdatetime, modby, moddatetime columns in most of my servertables.

I use the active column for most my tables. If a record is no longer actively used (discontinued inventory items, terminated employees) the user can set active to false. Inactive records don't show up in lookup droplists until the user clicks the Inactive button. If an active column is in the schema class, the fethallwhere in the schema class meta-data defaults to WHERE tablename.Active = 1, thereby reducing the intial list of records in the list to active records only.

Shift+click on the Find button in any list window to fetch all of the records. If there is an Active column in the headed list you can select any of the records on the list and then right-click on the selected records in the list. A menu item Toggle Active/Inactive for Selected Line(s) will appear in the context menu. Selecting this item will toggle the value of the active admin column for all of the selected lines. This saves the user from having to jump back and forth editing each individual record just make old records inactive.

Custom Meta-Data

Developers can add their own custom meta-data within the StudioWorks framework. The meta-data for each class and each column has a special row variable column named custominforow. You can defined this row variable with up to 400 columns of additional meta-data and within those 400 columns you can row and list columns so the number of additional meta-data columns is very large.

You will notice in the SQL Meta-Data Editor, within each meta-data tab, a tab strip in the window with a tab named Custom Info Row. It is here that you can add your own custom meta-data fields.

When you look at under the Custom Info Row tab you will see a subwindow with some instructions and a Copy button. If you click the Copy button StudioWorks will copy the subwindow window class from swBase4 to you main library. It will also copy the _listdef schema class used to define the custominforow to your main library.

There are 2 separate window classes and 2 separate schema classes that you can copy to your main library.

1. wSQLMetaDataClassEditorCustomInfoRow is the window class for editing your class meta-data. You have one custominforow for each SQL class.
2. wSQLMetaDataColsEditorCustomInfoRow is the window class for editing your columns meta-data. You have one custominforow for each column of each SQL class.
3. sSQLMetaDataClassCustomInfoRow_listdef is the schema class for defining the custominforow of your class meta-data.
4. sSQLMetaDataColsCustomInfoRow_listdef is the schema class for defining the custominforow of your columns meta-data.

Once the classes are in your main library you can modify them to suit whatever your heart desires.

You add a column to the applicable _listdef schema class, then add a matching label and field to the applicable window class.

After you close and reopen the SQL Meta-Data Editor your revised window class should appear under applicable the Custom Info Row tab and you should be able to enter meta-data. Be sure to test that your meta-data is actually saved.

You can add columns and fields any time you like. Your custominforow is automatically redefined when you edit the meta-data and existing data is copied to the new row.

You can access your meta-data through the oSQLLists object what is instantiated by the lsts tvar.

If you want to get the custominforow for a SQL class you would do the following:

; Get the meta-data custominforow for the specified SQL class.
Do lsts.$retSQLClassCustomInfoRow(SQLClassName) Returns CustomInfoRow
If CustomInfoRow.$colcount=0
   ; An error occurred.
   Calculate FlagOK as kFalse
Else
   
   Calculate MetaDataValue as CustomInfoRow.colname
   ; Continue with your code.
   
End If

If you want to get the custominforow for a column you would do the following:

; Get the meta-data custominforow for the specified SQL class column.
Do lsts.$retSQLColCustomInfoRow(SQLClassName,ColName) Returns CustomInfoRow
If CustomInfoRow.$colcount=0
   ; An error occurred.
   Calculate FlagOK as kFalse
Else
   
   Calculate MetaDataValue as CustomInfoRow.colname
   ; Continue with your code.
   
End If

StudioWorks Documentation - Copyright 2005 Vencor Software