[David Jack]

17/02/2022 at 2:52 pm

Has anyone attempted teaching Databases and SQL on RaspPi’s?

I’m very very tempted. I can’t stand Access and don’t feel the skills they learn via Access are interchangeable in anyway to SQL databases in the real world. It’s a hangover from the MS heavy Standard Grade.

Any advice? Any apps to use or avoid? We are unable to create a LAMP server using the council computers, so RapPi’s should and would be able to do the trick. I already know it’ll be fine for SSD and Web.

Is it compatible with the SQA course guidelines?

Any pointers or advice appreciated.

[Peter Thoresen]

17/02/2022 at 3:18 pm

I don’t know about using Raspberry Pi’s, but as soon as SQL became required we dropped Access for N5 and Higher – for the same reasons you give.

We used SQLiteStudio, and now use DB Browser for SQLite.

Both are available for Windows/MacOS/Linux, and are available as portable apps, so can be used without installation and at home.

[Fraser McKay]

17/02/2022 at 5:05 pm

I’ve done it on a Raspberry Pi for a week, when our network was down for refresh. There’s a (really brief) demo in one of the cssmeets videos (https://youtu.be/imvKyv-0glY?t=338). It only takes a minute to install SQLite on a Pi:

sudo apt install sqlite3

And then you can open a database file from the terminal:

sqlite3 database_name.sqlite

Pupils type their queries straight into the terminal (need to remember the semicolon, unlike Access). It was a last-minute decision to go with this for us, but I found it worked fine for the week when we needed it. We were doing wildcards and functions/group by. The only thing I didn’t try was Higher problems with two queries – just because we hadn’t got to that part of the course yet.

[Paul Gardner]

17/02/2022 at 5:52 pm

I’ve taught databases on the Raspberry Pi before and it worked really well. You can use the command line interface for setting up tables, performing queries and running SQL command files; there are a few GUI applications (DB Browser, Adminer, DB Visualizer) that work well and it’s also easy to set up a Python web server based interface that gives an easy to use GUI. I’m using this for this year’s National 5 and Higher Computing, inclding the course assessment tasks.

For an online example of what I mean, have a look at https://totaloriginallibrary.paulatdunedin.repl.co/
This is easy to port to the Raspberry Pi. All it uses is Python 3.

Let me know if you’d like details of how to copy the build from Replit and get it working on the Pi.

Paul

• This reply was modified 1 year ago by Paul Gardner. Reason: Spelling

[David Jack]

18/02/2022 at 2:20 pm

Thank you for the responses. I’ll dig into this information over the next few weeks.

[Greg Reid]

21/02/2022 at 11:34 pm

Hi David
Absolutely nothing wrong with using PIs from SQA perspective. The course was really designed in the hope that centres would move away from Access etc and leave that for Admin courses. More and more centres are using SQL servers of one or other variety.
If anything using PIs for the assignments offers the opportunity for more security and assurance that the candidates work is their own than a school network would afford.

[David Jack]

24/02/2022 at 10:13 am

I’ve spent some time looking at SQLite3 and DB browser.

Do the SQA have any issue with their being no Date field type in SQLite3?

The Date field is mentioned in the course spec.

Would be ok to do INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC?

[Peter Thoresen]

24/02/2022 at 11:19 am

SQLite doesn’t have a “DATE” datatype, but nor does Access. And neither has a “Boolean” datatype.

I make it clear to pupils that the SQA specified data types are for the design stage only, and will differ during implementation depending on the application used.
For SQLite, dates and times are text fields, and Booleans are integers (1/0).

Also, although SQLite doesn’t require the size of text fields to be given, I have my pupils type them in anyway – text(20)

[Paul Gardner]

24/02/2022 at 1:54 pm

For date fields in SQLite I get my pupils to create tables using the syntax, e.g.

CREATE TABLE test (
  start DATE not null,
  end   DATE not null);

and then insert data as text in ‘YYYY-MM-DD’ format, e.g.
INSERT INTO test VALUES ('2022-02-20', '2022-02-24');

they can then perform data calculations using the ‘julianday()’ function, e.g.
SELECT julianday(end)-julianday(start) AS duration FROM test;

this returns the nmber of days between start and end.

• This reply was modified 1 year ago by Paul Gardner. Reason: Spelling

[David Muir]

08/03/2022 at 4:06 pm

I’ve been experimenting with DB Browser and there’s much about it that I prefer to Access but I’m not sure I’m convinced enough yet to completely abandon Access. Since at least a couple of replies here mention it, I thought I’d hijack (no pun intended) David’s post to ask a few questions. (Hope that’s OK David.)

1. Can anyone recommend a good tutorial for DB Browser? I’m not hugely impressed with the three tutorials that are on the DB Browser site.

2. In one of Peter’s replies, he says he gets the pupils to use “text(20)” even though SQLite doesn’t “require” it. What does SQLite do with the “(20)” – just ignore it? If you wanted to set a max length, I guess you could do it with a CHECK constraint but is that more trouble than it’s worth?

3. One of Paul’s replies says that he uses “DATE” anyway even though SQLite doesn’t support it. What does SQLIte do with unsupported types like that? A bit of Googling suggests it maps it onto a type it does support (presumably based on whatever you put in the field… in this case, TEXT?). Can this be extended to using TIME and BOOLEAN as well? For example, would you teach something like:

CREATE TABLE pretend(
  name TEXT(30),
  DoB DATE,
  startTime TIME,
  promoted BOOLEAN);

Or is it better to stick to actual SQLite data types?

4. Last one (for now)… error reporting in DB Browser if a Check constraint fails seems very poor. Is there any way to make DB Browser give a customised error message that would at least tell you which constraint had failed?

• This reply was modified 1 year ago by David Muir. Reason: Added fourth question

[Paul Gardner]

08/03/2022 at 10:17 pm

One of my earlier posts appears in ‘My Activity’ but not in this thread, so here it is agian. Apologies if this comes up as a duplicate.
——————————————————————————————————–

SQLite is very relaxed about datatypes. Any column can store any type of data! Columns can be also defined without a data type specified at all. Without CHECK constraints, SQLite doesn’t enforce any type checking on data being inserted as the following code demonstrates:
create table test(
name text,
age int,
hours float,
pt boolean
);

insert into test values
(‘Paul’, 22, 4.6, true),
(22, 4.6, true, ‘Paul’),
(4.6, true, ‘Paul’, 22),
(true, ‘Paul’, 22, 4.6);

select typeof(name), typeof(age), typeof(hours), typeof(pt) from test;

text integer real integer
text real real text
text integer text integer
text text real real

If you want/need SQLite to enforce datatypes then you can add CHECK constraints when you create the table:
create table test(
name text check(typeof(name)=’text’),
age int check (typeof(age)=’integer’),
hours float check(typeof(hours)=’real’),
pt boolean check(typeof(pt)=’integer’ and pt between 0 and 1)
);

Even so, it still allows:
INSERT INTO test (name) VALUES (22/7);

and will store the value ‘3’ in the name field, as 22/7 is treated as an integer division and then the result, 3, is stored as text.

I tend to ignore this when teaching using SQLite and demonstrate SQL in the style of MySQL/Oracle. It’s useful to be aware of what’s actually happening to understand some of the odd side effects it occassionally causes.

For a full explantion of how any why this happens, see https://www.sqlite.org/datatype3.html

==============================================================================================

SQLite really only has 5 data types; null, integer, real, text, blob. When you specify a datatype in your create table it sets an ‘affinity’ for that column so that if you supply data it will use the affinity to coerce the data for the column (see how the value 22 was stored as text in the demo above). But, without check constraints, SQLite will happily store text in a field delared as an integer.

In answer to some of your questions:
2) The 20 in text(20) is not enforced.
3) DATE and DATETIME fields are created with ‘numeric’ affinity and can be used with date and time functions like julianday(). I prefer to specify date and time fields as text and use text/date functions with them. The advantage of dates expressed as ‘yyyy-mm-yy’ is that they sort in chronological order.
4) Constraints can be named to improve the error messages, e.g.
create table test1 (
age int not null constraint age_check check(age between 18 and 75)
);

Then, when a constraint error occurs you get a message like:
insert into test values (76);
Error: CHECK constraint failed: age_check

Paul

[sthomsonar]

09/03/2022 at 8:32 am

We teach everything on the Raspberry Pi and use SQLite to deliver the database unit. We apply the data types as Paul has outlined. I find the table outlining the nature of data types in SQLite helpful:

https://www.sqlite.org/datatype3.html

We have decided to stop using TEXT as a type and start using VARCHAR as it will tie into Advanced Higher better.

[David Jack]

09/03/2022 at 9:30 am

We are hoping to go down the route of only using RaspPi’s. This thread is a big help and when I’ve got time I hope to spend more time with SQLite3 and DBViewer.

[David Muir]

09/03/2022 at 3:40 pm

Thanks, Paul. Very helpful.

I had come across naming constraints already, as in your example:
constraint age_check check(age between 18 and 75)
and it certainly improves things… I was just hoping there was something even better, like the way Access lets you define your own error message.

I notice that DB Browser has a constraint tab in the “Edit table definition” dialog box but, as far as I can see, while it allows you to name a constraint, it does not allow you to add the CHECK section. Am I missing something obvious, or is the only way to name a constraint is to write it into the table definition in the SQL tab?

[Al Friend]

10/03/2022 at 9:25 am

Paul’s description of how to use TYPE as a constraint was very helpful. I’ll update what I’ve created in time.

David, I’m using DB Browser on Windows 10 so It might look a little different. Hopefully this helps:

(This is the first time I’ve shared a file via GitHub – I hope it works!)