Tip: Using Database MetaData To Develop

[HankFay]

I had some interesting USE CASE's to solve. Database MetaData proved to be the answer.

For ease of use I created these in the Database MetaData Editor, but any way they are created and applied to the database would have obtained the same result. Alter table modify constraint <column> metadata <metadata string> would do the same job.

What I show below is the metadata I created.

1) I had a caption for a field, nAcctBalance, that had to change based on the value of another field, lHasBalance

I first accessed the MetaData Editor for the nAcctBalance field by doing to the Data section, selecting the table and then the field, and then selecting MetaData Editor from the "gear" menu for the fields dialog.

formitem.caption=[lHasBalance]Balance,Balance Not Used

In the editor the 3 entries would be (showing | to indicate the sections):

formitem.section | lHasBalance | Balance,Balance Not Used

2) the same field, nAcctBalance, which had to be made not Editable when lHasBalance was .T.

formitem.editable | lHasBalance |TRUE,FALSE

Notice the use of oData-style .T. and .F.

3) make the data of nAcctBalance invisible when Balance Not Used

This could have been fancier, but works here:

formitem.dataForeColor | lHasBalance | Black,White

Refreshing the app shows the MetaData effects.

The beauty of this approach is that conditionals can't be used in the attribute inspector but can in Metadata.

And it refreshes instantly (when lHasBalance is checked or unchecked), even when adding a record and changing the value of lHasBalance. No refresh is need for the conditionals to apply.

This is astounding, really, beyond belief. I have focused on working with Metadata for 21 years and never dreamed of anything this powerful.

In our VFP app, we end up writing methods to run on a Refresh to make these kinds of things happen. We can handle of number of things dynamically, but here we have the ability to use it for every attribute the object has.

This is magic!

I hope this gets you started in using an incredibly powerful tool in Lianja

Hank

[josipradnik]

Thank you Hank for your tip.

You know that I like images. So...
I simulated (approximately) your tip on southwind's table PRODUCTS we all have .

PRODUCTS.UNITINSTOCK = nAcctBalance
PRODUCTS.DISCONTINUED = lHasBalance

Data workspace | Southwind | Products | Units In Stock
5th icon below (hand writing) of Column Names:



Then app named f3839_MetaData with Southwind | Form section | drag fields from PRODUCTS table: Product Name, Discontinued and Units In Stock

run



Moving thru records.
When Discontinued=False (No), caption of Units in Stock shows "Not discontinued" , red and disabled.
When Doscontinued=True (Yes), caption of Units in Stock shows "Discontinued" , black and enabled.

(I have no idea why section changes its backcolor while browsing records, but don't care)
Here is attached the app for anyone interested in.


Josip

[LeonMc]

Hi Josip/Hank

Thank you for the tip. It helps a lot. I love and understand the concept of metadata am but struggling with the syntax.

The reason for the red background is the meta data applied to the products table..e.g. if supplierid = 1 backcolor = red

hth

Leon

[HankFay]

Oops, I misunderstood Leon was finding the problem in Josip's app.

In Lily Tomlin's immortal words, "never mind."

[barrymavin]

Worth noting is that metadata is applied progessively in the same order as it is defined. This provides the ability to affect the same attribute based on different progressive conditions

[josipradnik]

Hi Leon,

Thanx for solving the background mystery.
I forgot that I was playing with metadata before in very same table, so it left here.
I did not look at table level when I managed column level to simulate Hank's tip.

Obviously, you have downloaded my app (sorry, my app polluted your version of southwind PRODUCTS table I included in lpk).
Without your download and detective work, it will stay a mystery for me.
Nice team work.

Josip

[LeonMc]

Hi Josip

I read this forum religiously every morning and have learnt so much. You guys are the real stars so happy to finally contribute in a small way.

What this has highlighted for me is that with the power of metadata one could end up with unexpected results, esp in a multi developer environment.

Barry's note on "applied progressively" is well worth remembering.

Leon

[HankFay]

Hi Leon,

Good point. So you can't willy-nilly just add metadata in using code. You have to look at what's there first.

This also raises the issue of version control. Using GIT, one would have to output the encoded JSON for a table and for a field in a table to a file, and push that to the branch on which one is working. As I think about it (thanks to your connecting the dots) I will create a directory under the application, and modify the files turning each ; into a crlf, so that each entry is on its own separate line. Reverting would then consist of changing crlf to ; and applying the metadata.

Anyone see an easier way?

Hank

[avianmanagement]

Hi Hank,

How about getting it filled from Xcase with the rest of the data structure. That way there would juts be one place for it to be altered by any developers.

yes I know you will see this as another excuse for me to get you to do Xcase to Lianja , which is is of course

[HankFay]

Hi David,

xCase to MM (metadata manager) is under construction, as time permits. Which isn't much, but stuff is getting done. The big issue, which I finally solved, was how to make the import not entirely written in code. A few ways of doing that proved to be clunky at best (that's been most of the delay), but about 6 or 8 weeks ago I found an elegant (as in: simple, powerful) solution, which is currently being implemented.

My more immediate need is to be able to efficiently create and maintain MetaData information, and test it, while in the UI. I have a real-world need, for one, and this will give me experience I need to integrate it with the man PSP MM app. Here are my requirements:

1. select a control
2. view the metadata already associated with that control
3. sort the order of the metadata
4. add/update/delete metadata
5. when adding, find an attribute by one of:
   
   1. typing in letters that do a $ match, presenting a list to select from
   2. picking by Category of attribute as reflected in the attribute wiki doc, and then picking an attribute
   
   
6. when adding or updating, see the "description" from the docs
7. not have to type in all the wiki information, and be able to update it easily
8. save the changed/new metadata to the DB/Table/Field
9. apply it to the section
10. create/replace a file in a folder underneath the app that describes the resulting metadata (basically, substitute a crlf for the separating semi-colon) for the database/table/field and stage/commit/push to the current GIT branch, so that anyone else working on the branch will have this metadata available, and it's in a format for easy viewing/merging.

How to do all the above is worked out, including the xCase model, and I'm in the process of implementing.

This "PSP DB Metadata Manager" will be part of the PSP Metadata Manager in some form or other. Probably a separate app, hitting the "local" cloud server, to interact with the currently open application.

And yes, Tables and VT's will be created/maintained from the PSP MM. That has been the plan all along. I've thought of (and in bouts of impatience started, multiple times) short-circuiting, but writing all the code to just toss it away when things are done the right way doesn't make sense.

The PSP MM will be a deployed app also, like the PSP DB Metadata Manager described above. The real world needs I have include being able to write an MSSQL query and test it out in real time, informed by the relations available, but giving complete flexibility (for complex queries we often do JOIN SELECT's, which xCase does not handle natively). There are other requirements having to do with things find-list definitions, lookup expressions, etc., where a change in fieldname, or a field dropping out, etc., throws a spanner in the works. That has to be a) handled and b) integrated into Metadata. There are many options to consider: should the lookup cursor be generated by an oData call, or by a VFP call (using exports.conf) to run VFP code on the server and then return as a resultset (which will come back to the web/mobile app as oData)? So the MM data structure has to hold that information, and the code has to populate exports.conf if needed, etc. Many options to consider. I will start small, and add more as I go on.

The great part is that (with V5.0) all the parts we need are there. In a way they are not in every other development stack I have investigated. Anywhere else, we would have to write all the parts built into Lianja. And a few years later (I've seen it happen) you would still be dealing with how to make the little things work.

More than you wanted to know. Helps me think about where I am and where I'm going with this stuff.

While writing this, my thoughts came clear on using GIT with Lianja, also. That will be another deployed app, it turns out. With a couple of simple caveats, it will be all button pushing. Normal users will only need a source compare/merge tool. Did you know that Beyond Compare now runs on Win/Mac/Linux? Someone in every org should have GitKraken, however, to handle any screwups.

Hank

PS: for anyone wanting to integrate programmatically with Git, you'll want this: http://www.nodegit.org/ Notice that the GitKraken team is a key contributor: whatta ya wanna bet GitKraken uses this. Yes, you can shell out to the git exe, but the control offered by the node integration with the key git dll is much greater: you get a return value. Node is already available in Lianja of course.