[Kexi] Using Kexi databases/queries in KWord (or other KOffice apps)

[Sebastian Sauer]

On Friday 28 July 2006 00:17, Matija Šuklje wrote:
> I subscribed now, so you don't have to use CC in case you intended to reply
> to my previous mail.

Ups. Sorry, I read your mail after sending the other one :)

So, for the users registered here, I replied with following mail at the 
koffice mailinglist;

----------  Forwarded Message  ----------

Subject: Re: Using Kexi databases/queries in KWord (or other KOffice apps)
Date: Friday 28 July 2006 16:53
From: Sebastian Sauer <mail at dipe.org>
To: matija.suklje at rutka.net
Cc: For discussion about KOffice <koffice at kde.org>

Hi Matija,

first a lot of thanks for the feedback. I may like to add, that we don't
ignored it, but just totaly agree with most of what you wrote. So, let's go
into details;

> Problem I wanted to solve:
> My brother and me are doing garden work for the whole house and have to
> write reports what and when we did something to get paid.

So far it sounds like a quit simple task. I used something similar to cash up
my worked hours. I used KSpread for that case. I normaly did it that way;

1. On a per document base
That way I am able to archive the monthly calcs in a very easy way on a per
file base. Also at any time I've only to deal with the data that is actualy
needed and not with the old ones where I already got payed for anyway. The
problem I had here is to be able to generate some kind of statistics for last
n months to be able to compare e.g. the times I normaly had to spend on a
single project or to just sum everything up I earned this year already.

2. Put everything into one single document
While KSpread got just yesterday very great speed improvments, the document
may still grow to a size where it's not fun to work with in anymore. But from
my own experiences, it's not that easy to reach that limit (btw, every
storage-method has it's limits, e.g. MS Access may start to get slow and just
don't performs queries any longer with more then ~50k records on his default
JET-engine) with just some thousands of numeric values. This is the solution
I used for a quit long time (more then a year) and it just worked very well
for my needings. I was able to calc everything, to create nice printable
reports and statistics.

3. Use a database as storage backend
While it may enough to just don't bother with a database backend for just
 some thousand of records, it may still very useful for more records, to earn
 better scaling, more speed or just reuse the backup-strategies provided by
 such a db-backend. The topic of databases is normaly a quit complex task. If
 it comes to professional usage, functionality like calculations or like
 statistics are pushed to the backend by using additional tools for the
 specific functionality or by using those "developing-languages" the database
 itself provides to deal with the data. So, long sentence, short conclusion;
 if there is a way to avoid databases, avoid them :)
So, let's take a look at the way KSpread deals with data compared to Kexi.
KSpread reads all of the data into the memory, deals with the data (e.g.
applies styles to display it, performs calculations, etc.) and once done
dumps out the data from the memory back to the storage (normaly a single
ods-file).
Kexi connects with a database and depending on what should be done, queries a
single record (so, just a small part of the whole data), deals with the data
(e.g. changes values within that single record, displays them, etc.) and if
needed pushes the single record back to the database to let the db save the
changes. To perform actions like summing values from different records up or
calc the average, is _not_ done by Kexi itself rather then by the
database-backend cause the database-backend knows best how to do it in the
most effective way. While such a clean split between the backend and the
frontend has a lot of advantages, it also limits the usage to what the
backend may support. Since normaly more then one backend is supported (for
the case of Kexi at least 3+n where n>=1 and n[0]==firebird :), the
functionality may got limited to what all of the backends supports. That's a
problem KSpread does not have. It does not depend on any 3th party code, on
some "standards" nobody really satisfies (SQL), etc.
So, again a long sentence with a short conclusion; it's not that easy to
connect KSpread with Kexi in a way that both are working transparently (as in
not doing import from database what is btw already possible with KSpread, see
Insert=>External data=>Database) together. Well, not easy doesn't mean
impossible since nothing is impossible :) See also the links at the bottom.

> How I *imagined* it could be done:
> - make a DB in Kexi
> - make a document (or template) in KWord that would query the DB and for
> that month
>     - make a spreadsheet table showing when what was done by whom ordered
> by date

That may possible with KWord's mail-merge. You are able to e.g. store the
KexiDB at a MySQL database and then use KWord's mail-merge to merge those
data into your document using QtSQL to access the MySQL database.
Another way may to use dcop (dbus with kde4/Koffice 2.0) from within a Kexi
script to control KWord or/and KSpread. So, it should be possible to iterate
e.g. with a simple Python or Ruby script through the database, format the
data and then insert the data e.g. at KSpread. KSpread from the 1.6-branch
(according to the releaseplan 1.6 alpha1 will be published next days/weeks)
does contain the ScriptEditor which has some example python-scripts that
demonstrate how to import data from a KexiDB into a KSpread document or how
to access KWord with dcop). But while it's possible, I would not see it as
optimal or as nice solution. It's just a "it works that way, but will maybe
change in future releases since it's not the best solution" thing :-)

>     - make a shortened report how work each "worker" has done and how much
> he earned in that month (e.g. Worker A watered the garden 12 times, cut the
> grass 5 times and therefore earned XYZ € this month)
>     - attach a short price list.
> - eventually maybe even make a script that would generate such a document
> by itself alltogether

I guess it really sounds like a very common solution. So, if you are
interessted, I could try to provide some help to get this task done. My main
interesst here would be, to have at the end a nice working template for
KSpread ( http://www.kde-files.org/index.php?xcontentmode=611 ), so that
other users don't need to reinvent the wheel.

> What I've done so far:
> - make a DB in Kexi with a table for adding when who's done what in the
> garden and a pricelist table
> - made an interface for adding/viewing the table in Kexi ...and deleted it,
> finding out I don't need it :P
> - found out I've grinded to a halt...

I Would really recommed to use KSpread without Kexi for that task. From my
point of view it looks as that may the better, easier and even more lighter
solution. But since I don't try to convince here and since you are the one
who knows best what your needings are, I could at least try to help you to
get some steps forward :)

> What I think still needs to be done, but have no clue how to do:
> - make a query to list all the work done in the last month (or another, if
> asked to)
> - make a query to sum up all the work done by a "worker" and calculate how
> much pay he should get

Those both are simple SQL select-statements. But since they depend strong on
how you designed your tables...

> - put all that into KWord   --- seems imposible to me right now o_O
> - after that maybe toy with making a script to do it all automagically :P

dcop with Kross or mail-merge as outlined above...

> Soooo, after this rather lenghtly explanation of what I've tried to do (and
> how very miserably I've failed at it), I ask for any help - even if it's
> "sod that and go make a spreadsheet out of it - it'll be simpler!".

hehe... yes, for your special case it really seems simpler. Not only to
design, but also to maintain and work with.

> I found it quite odd that I couldn't find any way to call queries from Kexi
> in KWord. Being a newbie in the office DB usage, it just occured to me that
> this was the only reason why someone would actually want a DB app in an
> office bundle - to get more complicated queries from a (bigger and/or
> complicated) DB into a simple spreadsheet, document or even
> drawing/diagram.

And that's where I agree absolutly. So, to sum it up; the integration between
the apps could be improved much more to allow such workflows (or other
workflows where no way around using a database does exist). It sounds as you
should really try to create a wishreport for this at http://bugs.kde.org to
be sure that wish doesn't got lost on the one hand and to allow others to
agree with your wish too and to vote for the wish or even extend it with
there own usage-scenarios.

Just as extension re the topic of more integration of Kexi with the other
KOffice-applications you may also like to take a look at
* http://www.kexi-project.org/wiki/wikiview/index.php?KOfficeIntegration
* http://kde.ground.cz/tiki-index.php?page=koffice2roadmap#id242570

So, you are really not the first one who complained about that and we take it
very seriously and plan to just do it. But such detailed usage-scenarious as
you wrote here are useful anyway. So, thank you a lot!

> p.s. I sent this e-mail already to the kexi list some days ago, but got no
> reply yet.

Yes, we arn't around fulltime and as you may see with this mail, it cost some
time to write a nice answer to such a nice question :-)

--
Sebastian Sauer aka dipesh[sebsauer]
http://www.dipe.org/public_key.asc
Fingerprint: 8F1E 219B 16E6 4EC7 29CC F408 E193 65E2 9134 2221
Coder in http://www.koffice.org && http://www.kmldonkey.org