mvhub-dev team mailing list archive

["James Reynolds Jr." <jreynoldm@xxxxxxxxx>]

James Reynolds Jr. has proposed merging lp:~jimbox-comcast/mvhub/alter_and_remove_tables into lp:mvhub.

Requested reviews:
  MVHub devs with commit rights (mvhub-commit)
Related bugs:
  #396376 change schema so modification_date is part of agency & program tables
  https://bugs.launchpad.net/bugs/396376


Added script 007_alter_tables_and_remove_old.sql to change the schema so date_created information from date_agency_created and date_program_created are now included in agency and program respectively. The unneeded tables are then dropped.

Several files were modified including:
app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl
app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl
app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl

Modified subroutines from program.pl and agency_from.pl to insert information into the correct tables.

reports.pl needed some queries modified to fit the new schema.
-- 
https://code.launchpad.net/~jimbox-comcast/mvhub/alter_and_remove_tables/+merge/28428
Your team MVHub Developers is subscribed to branch lp:mvhub.

=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl	2010-04-02 19:31:16 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl	2010-06-24 16:13:28 +0000
@@ -39,9 +39,9 @@
     {   title   => 'Agency Addition Dates',
         comment => "This gives the date when each agency was added to MVHub.",
         sql     => qq/
-			SELECT a.agency_name, d.date_created
-			FROM agency a JOIN date_agency_created d USING (agency_id)
-			ORDER BY d.date_created;
+			SELECT agency_name, date_created
+			FROM agency 
+			ORDER BY date_created;
 		/
     },
 
@@ -49,10 +49,10 @@
         comment =>
             "This gives the date when each program was added to MVHub.",
         sql => qq/
-			SELECT p.program_name, a.agency_name, d.date_created
-			FROM agency a, program p JOIN date_program_created d USING (program_id)
+			SELECT p.program_name, a.agency_name, p.date_created
+			FROM agency a, program p 
 			WHERE p.agency_id = a.agency_id
-			ORDER BY d.date_created;
+			ORDER BY p.date_created;
 		/
     },
 

=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl	2010-06-17 18:46:44 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl	2010-06-24 16:13:28 +0000
@@ -241,26 +241,13 @@
     if ( !defined $values_href->{password} ) {
         $values_href->{password} = generate_random_password();
     }
+
     MVHub::Utils::DB::insert_one_record(
         dbh         => $dbh,
         table       => 'agency',
         values_href => $values_href
     );
 
-# Get "last updated" date from production agency table so we can insert it into
-# date_agency_created.
-    my $sql
-        = MVHub::Utils::DB::get_sql_select_statement('AGENCY_X_LAST_UPDATED');
-    my $date_created = ( $dbh->selectrow_array( $sql, {}, $agency_id ) )[0];
-
-# Now that record's inserted into production table, add entry to date_agency_created table
-    MVHub::Utils::DB::insert_one_record(
-        dbh   => $dbh,
-        table => 'date_agency_created',
-        values_href =>
-            { agency_id => $agency_id, date_created => $date_created }
-    );
-
     return $agency_id;
 }
 

=== modified file 'app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl'
--- app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl	2010-06-17 18:46:44 +0000
+++ app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl	2010-06-24 16:13:28 +0000
@@ -360,29 +360,13 @@
     my $program_id = MVHub::Utils::DB::get_next_in_sequence( $dbh,
         'program_id_sequence' );
     $values_href->{program_id} = $program_id;
+
     MVHub::Utils::DB::insert_one_record(
         dbh         => $dbh,
         table       => 'program',
         values_href => $values_href
     );
 
-    my $sql = MVHub::Utils::DB::get_sql_select_statement(
-        'PROGRAM_X_LAST_UPDATED');
-
-    my $date_created
-        = ( $dbh->selectrow_array( $sql, {}, $values_href->{'program_id'} ) )
-        [0];
-
-# Now that record's inserted into production table, add entry to date_program_created table
-    MVHub::Utils::DB::insert_one_record(
-        dbh         => $dbh,
-        table       => 'date_program_created',
-        values_href => {
-            program_id   => $values_href->{'program_id'},
-            date_created => $date_created
-        }
-    );
-
     return $program_id;
 }
 

=== modified file 'app-mvhub/setup/database/README'
--- app-mvhub/setup/database/README	2010-06-09 01:52:04 +0000
+++ app-mvhub/setup/database/README	2010-06-24 16:13:28 +0000
@@ -20,9 +20,9 @@
 
 EXAMPLE: You have a change that adds a new test column to the agency
 		 table. The largest numbered file in the sql directory is 
-		 currently 003, so a good name would be:
+		 currently 006, so a good name would be:
 		 
-		 004_add_test_column_to_agency.sql
+		 007_add_test_column_to_agency.sql
 
 ___ OPEN up your newly-named file in an editor and make your changes
 	between the BEGIN and COMMIT lines.

=== modified file 'app-mvhub/setup/database/sql/004_remove_unused_tables_and_seq.sql'
--- app-mvhub/setup/database/sql/004_remove_unused_tables_and_seq.sql	2010-06-18 19:39:24 +0000
+++ app-mvhub/setup/database/sql/004_remove_unused_tables_and_seq.sql	2010-06-24 16:13:28 +0000
@@ -1,4 +1,3 @@
---add version number and note before you add the other sql
 BEGIN;
 
 INSERT INTO version_log ( version,note ) 

=== added file 'app-mvhub/setup/database/sql/007_alter_tables_and_remove_old.sql'
--- app-mvhub/setup/database/sql/007_alter_tables_and_remove_old.sql	1970-01-01 00:00:00 +0000
+++ app-mvhub/setup/database/sql/007_alter_tables_and_remove_old.sql	2010-06-24 16:13:28 +0000
@@ -0,0 +1,25 @@
+BEGIN;
+INSERT INTO version_log ( version,note ) 
+	VALUES (7,'Alters tables agency and program to accomodate data from the tables to be removed');
+	
+ALTER TABLE agency 
+ADD date_created date 
+DEFAULT CURRENT_TIMESTAMP NOT NULL;
+
+UPDATE agency a 
+SET date_created = dac.date_created 
+FROM date_agency_created dac
+WHERE a.agency_id = dac.agency_id;
+
+ALTER TABLE  program
+ADD date_created date
+DEFAULT CURRENT_TIMESTAMP NOT NULL;
+
+UPDATE program p
+SET date_created = dpc.date_created
+FROM date_program_created dpc
+WHERE p.program_id = dpc.program_id;
+	
+DROP TABLE date_agency_created, date_program_created;
+	
+COMMIT;