# new ClientRepository (connectionUBConnection, entityNameString)

Arguments:

Members

# rawResult : object instance

Raw result of method execution. Available after Promise of select* method execution is resolved. Can be used to get additional response parameters such as resultLock or resultAls. ** Client repository only **

  
      // get lock information together with `select` execution
     let repo = UB.Repository('tst_document').attrs('ID').misc({lockType: 'None'}).where('ID', '=', 332729226657819)
     let data = await repo.selectSingle()
     let lockInfo = repo.rawResult.resultLock
  

# UBQLv2 : boolean instance

Server side support UBQL v2 version (value in whereList)

# WhereCondition : string instance

Enumeration of all condition types. This enumeration defines a set of String values. It exists primarily for documentation purposes - in code use the actual string values like '>', don't reference them through this class like WhereCondition.more.

We define several aliases for the same condition. In case of direct HTTP request (without Repository) use only non-aliased values (i.e. more instead of '>' or 'gt')

Name Type Description
gt string Alias for more
">" string Alias for more
more string Greater than
lt string Alias for less
"<" string Alias for less
less string Less than
eq string Alias for equal
"=" string Alias for equal
equal string Equal to
ge string Alias for moreEqual
geq string Alias for moreEqual
">=" string Alias for moreEqual
moreEqual string Greater than or equal
le string Alias for lessEqual
leq string Alias for lessEqual
"<=" string Alias for lessEqual
lessEqual string Less than or equal
ne string Alias for notEqual
neq string Alias for notEqual
"<>" string Alias for notEqual
"!=" string Alias for notEqual
"!==" string Alias for notEqual
notEqual string Not equal
contains string Alias for like
like string Like condition. For attributes of type String only
notContains string Alias for notLike
notLike string Not like condition. For attributes of type String only
isNull string Is null
null string Alias for isNull
notNull string Alias for notIsNull
notIsNull string Not is null
isNotNull string Alias for notIsNull
isFalse string Is false, for attributes of type 'Boolean' only
isTrue string Is true, for attributes of type 'Boolean' only
beginWith string Alias for startWith
startWith string Start with. For attributes of type String only
startsWith string Alias for startWith
startswith string Alias for startWith
notBeginWith string Alias for notStartWith
notStartWith string Not start with. For attributes of type String only
notStartsWith string Alias for notStartWith
includes string Alias for in
in string One of. Can accept array of string on array of Int/Int64 as values depending on attribute type.
notIncludes string Alias for notIn
notIn string Not one of. See WhereCondition.in for details
match string For entities with FTS mixin enabled. TODO - expand
subquery string Execute a sub-query passed in values. Better to use 'in' condition with Repository as a values parameter or a CustomRepository.exists method
exists string Execute a exists(sub-query) passed in values. Better to use CustomRepository.exists method
notExists string Execute a not exists(sub-query) passed in values. Better to use CustomRepository.notExists method
custom string Custom condition. For Server-side call only. For this condition expression can be any SQL statement

Methods

# attrs (attrsstring) → CustomRepository instance

Adds attribute(s) or expression(s).

Can take expression as a field. In this case entity attribute name must be wrapped into [] brackets. In case of client-side execution the only valid expression is one of:

  • 'SUM', 'COUNT', 'AVG', 'MAX', 'MIN', 'CAST', 'COALESCE'

Arguments:
  
      // chaining
UB.Repository('uba_user').attrs('ID').attrs('name', 'firstName').attrs('disabled').selectAsObject()

// calculate sum over some attribute
UB.Repository('uba_user').attrs('SUM([disabled])').where('disabled', '=', true).selectScalar()

// In case of server-side execution any valid SQL expression is accepted by attr:
UB.Repository('uba_user').attrs('[ID] / 100 + 1').selectAsArray()

// JOIN `uba_userrole.roleID` is a attribute of type Entity. ORM choose `left join` in case attribute is `allowNull: true`
UB.Repository('uba_userrole').attrs(['userID', 'userID.name']).selectAsObject()

// todo Define a way to join for UNITY (@)

// get values for attribute of type MANY
UB.Repository('tst_maindata')
 .attrs('ID', 'manyValue', 'manyValue.caption')
 .where('code', '=', 'Code1')
 .selectAsObject({'manyValue.caption': 'captions'})
// result is `[{"ID":331947938939292,"manyValue":"1,2","captions":"caption 10,caption 20"}]`

// Get attribute value for multi-language ("isMultiLang": true in meta file) attribute other when current session language
UB.Repository('org_employee').attrs(['ID', 'lastName_en^']).selectAsObject()
  

# attrsIf (addingCondition*, attrsstring) → CustomRepository instance

Helper method for CustomRepository.attrs. Calls attrs in case addingCondition is truthy

Arguments:
  • addingCondition: *

    Attributes will be added only in case addingCondition is truthy

  • attrs: string| Array.<string>
  
      let isPessimisticLock = !!UB.connection.domain.get('uba_user').attributes.mi_modifyDate
// with whereIf
let repo = UB.Repository('uba_user').attrs('ID').attrsIf(isPessimisticLock, 'mi_modifyDate')
//without whereIf
let repo = UB.Repository('uba_user').attrs('ID')
if (isPessimisticLock) repo = repo.attrs('mi_modifyDate')
  

# clearWhereList () → CustomRepository instance

Remove all where conditions (except ones using in joinAs). This function mutates current Repository

  
      let repo1 = UB.Repository('uba_user').attrs('ID', 'code').where('ID', '>', 15, 'byID')
let repoWithoutWheres = repo1.clone().clearWhereList()
  

# clone () → CustomRepository instance

Creates a clone of this repository

  
      let repo1 = UB.Repository('uba_user').attrs('ID', 'code').where('ID', '>', 15, 'byID')
let repo2 = repo1.clone()
repo1.orderBy('code')
repo2.attrs('name').where('ID', '>', 100, 'byID')
repo1.selectAsObject() // return ordered users with ID > 15
repo2.selectAsObject() // return unordered users with their names and ID > 100
  

# correlation (subQueryAttributestring, masterAttributestring, conditionoptWhereCondition | string, clauseNameoptstring) → CustomRepository instance

If current repository is used as a sub-query for exists, notExists, in or notIn conditions correlation with a master repository will added

Arguments:
  • subQueryAttribute: string
  • masterAttribute: string
  • condition = eq: WhereCondition| string

    A subset from WhereCondition list applicable for correlation join

  • clauseName: string

    Optional clause name to be used in logic. If not passed unique clause names ('c1', 'c2', ...) where will be generated

# exists (subRepositoryCustomRepository, clauseNameoptstring) → CustomRepository instance

Adds where condition with EXISTS sub-query. Inside a sub-query there are two macros:

  • {master} will be replaced by master entity alias
  • {self} will be replaced by sub-query entity alias

Arguments:
  • subRepository: CustomRepository

    Repository, what represent a sub-query to be execute inside EXISTS statement

  • clauseName: string

    Optional clause name

  
      UB.Repository('uba_user').attrs(['ID', 'name']) //select users
  // who are not disabled
  .where('disabled', '=', 0)
  // which allowed access from Kiev
  .where('trustedIP', 'in',
   UB.Repository('geo_ip').attrs('IPAddr')
     .where('city', '=', 'Kiev')
  )
  // who do not log in during this year
  .notExists(
   UB.Repository('uba_audit')
     .correlation('actionUser', 'name')  // here we link to uba_user.name
     .where('actionTime', '>', new Date(2016, 1, 1))
     .where('actionType', '=', 'LOGIN')
  )
  // but modify some data
  .exists(
   UB.Repository('uba_auditTrail')
     .correlation('actionUser', 'ID') // here we link to uba_user.ID
     .where('actionTime', '>', new Date(2016, 1, 1))
  )
  .select()
  

# groupBy (attrstring) → CustomRepository instance

Adds grouping

Arguments:
  
      UB.Repository('my_entity').attrs('ID')
  .groupBy('code')
UB.Repository('uba_user').attrs('disabled')
  .groupBy('disabled').select()
UB.Repository('uba_user').attrs(['disabled','uPassword','COUNT([ID])'])
  .groupBy(['disabled','uPassword']).select()
  

# join (whereItemNamestring) → CustomRepository instance

Force where expressions to be used in join part of SQL statement instead of where part. Applicable only for not cached entities

Arguments:
  • whereItemName: string

    name of where item to use in join.

  
      // will generate
// SELECT A.ID, B.code FROM tst_document A LEFT JOIN tst_category B
//    ON (B.instanceID = A.ID and B.ubUser = 10)
// instead of
// SELECT A.ID, B.code FROM tst_document A LEFT JOIN tst_category B
//    ON B.instanceID = A.ID
//    WHERE B.ubUser = 10
UB.Repository('tst_document').attrs(['ID', '[category.code]'])
 .where('[category.ubUser]', '=', 10, 'wantInJoin')
 .join('wantInJoin')
 .selectAsObject().then(UB.logDebug)
  

# joinCondition (expressionstring, conditionCustomRepository.WhereCondition, valuesopt*, clauseNameoptstring) → CustomRepository instance

Adds join condition

Arguments:
  • expression: string

    Attribute name (with or without []) or valid expression with attributes in [].

  • condition: CustomRepository.WhereCondition

    Any value from WhereCondition list.

  • values: *

    Condition value. In case expression is complex can take {object} as value. In case values === undefined no values property passed to where list

  • clauseName: string

    Optional clause name to be used in {CustomRepository.logicalPredicates}. If not passed where will generate unique clause named 'c1', 'c2', ......

# limit (rowsLimitnumber) → CustomRepository instance

How many rows to select. If 0 - select all rows started from .start()

Arguments:
  
      // will return first two IDs from my_entity
let store = UB.Repository('my_entity').attrs('ID').limit(2).selectAsObject()
  

# logic (predicatestring) → CustomRepository instance

Arrange named where expressions in logical order. By default, where expressions are joined by AND logical predicate. It is possible to join it in custom order using logic. Pay attention to condition name we pass as a 4-th parameter to .where()

Arguments:
  • predicate: string

    logical predicate.

  
      UB.Repository('my_entity').attrs('ID')
  // code in ('1', '2', '3')
  .where('code', 'in', ['1', '2', '3'], 'byCode')
  // name like '%homer%'
  .where('name', 'contains', 'Homer', 'byName')
  //(birthday >= '2012-01-01') AND (birthday <= '2012-01-02')
  .where('birthday', 'geq', new Date()).where('birthday', 'leq', new Date() + 10)
  // (age + 10 >= 15)
  .where('[age] -10', '>=', {age: 15}, 'byAge')
  // (byCode OR byName) AND (all where items, not included in logic)
  .logic('([byCode]) OR ([byName])')
  

# misc (flagsobject) → CustomRepository instance

Apply miscellaneous options to resulting UBQL

Arguments:
  • flags: object
    • __mip_ondateDate

      Specify date on which to select data for entities with dataHistory mixin. Default to Now()

    • __mip_recordhistoryboolean

      Select only record history data for specified ID (for entities with dataHistory mixin)

    • __mip_recordhistory_allboolean

      Ignore __mip_ondate and select all data (acts as select for entities without dataHistory mixin)

    • __mip_disablecacheboolean

      For entities with cacheType in ["Session", "SessionEntity"] not check is data modified and always return result

    • __skipOptimisticLockboolean

      Skip optimistic lock for entities with mStorage.simpleAudit = true

    • __allowSelectSafeDeletedboolean

      Include softly deleted rows to the result

    • __useReplicaboolean

      Prefer to use DB replica for select statement (if configured, EE only)

    • __skipSelectAfterUpdateboolean

      Server-side only

    • __skipSelectAfterInsertboolean

      Server-side only

    • __skipSelectBeforeUpdateboolean

      Server-side only if added then mStorage mixin don't execute select before execution of update. As a UNSAFE side effect update won't check record is accessible to user. UNSAFE If Audit mixin is implemented for entity empty OldValue in inserted into uba_auditTrail in case this flag is enabled, so better to think twice before skip select before update

    • __skipRlsboolean

      Server-side only

    • __skipAclRlsboolean

      Server-side only

    • lockTypestring

      For entities with softLock mixin retrieve/set a lock during method execution Possible values:

      • 'None': get a lock info during select* execution (for a results with a single row)
      • Temp or Persist: set a lock (temp or persistent) together with select* execution (for a results with a single row)

  
      // this server-side call will select all currency, including deleted
UB.Repository('cdn_currency').attrs(['ID'])
  .misc({__allowSelectSafeDeleted: true}).selectAsArray();
  

# miscIf (addingCondition*, flagsobject) → CustomRepository instance

Helper method for CustomRepository.misc. Calls misc in case addingCondition is truthy

Arguments:
  • addingCondition: *

    flags will be applied only in case addingCondition is truthy

  • flags: object

# notExists (subRepositoryCustomRepository, clauseNameoptstring) → CustomRepository instance

Adds where condition with NOT EXISTS sub-query. See CustomRepository.exists for sample

Arguments:
  • subRepository: CustomRepository

    Repository, what represent a sub-query to be execute inside EXISTS statement

  • clauseName: string

    Optional clause name

# orderBy (attrstring, directionoptstring) → CustomRepository instance

Sorting. If expression already exists in order list it direction will be changed or, in case direction === null it will be removed

Arguments:
  • attr: string

    Sorted attribute

  • direction = 'asc': string| null

    Sort direction ('asc'|'desc'). If null - remove sorting by this attr

  
      let repo = UB.Repository('my_entity').attrs('ID').orderBy('code')
let orderedData = await repo.selectAsObject() // ordered. await is for client-side only
repo.orderBy('code', null) // remove order by code
let unorderedData = await repo.selectAsObject() // NOT ordered
  

# orderByDesc (attrstring) → CustomRepository instance

Adds descend sorting. The same as orderBy(attr, 'desc'). To remove such sorting call orderBy(attr, null)

Arguments:
  
      UB.Repository('my_entity').attrs('ID')
  // ORDER BY code, date_create DESC
  .orderBy('code').orderByDesc('date_create')
  

# select (fieldAliasesoptobject) → Promise.<Array.<object>> instance

Alias to selectAsObject

Arguments:

# selectAsArray () → Promise.<{entity: string, fieldList: string, method: string, resultData: TubCachedData}> instance

Asynchronously run request, constructed by Repository. Return promise, resolved to array of array representation of response. Actual data is placed to resultData response property.

  
      UB.Repository('ubm_navshortcut').attrs(['ID', 'code'])
   .where('code', 'in', ['uba_user', 'ubs_audit'])
   .select().then(UB.logDebug);
   // output is
   //{"resultData":{"data":[
   //  [3000000000004,"uba_user"],[3000000000039,"ubs_audit"]
   //],"fields":["ID","code"]},"total":2}

   // Response MAY (but may not even for the same request) contain other variables,
   // returned by server in case data retrieved not from cache, but resultData is always present

   // since uba_user have `unity` mixin it ID property point us to parent (`uba_subject` in this case)
   UB.Repository('uba_user').attrs(['ID', 'name', 'ID.name'])
   .selectAsArray().then(UB.logDebug);
   // {"entity":"uba_user","fieldList":["ID","name","ID.name"],"method":"select",
   // "resultData":{"fields":["ID","name","ID.name"],
   // "data":[[10,"admin","admin"]]},"total":1}
  

# selectAsArrayOfValues () → Promise.<Array.<(string | number)>> instance

For repository with ONE attribute returns a flat array of attribute values

  
      const usersIDs = await UB.Repository('uba_user').attrs('ID').limit(100).selectAsArrayOfValues()
// usersIDs is array of IDs [1, 2, 3, 4]
  

# selectAsObject (fieldAliasesoptObject.<string, string>) → Promise.<Array.<object>> instance

Asynchronously run request, constructed by Repository. Return promise, resolved to array of object representation of response.

Arguments:
  • fieldAliases: Object.<string, string>

    Optional object to change attribute names during transform array to object

  
      UB.Repository('ubm_navshortcut').attrs(['ID', 'code'])
     .where('code', 'in', ['uba_user', 'uba_auditTrail'])
     .selectAsObj().then(function(store) {
       console.log(store)
       // output is [{"ID":3000000000004,"code":"uba_user"},{"ID":3000000000039,"code":"ubs_audit"}]
   })

  // Optionally can rename attributes in the resulting object:

   UB.Repository('investment')
   .attrs(['ID', 'product', 'product.name', 'product.provider.name'])
   .selectAsObject({
        'product.name': 'productName',
        'product.provider.name': 'productProviderName'
    }).then(function(result){
        console.log(result);
        // output [{"ID": 1, "productName": "My product", "productProviderName": "My provider"}, ...]
    })
  

# selectAsStore (storeConfigoptobject) → Promise.<Array.<object>> instance

For core module (without Ext) - do the same as {ClientRepository.selectAsObj}

For ExtJS based client (actual implementation in {UB.ux.data.UBStore}) - create store based on request, constructed by Repository. Return promise resolved to loaded {UB.ux.data.UBStore} instance

Arguments:
  • storeConfig: object

    optional config passed to store constructor

  
      UB.Repository('ubm_navshortcut').attrs(['ID', 'code'])
  .where('code', 'in', ['uba_user', 'ubs_audit'])
  .selectAsStore().then(function(store){
     console.log(store.getTotalCount()); // here store is UB.ux.data.UBStore instance
  })
  

# selectById (IDnumber, fieldAliasesoptObject.<string, string>) → Promise.<(object | undefined)> instance

Select a single row by ID. If ubql result is empty - return {undefined}.

Return:

Promise, resolved to {Object|undefined}

Arguments:

# selectColumunar (fieldAliasesoptobject) → Promise.<{}> instance

Asynchronously run request, constructed by Repository. Return promise, resolved to columnar (each attribute values is a separate array) representation.

Useful for data analysis \ graph

Return:

Promise of object with keys = column name and value - array of attribute values

Arguments:
  • fieldAliases: object

    Optional object to change attribute names to column name. Object key is attribute name (as it in .attr) and value is preferred column name

  
      await UB.Repository('req_request')
  .attrs('department.name', 'COUNT([ID])')
  .groupBy('department.name').select({'department.name': 'department', 'COUNT([ID])': 'requestCount'})
// {
//   'department': ["Electricity of Kiev", "Water utility of Kiev"],
//   'requestCount': [5, 44]
// }
  

# selectScalar () → Promise.<(number | string | undefined)> instance

Perform select and return a value of the first attribute from the first row

WARNING method do not check repository contains the single row

Return:

Promise, resolved to {Number|String|undefined}

# selectSingle (fieldAliasesoptObject.<string, string>) → Promise.<(object | undefined)> instance

Select a single row. If ubql result is empty - return {undefined}.

WARNING method do not check repository contains the single row and always return a first row from result

Return:

Promise, resolved to {Object|undefined}

Arguments:

# start (startnumber) → CustomRepository instance

Retrieve first start rows

Arguments:
  
      let store = UB.Repository('my_entity').attrs('ID')
 //will return ID's from 15 to 25
 .start(15).limit(10).select()
  

# ubql () → UBQL instance

Construct a UBQL JSON

  
      let repo = UB.Repository('my_entity').attrs('ID').where('code', '=', 'a')
let inst = UB.DataStore(my_entity)
inst.run('select', repo.ubql())
  

# using (methodNamestring) → CustomRepository instance

Retrieve a data from server using methodName entity method. By default, select method will be used

Arguments:

# where (expressionstring, conditionCustomRepository.WhereCondition | string, valueopt*, optionsoptobject) → CustomRepository instance

Adds where expression

  • the expression may contain one of the following functions: 'SUM', 'COUNT', 'AVG', 'MAX', 'MIN', 'CAST', 'COALESCE', 'LENGTH', 'LOWER', 'UPPER', 'DAY', 'MONTH', 'YEAR', 'ROUND', 'FLOOR', 'CEILING'

  • for a Date/DateTime attributes special macros #maxdate or #currentdate can be used as a value:

    .where('dateValue', '=', '#maxdate')
    .where('dateTimeValue', '<', '#currentdate')
    
  • in and 'notIn` conditions can take a sub-repository as a value parameter value. See CustomRepository.exists for sample

  • for details how array parameters binds to DB query see array binding section in database tuning tutorial

Arguments:
  • expression: string

    Attribute name (with or without []) or valid expression with attributes in []

  • condition: CustomRepository.WhereCondition| string

    Any value from WhereCondition

  • value: *

    Condition value. If undefined value not passed to ubql

  • options: object| string
    • clauseNamestring

      clause name to be used in {CustomRepository.logicalPredicates}

      • If not passed unique clause name will be generated ('_1', '_2', ..).
      • In case a condition with the same name exists, it will be overwritten.

    • clearableboolean

      if === false then clearWhereList() will skip removing this where condition

    If string is passed it means clauseName, otherwise an object {clauseName, clearable}

  
      UB.Repository('my_entity').attrs('ID')
  // code in ('1', '2', '3')
  .where('code', 'in', ['1', '2', '3'])
  // code in (select code from my_codes where id = 10)
  .where('code', 'in', UB.Repository('my_codes').attrs('code').where('ID', '<', 10))
  // name like '%homer%'
  .where('[name]', 'contains', 'Homer')
  //(birtday >= '2012-01-01') AND (birtday <= '2012-01-02')
  .where('[birtday]', 'geq', new Date()).where('birtday', 'leq', new Date() + 10)
  // (age + 10 >= 15)
  .where('[age] -10', '>=', {age: 15}, 'byAge')
  .where('LENGTH([code])', '<', 5)
  // for condition match expression not need
  .where('', 'match', 'myvalue')
  

# whereIf (addingCondition*, expressionstring, conditionCustomRepository.WhereCondition | string, valuesopt*, clauseNameoptstring) → CustomRepository instance

Helper method for CustomRepository.where. Calls where in case addingCondition is truthy

Arguments:
  • addingCondition: *

    Where expression will be added only in case addingCondition is truthy

  • expression: string

    Attribute name (with or without []) or valid expression with attributes in []

  • condition: CustomRepository.WhereCondition| string

    Any value from WhereCondition

  • values: *

    Condition value. If undefined values not passed to ubql

  • clauseName: string

    Optional clause name to be used in {CustomRepository.logicalPredicates} If not passed where will generate unique clause named 'c1', 'c2', ......

  
      let filterString = 'foundAllLikeThis' // or may be empty string
// with whereIf
let repo = UB.Repository('my_entity').attrs('ID').whereIf(filterString, 'myAttr', 'like', filterString)

//without whereIf
let repo = UB.Repository('my_entity').attrs('ID')
if (filterString) repo = repo.where('myAttr', 'like', filterString)
  

# withTotal (valueoptboolean) → CustomRepository instance

Calculate total row number. WARNING!! This is VERY slow operation on DB level in case of many record

Result of calculation is returned in __totalRecCount parameter value in case selectAsArray() client call:

Arguments:
  • value = true: boolean

    If false will remove total requirements

  
      let result = UB.Repository('uba_user')
   .attrs(['ID', 'description'])
   .withTotal().selectAsArray();
console.log('Total count is:', result.__totalRecCount)

// Or into TubDataStore.totalRowCount in case of server side `selectAsStore()` call:
 let store = UB.Repository('uba_user')
   .attrs(['ID', 'description'])
   .withTotal().selectAsStore();
 console.log('Total count is:', store.totalRowCount);