aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAlexey Shchepin <alexey@process-one.net>2017-11-02 17:03:30 +0300
committerAlexey Shchepin <alexey@process-one.net>2017-11-02 18:21:40 +0300
commit78dfb832b8e5b711cfbbe3131493057325c0008d (patch)
tree8b3148730887908a2c6e676e6ce159b24b6d05d8
parentFix pkix:validate() return value (diff)
Add SQL_INSERT macro and update SQL queries to use server_host field
Diffstat
-rw-r--r--configure.ac9
-rw-r--r--include/ejabberd_sql_pt.hrl3
-rw-r--r--rebar.config1
-rw-r--r--sql/pg.new.sql604
-rw-r--r--src/ejabberd_auth_mnesia.erl1
-rw-r--r--src/ejabberd_auth_riak.erl9
-rw-r--r--src/ejabberd_auth_sql.erl71
-rw-r--r--src/ejabberd_sm_sql.erl6
-rw-r--r--src/ejabberd_sql_pt.erl200
-rw-r--r--src/mod_admin_update_sql.erl365
-rw-r--r--src/mod_announce_sql.erl27
-rw-r--r--src/mod_carboncopy_sql.erl5
-rw-r--r--src/mod_irc_sql.erl14
-rw-r--r--src/mod_last_sql.erl14
-rw-r--r--src/mod_mam_sql.erl119
-rw-r--r--src/mod_muc_sql.erl43
-rw-r--r--src/mod_offline_sql.erl39
-rw-r--r--src/mod_privacy_sql.erl83
-rw-r--r--src/mod_private_sql.erl20
-rw-r--r--src/mod_push_sql.erl34
-rw-r--r--src/mod_roster.erl8
-rw-r--r--src/mod_roster_sql.erl115
-rw-r--r--src/mod_shared_roster_sql.erl53
-rw-r--r--src/mod_vcard_sql.erl91
-rw-r--r--vars.config.in1
25 files changed, 1638 insertions, 297 deletions
diff --git a/configure.ac b/configure.ac
index 1fdf30293..17d32ac18 100644
--- a/configure.ac
+++ b/configure.ac
@@ -84,6 +84,14 @@ AC_ARG_ENABLE(roster_gateway_workaround,
*) AC_MSG_ERROR(bad value ${enableval} for --enable-roster-gateway-workaround) ;;
esac],[roster_gateway_workaround=false])
+AC_ARG_ENABLE(new_sql_schema,
+[AC_HELP_STRING([--enable-new-sql-schema], [use new SQL schema (default: no)])],
+[case "${enableval}" in
+ yes) new_sql_schema=true ;;
+ no) new_sql_schema=false ;;
+ *) AC_MSG_ERROR(bad value ${enableval} for --enable-new-sql-schema) ;;
+esac],[new_sql_schema=false])
+
AC_ARG_ENABLE(full_xml,
[AC_HELP_STRING([--enable-full-xml], [use XML features in XMPP stream (ex: CDATA) (default: no, requires XML compliant clients)])],
[case "${enableval}" in
@@ -273,6 +281,7 @@ fi
AC_SUBST(hipe)
AC_SUBST(roster_gateway_workaround)
+AC_SUBST(new_sql_schema)
AC_SUBST(full_xml)
AC_SUBST(db_type)
AC_SUBST(odbc)
diff --git a/include/ejabberd_sql_pt.hrl b/include/ejabberd_sql_pt.hrl
index 8e5e5c38c..438a6563d 100644
--- a/include/ejabberd_sql_pt.hrl
+++ b/include/ejabberd_sql_pt.hrl
@@ -27,6 +27,9 @@
-define(SQL_UPSERT_T(Table, Fields),
ejabberd_sql:sql_query_t(?SQL_UPSERT_MARK(Table, Fields))).
+-define(SQL_INSERT_MARK, sql_insert__mark_).
+-define(SQL_INSERT(Table, Fields), ?SQL_INSERT_MARK(Table, Fields)).
+
-record(sql_query, {hash, format_query, format_res, args, loc}).
-record(sql_escape, {string, integer, boolean}).
diff --git a/rebar.config b/rebar.config
index ce2806e33..7b03fc8c4 100644
--- a/rebar.config
+++ b/rebar.config
@@ -99,6 +99,7 @@
{if_have_fun, {rand, uniform, 1}, {d, 'RAND_UNIFORM'}},
{if_have_fun, {gb_sets, iterator_from, 2}, {d, 'GB_SETS_ITERATOR_FROM'}},
{if_have_fun, {public_key, short_name_hash, 1}, {d, 'SHORT_NAME_HASH'}},
+ {if_var_true, new_sql_schema, {d, 'NEW_SQL_SCHEMA'}},
{if_var_true, hipe, native},
{src_dirs, [asn1, src,
{if_var_true, tools, tools},
diff --git a/sql/pg.new.sql b/sql/pg.new.sql
new file mode 100644
index 000000000..b155a4223
--- /dev/null
+++ b/sql/pg.new.sql
@@ -0,0 +1,604 @@
+--
+-- ejabberd, Copyright (C) 2002-2017 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.
+--
+
+-- To update from the old schema, replace <HOST> with the host's domain:
+
+-- ALTER TABLE users ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE users DROP CONSTRAINT users_pkey;
+-- ALTER TABLE users ADD PRIMARY KEY (server_host, username);
+-- ALTER TABLE users ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE last ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE last DROP CONSTRAINT last_pkey;
+-- ALTER TABLE last ADD PRIMARY KEY (server_host, username);
+-- ALTER TABLE last ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE rosterusers ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- DROP INDEX i_rosteru_user_jid;
+-- DROP INDEX i_rosteru_username;
+-- DROP INDEX i_rosteru_jid;
+-- CREATE UNIQUE INDEX i_rosteru_sh_user_jid ON rosterusers USING btree (server_host, username, jid);
+-- CREATE INDEX i_rosteru_sh_username ON rosterusers USING btree (server_host, username);
+-- CREATE INDEX i_rosteru_sh_jid ON rosterusers USING btree (server_host, jid);
+-- ALTER TABLE rosterusers ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE rostergroups ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- DROP INDEX pk_rosterg_user_jid;
+-- CREATE INDEX i_rosterg_sh_user_jid ON rostergroups USING btree (server_host, username, jid);
+-- ALTER TABLE rostergroups ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE sr_group ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE sr_group ADD PRIMARY KEY (server_host, name);
+-- ALTER TABLE sr_group ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE sr_user ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- DROP INDEX i_sr_user_jid_grp;
+-- DROP INDEX i_sr_user_jid;
+-- DROP INDEX i_sr_user_grp;
+-- ALTER TABLE sr_user ADD PRIMARY KEY (server_host, jid, grp);
+-- CREATE INDEX i_sr_user_sh_jid ON sr_user USING btree (server_host, jid);
+-- CREATE INDEX i_sr_user_sh_grp ON sr_user USING btree (server_host, grp);
+-- ALTER TABLE sr_user ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE spool ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- DROP INDEX i_despool;
+-- CREATE INDEX i_spool_sh_username ON spool USING btree (server_host, username);
+-- ALTER TABLE spool ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE archive ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- DROP INDEX i_username;
+-- DROP INDEX i_username_timestamp;
+-- DROP INDEX i_timestamp;
+-- DROP INDEX i_peer;
+-- DROP INDEX i_bare_peer;
+-- CREATE INDEX i_archive_sh_username_timestamp ON archive USING btree (server_host, username, timestamp);
+-- CREATE INDEX i_archive_sh_timestamp ON archive USING btree (server_host, timestamp);
+-- CREATE INDEX i_archive_sh_peer ON archive USING btree (server_host, peer);
+-- CREATE INDEX i_archive_sh_bare_peer ON archive USING btree (server_host, bare_peer);
+-- ALTER TABLE archive ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE archive_prefs ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE archive_prefs DROP CONSTRAINT archive_prefs_pkey;
+-- ALTER TABLE archive_prefs ADD PRIMARY KEY (server_host, username);
+-- ALTER TABLE archive_prefs ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE vcard ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE vcard DROP CONSTRAINT vcard_pkey;
+-- ALTER TABLE vcard ADD PRIMARY KEY (server_host, username);
+-- ALTER TABLE vcard ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE vcard_search ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE vcard_search DROP CONSTRAINT vcard_search_pkey;
+-- DROP INDEX i_vcard_search_lfn;
+-- DROP INDEX i_vcard_search_lfamily;
+-- DROP INDEX i_vcard_search_lgiven;
+-- DROP INDEX i_vcard_search_lmiddle;
+-- DROP INDEX i_vcard_search_lnickname;
+-- DROP INDEX i_vcard_search_lbday;
+-- DROP INDEX i_vcard_search_lctry;
+-- DROP INDEX i_vcard_search_llocality;
+-- DROP INDEX i_vcard_search_lemail;
+-- DROP INDEX i_vcard_search_lorgname;
+-- DROP INDEX i_vcard_search_lorgunit;
+-- ALTER TABLE vcard_search ADD PRIMARY KEY (server_host, username);
+-- CREATE INDEX i_vcard_search_sh_lfn ON vcard_search(server_host, lfn);
+-- CREATE INDEX i_vcard_search_sh_lfamily ON vcard_search(server_host, lfamily);
+-- CREATE INDEX i_vcard_search_sh_lgiven ON vcard_search(server_host, lgiven);
+-- CREATE INDEX i_vcard_search_sh_lmiddle ON vcard_search(server_host, lmiddle);
+-- CREATE INDEX i_vcard_search_sh_lnickname ON vcard_search(server_host, lnickname);
+-- CREATE INDEX i_vcard_search_sh_lbday ON vcard_search(server_host, lbday);
+-- CREATE INDEX i_vcard_search_sh_lctry ON vcard_search(server_host, lctry);
+-- CREATE INDEX i_vcard_search_sh_llocality ON vcard_search(server_host, llocality);
+-- CREATE INDEX i_vcard_search_sh_lemail ON vcard_search(server_host, lemail);
+-- CREATE INDEX i_vcard_search_sh_lorgname ON vcard_search(server_host, lorgname);
+-- CREATE INDEX i_vcard_search_sh_lorgunit ON vcard_search(server_host, lorgunit);
+-- ALTER TABLE vcard_search ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE privacy_default_list ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE privacy_default_list DROP CONSTRAINT privacy_default_list_pkey;
+-- ALTER TABLE privacy_default_list ADD PRIMARY KEY (server_host, username);
+-- ALTER TABLE privacy_default_list ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE privacy_list ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- DROP INDEX i_privacy_list_username;
+-- DROP INDEX i_privacy_list_username_name;
+-- CREATE INDEX i_privacy_list_sh_username ON privacy_list USING btree (server_host, username);
+-- CREATE UNIQUE INDEX i_privacy_list_sh_username_name ON privacy_list USING btree (server_host, username, name);
+-- ALTER TABLE privacy_list ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE private_storage ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- DROP INDEX i_private_storage_username;
+-- DROP INDEX i_private_storage_username_namespace;
+-- ALTER TABLE private_storage ADD PRIMARY KEY (server_host, username, namespace);
+-- CREATE INDEX i_private_storage_sh_username ON private_storage USING btree (server_host, username);
+-- ALTER TABLE private_storage ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE roster_version ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE roster_version DROP CONSTRAINT roster_version_pkey;
+-- ALTER TABLE roster_version ADD PRIMARY KEY (server_host, username);
+-- ALTER TABLE roster_version ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE muc_room ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE muc_room ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE muc_registered ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE muc_registered ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE muc_online_room ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE muc_online_room ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE muc_online_users ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE muc_online_users ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE irc_custom ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE irc_custom ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE motd ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- ALTER TABLE motd DROP CONSTRAINT motd_pkey;
+-- ALTER TABLE motd ADD PRIMARY KEY (server_host, username);
+-- ALTER TABLE motd ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE sm ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- DROP INDEX i_sm_sid;
+-- DROP INDEX i_sm_username;
+-- ALTER TABLE sm ADD PRIMARY KEY (usec, pid);
+-- CREATE INDEX i_sm_sh_username ON sm USING btree (server_host, username);
+-- ALTER TABLE sm ALTER COLUMN server_host DROP DEFAULT;
+
+-- ALTER TABLE carboncopy ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
+-- DROP INDEX i_carboncopy_ur;
+-- DROP INDEX i_carboncopy_user;
+-- ALTER TABLE carboncopy ADD PRIMARY KEY (server_host, username, resource);
+-- CREATE INDEX i_carboncopy_sh_user ON carboncopy USING btree (server_host, username);
+-- ALTER TABLE carboncopy ALTER COLUMN server_host DROP DEFAULT;
+
+
+CREATE TABLE users (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ "password" text NOT NULL,
+ serverkey text NOT NULL DEFAULT '',
+ salt text NOT NULL DEFAULT '',
+ iterationcount integer NOT NULL DEFAULT 0,
+ created_at TIMESTAMP NOT NULL DEFAULT now(),
+ PRIMARY KEY (server_host, username)
+);
+
+-- Add support for SCRAM auth to a database created before ejabberd 16.03:
+-- ALTER TABLE users ADD COLUMN serverkey text NOT NULL DEFAULT '';
+-- ALTER TABLE users ADD COLUMN salt text NOT NULL DEFAULT '';
+-- ALTER TABLE users ADD COLUMN iterationcount integer NOT NULL DEFAULT 0;
+
+CREATE TABLE last (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ seconds text NOT NULL,
+ state text NOT NULL,
+ PRIMARY KEY (server_host, username)
+);
+
+
+CREATE TABLE rosterusers (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ jid text NOT NULL,
+ nick text NOT NULL,
+ subscription character(1) NOT NULL,
+ ask character(1) NOT NULL,
+ askmessage text NOT NULL,
+ server character(1) NOT NULL,
+ subscribe text NOT NULL,
+ "type" text,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE UNIQUE INDEX i_rosteru_sh_user_jid ON rosterusers USING btree (server_host, username, jid);
+CREATE INDEX i_rosteru_sh_username ON rosterusers USING btree (server_host, username);
+CREATE INDEX i_rosteru_sh_jid ON rosterusers USING btree (server_host, jid);
+
+
+CREATE TABLE rostergroups (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ jid text NOT NULL,
+ grp text NOT NULL
+);
+
+CREATE INDEX i_rosterg_sh_user_jid ON rostergroups USING btree (server_host, username, jid);
+
+CREATE TABLE sr_group (
+ name text NOT NULL,
+ server_host text NOT NULL,
+ opts text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now(),
+ PRIMARY KEY (server_host, name)
+);
+
+CREATE TABLE sr_user (
+ jid text NOT NULL,
+ server_host text NOT NULL,
+ grp text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now(),
+ PRIMARY KEY (server_host, jid, grp)
+);
+
+CREATE INDEX i_sr_user_sh_jid ON sr_user USING btree (server_host, jid);
+CREATE INDEX i_sr_user_sh_grp ON sr_user USING btree (server_host, grp);
+
+CREATE TABLE spool (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ xml text NOT NULL,
+ seq SERIAL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE INDEX i_spool_sh_username ON spool USING btree (server_host, username);
+
+CREATE TABLE archive (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ timestamp BIGINT NOT NULL,
+ peer text NOT NULL,
+ bare_peer text NOT NULL,
+ xml text NOT NULL,
+ txt text,
+ id SERIAL,
+ kind text,
+ nick text,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE INDEX i_archive_sh_username_timestamp ON archive USING btree (server_host, username, timestamp);
+CREATE INDEX i_archive_sh_timestamp ON archive USING btree (server_host, timestamp);
+CREATE INDEX i_archive_sh_peer ON archive USING btree (server_host, peer);
+CREATE INDEX i_archive_sh_bare_peer ON archive USING btree (server_host, bare_peer);
+
+CREATE TABLE archive_prefs (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ def text NOT NULL,
+ always text NOT NULL,
+ never text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now(),
+ PRIMARY KEY (server_host, username)
+);
+
+CREATE TABLE vcard (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ vcard text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now(),
+ PRIMARY KEY (server_host, username)
+);
+
+CREATE TABLE vcard_search (
+ username text NOT NULL,
+ lusername text NOT NULL,
+ server_host text NOT NULL,
+ fn text NOT NULL,
+ lfn text NOT NULL,
+ family text NOT NULL,
+ lfamily text NOT NULL,
+ given text NOT NULL,
+ lgiven text NOT NULL,
+ middle text NOT NULL,
+ lmiddle text NOT NULL,
+ nickname text NOT NULL,
+ lnickname text NOT NULL,
+ bday text NOT NULL,
+ lbday text NOT NULL,
+ ctry text NOT NULL,
+ lctry text NOT NULL,
+ locality text NOT NULL,
+ llocality text NOT NULL,
+ email text NOT NULL,
+ lemail text NOT NULL,
+ orgname text NOT NULL,
+ lorgname text NOT NULL,
+ orgunit text NOT NULL,
+ lorgunit text NOT NULL,
+ PRIMARY KEY (server_host, username)
+);
+
+CREATE INDEX i_vcard_search_sh_lfn ON vcard_search(server_host, lfn);
+CREATE INDEX i_vcard_search_sh_lfamily ON vcard_search(server_host, lfamily);
+CREATE INDEX i_vcard_search_sh_lgiven ON vcard_search(server_host, lgiven);
+CREATE INDEX i_vcard_search_sh_lmiddle ON vcard_search(server_host, lmiddle);
+CREATE INDEX i_vcard_search_sh_lnickname ON vcard_search(server_host, lnickname);
+CREATE INDEX i_vcard_search_sh_lbday ON vcard_search(server_host, lbday);
+CREATE INDEX i_vcard_search_sh_lctry ON vcard_search(server_host, lctry);
+CREATE INDEX i_vcard_search_sh_llocality ON vcard_search(server_host, llocality);
+CREATE INDEX i_vcard_search_sh_lemail ON vcard_search(server_host, lemail);
+CREATE INDEX i_vcard_search_sh_lorgname ON vcard_search(server_host, lorgname);
+CREATE INDEX i_vcard_search_sh_lorgunit ON vcard_search(server_host, lorgunit);
+
+CREATE TABLE privacy_default_list (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ name text NOT NULL,
+ PRIMARY KEY (server_host, username)
+);
+
+CREATE TABLE privacy_list (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ name text NOT NULL,
+ id SERIAL UNIQUE,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE INDEX i_privacy_list_sh_username ON privacy_list USING btree (server_host, username);
+CREATE UNIQUE INDEX i_privacy_list_sh_username_name ON privacy_list USING btree (server_host, username, name);
+
+CREATE TABLE privacy_list_data (
+ id bigint REFERENCES privacy_list(id) ON DELETE CASCADE,
+ t character(1) NOT NULL,
+ value text NOT NULL,
+ action character(1) NOT NULL,
+ ord NUMERIC NOT NULL,
+ match_all boolean NOT NULL,
+ match_iq boolean NOT NULL,
+ match_message boolean NOT NULL,
+ match_presence_in boolean NOT NULL,
+ match_presence_out boolean NOT NULL
+);
+
+CREATE INDEX i_privacy_list_data_id ON privacy_list_data USING btree (id);
+
+CREATE TABLE private_storage (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ namespace text NOT NULL,
+ data text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now(),
+ PRIMARY KEY (server_host, username, namespace)
+);
+
+CREATE INDEX i_private_storage_sh_username ON private_storage USING btree (server_host, username);
+
+
+CREATE TABLE roster_version (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ version text NOT NULL,
+ PRIMARY KEY (server_host, username)
+);
+
+-- To update from 0.9.8:
+-- CREATE SEQUENCE spool_seq_seq;
+-- ALTER TABLE spool ADD COLUMN seq integer;
+-- ALTER TABLE spool ALTER COLUMN seq SET DEFAULT nextval('spool_seq_seq');
+-- UPDATE spool SET seq = DEFAULT;
+-- ALTER TABLE spool ALTER COLUMN seq SET NOT NULL;
+
+-- To update from 1.x:
+-- ALTER TABLE rosterusers ADD COLUMN askmessage text;
+-- UPDATE rosterusers SET askmessage = '';
+-- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL;
+
+CREATE TABLE pubsub_node (
+ host text NOT NULL,
+ node text NOT NULL,
+ parent text NOT NULL DEFAULT '',
+ "type" text NOT NULL,
+ nodeid SERIAL UNIQUE
+);
+CREATE INDEX i_pubsub_node_parent ON pubsub_node USING btree (parent);
+CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node USING btree (host, node);
+
+CREATE TABLE pubsub_node_option (
+ nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+ name text NOT NULL,
+ val text NOT NULL
+);
+CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option USING btree (nodeid);
+
+CREATE TABLE pubsub_node_owner (
+ nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+ owner text NOT NULL
+);
+CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner USING btree (nodeid);
+
+CREATE TABLE pubsub_state (
+ nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+ jid text NOT NULL,
+ affiliation character(1),
+ subscriptions text NOT NULL DEFAULT '',
+ stateid SERIAL UNIQUE
+);
+CREATE INDEX i_pubsub_state_jid ON pubsub_state USING btree (jid);
+CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state USING btree (nodeid, jid);
+
+CREATE TABLE pubsub_item (
+ nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+ itemid text NOT NULL,
+ publisher text NOT NULL,
+ creation text NOT NULL,
+ modification text NOT NULL,
+ payload text NOT NULL DEFAULT ''
+);
+CREATE INDEX i_pubsub_item_itemid ON pubsub_item USING btree (itemid);
+CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item USING btree (nodeid, itemid);
+
+CREATE TABLE pubsub_subscription_opt (
+ subid text NOT NULL,
+ opt_name varchar(32),
+ opt_value text NOT NULL
+);
+CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt USING btree (subid, opt_name);
+
+CREATE TABLE muc_room (
+ name text NOT NULL,
+ host text NOT NULL,
+ server_host text NOT NULL,
+ opts text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE UNIQUE INDEX i_muc_room_name_host ON muc_room USING btree (name, host);
+
+CREATE TABLE muc_registered (
+ jid text NOT NULL,
+ host text NOT NULL,
+ server_host text NOT NULL,
+ nick text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE INDEX i_muc_registered_nick ON muc_registered USING btree (nick);
+CREATE UNIQUE INDEX i_muc_registered_jid_host ON muc_registered USING btree (jid, host);
+
+CREATE TABLE muc_online_room (
+ name text NOT NULL,
+ host text NOT NULL,
+ server_host text NOT NULL,
+ node text NOT NULL,
+ pid text NOT NULL
+);
+
+CREATE UNIQUE INDEX i_muc_online_room_name_host ON muc_online_room USING btree (name, host);
+
+CREATE TABLE muc_online_users (
+ username text NOT NULL,
+ server text NOT NULL,
+ resource text NOT NULL,
+ name text NOT NULL,
+ host text NOT NULL,
+ server_host text NOT NULL,
+ node text NOT NULL
+);
+
+CREATE UNIQUE INDEX i_muc_online_users ON muc_online_users USING btree (username, server, resource, name, host);
+CREATE INDEX i_muc_online_users_us ON muc_online_users USING btree (username, server);
+
+CREATE TABLE muc_room_subscribers (
+ room text NOT NULL,
+ host text NOT NULL,
+ jid text NOT NULL,
+ nick text NOT NULL,
+ nodes text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE INDEX i_muc_room_subscribers_host_jid ON muc_room_subscribers USING btree (host, jid);
+CREATE UNIQUE INDEX i_muc_room_subscribers_host_room_jid ON muc_room_subscribers USING btree (host, room, jid);
+
+CREATE TABLE irc_custom (
+ jid text NOT NULL,
+ host text NOT NULL,
+ server_host text NOT NULL,
+ data text NOT NULL,
+ created_at TIMESTAMP NOT NULL DEFAULT now()
+);
+
+CREATE UNIQUE INDEX i_irc_custom_jid_host ON irc_custom USING btree (jid, host);
+
+CREATE TABLE motd (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ xml text,
+ created_at TIMESTAMP NOT NULL DEFAULT now(),
+ PRIMARY KEY (server_host, username)
+);
+
+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);
+
+CREATE TABLE sm (
+ usec bigint NOT NULL,
+ pid text NOT NULL,
+ node text NOT NULL,
+ username text NOT NULL,
+ resource text NOT NULL,
+ priority text NOT NULL,
+ info text NOT NULL,
+ PRIMARY KEY (usec, pid)
+);
+
+CREATE INDEX i_sm_node ON sm USING btree (node);
+CREATE INDEX i_sm_sh_username ON sm USING btree (server_host, username);
+
+CREATE TABLE oauth_token (
+ token text NOT NULL,
+ jid text NOT NULL,
+ scope text NOT NULL,
+ expire bigint NOT NULL
+);
+
+CREATE UNIQUE INDEX i_oauth_token_token ON oauth_token USING btree (token);
+
+CREATE TABLE route (
+ domain text NOT NULL,
+ server_host text NOT NULL,
+ node text NOT NULL,
+ pid text NOT NULL,
+ local_hint text NOT NULL
+);
+
+CREATE UNIQUE INDEX i_route ON route USING btree (domain, server_host, node, pid);
+CREATE INDEX i_route_domain ON route USING btree (domain);
+
+CREATE TABLE bosh (
+ sid text NOT NULL,
+ node text NOT NULL,
+ pid text NOT NULL
+);
+
+CREATE UNIQUE INDEX i_bosh_sid ON bosh USING btree (sid);
+
+CREATE TABLE carboncopy (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ resource text NOT NULL,
+ namespace text NOT NULL,
+ node text NOT NULL,
+ PRIMARY KEY (server_host, username, resource)
+);
+
+CREATE INDEX i_carboncopy_sh_user ON carboncopy USING btree (server_host, username);
+
+CREATE TABLE proxy65 (
+ sid text NOT NULL,
+ pid_t text NOT NULL,
+ pid_i text NOT NULL,
+ node_t text NOT NULL,
+ node_i text NOT NULL,
+ jid_i text NOT NULL
+);
+
+CREATE UNIQUE INDEX i_proxy65_sid ON proxy65 USING btree (sid);
+CREATE INDEX i_proxy65_jid ON proxy65 USING btree (jid_i);
+
+CREATE TABLE push_session (
+ username text NOT NULL,
+ server_host text NOT NULL,
+ timestamp bigint NOT NULL,
+ service text NOT NULL,
+ node text NOT NULL,
+ xml text NOT NULL,
+ PRIMARY KEY (server_host, username, timestamp)
+);
+
+CREATE UNIQUE INDEX i_push_session_susn ON push_session USING btree (server_host, username, service, node);
diff --git a/src/ejabberd_auth_mnesia.erl b/src/ejabberd_auth_mnesia.erl
index 14b8e5a2d..7705f62e1 100644
--- a/src/ejabberd_auth_mnesia.erl
+++ b/src/ejabberd_auth_mnesia.erl
@@ -40,7 +40,6 @@
-include("ejabberd.hrl").
-include("logger.hrl").
--include("ejabberd_sql_pt.hrl").
-include("ejabberd_auth.hrl").
-record(reg_users_counter, {vhost = <<"">> :: binary(),
diff --git a/src/ejabberd_auth_riak.erl b/src/ejabberd_auth_riak.erl
index 37bd3daf4..3cdb74258 100644
--- a/src/ejabberd_auth_riak.erl
+++ b/src/ejabberd_auth_riak.erl
@@ -106,9 +106,12 @@ export(_Server) ->
[{passwd,
fun(Host, #passwd{us = {LUser, LServer}, password = Password})
when LServer == Host ->
- [?SQL("delete from users where username=%(LUser)s;"),
- ?SQL("insert into users(username, password) "
- "values (%(LUser)s, %(Password)s);")];
+ [?SQL("delete from users where username=%(LUser)s and %(LServer)H;"),
+ ?SQL_INSERT(
+ "users",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "password=%(Password)s"])];
(_Host, _R) ->
[]
end}].
diff --git a/src/ejabberd_auth_sql.erl b/src/ejabberd_auth_sql.erl
index 15e5076c9..3f328c4a1 100644
--- a/src/ejabberd_auth_sql.erl
+++ b/src/ejabberd_auth_sql.erl
@@ -61,11 +61,11 @@ set_password(User, Server, Password) ->
F = fun() ->
if is_record(Password, scram) ->
set_password_scram_t(
- User,
+ User, Server,
Password#scram.storedkey, Password#scram.serverkey,
Password#scram.salt, Password#scram.iterationcount);
true ->
- set_password_t(User, Password)
+ set_password_t(User, Server, Password)
end
end,
case ejabberd_sql:sql_transaction(Server, F) of
@@ -133,20 +133,22 @@ remove_user(User, Server) ->
-define(BATCH_SIZE, 1000).
-set_password_scram_t(LUser,
+set_password_scram_t(LUser, LServer,
StoredKey, ServerKey, Salt, IterationCount) ->
?SQL_UPSERT_T(
"users",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"password=%(StoredKey)s",
"serverkey=%(ServerKey)s",
"salt=%(Salt)s",
"iterationcount=%(IterationCount)d"]).
-set_password_t(LUser, Password) ->
+set_password_t(LUser, LServer, Password) ->
?SQL_UPSERT_T(
"users",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"password=%(Password)s"]).
get_password_scram(LServer, LUser) ->
@@ -154,32 +156,39 @@ get_password_scram(LServer, LUser) ->
LServer,
?SQL("select @(password)s, @(serverkey)s, @(salt)s, @(iterationcount)d"
" from users"
- " where username=%(LUser)s")).
+ " where username=%(LUser)s and %(LServer)H")).
add_user_scram(LServer, LUser,
StoredKey, ServerKey, Salt, IterationCount) ->
ejabberd_sql:sql_query(
LServer,
- ?SQL("insert into users(username, password, serverkey, salt, "
- "iterationcount) "
- "values (%(LUser)s, %(StoredKey)s, %(ServerKey)s,"
- " %(Salt)s, %(IterationCount)d)")).
+ ?SQL_INSERT(
+ "users",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "password=%(StoredKey)s",
+ "serverkey=%(ServerKey)s",
+ "salt=%(Salt)s",
+ "iterationcount=%(IterationCount)d"])).
add_user(LServer, LUser, Password) ->
ejabberd_sql:sql_query(
LServer,
- ?SQL("insert into users(username, password) "
- "values (%(LUser)s, %(Password)s)")).
+ ?SQL_INSERT(
+ "users",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "password=%(Password)s"])).
del_user(LServer, LUser) ->
ejabberd_sql:sql_query(
LServer,
- ?SQL("delete from users where username=%(LUser)s")).
+ ?SQL("delete from users where username=%(LUser)s and %(LServer)H")).
list_users(LServer, []) ->
ejabberd_sql:sql_query(
LServer,
- ?SQL("select @(username)s from users"));
+ ?SQL("select @(username)s from users where %(LServer)H"));
list_users(LServer, [{from, Start}, {to, End}])
when is_integer(Start) and is_integer(End) ->
list_users(LServer,
@@ -196,6 +205,7 @@ list_users(LServer, [{limit, Limit}, {offset, Offset}])
ejabberd_sql:sql_query(
LServer,
?SQL("select @(username)s from users "
+ "where %(LServer)H "
"order by username "
"limit %(Limit)d offset %(Offset)d"));
list_users(LServer,
@@ -207,7 +217,7 @@ list_users(LServer,
ejabberd_sql:sql_query(
LServer,
?SQL("select @(username)s from users "
- "where username like %(SPrefix2)s escape '^' "
+ "where username like %(SPrefix2)s escape '^' and %(LServer)H "
"order by username "
"limit %(Limit)d offset %(Offset)d")).
@@ -224,11 +234,11 @@ users_number(LServer) ->
" where oid = 'users'::regclass::oid"));
_ ->
ejabberd_sql:sql_query_t(
- ?SQL("select @(count(*))d from users"))
+ ?SQL("select @(count(*))d from users where %(LServer)H"))
end;
(_Type, _) ->
ejabberd_sql:sql_query_t(
- ?SQL("select @(count(*))d from users"))
+ ?SQL("select @(count(*))d from users where %(LServer)H"))
end).
users_number(LServer, [{prefix, Prefix}])
@@ -238,7 +248,7 @@ users_number(LServer, [{prefix, Prefix}])
ejabberd_sql:sql_query(
LServer,
?SQL("select @(count(*))d from users "
- "where username like %(SPrefix2)s escape '^'"));
+ "where username like %(SPrefix2)s escape '^' and %(LServer)H"));
users_number(LServer, []) ->
users_number(LServer).
@@ -254,7 +264,7 @@ convert_to_scram(Server) ->
case ejabberd_sql:sql_query_t(
?SQL("select @(username)s, @(password)s"
" from users"
- " where iterationcount=0"
+ " where iterationcount=0 and %(LServer)H"
" limit %(BatchSize)d")) of
{selected, []} ->
ok;
@@ -270,7 +280,7 @@ convert_to_scram(Server) ->
_ ->
Scram = ejabberd_auth:password_to_scram(Password),
set_password_scram_t(
- LUser,
+ LUser, LServer,
Scram#scram.storedkey,
Scram#scram.serverkey,
Scram#scram.salt,
@@ -294,20 +304,27 @@ export(_Server) ->
fun(Host, #passwd{us = {LUser, LServer}, password = Password})
when LServer == Host,
is_binary(Password) ->
- [?SQL("delete from users where username=%(LUser)s;"),
- ?SQL("insert into users(username, password) "
- "values (%(LUser)s, %(Password)s);")];
+ [?SQL("delete from users where username=%(LUser)s and %(LServer)H;"),
+ ?SQL_INSERT(
+ "users",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "password=%(Password)s"])];
(Host, #passwd{us = {LUser, LServer}, password = #scram{} = Scram})
when LServer == Host ->
StoredKey = Scram#scram.storedkey,
ServerKey = Scram#scram.serverkey,
Salt = Scram#scram.salt,
IterationCount = Scram#scram.iterationcount,
- [?SQL("delete from users where username=%(LUser)s;"),
- ?SQL("insert into users(username, password, serverkey, salt, "
- "iterationcount) "
- "values (%(LUser)s, %(StoredKey)s, %(ServerKey)s,"
- " %(Salt)s, %(IterationCount)d);")];
+ [?SQL("delete from users where username=%(LUser)s and %(LServer)H;"),
+ ?SQL_INSERT(
+ "users",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "password=%(StoredKey)s",
+ "serverkey=%(ServerKey)s",
+ "salt=%(Salt)s",
+ "iterationcount=%(IterationCount)d"])];
(_Host, _R) ->
[]
end}].
diff --git a/src/ejabberd_sm_sql.erl b/src/ejabberd_sm_sql.erl
index 2b94064ef..55e21040b 100644
--- a/src/ejabberd_sm_sql.erl
+++ b/src/ejabberd_sm_sql.erl
@@ -74,6 +74,7 @@ set_session(#session{sid = {Now, Pid}, usr = {U, LServer, R},
"!pid=%(PidS)s",
"node=%(Node)s",
"username=%(U)s",
+ "server_host=%(LServer)s",
"resource=%(R)s",
"priority=%(PrioS)s",
"info=%(InfoS)s"]) of
@@ -107,7 +108,8 @@ get_sessions(LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(usec)d, @(pid)s, @(node)s, @(username)s,"
- " @(resource)s, @(priority)s, @(info)s from sm")) of
+ " @(resource)s, @(priority)s, @(info)s from sm"
+ " where %(LServer)H")) of
{selected, Rows} ->
lists:flatmap(
fun(Row) ->
@@ -125,7 +127,7 @@ get_sessions(LUser, LServer) ->
LServer,
?SQL("select @(usec)d, @(pid)s, @(node)s, @(username)s,"
" @(resource)s, @(priority)s, @(info)s from sm"
- " where username=%(LUser)s")) of
+ " where username=%(LUser)s and %(LServer)H")) of
{selected, Rows} ->
{ok, lists:flatmap(
fun(Row) ->
diff --git a/src/ejabberd_sql_pt.erl b/src/ejabberd_sql_pt.erl
index e90947a5f..197e5ee6d 100644
--- a/src/ejabberd_sql_pt.erl
+++ b/src/ejabberd_sql_pt.erl
@@ -26,7 +26,7 @@
-module(ejabberd_sql_pt).
%% API
--export([parse_transform/2]).
+-export([parse_transform/2, format_error/1]).
-export([parse/2]).
@@ -39,7 +39,8 @@
args = [],
res = [],
res_vars = [],
- res_pos = 0}).
+ res_pos = 0,
+ server_host_used = false}).
-define(QUERY_RECORD, "sql_query").
@@ -48,6 +49,12 @@
-define(MOD, sql__module_).
+-ifdef(NEW_SQL_SCHEMA).
+-define(USE_NEW_SCHEMA, true).
+-else.
+-define(USE_NEW_SCHEMA, false).
+-endif.
+
%%====================================================================
%% API
%%====================================================================
@@ -57,11 +64,14 @@
%%--------------------------------------------------------------------
parse_transform(AST, _Options) ->
%io:format("PT: ~p~nOpts: ~p~n", [AST, Options]),
+ put(warnings, []),
NewAST = top_transform(AST),
%io:format("NewPT: ~p~n", [NewAST]),
- NewAST.
+ NewAST ++ get(warnings).
+format_error(no_server_host) ->
+ "server_host field is not used".
%%====================================================================
%% Internal functions
@@ -80,6 +90,12 @@ transform(Form) ->
S = erl_syntax:string_value(Arg),
Pos = erl_syntax:get_pos(Arg),
ParseRes = parse(S, Pos),
+ if
+ ParseRes#state.server_host_used ->
+ ok;
+ true ->
+ add_warning(Pos, no_server_host)
+ end,
set_pos(make_sql_query(ParseRes), Pos);
_ ->
throw({error, erl_syntax:get_pos(Form),
@@ -101,8 +117,17 @@ transform(Form) ->
parse_upsert(
erl_syntax:list_elements(FieldsArg)),
Pos = erl_syntax:get_pos(Form),
+ case lists:keymember(
+ "server_host", 1, ParseRes) of
+ true ->
+ ok;
+ false ->
+ add_warning(Pos, no_server_host)
+ end,
+ ParseRes2 =
+ filter_upsert_sh(Table, ParseRes),
set_pos(
- make_sql_upsert(Table, ParseRes, Pos),
+ make_sql_upsert(Table, ParseRes2, Pos),
Pos);
_ ->
throw({error, erl_syntax:get_pos(Form),
@@ -113,6 +138,38 @@ transform(Form) ->
throw({error, erl_syntax:get_pos(Form),
"wrong number of ?SQL_UPSERT args"})
end;
+ {?SQL_INSERT_MARK, 2} ->
+ case erl_syntax:application_arguments(Form) of
+ [TableArg, FieldsArg] ->
+ case {erl_syntax:type(TableArg),
+ erl_syntax:is_proper_list(FieldsArg)}of
+ {string, true} ->
+ Table = erl_syntax:string_value(TableArg),
+ ParseRes =
+ parse_insert(
+ erl_syntax:list_elements(FieldsArg)),
+ Pos = erl_syntax:get_pos(Form),
+ case lists:keymember(
+ "server_host", 1, ParseRes) of
+ true ->
+ ok;
+ false ->
+ add_warning(Pos, no_server_host)
+ end,
+ ParseRes2 =
+ filter_upsert_sh(Table, ParseRes),
+ set_pos(
+ make_sql_insert(Table, ParseRes2),
+ Pos);
+ _ ->
+ throw({error, erl_syntax:get_pos(Form),
+ "?SQL_INSERT arguments must be "
+ "a constant string and a list"})
+ end;
+ _ ->
+ throw({error, erl_syntax:get_pos(Form),
+ "wrong number of ?SQL_INSERT args"})
+ end;
_ ->
Form
end;
@@ -168,7 +225,7 @@ parse1([], Acc, State) ->
};
parse1([$@, $( | S], Acc, State) ->
State1 = append_string(lists:reverse(Acc), State),
- {Name, Type, S1, State2} = parse_name(S, State1),
+ {Name, Type, S1, State2} = parse_name(S, false, State1),
Var = "__V" ++ integer_to_list(State2#state.res_pos),
EVar = erl_syntax:variable(Var),
Convert =
@@ -192,21 +249,43 @@ parse1([$@, $( | S], Acc, State) ->
parse1(S1, [], State4);
parse1([$%, $( | S], Acc, State) ->
State1 = append_string(lists:reverse(Acc), State),
- {Name, Type, S1, State2} = parse_name(S, State1),
+ {Name, Type, S1, State2} = parse_name(S, true, State1),
Var = State2#state.param_pos,
- Convert =
- erl_syntax:application(
- erl_syntax:record_access(
- erl_syntax:variable(?ESCAPE_VAR),
- erl_syntax:atom(?ESCAPE_RECORD),
- erl_syntax:atom(Type)),
- [erl_syntax:variable(Name)]),
- State3 = State2,
State4 =
- State3#state{'query' = [{var, Var} | State3#state.'query'],
- args = [Convert | State3#state.args],
- params = [Var | State3#state.params],
- param_pos = State3#state.param_pos + 1},
+ case Type of
+ host ->
+ State3 = State2#state{server_host_used = true},
+ case ?USE_NEW_SCHEMA of
+ true ->
+ Convert =
+ erl_syntax:application(
+ erl_syntax:record_access(
+ erl_syntax:variable(?ESCAPE_VAR),
+ erl_syntax:atom(?ESCAPE_RECORD),
+ erl_syntax:atom(string)),
+ [erl_syntax:variable(Name)]),
+ State3#state{'query' = [{var, Var},
+ {str, "server_host="} |
+ State3#state.'query'],
+ args = [Convert | State3#state.args],
+ params = [Var | State3#state.params],
+ param_pos = State3#state.param_pos + 1};
+ false ->
+ append_string("0=0", State3)
+ end;
+ _ ->
+ Convert =
+ erl_syntax:application(
+ erl_syntax:record_access(
+ erl_syntax:variable(?ESCAPE_VAR),
+ erl_syntax:atom(?ESCAPE_RECORD),
+ erl_syntax:atom(Type)),
+ [erl_syntax:variable(Name)]),
+ State2#state{'query' = [{var, Var} | State2#state.'query'],
+ args = [Convert | State2#state.args],
+ params = [Var | State2#state.params],
+ param_pos = State2#state.param_pos + 1}
+ end,
parse1(S1, [], State4);
parse1([C | S], Acc, State) ->
parse1(S, [C | Acc], State).
@@ -216,32 +295,33 @@ append_string([], State) ->
append_string(S, State) ->
State#state{query = [{str, S} | State#state.query]}.
-parse_name(S, State) ->
- parse_name(S, [], 0, State).
+parse_name(S, IsArg, State) ->
+ parse_name(S, [], 0, IsArg, State).
-parse_name([], _Acc, _Depth, State) ->
+parse_name([], _Acc, _Depth, _IsArg, State) ->
throw({error, State#state.loc,
"expected ')', found end of string"});
-parse_name([$), T | S], Acc, 0, State) ->
+parse_name([$), T | S], Acc, 0, IsArg, State) ->
Type =
case T of
$d -> integer;
$s -> string;
$b -> boolean;
+ $H when IsArg -> host;
_ ->
throw({error, State#state.loc,
["unknown type specifier '", T, "'"]})
end,
{lists:reverse(Acc), Type, S, State};
-parse_name([$)], _Acc, 0, State) ->
+parse_name([$)], _Acc, 0, _IsArg, State) ->
throw({error, State#state.loc,
"expected type specifier, found end of string"});
-parse_name([$( = C | S], Acc, Depth, State) ->
- parse_name(S, [C | Acc], Depth + 1, State);
-parse_name([$) = C | S], Acc, Depth, State) ->
- parse_name(S, [C | Acc], Depth - 1, State);
-parse_name([C | S], Acc, Depth, State) ->
- parse_name(S, [C | Acc], Depth, State).
+parse_name([$( = C | S], Acc, Depth, IsArg, State) ->
+ parse_name(S, [C | Acc], Depth + 1, IsArg, State);
+parse_name([$) = C | S], Acc, Depth, IsArg, State) ->
+ parse_name(S, [C | Acc], Depth - 1, IsArg, State);
+parse_name([C | S], Acc, Depth, IsArg, State) ->
+ parse_name(S, [C | Acc], Depth, IsArg, State).
make_var(V) ->
@@ -444,7 +524,7 @@ make_sql_upsert_insert(Table, ParseRes) ->
join_states(Fields, ", "),
#state{'query' = [{str, ") VALUES ("}]},
join_states(Vals, ", "),
- #state{'query' = [{str, ")"}]}
+ #state{'query' = [{str, ");"}]}
]),
State.
@@ -498,6 +578,49 @@ check_upsert(ParseRes, Pos) ->
ok.
+parse_insert(Fields) ->
+ {Fs, _} =
+ lists:foldr(
+ fun(F, {Acc, Param}) ->
+ case erl_syntax:type(F) of
+ string ->
+ V = erl_syntax:string_value(F),
+ {_, _, State} = Res =
+ parse_insert_field(
+ V, Param, erl_syntax:get_pos(F)),
+ {[Res | Acc], State#state.param_pos};
+ _ ->
+ throw({error, erl_syntax:get_pos(F),
+ "?SQL_INSERT field must be "
+ "a constant string"})
+ end
+ end, {[], 0}, Fields),
+ Fs.
+
+parse_insert_field([$! | _S], _ParamPos, Loc) ->
+ throw({error, Loc,
+ "?SQL_INSERT fields must not start with \"!\""});
+parse_insert_field([$- | _S], _ParamPos, Loc) ->
+ throw({error, Loc,
+ "?SQL_INSERT fields must not start with \"-\""});
+parse_insert_field(S, ParamPos, Loc) ->
+ {Name, ParseState} = parse_insert_field1(S, [], ParamPos, Loc),
+ {Name, {true}, ParseState}.
+
+parse_insert_field1([], _Acc, _ParamPos, Loc) ->
+ throw({error, Loc,
+ "?SQL_INSERT fields must have the "
+ "following form: \"name=value\""});
+parse_insert_field1([$= | S], Acc, ParamPos, Loc) ->
+ {lists:reverse(Acc), parse(S, ParamPos, Loc)};
+parse_insert_field1([C | S], Acc, ParamPos, Loc) ->
+ parse_insert_field1(S, [C | Acc], ParamPos, Loc).
+
+
+make_sql_insert(Table, ParseRes) ->
+ make_sql_query(make_sql_upsert_insert(Table, ParseRes)).
+
+
concat_states(States) ->
lists:foldr(
fun(ST11, ST2) ->
@@ -566,3 +689,20 @@ set_pos(Tree, Pos) ->
_ -> Node
end
end, Tree).
+
+filter_upsert_sh(Table, ParseRes) ->
+ case ?USE_NEW_SCHEMA of
+ true ->
+ ParseRes;
+ false ->
+ lists:filter(
+ fun({Field, _Match, _ST}) ->
+ Field /= "server_host" orelse Table == "route"
+ end, ParseRes)
+ end.
+
+add_warning(Pos, Warning) ->
+ Marker = erl_syntax:revert(
+ erl_syntax:warning_marker({Pos, ?MODULE, Warning})),
+ put(warnings, [Marker | get(warnings)]),
+ ok.
diff --git a/src/mod_admin_update_sql.erl b/src/mod_admin_update_sql.erl
new file mode 100644
index 000000000..2f105d97d
--- /dev/null
+++ b/src/mod_admin_update_sql.erl
@@ -0,0 +1,365 @@
+%%%-------------------------------------------------------------------
+%%% 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-2017 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_opt_type/1,
+ get_commands_spec/0, depends/2]).
+
+% Commands API
+-export([update_sql/0]).
+
+
+-include("logger.hrl").
+-include("ejabberd.hrl").
+-include("ejabberd_commands.hrl").
+-include("xmpp.hrl").
+-include("ejabberd_sql_pt.hrl").
+
+%%%
+%%% gen_mod
+%%%
+
+start(_Host, _Opts) ->
+ ejabberd_commands:register_commands(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 SQL 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:get_pids(Host) of
+ [] ->
+ ok;
+ _ ->
+ update_sql(Host)
+ end
+ end, ?MYHOSTS),
+ ok.
+
+-record(state, {host :: binary(),
+ dbtype :: mysql | pgsql | sqlite | mssql | odbc,
+ escape}).
+
+update_sql(Host) ->
+ LHost = jid:nameprep(Host),
+ DBType = ejabberd_config:get_option({sql_type, LHost}, undefined),
+ IsSupported =
+ case DBType of
+ pgsql -> true;
+ _ -> false
+ end,
+ if
+ not IsSupported ->
+ io:format("Converting ~p DB is not supported~n", [DBType]),
+ error;
+ true ->
+ Escape =
+ case DBType of
+ mssql -> fun ejabberd_sql:standard_escape/1;
+ sqlite -> fun ejabberd_sql:standard_escape/1;
+ _ -> fun ejabberd_sql:escape/1
+ end,
+ State = #state{host = LHost,
+ dbtype = DBType,
+ escape = Escape},
+ update_tables(State)
+ end.
+
+update_tables(State) ->
+ add_sh_column(State, "users"),
+ drop_pkey(State, "users"),
+ add_pkey(State, "users", ["server_host", "username"]),
+ drop_sh_default(State, "users"),
+
+ add_sh_column(State, "last"),
+ drop_pkey(State, "last"),
+ add_pkey(State, "last", ["server_host", "username"]),
+ drop_sh_default(State, "last"),
+
+ add_sh_column(State, "rosterusers"),
+ drop_index(State, "i_rosteru_user_jid"),
+ drop_index(State, "i_rosteru_username"),
+ drop_index(State, "i_rosteru_jid"),
+ create_unique_index(State, "rosterusers", "i_rosteru_sh_user_jid", ["server_host", "username", "jid"]),
+ create_index(State, "rosterusers", "i_rosteru_sh_username", ["server_host", "username"]),
+ create_index(State, "rosterusers", "i_rosteru_sh_jid", ["server_host", "jid"]),
+ drop_sh_default(State, "rosterusers"),
+
+ add_sh_column(State, "rostergroups"),
+ drop_index(State, "pk_rosterg_user_jid"),
+ create_index(State, "rostergroups", "i_rosterg_sh_user_jid", ["server_host", "username", "jid"]),
+ drop_sh_default(State, "rostergroups"),
+
+ add_sh_column(State, "sr_group"),
+ add_pkey(State, "sr_group", ["server_host", "name"]),
+ drop_sh_default(State, "sr_group"),
+
+ add_sh_column(State, "sr_user"),
+ drop_index(State, "i_sr_user_jid_grp"),
+ drop_index(State, "i_sr_user_jid"),
+ drop_index(State, "i_sr_user_grp"),
+ add_pkey(State, "sr_user", ["server_host", "jid", "grp"]),
+ create_index(State, "sr_user", "i_sr_user_sh_jid", ["server_host", "jid"]),
+ create_index(State, "sr_user", "i_sr_user_sh_grp", ["server_host", "grp"]),
+ drop_sh_default(State, "sr_user"),
+
+ add_sh_column(State, "spool"),
+ drop_index(State, "i_despool"),
+ create_index(State, "spool", "i_spool_sh_username", ["server_host", "username"]),
+ drop_sh_default(State, "spool"),
+
+ add_sh_column(State, "archive"),
+ drop_index(State, "i_username"),
+ drop_index(State, "i_username_timestamp"),
+ drop_index(State, "i_timestamp"),
+ drop_index(State, "i_peer"),
+ drop_index(State, "i_bare_peer"),
+ 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_peer", ["server_host", "peer"]),
+ create_index(State, "archive", "i_archive_sh_bare_peer", ["server_host", "bare_peer"]),
+ drop_sh_default(State, "archive"),
+
+ add_sh_column(State, "archive_prefs"),
+ drop_pkey(State, "archive_prefs"),
+ add_pkey(State, "archive_prefs", ["server_host", "username"]),
+ drop_sh_default(State, "archive_prefs"),
+
+ add_sh_column(State, "vcard"),
+ drop_pkey(State, "vcard"),
+ add_pkey(State, "vcard", ["server_host", "username"]),
+ drop_sh_default(State, "vcard"),
+
+ add_sh_column(State, "vcard_search"),
+ drop_pkey(State, "vcard_search"),
+ drop_index(State, "i_vcard_search_lfn"),
+ drop_index(State, "i_vcard_search_lfamily"),
+ drop_index(State, "i_vcard_search_lgiven"),
+ drop_index(State, "i_vcard_search_lmiddle"),
+ drop_index(State, "i_vcard_search_lnickname"),
+ drop_index(State, "i_vcard_search_lbday"),
+ drop_index(State, "i_vcard_search_lctry"),
+ drop_index(State, "i_vcard_search_llocality"),
+ drop_index(State, "i_vcard_search_lemail"),
+ drop_index(State, "i_vcard_search_lorgname"),
+ drop_index(State, "i_vcard_search_lorgunit"),
+ add_pkey(State, "vcard_search", ["server_host", "username"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lfn", ["server_host", "lfn"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lfamily", ["server_host", "lfamily"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lgiven", ["server_host", "lgiven"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lmiddle", ["server_host", "lmiddle"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lnickname", ["server_host", "lnickname"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lbday", ["server_host", "lbday"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lctry", ["server_host", "lctry"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_llocality", ["server_host", "llocality"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lemail", ["server_host", "lemail"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lorgname", ["server_host", "lorgname"]),
+ create_index(State, "vcard_search", "i_vcard_search_sh_lorgunit", ["server_host", "lorgunit"]),
+ drop_sh_default(State, "vcard_search"),
+
+ add_sh_column(State, "privacy_default_list"),
+ drop_pkey(State, "privacy_default_list"),
+ add_pkey(State, "privacy_default_list", ["server_host", "username"]),
+ drop_sh_default(State, "privacy_default_list"),
+
+ add_sh_column(State, "privacy_list"),
+ drop_index(State, "i_privacy_list_username"),
+ drop_index(State, "i_privacy_list_username_name"),
+ create_index(State, "privacy_list", "i_privacy_list_sh_username", ["server_host", "username"]),
+ create_unique_index(State, "privacy_list", "i_privacy_list_sh_username_name", ["server_host", "username", "name"]),
+ drop_sh_default(State, "privacy_list"),
+
+ add_sh_column(State, "private_storage"),
+ drop_index(State, "i_private_storage_username"),
+ drop_index(State, "i_private_storage_username_namespace"),
+ add_pkey(State, "private_storage", ["server_host", "username", "namespace"]),
+ create_index(State, "private_storage", "i_private_storage_sh_username", ["server_host", "username"]),
+ drop_sh_default(State, "private_storage"),
+
+ add_sh_column(State, "roster_version"),
+ drop_pkey(State, "roster_version"),
+ add_pkey(State, "roster_version", ["server_host", "username"]),
+ drop_sh_default(State, "roster_version"),
+
+ add_sh_column(State, "muc_room"),
+ drop_sh_default(State, "muc_room"),
+
+ add_sh_column(State, "muc_registered"),
+ drop_sh_default(State, "muc_registered"),
+
+ add_sh_column(State, "muc_online_room"),
+ drop_sh_default(State, "muc_online_room"),
+
+ add_sh_column(State, "muc_online_users"),
+ drop_sh_default(State, "muc_online_users"),
+
+ add_sh_column(State, "irc_custom"),
+ drop_sh_default(State, "irc_custom"),
+
+ add_sh_column(State, "motd"),
+ drop_pkey(State, "motd"),
+ add_pkey(State, "motd", ["server_host", "username"]),
+ drop_sh_default(State, "motd"),
+
+ add_sh_column(State, "sm"),
+ drop_index(State, "i_sm_sid"),
+ drop_index(State, "i_sm_username"),
+ add_pkey(State, "sm", ["usec", "pid"]),
+ create_index(State, "sm", "i_sm_sh_username", ["server_host", "username"]),
+ drop_sh_default(State, "sm"),
+
+ add_sh_column(State, "carboncopy"),
+ drop_index(State, "i_carboncopy_ur"),
+ drop_index(State, "i_carboncopy_user"),
+ add_pkey(State, "carboncopy", ["server_host", "username", "resource"]),
+ create_index(State, "carboncopy", "i_carboncopy_sh_user", ["server_host", "username"]),
+ drop_sh_default(State, "carboncopy"),
+
+ add_sh_column(State, "push_session"),
+ drop_index(State, "i_push_usn"),
+ drop_index(State, "i_push_ut"),
+ add_pkey(State, "push_session", ["server_host", "username", "timestamp"]),
+ create_index(State, "push_session", "i_push_session_susn", ["server_host", "username", "service", "node"]),
+ drop_sh_default(State, "push_session"),
+
+ ok.
+
+add_sh_column(#state{dbtype = pgsql} = State, Table) ->
+ sql_query(
+ State#state.host,
+ ["ALTER TABLE ", Table, " ADD COLUMN server_host text NOT NULL DEFAULT '",
+ (State#state.escape)(State#state.host),
+ "';"]);
+add_sh_column(#state{dbtype = mysql} = State, Table) ->
+ sql_query(
+ State#state.host,
+ ["ALTER TABLE ", Table, " ADD COLUMN server_host text NOT NULL DEFAULT '",
+ (State#state.escape)(State#state.host),
+ "';"]).
+
+drop_pkey(#state{dbtype = pgsql} = State, Table) ->
+ sql_query(
+ State#state.host,
+ ["ALTER TABLE ", Table, " DROP CONSTRAINT ", Table, "_pkey;"]);
+drop_pkey(#state{dbtype = mysql} = State, Table) ->
+ sql_query(
+ State#state.host,
+ ["ALTER TABLE ", Table, " DROP PRIMARY KEY;"]).
+
+add_pkey(#state{dbtype = pgsql} = State, Table, Cols) ->
+ SCols = string:join(Cols, ", "),
+ sql_query(
+ State#state.host,
+ ["ALTER TABLE ", Table, " ADD PRIMARY KEY (", SCols, ");"]);
+add_pkey(#state{dbtype = mysql} = State, Table, Cols) ->
+ SCols = string:join(Cols, ", "),
+ sql_query(
+ State#state.host,
+ ["ALTER TABLE ", Table, " ADD PRIMARY KEY (", SCols, ");"]).
+
+drop_sh_default(#state{dbtype = pgsql} = State, Table) ->
+ sql_query(
+ State#state.host,
+ ["ALTER TABLE ", Table, " ALTER COLUMN server_host DROP DEFAULT;"]);
+drop_sh_default(#state{dbtype = mysql} = State, Table) ->
+ sql_query(
+ State#state.host,
+ ["ALTER TABLE ", Table, " ALTER COLUMN server_host DROP DEFAULT;"]).
+
+drop_index(#state{dbtype = pgsql} = State, Index) ->
+ sql_query(
+ State#state.host,
+ ["DROP INDEX ", Index, ";"]);
+drop_index(#state{dbtype = mysql} = State, Index) ->
+ sql_query(
+ State#state.host,
+ ["DROP INDEX ", Index, ";"]).
+
+create_unique_index(#state{dbtype = pgsql} = State, Table, Index, Cols) ->
+ SCols = string:join(Cols, ", "),
+ sql_query(
+ State#state.host,
+ ["CREATE UNIQUE INDEX ", Index, " ON ", Table, " USING btree (",
+ SCols, ");"]);
+create_unique_index(#state{dbtype = mysql} = State, Table, Index, Cols) ->
+ Cols2 = [C ++ "(75)" || C <- Cols],
+ SCols = string:join(Cols2, ", "),
+ sql_query(
+ State#state.host,
+ ["CREATE UNIQUE INDEX ", Index, " ON ", Table, "(",
+ SCols, ");"]).
+
+create_index(#state{dbtype = pgsql} = State, Table, Index, Cols) ->
+ SCols = string:join(Cols, ", "),
+ sql_query(
+ State#state.host,
+ ["CREATE INDEX ", Index, " ON ", Table, " USING btree (",
+ SCols, ");"]);
+create_index(#state{dbtype = mysql} = State, Table, Index, Cols) ->
+ Cols2 = [C ++ "(75)" || C <- Cols],
+ SCols = string:join(Cols2, ", "),
+ sql_query(
+ State#state.host,
+ ["CREATE INDEX ", Index, " ON ", Table, "(",
+ SCols, ");"]).
+
+sql_query(Host, Query) ->
+ io:format("executing \"~s\" on ~s~n", [Query, Host]),
+ case ejabberd_sql:sql_query(Host, Query) of
+ {error, Error} ->
+ io:format("error: ~p~n", [Error]),
+ ok;
+ _ ->
+ ok
+ end.
+
+mod_opt_type(_) -> [].
diff --git a/src/mod_announce_sql.erl b/src/mod_announce_sql.erl
index 1dea0ba75..c5c9eb58f 100644
--- a/src/mod_announce_sql.erl
+++ b/src/mod_announce_sql.erl
@@ -51,6 +51,7 @@ set_motd_users(LServer, USRs) ->
?SQL_UPSERT_T(
"motd",
["!username=%(U)s",
+ "!server_host=%(LServer)s",
"xml=''"])
end, USRs)
end,
@@ -62,20 +63,23 @@ set_motd(LServer, Packet) ->
?SQL_UPSERT_T(
"motd",
["!username=''",
+ "!server_host=%(LServer)s",
"xml=%(XML)s"])
end,
transaction(LServer, F).
delete_motd(LServer) ->
F = fun() ->
- ejabberd_sql:sql_query_t(?SQL("delete from motd"))
+ ejabberd_sql:sql_query_t(
+ ?SQL("delete from motd where %(LServer)H"))
end,
transaction(LServer, F).
get_motd(LServer) ->
case catch ejabberd_sql:sql_query(
LServer,
- ?SQL("select @(xml)s from motd where username=''")) of
+ ?SQL("select @(xml)s from motd"
+ " where username='' and %(LServer)H")) of
{selected, [{XML}]} ->
parse_element(XML);
{selected, []} ->
@@ -88,7 +92,7 @@ is_motd_user(LUser, LServer) ->
case catch ejabberd_sql:sql_query(
LServer,
?SQL("select @(username)s from motd"
- " where username=%(LUser)s")) of
+ " where username=%(LUser)s and %(LServer)H")) of
{selected, [_|_]} ->
{ok, true};
{selected, []} ->
@@ -102,6 +106,7 @@ set_motd_user(LUser, LServer) ->
?SQL_UPSERT_T(
"motd",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"xml=''"])
end,
transaction(LServer, F).
@@ -111,16 +116,24 @@ export(_Server) ->
fun(Host, #motd{server = LServer, packet = El})
when LServer == Host ->
XML = fxml:element_to_binary(El),
- [?SQL("delete from motd where username='';"),
- ?SQL("insert into motd(username, xml) values ('', %(XML)s);")];
+ [?SQL("delete from motd where username='' and %(LServer)H;"),
+ ?SQL_INSERT(
+ "motd",
+ ["username=''",
+ "server_host=%(LServer)s",
+ "xml=%(XML)s"])];
(_Host, _R) ->
[]
end},
{motd_users,
fun(Host, #motd_users{us = {LUser, LServer}})
when LServer == Host, LUser /= <<"">> ->
- [?SQL("delete from motd where username=%(LUser)s;"),
- ?SQL("insert into motd(username, xml) values (%(LUser)s, '');")];
+ [?SQL("delete from motd where username=%(LUser)s and %(LServer)H;"),
+ ?SQL_INSERT(
+ "motd",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "xml=''"])];
(_Host, _R) ->
[]
end}].
diff --git a/src/mod_carboncopy_sql.erl b/src/mod_carboncopy_sql.erl
index 3271d8a1c..1b8e1e111 100644
--- a/src/mod_carboncopy_sql.erl
+++ b/src/mod_carboncopy_sql.erl
@@ -42,6 +42,7 @@ enable(LUser, LServer, LResource, NS) ->
NodeS = erlang:atom_to_binary(node(), latin1),
case ?SQL_UPSERT(LServer, "carboncopy",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"!resource=%(LResource)s",
"namespace=%(NS)s",
"node=%(NodeS)s"]) of
@@ -56,7 +57,7 @@ disable(LUser, LServer, LResource) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("delete from carboncopy where username=%(LUser)s "
- "and resource=%(LResource)s")) of
+ "and %(LServer)H and resource=%(LResource)s")) of
{updated, _} ->
ok;
Err ->
@@ -68,7 +69,7 @@ list(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(resource)s, @(namespace)s, @(node)s from carboncopy "
- "where username=%(LUser)s")) of
+ "where username=%(LUser)s and %(LServer)H")) of
{selected, Rows} ->
{ok, [{Resource, NS, binary_to_atom(Node, latin1)}
|| {Resource, NS, Node} <- Rows]};
diff --git a/src/mod_irc_sql.erl b/src/mod_irc_sql.erl
index f9a7d716f..1f8d7d16a 100644
--- a/src/mod_irc_sql.erl
+++ b/src/mod_irc_sql.erl
@@ -46,7 +46,7 @@ get_data(LServer, Host, From) ->
case catch ejabberd_sql:sql_query(
LServer,
?SQL("select @(data)s from irc_custom"
- " where jid=%(SJID)s and host=%(Host)s")) of
+ " where jid=%(SJID)s and host=%(Host)s and %(LServer)H")) of
{selected, [{SData}]} ->
mod_irc:data_to_binary(From, ejabberd_sql:decode_term(SData));
{'EXIT', _} -> error;
@@ -61,6 +61,7 @@ set_data(LServer, Host, From, Data) ->
"irc_custom",
["!jid=%(SJID)s",
"!host=%(Host)s",
+ "server_host=%(LServer)s",
"data=%(SData)s"]),
ok
end,
@@ -73,11 +74,16 @@ export(_Server) ->
case str:suffix(Host, IRCHost) of
true ->
SJID = jid:encode(jid:make(U, S)),
+ LServer = ejabberd_router:host_of_route(IRCHost),
SData = misc:term_to_expr(Data),
[?SQL("delete from irc_custom"
- " where jid=%(SJID)s and host=%(IRCHost)s;"),
- ?SQL("insert into irc_custom(jid, host, data)"
- " values (%(SJID)s, %(IRCHost)s, %(SData)s);")];
+ " where jid=%(SJID)s and host=%(IRCHost)s and %(LServer)H;"),
+ ?SQL_INSERT(
+ "irc_custom",
+ ["jid=%(SJID)s",
+ "host=%(Host)s",
+ "server_host=%(LServer)s",
+ "data=%(SData)s"])];
false ->
[]
end
diff --git a/src/mod_last_sql.erl b/src/mod_last_sql.erl
index b777ba30d..f0889e4ec 100644
--- a/src/mod_last_sql.erl
+++ b/src/mod_last_sql.erl
@@ -46,7 +46,7 @@ get_last(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(seconds)d, @(state)s from last"
- " where username=%(LUser)s")) of
+ " where username=%(LUser)s and %(LServer)H")) of
{selected, []} ->
error;
{selected, [{TimeStamp, Status}]} ->
@@ -60,6 +60,7 @@ get_last(LUser, LServer) ->
store_last_info(LUser, LServer, TimeStamp, Status) ->
case ?SQL_UPSERT(LServer, "last",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"seconds=%(TimeStamp)d",
"state=%(Status)s"]) of
ok ->
@@ -73,16 +74,19 @@ store_last_info(LUser, LServer, TimeStamp, Status) ->
remove_user(LUser, LServer) ->
ejabberd_sql:sql_query(
LServer,
- ?SQL("delete from last where username=%(LUser)s")).
+ ?SQL("delete from last where username=%(LUser)s and %(LServer)H")).
export(_Server) ->
[{last_activity,
fun(Host, #last_activity{us = {LUser, LServer},
timestamp = TimeStamp, status = Status})
when LServer == Host ->
- [?SQL("delete from last where username=%(LUser)s;"),
- ?SQL("insert into last(username, seconds, state)"
- " values (%(LUser)s, %(TimeStamp)d, %(Status)s);")];
+ [?SQL("delete from last where username=%(LUser)s and %(LServer)H;"),
+ ?SQL_INSERT("last",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "seconds=%(TimeStamp)d",
+ "state=%(Status)s"])];
(_Host, _R) ->
[]
end}].
diff --git a/src/mod_mam_sql.erl b/src/mod_mam_sql.erl
index 7e02b5791..53ccd94a4 100644
--- a/src/mod_mam_sql.erl
+++ b/src/mod_mam_sql.erl
@@ -38,6 +38,12 @@
-include("logger.hrl").
-include("ejabberd_sql_pt.hrl").
+-ifdef(NEW_SQL_SCHEMA).
+-define(USE_NEW_SCHEMA, true).
+-else.
+-define(USE_NEW_SCHEMA, false).
+-endif.
+
%%%===================================================================
%%% API
%%%===================================================================
@@ -47,23 +53,32 @@ init(_Host, _Opts) ->
remove_user(LUser, LServer) ->
ejabberd_sql:sql_query(
LServer,
- ?SQL("delete from archive where username=%(LUser)s")),
+ ?SQL("delete from archive where username=%(LUser)s and %(LServer)H")),
ejabberd_sql:sql_query(
LServer,
- ?SQL("delete from archive_prefs where username=%(LUser)s")).
+ ?SQL("delete from archive_prefs where username=%(LUser)s and %(LServer)H")).
remove_room(LServer, LName, LHost) ->
LUser = jid:encode({LName, LHost, <<>>}),
remove_user(LUser, LServer).
delete_old_messages(ServerHost, TimeStamp, Type) ->
- TypeClause = if Type == all -> <<"">>;
- true -> [<<" and kind='">>, misc:atom_to_binary(Type), <<"'">>]
- end,
- TS = integer_to_binary(now_to_usec(TimeStamp)),
- ejabberd_sql:sql_query(
- ServerHost, [<<"delete from archive where timestamp<">>,
- TS, TypeClause, <<";">>]),
+ TS = now_to_usec(TimeStamp),
+ case Type of
+ all ->
+ ejabberd_sql:sql_query(
+ ServerHost,
+ ?SQL("delete from archive"
+ " where timestamp < %(TS)d and %(ServerHost)H"));
+ _ ->
+ SType = misc:atom_to_binary(Type),
+ ejabberd_sql:sql_query(
+ ServerHost,
+ ?SQL("delete from archive"
+ " where timestamp < %(TS)d"
+ " and kind=%(SType)s"
+ " and %(ServerHost)H"))
+ end,
ok.
extended_fields() ->
@@ -86,16 +101,17 @@ store(Pkt, LServer, {LUser, LHost}, Type, Peer, Nick, _Dir) ->
SType = misc:atom_to_binary(Type),
case ejabberd_sql:sql_query(
LServer,
- ?SQL("insert into archive (username, timestamp,"
- " peer, bare_peer, xml, txt, kind, nick) values ("
- "%(SUser)s, "
- "%(TSinteger)d, "
- "%(LPeer)s, "
- "%(BarePeer)s, "
- "%(XML)s, "
- "%(Body)s, "
- "%(SType)s, "
- "%(Nick)s)")) of
+ ?SQL_INSERT(
+ "archive",
+ ["username=%(SUser)s",
+ "server_host=%(LServer)s",
+ "timestamp=%(TSinteger)d",
+ "peer=%(LPeer)s",
+ "bare_peer=%(BarePeer)s",
+ "xml=%(XML)s",
+ "txt=%(Body)s",
+ "kind=%(SType)s",
+ "nick=%(Nick)s"])) of
{updated, _} ->
{ok, ID};
Err ->
@@ -113,6 +129,7 @@ write_prefs(LUser, _LServer, #archive_prefs{default = Default,
ServerHost,
"archive_prefs",
["!username=%(LUser)s",
+ "!server_host=%(ServerHost)s",
"def=%(SDefault)s",
"always=%(SAlways)s",
"never=%(SNever)s"]) of
@@ -126,7 +143,7 @@ get_prefs(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(def)s, @(always)s, @(never)s from archive_prefs"
- " where username=%(LUser)s")) of
+ " where username=%(LUser)s and %(LServer)H")) of
{selected, [{SDefault, SAlways, SNever}]} ->
Default = erlang:binary_to_existing_atom(SDefault, utf8),
Always = ejabberd_sql:decode_term(SAlways),
@@ -192,8 +209,13 @@ export(_Server) ->
SDefault = erlang:atom_to_binary(Default, utf8),
SAlways = misc:term_to_expr(Always),
SNever = misc:term_to_expr(Never),
- [?SQL("insert into archive_prefs (username, def, always, never) values"
- "(%(LUser)s, %(SDefault)s, %(SAlways)s, %(SNever)s);")];
+ [?SQL_INSERT(
+ "archive_prefs",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "def=%(SDefault)s",
+ "always=%(SAlways)s",
+ "never=%(SNever)s"])];
(_Host, _R) ->
[]
end},
@@ -212,11 +234,17 @@ export(_Server) ->
XML = fxml:element_to_binary(Pkt),
Body = fxml:get_subtag_cdata(Pkt, <<"body">>),
SType = misc:atom_to_binary(Type),
- [?SQL("insert into archive (username, timestamp, "
- "peer, bare_peer, xml, txt, kind, nick) "
- "values (%(SUser)s, %(TStmp)d, %(LPeer)s, "
- "%(BarePeer)s, %(XML)s, %(Body)s, %(SType)s, "
- "%(Nick)s);")];
+ [?SQL_INSERT(
+ "archive",
+ ["username=%(SUser)s",
+ "server_host=%(LServer)s",
+ "timestamp=%(TStmp)d",
+ "peer=%(LPeer)s",
+ "bare_peer=%(BarePeer)s",
+ "xml=%(XML)s",
+ "txt=%(Body)s",
+ "kind=%(SType)s",
+ "nick=%(Nick)s"])];
(_Host, _R) ->
[]
end}].
@@ -303,11 +331,24 @@ make_sql_query(User, LServer, MAMQuery, RSM) ->
[]
end,
SUser = Escape(User),
+ SServer = Escape(LServer),
- Query = [<<"SELECT ">>, TopClause, <<" timestamp, xml, peer, kind, nick"
- " FROM archive WHERE username='">>,
- SUser, <<"'">>, WithClause, WithTextClause, StartClause, EndClause,
- PageClause],
+ Query =
+ case ?USE_NEW_SCHEMA of
+ true ->
+ [<<"SELECT ">>, TopClause,
+ <<" timestamp, xml, peer, kind, nick"
+ " FROM archive WHERE username='">>,
+ SUser, <<"' and server_host='">>,
+ SServer, <<"'">>, WithClause, WithTextClause,
+ StartClause, EndClause, PageClause];
+ false ->
+ [<<"SELECT ">>, TopClause,
+ <<" timestamp, xml, peer, kind, nick"
+ " FROM archive WHERE username='">>,
+ SUser, <<"'">>, WithClause, WithTextClause,
+ StartClause, EndClause, PageClause]
+ end,
QueryPage =
case Direction of
@@ -322,9 +363,19 @@ make_sql_query(User, LServer, MAMQuery, RSM) ->
[Query, <<" ORDER BY timestamp ASC ">>,
LimitClause, <<";">>]
end,
- {QueryPage,
- [<<"SELECT COUNT(*) FROM archive WHERE username='">>,
- SUser, <<"'">>, WithClause, WithTextClause, StartClause, EndClause, <<";">>]}.
+ case ?USE_NEW_SCHEMA of
+ true ->
+ {QueryPage,
+ [<<"SELECT COUNT(*) FROM archive WHERE username='">>,
+ SUser, <<"' and server_host='">>,
+ SServer, <<"'">>, WithClause, WithTextClause,
+ StartClause, EndClause, <<";">>]};
+ false ->
+ {QueryPage,
+ [<<"SELECT COUNT(*) FROM archive WHERE username='">>,
+ SUser, <<"'">>, WithClause, WithTextClause,
+ StartClause, EndClause, <<";">>]}
+ end.
-spec get_max_direction_id(rsm_set() | undefined) ->
{integer() | undefined,
diff --git a/src/mod_muc_sql.erl b/src/mod_muc_sql.erl
index 7ec598bf3..41ad92bf9 100644
--- a/src/mod_muc_sql.erl
+++ b/src/mod_muc_sql.erl
@@ -68,16 +68,18 @@ store_room(LServer, Host, Name, Opts, ChangesHints) ->
"muc_room",
["!name=%(Name)s",
"!host=%(Host)s",
- "opts=%(SOpts)s"]),
- case ChangesHints of
- Changes when is_list(Changes) ->
- [change_room(Host, Name, Change) || Change <- Changes];
- _ ->
- ejabberd_sql:sql_query_t(?SQL("delete from muc_room_subscribers where "
- "room=%(Name)s and host=%(Host)s")),
- [change_room(Host, Name, {add_subscription, JID, Nick, Nodes})
- || {JID, Nick, Nodes} <- Subs]
- end
+ "server_host=%(LServer)s",
+ "opts=%(SOpts)s"]),
+ case ChangesHints of
+ Changes when is_list(Changes) ->
+ [change_room(Host, Name, Change) || Change <- Changes];
+ _ ->
+ ejabberd_sql:sql_query_t(
+ ?SQL("delete from muc_room_subscribers where "
+ "room=%(Name)s and host=%(Host)s")),
+ [change_room(Host, Name, {add_subscription, JID, Nick, Nodes})
+ || {JID, Nick, Nodes} <- Subs]
+ end
end,
ejabberd_sql:sql_transaction(LServer, F).
@@ -226,6 +228,7 @@ set_nick(LServer, Host, From, Nick) ->
"muc_registered",
["!jid=%(JID)s",
"!host=%(Host)s",
+ "server_host=%(LServer)s",
"nick=%(Nick)s"]),
ok;
true ->
@@ -257,6 +260,7 @@ register_online_room(ServerHost, Room, Host, Pid) ->
"muc_online_room",
["!name=%(Room)s",
"!host=%(Host)s",
+ "server_host=%(ServerHost)s",
"node=%(NodeS)s",
"pid=%(PidS)s"]) of
ok ->
@@ -331,6 +335,7 @@ register_online_user(ServerHost, {U, S, R}, Room, Host) ->
"!resource=%(R)s",
"!name=%(Room)s",
"!host=%(Host)s",
+ "server_host=%(ServerHost)s",
"node=%(NodeS)s"]) of
ok ->
ok;
@@ -379,9 +384,12 @@ export(_Server) ->
SOpts = misc:term_to_expr(Opts),
[?SQL("delete from muc_room where name=%(Name)s"
" and host=%(RoomHost)s;"),
- ?SQL("insert into muc_room(name, host, opts) "
- "values ("
- "%(Name)s, %(RoomHost)s, %(SOpts)s);")];
+ ?SQL_INSERT(
+ "muc_room",
+ ["name=%(Name)s",
+ "host=%(Host)s",
+ "server_host=%(Host)s",
+ "opts=%(SOpts)s"])];
false ->
[]
end
@@ -394,9 +402,12 @@ export(_Server) ->
SJID = jid:encode(jid:make(U, S)),
[?SQL("delete from muc_registered where"
" jid=%(SJID)s and host=%(RoomHost)s;"),
- ?SQL("insert into muc_registered(jid, host, "
- "nick) values ("
- "%(SJID)s, %(RoomHost)s, %(Nick)s);")];
+ ?SQL_INSERT(
+ "muc_registered",
+ ["jid=%(SJID)s",
+ "host=%(Host)s",
+ "server_host=%(Host)s",
+ "nick=%(Nick)s"])];
false ->
[]
end
diff --git a/src/mod_offline_sql.erl b/src/mod_offline_sql.erl
index f43f4c929..53a0d3451 100644
--- a/src/mod_offline_sql.erl
+++ b/src/mod_offline_sql.erl
@@ -56,8 +56,11 @@ store_message(#offline_msg{us = {LUser, LServer}} = M) ->
xmpp:encode(NewPacket)),
case ejabberd_sql:sql_query(
LServer,
- ?SQL("insert into spool(username, xml) values "
- "(%(LUser)s, %(XML)s)")) of
+ ?SQL_INSERT(
+ "spool",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "xml=%(XML)s"])) of
{updated, _} ->
ok;
_ ->
@@ -87,10 +90,8 @@ remove_expired_messages(_LServer) ->
remove_old_messages(Days, LServer) ->
case ejabberd_sql:sql_query(
LServer,
- [<<"DELETE FROM spool"
- " WHERE created_at < "
- "NOW() - INTERVAL '">>,
- integer_to_list(Days), <<"' DAY;">>]) of
+ ?SQL("DELETE FROM spool"
+ " WHERE created_at < NOW() - INTERVAL %(Days)d DAY")) of
{updated, N} ->
?INFO_MSG("~p message(s) deleted from offline spool", [N]);
_Error ->
@@ -101,13 +102,13 @@ remove_old_messages(Days, LServer) ->
remove_user(LUser, LServer) ->
ejabberd_sql:sql_query(
LServer,
- ?SQL("delete from spool where username=%(LUser)s")).
+ ?SQL("delete from spool where username=%(LUser)s and %(LServer)H")).
read_message_headers(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(xml)s, @(seq)d from spool"
- " where username=%(LUser)s order by seq")) of
+ " where username=%(LUser)s and %(LServer)H order by seq")) of
{selected, Rows} ->
lists:flatmap(
fun({XML, Seq}) ->
@@ -129,6 +130,7 @@ read_message(LUser, LServer, Seq) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(xml)s from spool where username=%(LUser)s"
+ " and %(LServer)H"
" and seq=%(Seq)d")) of
{selected, [{RawXML}|_]} ->
case xml_to_offline_msg(RawXML) of
@@ -144,7 +146,7 @@ read_message(LUser, LServer, Seq) ->
remove_message(LUser, LServer, Seq) ->
ejabberd_sql:sql_query(
LServer,
- ?SQL("delete from spool where username=%(LUser)s"
+ ?SQL("delete from spool where username=%(LUser)s and %(LServer)H"
" and seq=%(Seq)d")),
ok.
@@ -152,7 +154,7 @@ read_all_messages(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(xml)s from spool where "
- "username=%(LUser)s order by seq")) of
+ "username=%(LUser)s and %(LServer)H order by seq")) of
{selected, Rs} ->
lists:flatmap(
fun({XML}) ->
@@ -173,7 +175,7 @@ count_messages(LUser, LServer) ->
case catch ejabberd_sql:sql_query(
LServer,
?SQL("select @(count(*))d from spool "
- "where username=%(LUser)s")) of
+ "where username=%(LUser)s and %(LServer)H")) of
{selected, [{Res}]} ->
Res;
_ -> 0
@@ -183,7 +185,8 @@ export(_Server) ->
[{offline_msg,
fun(Host, #offline_msg{us = {LUser, LServer}})
when LServer == Host ->
- [?SQL("delete from spool where username=%(LUser)s;")];
+ [?SQL("delete from spool where username=%(LUser)s"
+ " and %(LServer)H;")];
(_Host, _R) ->
[]
end},
@@ -199,8 +202,11 @@ export(_Server) ->
Packet1, jid:make(LServer),
TimeStamp, <<"Offline Storage">>),
XML = fxml:element_to_binary(xmpp:encode(Packet2)),
- [?SQL("insert into spool(username, xml) values ("
- "%(LUser)s, %(XML)s);")]
+ [?SQL_INSERT(
+ "spool",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "xml=%(XML)s"])]
catch _:{xmpp_codec, Why} ->
?ERROR_MSG("failed to decode packet ~p of user ~s@~s: ~s",
[El, LUser, LServer, xmpp:format_error(Why)]),
@@ -249,9 +255,10 @@ get_and_del_spool_msg_t(LServer, LUser) ->
Result =
ejabberd_sql:sql_query_t(
?SQL("select @(username)s, @(xml)s from spool where "
- "username=%(LUser)s order by seq;")),
+ "username=%(LUser)s and %(LServer)H order by seq;")),
ejabberd_sql:sql_query_t(
- ?SQL("delete from spool where username=%(LUser)s;")),
+ ?SQL("delete from spool where"
+ " username=%(LUser)s and %(LServer)H;")),
Result
end,
ejabberd_sql:sql_transaction(LServer, F).
diff --git a/src/mod_privacy_sql.erl b/src/mod_privacy_sql.erl
index b19c95fe5..7939cbb26 100644
--- a/src/mod_privacy_sql.erl
+++ b/src/mod_privacy_sql.erl
@@ -56,13 +56,13 @@ unset_default(LUser, LServer) ->
set_default(LUser, LServer, Name) ->
F = fun () ->
- case get_privacy_list_names_t(LUser) of
+ case get_privacy_list_names_t(LUser, LServer) of
{selected, []} ->
{error, notfound};
{selected, Names} ->
case lists:member({Name}, Names) of
true ->
- set_default_privacy_list(LUser, Name);
+ set_default_privacy_list(LUser, LServer, Name);
false ->
{error, notfound}
end
@@ -72,14 +72,14 @@ set_default(LUser, LServer, Name) ->
remove_list(LUser, LServer, Name) ->
F = fun () ->
- case get_default_privacy_list_t(LUser) of
+ case get_default_privacy_list_t(LUser, LServer) of
{selected, []} ->
- remove_privacy_list_t(LUser, Name);
+ remove_privacy_list_t(LUser, LServer, Name);
{selected, [{Default}]} ->
if Name == Default ->
{error, conflict};
true ->
- remove_privacy_list_t(LUser, Name)
+ remove_privacy_list_t(LUser, LServer, Name)
end
end
end,
@@ -91,13 +91,14 @@ set_lists(#privacy{us = {LUser, LServer},
F = fun() ->
lists:foreach(
fun({Name, List}) ->
- add_privacy_list(LUser, Name),
+ add_privacy_list(LUser, LServer, Name),
{selected, [<<"id">>], [[I]]} =
- get_privacy_list_id_t(LUser, Name),
+ get_privacy_list_id_t(LUser, LServer, Name),
RItems = lists:map(fun item_to_raw/1, List),
set_privacy_list(I, RItems),
if is_binary(Default) ->
- set_default_privacy_list(LUser, Default);
+ set_default_privacy_list(
+ LUser, LServer, Default);
true ->
ok
end
@@ -108,11 +109,11 @@ set_lists(#privacy{us = {LUser, LServer},
set_list(LUser, LServer, Name, List) ->
RItems = lists:map(fun item_to_raw/1, List),
F = fun () ->
- ID = case get_privacy_list_id_t(LUser, Name) of
+ ID = case get_privacy_list_id_t(LUser, LServer, Name) of
{selected, []} ->
- add_privacy_list(LUser, Name),
+ add_privacy_list(LUser, LServer, Name),
{selected, [{I}]} =
- get_privacy_list_id_t(LUser, Name),
+ get_privacy_list_id_t(LUser, LServer, Name),
I;
{selected, [{I}]} -> I
end,
@@ -199,9 +200,12 @@ export(Server) ->
when LServer == Host ->
if Default /= none ->
[?SQL("delete from privacy_default_list where"
- " username=%(LUser)s;"),
- ?SQL("insert into privacy_default_list(username, name) "
- "values (%(LUser)s, %(Default)s);")];
+ " username=%(LUser)s and %(LServer)H;"),
+ ?SQL_INSERT(
+ "privacy_default_list",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "name=%(Default)s"])];
true ->
[]
end ++
@@ -210,11 +214,14 @@ export(Server) ->
RItems = lists:map(fun item_to_raw/1, List),
ID = get_id(),
[?SQL("delete from privacy_list where"
- " username=%(LUser)s and"
+ " username=%(LUser)s and %(LServer)H and"
" name=%(Name)s;"),
- ?SQL("insert into privacy_list(username, "
- "name, id) values ("
- "%(LUser)s, %(Name)s, %(ID)d);"),
+ ?SQL_INSERT(
+ "privacy_list",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "name=%(Name)s",
+ "id=%(ID)d"]),
?SQL("delete from privacy_list_data where"
" id=%(ID)d;")] ++
[?SQL("insert into privacy_list_data(id, t, "
@@ -312,28 +319,28 @@ get_default_privacy_list(LUser, LServer) ->
ejabberd_sql:sql_query(
LServer,
?SQL("select @(name)s from privacy_default_list "
- "where username=%(LUser)s")).
+ "where username=%(LUser)s and %(LServer)H")).
-get_default_privacy_list_t(LUser) ->
+get_default_privacy_list_t(LUser, LServer) ->
ejabberd_sql:sql_query_t(
?SQL("select @(name)s from privacy_default_list "
- "where username=%(LUser)s")).
+ "where username=%(LUser)s and %(LServer)H")).
get_privacy_list_names(LUser, LServer) ->
ejabberd_sql:sql_query(
LServer,
?SQL("select @(name)s from privacy_list"
- " where username=%(LUser)s")).
+ " where username=%(LUser)s and %(LServer)H")).
-get_privacy_list_names_t(LUser) ->
+get_privacy_list_names_t(LUser, LServer) ->
ejabberd_sql:sql_query_t(
?SQL("select @(name)s from privacy_list"
- " where username=%(LUser)s")).
+ " where username=%(LUser)s and %(LServer)H")).
-get_privacy_list_id_t(LUser, Name) ->
+get_privacy_list_id_t(LUser, LServer, Name) ->
ejabberd_sql:sql_query_t(
?SQL("select @(id)d from privacy_list"
- " where username=%(LUser)s and name=%(Name)s")).
+ " where username=%(LUser)s and %(LServer)H and name=%(Name)s")).
get_privacy_list_data(LUser, LServer, Name) ->
ejabberd_sql:sql_query(
@@ -343,37 +350,41 @@ get_privacy_list_data(LUser, LServer, Name) ->
"@(match_presence_out)b from privacy_list_data "
"where id ="
" (select id from privacy_list"
- " where username=%(LUser)s and name=%(Name)s) "
+ " where username=%(LUser)s and %(LServer)H and name=%(Name)s) "
"order by ord")).
-set_default_privacy_list(LUser, Name) ->
+set_default_privacy_list(LUser, LServer, Name) ->
?SQL_UPSERT_T(
"privacy_default_list",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"name=%(Name)s"]).
unset_default_privacy_list(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("delete from privacy_default_list"
- " where username=%(LUser)s")) of
+ " where username=%(LUser)s and %(LServer)H")) of
{updated, _} -> ok;
Err -> Err
end.
-remove_privacy_list_t(LUser, Name) ->
+remove_privacy_list_t(LUser, LServer, Name) ->
case ejabberd_sql:sql_query_t(
?SQL("delete from privacy_list where"
- " username=%(LUser)s and name=%(Name)s")) of
+ " username=%(LUser)s and %(LServer)H and name=%(Name)s")) of
{updated, 0} -> {error, notfound};
{updated, _} -> ok;
Err -> Err
end.
-add_privacy_list(LUser, Name) ->
+add_privacy_list(LUser, LServer, Name) ->
ejabberd_sql:sql_query_t(
- ?SQL("insert into privacy_list(username, name) "
- "values (%(LUser)s, %(Name)s)")).
+ ?SQL_INSERT(
+ "privacy_list",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "name=%(Name)s"])).
set_privacy_list(ID, RItems) ->
ejabberd_sql:sql_query_t(
@@ -395,12 +406,12 @@ set_privacy_list(ID, RItems) ->
del_privacy_lists(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
- ?SQL("delete from privacy_list where username=%(LUser)s")) of
+ ?SQL("delete from privacy_list where username=%(LUser)s and %(LServer)H")) of
{updated, _} ->
case ejabberd_sql:sql_query(
LServer,
?SQL("delete from privacy_default_list "
- "where username=%(LUser)s")) of
+ "where username=%(LUser)s and %(LServer)H")) of
{updated, _} -> ok;
Err -> Err
end;
diff --git a/src/mod_private_sql.erl b/src/mod_private_sql.erl
index 907eeaf3a..5ed584c30 100644
--- a/src/mod_private_sql.erl
+++ b/src/mod_private_sql.erl
@@ -49,6 +49,7 @@ set_data(LUser, LServer, Data) ->
?SQL_UPSERT_T(
"private_storage",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"!namespace=%(XMLNS)s",
"data=%(SData)s"])
end, Data)
@@ -64,7 +65,8 @@ get_data(LUser, LServer, XMLNS) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(data)s from private_storage"
- " where username=%(LUser)s and namespace=%(XMLNS)s")) of
+ " where username=%(LUser)s and %(LServer)H"
+ " and namespace=%(XMLNS)s")) of
{selected, [{SData}]} ->
parse_element(LUser, LServer, SData);
{selected, []} ->
@@ -77,7 +79,7 @@ get_all_data(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(namespace)s, @(data)s from private_storage"
- " where username=%(LUser)s")) of
+ " where username=%(LUser)s and %(LServer)H")) of
{selected, []} ->
error;
{selected, Res} ->
@@ -95,7 +97,8 @@ get_all_data(LUser, LServer) ->
del_data(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
- ?SQL("delete from private_storage where username=%(LUser)s")) of
+ ?SQL("delete from private_storage"
+ " where username=%(LUser)s and %(LServer)H")) of
{updated, _} ->
ok;
_ ->
@@ -109,10 +112,13 @@ export(_Server) ->
when LServer == Host ->
SData = fxml:element_to_binary(Data),
[?SQL("delete from private_storage where"
- " username=%(LUser)s and namespace=%(XMLNS)s;"),
- ?SQL("insert into private_storage(username, "
- "namespace, data) values ("
- "%(LUser)s, %(XMLNS)s, %(SData)s);")];
+ " username=%(LUser)s and %(LServer)H and namespace=%(XMLNS)s;"),
+ ?SQL_INSERT(
+ "private_storage",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "namespace=%(XMLNS)s",
+ "data=%(SData)s"])];
(_Host, _R) ->
[]
end}].
diff --git a/src/mod_push_sql.erl b/src/mod_push_sql.erl
index e4634a34e..a94b686fc 100644
--- a/src/mod_push_sql.erl
+++ b/src/mod_push_sql.erl
@@ -43,6 +43,7 @@ store_session(LUser, LServer, NowTS, PushJID, Node, XData) ->
Service = jid:encode(PushLJID),
case ?SQL_UPSERT(LServer, "push_session",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"!timestamp=%(TS)d",
"!service=%(Service)s",
"!node=%(Node)s",
@@ -60,7 +61,8 @@ lookup_session(LUser, LServer, PushJID, Node) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(timestamp)d, @(xml)s from push_session "
- "where username=%(LUser)s and service=%(Service)s "
+ "where username=%(LUser)s and %(LServer)H "
+ "and service=%(Service)s "
"and node=%(Node)s")) of
{selected, [{TS, XML}]} ->
NowTS = misc:usec_to_now(TS),
@@ -78,7 +80,7 @@ lookup_session(LUser, LServer, NowTS) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(service)s, @(node)s, @(xml)s "
- "from push_session where username=%(LUser)s "
+ "from push_session where username=%(LUser)s and %(LServer)H "
"and timestamp=%(TS)d")) of
{selected, [{Service, Node, XML}]} ->
PushLJID = jid:tolower(jid:decode(Service)),
@@ -97,7 +99,8 @@ lookup_sessions(LUser, LServer, PushJID) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(timestamp)d, @(xml)s, @(node)s from push_session "
- "where username=%(LUser)s and service=%(Service)s")) of
+ "where username=%(LUser)s and %(LServer)H "
+ "and service=%(Service)s")) of
{selected, Rows} ->
{ok, lists:map(
fun({TS, XML, Node}) ->
@@ -114,7 +117,8 @@ lookup_sessions(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(timestamp)d, @(xml)s, @(node)s, @(service)s "
- "from push_session where username=%(LUser)s")) of
+ "from push_session "
+ "where username=%(LUser)s and %(LServer)H")) of
{selected, Rows} ->
{ok, lists:map(
fun({TS, XML, Node, Service}) ->
@@ -132,7 +136,8 @@ lookup_sessions(LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(username)s, @(timestamp)d, @(xml)s, "
- "@(node)s, @(service)s from push_session")) of
+ "@(node)s, @(service)s from push_session "
+ "where %(LServer)H")) of
{selected, Rows} ->
{ok, lists:map(
fun({LUser, TS, XML, Node, Service}) ->
@@ -151,7 +156,7 @@ delete_session(LUser, LServer, NowTS) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("delete from push_session where "
- "username=%(LUser)s and timestamp=%(TS)d")) of
+ "username=%(LUser)s and %(LServer)H and timestamp=%(TS)d")) of
{updated, _} ->
ok;
Err ->
@@ -163,7 +168,8 @@ delete_old_sessions(LServer, Time) ->
TS = misc:now_to_usec(Time),
case ejabberd_sql:sql_query(
LServer,
- ?SQL("delete from push_session where timestamp<%(TS)d")) of
+ ?SQL("delete from push_session where timestamp<%(TS)d "
+ "and %(LServer)H")) of
{updated, _} ->
ok;
Err ->
@@ -183,12 +189,18 @@ export(_Server) ->
Service = jid:encode(PushLJID),
XML = encode_xdata(XData),
[?SQL("delete from push_session where "
- "username=%(LUser)s and timestamp=%(TS)d and "
+ "username=%(LUser)s and %(LServer)H and "
+ "timestamp=%(TS)d and "
"service=%(Service)s and node=%(Node)s and "
"xml=%(XML)s;"),
- ?SQL("insert into push_session(username, timestamp, "
- "service, node, xml) values ("
- "%(LUser)s, %(TS)d, %(Service)s, %(Node)s, %(XML)s);")];
+ ?SQL_INSERT(
+ "push_session",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "timestamp=%(TS)d",
+ "service=%(Service)s",
+ "node=%(Node)s",
+ "xml=%(XML)s"])];
(_Host, _R) ->
[]
end}].
diff --git a/src/mod_roster.erl b/src/mod_roster.erl
index c03ea1154..a86b50d98 100644
--- a/src/mod_roster.erl
+++ b/src/mod_roster.erl
@@ -1180,12 +1180,18 @@ import_stop(_LServer, _DBType) ->
ets:delete(rostergroups_tmp),
ok.
+-ifdef(NEW_SQL_SCHEMA).
+-define(ROW_LENGTH, 10).
+-else.
+-define(ROW_LENGTH, 9).
+-endif.
+
import(LServer, {sql, _}, _DBType, <<"rostergroups">>, [LUser, SJID, Group]) ->
LJID = jid:tolower(jid:decode(SJID)),
ets:insert(rostergroups_tmp, {{LUser, LServer, LJID}, Group}),
ok;
import(LServer, {sql, _}, DBType, <<"rosterusers">>, Row) ->
- I = mod_roster_sql:raw_to_record(LServer, lists:sublist(Row, 9)),
+ I = mod_roster_sql:raw_to_record(LServer, lists:sublist(Row, ?ROW_LENGTH)),
Groups = [G || {_, G} <- ets:lookup(rostergroups_tmp, I#roster.usj)],
RosterItem = I#roster{groups = Groups},
Mod = gen_mod:db_mod(DBType, ?MODULE),
diff --git a/src/mod_roster_sql.erl b/src/mod_roster_sql.erl
index 77899624a..82a3c4951 100644
--- a/src/mod_roster_sql.erl
+++ b/src/mod_roster_sql.erl
@@ -49,7 +49,7 @@ read_roster_version(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
?SQL("select @(version)s from roster_version"
- " where username = %(LUser)s")) of
+ " where username = %(LUser)s and %(LServer)H")) of
{selected, [{Version}]} -> {ok, Version};
{selected, []} -> error;
_ -> {error, db_failure}
@@ -57,11 +57,11 @@ read_roster_version(LUser, LServer) ->
write_roster_version(LUser, LServer, InTransaction, Ver) ->
if InTransaction ->
- set_roster_version(LUser, Ver);
+ set_roster_version(LUser, LServer, Ver);
true ->
transaction(
LServer,
- fun () -> set_roster_version(LUser, Ver) end)
+ fun () -> set_roster_version(LUser, LServer, Ver) end)
end.
get_roster(LUser, LServer) ->
@@ -69,7 +69,8 @@ get_roster(LUser, LServer) ->
LServer,
?SQL("select @(username)s, @(jid)s, @(nick)s, @(subscription)s, "
"@(ask)s, @(askmessage)s, @(server)s, @(subscribe)s, "
- "@(type)s from rosterusers where username=%(LUser)s")) of
+ "@(type)s from rosterusers "
+ "where username=%(LUser)s and %(LServer)H")) of
{selected, Items} when is_list(Items) ->
JIDGroups = case get_roster_jid_groups(LServer, LUser) of
{selected, JGrps} when is_list(JGrps) ->
@@ -130,36 +131,42 @@ remove_user(LUser, LServer) ->
LServer,
fun () ->
ejabberd_sql:sql_query_t(
- ?SQL("delete from rosterusers where username=%(LUser)s")),
+ ?SQL("delete from rosterusers"
+ " where username=%(LUser)s and %(LServer)H")),
ejabberd_sql:sql_query_t(
- ?SQL("delete from rostergroups where username=%(LUser)s"))
+ ?SQL("delete from rostergroups"
+ " where username=%(LUser)s and %(LServer)H"))
end),
ok.
-update_roster(LUser, _LServer, LJID, Item) ->
+update_roster(LUser, LServer, LJID, Item) ->
SJID = jid:encode(LJID),
ItemVals = record_to_row(Item),
ItemGroups = Item#roster.groups,
roster_subscribe(ItemVals),
ejabberd_sql:sql_query_t(
?SQL("delete from rostergroups"
- " where username=%(LUser)s and jid=%(SJID)s")),
+ " where username=%(LUser)s and %(LServer)H and jid=%(SJID)s")),
lists:foreach(
fun(ItemGroup) ->
ejabberd_sql:sql_query_t(
- ?SQL("insert into rostergroups(username, jid, grp) "
- "values (%(LUser)s, %(SJID)s, %(ItemGroup)s)"))
+ ?SQL_INSERT(
+ "rostergroups",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "jid=%(SJID)s",
+ "grp=%(ItemGroup)s"]))
end,
ItemGroups).
-del_roster(LUser, _LServer, LJID) ->
+del_roster(LUser, LServer, LJID) ->
SJID = jid:encode(LJID),
ejabberd_sql:sql_query_t(
?SQL("delete from rosterusers"
- " where username=%(LUser)s and jid=%(SJID)s")),
+ " where username=%(LUser)s and %(LServer)H and jid=%(SJID)s")),
ejabberd_sql:sql_query_t(
?SQL("delete from rostergroups"
- " where username=%(LUser)s and jid=%(SJID)s")).
+ " where username=%(LUser)s and %(LServer)H and jid=%(SJID)s")).
read_subscription_and_groups(LUser, LServer, LJID) ->
SJID = jid:encode(LJID),
@@ -200,9 +207,13 @@ export(_Server) ->
{roster_version,
fun(Host, #roster_version{us = {LUser, LServer}, version = Ver})
when LServer == Host ->
- [?SQL("delete from roster_version where username=%(LUser)s;"),
- ?SQL("insert into roster_version(username, version) values("
- " %(LUser)s, %(Ver)s);")];
+ [?SQL("delete from roster_version"
+ " where username=%(LUser)s and %(LServer)H;"),
+ ?SQL_INSERT(
+ "roster_version",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "version=%(Ver)s"])];
(_Host, _R) ->
[]
end}].
@@ -213,27 +224,29 @@ import(_, _, _) ->
%%%===================================================================
%%% Internal functions
%%%===================================================================
-set_roster_version(LUser, Version) ->
+set_roster_version(LUser, LServer, Version) ->
?SQL_UPSERT_T(
"roster_version",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"version=%(Version)s"]).
get_roster_jid_groups(LServer, LUser) ->
ejabberd_sql:sql_query(
LServer,
?SQL("select @(jid)s, @(grp)s from rostergroups where "
- "username=%(LUser)s")).
+ "username=%(LUser)s and %(LServer)H")).
-get_roster_groups(_LServer, LUser, SJID) ->
+get_roster_groups(LServer, LUser, SJID) ->
ejabberd_sql:sql_query_t(
?SQL("select @(grp)s from rostergroups"
- " where username=%(LUser)s and jid=%(SJID)s")).
+ " where username=%(LUser)s and %(LServer)H and jid=%(SJID)s")).
-roster_subscribe({LUser, SJID, Name, SSubscription, SAsk, AskMessage}) ->
+roster_subscribe({LUser, LServer, SJID, Name, SSubscription, SAsk, AskMessage}) ->
?SQL_UPSERT_T(
"rosterusers",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"!jid=%(SJID)s",
"nick=%(Name)s",
"subscription=%(SSubscription)s",
@@ -243,57 +256,67 @@ roster_subscribe({LUser, SJID, Name, SSubscription, SAsk, AskMessage}) ->
"subscribe=''",
"type='item'"]).
-get_roster_by_jid(_LServer, LUser, SJID) ->
+get_roster_by_jid(LServer, LUser, SJID) ->
ejabberd_sql:sql_query_t(
?SQL("select @(username)s, @(jid)s, @(nick)s, @(subscription)s,"
" @(ask)s, @(askmessage)s, @(server)s, @(subscribe)s,"
" @(type)s from rosterusers"
- " where username=%(LUser)s and jid=%(SJID)s")).
+ " where username=%(LUser)s and %(LServer)H and jid=%(SJID)s")).
get_rostergroup_by_jid(LServer, LUser, SJID) ->
ejabberd_sql:sql_query(
LServer,
?SQL("select @(grp)s from rostergroups"
- " where username=%(LUser)s and jid=%(SJID)s")).
+ " where username=%(LUser)s and %(LServer)H and jid=%(SJID)s")).
get_subscription(LServer, LUser, SJID) ->
ejabberd_sql:sql_query(
LServer,
?SQL("select @(subscription)s from rosterusers "
- "where username=%(LUser)s and jid=%(SJID)s")).
+ "where username=%(LUser)s and %(LServer)H and jid=%(SJID)s")).
-update_roster_sql({LUser, SJID, Name, SSubscription, SAsk, AskMessage},
+update_roster_sql({LUser, LServer, SJID, Name, SSubscription, SAsk, AskMessage},
ItemGroups) ->
[?SQL("delete from rosterusers where"
- " username=%(LUser)s and jid=%(SJID)s;"),
- ?SQL("insert into rosterusers("
- " username, jid, nick,"
- " subscription, ask, askmessage,"
- " server, subscribe, type) "
- "values ("
- "%(LUser)s, "
- "%(SJID)s, "
- "%(Name)s, "
- "%(SSubscription)s, "
- "%(SAsk)s, "
- "%(AskMessage)s, "
- "'N', '', 'item');"),
+ " username=%(LUser)s and %(LServer)H and jid=%(SJID)s;"),
+ ?SQL_INSERT(
+ "rosterusers",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "jid=%(SJID)s",
+ "nick=%(Name)s",
+ "subscription=%(SSubscription)s",
+ "ask=%(SAsk)s",
+ "askmessage=%(AskMessage)s",
+ "server='N'",
+ "subscribe=''",
+ "type='item'"]),
?SQL("delete from rostergroups where"
- " username=%(LUser)s and jid=%(SJID)s;")]
+ " username=%(LUser)s and %(LServer)H and jid=%(SJID)s;")]
++
- [?SQL("insert into rostergroups(username, jid, grp) "
- "values (%(LUser)s, %(SJID)s, %(ItemGroup)s);")
+ [?SQL_INSERT(
+ "rostergroups",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "jid=%(SJID)s",
+ "grp=%(ItemGroup)s"])
|| ItemGroup <- ItemGroups].
raw_to_record(LServer,
- [User, SJID, Nick, SSubscription, SAsk, SAskMessage,
+ [User, LServer, SJID, Nick, SSubscription, SAsk, SAskMessage,
_SServer, _SSubscribe, _SType]) ->
raw_to_record(LServer,
- {User, SJID, Nick, SSubscription, SAsk, SAskMessage,
+ {User, LServer, SJID, Nick, SSubscription, SAsk, SAskMessage,
_SServer, _SSubscribe, _SType});
raw_to_record(LServer,
{User, SJID, Nick, SSubscription, SAsk, SAskMessage,
_SServer, _SSubscribe, _SType}) ->
+ raw_to_record(LServer,
+ {User, LServer, SJID, Nick, SSubscription, SAsk, SAskMessage,
+ _SServer, _SSubscribe, _SType});
+raw_to_record(LServer,
+ {User, LServer, SJID, Nick, SSubscription, SAsk, SAskMessage,
+ _SServer, _SSubscribe, _SType}) ->
try jid:decode(SJID) of
JID ->
LJID = jid:tolower(JID),
@@ -331,7 +354,7 @@ raw_to_record(LServer,
end.
record_to_row(
- #roster{us = {LUser, _LServer},
+ #roster{us = {LUser, LServer},
jid = JID, name = Name, subscription = Subscription,
ask = Ask, askmessage = AskMessage}) ->
SJID = jid:encode(jid:tolower(JID)),
@@ -349,7 +372,7 @@ record_to_row(
in -> <<"I">>;
none -> <<"N">>
end,
- {LUser, SJID, Name, SSubscription, SAsk, AskMessage}.
+ {LUser, LServer, SJID, Name, SSubscription, SAsk, AskMessage}.
format_row_error(User, Server, Why) ->
[case Why of
diff --git a/src/mod_shared_roster_sql.erl b/src/mod_shared_roster_sql.erl
index 51b332455..488e0ec76 100644
--- a/src/mod_shared_roster_sql.erl
+++ b/src/mod_shared_roster_sql.erl
@@ -50,7 +50,7 @@ init(_Host, _Opts) ->
list_groups(Host) ->
case ejabberd_sql:sql_query(
Host,
- ?SQL("select @(name)s from sr_group")) of
+ ?SQL("select @(name)s from sr_group where %(Host)H")) of
{selected, Rs} -> [G || {G} <- Rs];
_ -> []
end.
@@ -58,7 +58,7 @@ list_groups(Host) ->
groups_with_opts(Host) ->
case ejabberd_sql:sql_query(
Host,
- ?SQL("select @(name)s, @(opts)s from sr_group"))
+ ?SQL("select @(name)s, @(opts)s from sr_group where %(Host)H"))
of
{selected, Rs} ->
[{G, mod_shared_roster:opts_to_binary(ejabberd_sql:decode_term(Opts))}
@@ -72,6 +72,7 @@ create_group(Host, Group, Opts) ->
?SQL_UPSERT_T(
"sr_group",
["!name=%(Group)s",
+ "!server_host=%(Host)s",
"opts=%(SOpts)s"])
end,
ejabberd_sql:sql_transaction(Host, F).
@@ -79,9 +80,9 @@ create_group(Host, Group, Opts) ->
delete_group(Host, Group) ->
F = fun () ->
ejabberd_sql:sql_query_t(
- ?SQL("delete from sr_group where name=%(Group)s")),
+ ?SQL("delete from sr_group where name=%(Group)s and %(Host)H")),
ejabberd_sql:sql_query_t(
- ?SQL("delete from sr_user where grp=%(Group)s"))
+ ?SQL("delete from sr_user where grp=%(Group)s and %(Host)H"))
end,
case ejabberd_sql:sql_transaction(Host, F) of
{atomic,{updated,_}} -> {atomic, ok};
@@ -91,7 +92,8 @@ delete_group(Host, Group) ->
get_group_opts(Host, Group) ->
case catch ejabberd_sql:sql_query(
Host,
- ?SQL("select @(opts)s from sr_group where name=%(Group)s")) of
+ ?SQL("select @(opts)s from sr_group"
+ " where name=%(Group)s and %(Host)H")) of
{selected, [{SOpts}]} ->
mod_shared_roster:opts_to_binary(ejabberd_sql:decode_term(SOpts));
_ -> error
@@ -103,6 +105,7 @@ set_group_opts(Host, Group, Opts) ->
?SQL_UPSERT_T(
"sr_group",
["!name=%(Group)s",
+ "!server_host=%(Host)s",
"opts=%(SOpts)s"])
end,
ejabberd_sql:sql_transaction(Host, F).
@@ -111,7 +114,8 @@ get_user_groups(US, Host) ->
SJID = make_jid_s(US),
case catch ejabberd_sql:sql_query(
Host,
- ?SQL("select @(grp)s from sr_user where jid=%(SJID)s")) of
+ ?SQL("select @(grp)s from sr_user"
+ " where jid=%(SJID)s and %(Host)H")) of
{selected, Rs} -> [G || {G} <- Rs];
_ -> []
end.
@@ -119,7 +123,8 @@ get_user_groups(US, Host) ->
get_group_explicit_users(Host, Group) ->
case catch ejabberd_sql:sql_query(
Host,
- ?SQL("select @(jid)s from sr_user where grp=%(Group)s")) of
+ ?SQL("select @(jid)s from sr_user"
+ " where grp=%(Group)s and %(Host)H")) of
{selected, Rs} ->
lists:map(
fun({JID}) ->
@@ -134,7 +139,8 @@ get_user_displayed_groups(LUser, LServer, GroupsOpts) ->
SJID = make_jid_s(LUser, LServer),
case catch ejabberd_sql:sql_query(
LServer,
- ?SQL("select @(grp)s from sr_user where jid=%(SJID)s")) of
+ ?SQL("select @(grp)s from sr_user"
+ " where jid=%(SJID)s and %(LServer)H")) of
{selected, Rs} ->
[{Group, proplists:get_value(Group, GroupsOpts, [])}
|| {Group} <- Rs];
@@ -146,7 +152,7 @@ is_user_in_group(US, Group, Host) ->
case catch ejabberd_sql:sql_query(
Host,
?SQL("select @(jid)s from sr_user where jid=%(SJID)s"
- " and grp=%(Group)s")) of
+ " and %(Host)H and grp=%(Group)s")) of
{selected, []} -> false;
_ -> true
end.
@@ -155,15 +161,18 @@ add_user_to_group(Host, US, Group) ->
SJID = make_jid_s(US),
ejabberd_sql:sql_query(
Host,
- ?SQL("insert into sr_user(jid, grp) values ("
- "%(SJID)s, %(Group)s)")).
+ ?SQL_INSERT(
+ "sr_user",
+ ["jid=%(SJID)s",
+ "server_host=%(Host)s",
+ "grp=%(Group)s"])).
remove_user_from_group(Host, US, Group) ->
SJID = make_jid_s(US),
F = fun () ->
ejabberd_sql:sql_query_t(
- ?SQL("delete from sr_user where jid=%(SJID)s and"
- " grp=%(Group)s")),
+ ?SQL("delete from sr_user where jid=%(SJID)s and %(Host)H"
+ " and grp=%(Group)s")),
ok
end,
ejabberd_sql:sql_transaction(Host, F).
@@ -173,9 +182,12 @@ export(_Server) ->
fun(Host, #sr_group{group_host = {Group, LServer}, opts = Opts})
when LServer == Host ->
SOpts = misc:term_to_expr(Opts),
- [?SQL("delete from sr_group where name=%(Group)s;"),
- ?SQL("insert into sr_group(name, opts) values ("
- "%(Group)s, %(SOpts)s);")];
+ [?SQL("delete from sr_group where name=%(Group)s and %(Host)H;"),
+ ?SQL_INSERT(
+ "sr_group",
+ ["name=%(Group)s",
+ "server_host=%(Host)s",
+ "opts=%(SOpts)s"])];
(_Host, _R) ->
[]
end},
@@ -184,9 +196,12 @@ export(_Server) ->
when LServer == Host ->
SJID = make_jid_s(U, S),
[?SQL("select @(jid)s from sr_user where jid=%(SJID)s"
- " and grp=%(Group)s;"),
- ?SQL("insert into sr_user(jid, grp) values ("
- "%(SJID)s, %(Group)s);")];
+ " and %(Host)H and grp=%(Group)s;"),
+ ?SQL_INSERT(
+ "sr_user",
+ ["jid=%(SJID)s",
+ "server_host=%(Host)s",
+ "grp=%(Group)s"])];
(_Host, _R) ->
[]
end}].
diff --git a/src/mod_vcard_sql.erl b/src/mod_vcard_sql.erl
index 6092c3500..07d90b69e 100644
--- a/src/mod_vcard_sql.erl
+++ b/src/mod_vcard_sql.erl
@@ -39,6 +39,12 @@
-include("ejabberd_sql_pt.hrl").
-include("translate.hrl").
+-ifdef(NEW_SQL_SCHEMA).
+-define(USE_NEW_SCHEMA, true).
+-else.
+-define(USE_NEW_SCHEMA, false).
+-endif.
+
%%%===================================================================
%%% API
%%%===================================================================
@@ -54,7 +60,8 @@ is_search_supported(_LServer) ->
get_vcard(LUser, LServer) ->
case ejabberd_sql:sql_query(
LServer,
- ?SQL("select @(vcard)s from vcard where username=%(LUser)s")) of
+ ?SQL("select @(vcard)s from vcard"
+ " where username=%(LUser)s and %(LServer)H")) of
{selected, [{SVCARD}]} ->
case fxml_stream:parse_element(SVCARD) of
{error, _Reason} -> error;
@@ -94,10 +101,12 @@ set_vcard(LUser, LServer, VCARD,
fun() ->
?SQL_UPSERT(LServer, "vcard",
["!username=%(LUser)s",
+ "!server_host=%(LServer)s",
"vcard=%(SVCARD)s"]),
?SQL_UPSERT(LServer, "vcard_search",
["username=%(User)s",
"!lusername=%(LUser)s",
+ "!server_host=%(LServer)s",
"fn=%(FN)s",
"lfn=%(LFN)s",
"family=%(Family)s",
@@ -183,9 +192,11 @@ remove_user(LUser, LServer) ->
LServer,
fun() ->
ejabberd_sql:sql_query_t(
- ?SQL("delete from vcard where username=%(LUser)s")),
+ ?SQL("delete from vcard"
+ " where username=%(LUser)s and %(LServer)H")),
ejabberd_sql:sql_query_t(
- ?SQL("delete from vcard_search where lusername=%(LUser)s"))
+ ?SQL("delete from vcard_search"
+ " where lusername=%(LUser)s and %(LServer)H"))
end).
export(_Server) ->
@@ -193,9 +204,12 @@ export(_Server) ->
fun(Host, #vcard{us = {LUser, LServer}, vcard = VCARD})
when LServer == Host ->
SVCARD = fxml:element_to_binary(VCARD),
- [?SQL("delete from vcard where username=%(LUser)s;"),
- ?SQL("insert into vcard(username, vcard) values ("
- "%(LUser)s, %(SVCARD)s);")];
+ [?SQL("delete from vcard"
+ " where username=%(LUser)s and %(LServer)H;"),
+ ?SQL_INSERT("vcard",
+ ["username=%(LUser)s",
+ "server_host=%(LServer)s",
+ "vcard=%(SVCARD)s"])];
(_Host, _R) ->
[]
end},
@@ -212,26 +226,34 @@ export(_Server) ->
orgname = OrgName, lorgname = LOrgName,
orgunit = OrgUnit, lorgunit = LOrgUnit})
when LServer == Host ->
- [?SQL("delete from vcard_search where lusername=%(LUser)s;"),
- ?SQL("insert into 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) values ("
- " %(LUser)s, %(User)s,"
- " %(FN)s, %(LFN)s,"
- " %(Family)s, %(LFamily)s,"
- " %(Given)s, %(LGiven)s,"
- " %(Middle)s, %(LMiddle)s,"
- " %(Nickname)s, %(LNickname)s,"
- " %(BDay)s, %(LBDay)s,"
- " %(CTRY)s, %(LCTRY)s,"
- " %(Locality)s, %(LLocality)s,"
- " %(EMail)s, %(LEMail)s,"
- " %(OrgName)s, %(LOrgName)s,"
- " %(OrgUnit)s, %(LOrgUnit)s);")];
+ [?SQL("delete from vcard_search"
+ " where lusername=%(LUser)s and %(LServer)H;"),
+ ?SQL_INSERT("vcard_search",
+ ["username=%(User)s",
+ "lusername=%(LUser)s",
+ "server_host=%(LServer)s",
+ "fn=%(FN)s",
+ "lfn=%(LFN)s",
+ "family=%(Family)s",
+ "lfamily=%(LFamily)s",
+ "given=%(Given)s",
+ "lgiven=%(LGiven)s",
+ "middle=%(Middle)s",
+ "lmiddle=%(LMiddle)s",
+ "nickname=%(Nickname)s",
+ "lnickname=%(LNickname)s",
+ "bday=%(BDay)s",
+ "lbday=%(LBDay)s",
+ "ctry=%(CTRY)s",
+ "lctry=%(LCTRY)s",
+ "locality=%(Locality)s",
+ "llocality=%(LLocality)s",
+ "email=%(EMail)s",
+ "lemail=%(LEMail)s",
+ "orgname=%(OrgName)s",
+ "lorgname=%(LOrgName)s",
+ "orgunit=%(OrgUnit)s",
+ "lorgunit=%(LOrgUnit)s"])];
(_Host, _R) ->
[]
end}].
@@ -245,10 +267,19 @@ import(_, _, _) ->
make_matchspec(LServer, Data) ->
filter_fields(Data, <<"">>, LServer).
-filter_fields([], Match, _LServer) ->
- case Match of
- <<"">> -> <<"">>;
- _ -> [<<" where ">>, Match]
+filter_fields([], Match, LServer) ->
+ case ?USE_NEW_SCHEMA of
+ true ->
+ SServer = ejabberd_sql:escape(LServer),
+ case Match of
+ <<"">> -> [<<"where server_host='">>, SServer, <<"'">>];
+ _ -> [<<" where server_host='">>, SServer, <<"' and ">>, Match]
+ end;
+ false ->
+ case Match of
+ <<"">> -> <<"">>;
+ _ -> [<<" where ">>, Match]
+ end
end;
filter_fields([{SVar, [Val]} | Ds], Match, LServer)
when is_binary(Val) and (Val /= <<"">>) ->
diff --git a/vars.config.in b/vars.config.in
index 47fc5dd44..ff549242d 100644
--- a/vars.config.in
+++ b/vars.config.in
@@ -25,6 +25,7 @@
{debug, @debug@}.
{hipe, @hipe@}.
{erlang_deprecated_types, @erlang_deprecated_types@}.
+{new_sql_schema, @new_sql_schema@}.
%% Ad-hoc directories with source files
{tools, @tools@}.
generated by cgit v1.2.3 (git 2.25.1) at 2024-12-04 19:17:40 +0000