[Tutor] Proper SQLite cursor handling?

[Alan Gauld]

On 05/07/2021 02:33, boB Stepp wrote:

> ...  I am trying to follow the MVC design pattern.

MVC is a great principle but unless you have an existing MVC framework
it is quite hard to implement completely. In particular you need some
kind of protocol (eg publish/subscribe or broadcast) to keep model
and view synchronised. But if you stick with the basic concepts it's
a good strategy. but like most things OOP it works more effectively
when things scale up. In simple programs it can add extra effort.

> I have not focused at all on the View(s) at this time.  My main focus
> has been on the SolitaireGame and Controller classes along with who is
> responsible for the database and its access.  The Controller is a bit
> hodge-podge, but is meant to coordinate the currently known minimal
> functionality I am planning.

Controllers tend to be confusing. In essence their job is simple:
capture some input and send it on to either the view or the model
or both as necessary. But to do that often requires that they know
more than they should about the makeup of the model and view!
There is usually a fair amount of coupling between controller
and view, hopefully less so with the model.

> Questions:
> 
> 1)  Am I doing the Controller properly?

Probably not unless you are building a full MVC framework.
But I wouldn't stress over it! But I haven't looked at the
code yet...

> 2)  You will note that SolitaireGame will detect a new game situation
> where the user must enter needed data.  Alan has emphasized that
> SolitaireGame is solely responsible for its data.  But getting user
> input is normally handled by the Display and Controller.  How do I
> "properly" acquire the needed data from the user?

The controller reads data but it does nothing with it other
than identify a destination object. Often a controller sends the
data to the view which updates the display (and the model if
necessary) There will usually be one controller per UI "screen"
and that may encompass multiple view and their associated
model objects. One approach to controllers is that they should
coordinate the activities of the various views and models
within the screen/window. The other is that the controlled
embodies the actual workflow of the application, In either case
controllers are most useful where there are multiple objects
involved. Having now looked at the code I don't think a
separate controller is necessary in your app since it looks
like you will only have one view and one model (unless maybe
you have multiple games running at once?)

One problem with MVC is that there are many variants of the pattern.
The Smalltalk approach (the original MVC) has the concept of
"pluggable controllers" where you register a controller with
a view or model and they use a standard API to communicate.
But most web frameworks don't do that. And many GUIs incorporate
the controller into the view to produce a Model-View architecture
instead. This is often easier for smaller projects.

> 3)  Am I handling the database stuff properly so far?
> 
> 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.

> 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.

> If it exists (not a new game) this data
> needs to be acquired by the SolitaireGame object.  I want these game
> tables to be able to extract statistical data to help compare various
> strategies for playing the solitaire games.  

Do you want the tables to hold the statistical data or can
you calculate it on demand? The latter means it is always current.
Storing the stats at the time of creation implies it is only
relevant to a historical date or that you need to update every
row when the stats change. You can do that with a database
trigger but "there be dragons..." Especially for performance.

> 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)

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

> 6)  The UI will be the last thing I worry about and will probably
> develop iteratively from a command line display to possibly a
> curses-based display (Because I want to make use of Alan's book!), and
> finally to a tkinter UI.  How does one test UIs?  

There are libraries and tools for generating events at the
OS level and you can drive these with scripts. But its very
time consuming to do thoroughly. I'd only go that far if it was a
commercial project with a long expected lifetime.

Mostly, I just play with the GUI by hand... Or better, get
somebody else to do so!

> in mouse coordinates and trigger a mouse or keyboard event?

At a basic level most GUIs include a function/method for pushing
events onto the event queue. You can use that to simulate many
things but it requires you to think about events at a very
low level (think mouse movements, keystrokes etc) I wouldn't
recommend it except for a few specific scenarios where a
few events are all that's required.

> ===========================================================
> """Program to keep track of solitaire scores.
> 
> Definitions:
>     Foundations:  Target area of four piles arranged by suits.  Object of most
>         games is to get as many cards as possible into this area as these cards
>         count positively towards one's hand score.
>     Reserve:  Most games start with a fixed number of cards in this pile, which
>         the player endeavors to deplete, as in most solitaire games any
>         remaining cards in this pile count negatively against one's hand score.
>     Hand:  One round of a solitaire game.
> """
> 
> import sqlite3
> from typing import Tuple
> 
> 
> def open_db() -> sqlite3.Connection:
>     """Create/open database and return connection object."""
>     # TODO:  Implement more robust DB_PATH handling.
>     DB_PATH = "C:/Projects/solitaire_scorekeeper/solitaire.db"
>     con = sqlite3.connect(DB_PATH)
> 
>     # Create games table on first use of program.

Personally I always put database creation into a separate utility
program, otherwise it clutters up your code with a lot of stuff
that only ever runs once(or very rarely). By all means check the
database exists and is populated with the requisite tables but
leave the DDL stuff to a separate program.

>     cur = con.cursor()
>     cur.execute(
>         """
>         CREATE TABLE IF NOT EXISTS games (
>             game_id INTEGER PRIMARY KEY,
>             game_name TEXT UNIQUE,
>             strategy TEXT,
>             num_reserve_cards INTEGER,
>             num_foundation_cards INTEGER,
>             reserve_card_value INTEGER,
>             foundation_card_value INTEGER,
>             total_score INTEGER DEFAULT 0
>         );
>         """

I'd strongly recommend adding some constraints, at the very least
NOT NULL where appropriate. They are a good way of catching errors.


>     )
>     cur.close()
>     con.commit()
>     return con
> 
> 
> class SolitaireGame:
>     """Representation of a solitaire game."""
> 
>     def __init__(self, game_name: str, db_connection:
> sqlite3.Connection) -> None:
>         """Create or open a solitaire game."""
>         self.game_name = game_name
>         self.con = db_connection
>         (
>             self.game_id,
>             _,  # Already have game_name value.
>             self.strategy,
>             self.num_reserve_cards,
>             self.num_foundation_cards,
>             self.reserve_card_value,
>             self.foundation_card_value,
>             self.total_score,
>         ) = self._get_game_data()

Since get_game_data is a method of self why doesn't it
populate the attributes? I don't see any point in the
big tuple.

>         self.max_hand_score = self.num_foundation_cards *
> self.foundation_card_value
>         self.min_hand_score = self.num_reserve_cards * self.reserve_card_value
> 
>     def _get_game_data(self) -> Tuple[int, str, str, int, int, int, int, int]:
>         """Retrieve game data from database."""
>         cur = self.con.cursor()
>         cur.execute(
>             f"""
>         SELECT * FROM games WHERE game_name = {self.game_name};
>         """
>         )
>         game_data = cur.fetchall()[0]
>         if not game_data:
>             # Must be new game, so no game_data present.
>             # Get user input to initialize new game data.
>             # Be sure to write user-inputted data to games table!
>             pass

I don't think this is a good idea. Getting the user involved in
initialization is usually a bad idea. I'd be more inclined to just
return empty data. The game can then prompt the user (by sending
a message to the controller) to provide the data.

>         return game_data

As mentioned above, I think this method should populate
the attributes itself. Passing large collections around inside
the object is just a maintenance issue waiting to happen.

> class Controller:
>     """Coordinator of actions between Display and SolitaireGame."""
> 
>     def __init__(self) -> None:
>         """Instantiate controller and initialize database."""
>         self.con = open_db()
>         self.open_games = {}
>         self.active_game = ""
> 
>     def new_game(self):
>         """Create new solitaire game."""
>         """
>         # Ask user for game_name: str.
>             # Name must be unique.
>             # Validate name:  BEWARE MALICIOUS USER INPUT!!!
>         # Call SolitaireGame.
>         """
> 
>     def open_game(self):
>         """Open existing solitaire game."""
>         # Call SolitaireGame.
> 
>     def record_game(self):
>         """Record game score."""
>         # Call appropriate method of SolitaireGame.
> 
>     def exit_program(self):
>         """Shut down program safely."""
>         # Ensure all db commits done.
>         # Ensure db properly closed.
> 

This feels more like the application than a controller.
its not really coordinating anything. I'm inclined to
say just move the code into the application.

> 
> def Application():
>     """Run program."""
> 
>     controller = Controller()
>     # Program event loop.

Especially since the application does nothing but
instantiate the controller! The controller is the
application.

What's missing is how the application actually runs.
You need some kind of view to launch the thing.
It might just be a CLI initially but the
controller/application needs to present something
to the user. Otherwise all you have is a very
transient connection to a database.

If you were using a GUI you would start the
GUI running and pass the controller for it
to communicate with the game.

If a CLI you need to create your own loop
mechanism within the CLI view code. And if
a web app you need to send the first screen
to the browser and connect the controller
to the web framework request handlers.
But something needs to initiate event handling.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos