boB Stepp wrote: > On Sat, Oct 14, 2017 at 4:45 AM, Peter Otten <__pete...@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! _______________________________________________ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
