Difference between revisions of "Querying your own database"
| (20 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | Your entire database can be downloaded in a single file. Please see [ | + | Your entire database can be downloaded in a single file. Please see [[Backup_Data | 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: | + | 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 | * Advanced reporting using SQL queries | ||
* As an emergency backup, e.g. during outages at the data centre or network infrastructure. | * As an emergency backup, e.g. during outages at the data centre or network infrastructure. | ||
| − | This page provides some sample queries to help understand the underlying structure of the database. | + | You can then query the database using a tool like [https://www.mysql.com/products/workbench/ MySQL Workbench] or [https://www.heidisql.com/ 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''' | '''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: | 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: | ||
| + | |||
| + | <code> | ||
| + | SELECT orgtype_org_field_name | ||
| + | |||
| + | FROM organisations_types | ||
| + | |||
| + | WHERE orgtype_name_plural = 'Volunteers' | ||
| + | |||
| + | </code> | ||
| + | |||
| + | Which returns 'org_volunteer'. Then use that to select from the organisations table: | ||
| + | |||
<code> | <code> | ||
| − | + | SELECT * | |
| + | |||
| + | FROM organisations | ||
| + | |||
| + | WHERE org_volunteer = 'Y' AND org_active = 'Y' | ||
</code> | </code> | ||
| + | |||
| + | |||
| + | '''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. | ||
| + | <code> | ||
| + | |||
| + | 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() | ||
| + | </code> | ||
| + | |||
| + | |||
| + | '''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. | ||
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.
