No, it’s not the title of a movie. Today I’m trying to deal with some lost collection on the Chandler calendar server.

Since I can’t seem to find them from the interface I decided to analyze the derby database myself. To do so I put it into the Netbeans Derby database directory, which in my case was in the following location:

C:\Documents and Settings\Stefano\.netbeans-derby

I simply added a chandler directory there with the content of the database from my linux server. Then went to Services tab, started Java DB and created a connection to localhost port 1527, username sa schema SA.

After looking a little bit the db I decided that most stuff must be in the item table.

select itemtype, count(*) AS num
from SA.ITEM
group by itemtype
order by num

homecollection      3
collection     11
note            298

select *
from SA.ITEM
where itemtype='collection'

The id of the collections I’m interested in are 1703947   and 1703948

Then I took a look to the invite url of another collection on the same server:

http://192.168.1.254:8080/chandler/pim/collection/17ec0f27-bc55-11dd-fca5-bdcd03578bac?ticket=tygnbop2t1

I found out that this long identifier (17ec0f27-bc55-11dd-fca5-bdcd03578bac) is contained in the itemname and in the uid column of ITEM, while the ticket number was not in this table, but in the TICKETS table.

So I came up with the following query:

select it.uid, tk.ticketkey, it.displayname
from SA.ITEM AS it
join SA.TICKETS AS tk
ON it.id = tk.itemid
where itemtype='collection'

and the rows I’m interested in were the following:

b1816ede-bc66-11dd-9da3-99b0f90c4916    rzleaes000
b1816ede-bc66-11dd-9da3-99b0f90c4916    c2cl4ws001
b89a2ec2-bc66-11dd-9ec7-99b0f90c4916    dztu48s0f0
b89a2ec2-bc66-11dd-9ec7-99b0f90c4916    bq8gp3s0g0

With this result I could build the urls for invite and recover my collections!

http://192.168.1.254:8080/chandler/pim/collection/b1816ede-bc66-11dd9da3-99b0f90c4916?ticket=rzleaes000

http://192.168.1.254:8080/chandler/pim/collection/b89a2ec2-bc66-11dd9ec7-99b0f90c4916?ticket=dztu48s0f0