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

[Peter Otten]

boB Stepp wrote:

> On Sat, Oct 14, 2017 at 4:45 AM, Peter Otten <__peter__ at web.de> wrote:
> 
>> If this is a long term project there will be changes in the schema.
>> However, I don't think it is necessary to check for individual tables.
>> You typically start with a few tables
>>
>> create table alpha
>> create table beta
>> create table gamma
>>
>> and later add a few more or change columns
>>
>> alter table alpha
>> create table delta
>>
>> In this example you have three versions of the database
>>
>> version 0: empty
>> version 1: three tables
>> version 2: four tables, one table modified
>>
>> If you add a table for your own metadata you ownly need to store that
>> version number. The necessary steps when you open the database are then
>>
>> - read version from bobs_metadata (if that fails you are at version 0)
>>
>> If you are at version 0 "migrate" to version one:
>>
>> - execute script that creates alpha, beta, gamma, and bobs_metadata, the
>>   latter with one row containing version=1
>>
>> If you are at version 1 migrate to version two:
>>
>> - execute migration script from 1 to 2 modifying alpha, creating delta,
>> and
>>   updating to version=2
>>
>> If you are at version 2:
>>
>> - do nothing, database is already in the state required by your
>> application.
> 
> 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.


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

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

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

Bad: one database per patient. You are using the file system as a meta-
database.

Better: an additional patients table and a column patientid in all tables 
containing patient data.

> Also, when I figure out how to
> test this database stuff, I imagine I will be using a test db for the
> testing, not the actual one.  Again, this argues for not hard-coding
> the database name.
> 
> 3)  I am supposed to be delving into writing classes on this project.
> Should the code so far stay as a function or get incorporated into a
> class?  My original intent was to do a class for the
> BloodPressureReadings table, but I am not at the point of going there
> yet.

Relax. A function is an instance of a class with no state and a __call__ 
method ;)

> 4)  I wish there was a PEP 8 for SQL!  I have several SQL books I have
> consulted, but I have gotten conflicting suggestions for SQL code
> style.  I have tried to adopt something that seems to me to be both
> consistent and reasonable, but is it good enough?
> 
> I await the arrival of the list's wisdom!
> 
> Cheers!