ORM_Connection + ORM_Table

Source: source/classes/fworm.prg

The ORM (Object-Relational Mapping) classes provide a multi-backend abstraction layer that works with ADO, FWH (MariaDB), and Dolphin (DLP) connections. ORM_Connection manages the database link and exposes tables as ORM_Table objects. ORM_Table provides a fluent query-building interface with chainable Where/OrderBy/Select methods, aggregation functions, relationship navigation, and dynamic field access via an error handler.

Architecture

flowchart LR App[Application] --> OC[ORM_Connection] OC --> OT[ORM_Table] OT --> RS[Result Set] OC --> ADO[ADO] OC --> FWH[FWMariaConnection] OC --> DLP[Dolphin]

ORM_Connection

ORM_Connection abstracts database connections across three backends: ADO (for any ODBC-compliant RDBMS), FWH (native MariaDB via FWMariaConnection), and DLP (Dolphin library for MariaDB). It accepts either connection parameters or an existing connection object.

DATA Members

DATATypeDescription
rdbmsCharacterRDBMS type ("MYSQL", "MSSQL", "DBASE", "ORACLE", etc.)
oCnObjectUnderlying connection object (ADO/FWH/DLP)
nLibNumericLibrary constant: LIB_ADO(1), LIB_FWH(2), LIB_DLP(4)
cServerCharacterServer hostname
cDataBaseCharacterDatabase name
cUserNameCharacterDatabase user name

Methods

MethodDescription
New( rdbms, cServer, cDataBase, cUserName, cPassword )Create and connect to a database. If first parameter is an object, wraps an existing connection.
Connect( cPassWord )Establish the connection
Tables()Return an FW_Array of table names in the database
Table( cTable )Return an ORM_Table object for the given table name
HasTable( cTable )Check if a table exists
Close()Close the connection

ORM_Table

ORM_Table provides recordset navigation, query building, and data manipulation for a single database table. Its error handler exposes field values as dynamic properties and sub-tables as related ORM_Table objects.

Key DATA Members

DATATypeDescription
cTableNameCharacterTable name
cPrimaryKeyCharacterPrimary key field name(s)
cSelectCharacterSELECT clause (field list or SQL expression)
cWhereCharacterWHERE clause (accumulated via Where() calls)
cOrderByCharacterORDER BY clause (accumulated via OrderBy() calls)
lAutoSaveLogicalIf .T., saves changes automatically (default .F.)
aRelateArrayRelationship definition: { cField, oTable, cFKey }

Methods

MethodDescription
New( oConnection, cTable )Create an ORM_Table bound to a connection
Where( ... )Add filter condition(s). Chainable, returns Self.
OrderBy( ... )Add sort specification(s). Chainable, returns Self.
Select( ... )Add field(s) to the SELECT clause. Chainable.
First()Navigate to the first record. Returns Self.
Last()Navigate to the last record. Returns Self.
Next()Navigate to the next record. Returns Self.
Prev()Navigate to the previous record. Returns Self.
Move( n )Navigate forward/backward by n records. Returns Self.
Add( aFlds, aVals )Add a new record with the given field values
Del()Delete the current record
Find( uVal, u )Find a record by primary key value, or (cField, uVal) pair
Count()Return the number of matching records
Sum( cField )Return the SUM of a numeric field
Max( cField )Return the MAX value of a field
Min( cField )Return the MIN value of a field
Avg( cField )Return the AVG of a numeric field
Std( cField )Return the STD (standard deviation) of a field
Browse( acFields )Display the recordset in an XBrowse dialog
Relate( cField, oTable, cFKey )Define a relationship to another ORM_Table
Save()Save pending changes. Returns Self.
Get( u )Get field value by name or position
Close()Close the recordset and free resources

Dynamic Field Access

ORM_Table uses an error handler to provide direct property-style access to field values. Any message that matches a table field name is automatically translated to a field get/set: 

// Instead of: oTable:Get( "name" )
? oTable:name        // dynamic access via error handler
oTable:city := "Madrid"   // set field value

Similarly, accessing a property that matches another table name returns a new ORM_Table for that table, enabling seamless navigation between related tables: 

// If "orders" is a table, this returns ORM_Table for orders:
oTable:orders:Where( "status = 'active'" )

Example: Chain Queries with Relationships

#include "FiveWin.ch"

function Main()

   local oCn, oCustomers

   // Create connection to MariaDB via FWH backend
   oCn := ORM_Connection():New( "FWH", "localhost", "mydb", "root", "password" )
   if oCn == nil
      MsgStop( "Connection failed" )
      return nil
   endif

   // Get a table and chain query filters
   oCustomers := oCn:Table( "customers" )

   oCustomers:Where( "city", "Madrid" ):OrderBy( "name" )

   oCustomers:First()
   while !oCustomers:oRs:Eof()
      ? oCustomers:name, oCustomers:email   // dynamic field access
      oCustomers:Next()
   end

   // Aggregation
   ? "Total customers in Madrid:", oCustomers:Count()
   ? "Max credit:", oCustomers:Max( "credit_limit" )

   // Find by primary key
   oCustomers:Find( 101 )
   ? oCustomers:name

   // Add a new record
   oCustomers:Add( { "name", "email" }, { "New Client", "client@test.com" } )

   oCn:Close()

return nil

Notes

See Also