Search code examples
sqlvcf-vcardcarddav

vcard vs sql-table for contacts

The other day I was looking at SOGo SQL tables and saw that the records are stored as vcard data instead of a fine table with different columns like surname, phone number, etc.

Though there is a table called sogo_quick_contacts with the schema I was expecting, not all the columns are there, only some basic ones.

I'm wondering why is it like that way? Is it better to query a record with the whole vcard-data and extract the information I require? Wouldn't it be better (faster) to apply a SELECT query indicating some columns I'm looking for, if they were available?

CardDav seems to provide this vcard-data, are they more suitable to contacts lookup, why?

What if I want to just list the names and birthdays. Wouldn't extracting all the vcards much slower then using a SQL Query where I have everything split up for different columns?

Solution

  • There are a lot of things which played a role in the way the ScalableOGo database schema is designed. Which BTW was designed by me ;-)

    I think the core thing here is that it is designed specifically for two types of clients: a) native CardDAV clients (macOS/iOS contacts, Thunderbird) and b) the ScalableOGo web interface.

    Native clients essentially never do the type of query you are asking about. They always sync a full vCard to their local cache. So there has to be a fast way to store and retrieve a full vCard, it is the most common operation against the server.

    Web clients in 2003 (I suppose that was around the time I wrote the original web client) didn't yet have the capacity to store full objects locally and had to do what you are asking for: query just the fields the web client needs to display on a respective page. This is what the 'quick' tables are for. They contain the columns the web clients needs to display overviews and such. It is essentially an app server provided index over the vCard content.

    This should be the main answer to your question.

    There are other reasons too, some in no particular order:

    • a vCard is quite complex, to convert it to a proper SQL schema / normalise it, is (was at the time, but this is still relevant, since the scale of systems grew 100-fold over the last 15 years) quite compute intensive (hence OpenGroupware.org vs ScalableOGo) A BLOB just needs to be streamed to disk.
    • a CardDAV server is supposed to store a full vCard as-is, byte-by-byte. So that the clients can do ETag protected requests. And store custom fields (all clients use their own X- tags for client specific fields)
    • the quick tables are also setup so that they can be build asynchronously, though I think that feature never made it into SOGo. If a client quickly loads 10000 vCards into the server (e.g. just dragging the vCards into the server using Finder), the server can batch-update the quick table in the background. The vCard to DB conversion doesn't have to happen in real time.
    • (notably native clients often have a similar 'quick' table setup locally.)

    Hope this helps. Maybe one would design the thing a little different in 2017, though I think the basic ideas are still sound ;-)