Merge lp:~jimbox-comcast/mvhub/remove_additional_seq into lp:~mvhub-commit/mvhub/trunk

[James Reynolds Jr.]

Adds a script to drop two vestigial sequences from the database.

[Dan MacNeil (omacneil)]

The old version of 004_remove_unused_tables_and_seq.sql (now in trunk)

...did some stuff and set version_log.version =4

The new version does **more** stuff, but under many circumstances this will never be called.

If a version_log.version == 4, 004_remove_unused_tables_seq.sql won't be run.

You need to put the new stuff in 006_remove_more_unused_seq.sql

In app-mvhub/project-tools/bin/mv_update_development the database is DROPped , new data is loaded and all the schema updating scripts in app-mvhub/setup/database/sql are run.

In production, we don't care to wipe old data, so only the new, unrun sql scripts are run.

Make fix & push branch

review: Needs Fixing

[lp:~jimbox-comcast/mvhub/remove_additional_seq]

413. By James Reynolds <email address hidden> on 2010-06-18

Moved the instruction to remove the two seq synonym_id_sequence and user_id_sequence to 006_~.

414. By James Reynolds <email address hidden> on 2010-06-18

Merged with trunk to prep for testing before submitting a merge request.

415. By James Reynolds <email address hidden> on 2010-06-18

Reverted to version 412 then created a new sql script to remove two seq.

416. By James Reynolds <email address hidden> on 2010-07-08

Prepped files to merge with trunk before pushing to launchpad.

417. By James Reynolds <email address hidden> on 2010-07-08

Succesfully merged with trunk. Next testing before the merge request.

418. By James Reynolds <email address hidden> on 2010-07-09

Changed 004 to origional so there are no conflicts and changed 006 to only remove user_id_seq.

419. By James Reynolds <email address hidden> on 2010-07-09

This branch is now prepared for merging.

420. By James Reynolds <email address hidden> on 2010-07-11

Modified the old script so it contained the latest version number.

421. By James Reynolds <email address hidden> on 2010-07-11

Merged branch with trunk before pushing to launchpad.

Unmerged revisions

=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib	2010-06-09 14:25:30 +0000
+++ app-mvhub/conf/sql_select.lib	2010-06-18 18:31:24 +0000
@@ -259,18 +259,6 @@
 WHERE 
   program_id = ?   
 
-[SYNONYM_STEM_X_CONCAT_SUBMITTED_WORDS]
-SELECT 
-  our_aggregate_concat(submitted_word) 
-AS 
-  submitted_words 
-FROM 
-  synonym_stem 
-GROUP BY 
-  synonym_id 
-ORDER BY
-  submitted_words
-
 [SYNONYM_STEM_X_WORD_STEM]  
 SELECT DISTINCT 
   word_stem

=== 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-10 20:15:47 +0000
+++ app-mvhub/setup/database/sql/004_remove_unused_tables_and_seq.sql	2010-06-18 18:31:24 +0000
@@ -1,4 +1,3 @@
---add version number and note before you add the other sql
 BEGIN;
 
 INSERT INTO version_log ( version,note ) 
@@ -6,6 +5,6 @@
 
 DROP TABLE user_type, log_category_search, web_requests CASCADE;
 
-DROP SEQUENCE user_type_type_id_seq, log_category_search_search_id_seq, web_requests_id_seq;
-     
+DROP SEQUENCE user_type_type_id_seq, log_category_search_search_id_seq, web_requests_id_seq,
+     	         
 COMMIT;

=== removed file 'app-mvhub/setup/database/sql/005_synonym_cat_functions.sql'
--- app-mvhub/setup/database/sql/005_synonym_cat_functions.sql	2010-06-16 16:30:44 +0000
+++ app-mvhub/setup/database/sql/005_synonym_cat_functions.sql	1970-01-01 00:00:00 +0000
@@ -1,22 +0,0 @@
-BEGIN;
-INSERT INTO version_log (version, note ) 
-	VALUES (5,'adding two functions to return concatenated 
-synonyms');
-/* Returns the concatenation of its 2 arguments, separated by a comma and a space. */
-CREATE OR REPLACE FUNCTION  our_concat(text, text) RETURNS text AS 
-$$
-    SELECT $1 || ', ' || $2;
- $$  LANGUAGE sql
-  STABLE
-  RETURNS NULL ON NULL INPUT;
-
-
-/* Returns a concatenation of all of the values in a grouped column, 
-   delimited by a comma and a space. 
-*/
-CREATE AGGREGATE our_aggregate_concat (
-    sfunc = our_concat,
-    basetype = text,
-    stype = text
-);
-COMMIT;

=== added file 'app-mvhub/setup/database/sql/006_remove_more_unused_seq.sql'
--- app-mvhub/setup/database/sql/006_remove_more_unused_seq.sql	1970-01-01 00:00:00 +0000
+++ app-mvhub/setup/database/sql/006_remove_more_unused_seq.sql	2010-06-18 18:31:24 +0000
@@ -0,0 +1,8 @@
+BEGIN;
+
+INSERT INTO version_log ( version,note ) 
+	VALUES (6,'Removes two unused seq');
+
+DROP SEQUENCE synonym_id_sequence, user_id_sequence;
+     	         
+COMMIT;

=== modified file 'lib-mvhub/lib/MVHub/Synonyms.pm'
--- lib-mvhub/lib/MVHub/Synonyms.pm	2010-06-09 15:43:40 +0000
+++ lib-mvhub/lib/MVHub/Synonyms.pm	2010-06-18 18:31:24 +0000
@@ -37,8 +37,11 @@
 # one word synonyms of each other.
 sub fetch_synonyms_from_db {
     my $dbh = MVHub::Utils::DB::get_dbh();
-    my $sql = MVHub::Utils::DB::get_sql_select_statement(
-        'SYNONYM_STEM_X_CONCAT_SUBMITTED_WORDS');
+    my $sql
+        = 'SELECT our_aggregate_concat(submitted_word) AS submitted_words '
+        . 'FROM synonym_stem '
+        . 'GROUP BY synonym_id '
+        . 'ORDER BY submitted_words';
     my $rows_aref = $dbh->selectcol_arrayref($sql);
     foreach ( @{$rows_aref} ) {
         s/,//g;