Adding more database-y exercises to the SQLite track?

Exercism
44

Have a look at the latest version at Applying tdd on select, using textual output by blackk-foxx · Pull Request #219 · exercism/sqlite · GitHub and let me know what you think.

45

:thinking: Could we use auxiliary tables? Like:

CREATE TABLE good_test_name_goes_here AS
  -- Write your select here
  SELECT name, sql
    FROM sqlite_master
   WHERE type = 'table'
;

we could “easy” checks if the table(s) was created and if the content is correct, also we can show the select output to the student if needed too.

46

Interesting idea! This would probably be more robust than relying on bare SELECT statements being in a “magic” order, right? So the stub file would look something like

CREATE TABLE all_records_test AS
-- Task 1. Select all records.
-- TODO: Write your solution for Task 1 here.
;

CREATE TABLE location_and_temperature_test AS
-- Task 2. Select only location and temperature data.
-- TODO: Write your solution for Task 2 here.
;

(etc.)
47

Yup. This is basically the current framework where students write to tables. Providing stub structure and using a sub select query is what I was recommending.

1 Like
48

Or, to include extra check for data types and columns, we could first create the auxiliary tables and use insert. To check the content we could use the EXCEPTapproach to compare student tables with expected tables. If we use insert maybe we could make more simple WHERE (without EXCEPT) and check if the row numbers are equals because the number of columns, datatypes and table content is already defined.

But it’s just ideas.

1 Like
49

@IsaacG - Can you please review the latest version in Applying tdd on select-only exercise by blackk-foxx · Pull Request #219 · exercism/sqlite · GitHub? I think this methodology is ready to embody in a new concept exercise. What do you think?

50

This looks like a solid approach. I believe it can be used.

@glennj @vaeng Any thoughts here?

51

OK, I have posted Add basic SELECT concept/exercise by blackk-foxx · Pull Request #223 · exercism/sqlite · GitHub for review.

52

Can anybody help with supporting the tests on Windows (see https://github.com/exercism/sqlite/pull/223#discussion_r2644169705)?

53

I don’t have any Windows machines or VM. Would it be reasonable to suggest WSL for the track?

1 Like
54

The track installation docs would need to be updated since if I recall correctly, we tell folks to use the pre-compiled Windows binaries. Would WSL or a VM be needed for all exercises going forward or not? It seems weird that we’d say some exercises need a VM, some don’t without at least briefly mentioning why.

55

Regarding Glenn’s PR comment, would git-bash and jq be reasonable requirements for running the tests on Windows?