Create an upgrade SQL script to migrate users from a Climsoft v4.1.1 database to version 4.2

[isedwards]

Using MySQL or MariaDB and starting with the mariadb_climsoft_db_v4_all.sql script from the climsoft-database v4.1.1 release, create an upgrade script to apply the following changes:

• Include the ALTER TABLE ... ADD primary key ... statements from Adding primary keys to tables #2
• The mm_04 field in the form_monthly table is not a required field and should be changed from NOT NULL to DEFAULT NULL with ALTER TABLE form_monthly MODIFY mm_04 varchar(255) default null;
• The following tables are not in use and should be dropped: abc, aws1, aws_elements, aws_stations, aws_test, ss, testing_aws, testing_aws1
• The final statement in the update script, if all other statements execute successfully, should be inserting the following row into the regkeys table: INSERT INTO regkeys (keyName, keyValue, keyDescription) VALUES ('climsoftversion', '4.2.0', 'The version number of the current database schema being used')
• Include the commands below to add comments to the tables in the database

ALTER TABLE acquisitiontype COMMENT = 'Acquisition Type';
ALTER TABLE aws_basestation COMMENT = 'Stores Automatic Weather Station Base stations or Servers details';
ALTER TABLE aws_lsi COMMENT = 'Stores Automatic Weather Station Data Structure settings under the name LSI';
ALTER TABLE aws_lsi1 COMMENT = 'Stores Automatic Weather Station Data Structure settings under the name LSI1';
ALTER TABLE aws_malawi1 COMMENT = 'Stores Automatic Weather Station Data Structure settings under the name malawi1';
ALTER TABLE aws_malawi12 COMMENT = 'Stores Automatic Weather Station Data Structure settings under the name malawi12';
ALTER TABLE aws_mss COMMENT = 'Stores_Message Switching System settings details';
ALTER TABLE aws_process_parameters COMMENT = 'Stores Automatic Weather Station Real Time Processing Parameters_Settings';
ALTER TABLE aws_rema1 COMMENT = 'Stores Automatic Weather Station Data Structure settings under the name rema1';
ALTER TABLE aws_rwanda1 COMMENT = 'Stores Automatic Weather Station Data Structure settings under the name rwanda1';                                                                                                                ALTER TABLE aws_rwanda4 COMMENT = 'Stores Automatic Weather Station Data Structure settings under the name rwanda4';
ALTER TABLE aws_rwanda_rain COMMENT = 'Stores Automatic Weather Station Data Structure settings named rwanda_rain';
ALTER TABLE aws_sasscal1 COMMENT = 'Stores Automatic Weather Station Data Structure settings named sasscal1';
ALTER TABLE aws_sites COMMENT = 'Stores Automatic Weather Station Sites settings';
ALTER TABLE aws_structures COMMENT = 'Stores Automatic Weather Station structure definitions';
ALTER TABLE aws_tahmo COMMENT = 'Astores Automatic Weather Station Data Structure settings named tahmo';
ALTER TABLE aws_toa5_bw1 COMMENT = 'Automatic Weather Station Data Structure settings named toa5_bw1';
ALTER TABLE aws_toa5_mg2 COMMENT = 'Automatic Weather Station Data Structure settings named toa5_mg2';
ALTER TABLE bufr_crex_data COMMENT = 'Stores Code settings of Data in BUFR and CREX';
ALTER TABLE bufr_crex_master COMMENT = 'Stores Master Code settings of Data in BUFR and CREX';
ALTER TABLE bufr_indicators COMMENT = 'Stores BUFR Indicator Settings';
ALTER TABLE ccitt COMMENT = 'Stores CCITT Settings';
ALTER TABLE climsoftusers COMMENT = 'Stores Climsoft users ';
ALTER TABLE code_flag COMMENT = 'Stores Flag codes used in Climsoft';
ALTER TABLE data_forms COMMENT = 'Lists Data Entry Forms in Climsoft';
ALTER TABLE faultresolution COMMENT = 'List Fault Resolution';
ALTER TABLE featuregeographicalposition COMMENT = 'Feature Geographical Position';
ALTER TABLE flags COMMENT = 'List Flags used in Climsoft';
ALTER TABLE flagtable COMMENT = 'List the Flags used in Climsoft';
ALTER TABLE form_agro1 COMMENT = 'Data Entry Form to key in  Agrosynoptic Data';
ALTER TABLE form_daily2 COMMENT = 'Data Entry Form to key in  Daily Data';
ALTER TABLE form_hourly COMMENT = 'Data Entry Form to key in  Hourly Data';
ALTER TABLE form_hourlywind COMMENT = 'Data Entry Form to key in  Hourly Wind Data';
ALTER TABLE form_hourly_time_selection COMMENT = 'Form holding Time selection for Hourly data';
ALTER TABLE form_monthly COMMENT = 'Data Entry Form to key in  Monthly Data';
ALTER TABLE form_synoptic2_tdcf COMMENT = 'Data Entry Form to key in  Synoptic  Data for Table Driven Code Form (TDCF)';
ALTER TABLE form_synoptic_2_ra1 COMMENT = 'Data Entry Form to key in  Synoptic  Data for Region I (RAI)';
ALTER TABLE gaps COMMENT = 'Stores Data gaps for the Inventory of missing data';
ALTER TABLE instrument COMMENT = 'Instrument details';
ALTER TABLE instrumentfaultreport COMMENT = 'Instrument Faults report table';
ALTER TABLE instrumentinspection COMMENT = 'Instrument Inspection';
ALTER TABLE language_translation COMMENT = 'Language Translation';
ALTER TABLE missing_data COMMENT = 'Missing Data for the Inventory of missing data';
ALTER TABLE missing_stats COMMENT = 'Missing Data statisticts';
ALTER TABLE obselement COMMENT = 'Stores Observation Elements in Climsoft';
ALTER TABLE observationfinal COMMENT = 'Stores Observation at Final stage';
ALTER TABLE observationinitial COMMENT = 'Stores Observation at the Initial stage';
ALTER TABLE observationschedule COMMENT = 'Observation Schedule';
ALTER TABLE obsscheduleclass COMMENT = 'Observation Schedule Class';
ALTER TABLE paperarchive COMMENT = 'List the images path details';
ALTER TABLE paperarchivedefinition COMMENT = 'Paper Archive Definition';
ALTER TABLE physicalfeature COMMENT = 'Physical Feature';
ALTER TABLE physicalfeatureclass COMMENT = 'Physical Feature Class';
ALTER TABLE qcabslimits COMMENT = 'Quality Control for Absolute Limits';
ALTER TABLE qcstatusdefinition COMMENT = 'Quality Control status Definition';
ALTER TABLE qctype COMMENT = 'Quality Control Type';
ALTER TABLE qc_interelement_1 COMMENT = 'Quality Control for inter element Comparison1';
ALTER TABLE qc_interelement_2 COMMENT = 'Quality Control for inter element Comparison2';
ALTER TABLE qc_interelement_relationship_definition COMMENT = 'Quality Control for Interelement Relationship Definition';
ALTER TABLE regkeys COMMENT = 'Register Keys';
ALTER TABLE routinereportdefinition COMMENT = 'Routine Report Definition';
ALTER TABLE routinereporttransmission COMMENT = 'Routine Report Transmission';
ALTER TABLE seq_daily_element COMMENT = 'Sequencer for Elements in Form Daily';
ALTER TABLE seq_day COMMENT = 'Sequencer for Day';
ALTER TABLE seq_element COMMENT = 'Sequencer for Elements ';
ALTER TABLE seq_hour COMMENT = 'Sequencer for Hours';
ALTER TABLE seq_leap_year COMMENT = 'Sequencer for Leap Year';
ALTER TABLE seq_month COMMENT = 'Sequencer for Month';
ALTER TABLE seq_monthly_element COMMENT = 'Sequencer for Elements in Form Monthly';
ALTER TABLE seq_month_day COMMENT = 'Sequencer Month and Day';
ALTER TABLE seq_month_day_element COMMENT = 'Sequencer Month, Day and Elements';
ALTER TABLE seq_month_day_element_leap_yr COMMENT = 'Sequencer Month Day, Elements and Leap Year';
ALTER TABLE seq_month_day_leap_yr COMMENT = 'Sequencer Month, Dayand Leap Year';
ALTER TABLE seq_month_day_synoptime COMMENT = 'Sequencer Month, Day and Synoptic Time';
ALTER TABLE seq_month_day_synoptime_leap_yr COMMENT = 'Sequencer Month, Day and Synoptic Time and Leap Year';
ALTER TABLE seq_year COMMENT = 'Sequencer for Year';
ALTER TABLE station COMMENT = 'Station';
ALTER TABLE stationelement COMMENT = 'Station Element';
ALTER TABLE stationidalias COMMENT = 'Station Id Alias';
ALTER TABLE stationlocationhistory COMMENT = 'Station Location History';
ALTER TABLE stationnetworkdefinition COMMENT = 'Station Network Definition';
ALTER TABLE stationqualifier COMMENT = 'Qualifier for Station ';
ALTER TABLE synopfeature COMMENT = 'Stores details of Station Synoptic Features';
ALTER TABLE tblproducts COMMENT = 'Stores Details of Climate Products in Climsoft';
ALTER TABLE tdcf_indicators COMMENT = 'Stores Indicators details for Table Driven Code Forms(TDCFs)';
ALTER TABLE tm_307073 COMMENT = 'Stores Details of  WMO Codes Template 307073';
ALTER TABLE tm_307080 COMMENT = 'Stores Details of  WMO Codes Template  3070780';
ALTER TABLE tm_307081 COMMENT = 'Stores Details of  WMO Codes Template  3070181';
ALTER TABLE tm_307082 COMMENT = 'Code TemplateStores Details of  WMO Codes Template';
ALTER TABLE tm_307083 COMMENT = 'Stores Details of  WMO Codes Template  307083';
ALTER TABLE tm_307084 COMMENT = 'Stores Details of  WMO Codes Template  307084';
ALTER TABLE tm_307086 COMMENT = 'Stores Details of  WMO Codes Template  307086';
ALTER TABLE tm_307089 COMMENT = 'Stores Details of  WMO Codes Template  307089';
ALTER TABLE tm_307091 COMMENT = 'Stores Details of  WMO Codes Template  307091';
ALTER TABLE tm_307092 COMMENT = 'Stores Details of  WMO Codes Template  307092';
ALTER TABLE tm_309052 COMMENT = 'Stores Details of  WMO Codes Template  309052';
ALTER TABLE userrecords COMMENT = 'Stores Details of Users records';