| CODENOTIFIER | HelpYou are not signed inSign in |
Project: ejabberd
Revision: 1595
Author: jsautret
Date: 03 Oct 2008 11:29:48
Changes: * src/mod_vcard_odbc: added vCard support for MS SQL Server 2005.
* src/odbc/odbc_queries.erl: likewise.
* src/odbc/mssql2005.sql: likewise.
| ... | ...@@ -10,7 +10,7 @@ | |
| 10 | 10 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
| 11 | 11 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
| 12 | 12 | * General Public License for more details. |
| 13 | * | |
| 13 | * | |
| 14 | 14 | * You should have received a copy of the GNU General Public License |
| 15 | 15 | * along with this program; if not, write to the Free Software |
| 16 | 16 | * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA |
| ... | ...@@ -104,6 +104,10 @@ | |
| 104 | 104 | drop table [dbo].[vcard] |
| 105 | 105 | GO |
| 106 | 106 | |
| 107 | if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vcard_search]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) | |
| 108 | drop table [dbo].[vcard_search] | |
| 109 | GO | |
| 110 | ||
| 107 | 111 | if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[private_storage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) |
| 108 | 112 | drop table [dbo].[private_storage] |
| 109 | 113 | GO |
| ... | ...@@ -119,7 +123,7 @@ | |
| 119 | 123 | CREATE TABLE [dbo].[rostergroups] ( |
| 120 | 124 | [username] [varchar] (250) NOT NULL , |
| 121 | 125 | [jid] [varchar] (250) NOT NULL , |
| 122 | [grp] [varchar] (100) NOT NULL | |
| 126 | [grp] [varchar] (100) NOT NULL | |
| 123 | 127 | ) ON [PRIMARY] |
| 124 | 128 | GO |
| 125 | 129 | |
| ... | ...@@ -133,7 +137,7 @@ | |
| 133 | 137 | [server] [char] (1) NOT NULL , |
| 134 | 138 | [subscribe] [varchar] (200) NULL , |
| 135 | 139 | [type] [varchar] (50) NULL , |
| 136 | CONSTRAINT [PK_rosterusers] PRIMARY KEY NONCLUSTERED | |
| 140 | CONSTRAINT [PK_rosterusers] PRIMARY KEY NONCLUSTERED | |
| 137 | 141 | ( |
| 138 | 142 | [username] ASC, |
| 139 | 143 | [jid] ASC |
| ... | ...@@ -154,31 +158,41 @@ | |
| 154 | 158 | CREATE TABLE [dbo].[users] ( |
| 155 | 159 | [username] [varchar] (250) NOT NULL , |
| 156 | 160 | [password] [varchar] (50) NOT NULL , |
| 157 | [created] [datetime] NULL | |
| 161 | [created] [datetime] NULL | |
| 158 | 162 | ) ON [PRIMARY] |
| 159 | 163 | GO |
| 160 | 164 | |
| 161 | 165 | CREATE TABLE [dbo].[vcard] ( |
| 162 | 166 | [username] [varchar] (250) NOT NULL , |
| 163 | [full_name] [varchar] (250) NULL , | |
| 164 | [first_name] [varchar] (50) NULL , | |
| 165 | [last_name] [varchar] (50) NULL , | |
| 166 | [nick_name] [varchar] (50) NULL , | |
| 167 | [url] [varchar] (1024) NULL , | |
| 168 | [address1] [varchar] (50) NULL , | |
| 169 | [address2] [varchar] (50) NULL , | |
| 170 | [locality] [varchar] (50) NULL , | |
| 171 | [region] [varchar] (50) NULL , | |
| 172 | [pcode] [varchar] (50) NULL , | |
| 173 | [country] [varchar] (50) NULL , | |
| 174 | [telephone] [varchar] (50) NULL , | |
| 175 | [email] [varchar] (250) NULL , | |
| 176 | [orgname] [varchar] (50) NULL , | |
| 177 | [orgunit] [varchar] (50) NULL , | |
| 178 | [title] [varchar] (50) NULL , | |
| 179 | [role] [varchar] (50) NULL , | |
| 180 | [b_day] [datetime] NULL , | |
| 181 | [descr] [varchar] (500) NULL | |
| 167 | [vcard] [text] NOT NULL | |
| 168 | ) ON [PRIMARY] | |
| 169 | GO | |
| 170 | ||
| 171 | CREATE TABLE [dbo].[vcard_search] ( | |
| 172 | [username] [varchar] (250) NOT NULL , | |
| 173 | [lusername] [varchar] (250) NOT NULL , | |
| 174 | [fn] [text] NOT NULL , | |
| 175 | [lfn] [varchar] (250) NOT NULL , | |
| 176 | [family] [text] NOT NULL , | |
| 177 | [lfamily] [varchar] (250) NOT NULL , | |
| 178 | [given] [text] NOT NULL , | |
| 179 | [lgiven] [varchar] (250) NOT NULL , | |
| 180 | [middle] [text] NOT NULL , | |
| 181 | [lmiddle] [varchar] (250) NOT NULL , | |
| 182 | [nickname] [text] NOT NULL , | |
| 183 | [lnickname] [varchar] (250) NOT NULL , | |
| 184 | [bday] [text] NOT NULL , | |
| 185 | [lbday] [varchar] (250) NOT NULL , | |
| 186 | [ctry] [text] NOT NULL , | |
| 187 | [lctry] [varchar] (250) NOT NULL , | |
| 188 | [locality] [text] NOT NULL , | |
| 189 | [llocality] [varchar] (250) NOT NULL , | |
| 190 | [email] [text] NOT NULL , | |
| 191 | [lemail] [varchar] (250) NOT NULL , | |
| 192 | [orgname] [text] NOT NULL , | |
| 193 | [lorgname] [varchar] (250) NOT NULL , | |
| 194 | [orgunit] [text] NOT NULL , | |
| 195 | [lorgunit] [varchar] (250) NOT NULL | |
| 182 | 196 | ) ON [PRIMARY] |
| 183 | 197 | GO |
| 184 | 198 | |
| ... | ...@@ -199,7 +213,7 @@ | |
| 199 | 213 | [username] [varchar](250) NOT NULL, |
| 200 | 214 | [name] [varchar](250) NOT NULL, |
| 201 | 215 | [id] [bigint] IDENTITY(1,1) NOT NULL, |
| 202 | CONSTRAINT [PK_privacy_list] PRIMARY KEY CLUSTERED | |
| 216 | CONSTRAINT [PK_privacy_list] PRIMARY KEY CLUSTERED | |
| 203 | 217 | ( |
| 204 | 218 | [id] ASC |
| 205 | 219 | )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] |
| ... | ...@@ -224,73 +238,98 @@ | |
| 224 | 238 | - id in privacy_list is a SERIAL autogenerated number |
| 225 | 239 | - id in privacy_list_data must exist in the table privacy_list */ |
| 226 | 240 | |
| 227 | ALTER TABLE [dbo].[last] WITH NOCHECK ADD | |
| 228 | CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED | |
| 241 | ALTER TABLE [dbo].[last] WITH NOCHECK ADD | |
| 242 | CONSTRAINT [PK_last] PRIMARY KEY CLUSTERED | |
| 229 | 243 | ( |
| 230 | 244 | [username] |
| 231 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 245 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 232 | 246 | GO |
| 233 | 247 | |
| 234 | ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD | |
| 235 | CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED | |
| 248 | ALTER TABLE [dbo].[rostergroups] WITH NOCHECK ADD | |
| 249 | CONSTRAINT [PK_rostergroups] PRIMARY KEY CLUSTERED | |
| 236 | 250 | ( |
| 237 | 251 | [username], |
| 238 | 252 | [jid], |
| 239 | 253 | [grp] |
| 240 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 254 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 241 | 255 | GO |
| 242 | 256 | |
| 243 | ALTER TABLE [dbo].[spool] WITH NOCHECK ADD | |
| 244 | CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED | |
| 257 | ALTER TABLE [dbo].[spool] WITH NOCHECK ADD | |
| 258 | CONSTRAINT [PK_spool] PRIMARY KEY CLUSTERED | |
| 245 | 259 | ( |
| 246 | 260 | [username], |
| 247 | 261 | [id] |
| 248 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 262 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 249 | 263 | GO |
| 250 | 264 | |
| 251 | ALTER TABLE [dbo].[users] WITH NOCHECK ADD | |
| 252 | CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED | |
| 265 | ALTER TABLE [dbo].[users] WITH NOCHECK ADD | |
| 266 | CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED | |
| 253 | 267 | ( |
| 254 | 268 | [username] |
| 255 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 269 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 256 | 270 | GO |
| 257 | 271 | |
| 258 | ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD | |
| 259 | CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED | |
| 272 | ALTER TABLE [dbo].[vcard] WITH NOCHECK ADD | |
| 273 | CONSTRAINT [PK_vcard] PRIMARY KEY CLUSTERED | |
| 260 | 274 | ( |
| 261 | 275 | [username] |
| 262 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 276 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 263 | 277 | GO |
| 264 | 278 | |
| 279 | ||
| 280 | CREATE INDEX [IX_vcard_search_lfn] ON [dbo].[vcard_search]([lfn]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 281 | GO | |
| 282 | CREATE INDEX [IX_vcard_search_lfamily] ON [dbo].[vcard_search]([lfamily]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 283 | GO | |
| 284 | CREATE INDEX [IX_vcard_search_lgiven] ON [dbo].[vcard_search]([lgiven]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 285 | GO | |
| 286 | CREATE INDEX [IX_vcard_search_lmiddle] ON [dbo].[vcard_search]([lmiddle]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 287 | GO | |
| 288 | CREATE INDEX [IX_vcard_search_lnickname] ON [dbo].[vcard_search]([lnickname]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 289 | GO | |
| 290 | CREATE INDEX [IX_vcard_search_lbday] ON [dbo].[vcard_search]([lbday]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 291 | GO | |
| 292 | CREATE INDEX [IX_vcard_search_lctry] ON [dbo].[vcard_search]([lctry]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 293 | GO | |
| 294 | CREATE INDEX [IX_vcard_search_llocality] ON [dbo].[vcard_search]([llocality]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 295 | GO | |
| 296 | CREATE INDEX [IX_vcard_search_lemail] ON [dbo].[vcard_search]([lemail]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 297 | GO | |
| 298 | CREATE INDEX [IX_vcard_search_lorgname] ON [dbo].[vcard_search]([lorgname]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 299 | GO | |
| 300 | CREATE INDEX [IX_vcard_search_lorgunit] ON [dbo].[vcard_search]([lorgunit]) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 301 | GO | |
| 302 | ||
| 303 | ||
| 265 | 304 | CREATE CLUSTERED INDEX [IX_rosterusers_user] ON [dbo].[rosterusers]([username]) WITH FILLFACTOR = 90 ON [PRIMARY] |
| 266 | 305 | GO |
| 267 | 306 | |
| 268 | ALTER TABLE [dbo].[last] WITH NOCHECK ADD | |
| 307 | ALTER TABLE [dbo].[last] WITH NOCHECK ADD | |
| 269 | 308 | CONSTRAINT [DF_last_updated] DEFAULT (getdate()) FOR [Modify_Date] |
| 270 | 309 | GO |
| 271 | 310 | |
| 272 | ALTER TABLE [dbo].[spool] WITH NOCHECK ADD | |
| 311 | ALTER TABLE [dbo].[spool] WITH NOCHECK ADD | |
| 273 | 312 | CONSTRAINT [DF_spool_notifyprocessed] DEFAULT (0) FOR [notifyprocessed], |
| 274 | 313 | CONSTRAINT [DF_spool_created] DEFAULT (getdate()) FOR [created], |
| 275 | 314 | CONSTRAINT [DF_spool_MustDelete] DEFAULT (0) FOR [MustDelete] |
| 276 | 315 | GO |
| 277 | 316 | |
| 278 | ALTER TABLE [dbo].[users] WITH NOCHECK ADD | |
| 317 | ALTER TABLE [dbo].[users] WITH NOCHECK ADD | |
| 279 | 318 | CONSTRAINT [DF_users_created] DEFAULT (getdate()) FOR [created] |
| 280 | 319 | GO |
| 281 | 320 | |
| 282 | ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD | |
| 283 | CONSTRAINT [PK_privacy_defaut_list] PRIMARY KEY CLUSTERED | |
| 321 | ALTER TABLE [dbo].[privacy_default_list] WITH NOCHECK ADD | |
| 322 | CONSTRAINT [PK_privacy_defaut_list] PRIMARY KEY CLUSTERED | |
| 284 | 323 | ( |
| 285 | 324 | [username] |
| 286 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 325 | ) WITH FILLFACTOR = 90 ON [PRIMARY] | |
| 287 | 326 | GO |
| 288 | 327 | |
| 289 | 328 | CREATE INDEX [IX_rostergroups_jid] ON [dbo].[rostergroups]([jid]) WITH FILLFACTOR = 90 ON [PRIMARY] |
| 290 | 329 | GO |
| 291 | 330 | |
| 292 | 331 | CREATE INDEX [IX_rostergroups_user] ON [dbo].[rostergroups]([username]) WITH FILLFACTOR = 90 ON [PRIMARY] |
| 293 | GO | |
| 332 | GO | |
| 294 | 333 | |
| 295 | 334 | CREATE INDEX [IX_spool_user] ON [dbo].[spool]([username]) WITH FILLFACTOR = 90 ON [PRIMARY] |
| 296 | 335 | GO |
| ... | ...@@ -385,7 +424,7 @@ | |
| 385 | 424 | @Type |
| 386 | 425 | ); |
| 387 | 426 | END |
| 388 | ||
| 427 | ||
| 389 | 428 | --- Update Roster Groups if exist else add group entry |
| 390 | 429 | IF NOT EXISTS (SELECT username FROM rostergroups WITH (NOLOCK) WHERE rostergroups.username=@Username AND rostergroups.jid=@JID AND rostergroups.grp=@Grp) |
| 391 | 430 | BEGIN |
| ... | ...@@ -543,12 +582,12 @@ | |
| 543 | 582 | @Password varchar(50) |
| 544 | 583 | AS |
| 545 | 584 | BEGIN |
| 546 | INSERT INTO users | |
| 547 | ( [username], | |
| 585 | INSERT INTO users | |
| 586 | ( [username], | |
| 548 | 587 | [password] |
| 549 | ) | |
| 550 | VALUES | |
| 551 | ( @Username, | |
| 588 | ) | |
| 589 | VALUES | |
| 590 | ( @Username, | |
| 552 | 591 | @Password |
| 553 | 592 | ); |
| 554 | 593 | END |
| ... | ...@@ -559,7 +598,7 @@ | |
| 559 | 598 | /** Update users password **/ |
| 560 | 599 | /******************************************************************/ |
| 561 | 600 | CREATE PROCEDURE [dbo].[set_password] |
| 562 | @Username varchar(200), | |
| 601 | @Username varchar(200), | |
| 563 | 602 | @Password varchar(50) |
| 564 | 603 | AS |
| 565 | 604 | BEGIN |
| ... | ...@@ -582,7 +621,7 @@ | |
| 582 | 621 | @Username varchar(200) |
| 583 | 622 | AS |
| 584 | 623 | BEGIN |
| 585 | SELECT users.password as password | |
| 624 | SELECT users.password as password | |
| 586 | 625 | FROM users WITH (NOLOCK) |
| 587 | 626 | WHERE username=@Username; |
| 588 | 627 | END |
| ... | ...@@ -594,7 +633,7 @@ | |
| 594 | 633 | /***************************************************************/ |
| 595 | 634 | CREATE PROCEDURE [dbo].[clean_spool_msg] |
| 596 | 635 | AS |
| 597 | DECLARE | |
| 636 | DECLARE | |
| 598 | 637 | @dt datetime, |
| 599 | 638 | @myRowCount int |
| 600 | 639 | BEGIN |
| ... | ...@@ -606,7 +645,7 @@ | |
| 606 | 645 | BEGIN |
| 607 | 646 | BEGIN TRANSACTION |
| 608 | 647 | SELECT @dt = DATEADD(d, -3, GETDATE()) |
| 609 | DELETE FROM spool | |
| 648 | DELETE FROM spool | |
| 610 | 649 | WITH (ROWLOCK) |
| 611 | 650 | WHERE (MustDelete=1) OR (Created < @dt); |
| 612 | 651 | |
| ... | ...@@ -640,11 +679,11 @@ | |
| 640 | 679 | AS |
| 641 | 680 | BEGIN |
| 642 | 681 | BEGIN TRANSACTION |
| 643 | DELETE FROM rosterusers | |
| 682 | DELETE FROM rosterusers | |
| 644 | 683 | WITH (ROWLOCK) |
| 645 | 684 | WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID); |
| 646 | ||
| 647 | DELETE FROM rostergroups | |
| 685 | ||
| 686 | DELETE FROM rostergroups | |
| 648 | 687 | WITH (ROWLOCK) |
| 649 | 688 | WHERE (rostergroups.username = @Username) AND (rostergroups.jid = @JID); |
| 650 | 689 | COMMIT |
| ... | ...@@ -660,7 +699,7 @@ | |
| 660 | 699 | @Username varchar(250) |
| 661 | 700 | AS |
| 662 | 701 | BEGIN |
| 663 | DELETE FROM spool | |
| 702 | DELETE FROM spool | |
| 664 | 703 | WITH (ROWLOCK) |
| 665 | 704 | WHERE spool.username=@Username; |
| 666 | 705 | END |
| ... | ...@@ -674,7 +713,7 @@ | |
| 674 | 713 | @Username varchar(200) |
| 675 | 714 | AS |
| 676 | 715 | BEGIN |
| 677 | DELETE FROM users | |
| 716 | DELETE FROM users | |
| 678 | 717 | WITH (ROWLOCK) |
| 679 | 718 | WHERE username=@Username; |
| 680 | 719 | END |
| ... | ...@@ -691,7 +730,7 @@ | |
| 691 | 730 | @Pwd varchar(50) |
| 692 | 731 | BEGIN |
| 693 | 732 | EXECUTE @Pwd = dbo.get_password @Username |
| 694 | DELETE FROM users | |
| 733 | DELETE FROM users | |
| 695 | 734 | WITH (ROWLOCK) |
| 696 | 735 | WHERE username=@Username |
| 697 | 736 | |
| ... | ...@@ -709,11 +748,11 @@ | |
| 709 | 748 | AS |
| 710 | 749 | BEGIN |
| 711 | 750 | BEGIN TRANSACTION |
| 712 | DELETE FROM rosterusers | |
| 751 | DELETE FROM rosterusers | |
| 713 | 752 | WITH (ROWLOCK) |
| 714 | 753 | WHERE rosterusers.username = @Username; |
| 715 | ||
| 716 | DELETE FROM rostergroups | |
| 754 | ||
| 755 | DELETE FROM rostergroups | |
| 717 | 756 | WITH (ROWLOCK) |
| 718 | 757 | WHERE rostergroups.username = @Username; |
| 719 | 758 | COMMIT |
| ... | ...@@ -739,9 +778,9 @@ | |
| 739 | 778 | FROM spool WITH (NOLOCK) |
| 740 | 779 | WHERE spool.username=@Username; |
| 741 | 780 | |
| 742 | DELETE spool | |
| 781 | DELETE spool | |
| 743 | 782 | WITH (ROWLOCK) |
| 744 | WHERE spool.username=@Username | |
| 783 | WHERE spool.username=@Username | |
| 745 | 784 | END |
| 746 | 785 | ELSE |
| 747 | 786 | BEGIN |
| ... | ...@@ -758,7 +797,7 @@ | |
| 758 | 797 | @Username varchar(250) |
| 759 | 798 | AS |
| 760 | 799 | BEGIN |
| 761 | SELECT last.seconds AS seconds, | |
| 800 | SELECT last.seconds AS seconds, | |
| 762 | 801 | last.state AS state |
| 763 | 802 | FROM last WITH (NOLOCK) |
| 764 | 803 | WHERE last.username=@Username; |
| ... | ...@@ -773,26 +812,26 @@ | |
| 773 | 812 | @Username varchar(250) |
| 774 | 813 | AS |
| 775 | 814 | DECLARE |
| 776 | @vRosterusers table( username varchar(1), | |
| 777 | jid varchar(1), | |
| 778 | nick varchar(1), | |
| 779 | subscription varchar(1), | |
| 780 | ask varchar(1), | |
| 781 | askmessage varchar(1), | |
| 782 | server varchar(1), | |
| 783 | subscribe varchar(1), | |
| 815 | @vRosterusers table( username varchar(1), | |
| 816 | jid varchar(1), | |
| 817 | nick varchar(1), | |
| 818 | subscription varchar(1), | |
| 819 | ask varchar(1), | |
| 820 | askmessage varchar(1), | |
| 821 | server varchar(1), | |
| 822 | subscribe varchar(1), | |
| 784 | 823 | type varchar(1)) |
| 785 | 824 | BEGIN |
| 786 | 825 | IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username = @Username) |
| 787 | 826 | BEGIN |
| 788 | SELECT rosterusers.username AS username, | |
| 789 | rosterusers.jid AS jid, | |
| 790 | rosterusers.nick AS nick, | |
| 791 | rosterusers.subscription AS subscription, | |
| 827 | SELECT rosterusers.username AS username, | |
| 828 | rosterusers.jid AS jid, | |
| 829 | rosterusers.nick AS nick, | |
| 830 | rosterusers.subscription AS subscription, | |
| 792 | 831 | rosterusers.ask AS ask, |
| 793 | 832 | rosterusers.askmessage AS askmessage, |
| 794 | rosterusers.server AS server, | |
| 795 | rosterusers.subscribe AS subscribe, | |
| 833 | rosterusers.server AS server, | |
| 834 | rosterusers.subscribe AS subscribe, | |
| 796 | 835 | rosterusers.type AS type |
| 797 | 836 | FROM rosterusers WITH (NOLOCK) |
| 798 | 837 | WHERE rosterusers.username = @Username; |
| ... | ...@@ -813,26 +852,26 @@ | |
| 813 | 852 | @JID varchar(250) |
| 814 | 853 | AS |
| 815 | 854 | DECLARE |
| 816 | @vRosterusers table( username varchar(1), | |
| 817 | jid varchar(1), | |
| 818 | nick varchar(1), | |
| 819 | subscription varchar(1), | |
| 855 | @vRosterusers table( username varchar(1), | |
| 856 | jid varchar(1), | |
| 857 | nick varchar(1), | |
| 858 | subscription varchar(1), | |
| 820 | 859 | ask varchar(1), |
| 821 | 860 | askmessage varchar(1), |
| 822 | server varchar(1), | |
| 823 | subscribe varchar(1), | |
| 861 | server varchar(1), | |
| 862 | subscribe varchar(1), | |
| 824 | 863 | type varchar(1)) |
| 825 | 864 | BEGIN |
| 826 | 865 | IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID)) |
| 827 | 866 | BEGIN |
| 828 | SELECT rosterusers.username AS username, | |
| 829 | rosterusers.jid AS jid, | |
| 830 | rosterusers.nick AS nick, | |
| 831 | rosterusers.subscription AS subscription, | |
| 867 | SELECT rosterusers.username AS username, | |
| 868 | rosterusers.jid AS jid, | |
| 869 | rosterusers.nick AS nick, | |
| 870 | rosterusers.subscription AS subscription, | |
| 832 | 871 | rosterusers.ask AS ask, |
| 833 | 872 | rosterusers.askmessage AS askmessage, |
| 834 | rosterusers.server AS server, | |
| 835 | rosterusers.subscribe AS subscribe, | |
| 873 | rosterusers.server AS server, | |
| 874 | rosterusers.subscribe AS subscribe, | |
| 836 | 875 | rosterusers.type AS type |
| 837 | 876 | FROM rosterusers WITH (NOLOCK) |
| 838 | 877 | WHERE (rosterusers.username = @Username) AND (rosterusers.jid = @JID); |
| ... | ...@@ -852,12 +891,12 @@ | |
| 852 | 891 | @Username varchar(200) |
| 853 | 892 | AS |
| 854 | 893 | DECLARE |
| 855 | @vrostergroups table( jid varchar(1), | |
| 894 | @vrostergroups table( jid varchar(1), | |
| 856 | 895 | grp varchar(1)) |
| 857 | 896 | BEGIN |
| 858 | 897 | IF EXISTS (SELECT username FROM rostergroups with (nolock) WHERE rostergroups.username = @Username) |
| 859 | 898 | BEGIN |
| 860 | SELECT rostergroups.jid AS jid, | |
| 899 | SELECT rostergroups.jid AS jid, | |
| 861 | 900 | rostergroups.grp AS grp |
| 862 | 901 | FROM rostergroups WITH (NOLOCK) |
| 863 | 902 | WHERE rostergroups.username = @Username; |
| ... | ...@@ -930,7 +969,7 @@ | |
| 930 | 969 | BEGIN |
| 931 | 970 | IF EXISTS (SELECT username FROM rosterusers with (nolock) WHERE rosterusers.username=@Username AND rosterusers.jid=@JID) |
| 932 | 971 | BEGIN |
| 933 | SELECT rosterusers.subscription AS subscription | |
| 972 | SELECT rosterusers.subscription AS subscription | |
| 934 | 973 | FROM rosterusers WITH (NOLOCK) |
| 935 | 974 | WHERE rosterusers.username=@Username AND rosterusers.jid=@JID; |
| 936 | 975 | END |
| ... | ...@@ -958,7 +997,7 @@ | |
| 958 | 997 | /******************************************************************/ |
| 959 | 998 | CREATE PROCEDURE [dbo].[set_last] |
| 960 | 999 | @Username varchar(250), |
| 961 | @Seconds varchar(50), | |
| 1000 | @Seconds varchar(50), | |
| 962 | 1001 | @State varchar(100) |
| 963 | 1002 | AS |
| 964 | 1003 | BEGIN |
| ... | ...@@ -1050,4 +1089,151 @@ | |
| 1050 | 1089 | GO |
| 1051 | 1090 | |
| 1052 | 1091 | |
| 1092 | /******************************************************************/ | |
| 1093 | /****** Object: StoredProcedure [dbo].[set_vcard] **/ | |
| 1094 | /** Set the user's vCard **/ | |
| 1095 | /******************************************************************/ | |
| 1096 | CREATE PROCEDURE [dbo].[set_vcard] | |
| 1097 | @VCard varchar(8000), | |
| 1098 | @Username varchar(250), | |
| 1099 | @Lusername varchar(250), | |
| 1100 | @Fn varchar(8000), | |
| 1101 | @Lfn varchar(250), | |
| 1102 | @Family varchar(8000), | |
| 1103 | @Lfamily varchar(250), | |
| 1104 | @Given varchar(8000), | |
| 1105 | @Lgiven varchar(250), | |
| 1106 | @Middle varchar(8000), | |
| 1107 | @Lmiddle varchar(250), | |
| 1108 | @Nickname varchar(8000), | |
| 1109 | @Lnickname varchar(250), | |
| 1110 | @Bday varchar(8000), | |
| 1111 | @Lbday varchar(250), | |
| 1112 | @Ctry varchar(8000), | |
| 1113 | @Lctry varchar(250), | |
| 1114 | @Locality varchar(8000), | |
| 1115 | @Llocality varchar(250), | |
| 1116 | @Email varchar(8000), | |
| 1117 | @Lemail varchar(250), | |
| 1118 | @Orgname varchar(8000), | |
| 1119 | @Lorgname varchar(250), | |
| 1120 | @Orgunit varchar(8000), | |
| 1121 | @Lorgunit varchar(250) | |
| 1122 | AS | |
| 1123 | BEGIN | |
| 1124 | IF EXISTS (SELECT username FROM vcard with (nolock) WHERE vcard.username = @Username) | |
| 1125 | BEGIN | |
| 1126 | UPDATE [vcard] | |
| 1127 | SET [vcard].username = @LUsername, | |
| 1128 | [vcard].vcard = @Vcard | |
| 1129 | WHERE vcard.username = @LUsername; | |
| 1130 | ||
| 1131 | UPDATE [vcard_search] | |
| 1132 | SET [vcard_search].username = @Username, | |
| 1133 | [vcard_search].lusername = @Lusername, | |
| 1134 | [vcard_search].fn = @Fn, | |
| 1135 | [vcard_search].lfn = @Lfn, | |
| 1136 | [vcard_search].family = @Family, | |
| 1137 | [vcard_search].lfamily = @Lfamily, | |
| 1138 | [vcard_search].given = @Given, | |
| 1139 | [vcard_search].lgiven = @Lgiven, | |
| 1140 | [vcard_search].middle = @Middle, | |
| 1141 | [vcard_search].lmiddle = @Lmiddle, | |
| 1142 | [vcard_search].nickname = @Nickname, | |
| 1143 | [vcard_search].lnickname = @Lnickname, | |
| 1144 | [vcard_search].bday = @Bday, | |
| 1145 | [vcard_search].lbday = @Lbday, | |
| 1146 | [vcard_search].ctry = @Ctry, | |
| 1147 | [vcard_search].lctry = @Lctry, | |
| 1148 | [vcard_search].locality = @Locality, | |
| 1149 | [vcard_search].llocality = @Llocality, | |
| 1150 | [vcard_search].email = @Email, | |
| 1151 | [vcard_search].lemail = @Lemail, | |
| 1152 | [vcard_search].orgname = @Orgname, | |
| 1153 | [vcard_search].lorgname = @Lorgname, | |
| 1154 | [vcard_search].orgunit = @Orgunit, | |
| 1155 | [vcard_search].lorgunit = @Lorgunit | |
| 1156 | WHERE vcard_search.lusername = @LUsername; | |
| 1157 | END | |
| 1158 | ELSE | |
| 1159 | BEGIN | |
| 1160 | INSERT INTO [vcard] | |
| 1161 | ( [vcard].username, | |
| 1162 | [vcard].vcard | |
| 1163 | ) | |
| 1164 | VALUES | |
| 1165 | ( @lUsername, | |
| 1166 | @Vcard | |
| 1167 | ); | |
| 1053 | 1168 | |
| 1169 | INSERT INTO [vcard_search] | |
| 1170 | ( | |
| 1171 | [vcard_search].username , | |
| 1172 | [vcard_search].lusername , | |
| 1173 | [vcard_search].fn , | |
| 1174 | [vcard_search].lfn , | |
| 1175 | [vcard_search].family , | |
| 1176 | [vcard_search].lfamily , | |
| 1177 | [vcard_search].given , | |
| 1178 | [vcard_search].lgiven , | |
| 1179 | [vcard_search].middle , | |
| 1180 | [vcard_search].lmiddle , | |
| 1181 | [vcard_search].nickname, | |
| 1182 | [vcard_search].lnickname, | |
| 1183 | [vcard_search].bday, | |
| 1184 | [vcard_search].lbday, | |
| 1185 | [vcard_search].ctry, | |
| 1186 | [vcard_search].lctry, | |
| 1187 | [vcard_search].locality, | |
| 1188 | [vcard_search].llocality, | |
| 1189 | [vcard_search].email, | |
| 1190 | [vcard_search].lemail, | |
| 1191 | [vcard_search].orgname, | |
| 1192 | [vcard_search].lorgname, | |
| 1193 | [vcard_search].orgunit, | |
| 1194 | [vcard_search].lorgunit | |
| 1195 | ) | |
| 1196 | VALUES | |
| 1197 | ( | |
| 1198 | @Username, | |
| 1199 | @Lusername, | |
| 1200 | @Fn, | |
| 1201 | @Lfn, | |
| 1202 | @Family, | |
| 1203 | @Lfamily, | |
| 1204 | @Given, | |
| 1205 | @Lgiven, | |
| 1206 | @Middle, | |
| 1207 | @Lmiddle, | |
| 1208 | @Nickname, | |
| 1209 | @Lnickname, | |
| 1210 | @Bday, | |
| 1211 | @Lbday, | |
| 1212 | @Ctry, | |
| 1213 | @Lctry, | |
| 1214 | @Locality, | |
| 1215 | @Llocality, | |
| 1216 | @Email, | |
| 1217 | @Lemail, | |
| 1218 | @Orgname, | |
| 1219 | @Lorgname, | |
| 1220 | @Orgunit, | |
| 1221 | @Lorgunit | |
| 1222 | ) | |
| 1223 | END | |
| 1224 | END | |
| 1225 | GO | |
| 1226 | ||
| 1227 | /******************************************************************/ | |
| 1228 | /****** Object: StoredProcedure [dbo].[get_vcard] **/ | |
| 1229 | /** Retrive the user's vCard **/ | |
| 1230 | /******************************************************************/ | |
| 1231 | CREATE PROCEDURE [dbo].[get_vcard] | |
| 1232 | @Username varchar(250) | |
| 1233 | AS | |
| 1234 | BEGIN | |
| 1235 | SELECT vcard.vcard as vcard | |
| 1236 | FROM vcard WITH (NOLOCK) | |
| 1237 | WHERE username=@Username; | |
| 1238 | END | |
| 1239 | GO |
| ... | ...@@ -61,6 +61,8 @@ | |
| 61 | 61 | set_private_data_sql/3, |
| 62 | 62 | get_private_data/3, |
| 63 | 63 | del_user_private_storage/2, |
| 64 | set_vcard/26, | |
| 65 | get_vcard/2, | |
| 64 | 66 | escape/1, |
| 65 | 67 | count_records_where/3]). |
| 66 | 68 | |
| ... | ...@@ -378,6 +380,42 @@ | |
| 378 | 380 | LServer, |
| 379 | 381 | ["delete from private_storage where username='", Username, "';"]). |
| 380 | 382 | |
| 383 | ||
| 384 | set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, SFamily, SGiven, | |
| 385 | SLBDay, SLCTRY, SLEMail, SLFN, SLFamily, SLGiven, SLLocality, | |
| 386 | SLMiddle, SLNickname, SLOrgName, SLOrgUnit, SLocality, SMiddle, | |
| 387 | SNickname, SOrgName, SOrgUnit, SVCARD, Username) -> | |
| 388 | ejabberd_odbc:sql_transaction( | |
| 389 | LServer, | |
| 390 | [["delete from vcard where username='", LUsername, "';"], | |
| 391 | ["insert into vcard(username, vcard) " | |
| 392 | "values ('", LUsername, "', '", SVCARD, "');"], | |
| 393 | ["delete from vcard_search where lusername='", LUsername, "';"], | |
| 394 | ["insert into vcard_search(" | |
| 395 | " username, lusername, fn, lfn, family, lfamily," | |
| 396 | " given, lgiven, middle, lmiddle, nickname, lnickname," | |
| 397 | " bday, lbday, ctry, lctry, locality, llocality," | |
| 398 | " email, lemail, orgname, lorgname, orgunit, lorgunit)" | |
| 399 | "values (", | |
| 400 | " '", Username, "', '", LUsername, "'," | |
| 401 | " '", SFN, "', '", SLFN, "'," | |
| 402 | " '", SFamily, "', '", SLFamily, "'," | |
| 403 | " '", SGiven, "', '", SLGiven, "'," | |
| 404 | " '", SMiddle, "', '", SLMiddle, "'," | |
| 405 | " '", SNickname, "', '", SLNickname, "'," | |
| 406 | " '", SBDay, "', '", SLBDay, "'," | |
| 407 | " '", SCTRY, "', '", SLCTRY, "'," | |
| 408 | " '", SLocality, "', '", SLLocality, "'," | |
| 409 | " '", SEMail, "', '", SLEMail, "'," | |
| 410 | " '", SOrgName, "', '", SLOrgName, "'," | |
| 411 | " '", SOrgUnit, "', '", SLOrgUnit, "');"]]). | |
| 412 | ||
| 413 | get_vcard(LServer, Username) -> | |
| 414 | ejabberd_odbc:sql_query( | |
| 415 | LServer, | |
| 416 | ["select vcard from vcard " | |
| 417 | "where username='", Username, "';"]). | |
| 418 | ||
| 381 | 419 | %% Characters to escape |
| 382 | 420 | escape($\0) -> "\\0"; |
| 383 | 421 | escape($\n) -> "\\n"; |
| ... | ...@@ -597,6 +635,25 @@ | |
| 597 | 635 | LServer, |
| 598 | 636 | ["EXECUTE dbo.del_user_storage '", Username, "'"]). |
| 599 | 637 | |
| 638 | set_vcard(LServer, LUsername, SBDay, SCTRY, SEMail, SFN, SFamily, SGiven, | |
| 639 | SLBDay, SLCTRY, SLEMail, SLFN, SLFamily, SLGiven, SLLocality, | |
| 640 | SLMiddle, SLNickname, SLOrgName, SLOrgUnit, SLocality, SMiddle, | |
| 641 | SNickname, SOrgName, SOrgUnit, SVCARD, Username) -> | |
| 642 | ejabberd_odbc:sql_query( | |
| 643 | LServer, | |
| 644 | ["EXECUTE dbo.set_vcard '", SVCARD, "' , '", Username, "' , '", LUsername, "' , '", | |
| 645 | SFN, "' , '", SLFN, "' , '", SFamily, "' , '", SLFamily, "' , '", | |
| 646 | SGiven, "' , '", SLGiven, "' , '", SMiddle, "' , '", SLMiddle, "' , '", | |
| 647 | SNickname, "' , '", SLNickname, "' , '", SBDay, "' , '", SLBDay, "' , '", | |
| 648 | SCTRY, "' , '", SLCTRY, "' , '", SLocality, "' , '", SLLocality, "' , '", | |
| 649 | SEMail, "' , '", SLEMail, "' , '", SOrgName, "' , '", SLOrgName, "' , '", | |
| 650 | SOrgUnit, "' , '", SLOrgUnit, "'"]). | |
| 651 | ||
| 652 | get_vcard(LServer, Username) -> | |
| 653 | ejabberd_odbc:sql_query( | |
| 654 | LServer, | |
| 655 | ["EXECUTE dbo.get_vcard '", Username, "'"]). | |
| 656 | ||
| 600 | 657 | %% Characters to escape |
| 601 | 658 | escape($\0) -> "\\0"; |
| 602 | 659 | escape($\t) -> "\\t"; |
| ... | ...@@ -1,3 +1,9 @@ | |
| 1 | 2008-10-03 Jerome Sautret <jerome.sautret@process-one.net> | |
| 2 | ||
| 3 | * src/mod_vcard_odbc: added vCard support for MS SQL Server 2005. | |
| 4 | * src/odbc/odbc_queries.erl: likewise. | |
| 5 | * src/odbc/mssql2005.sql: likewise. | |
| 6 | ||
| 1 | 7 | 2008-10-01 Mickael Remond <mremond@process-one.net> |
| 2 | 8 | |
| 3 | 9 | * src/mod_shared_roster.erl: Correct roster push when changing |
| ... | ...@@ -8,7 +14,7 @@ | |
| 8 | 14 | * src/*/Makefile.win32: Provide explicit beam filenames because |
| 9 | 15 | nmake does not accept wildcards (thanks to Attila |
| 10 | 16 | Vangel)(EJAB-543) |
| 11 | ||
| 17 | ||
| 12 | 18 | 2008-09-24 Christophe Romain <christophe.romain@process-one.net> |
| 13 | 19 | |
| 14 | 20 | * src/mod_pubsub/mod_pubsub.erl: Allow PEP node type to be mapped with |