r15094 - gnucash/branches/gda-dev - Initial commit of initial gda backend framework. See GDA_STATUS.

[Phil Longstaff]

On Wed, 2006-08-11 at 13:54 -0500, Derek Atkins wrote:
> Phil Longstaff <plongstaff at rogers.com> writes:
> 
> >> The business ddl should live unser src/business/business-core/gda
> >> (along with any other business-specific GDA code).
> >
> > I agree. I created that ddl file by looking at the xml code and creating
> > a ddl file from it.  I haven't even tried to see if mysql or any other
> > db will accept the ddl.  Eventually it will be moved (or even moved
> > internally into the code in some fashion).
> 
> Ahh, I see..  I think it might be a good idea to derive the DDL from
> the C code that generates the XML, instead of looking at the XML
> itself.  The XML might not show you optional parameters.

Terminology glitch: "xml code" == "C code which generates XML" i.e. I
did what you suggest.

> 
> >> You should just add a configure test to find libgda and use that.  You
> >> could add a --with-gda-prefix= so you could bypass the specific
> >> pkgconfig checks.  But you shouldn't hard-code the path in the
> >> Makefile.  The configure script should export at least two macros:
> >> GDA_CFLAGS and GDA_LIBS (or LIBGDA_, and it could be _INCS instead of
> >> _CFLAGS) -- but you get the point.
> >
> > I agree.  However, I took at look at configure.in to see how to do this
> > and saw lots of AC_xxx macros which I didn't understand.  I didn't see
> > another --with-xxx-prefix option that I could use as a template.  I did
> > what worked for me in the short term, and have it on the TODO list to
> > configure it properly.
> 
> google is your friend.  (honestly, someone can help you with this
> part).  AC_ARG_WITH() is what you need for the --with-xxx-prefix part.
> You can look at the --with-qof= for an idea.  And there are plenty of
> places where we use PKG_CONFIG to extract the cflags and ldflags for a
> package.

I know.  It just wasn't at the top of my todo list.

> 
> >> > +Execution:
> >> > +- A basic GDA backend framework now exists.  This framework accepts URLs of
> >> > +the form gda://DSN:USERNAME:PASSWORD.  "gda" is required.  "DSN" represents
> >> > +a dataset configured in ~/.libgda/config.  USERNAME and PASSWORD are not
> >> > +required but can be specified.
> >> 
> >> How would this work with SQLite?
> >
> > It will work with SQLite if a DSN is configured.  Another TODO item is
> > to switch file:// over to use SQLite which will just take a file name.
> 
> Sorry, please assume I know nothing about GDA.  I'm an end user.  I
> have no idea what a DSN is, what it means, or how to configure one.
> As a user I just want to type in a filename and let gnucash do its
> magic; under the covers gnucash should translate that filename into
> whatever URL it needs to pass to GDA....
> 
> Note that switching file:// over to SQLite is a little more
> complicated..  In particular we need to deal with updating from
> XML->SQLite, and probably change the File -> New File subsystem to
> request a filename (or DSN, whatever that is) for the new file so it
> can get created immediately.

During development, use of a DSN will be required, since my aim is to
get the rest of the SQL working.   Note that a DSN can request the use
of SQLite so no capabilities are lost.  Modifying the connection system
to handle SQLite db files is on the TODO list but not at top priority.

Question: A user updates to GC 2.2 (or whatever rev has the new backend)
and opens his XML file.  He then saves the file using the Save button.
Is it saved as SQLite?  Does the need for the Save button disappear
because he runs from then on with the GDA backend?  Or, does he
specifically need to Save As to convert to SQLite?

> 
> > BTW, the file UI is built around files.  Save As, for example, doesn't
> > allow me to save as gda://gnucash.
> 
> This is a bug that we need to fix.

Is there a bug logged for it?  If not, I'll log one.

> >> > +- The backend will save commodities and load them on startup.  However, they
> >> > +will not have the correct GUIDs.
> >> 
> >> I'm not convinced that Commodidies need to have (or SHOULD have)
> >> GUIDs.  In fact, the XML backend doesn't store the commodity GUID,
> >> either.  Everything is referenced PURELY by the Commodity
> >> <Namespace,ID> tuple.  The database should mirror this.
> >
> > The problem is ran into is how to handle it when a commodity's name or
> > namespace is changed.  If a guid is used as the commodity ID, I just
> > update the commodity.  If I use the tuple, I need to update every
> > account, transaction, ... that uses that commodity.  The XML backend
> > doesn't have this problem because internally, the name can be changed,
> > and the XML backend can write the new tuple with the new namespace or
> > ID.
> 
> Good point.  Well, the only GUIDs you need to care about are the ones
> that are actually used in the database.  But we also need to let
> gnucash (the app) update currencies that change over time.  So perhaps
> what we want to do is load all the currencies from the DB, then
> "merge" them in from the application?  What this DOES mean is that the
> app's "create currencies" function would need to be changed to see if
> a currency already exists and if so just update it (if necessary) with
> the new settings -- and this could change the contents of the DB.  But
> the DB really only needs to store the currencies in use by the rest of
> the database.

I tried merging.  If I load a commodity from the db (note that I need to
use memcpy to stamp the guid because there is no
gnc_commodity_set_guid(), qof_instance_set_guid() or
qof_entity_set_guid()).  gnc_commodity_table_add_commodity().
gnc_commodity_table_insert_commodity() will very nicely see if a
commodity with the same namespace/mnemonic pair exist and copy the new
one over the old one.  However, even if it copies over the guid, it
doesn't rehash the commodity in the qof_collection, so the commodity
can't be retrieved by the guid in the db.

What I'm implementing for now is a scheme to translate between
namespace/mnemonic pairs and guids in the db.  If a row in the db (e.g.
account table) has a certain commodity guid, I look for that guid in the
db commodity table and get namespace/mnemonic.  I then use
gnc_commodity_table_lookup() to find the commodity with that
namespace/mnemonic pair, and use that gnc_commodity for
xaccAccountSetCommodity().  If I need to commit a gnc_commodity, I get
its namespace/mnemonic pair, find the db commodity with those values
(during the begin_edit), and then update that row during the
commit_edit().  This maintains all db guid references and allows the
user to update namespace and/or mnemonic for any commodity.

This is all kind of ugly, but my preference right now is to get a gda
backend that works.  Later, commodity handling can be cleaned up.

> 
> >> > +	PRIMARY KEY(slot_id)
> >> 
> >> Actually, I think the primary key would be the <obj_guid,name> tuple.
> >
> > Could be, since as far as I can tell, the slot info really belongs to
> > the owning object.
> 
> Yes, the slot info really does belong to the owning object.

I haven't gotten to slots yet.  I'll need to in order to properly handle
accounts/other objects.

> 
> [snip]
> >> For example, I could see how you could define the following mappings:
> >> 
> >>   column -> QofParam
> >>   QofType -> GdaType
> >> 
> >> and then use this abstraction to say something like:
> >> 
> >> AccountColumns[] = { QOF_PARAM_GUID, ACC_TYPE, ACC_CMDTY, ... };
> >> 
> >> Then you can use the QofParam Getters/Setters...
> >
> > This was partly to experiment with Qof and with Gda and GValues and ...
> > to see how things actually work.  Unfortunately, not all db fields can
> > be handled via a mapping (e.g. Account parent GUID).
> 
> Note that the mapping would work back into QOF.  The Account parent GUID
> already has a qof mapping, which would use qofAccountSetParent() which
> just takes a QofEntity..  But keep in mind that QOF knows that the
> parent is of type ID_ACCOUNT, so you would know how to convert between
> ID_ACCOUNT and the row content.  E.g., you would know that to STORE
> the data you would use:
> 
>     guid_to_string(get_guid(qof_getter(obj)))
> 
> and to LOAD this you would use:
> 
>     qof_setter(lookup_by_guid(ID_ACCOUNT,string_to_guid(row_data)))
> 
> This is all abstracted because you know how to store an ID_ACCOUNT QOF
> Type from the QOF Param Tables and use the QOF Getters/Setters.
> 
> I still believe that all db fields can be handled via a mapping.  You
> just might need to add additional handling for certain QOF types...
> In particular QOF Types that are compsite objects are referenced by ID
> so you would just need a special handler.

I have a simple mapper between columns and local variables which isn't
quite how I want it, but is working.

The problem with the account parent example is that there is no way to
set account B as the parent of account A.  Instead, you need to set
account A as a child of account B.

One issue I thought of today: The idea of mapping all db fields is made
more complex by gnc_numeric.  Currently, I store the num and denom as 2
fields.  This knowledge is hidden in the code which handles the
mappings.  However, I may need a 3rd field which will be a real with the
value of the gnc_numeric.  Otherwise, num and denom will not be too
useful if I need comparisons (e.g. find all splits with a value >
$3.69).  Even a test for equality is difficult, because 10/100 ==
100/1000 but how I would determine this just using num and denom values
is hard.  An alternative is to see what capabilities different SQL
engines have.  Can I ask for SELECT * FROM split WHERE
(value_num/value_denom > 3.69)?

> 
> >> > +	pAccount = xaccMallocAccount( pBook );
> >> 
> >> You should begin_edit here...
> >
> > except that all I want to do is create a new account structure.  I
> > really don't want any background stuff.  I certainly don't want to
> > commit (and I've already added a flag turning off commits while doing an
> > initial load).
> 
> Hmm... True...

I've already found a number of ugly uses of begin/end edit.  As I
mentioned in an earlier e-mail, if I create an account, the commodity is
updated twice (two UPDATEs to the db).  Also, if you open the Price
Editor dialog and press Add, a GNCPrice is added to the db (when the
dialog opens, it creates a new GNCPrice and sets the source to
"user:price-editor" which forces a commit).  I haven't checked how it
would work if I press cancel and don't actually create the new price.  I
assume it destroys the GNCPrice which deletes it from the db.  Of
course, the real fix is to not create the GNCPrice until the user
presses OK.  I'll log a bug for this.

> >> [snip]
> >> > +			sprintf( cmdbuf, "UPDATE accounts set name='%s',account_type_id=%d,commodity_guid='%s',parent_guid='%s',code='%s',description='%s' WHERE guid='%s';\n",
> >> > +				name, type, commodity_guid_buf, parent_guid_buf, code, description,
> >> > +				guid_buf );
> >> 
> >> This could easily overwrite the buffer size.  Also, what happens if
> >> the user puts parens into the data?  E.g., what happens if I have an
> >> account name of something like "Foo's Account" -- that would cause a
> >> SQL error.  Worse, this could cause a user to be able to potentially
> >> execute arbitrary SQL.
> >> 
> >> Doesn't GDA have a feature to pull variables from an argument array
> >> instead of printing them into standard SQL String?  E.g., something like:
> >> 
> >>    gda_build_query("UPDATE accounts set name=%1,account_id=%2,...",
> >>                    name, type, ...);
> [snip]
> >> Again, you've got a huge potential for buffer overrun here.   We should
> >> probably use g_strdup_printf() here, again, or at least be amenable to
> >> arbitrary string sizes.   Also, notice this SEGV waiting again?  ;)
> >
> > Again, the segv is a FIXME.  I need to look more at how to handle error
> > conditions.
> 
> There's also the string-quoting issue...
> 
> > Thanks for the comments.
> 
> You're welcome.  I hope the (constructive) criticism has been
> useful and helpful.

Yes it has.

Phil