Support for roster versioning (EJAB-964)

Introduces two options for mod_roster and mod_roster_odbc:
- {versioning, true | false}   Enable or disable roster versioning on ejabberd.
- {store_current_id, true | false}   If true, the current roster version is stored on DB (internal or odbc). Otherwise it is calculated on the fly each time.

Performance:
Setting store_current_id to true should help in reducing the load for both ejabberd and the DB.

Details: 
If store_current_id is false,  the roster version is a hash of the entire roster. If store_current_id is true, the roster version is a hash, but of the current time
(this has to do with transactional semantics; we need to perform both the roster update and the version update on the same transaction, but we don't   
have the entire roster when we are changing a single item on DB. Loading it there requires significant changes to be introduced, so I opted for this simpler approach).

In either case, there is no difference for the clients, the roster version ID is opaque.

IMPORTANT:
mod_shared_roster is not compatible with the option 'store_current_id'.  Shared roster and roster versioning can be both enabled, but store_current_id MUST be set to false.

SVN Revision: 2428

4 files changed, 44 insertions, 1 deletions

diff --git a/src/odbc/mssql2000.sql b/src/odbc/mssql2000.sql
index 1bb93d783..89429a210 100644
--- a/src/odbc/mssql2000.sql
+++ b/src/odbc/mssql2000.sql
@@ -209,6 +209,14 @@ CREATE TABLE [dbo].[privacy_list_data] (
 ) ON [PRIMARY]

 GO

 

+/* Not tested on mssql */

+CREATE TABLE [dbo].[roster_version] (

+	[username] [varchar] (250) NOT NULL ,

+	[version] [varchar] (64) NOT NULL 

+) ON [PRIMARY]

+GO

+

+

 /* Constraints to add:

 - id in privacy_list is a SERIAL autogenerated number

 - id in privacy_list_data must exist in the table privacy_list */

@@ -244,6 +252,13 @@ ALTER TABLE [dbo].[users] WITH NOCHECK ADD
 	) WITH  FILLFACTOR = 90  ON [PRIMARY] 

 GO

 

+ALTER TABLE [dbo].[roster_version] WITH NOCHECK ADD 

+	CONSTRAINT [PK_roster_version] PRIMARY KEY  CLUSTERED 

+	(

+		[username]

+	) WITH  FILLFACTOR = 90  ON [PRIMARY] 

+GO

+

 ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD 

 	CONSTRAINT [PK_vcard] PRIMARY KEY  CLUSTERED 

 	(

@@ -275,6 +290,8 @@ ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD
 	) WITH  FILLFACTOR = 90  ON [PRIMARY] 

 GO

 

+

+

  CREATE  INDEX [IX_rostergroups_jid] ON [dbo].[rostergroups]([jid]) WITH  FILLFACTOR = 90 ON [PRIMARY]

 GO

 

diff --git a/src/odbc/mysql.sql b/src/odbc/mysql.sql
index e975b9231..dfbf69437 100644
--- a/src/odbc/mysql.sql
+++ b/src/odbc/mysql.sql
@@ -148,6 +148,12 @@ CREATE TABLE private_storage (
 CREATE INDEX i_private_storage_username USING BTREE ON private_storage(username);
 CREATE UNIQUE INDEX i_private_storage_username_namespace USING BTREE ON private_storage(username(75), namespace(75));
 
+-- Not tested in mysql
+CREATE TABLE roster_version (
+    username varchar(250) PRIMARY KEY,
+    version text NOT NULL
+) CHARACTER SET utf8;
+
 -- To update from 1.x:
 -- ALTER TABLE rosterusers ADD COLUMN askmessage text AFTER ask;
 -- UPDATE rosterusers SET askmessage = '';
diff --git a/src/odbc/odbc_queries.erl b/src/odbc/odbc_queries.erl
index ef1f59218..f70b8fb35 100644
--- a/src/odbc/odbc_queries.erl
+++ b/src/odbc/odbc_queries.erl
@@ -78,7 +78,9 @@
 	 set_vcard/26,
 	 get_vcard/2,
 	 escape/1,
-	 count_records_where/3]).
+	 count_records_where/3,
+	 get_roster_version/2,
+	 set_roster_version/2]).
 
 %% We have only two compile time options for db queries:
 %-define(generic, true).
@@ -555,6 +557,13 @@ count_records_where(LServer, Table, WhereClause) ->
     ejabberd_odbc:sql_query(
       LServer,
       ["select count(*) from ", Table, " ", WhereClause, ";"]).
+
+
+get_roster_version(LServer, LUser) ->
+	ejabberd_odbc:sql_query(LServer,
+		["select version from roster_version where username = '", LUser, "'"]).
+set_roster_version(LUser, Version) ->
+	update_t("roster_version", ["username", "version"], [LUser, Version], ["username = '", LUser, "'"]).
 -endif.
 
 %% -----------------
@@ -791,4 +800,10 @@ count_records_where(LServer, Table, WhereClause) ->
     ejabberd_odbc:sql_query(
       LServer,
       ["select count(*) from ", Table, " ", WhereClause, " with (nolock)"]).
+
+get_roster_version(LServer, LUser) ->
+	ejabberd_odbc:sql_query(LServer, 
+		["select version from dbo.roster_version where username = '", LUser, "'"]).
+set_roster_version(LUser, Version) ->
+	update_t("dbo.roster_version", ["username", "version"], [LUser, Version], ["username = '", LUser, "'"]).
 -endif.
diff --git a/src/odbc/pg.sql b/src/odbc/pg.sql
index 3cf3a0949..2273ad954 100644
--- a/src/odbc/pg.sql
+++ b/src/odbc/pg.sql
@@ -146,6 +146,11 @@ CREATE INDEX i_private_storage_username ON private_storage USING btree (username
 CREATE UNIQUE INDEX i_private_storage_username_namespace ON private_storage USING btree (username, namespace);
 
 
+CREATE TABLE roster_version (
+    username text PRIMARY KEY,
+    version text NOT NULL
+);
+
 -- To update from 0.9.8:
 -- CREATE SEQUENCE spool_seq_seq;
 -- ALTER TABLE spool ADD COLUMN seq integer;