Tutorial on using sqlite on GRAV
Task
The Lacrosse team has asked for a web interface to keep track of player information. (This is based on a question I was asked on the GRAV forum).Solution
In this tutorial, I will show one way to list the data, add to the data, update data. It is possible to add a button to delete rows, but I think that sort of power should be left with the site administrator, not with an ordinary user.
It is assumed that anyone reading this tutorial will have already installed a version of GRAV, and installed the Admin plugin. Consequently, later when I write 'install <plugin>', I can assume that the reader will know to login to the Admin plugin, click on the plugins tab, search for the plugin name and install it. There are other, more painful, ways to install a plugin, but if you know about them, you know enough for this tutorial.
When I first started using GRAV, I found the directory structure confusing. So for the sake of clarity, I will assume that GRAV has been installed on a Linux based server with the full path '/var/www/html/', which means that the following directories will exist: '/var/www/html/user/pages', /var/www/html/user/data', etc. Rather than write this out all the time, I will write, eg., '<grav root>/user/data'.
In addition, the reader should know the basics about the way GRAV sets up the directory structure, and how to write the *.md files that get translated into pages on the website. So the information of the 'landing page' of the website might possibly be at '<grav root>/user/pages/01.team/default.md'. In this tutorial, I will stick with vanilla templates, such as 'default' and 'form' (I assume you understand about GRAV templates and how a file called 'default.md' is associated with a template called 'default.html.twig').
There are several ways to approach the task, so for clarity I have made some choices. The aim is to explain how to integrate a database using the sqlite plugin and data presentation using datatables, not how to structure databases, write plugins or create a good website.
First lets look at the problem. Suppose that the data has already been kept in an SQL database, and the schema is:
CREATE TABLE tryouts (tryoutid integer primary key, team text, name text, grade integer, age integer, phone text, email text, parent1 text, p1phone text, p1email text, parent2 text, p2phone text, p2email text);
An obvious comment is that this does not look to be an optimal table design. I looks more like an SQL table that was created using data originally kept in a speadsheet, such as Excel. However, table design and storage of information is a another subject.
The main issues for good database design is how easy it is to update the data, and how easy is it to obtain from the data base answers to queries you would like to make. We will invent some queries below based on the database schema we have.
The first thing to do is to install the sqlite plugin for GRAV. It should not have any significance for a simple database, but the sqlite plugin uses the PHP Sqlite3 module. If you are following this tutorial with an existing database and you have a problem, just check that the database is compatible with Sqlite3.
Once installed the plugin has some configuration options. If what I say here is not entirely clear, try looking at the plugin documentation (the README.md) and compare and contrast.
The main configuration option is the path to the database file. Lets assume that we are going to use an existing Sqlite3 database called 'lax.sqlite3'. This is the data to be given to the sqlite plugin configuration field 'Name of database'.
The next configuration field is 'Route to Database'. This is slightly more complicated. We know that the standard directory set up for GRAV has a directory 'users', which contains several subdirectories, the main one being 'user/pages'. It also has a subdirectory 'user/data'. Since it is a good idea to keep information grouped in a thematic way, the database information should be in its own directory, eg. 'sqlite'. So create a subdirectory 'sqlite' under 'user/data', and then copy the database file to that directory. The full path of the database file is therefore '<grav root>/user/data/sqlite/lax.sqlite3'.
The other configuration options can be left disabled. You will probably only need them if you are developing your own plugin and it has interactions with the database. If, however, you want to use the data logging feature, you should consider using the 'datamanager' plugin.
List Database
The next step is to see what is in the database. I will assume that the list should be in a separate tab or 'page' called 'tryouts. So we create a file '<grav root>/user/02.tryouts_page/default.md' and place the following contents in it:
--- title: Team Testing --- [sql-table]SELECT * from tryouts[/sql-table]
This will generate a very plain table, and if there are a lot of rows in the table, they will 'flood' the page. I prefer to have my data tables in a much more managable form. I also like to search the table and to sort the table by clicking on the headers.
All of these things can be done with an awesome jQuery plugin that can be added to GRAV. Install the 'datatables' plugin. There is no configuration needed.
Now change the file '02.tryouts_page/default.md' to contain:
--- title: Team Testing --- [datatables][sql-table]SELECT * from tryouts[/sql-table][/datatables]As you will see, the table is now much nicer on the page. However, no one really needs to see the field 'tryoutid', so you can add some options to the `datatables` shortcode, eg. '[datatables hidden=tryoutid]'.
The column for 'tryoutid' will now not be shown. However, the data is still in the table. If you look at the page source of the page generated in html, you will find the table of data, including the column for the 'tryoutid', but it will have the property 'hidden' in it.
Suppose that you only want to show the names of tryouts with ages 14 or over, and the name of the first parent. The appropriate SELECT statement would be:
SELECT name, parent1 FROM tryouts WHERE age >= 14
Your table will now have the headers 'name' and 'parent', but you would prefer to have the headers 'Player Name' and 'Parent Name'. Change the SELECT statement to
SELECT name as 'Player Name', parent1 as 'Parent Name' FROM tryouts WHERE age >= 14
The sqlite plugin will take the Column name as the Header Text, so by setting the Column name returned by the SELECT stanza, you can adjust the column name.
Add Data
The next step is to add data. This is relatively simple and is provided for with the sqlite plugin via a Form process. Here I will assume that you have read the documentation about Forms from learn.grav.org, and that you have already created some forms.
Let's add a Form below the table and we need data for the fields name, team, grade, age, phone, email, parent1, p1phone, p1email, parent2, p2phone, p2email. The database itself will add tryoutid.
Some comments:
- It is not a good idea to allow anyone accessing the website to have a chance to change data. So any page that has a way of changing data should be password protected. There are several ways to do this in GRAV, so I will not say any more.
- I will only use the form's 'text' file type for simplicity. This should not be a problem for integers, but you may want to use more sophisticated data entry options for dates and so on.
- I am including the form in a 'default.md' file and not a 'form.md' file. This is so I can place the form where I want it, viz. below the table. However, I have to explicitly name the form and explicitly include it. If I were to use a 'form.md' file, the GRAV template would include the form in a specific place.
---
title: Tryouts
process:
twig: true
cache_enable: false
forms:
add_player:
action: /tryouts_page
fields:
- name: team
type: text
- name: name
type: text
- name: grade
type: text
- name: age
type: text
- name: phone
type: text
- name: parent1
type: text
- name: p1phone
type: text
- name: p1email
type: text
- name: parent2
type: text
- name: p2phone
type: text
- name: p2email
type: text
buttons:
- type: submit
- value: Add player information
- type: reset
- value: Reset form
process:
- sql-insert:
table: tryouts
---
# Tryout players information
[datatables][sql-table hidden=tryoutid]SELECT * from tryouts[/sql-table][/datatables]
# Add a new player
{% include "forms/form.html.twig" with { form: forms('add_player') } %}
- The action field is important. It must match the route where the form is "processed". So, we have placed the 'default.md' file in '<grav root>/user/pages/02.tryouts_page', so the route is '/tryouts_page'. Grav needs to know where to process the information coming back from the user. The processing is associated with the route.
- The form is processed and placed where we want it using {% %} twig processing.
- There is nearly no data checking (GRAV does some checking to eliminate bad chars and injection) but input data should be checked.
- It is possible to put 'DISTINCT' properties when creating a Database, so if you use the same 'name', for example, the database itself will register an error.
- It is possible to use various validation rules (see GRAV learn for more details)
- It is possible to create your own plugin to check, but I think for most purposes the previous two techniques are sufficient.
Update information.
This is more complicated because we need to find a mechanism for selecting a row from the database, modify some field, and inform GRAV about the row and the field and the new data.
The way I solved this is to use the functionality of the datatables jQuery, as exposed in the datatables plugin. It is possible to tell datatables to highlight a row, and when the row is selected (by clicking on it), to call a Javascript function. Then we can create a Form, and have the Javascript fill the form with data, then when the Update button is pressed, the sqlite update process is called.
For simplicity, I am only going to show this for a single field, namely 'phone'.
For a good user interface, the user should be informed which row is going to be changed. So we will put the data from the user name into a display part of the Form.
Access to the datatables jQuery is provided by the [dt-script] shortcode, which must be 'inside' the [datatables] shortcode. It adds the javascript code to the function that instantiates and calls the datatables jQuery. The object called 'selector' is set by the jQuery plugin. If you want more information, you will need to read the plugin code.
The code to be added consists of three parts:
- the lines of code that run when the Datatables jQuery detects that a row has been selected.
- The row has already been selected. I want the row then to be deselected and for the data associated with it to be unset.
- The row has not been selected. I want the data associated with that row to be transferred to the Form.
- the lines of code run when the 'Reset' button is clicked. After looking up the code on the internet, I found that some problems are avoided by wrapping this code inside a TimeOut function. That has nothing to do with GRAV.
Note that since the table contains the 'tryoutid' number even though it does not display it, the DataTables jQuery can extract it from the table and it can be used in the 'where' definition.
---
title: Tryouts
process:
twig: true
cache_enable: false
forms:
add_player:
action: /tryouts_page
fields:
- name: team
type: text
- name: name
type: text
- name: grade
type: text
- name: age
type: text
- name: phone
type: text
- name: parent1
type: text
- name: p1phone
type: text
- name: p1email
type: text
- name: parent2
type: text
- name: p2phone
type: text
- name: p2email
type: text
buttons:
- type: submit
- value: Add player information
- type: reset
- value: Reset form
process:
- sql-insert:
table: tryouts
change_phone:
action: /tryouts_page
fields:
- name: name
label: Player
type: display
- name: phone
type: text
- name: where
type: hidden
process:
- sql-update:
table: tryouts
---
# Tryout players information
[datatables]
[sql-table hidden=tryoutid]
SELECT tryoutid, team, name, phone, grade, age, parent1, p1phone, p1email, parent2, p2phone, p2email
FROM tryouts
[/sql-table]
[dt-script]
var table = $(selector).DataTable();
$(selector + ' tbody').on( 'click', 'tr', function () {
if ( $(this).hasClass('selected') ) {
$(this).removeClass('selected');
$('#change_phone input[name="data[where]"]').val('');
$('#change_phone input[name="data[phone]"]').val('');
$('#change_phone div:first-of-type div:nth-of-type(2) div').html('undefined');
}
else {
table.$('tr.selected').removeClass('selected');
$(this).addClass('selected');
var rd = table.row('.selected').data();
$('#change_phone input[name="data[where]"]').val('tryoutid=' + rd[0]);
$('#change_phone input[name="data[phone]"]').val(rd[2]);
$('#change_phone div:first-of-type div:nth-of-type(2) div').html(rd[1]);
}
} );
$('#alter-client-form').on('reset', function(e) {
setTimeout( function() {
table.$('tr.selected').removeClass('selected');
$('#change_phone input[name="data[where]"]').val('');
$('#change_phone input[name="data[phone]"]').val('');
$('#change_phone div:first-of-type div:nth-of-type(2) div').html('undefined');
});
});
[/dt-script]
[/datatables]
# Add a new player
{% include "forms/form.html.twig" with { form: forms('add_player') } %}
# Update player's telephone number
{% include "forms/form.html.twig" with { form: forms('change_phone') } %}
- Please note that javascript is extracting data from an array using an index. This means that we must specify the order in which sqlite provides the data. That is why the SELECT stanza is changed from '*' to a specific list of fields.
- The idiom 'input[name="data[where]"]' arises because GRAV names the input fields of a Form as 'data'<name of field as defined in FORM definition>.
- The idiom 'div:first-of-type div:nth-of-type(2) div' arises from the way GRAV form renders a 'display' element (the name field is given as type: display). GRAV does not transfer information from type: display fields.
Summary
This page will now render a selectable, sortable table taken from an sqlite database file. A form is given to add a row to the database. A form is given to allow for a change in the phone field of an existing row.
Caveats
This code may contain typos because I have not been able to test it.