diff options
| -rw-r--r-- | configure.ac | 9 | ||||
| -rw-r--r-- | include/ejabberd_sql_pt.hrl | 3 | ||||
| -rw-r--r-- | rebar.config | 1 | ||||
| -rw-r--r-- | sql/pg.new.sql | 604 | ||||
| -rw-r--r-- | src/ejabberd_auth_mnesia.erl | 1 | ||||
| -rw-r--r-- | src/ejabberd_auth_riak.erl | 9 | ||||
| -rw-r--r-- | src/ejabberd_auth_sql.erl | 71 | ||||
| -rw-r--r-- | src/ejabberd_sm_sql.erl | 6 | ||||
| -rw-r--r-- | src/ejabberd_sql_pt.erl | 200 | ||||
| -rw-r--r-- | src/mod_admin_update_sql.erl | 365 | ||||
| -rw-r--r-- | src/mod_announce_sql.erl | 27 | ||||
| -rw-r--r-- | src/mod_carboncopy_sql.erl | 5 | ||||
| -rw-r--r-- | src/mod_irc_sql.erl | 14 | ||||
| -rw-r--r-- | src/mod_last_sql.erl | 14 | ||||
| -rw-r--r-- | src/mod_mam_sql.erl | 119 | ||||
| -rw-r--r-- | src/mod_muc_sql.erl | 43 | ||||
| -rw-r--r-- | src/mod_offline_sql.erl | 39 | ||||
| -rw-r--r-- | src/mod_privacy_sql.erl | 83 | ||||
| -rw-r--r-- | src/mod_private_sql.erl | 20 | ||||
| -rw-r--r-- | src/mod_push_sql.erl | 34 | ||||
| -rw-r--r-- | src/mod_roster.erl | 8 | ||||
| -rw-r--r-- | src/mod_roster_sql.erl | 115 | ||||
| -rw-r--r-- | src/mod_shared_roster_sql.erl | 53 | ||||
| -rw-r--r-- | src/mod_vcard_sql.erl | 91 | ||||
| -rw-r--r-- | vars.config.in | 1 |
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@}. |