Difference between revisions of "Querying your own database"

Revision as of 10:34, 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 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 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()

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.