Slurp'n burp

Author: knut.dybendahl@gmail.com (Knut)

In many ways, this 'old' term is something I've always loved about Uniface; Point an empty Uniface repository (model wise) to one of many RDBMS's - and hey presto, Uniface 'slurps' in the table definitions, fields and keys and 'burps' those said definitions into the model. Unfortunately, there isn't such a feature for MySQL - whilst there is one available for PostgrSQL.... Short of attempting to export / convert a couple of hundred tables to MS-SQL or Oracle - and then 'slurp' from there - is there anybody out there who have any other method easily available? To the lab - are there any plans of adding this to the MySQL driver? Here's to hoping... Knut

16 Comments

  1. Hi Knut, I would just use the case load (CIF) functionality. The content of the CIF file can be constructed from the metadata of the database with just some simple SQL statements. Then use the Administration -- Exchange Models -- CASE Load The CIF is documented only in some older uniface versions (7.204 as CBC.PDF). In 2013 I raised a Call: "request documentation of the CIF file (Case Bridge) according to the 9.6 version" but heard that this "legacy" functionality used by only a few customers will be removed soon. Greetings from Frankfurt/Germay, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  2. Hi Ulrich, Yes, I looked at the cif file method - the issue is more from a desire to have a 'point-n-shoot' option - using a cif approach would make me responsible for the datatype mappings et al... Since I don't know what Uniface is doing internally, I'd rather not be the scapegoat if/when the mapping is wrong. .... :-)


    Author: Knut (knut.dybendahl@gmail.com)
  3. Hi Knut, in each database connector there is a documentation with mappings from native to uniface. So it looks like you can trust the "MySQL Data Packing" from the helpfile.   And CIF is just a way to import Models just like you would enter it in the IDF: So field "A23", datatype "STRING", interface "C(12)" should be faminiar.   All you have to do is to convert the metadata from the database (columns, keys, tables) to CIF which is a one time effort; And if you find out that some mapping needs a correction, you can do this with CIF as well without loosing other changes (!!!). In 2013 I created a Domain Specific Language to generate the CIF files with an oustanding productivity: datamodel fast_taric "first DSL tutorial" entity TBL_Abg_Art "TBL_Abg_Art [T23000]" { field ABG_SATZ_KOMP_ID "Abgabenart-Id (TARIC2)" format=CHAR(2) field DAT_START "Datum Gültigkeitsbeginn (TARIC2)" format=DATE field DAT_END "Datum Gültigkeitsende (TARIC2)" format=DATE field ABG_SATZ_KOMP_ANW_CODE "Code der Anwendbarkeit eines Abgabensatzes (TARIC2)" format=CHAR(1) field MASS_EINH_ANW_CODE "Anwendbarer Code der Maßeinheit (TARIC2)" format=CHAR(1) field WAEHR_EINH_ANW_CODE "Anwendbarer Code der Währungseinheit (TARIC2)" format=CHAR(1) field AKRONYM "Abkürzung (TARIC2)" format=CHAR(5) field KURZ_BESCHR "Kurztext (TARIC2)" format=VARCHAR2(100) keyfields="ABG_SATZ_KOMP_ID" } entity TBL_Besch "TBL_Besch [T20500]" { field BESCH_ART_CODE "Bescheinigungsartcode (TARIC2)" format=CHAR(1) field BESCH_KONTR_NR "Bescheinigungsreferenznummer (TARIC2)" format=CHAR(3) field DAT_START "Datum Gültigkeitsbeginn (TARIC2)" format=DATE field DAT_END "Datum Gültigkeitsende (TARIC2)" format=DATE field KURZ_BESCHR "Kurztext (TARIC2)" format=VARCHAR2(100) keyfields="BESCH_ART_CODE,BESCH_KONTR_NR" }


    Author: ulrich-merkel (ulrichmerkel@web.de)
  4. To be quite honest, the lack of load defs in the MySQL driver came as a bit of a surprise to me, it only hit my radar when we started looking into how we'll handle it in Uniface 10. I think that we introduced the driver in the Uniface 8.4 era, it was one of my projects the first time I was PM, but it was to far back to remember any discussions about load defs. The fact that is doesn't seem to have come up elsewhere says something.  I made sure it was included in both the PostgreSQL and the SAP Hana drivers which are the drivers delivered since the MySQL driver.  Knut, if this is important to you in Uniface 9, we should discuss it. It should make it into Uniface 10,it won't be in the September release, but afterwards. At the moment the functionality is entirely Uniface code (in the UDE/IDE) and obviously varies from database to database because the system tables vary from database to database. Without getting into a lot of details of driver architectures, we need to take a look if we're really optimal on that.  Note: revised text, I was originally incorrect that the current functionality is both in the UDE and in the driver. And I've made it clearer that it won't be in the Sept release of u10. 


    Author: Adrian Gosbell (adrian.gosbell@synapse-i.jp)
  5. I can only confirm that 'Load Definitions' (or slurp and burp as you call it) works fine for Uniface 9.7.01 in combination with PostgreSQL 9.4, as I have done the exact thing (pointing repository on new empty PostgreSQL) just last friday. I have not (yet?) used MySQL. As a workaround for you I'm thinking of:

    • Do the slurp 'n burp on Uniface with any other open source database, i.e. PostgreSQL, or the standard SQLite installation (version 9.7)
    • Do the /ins meta to get the DICT model in your empty repository.
    • Export the entire repository to xml data files using Utilities\Depoyment\Convert Data. I'm afraid this will be a table by table exercise, unless some of the other forum experts knows a trick
    • Use the 'mql2x..' files from the Uniface installation directory (\uniface\sql\) to create the MySQL repository.
    • Reassign your IDF to the newly created MySQL repository by changing your ASN file(s).
    • Import the repository using Utilities\Depoyment\Convert Data. Perhaps merging your separate XML files into one big XML file will work here (and will save you the hassle of the table-by-table exercise).

    Overlooking all of these steps I do get your point of asking for a 'Load Defaults' on MySQL Wink


    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
  6. Hi Adrian, Arjen , Ulrich - Appreciate the time you've taken to answer my question - so I think what I'll attempt to do is this (for this project at least); 1) Extract the DDL from MySQL and export to a file - or use a MSSQL convert utility. 2) Run that script against MS-SQL - and see what happens! 3) Import the MS-SQL DDL (slurp) into Uniface. 4) Use the MySQL create table utility in Uniface 5) Compare the output from Uniface with what MySQL gave me. 6) Change / tweak the Uniface definitions until Uniface output matches MySQL. Not too hard or too difficult - will take a few hours to do - but hey.... Look forward to Uniface 10... Smile Knut


    Author: Knut (knut.dybendahl@gmail.com)
  7. You're welcome Knut  Smile In case you'd still like to use any part of my plan: - Don't forget to use the IDF: path (not the DEF: path) - Define redirection of your *.xml files in your ASN file for convienience - Use the command line option for copying out: (/cpy idf:*.dict xml:) to prevent the plan turning into a table-by-table exercise. - Use the command line option for copying in: (/cpy xml:*.xml idf:) to prevent the plan turning into a table-by-table exercise. Comparing my plan with your plan I think: - My plan will cost extra time on setting up a database that you don't want in the end (SQLite or PostgreSQL) - My plan will save time on not having to change/tweak any Uniface definitions. The larger your number of tables, the more my plan will save time, I think Not that it's a competition, it's just an alternative Laugh Best Regards, Arjen


    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
  8. IIRC, when we investigated in this database hopping, there was an issue that we had to buy a license for that additional database.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  9. Hi Uli, Being a solution consultant (sales engineer) for Uniface I sometimes forget that I'm always working with 'full size' license files Embarassed. You're rigth indeed: PostgreSQL driver is not included in the standard package, but SQLite driver is standard for 9.7 and the higher 9.6 versions. Cheers, Arjen


    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
  10. In my first answer in this topic where I mention 'Load definitions' in the first line, I should have said 'Load defaults', because that's what we're talking about, aren't we? Furthermore, by chatting to a Customer Support collegue over lunch, he was baffled by this. His suggestion is to first check if your usys:*.xml setting is defined right in your ASN file(s), for exemple: [FILES] usys:*.xml          usysadm:..\misc\*.xml


    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
  11. Hey Knut, I've been reminded by Henk that burp and slurp is old time for filedump and fileload!  Actually in my head, I thought it was related to those old txt file im/out superkey combinations from the old CHUI days. But I'm not the oldest timer in Uniface land! 


    Author: Adrian Gosbell (adrian.gosbell@synapse-i.jp)
  12. Adrian Gosbell said Hey Knut, I've been reminded by Henk that burp and slurp is old time for filedump and fileload!  Actually in my head, I thought it was related to those old txt file im/out superkey combinations from the old CHUI days. But I'm not the oldest timer in Uniface land! 

    It's not on topic for Load Defs, but Adrian is equally correct: Uniface macros like ^SAVE, ^INS_FILE and ^REM_FILE used to be mapped to keys/key combinations in the CHUI days (the VT100 keyboard translation tables etc.). Those macros would perform actions similar to the proc statements file_load and file_dump.  Colloquially, in the Lab and probably in the Uniface courses of those days, these actions were called slurp for 'load' and burp for 'dump'. I don't think these terms ever made it to the User Manual (1 manual!) back then. So much for memories of the old days ;-) 


    Author: Henk van der Veer (henk.van.der.veer@uniface.com)
  13. Funny how all of these memories come back - I learned the phrase from non other than Roy Franzen - it might have been around files - but stuck when the DB drivers started to 'slurp' as well ...Laugh


    Author: Knut (knut.dybendahl@gmail.com)
  14. I just noticed that this thread is posted in the 'Uniface Jobs' section of the forum Confused ?


    Author: Arjen van Vliet (arjen.van.vliet@uniface.com)
  15. Arjen van Vliet said I just noticed that this thread is posted in the 'Uniface Jobs' section of the forum Confused ?

    I've moved the topic now to 'Uniface Development'.


    Author: diseli (daniel.iseli@uniface.com)
  16. ulrich-merkel said Hi Knut, I would just use the case load (CIF) functionality. ...

    Over the weekend, I just constructed a basic prototype CIF generator for a MySQL "load definition". Looks promising. It takes only free-of-charge tools like "Toad for MySQL" and (M)AWK.EXE and does not require any database hopping. But as I neither own a uniface mysql license nor have some representative database structures which awaiting loading, I could not verify the transformation is correct and not proof that "the usual spectrum of datatypes" are covered. So if someone with a license and some (or a lot of) databases is interested in a cooperation, please contact me. Greetings from a sunny Frankfurt/Germany morning, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)