[Tutor] Proper SQLite cursor handling?

[Cameron Simpson]

On 05Jul2021 12:17, Alan Gauld <alan.gauld at yahoo.co.uk> wrote:
>On 05/07/2021 02:33, boB Stepp wrote:
>> 4)  I haven't made it this far yet, but part of each game will be a
>> record of each hand played.  The current table "games" will refer to
>> these tables, one per game.
>
>Can you elaborate by what you mean by games?
>Do you mean each type of game(clock, spyder, canfield, etc)
>will have its own table or do you mean each game session?
>(game1,game2 etc.)
>
>If the latter I'd expect it all to be a single table.
>But if the former you may have different attributes
>per game type and multiple tables makes sense.

We may have a nomenclature problem here.

It looks like boB is making a single table, with a _row_ per game, at 
least far as the list-of-games goes.

>> The fields in each of these tables, named
>> by "game_name" will be:  hand_number (primary key), date_played,
>> time_recorded and hand_score.  Of course for every new game this table
>> will have to be created.
>
>If the fields are the same just use a single table and add a
>game_id field. The more tables you create the more complex
>the queries become. SQL is designed to filter data out of
>a large mixed collection.

Again, I think bobB has a single db table with many rows, as you'd 
expect.

>> 5)  I am about to start writing tests and then flesh out the code.
>> How does one test stuff reliant on databases?  Thoughts that come to
>> mind are having special test databases accessed by changing the
>> database path to a testing database directory.  Creating in-memory
>> databases as a mock.  Something else?
>
>All of the above. Most big projects I've worked on we created a
>specific test database that could be easily reloaded. It was
>carefully designed to have data of all the different scenarios
>we might need(including broken values)

The scenario Alan's describing here implies making the "oriiginal" test 
db (or just maintaining one) with the test situations and having a dump 
of it lying around. During testing you load the dump into a fresh 
scratch database and test, not affecting the original.

I've been working in a Django dev env recently, and the test suites make 
a new empty db per test run, and the test creates the scenario to be 
tested (eg makes some database entries with the scenario for the test).  
I think this is arranged to be cheap yet isolated by doing tests inside 
a transaction: start transaction, set up db for the test, run test, roll 
the transaction back leaving the db as it was before. That way the db 
can be made once for the whole test run.

>A mock is often used in unit tests, especially if the DB
>has a higher level API - mocking SQL itself it much harder!

Also, SQLite has a ":memory:" db backend instead of a file, meaning you 
can cmake a new in-memory db with no need to make a file on disc in some 
scratch area or with a funny name.

Cheers,
Cameron Simpson <cs at cskk.id.au>