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

[Alan Gauld]

On 17/08/15 00:52, boB Stepp wrote:

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

No.
The executescript() method *replaces* .read
.read is only used within the interpreter.
There are a whole bunch of these pseudo-commands,
including .quit to exit.

In executescript() you just pass in the name
of the file you want executed. Very easy.
But like os.system() not so easy to check
it all worked OK... an exercise for the reader,
as they say...

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

Again the . commands.
Try

  sqlite3> .help

You'll like it :-)

In particular try the .tables, .trace and .schema commands.

> 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 don;t know it - I own "Using SQLite" from O'Reilly.
But the web docs are good too.

> 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'd allow a small amount of leeway here.
The usual approach is to use string fields for the data
and a meta table with field, type, name information.

So if you have a student table with three user defined fields
UDF1, UDF2,UDF3. You define a meta table called meta_student
that has field, name, type columns containing things like:

UDF1, BOOL, hasTattoos
UDF2, FILENAME, Mugshot
UDF3, STRING, Birthmark

(You could have a single meta_data table with table as
first column but that just makes your SQL even more
complex IMHO! Small tables are OK.
Also some implementations allow user specific definitions
but that breaks badly if records are shared if, for example,
user1.UDF1 is hasTattoos and user2.UDF1 is isPregnant)

BUT! You need a lot of infra-structure around this since these
fields will be global to all users. And you need to manually
do any data validation of input to ensure that only valid
data gets stored. You will need a bank of supported
types/validator functions. And your UI needs to ensure users
can only create appropriate options (think drop down lists
and dictionaries).

Personally I'd only add this flexibility if you really need it.
Its a major code overhead and a big performance hit too.

> allowed such functionality.  But in this example, how many new columns
> of table data do I cut off the tests at, for instance?

I've seen commercial systems that allow up to 100 UDFs.
In practice I'd go for 3-10. if they need more than 10
then your requirements capture and user testing was
very poor!

> Eventually RAM or some kind of overflow condition will transpire,

With a database that tends to be disk space so think Terabytes.
Its not normally an issue these days!

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

The biggest issue, as with user defined attributes in objects
is that your static code doesn't know what these new fields are.
Imagine how they play with a search function? Building a
SQL query against a boolean value in the UDF1 column means
translating the boolean result into its string equivalent
and doing a fill scan text search. Slow....

You really want database tables to be defined as fully as
possible as soon as possible. UDFs are a (sometimes necessary)
cost of failure!

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