processone / ejabberd / 471

Build # 471

Build Type

push

github

Committed by GitHub

Commit Message

Merge pull request #3980 from nosnilmot/sql-maintenance

Run Details

114 of 114 new or added lines in 3 files covered. (100.0%)

13456 of 40618 relevant lines covered (33.13%)

671.2 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

0.0

/src/mod_admin_update_sql.erl

%%%-------------------------------------------------------------------
%%% File    : mod_admin_update_sql.erl
%%% Author  : Alexey Shchepin <alexey@process-one.net>
%%% Purpose : Convert SQL DB to the new format
%%% Created :  9 Aug 2017 by Alexey Shchepin <alexey@process-one.net>
%%%
%%%
%%% ejabberd, Copyright (C) 2002-2023   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 MS SQL, MySQL or 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
            mssql -> true;
            mysql -> true;
            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, "rosterusers", "i_rosteru_user_jid"),
    drop_index(State, "rosterusers", "i_rosteru_username"),
    drop_index(State, "rosterusers", "i_rosteru_jid"),
    create_unique_index(State, "rosterusers", "i_rosteru_sh_user_jid", ["server_host", "username", "jid"]),
    create_index(State, "rosterusers", "i_rosteru_sh_jid", ["server_host", "jid"]),
    drop_sh_default(State, "rosterusers"),

    add_sh_column(State, "rostergroups"),
    drop_index(State, "rostergroups", "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"),
    drop_index(State, "sr_group", "i_sr_group_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, "sr_user", "i_sr_user_jid_grp"),
    drop_index(State, "sr_user", "i_sr_user_jid"),
    drop_index(State, "sr_user", "i_sr_user_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_grp", ["server_host", "grp"]),
    drop_sh_default(State, "sr_user"),

    add_sh_column(State, "spool"),
    drop_index(State, "spool", "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, "archive", "i_username"),
    drop_index(State, "archive", "i_username_timestamp"),
    drop_index(State, "archive", "i_timestamp"),
    drop_index(State, "archive", "i_peer"),
    drop_index(State, "archive", "i_bare_peer"),
    drop_index(State, "archive", "i_username_peer"),
    drop_index(State, "archive", "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, "vcard_search", "i_vcard_search_lfn"),
    drop_index(State, "vcard_search", "i_vcard_search_lfamily"),
    drop_index(State, "vcard_search", "i_vcard_search_lgiven"),
    drop_index(State, "vcard_search", "i_vcard_search_lmiddle"),
    drop_index(State, "vcard_search", "i_vcard_search_lnickname"),
    drop_index(State, "vcard_search", "i_vcard_search_lbday"),
    drop_index(State, "vcard_search", "i_vcard_search_lctry"),
    drop_index(State, "vcard_search", "i_vcard_search_llocality"),
    drop_index(State, "vcard_search", "i_vcard_search_lemail"),
    drop_index(State, "vcard_search", "i_vcard_search_lorgname"),
    drop_index(State, "vcard_search", "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, "privacy_list", "i_privacy_list_username"),
    drop_index(State, "privacy_list", "i_privacy_list_username_name"),
    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, "private_storage", "i_private_storage_username"),
    drop_index(State, "private_storage", "i_private_storage_username_namespace"),
    add_pkey(State, "private_storage", ["server_host", "username", "namespace"]),
    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, "sm", "i_sm_sid"),
    drop_index(State, "sm", "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, "push_session", "i_push_usn"),
    drop_index(State, "push_session", "i_push_ut"),
    create_unique_index(State, "push_session", "i_push_session_susn", ["server_host", "username", "service", "node"]),
    create_index(State, "push_session", "i_push_session_sh_username_timestamp", ["server_host", "username", "timestamp"]),
    drop_sh_default(State, "push_session"),

    add_sh_column(State, "mix_pam"),
    drop_index(State, "mix_pam", "i_mix_pam"),
    drop_index(State, "mix_pam", "i_mix_pam_u"),
    drop_index(State, "mix_pam", "i_mix_pam_us"),
    create_unique_index(State, "mix_pam", "i_mix_pam", ["username", "server_host", "channel", "service"]),
    drop_sh_default(State, "mix_pam"),

    add_sh_column(State, "mqtt_pub"),
    drop_index(State, "mqtt_pub", "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 = mssql} = State, Table) ->
    sql_query(
      State#state.host,
      ["ALTER TABLE [", Table, "] ADD [server_host] varchar (250) NOT NULL CONSTRAINT [server_host_default] 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 varchar(191) 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 = mssql} = State, Table) ->
    sql_query(
      State#state.host,
      ["ALTER TABLE [", Table, "] DROP CONSTRAINT [", Table, "_PRIMARY];"]);
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 = mssql} = State, Table, Cols) ->
    SCols = string:join(Cols, "], ["),
    sql_query(
      State#state.host,
      ["ALTER TABLE [", Table, "] ADD CONSTRAINT [", Table, "_PRIMARY] PRIMARY KEY CLUSTERED ([", SCols, "]) ",
       "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ",
       "ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];"]);
add_pkey(#state{dbtype = mysql} = State, Table, Cols) ->
    Cols2 = [C ++ mysql_keylen(Table, C) || C <- Cols],
    SCols = string:join(Cols2, ", "),
    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 = mssql} = State, Table) ->
    sql_query(
      State#state.host,
      ["ALTER TABLE [", Table, "] DROP CONSTRAINT [server_host_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, _Table, Index) ->
    sql_query(
      State#state.host,
      ["DROP INDEX ", Index, ";"]);
drop_index(#state{dbtype = mssql} = State, Table, Index) ->
    sql_query(
      State#state.host,
      ["DROP INDEX [", mssql_old_index_name(Index), "] ON [", Table, "];"]);
drop_index(#state{dbtype = mysql} = State, Table, Index) ->
    sql_query(
      State#state.host,
      ["ALTER TABLE ", Table, " 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 = mssql} = State, Table, "i_privacy_list_sh_username_name" = Index, Cols) ->
    create_index(State, Table, Index, Cols);
create_unique_index(#state{dbtype = mssql} = State, Table, Index, Cols) ->
    SCols = string:join(Cols, ", "),
    sql_query(
      State#state.host,
      ["CREATE UNIQUE ", mssql_clustered(Index), "INDEX [", mssql_new_index_name(Index), "] ",
       "ON [", Table, "] (", SCols, ") ",
       "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);"]);
create_unique_index(#state{dbtype = mysql} = State, Table, Index, Cols) ->
    Cols2 = [C ++ mysql_keylen(Index, C) || 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 = mssql} = State, Table, Index, Cols) ->
    SCols = string:join(Cols, ", "),
    sql_query(
      State#state.host,
      ["CREATE INDEX [", mssql_new_index_name(Index), "] ON [", Table, "] (", SCols, ") ",
       "WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);"]);
create_index(#state{dbtype = mysql} = State, Table, Index, Cols) ->
    Cols2 = [C ++ mysql_keylen(Index, C) || C <- Cols],
    SCols = string:join(Cols2, ", "),
    sql_query(
      State#state.host,
      ["CREATE INDEX ", Index, " ON ", Table, "(",
       SCols, ");"]).

mssql_old_index_name("i_bare_peer") -> "archive_bare_peer";
mssql_old_index_name("i_peer") -> "archive_peer";
mssql_old_index_name("i_timestamp") -> "archive_timestamp";
mssql_old_index_name("i_username") -> "archive_username";
mssql_old_index_name("i_username_bare_peer") -> "archive_username_bare_peer";
mssql_old_index_name("i_username_peer") -> "archive_username_peer";
mssql_old_index_name("i_username_timestamp") -> "archive_username_timestamp";
mssql_old_index_name("i_push_usn") -> "i_push_usn";
mssql_old_index_name("i_push_ut") -> "i_push_ut";
mssql_old_index_name("pk_rosterg_user_jid") -> "rostergroups_username_jid";
mssql_old_index_name("i_rosteru_jid") -> "rosterusers_jid";
mssql_old_index_name("i_rosteru_username") -> "rosterusers_username";
mssql_old_index_name("i_rosteru_user_jid") -> "rosterusers_username_jid";
mssql_old_index_name("i_despool") -> "spool_username";
mssql_old_index_name("i_sr_user_jid_grp") -> "sr_user_jid_group";
mssql_old_index_name(Index) -> string:substr(Index, 3).

mssql_new_index_name("i_rosterg_sh_user_jid") -> "rostergroups_sh_username_jid";
mssql_new_index_name("i_rosteru_sh_jid") -> "rosterusers_sh_jid";
mssql_new_index_name("i_rosteru_sh_user_jid") -> "rosterusers_sh_username_jid";
mssql_new_index_name("i_sr_user_sh_jid_grp") -> "sr_user_sh_jid_group";
mssql_new_index_name(Index) -> string:substr(Index, 3).

mssql_clustered("i_mix_pam") -> "";
mssql_clustered("i_push_session_susn") -> "";
mssql_clustered(_) -> "CLUSTERED ".

mysql_keylen(_, "bare_peer") -> "(191)";
mysql_keylen(_, "channel") -> "(191)";
mysql_keylen(_, "domain") -> "(75)";
mysql_keylen(_, "jid") -> "(75)";
mysql_keylen(_, "name") -> "(75)";
mysql_keylen(_, "node") -> "(75)";
mysql_keylen(_, "peer") -> "(191)";
mysql_keylen(_, "pid") -> "(75)";
mysql_keylen(_, "server_host") -> "(191)";
mysql_keylen(_, "service") -> "(191)";
mysql_keylen(_, "topic") -> "(191)";
mysql_keylen("i_privacy_list_sh_username_name", "username") -> "(75)";
mysql_keylen("i_rosterg_sh_user_jid", "username") -> "(75)";
mysql_keylen("i_rosteru_sh_user_jid", "username") -> "(75)";
mysql_keylen(_, "username") -> "(191)";
mysql_keylen(_, _) -> "".

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 MS SQL, MySQL, and PostgreSQL are supported. "
             "When the module is loaded use _`update_sql`_ API.")}.

1	%%%-------------------------------------------------------------------
2	%%% File : mod_admin_update_sql.erl
3	%%% Author : Alexey Shchepin <alexey@process-one.net>
4	%%% Purpose : Convert SQL DB to the new format
5	%%% Created : 9 Aug 2017 by Alexey Shchepin <alexey@process-one.net>
6	%%%
7	%%%
8	%%% ejabberd, Copyright (C) 2002-2023 ProcessOne
9	%%%
10	%%% This program is free software; you can redistribute it and/or
11	%%% modify it under the terms of the GNU General Public License as
12	%%% published by the Free Software Foundation; either version 2 of the
13	%%% License, or (at your option) any later version.
14	%%%
15	%%% This program is distributed in the hope that it will be useful,
16	%%% but WITHOUT ANY WARRANTY; without even the implied warranty of
17	%%% MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
18	%%% General Public License for more details.
19	%%%
20	%%% You should have received a copy of the GNU General Public License along
21	%%% with this program; if not, write to the Free Software Foundation, Inc.,
22	%%% 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23	%%%
24	%%%-------------------------------------------------------------------
25
26	-module(mod_admin_update_sql).
27	-author('alexey@process-one.net').
28
29	-behaviour(gen_mod).
30
31	-export([start/2, stop/1, reload/3, mod_options/1,
32	get_commands_spec/0, depends/2, mod_doc/0]).
33
34	% Commands API
35	-export([update_sql/0]).
36
37
38	-include("logger.hrl").
39	-include("ejabberd_commands.hrl").
40	-include_lib("xmpp/include/xmpp.hrl").
41	-include("ejabberd_sql_pt.hrl").
42	-include("translate.hrl").
43
44	%%%
45	%%% gen_mod
46	%%%
47
48	start(_Host, _Opts) ->
49	ejabberd_commands:register_commands(?MODULE, get_commands_spec()).	×
50
51	stop(_Host) ->
52	ejabberd_commands:unregister_commands(get_commands_spec()).	×
53
54	reload(_Host, _NewOpts, _OldOpts) ->
55	ok.	×
56
57	depends(_Host, _Opts) ->
58	[].	×
59
60	%%%
61	%%% Register commands
62	%%%
63
64	get_commands_spec() ->
65	[#ejabberd_commands{name = update_sql, tags = [sql],	×
66	desc = "Convert MS SQL, MySQL or PostgreSQL DB to the new format",
67	module = ?MODULE, function = update_sql,
68	args = [],
69	args_example = [],
70	args_desc = [],
71	result = {res, rescode},
72	result_example = ok,
73	result_desc = "Status code: 0 on success, 1 otherwise"}
74	].
75
76	update_sql() ->
77	lists:foreach(	×
78	fun(Host) ->
79	case ejabberd_sql_sup:is_started(Host) of	×
80	false ->
81	ok;	×
82	true ->
83	update_sql(Host)	×
84	end
85	end, ejabberd_option:hosts()).
86
87	-record(state, {host :: binary(),
88	dbtype :: mysql \| pgsql \| sqlite \| mssql \| odbc,
89	escape}).
90
91	update_sql(Host) ->
92	LHost = jid:nameprep(Host),	×
93	DBType = ejabberd_option:sql_type(LHost),	×
94	IsSupported =	×
95	case DBType of
96	mssql -> true;	×
97	mysql -> true;	×
98	pgsql -> true;	×
99	_ -> false	×
100	end,
101	if	×
102	not IsSupported ->
103	io:format("Converting ~p DB is not supported~n", [DBType]),	×
104	error;	×
105	true ->
106	Escape =	×
107	case DBType of
108	mssql -> fun ejabberd_sql:standard_escape/1;	×
109	sqlite -> fun ejabberd_sql:standard_escape/1;	×
110	_ -> fun ejabberd_sql:escape/1	×
111	end,
112	State = #state{host = LHost,	×
113	dbtype = DBType,
114	escape = Escape},
115	update_tables(State)	×
116	end.
117
118	update_tables(State) ->
119	add_sh_column(State, "users"),	×
120	drop_pkey(State, "users"),	×
121	add_pkey(State, "users", ["server_host", "username"]),	×
122	drop_sh_default(State, "users"),	×
123
124	add_sh_column(State, "last"),	×
125	drop_pkey(State, "last"),	×
126	add_pkey(State, "last", ["server_host", "username"]),	×
127	drop_sh_default(State, "last"),	×
128
129	add_sh_column(State, "rosterusers"),	×
130	drop_index(State, "rosterusers", "i_rosteru_user_jid"),	×
131	drop_index(State, "rosterusers", "i_rosteru_username"),	×
132	drop_index(State, "rosterusers", "i_rosteru_jid"),	×
133	create_unique_index(State, "rosterusers", "i_rosteru_sh_user_jid", ["server_host", "username", "jid"]),	×
134	create_index(State, "rosterusers", "i_rosteru_sh_jid", ["server_host", "jid"]),	×
135	drop_sh_default(State, "rosterusers"),	×
136
137	add_sh_column(State, "rostergroups"),	×
138	drop_index(State, "rostergroups", "pk_rosterg_user_jid"),	×
139	create_index(State, "rostergroups", "i_rosterg_sh_user_jid", ["server_host", "username", "jid"]),	×
140	drop_sh_default(State, "rostergroups"),	×
141
142	add_sh_column(State, "sr_group"),	×
143	drop_index(State, "sr_group", "i_sr_group_name"),	×
144	create_unique_index(State, "sr_group", "i_sr_group_sh_name", ["server_host", "name"]),	×
145	drop_sh_default(State, "sr_group"),	×
146
147	add_sh_column(State, "sr_user"),	×
148	drop_index(State, "sr_user", "i_sr_user_jid_grp"),	×
149	drop_index(State, "sr_user", "i_sr_user_jid"),	×
150	drop_index(State, "sr_user", "i_sr_user_grp"),	×
151	create_unique_index(State, "sr_user", "i_sr_user_sh_jid_grp", ["server_host", "jid", "grp"]),	×
152	create_index(State, "sr_user", "i_sr_user_sh_grp", ["server_host", "grp"]),	×
153	drop_sh_default(State, "sr_user"),	×
154
155	add_sh_column(State, "spool"),	×
156	drop_index(State, "spool", "i_despool"),	×
157	create_index(State, "spool", "i_spool_sh_username", ["server_host", "username"]),	×
158	drop_sh_default(State, "spool"),	×
159
160	add_sh_column(State, "archive"),	×
161	drop_index(State, "archive", "i_username"),	×
162	drop_index(State, "archive", "i_username_timestamp"),	×
163	drop_index(State, "archive", "i_timestamp"),	×
164	drop_index(State, "archive", "i_peer"),	×
165	drop_index(State, "archive", "i_bare_peer"),	×
166	drop_index(State, "archive", "i_username_peer"),	×
167	drop_index(State, "archive", "i_username_bare_peer"),	×
168	create_index(State, "archive", "i_archive_sh_username_timestamp", ["server_host", "username", "timestamp"]),	×
169	create_index(State, "archive", "i_archive_sh_timestamp", ["server_host", "timestamp"]),	×
170	create_index(State, "archive", "i_archive_sh_username_peer", ["server_host", "username", "peer"]),	×
171	create_index(State, "archive", "i_archive_sh_username_bare_peer", ["server_host", "username", "bare_peer"]),	×
172	drop_sh_default(State, "archive"),	×
173
174	add_sh_column(State, "archive_prefs"),	×
175	drop_pkey(State, "archive_prefs"),	×
176	add_pkey(State, "archive_prefs", ["server_host", "username"]),	×
177	drop_sh_default(State, "archive_prefs"),	×
178
179	add_sh_column(State, "vcard"),	×
180	drop_pkey(State, "vcard"),	×
181	add_pkey(State, "vcard", ["server_host", "username"]),	×
182	drop_sh_default(State, "vcard"),	×
183
184	add_sh_column(State, "vcard_search"),	×
185	drop_pkey(State, "vcard_search"),	×
186	drop_index(State, "vcard_search", "i_vcard_search_lfn"),	×
187	drop_index(State, "vcard_search", "i_vcard_search_lfamily"),	×
188	drop_index(State, "vcard_search", "i_vcard_search_lgiven"),	×
189	drop_index(State, "vcard_search", "i_vcard_search_lmiddle"),	×
190	drop_index(State, "vcard_search", "i_vcard_search_lnickname"),	×
191	drop_index(State, "vcard_search", "i_vcard_search_lbday"),	×
192	drop_index(State, "vcard_search", "i_vcard_search_lctry"),	×
193	drop_index(State, "vcard_search", "i_vcard_search_llocality"),	×
194	drop_index(State, "vcard_search", "i_vcard_search_lemail"),	×
195	drop_index(State, "vcard_search", "i_vcard_search_lorgname"),	×
196	drop_index(State, "vcard_search", "i_vcard_search_lorgunit"),	×
197	add_pkey(State, "vcard_search", ["server_host", "username"]),	×
198	create_index(State, "vcard_search", "i_vcard_search_sh_lfn", ["server_host", "lfn"]),	×
199	create_index(State, "vcard_search", "i_vcard_search_sh_lfamily", ["server_host", "lfamily"]),	×
200	create_index(State, "vcard_search", "i_vcard_search_sh_lgiven", ["server_host", "lgiven"]),	×
201	create_index(State, "vcard_search", "i_vcard_search_sh_lmiddle", ["server_host", "lmiddle"]),	×
202	create_index(State, "vcard_search", "i_vcard_search_sh_lnickname", ["server_host", "lnickname"]),	×
203	create_index(State, "vcard_search", "i_vcard_search_sh_lbday", ["server_host", "lbday"]),	×
204	create_index(State, "vcard_search", "i_vcard_search_sh_lctry", ["server_host", "lctry"]),	×
205	create_index(State, "vcard_search", "i_vcard_search_sh_llocality", ["server_host", "llocality"]),	×
206	create_index(State, "vcard_search", "i_vcard_search_sh_lemail", ["server_host", "lemail"]),	×
207	create_index(State, "vcard_search", "i_vcard_search_sh_lorgname", ["server_host", "lorgname"]),	×
208	create_index(State, "vcard_search", "i_vcard_search_sh_lorgunit", ["server_host", "lorgunit"]),	×
209	drop_sh_default(State, "vcard_search"),	×
210
211	add_sh_column(State, "privacy_default_list"),	×
212	drop_pkey(State, "privacy_default_list"),	×
213	add_pkey(State, "privacy_default_list", ["server_host", "username"]),	×
214	drop_sh_default(State, "privacy_default_list"),	×
215
216	add_sh_column(State, "privacy_list"),	×
217	drop_index(State, "privacy_list", "i_privacy_list_username"),	×
218	drop_index(State, "privacy_list", "i_privacy_list_username_name"),	×
219	create_unique_index(State, "privacy_list", "i_privacy_list_sh_username_name", ["server_host", "username", "name"]),	×
220	drop_sh_default(State, "privacy_list"),	×
221
222	add_sh_column(State, "private_storage"),	×
223	drop_index(State, "private_storage", "i_private_storage_username"),	×
224	drop_index(State, "private_storage", "i_private_storage_username_namespace"),	×
225	add_pkey(State, "private_storage", ["server_host", "username", "namespace"]),	×
226	drop_sh_default(State, "private_storage"),	×
227
228	add_sh_column(State, "roster_version"),	×
229	drop_pkey(State, "roster_version"),	×
230	add_pkey(State, "roster_version", ["server_host", "username"]),	×
231	drop_sh_default(State, "roster_version"),	×
232
233	add_sh_column(State, "muc_room"),	×
234	drop_sh_default(State, "muc_room"),	×
235
236	add_sh_column(State, "muc_registered"),	×
237	drop_sh_default(State, "muc_registered"),	×
238
239	add_sh_column(State, "muc_online_room"),	×
240	drop_sh_default(State, "muc_online_room"),	×
241
242	add_sh_column(State, "muc_online_users"),	×
243	drop_sh_default(State, "muc_online_users"),	×
244
245	add_sh_column(State, "motd"),	×
246	drop_pkey(State, "motd"),	×
247	add_pkey(State, "motd", ["server_host", "username"]),	×
248	drop_sh_default(State, "motd"),	×
249
250	add_sh_column(State, "sm"),	×
251	drop_index(State, "sm", "i_sm_sid"),	×
252	drop_index(State, "sm", "i_sm_username"),	×
253	add_pkey(State, "sm", ["usec", "pid"]),	×
254	create_index(State, "sm", "i_sm_sh_username", ["server_host", "username"]),	×
255	drop_sh_default(State, "sm"),	×
256
257	add_sh_column(State, "push_session"),	×
258	drop_index(State, "push_session", "i_push_usn"),	×
259	drop_index(State, "push_session", "i_push_ut"),	×
260	create_unique_index(State, "push_session", "i_push_session_susn", ["server_host", "username", "service", "node"]),	×
261	create_index(State, "push_session", "i_push_session_sh_username_timestamp", ["server_host", "username", "timestamp"]),	×
262	drop_sh_default(State, "push_session"),	×
263
264	add_sh_column(State, "mix_pam"),	×
265	drop_index(State, "mix_pam", "i_mix_pam"),	×
266	drop_index(State, "mix_pam", "i_mix_pam_u"),	×
267	drop_index(State, "mix_pam", "i_mix_pam_us"),	×
268	create_unique_index(State, "mix_pam", "i_mix_pam", ["username", "server_host", "channel", "service"]),	×
269	drop_sh_default(State, "mix_pam"),	×
270
271	add_sh_column(State, "mqtt_pub"),	×
272	drop_index(State, "mqtt_pub", "i_mqtt_topic"),	×
273	create_unique_index(State, "mqtt_pub", "i_mqtt_topic_server", ["topic", "server_host"]),	×
274	drop_sh_default(State, "mqtt_pub"),	×
275
276	ok.	×
277
278	add_sh_column(#state{dbtype = pgsql} = State, Table) ->
279	sql_query(	×
280	State#state.host,
281	["ALTER TABLE ", Table, " ADD COLUMN server_host text NOT NULL DEFAULT '",
282	(State#state.escape)(State#state.host),
283	"';"]);
284	add_sh_column(#state{dbtype = mssql} = State, Table) ->
285	sql_query(	×
286	State#state.host,
287	["ALTER TABLE [", Table, "] ADD [server_host] varchar (250) NOT NULL CONSTRAINT [server_host_default] DEFAULT '",
288	(State#state.escape)(State#state.host),
289	"';"]);
290	add_sh_column(#state{dbtype = mysql} = State, Table) ->
291	sql_query(	×
292	State#state.host,
293	["ALTER TABLE ", Table, " ADD COLUMN server_host varchar(191) NOT NULL DEFAULT '",
294	(State#state.escape)(State#state.host),
295	"';"]).
296
297	drop_pkey(#state{dbtype = pgsql} = State, Table) ->
298	sql_query(	×
299	State#state.host,
300	["ALTER TABLE ", Table, " DROP CONSTRAINT ", Table, "_pkey;"]);
301	drop_pkey(#state{dbtype = mssql} = State, Table) ->
302	sql_query(	×
303	State#state.host,
304	["ALTER TABLE [", Table, "] DROP CONSTRAINT [", Table, "_PRIMARY];"]);
305	drop_pkey(#state{dbtype = mysql} = State, Table) ->
306	sql_query(	×
307	State#state.host,
308	["ALTER TABLE ", Table, " DROP PRIMARY KEY;"]).
309
310	add_pkey(#state{dbtype = pgsql} = State, Table, Cols) ->
311	SCols = string:join(Cols, ", "),	×
312	sql_query(	×
313	State#state.host,
314	["ALTER TABLE ", Table, " ADD PRIMARY KEY (", SCols, ");"]);
315	add_pkey(#state{dbtype = mssql} = State, Table, Cols) ->
316	SCols = string:join(Cols, "], ["),	×
317	sql_query(	×
318	State#state.host,
319	["ALTER TABLE [", Table, "] ADD CONSTRAINT [", Table, "_PRIMARY] PRIMARY KEY CLUSTERED ([", SCols, "]) ",
320	"WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ",
321	"ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY];"]);
322	add_pkey(#state{dbtype = mysql} = State, Table, Cols) ->
323	Cols2 = [C ++ mysql_keylen(Table, C) \|\| C <- Cols],	×
324	SCols = string:join(Cols2, ", "),	×
325	sql_query(	×
326	State#state.host,
327	["ALTER TABLE ", Table, " ADD PRIMARY KEY (", SCols, ");"]).
328
329	drop_sh_default(#state{dbtype = pgsql} = State, Table) ->
330	sql_query(	×
331	State#state.host,
332	["ALTER TABLE ", Table, " ALTER COLUMN server_host DROP DEFAULT;"]);
333	drop_sh_default(#state{dbtype = mssql} = State, Table) ->
334	sql_query(	×
335	State#state.host,
336	["ALTER TABLE [", Table, "] DROP CONSTRAINT [server_host_default];"]);
337	drop_sh_default(#state{dbtype = mysql} = State, Table) ->
338	sql_query(	×
339	State#state.host,
340	["ALTER TABLE ", Table, " ALTER COLUMN server_host DROP DEFAULT;"]).
341
342	drop_index(#state{dbtype = pgsql} = State, _Table, Index) ->
343	sql_query(	×
344	State#state.host,
345	["DROP INDEX ", Index, ";"]);
346	drop_index(#state{dbtype = mssql} = State, Table, Index) ->
347	sql_query(	×
348	State#state.host,
349	["DROP INDEX [", mssql_old_index_name(Index), "] ON [", Table, "];"]);
350	drop_index(#state{dbtype = mysql} = State, Table, Index) ->
351	sql_query(	×
352	State#state.host,
353	["ALTER TABLE ", Table, " DROP INDEX ", Index, ";"]).
354
355	create_unique_index(#state{dbtype = pgsql} = State, Table, Index, Cols) ->
356	SCols = string:join(Cols, ", "),	×
357	sql_query(	×
358	State#state.host,
359	["CREATE UNIQUE INDEX ", Index, " ON ", Table, " USING btree (",
360	SCols, ");"]);
361	create_unique_index(#state{dbtype = mssql} = State, Table, "i_privacy_list_sh_username_name" = Index, Cols) ->
362	create_index(State, Table, Index, Cols);	×
363	create_unique_index(#state{dbtype = mssql} = State, Table, Index, Cols) ->
364	SCols = string:join(Cols, ", "),	×
365	sql_query(	×
366	State#state.host,
367	["CREATE UNIQUE ", mssql_clustered(Index), "INDEX [", mssql_new_index_name(Index), "] ",
368	"ON [", Table, "] (", SCols, ") ",
369	"WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);"]);
370	create_unique_index(#state{dbtype = mysql} = State, Table, Index, Cols) ->
371	Cols2 = [C ++ mysql_keylen(Index, C) \|\| C <- Cols],	×
372	SCols = string:join(Cols2, ", "),	×
373	sql_query(	×
374	State#state.host,
375	["CREATE UNIQUE INDEX ", Index, " ON ", Table, "(",
376	SCols, ");"]).
377
378	create_index(#state{dbtype = pgsql} = State, Table, Index, Cols) ->
379	SCols = string:join(Cols, ", "),	×
380	sql_query(	×
381	State#state.host,
382	["CREATE INDEX ", Index, " ON ", Table, " USING btree (",
383	SCols, ");"]);
384	create_index(#state{dbtype = mssql} = State, Table, Index, Cols) ->
385	SCols = string:join(Cols, ", "),	×
386	sql_query(	×
387	State#state.host,
388	["CREATE INDEX [", mssql_new_index_name(Index), "] ON [", Table, "] (", SCols, ") ",
389	"WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);"]);
390	create_index(#state{dbtype = mysql} = State, Table, Index, Cols) ->
391	Cols2 = [C ++ mysql_keylen(Index, C) \|\| C <- Cols],	×
392	SCols = string:join(Cols2, ", "),	×
393	sql_query(	×
394	State#state.host,
395	["CREATE INDEX ", Index, " ON ", Table, "(",
396	SCols, ");"]).
397
398	mssql_old_index_name("i_bare_peer") -> "archive_bare_peer";	×
399	mssql_old_index_name("i_peer") -> "archive_peer";	×
400	mssql_old_index_name("i_timestamp") -> "archive_timestamp";	×
401	mssql_old_index_name("i_username") -> "archive_username";	×
402	mssql_old_index_name("i_username_bare_peer") -> "archive_username_bare_peer";	×
403	mssql_old_index_name("i_username_peer") -> "archive_username_peer";	×
404	mssql_old_index_name("i_username_timestamp") -> "archive_username_timestamp";	×
405	mssql_old_index_name("i_push_usn") -> "i_push_usn";	×
406	mssql_old_index_name("i_push_ut") -> "i_push_ut";	×
407	mssql_old_index_name("pk_rosterg_user_jid") -> "rostergroups_username_jid";	×
408	mssql_old_index_name("i_rosteru_jid") -> "rosterusers_jid";	×
409	mssql_old_index_name("i_rosteru_username") -> "rosterusers_username";	×
410	mssql_old_index_name("i_rosteru_user_jid") -> "rosterusers_username_jid";	×
411	mssql_old_index_name("i_despool") -> "spool_username";	×
412	mssql_old_index_name("i_sr_user_jid_grp") -> "sr_user_jid_group";	×
413	mssql_old_index_name(Index) -> string:substr(Index, 3).	×
414
415	mssql_new_index_name("i_rosterg_sh_user_jid") -> "rostergroups_sh_username_jid";	×
416	mssql_new_index_name("i_rosteru_sh_jid") -> "rosterusers_sh_jid";	×
417	mssql_new_index_name("i_rosteru_sh_user_jid") -> "rosterusers_sh_username_jid";	×
418	mssql_new_index_name("i_sr_user_sh_jid_grp") -> "sr_user_sh_jid_group";	×
419	mssql_new_index_name(Index) -> string:substr(Index, 3).	×
420
421	mssql_clustered("i_mix_pam") -> "";	×
422	mssql_clustered("i_push_session_susn") -> "";	×
423	mssql_clustered(_) -> "CLUSTERED ".	×
424
425	mysql_keylen(_, "bare_peer") -> "(191)";	×
426	mysql_keylen(_, "channel") -> "(191)";	×
427	mysql_keylen(_, "domain") -> "(75)";	×
428	mysql_keylen(_, "jid") -> "(75)";	×
429	mysql_keylen(_, "name") -> "(75)";	×
430	mysql_keylen(_, "node") -> "(75)";	×
431	mysql_keylen(_, "peer") -> "(191)";	×
432	mysql_keylen(_, "pid") -> "(75)";	×
433	mysql_keylen(_, "server_host") -> "(191)";	×
434	mysql_keylen(_, "service") -> "(191)";	×
435	mysql_keylen(_, "topic") -> "(191)";	×
436	mysql_keylen("i_privacy_list_sh_username_name", "username") -> "(75)";	×
437	mysql_keylen("i_rosterg_sh_user_jid", "username") -> "(75)";	×
438	mysql_keylen("i_rosteru_sh_user_jid", "username") -> "(75)";	×
439	mysql_keylen(_, "username") -> "(191)";	×
440	mysql_keylen(_, _) -> "".	×
441
442	sql_query(Host, Query) ->
443	io:format("executing \"~ts\" on ~ts~n", [Query, Host]),	×
444	case ejabberd_sql:sql_query(Host, Query) of	×
445	{error, Error} ->
446	io:format("error: ~p~n", [Error]),	×
447	ok;	×
448	_ ->
449	ok	×
450	end.
451
452	mod_options(_) -> [].	×
453
454	mod_doc() ->
455	#{desc =>	×
456	?T("This module can be used to update existing SQL database "
457	"from the default to the new schema. Check the section "
458	"http://../database/#default-and-new-schemas[Default and New Schemas] for details. "
459	"Please note that only MS SQL, MySQL, and PostgreSQL are supported. "
460	"When the module is loaded use _`update_sql`_ API.")}.

processone / ejabberd / 471

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous