[Phpgroupware-cvs] CVS: cdb/doc cdb.views.txt,NONE,1.1

[Patrick Walsh <address@hidden>]

Update of /cvsroot/phpgroupware/cdb/doc
In directory subversions:/tmp/cvs-serv18973/doc

Added Files:
        cdb.views.txt 
Log Message:
Working up views designs


--- NEW FILE ---
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)