phpgroupware-cvs
[Top][All Lists]
Advanced
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Phpgroupware-cvs] old/cdb/doc CREDIT, 1.1 cdb.fields.txt, 1.1 cdb-class

From: skwashd
Subject: [Phpgroupware-cvs] old/cdb/doc CREDIT, 1.1 cdb.fields.txt, 1.1 cdb-classes.txt, 1.1 README.outlook2xyz, 1.1 cdb.preferences.txt, 1.1 cdb.sql-README.txt, 1.1 index.html, 1.1 cdb.views.txt, 1.1
Date: Thu, 5 May 2005 02:56:00 +0200 
Update of old/cdb/doc

Added Files:
     Branch: MAIN
            CREDIT 
            cdb.fields.txt 
            cdb-classes.txt 
            README.outlook2xyz 
            cdb.preferences.txt 
            cdb.sql-README.txt 
            index.html 
            cdb.views.txt 

Log Message:
cvs clean up

====================================================
Index: CREDIT
phpGroupWare's CDB Project
Maintained by Patrick Walsh <mr_e -at- phpgroupware.net>;  mr_e on #phpgroupware


For now this is just a list of people offering to help
with the cdb project.

from #phpgroupware

dtm (non-coding stuff: docs, testing, etc.) <dtm -at- mmmgood.net>
blinky <blinky -at- phpgroupware.net>
gnrfan <powell -at- linuxstart.com>
bjmorel <brianmorel -at- yahoo.com>

from mailing list

Bob Schader <bobs -at- product-des.com>
Bo Kersey <bo -at- vircio.com>
Mathieu van Loon <mvanloon -at- itti.nl>
Miles Lott <milos -at- insync.net> (Milosch in #phpgroupware)
River Hume <river -at- binaryaxiom.com>


====================================================
Index: cdb.fields.txt
Alright, for the purposes of filters, sync'ing, etc., we need to create names 
and mappings for all of our information.

First of all, let's come up with a list of valid tables.  All tables are 
prefixed with phpgw_cdb_ so we'll just assume that and ignore it for the 
moment.  In fact, we'll always ignore that.

Fields are the straight forward fields that we get to just send back without 
processing.  We like these.

Aliases are for those fields that we have to do extra lookups and fancy joins 
for.  These are pains in the buttocks.

Table                   Field                   Alias           Comments
-----                   -----                   -----           --------
contact_main    contact_id
contact_main                                    organization    Returns main 
organization (str)
contact_main                                    organization_location   Returns 
main org
                                                                                
        location (string)
contact_main    first_name
contact_main    middle_name
contact_main    last_name
contact_main    last_name_prefix
contact_main    initials
contact_main    location
contact_main    nickname
contact_main    profession
contact_main    suffix
contact_main    title
contact_main                                    mailing_address Returns as 
string
contact_main                                    note
contact_main                                    category        Problem is that 
each contact has
                                                                                
        multiple categories.  This returns
                                                                                
        one record for each category.
contact_main                                    categories      Returns ',' sep 
list of categories
contact_phone                                   [category]      OK, this one is 
even stranger than
                                                                                
        the others.  category can be
                                                                                
        anything listed in the contact_
                                                                                
        phone_category table, such as Home,
                                                                                
        Work, etc.  Spaces turn into _'s.
                                                                                
        So "Business Fax" becomes
                                                                                
        business_fax, etc.
contact_phone                                   all_phones      Since more than 
8 phone numbers is
                                                                                
        possible, search/return them all as
                                                                                
        <br> separated string
contact_phone                                   phone1          Returns phone 1 
(string)
contact_phone                                   phone2
contact_phone                                   phone3
contact_phone                                   ...
contact_phone                                   phone8
contact_address                                 all_addresses   same as 
all_phones
contact_address                                 [category]      Same as 
contact_phone.[category]
contact_address                                 [category].city
contact_address                                 [category].country
contact_address                                 [category].street
contact_address                                 [category].postal_code

Table                   Field                   Alias           Comments
-----                   -----                   -----           --------
organization    name
organization    home_page
organization    network_name
organization    gov_id_num
organization                                    note
organization                                    all_locations   <br> sep list 
of locations
organization                                    all_addresses   <br> sep list 
of addresses
organization                                    all_phones              <br> 
sep list of phones
organization                                    all_contacts    comma sep list 
of associated
                                                                                
                contacts
organization                                    main_location
organization                                    main_address
organization                                    main_phone
organization                                    category
organization_client     account
organization_client billing_info
organization_client customer_id
organization_client     referred_by
contact_organization    assistant_name
contact_organization    assistant_phone
contact_organization    assistant_email
contact_organization    department
contact_organization    job_title
contact_organization    manager_name
contact_client  account
contact_client  billing_info
contact_client  customer_id
contact_client  referred_by
contact_personal        birthday
contact_personal        children
contact_personal        gender
contact_personal        gov_id_num
contact_personal        hobbies
contact_personal                                language_code
contact_personal                                language_name
contact_personal        spouse
contact_personal        anniversary
meta                    status
meta                                                    created                 
Aliased because needs to be
                                                                                
                converted to a legible date form
meta                                                    modified                
Ditto
meta                    file_as
meta                                                    followup_status
meta                                                    created_by              
Needs to be converted to name
meta                    keywords
meta                                                    contact_flags   comma 
sep list
contact_internet        email_home
contact_internet        email_home_display
contact_internet        email_business
contact_internet        email_business_display
contact_internet        email_other
contact_internet        email_other_display
contact_internet        ftp
contact_internet        free_busy_address
contact_internet        personal_home_page
contact_internet        business_home_page
contact_internet        other_home_page
contact_internet                                send_as_plain_text
contact_internet        icq
contact_internet                                all_emails      Search/return 
all e-mails, <br> sep

====================================================
Index: cdb-classes.txt
------------------------------
-- Class design for cdb.sql --
------------------------------
v.1.9

Written by Patrick Walsh (address@hidden)


Changelog
---------

v.1.9
        - Modified entity class to be the main operational point and
          to reflect the new globally unique entity id.
        - Added contact_id to contact class.  id() returns entity id now

v.1.8
        - Added comments to class definitions that note the functions
          inherited from generic.class.php

Intro
-----
    This file is intended to compliment the cdb.sql and
cdb.sql-README.txt files.  It proposes a class structure for the
phpGroupWare project for interface with the data structure.  This would
be a part of the API.

    The goal is to provide phpGroupWare developers with full access to
all the data in the contact database while also providing an easy-to-use
programming interface.

    Initially two API's were discussed, the simple API that would always
be available to developers, and the advanced API, which developers would
need to specifically add before gaining access to the functionality.

    In writing this proposal I'm going to try to combine the simple and
advanced API's into one simple but all powerful API.

    One note: I've turned all properties into get and let functions a la
Microsoft's COM interfaces.  I don't advocate them, but they allow us to
provide read-only properties without confusing properties and functions.
Also, by using functions for the property change functions, we can set
dirty flags and reduce updates.


Requirements
------------
    Preface: this db design allows individual contacts and organization
             contacts as well as associations between organizations and
             individuals.  Since in many respects an individual contact
             and an organization contact function similarly, I will be
             referring to the combined concept of organizations and
             individual contacts as entities.

                     Entity == Organization || Contact

    1. Mechanism for adding entities.
    2. Mechanism for deleting entities.
    3. Mechanism for searching for an entity by any criteria (any?) and
       returning an array of entities matching the criteria.
    4. Mechanism for editing relationships and properties of any entity.


Class Structure
---------------
    The only meaningful interface to the potential plethora of
information for any given entity is a class structure.  The design of
this class structure can make or break this app.  I propose a root
entity class.  An entity object contains either an organization or a
contact.  Both the client and organization objects contain only their
"main" information and use subclasses to handle extended information.
The extended information is only loaded when needed.  The interface
to the classes will be specified below, but the class hierarchy will
look like this:

    entity.class
       organization.class
           entity.meta.class
           organization.client.class
           organization.location.class
       contact.class
           entity.meta.class
           contact.client.class
           contact.personal.class
               languages.class
           contact.internet.class
           contact.phone.class
               phone.class
           contact.address.class
               address.class

    Note that these classes do not line up exactly with the tables.  This
is because the main class will include the Notes and Category
information.


Class Interfaces
----------------

 - entity.class functions -
     get_type() returns "organization"|"contact"
     get_org_data() returns organization.class
     get_contact_data() returns contact.class
     get_data() generic -- returns either organization or contact class
     load(int entity_id) -- loads either organization or contact class
     create(strType) -- creates a new entity of type strType. returns id.

 - organization.class functions -
     id() returns int
          function used to obtain the org id.  if no org is loaded in
          the class, this returns zero.  This function is inherited, do
          not create it.
     create(string name) returns int
          creates a new org with name name.  on success it returns the
          new org id, zero on failure
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     exist(int id) returns boolean
          used to test and see if there is a database entry for the given
          org id.  returns true if it exists.  this checks the status
          flag in the organization_meta table to make sure the record has
          not been marked for deletion.  This function is inherited, do
          not create it.
     delete() returns boolean
          marks the current org for deletion.  returns true if
          permissions allow current user to perform the operation and the
          status flag was successfully changed to Deleted.  A cron job,
          or some routine admin function should go through the meta table
          and delete all marked for deletion after a certain time has
          elapsed.  also needs to remove all references by contacts, if
          any.
     can_read(int id) returns boolean
          tests current user's read permissions on org id.  this function
          is inherited.  do not create.
     can_write(int id) returns boolean
          tests current user's write permissions on org id.  if no id is
          passed, or zero is passed, the currently loaded org, if any, is
          tested for write permissions.  this function is inherited.
     get_client_data() returns organization.client.class
     get_meta_data() returns entity.meta.class

     get_name() returns string
     let_name(string)
     get_locations() returns array of strings (location id/location name
          key/data pairs)
     get_location_data(int id)
          returns the organization.location.class with phone/address
     get_home_page() returns string
     let_home_page(string)
     get_network_name()
     let_network_name(string)
     get_gov_id() returns string
     let_gov_id(string)
     get_categories() returns array of strings
     let_categories(array of strings)
     get_note() returns string
     let_note(string)

 - organization.location.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     create(int type, [string number]) returns int
          creates a new phone number of type type.  on success it
          returns the new phone id, zero on failure

     get_city() returns string
     let_city(string)
     get_street() returns string
     let_street(string)
     get_country() returns string
     let_country(string)
     get_postalcode() returns string
     let_postalcode(string)
     get_number() returns string
     let_number(string)

 - entity.meta.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     date_created() returns date
     date_modified() returns date
     created_by() returns user id
     version() returns float
        this will be used in the case of changed db formats to facilitate
        smooth transitions of data
     get_status() returns string
        one of "active," "pending," or "deleted"
     let_status(string)
     get_file_as() returns string
     let_file_as (string)
        defaults to "last_name, first_name"
     get_followup_status() returns int
     let_followup_status (int)
     get_keywords() returns string
        comma delimited list
     let_keywords(string)
     get_group_access() returns int
        ????  perhaps this should be a string?  controls on access?
     let_group_access(int)
     get_ldap_sync() returns boolean
        determines whether or not to sync with an ldap server
     let_ldap_sync(boolean)
     get_ldap_dn() returns string
        if ldap_sync is true, and this record has already been
        propogated, then this will return the dn which can directly
        reference the ldap entry.
     let_ldap_dn(string)

 - organization.client.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     get_account() returns string
     let_account(string)
     get_billing_info() returns string
     let_billing_info(string)
     get_referred_by() returns string
     let_referred_by(string)
     get_customer_id() returns string
     let_customer_id(string)

 - contact.class functions -
         id() returs int
                  inherited from generic.  will contain entity_id.
     contact_id() returns int
          function used to obtain the contact id.  if no contact is
          loaded in the class, this returns zero.
     create(string name) returns int
          creates a new contact with name name.  on success it returns
          the new contact id, zero on failure
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     exist(int id) returns boolean
          used to test and see if there is a database entry for the given
          contact id.  returns true if it exists.  this checks the status
          flag in the contact_meta table to make sure the record has not
          been marked for deletion.  this function is inherited, do not
          create it.
     delete() returns boolean
          marks the current contact for deletion.  returns true if
          permissions allow current user to perform the operation and the
          status flag was successfully changed to Deleted.  A cron job,
          or some routine admin function should go through the meta table
          and delete all marked for deletion after a certain time has
          elapsed.
     can_read(int id) returns boolean
          tests current user's read permissions on contact id.  this
          function is inherited, do not create.
     can_write(int id) returns boolean
          tests current user's write permissions on contact id.  if no id
          is passed, or zero is passed, the currently loaded contact, if
          any, is tested for write permissions.  this function is
          inherited.  do not create it.
     get_client_data() returns contact.client.class
     get_meta_data() returns contact.meta.class
     get_personal_data() returns contact.personal.class
     get_internet_data() returns contact.internet.class
     get_phone_data() returns contact.phone.class
     get_address_data() returns contact.address.class

     get_main_organization() returns organization.class
     let_main_organization(organization.class)
     get_first_name() returns string
     let_first_name(string)
     get_middle_name() returns string
     let_middle_name (string)
     get_last_name () returns string
     let_last_name(string)
     get_last_name_prefix () returns string
     let_last_name_prefix(string)
     get_initials () returns string
     let_initials(string)
     get_location () returns string
     let_location(string)
     get_nickname () returns string
     let_nickname(string)
     get_profession() returns string
     let_profession (string)
     get_suffix() returns string
     let_suffix (string)
     get_title () returns string
     let_title(string)

     get_mailing_address() returns string
          this function returns the address as a string, but there is no
          complimentary let_mailing_address.  Addresses should be changed
          via the contact.address.class subclass, but the main page's
          pointer is changed by the let_mailing_address_selector func.
     get_mailing_address_selector() returns int
     let_mailing_address_selector(int)
          so the int is the ref number and now get_mailing_address will
          return the string of the new num, if the num belongs to the
          contact.  otherwise it will return a blank string.
     get_web() returns string
     get_web_selector() returns int
     let_web_selector(int)
          above three work just like with mailing address, but for int
          instead of an id number, 1=home, 2=business, 3=other
     get_email() returns string
     get_email_selector() returns int
          again, like an address, a contact can have multiple e-mails and
          one primary e-mail.  this would return the num of the
          primary e-mail address.  1=home, 2=business, 3=other
     let_email_selector(int)
     get_phonex(int x) returns string
          there are eight primary phone numbers.  only the first four are
          sync'ed with palm pilots.  get_phonex takes a parameter
          representing x that is a number between 1 and 8.
     get_phonex_selector(int x) returns int
     let_phonex_selector(int x, int ref)
          let_phonex_selector and get_phonex_selector also take an x
          parameter signifying which phone display slot is being changed.

     get_note() returns string
     let_note(string)

 - contact.client.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     get_account() returns string
     let_account(string)
     get_billing_info() returns string
     let_billing_info(string)
     get_referred_by() returns string
     let_referred_by(string)
     get_customer_id() returns string
     let_customer_id(string)

 - contact.personal.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     get_birthday() returns date
     let_birthday(date)
     get_children() returns string
     let_children(string)
     get_gender() returns "unspecified"|"male"|"female"
     let_gender(string)
     get_gov_id() returns string
     let_gov_id(string)
     get_hobbies() returns string
     let_hobbies(string)
     get_language() returns string
     get_language_id() returns int
     let_language_id(int)
     get_language_data() returns languages.class
           languages.class provides a list of available languages and an
           interface for adding new languages or renaming existing
           languages
     get_spouse() returns string
     let_spouse(string)
     get_anniversary() returns date
     let_anniversary(date)

 - contact.internet.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     get_email_home() returns string
     let_email_home(string)
     get_email_home_display() returns string
     let_email_home_display(string)
     get_email_business() returns string
     let_email_business(string)
     get_email_business_display() returns string
     let_email_business_display(string)
     get_email_other() returns string
     let_email_other(string)
     get_email_other_display() returns string
     let_email_other_display(string)
     get_ftp() returns string
     let_ftp(string)
     get_freebusy_address() returns string
     let_freebusy_address(string)
     get_homepage_home() returns string
     let_homepage_home(string)
     get_homepage_business() returns string
     let_homepage_business(string)
     get_homepage_other() returns string
     let_homepage_other(string)
     get_icq() returns string
     let_icq(string)
     get_sendasplaintext() as boolean
     let_sendasplaintext(boolean)

 - contact.phone.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.

     get_phone_list() returns array of phone.class with id's as keys

 - phone.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     create(int type, [string number]) returns int
          creates a new phone number of type type.  on success it
          returns the new phone id, zero on failure

     get_phone_id() returns int
     get_type_name() returns string
     get_type() returns int
     let_type(int)
     get_type_list() returns array of id/string pairs
     get_number() returns string
     let_number(string)

 - contact.address.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.
     create(int type) returns int
          creates a new address of category type.  on success it returns
          the new address id, on fail it returns zero.

     get_address_list() returns array of address.class with id's as keys

 - address.class functions -
     save() returns boolean
          used to commit changes to the object to the database.  returns
          true on success, false on failure.
     load(int id) returns boolean
          used to populate the object with the properties from the
          id record in the database.  returns true if successful and
          false on failure.  failure would happen when the current user
          does not have sufficient read permissions or when there is no
          such id.

     get_city() returns string
     let_city(string)
     get_street() returns string
     let_street(string)
     get_country() returns string
     let_country(string)
     get_postalcode() returns string
     let_postalcode(string)

====================================================
Index: README.outlook2xyz
Outlook 2 xyz

This is a little utility that is almost a separate app, but not enough
so that I wanted to make it a separate app.  It is a framework for
migrating data from an Comma Separated contacts file exported from Outlook
to anything.  Currently it only works for sending data to ldap, as that
was my need when I wrote it.  Maybe someone else will write the code for
migrating to addressbook or cdb.

Patrick Walsh <address@hidden>

I have added addressbook import.  The records can be set as private or
public.  There is also now selection for the LDAP context in which to
store contacts.  These should default to a dn that is dependent upon
your LDAP setup for phpgw, if you are using LDAP.

Miles Lott <address@hidden>

====================================================
Index: cdb.preferences.txt
This file will be used to track the various preferences that we will track.

System-wide
        1) Cache expiration: 1day, 6hours, 1hour, 15minutes.  Default=1hour.

User-wide

====================================================
Index: cdb.sql-README.txt
------------------------
-- README for cdb.sql --
------------------------
v.1.7


Written by Patrick Walsh (address@hidden)


Intro
-----
  This file is intended to explain the design ideas behind the cdb.sql
file.  That file defines a database structure for an Outlook-like
contact management system.  Hopefully the two will be completely
compatible, but the cdb system will be far superior.


Database Tables and Relationships
---------------------------------
  This is a somewhat complex relational design with a number of
many-to-one relationships.  However, this is the only reasonable way
to create a very usable and flexible contact database.  Some of the
design goals are listed in the next section.

   - contact_main
        Core information that would be displayed on the main page.  This
        includes names, profession, title, and links to the primary
        contact methods such as the mailing address, the top four phone
        numbers, main e-mail, main organization, etc.

   - contact_meta
        Stores meta info such as date created, date last modified, file_as,
        flag_status, followup_status, created_by, group_access
        permissions, etc.

   - contact_personal
        Stores info, where applicable, such as birthday, children,
        hobbies, spouse name and so forth.

   - contact_client
        Contains a customer_id, billing_info, referred_by and account
        info.

   - contact_notes
        Contains just the note associated with each contact, if one
        exists.  This is in it's own table to keep the other tables
        optimized.

   - contact_internet
        Contains a contact's internet info.

   ** Note that all of the above tables have a one-to-one relationship to
      each other.  There is an entry only if data of a given type is
      entered.

   - contact_phone
        This table contains all phone numbers associated with all
        contacts.  Someone wanting to see all phone numbers for a given
        contact would effectively select all phone numbers in this table
        belonging to the selected contact.

        Each phone number has the number (up to 100 chars), a link to the
        phone_category and a link to the entity_id.

   - contact_phone_category
        This is a table that can be customized by the sysadmin and it
        contains a comprehensive list of different types of phone
        numbers.  Business Phone, Business Fax, Car Phone, Vacation Home
        Phone, Home Phone, etc., are all examples of phone categories.

        There can be any number of phone numbers in a given category for
        a particular contact.

   - contact_address
        Contains the elements of an address, a link to the associated
        contact, and a link to the address_category.  This works in the
        same way as contact_phone.

   - contact_address_category
        Works in exactly the same way as the contact_phone_category.

   - category
        Contains a list of all categories.  Simply contains the category
        id and associated category name.

   - category_link
        This is how each contact can be a part of multiple categories.
        Each contact/category pair has a line in this table.

        Organizations can also be categorized.  This table now (v.1.1)
        has a flag for determining whether the link is to an organization
        or to an individual.

   - language
        Works very much like category: contains a list of languages and
        associated language codes.  These are the two digit language
        codes used by phpgw.

        Unlike categories, there is only one language per contact.  This
        is stored in the contact_personal table.  If there is no entry in
        that table for a given contact, English (en) is assumed.

        Perhaps language should be in contact_main instead?

   - contact_flag
        This table has a list specific to each user with what labels --
        called status flags by Outlook -- they want available for each
        contact.

        This table stores the user_id the flag_name, the flag_color and
        the flag_id.  A flagged contact gets a background in of
        flag_color.

   - contact_flag_link
        Each user gets one flag per contact.  This table has the user_id,
        the user's unique flag_id, and the contact_id.

   - contact_followup
        Systemwide list of followup flags such as "Send E-mail," "Send
        Letter,"Call," "Arrange Meeting," or whatever suits a particular
        installation.  The table has a followup_id, followup_name and
        followup_color for the flag color.

        The contact_meta table has a reference to one of these.

   - organization
        This is where it starts getting complex and there are still a few
        design questions.

        This table has a list of organizations with an id for each, a
        name for each, the main phone number, the home page, etc.

        Perhaps we should have tables for allowing multiple locations and
        different phone numbers for different office locations and then
        allow each contact to associate themselves with a particular
        office...?

   - organization_notes
        One to one relationship to organization.

   - organization_client
        One to one relationship to organization.  Functions exactly like
        contact_client except that it allows for organizations to be
        customers/clients.

   - organization_meta
        Same as contact_meta but for organizations.

   - organization_location
        Each organization can have multiple office locations with
        distinct addresses and phone numbers.  A contact is not just
        associated with an organization, but with a particular location
        and organization combo.

   - contact_organization_link
        [this table has been removed.  the contact_organization table
        achieves the same purpose]

   - contact_organization
        This is perhaps the most complex table as it has a many to many
        relationship between contacts and organizations.  Each contact
        has a main organization selected in their contact_main table.

        This is a table with org_id <-> contact_id links.

        For any link where there is more specific information to be
        added such as the contact's assistant's name/number at the
        organization, the contact's manager's name/number, office
        location and so forth.


Design Goals
------------
  There are a number of situations where normal contact management
databases fall short and a select few situations where Outlook falls
short.

  Imagine the contact database of a Certified Public Accountant.  Such a
database would be filled with clients, attorneys, other cpa's, business
contacts, companies, trusts, personal contacts and so forth.  Many of
these people could fall into multiple categories: an attorney and a
client, for instance.  This brings me to the first design goal:

    1) Categorization of contacts where one contact may be in any number
       of categories.

  Most addressbooks allow for a person to have a home and work address
and home and work telephone numbers.  Unfortunately this can leave a
large number of phone numbers for the Notes section.  Consider the CPA's
rich clients who have multiple homes, perhaps multiple office locations,
a pager, a cell phone, a private line, a normal line, etc.  Don't laugh,
I have a good six telephone numbers myself and I have contacts who have
more than that!  This brings me to the second design goal:

    2) Allow for an unlimited number of phone numbers in which there are
       four primary numbers and any number of additional numbers.  Each
       number is associated with a phone category such as Business Phone,
       Business Pager, Business Fax, Home Phone, Home Fax, Vacation Home,
       and so forth -- and there can be any number of each of these.  The
       list of categories could be customized by the sysadmin.

       A similar system would be used for addresses with one marked as
       the primary mailing address for mail merge operations and such.

  Next, one often has a number of contacts in the same company.  For
instance, suppose our CPA uses a law firm, but several different
attorneys within the law firm for different areas of law.  Outlook and
most other contact databases force you to put the company information in
for each person.  This is a waste of time and space and is a potential
source of typos that could mess up filters for a particular company.
Further, a company can be a contact with further contacts within the
company.  This brings me to the third design goal:

    3) Create a system by which contacts can share company information.
       Company information includes the company name, main phone number,
       home page, notes, and id number.

       But lets not limit ourselves to companies -- let's call these
       organizations and let them be any legal entites: estates, trusts,
       companies and so forth.  In such cases the id number would be the
       federal id number used for taxes -- similar to an individual's
       social security number.  This is probably specific to CPA's.

       Any given contact can be associated with a number of
       organizations.  A person may be the executor of an estate, work
       for a company, and be a beneficiary of a trust.

  There is a need also for information that is specific to a particular
contact in conjunction with a particular organization.

    4) A contact may have an assistant's name, an assistant's phone
       number, a department name, a manager's name and so forth for
       each organization that a contact is associated with.

  There needs to be a framework for contacts who are also customers or
clients.  One would want to assign each of these people client or customer
id numbers for tracking billing histories, support histories, and so
forth.  But such data would be tracked in another database system.

    5) Each contact who is a customer/client would have a customer id,
       billing info, who they were referred by, and an account.  Further,
       organizations may also be customers/clients and as such they can
       also have customer id numbers, billing info, etc.

  Contact entries can store personal information to jog the memories of
forgettful people like me.

    6) Optionally store personal info such as hobbies, childrens' names,
       spouse's name, anniversary, etc.

    7) File As field for determining where to file a company or person --
       for instance, if a married friend has adopted her husband's name,
       but you can only remember her maiden name, you might have her last
       name properly listed, but still file her under her maiden name.

  There should be a mechanism for flagging and/or labelling contacts.
There are times when it becomes obvious that a contact's information is
out of date and you want someone to review the information.  Or you may
want to flag a contact for later followup.

    8) Each person can make their own set of status flags and these flags
       will act like Eudora labels.  When flagged, the contact row will
       be colored with the color of the flag. Each person could use this
       feature in their own way.  Sales people may want to label
       potential customers with different colors depending on their
       chance of making a sale.

    9) There will be a system wide list of follow-up flags that can be
       associated with a contact.  Such flags would be seen by anyone,
       in contrast to the user-specific labels above.  Example values
       that would add a colored flag to a column are:

              - Follow-up
              - For your information
              - Review
              - Call
              - Send E-mail
              - Send Letter
              - Arrange meeting

  Few contact management databases provide sufficient Internet
information.  There needs to be a framework for storing e-mail
addresses including personal and work e-mail, ftp, personal
home page, business home page, icq number and so forth.

    10) Use a table to store internet contact info.

    11) Provide a mechanism for storing a contact's spoken language
        so that correspondence is done in the correct language.

====================================================
Index: index.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<HTML>
<HEAD>
        <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
        <META NAME="GENERATOR" CONTENT="dirhtml.pl">
        <META NAME="Author" CONTENT="Miles Lott">
        <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
        <META HTTP-EQUIV="Cache-control" CONTENT="no-cache">
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#333333" LINK="#333399" VLINK="#003399" 
ALINK="#003399" TOPMARGIN="0" LEFTMARGIN="0" MARGINWIDTH="0" MARGINHEIGHT="0">

<table cellspacing="0" border="0" height="20" width="100%">
<tr>
        <td colspan="3"><b>Type</b></td>
        <td><b>Name</b></td>
        <td><b>Size</b></td><td><b>Date</b></td>
</tr>
<tr>
        <td colspan="3"><b>DIR</b></td><td><a href="..">..</a></td>
</tr>
<tr>
        <td colspan="3"><b>DIR</b></td><td><a href="CREDIT">CREDIT</a></td><td> 
620 </td><td> Thu Mar  7 22:05:24 2002 </td>
</tr>
<tr>
        <td colspan="3"><b>FILE</b></td><td><a 
href="./cdb-classes.txt">./cdb-classes.txt</a> </td><td> 21438 </td><td> Tue 
Mar 26 18:48:51 2002 </td>
</tr>
<tr>
        <td colspan="3"><b>FILE</b></td><td><a 
href="./cdb.fields.txt">./cdb.fields.txt</a> </td><td> 4168 </td><td> Tue Mar 
26 18:48:51 2002 </td>
</tr>
<tr>
        <td colspan="3"><b>FILE</b></td><td><a 
href="./cdb.preferences.txt">./cdb.preferences.txt</a> </td><td> 170 </td><td> 
Wed Mar 20 13:37:03 2002 </td>
</tr>
<tr>
        <td colspan="3"><b>FILE</b></td><td><a href="./cdb.sql">./cdb.sql</a> 
</td><td> 20816 </td><td> Tue Mar 19 19:55:39 2002 </td>
</tr>
<tr>
        <td colspan="3"><b>FILE</b></td><td><a 
href="./cdb.sql-README.txt">./cdb.sql-README.txt</a> </td><td> 12681 </td><td> 
Sun Feb 17 15:42:14 2002 </td>
</tr>
<tr>
        <td colspan="3"><b>FILE</b></td><td><a 
href="./cdb.views.txt">./cdb.views.txt</a> </td><td> 8846 </td><td> Tue Mar 26 
18:48:51 2002 </td>
</tr>
<tr>
        <td colspan="3"><b>FILE</b></td><td><a 
href="./cdb_language.sql">./cdb_language.sql</a> </td><td> 12113 </td><td> Thu 
Oct 12 20:17:37 2000 </td>
</tr>
</table>
<PRE>

</PRE>
<br>Created by dirhtml.pl at Sun May 19 20:22:28 2002 CST by Milosch
</BODY>
</HTML>

====================================================
Index: cdb.views.txt
Alright, time to figure out how to make configurable views.  First, there are a 
few set views that I want to see, then I want people to be able to make their 
own views.  That will definitely be the tricky part.  I think there will have 
to be some base types where people choose what fields, sorting and grouping 
options they want.  Filtering options too.

So, here are the views:

        -- Relationship explorer --
                Probably leave this for last.  Will likely require dynamic 
flash or something to do it right.  Ideally, you have labeled icons for 
contacts and orgs with one in the center and arrows to all of the contacts/orgs 
that are related.  Click on a new one, get a new center.  There will have to be 
an index on the side for selecting a particular entity as well.

        -- Grid view --
                Probably the first and easiest.  Configurable column set.  
Configurable sorting.  No grouping in this view.

        -- Grouped grid view --
                Same as below but grouped by category or whatever.

        -- Detailed grid view --
                Each grid item has indented detail items beneath it.

        -- Card view --
                Ripped off from Outlook's card view.  Might be a little hard to 
duplicate in html though.

        Now if this were truly going to be useful, there would be some sort of 
sharing and permission structure on the views that would allow administrators 
to create global views and individuals to create personal views.  But that's 
going to be a pain in the butt.  Alas... it will be necessary.  But I'll figure 
it out later.

        So we need a views database table.  One of the fields will be "fields" 
and will contain a comma separated list of each of the fields to show in the 
view.  Of course this is complicated a bit by the fact that all of our fields 
are not in one table or even one class.  So we have some figuring to do here.  
I think each field must be described by a fieldname and a class name.

        The next field needs to be grouping.  You group by things that overlap. 
 So, for example, you might group by category, or by ownership, or by 
permissions, or by flag or by followup, perhaps by city, country, zip code, 
area code.  What else?  I think this needs to be a finite list to be chosen 
from.  Maybe two or three deep groupings should be allowed: first by category, 
then by city, etc.

        Next up we need the sorting field.  Same thing: comma separated list, 
up to three deep of sort keys.

        Damn, did I mention that this is going to be difficult?  I haven't even 
talked about filtering yet.  And that's because I don't know how to store it 
yet.  Th real difficulty here is making a simple mechanism for encapsulating 
what information is and isn't wanted including and's, or's, equals, not equals, 
greater than, less than, etc., etc.  And doing all of this and protecting 
people from SQL but being able to translate it for them.  Egads.

        OK, I haven't even mentioned the huge performance problems of doing 
large and complex joins and selects and then sorting and grouping them in an 
array in memory.  First, for any large group of contacts, just a few people 
viewing them at once could easily suck up all the memory on a machine.  
Nevermind the processing intensity for sorting the contacts with each page 
view.  And then probably we're only showing a fraction of the contacts.

        The solution to this would be using database views, but MySql does not 
support views (although Postgresql does).  So we need to improvise a bit.  The 
solution, I think, is to use temporary tables.  The syntax in Postgresql and 
Mysql is different, but the effect is the same.  You can create a table based 
on the results of a big select and join and that table will last only as long 
as the session.  This serves several purposes, the coolest being that you can 
manipulate the results of a given filter easily and also that results can be 
cached.  I think that if two people view a given view then they will both get 
the benefits of the cached filter data.  So, here's the syntax for creating 
temporary tables:

mysql:
        CREATE TEMPORARY TABLE tbl SELECT blah blah
pgsql:
        CREATE TEMPORARY TABLE tbl AS (SELECT blah blah)

        Some notes on a cached table.  The cache includes all fields shown in 
the
view for all records matching the filter, regardless of permissions.  This way
the cache can be used by different people.  The cached data is not used when
contacts are viewed individually, only when they are browsed via a view.  This
is important because a record can be updated and the cache invalidated (though
we don't flag it as such) and not updated until the next update interval (a
global preference).  This means that you can browse old information, but if
you click on it to see details, you get current information.  It also means
that if, for example, you change the categories of a bunch of records, and are
viewing them in a category grouped view, your changes won't be updated.
Rather than invalidating each view on every change, I'd rather just offer a
button that forces the update of the cache.

        Alright, so we have two phases in making a view.  Make that three 
phases.
First, a temporary table is created based on the "fields" and "filter" stuff.
Then the data is whittled down to what's going to be displayed by grouping,
sorting, checking permissions, and limiting data to what's going to be output.

        The third phase is applying the data to the view type and outputing the
html.

        Alright, now we just have to figure out the mechanisms for storing and
translating all this stuff.  The key is to create a sensible API that can also
be used for retrieving groups of records for other reasons... like searches
and such.

Field Identification
--------------------
table.field

...okay, but this only works for standard fields.  There are some fields that
will need to be treated specially.  For example, the phone, address, and
category fields can't be joined normally.  We'll just have to create some 
special field identifiers that are treated specially when we're creating our 
sql statement.

Filter Specification
--------------------
Well, I guess we can use good old sql stuff for the filter specification.
There just aren't many other choices that make sense.  Reverse polish notation
would allow us to avoid parenthesis, but I think that that would confuse too
many people.  So... a filter will look like this:

(table.field > 4) or (table.field < 1)

        Of course this will be more sophisticated for us to parse in figuring 
out
our sql query.  Particularly since we need to sanity check it and deal with
our special fields.  Not to mention figuring out the joins.

        The UI will probably be a matter of allowing people to create a group of
ands separated by ors... effectively:
(x and y and z) or (a and b) or (b and c)
(a) or (b)
(a and b)
etc.
Though I'm still not quite sure how to make this into an easy interface.
However, it will probably be an array of expressions where each element of the
array representes a block of ANDs.

Unresolved Questions
--------------------

        1) How do you do multiple LEFT JOINs and is it the same in pgsql and
mysql?  A single LEFT JOIN is pretty easy, right, but what about multiple
tables with different keys?

        Partial answer:
        mysql:
                Not sure about multiple joins, but here's the basic thing:
                SELECT t1.x, t2.y FROM t1 LEFT JOIN t2 ON t1.idx = t2.idx WHERE 
...

        pgsql:
                SELECT t1.x, t2.y FROM t1 LEFT JOIN t2 ON t1.idx = t2.idx WHERE 
...
                For multiple joins:
                SELECT t1.x, t2.y, t3.z FROM t1 LEFT JOIN (t2 JOIN t3 ON
(t2.idx=t3.idx)) ON (t1.idx=t2.idx)

        2) How long do sessions last?
        3) Can a cached (temp table) view be used by multiple people?  For how 
long?  If for a long time then it will be out of date.  If for too short, it 
will defeat the purpose of the cache.

Databases Tables
----------------
        What needs to be stored:
                - Most Recently Viewed Orgs
                - Most Recently Viewed Contacts
                - Most Recently Viewed Views
                - Views

        Tables:
                phpgw_cdb_views
                        fields: 
view_id,cache_date,owner,group_owner,public,name,type,fields,filter,sort,group
                        Most of these are self explanatory according to the 
descriptions
above.  The new fields are owner, group_owner and public.  owner stores the
account_id of the creator of the view.  group_owner will store the group that
the view is associated with, if any.  At this time it will only store one
group.  Perhaps in the future it could store multiple groups.  And public will
be a flag representing either Globally Public, Group Public, or Private.
Also, cache_date will be used to store the freshness of any existing cache.

                phpgw_cdb_recent
                        fields: account_id,orgs,contacts,views

Constraints
-----------
        Filter names cannot have spaces and cannot start with underscores.

Optimizations
-------------
        Should really create a categories field that gets updated whenever
category links are changed.  That would save a lot of trouble for certain
views.
reply via email to
[Prev in Thread] Current Thread [Next in Thread]