Author: Eric Lendvai     

Eric Lendvai

×

Bio
Full Stack Web and Desktop App Developer with 30+ years of experience in designing and developing medium to large applications in multiple computer languages.

Website
https://www.linkedin.com/in/ericlendvai/

Country
United States of America

Languages
English, French, Hungarian

Done

The following are instructions for 3rd party applications used for/by this library. The instructions are documented for use under MS Windows.
Please skip any sections if you already have those installed and configured.

Git

Download git from https://git-scm.com/
Install using all default options.

Enhanced Git Prompt in MS Windows (optional)

Instructions to create a better git aware powershell with a colored git prompt that display the branch information and status.

Start Command Prompt "Run as Admin", then >powershell   or "Run As Admin" Powershell
From Powershell execute the following:
>Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy RemoteSigned -Force
>Install-Module posh-git -Scope CurrentUser -Force
>Import-Module posh-git
>Add-PoshGitToProfile -AllHosts
See: https://github.com/dahlbyk/posh-git

Example of an Enhanced Git Prompt

VSCODE and Extensions

Please refer to the article "Developing and Debugging Harbour Programs with VSCODE (Visual Studio Code)"

Amongst all the installation methods, I would recommend using and downloading https://code.visualstudio.com/download

In this case, if you are concerned about monitoring by Microsoft, or automatic updates, simply use the following VSCODE setting:

"telemetry.enableTelemetry": false,
"telemetry.enableCrashReporter": false,
"extensions.autoUpdate":false,

Install at the minimum the following VSCODE extensions:

• "Harbour and xHarbour" By Antonino Perricone
• "Tasks" by actboy168

Harbour and C Compiler
Install Harbour and the C compiler of your choice as per the article "How to Install Harbour on Windows"
I recommend installing Harbour in c:\Harbour and using Mingw64.

The following are the steps I used for installing Harbour in c:\Harbour:

From an Admin Command Prompt:
Created a folder C:\github\Harbour and from there executed
git clone >https://github.com/harbour/core.git
That create a core subfolder (C:\github\Harbour\core) which I copied to c:\harbour

Local PostgreSQL server (optional but recommended)

To install PostgreSQL go to https://www.postgresql.org/download/

At the last step of the install you may want to select to also run "Stack Builder" to install the psqlODBC (64-bit) driver.

Also you can configure pgAdmin to run in Desktop mode.
Check out https://www.pgadmin.org/faq/ the section "What are Server Mode and Desktop Mode?"
See: https://community.wegalvanize.com/s/article/How-to-run-pgAdmin-4-as-native-desktop-app?language=en_US

Any PostgreSQL database using UUID fields

Install the pgcrypto extension to provide support for UUID fields. Use the following command while connected as a superuser.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Local MariaDB server (optional but recommended)
MariaDB is almost equivalent to MySQL and was created as a fork from MySQL but not owned by Oracle. See: https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/
To install MariaDB Server go to https://mariadb.org/download/

The MariaDB server install also give you the option to install HeidiSQL. It is an excellent management tool for MariaDB and MySQL. It can also be used for PostgreSQL, although lack many features of pgAdmin. But one of the nicest features is the ability to export data out of one database into an other empty database across  connections.

Client Connection Library (ODBC)

Currently this library use ODBC as a method to connect to SQL servers (Not MS SQL, but any SQL servers). You only need to install the ODBC Drivers, but do not need to setup a DSN (Data Source Name) entry. ODBC drivers are provided for most OS, including MS Windows, Mac OS and the most popular Linux distros.

Ensure you match your Harbour bit mode, 32-bit or 64-bit. At this point all my development is done using 64-bit. Even PostgreSQL only ships 64-bit mode since version 11.

You will need to specify which ODBC driver name should be used to connect to the SQL server of your choice.

If not specified, "MySQL ODBC 8.0 Unicode Driver" will be used when connecting to MySQL or MariaDB, and "PostgreSQL Unicode" for PostgreSQL.

At this point this library will only support UTF8 (Unicode) encoding, since this covers all spoken languages.

You can find the driver at the following links:

MariaDB and MySQL	For all platforms: https://mariadb.com/downloads/?showall=1&tab=connectors&group=mariadbconnectors&product=ODBC%20connector#connectors
PostgreSQL	For MS Windows: https://www.postgresql.org/ftp/odbc/versions/msi/ For Linux: search you package manager or build from source, https://odbc.postgresql.org/docs/unix-compilation.html

Harbour_ORM Library and dependency

Use Git to download the following two repos:

• https://github.com/EricLendvai/Harbour_VFP.git
• https://github.com/EricLendvai/Harbour_ORM.git

I usually download those in a \github\<AuthorName> folder than copy them to c:\Harbour_VFP and c:\Harbour_ORM respectively, but do not include the ".git" hidden sub-folder.

By doing this technique I can alway re-pull the repos and see what changed before incorporating changes in my other projects.

You could run the following from an Admin Command prompt to automate the process:

C:MD \GitHubMD \GitHub\EricLendvaiCD \GitHub\EricLendvaigit clone https://github.com/EricLendvai/Harbour_VFP.gitgit clone https://github.com/EricLendvai/Harbour_ORM.gitMD \Harbour_VFPMD \Harbour_ORMXCOPY c:\GitHub\EricLendvai\Harbour_VFP c:\Harbour_VFP /sXCOPY c:\GitHub\EricLendvai\Harbour_ORM c:\Harbour_ORM /s

The compilation of this library is best performed from within VSCODE. For that reason, all VSCODE configuration files are also including in the repo.

In regards to the Harbour_ORM library itself, all the source code files needed to compile the library are in the root folder of the repo. The ".vscode" folder includes some of the configuration files for VSCODE.

The "hb_orm.code-workspace" file located in the root folder is the VSCODE workspace file you should use to start VSCODE.

First update any path in the "hb_orm.code-workspace" file and the json files located in "\.vscode" folder to point to the location where you installed the source code of Harbour_VFP and Harbour_ORM.

The compilation process use the hbmk2 Harbour tool. The "BuildLIB.bat" will be called by VSCODE tasks.

In regards to example programs, which demonstrates how to use the Harbour_ORM, they are located in sub-folders of the "Examples" folder. 

Building these example programs are done in the same manner as the library itself, using VSCODE.

Open any "Examples\*\*.code-workspace" file with VSCODE, to open the examples' workspaces. 

Remember to first update any path in the *.code-workspace file and the json files located in "\Examples\*\.vscode" to point to the location where you installed the source code of Harbour_VFP and Harbour_ORM.

Currently in the repos, everything is defaulting to "C:\Harbour_VFP" and "C:\Harbour_ORM"

The compilation process use the hbmk2 Harbour tool. The "\Examples\BuildEXE.bat" will be called by VSCODE tasks.

As per the installation instructions the "Tasks" VSCODE extension will display some buttons to help you compile the library and/or compile/debug/run example programs. 

If your open the "hb_orm.code-workspace" workspace you will be able to compile the library, but not run it. The actual execution of the library must be done via the examples.

If you intend to debug an example program, you may want to also build in debug mode the library itself.

The environment is configured in such manner that debug and release build will be stored in their own folders. Currently all debugging is done when when using the Mingw64 C compiler.

Release build can be done in MSVC64.

If you are opening one of the example workspace files, you will also be able to run the examples.

Debugging your code is so simple with VSCODE. Beside using the VSCODE method to setup breakpoints, you can also create code-based explicit breakpoints with the use of "AltD()" function.

Under MS Windows, you can also send messages to DebugView using the function hb_orm_SendToDebugView(cMessage,[xValue]).

Example of a debugging session:

I would recommend starting DebugView with the following settings and using the leading text "[Harbour]" in your messages:

l_oSQLConnection1 := hb_SQLConnect()with object l_oSQLConnection1    :MySQLEngineConvertIdentifierToLowerCase := .f.    :SetDriver("MariaDB ODBC 3.1 Driver")    :SetBackendType("MariaDB")    :SetUser("root")    :SetPassword("password")    :SetDatabase("test001")    // :SetServer("127.0.0.1")    :SetPrimaryKeyFieldName("key")    l_iSQLHandle := :Connect()    do case    case l_iSQLHandle == 0        ?"Already Connected"    case l_iSQLHandle < 0        ? :GetErrorMessage()    otherwise        ?"connection is",l_iSQLHandle    endcase    ?"MariaDB Get last Handle",:GetHandle()   endl_oSQLConnection2 := hb_SQLConnect("PostgreSQL",,,,"postgres","password","test001","set001")with object l_oSQLConnection2    :PostgreSQLIdentifierCasing := HB_ORM_POSTGRESQL_CASE_SENSITIVE    :PostgreSQLHBORMSchemaName := "MyDataDic"    l_iSQLHandle := :Connect()    do case    case l_iSQLHandle == 0        ?"Already Connected"    case l_iSQLHandle < 0        ? :GetErrorMessage()    otherwise        ?"connection is",l_iSQLHandle    endcase    ?"PostgreSQL Get last Handle",:GetHandle()   end

For the list of parameters and list of methods, please review the file "hb_orm_sqlconnect_class_definition.prg" or the reference section in this article.

The connections are done using ODBC drivers, but do not rely on DSN definitions. All ODBC settings are handle using method calls.

When the :Connect() method of a SQLConnect object is called the property :p_Schema will be initialized with the definition of every tables, fields (columns) and index definition.
This :p_Schema property will help facilitate the auto-casing for tables and fields. This is very useful if you configured the backend server to be case-sensitive.
In your Harbour code you could refer to tables and field in any case since the ORM will adjust the queries and commands to match casing. Due to this feature, you may not define more than one table or field in a table, with the same name in a case-insensitive manner.
For example, if in a table named "Client" you had a column named "DateOfFirstContact", you could refer to that field as "CLIENT.DATEOFFIRSTCONTACT" or "client.dateoffirstcontact" or any other casing.
The :p_Schema property is also used to apply schema migrations (across multiple schema names).

PostgreSQL notes:
The process of loading schema definitions in :p_Schema can take a few seconds for databases with thousands of tables. In PostgreSQL, querying its meta data can be quite slow. For this reason the ORM will cache the definition of all tables and indexes, for all schemas (PostgreSQL Schemas) in the database. A trigger will help monitor structure changes and invalidate the cache. Cache data will be stored in tables named "SchemaCache*".

All ORM support tables, including the structure cache files will be stored in a Schema (PostgreSQL namespace) named "hb_orm". You may set the property :PostgreSQLHBORMSchemaName to any other name. This must be done before calling all :Connect() methods.

Managing deployment of an app that relies on PostgreSQL or MySQL/MariaDB can be tricky. 

You need to ensure your schema version (table structures and indexes) match the application you deploy.

There are several tools that already exists for creating tables and indexes, and updating their structure. Products like Flyway, Liquibase and dbdeploy run using Java or .Net. Most require a fixed sequence of script to migrate schema. Meaning you must change your schema without skipping a particular version.

A long time ago I decided on a few business rules about how to handle data structures. These rules allow me to simplify the process and make my deployments more robust and are the main reason I created a Harbour based migration set of features.

Schema Rules:

• All tables must have a primary key that is an auto-increment integer or big integer.
• An incremental schema version number is recorded with the data, and the application specify the version it needs.
• Fields don't change types.
• Indexes don't change definition. A new index name can be created with a new  index expression.
• Tables and Fields are never deleted via automated migration process (will define that process later).
• Tables and Field are never renamed. For example if a field name has to change, a new field is created with the new name, and data is copied from the old field to the new field. Deleting the old field will block downgrading schema.

The following is the list of steps a migration should have.

Migration Process Steps:

1. Read the current version of the schema.
2. If same as the one the application expect, exit the migration process.
3. Issue an Application Lock to the schema to other users.
4. Compare the current schema definition with what the application needs and generate SQL commands to alter the structure.
5. Apply the generated migration code.
6. Starting from the on-file database version number, start applying data content transformations, like initializing field content or moving data into new fields. Optionally delete old tables and fields, but this will need to prevent rollbacks.
7. As we apply data version specific changes, record the version we completed. Reviewing some actual code will make this step clearer.
8. Remove any Application Locks.

Based on the Schema Rules and Migration Process steps I usually embed the migration process inside my applications. Updating an application will almost always trigger a schema migration. When an application starts, it will always check the schema version of the database is at least the version it expects.

All schema migration functions are implement as methods of a SQLConnect object. For the purpose of this documentation, let's call it l_oSQLConnection1.

The following is a list of methods that can be used to implement all the steps of the previously defined "Migration Process Steps".

1. l_oSQLConnection1:GetSchemaDefinitionVersion(par_cSchemaDefinitionName) . The par_cSchemaDefinitionName can be any text, for example "main". In theory you could split into multiple modules your application, and do the same for schema management. The ORM will maintain a table named "SchemaVersion" to record the current version numbers.

3. l_oSQLConnection1:Lock("SchemaVersion",1). The first parameter is a table name, "SchemaVersion" is a ORM support table, and 1 is a key value. The record does not have to exist, just all the instances of your application should standardize on a key number, and that is the reason I choose 1. It is up to you about how to handle notification to users in case the lock was not granted, and how to tell them that a maintenance is in progress.

4. and 5. Create a variable, for example l_SchemaDefinition1 (as demonstrated in "\Examples\SQL_CRUD\SQL_CRUD.prg") and call l_oSQLConnection1:MigrateSchema(l_SchemaDefinition1). 

This method returns an array {nResult,cSQLScript,cLastError} where:

• nResult could be 0 = Nothing Migrated, 1 = Migrated, -1 = Error Migrating
• cSQLScript is the SQL script that was generated on the fly and actually executed
• cLastError if the script failed, this will have an error text explaining the failure.

6. and 7. Use l_oSQLConnection1:SetSchemaDefinitionVersion(par_cSchemaDefinitionName,par_iVersion) after every version specific transformations. See 1. about par_cSchemaDefinitionName, and par_iVersion will be your new database schema version.

8. l_oSQLConnection1:Unlock("SchemaVersion",1), see 3.

In regards to the :p_Schema property (the one that stores the schema structure of the entire database you connect to), and the parameter you provide to the :MigrateSchema() method, they are very similar. 

The first differences between those two is that :p_Schema covers all tables in the database, and SchemaDefinition parameter may only represent some tables, since more than one :MigrateSchema() can be applied to a database (like program module specific tables).

The second difference is that the :p_Schema property is already specific to the backend you connect to, while the SchemaDefinition parameter may contain definitions specific to a particular backend. For example you may want a certain field or index to only exists in PostgreSQL and not in MySQL, or vice versa.

Structure of a SchemaDefinition variable used by the MigrateSchema method:

In the following explanation I will use a hypothetical variable  l_Schema to describe the structure of SchemaDefinition variables.

First of all l_Schema is a hash array where the keys are the table names. Then each value is a two element array. The first will contain a hash array of field definitions, the second another hash array of index definition or null (NIL) if the table has no indexes. Indexes on the primary key for every tables is not listed as one of the index definitions.

The field definition hash array has the field names as keys and the value can be an array defining the field attributes, or an array of arrays where the first dimension tell the kind of backend this relates too. 

The index definition hash array, if exists, has the index names as keys (name must be unique for current table) and the value is similar in concept to the field definition hash arrays.

I know this can be a little tricky to visualize, so let's review an actual implementation of this l_Schema variable (from the method UpdateORMSupportSchema() class hb_orm_SQLConnect).

At line {{TableSchemaVersion}}  we are defining the ORM support table "SchemaVersion"

Line {{FieldDefinitionArray}} is an example of a field definition that should exist for any backend. Field definition arrays have the following elements:

1. Backend Type. You may use the pre-processor defintions HB_ORM_SCHEMA_MYSQL_OBJECT and HB_ORM_SCHEMA_POSTGRESQL_OBJECT (defined in hb_orm.ch)
2. Field Type. See "Field Type" section in this article for the valid list.
3. Field Length.
4. Field decimal precision.
5. Field Attributes. Optional. String can include more than one attribute. See "Field Type" section in this article for the valid list.

All element of the field definition array must be defined, except that the first one may be null (NIL).

Line {{IndexDefinitionsArray}} is the start of index definitions for the table "SchemaTableNumber".

Index definition arrays have the following elements:

1. Backend Type. (Same as Field Definitions)
2. Index Name (leading table and schema name (PostgreSQL only) and trailing "idx") will be added in the actual database.
3. Is Unique (to enforce unique entries.) (I never had to use this setting before, since I wanted to detect non unique entries.)
4. Algorithm (depend of backends, refer to PostgreSQL/MySQL/MariaDB documentation)

All element of the index definition array must be defined, except that the first one may be null (NIL).

Here is an example of how pgAdmin (PostgreSQL) presents the structure of the table "SchemaTableNumber".

local l_Schema := ;    {"SchemaVersion"=>{"Fields"=>;      {"pk"     =>{, "I",  0,  0,""};      ,"name"   =>{{HB_ORM_SCHEMA_MYSQL_OBJECT     ,"CV",254,  0,},;                   {HB_ORM_SCHEMA_POSTGRESQL_OBJECT, "M",  0,  0,}};      ,"version"=>{, "I",  0,  0,}};      ,"Indexes"=>;      NIL};     ,"SchemaAutoTrimLog" => {"Fields"=>;      {"pk"           =>{                                , "IB",  0,  0,"+"};      ,"eventid"      =>{                                ,  "C",HB_ORM_MAX_EVENTID_SIZE,0,"N"};      ,"datetime"     =>{                                ,"DTZ",  0,  0,};      ,"ip"           =>{                                ,  "C", 43,  0,};      ,"schemaname"   =>{HB_ORM_SCHEMA_POSTGRESQL_OBJECT ,  "M",  0,  0,};         ,"tablename"    =>{{HB_ORM_SCHEMA_MYSQL_OBJECT     , "CV",254,  0,},;                           {HB_ORM_SCHEMA_POSTGRESQL_OBJECT,  "M",  0,  0,}};      ,"recordpk"     =>{                                , "IB",  0,  0,};      ,"fieldname"    =>{{HB_ORM_SCHEMA_MYSQL_OBJECT     , "CV",254,  0,},;                         {HB_ORM_SCHEMA_POSTGRESQL_OBJECT,  "M",  0,  0,}};      ,"fieldtype"    =>{                                ,  "C",  3,  0,};      ,"fieldlen"     =>{                                ,  "I",  0,  0,};      ,"fieldvaluer"  =>{                                ,  "R",  0,  0,"N"};      ,"fieldvaluem"  =>{                                ,  "M",  0,  0,"N"}};      ,"Indexes"=>;      NIL};     ,"SchemaAndDataErrorLog" => {"Fields"=>;      {"pk"           =>{                                , "IB",  0,  0,"+"};      ,"eventid"      =>{                                ,  "C",HB_ORM_MAX_EVENTID_SIZE,0,"N"};      ,"datetime"     =>{                                ,"DTZ",  0,  0,};      ,"ip"           =>{                                ,  "C", 43,  0,};      ,"schemaname"   =>{HB_ORM_SCHEMA_POSTGRESQL_OBJECT ,  "M",  0,  0,"N"};      ,"tablename"    =>{{HB_ORM_SCHEMA_MYSQL_OBJECT     , "CV",254,  0,"N"},;                         {HB_ORM_SCHEMA_POSTGRESQL_OBJECT,  "M",  0,  0,"N"}};      ,"recordpk"     =>{                                , "IB",  0,  0,"N"};      ,"errormessage" =>{                                ,  "M",  0,  0,"N"};      ,"appstack"     =>{                                ,  "M",  0,  0,"N"}};      ,"Indexes"=>;      NIL};     ,"SchemaTableNumber" => {"Fields"=>;   // Used to get a single number for a table name, to be used with pg_advisory_lock()      {"pk"           =>{                                ,  "I",  0,  0,"+"};   // Will never have more than 2**32 tables.      ,"schemaname"   =>{HB_ORM_SCHEMA_POSTGRESQL_OBJECT ,  "M",  0,  0,};      ,"tablename"    =>{{HB_ORM_SCHEMA_MYSQL_OBJECT     , "CV",254,  0,},;                         {HB_ORM_SCHEMA_POSTGRESQL_OBJECT,  "M",  0,  0,}}};      ,"Indexes"=>;      {"schemaname" =>{HB_ORM_SCHEMA_POSTGRESQL_OBJECT,"schemaname",.f.,"BTREE"};         ,"tablename"  =>{                               ,"tablename" ,.f.,"BTREE"}}};    }

This section is only to assist you if you want to install a local PostgreSQL server on your development machine.

The instructions were created on a Windows 10 Pro machine 64-bit.

Download PostgreSQL 13 64-bit (no 32-bit is available) or newer:

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads and use the Windows x86-64 Download button for PostgreSQL 13 or higher.

One of the only suggestions for using different than default settings during install, is to install the actual PostgreSQL data files not in a sub-folder of C:\Program Files\.

Instead of "C:\Program Files\PostgreSQL\13\data" I usually create a folder "C:\PostgreSQL_Data\13" and specify it for the "Data Directory" setting.

The following are screen shots of the PostgreSQL server install and the ODBC driver.