Board index FiveWin for Harbour/xHarbour Harbour ORM Reference Manual (HDBC)

Posts: 817

Joined: Sun Jun 15, 2008 07:47 PM

Harbour ORM Reference Manual (HDBC)
Posted: Sat Mar 14, 2026 12:01 PM

Harbour ORM Reference Manual (HDBC)

Welcome, colleague. This manual details the mechanics of the ORM (Object-Relational Mapping) layer included in the HDBC ecosystem, inspired by the Active Record and modern Query Builders patterns. This ORM provides an elegant, object-oriented, and fluid interface to interact with your databases, abstracting pure SQL statements and allowing you to manipulate records exactly as if they were native Harbour objects.

---

Index

Setup and Fundamentals
THModel (Active Record and Query Builder)
THCollection (Data Collections)
Schema Builder (Table Creation)
Grammar Resolution (THGrammar)

---

1. Setup and Fundamentals

To use the ORM, you must have an active connection originated from the core in hxdbc (usually a THDBC object). The ORM relies on a default Thread Static connection being set globally per thread before invoking models.

Initial Binding:

Code (harbour): Select all Collapse

#include "hdbc.ch"

// 1. Create your standard C++ connection
LOCAL oDb := THDbc():new( HDBC_DRIVER_SQLITE )
oDb:connect( "database=app.db" )

// 2. Globally mount the connection to the ORM core
THModel():setConnection( oDb )

By doing this, any class inheriting from THModel will know exactly where to route its operations.

---

2. THModel (Active Record and Query Builder)

The THModel class is the heart of the system. It implements both model-level properties (Active Record) and command chaining methods (Query Builder).

To create a custom model, simply inherit from THModel:

Code (harbour): Select all Collapse

// model_user.prg
#include "hbclass.ch"

CLASS User FROM THModel
   // Override config (Optional, ORM assumes conventions otherwise)
   DATA table_name INIT "users"
   DATA primary_key INIT "id"
   DATA timestamps INIT .T.       // Automatically manages created_at and updated_at
   DATA useSoftDeletes INIT .F.   // Employs deleted_at instead of physical dropping
ENDCLASS

2.1 Column Access and CRUD Operations (Active Record)

Interact with a single record by materializing the remote database row onto the target instance object.

Mapping Attributes as Physical Object Properties
Row boundaries are primarily deposited underneath the target's internal dictionary mapping payload, accessible conventionally through getAttribute("field"). Nonetheless, enforcing cleaner and strictly oriented object-oriented paradigms ("Pure OOP"), you are encouraged to explicitly state your table fields at the class header layout relying on DATA instructions. This seamlessly correlates native Harbour instantiated fields facing the fetched background tuple empowering elegant read & write manipulation through direct pointer-syntax (:):

Code (harbour): Select all Collapse

CLASS Category FROM THModel
   DATA table_name INIT "categories"
   
   // Explicit mapping = Fluent OOP `oCat:name` property access
   DATA id, name, description 
ENDCLASS

Find by ID (find) & Attribute Reading

Code (harbour): Select all Collapse

LOCAL oCat := Category():find( 1 )
IF oCat != nil
   // Formally routed 
   ? "ID:", oCat:getAttribute( "id" )
   
   // Fluid property mapping! (Requires assigning `DATA name` on class creation)
   ? "Name:", oCat:name
   ? "Details:", oCat:description
ENDIF

Creation (create / save)
You can insert data manually via assignments or massively (fill()).

Code (harbour): Select all Collapse

// Option A: Mass-assignment (returns the successfully saved object)
LOCAL oUser := User():create( { "name" => "Manu", "email" => "manu@test.com" } )

// Option B: Instantiate and save manually
LOCAL oNew := User():new()
oNew:setAttribute( "name", "Pepe" )
oNew:save()

Updating (update / save)

Code (harbour): Select all Collapse

LOCAL oUser := User():find( 5 )
oUser:setAttribute( "name", "Jose" )
oUser:save() // Triggers the UPDATE command automatically

Deletion (delete)

Code (harbour): Select all Collapse

LOCAL oUser := User():find( 5 )
oUser:delete() // Physical drop or Logical hide if useSoftDeletes is .T.

2.2 Query Builder (Chained Queries)

Leverage fluid syntax to synthesize complex filters without littering your prg code with raw textual SQL strings. They will reliably output THCollection type objects.

Multiple Selections (where, whereIn, get)

Code (harbour): Select all Collapse

// All active users
LOCAL oActives := User():where( "status", "=", "ACTIVE" ):get()

// Array batch retrieval
LOCAL oVIPS := User():whereIn( "role_id", {1, 2} ):order( "name DESC" ):get()

Performance Tip (mutable): By convention, chainable queries proactively clone the base object whenever you route a clause (where()). If your chaining gets significantly long, enable the mutable path:
User():mutable(.T.):where(...):orderBy(...):limit(10):get()

Groupings and Offsets

Code (harbour): Select all Collapse

LOCAL oStats := User():select( "role_id", "COUNT(*) as total" ) ;
                      :groupBy( "role_id" ) ;
                      :having( "total", ">", 5 ) ;
                      :limit( 10 ) ;
                      :get()

Obtain a single match (first)

Code (harbour): Select all Collapse

LOCAL oPrime := User():where( "score", ">", 1000 ):first()

Numeric Aggregators

Code (harbour): Select all Collapse

LOCAL nMedia := User():where( "status", "=", 1 ):avg( "edad" )
LOCAL nTotal := User():count()

2.3 Utilities and Upsert

upsert( hMatch, hValues ): Highly convenient. Attempts an update if the hMatch conditions score a hit; otherwise, injects a new row seamlessly combining the payload of both Hashes under the hood.
firstOrCreate( hMatch, hValues ) and firstOrNew( hMatch, hValues ): Pinpoints a record. If unfound, returns it fully persisted (OrCreate) or solely as a temporary memory instance (OrNew).
pluck( cCol ): If you only desire an elemental native array {"Pepe", "Manu"}, you may skip heavy object hydration:
Code (harbour): Select all Collapse
```
  LOCAL aNames := User():where("age",">",18):pluck("name")
```

3. Relationships and Advanced Model Features

The ORM truly excels when it comes to untangling scattered records (Data Relationships) and regulating the lifetime behavior span through automated events.

3.1 Relationships

You may link varying models inherently sidestepping tedious bare-metal SQL JOIN constraints. Every standard correlation modality is firmly integrated (Has One, Has Many, Belongs To, Belongs To Many).

Code (harbour): Select all Collapse

CLASS User FROM THModel
   // ...
   METHOD profile()
   METHOD posts()
   METHOD roles()
ENDCLASS

// 1 to 1 Mapping
METHOD profile() CLASS User ; return ::hasOne( "Profile", "user_id" )

// 1 to N Mapping
METHOD posts() CLASS User ; return ::hasMany( "Post", "user_id" )

// N to N (Relies on an intermediate pivot juncture table)
METHOD roles() CLASS User ; return ::belongsToMany( "Role", "role_user", "user_id", "role_id" )

Executing Relationships:

Code (harbour): Select all Collapse

LOCAL oUser := User():find(5)

// Lazy Loading (Fetched from DB exclusively upon invocation)
? "Profile bio:", oUser:profile:first():bio
? "Amounts of written posts:", oUser:posts:count()

// Eager Loading (Optimal pre-hydration technique to counter N+1 query struggles)
LOCAL oUsers := User():with("posts"):all()
oUsers:each({|u| ? u:name, u:posts:count() })

3.2 Accessors (Read Mutators)

Accessors grant you the power to dynamically skew or reformat raw physical database attributes whenever evaluated by assembling methods following the get[Column]Attribute nomenclature protocol:

Code (harbour): Select all Collapse

CLASS User FROM THModel
   // ...
   METHOD getNameAttribute( cName )
ENDCLASS

METHOD getNameAttribute( cName ) CLASS User 
   return Upper( cName ) + " (Registered)"

3.3 Observers

Intercept a model entity life cycle queuing backend-managed triggers written fully in Harbour logic, such as saving or saved.

Code (harbour): Select all Collapse

// First, declare an observer class grouping the logic
CLASS UserObserver
   METHOD saving( oModel )
ENDCLASS

PROCEDURE saving( oModel ) CLASS UserObserver
   ? "About to endure persistence round for:", oModel:name
return

// Engage it anywhere on your workflow tree:
User():observe( "UserObserver" )

LOCAL oU := User():new()
oU:name := "Test"
oU:save() // <- Will instantly fire: "About to endure persistence round for: Test"

3.4 Scopes (Reusable Query Boundaries)

Whenever dealing repeatedly regarding recursive logic conditionals (ex: Gathering Users assigned exclusively to admin tiers, or items presently in-stock), enclose them seamlessly behind models exploiting the prefix scope:

Code (harbour): Select all Collapse

CLASS User FROM THModel
   // ...
   METHOD scopeActives( oQuery )
ENDCLASS

METHOD scopeActives( oQuery ) CLASS User ; return oQuery:where( "status", "=", 1 )

// Implementation string:
LOCAL oActives := User():scope( "actives" ):order("id DESC"):get()

3.5 Pagination & Chunking

Resist inflating memory boundaries calling get() indiscriminately upon humongous +100k matrices; instead, paginate arrays evenly to output towards Grids or segment massive background processes cleanly.

Code (harbour): Select all Collapse

// Traditional Pagination (Splendid match facing API routes)
LOCAL oPager := User():paginate( 50, 1 ) // (ItemsPerPage, TargetPageOffset)
? "Total pages calculated:", oPager["total_pages"]
? "Matrix length per iteration:", Len( oPager["data"] )

// Memory-Friendly Chunking iteration (Ideal rendering heavy background scripts)
User():chunk( 1000, {|oSplittedChunk| ;
   oSplittedChunk:each({|oU| oU:slowMailerDeliveryMethod() }), ;
   .T. ; // Issue .F. manually whenever wanting to halt chunk distribution loop
})

---

4. THCollection (Data Collections)

The mass yield of a Query Builder execution (->get()) isn't a raw barebone Array, it is a multi-purpose matrix wrapper object named THCollection built to ease iterations and cross-formatting (like JSON conversions).

Code (harbour): Select all Collapse

LOCAL oUsersColl := User():where("status", "=", 1):get()

? "Records found:", oUsersColl:count()

// The class is completely compatible with FOR EACH standard loops
LOCAL oUser
FOR EACH oUser IN oUsersColl
   ? oUser:getAttribute("email")
NEXT

Integral Functional Collection Methods

each( bBlock ): Transpiles the CodeBlock execution pointing directly against the wrapped element instance.
map( bBlock ): Transforms the totality emitting a brand new modified THCollection array.
filter( bBlock ): Helpful for distilling locally downloaded structures evaluating .T. / .F. blocks.
isEmpty() and first().
toJson(): Fundamental feature for web-based services. Compiles a serialized String layout encompassing the global arrays into uniform entities instantly. It's essentially what you return to clients out of a Rest endpoint.

---

4. Schema Builder (Table Creation & Migration)

HDBC offers DDL abstractions relying on THSchema. It permits programming architectural migrations agnostic-wise avoiding hand-to-hand native differences regarding dialects corresponding to SQLite, MySQL, or PostgreSQL constraints schemas.

Table creation is accomplished by passing a codeblock to the create method. This block receives a "blueprint" object representing the new table, which you will use to define its columns:

Code (harbour): Select all Collapse

#include "hdbc.ch"

// On your boot configuration / migration files:
THSchema():create( "users", {| table | ;
    table:id(), ;                                    // Generates Auto_Increment integer Primary Key
    table:string("email", 120), ;                    // Capped length Varchar (120 chars)
    table:string("password", 255), ;                 // Varchar (255 chars)
    table:boolean("active"), ;                       // Truthy Booleans
    table:decimal("balance", 10, 2), ;               // Exact Monetary formats (Precision 10, Scale 2)
    table:text("bio"), ;                             // Unlimited bulk text / Memo
    table:json("config"), ;                          // Special JSON column
    table:timestamps() ;                             // Injects `created_at` and `updated_at` (Datetimes)
} )

The localized table argument supplied to your trailing CodeBlock stands functionally as an internal THBlueprint/THField factory. Adhering towards whatever core Grammar you currently rely upon, constraints transform physically into their top optimally correlated base-type representations (Ex: A boolean instruction becomes an embedded INTEGER 1/0 provided your target dialect doesn't grasp innate logic bit flags, like SQLite).

Explicit Object-Oriented Table Creation

If you prefer not to use codeblocks (closures), or if your application requires assembling the architecture dynamically element by element during runtime, you can instantiate THSchema directly and append fields strictly relying on the THField entity factory:

Code (harbour): Select all Collapse

// You may optionally drop the structure beforehand
THSchema():new( "products" ):dropIfExists() 

LOCAL oSchema := THSchema():new( "products" )
oSchema:add( THField():id() )
oSchema:add( THField():string( "name", 150 ) )
oSchema:add( THField():decimal( "price", 10, 2 ) )
oSchema:add( THField():boolean( "active" ) )
oSchema:add( THField():timestamps() )
oSchema:create()

Available Column Types

The Table Blueprint exposes the following methods for declaring column typologies:

Blueprint Method	Resulting Column Description
table:id("name")	Auto-Increment Primary Key. Defaults to "id" if left blank.
table:string("c", l)	Equivalent to VARCHAR. Accepts an optional length limit l.
table:integer("c")	Standard numeric integer (INTEGER).
table:float("c")	Floating point decimal number (FLOAT).
table:decimal("c",p,s)	Exact decimal. p is total precision, s is scale (decimals).
table:boolean("c")	True/false values. Mapped to secure 1/0 integers if engine demands it.
table:date("c")	Date value solely (DATE).
table:datetime("c")	Exact Date and Time structure (DATETIME).
table:timestamp("c")	SQL Temporal Marker (TIMESTAMP or equivalent).
table:text("c")	Massive text storage (TEXT or MEMO).
table:json("c")	JSON structure if natively encapsulated by driver, pure String otherwise.
table:timestamps()	Helper utility. Automatically generates created_at and updated_at.

Post-Operations and Maintenance

You can also seamlessly intervene or destroy fully grown structures:

Destroying Tables:

Code (harbour): Select all Collapse

THSchema():drop("users")
THSchema():dropIfExists("users") // Prevents crashing if table was already wiped out

---

5. Grammar Resolution (THGrammar)

The THGrammar structure lies behind the ORM's curtains. It works entirely as an Abstract Layer.

Each individual database driver integrated through HDBC (e.g. Postgres, MariaDB) natively introduces into the ORM their proprietary child extension originating from THGrammar. Consequently, the framework acknowledges exact translation algorithms handling structural nuances formatting engine reserved-words or parameter enclosures appropriately (Ex: Dealing whether LIMIT and OFFSET diverge drastically or wrapping syntax \`` vs[]or""`).

You are not required to instantiate it yourself under normal conditions, nevertheless, you hold complete permission querying structural behaviors querying back towards your host THDbc:

Code (harbour): Select all Collapse

// Retrieve the live dialect injector coupled to your current session
LOCAL oGrammar := THModel():getGrammar()
? oGrammar:sqlListTables()

---

6. Complete Practical Example

This final chapter merges previously digested knowledge setting up an end-to-end sandbox application. It features booting an interconnected Author & Book universe from absolute zero dictating their corresponding classes, persisting test entities (Creates), eagerly retrieving chains mapping collections (Reads), subsequently handling dynamic adjustments (Updates), and lastly eradicating target instances from memory and physically dropping rows (Deletes).

Code (harbour): Select all Collapse

#include "hdbc_conn.ch"
#include "hbclass.ch"
#include "hdbc.ch"

// ---------------------------------------------------------
// 1. MODELS DEFINITION
// ---------------------------------------------------------
CLASS Author FROM THModel
   DATA table_name INIT "authors"
   DATA id, name
   
   METHOD books() // 1:N Relationship Endpoint
ENDCLASS

METHOD books() CLASS Author ; return ::hasMany( "Book", "author_id" )

CLASS Book FROM THModel
   DATA table_name INIT "books"
   DATA id, author_id, title, in_stock
   
   METHOD author() // Inverse N:1 Mapping
ENDCLASS

METHOD author() CLASS Book ; return ::belongsTo( "Author", "author_id" )

// ---------------------------------------------------------
// 2. MAIN EXECUTION PROCEDURE
// ---------------------------------------------------------
PROCEDURE Main()
   LOCAL oDb := THDbc():new( HDBC_DRIVER_SQLITE )
   LOCAL oAuthor, oBook, oAuthorsSet
   
   // A. CORE CONNECTION MOUNTING
   oDb:connect( "database=library.db" )
   THModel():setConnection( oDb )
   
   // B. SCHEMA BUILDER (Creating structures if lacking)
   
   // Create authors table
   THSchema():new( "authors" ):dropIfExists()
   oSchema := THSchema():new( "authors" )
   oSchema:add( THField():id() )
   oSchema:add( THField():string( "name", 150 ) )
   oSchema:add( THField():timestamps() )
   oSchema:create()
   
   // Create books table
   THSchema():new( "books" ):dropIfExists()
   oSchema := THSchema():new( "books" )
   oSchema:add( THField():id() )
   oSchema:add( THField():integer( "author_id" ) )
   oSchema:add( THField():string( "title", 200 ) )
   oSchema:add( THField():boolean( "in_stock" ):default(.T.) )
   oSchema:add( THField():timestamps() )
   oSchema:create()
   
   // C. CREATE (Mass Fill Injections)
   // Direct Table Hydration
   Author():create({ "id" => 1, "name" => "Edgar Allan Poe" })
   Author():create({ "id" => 2, "name" => "H. P. Lovecraft" })
   
   // Relational Book Inserts manually tying FK links
   Book():create({ "author_id" => 1, "title" => "The Raven", "in_stock" => 1 })
   Book():create({ "author_id" => 1, "title" => "The Tell-Tale Heart", "in_stock" => 0 })
   Book():create({ "author_id" => 2, "title" => "The Call of Cthulhu", "in_stock" => 1 })

   // D. READ & LISTINGS (Preloading with Eager Loading)
   ? "---- CATALOGUE BROWSER ----"
   oAuthorsSet := Author():with("books"):all()
   
   oAuthorsSet:each({|a| ;
       QOut( "=> Found Author: " + a:name ), ;
       a:books:each({|b| ;
          QOut( "   * Logged Title: " + b:title + " (Stock Flag: " + hb_ValToStr(b:in_stock) + ")" ) ;
       }) ;
   })
   
   // E. MODIFICATIONS (Updates)
   ? "---- RE-STOCKING INVENTORY ----"
   oBook := Book():where("title", "LIKE", "%Tell-Tale%"):first()
   IF oBook != nil
      oBook:in_stock := 1 // Changing the local attribute state
      oBook:save()        // Broadcasting physical SQL update remotely
      ? "Title successfully restocked:", oBook:title
   ENDIF

   // F. DELETING (Dropping Records)
   ? "---- PRUNING OLD DATA ----"
   oAuthor := Author():find( 2 ) // Unearthing Lovecraft
   IF oAuthor != nil
      // Sweep inner relational associations orderly sequentially
      oAuthor:books:each({|b| b:delete() })
      
      // Ultimately drop the parent
      oAuthor:delete()
      ? "Target author alongside its linked bibliography effectively destroyed."
   ENDIF
   
   THModel():end()
   oDb:disconnect()
RETURN

---

⚙️ End of ORM manual. Consolidate THModel capabilities incorporating solid data rules to efficiently steer business logic enforcing tight security. 🚀

______________________________________________________________________________

Sevilla - Andalucía

Continue the discussion