SQL coding guidelines

SQL queries Zabbix components create/use must adhere to the following guidelines.

General

  • SQL statements should always be uppercase for the frontend code, and lowercase for server, proxy and database upgrade patches. This makes it easier to distinguish query source when debugging.
  • All column identifiers must be written in lowercase. This is required because, as opposed to other databases, PostgreSQL returns results with keys in lowercase.
  • Spaces should not be used around operators (comparison, mathematical, assignment, etc) or commas that separate fields or values. This reduces the amount of traffic to an SQL server.
  • Only single spaces should be used.
  • Inside a query, only spaces should be used, not tabs.
  • For a multi-line query, spaces should always be added at the beginning of a new line, not at the end of the previous one.
  • For PHP, table aliases should always be used, regardless of the number of tables involved in a query. For server and proxy, they are only used if more than one table is involved in a query.
    Specifically, in database patches:
    • If a table alter query changes only one property, it takes one line. If it changes multiple properties, each property (including the first) gets its own line.

Server

  • When the newline is required, it must start from SQL keyword - 'from', 'where', 'and', 'order by' etc.
  • Closing bracket indentation must match the opening bracket.
  • Inner selects must not have redundant empty spaces.
  • When using 'not exists' construct, null needs to be selected (not 1).
Examples

Server SQL statements:

"select hostid,host from hosts where hostid=" ZBX_FS_UI64 " and status=%d;"
"update hosts set host='%s',ip='%s',useip=%d where hostid=" ZBX_FS_UI64 ";"

Wrong:

    if (ZBX_DB_OK > zbx_db_execute("delete from item_rtdata"
            " where exists ("
                " select 1 from items i where"
                    " item_rtdata.itemid=i.itemid and i.flags=2"
                ")"))

Right:

    if (ZBX_DB_OK > zbx_db_execute("delete from item_rtdata"
            " where exists ("
                "select null from items i where item_rtdata.itemid=i.itemid and i.flags=2"
            ")"))

All SQL parts in a multi-line query should have tree style

$sqlParts['where']['monitored'] = 'NOT EXISTS ('.
    'SELECT NULL'.
    ' FROM functions f,items i,hosts h'.
    ' WHERE t.triggerid=f.triggerid'.
        ' AND f.itemid=i.itemid'.
        ' AND i.hostid=h.hostid'.
        ' AND ('.
            'i.status<>'.ITEM_STATUS_ACTIVE.
            ' OR h.status<>'.HOST_STATUS_MONITORED.
        ')'.
    ')';