[prev in list] [next in list] [prev in thread] [next in thread] 

List:       koffice
Subject:    Re: Using Kexi databases/queries in KWord (or other KOffice apps)
From:       Sebastian Sauer <mail () dipe ! org>
Date:       2006-07-28 14:53:56
Message-ID: 200607281653.56951.mail () dipe ! org
[Download RAW message or body]

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
____________________________________
koffice mailing list
koffice@kde.org
To unsubscribe please visit:
https://mail.kde.org/mailman/listinfo/koffice

[prev in list] [next in list] [prev in thread] [next in thread]