mvhub-dev team mailing list archive

[Priya Ravindran <pthirumal@xxxxxxxxxx>]

Priya Ravindran has proposed merging lp:~priya/mvhub/move_sql_lib_synonym into lp:mvhub.

Requested reviews:
  MVHub devs with commit rights (mvhub-commit)


MOved queries from Synonyms.pm to sql_select.lib and also created a stored procedure our_aggregate_concat by running sql scripts in 004_synonym_cat_function.
-- 
https://code.launchpad.net/~priya/mvhub/move_sql_lib_synonym/+merge/27190
Your team MVHub Developers is subscribed to branch lp:mvhub.

=== modified file 'app-mvhub/conf/sql_select.lib'
--- app-mvhub/conf/sql_select.lib	2010-05-14 17:33:15 +0000
+++ app-mvhub/conf/sql_select.lib	2010-06-09 18:12:29 +0000
@@ -259,6 +259,18 @@
 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

=== added file 'app-mvhub/setup/database/sql/004_synonym_cat_functions.sql'
--- app-mvhub/setup/database/sql/004_synonym_cat_functions.sql	1970-01-01 00:00:00 +0000
+++ app-mvhub/setup/database/sql/004_synonym_cat_functions.sql	2010-06-09 18:12:29 +0000
@@ -0,0 +1,21 @@
+BEGIN;
+INSERT INTO version_log (version, note ) 
+	VALUES (4,'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;

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