mvhub-dev team mailing list archive
-
mvhub-dev team
-
Mailing list archive
-
Message #00348
[Branch ~mvhub-commit/mvhub/trunk] Rev 424: merged lp:~jimbox-comcast/mvhub/alter_and_remove_tables
Merge authors:
James Reynolds <jimbox@xxxxxxxxxx>
Related merge proposals:
https://code.launchpad.net/~jimbox-comcast/mvhub/alter_and_remove_tables/+merge/28428
proposed by: James Reynolds Jr. (jimbox-comcast)
review: Approve - Dan MacNeil (omacneil)
------------------------------------------------------------
revno: 424 [merge]
committer: Dan MacNeil <dan@xxxxxxxxxx>
branch nick: trunk
timestamp: Thu 2010-06-24 21:00:26 -0400
message:
merged lp:~jimbox-comcast/mvhub/alter_and_remove_tables
added:
app-mvhub/setup/database/sql/007_alter_tables_and_remove_old.sql
modified:
app-mvhub/DocumentRoot/cgi-bin/mvhub/admin/reports.pl
app-mvhub/DocumentRoot/cgi-bin/mvhub/agency_form.pl
app-mvhub/DocumentRoot/cgi-bin/mvhub/program.pl
app-mvhub/setup/database/README
app-mvhub/setup/database/sql/004_remove_unused_tables_and_seq.sql
--
lp:mvhub
https://code.launchpad.net/~mvhub-commit/mvhub/trunk
Your team MVHub Developers is subscribed to branch lp:mvhub.
To unsubscribe from this branch go to https://code.launchpad.net/~mvhub-commit/mvhub/trunk/+edit-subscription
=== 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-21 19:47:59 +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 15:15:46 +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 15:15:46 +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-16 18:18:35 +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-23 18:39:04 +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-21 21:41:03 +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;
