[Phpgroupware-cvs] CVS: cdb/doc cdb.views.txt,1.2,1.3

[Patrick Walsh <address@hidden>]

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

Modified Files:
        cdb.views.txt 
Log Message:
Making changes as I go farther in the coding process.


Index: cdb.views.txt
===================================================================
RCS file: /cvsroot/phpgroupware/cdb/doc/cdb.views.txt,v
retrieving revision 1.2
retrieving revision 1.3
diff -C2 -r1.2 -r1.3
*** cdb.views.txt       14 Feb 2002 22:55:28 -0000      1.2
--- cdb.views.txt       28 Feb 2002 01:56:58 -0000      1.3
***************
*** 3,7 ****
  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.
  
--- 3,7 ----
  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.
  
***************
*** 37,40 ****
--- 37,53 ----
        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.
***************
*** 70,71 ****
--- 83,138 ----
  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
+