how can i create and remove users directly from the DB?
i mean, what tables and fields should i add and remove from the DB (mysql)?
is there any risks to use this instead of userimportexport plugin and admin console?
regards,
Rodrigo.
Hi Rodrigo,
You can add users directly to the database by using a simple insert statement like:
INSERT INTO jiveUser (username,password,name,email,creationDate,modificationDate)
VALUES (?,?,?,?,?,?);
/code
Username, password, creationDate and modificationDate are all required fields. The dates are stored as integers and their values are calculated by the number of milliseconds since January 1, 1970 00:00:00 GMT.
The problem with adding users to the database directly is that Messenger caches its user list and only refreshes every 6 hours or upon a restart, so it could be sometime before new users are recognized and are able to login or be viewed via the Admin Console. However, if a new user is created via the Admin Console, userImportExport plugin or through client registraion the Messenger API is used which updates the cache immediately so the new user is visible to everyone.
Hope that helps,
Ryan
hi Ryan,
and if i want to add them with contatcs (roster)? is that possible? what sould i do?
regards,
Rodrigo.
Hi Rodrigo,
and if i want to add them with contatcs (roster)? is
that possible? what sould i do?
Yes, it is possible, but it is a bit more involved since you have to do selects, updates and inserts to multiple tables, because of this, modifying user rosters while Messenger is running is not recommneded.
To add a roster item you can use the following insert statement:
INSERT INTO jiveRoster (username, rosterID, jid, sub, ask, recv, nick)
VALUES (?,?,?,?,?,?,?);
/code
Alll fields are required, username is the name of the user you are adding a contact to, rosterID comes from the jiveID table (primary key value of 18), jid is the full jid of the user you are adding to the roster, the values for sub/ask/rec can be url=http://www.jivesoftware.org/forums/message.jspa?messageID=101787#101787found here[/url], and the nick is the how the entry will appear in the user roster list.
The tricky thing is when you get the id (rosterID) from the jiveID table you will have to increment the id value and update the jiveID table. So you’'d have to do something like:
//get the next id
SELECT id FROM jiveID WHERE idType = 18;
//do your insert into the jiveRoster table with the id you just got
//increment the id by one
//then update the jiveID table
UPDATE jiveID SET id = ? WHERE idType = 18;
/code
To reiterate, you should not do this while Messenger is running as the sequence of the jveID table could get messed up.
Hope that helps,
Ryan
hi Ryan,
i’'m testing what you just saying. so just to let me know if i really understand.
i just don’'t get how do the field ‘‘id’’ type 18 in jiveID is increased. i mean, as you can see in this example below, the id is 6 and after i add the admin to sead.raphael and vice-versa the id jump to 11. why is that? and the field ‘‘rosterID’’ is an incremental field? i have to add incrementing by 1 from the greater one?
am i missing something?
thanks,
Rodrigo.
==========
mysql> select * from jiveRoster;
*----
*–
+
+-+-+–+–
rosterID
username
jid
sub
ask
recv
nick
*----
*–
+
+-+-+–+–
1
sead.rodrigo
3
-1
-1
NULL
2
sead.raphael
3
-1
-1
NULL
3
sead.rodrigo
3
-1
-1
admin
4
admin
3
-1
-1
sead.rodrigo
*----
*–
+
+-+-+–+–
4 rows in set (0.00 sec)
mysql> select * from jiveID;
*----
*----+
idType
id
*----
*----+
18
6
19
4
23
1
*----
*----+
3 rows in set (0.00 sec)
===========
=========
mysql> select * from jiveRoster;
*----
*–
+
+-+-+–+–
rosterID
username
jid
sub
ask
recv
nick
*----
*–
+
+-+-+–+–
1
sead.rodrigo
3
-1
-1
NULL
2
sead.raphael
3
-1
-1
NULL
3
sead.rodrigo
3
-1
-1
admin
4
admin
3
-1
-1
sead.rodrigo
5
sead.raphael
3
-1
-1
admin
6
admin
3
-1
-1
sead.raphael
*----
*–
+
+-+-+–+–
6 rows in set (0.00 sec)
mysql> select * from jiveID;
*----
*----+
idType
id
*----
*----+
18
11
19
4
23
1
*----
*----+
3 rows in set (0.00 sec)
==========
Hi Rodrigo,
am i missing something?
No, you are not missing anything. I should have mentioned in my last post that Messenger has a small optimization where it grabs id’‘s in batches of 5 so in some cases it won’'t have to go off to the database each time time a roster entry is made; take a look at the SequenceManager in the Messenger source to see exactly how this is done. This optimization is one of the reasons why it is best not to be doing these sorts of inserts while Messenger is running. However, if you are manually inserting roster entries you can just increment the id by one each time.
Hope that helps,
Ryan
hi Ryan,
i’‘ll try to do this. if something goes wrong, i’'ll bother you again
thanks,
Rodrigo.
So, when inserting new rosters, i incremented my jiveID by one. Should I have it incremented by 5?
If so, I will need to delete my entire roster (no problem) and redo it. But, what value do I inform as a inicial value for jiveID? So my ID’'s will start from the begining.
I really need to perform this via SQL and Im using MS SQL Server.
Regards
So, when inserting new rosters, i incremented my
jiveID by one. Should I have it incremented by 5?
No, you can increment by any amount, Messenger is just set to begin incrementing by 5.
-Ryan
And is there an initial values specified for the id? Or may I start with one?
EDIT: I can start with one. Sorry ''bout this one
Message was edited by:
marciorf