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

[boB Stepp]

On Sun, Aug 16, 2015 at 7:55 PM, Alan Gauld <alan.gauld at btinternet.com> wrote:
> 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.

Oops!  I wrote the above paragraph in a different form, thinking that
.read was in the Python DB API in some form, too, then went to the
docs to verify this, and saw that the method you mentioned,
executescript(), was serving the interpreter's .read command's
function, and did not rewrite the paragraph appropriately.  I knew
what you meant, though.

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

Hmm.  This is much more complex than I imagined, and I'm cynical by nature.

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

Well, while this is a one-teacher project only, there will be constant
back-and-forth between me and said teacher, so I really would not need
this for her.  However, if this program actually gets created and
works well, then I can see other teachers in the school using it, and
they may have different data they want to track.  So I was thinking of
making it very flexible in this regard.  Maybe I should hold off on
this until I see how what I need to do now plays out.

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

[...]

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

I'm glad I think to ask these questions!  And that you folks are here
to answer them!!

boB