Developers

UPDATE

Syntax

The UPDATE statement allows values or sub-trees in the PQL database to be modified.

pql_statement ::= UPDATE [@timestamp] path
SET|MERGE assignment_list
[WHERE boolean_expression]
timestamp ::= 'yyyy-mm-ddThh:mm:dd.uuuuuuZ'
assignment_list
assignment		 ::=
::=		 assignment [, assignment]
path = expression | path = tree_literal
tree_literal
tree_assignment		 ::=
::=		 {tree_assignment [, tree_assignment]}
identifier => expression | identifier => tree_literal

Description

UPDATE Modifies data in the PQL tree. If a timestamp is specified then the update is performed as if at that time in the past and so can be used to modify history for the tree.

The path indicates the starting point in the tree to perform the modification. The actual nodes to be updates are determined by concatenating the path with each sub-path specified in the assignment clauses.

SET | MERGE An update statement can either perform a SET operation where old data is replaced with new data, or a MERGE operation where Paglo makes an intelligent decision about which data to insert, which to update (replace), and which data to leave unchanged.
WHERE Limits which nodes are considered for update.

Examples


Example 1: Simple update

Suppose you want to update your access point data. You could write a PQL statement like this:

  UPDATE /network/device/interface[name='eth0']
  SET name = 'eth17'

This PQL statement starts with the UPDATE keyword to replace existing data with new data. It is followed by the path, which specifies the nodes containing the data to replace. The SET name = 'eth17' clause indicates the new values.

If run against the sample database, this statement produces the following results:

row 
  * 
    network 
      device 
        system 
          name 5627 
          computed_vendor LANIER 
          computed_model 5627 
          computed_class printer 
          computed_score 115 
        os 
          version LANIER 5627 5.20 
        interface 
          name eth17 
          mac_address 01:01:02:03:04:05 
          in_octets 1995335536 
          out_octets 248342455 
          oper_status 1 
      device 
        system 
          name ESMITH 
          computed_vendor Intel 
          computed_class server 
          computed_score 10 
        os 
          version Linux 
        interface 
          name eth17  
          mac_address 02:A1:A2:A3:A4:A4 
          in_octets 1229592351 
          out_octets 1472928781 
          oper_status 1 
        interface 
          name eth1 
          mac_address 03:A1:A2:A3:A4:A5 
          in_octets 0 
          out_octets 0 
          oper_status 0 
      device 
        system 
          name AJONES 
          computed_vendor Intel 
          computed_class server 
          computed_score 10 
        os 
          version Linux 
        interface 
          name eth17  
          mac_address 04:A1:A2:A3:A4:A8 
          in_octets 1223098455 
          out_octets 1523093749 
          oper_status 1 
        interface 
          name eth1 
          mac_address 05:A1:A2:A3:A4:A9 
          in_octets 0 
          out_octets 0 
          oper_status 0 
      device 
        system 
          computed_model Ethernet Switch 
          computed_class switch 
          computed_score 25 
        os 
          version Ethernet Switch 
        interface 
          name eth17  
          mac_address 06:13:72:F3:0A:F5 
          in_octets 0 
          out_octets 0 
          oper_status 2 
        interface 
          name eth1 
          mac_address 07:13:72:F3:0A:F6 
          in_octets 0 
          out_octets 0 
          oper_status 2 
      device 
        system 
          name KRYPTON 
          computed_model Windows Workstation 
          computed_class workstation 
          computed_score 125 
        os 
          version Darwin Kernel Version 9.2.2 
        interface 
          name eth17  
          mac_address 08:16:CB:FF:FE:66 
          in_octets 0 
          out_octets 346 
          oper_status 1 
        interface 
          name eth1 
          mac_address 09:16:CB:FF:FE:67 
          in_octets 0 
          out_octets 0 
          oper_status 2 
        interface 
          name eth2 
          mac_address 10:16:CB:FF:FE:68 
          in_octets 0 
          out_octets 0 
          oper_status 2 
        interface 
          name eth3 
          mac_address 11:16:CB:FF:FE:69 
          in_octets 1598233842 
          out_octets 2360815490 
          oper_status 1 
      device 
        system 
          name NISSINGETTY 
          computed_vendor Juniper Networks 
          computed_model Netscreen Router 
          computed_class router 
          computed_score 60 
        os 
          version NetScreen-5GT 5.1.043a 
        interface 
          name eth17  
          mac_address 12:10:D8:99:2B:C2 
          in_octets 1385354750 
          out_octets 1451321493 
          oper_status 1 
        interface 
          name eth1 
          mac_address 13:10:D8:99:2B:C1 
          in_octets 3312952833 
          out_octets 3239791359 
          oper_status 1

Example 2: Specify the node to update

You must specify the exact node for update. You can do so in two ways:


Example 2a: Specify nodes through qualified paths

This statement specifies the node through a qualified path:

UPDATE /network/device
SET interface[mac_address='01:01:02:03:04:05']/oper_status 
   = '3'
WHERE name = 'eth0'

If run against the sample database, this statement produces the following results:

row 
  * 
    network 
      device 
        system 
          name 5627 
          computed_vendor LANIER 
          computed_model 5627 
          computed_class printer 
          computed_score 115 
        os 
          version LANIER 5627 5.20 
        interface 
          name eth0
          mac_address 01:01:02:03:04:05
          in_octets 1995335536 
          out_octets 248342455 
          oper_status 3 
      device 
        system 
          name ESMITH 
      . . .

Example 2b: Specify nodes through predicates

This example specifies the node through the predicate:

UPDATE /network/device/interface[mac_address = 
  '01:01:02:03:04:05']
SET mac_address='A1:A1:A2:A3:A4:A5'

If run against the sample database, this statement produces the following results:

row 
  * 
    network 
      device 
        system 
          name 5627 
          computed_vendor LANIER 
          computed_model 5627 
          computed_class printer 
          computed_score 115 
        os 
          version LANIER 5627 5.20 
        interface 
          name eth0
          mac_address A1:A1:A2:A3:A4:A5
          in_octets 1995335536 
          out_octets 248342455 
          oper_status 1 
      device 
        system 
          name ESMITH 
      . . .

Example 3: Set multiple values

This example specifies the node through a predicate, while setting multiple values in the same statement.

UPDATE /network/device/interface[mac_address = 
  '01:01:02:03:04:05']
SET out_octets='3000000000', in_octets='2000000000'

If this statement is run on the sample database, it produces the following results:

row
  *
    network
      device
        system
          name 5627
          computed_vendor LANIER
          computed_model 5627
          computed_class printer
          computed_score 115
        os
          version LANIER 5627 5.20
        interface
          name eth0
          mac_address 01:01:02:03:04:05
          in_octets 2000000000
          out_octets 3000000000
          oper_status 1
      device
        system
          nameESMITH
      . . .

Example 4: Update historical values

This example updates historical values by adding a timestamp. Note that the timestamp must be in single quotes: '2008-04-05T19:06:05.192700Z'.

UPDATE@'2008-04-05T19:06:05.192700Z' /network/device
SET traffic_in = 700, traffic_out = 800
WHERE interface/mac_address = '01:01:02:03:04:05'

This statement produces the same results:

row: 
row
  *
    network
      device
        system
          name 5627
          computed_vendor LANIER
          computed_model 5627
          computed_class printer
          computed_score 115
        os
          version LANIER 5627 5.20
        interface
          name eth0
          mac_address 01:01:02:03:04:05
          in_octets 700
          out_octets 800
          oper_status 1
      device
        system
          name ESMITH
      . . .

After you update the database, you can then run a query to see the resulting history: 

 SELECT history(
   traffic_in, 
   traffic_out, 
   interface/ip_address
 ) 
 FROM /network/device

If this statement is run on the sample database, it produces the following results:

row
  history(traffic_in, traffic_out, interface/ip_address)
    history
      when 2008-04-05T19:06:05.192700Z
    history
      when2008-04-06T02:18:05.192700Z
    history
      when2008-04-06T09:30:05.192700Z
    history
      when2008-04-06T16:42:05.192700Z
    history
      when2008-04-06T23:54:05.192700Z
    history
      when2008-04-07T07:06:05.192700Z
    history
      when2008-04-07T14:18:05.192700Z
    . . .

Compare UPDATE and INSERT

UPDATE statements replace values; INSERT adds them. In the following example the INSERT statement adds a new PRINTER, whereas the UPDATE statement changes the name of the 5627 printer to PRINTER:

INSERT INTO / VALUES {
  device => {
    name => 'PRINTER', 
     interface => {
       mac_address => 'B1:B2:B3:B4:B5:B6'
    }
  }
} 

UPDATE /network/device/system[name = '5627']
SET name = 'PRINTER'

If run against the sample database, the INSERT statement produces very different results from the UPDATE statement:

row
  *
    network
      device
        name PRINTER
          interface
            mac_address B1:B2:B3:B4:B5:B6 
      device
        system
          name PRINTER
          computed_vendor LANIER
          computed_model 5627
          computed_class printer
          computed_score 115
        os
          version LANIER 5627 5.20 
        interface
          name eth0
          mac_address A1:A1:A2:A3:A4:A5
          in_octets 1995335536
          out_octets 248342455
          oper_status 1
      device
        system
          name ESMITH
      . . .

For more examples, see Examples and More examples.



How do I find out more?

Camera
News
Check out the
headlines
Blog_icon_blue
Blog! Blog! Blog!
Straight talk from the
open enterprise