This package contains the functionality to search database tables of zammad via REST.
Be aware that this package is still in early development and will be reworked heavily in the next releases.
To use the endpoints you will need a admin with the permissions admin.table_search
.
Note: Do only use this package with PostgresSQL. There will be no MySQL support anymore, since it already deprecated.
-
Click in your admin interface to the area Admin -> Packages.
-
Download the current version for the package which is compatible to your zammad version.
-
Install the package.
-
Run all displayed commands in the UI on your shell and restart your zammad service.
This endpoint shows all tables existing in zammad.
$ curl -s -uadmin@example.com:test http://ubuntu-rs:3000/api/v1/tables | jq .
{
"ticket_flags": "Ticket::Flag",
"ticket_priorities": "Ticket::Priority",
"ticket_shared_draft_starts": "Ticket::SharedDraftStart",
"ticket_shared_draft_zooms": "Ticket::SharedDraftZoom",
"ticket_states": "Ticket::State",
"ticket_state_types": "Ticket::StateType",
"ticket_time_accounting_types": "Ticket::TimeAccounting::Type",
"ticket_time_accountings": "Ticket::TimeAccounting",
"tickets": "Ticket",
...
}
To get an overview over all columns of an table you can use this endpoint.
$ curl -s -u"admin@example.com:test" http://ubuntu-rs:3000/api/v1/tables/ticket_priorities/columns | jq .
{
"id": "integer",
"name": "string",
"default_create": "boolean",
"ui_icon": "string",
"ui_color": "string",
"note": "string",
"active": "boolean",
"updated_by_id": "integer",
"created_by_id": "integer",
"created_at": "datetime",
"updated_at": "datetime"
}
If you only need one row then there is also a show endpoint:
$ curl -s -u"admin@example.com:test" http://ubuntu-rs:3000/api/v1/tables/ticket_priorities/1 | jq .
{
"id": 1,
"name": "1 low",
"default_create": false,
"ui_icon": "low-priority",
"ui_color": "low-priority",
"note": null,
"active": true,
"updated_by_id": 1,
"created_by_id": 1,
"created_at": "2023-09-04T10:45:15.514Z",
"updated_at": "2023-09-04T10:45:15.548Z"
}
This is the magic endpoint which you can use for every table and where you can use the operators.
$ curl -s -uadmin@example.com:test http://ubuntu-rs:3000/api/v1/tables/ticket_priorities?id=1 | jq .
[
{
"id": 1,
"name": "1 low",
"default_create": false,
"ui_icon": "low-priority",
"ui_color": "low-priority",
"note": null,
"active": true,
"updated_by_id": 1,
"created_by_id": 1,
"created_at": "2023-09-04T10:45:15.514Z",
"updated_at": "2023-09-04T10:45:15.548Z"
}
]
to paginate thorugh the results you can use the offset
(default: 0) and limit
(default: 500) parameter.
$ curl -s -uadmin@example.com:test http://ubuntu-rs:3000/api/v1/tables/ticket_priorities?id=1&offset=1&limit=100 | jq .
Exact values match for all columns.
http://ubuntu-rs:3000/api/v1/tables/tickets?id=1
http://ubuntu-rs:3000/api/v1/tables/tickets?id_not=1
Range condtions for date columns.
ge, gt, le, lt
http://ubuntu-rs:3000/api/v1/tables/tickets?created_ge=2023-09-04T10:45:16.017Z
http://ubuntu-rs:3000/api/v1/tables/tickets?created_gt=2023-09-04T10:45:16.017Z
http://ubuntu-rs:3000/api/v1/tables/tickets?created_le=2023-09-04T10:45:16.017Z
http://ubuntu-rs:3000/api/v1/tables/tickets?created_lt=2023-09-04T10:45:16.017Z
Fulltext search string columns.
http://ubuntu-rs:3000/api/v1/tables/tickets?search=Welcome
Contains for string columns.
http://ubuntu-rs:3000/api/v1/tables/tickets?title_contains=Welcome
http://ubuntu-rs:3000/api/v1/tables/tickets?title_contains_not=Welcome
Regex match for string columns
http://ubuntu-rs:3000/api/v1/tables/ticket_priorities?name_regex=(low|high)
http://ubuntu-rs:3000/api/v1/tables/ticket_priorities?name_regex_not=(low|high)
Empty match for string columns.
http://ubuntu-rs:3000/api/v1/tables/ticket_priorities?note_empty=1
http://ubuntu-rs:3000/api/v1/tables/ticket_priorities?note_empty_not=1
Empty match for all columns.
http://ubuntu-rs:3000/api/v1/tables/ticket_priorities?note_null=1
http://ubuntu-rs:3000/api/v1/tables/ticket_priorities?note_null_not=1
In array for all columns.
http://ubuntu-rs:3000/api/v1/tables/tickets?id_in=1,2,3
http://ubuntu-rs:3000/api/v1/tables/tickets?id_in_not=1,2,3
MIT