FW: DB widget [very long explanation]

["Tom Musgrove" <TomM pentstar com>]

Greeting Gnome-DB hackers,

I have a group of hackers that are planning to add functionality for
Evolution to use a Database for its backend (Evolution is an Outlook 2000
clone for contact management and email - see http://helixcode.com for more
information).  One of the programmers has written a database widget already,
but I felt it might be a better idea to see if we could leverage the
gnome-db code.

Below is a discussion of what functionality is eventually desired. Ashley
ash@comcen.com.au wrote the widget, and is explaining his desired goals and
visions for it... - much of which is being addressed already by the gnome-db
and GNUe, and Glade projects.

I've also appended a conversation between myself and Ashley, as well as
attached his original code for the DB widget.

Tom M.
TomM@pentstar.com

******Begin Previous discussion with Ashley *********

> I'd be interested in having someone check with gnome-db, to see if it
> might now meet our needs (I believe its undergone significant changes
since
> the time you mentioned checking it).  They would like to see their code in
as many things as
> possible, thus if you/we were to explain our needs to them, they could
probably tell
> us how doable/difficult it would be with their code. (They are currently
splitting
> their code so that Gnome dependent parts are separated out, because the
GNUe
> project requested it for their project...)  Would you be amendable to me
> forwarding your response to Brians questions, along with your initial code
> to the gnome-db list and ask them for their comments?  I doubt it would
hurt
> to ask, and it could save you (and us <grin>) substantial effort if
> something is workable.
>
> What do you think?
>

Absolutely...  If it can do it, it would be far easier.  The most important
thing I think is libglade compatibility.  If the widgets require a long
setup
sequence, I don't think libglade would be able to be used, and thus almost
useless to us unless we write our own xml parser (which may or may not be
easier in the end.  If we use libglade we can use a butchered vesion of
glade
for our layout designer though.)

A good thing with gnome-db though is they already have (some / a bit?) of
reporting stuff..  I think the reporting is the thing that is either going
to
make or break us.

***** End discussion with Ashley *****

-----Original Message-----
From: ash@comcen.com.au [mailto:ash@comcen.com.au]On Behalf Of Ashley
Cambrell
Sent: Monday, August 14, 2000 7:14 PM
To: Brian Fallik
Cc: Tom Musgrove; Brian Fallik; Richard Shoeller; sejal@iname.com;
rjune@ims1.imagestream-is.com
Subject: Re: DB widget [very long explanation]


Hi Brian,

I hope you don't mind but I have CC'ed this to the others as well.  (I think
it
will possible help people understand)

Also, if you do see holes in the plan, don't hesitate to point them out.
I'm
the sort of person that always likes to learn. (as long as it's not abusive
=
- )    )

NB the "layout" thing I keep referring to below is the guts of the contact
manager, the main dynamic form that is used to view and enter contacts.


Brian Fallik wrote:

> Ashley,
>
> Thanks for sending me the code for the db widget.  I was able to get it
> setup with PostgreSQL and run the test harness.  Here's my background so
you
> can understand where I'm coming from.
>
> I have just started learning the Gnome/GTK+ programming model.  I have
> pretty extensive programming experience in a variety of languages, both
> professionally and while enrolled in school.  Anyway, I reviewed your code
> as a C programmer looking for common programming mistakes, etc.
>
> However, in order to test the widget further, I would need a better
> high-level understanding of its purpose and design strategy.  In other
> words, I have several high-level questions that would be helpful if
> answered.
>
> If this is a generic database widget, why is the cfdb object tied to a
> particular SQL server and front end interface.  It seems like these layers
> would do better to be abstracted out for better modularity.  What was the
> widget designed specifically for?  I used the sample application to test
> functionality, but it would be better to understand how it fits into the
> bigger picture, Evolution.
>

Basically I made it postgres depend because I am by no means a C programmer,
but have had extensive experience with postgres in other languages (they all
use a very similar API). I have programmed in less typed and generally
easier
languages such as perl, PHP3, Java and C++ (C++ is far more different than C
than I ever thought) so you can sort of see my background, and why I have
made
such common C memory management mistakes  (Richard can point out a few
)   ).

Initially I didn't know I could actually do what I wanted to do (this has
been
my "maiden" program in C), so I tried to  stick with something I knew just
to
see if I could actually do it.  I figure that it wouldn't be too hard to
change
the places I actually call the postgres API into a generic interface.

My final vision in terms of Evolution is to possible have a backend like
wombat, so that the client side abstraction is complete, and we just make
general SQL calls to the backend server.  Then we could almost have it use
an
database system from the Act! standard xBase format to Postgres, or MySql.
Just change the module that the backend calls.  ( I think it's dlopen ?
dlopen("postgres-sql.a");   ??? )

The other option is to not have a backend like wombat, but to use different
cfdb widgets.   Call this one pg_cfdb, and
a mysql one called my_cfdb.  The only place where the widgets are used is in
the actual contact manager "layout", not for any dialog boxes etc...  (more
about the "greater" vision later  =-)   )  This might be easier (read below)
though.

The reasons I wrote the cfdb widget and didn't use gnome-db or the like are:

- I wanted a general purpose GTK only widget so people could use it for
small
rapid database apps (I run a few websites for work that have "Request more
info" forms that dump into a postgres database (six different websites) and
I
have written a program in <shock> VB </shock> that lets them view the
results.
I create a new one of these simple database view programs every month. The
only
thing I like about <horror> VB </horror> is that it is very easy to create a
little database app (once you start going beyond that it is terrible).   Put
a
database control on the form, give it the database string, put a few
textboxes
and label, select the database control in the textbox properties and the
field
you want to fill it with, and hit run.  The cfdb widget  makes it very
similar
to this.  ) This then leads to second reason.

- From what I read about gnome-db it is a very big library, and in terms of
a
widget just for the "layout" side of things, gnome-db didn't seem to be
atomic(?)(self contained) enough.  You can't just pass it a const connection
string and have it do it's stuff.  And this is exactly what you need for a
set
up like Act! (or Evolution contact manager), where you have a user definable
main form ( the "layout" ) that is more or less independent of everything
else,
except when you need to pass a lookup string (query) or get a particular bit
of
information from it (the current contact id or name so you can pass it to
the
calendar).  **  The rest of the program can be fixed, but the "layout" has
to
be as dynamic as possible. **

- (This one has just come to me) It might be easier if we have different
versions of the widget to support different databases.  With the way the
database widget is designed (have people had a look at how it does
relational
lookups?) we could have multiple databases running off the one form...  The
main cfdb widget uses postgresql, and there are some other that use
different
db's.

<ludicrous idea>

----------------------------------------
|            (Client CFDB - postgres)                                    |
|
|
|   --------------------------------             |
|   |          (Payment CFDB - mysql)                    |              |
|   |-------------------------------|              |
|
|
|
|
|   ----------------------------------         |
|   |      (Someother Related Information - Orcale ) |         |
|   |
|         |
|   ----------------------------------|        |
----------------------------------------

</ludicrous idea>

There are of course problems ( how do you make sure that the primary key for
payments matches the foreign key in Client etc...) but it highlights how
flexible the cfdb widget is.  Try doing that with Act!  =)   ( not that you
can
do any sort of relational thing with Act!)


As for the Evolution - Contact Manager....

May be I should write a functional spec or a "vision" for how I see
Evolution
contact manager.
Here's the basics.

The whole thing consists of 4 main parts:
1. Main Evolution Shell (plus the other parts of evolution e.g. calendar
etc.)
2. Evolution_Contact Manager ( anyone for Contact Force? or too
ostentatious)
    2.1 Outer Evolution _Contact Manager program
    2.2 Main Evolution_Contact Manager "layout"

2.1 Contains the support stuff like Define Fields, Define Users, Report
Tools,
and any other dialogs, tools, or such.


2.2 Is the dynamic form builder that houses what the user actually sees

"layout" <-> form builder relationship:
    The "layout" is an xml file that load the interface and the interface
details (libglade).  It contains the complete layout form, including the
Notes/History widget (will be another cfdb type widget) and any other table
type setup, any cfdb widgets, and any entry / viewing widgets (Entries or
Combos).  It stores all "Visual Properties". It doesn't store field
attributes
(what the field is, whether it is read-only etc.)

Database <-> "layout" (Database <-> form builder)  relation ship.
    The form builder has a field properties reader.  The database contains a
table called cfc_fields (contact force control) which records all the actual
field properties (can the field be edited, is the field a drop down box
combo
field (these are actually proper relation fields that have a separate table
for
the combo box contents), are new items in the drop down list automatically
added to the drop down table, or do they have they have to go through the
define fields dialog (ala Act!), etc.])

The cfc_fields table has
- field_id (auto - increment identifier)

- table_name --
                        |--- Primary key
- field_name --

- field_text_name (Fields are assign text names by the user and converted to
database compatible names e.g.. Payment From Suppliers field ->
payments_from_suppliers )

- field_type (int bool text (or varchar) currency etc.)  (it is very hard to
find the type of field in Postgres)

- default_value (impossible to lookup in postgres)

- field_be_blank ( Allow NULLS in the field )

- dd_has  ( Does the field have a combo and is it filled with a table )

- dd_from_other  ( Drop Down From Other table - In Act! you can fill a Combo
box from another table's combo box (it is not really need with us b/c of the
way we assign tables to combo boxes)

-dd_table ( The table that the combo box is filled from )

- dd_allow_edit ( Is the drop down box fixed?  - e.g. Already contains all
the
countries of the world so we don't want / need people adding to it)

- dd_auto_add ( Do we add any new value typed into a combo straight into the
dd
table? )

- dd_show_descript ( Do we have a combo that has 2 column in the dro down
list
that shows the items value and description? )

Drop Down Tables (dd_table) are normal tables that have a primary key (did),
an
item (dd_item) [this is what the combo is filled with] and a description
(dd_description) of what the item is   ( This is all heavily borrowed from
other contact managers )

Drop Down Tables have a dd_ prefix and a converted name  e.g.  user defined
States drop down goes to dd_states etc.  (all these text -> database name
functions have already been written)

Normal Contact Manager Tables have a prefix of cf_  and a converted name.
You
can only have 1 table per database widget.

- ***-  We need a table widget that is similar to cfdb (takes a table, some
field names and a lookup and displays the entire "record set").  This would
be
the basis for the Note/History widget .  The history part could be handled
by
triggers (does mysql support triggers?)  And the notes part could be handled
by
putting data straight into the table (via the tablewidget).


I hope the above doesn't confuse you to much..  (I tend to ramble sometimes
;-)    )...

<[NOTE from Tom M. I snipped a great deal of discussion of bugs and
such...]>


Thanks to everybody else as well!!!

Ashley Cambrell

****** Original explanation with the widget code... ******

Hi All !!

Sorry for the delay, I had a very busy day yesterday and I didn't have time
to change a couple of things with the widget before I sent it out.

Please read the README file cause it explains the widget and what to do to
create the sample database for the test program. (the README is pretty
detailed)

Have a look at it and see what you think  (should be very easy to
incorporate into a libglade type setup for dynamic layouts)

I can see already that it will needs to be able to run arbitary functions at
certain times (function pointers) for things like custom data validation
(there is none at the moment) and also for use in the contact manager.  (The
Contact Manager stores field specific information in the database and the
layout (libglade  xml file) only stores screen specific information, so
there need to be a way of setting the correct attributes of certain widgets
on widget registration)

Combo support also needs to be added, which sort of ties in with the above..

And of course some memory management stuff   "destroy" function.

Anyways...  Here it tis...

Ashley Cambrell

***** End original explanation ******

cfdb-0.6.tar.gz