Difference between revisions of "Querying your own database"

Latest revision as of 10:57, 16 October 2024

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'

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.

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.