Tables

Service Id: platform6.storedprocedures

1. List a Tables Records

Header key	Description	Value
platform6.request.action	The action to perform (required)	list.records
tableId	The table’s identifier (required)
fields	A records of conditions used to filter the retrieved records
offset	The number which indicates the distance between the table’s first record and the first record displayed (default to 0)
limit	The number of the retrieved records (by default, all the records are returned)
orderByFieldName	The field by which the records are sorted
sortAscending	Whether the sort is ascending or descending (default to true)
platform6.request.user	The email address of the user sending the message

Warning

All the header’s values must be strings!

A valid response will be:

Header key	Description
platform6.response.status	OK
results	The retrieved table’s records
fields	The table’s fields

Examples

We’ll use the Exchange_rates table for the examples.

Currency_From	Currency_To	Rate
ARS	USD	0.069
AUD	USD	0.724508
BDT	USD	0.013
BRL	USD	0.25

The call below will list all the table’s records without further settings.

def cm = [
    headers: [
        'platform6.request.action': 'list.records',
        'tableId': 'Exchange_rates'
    ]
]

def request = p6.service.request('platform6.tables', cm)
print request.headers['results']
print request.headers['fields']

The output will be:

INFO [root] stdout: [["ARS","USD","0.069"],["AUD","USD","0.724508"],["BDT","USD","0.013"],["BRL","USD","0.25"]]
INFO [root] stdout: ["Currency_From","Currency_To","Rate"]

If the table is not found, it will return an error:

{
    "message" : "Requested table is not found: 'Exchange_rates'.",
    "stackTrace" : [
        "io.platform6.common.util.P6Exception: Requested table is not found: 'Exchange_rates'.",
        "    at io.platform6.core.service.tables.TablesAction$class.recordsRecords(TablesAction.scala:29)",
        "    at io.platform6.core.service.tables.TablesService.recordsRecords(TablesService.scala:66)",
        "    at io.platform6.core.service.tables.TablesService.notifyRequestMessage(TablesService.scala:161)",
        "    at io.platform6.core.impl.servicecomponent.AbstractServiceComponent.onCommonMessage(AbstractServiceComponent.java:704)",
        "    at io.platform6.core.impl.platform.messagebus.BusQueueController$ServiceQueueRunner.run(BusQueueController.java:183)",
        "    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)",
        "    at java.util.concurrent.FutureTask.run(FutureTask.java:266)",
        "    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)",
        "    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)",
        "    at java.lang.Thread.run(Thread.java:748)"
    ]
}

Here are some examples with additional parameters:

1. The parameter fields is set to [ Rate: '0.15' ]: it will return all the records for which the Rate field has the value 0.15.

   import groovy.json.JsonOutput
   
   def cm = [
       headers: [
           'platform6.request.action': 'list.records',
           'tableId': 'Exchange_rates',
           'fields': JsonOutput.toJson([ Rate: '0.15' ])
       ]
   ]
   
    print p6.service.request('platform6.tables', cm).headers['results']
   

   The response will be:

   INFO [root] stdout: [["CNY","USD","0.15"],["DKK","USD","0.15"]]
   

2. The parameter offset is set to 1: it will return all the records after the first record excluded.

   def cm = [
       headers: [
           'platform6.request.action': 'list.records',
           'tableId': 'Exchange_rates',
           'offset': '1'
       ]
   ]
   
    print p6.service.request('platform6.tables', cm).headers['results']
   

   The response will be:

   INFO [root] stdout: [["AUD","USD","0.724508"],["BDT","USD","0.013"],["BRL","USD","0.25"]]
   

3. The parameter limit is set to 2: it will return the two first records of the table.

   def cm = [
       headers: [
           'platform6.request.action': 'list.records',
           'tableId': 'Exchange_rates',
           'limit': '2'
       ]
   ]
   
   print p6.service.request('platform6.tables', cm).headers['results']
   

   The response will be:

   INFO [root] stdout: [["AUD","USD","0.724508"],["ARS","USD","0.069"]]
   

4. The parameters orderByFieldName is set to Rate and sortAscending is set to false: it will sort the records in descending order by the Rate field.

   def cm = [
       headers: [
           'platform6.request.action': 'list.records',
           'tableId': 'Exchange_rates',
           'orderByFieldName': 'Rate',
           'sortAscending': 'false'
       ]
   ]
   
   print p6.service.request('platform6.tables', cm).headers['results']
   

   The response will be:

   INFO [root] stdout: [["AUD","USD","0.724508"],["BRL","USD","0.25"],["ARS","USD","0.069"],["BDT","USD","0.013"]]
   

2. Upsert Records to a Table

Header key	Description	Value
platform6.request.action	The action to perform (required)	upsert.records
tableId	The table’s identifier (required)
records	A new set of records to insert into the table

If a new record has the same values for the primary keys than an existing record, the existing record’s values are updated with the new ones.

If not, the new record is added to the table.

If the list of records is empty, nothing happens.

_A valid response will be:

Header key	Description
platform6.response.status	OK

No headers are returned in the response.

Examples

We’ll use again the Exchange_rates table for the example.

Currency_From	Currency_To	Rate
ARS	USD	0.069
AUD	USD	0.724508
BDT	USD	0.013
BRL	USD	0.25

The fields Currency_From and Currency_To are the table’s primary keys.

import groovy.json.JsonOutput

def records = []

def record1 = [
    Currency_From: 'CAD',
    Currency_To: 'USD',
    Rate: '0.728980'
]
records << record1

def record2 = [
    Currency_From: 'CHF',
    Currency_To: 'USD',
    Rate: '1.03'
]
records << record2

def record3 = [
    Currency_From: 'ARS',
    Currency_To: 'USD',
    Rate: '0.15'
]
records << record3

def cm = [
    headers: [
        'platform6.request.action': 'upsert.records',
        'tableId': 'Exchange_rates',
        'records': JsonOutput.toJson(records)
    ]
]

p6.service.request('platform6.tables', cm)

Since the record3’s primary keys already exist in the table, the field Rate will be updated with the new value.

The primary keys of the records record1 and record2 do not already exist in the table so the two records will be added to the table.

The resulting table will be:

Currency_From	Currency_To	Rate
ARS	USD	0.15
AUD	USD	0.724508
BDT	USD	0.013
BRL	USD	0.25
CAD	USD	0.728980
CHF	USD	1.03

3. Insert Records in a Table

Header key	Description	Value
platform6.request.action	The action to perform (required)	insert.records
tableId	The table’s identifier (required)
records	A new set of records to insert into the table

_A valid response will be:

Header key	Description
platform6.response.status	OK

4. Delete Records from a Table

Header key	Description	Value
platform6.request.action	The action to perform (required)	delete.records
tableId	The table’s identifier (required)
records	A new set of records to delete from the table

_A valid response will be:

Header key	Description
platform6.response.status	OK

5. Update Table Data Indexes

Header key	Description	Value
platform6.request.action	The action to perform (required)	table.updateindexes
tableId	The table’s identifier (required)

_A valid response will be:

Header key	Description
platform6.response.status	OK
platform6.response.value	[job id of indexing job]

6. Test for Table Existence Given Name and AppKey

Header key	Description	Value
platform6.request.action	The action to perform (required)	table.exist
tableName	The table’s name (required)
appKey	The table’s appKey (required)

_A valid response will be:

Header key	Description
platform6.response.status	true

7. Test for Table Existence Given Name and AppKey

Header key	Description	Value
platform6.request.action	The action to perform (required)	table.create
tableModel	TableModel: JSON Table Model (required)

_A valid response will be:

Header key	Description
platform6.response.status	OK

8. Test for Table Existence Given Name and AppKey

Header key	Description	Value
platform6.request.action	The action to perform (required)	table.fields
tableId	The table’s identifier (required)

_A valid response will be:

Header key	Description
platform6.response.status	OK
fields	List: table fields
keys	List: table keys