CiviCRM to take advantage of SQL translations

--- civicrm/CRM/Core/I18n/Schema-4.1.2-wordpress.php    2011-11-30 13:39:50.000000000 +0100
+++ civicrm/CRM/Core/I18n/Schema.php    2012-06-29 12:34:12.009290721 +0200
@@ -212,6 +212,8 @@
      */
     static function addLocale($locale, $source)
     {
+        global $civicrm_root;
+
         // get the current supported locales
         $domain = new CRM_Core_DAO_Domain();
         $domain->find(true);
@@ -225,16 +227,107 @@
         // build the required SQL queries
         $columns = CRM_Core_I18n_SchemaStructure::columns();
         $indices = CRM_Core_I18n_SchemaStructure::indices();
+
+        ////// HACK: try to use the translated strings from the sql file
+        $sqlPath = $civicrm_root . DIRECTORY_SEPARATOR . 'sql';
+        $fileName = ($locale == 'en_US') ?
+            $sqlPath . DIRECTORY_SEPARATOR . 'civicrm_data.mysql' :
+            $sqlPath . DIRECTORY_SEPARATOR . "civicrm_data.{$locale}.mysql";
+
+        $translations = array();
+
+        if ( file_exists($fileName)) {
+
+            $string = file_get_contents($fileName);
+
+            // change \r\n to fix windows issues
+            $string = str_replace("\r\n", "\n", $string);
+
+            //get rid of comments starting with # and --
+            $string = preg_replace("/^#[^\n]*$/m", "\n", $string);
+            $string = preg_replace("/^(--[^-]).*/m", "\n", $string);
+
+            $queries = preg_split('/;\s*$/m', $string);
+
+            $Trim = function( &$e ) { $e = trim($e, "`\x20\x09\x0A\x0D\x00\x0B"); };
+
+            foreach ($queries as $query) {
+
+                $query = trim($query);
+
+                // we are just working with "INSERT INTO" queries that also contain a 'name' field
+                if (!empty($query) && preg_match('/^INSERT INTO[^(]*\([^)]*\bname\b[^)]*\)/', $query) ) {
+
+                    // match the <table name> <field names> <list of values>
+                    preg_match('/^INSERT\sINTO\s*`?(?<table>[a-zA-Z_-]+)`?\s*\((?<fields>[^)]*)\)\s*VALUES\s*(?<values>.*)$/ms', $query, $insert);
+                    $table  = $insert['table'];
+                    $fields = $insert['fields'];
+                    $values = $insert['values'];
+
+                    // doesn't make sense if the table is not localizable
+                    if ( ! array_key_exists($table, $columns) ) {
+                        continue;
+                    }
+
+                    // obtain and clean field names
+                    $fields = explode( ',', $fields);
+                    array_walk( $fields, $Trim);
+
+                    // localizable fields of this table
+                    $l10n_fields = array_keys( $columns[ $table ] );
+
+                    // match each row of values
+                    preg_match_all("/\(
+                        ((?:
+                            [^()'] |
+                            \" (?: [^\"] | \\\" )* \" |
+                            '  (?: [^']  |  \\' )*  ' |
+                            \w[\w\d_-]*\( [^()]* \) # not accepting any parenthesis inside here, sorry
+                        )*)
+                        \) \s* (?: , | $ ) /Umx", $values, $values_rows, PREG_SET_ORDER);
+
+                    foreach ($values_rows as $values_row) {
+
+                        // for each row get the actual values
+                        preg_match_all("/ (
+                                [^()']* |
+                                \" (?: [^\"] | \\\" )* \" |
+                                 ' (?:  [^'] |  \\' )*  ' |
+                                \w[\w\d_-]*\( [^()]* \)
+                            ) \s* (?: , | $ ) /Umx", $values_row[1], $values, PREG_SET_ORDER);
+
+                        $row = array();
+                        for($i = 0; $i < count($fields); $i++) {
+                            if ( $fields[$i] == 'name' || in_array( $fields[$i], $l10n_fields)) {
+                                $row[ $fields[$i] ] = $values[$i][1];
+                            }
+                        }
+                        $translations[ $table ][] = $row;
+                    }
+                }
+            }
+        }
+
         $queries = array();
         foreach ($columns as $table => $hash) {
             // add new columns
             foreach ($hash as $column => $type) {
                 // CRM-7854: skip existing columns
-                if (CRM_Core_DAO::checkFieldExists($table, "{$column}_{$locale}", false)) continue;
+                if (CRM_Core_DAO::checkFieldExists($table, "{$column}_{$locale}", FALSE)) {
+                    continue;
+                }
                 $queries[] = "ALTER TABLE {$table} ADD {$column}_{$locale} {$type}";
-                $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
+                // Update to the translated values, if they exist
+                if ($translations && array_key_exists( $table, $translations) ) {
+                    foreach ($translations[$table] as $row) {
+                        $insert_value = ( $row[$column] ) ? $row[$column] : $column . '_' . $source;
+                        $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$insert_value} WHERE name = {$row['name']}";
+                    }
+                } else {
+                    $queries[] = "UPDATE {$table} SET {$column}_{$locale} = {$column}_{$source}";
+                }
             }
-
+
             // add view
             $queries[] = self::createViewQuery($locale, $table, $dao);