File:    xmlBlaster/doc/jdbc/admin.txt
Comment: New Database schema for xmlBlaster http://www.xmlBlaster.org
Date:    2008-11-16


Note 1: durable='F' means 'F' == False: transient, swapped messages ('T' means True = persistent messages)

Note 2: All I_MAP entries (xbtype='topicStore', 'session', 'subscribe') have a one-to-one relation for xbref/xbmeat
        except the 'msgUnitStore'.
        The reference counter queue like 'history' 'subject' 'callback' have a 'xbref' many-to-one relation to 'msgUnitStore'.

Note 3: The rules apply if xbstore/xbref/xbmeat are running in same database
        This covers also if QueuePlugin and StoragePlugin are one RAM and the other JDBC

Note 4: In cluster environment you have to set where condition xbnode='heron' (your node you work on)

Note 5: Many cluster nodes may coexist in same database


Rule 1: If xmlBlaster node is stopped: No transient swapped messages
        You should delete all xbref.xbdurable='F' or xbmeat.xbdurable='F' of this node
                  delete from xbref where xbdurable='F';
                  delete from xbmeat where xbdurable='F';

Rule 2: xmlBlaster running or stopped:
        All xbmeat with xbmeat.xbdurable='T' which have NO xbref can be deleted (for this node)
        delete from xbmeat where xbmeatid in (select xbmeat.xbmeatid from xbref right outer join xbmeat on (xbmeat.xbmeatid=xbref.xbmeatid) where xbrefid is null AND xbmeat.xbdurable='T');

Rule 3: xmlBlaster running or stopped:
        All xbref with xbref.xbdurable='T' which have NO xbmeat can be deleted (for this node)
        delete from xbref  where xbrefid  in (select xbref.xbrefid   from xbref left  outer join xbmeat on (xbmeat.xbmeatid=xbref.xbmeatid) where xbmeat.xbmeatid is null AND xbref.xbdurable='T');

Rule 4: No xbref and No xbmeat can exist without xbstore (which is checked by DB constraint)

Rule 5: If xmlBlaster node is stopped:
        No xbstore shall exist without xbref or xbmeat (for the stopped node)

Rule 6: If xmlBlaster node is stopped:
        No login session with negative pubSessionId
        TODO: How to query xbpostfix like 'client_%-<number>'


Optional index you can add:
                  ?? alter table xbref add constraint fkxbmeatref foreign key (xbmeatid, xbstoreid) references xbmeat;
        create index IX_MEAT_DURABLE ON XBMEAT (xbdurable);
        create index IX_REF_DURABLE ON XBREF (xbdurable);
        create index IX_REF_PRIO ON XBREF (xbprio);
        drop index IX_MEAT_DURABLE ON XBMEAT;
        drop index IX_REF_DURABLE ON XBREF;
        drop index IX_REF_PRIO ON XBREF;


-- Count number of meat which have at least one reference
select count(*) from (select distinct xbmeat.xbmeatid from xbref left outer join xbmeat on (xbref.xbmeatid=xbmeat.xbmeatid)) as foo;

-- Artificially create some leaks:
delete from xbref where xbmeatid in (1226442353154000000, 1226442352435000000, 1226425291898000000);

-- Find leaks of meat (do not delete on running xmlBlaster as it would destroy swapped entries!)
select count(*) from xbref right outer join xbmeat on (xbmeat.xbmeatid=xbref.xbmeatid) where xbrefid is null;
select xbmeat.xbmeatid from xbref right outer join xbmeat on (xbmeat.xbmeatid=xbref.xbmeatid) where xbrefid is null;

-- Find leaks of durable meat with running xmlBlaster
select count(*) from xbref right outer join xbmeat on (xbmeat.xbmeatid=xbref.xbmeatid) where xbrefid is null AND xbmeat.xbdurable='T';

-- Find session entries and showing the QoS
select s.xbstoreid, s.xbtype, m.xbmeatid, m.xbmsgqos from xbstore s left outer join xbmeat m on s.xbstoreid=m.xbstoreid where s.xbtype='session';

-- Show connection queue entries of user publisher/1
select s.xbstoreid, s.xbtype, m.xbmeatid, m.xbdatatype from xbstore s left outer join xbmeat m on s.xbstoreid=m.xbstoreid where s.xbtype='connection' and s.xbpostfix='client/publisher/1';   

-- Show callback queue entries of user publisher/1
select s.xbstoreid, s.xbtype, r.xbmeatid, r.xbmethodname from xbstore s left outer join xbref r on s.xbstoreid=r.xbstoreid where s.xbtype='callback' and s.xbpostfix='client/joe/1';
select s.xbstoreid, s.xbtype, m.xbmeatid, m.xbdatatype from xbstore s left outer join xbmeat m on s.xbstoreid=m.xbstoreid where s.xbtype='callback' and s.xbpostfix='client/publisher/1';

-- Find meat which contains a topicId
select xbstoreid, xbmeatid, xbmsgkey from xbmeat where xbmsgkey like '%Hello%'

-- Consistency check number of: All must result 0 or max 1
select count(*) from xbstore where xbtype='session';
select count(*) from xbstore where xbtype='subscribe';
select count(*) from xbstore where xbtype='topicStore';