Database query

Database Queries

ACMA provides powerful database search capabilities to locate objects based on complex criteria. Database queries are essential components used throughout ACMA's functionality, enabling dynamic object relationships and conditional processing.

Query Usage in ACMA

Database queries are utilized in several key areas:

Component	Purpose	Use Case Examples
Reference lookup constructor	Retrieve attribute values from related objects	Get manager's department, lookup organizational details
Exit events	Find target objects for event notifications	Send events to all users in a department
Class constructors (Undelete Parameters)	Locate existing deleted objects for restoration	Find previously deleted user accounts
Pre-operation events	Identify objects for pre-processing logic	Validate references before operations

Query Structure and Logic

Query Groups

All database queries begin with a top-level query group that defines the logical operator for combining conditions:

Operator	Logic	Description	Use Case
Any	OR	Any condition must be satisfied	Find users in IT OR Finance departments
All	AND	All conditions must be satisfied	Find active users AND in Sales department
One	XOR	Exactly one condition satisfied	Find users who are either employees OR contractors (not both)
None	NOT	No conditions can be satisfied	Find users NOT in restricted departments

Hierarchical Queries

Query groups support nesting for complex logic:

Main Group (All)
├── User Active = True                    ← Must be active
├── Department Group (Any)                ← AND in one of these departments:
│   ├── Department = "IT"                 
│   └── Department = "Finance"
└── Exclusion Group (None)                ← AND not in any of these:
    ├── Status = "Suspended"
    └── Status = "Terminated"

Value Queries

Value queries define specific comparison operations on object attributes, executing directly against the database for optimal performance.

Important Timing Considerations

• Database state: Queries execute against committed database state
• In-memory changes: Uncommitted attribute changes exist only in memory
• Search accuracy: Searches against unsaved objects may return incorrect results
• Best practice: Ensure objects are saved before query-dependent operations

Query Parameters

Parameter	Description	Examples
Search Attribute	Target attribute for comparison	firstName, department, employeeNumber
Operator	Comparison type (see operators below)	Equals, Contains, Is greater than
Value	ACMA declarative language expression	{firstName}, "IT", {constant:minAge}
Transforms	Data transformations applied to value	ToUpper, Trim, Left:3

Supported Operators by Data Type

Universal Operators (All Types)

• Is present - Attribute has any value
• Is not present - Attribute is null or empty

String Operations

• Equals / Does not equal - Exact match comparison
• Contains / Does not contain - Substring search
• Starts with - Prefix matching
• Ends with - Suffix matching

Example: Find users whose last name starts with "Sm"

Search Attribute: sn
Operator: Starts with
Value: "Sm"

Boolean Operations

• Equals / Does not equal - True/false comparison

Example: Find active accounts

Search Attribute: isActive
Operator: Equals
Value: true

Reference Operations

• Equals / Does not equal - Object reference comparison
• Is present / Is not present - Reference existence check

Example: Find users managed by specific person

Search Attribute: manager
Operator: Equals
Value: {objectId:12345}

Numeric Operations (Integer/DateTime)

• Equals / Does not equal - Exact value match
• Is greater than / Is greater than or equal to - Range comparison (upper)
• Is less than / Is less than or equal to - Range comparison (lower)
• Is present / Is not present - Value existence check

Example: Find users hired in the last year

Search Attribute: hireDate
Operator: Is greater than
Value: {dateTime:addDays({dateTime:now},-365)}

Performance and Optimization

Indexing Strategy

Proper indexing is crucial for query performance:

• Frequent searches: Index attributes used in common queries
• Performance impact: Unindexed searches cause full table scans
• Write performance: Excessive indexing slows database writes
• Balance: Strategic indexing based on query patterns

Recommendation: Review the Attributes indexing section for detailed indexing guidance.

Query Design Best Practices

Object Class Constraints

• Issue: Queries aren't automatically constrained by object class
• Risk: Searching for attributes that don't exist on all object types
• Solution: Always include object class filters

Example: Constrain search to person objects

Query Group (All)
├── objectClass = "person"
└── department = "IT"

Efficient Query Structure

• Specific first: Place most selective conditions early
• Index utilization: Leverage indexed attributes in primary conditions
• Minimize scope: Use object class constraints to reduce search space
• Test performance: Validate query speed with production-like data volumes

Common Query Patterns

Find Users by Department

Query Group (All)
├── objectClass = "person"
└── orgUnitdisplayName = "IT"

Find Users Without Manager

Query Group (All)
├── objectClass = "person"
└── manager is not present

Complex Organizational Query

Query Group (All)
├── objectClass = "person"
├── isActive = true
├── Department Group (Any)
│   ├── department = "IT"
│   ├── department = "Finance"
│   └── department = "HR"
└── Exclusion Group (None)
    ├── employeeType = "Contractor"
    └── status = "Terminated"