[JDEV] xdb_sql: queries

[Mike Shoyher]

Hello jdev,

I'm trying to make Oracle interface for xdb_sql, run into several
problems. The database structure is mostly copied from Postgres with
obvious changes. I would appreciate some advice on what I am doing
wrong

The first problem is registration. I see following sequence of events

oracle_query: SELECT password FROM users WHERE username = '1 at domain.com'

[ .. ]

Sun Dec 29 08:34:32 2002  deliver.c:474 DELIVER 2:domain.com <xdb
type='set' to='1 at domain.com' from='sessions' ns='jabber:iq:last' id='2'><query xmlns='jabber:iq:last'
last='1041179672'>Registered</query></xdb>

[ .. ]

Sun Dec 29 08:34:32 2002  xdb_sql.c:124 modules = 8153498
oracle_query: DELETE FROM last WHERE username = '1 at domain.com'
oracle_query: INSERT INTO last (username, seconds, state) VALUES
('1 at domain.com', '1041179672', 'Registered')


At this point the user isn't created yet so last.username which has
users.username as a parent cannot be set to 1 at domain.com, so we fail
foreign key integrity check

Sun Dec 29 08:34:32 2002  deliver.c:474 DELIVER 2:domain.com <xdb
type='set' to='1 at domain.com' from='sessions' ns='jabber:iq:auth' id='3'>
<password xmlns='jabber:iq:auth'>1</password></xdb>

[..]

oracle_query: INSERT INTO users (username, password) VALUES ('1 at domain.com', '1')

Now user is created

Sun Dec 29 08:34:32 2002  deliver.c:474 DELIVER 2:domain.com <xdb type='set' to='1 at domain.com'
from='sessions' ns='jabber:iq:auth:0k' id='4'/>

[..]

oracle_query: DELETE FROM users0k WHERE username = '1 at domain.com'
oracle_query: INSERT INTO users0k (username, hash, token, sequence)
VALUES ('1 at domain.com', 'set', '[<81>Г╓я', 'Р<99>^')

This one fails. The values aren't ASCII so substituting them as strings
doesn't work. It may work for other databases though.

Also I tried to insert users to the roster

Sun Dec 29 09:00:00 2002  deliver.c:474 DELIVER 2:domain.com <xdb
type='set' to='6 at domain.com' from='sessions' ns='jabber:iq:roster' id='11'>
<query xmlns='jabber:iq:roster'><item jid='7 at domain.com' subscription='none'/></query></xdb>

oracle_query: DELETE FROM rostergroups WHERE username = '6 at domain.com'
xdboracle_free_result: done
oracle_query: INSERT INTO rosterusers (username,jid,nick,subscription,ask,server,subscribe,type)
VALUES ('6 at domain.com','7 at domain.com',' ','N','-','',' ','item')

Server is empty here while marked as NOT NULL in sample_database so
the query fails. What is 'server' here? Also my client (wxScabber)
sends the 'subscribe' string way longer that 10 chars. It makes
following query which fails

 <xdb type='set' to='7 at domain.com' from='sessions' ns='jabber:iq:roster' id='16'><query xmlns='jabber:iq:roster'>
 <item jid='6 at domain.com' subscription='none' subscribe='Please let me subscribe.' hidden=''/></query></xdb>

INSERT INTO rosterusers (username,jid,nick,subscription,ask,server,subscribe,type)
VALUES ('7 at domain.com','6 at domain.com',' ','N','-','','Please let me subscribe.','item')

What is the proper length of the subscribe field?

Thanks

-- 
Best regards,
 Mike                          mailto:mike at shoyher.com