diff options
Diffstat (limited to 'src/odbc')
| -rw-r--r-- | src/odbc/Makefile.in | 2 | ||||
| -rw-r--r-- | src/odbc/ejabberd_odbc.erl | 613 | ||||
| -rw-r--r-- | src/odbc/ejabberd_odbc_sup.erl | 90 | ||||
| -rw-r--r-- | src/odbc/mysql.sql | 9 | ||||
| -rw-r--r-- | src/odbc/odbc_queries.erl | 1221 | ||||
| -rw-r--r-- | src/odbc/pg.sql | 9 |
6 files changed, 995 insertions, 949 deletions
diff --git a/src/odbc/Makefile.in b/src/odbc/Makefile.in index d51439fb9..3f4898d3a 100644 --- a/src/odbc/Makefile.in +++ b/src/odbc/Makefile.in @@ -14,7 +14,7 @@ EFLAGS += -pz .. # make debug=true to compile Erlang module with debug informations. ifdef debug - EFLAGS+=+debug_info +export_all + EFLAGS+=+debug_info endif OUTDIR = .. diff --git a/src/odbc/ejabberd_odbc.erl b/src/odbc/ejabberd_odbc.erl index a399a53d1..1cb157c05 100644 --- a/src/odbc/ejabberd_odbc.erl +++ b/src/odbc/ejabberd_odbc.erl @@ -25,6 +25,7 @@ %%%---------------------------------------------------------------------- -module(ejabberd_odbc). + -author('alexey@process-one.net'). -define(GEN_FSM, p1_fsm). @@ -45,68 +46,87 @@ keep_alive/1]). %% gen_fsm callbacks --export([init/1, - handle_event/3, - handle_sync_event/4, - handle_info/3, - terminate/3, - print_state/1, +-export([init/1, handle_event/3, handle_sync_event/4, + handle_info/3, terminate/3, print_state/1, code_change/4]). %% gen_fsm states --export([connecting/2, - connecting/3, - session_established/2, - session_established/3]). +-export([connecting/2, connecting/3, + session_established/2, session_established/3]). -include("ejabberd.hrl"). --record(state, {db_ref, - db_type, - start_interval, - host, - max_pending_requests_len, - pending_requests}). +-record(state, + {db_ref = self() :: pid(), + db_type = odbc :: pgsql | mysql | odbc, + start_interval = 0 :: non_neg_integer(), + host = <<"">> :: binary(), + max_pending_requests_len :: non_neg_integer(), + pending_requests = {0, queue:new()} :: {non_neg_integer(), queue()}}). -define(STATE_KEY, ejabberd_odbc_state). + -define(NESTING_KEY, ejabberd_odbc_nesting_level). + -define(TOP_LEVEL_TXN, 0). + -define(MAX_TRANSACTION_RESTARTS, 10). + -define(PGSQL_PORT, 5432). + -define(MYSQL_PORT, 3306). --define(TRANSACTION_TIMEOUT, 60000). % milliseconds +-define(TRANSACTION_TIMEOUT, 60000). + -define(KEEPALIVE_TIMEOUT, 60000). --define(KEEPALIVE_QUERY, "SELECT 1;"). + +-define(KEEPALIVE_QUERY, <<"SELECT 1;">>). %%-define(DBGFSM, true). -ifdef(DBGFSM). + -define(FSMOPTS, [{debug, [trace]}]). + -else. + -define(FSMOPTS, []). + -endif. %%%---------------------------------------------------------------------- %%% API %%%---------------------------------------------------------------------- start(Host) -> - ?GEN_FSM:start(ejabberd_odbc, [Host], fsm_limit_opts() ++ ?FSMOPTS). + (?GEN_FSM):start(ejabberd_odbc, [Host], + fsm_limit_opts() ++ (?FSMOPTS)). start_link(Host, StartInterval) -> - ?GEN_FSM:start_link(ejabberd_odbc, [Host, StartInterval], - fsm_limit_opts() ++ ?FSMOPTS). + (?GEN_FSM):start_link(ejabberd_odbc, + [Host, StartInterval], + fsm_limit_opts() ++ (?FSMOPTS)). + +-type sql_query() :: [sql_query() | binary()]. +-type sql_query_result() :: {updated, non_neg_integer()} | + {error, binary()} | + {selected, [binary()], + [[binary()]]}. + +-spec sql_query(binary(), sql_query()) -> sql_query_result(). sql_query(Host, Query) -> sql_call(Host, {sql_query, Query}). %% SQL transaction based on a list of queries %% This function automatically -sql_transaction(Host, Queries) when is_list(Queries) -> - F = fun() -> - lists:foreach(fun(Query) -> - sql_query_t(Query) - end, +-spec sql_transaction(binary(), [sql_query()] | fun(() -> any())) -> + {atomic, any()} | + {aborted, any()}. + +sql_transaction(Host, Queries) + when is_list(Queries) -> + F = fun () -> + lists:foreach(fun (Query) -> sql_query_t(Query) end, Queries) end, sql_transaction(Host, F); @@ -115,67 +135,64 @@ sql_transaction(Host, F) when is_function(F) -> sql_call(Host, {sql_transaction, F}). %% SQL bloc, based on a erlang anonymous function (F = fun) -sql_bloc(Host, F) -> - sql_call(Host, {sql_bloc, F}). +sql_bloc(Host, F) -> sql_call(Host, {sql_bloc, F}). sql_call(Host, Msg) -> case get(?STATE_KEY) of - undefined -> - ?GEN_FSM:sync_send_event(ejabberd_odbc_sup:get_random_pid(Host), - {sql_cmd, Msg, now()}, ?TRANSACTION_TIMEOUT); - _State -> - nested_op(Msg) + undefined -> + (?GEN_FSM):sync_send_event(ejabberd_odbc_sup:get_random_pid(Host), + {sql_cmd, Msg, now()}, + ?TRANSACTION_TIMEOUT); + _State -> nested_op(Msg) end. -% perform a harmless query on all opened connexions to avoid connexion close. keep_alive(PID) -> - ?GEN_FSM:sync_send_event(PID, {sql_cmd, {sql_query, ?KEEPALIVE_QUERY}, now()}, - ?KEEPALIVE_TIMEOUT). + (?GEN_FSM):sync_send_event(PID, + {sql_cmd, {sql_query, ?KEEPALIVE_QUERY}, now()}, + ?KEEPALIVE_TIMEOUT). + +-spec sql_query_t(sql_query()) -> sql_query_result(). %% This function is intended to be used from inside an sql_transaction: sql_query_t(Query) -> QRes = sql_query_internal(Query), case QRes of - {error, Reason} -> - throw({aborted, Reason}); - Rs when is_list(Rs) -> - case lists:keysearch(error, 1, Rs) of - {value, {error, Reason}} -> - throw({aborted, Reason}); - _ -> - QRes - end; - _ -> - QRes + {error, Reason} -> throw({aborted, Reason}); + Rs when is_list(Rs) -> + case lists:keysearch(error, 1, Rs) of + {value, {error, Reason}} -> throw({aborted, Reason}); + _ -> QRes + end; + _ -> QRes end. %% Escape character that will confuse an SQL engine -escape(S) when is_list(S) -> - [odbc_queries:escape(C) || C <- S]; -escape(S) when is_binary(S) -> - escape(binary_to_list(S)). +escape(S) -> + << <<(odbc_queries:escape(Char))/binary>> || <<Char>> <= S >>. %% Escape character that will confuse an SQL engine %% Percent and underscore only need to be escaped for pattern matching like %% statement -escape_like(S) when is_list(S) -> - [escape_like(C) || C <- S]; -escape_like($%) -> "\\%"; -escape_like($_) -> "\\_"; -escape_like(C) -> odbc_queries:escape(C). - -to_bool("t") -> true; -to_bool("true") -> true; -to_bool("1") -> true; +escape_like(S) when is_binary(S) -> + << <<(escape_like(C))/binary>> || <<C>> <= S >>; +escape_like($%) -> <<"\\%">>; +escape_like($_) -> <<"\\_">>; +escape_like(C) when is_integer(C), C >= 0, C =< 255 -> odbc_queries:escape(C). + +to_bool(<<"t">>) -> true; +to_bool(<<"true">>) -> true; +to_bool(<<"1">>) -> true; to_bool(true) -> true; to_bool(1) -> true; to_bool(_) -> false. encode_term(Term) -> - escape(lists:flatten(io_lib:print(Term))). + escape(list_to_binary( + erl_prettypr:format(erl_syntax:abstract(Term)))). -decode_term(Str) -> - {ok, Tokens, _} = erl_scan:string(Str ++ "."), +decode_term(Bin) -> + Str = binary_to_list(<<Bin/binary, ".">>), + {ok, Tokens, _} = erl_scan:string(Str), {ok, Term} = erl_parse:parse_term(Tokens), Term. @@ -183,76 +200,83 @@ decode_term(Str) -> %%% Callback functions from gen_fsm %%%---------------------------------------------------------------------- init([Host, StartInterval]) -> - case ejabberd_config:get_local_option({odbc_keepalive_interval, Host}) of - KeepaliveInterval when is_integer(KeepaliveInterval) -> - timer:apply_interval(KeepaliveInterval*1000, ?MODULE, - keep_alive, [self()]); - undefined -> - ok; - _Other -> - ?ERROR_MSG("Wrong odbc_keepalive_interval definition '~p'" - " for host ~p.~n", [_Other, Host]) + case ejabberd_config:get_local_option( + {odbc_keepalive_interval, Host}, + fun(I) when is_integer(I), I>0 -> I end) of + undefined -> + ok; + KeepaliveInterval -> + timer:apply_interval(KeepaliveInterval * 1000, ?MODULE, + keep_alive, [self()]) end, [DBType | _] = db_opts(Host), - ?GEN_FSM:send_event(self(), connect), + (?GEN_FSM):send_event(self(), connect), ejabberd_odbc_sup:add_pid(Host, self()), - {ok, connecting, #state{db_type = DBType, - host = Host, - max_pending_requests_len = max_fsm_queue(), - pending_requests = {0, queue:new()}, - start_interval = StartInterval}}. + {ok, connecting, + #state{db_type = DBType, host = Host, + max_pending_requests_len = max_fsm_queue(), + pending_requests = {0, queue:new()}, + start_interval = StartInterval}}. connecting(connect, #state{host = Host} = State) -> ConnectRes = case db_opts(Host) of - [mysql | Args] -> - apply(fun mysql_connect/5, Args); - [pgsql | Args] -> - apply(fun pgsql_connect/5, Args); - [odbc | Args] -> - apply(fun odbc_connect/1, Args) + [mysql | Args] -> apply(fun mysql_connect/5, Args); + [pgsql | Args] -> apply(fun pgsql_connect/5, Args); + [odbc | Args] -> apply(fun odbc_connect/1, Args) end, {_, PendingRequests} = State#state.pending_requests, case ConnectRes of - {ok, Ref} -> - erlang:monitor(process, Ref), - lists:foreach( - fun(Req) -> - ?GEN_FSM:send_event(self(), Req) - end, queue:to_list(PendingRequests)), - {next_state, session_established, - State#state{db_ref = Ref, - pending_requests = {0, queue:new()}}}; - {error, Reason} -> - ?INFO_MSG("~p connection failed:~n" - "** Reason: ~p~n" - "** Retry after: ~p seconds", - [State#state.db_type, Reason, - State#state.start_interval div 1000]), - ?GEN_FSM:send_event_after(State#state.start_interval, + {ok, Ref} -> + erlang:monitor(process, Ref), + lists:foreach(fun (Req) -> + (?GEN_FSM):send_event(self(), Req) + end, + queue:to_list(PendingRequests)), + {next_state, session_established, + State#state{db_ref = Ref, + pending_requests = {0, queue:new()}}}; + {error, Reason} -> + ?INFO_MSG("~p connection failed:~n** Reason: ~p~n** " + "Retry after: ~p seconds", + [State#state.db_type, Reason, + State#state.start_interval div 1000]), + (?GEN_FSM):send_event_after(State#state.start_interval, connect), - {next_state, connecting, State} + {next_state, connecting, State} end; connecting(Event, State) -> - ?WARNING_MSG("unexpected event in 'connecting': ~p", [Event]), + ?WARNING_MSG("unexpected event in 'connecting': ~p", + [Event]), {next_state, connecting, State}. -connecting({sql_cmd, {sql_query, ?KEEPALIVE_QUERY}, _Timestamp}, From, State) -> - ?GEN_FSM:reply(From, {error, "SQL connection failed"}), +connecting({sql_cmd, {sql_query, ?KEEPALIVE_QUERY}, + _Timestamp}, + From, State) -> + (?GEN_FSM):reply(From, + {error, <<"SQL connection failed">>}), {next_state, connecting, State}; -connecting({sql_cmd, Command, Timestamp} = Req, From, State) -> - ?DEBUG("queuing pending request while connecting:~n\t~p", [Req]), +connecting({sql_cmd, Command, Timestamp} = Req, From, + State) -> + ?DEBUG("queuing pending request while connecting:~n\t~p", + [Req]), {Len, PendingRequests} = State#state.pending_requests, - NewPendingRequests = - if Len < State#state.max_pending_requests_len -> - {Len + 1, queue:in({sql_cmd, Command, From, Timestamp}, PendingRequests)}; - true -> - lists:foreach( - fun({sql_cmd, _, To, _Timestamp}) -> - ?GEN_FSM:reply( - To, {error, "SQL connection failed"}) - end, queue:to_list(PendingRequests)), - {1, queue:from_list([{sql_cmd, Command, From, Timestamp}])} - end, + NewPendingRequests = if Len < + State#state.max_pending_requests_len -> + {Len + 1, + queue:in({sql_cmd, Command, From, Timestamp}, + PendingRequests)}; + true -> + lists:foreach(fun ({sql_cmd, _, To, + _Timestamp}) -> + (?GEN_FSM):reply(To, + {error, + <<"SQL connection failed">>}) + end, + queue:to_list(PendingRequests)), + {1, + queue:from_list([{sql_cmd, Command, From, + Timestamp}])} + end, {next_state, connecting, State#state{pending_requests = NewPendingRequests}}; connecting(Request, {Who, _Ref}, State) -> @@ -260,17 +284,21 @@ connecting(Request, {Who, _Ref}, State) -> [Request, Who]), {reply, {error, badarg}, connecting, State}. -session_established({sql_cmd, Command, Timestamp}, From, State) -> +session_established({sql_cmd, Command, Timestamp}, From, + State) -> run_sql_cmd(Command, From, State, Timestamp); session_established(Request, {Who, _Ref}, State) -> - ?WARNING_MSG("unexpected call ~p from ~p in 'session_established'", + ?WARNING_MSG("unexpected call ~p from ~p in 'session_establ" + "ished'", [Request, Who]), {reply, {error, badarg}, session_established, State}. -session_established({sql_cmd, Command, From, Timestamp}, State) -> +session_established({sql_cmd, Command, From, Timestamp}, + State) -> run_sql_cmd(Command, From, State, Timestamp); session_established(Event, State) -> - ?WARNING_MSG("unexpected event in 'session_established': ~p", [Event]), + ?WARNING_MSG("unexpected event in 'session_established': ~p", + [Event]), {next_state, session_established, State}. handle_event(_Event, StateName, State) -> @@ -284,22 +312,20 @@ code_change(_OldVsn, StateName, State, _Extra) -> %% We receive the down signal when we loose the MySQL connection (we are %% monitoring the connection) -handle_info({'DOWN', _MonitorRef, process, _Pid, _Info}, _StateName, State) -> - ?GEN_FSM:send_event(self(), connect), +handle_info({'DOWN', _MonitorRef, process, _Pid, _Info}, + _StateName, State) -> + (?GEN_FSM):send_event(self(), connect), {next_state, connecting, State}; handle_info(Info, StateName, State) -> - ?WARNING_MSG("unexpected info in ~p: ~p", [StateName, Info]), + ?WARNING_MSG("unexpected info in ~p: ~p", + [StateName, Info]), {next_state, StateName, State}. terminate(_Reason, _StateName, State) -> ejabberd_odbc_sup:remove_pid(State#state.host, self()), case State#state.db_type of - mysql -> - %% old versions of mysql driver don't have the stop function - %% so the catch - catch mysql_conn:stop(State#state.db_ref); - _ -> - ok + mysql -> catch mysql_conn:stop(State#state.db_ref); + _ -> ok end, ok. @@ -308,23 +334,23 @@ terminate(_Reason, _StateName, State) -> %% Purpose: Prepare the state to be printed on error log %% Returns: State to print %%---------------------------------------------------------------------- -print_state(State) -> - State. +print_state(State) -> State. + %%%---------------------------------------------------------------------- %%% Internal functions %%%---------------------------------------------------------------------- run_sql_cmd(Command, From, State, Timestamp) -> case timer:now_diff(now(), Timestamp) div 1000 of - Age when Age < ?TRANSACTION_TIMEOUT -> - put(?NESTING_KEY, ?TOP_LEVEL_TXN), - put(?STATE_KEY, State), - abort_on_driver_error(outer_op(Command), From); - Age -> - ?ERROR_MSG("Database was not available or too slow," - " discarding ~p milliseconds old request~n~p~n", - [Age, Command]), - {next_state, session_established, State} + Age when Age < (?TRANSACTION_TIMEOUT) -> + put(?NESTING_KEY, ?TOP_LEVEL_TXN), + put(?STATE_KEY, State), + abort_on_driver_error(outer_op(Command), From); + Age -> + ?ERROR_MSG("Database was not available or too slow, " + "discarding ~p milliseconds old request~n~p~n", + [Age, Command]), + {next_state, session_established, State} end. %% Only called by handle_call, only handles top level operations. @@ -332,143 +358,125 @@ run_sql_cmd(Command, From, State, Timestamp) -> outer_op({sql_query, Query}) -> sql_query_internal(Query); outer_op({sql_transaction, F}) -> - outer_transaction(F, ?MAX_TRANSACTION_RESTARTS, ""); -outer_op({sql_bloc, F}) -> - execute_bloc(F). + outer_transaction(F, ?MAX_TRANSACTION_RESTARTS, <<"">>); +outer_op({sql_bloc, F}) -> execute_bloc(F). %% Called via sql_query/transaction/bloc from client code when inside a %% nested operation nested_op({sql_query, Query}) -> - %% XXX - use sql_query_t here insted? Most likely would break - %% callers who expect {error, _} tuples (sql_query_t turns - %% these into throws) sql_query_internal(Query); nested_op({sql_transaction, F}) -> NestingLevel = get(?NESTING_KEY), - if NestingLevel =:= ?TOP_LEVEL_TXN -> - %% First transaction inside a (series of) sql_blocs - outer_transaction(F, ?MAX_TRANSACTION_RESTARTS, ""); - true -> - %% Transaction inside a transaction - inner_transaction(F) + if NestingLevel =:= (?TOP_LEVEL_TXN) -> + outer_transaction(F, ?MAX_TRANSACTION_RESTARTS, <<"">>); + true -> inner_transaction(F) end; -nested_op({sql_bloc, F}) -> - execute_bloc(F). +nested_op({sql_bloc, F}) -> execute_bloc(F). %% Never retry nested transactions - only outer transactions inner_transaction(F) -> PreviousNestingLevel = get(?NESTING_KEY), case get(?NESTING_KEY) of - ?TOP_LEVEL_TXN -> - {backtrace, T} = process_info(self(), backtrace), - ?ERROR_MSG("inner transaction called at outer txn level. Trace: ~s", - [T]), - erlang:exit(implementation_faulty); - _N -> ok + ?TOP_LEVEL_TXN -> + {backtrace, T} = process_info(self(), backtrace), + ?ERROR_MSG("inner transaction called at outer txn " + "level. Trace: ~s", + [T]), + erlang:exit(implementation_faulty); + _N -> ok end, put(?NESTING_KEY, PreviousNestingLevel + 1), Result = (catch F()), put(?NESTING_KEY, PreviousNestingLevel), case Result of - {aborted, Reason} -> - {aborted, Reason}; - {'EXIT', Reason} -> - {'EXIT', Reason}; - {atomic, Res} -> - {atomic, Res}; - Res -> - {atomic, Res} + {aborted, Reason} -> {aborted, Reason}; + {'EXIT', Reason} -> {'EXIT', Reason}; + {atomic, Res} -> {atomic, Res}; + Res -> {atomic, Res} end. outer_transaction(F, NRestarts, _Reason) -> PreviousNestingLevel = get(?NESTING_KEY), case get(?NESTING_KEY) of - ?TOP_LEVEL_TXN -> - ok; - _N -> - {backtrace, T} = process_info(self(), backtrace), - ?ERROR_MSG("outer transaction called at inner txn level. Trace: ~s", - [T]), - erlang:exit(implementation_faulty) + ?TOP_LEVEL_TXN -> ok; + _N -> + {backtrace, T} = process_info(self(), backtrace), + ?ERROR_MSG("outer transaction called at inner txn " + "level. Trace: ~s", + [T]), + erlang:exit(implementation_faulty) end, - sql_query_internal("begin;"), + sql_query_internal(<<"begin;">>), put(?NESTING_KEY, PreviousNestingLevel + 1), Result = (catch F()), put(?NESTING_KEY, PreviousNestingLevel), case Result of - {aborted, Reason} when NRestarts > 0 -> - %% Retry outer transaction upto NRestarts times. - sql_query_internal("rollback;"), - outer_transaction(F, NRestarts - 1, Reason); - {aborted, Reason} when NRestarts =:= 0 -> - %% Too many retries of outer transaction. - ?ERROR_MSG("SQL transaction restarts exceeded~n" - "** Restarts: ~p~n" - "** Last abort reason: ~p~n" - "** Stacktrace: ~p~n" - "** When State == ~p", - [?MAX_TRANSACTION_RESTARTS, Reason, - erlang:get_stacktrace(), get(?STATE_KEY)]), - sql_query_internal("rollback;"), - {aborted, Reason}; - {'EXIT', Reason} -> - %% Abort sql transaction on EXIT from outer txn only. - sql_query_internal("rollback;"), - {aborted, Reason}; - Res -> - %% Commit successful outer txn - sql_query_internal("commit;"), - {atomic, Res} + {aborted, Reason} when NRestarts > 0 -> + sql_query_internal(<<"rollback;">>), + outer_transaction(F, NRestarts - 1, Reason); + {aborted, Reason} when NRestarts =:= 0 -> + ?ERROR_MSG("SQL transaction restarts exceeded~n** " + "Restarts: ~p~n** Last abort reason: " + "~p~n** Stacktrace: ~p~n** When State " + "== ~p", + [?MAX_TRANSACTION_RESTARTS, Reason, + erlang:get_stacktrace(), get(?STATE_KEY)]), + sql_query_internal(<<"rollback;">>), + {aborted, Reason}; + {'EXIT', Reason} -> + sql_query_internal(<<"rollback;">>), {aborted, Reason}; + Res -> sql_query_internal(<<"commit;">>), {atomic, Res} end. execute_bloc(F) -> - %% We don't alter ?NESTING_KEY here as only SQL transactions alter - %% txn nesting case catch F() of - {aborted, Reason} -> - {aborted, Reason}; - {'EXIT', Reason} -> - {aborted, Reason}; - Res -> - {atomic, Res} + {aborted, Reason} -> {aborted, Reason}; + {'EXIT', Reason} -> {aborted, Reason}; + Res -> {atomic, Res} end. sql_query_internal(Query) -> State = get(?STATE_KEY), Res = case State#state.db_type of - odbc -> - odbc:sql_query(State#state.db_ref, Query); - pgsql -> - pgsql_to_odbc(pgsql:squery(State#state.db_ref, Query)); - mysql -> - ?DEBUG("MySQL, Send query~n~p~n", [Query]), - R = mysql_to_odbc(mysql_conn:fetch(State#state.db_ref, - Query, self())), - %% ?INFO_MSG("MySQL, Received result~n~p~n", [R]), - R - end, + odbc -> + to_odbc(odbc:sql_query(State#state.db_ref, Query, + (?TRANSACTION_TIMEOUT) - 1000)); + pgsql -> + pgsql_to_odbc(pgsql:squery(State#state.db_ref, Query)); + mysql -> + ?DEBUG("MySQL, Send query~n~p~n", [Query]), + %%squery to be able to specify result_type = binary + %%[Query] because mysql_conn expect query to be a list (elements can be binaries, or iolist) + %% but doesn't accept just a binary + R = mysql_to_odbc(mysql_conn:squery(State#state.db_ref, + [Query], self(), + [{timeout, (?TRANSACTION_TIMEOUT) - 1000}, + {result_type, binary}])), + %% ?INFO_MSG("MySQL, Received result~n~p~n", [R]), + R + end, case Res of - {error, "No SQL-driver information available."} -> - % workaround for odbc bug - {updated, 0}; - _Else -> Res + {error, <<"No SQL-driver information available.">>} -> + {updated, 0}; + _Else -> Res end. %% Generate the OTP callback return tuple depending on the driver result. -abort_on_driver_error({error, "query timed out"} = Reply, From) -> - %% mysql driver error - ?GEN_FSM:reply(From, Reply), +abort_on_driver_error({error, <<"query timed out">>} = + Reply, + From) -> + (?GEN_FSM):reply(From, Reply), {stop, timeout, get(?STATE_KEY)}; -abort_on_driver_error({error, "Failed sending data on socket" ++ _} = Reply, +abort_on_driver_error({error, + <<"Failed sending data on socket", _/binary>>} = + Reply, From) -> - %% mysql driver error - ?GEN_FSM:reply(From, Reply), + (?GEN_FSM):reply(From, Reply), {stop, closed, get(?STATE_KEY)}; abort_on_driver_error(Reply, From) -> - ?GEN_FSM:reply(From, Reply), + (?GEN_FSM):reply(From, Reply), {next_state, session_established, get(?STATE_KEY)}. - %% == pure ODBC code %% part of init/1 @@ -482,44 +490,54 @@ odbc_connect(SQLServer) -> %% part of init/1 %% Open a database connection to PostgreSQL pgsql_connect(Server, Port, DB, Username, Password) -> - pgsql:connect(Server, DB, Username, Password, Port). + case pgsql:connect([{host, Server}, + {database, DB}, + {user, Username}, + {password, Password}, + {port, Port}, + {as_binary, true}]) of + {ok, Ref} -> + pgsql:squery(Ref, [<<"alter database ">>, DB, <<" set ">>, + <<"standard_conforming_strings='off';">>]), + {ok, Ref}; + Err -> + Err + end. %% Convert PostgreSQL query result to Erlang ODBC result formalism pgsql_to_odbc({ok, PGSQLResult}) -> case PGSQLResult of - [Item] -> - pgsql_item_to_odbc(Item); - Items -> - [pgsql_item_to_odbc(Item) || Item <- Items] + [Item] -> pgsql_item_to_odbc(Item); + Items -> [pgsql_item_to_odbc(Item) || Item <- Items] end. -pgsql_item_to_odbc({"SELECT" ++ _, Rows, Recs}) -> - {selected, - [element(1, Row) || Row <- Rows], - [list_to_tuple(Rec) || Rec <- Recs]}; -pgsql_item_to_odbc("INSERT " ++ OIDN) -> - [_OID, N] = string:tokens(OIDN, " "), - {updated, list_to_integer(N)}; -pgsql_item_to_odbc("DELETE " ++ N) -> - {updated, list_to_integer(N)}; -pgsql_item_to_odbc("UPDATE " ++ N) -> - {updated, list_to_integer(N)}; -pgsql_item_to_odbc({error, Error}) -> - {error, Error}; -pgsql_item_to_odbc(_) -> - {updated,undefined}. +pgsql_item_to_odbc({<<"SELECT", _/binary>>, Rows, + Recs}) -> + {selected, [element(1, Row) || Row <- Rows], Recs}; +pgsql_item_to_odbc(<<"INSERT ", OIDN/binary>>) -> + [_OID, N] = str:tokens(OIDN, <<" ">>), + {updated, jlib:binary_to_integer(N)}; +pgsql_item_to_odbc(<<"DELETE ", N/binary>>) -> + {updated, jlib:binary_to_integer(N)}; +pgsql_item_to_odbc(<<"UPDATE ", N/binary>>) -> + {updated, jlib:binary_to_integer(N)}; +pgsql_item_to_odbc({error, Error}) -> {error, Error}; +pgsql_item_to_odbc(_) -> {updated, undefined}. %% == Native MySQL code %% part of init/1 %% Open a database connection to MySQL mysql_connect(Server, Port, DB, Username, Password) -> - case mysql_conn:start(Server, Port, Username, Password, DB, fun log/3) of - {ok, Ref} -> - mysql_conn:fetch(Ref, ["set names 'utf8';"], self()), - {ok, Ref}; - Err -> - Err + case mysql_conn:start(binary_to_list(Server), Port, + binary_to_list(Username), binary_to_list(Password), + binary_to_list(DB), fun log/3) + of + {ok, Ref} -> + mysql_conn:fetch(Ref, [<<"set names 'utf8';">>], + self()), + {ok, Ref}; + Err -> Err end. %% Convert MySQL query result to Erlang ODBC result formalism @@ -528,58 +546,67 @@ mysql_to_odbc({updated, MySQLRes}) -> mysql_to_odbc({data, MySQLRes}) -> mysql_item_to_odbc(mysql:get_result_field_info(MySQLRes), mysql:get_result_rows(MySQLRes)); -mysql_to_odbc({error, MySQLRes}) when is_list(MySQLRes) -> +mysql_to_odbc({error, MySQLRes}) + when is_binary(MySQLRes) -> {error, MySQLRes}; mysql_to_odbc({error, MySQLRes}) -> {error, mysql:get_result_reason(MySQLRes)}. %% When tabular data is returned, convert it to the ODBC formalism mysql_item_to_odbc(Columns, Recs) -> - %% For now, there is a bug and we do not get the correct value from MySQL - %% module: - {selected, - [element(2, Column) || Column <- Columns], - [list_to_tuple(Rec) || Rec <- Recs]}. + {selected, [element(2, Column) || Column <- Columns], Recs}. + +to_odbc({selected, Columns, Recs}) -> + {selected, Columns, [tuple_to_list(Rec) || Rec <- Recs]}; +to_odbc(Res) -> + Res. -% log function used by MySQL driver log(Level, Format, Args) -> case Level of - debug -> - ?DEBUG(Format, Args); - normal -> - ?INFO_MSG(Format, Args); - error -> - ?ERROR_MSG(Format, Args) + debug -> ?DEBUG(Format, Args); + normal -> ?INFO_MSG(Format, Args); + error -> ?ERROR_MSG(Format, Args) end. db_opts(Host) -> - case ejabberd_config:get_local_option({odbc_server, Host}) of - %% Default pgsql port - {pgsql, Server, DB, User, Pass} -> - [pgsql, Server, ?PGSQL_PORT, DB, User, Pass]; - {pgsql, Server, Port, DB, User, Pass} when is_integer(Port) -> - [pgsql, Server, Port, DB, User, Pass]; - %% Default mysql port - {mysql, Server, DB, User, Pass} -> - [mysql, Server, ?MYSQL_PORT, DB, User, Pass]; - {mysql, Server, Port, DB, User, Pass} when is_integer(Port) -> - [mysql, Server, Port, DB, User, Pass]; - SQLServer when is_list(SQLServer) -> - [odbc, SQLServer] + case ejabberd_config:get_local_option( + {odbc_server, Host}, + fun({Type, Server, DB, User, Pass}) -> + {Type, + iolist_to_binary(Server), + case Type of + mysql -> ?MYSQL_PORT; + pgsql -> ?PGSQL_PORT + end, + iolist_to_binary(DB), + iolist_to_binary(User), + iolist_to_binary(Pass)}; + ({Type, Server, Port, DB, User, Pass}) + when ((Type == mysql) or (Type == pgsql)) + and (is_integer(Port) and ((Port > 0) + and (Port < 65536))) -> + {Type, + iolist_to_binary(Server), + Port, + iolist_to_binary(DB), + iolist_to_binary(User), + iolist_to_binary(Pass)}; + (S) -> + iolist_to_binary(S) + end, <<"localhost">>) of + {Type, Server, Port, DB, User, Pass} -> + [Type, Server, Port, DB, User, Pass]; + SQLServer -> + [odbc, SQLServer] end. max_fsm_queue() -> - case ejabberd_config:get_local_option(max_fsm_queue) of - N when is_integer(N), N>0 -> - N; - _ -> - undefined - end. + ejabberd_config:get_local_option( + max_fsm_queue, + fun(N) when is_integer(N), N > 0 -> N end). fsm_limit_opts() -> case max_fsm_queue() of - N when is_integer(N) -> - [{max_queue, N}]; - _ -> - [] + N when is_integer(N) -> [{max_queue, N}]; + _ -> [] end. diff --git a/src/odbc/ejabberd_odbc_sup.erl b/src/odbc/ejabberd_odbc_sup.erl index 442b0f906..0c748d147 100644 --- a/src/odbc/ejabberd_odbc_sup.erl +++ b/src/odbc/ejabberd_odbc_sup.erl @@ -25,79 +25,53 @@ %%%---------------------------------------------------------------------- -module(ejabberd_odbc_sup). + -author('alexey@process-one.net'). %% API --export([start_link/1, - init/1, - add_pid/2, - remove_pid/2, - get_pids/1, - get_random_pid/1 - ]). +-export([start_link/1, init/1, add_pid/2, remove_pid/2, + get_pids/1, get_random_pid/1]). -include("ejabberd.hrl"). -define(DEFAULT_POOL_SIZE, 10). --define(DEFAULT_ODBC_START_INTERVAL, 30). % 30 seconds -% time to wait for the supervisor to start its child before returning -% a timeout error to the request --define(CONNECT_TIMEOUT, 500). % milliseconds +-define(DEFAULT_ODBC_START_INTERVAL, 30). +-define(CONNECT_TIMEOUT, 500). -record(sql_pool, {host, pid}). start_link(Host) -> mnesia:create_table(sql_pool, - [{ram_copies, [node()]}, - {type, bag}, + [{ram_copies, [node()]}, {type, bag}, {local_content, true}, {attributes, record_info(fields, sql_pool)}]), mnesia:add_table_copy(sql_pool, node(), ram_copies), - F = fun() -> - mnesia:delete({sql_pool, Host}) - end, + F = fun () -> mnesia:delete({sql_pool, Host}) end, mnesia:ets(F), - supervisor:start_link({local, gen_mod:get_module_proc(Host, ?MODULE)}, + supervisor:start_link({local, + gen_mod:get_module_proc(Host, ?MODULE)}, ?MODULE, [Host]). init([Host]) -> - PoolSize = case ejabberd_config:get_local_option({odbc_pool_size, Host}) of - I when is_integer(I) -> - I; - undefined -> - ?DEFAULT_POOL_SIZE; - Other -> - ?ERROR_MSG("Wrong odbc_pool_size definition '~p' " - "for host ~p, default to ~p~n", - [Other, Host, ?DEFAULT_POOL_SIZE]), - ?DEFAULT_POOL_SIZE - end, - StartInterval = case ejabberd_config:get_local_option({odbc_start_interval, - Host}) of - Interval when is_integer(Interval) -> - Interval; - undefined -> - ?DEFAULT_ODBC_START_INTERVAL; - _Other2 -> - ?ERROR_MSG("Wrong odbc_start_interval " - "definition '~p' for host ~p, " - "defaulting to ~p~n", - [_Other2, Host, - ?DEFAULT_ODBC_START_INTERVAL]), - ?DEFAULT_ODBC_START_INTERVAL - end, - {ok, {{one_for_one, PoolSize*10, 1}, - lists:map( - fun(I) -> - {I, - {ejabberd_odbc, start_link, [Host, StartInterval*1000]}, - transient, - 2000, - worker, - [?MODULE]} - end, lists:seq(1, PoolSize))}}. + PoolSize = ejabberd_config:get_local_option( + {odbc_pool_size, Host}, + fun(I) when is_integer(I), I>0 -> I end, + ?DEFAULT_POOL_SIZE), + StartInterval = ejabberd_config:get_local_option( + {odbc_start_interval, Host}, + fun(I) when is_integer(I), I>0 -> I end, + ?DEFAULT_ODBC_START_INTERVAL), + {ok, + {{one_for_one, PoolSize * 10, 1}, + lists:map(fun (I) -> + {I, + {ejabberd_odbc, start_link, + [Host, StartInterval * 1000]}, + transient, 2000, worker, [?MODULE]} + end, + lists:seq(1, PoolSize))}}. get_pids(Host) -> Rs = mnesia:dirty_read(sql_pool, Host), @@ -108,17 +82,13 @@ get_random_pid(Host) -> lists:nth(erlang:phash(now(), length(Pids)), Pids). add_pid(Host, Pid) -> - F = fun() -> - mnesia:write( - #sql_pool{host = Host, - pid = Pid}) + F = fun () -> + mnesia:write(#sql_pool{host = Host, pid = Pid}) end, mnesia:ets(F). remove_pid(Host, Pid) -> - F = fun() -> - mnesia:delete_object( - #sql_pool{host = Host, - pid = Pid}) + F = fun () -> + mnesia:delete_object(#sql_pool{host = Host, pid = Pid}) end, mnesia:ets(F). diff --git a/src/odbc/mysql.sql b/src/odbc/mysql.sql index 17c7d9b76..976230117 100644 --- a/src/odbc/mysql.sql +++ b/src/odbc/mysql.sql @@ -273,3 +273,12 @@ CREATE TABLE motd ( xml text, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) CHARACTER SET utf8; + +CREATE TABLE caps_features ( + node varchar(250) NOT NULL, + subnode varchar(250) NOT NULL, + feature text, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) CHARACTER SET utf8; + +CREATE INDEX i_caps_features_node_subnode ON caps_features(node(75), subnode(75)); diff --git a/src/odbc/odbc_queries.erl b/src/odbc/odbc_queries.erl index bd4812609..66da7906f 100644 --- a/src/odbc/odbc_queries.erl +++ b/src/odbc/odbc_queries.erl @@ -25,52 +25,28 @@ %%%---------------------------------------------------------------------- -module(odbc_queries). + -author("mremond@process-one.net"). --export([get_db_type/0, - update_t/4, - sql_transaction/2, - get_last/2, - set_last_t/4, - del_last/2, - get_password/2, - set_password_t/3, - add_user/3, - del_user/2, - del_user_return_password/3, - list_users/1, - list_users/2, - users_number/1, - users_number/2, - add_spool_sql/2, - add_spool/2, - get_and_del_spool_msg_t/2, - del_spool_msg/2, - get_roster/2, - get_roster_jid_groups/2, - get_roster_groups/3, - del_user_roster_t/2, - get_roster_by_jid/3, - get_rostergroup_by_jid/3, - del_roster/3, - del_roster_sql/2, - update_roster/5, - update_roster_sql/4, - roster_subscribe/4, - get_subscription/3, - set_private_data/4, - set_private_data_sql/3, - get_private_data/3, - del_user_private_storage/2, - get_default_privacy_list/2, - get_default_privacy_list_t/1, - get_privacy_list_names/2, - get_privacy_list_names_t/1, - get_privacy_list_id/3, - get_privacy_list_id_t/2, - get_privacy_list_data/3, +-export([get_db_type/0, update_t/4, sql_transaction/2, + get_last/2, set_last_t/4, del_last/2, get_password/2, + set_password_t/3, add_user/3, del_user/2, + del_user_return_password/3, list_users/1, list_users/2, + users_number/1, users_number/2, add_spool_sql/2, + add_spool/2, get_and_del_spool_msg_t/2, del_spool_msg/2, + get_roster/2, get_roster_jid_groups/2, + get_roster_groups/3, del_user_roster_t/2, + get_roster_by_jid/3, get_rostergroup_by_jid/3, + del_roster/3, del_roster_sql/2, update_roster/5, + update_roster_sql/4, roster_subscribe/4, + get_subscription/3, set_private_data/4, + set_private_data_sql/3, get_private_data/3, get_private_data/2, + del_user_private_storage/2, get_default_privacy_list/2, + get_default_privacy_list_t/1, get_privacy_list_names/2, + get_privacy_list_names_t/1, get_privacy_list_id/3, + get_privacy_list_id_t/2, get_privacy_list_data/3, get_privacy_list_data_by_id/2, - get_privacy_list_data_by_id_t/1, + get_privacy_list_data_by_id_t/1, set_default_privacy_list/2, unset_default_privacy_list/2, remove_privacy_list/2, @@ -88,8 +64,11 @@ %-define(generic, true). %-define(mssql, true). -ifndef(mssql). + -undef(generic). + -define(generic, true). + -endif. -include("ejabberd.hrl"). @@ -97,49 +76,49 @@ %% Almost a copy of string:join/2. %% We use this version because string:join/2 is relatively %% new function (introduced in R12B-0). -join([], _Sep) -> - []; -join([H|T], Sep) -> - [H, [[Sep, X] || X <- T]]. +join([], _Sep) -> []; +join([H | T], Sep) -> [H, [[Sep, X] || X <- T]]. %% ----------------- %% Generic queries -ifdef(generic). -get_db_type() -> - generic. +get_db_type() -> generic. %% Safe atomic update. update_t(Table, Fields, Vals, Where) -> - UPairs = lists:zipwith(fun(A, B) -> A ++ "='" ++ B ++ "'" end, + UPairs = lists:zipwith(fun (A, B) -> + <<A/binary, "='", B/binary, "'">> + end, Fields, Vals), - case ejabberd_odbc:sql_query_t( - ["update ", Table, " set ", - join(UPairs, ", "), - " where ", Where, ";"]) of - {updated, 1} -> - ok; - _ -> - ejabberd_odbc:sql_query_t( - ["insert into ", Table, "(", join(Fields, ", "), - ") values ('", join(Vals, "', '"), "');"]) + case ejabberd_odbc:sql_query_t([<<"update ">>, Table, + <<" set ">>, join(UPairs, <<", ">>), + <<" where ">>, Where, <<";">>]) + of + {updated, 1} -> ok; + _ -> + ejabberd_odbc:sql_query_t([<<"insert into ">>, Table, + <<"(">>, join(Fields, <<", ">>), + <<") values ('">>, join(Vals, <<"', '">>), + <<"');">>]) end. update(LServer, Table, Fields, Vals, Where) -> - UPairs = lists:zipwith(fun(A, B) -> A ++ "='" ++ B ++ "'" end, + UPairs = lists:zipwith(fun (A, B) -> + <<A/binary, "='", B/binary, "'">> + end, Fields, Vals), - case ejabberd_odbc:sql_query( - LServer, - ["update ", Table, " set ", - join(UPairs, ", "), - " where ", Where, ";"]) of - {updated, 1} -> - ok; - _ -> - ejabberd_odbc:sql_query( - LServer, - ["insert into ", Table, "(", join(Fields, ", "), - ") values ('", join(Vals, "', '"), "');"]) + case ejabberd_odbc:sql_query(LServer, + [<<"update ">>, Table, <<" set ">>, + join(UPairs, <<", ">>), <<" where ">>, Where, + <<";">>]) + of + {updated, 1} -> ok; + _ -> + ejabberd_odbc:sql_query(LServer, + [<<"insert into ">>, Table, <<"(">>, + join(Fields, <<", ">>), <<") values ('">>, + join(Vals, <<"', '">>), <<"');">>]) end. %% F can be either a fun or a list of queries @@ -149,758 +128,810 @@ sql_transaction(LServer, F) -> ejabberd_odbc:sql_transaction(LServer, F). get_last(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["select seconds, state from last " - "where username='", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"select seconds, state from last where " + "username='">>, + Username, <<"'">>]). set_last_t(LServer, Username, Seconds, State) -> - update(LServer, "last", ["username", "seconds", "state"], + update(LServer, <<"last">>, + [<<"username">>, <<"seconds">>, <<"state">>], [Username, Seconds, State], - ["username='", Username, "'"]). + [<<"username='">>, Username, <<"'">>]). del_last(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["delete from last where username='", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"delete from last where username='">>, Username, + <<"'">>]). get_password(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["select password from users " - "where username='", Username, "';"]). + ejabberd_odbc:sql_query(LServer, + [<<"select password from users where username='">>, + Username, <<"';">>]). set_password_t(LServer, Username, Pass) -> - ejabberd_odbc:sql_transaction( - LServer, - fun() -> - update_t("users", ["username", "password"], - [Username, Pass], - ["username='", Username ,"'"]) - end). + ejabberd_odbc:sql_transaction(LServer, + fun () -> + update_t(<<"users">>, + [<<"username">>, + <<"password">>], + [Username, Pass], + [<<"username='">>, Username, + <<"'">>]) + end). add_user(LServer, Username, Pass) -> - ejabberd_odbc:sql_query( - LServer, - ["insert into users(username, password) " - "values ('", Username, "', '", Pass, "');"]). + ejabberd_odbc:sql_query(LServer, + [<<"insert into users(username, password) " + "values ('">>, + Username, <<"', '">>, Pass, <<"');">>]). del_user(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["delete from users where username='", Username ,"';"]). + ejabberd_odbc:sql_query(LServer, + [<<"delete from users where username='">>, Username, + <<"';">>]). del_user_return_password(_LServer, Username, Pass) -> - P = ejabberd_odbc:sql_query_t( - ["select password from users where username='", - Username, "';"]), - ejabberd_odbc:sql_query_t(["delete from users " - "where username='", Username, - "' and password='", Pass, "';"]), + P = + ejabberd_odbc:sql_query_t([<<"select password from users where username='">>, + Username, <<"';">>]), + ejabberd_odbc:sql_query_t([<<"delete from users where username='">>, + Username, <<"' and password='">>, Pass, + <<"';">>]), P. list_users(LServer) -> - ejabberd_odbc:sql_query( - LServer, - "select username from users"). - -list_users(LServer, [{from, Start}, {to, End}]) when is_integer(Start) and - is_integer(End) -> - list_users(LServer, [{limit, End-Start+1}, {offset, Start-1}]); -list_users(LServer, [{prefix, Prefix}, {from, Start}, {to, End}]) when is_list(Prefix) and - is_integer(Start) and - is_integer(End) -> - list_users(LServer, [{prefix, Prefix}, {limit, End-Start+1}, {offset, Start-1}]); - -list_users(LServer, [{limit, Limit}, {offset, Offset}]) when is_integer(Limit) and - is_integer(Offset) -> - ejabberd_odbc:sql_query( - LServer, - io_lib:format( - "select username from users " ++ - "order by username " ++ - "limit ~w offset ~w", [Limit, Offset])); -list_users(LServer, [{prefix, Prefix}, - {limit, Limit}, - {offset, Offset}]) when is_list(Prefix) and - is_integer(Limit) and - is_integer(Offset) -> - ejabberd_odbc:sql_query( - LServer, - io_lib:format("select username from users " ++ - "where username like '~s%' " ++ - "order by username " ++ - "limit ~w offset ~w ", [Prefix, Limit, Offset])). + ejabberd_odbc:sql_query(LServer, + [<<"select username from users">>]). + +list_users(LServer, [{from, Start}, {to, End}]) + when is_integer(Start) and is_integer(End) -> + list_users(LServer, + [{limit, End - Start + 1}, {offset, Start - 1}]); +list_users(LServer, + [{prefix, Prefix}, {from, Start}, {to, End}]) + when is_binary(Prefix) and is_integer(Start) and + is_integer(End) -> + list_users(LServer, + [{prefix, Prefix}, {limit, End - Start + 1}, + {offset, Start - 1}]); +list_users(LServer, [{limit, Limit}, {offset, Offset}]) + when is_integer(Limit) and is_integer(Offset) -> + ejabberd_odbc:sql_query(LServer, + [list_to_binary( + io_lib:format( + "select username from users " ++ + "order by username " ++ + "limit ~w offset ~w", + [Limit, Offset]))]); +list_users(LServer, + [{prefix, Prefix}, {limit, Limit}, {offset, Offset}]) + when is_binary(Prefix) and is_integer(Limit) and + is_integer(Offset) -> + ejabberd_odbc:sql_query(LServer, + [list_to_binary( + io_lib:format( + "select username from users " ++ + "where username like '~s%' " ++ + "order by username " ++ + "limit ~w offset ~w ", + [Prefix, Limit, Offset]))]). users_number(LServer) -> - case element(1, ejabberd_config:get_local_option({odbc_server, LServer})) of - pgsql -> - case ejabberd_config:get_local_option({pgsql_users_number_estimate, LServer}) of - true -> - ejabberd_odbc:sql_query( - LServer, - "select reltuples from pg_class where oid = 'users'::regclass::oid"); - _ -> - ejabberd_odbc:sql_query( - LServer, - "select count(*) from users") - end; - _ -> - ejabberd_odbc:sql_query( - LServer, - "select count(*) from users") + case element(1, + ejabberd_config:get_local_option( + {odbc_server, LServer}, fun(V) -> V end)) + of + pgsql -> + case + ejabberd_config:get_local_option( + {pgsql_users_number_estimate, LServer}, + fun(V) when is_boolean(V) -> V end, + false) + of + true -> + ejabberd_odbc:sql_query(LServer, + [<<"select reltuples from pg_class where " + "oid = 'users'::regclass::oid">>]); + _ -> + ejabberd_odbc:sql_query(LServer, + [<<"select count(*) from users">>]) + end; + _ -> + ejabberd_odbc:sql_query(LServer, + [<<"select count(*) from users">>]) end. -users_number(LServer, [{prefix, Prefix}]) when is_list(Prefix) -> - ejabberd_odbc:sql_query( - LServer, - io_lib:fwrite("select count(*) from users " ++ - %% Warning: Escape prefix at higher level to prevent SQL - %% injection. - "where username like '~s%'", [Prefix])); +users_number(LServer, [{prefix, Prefix}]) + when is_binary(Prefix) -> + ejabberd_odbc:sql_query(LServer, + [list_to_binary( + io_lib:fwrite( + "select count(*) from users " ++ + %% Warning: Escape prefix at higher level to prevent SQL + %% injection. + "where username like '~s%'", + [Prefix]))]); users_number(LServer, []) -> users_number(LServer). add_spool_sql(Username, XML) -> - ["insert into spool(username, xml) " - "values ('", Username, "', '", - XML, - "');"]. + [<<"insert into spool(username, xml) values ('">>, + Username, <<"', '">>, XML, <<"');">>]. add_spool(LServer, Queries) -> - ejabberd_odbc:sql_transaction( - LServer, Queries). + ejabberd_odbc:sql_transaction(LServer, Queries). get_and_del_spool_msg_t(LServer, Username) -> - F = fun() -> - Result = ejabberd_odbc:sql_query_t( - ["select username, xml from spool where username='", Username, "'" - " order by seq;"]), - ejabberd_odbc:sql_query_t( - ["delete from spool where username='", Username, "';"]), + F = fun () -> + Result = + ejabberd_odbc:sql_query_t([<<"select username, xml from spool where " + "username='">>, + Username, + <<"' order by seq;">>]), + ejabberd_odbc:sql_query_t([<<"delete from spool where username='">>, + Username, <<"';">>]), Result end, - ejabberd_odbc:sql_transaction(LServer,F). + ejabberd_odbc:sql_transaction(LServer, F). del_spool_msg(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["delete from spool where username='", Username, "';"]). + ejabberd_odbc:sql_query(LServer, + [<<"delete from spool where username='">>, Username, + <<"';">>]). get_roster(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["select username, jid, nick, subscription, ask, " - "askmessage, server, subscribe, type from rosterusers " - "where username='", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"select username, jid, nick, subscription, " + "ask, askmessage, server, subscribe, " + "type from rosterusers where username='">>, + Username, <<"'">>]). get_roster_jid_groups(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["select jid, grp from rostergroups " - "where username='", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"select jid, grp from rostergroups where " + "username='">>, + Username, <<"'">>]). get_roster_groups(_LServer, Username, SJID) -> - ejabberd_odbc:sql_query_t( - ["select grp from rostergroups " - "where username='", Username, "' " - "and jid='", SJID, "';"]). + ejabberd_odbc:sql_query_t([<<"select grp from rostergroups where username='">>, + Username, <<"' and jid='">>, SJID, <<"';">>]). del_user_roster_t(LServer, Username) -> - ejabberd_odbc:sql_transaction( - LServer, - fun() -> - ejabberd_odbc:sql_query_t( - ["delete from rosterusers " - " where username='", Username, "';"]), - ejabberd_odbc:sql_query_t( - ["delete from rostergroups " - " where username='", Username, "';"]) - end). + ejabberd_odbc:sql_transaction(LServer, + fun () -> + ejabberd_odbc:sql_query_t([<<"delete from rosterusers where " + "username='">>, + Username, + <<"';">>]), + ejabberd_odbc:sql_query_t([<<"delete from rostergroups where " + "username='">>, + Username, + <<"';">>]) + end). get_roster_by_jid(_LServer, Username, SJID) -> - ejabberd_odbc:sql_query_t( - ["select username, jid, nick, subscription, " - "ask, askmessage, server, subscribe, type from rosterusers " - "where username='", Username, "' " - "and jid='", SJID, "';"]). + ejabberd_odbc:sql_query_t([<<"select username, jid, nick, subscription, " + "ask, askmessage, server, subscribe, " + "type from rosterusers where username='">>, + Username, <<"' and jid='">>, SJID, <<"';">>]). get_rostergroup_by_jid(LServer, Username, SJID) -> - ejabberd_odbc:sql_query( - LServer, - ["select grp from rostergroups " - "where username='", Username, "' " - "and jid='", SJID, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"select grp from rostergroups where username='">>, + Username, <<"' and jid='">>, SJID, <<"'">>]). del_roster(_LServer, Username, SJID) -> - ejabberd_odbc:sql_query_t( - ["delete from rosterusers " - " where username='", Username, "' " - " and jid='", SJID, "';"]), - ejabberd_odbc:sql_query_t( - ["delete from rostergroups " - " where username='", Username, "' " - " and jid='", SJID, "';"]). + ejabberd_odbc:sql_query_t([<<"delete from rosterusers where " + "username='">>, + Username, <<"' and jid='">>, SJID, + <<"';">>]), + ejabberd_odbc:sql_query_t([<<"delete from rostergroups where " + "username='">>, + Username, <<"' and jid='">>, SJID, + <<"';">>]). del_roster_sql(Username, SJID) -> - [["delete from rosterusers " - " where username='", Username, "' " - " and jid='", SJID, "';"], - ["delete from rostergroups " - " where username='", Username, "' " - " and jid='", SJID, "';"]]. - -update_roster(_LServer, Username, SJID, ItemVals, ItemGroups) -> - update_t("rosterusers", - ["username", "jid", "nick", "subscription", "ask", - "askmessage", "server", "subscribe", "type"], + [[<<"delete from rosterusers where " + "username='">>, + Username, <<"' and jid='">>, SJID, <<"';">>], + [<<"delete from rostergroups where " + "username='">>, + Username, <<"' and jid='">>, SJID, <<"';">>]]. + +update_roster(_LServer, Username, SJID, ItemVals, + ItemGroups) -> + update_t(<<"rosterusers">>, + [<<"username">>, <<"jid">>, <<"nick">>, + <<"subscription">>, <<"ask">>, <<"askmessage">>, + <<"server">>, <<"subscribe">>, <<"type">>], ItemVals, - ["username='", Username, "' and jid='", SJID, "'"]), - ejabberd_odbc:sql_query_t( - ["delete from rostergroups " - " where username='", Username, "' " - " and jid='", SJID, "';"]), - lists:foreach(fun(ItemGroup) -> - ejabberd_odbc:sql_query_t( - ["insert into rostergroups(" - " username, jid, grp) " - " values ('", join(ItemGroup, "', '"), "');"]) + [<<"username='">>, Username, <<"' and jid='">>, SJID, + <<"'">>]), + ejabberd_odbc:sql_query_t([<<"delete from rostergroups where " + "username='">>, + Username, <<"' and jid='">>, SJID, + <<"';">>]), + lists:foreach(fun (ItemGroup) -> + ejabberd_odbc:sql_query_t([<<"insert into rostergroups( " + " username, jid, grp) values ('">>, + join(ItemGroup, + <<"', '">>), + <<"');">>]) end, ItemGroups). -update_roster_sql(Username, SJID, ItemVals, ItemGroups) -> - [["delete from rosterusers " - " where username='", Username, "' " - " and jid='", SJID, "';"], - ["insert into rosterusers(" - " username, jid, nick, " - " subscription, ask, askmessage, " - " server, subscribe, type) " - " values ('", join(ItemVals, "', '"), "');"], - ["delete from rostergroups " - " where username='", Username, "' " - " and jid='", SJID, "';"]] ++ - [["insert into rostergroups(" - " username, jid, grp) " - " values ('", join(ItemGroup, "', '"), "');"] || - ItemGroup <- ItemGroups]. +update_roster_sql(Username, SJID, ItemVals, + ItemGroups) -> + [[<<"delete from rosterusers where " + "username='">>, + Username, <<"' and jid='">>, SJID, <<"';">>], + [<<"insert into rosterusers( " + " username, jid, nick, " + " subscription, ask, askmessage, " + " server, subscribe, type) " + "values ('">>, + join(ItemVals, <<"', '">>), <<"');">>], + [<<"delete from rostergroups where " + "username='">>, + Username, <<"' and jid='">>, SJID, <<"';">>]] + ++ + [[<<"insert into rostergroups( " + " username, jid, grp) values ('">>, + join(ItemGroup, <<"', '">>), <<"');">>] + || ItemGroup <- ItemGroups]. roster_subscribe(_LServer, Username, SJID, ItemVals) -> - update_t("rosterusers", - ["username", "jid", "nick", "subscription", "ask", - "askmessage", "server", "subscribe", "type"], + update_t(<<"rosterusers">>, + [<<"username">>, <<"jid">>, <<"nick">>, + <<"subscription">>, <<"ask">>, <<"askmessage">>, + <<"server">>, <<"subscribe">>, <<"type">>], ItemVals, - ["username='", Username, "' and jid='", SJID, "'"]). + [<<"username='">>, Username, <<"' and jid='">>, SJID, + <<"'">>]). get_subscription(LServer, Username, SJID) -> - ejabberd_odbc:sql_query( - LServer, - ["select subscription from rosterusers " - "where username='", Username, "' " - "and jid='", SJID, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"select subscription from rosterusers " + "where username='">>, + Username, <<"' and jid='">>, SJID, <<"'">>]). set_private_data(_LServer, Username, LXMLNS, SData) -> - update_t("private_storage", - ["username", "namespace", "data"], - [Username, LXMLNS, SData], - ["username='", Username, "' and namespace='", LXMLNS, "'"]). + update_t(<<"private_storage">>, + [<<"username">>, <<"namespace">>, <<"data">>], + [Username, LXMLNS, SData], + [<<"username='">>, Username, <<"' and namespace='">>, + LXMLNS, <<"'">>]). set_private_data_sql(Username, LXMLNS, SData) -> - [["delete from private_storage " - "where username='", Username, "' and " - "namespace='", LXMLNS, "';"], - ["insert into private_storage(username, namespace, data) " - "values ('", Username, "', '", LXMLNS, "', " - "'", SData, "');"]]. + [[<<"delete from private_storage where username='">>, + Username, <<"' and namespace='">>, LXMLNS, <<"';">>], + [<<"insert into private_storage(username, " + "namespace, data) values ('">>, + Username, <<"', '">>, LXMLNS, <<"', '">>, SData, + <<"');">>]]. get_private_data(LServer, Username, LXMLNS) -> - ejabberd_odbc:sql_query( - LServer, - ["select data from private_storage " - "where username='", Username, "' and " - "namespace='", LXMLNS, "';"]). + ejabberd_odbc:sql_query(LServer, + [<<"select data from private_storage where " + "username='">>, + Username, <<"' and namespace='">>, LXMLNS, + <<"';">>]). + +get_private_data(LServer, Username) -> + ejabberd_odbc:sql_query(LServer, + [<<"select namespace, data from private_storage " + "where username='">>, Username, <<"';">>]). del_user_private_storage(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["delete from private_storage where username='", Username, "';"]). - -set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, SFamily, SGiven, - SLBDay, SLCTRY, SLEMail, SLFN, SLFamily, SLGiven, SLLocality, - SLMiddle, SLNickname, SLOrgName, SLOrgUnit, SLocality, SMiddle, - SNickname, SOrgName, SOrgUnit, SVCARD, Username) -> - ejabberd_odbc:sql_transaction( - LServer, - fun() -> - update_t("vcard", ["username", "vcard"], - [LUsername, SVCARD], - ["username='", LUsername, "'"]), - update_t("vcard_search", - ["username", "lusername", "fn", "lfn", "family", - "lfamily", "given", "lgiven", "middle", "lmiddle", - "nickname", "lnickname", "bday", "lbday", "ctry", - "lctry", "locality", "llocality", "email", "lemail", - "orgname", "lorgname", "orgunit", "lorgunit"], - [Username, LUsername, SFN, SLFN, SFamily, SLFamily, - SGiven, SLGiven, SMiddle, SLMiddle, SNickname, - SLNickname, SBDay, SLBDay, SCTRY, SLCTRY, - SLocality, SLLocality, SEMail, SLEMail, SOrgName, - SLOrgName, SOrgUnit, SLOrgUnit], - ["lusername='", LUsername, "'"]) - end). + ejabberd_odbc:sql_query(LServer, + [<<"delete from private_storage where username='">>, + Username, <<"';">>]). + +set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, + SFamily, SGiven, SLBDay, SLCTRY, SLEMail, SLFN, + SLFamily, SLGiven, SLLocality, SLMiddle, SLNickname, + SLOrgName, SLOrgUnit, SLocality, SMiddle, SNickname, + SOrgName, SOrgUnit, SVCARD, Username) -> + ejabberd_odbc:sql_transaction(LServer, + fun () -> + update_t(<<"vcard">>, + [<<"username">>, + <<"vcard">>], + [LUsername, SVCARD], + [<<"username='">>, LUsername, + <<"'">>]), + update_t(<<"vcard_search">>, + [<<"username">>, + <<"lusername">>, <<"fn">>, + <<"lfn">>, <<"family">>, + <<"lfamily">>, <<"given">>, + <<"lgiven">>, <<"middle">>, + <<"lmiddle">>, + <<"nickname">>, + <<"lnickname">>, <<"bday">>, + <<"lbday">>, <<"ctry">>, + <<"lctry">>, <<"locality">>, + <<"llocality">>, + <<"email">>, <<"lemail">>, + <<"orgname">>, + <<"lorgname">>, + <<"orgunit">>, + <<"lorgunit">>], + [Username, LUsername, SFN, + SLFN, SFamily, SLFamily, + SGiven, SLGiven, SMiddle, + SLMiddle, SNickname, + SLNickname, SBDay, SLBDay, + SCTRY, SLCTRY, SLocality, + SLLocality, SEMail, SLEMail, + SOrgName, SLOrgName, + SOrgUnit, SLOrgUnit], + [<<"lusername='">>, + LUsername, <<"'">>]) + end). get_vcard(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["select vcard from vcard " - "where username='", Username, "';"]). + ejabberd_odbc:sql_query(LServer, + [<<"select vcard from vcard where username='">>, + Username, <<"';">>]). get_default_privacy_list(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["select name from privacy_default_list " - "where username='", Username, "';"]). + ejabberd_odbc:sql_query(LServer, + [<<"select name from privacy_default_list " + "where username='">>, + Username, <<"';">>]). get_default_privacy_list_t(Username) -> - ejabberd_odbc:sql_query_t( - ["select name from privacy_default_list " - "where username='", Username, "';"]). + ejabberd_odbc:sql_query_t([<<"select name from privacy_default_list " + "where username='">>, + Username, <<"';">>]). get_privacy_list_names(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["select name from privacy_list " - "where username='", Username, "';"]). + ejabberd_odbc:sql_query(LServer, + [<<"select name from privacy_list where " + "username='">>, + Username, <<"';">>]). get_privacy_list_names_t(Username) -> - ejabberd_odbc:sql_query_t( - ["select name from privacy_list " - "where username='", Username, "';"]). + ejabberd_odbc:sql_query_t([<<"select name from privacy_list where " + "username='">>, + Username, <<"';">>]). get_privacy_list_id(LServer, Username, SName) -> - ejabberd_odbc:sql_query( - LServer, - ["select id from privacy_list " - "where username='", Username, "' and name='", SName, "';"]). + ejabberd_odbc:sql_query(LServer, + [<<"select id from privacy_list where username='">>, + Username, <<"' and name='">>, SName, <<"';">>]). get_privacy_list_id_t(Username, SName) -> - ejabberd_odbc:sql_query_t( - ["select id from privacy_list " - "where username='", Username, "' and name='", SName, "';"]). + ejabberd_odbc:sql_query_t([<<"select id from privacy_list where username='">>, + Username, <<"' and name='">>, SName, <<"';">>]). get_privacy_list_data(LServer, Username, SName) -> - ejabberd_odbc:sql_query( - LServer, - ["select t, value, action, ord, match_all, match_iq, " - "match_message, match_presence_in, match_presence_out " - "from privacy_list_data " - "where id = (select id from privacy_list where " - " username='", Username, "' and name='", SName, "') " - "order by ord;"]). + ejabberd_odbc:sql_query(LServer, + [<<"select t, value, action, ord, match_all, " + "match_iq, match_message, match_presence_in, " + "match_presence_out from privacy_list_data " + "where id = (select id from privacy_list " + "where username='">>, + Username, <<"' and name='">>, SName, + <<"') order by ord;">>]). get_privacy_list_data_by_id(LServer, ID) -> - ejabberd_odbc:sql_query( - LServer, - ["select t, value, action, ord, match_all, match_iq, " - "match_message, match_presence_in, match_presence_out " - "from privacy_list_data " - "where id='", ID, "' order by ord;"]). + ejabberd_odbc:sql_query(LServer, + [<<"select t, value, action, ord, match_all, " + "match_iq, match_message, match_presence_in, " + "match_presence_out from privacy_list_data " + "where id='">>, + ID, <<"' order by ord;">>]). get_privacy_list_data_by_id_t(ID) -> - ejabberd_odbc:sql_query_t( - ["select t, value, action, ord, match_all, match_iq, " - "match_message, match_presence_in, match_presence_out " - "from privacy_list_data " - "where id='", ID, "' order by ord;"]). + ejabberd_odbc:sql_query_t([<<"select t, value, action, ord, match_all, " + "match_iq, match_message, match_presence_in, " + "match_presence_out from privacy_list_data " + "where id='">>, + ID, <<"' order by ord;">>]). set_default_privacy_list(Username, SName) -> - update_t("privacy_default_list", ["username", "name"], - [Username, SName], ["username='", Username, "'"]). + update_t(<<"privacy_default_list">>, + [<<"username">>, <<"name">>], [Username, SName], + [<<"username='">>, Username, <<"'">>]). unset_default_privacy_list(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["delete from privacy_default_list " - " where username='", Username, "';"]). + ejabberd_odbc:sql_query(LServer, + [<<"delete from privacy_default_list " + " where username='">>, + Username, <<"';">>]). remove_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t( - ["delete from privacy_list " - "where username='", Username, "' and name='", SName, "';"]). + ejabberd_odbc:sql_query_t([<<"delete from privacy_list where username='">>, + Username, <<"' and name='">>, SName, <<"';">>]). add_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t( - ["insert into privacy_list(username, name) " - "values ('", Username, "', '", SName, "');"]). + ejabberd_odbc:sql_query_t([<<"insert into privacy_list(username, name) " + "values ('">>, + Username, <<"', '">>, SName, <<"');">>]). set_privacy_list(ID, RItems) -> - ejabberd_odbc:sql_query_t( - ["delete from privacy_list_data " - "where id='", ID, "';"]), - lists:foreach(fun(Items) -> - ejabberd_odbc:sql_query_t( - ["insert into privacy_list_data(" - "id, t, value, action, ord, match_all, match_iq, " - "match_message, match_presence_in, " - "match_presence_out " - ") " - "values ('", ID, "', '", - join(Items, "', '"), "');"]) - end, RItems). + ejabberd_odbc:sql_query_t([<<"delete from privacy_list_data where " + "id='">>, + ID, <<"';">>]), + lists:foreach(fun (Items) -> + ejabberd_odbc:sql_query_t([<<"insert into privacy_list_data(id, t, " + "value, action, ord, match_all, match_iq, " + "match_message, match_presence_in, match_prese" + "nce_out ) values ('">>, + ID, <<"', '">>, + join(Items, <<"', '">>), + <<"');">>]) + end, + RItems). del_privacy_lists(LServer, Server, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["delete from privacy_list where username='", Username, "';"]), - ejabberd_odbc:sql_query( - LServer, - ["delete from privacy_list_data where value='", Username++"@"++Server, "';"]), - ejabberd_odbc:sql_query( - LServer, - ["delete from privacy_default_list where username='", Username, "';"]). - %% Characters to escape -escape($\0) -> "\\0"; -escape($\n) -> "\\n"; -escape($\t) -> "\\t"; -escape($\b) -> "\\b"; -escape($\r) -> "\\r"; -escape($') -> "''"; -escape($") -> "\\\""; -escape($\\) -> "\\\\"; -escape(C) -> C. - %% Count number of records in a table given a where clause -count_records_where(LServer, Table, WhereClause) -> - ejabberd_odbc:sql_query( - LServer, - ["select count(*) from ", Table, " ", WhereClause, ";"]). + ejabberd_odbc:sql_query(LServer, + [<<"delete from privacy_list where username='">>, + Username, <<"';">>]), + ejabberd_odbc:sql_query(LServer, + [<<"delete from privacy_list_data where " + "value='">>, + <<Username/binary, "@", Server/binary>>, + <<"';">>]), + ejabberd_odbc:sql_query(LServer, + [<<"delete from privacy_default_list where " + "username='">>, + Username, <<"';">>]). + +escape($\000) -> <<"\\0">>; +escape($\n) -> <<"\\n">>; +escape($\t) -> <<"\\t">>; +escape($\b) -> <<"\\b">>; +escape($\r) -> <<"\\r">>; +escape($') -> <<"''">>; +escape($") -> <<"\\\"">>; +escape($\\) -> <<"\\\\">>; +escape(C) -> <<C>>. +count_records_where(LServer, Table, WhereClause) -> + ejabberd_odbc:sql_query(LServer, + [<<"select count(*) from ">>, Table, <<" ">>, + WhereClause, <<";">>]). get_roster_version(LServer, LUser) -> - ejabberd_odbc:sql_query(LServer, - ["select version from roster_version where username = '", LUser, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"select version from roster_version where " + "username = '">>, + LUser, <<"'">>]). + set_roster_version(LUser, Version) -> - update_t("roster_version", ["username", "version"], [LUser, Version], ["username = '", LUser, "'"]). + update_t(<<"roster_version">>, + [<<"username">>, <<"version">>], [LUser, Version], + [<<"username = '">>, LUser, <<"'">>]). + -endif. %% ----------------- %% MSSQL queries -ifdef(mssql). -get_db_type() -> - mssql. - %% Queries can be either a fun or a list of queries -sql_transaction(LServer, Queries) when is_list(Queries) -> - %% SQL transaction based on a list of queries - %% This function automatically - F = fun() -> - lists:foreach(fun(Query) -> - ejabberd_odbc:sql_query(LServer, Query) - end, Queries) - end, +get_db_type() -> mssql. + +sql_transaction(LServer, Queries) + when is_list(Queries) -> + F = fun () -> + lists:foreach(fun (Query) -> + ejabberd_odbc:sql_query(LServer, Query) + end, + Queries) + end, {atomic, catch F()}; sql_transaction(_LServer, FQueries) -> {atomic, catch FQueries()}. get_last(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_last '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_last '">>, Username, <<"'">>]). set_last_t(LServer, Username, Seconds, State) -> - Result = ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.set_last '", Username, "', '", Seconds, - "', '", State, "'"]), + Result = ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.set_last '">>, Username, + <<"', '">>, Seconds, <<"', '">>, State, + <<"'">>]), {atomic, Result}. del_last(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.del_last '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.del_last '">>, Username, <<"'">>]). get_password(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_password '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_password '">>, Username, + <<"'">>]). set_password_t(LServer, Username, Pass) -> - Result = ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.set_password '", Username, "', '", Pass, "'"]), + Result = ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.set_password '">>, + Username, <<"', '">>, Pass, <<"'">>]), {atomic, Result}. add_user(LServer, Username, Pass) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.add_user '", Username, "', '", Pass, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.add_user '">>, Username, <<"', '">>, + Pass, <<"'">>]). del_user(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.del_user '", Username ,"'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.del_user '">>, Username, <<"'">>]). del_user_return_password(LServer, Username, Pass) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.del_user_return_password '", Username, "'"]), + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.del_user_return_password '">>, + Username, <<"'">>]), Pass. list_users(LServer) -> - ejabberd_odbc:sql_query( - LServer, - "EXECUTE dbo.list_users"). + ejabberd_odbc:sql_query(LServer, + <<"EXECUTE dbo.list_users">>). -list_users(LServer, _) -> - % scope listing not supported - list_users(LServer). +list_users(LServer, _) -> list_users(LServer). users_number(LServer) -> - ejabberd_odbc:sql_query( - LServer, - "select count(*) from users with (nolock)"). + ejabberd_odbc:sql_query(LServer, + <<"select count(*) from users with (nolock)">>). -users_number(LServer, _) -> - % scope listing not supported - users_number(LServer). +users_number(LServer, _) -> users_number(LServer). add_spool_sql(Username, XML) -> - ["EXECUTE dbo.add_spool '", Username, "' , '",XML,"'"]. + [<<"EXECUTE dbo.add_spool '">>, Username, <<"' , '">>, + XML, <<"'">>]. add_spool(LServer, Queries) -> - lists:foreach(fun(Query) -> + lists:foreach(fun (Query) -> ejabberd_odbc:sql_query(LServer, Query) end, Queries). get_and_del_spool_msg_t(LServer, Username) -> - [Result] = case ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_and_del_spool_msg '", Username, "'"]) of - Rs when is_list(Rs) -> - lists:filter(fun({selected, _Header, _Row}) -> - true; - ({updated, _N}) -> - false + [Result] = case ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_and_del_spool_msg '">>, + Username, <<"'">>]) + of + Rs when is_list(Rs) -> + lists:filter(fun ({selected, _Header, _Row}) -> true; + ({updated, _N}) -> false end, Rs); - Rs -> [Rs] + Rs -> [Rs] end, {atomic, Result}. del_spool_msg(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.del_spool_msg '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.del_spool_msg '">>, Username, + <<"'">>]). get_roster(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_roster '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_roster '">>, Username, + <<"'">>]). get_roster_jid_groups(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_roster_jid_groups '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_roster_jid_groups '">>, + Username, <<"'">>]). get_roster_groups(LServer, Username, SJID) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_roster_groups '", Username, "' , '", SJID, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_roster_groups '">>, Username, + <<"' , '">>, SJID, <<"'">>]). del_user_roster_t(LServer, Username) -> - Result = ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.del_user_roster '", Username, "'"]), + Result = ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.del_user_roster '">>, + Username, <<"'">>]), {atomic, Result}. get_roster_by_jid(LServer, Username, SJID) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_roster_by_jid '", Username, "' , '", SJID, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_roster_by_jid '">>, Username, + <<"' , '">>, SJID, <<"'">>]). get_rostergroup_by_jid(LServer, Username, SJID) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_rostergroup_by_jid '", Username, "' , '", SJID, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_rostergroup_by_jid '">>, + Username, <<"' , '">>, SJID, <<"'">>]). del_roster(LServer, Username, SJID) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.del_roster '", Username, "', '", SJID, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.del_roster '">>, Username, + <<"', '">>, SJID, <<"'">>]). del_roster_sql(Username, SJID) -> - ["EXECUTE dbo.del_roster '", Username, "', '", SJID, "'"]. + [<<"EXECUTE dbo.del_roster '">>, Username, <<"', '">>, + SJID, <<"'">>]. -update_roster(LServer, Username, SJID, ItemVals, ItemGroups) -> - Query1 = ["EXECUTE dbo.del_roster '", Username, "', '", SJID, "' "], +update_roster(LServer, Username, SJID, ItemVals, + ItemGroups) -> + Query1 = [<<"EXECUTE dbo.del_roster '">>, Username, + <<"', '">>, SJID, <<"' ">>], ejabberd_odbc:sql_query(LServer, lists:flatten(Query1)), - Query2 = ["EXECUTE dbo.add_roster_user ", ItemVals], + Query2 = [<<"EXECUTE dbo.add_roster_user ">>, ItemVals], ejabberd_odbc:sql_query(LServer, lists:flatten(Query2)), - Query3 = ["EXECUTE dbo.del_roster_groups '", Username, "', '", SJID, "' "], + Query3 = [<<"EXECUTE dbo.del_roster_groups '">>, + Username, <<"', '">>, SJID, <<"' ">>], ejabberd_odbc:sql_query(LServer, lists:flatten(Query3)), - lists:foreach(fun(ItemGroup) -> - Query = ["EXECUTE dbo.add_roster_group ", + lists:foreach(fun (ItemGroup) -> + Query = [<<"EXECUTE dbo.add_roster_group ">>, ItemGroup], - ejabberd_odbc:sql_query(LServer, - lists:flatten(Query)) + ejabberd_odbc:sql_query(LServer, lists:flatten(Query)) end, ItemGroups). -update_roster_sql(Username, SJID, ItemVals, ItemGroups) -> - ["BEGIN TRANSACTION ", - "EXECUTE dbo.del_roster_groups '", Username, "','", SJID, "' ", - "EXECUTE dbo.add_roster_user ", ItemVals, " "] ++ - [lists:flatten("EXECUTE dbo.add_roster_group ", ItemGroup, " ") - || ItemGroup <- ItemGroups] ++ - ["COMMIT"]. +update_roster_sql(Username, SJID, ItemVals, + ItemGroups) -> + [<<"BEGIN TRANSACTION ">>, + <<"EXECUTE dbo.del_roster_groups '">>, Username, + <<"','">>, SJID, <<"' ">>, + <<"EXECUTE dbo.add_roster_user ">>, ItemVals, <<" ">>] + ++ + [lists:flatten(<<"EXECUTE dbo.add_roster_group ">>, + ItemGroup, <<" ">>) + || ItemGroup <- ItemGroups] + ++ [<<"COMMIT">>]. roster_subscribe(LServer, _Username, _SJID, ItemVals) -> - catch ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.add_roster_user ", ItemVals]). + catch ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.add_roster_user ">>, + ItemVals]). get_subscription(LServer, Username, SJID) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_subscription '", Username, "' , '", SJID, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_subscription '">>, Username, + <<"' , '">>, SJID, <<"'">>]). set_private_data(LServer, Username, LXMLNS, SData) -> - ejabberd_odbc:sql_query( - LServer, - set_private_data_sql(Username, LXMLNS, SData)). + ejabberd_odbc:sql_query(LServer, + set_private_data_sql(Username, LXMLNS, SData)). set_private_data_sql(Username, LXMLNS, SData) -> - ["EXECUTE dbo.set_private_data '", Username, "' , '", LXMLNS, "' , '", SData, "'"]. + [<<"EXECUTE dbo.set_private_data '">>, Username, + <<"' , '">>, LXMLNS, <<"' , '">>, SData, <<"'">>]. get_private_data(LServer, Username, LXMLNS) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_private_data '", Username, "' , '", LXMLNS, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_private_data '">>, Username, + <<"' , '">>, LXMLNS, <<"'">>]). del_user_private_storage(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.del_user_storage '", Username, "'"]). - -set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, SFamily, SGiven, - SLBDay, SLCTRY, SLEMail, SLFN, SLFamily, SLGiven, SLLocality, - SLMiddle, SLNickname, SLOrgName, SLOrgUnit, SLocality, SMiddle, - SNickname, SOrgName, SOrgUnit, SVCARD, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.set_vcard '", SVCARD, "' , '", Username, "' , '", LUsername, "' , '", - SFN, "' , '", SLFN, "' , '", SFamily, "' , '", SLFamily, "' , '", - SGiven, "' , '", SLGiven, "' , '", SMiddle, "' , '", SLMiddle, "' , '", - SNickname, "' , '", SLNickname, "' , '", SBDay, "' , '", SLBDay, "' , '", - SCTRY, "' , '", SLCTRY, "' , '", SLocality, "' , '", SLLocality, "' , '", - SEMail, "' , '", SLEMail, "' , '", SOrgName, "' , '", SLOrgName, "' , '", - SOrgUnit, "' , '", SLOrgUnit, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.del_user_storage '">>, Username, + <<"'">>]). + +set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, + SFamily, SGiven, SLBDay, SLCTRY, SLEMail, SLFN, + SLFamily, SLGiven, SLLocality, SLMiddle, SLNickname, + SLOrgName, SLOrgUnit, SLocality, SMiddle, SNickname, + SOrgName, SOrgUnit, SVCARD, Username) -> + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.set_vcard '">>, SVCARD, <<"' , '">>, + Username, <<"' , '">>, LUsername, <<"' , '">>, SFN, + <<"' , '">>, SLFN, <<"' , '">>, SFamily, + <<"' , '">>, SLFamily, <<"' , '">>, SGiven, + <<"' , '">>, SLGiven, <<"' , '">>, SMiddle, + <<"' , '">>, SLMiddle, <<"' , '">>, SNickname, + <<"' , '">>, SLNickname, <<"' , '">>, SBDay, + <<"' , '">>, SLBDay, <<"' , '">>, SCTRY, + <<"' , '">>, SLCTRY, <<"' , '">>, SLocality, + <<"' , '">>, SLLocality, <<"' , '">>, SEMail, + <<"' , '">>, SLEMail, <<"' , '">>, SOrgName, + <<"' , '">>, SLOrgName, <<"' , '">>, SOrgUnit, + <<"' , '">>, SLOrgUnit, <<"'">>]). get_vcard(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_vcard '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_vcard '">>, Username, <<"'">>]). get_default_privacy_list(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_default_privacy_list '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_default_privacy_list '">>, + Username, <<"'">>]). get_default_privacy_list_t(Username) -> - ejabberd_odbc:sql_query_t( - ["EXECUTE dbo.get_default_privacy_list '", Username, "'"]). + ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.get_default_privacy_list '">>, + Username, <<"'">>]). get_privacy_list_names(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_privacy_list_names '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_privacy_list_names '">>, + Username, <<"'">>]). get_privacy_list_names_t(Username) -> - ejabberd_odbc:sql_query_t( - ["EXECUTE dbo.get_privacy_list_names '", Username, "'"]). + ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.get_privacy_list_names '">>, + Username, <<"'">>]). get_privacy_list_id(LServer, Username, SName) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_privacy_list_id '", Username, "' , '", SName, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_privacy_list_id '">>, Username, + <<"' , '">>, SName, <<"'">>]). get_privacy_list_id_t(Username, SName) -> - ejabberd_odbc:sql_query_t( - ["EXECUTE dbo.get_privacy_list_id '", Username, "' , '", SName, "'"]). + ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.get_privacy_list_id '">>, + Username, <<"' , '">>, SName, <<"'">>]). get_privacy_list_data(LServer, Username, SName) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_privacy_list_data '", Username, "' , '", SName, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_privacy_list_data '">>, + Username, <<"' , '">>, SName, <<"'">>]). get_privacy_list_data_by_id(LServer, ID) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_privacy_list_data_by_id '", ID, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_privacy_list_data_by_id '">>, + ID, <<"'">>]). get_privacy_list_data_by_id_t(ID) -> - ejabberd_odbc:sql_query_t( - ["EXECUTE dbo.get_privacy_list_data_by_id '", ID, "'"]). + ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.get_privacy_list_data_by_id '">>, + ID, <<"'">>]). set_default_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t( - ["EXECUTE dbo.set_default_privacy_list '", Username, "' , '", SName, "'"]). + ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.set_default_privacy_list '">>, + Username, <<"' , '">>, SName, <<"'">>]). unset_default_privacy_list(LServer, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.unset_default_privacy_list '", Username, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.unset_default_privacy_list '">>, + Username, <<"'">>]). remove_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t( - ["EXECUTE dbo.remove_privacy_list '", Username, "' , '", SName, "'"]). + ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.remove_privacy_list '">>, + Username, <<"' , '">>, SName, <<"'">>]). add_privacy_list(Username, SName) -> - ejabberd_odbc:sql_query_t( - ["EXECUTE dbo.add_privacy_list '", Username, "' , '", SName, "'"]). + ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.add_privacy_list '">>, + Username, <<"' , '">>, SName, <<"'">>]). set_privacy_list(ID, RItems) -> - ejabberd_odbc:sql_query_t( - ["EXECUTE dbo.del_privacy_list_by_id '", ID, "'"]), - - lists:foreach(fun(Items) -> - ejabberd_odbc:sql_query_t( - ["EXECUTE dbo.set_privacy_list '", ID, "', '", join(Items, "', '"), "'"]) - end, RItems). + ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.del_privacy_list_by_id '">>, + ID, <<"'">>]), + lists:foreach(fun (Items) -> + ejabberd_odbc:sql_query_t([<<"EXECUTE dbo.set_privacy_list '">>, + ID, <<"', '">>, + join(Items, <<"', '">>), + <<"'">>]) + end, + RItems). del_privacy_lists(LServer, Server, Username) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.del_privacy_lists @Server='", Server ,"' @username='", Username, "'"]). - %% Characters to escape -escape($\0) -> "\\0"; -escape($\t) -> "\\t"; -escape($\b) -> "\\b"; -escape($\r) -> "\\r"; -escape($') -> "\''"; -escape($") -> "\\\""; -escape(C) -> C. - %% Count number of records in a table given a where clause + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.del_privacy_lists @Server='">>, + Server, <<"' @username='">>, Username, <<"'">>]). + +escape($\000) -> <<"\\0">>; +escape($\t) -> <<"\\t">>; +escape($\b) -> <<"\\b">>; +escape($\r) -> <<"\\r">>; +escape($') -> <<"''">>; +escape($") -> <<"\\\"">>; +escape(C) -> C. + count_records_where(LServer, Table, WhereClause) -> - ejabberd_odbc:sql_query( - LServer, - ["select count(*) from ", Table, " with (nolock) ", WhereClause]). + ejabberd_odbc:sql_query(LServer, + [<<"select count(*) from ">>, Table, + <<" with (nolock) ">>, WhereClause]). get_roster_version(LServer, LUser) -> - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.get_roster_version '", LUser, "'"]). + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.get_roster_version '">>, LUser, + <<"'">>]). set_roster_version(Username, Version) -> - %% This function doesn't know the vhost, so we hope it's the first one defined: - LServer = ?MYNAME, - ejabberd_odbc:sql_query( - LServer, - ["EXECUTE dbo.set_roster_version '", Username, "', '", Version, "'"]). + LServer = (?MYNAME), + ejabberd_odbc:sql_query(LServer, + [<<"EXECUTE dbo.set_roster_version '">>, Username, + <<"', '">>, Version, <<"'">>]). + -endif. diff --git a/src/odbc/pg.sql b/src/odbc/pg.sql index 4ed897e79..0b641d575 100644 --- a/src/odbc/pg.sql +++ b/src/odbc/pg.sql @@ -274,3 +274,12 @@ CREATE TABLE motd ( xml text, created_at TIMESTAMP NOT NULL DEFAULT now() ); + +CREATE TABLE caps_features ( + node text NOT NULL, + subnode text NOT NULL, + feature text, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_caps_features_node_subnode ON caps_features USING btree (node, subnode); |