MySQL structure

[binnacle]

I am new to Zimbra. I am having trouble finding documentation on the structure of the MySQL database, particularly the contacts. Can anyone point me in the right directions?

[dkarp]

Please don't operate directly on the database. The system expects that the database won't change out from under it, and if you do make such changes it can get out of sync with bad results. If your changes aren't well-formed, the web UI won't even run.

You can import and create contacts using the SOAP and REST APIs. Please use them.

(The contact contents are stored in BEncoded format in the database's METADATA column.)

[binnacle]

I was not expecting to change it, but I would like to know the structure.

[dkarp]

Um... why? You can get at the data as XML via SOAP and as VCF and CSV via REST, so what's the point in accessing the database directly?

Anyways, if you're determined to go this route, to get the fields of the contact:

1. apply the BDecoding algorithm to the METADATA column of the contact's database row
2. the METADATA should decode to a "directory"
3. the value of the "fld" attribute is the key/value "directory" holding the contact's fields as strings

Note that values are stored in the database as UTF-8 and Zimbra's BEncoding implementation handles strings containing multibyte characters by assuming that the BEncoded string has been decoded from UTF-8 before the character counts on string values are considered.

[binnacle]

Thank you for hanging with me on this.

Here is the deal. We are looking at how to integrate Zimbra into a specialized set of data that we have built. We don't want to recreate all of the contact management work that you guys have done so well but we also don't want to have to upload the entire database to the Zimbra server. So, we are going to build a Zimlet. To do so though I need a unique identifier. (Our database has over 50,000 unique contacts in it which means more than one person with the same name. Moreover it is a relationship database that cannot easily be flattened.) So, I am assuming that there is a GUID on your side that I could use. I could upload some basic flat information to the Zimbra server and then keep on my side a link table linking your GUID to something on my side. Then I could build a Zimlet that would dynamically build the additional relational type data through an AJAX call.

If there is a better way to think of this I would appreciate knowing about. (I am also frustrated by the limited set of data fields that appear to be available for user edits. There don't appear to be any user defined fields so if I cannot find another route, I'll have to repurpose a field that is not likely to be used such as Email3.)

[dkarp]

You don't want our internal contact format, you just want a GUID.

The "id" attribute of a contact is unique across the mailbox. If you want a GUID, compose the user's account ID ("zimbraId") with this numeric item ID. If you're working from a zimlet, you don't even need to look at our database -- the contact's ID should be available from the zimlet.

As for the contact field display/editing, you may need to make your zimlet extend the contact UI.

[binnacle]

But I also need to know the size of the fields and the fieldtype so that what I do put into the Zimbra server is compatible. I really hate the idea that I might have to do this by trial and error. Isn't there a document detailing the fields, their type, size, and organizational structure?

[dkarp]

Using the normal APIs, create one shell Zimbra contact per contact in your DB. When you create the shell contact add a little bit of identifying information, and then in your own system associate the ZImbra contact ID with your contact. Then write a Zimlet that fetches data from your system every time Zimbra needs to render one of these shell contacts.

Note that Zimbra supports arbitrary fields in contatcs. The web UI only exposes a subset of fields, but you can put pretty much any Unicode key and value in a Zimbra contact.


You don't want to be writing directly to the ZImbra database for the reasons I've already mentioned.

[binnacle]

Sorry you lost me. I will need to integrate basic identifying information such as name, current address, company, phone, fax, email, etc. Other relational data I will add through the Zimlet.

I'm still lost at the beginning. Does the first name field in the contacts database have a set width and type. I need to know so that I can set up my export to the CSV file correctly.

[dkarp]

I completely misunderstood you. Sorry for the confusion.

No, all contact fields are pretty much free-form. You can make the values as long as you like, within reason. The only constraint is that, when we serialize all the fields together internally as UTF-8, the total must be roughly below 40KB.