Hi Hackers,
Attached is the patch to add schema qualified dictionary names in FTS
configuration to overcome the confusion of duplicate dictionary names.
Also modified the resql test cases accordingly.
Please review.
Regards,
Ganesh Jaybhay
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/dictionaries.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/dictionaries.sql
index f0e7b0f..e7d81d4 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/dictionaries.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/dictionaries.sql
@@ -1,7 +1,6 @@
{# FETCH DICTIONARIES statement #}
-SELECT
- dictname
-FROM
- pg_ts_dict
-ORDER BY
- dictname
\ No newline at end of file
+SELECT CONCAT(pg_ns.nspname, '.', pg_td.dictname) as dictname
+-- pg_td.dictname
+FROM pg_ts_dict pg_td
+LEFT OUTER JOIN pg_namespace pg_ns
+ON pg_td.dictnamespace = pg_ns.oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/sql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/sql.sql
index 76ed60e..213cd19 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/sql.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/sql.sql
@@ -24,10 +24,10 @@ FROM
array_to_string(
array(
SELECT
- 'ALTER TEXT SEARCH CONFIGURATION ' || quote_ident(nspname) ||
+ 'ALTER TEXT SEARCH CONFIGURATION ' || quote_ident(b.nspname) ||
E'.' || quote_ident(cfg.cfgname) || ' ADD MAPPING FOR ' ||
t.alias || ' WITH ' ||
- array_to_string(array_agg(dict.dictname), ', ') || ';'
+ array_to_string(array_agg(CONCAT(pg_ns.nspname, '.', dict.dictname)), ', ') || ';'
FROM
pg_ts_config_map map
LEFT JOIN (
@@ -38,6 +38,7 @@ FROM
pg_catalog.ts_token_type(cfg.cfgparser)
) t ON (t.tokid = map.maptokentype)
LEFT OUTER JOIN pg_ts_dict dict ON (map.mapdict = dict.oid)
+ LEFT OUTER JOIN pg_namespace pg_ns ON (pg_ns.oid = dict.dictnamespace)
WHERE
map.mapcfg = cfg.oid
GROUP BY t.alias
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/tokenDictList.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/tokenDictList.sql
index e8a2d5b..3783928 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/tokenDictList.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/templates/fts_configurations/sql/default/tokenDictList.sql
@@ -9,15 +9,16 @@ SELECT
WHERE
t.tokid = maptokentype
) AS token,
- array_agg(dictname) AS dictname
+ array_agg(CONCAT(pg_ns.nspname, '.', pg_ts_dict.dictname)) AS dictname
FROM
pg_ts_config_map
LEFT OUTER JOIN pg_ts_config ON mapcfg = pg_ts_config.oid
LEFT OUTER JOIN pg_ts_dict ON mapdict = pg_ts_dict.oid
+ LEFT OUTER JOIN pg_namespace pg_ns ON pg_ns.oid = pg_ts_dict.dictnamespace
WHERE
mapcfg={{cfgid}}::OID
GROUP BY
token
ORDER BY
1
-{% endif %}
\ No newline at end of file
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_add_token.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_add_token.sql
index fcf1c10..f7b8548 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_add_token.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_add_token.sql
@@ -7,4 +7,4 @@ CREATE TEXT SEARCH CONFIGURATION test.test_fts_configuration_def1 (
);
COMMENT ON TEXT SEARCH CONFIGURATION test.test_fts_configuration_def1 IS 'test comment';
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def1 ADD MAPPING FOR file WITH english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def1 ADD MAPPING FOR file WITH pg_catalog.english_stem;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_remove_token.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_remove_token.sql
index 130488b..05ff797 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_remove_token.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_remove_token.sql
@@ -5,21 +5,21 @@
CREATE TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 (
PARSER = default
);
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR asciihword WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR asciiword WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR email WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR file WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR float WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR host WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_asciipart WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_numpart WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_part WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR int WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR numhword WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR numword WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR sfloat WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR uint WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR url WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR url_path WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR version WITH simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR asciihword WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR asciiword WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR email WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR file WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR float WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR host WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_asciipart WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_numpart WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_part WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR int WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR numhword WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR numword WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR sfloat WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR uint WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR url WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR url_path WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR version WITH pg_catalog.simple;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_rename.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_rename.sql
index 6924617..dd64a8f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_rename.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/alter_fts_conf_rename.sql
@@ -7,4 +7,4 @@ CREATE TEXT SEARCH CONFIGURATION test.test_fts_configuration (
);
COMMENT ON TEXT SEARCH CONFIGURATION test.test_fts_configuration IS 'test comment';
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration ADD MAPPING FOR file WITH english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration ADD MAPPING FOR file WITH pg_catalog.english_stem;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/create_fts_conf_def2.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/create_fts_conf_def2.sql
index 07cd7b9..fd1cc24 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/create_fts_conf_def2.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/create_fts_conf_def2.sql
@@ -5,22 +5,22 @@
CREATE TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 (
PARSER = default
);
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR asciihword WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR asciiword WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR email WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR file WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR float WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR host WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_asciipart WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_numpart WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_part WITH english_stem;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR int WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR numhword WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR numword WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR sfloat WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR uint WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR url WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR url_path WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR version WITH simple;
-ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR word WITH english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR asciihword WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR asciiword WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR email WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR file WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR float WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR host WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_asciipart WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_numpart WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR hword_part WITH pg_catalog.english_stem;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR int WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR numhword WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR numword WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR sfloat WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR uint WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR url WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR url_path WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR version WITH pg_catalog.simple;
+ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def2 ADD MAPPING FOR word WITH pg_catalog.english_stem;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/msql_alter_fts_conf_add_token.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/msql_alter_fts_conf_add_token.sql
index 23419b7..c2333b4 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/msql_alter_fts_conf_add_token.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/msql_alter_fts_conf_add_token.sql
@@ -1,3 +1,3 @@
ALTER TEXT SEARCH CONFIGURATION test.test_fts_configuration_def1
ADD MAPPING FOR file
- WITH english_stem;
+ WITH pg_catalog.english_stem;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/test.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/test.json
index b3b1b51..aae55ea 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/test.json
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/fts_configurations/tests/default/test.json
@@ -54,7 +54,7 @@
"sql_endpoint": "NODE-fts_configuration.sql_id",
"msql_endpoint": "NODE-fts_configuration.msql_id",
"data": {
- "tokens": {"added": [{"token": "file", "dictname": ["english_stem"]}]}
+ "tokens": {"added": [{"token": "file", "dictname": ["pg_catalog.english_stem"]}]}
},
"expected_sql_file": "alter_fts_conf_add_token.sql",
"expected_msql_file": "msql_alter_fts_conf_add_token.sql"
@@ -97,7 +97,7 @@
"sql_endpoint": "NODE-fts_configuration.sql_id",
"msql_endpoint": "NODE-fts_configuration.msql_id",
"data": {
- "tokens": {"deleted": [{"token": "word", "dictname": ["english_stem"]}]}
+ "tokens": {"deleted": [{"token": "word", "dictname": ["pg_catalog.english_stem"]}]}
},
"expected_sql_file": "alter_fts_conf_remove_token.sql",
"expected_msql_file": "msql_alter_fts_conf_remove_token.sql"
