[Tutor] How to test for the existence of a table in a sqlite3 db?

[boB Stepp]

On Sun, Oct 15, 2017 at 2:56 AM, Peter Otten <__peter__ at web.de> wrote:
> boB Stepp wrote:

>>
>> I am puzzled.  If one is using version control, then what is the
>> advantage of this incremental approach in creating and populating the
>> database?  Instead, why not just have a single SQL file that generates
>> the finished initial state of the db for the current version?  One can
>> always use the version control system to roll back to an earlier
>> stage.
>
> My idea was presented under the assumption that the there is user data
> entered in version 1 that needs to be preserved when version 2 of the
> application replaces 1.

Ah!  I was missing the forest for the trees.  Sorry 'bout that!

>> I have chickened out and not done a TDD approach yet.  I will probably
>> pause here, wait for feedback from this list, and try to figure out
>> how I should test what I have so far.  And how do you test SQL scripts
>> anyway?
>
> Example: Run the routine to enter a row, then check if it's there and
> contains what you expected. That should fail before the script is run, and
> succeed afterwards.

I guess what bothers me here is that it seems I have to write some
code in the tests file just to get the test database to the point
where I can write the necessary asserts.  But I would have to do that
anyway, wouldn't I?  The whole point of test fixtures, setup and tear
down code, etc.  It just looks like testing dbs will be somewhat
messier than what I have dealt with to date.

>>
>> Some things I am still pondering:
>>
>> 1)  If I adopt the incremental approach to creating and initializing
>> the working db, then it seems that the list, "sql_scripts", should not
>> be hard-coded into the program.  It seems to me it should be off
>> somewhere by itself with perhaps other things that might evolve/change
>> over time in its own file where it (and its brethren) are easy to
>> locate and update.
>
> I think it should be hardcoded. You don't want to run arbitrary scripts that
> happen to be in a folder, say. Version control can take care of any changes.

In some ways I think too much.  I struggle here on what should be my
best practice, to minimize the amount of hard-coded data (I think
usually a good idea.) or, in a particular case like this one, to do
the hard-coding.

>>
>> 2)  Likewise, "db_filename", is currently hard-coded in the if block
>> to start the program.  I have not decided yet what the end result will
>> be, but I might want to allow for the possibility of allowing the user
>> (me) to create multiple databases.
>
> If one user needs multiple databases that /may/ be an indication that you
> are not storing enough information in the database.

I was thinking ahead to a follow-up project, the chess rating db.  For
this I had contemplated having separate sqlite3 database files for
each school year instead of adding school year information to a single
database.

-- 
boB