See www.zabbix.com for the official Zabbix site.
SQL coding guidelines
SQL queries Zabbix components create/use must adhere to the following guidelines.
- SQL statements are always uppercase for the frontend code and database upgrade patches, and lowercase for server and proxy. This makes it easier to distinguish query source when debugging.
- All column identifiers must be written in lower-case. This is required because, as apposed to other databases, PostgreSQL returns results with keys in lower-case.
- No spaces are used around operators (comparison, mathematical, assignment etc) or commas that separate fields or values. This reduces amount of traffic to the SQL server.
- Only single spaces are used.
- Inside a query, only spaces are used, not tabs.
- For a multi-line query, spaces are always 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 it's own line.
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 ";"
- All SQL parts in multi-line query should be 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. ')'. ')';