Querying your own database

Your entire database can be downloaded in a single file. Please see How to backup your data.

If you are familiar with administering relational databases, you may wish to rebuild the database into a local MySQL or MariaDb server. This may be for:

• Advanced reporting using SQL queries
• As an emergency backup, e.g. during outages at the data centre or network infrastructure.

You can then query the database using a tool like MySQL Workbench or HeidiSQL.

This process is not officially supported, but we very much believe your data is your own, and this page provides some sample queries to help understand the underlying structure of the database.

A list of people

The types of Organisations and People are stored in the organisations_types table. The orgs/people are stored in the organisations table. A list of active volunteers can be found using:

SELECT orgtype_org_field_name

FROM organisations_types

WHERE orgtype_name_plural = 'Volunteers'

Which returns 'org_volunteer'. Then use that to select from the organisations table:

SELECT *

FROM organisations

WHERE org_volunteer = 'Y' AND org_active = 'Y'

Contacts and Referrals

Note the LEFT JOIN for jcitems, because it is valid for contacts to have no time and travel associated with them

SELECT *

FROM actions

JOIN diary ON action_diary_id = diary_id

JOIN organisations AS client ON diary_client_id = client.org_id

WHERE action_done_date > '2024-01-01 00:00:00'

Ouststanding Roster Job Cards

Note the LEFT JOIN for workers, because it is valid for a client to have a job card which does not have an assigned worker.

SELECT *

FROM carer_jcards

JOIN carer_time_worked ON cjcard_ctwork_id = ctwork_id

JOIN organisations AS client ON ctwork_client_id = client.org_id

LEFT JOIN organisations AS worker ON cjcard_actual_carer_id = worker.org_id

WHERE cjcard_date_work_due > NOW()

Projects

Projects are stored in the projects table. For most reports you typically only want certain projects. Most tables relating to activity will either have a _project_id field, or a _diary_id field which joins to the diary table (referrals) then use the diary_project field.

Data Dictionary

Most of the relations for fields on the client record can be found in the datadict table.

Extension Databases

These are created as individual tables called ext_1, ext_2, ext_3 etc. Their associated metadata is stored in the tables beginning ext__

Support

Please note, although querying the database using the backup is possible, it is not formally supported and therefore we can't advise or support you through this process.