CRUD on DataModels

Create Query:

Create DataModel Query

#
Query:
  Create:
    VendorInfo:
      vendorName: "Phidget"
      vendorTitle: "Sensor Provider"

Create Query Result:

Note that VendorSysId of type Sid is automatically generated without specifying the value in Create Query Statement.

Create DataModel Query Results

#
Create(Status: "Success"):
  VendorInfo:
    VendorSysId: "KNTJ5MYXAAAAUAABA7EIY2FH":
    vendorName(Status: "Success+Created:1:1457715589911;", Value: "Phidget")
    vendorTitle(Status: "Success+Created:1:1457715589914;", Value: "Sensor Provider")

Find Query:

Find DataModel Query

#
Query:
  Find(format: "all"):
    VendorInfo:
      vendorName(eq: "Phidget")

Find Query Result:

Find DataModel Query Result

#
Find(Status: "Success", Format: "all"):
  Result:
    VendorInfo(QName: "SimpleModel.MyModels.VendorInfo"):
      VendorSysId: "KNRX3TBUAAAAUAABA4LIWHP2"
      vendorTitle(Value: "Sensor Company", Known: "Sensor Company", Version: "1", Timestamp: "1457663102018", DateTime: "2016-03-10 18:25:02.018", QName: "SimpleModel.MyModels.VendorInfo.vendorTitle", FName: "vendorTitle")
      vendorName(Value: "Phidget", Known: "Phidget", Version: "1", Timestamp: "1457663102004", DateTime: "2016-03-10 18:25:02.004", QName: "SimpleModel.MyModels.VendorInfo.vendorName", FName: "vendorName")

Example of Find Filter on Attributes of type Datetime

Datamodel definiton

#
DataModel(Name: "MyTasks"):
  Sid(Name: "TaskID")
  String(Name: "TaskName")
  String(Name: "TaskDesc")
  DateTime(Name: "TaskTime", Format: "$SimpleDateFormat(yyyy-MM-dd'T'HH:mm:ss'Z')")
  # TaskTime is a DateTime of SimpleDateFormat
  Integer(Name: "TaskStatus")
  Integer(Name: "RemindBeforeXMinutes")

Datetime with simple Greater Than filter

Datetime filter

#
Query:
  Find:
    MyTasks:
      TaskID(ne: "")
      TaskTime(gt: "[:$Now('%1$tY-%1$tm-%1$tdT%1$tTZ'):]")  # Get the time in SimpleDateFormat

Datetime with Range-based filters

Datetime filter

#
Query:
  Find(format: "Version"):
    MyTasks:
      TaskID(ne: "")
      TaskTime(ge: "[:$Now('%1$tY-%1$tm-%1$tdT%1$tTZ', +PT15M):]")
      TaskTime(lt: "[:$Now('%1$tY-%1$tm-%1$tdT%1$tTZ', +PT6M):]")
      RemindBeforeXMinutes(le: "15")
      RemindBeforeXMinutes(gt: "0")

Update Query:

Update DataModel Query

#
Query:
  Find(format: "version"):
    VendorInfo:
      vendorName(eq: "Phidget")
  SetResponseData:
    Key: Message.Value.Find.Result.VendorInfo.vendorName
    Value: "Phidget Inc"
  Update:
    From: Result
    Include: $findResponse.Message.Value.Find

Update Query Result:

Note that the Version number is incremented automatically if the update is successful.

Update DataModel Query

#
Find(Status: "Success", Format: "version"):
  Result:
    VendorInfo:
      VendorSysId: "KNTMS6F7AAAAUAABA7SVACBP"
      vendorTitle(Value: "Sensor Provider", Version: "1")
      vendorName: "Phidget Inc"
Update(Status: "Success", Format: "version"):
  VendorInfo:
    VendorSysId: "KNTMS6F7AAAAUAABA7SVACBP"
    vendorName(Status: "Success=Updated:2:1457718402256;", Value: "Phidget Inc", Version: "2")
    vendorTitle(Status: "Success_NoAction:1:1457718393024;", Value: "Sensor Provider", Version: "1")

Delete Query:

Delete DataModel Query

#
Query:
  DeleteAll:
    VendorInfo:
      vendorName(eq: "Phidget")

DataModel with one Complex type.

DataModel with one Complex Type

#
Def(Name: "PostalAddress"):
  String(Name: "Street")
  String(Name: "City")
  String(Name: "Zipcode")
DataModel(Name: "VendorInfo"):
  Sid name: "VendorSysId")
  String(Name: "vendorName")
  String(Name: "vendorTitle")
  PostalAddress name: "VendorAddress")

DataModel with one Attribute that is of Array defined via Cardinality.

Save Query Results to a CSV

In some cases the user may want to save the query results to a CSV file. This can be achieved easily using 3 step process:

Find Data
Convert result to comma separated lines
Save the content
View the content

For example: Let's say you have a DataModel VendorInfo as defined below.

Sample project

#
Namespace(Name: "SimpleModel"):
  Domain(Name: "MyModels"):
    DataModel(Name: "VendorInfo"):
      Sid name: "VendorSysId")
      String(Name: "vName")
      String(Name: "vTitle")
      String(Name: "vLocation")

Generic Macro to Save File:

You can make is as part of your project.

Note the content has to be part of ProcessData.

Generic SaveFile Macro

#
Macro(Name: "SaveFile"):
  Argument:
    FilePath: ""
  Result:
    JexlScript:
      var file = "[:$Macro.Argument.FilePath:]";
      var data = sffContext.getProcessData(["toCSV","result"]);
      sffLog.info("Data", + data);
      sff:saveTxt("text", file, data);
      return null;

FindConvertAndSave

#
Query:
  Find:
    VendorInfo:
      vName(ne: "")
  SetProcessData(key: "toCSV.result", value: "Name,Title,Location[:EOL:]")
  foreach(value: $vendor, in: $findResponse.Message.Value.Find.Result.VendorInfo):
    SetProcessData key: "toCSV.result", Value: "[:$ProcessData.toCSV.result:][:$TL.vendor.vName:],[:$TL.vendor.vTitle:],[:$TL.vendor.vLocation:][:EOL:]")
  SaveFile:
    FilePath: "./resources/querydata.csv"

Save Query Output to a File

#
Query:
  Find:
    VendorInfo:
      vName(ne: "")
  SetProcessData key: "toCSV.result", value: "Name,Title,Location[:EOL:]")
  foreach(value: $vendor, in: $findResponse.Message.Value.Find.Result.VendorInfo):
    SetProcessData key: "toCSV.result", Value: "[:$ProcessData.toCSV.result:][:$TL.vendor.vName:],[:$TL.vendor.vTitle:],[:$TL.vendor.vLocation:][:EOL:]")
  SetLocalData key: "FILE", value: "#o'resources/querydata.csv'#")
  logInfo = [:[:$TL.FILE:][:$ProcessData.toCSV.result:]:]

Finally to View the file:

View OR Download File

http://<ip>:<port>/fid-tqlengineres/<nameoffile>

TQL Docs

CRUD on DataModels

Save Query Results to a CSV

Related pages