openfire 분석 - 1

* openfire : 3.6.4

openfire을 설치를 하였습니다. 분석하기 위해서 다음과 같이 정의 해볼까 합니다.

* 로그인시 Query

  * SELECT name,startTime,endTime FROM ofUserFlag WHERE username=$1 AND name='lockout'
  * SELECT plainPassword,encryptedPassword FROM ofUser WHERE username=$1
  * SELECT name, email, creationDate, modificationDate FROM ofUser WHERE username=$1
  * SELECT name,startTime,endTime FROM ofUserFlag WHERE username=$1 AND name='lockout'
  * SELECT vcard FROM ofVCard WHERE username=$1
  * SELECT stanza, creationDate FROM ofOffline WHERE username=$1
  * SELECT DISTINCT serviceID FROM ofPubsubNode WHERE serviceID=$1
  * SELECT DISTINCT serviceID FROM ofPubsubNode WHERE serviceID=$1
  * DELETE FROM ofPresence WHERE username=$1
  * SELECT privateData FROM ofPrivate WHERE username=$1 AND namespace=$2
  * SELECT privateData FROM ofPrivate WHERE username=$1 AND namespace=$2

* 로그아웃시 Query

INSERT INTO ofPresence(username, offlinePresence, offlineDate) VALUES($1,$2,$3)

* 자리비움일 경우 Query

* DELETE FROM ofPresence WHERE username=$1

* 자리비움으로 자동 설정되는 Query

DELETE FROM ofPresence WHERE username=$1

* 계정생성시 Query

SELECT name, email, creationDate, modificationDate FROM ofUser WHERE username=$1
INSERT INTO ofUser (username,plainPassword,encryptedPassword,name,email,creationDate,modificationDate) VALUES ($1,$2,$3,$4,$5,$6,$7)

* 친구추가시

INSERT INTO ofRoster (username, rosterID, jid, sub, ask, recv, nick) VALUES ($1, $2, $3, $4, $5, $6, $7)
INSERT INTO ofRosterGroups (rosterID, rank, groupName) VALUES ($1, $2, $3)
UPDATE ofRoster SET sub=$1, ask=$2, recv=$3, nick=$4 WHERE rosterID=$5
DELETE FROM ofRosterGroups WHERE rosterID=$1
INSERT INTO ofRosterGroups (rosterID, rank, groupName) VALUES ($1, $2, $3)
INSERT INTO ofRoster (username, rosterID, jid, sub, ask, recv, nick) VALUES ($1, $2, $3, $4, $5, $6, $7)
UPDATE ofRoster SET sub=$1, ask=$2, recv=$3, nick=$4 WHERE rosterID=$5
DELETE FROM ofRosterGroups WHERE rosterID=$1