OQL (Object Query Language)

OQL is the built-in dynamic CRUD engine. It maps per-client YAML config files to HTTP endpoints automatically — no handler or repository code required for basic operations.

How It Works

YAML config files in clients_configs/{accname}/oql/{module}/yaml/{object}.yaml define the object schema.
The SDK reads these at startup and registers routes.
Standard CRUD endpoints are served automatically for each object.
Custom business logic is layered on top via Extended Objects.

Directory Structure

clients_configs/
└── {accname}/
    └── oql/
        └── {module}/
            └── yaml/
                └── {object-name}.yaml

Active modules registered in internal/router/router.go:

Module	Domain
`sf`	Settings Framework (access groups, employee groups, system tables, etc.)
`ge`	General (cities, countries, currencies, etc.)
`em`	Employee module (positions, employment status, etc.)
`og`	Org structure (companies, cost centers, job grades, etc.)
`py`	Payroll
`ta`	Time & Attendance

Auto-Generated Endpoints

For each YAML object, the following routes are served automatically:

POST /oql/{module}/{object}/listing
POST /oql/{module}/{object}/view
POST /oql/{module}/{object}/add
POST /oql/{module}/{object}/edit
POST /oql/{module}/{object}/delete
POST /oql/{module}/{object}/reference

YAML Schema Reference

Minimal Example

access:
    default: sys.setting.system.doc
table:
    name: TSFMSYSTABLE
    alias: ST
    primary_keys:
        - table_name
fields:
    - name: table_name
      table_alias: ST
    - name: description
      table_alias: ST

Top-Level Keys

Key	Required	Description
`access`	yes	Access control keys per operation
`table`	yes	Primary table definition
`fields`	yes	Columns exposed by this object
`selector`	no	Computed/expression fields
`soft_filter`	no	Always-applied WHERE conditions
`sort`	no	Default ORDER BY
`limit`	no	Default page size
`lookup`	no	Dropdown/reference field subset
`data_auth`	no	Row-level data authorization

`access`

Controls which permission key is checked for each operation. Use NONE to deny all, omit a key to fall back to default.

access:
    default: sys.setting.access.datagroup      # fallback for unlisted operations
    add: sys.setting.access.datagroup:add
    edit: sys.setting.access.datagroup:edit
    delete: sys.setting.access.datagroup:delete
    listing: sys.setting.access.datagroup:read
    view: sys.setting.access.datagroup:read
    _custom_action: some.permission.key        # custom extended actions use _ prefix

Special values:

NONE — deny all access (no auth check bypasses)
<nil> — skip access check entirely (used in data_auth)

`table`

Defines the primary table and optional JOIN relationships.

table:
    name: TCLMACCESSGROUP    # DB table name (case-insensitive for most drivers)
    alias: AG                # SQL alias used in fields/conditions
    primary_keys:
        - usergroup_id       # one or more PK columns
    relations:               # optional JOINs (see below)

`table.relations` — JOINs

Defines JOINs as a nested tree. Each relation has:

Key	Values	Description
`join_type`	`INNER`, `LEFT`	SQL JOIN type
`on`	list of conditions	JOIN condition fields
`table`	object or string	Joined table (inline object with `name`+`alias`, or plain string with separate `alias`)

Simple form (table as string + separate alias):

relations:
    - join_type: INNER
      on:
        - field_name: user_id
          table_alias: E
          op: =
          value:
            name: user_id
            table_alias: U
      table: TEOMEmpPersonal
      alias: E

Nested form (table as inline object, supports deep nesting):

relations:
    - join_type: LEFT
      on:
        - field_name: state_id
          table_alias: CITY
          op: =
          value:
            name: state_id
            table_alias: STATE
      table:
        name: TGEMSTATE
        alias: STATE
        relations:                     # nested — STATE also joins COUNTRY
            - join_type: LEFT
              on:
                - field_name: country_id
                  table_alias: STATE
                  op: =
                  value:
                    name: country_id
                    table_alias: COUNTRY
              table:
                name: TGEMCOUNTRY
                alias: COUNTRY

Multi-condition JOIN (multiple on entries = AND):

on:
    - field_name: worklocation_code
      table_alias: wloc
      op: =
      value:
        name: work_location_code
        table_alias: empco
    - field_name: company_id
      table_alias: wloc
      op: =
      value:
        name: company_id
        table_alias: empco

`fields`

Columns exposed by the object. Each entry maps a DB column to an API field name.

fields:
    - name: usergroup_id       # DB column name
      table_alias: AG          # which table alias to qualify with

    - name: enabled            # DB column name
      table_alias: ST
      field_alias: status      # override the API field name

    - name: pos_name
      table_alias: P
      mlang_modifier: true     # auto-appends language suffix: pos_name_en / pos_name_id / etc.

    - name: pos_name           # same DB column, different alias (used for multi-role joins)
      field_alias: dept_name
      table_alias: D
      mlang_modifier: true

Field options:

Option	Description
`name`	DB column name
`table_alias`	Qualifying table alias
`field_alias`	Override API output name
`mlang_modifier`	Appends `_{lang}` suffix for multilanguage columns

`selector`

Computed or expression fields added to SELECT. Not part of fields — use for CASE expressions, derived columns, or raw SQL fragments.

selector:
    STATUS_NAME:
        field_alias: STATUS_NAME
        expression:
            value: CASE city.status WHEN 0 THEN 'Unverified' ELSE 'Verified' END

    dorder:                              # override a column with an expression
        name: (CASE WHEN DORDER IS NULL THEN 9999 ELSE DORDER END)
        field_alias: dorder

    service_length:                      # raw SQL fragment (unusual, use carefully)
        name: join_date,getdate())
        field_alias: service_length
        table_alias: TIMESTAMPDIFF(month,empgroup

    emp_photo:                           # direct column selector
        name: photo
        field_alias: AVATAR
        table_alias: employee

`soft_filter`

Always-applied WHERE conditions — appended to every query regardless of request payload.

soft_filter:
    - field_name: company_id
      table_alias: G
      op: =
      value: '[COID]'        # [COID] = resolved from current session's company ID

    - field_name: status
      table_alias: G
      op: in
      value:
        - 0
        - 1

    - field_name: pos_flag
      table_alias: A
      op: =
      value: 2

Supported op values: =, !=, <>, >, <, >=, <=, in, like.

Special placeholders in value:

[COID] — resolved to the current user's company ID at runtime.

`sort`

Default ORDER BY applied when no order is specified in the request.

sort:
    - name: table_name
      order: ASC
    - name: created_date
      order: DESC

`limit`

Default page size when limit is not provided in the request body.

limit: 10

`data_auth`

Row-level data authorization. Controls whether data access filtering is applied.

data_auth:
    default: <nil>     # <nil> = skip data auth entirely

`lookup`

A slimmed-down field set returned for dropdown/reference use cases (used by the reference endpoint). Optionally has its own access key and selector.

lookup:
    fields:
        - name: company_id
          field_alias: optvalue
          table_alias: COMPANY
        - name: company_name
          field_alias: opttext
          table_alias: COMPANY
          mlang_modifier: true
    access: hrm.employee        # optional override access key for reference
    selector:
        parent_code:
            name: parent_code
            table_alias: CC

Request / Response Types

Listing — `POST /oql/{module}/{object}/listing`

Request (LuceeReqListing):

{
  "selector": "field1,field2",
  "limit": 20,
  "page": 1,
  "ufilter": {
    "and": [{"group_name": "Admin"}],
    "or":  [{"status": 1}]
  },
  "order": "group_name asc"
}

Response:

{
  "rows": [...],
  "rowCount": 100,
  "columns": [...]
}

View — `POST /oql/{module}/{object}/view`

Request (LuceeReqView):

{
  "key": {"usergroup_id": 1}
}

Response: {"row": {...}}

Add — `POST /oql/{module}/{object}/add`

Request (LuceeReqAdd):

{
  "entries": {
    "group_name": "New Group",
    "status": 1,
    "company_id": 10
  }
}

Response: {"row": {...}} — the inserted row.

Edit — `POST /oql/{module}/{object}/edit`

Request (LuceeReqEdit):

{
  "key": {"usergroup_id": 1},
  "entries": {
    "group_name": "Updated Name"
  }
}

Response: {"row": {...}} — the updated row.

Delete — `POST /oql/{module}/{object}/delete`

Request (LuceeReqDelete):

{
  "key": {"usergroup_id": 1}
}

Response: {"message": "OK"}

Reference — `POST /oql/{module}/{object}/reference`

Same request shape as listing. Returns lookup.fields subset, used for dropdowns.

OQL in Go Code

Calling OQL from a usecase

All SDK operations use the global sdk.OQL.* singleton:

import (
    "gitlab.dataon.com/sunfish-framework/sdk"
    oqlTypes "gitlab.dataon.com/sunfish-framework/sdk/public/oql/types"
    dtoql "framework-settings/internal/dto/oql"
)

// Listing
builder, err := sdk.OQL.Listing(ctx, "sf", "access-group")
result, err := builder.
    Selector(strings.Split(req.Selector, ",")...).
    Where(sdk.OQL.LuceeParser.ParseLuceeUfilter(oqlTypes.ParseLuceeUfilterParams{
        Ufilter:   req.Ufilter,
        RefFields: builder.Fields(),  // pass fields for type-safe filtering
    })...).
    Limit(req.Limit).
    Page(req.Page).
    Sort(sdk.OQL.LuceeParser.ParseLuceeOrder(req.Order)...).
    Execute(ctx)

// View
builder, err := sdk.OQL.View(ctx, "sf", "access-group")
result, err := builder.PKIdentifier(req.Key).Execute(ctx)

// Add
builder, err := sdk.OQL.Add(ctx, "sf", "systable")
result, err := builder.Entry(req.Entries).Execute(ctx)

// Edit
builder, err := sdk.OQL.Edit(ctx, "sf", "systable")
result, err := builder.PKIdentifier(req.Key).Entry(req.Entries).Execute(ctx)

// Delete
builder, err := sdk.OQL.Delete(ctx, "sf", "systable")
_, err = builder.PKIdentifier(req.Key).Execute(ctx)

// Reference
builder, err := sdk.OQL.Reference(ctx, "em", "position")
result, err := builder.
    Selector("position_id", "pos_name").
    Where(...).
    Execute(ctx)

Result types

// Multi-row operations (listing, reference)
result *oqlTypes.MultiRowsResult
result.Rows      // []map[string]any
result.Columns   // []string
result.RowCount  // int64

// Single-row operations (view, add, edit)
result *oqlTypes.SingleRowResult
result.Row       // map[string]any

Extended Objects — Custom Logic on OQL Objects

When standard CRUD is insufficient, extend an OQL object with custom endpoints.

Pattern

import "gitlab.dataon.com/sunfish-framework/sdk/public/util/fiberkit/oql"

type MyObject struct {
    oql.ExtensionRegistry   // embed — no Initialize() call needed
    usecase uc.Iusecase
}

func NewExtendedObject(uc uc.Iusecase) *MyObject {
    return &MyObject{usecase: uc}
}

func (o *MyObject) ObjectName() string { return "my-object" }  // matches YAML filename
func (o *MyObject) Module() string     { return "sf" }         // matches module dir

// Any public method with fiber.Handler signature becomes a custom endpoint
func (o *MyObject) Members(c *fiber.Ctx) error {
    ctx := c.UserContext()
    // ...
    return sdk.Util.StandardResponse.Response(ctx, types.ResponseParams{
        Data:            result,
        RowCount:        count,
        UseLegacyFormat: c.QueryBool(legacyformat.LegacyFormatQueryKey),
    })
}

Registration

In the module router (internal/handler/extendedmodule/sf/module_router.go):

return fiberkit.Module.Routers.OQL.RegisterExtensions(
    holiday,
    accessgroup,
    employeegroup,
    systable,
)

Custom endpoints are served at:

POST /oql/{module}/{object}/{method-name-lowercase}

For example, Members on access-group in module sf:

POST /oql/sf/access-group/members

Adding a New OQL Object

Step 1 — Create the YAML

# clients_configs/main/oql/sf/yaml/my-object.yaml
access:
    default: sys.setting.my.permission
table:
    name: MY_TABLE
    alias: T
    primary_keys:
        - my_id
fields:
    - name: my_id
      table_alias: T
    - name: my_name
      table_alias: T

All 6 CRUD endpoints are now live automatically.

Step 2 — Add custom logic (if needed)

Only needed when the standard CRUD isn't enough:

Define Iusecase in internal/interface/usecase/{domain}/
Implement Usecase in internal/usecase/{domain}/
Create handler in internal/handler/extendedmodule/{module}/{domain}/
Register in internal/handler/extendedmodule/{module}/module_router.go

Step 3 — Per-client overrides (optional)

Copy the YAML to a client-specific path to override the default:

clients_configs/{accname}/oql/{module}/yaml/{object}.yaml

`ufilter` Format

Used in listing, reference, and programmatic Where() calls.

{
  "and": [
    {"group_name": "Admin"},
    {"status": 1},
    {"company_id": {">=": 10}}
  ],
  "or": [
    {"group_type": 2},
    {"group_type": 3}
  ]
}

In Go, when remapping field names before passing to OQL (column names differ between the request and the YAML):

colMap := map[string]string{
    "emp_name":  "full_name",
    "dept_name": "D.pos_name",
}

remapClause := func(items []any) map[string]any {
    clause := map[string]any{}
    for _, item := range items {
        v, ok := item.(map[string]any)
        if !ok { continue }
        for k, val := range v {
            key := k
            if mapped, ok := colMap[strings.ToLower(k)]; ok {
                key = mapped
            }
            clause[key] = val
        }
    }
    return clause
}

ufilter := map[string]any{}
if and, ok := payload.Ufilter["and"].([]any); ok {
    ufilter["and"] = remapClause(and)
}

Existing Objects Reference

Module `sf`

Object	Table	PK	Notes
`access-group`	TCLMACCESSGROUP	usergroup_id
`access-group-members`	TCLMUser	user_id	multi-join
`employee-group`	TSFMFilterGroup	seq_id	soft_filter on owner
`emp-group-member`	TEOMEmpPersonal	emp_id	deep nested joins
`emp-group-member-ne`	TEOMEmpPersonal	emp_id	no-entity variant
`datagroup`	TCLMDataGroup	datagroup_id	expression fields
`systable`	TSFMSYSTABLE	table_name	string PK, selector
`holiday`	TGEMHOLIDAY	seq_id	default limit 10
`menu`	TSFMMenu	menu_id
`reqapvset`	TCLCREQAPPSETTING	seq_id	soft_filter COID
`filter-group`	TSFMFILTERGROUP	seq_id
`user-management`	TCLMUser	user_id	deep nested joins

Module `ge`

Object	Table	PK	Notes
`city`	TGEMCITY	city_id	nested LEFT joins, default limit 10
`country`	TGEMCOUNTRY	country_id
`currency`	—	—

Module `og`

Object	Table	PK	Notes
`company`	TEOMCOMPANY	company_id	lookup access override
`cost-center`	TEOMCOSTCENTER	costcenter_code	string PK, mlang
`org-struct-tree`	TEOMPOSITION	position_id	soft_filter COID
`job-grade`	—	—

Module `em`

Object	Table	PK	Notes
`position`	teomposition	position_id	mlang, lookup optvalue/opttext
`employment-status`	—	—
`marital`	—	—

推荐订阅源

DEV Community

How It Works

Directory Structure

Auto-Generated Endpoints

YAML Schema Reference

Minimal Example

Top-Level Keys

access

table

table.relations — JOINs

fields

selector

soft_filter

sort

limit

data_auth

lookup