[Top][All Lists]
[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.
- [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, (continued)
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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,
skwashd <=
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- [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, skwashd, 2005/05/07
- Prev by Date:
[Phpgroupware-cvs] old/anthill/etc 0.2.3-0.3.0.0-a.sql, 1.1 anthill-emailuserscron.php, 1.1 0.2.2-0.2.3.sql, 1.1 0.2.0rc2-0.2.0rc3.sql, 1.1 0.2.0pre2-0.2.0pre3.sql, 1.1 mysql.dump, 1.1 pgsql.dump, 1.1 upgradepw.php, 1.1 upgrade-0.2.3-0.3.0-mysql.php, 1.1 testreportengine.php, 1.1 0.2.0pre1-0.2.0pre2.sql, 1.1 0.2.0-0.2.0.1.sql, 1.1 0.1.5-0.1.6.sql, 1.1 0.1.6-0.2.0.sql, 1.1 0.1.4-0.1.5.sql, 1.1
- Next by Date:
[Phpgroupware-cvs] squirrelmail/help/ru main_folder.hlp, 1.2 folders.hlp, 1.2 compose.hlp, 1.2 options.hlp, 1.2 read_mail.hlp, 1.2 search.hlp, 1.2 basic.hlp, 1.2 addresses.hlp, 1.2 FAQ.hlp, 1.2
- Previous by thread:
[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
- Next by thread:
[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
- Index(es):