[Tutor] How to test my code's interactions with SQLite db?

[boB Stepp]

On Sun, Aug 16, 2015 at 6:04 PM, Alan Gauld <alan.gauld at btinternet.com> wrote:
> On 16/08/15 23:29, boB Stepp wrote:
>
>> http://www.sqlite.org/download.html
>
>
> You definitely want this.
> You treat it like the >>> prompt in Pyython.

I had just finished installing and testing the installation just
before your email arrived.  Very easy to do!

> A place to try out SQL queries before you put
> them into your Python code. Also you can write
> long sql code in a .sql filer and read them
> into the interpreter using the .read command
>
> sqlite3> .read createdb.sql
> sqlite3> .read populate_tests.sql
> sqlite3> .read clear_tables.sql
> sqlite3> .read populate_base_data.sql

I am assuming that the .read command would be replaced inside the
program by the cursor.executescript() method you mentioned?  This will
be quite handy, I think.

> Also you can ask the interpreter to describe table
> structures and constraints etc. Think TDD here...

Would you mind giving a specific example illustrating how this would
aid testing?  Being an insane accumulator of books, I have acquired,
"The Definitive Guide to SQLite, 2nd ed.", by Grand Allen and Mike
Owens, c. 2010.  I haven't made it yet to the commands you are
alluding to.

>> design tool as the command-line SQLite would allow me to easily get a
>> visual representation of the tables, etc.,
>
>
> Depends what you mean by "visual"!

I just meant getting a text display of table contents in neat aligned
columns.  The book I mentioned has already showed me how to do this.
This will be more than plenty for me.  At this time, while
concentrating on learning SQL, I don't want to take any GUI shortcuts.

>> is that I would unknowingly structure my test db differently than what
>> my program would generate.
>
>
> As I said, create separate .sql files to create an empty database and to
> populate the structure with initial data. That way you use the exact same
> structure file in your code as in your tests (remember the
> cursor.executescript() method I mentioned earlier! - that works
> from inside your code as well as the >>> prompt!)

This looks okay from the bare bones standpoint.  However, I am
wondering how much pre-existing structure I should impose on the user,
and how much flexibility I should allow the user to have to extend the
db structure as "shipped".  For instance I can easily see the user
wanting to add new types of information she wants to track for her
student.  I think the program should allow her to do things like this,
but while totally hiding the SQL interactions behind the scenes from
the user.  But allowing this sort of functionality automatically
creates differences between what I've been testing in development and
what the user creates through normal program use.

I suppose, however, that I can design test cases that mimics all
allowed such functionality.  But in this example, how many new columns
of table data do I cut off the tests at, for instance?  Eventually RAM
or some kind of overflow condition will transpire, which suggests if I
want to test that a *lot* of table columns can be added without
breaking anything will require me to set some reasonable upper limit
for what constitutes a *lot*.

boB