%%%------------------------------------------------------------------- %%% File : mod_admin_update_sql.erl %%% Author : Alexey Shchepin %%% Purpose : Convert SQL DB to the new format %%% Created : 9 Aug 2017 by Alexey Shchepin %%% %%% %%% ejabberd, Copyright (C) 2002-2022 ProcessOne %%% %%% This program is free software; you can redistribute it and/or %%% modify it under the terms of the GNU General Public License as %%% published by the Free Software Foundation; either version 2 of the %%% License, or (at your option) any later version. %%% %%% This program is distributed in the hope that it will be useful, %%% but WITHOUT ANY WARRANTY; without even the implied warranty of %%% MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU %%% General Public License for more details. %%% %%% You should have received a copy of the GNU General Public License along %%% with this program; if not, write to the Free Software Foundation, Inc., %%% 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. %%% %%%------------------------------------------------------------------- -module(mod_admin_update_sql). -author('alexey@process-one.net'). -behaviour(gen_mod). -export([start/2, stop/1, reload/3, mod_options/1, get_commands_spec/0, depends/2, mod_doc/0]). % Commands API -export([update_sql/0]). -include("logger.hrl"). -include("ejabberd_commands.hrl"). -include_lib("xmpp/include/xmpp.hrl"). -include("ejabberd_sql_pt.hrl"). -include("translate.hrl"). %%% %%% gen_mod %%% start(_Host, _Opts) -> ejabberd_commands:register_commands(?MODULE, get_commands_spec()). stop(_Host) -> ejabberd_commands:unregister_commands(get_commands_spec()). reload(_Host, _NewOpts, _OldOpts) -> ok. depends(_Host, _Opts) -> []. %%% %%% Register commands %%% get_commands_spec() -> [#ejabberd_commands{name = update_sql, tags = [sql], desc = "Convert PostgreSQL DB to the new format", module = ?MODULE, function = update_sql, args = [], args_example = [], args_desc = [], result = {res, rescode}, result_example = ok, result_desc = "Status code: 0 on success, 1 otherwise"} ]. update_sql() -> lists:foreach( fun(Host) -> case ejabberd_sql_sup:is_started(Host) of false -> ok; true -> update_sql(Host) end end, ejabberd_option:hosts()). -record(state, {host :: binary(), dbtype :: mysql | pgsql | sqlite | mssql | odbc, escape}). update_sql(Host) -> LHost = jid:nameprep(Host), DBType = ejabberd_option:sql_type(LHost), IsSupported = case DBType of pgsql -> true; _ -> false end, if not IsSupported -> io:format("Converting ~p DB is not supported~n", [DBType]), error; true -> Escape = case DBType of mssql -> fun ejabberd_sql:standard_escape/1; sqlite -> fun ejabberd_sql:standard_escape/1; _ -> fun ejabberd_sql:escape/1 end, State = #state{host = LHost, dbtype = DBType, escape = Escape}, update_tables(State) end. update_tables(State) -> add_sh_column(State, "users"), drop_pkey(State, "users"), add_pkey(State, "users", ["server_host", "username"]), drop_sh_default(State, "users"), add_sh_column(State, "last"), drop_pkey(State, "last"), add_pkey(State, "last", ["server_host", "username"]), drop_sh_default(State, "last"), add_sh_column(State, "rosterusers"), drop_index(State, "i_rosteru_user_jid"), drop_index(State, "i_rosteru_username"), drop_index(State, "i_rosteru_jid"), create_unique_index(State, "rosterusers", "i_rosteru_sh_user_jid", ["server_host", "username", "jid"]), create_index(State, "rosterusers", "i_rosteru_sh_username", ["server_host", "username"]), create_index(State, "rosterusers", "i_rosteru_sh_jid", ["server_host", "jid"]), drop_sh_default(State, "rosterusers"), add_sh_column(State, "rostergroups"), drop_index(State, "pk_rosterg_user_jid"), create_index(State, "rostergroups", "i_rosterg_sh_user_jid", ["server_host", "username", "jid"]), drop_sh_default(State, "rostergroups"), add_sh_column(State, "sr_group"), add_pkey(State, "sr_group", ["server_host", "name"]), create_unique_index(State, "sr_group", "i_sr_group_sh_name", ["server_host", "name"]), drop_sh_default(State, "sr_group"), add_sh_column(State, "sr_user"), drop_index(State, "i_sr_user_jid_grp"), drop_index(State, "i_sr_user_jid"), drop_index(State, "i_sr_user_grp"), add_pkey(State, "sr_user", ["server_host", "jid", "grp"]), create_unique_index(State, "sr_user", "i_sr_user_sh_jid_grp", ["server_host", "jid", "grp"]), create_index(State, "sr_user", "i_sr_user_sh_jid", ["server_host", "jid"]), create_index(State, "sr_user", "i_sr_user_sh_grp", ["server_host", "grp"]), drop_sh_default(State, "sr_user"), add_sh_column(State, "spool"), drop_index(State, "i_despool"), create_index(State, "spool", "i_spool_sh_username", ["server_host", "username"]), drop_sh_default(State, "spool"), add_sh_column(State, "archive"), drop_index(State, "i_username"), drop_index(State, "i_username_timestamp"), drop_index(State, "i_timestamp"), drop_index(State, "i_peer"), drop_index(State, "i_bare_peer"), drop_index(State, "i_username_peer"), drop_index(State, "i_username_bare_peer"), create_index(State, "archive", "i_archive_sh_username_timestamp", ["server_host", "username", "timestamp"]), create_index(State, "archive", "i_archive_sh_timestamp", ["server_host", "timestamp"]), create_index(State, "archive", "i_archive_sh_username_peer", ["server_host", "username", "peer"]), create_index(State, "archive", "i_archive_sh_username_bare_peer", ["server_host", "username", "bare_peer"]), drop_sh_default(State, "archive"), add_sh_column(State, "archive_prefs"), drop_pkey(State, "archive_prefs"), add_pkey(State, "archive_prefs", ["server_host", "username"]), drop_sh_default(State, "archive_prefs"), add_sh_column(State, "vcard"), drop_pkey(State, "vcard"), add_pkey(State, "vcard", ["server_host", "username"]), drop_sh_default(State, "vcard"), add_sh_column(State, "vcard_search"), drop_pkey(State, "vcard_search"), drop_index(State, "i_vcard_search_lfn"), drop_index(State, "i_vcard_search_lfamily"), drop_index(State, "i_vcard_search_lgiven"), drop_index(State, "i_vcard_search_lmiddle"), drop_index(State, "i_vcard_search_lnickname"), drop_index(State, "i_vcard_search_lbday"), drop_index(State, "i_vcard_search_lctry"), drop_index(State, "i_vcard_search_llocality"), drop_index(State, "i_vcard_search_lemail"), drop_index(State, "i_vcard_search_lorgname"), drop_index(State, "i_vcard_search_lorgunit"), add_pkey(State, "vcard_search", ["server_host", "username"]), create_index(State, "vcard_search", "i_vcard_search_sh_lfn", ["server_host", "lfn"]), create_index(State, "vcard_search", "i_vcard_search_sh_lfamily", ["server_host", "lfamily"]), create_index(State, "vcard_search", "i_vcard_search_sh_lgiven", ["server_host", "lgiven"]), create_index(State, "vcard_search", "i_vcard_search_sh_lmiddle", ["server_host", "lmiddle"]), create_index(State, "vcard_search", "i_vcard_search_sh_lnickname", ["server_host", "lnickname"]), create_index(State, "vcard_search", "i_vcard_search_sh_lbday", ["server_host", "lbday"]), create_index(State, "vcard_search", "i_vcard_search_sh_lctry", ["server_host", "lctry"]), create_index(State, "vcard_search", "i_vcard_search_sh_llocality", ["server_host", "llocality"]), create_index(State, "vcard_search", "i_vcard_search_sh_lemail", ["server_host", "lemail"]), create_index(State, "vcard_search", "i_vcard_search_sh_lorgname", ["server_host", "lorgname"]), create_index(State, "vcard_search", "i_vcard_search_sh_lorgunit", ["server_host", "lorgunit"]), drop_sh_default(State, "vcard_search"), add_sh_column(State, "privacy_default_list"), drop_pkey(State, "privacy_default_list"), add_pkey(State, "privacy_default_list", ["server_host", "username"]), drop_sh_default(State, "privacy_default_list"), add_sh_column(State, "privacy_list"), drop_index(State, "i_privacy_list_username"), drop_index(State, "i_privacy_list_username_name"), create_index(State, "privacy_list", "i_privacy_list_sh_username", ["server_host", "username"]), create_unique_index(State, "privacy_list", "i_privacy_list_sh_username_name", ["server_host", "username", "name"]), drop_sh_default(State, "privacy_list"), add_sh_column(State, "private_storage"), drop_index(State, "i_private_storage_username"), drop_index(State, "i_private_storage_username_namespace"), add_pkey(State, "private_storage", ["server_host", "username", "namespace"]), create_index(State, "private_storage", "i_private_storage_sh_username", ["server_host", "username"]), drop_sh_default(State, "private_storage"), add_sh_column(State, "roster_version"), drop_pkey(State, "roster_version"), add_pkey(State, "roster_version", ["server_host", "username"]), drop_sh_default(State, "roster_version"), add_sh_column(State, "muc_room"), drop_sh_default(State, "muc_room"), add_sh_column(State, "muc_registered"), drop_sh_default(State, "muc_registered"), add_sh_column(State, "muc_online_room"), drop_sh_default(State, "muc_online_room"), add_sh_column(State, "muc_online_users"), drop_sh_default(State, "muc_online_users"), add_sh_column(State, "motd"), drop_pkey(State, "motd"), add_pkey(State, "motd", ["server_host", "username"]), drop_sh_default(State, "motd"), add_sh_column(State, "sm"), drop_index(State, "i_sm_sid"), drop_index(State, "i_sm_username"), add_pkey(State, "sm", ["usec", "pid"]), create_index(State, "sm", "i_sm_sh_username", ["server_host", "username"]), drop_sh_default(State, "sm"), add_sh_column(State, "push_session"), drop_index(State, "i_push_usn"), drop_index(State, "i_push_ut"), add_pkey(State, "push_session", ["server_host", "username", "timestamp"]), create_unique_index(State, "push_session", "i_push_session_susn", ["server_host", "username", "service", "node"]), drop_sh_default(State, "push_session"), add_sh_column(State, "mix_pam"), drop_index(State, "i_mix_pam"), drop_index(State, "i_mix_pam_us"), create_unique_index(State, "mix_pam", "i_mix_pam", ["username", "server_host", "channel", "service"]), create_index(State, "mix_pam", "i_mix_pam_us", ["username", "server_host"]), drop_sh_default(State, "mix_pam"), add_sh_column(State, "route"), drop_index(State, "i_route"), create_unique_index(State, "route", "i_route", ["domain", "server_host", "node", "pid"]), drop_sh_default(State, "route"), add_sh_column(State, "mqtt_pub"), drop_index(State, "i_mqtt_topic"), create_unique_index(State, "mqtt_pub", "i_mqtt_topic_server", ["topic", "server_host"]), drop_sh_default(State, "mqtt_pub"), ok. add_sh_column(#state{dbtype = pgsql} = State, Table) -> sql_query( State#state.host, ["ALTER TABLE ", Table, " ADD COLUMN server_host text NOT NULL DEFAULT '", (State#state.escape)(State#state.host), "';"]); add_sh_column(#state{dbtype = mysql} = State, Table) -> sql_query( State#state.host, ["ALTER TABLE ", Table, " ADD COLUMN server_host text NOT NULL DEFAULT '", (State#state.escape)(State#state.host), "';"]). drop_pkey(#state{dbtype = pgsql} = State, Table) -> sql_query( State#state.host, ["ALTER TABLE ", Table, " DROP CONSTRAINT ", Table, "_pkey;"]); drop_pkey(#state{dbtype = mysql} = State, Table) -> sql_query( State#state.host, ["ALTER TABLE ", Table, " DROP PRIMARY KEY;"]). add_pkey(#state{dbtype = pgsql} = State, Table, Cols) -> SCols = string:join(Cols, ", "), sql_query( State#state.host, ["ALTER TABLE ", Table, " ADD PRIMARY KEY (", SCols, ");"]); add_pkey(#state{dbtype = mysql} = State, Table, Cols) -> SCols = string:join(Cols, ", "), sql_query( State#state.host, ["ALTER TABLE ", Table, " ADD PRIMARY KEY (", SCols, ");"]). drop_sh_default(#state{dbtype = pgsql} = State, Table) -> sql_query( State#state.host, ["ALTER TABLE ", Table, " ALTER COLUMN server_host DROP DEFAULT;"]); drop_sh_default(#state{dbtype = mysql} = State, Table) -> sql_query( State#state.host, ["ALTER TABLE ", Table, " ALTER COLUMN server_host DROP DEFAULT;"]). drop_index(#state{dbtype = pgsql} = State, Index) -> sql_query( State#state.host, ["DROP INDEX ", Index, ";"]); drop_index(#state{dbtype = mysql} = State, Index) -> sql_query( State#state.host, ["DROP INDEX ", Index, ";"]). create_unique_index(#state{dbtype = pgsql} = State, Table, Index, Cols) -> SCols = string:join(Cols, ", "), sql_query( State#state.host, ["CREATE UNIQUE INDEX ", Index, " ON ", Table, " USING btree (", SCols, ");"]); create_unique_index(#state{dbtype = mysql} = State, Table, Index, Cols) -> Cols2 = [C ++ "(75)" || C <- Cols], SCols = string:join(Cols2, ", "), sql_query( State#state.host, ["CREATE UNIQUE INDEX ", Index, " ON ", Table, "(", SCols, ");"]). create_index(#state{dbtype = pgsql} = State, Table, Index, Cols) -> SCols = string:join(Cols, ", "), sql_query( State#state.host, ["CREATE INDEX ", Index, " ON ", Table, " USING btree (", SCols, ");"]); create_index(#state{dbtype = mysql} = State, Table, Index, Cols) -> Cols2 = [C ++ "(75)" || C <- Cols], SCols = string:join(Cols2, ", "), sql_query( State#state.host, ["CREATE INDEX ", Index, " ON ", Table, "(", SCols, ");"]). sql_query(Host, Query) -> io:format("executing \"~ts\" on ~ts~n", [Query, Host]), case ejabberd_sql:sql_query(Host, Query) of {error, Error} -> io:format("error: ~p~n", [Error]), ok; _ -> ok end. mod_options(_) -> []. mod_doc() -> #{desc => ?T("This module can be used to update existing SQL database " "from the default to the new schema. Check the section " "http://../database/#default-and-new-schemas[Default and New Schemas] for details. " "Please note that only PostgreSQL is supported. " "When the module is loaded use _`update_sql`_ API.")}.