add support for cockroachDB

[ik_zelf]

cockroachDB is a very nice database, built for cloud usage, from the ground up. The database is built with postgres client compatibility in mind but it wil never get the full 100%.

For that reason it would be nice to add support for it in a way that the minor differences with postgres can be handled.

One thing would be to use select version(); to detect the correct database:
CockroachDB CCL v2.0-alpha.20180212 (darwin amd64, built 2018/02/12 17:56:22, go1.9.3)
or
PostgreSQL 9.6.5 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

currently set schema is not supported. If zabbix defaults to checking for tables using the username instead of public, the tests succeed.
src/zbxdbhigh/db.c should be changed
(
#elif defined(HAVE_POSTGRESQL)
2049 table_schema_esc = DBdyn_escape_string(NULL == CONFIG_DBSCHEMA || '\0' == *CONFIG_DBSCHEMA ?
2050 "public" : CONFIG_DBSCHEMA);
)

and in src/zbxdb/db.c
(
/* disable "nonstandard use of \' in a string literal" warning */
555 if (0 < (ret = zbx_db_execute("%s", "set escape_string_warning to off")))
556 ret = ZBX_DB_OK;
)
and
if (90000 <= ZBX_PG_SVERSION)
574 {
575 /* change the output format for values of type bytea from hex (the default) to escape */
576 if (0 < (ret = zbx_db_execute("%s", "set bytea_output=escape")))
577 ret = ZBX_DB_OK;
578 }

should be ignored.
After that there is a slow query, probably due to way that cockroach is using partitioning:
1010:20180222:200634.834 slow query: 105.797424 sec, "select distinct d.triggerid_down,d.triggerid_up from trigger_depends d,triggers t,hosts h,items i,functions f where t.triggerid=d.triggerid_down and t.flags<>2 and h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=d.triggerid_down and h.status in (0,1)"

1120:20180222:204044.524 slow query: 19.266346 sec, "select itemid,min(clock) from history group by itemid"

1120:20180222:204128.773 slow query: 41.102825 sec, "select itemid,min(clock) from history_uint group by itemid"

1120:20180222:204131.798 slow query: 3.022609 sec, "select itemid,min(clock) from history_text group by itemid"

1120:20180222:204156.105 slow query: 24.306423 sec, "select itemid,min(clock) from trends group by itemid"

1128:20180222:204159.752 slow query: 3.282665 sec, "select distinct userid,mediatypeid from alerts where actionid=7 and eventid=273414 and mediatypeid is not null and alerttype=0"

1120:20180222:204250.121 slow query: 54.014412 sec, "select itemid,min(clock) from trends_uint group by itemid"

I have also opened an issue in cockroachDB GitHub https://github.com/cockroachdb/cockroach/issues/22937

One extra note: this database requires all foreign key's to be indexed. Not a big issue but this surprised me a bit.

[ik_zelf]

found a large query improvement in libs/zbxdbcache/dbsync.c :
int zbx_dbsync_compare_trigger_dependency(zbx_dbsync_t *sync)
{
DB_ROW row;
DB_RESULT result;
zbx_hashset_t deps;
zbx_hashset_iter_t iter;
ZBX_DC_TRIGGER_DEPLIST *dep_down, *dep_up;
zbx_uint64_pair_t *dep, dep_local;
char down_s[MAX_ID_LEN + 1], up_s[MAX_ID_LEN + 1];
char *del_row[2] = {down_s, up_s};
int i;

if (NULL == (result = DBselect(
"select distinct d.triggerid_down,d.triggerid_up"
" from trigger_depends d,triggers t,hosts h,items i,functions f"
" where t.triggerid=d.triggerid_down"
" and t.flags<>%d"
" and h.hostid=i.hostid"
" and i.itemid=f.itemid"
" and f.triggerid=d.triggerid_down"
" and h.status in (%d,%d)",
ZBX_FLAG_DISCOVERY_PROTOTYPE, HOST_STATUS_MONITORED, HOST_STATUS_NOT_MONITORED)))
{
return FAIL;
}

cockroach seems to generate better plans with ANSI joins. The above query takes 2 minutes,
rewritten as:
select distinct d.triggerid_down,d.triggerid_up
from trigger_depends d join triggers t
on (t.triggerid = d.triggerid_down)
join functions f
on (f.triggerid = d.triggerid_down)
join items i
on (i.itemid = f.itemid)
join hosts h
on (h.hostid = i.hostid)
where t.flags<>2
and h.status in (0,1)

it runs as expected, a few ms.